快速学会MySQL常用操作方法

港控/mmm° 2022-09-08 06:18 211阅读 0赞

### 文章目录 ###

*  什么是数据库？
 *  数据库管理系统？
 *  数据库存储数据的特点
 *  什么是关系型数据管理系统？
 *  MySQL数据库
 *  MySQL数据库服务启动
 *  客户端连接MySQL
 *  导出和导入数据
 *   *  从MySQL服务器导出数据
     *  导入备份的sql脚本到MySQL服务器
 *  MySQL数据类型
 *   *  （1）数值类型
     *  （2）日期时间类型
     *   *  \`datetime\`和\`timestamp\`区别？
     *  （3）字符串类型
 *  数据库定义语言DDL
 *   *  创建数据库
     *  使用数据库
     *  注释
     *  显示数据库
     *  删除数据库
     *  创建表
     *  查看当前数据库所有表
     *  查看表结构
     *  改变表结构
     *   *  添加一列
         *  修改列的数据类型
         *  修改列名称和数据类型
         *  删除某一列
         *  表的重命名
     *  删除表
     *  ==总结==
 *  数据库操纵语言DML
 *   *  向表中插入数据
     *  更新表中的数据
     *  删除表中的数据
 *  数据完整性
 *   *  1. 主键约束
     *  2. 非空约束
     *  3. 外键约束
     *  4. 唯一约束
     *  5. 默认约束
 *  数据库查询语言DQL
 *   *  查询所有的数据
     *  查询指定的列
     *  列别名
     *  表别名
     *  DQL的算数运算符
     *  DQL的比较运算符
     *  区间查询
     *  in 集合查询
     *  like 模糊查询
     *   *  1. 前面精确后面模糊
         *  2. 前面模糊后面精确
         *  3. 前后模糊中间精确
     *  distinct 过滤掉重复的行
     *  group by分组和汇总查询
     *  having
     *  order by 排序
     *  limit 返回最大限制的行数
     *  函数
     *   *  聚合函数
         *   *  sum
             *  max
             *  min
             *  avg
             *  用\`count(\*)\`，\`count(1)\`谁好呢?
         *  字符串函数
         *  数值函数/数学函数
         *  日期函数
         *  流程函数
         *  其他函数
     *  子查询
     *   *  子查询小结
         *  \`where\`和\`having\`区别？
     *  多表联合查询
     *   *  内连接
         *  左外连接
         *  右外连接
         *  合并查询
         *  自连接
 *  用户权限设置
 *  MySQL事务 TCL
 *   *  事务的四大特征
     *  事务的提交方式
     *  操作事务
     *  事务隔离级别
     *   *  如何查看MySQL数据库隔离级别？
 *  变量
 *   *  变量分类
     *   *  全局变量
         *  会话变量
         *  用户变量
         *  局部变量
     *  用户变量和局部变量的对比
 *  存储过程
 *   *  参数
     *  调用存储过程
     *  水仙花数
     *  1~n的累加求和
     *  求三角形的斜边
 *  视图

# 什么是数据库？ #

**存储的数据的容器**

# 数据库管理系统？ #

`DataBase Management System` 简称 **`DBMS`**  
简单来说，就是管理数据库的软件

# 数据库存储数据的特点 #

我们**在数据库中设计一张表，相当于在Java中设计一个类**。表由一列或者多列组成，我们也称为字段，列(字段)类似Java的“属性”。  
**表中的数据是按行存储的，每一行类似Java的“对象”**。

# 什么是关系型数据管理系统？ #

全称：`Relationship DataBase Management System`(**`RDBMS`**)

关系型数据管理系统(RDBMS)建立在数据库管理系统(DBMS)之上，**能够在实体(表)和实体(表)之间建立某种联系(关联)**。  
MySQL就是一个经典的关系型数据库管理系统(RDBMS)。

注重的是“**数据持久化**”，将内存的数据持久的写入磁盘

# MySQL数据库 #

一种开放源代码的关系型数据库管理系统

# MySQL数据库服务启动 #

1.  “`我的电脑/计算机`”–>右键–>“`管理`”–>“`服务`”–>启动和关闭MySQL服务
2.  “开始菜单”–>“`控制面板`”–>“`管理工具`”–>“`服务`”–>启动和关闭MySQL
3.  “`任务管理器`”–>“`服务`”–>启动和关闭MySQL
4.  以“管理员”身份运行DOS输入下面的命令

net  stop  MySQL服务名
    net  start  MySQL服务名

# 客户端连接MySQL #

1.  命令行

mysql -h 主机IP地址 -P 端口号	-u 用户名	-p回车
    Enter Password:密码

1.  可视化工具Navicat或SQLyog等

# 导出和导入数据 #

## 从MySQL服务器导出数据 ##

C:\Windows\System32> mysqldump -h主机地址 -P端口号 -u用户名 -p密码  --databases 数据库名 > 文件路径/文件名.sql
    
    例如：mysqldump -hlocalhost -P3306 -uroot -proot --databases java0817 > e:\java0817.sql

## 导入备份的sql脚本到MySQL服务器 ##

mysql> source  sql脚本路径名.sql
    
    例如：mysql>source e:/db1.sql;

# MySQL数据类型 #

## （1）数值类型 ##

*  整型系列：xxxInt

> int(M) M表示数据显示的宽度,与实际存储的长度无关

![在这里插入图片描述][d3383d1c4e0f4b6eb85e5e5254fa7b02.png]

*  浮点型系列：float,double

> double(M,D)：表示**最长为M位**，其中**小数点后D位**
> 
> score double(5,2) 一共有5位，其中小数占据了两位 999.99
> 
> 例如：double(5,2)表示的数据范围\[-999.99,999.99\]，总长度为5，其中小数占据两位。如果超过这个范围会报错。

*  定点型系列：decimal（底层实际上是使用字符串进行存储）

> decimal(M,D)：表示**最长为M位**，其中**小数点后D位**

*  位类型：bit

> 字节范围是：1-8，值范围是：bit(1)~bit(64)，默认bit(1)

> 用来存储二进制数。  
> 对于位字段，直接使用select命令将不会看到结果。可以使用bit()或hex()函数进行读取。  
> 插入bit类型字段时，使用bit()函数转为二进制值再插入，因为二进制码是“01”。

## （2）日期时间类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_28_color_FFFFFF_t_70_g_se_x_16]

### `datetime`和`timestamp`区别？ ###

1.  使用timestamp表示日期时间，如果将来不给这个字段赋值，或赋值为null，则默认使用当前系统时间来自动赋值
2.  timestamp和时区有关，受MySQL版本影响很大，工作中用的少。

## （3）字符串类型 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_23_color_FFFFFF_t_70_g_se_x_16]

*  字符串类型char,varchar(M)

> **char如果没有指定宽度，默认为1个字符**
> 
> varchar(M)，**必须指定宽度**
> 
> varchar相当于Java的StringBuilder，char相当于Java的String。数据库的数据随时都会发生变化，所以使用varchar存储字符数据。工作中char很少使用。

*  binary和varbinary类似于char和varchar，不同的是它们包含二进制字符串，不支持模糊查询之类的。
 *  一般在保存少量字符串的时候，我们会选择char和varchar；而在**保存较大文本**时，通常会选择使用`text`或`blob`系列。blob和text值会引起一些性能问题，特别是在执行了大量的删除操作时，会在数据表中留下很大的“空洞”，为了提高性能，建议定期时候用optimize table功能对这类表进行碎片整理。可以使用合成的(Synthetic)索引来提高大文本字段的查询性能，如果需要对大文本字段进行模糊查询，MySql提供了前缀索引。但是仍然要在不必要的时候避免检索大型的blob或text值。

# 数据库定义语言DDL #

创建数据库、创建表、修改表结构等，这些操作**和数据无关，只跟数据的结构有关**

## 创建数据库 ##

-- 创建数据库
    create database <数据库名称>;
    -- 判断数据库是否存在，再创建
    create database if not exists <数据库名称> default charset <字符集编码>;

## 使用数据库 ##

-- 告诉数据库服务器，我将使用哪个数据库
    use <数据库名称>;

## 注释 ##

-- 注释
    单行注释：#注释内容
    单行注释：-- 空格注释内容 其中--后面的空格必须有
    多行注释：/* 注释内容 */

## 显示数据库 ##

-- 显示数据库管理系统(数据库服务器)下面所有的数据库
    show databases;

## 删除数据库 ##

-- (1)
    drop database <数据库名称>;
    
    -- (2)数据库存在才删除
    drop database if exists <数据库名称>;

## 创建表 ##

create table <表名称>(
     列名称1   数据类型,
     列名称2   数据类型,
     ................    
     列名称n   数据类型
    );

-- 创建数据表
    create table students(
    	`id` int, #学号，主键，自动自增
    	`name` varchar(50) not null, #姓名，非空，唯一
    	gender varchar(10) DEFAULT '女', #性别，默认
    	birthday datetime, #生日
    	major varchar(20) #专业
    )default charset=utf8; #设置编码utf8

## 查看当前数据库所有表 ##

show tables;

## 查看表结构 ##

-- 第一种方式
    desc <表名称>;
    
    -- 第二种方式
    show create table <表名称>;

## 改变表结构 ##

添加一列(`add`)、修改列的数据类型(`modify`)、修改列名称和数据类型(`change`)、删除某一列(`drop`)、表的重命名(`rename`)。

alter table <表名称> [add|modify|change|drop|rename];

**下面修改表结构都对此表`book`进行操作**

create table book(
    	bid int auto_increment primary key,
    	title varchar(50)
    )default charset=utf8;

### 添加一列 ###

-- 语法：
    alter table <表名称> add  <新的列名称> <数据类型>;
    -- 添加一个字段
    alter table book add column author varchar(30);

### 修改列的数据类型 ###

-- 语法：
    alter table  <表名称> modify  <列名称> <数据类型>;
    -- 修改字段类型
    alter table book modify column bname varchar(100);

### 修改列名称和数据类型 ###

-- 语法：
    alter table  <表名称>  change <旧的列名称> <新的列名称> <数据类型>;
    -- 修改字段名
    alter table book change column title bname varchar(50);

### 删除某一列 ###

-- 语法：
    alter table <表名称> drop <列名称>;
    -- 删除字段
    alter table book drop column author;

### 表的重命名 ###

-- 语法：
    alter table <表名称> rename <新的表名称>;
    -- 修改表名
    alter table book rename to bookInfo;
    alter table bookInfo rename bookInfos;

## 删除表 ##

-- 语法：
    drop table <表名称> ;
    -- 删除表book
    drop table book;

## 总结 ##

-- 数据库操作
    create database 
    drop   database
    use 数据库名称
    show databases;
    -- 表的操作
    create table 
    drop table
    alter table
    -- 查看表结构
    desc 表名称;
    show create table  表名称;

# 数据库操纵语言DML #

增加(插入)数据 insert 、删除数据 delete、修改数据update。以上操作**只改变表中的数据不会改变表结构**。

## 向表中插入数据 ##

**下面修改表数据都对此表`student`进行操作**

-- 删除表
    drop table if exists student;
    
    -- 创建数据表
    create table students(
    	`id` int, #学号，主键，自动自增
    	`name` varchar(50) not null, #姓名，非空，唯一
    	gender varchar(10) DEFAULT '女', #性别，默认
    	birthday datetime, #生日
    	major varchar(20) #专业
    )default charset=utf8; #设置编码utf8

-- 第一种插入数据，语法如下：
    -- 为表的所有列插入数据
    insert into <表名称> values(值1,值2...值n); -- 每个值之间以半角逗号分离
    
    -- 例如：向tb_province表插入一条数据
    insert into student values (default,'颤三',default,'1955-4-5','计算机'); #增
    
    -- 第二种插入数据，语法如下：
    -- 插入部分数据，列数量和值数量必须一致
    insert <表名称>(列1,列2)values(值1,值2);
    
    insert into student(id,`name`,gender,birthday,major) values
    (default,'李四',DEFAULT,'1955-4-5','计算机'); #增
    
    -- 第三种插入数据，语法如下：
    -- 在一个insert关键字中插入多条数据
    insert  <表名称> values (所有的列名称), (所有的列名称),(所有的列名称);
    
    insert into student values
    (default,'方法','男','1955-4-5','计算机'),
    (default,'华为','男','1955-4-5','计算机'); #增

## 更新表中的数据 ##

-- 语法：
    update 表名称 set 列名称=值,列名称=值.... <where 条件>;
    -- 如果修改多个数据由半角逗号分隔
    -- where表示行过滤，只修改满足条件的数据
    
    -- 修改1号学生的姓名
    update student set `name`='李四' where id=1; #改
    -- 修改专业和性别，1号学生的
    update student set major='会计学',gender='女' where id=2;

## 删除表中的数据 ##

`delete from` 只会删除表中的数据不会删除表结构

-- 语法：
    delete from <表名称> where  条件;
    
    -- 删除所有记录,自编号顺延
    delete from student; #删
    -- 删除学号=2的学生
    delete from student where id=2;
    -- 无条件 删除所有记录，要求自编号重新开始
    truncate table student;

# 数据完整性 #

**下面修改表结构都对此表`student`进行操作**

## 1. 主键约束 ##

**`primary key`** 非空并且唯一

primary key(主键列)

alter table students add constraint pk_id1 primary key(`id`);
    alter table students add primary key(`name`);
    alter table students drop primary key;
    
    #自定义自动编号的初始值
    alter table student auto_increment=100;

## 2. 非空约束 ##

**`not null`** 确保列的值不能为空

**注意：主键约束通常在表的内部创建**

## 3. 外键约束 ##

**`foreign key`** **`references`**  
在多个表之间建立约束，建立外键的目的确保数据一致性、有效性。

**特征**：在某张表定义一个外部关键字去关联另外一张表的主键，外键和另一张表的主键列数据必须一致，否则就违背了数据完整性。

语法： foreign key(外键列名称)  references  关联表(关联表的主键);

**下面修改表结构都对此表`department`和`employee`进行操作**

# 部门表
    create TABLE department(
    did int auto_increment PRIMARY KEY,
    dname VARCHAR(50)
    )DEFAULT CHARSET='utf8';
    
    # 员工表
    create table employee(
    	eid int auto_increment PRIMARY key,
    	ename VARCHAR(50),
    	gender VARCHAR(50),
    	did int
    )DEFAULT CHARSET='utf8';

**`on delete cascade`** 级联删除  
**`on update cascade`** 级联修改

# 在从表（员工表）上添加外键，关联到主表（部门表）上的主键上；
    # 级联删除/级联更新，删除或更新主表上的信息会连带着删除子表有关联的数据
    alter table employee
    add CONSTRAINT fk_dpt
    FOREIGN KEY(did)
    REFERENCES department(did) 
    on delete cascade on update cascade;

## 4. 唯一约束 ##

**`unique`** 确保该列的每一条数据都不能重复

-- 修改表：添加唯一约束
    alter table student add constraint uq_name unique(`name`);

## 5. 默认约束 ##

**`default`** 定义一个列为其赋默认值

create table default_table(
    	id int auto_increment primary key, -- 主键，非空且唯一
    	gender varchar(3) default '女' -- 默认约束
    )default charset=utf8;
    insert into default_table values(default,default);
    insert into default_table values(default,default);
    insert into default_table values(default,default);
    select * from default_table;

![在这里插入图片描述][0f2d5064d70641e98ce52abf020e3d34.png]

**小结：`没有外键`的表叫做`主表`，`有外键`的表叫做`从表`。**

**创建表顺序**：先`建立主表`，后建立从表  
**插入数据**：先向`主表插入数据`，后向从表插入数据。  
**删除数据**：先`删除从表的数据`，后删除主表的数据。

# 数据库查询语言DQL #

select  -- 筛选列名称
    from -- 确定表名称
    where  -- where后面的条件，进行行过滤，过滤掉不满足条件的行
    group by  -- 对列进行分组
    having   -- 行过滤
    order by -- 排序
    limit  -- 返回限制的行数

select *
    from tb_province;
    -- MySQL控制台向MySQL服务器发送命令
    -- 服务器先收到 from tb_province，表示要查询服务器的那张表，确定表名称
    -- 然后服务器收到 select 选择要查询哪些列，此时的 * 表示查询表中所有列
    -- from tb_province 表名称表示从服务器数据文件去检索(查询)数据
    -- 先执行from关键字后执行select关键字
    -- select * from tb_province; 表示从tb_province表中选择所有的列进行查询

## 查询所有的数据 ##

use j0705;
    -- * 表示匹配所有列
    -- 首先执行 from emp 从数据锁定j1012数据库的数据文件,然后从数据文件中选择所有列
    select *
    from student;

注意：查询数据库之前一定要指定数据库

## 查询指定的列 ##

-- as 为别名，可省略as
    select `name` as 姓名,major as 专业
    from student as S;

## 列别名 ##

列别名通常写在列名称的后面，列名称和列别名之间有一个空格

select `name` as 姓名,major as 专业
    from student as S;

## 表别名 ##

提高DQL的可读性，为后面学习多表联合查询做铺垫

select `name` as 姓名,major as 专业
    from student as S;

## DQL的算数运算符 ##

+ - * /

-- 把学生表的“年龄”+10
    select `name`,age+10 from student;

## DQL的比较运算符 ##

> >=   <  <=  
    !=和<> 都表示不等于
    = 表示等于 ，MySQL没有 == 运算符

比较运算符用在`from之后`，不能用在from之前，对条件进行过滤，过滤掉不满足条件的记录。

## DQL的逻辑运算符
    
    ```sql
    Java:   &&    ||  !
        	并且  或者  非
    MySQL:  and   or  not

通常写在`where后面`

## 区间查询 ##

**下面查询都对此表`student`进行操作**

drop table  if exists score;
    create table `score` (
    `id` int primary key auto_increment, -- 自动编号
    `sid` int,                           -- 学号
    `course` varchar ( 50 ),             -- 科目
    `result` int,                         -- 分数
    `examinDate` datetime                -- 考试日期
    ) engine = InnoDB auto_increment = 1 default charset = utf8;
    -- truncate table score;
    insert into score values(default,1,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,1,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,1,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,1,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,2,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,2,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,2,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,2,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,3,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,3,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,3,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,3,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,4,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,4,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,4,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,4,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,5,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,5,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,5,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,5,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,6,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,6,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,6,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,6,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,7,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,7,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,7,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,7,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,8,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,8,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,8,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,8,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,9,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,9,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,9,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,9,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,10,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,10,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,10,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,10,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,11,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,11,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,11,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,11,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,12,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,12,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,12,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,12,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,13,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,13,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,13,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,13,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,14,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,14,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,14,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,14,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,15,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,15,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,15,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,15,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,16,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,16,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,16,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,16,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,17,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,17,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,17,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,17,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,18,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,18,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,18,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,18,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,19,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,19,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,19,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,19,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,20,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,20,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,20,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,20,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,21,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,21,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,21,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,21,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,22,'Java',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,22,'MySQL',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,22,'HTML',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));
    insert into score values(default,22,'SSM',round(rand()*50+50),concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28)));

**`between and`** 关键字 在…之间

-- 查询2020年4月份SSM科目的成绩
    select * from score
    where course='SSM' and examinDate between '2020-4-1' and '2020-4-30';
    -- 等同于下面的条件
    -- where examinDate >= '2020-4-1'and examinDate <= '2020-4-30'
    -- 小结：between后面跟一个“低值”，and后面跟一个“高值”
    
    -- 统计2020年9月份挂科的人数
    select * from score
    where result<60 and examinDate between'2020-9-1' and '2020-9-31';

## in 集合查询 ##

**`in`** 关键字 在…里面

-- 查询Java和MySQL的成绩记录
    select * from score
    where course in('Java','MySQL');

## like 模糊查询 ##

**下面查询将使用`score`和`student`进行联合查询**

/* Navicat Premium Data Transfer Source Server : zhang_MySQL Source Server Type : MySQL Source Server Version : 50540 Source Host : localhost:3306 Source Schema : zhang Target Server Type : MySQL Target Server Version : 50540 File Encoding : 65001 Date: 25/08/2021 10:42:24 */
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
      `gender` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      `birthday` datetime DEFAULT NULL,
      `major` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 72 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1, '黄老邪', '男', '1992-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (2, '岳不群', '男', '1993-05-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (3, '令狐冲', '男', '1994-07-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (4, '洪七公', '男', '1995-09-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (5, '欧阳克', '男', '1990-01-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (6, '张无忌', '男', '1982-02-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (7, '宋远桥', '男', '1974-03-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (8, '任我行', '男', '1996-04-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (9, '风清扬', '男', '1998-05-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (10, '小龙女', '女', '1999-06-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (11, '李莫愁', '女', '1963-07-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (12, '洪夫人', '女', '1985-08-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (13, '石破天', '男', '1992-09-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (14, '欧阳锋', '男', '1995-10-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (15, '石观音', '女', '1996-11-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (16, '陈圆圆', '女', '1999-12-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (17, '孙不二', '女', '1990-05-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (18, '林朝英', '女', '1997-06-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (19, '王重阳', '男', '1992-07-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (20, '陆无双', '女', '1993-08-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (21, '耶律齐', '男', '1995-03-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (22, '任盈盈', '女', '1999-09-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (23, '周芷若', '女', '1986-08-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (24, '阳顶天', '男', '1988-11-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (25, '张翠山', '男', '1994-10-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (26, '殷素素', '女', '1983-06-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (27, '赵敏', '女', '1988-03-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (28, '宁中则', '女', '1986-08-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (29, '曲非烟', '女', '1980-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (30, '左冷禅', '男', '1978-01-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (31, '灭绝师太', '女', '1973-01-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (32, '鹿杖客', '男', '1989-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (33, '鹤笔翁', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (34, '金毛狮王', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (35, '殷天正', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (36, '黛绮丝', '女', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (37, '韦一笑', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (38, '韦小宝', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (39, '胡一刀', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (40, '苗人凤', '男', '1992-01-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (41, '余沧海', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (42, '林元图', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (43, '慕容复', '男', '1992-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (44, '王语嫣', '女', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (45, '段正淳', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (46, '乔峰', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (47, '虚竹', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (48, '萧远山', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (49, '慕容博', '男', '1992-01-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (50, '鸠摩智', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (51, '甘宝宝', '女', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (52, '秦红棉', '女', '1992-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (53, '阮竹星', '女', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (54, '丁春秋', '男', '1997-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (55, '李秋水', '女', '1993-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (56, '马夫人', '女', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (57, '段正明', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (58, '刀白凤', '女', '1992-01-03 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (59, '叶二娘', '女', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (60, '段智兴', '男', '1992-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (61, '段延庆', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (62, '李寻欢', '男', '1978-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (63, '楚留香', '男', '1994-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (64, '叶孤城', '男', '1992-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (65, '陆小凤', '男', '1992-01-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (66, '荆无命', '男', '1987-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (67, '胡铁花', '男', '1979-04-03 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (68, '白素贞', '女', '1974-06-06 00:00:00', '土木工程');
    INSERT INTO `student` VALUES (69, '木婉清', '女', '1981-06-18 00:00:00', '计算机应用');
    INSERT INTO `student` VALUES (70, '南宫燕', '女', '1996-01-03 00:00:00', '电子信息');
    INSERT INTO `student` VALUES (71, '宋甜儿', '女', '1998-01-03 00:00:00', '计算机应用');
    
    SET FOREIGN_KEY_CHECKS = 1;

**`like`** 关键字 像…一样  
**语法**：`where 字段名称 like '要查找的字符串%'`

`%`表示模糊匹配多个字符  
`_`表示匹配单个字符

要查找的字符串必须写在一对半角单引号里面

模糊查询有三种场景：前面精确后面模糊、前面模糊后面精确、先后模糊中间精确

### 1. 前面精确后面模糊 ###

-- 查询所有以'李'开头的员工姓名
    select * from student where `name` like '李%';

### 2. 前面模糊后面精确 ###

**语法**：`where 字段名称 like '%要查找的字符串'`

-- 查找专业以'机'结尾的所有学生信息
    select * from student where major like '%机';

### 3. 前后模糊中间精确 ###

**语法**：`where 字段名称 like '%要查找的字符串%'`

-- 查找专业包含'机'的所有学生信息
    select * from student where major like '%算%';

**模糊查询小结：第二种和第三种，它们查询效率不高工作中慎用。**

## distinct 过滤掉重复的行 ##

**`distinct`** `关键字`  
**distinct关键字表示过滤掉重复的行，后面跟列名称**

-- 查询学生表存在的学生信息，要求学生不能重复
    select distinct `name`
    from student;

## group by分组和汇总查询 ##

`group by` 将表中的数据分成若干组

select 列名称,聚合函数
    from 表
    group by 列名称[列别名];

-- 查询各单科最高分
    select course 科目,max(result) 最高分 from score
    group by course;

![在这里插入图片描述][8299b1f543bc43dda796a32685751cc0.png]

## having ##

having跟where关键字一样都用于行过滤，但是`where不支持聚合函数`，如果对聚合函数进行过滤此时使用having关键字。

-- 统计HTML的成绩高于该科目平均分的成绩记录
    select * from score
    where result>(select avg(result) from score where course='HTML')
    group by course
    having course='HTML';

![在这里插入图片描述][db8f122f29b34c8fac0b4fca39245751.png]

## order by 排序 ##

`order by 列名称 或者 列别名 asc 或者 desc`

> asc 升序排序  
> desc 降序排序  
> 默认as

-- 统计个人总分排名：姓名、总分 由高到低排列
    select sname 姓名,sum(result) 总分 from score
    group by sname
    order by sum(result) desc;

## limit 返回最大限制的行数 ##

limit限制最多返回的行数，工作中通常用户**分页查询**

`limit n;` 此时n表示**最多返回n表**

`limit m , n ;` 此时**m表示下标**，n表示**最多返回n条**

## 函数 ##

### 聚合函数 ###

`count`(计数)、`sum`(求总和)、`avg`(求平均值)、`max`(计算最大值)、`min`(计算最小值)  
每个聚合函数**查询结果**只能是"**`单行单列`**"

#### sum ####

计算某一列的**和**

-- 统计个人总分排名：姓名、总分 由高到低排列
    select sname 姓名,sum(result) 总分 from score
    group by sname
    order by sum(result) desc;

#### max ####

计算某一列的**最大值**

-- 查询各单科最高分
    select course 科目,max(result) 最高分 from score
    group by course;;

#### min ####

计算某一列的**最小值**

-- 使用聚合函数min，返回sal列的最低工资
    select min(sal) 最低工资
    from emp;

#### avg ####

计算某一列的**平均值**

-- 2020年5月份各科成绩的平均分
    select course 科目,avg(result) 平均分 from score
    where examinDate between '2020-5-1' and '2020-5-31'
    group by course;

#### 用`count(*)`，`count(1)`谁好呢? ####

其实，对于`myisam引擎`的表是**没有区别**的。这种引擎内部有一计数器在维护着行数。

`Innodb引擎`的表用**count(\*)直接读行数，效率很低**，因为innodb真的要去数一遍。

### 字符串函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>concat</code>(S1,S2,…,Sn)</td> 
   <td>连接S1,S2,…,Sn为一个字符串</td> 
  </tr> 
  <tr> 
   <td>concat_ws(分隔符, S1,S2,…,Sn)</td> 
   <td>同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上分隔符</td> 
  </tr> 
  <tr> 
   <td><code>length</code>(s)</td> 
   <td>返回字符串s的字节数，和字符集有关</td> 
  </tr> 
  <tr> 
   <td><code>upper</code>(s) 或 ucase(s)</td> 
   <td>将字符串s的所有字母转成大写字母</td> 
  </tr> 
  <tr> 
   <td><code>lower</code>(s) 或lcase(s)</td> 
   <td>将字符串s的所有字母转成小写字母</td> 
  </tr> 
  <tr> 
   <td>left(s,n)</td> 
   <td>返回字符串s最左边的n个字符</td> 
  </tr> 
  <tr> 
   <td>right(s,n)</td> 
   <td>返回字符串s最右边的n个字符</td> 
  </tr> 
  <tr> 
   <td><code>lpad(str, len, pad)</code></td> 
   <td>用字符串pad对str最左边进行填充，直到str的长度为len个字符</td> 
  </tr> 
  <tr> 
   <td><code>rpad(str ,len, pad)</code></td> 
   <td>用字符串pad对str最右边进行填充，直到str的长度为len个字符</td> 
  </tr> 
  <tr> 
   <td><code>ltrim</code>(s)</td> 
   <td>去掉字符串s左侧的空格</td> 
  </tr> 
  <tr> 
   <td><code>rtrim</code>(s)</td> 
   <td>去掉字符串s右侧的空格</td> 
  </tr> 
  <tr> 
   <td><code>trim</code>(s)</td> 
   <td>去掉字符串s开始与结尾的空格</td> 
  </tr> 
  <tr> 
   <td>trim(【both】s1 from s)</td> 
   <td>去掉字符串s开始与结尾的s1</td> 
  </tr> 
  <tr> 
   <td>trim(【leading】s1 from s)</td> 
   <td>去掉字符串s开始处的s1</td> 
  </tr> 
  <tr> 
   <td>trim(【trailing】s1 from s)</td> 
   <td>去掉字符串s结尾处的s1</td> 
  </tr> 
  <tr> 
   <td>repeat(str, n)</td> 
   <td>返回str重复n次的结果</td> 
  </tr> 
  <tr> 
   <td><code>replace</code>str, a, b）</td> 
   <td>用字符串b替换字符串str中所有出现的字符串a</td> 
  </tr> 
  <tr> 
   <td><code>strcmp</code>(s1,s2)</td> 
   <td>比较字符串s1,s2</td> 
  </tr> 
  <tr> 
   <td><code>substring(s,index,len)</code></td> 
   <td>返回从字符串s的index位置其len个字符</td> 
  </tr> 
  <tr> 
   <td><code>instr(s,s1)</code></td> 
   <td>从头开始查找元素第一次出现的位置</td> 
  </tr> 
 </tbody> 
</table>

/*字符函数*/
    -- 长度 utf8,汉字占3个字节
    select length('士大夫abc');
    
    -- 字符拼接
    select concat('sd','we');
    -- 大小写转换
    select upper('avd');
    select lower('DFFG');
    
    -- 截取字符串 从1开始,第三个参数为要截取的长度
    select substring('方法和速度',4);
    select substring('方法和速度',3,1);
    
    -- indexOf 从头开始查找元素第一次出现的位置
    select instr('打开两个陈国华','国华');
    select instr('打开两个陈国华','sss');
    
    -- trim 去除前后空格
    select length(trim(' abc '));
    
    -- replace 原字符串，要替换的，替换为
    select replace('打开两个陈国华','两个','10个');
    
    -- 填充 
    -- lpad左 左边填充，到11位，用 *
    -- rpad右 右边填充，到11位，用 *
    select lpad('3652',11,'*');
    select rpad('186',11,'*');
    
    set @phone='17683866724';
    select concat(substring(@phone,1,3),lpad(substring(@phone,8),8,'*'));
    
    select * from score where length(`sname`)=9;
    
    select * from score where substring(`sname`,1,1)='李';
    
    update score 
    set `sname`=concat('东方',substring(`sname`,3))
    where substring(`sname`,1,2)='欧阳';
    
    update score set `sname`=replace(`sname`,'欧阳','东方');
    
    select * from score;
    
    update employee
    set salary=salary+500
    where instr(`ename`,'狐')!=0;
    
    select * from employee where ename like '%狐%';

### 数值函数/数学函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>abc(x)</td> 
   <td>返回x的绝对值</td> 
  </tr> 
  <tr> 
   <td><code>ceil</code>(x)</td> 
   <td>返回大于x的最小整数值 向上取整</td> 
  </tr> 
  <tr> 
   <td><code>floor</code>(x)</td> 
   <td>返回小于x的最大整数值 向下取整</td> 
  </tr> 
  <tr> 
   <td>mod(x,y)</td> 
   <td>返回x/y的模</td> 
  </tr> 
  <tr> 
   <td><code>rand</code>()</td> 
   <td>返回0~1的随机值</td> 
  </tr> 
  <tr> 
   <td><code>round</code>(x,y)</td> 
   <td>返回参数x的四舍五入的有y位的小数的值</td> 
  </tr> 
  <tr> 
   <td><code>truncate(x,y)</code></td> 
   <td>返回数字x截断为y位小数的结果</td> 
  </tr> 
  <tr> 
   <td>sqrt(x)</td> 
   <td>返回x的平方根</td> 
  </tr> 
  <tr> 
   <td>pow(x,y)</td> 
   <td>返回x的y次方</td> 
  </tr> 
 </tbody> 
</table>

/*数学函数*/
    -- 四舍五入
    select round(3.14);
    select round(3.54);
    select round(3.567,2); -- 保留小数点后2位
    
    -- 向上取整 天花板
    select ceil(3.14);
    select ceil(-3.14);
    
    -- 向下取整 地板
    select floor(3.94);
    select floor(-3.94);
    
    -- mod %取余
    select mod(5,3);
    select mod(10,-3); -- 被除数是正，结果就是正
    select mod(-10,3); -- 被除数是负，结果就是负
    select mod(-10,-3);
    
    -- 3.627 截取2位，不考虑四舍五入
    select truncate(3.627,2);
    select abs(-100);
    
    -- 产生随机数(0~1) 0<= <1
     select floor(rand()*10+1);

### 日期函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>curdate()</strong> 或 <code>current_date()</code></td> 
   <td>返回当前日期</td> 
  </tr> 
  <tr> 
   <td><strong>curtime()</strong> 或 <code>current_time</code>()</td> 
   <td>返回当前时间</td> 
  </tr> 
  <tr> 
   <td><strong><code>now()</code></strong> / sysdate() / current_timestamp() / locatime() / localtimestamp()</td> 
   <td>返回当前系统日期时间</td> 
  </tr> 
  <tr> 
   <td><strong><code>year</code>(date) / <code>month</code>(date) / <code>day</code>(date) / <code>hour</code>(time) / <code>minute</code>(time) / <code>second</code>(time)</strong></td> 
   <td>返回具体的时间值</td> 
  </tr> 
  <tr> 
   <td>week(date) / weekofyear(date)</td> 
   <td>返回一年中的第几周</td> 
  </tr> 
  <tr> 
   <td>dayofweek()</td> 
   <td>返回周几，注意：周日是1，周一是2，。。。周六是7</td> 
  </tr> 
  <tr> 
   <td>weekday(date)</td> 
   <td>返回周几，注意，周1是0，周2是1，。。。周日是6</td> 
  </tr> 
  <tr> 
   <td>dayname(date)</td> 
   <td>返回星期：MONDAY,TUESDAY…SUNDAY</td> 
  </tr> 
  <tr> 
   <td>monthname(date)</td> 
   <td>返回月份：January，。。。。。</td> 
  </tr> 
  <tr> 
   <td><code>datediff(date1,date2)</code> / <code>timediff(time1, time2)</code></td> 
   <td>返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔</td> 
  </tr> 
  <tr> 
   <td><code>date_add(datetime, interval expr type)</code></td> 
   <td>返回与给定日期时间相差的INTERVAL(间隔)时间段的日期时间</td> 
  </tr> 
  <tr> 
   <td><code>date_fromat(datetime ,fmt)</code></td> 
   <td>按照字符串fmt格式化日期datetime值</td> 
  </tr> 
  <tr> 
   <td><code>str_to_date(str, fmt)</code></td> 
   <td>按照字符串fmt对str进行解析，解析为一个日期</td> 
  </tr> 
  <tr> 
   <td><code>convert(expr, type)</code></td> 
   <td>能把字段转成指定类型</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <thead> 
  <tr> 
   <th>格式符</th> 
   <th>说明</th> 
   <th>格式符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>%Y</code></td> 
   <td>4位数字表示年份</td> 
   <td>%y</td> 
   <td>表示两位数字表示年份</td> 
  </tr> 
  <tr> 
   <td>%M</td> 
   <td>月名表示月份（January,…）</td> 
   <td><code>%m</code></td> 
   <td>两位数字表示月份（01,02,03。。。）</td> 
  </tr> 
  <tr> 
   <td>%b</td> 
   <td>缩写的月名（Jan.，Feb.，…）</td> 
   <td>%c</td> 
   <td>数字表示月份（1,2,3,…）</td> 
  </tr> 
  <tr> 
   <td>%D</td> 
   <td>英文后缀表示月中的天数（1st,2nd,3rd,…）</td> 
   <td><code>%d</code></td> 
   <td>两位数字表示月中的天数(01,02…)</td> 
  </tr> 
  <tr> 
   <td>%e</td> 
   <td>数字形式表示月中的天数（1,2,3,4,5…）</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td><code>%H</code></td> 
   <td>两位数字表示小数，24小时制（01,02…）</td> 
   <td>%h和%I</td> 
   <td>两位数字表示小时，12小时制（01,02…）</td> 
  </tr> 
  <tr> 
   <td>%k</td> 
   <td>数字形式的小时，24小时制(1,2,3)</td> 
   <td>%l</td> 
   <td>数字形式表示小时，12小时制（1,2,3,4…）</td> 
  </tr> 
  <tr> 
   <td><code>%i</code></td> 
   <td>两位数字表示分钟（00,01,02）</td> 
   <td>%S和<code>%s</code></td> 
   <td>两位数字表示秒(00,01,02…)</td> 
  </tr> 
  <tr> 
   <td>%W</td> 
   <td>一周中的星期名称（Sunday…）</td> 
   <td>%a</td> 
   <td>一周中的星期缩写（Sun.，Mon.,Tues.，…）</td> 
  </tr> 
  <tr> 
   <td>%w</td> 
   <td>以数字表示周中的天数(0=Sunday,1=Monday…)</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>%j</td> 
   <td>以3位数字表示年中的天数(001,002…)</td> 
   <td>%U</td> 
   <td>以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天</td> 
  </tr> 
  <tr> 
   <td>%u</td> 
   <td>以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>%T</td> 
   <td>24小时制</td> 
   <td>%r</td> 
   <td>12小时制</td> 
  </tr> 
  <tr> 
   <td>%p</td> 
   <td>AM或PM</td> 
   <td>%%</td> 
   <td>表示%</td> 
  </tr> 
 </tbody> 
</table>

/*时间函数*/
     -- 当前系统时间 yyyy-MM-dd HH-mm-ss
     select now();
     -- 当前系统日期 yyyy-MM-dd
     select current_date();
     select curDate();
     -- HH-mm-ss
     select current_time();
     select curTime();
     -- 年
     select year(now());
     -- 月
     select month(now()); 
     select day(now()); #天
     select hour(now());#时
     select minute(now());#分
     select second(now());#秒
     
     -- 字符串转换为时间
     select str_to_date('2021-8-6','%Y-%m-%d');
     
     -- 时间转为字符串
     select date_format(now(),'%Y年%m月%d日%H时%i分%s秒');
     
     /* %Y：四位数的年份 1998 %y：两位数的年份 98 %m：两位数的月份 01-12 %c：一位数的月份 1-12 %d：两位数的天 01-31 %H：24小时制 %h：12小时制 %i：分 %s：秒 */
     
     -- 增加时间间隔
     select date_add(now(),interval 2 day);
     select date_add(now(),interval 60 second);
     select date_add(now(),interval '01:05:30',hour_second);
     select date_add(now(),interval '1 01:05:30',day_second);
     SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);
    SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);   #可以是负数
    SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);   #需要单引号
     -- 减少时间间隔 
      select date_add(now(),interval -2 day);
     select date_sub(now(),interval 2 day);
     
     -- 计算时间间隔
     select datediff('2021-8-25',now())*24*60*60;

### 流程函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>IFNULL(value1, value2)</code></td> 
   <td>如果value1不为空，返回value1，否则返回value2</td> 
  </tr> 
  <tr> 
   <td><code>CASE WHEN 条件1 THEN result1</code>WHEN 条件2 THEN result2 … [ELSE resultn] END</td> 
   <td>相当于Java的if…else if…else…</td> 
  </tr> 
  <tr> 
   <td><code>CASE expr WHEN 常量值1 THEN 值1</code> WHEN 常量值2 THEN 值2 … [ELSE 值n] END</td> 
   <td>相当于Java的switch…case…</td> 
  </tr> 
 </tbody> 
</table>

select
    	enumber 工号,
    	ename 姓名,
    	case sex
    		when '男' then '先生'
    		when '女' then '女士'
    	end 性别,
    	case pnumber
    		when 100 then '市场部'
    		when 101 then '研发部'
    		when 102 then '销售部'
    	end 部门编号,
    	case
    		when salary<5000 then '屌丝'
    		when salary>=5000 and salary<10000 then '白领'
    		else '金领'
    	end 阶层,
    	salary 工资
    from employee;
    -------------------------------
    select
    	pnumber '部门',
    	count(case sex when '男' then 1 else null end) '男',
    	count(case sex when '女' then 1 else null end) '女',
    	count(*) '总人数'
    from employee
    group by pnumber;

### 其他函数 ###

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>用法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>database()</td> 
   <td>返回当前数据库名</td> 
  </tr> 
  <tr> 
   <td>version()</td> 
   <td>返回当前数据库版本</td> 
  </tr> 
  <tr> 
   <td>user()</td> 
   <td>返回当前登录用户名</td> 
  </tr> 
  <tr> 
   <td>password(str)</td> 
   <td>返回字符串str的加密版本，41位长的字符串</td> 
  </tr> 
  <tr> 
   <td>md5(str)</td> 
   <td>返回字符串str的md5值，也是一种加密方式</td> 
  </tr> 
 </tbody> 
</table>

## 子查询 ##

在一个DQL查询中嵌套一个DQL查询，所有的子查询都包含在父查询里面。所有的子查询必须定义在一对小括号中。

子查询可以出现的地方：`where之后`、`having之后`、`from之后`、`from之前`

子查询返回的结果集有三中：单(**单行单列**)、多(**多行单列**)、多(**多行多列**)

-- 高于平均工资
    select * from employee
    where salary>(select avg(salary) from employee);
    
    -- 高于任一部门的最高工资
    select * from employee
    where salary>any(select max(salary) from employee group by pnumber);
    
    -- 平均工资高于2000的部门编号和部门平均工资
    select pnumber 部门编号,avg(salary) 部门平均工资
    from employee
    group by pnumber
    having 部门平均工资> 2000;
    -- 子查询
    select 部门编号,部门平均工资
    from (
    		select pnumber 部门编号,avg(salary) 部门平均工资
    		from employee
    		group by pnumber
    )e
    where 部门平均工资>7500
    
    -- 随机日期
    select concat('2020-',mod(floor(1+rand()*10),9),'-',floor(1+rand()*28));
    select concat('2020-',floor(1+rand()*12),'-',floor(1+rand()*28));
    
    select * from score;
    select * from student;
    -- 查看 电子信息专业的成绩信息
    select * from score
    where sid=any(select id from student where major like '%电子信息%');
    
    -- 查看女生的HTML科目成绩信息
    select * from score
    where sid=any(select id from student where gender='女')
    and course like '%HTML%';
    
    -- 查看95后的男生的java成绩信息
    select * from score
    where sid=any(select id from student where gender='男')
    and course like '%Java%' and substring(examinDate,1,4)>=1995;
    
    /* 使用比较运算符 any some all 不需要 in */

### 子查询小结 ###

1.  子查询出现的地方 `where之后`、`from之前`、`from之后`、`having之后`
2.  子查询返回的结果：单行单列(where之后、having之后、from之前) 、多行单列( where之后的any和all)、多行多列(from之后)

from之前的子查询：**伪**列

from之后的子查询：**伪**表

1.  工作中尽量不要在having后面编写子查询，而是使用伪表+where条件

### `where`和`having`区别？ ###

having主要对**分组**和**聚合函数**查询的结果集进行过滤

where 对**from结果集进行过滤**，**where不支持聚合函数过滤**，因为where先执行group by后执行

## 多表联合查询 ##

常用的多表联合查询：内连接(`inner join`)、左外连接(`left join`)、右外连接(`right join`)、自连接、全连接(`union union all`)。

### 内连接 ###

**`inner join on`**

inner join查询多张表条件**都满足**的数据(A表和B表的交集)

on **条件过滤**，过滤掉不满足条件的语句  
![在这里插入图片描述][bbeea891031640d6b65773b3cf7b3842.png]

-- 查询令狐冲的考试信息： 学号、姓名、科目、分数、考试日期
    select sc.sid '学号',stu.`name` '姓名',sc.course '科目',sc.result '分数',sc.examinDate '考试日期'
    from score sc inner join student stu
    on sc.sid=stu.id
    where stu.`name`='令狐冲';

小结：多表联合查询在指定列时**必须使用"表名称".列名称，或者"表别名".列名称**。因为必须告诉MySQL服务器你的列属于那张表。

### 左外连接 ###

**`left join on`**  
**A∩B** 查询两张表都满足的数据，以及左边表(A表)独有的数据(以左边表为主查询表)。  
![在这里插入图片描述][8ba6943ee31e4281bed080b09dbf85ad.png]

select emp.empno,emp.ename,dept.dname
    from dept left join emp 
    on emp.deptno = dept.deptno;
    
    # 找出没有正常部门的员工 左外连接
    select e.enumber '员工编号',e.ename '员工姓名',d.did '部门编号'
    from employee e left join department d
    on e.pnumber=d.did
    where d.did is null;
    
    -- 查询没有员工的部门信息 左外连接
    select d.did '部门编号',d.dname '部门名称'
    from department d  left join employee e
    on d.did=e.pnumber
    where e.pnumber is null;

### 右外连接 ###

**`right join on`**  
**A∩B**查询两张表都满足的数据，以及右边表独有的数据(以右边表为主查询表)。  
![在这里插入图片描述][1d07016d2f21480bb8f20b524650243a.png]

# 右外连接
    select e.enumber '员工编号',e.ename '员工姓名',d.did '部门编号'
    from department d right join employee e
    on d.did=e.pnumber
    where d.did is null;
    
    -- 右外连接
    select d.did '部门编号',d.dname '部门名称'
    from employee e right join department d
    on e.pnumber=d.did
    where e.pnumber is null;

### 合并查询 ###

**`union all` 和 `union`**  
union all 将多个DQL语句合并成一个结果集，**不会去掉重复的数据**

union 将多个DQL语句合并成一个结果集，**会去掉重复的数据**

-- 查询职位是“职员”的所有员工信息
    -- 查询部门编号为10的所有员工信息
    select empno,ename,job,sal,deptno
    from emp
    where job='CLERK';
    
    -- 查询部门编号为10的所有员工信息
    select empno,ename,job,sal,deptno
    from emp
    where deptno=10;
    
    -- union all 将上面的DQL合并成一个结果集
    select empno,ename,job,sal,deptno
    from emp
    where job='CLERK'
    union all
    select empno,ename,job,sal,deptno
    from emp
    where deptno=10;
    
    编号7934的员工出现了两次，union all 没有去掉重复的数据     
    
    如何去掉重复的数据呢?
    select empno,ename,job,sal,deptno
    from emp
    where job='CLERK'
    union
    select empno,ename,job,sal,deptno
    from emp
    where deptno=10;

**小结**：

1.  union能够去掉多个结果集中重复的数据
2.  union all由于没有去重，所以效率高于union

### 自连接 ###

多个关联查询的表是同一张表，通过取别名的方式生成两张虚拟表

语法如下：
    select 列名称
    from 表名称 表别名1 inner/left/right join 表别名 表别名2;

-- 查询员工编号、员工姓名、员工工资、领导编号、领导姓名、领导工资
    /* 1 确定表名称 emp 2 确定关联条件 3 确定列名称 */
    select e1.empno 员工编号,e1.ename 员工姓名,e1.sal 员工工资,e2.mgr 领导编号,e2.ename 领导姓名,e2.sal 领导工资
    from emp e1 inner join emp e2 on e1.empno = e2.empno;
    -- 查询没有领导的员工信息
    select e1.empno 员工编号,e1.ename 员工姓名,e1.sal 员工工资
    from emp e1 inner join emp e2 on e1.empno = e2.empno
    where e2.mgr is null;

# 用户权限设置 #

-- 新建用户
    create user 'am'@'localhost' identified by '123456';
    
    -- 删除用户
    drop user 'am'@'localhost';
    
    -- 修改用户名
    rename user 'am'@'localhost' to 'admin'@'localhost';
    -- alter user '用户名'@'localhost' identified with mysql_native_password by '用户名'
    -- 权限 
    #grant select on j0705.score to 'admin'@'localhost' identified by '123456'; -- version 5.0
    grant select on j0705.score to 'admin'@'localhost'; -- version 8.0
    
    
    -- 查看用户权限
    show grants for 'admin'@'localhost';
    
    -- 所有权限
    grant all on *.* to 'admin'@'localhost';
    
    -- 取消授权
    revoke all on *.* from 'admin'@'localhost';

# MySQL事务 TCL #

事务就是完成某个独立的行为所需的一系列步骤的集合。

事务管理的行为**要么全部执行成功，要么全部执行失败**

## 事务的四大特征 ##

**原子性**：转账全部成功or全部失败，转账使用事务管理就是一个整体不可再分

**一致性**：转账之前和转账之后总金额不变

**隔离性**：多个事务之间进行的操作彼此保持隔离

**持久性**：事务执行成功之后数据会持久化到磁盘中数据文件中(转账成功李四金额多了500)

事务四大特征简称:**`ACID`**

**`Atomicity(原子性) Consistency(一致性) Isolation(隔离性) Durability(持久性)`**

## 事务的提交方式 ##

**自动提交**和**手动提交**

-- 通过下面的命令可以查看事务提交方式 1 自动提交 0 手动提交 MySQL默认自动提交
    select @autocommit=0;
    start transaction
    update tb_account set account_balance = 1000;
    commit;

## 操作事务 ##

create table bank(
     account varchar(30) primary key,
     money double -- 账户余额 
    )default charset=utf8;
    -- 2插入数据
    set autocommit=1;
    truncate table bank;
    insert into bank values('张三',2000);
    insert into bank values('李四',3000);
    ----------------------------------------------------------------
    CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_tx`(
    	`from` varchar(20),
    	`to` varchar(20),
    	`modifymoney` double
    )
    BEGIN
    	declare balance double; -- 余额
    	-- 开启显式事务
    	set autocommit = 0;
    	start transaction;
    	-- 扣款
    	update bank b set b.money=b.money-`modifymoney` where account=`from`;
    	-- 查询from的余额
    	select money into balance from bank where account=`from`;
    	if balance<1
    	then
    	select '余额不足！'
    		rollback;
    	else
    		-- 收款
    		update bank b set b.money=b.money+`modifymoney` where account=`to`;
    		commit;
    	end if;
    	select * from bank where account in(`from`,`to`);
    END

call pro_tx('张三','李四',500);

![在这里插入图片描述][b6e122d0dd1a43619224eb5191ab3dcc.png]  
![在这里插入图片描述][2ddc812986e04c73906d9f9dba43e9ac.png]

## 事务隔离级别 ##

-- 读未提交
    read uncommitted
    -- 读已提交
    read committed
    -- 可重复读
    repeatable read
    -- 串行化
    serializable

### 如何查看MySQL数据库隔离级别？ ###

select @@tx_isolation;
    -- MySQL数据库默认事务隔离级别是repeatable read，
    -- Oracle数据库默认事务隔离级别是read committed。
    -- 隔离级别越高越安全，但是性能也会越差。
    -- 所以工作中我们不用第一个隔离级别(不安全)，也不用第四个隔离级别(效率低)，通常只会在read committed和repeatable read之间进行切换

# 变量 #

## 变量分类 ##

1. 系院变量
    	（1）全局变量
    	（2）会话变量
    2. 自定义变量
    	（1）用户变量
    	（2）局部变量

### 全局变量 ###

-- 查看全部全局变量
    show global variables;
    -- 模糊查询和char有关的
    show global variables like '%char%';

### 会话变量 ###

-- 会话变量，针对当前会话（连接）有效
    -- 不能跨越连接
    show session variables;
    select @@autocommit;
    select @@session.tx_isolation; # transcation
    #set @@session.tx_isolation='XXX';

### 用户变量 ###

-- 定义用户变量
    set @nickname='折腾的小飞'; #1
    set @nickname:='折腾的小飞123'; #2
    select @nickname;
    select `name` into @hero from student where id=3; #3
    select @hero;

### 局部变量 ###

-- 定义局部变量：必须在指定的代码块，如函数和存储过程
    declare nick varchar(10) default 'hello';
    set nick = 'abc';
    select nick;

## 用户变量和局部变量的对比 ##

<table> 
 <thead> 
  <tr> 
   <th>作用域</th> 
   <th>定义的位置</th> 
   <th>定义的位置</th> 
   <th>语法</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong><code>用户</code>变量</strong></td> 
   <td>当前会话</td> 
   <td>会话的任何地方</td> 
   <td><strong><code>@变量名</code></strong></td> 
  </tr> 
  <tr> 
   <td><strong><code>局部</code>变量</strong></td> 
   <td>begin end</td> 
   <td>begin的第一行开始定义</td> 
   <td><strong><code>变量名</code></strong></td> 
  </tr> 
 </tbody> 
</table>

# 存储过程 #

CREATE DEFINER=`root`@`localhost` PROCEDURE `work1`()
    BEGIN
    	//sql语句
    END

无参过程：
    CREATE DEFINER=`root`@`localhost` PROCEDURE `std_list`()
    BEGIN
    	select * from student;
    END
    
    有参过程：
    CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`(n int)
    BEGIN	
    	/*计算： 1+2+3+....+10 = ?*/
    	declare i int ;
    	declare sum int;	
    	set i=1;
    	set sum = 0;
    	while (i<=n) do
    		set sum = sum + i;
    		set i =  i + 1;
    	end while;
    	select sum;
    END
    
    CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_test`(result int)
    BEGIN
    	if result < 60 then
    		select '挂科' 结果;
    	else
    		select '合格' 结果;
    	end if;
    	
    END

## 参数 ##

# in 输入类型参数
    # out 输出类型参数
    /* 定义存储过程，完成以下练习： 1. 输入学号和科目，获取姓名、参加考试的次数、 该科目的分数及等级（挂科、合格、良好、优秀）、 所有考试记录的平均分 */
    CREATE DEFINER=`root`@`localhost` PROCEDURE `work1`(
    	in `sid` int,
    	in `course` varchar(20),
    	out `sname` varchar(20),
    	out `count` int
    )
    BEGIN
    	select stu.`name`,count(*)
    	into `sname`,`count`
    	from student stu inner join score sc
    	on stu.id=sc.sid
    	where sc.sid=`sid` and sc.course=`course`;
    	
    	select result '分数',
    	case
    		when result between 90 and 100 then '优秀'
    		when result between 80 and 89 then '良好'
    		when result between 60 and 79 then '合格'
    		else '挂科'
    	end '等级'
    	from score
    	where course=`course`;
    	
    	select round(avg(result),2) '平均分' from score
    	where course=`course`;
    END
    ------------------------------------------
    set @sid=2;
    set @score='SSM';
    set @sname='';
    set @count='';
    call work1(@sid,@score,@sname,@count);
    select @sid '学号',@score '科目',@sname '姓名',@count '参加考试的次数';

## 调用存储过程 ##

call 存储过程名称(参数);

## 水仙花数 ##

CREATE DEFINER=`root`@`localhost` PROCEDURE `daffnum`()
    BEGIN
    	declare i int;
    	declare a int;
    	declare b int;
    	declare c int;
    	set i = 100;
    	while i<=999 do
    		set a = floor(i/100);
    		set b = mod(floor(i/10),10);
    		set c = mod(i,10);
    		if (pow(a,3)+pow(b,3)+pow(c,3)=i)
    			then select i;
    		end if;
    		set i=i+1;
    	end while;
    END
    call daffnum();

## 1~n的累加求和 ##

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc`(n int)
    BEGIN
    	declare i int ;
    	declare sum int;
    	set i=1;
    	set sum = 0;
    	while (i<=n) do
    		set sum=sum+i;
    		set i = i +1;
    	end while ;
    	select sum;
    END
    call proc(10);

## 求三角形的斜边 ##

CREATE DEFINER=`root`@`localhost` PROCEDURE `tralle`(a int,b int)
    BEGIN
    	select round(sqrt(pow(a,2)+pow(b,2)),3);
    END
    call tralle(3,4);

**下面根据`student`表和`score`表进行查询**

1.根据学号，查询姓名、专业、总分，并打印输出
    CREATE DEFINER=`root`@`localhost` PROCEDURE `select_sid`(s int)
    BEGIN
    	select stu.`name` '姓名',stu.major '专业',sum(sc.result) '总分'
    	from score sc inner join student stu
    	on sc.sid=stu.id
    	where sc.sid=s
    	group by stu.`name`;
    END
    call select_sid(1);
    2.根据专业和课程，查询该专业的该课程考试的及格人数、合格率
    CREATE DEFINER=`root`@`localhost` PROCEDURE `select_ms`(zy varchar(20),kc varchar(20))
    BEGIN
    	select zy '专业', kc '课程',sum(sc.result>=60) '及格人数',concat(round(100*sum(case when sc.result>=60 then 1 else 0 end)/count(*),2),'%') '合格率'
    	from score sc inner join student stu
    	on sc.sid=stu.id
    	where stu.major=zy and sc.course=kc;
    END
    call select_ms('计算机应用','HTML');
    ------------------------------------------------
    CREATE DEFINER=`root`@`localhost` PROCEDURE `select1`(c varchar(20))
    BEGIN
    	select avg(score.result) into @avgs from score;
    	select max(sc.result) '最高分',avg(sc.result) '平均分',
    		sum(sc.result>@avgs) '高于平均分的人数'
    	from score sc inner join student stu
    	on sc.sid=stu.id
    	where sc.course=c;
    END
    call select1('SSM');
    ------------------------------------------------
    定义存储过程，完成以下练习:
    1.输入学号和科目，获取姓名、参加考试的次数、
    该科目的分数及等级(挂科、合格、良好、优秀)
    所有考试记录的平均分
    CREATE DEFINER=`root`@`localhost` PROCEDURE `work1`(
    	in `sid` int,
    	in `course` varchar(20),
    	out `sname` varchar(20),
    	out `count` int
    )
    BEGIN
    	select stu.`name`,count(*)
    	into `sname`,`count`
    	from student stu inner join score sc
    	on stu.id=sc.sid
    	where sc.sid=`sid` and sc.course=`course`;
    	
    	select result '分数',
    	case
    		when result between 90 and 100 then '优秀'
    		when result between 80 and 89 then '良好'
    		when result between 60 and 79 then '合格'
    		else '挂科'
    	end '等级'
    	from score
    	where course=`course`;
    	
    	select round(avg(result),2) '平均分' from score
    	where course=`course`;
    END
    ------------------------------------------------
    set @sid=2;
    set @score='SSM';
    set @sname='';
    set @count='';
    call work1(@sid,@score,@sname,@count);
    select @sid '学号',@score '科目',@sname '姓名',@count '参加考试的次数';

-- 根据传入的学号计算星座，年龄，下一个生日时间；利用存储过程，事务进行处理
    CREATE DEFINER=`root`@`localhost` PROCEDURE `pro`(sid int)
    BEGIN
    	declare birth date; -- 提取生日
    	select birthday into birth from student where id=sid;
    	
    	create table if not exists birthInfo(
    		id int primary key,
    		sname varchar(20) not null,
    		gender varchar(3) not null,
    		major varchar(30) not null,
    		constellation varchar(12),
    		age varchar(10),
    		nextbirth varchar(10)
    	)default charset=utf8;
    	
    	set @b_constellation='';-- 星座
    	set @b_age=''; -- 年龄
    	set @nextbirth=''; -- 下一次生日
    	select `name` into @b_name from student where id=sid; -- 姓名
    	select gender into @b_gender from student where id=sid; -- 性别
    	select major into @b_major from student where id=sid; -- 专业
    		
    
    	
    	-- 星座
    	set @newbirth=0;
    	-- 截取日期的月份和日期，转换为int类型
    	set @newbirth=convert(concat(substring(birth,6,2),substring(birth,9)),SIGNED);
    	
    	if @newbirth between 120 and 218 then set @b_constellation='水平座';
    	elseif @newbirth between 219 and 320 then set @b_constellation='双鱼座';
    	elseif @newbirth between 321 and 419 then set @b_constellation='白羊座';
    	elseif @newbirth between 420 and 520 then set @b_constellation='金牛座';
    	elseif @newbirth between 521 and 621 then set @b_constellation='双子座';
    	elseif @newbirth between 622 and 722 then set @b_constellation='巨蟹座';
    	elseif @newbirth between 723 and 822 then set @b_constellation='狮子座';
    	elseif @newbirth between 823 and 922 then set @b_constellation='处女座';
    	elseif @newbirth between 923 and 1023 then set @b_constellation='天秤座';
    	elseif @newbirth between 1024 and 1122 then set @b_constellation='天蝎座';
    	elseif @newbirth between 1123 and 1221 then set @b_constellation='射手座';
    	else set @b_constellation='魔羯座';
    	end if;
    	select @b_constellation;
    	
    	-- 年龄
    	set @b_age=concat(floor(datediff(now(),birth)/365),'岁');
    	select @b_age;
    	
    	-- 下一次生日
    	-- 生日年份设置为当前年份
    	set @newbirth=str_to_date(concat(year(now()),substring(birth,5)),'%Y-%m-%d');
    	if datediff(@newbirth,now())<0 then set @nextbirth=concat(365+datediff(@newbirth,now()),'天');
    	else set @nextbirth=concat(datediff(@newbirth,now()),'天');
    	end if;
    	select @nextbirth;
    	set autocommit=0;
    	start transaction;
    	if((@b_constellation is null) or (@b_age  is null) or (@nextbirth is null)) then rollback; end if;
    	insert into birthInfo values(sid,@b_name,@b_gender,@b_major,@b_constellation,@b_age,@nextbirth);
    	commit;
    	END
    -------------------------------------------
    -- 生日start
    call pro(2);
    call pro(3);
    call pro(4);
    call pro(5);
    select * from birthinfo;

**添加成功！**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_20_color_FFFFFF_t_70_g_se_x_16]  
若要记录每次查询结果，可取消主键！

alter table birthinfo drop primary key;
    -- 再执行一遍
    call pro(2);
    call pro(3);
    call pro(4);
    call pro(5);
    select * from birthinfo;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_19_color_FFFFFF_t_70_g_se_x_16]  
**OK！！！**

# 视图 #

视图：是一个**虚拟表**，**保存的不是数据，而是sql逻辑**，主要保存复杂的sql逻辑，达到简化查询的效果

-- 查询视图
    create view v1
    as
    	select * from
    	from student stu inner join score sc
    	on stu.id=sc.sid;
    select * from v1;
    
    -- 插入视图
    create view v_insert
    as
    	select * from student;
    insert into v_insert values(default,'视图学生','男','1998-4-5','计算机应用');
    
    select * from v_insert where `name`='视图学生';

[d3383d1c4e0f4b6eb85e5e5254fa7b02.png]: /images/20220829/b2744c1bb7ac44e4a1aa9a7dce457174.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_28_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/f579a959a0074ed2ba117f4c0dcb9ae6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_23_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/601ded53afcf41ecac674c93d0668e31.png
[0f2d5064d70641e98ce52abf020e3d34.png]: /images/20220829/dc6d9aef0d384fb0bc03b867fd2e3a78.png
[8299b1f543bc43dda796a32685751cc0.png]: /images/20220829/9d1f38e934a24518b18fdbc6a29d9a65.png
[db8f122f29b34c8fac0b4fca39245751.png]: /images/20220829/67c70dcff9ad4cb0808f11b746c47531.png
[bbeea891031640d6b65773b3cf7b3842.png]: /images/20220829/903fb9608cd84c1385259e876f429ca6.png
[8ba6943ee31e4281bed080b09dbf85ad.png]: /images/20220829/e6888396cd204665a79122b2b78eb275.png
[1d07016d2f21480bb8f20b524650243a.png]: /images/20220829/424981b47f3348bbb33a5e4a72f6b740.png
[b6e122d0dd1a43619224eb5191ab3dcc.png]: /images/20220829/75c3e7f25e6f48bf902c88f87c08a151.png
[2ddc812986e04c73906d9f9dba43e9ac.png]: /images/20220829/570fb2322aa64f8086e18b529649c836.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/3ad991bc75034e0293c0847e3b96f7d7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBA5oqY6IW-55qE5bCP6aOe_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/6fd15226d166443d8d75645357094ace.png