MySQL系列专题(2)-MySQL的SQL语句和高级特性

Dear 丶 2022-09-12 01:50 221阅读 0赞

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5rOi5rOi54Ok6bit_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]

### 一、 DML 操作【`重点`】 ###

--------------------

#### 1.1 新增（INSERT） ####

> INSERT INTO 表名(列 1，列 2，列 3…) VALUES(值 1，值 2，值 3…);

##### 1.1.1 添加一条信息 #####

#添加一条工作岗位信息
    INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES('JAVA_Le','JAVA_Lecturer',2500,9000);

#添加一条员工信息
    INSERT INTO `t_employees`
    （EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID）
    VALUES 
    ('194','Samuel','McCain','SMCCAIN', '650.501.3876', '1998-07-01', 'SH_CLERK', '3200', NULL, '123', '50');

*  注意：表名后的列名和 VALUES 里的值要一一对应（个数、顺序、类型）

#### 1.2 修改（UPDATE） ####

> UPDATE 表名 SET 列 1=新值 1 ,列 2 = 新值 2,…WHERE 条件;

##### 1.2.1 修改一条信息 #####

#修改编号为100 的员工的工资为 25000
    UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';

#修改编号为135 的员工信息岗位编号为 ST_MAN，工资为3500
    UPDATE t_employees SET JOB_ID=ST_MAN,SALARY = 3500 WHERE EMPLOYEE_ID = '135';

*  注意：SET 后多个列名=值，绝大多数情况下都要加 WHERE 条件，指定修改，否则为整表更新

#### 1.3 删除（DELETE） ####

> DELETE FROM 表名 WHERE 条件；

##### 1.3.1 删除一条信息 #####

#删除编号为135 的员工
    DELETE FROM t_employees WHERE EMPLOYEE_ID='135';

#删除姓Peter，并且名为 Hall 的员工
    DELETE FROM t_employees WHERE FIRST_NAME = 'Peter' AND LAST_NAME='Hall';

*  注意：删除时，如若不加 WHERE条件，删除的是整张表的数据

#### 1.4 清空整表数据（TRUNCATE） ####

> TRUNCATE TABLE 表名; 该语法不属于DML语句.

##### 1.4.1 清空整张表 #####

#清空t_countries整张表
    TRUNCATE TABLE t_countries;

*  注意：与 DELETE 不加 WHERE 删除整表数据不同，TRUNCATE 是把表销毁，再按照原表的格式创建一张新表

###  ###

### 2、数据查询【`重点`】 ###

--------------------

#### 2.1 数据库表的基本结构 ####

> 关系结构数据库是以表格（Table）进行数据存储，表格由“行”和“列”组成

*  经验：执行查询语句返回的结果集是一张虚拟表。

-- 员工表  employee
    employee_id --编号
    first_name --名字
    email --邮箱
    salary  --月薪
    commission_pct --提成
    manager_id --所属经理编号
    department_id --部门id
    job_id --工作编号

-- 部门表 department
    department_id --部门编号
    department_name --部门名称
    
    -- 工作表 job
    job_id --工作id
    job_name --工作名称
    job_desc --工作内容
    
    --经理表 manager
    manager_id --经理编号
    manager_name --经理名字

#### 2.2 基本查询 ####

> 语法：SELECT 列名 FROM 表名

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SELECT</td> 
   <td>指定要查询的列</td> 
  </tr> 
  <tr> 
   <td>FROM</td> 
   <td>指定要查询的表</td> 
  </tr> 
 </tbody> 
</table>

##### 2.2.1 查询部分列 #####

#查询员工表中所有员工的编号、名字、邮箱
    SELECT employee_id,first_name,email 
    FROM t_employees;

##### 2.2.2 查询所有列 #####

#查询员工表中所有员工的所有信息（所有列）
    SELECT 所有列的列名 FROM t_employees;
    SELECT * FROM t_employees;

*  注意：生产环境下，优先使用列名查询。\*的方式需转换成全列名，效率低，可读性差。

##### 2.2.3 对列中的数据进行运算 #####

#查询员工表中所有员工的编号、名字、年薪
    SELECT employee_id , first_name , salary*12 
    FROM t_employees;

<table> 
 <thead> 
  <tr> 
   <th>算数运算符</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>+</td> 
   <td>两列做加法运算</td> 
  </tr> 
  <tr> 
   <td>-</td> 
   <td>两列做减法运算</td> 
  </tr> 
  <tr> 
   <td>*</td> 
   <td>两列做乘法运算</td> 
  </tr> 
  <tr> 
   <td>/</td> 
   <td>两列做除法运算</td> 
  </tr> 
 </tbody> 
</table>

*  注意：%是占位符，而非模运算符。

##### 2.2.4 列的别名 #####

> 列 as ‘列名’

#查询员工表中所有员工的编号、名字、年薪（列名均为中文）
    SELECT employee_id as "编号" , first_name as "名字" , salary*12 as "年薪" 
    FROM t_employees;

##### 2.2.5 查询结果去重 #####

> DISTINCT 列名

#查询员工表中所有经理的ID。
    SELECT DISTINCT manager_id 
    FROM t_employees;

#### 2.3排序查询 ####

> 语法： SELECT 列名 FROM 表名 ORDER BY 排序列 \[排序规则\]

<table> 
 <thead> 
  <tr> 
   <th>排序规则</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ASC</td> 
   <td>对前面排序列做升序排序</td> 
  </tr> 
  <tr> 
   <td>DESC</td> 
   <td>对前面排序列做降序排序</td> 
  </tr> 
 </tbody> 
</table>

##### 2.3.1 依据单列排序 #####

#查询员工的编号，名字，薪资。按照工资高低进行降序排序。
    SELECT employee_id , first_name , salary
    FROM t_employees
    ORDER BY salary DESC;

##### 2.3.2 依据多列排序 #####

#查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序）。
    SELECT employee_id , first_name , salary
    FROM t_employees
    ORDER BY salary DESC , employee_id ASC;

#### 2.4 条件查询 ####

> 语法：SELECT 列名 FROM 表名 WHERE 条件

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>WHERE 条件</td> 
   <td>在查询结果中，筛选符合条件的查询结果，条件为布尔表达式</td> 
  </tr> 
 </tbody> 
</table>

##### 2.4.1 等值判断（=） #####

#查询薪资是11000的员工信息（编号、名字、薪资）
    SELECT employee_id , first_name , salary
    FROM t_employees
    WHERE salary = 11000;

*  注意：与 java 不同（==），mysql 中等值判断使用 =

##### 2.4.2 逻辑判断（and、or、not） #####

#查询薪资是11000并且提成是0.30的员工信息（编号、名字、薪资）
    SELECT employee_id , first_name , salary
    FROM t_employees
    WHERE salary = 11000 AND commission_pct = 0.30;

##### 2.4.3 不等值判断（> 、< 、>= 、<= 、!= 、<>） #####

#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
    SELECT employee_id , first_name , salary
    FROM t_employees
    WHERE salary >= 6000 AND salary <= 10000;

##### 2.4.4 区间判断（between and） #####

#查询员工的薪资在6000~10000之间的员工信息（编号，名字，薪资）
    SELECT employee_id , first_name , salary
    FROM t_employees
    WHERE salary BETWEEN 6000 AND 10000; #闭区间，包含区间边界的两个值

*  注：在区间判断语法中，小值在前，大值在后，反之，得不到正确结果

##### 2.4.5 NULL 值判断（IS NULL、IS NOT NULL） #####

>  *  IS NULL  
>     列名 IS NULL
>  *  IS NOT NULL  
>     列名 IS NOT NULL

#查询没有提成的员工信息（编号，名字，薪资 , 提成）
    SELECT employee_id , first_name , salary , commission_pct
    FROM t_employees
    WHERE commission_pct IS NULL;

##### 2.4.6 枚举查询（ IN (值 1，值 2，值 3 ) ） #####

#查询部门编号为70、80、90的员工信息（编号，名字，薪资 , 部门编号）
    SELECT employee_id , first_name , salary , department_id
    FROM t_employees
    WHERE department_id IN(70,80,90);
    注：in的查询效率较低，可通过多条件拼接。

##### 2.4.7 模糊查询 #####

>  *  LIKE \_ （单个任意字符）  
>     列名 LIKE ‘张\_’
>  *  LIKE %（任意长度的任意字符）  
>     列名 LIKE ‘张%’

*  注意：模糊查询只能和 LIKE 关键字结合使用

#查询名字以"L"开头的员工信息（编号，名字，薪资 , 部门编号）
    SELECT employee_id , first_name , salary , department_id
    FROM t_employees
    WHERE first_name LIKE 'L%';
    
    
    #查询名字以"L"开头并且长度为4的员工信息（编号，名字，薪资 , 部门编号）
    SELECT employee_id , first_name , salary , department_id
    FROM t_employees
    WHERE first_name LIKE 'L___';

##### 2.4.8 分支结构查询 #####

CASE
        WHEN 条件1 THEN 结果1
        WHEN 条件2 THEN 结果2
        WHEN 条件3 THEN 结果3
        ELSE 结果
    END

*  注意：通过使用CASE END进行条件判断，每条数据对应生成一个值。
 *  经验：类似 Java 中的switch。

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
    SELECT employee_id , first_name , salary , department_id , 
           CASE
               WHEN salary>=10000 THEN 'A'
               WHEN salary>=8000 AND salary<10000 THEN 'B'
               WHEN salary>=6000 AND salary<8000  THEN 'C'
               WHEN salary>=4000 AND salary<6000  THEN 'D'
       ELSE 'E'
           END as "LEVEL"
    FROM t_employees;

#### 2.5 时间查询 ####

> 语法：SELECT 时间函数(\[参数列表\])

*  经验：执行时间函数查询，会自动生成一张虚表（一行一列）

<table> 
 <thead> 
  <tr> 
   <th>时间函数</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SYSDATE()</td> 
   <td>当前系统时间（日、月、年、时、分、秒）</td> 
  </tr> 
  <tr> 
   <td>CURDATE()</td> 
   <td>获取当前日期</td> 
  </tr> 
  <tr> 
   <td>CURTIME()</td> 
   <td>获取当前时间</td> 
  </tr> 
  <tr> 
   <td>WEEK(DATE)</td> 
   <td>获取指定日期为一年中的第几周</td> 
  </tr> 
  <tr> 
   <td>YEAR(DATE)</td> 
   <td>获取指定日期的年份</td> 
  </tr> 
  <tr> 
   <td>HOUR(TIME)</td> 
   <td>获取指定时间的小时值</td> 
  </tr> 
  <tr> 
   <td>MINUTE(TIME)</td> 
   <td>获取时间的分钟值</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF(DATE1,DATE2)</td> 
   <td>获取DATE1 和 DATE2 之间相隔的天数</td> 
  </tr> 
  <tr> 
   <td>ADDDATE(DATE,N)</td> 
   <td>计算DATE 加上 N 天后的日期</td> 
  </tr> 
 </tbody> 
</table>

##### 2.5.1 获得当前系统时间 #####

#查询当前时间
    SELECT SYSDATE();

#查询当前时间
    SELECT NOW();

#获取当前日期
    SELECT CURDATE();

#获取当前时间
    SELECT CURTIME();

#### 2.6 字符串查询 ####

> 语法： SELECT 字符串函数 (\[参数列表\])

<table> 
 <thead> 
  <tr> 
   <th>字符串函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CONCAT(str1,str2,str…)</td> 
   <td>将 多个字符串连接</td> 
  </tr> 
  <tr> 
   <td>INSERT(str,pos,len,newStr)</td> 
   <td>将str 中指定 pos 位置开始 len 长度的内容替换为 newStr</td> 
  </tr> 
  <tr> 
   <td>LOWER(str)</td> 
   <td>将指定字符串转换为小写</td> 
  </tr> 
  <tr> 
   <td>UPPER(str)</td> 
   <td>将指定字符串转换为大写</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(str,num,len)</td> 
   <td>将str 字符串指定num位置开始截取 len 个内容</td> 
  </tr> 
 </tbody> 
</table>

##### 2.6.1 字符串应用 #####

#拼接内容
    SELECT CONCAT('My','S','QL');

#字符串替换
    SELECT INSERT('这是一个数据库',3,2,'MySql');#结果为这是 MySql 数据库

#指定内容转换为小写
    SELECT LOWER('MYSQL');#mysql

#指定内容转换为大写
    SELECT UPPER('mysql');#MYSQL

#指定内容截取
    SELECT SUBSTRING('JavaMySQLOracle',5,5);#MySQL

#### 2.7 聚合函数 ####

> 语法：SELECT 聚合函数(列名) FROM 表名;

*  经验：对多条数据的单列进行统计，返回统计后的一行结果。

<table> 
 <thead> 
  <tr> 
   <th>聚合函数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SUM()</td> 
   <td>求所有行中单列结果的总和</td> 
  </tr> 
  <tr> 
   <td>AVG()</td> 
   <td>平均值</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>最大值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>最小值</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>求总行数</td> 
  </tr> 
 </tbody> 
</table>

##### 2.7.1 单列总和 #####

#统计所有员工每月的工资总和
    SELECT sum(salary)
    FROM t_employees;

##### 2.7.2 单列平均值 #####

#统计所有员工每月的平均工资
    SELECT AVG(salary)
    FROM t_employees;

##### 2.7.3 单列最大值 #####

#统计所有员工中月薪最高的工资
    SELECT MAX(salary)
    FROM t_employees;

##### 2.7.4 单列最小值 #####

#统计所有员工中月薪最低的工资
    SELECT MIN(salary)
    FROM t_employees;

##### 2.7.5 总行数 #####

#统计员工总数
    SELECT COUNT(*)
    FROM t_employees;

#统计有提成的员工人数
    SELECT COUNT(commission_pct) 
    FROM t_employees;

*   注意：聚合函数自动忽略null值，不进行统计。

#### 2.8 分组查询 ####

> 语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据（列）;

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>GROUP BY</td> 
   <td>分组依据，必须在 WHERE 之后生效</td> 
  </tr> 
 </tbody> 
</table>

2.8.1 查询各部门的总人数

#思路：
    #1.按照部门编号进行分组（分组依据是 department_id）
    #2.再针对各部门的人数进行统计（count）
    SELECT department_id,COUNT(employee_id)
    FROM t_employees
    GROUP BY department_id;

2.8.2 查询各部门的平均工资

#思路：
    #1.按照部门编号进行分组（分组依据department_id）。
    #2.针对每个部门进行平均工资统计（avg）。
    SELECT department_id , AVG(salary)
    FROM t_employees
    GROUP BY department_id

2.8.3 查询各个部门、各个岗位的人数

#思路：
    #1.按照部门编号进行分组（分组依据 department_id）。
    #2.按照岗位名称进行分组（分组依据 job_id）。
    #3.针对每个部门中的各个岗位进行人数统计（count）。
    SELECT department_id , job_id , COUNT(employee_id)
    FROM t_employees
    GROUP BY department_id , job_id;

2.8.4 常见问题

#查询各个部门id、总人数、first_name
    SELECT department_id , COUNT(*) , first_name
    FROM t_employees
    GROUP BY department_id; #error

*  注：分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

#### 2.9 分组过滤查询 ####

> 语法：SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>HAVING 过滤规则</td> 
   <td>过滤规则定义对分组后的数据进行过滤</td> 
  </tr> 
 </tbody> 
</table>

2.9.1 统计部门的最高工资

#统计60、70、90号部门的最高工资
    #思路：
    #1).    确定分组依据（department_id）
    #2).    对分组后的数据，过滤出部门编号是60、70、90信息
    #3).    max()函数处理
    
    SELECT department_id , MAX(salary)
    FROM t_employees
    GROUP BY department_id
    HAVING department_id in (60,70,90)
    
    # group确定分组依据department_id 
    #having过滤出60 70 90部门
    #select查看部门编号和max函数。

#### 2.10 限定查询 ####

> SELECT 列名 FROM 表名 LIMIT 起始行，查询行数

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>LIMIT offset_start，row_count</td> 
   <td>限定查询结果的起始行和总行数</td> 
  </tr> 
 </tbody> 
</table>

##### 2.10.1 查询前 5 行记录 #####

#查询表中前五名员工的所有信息
    SELECT * FROM t_employees LIMIT 0,5;

*  注意：起始行是从 0 开始，代表了第一行。第二个参数代表的是从指定行开始查询几行

##### 2.10.2 查询范围记录 #####

#查询表中从第四条开始，查询 10 行
    SELECT * FROM t_employees LIMIT 3,10;

##### 2.10.3 LIMIT典型应用 #####

分页查询：一页显示 10 条，一共查询三页

#思路：第一页是从 0开始，显示 10 条
    SELECT * FROM LIMIT 0,10;
    
    #第二页是从第 10 条开始，显示 10 条
    SELECT * FROM LIMIT 10,10;
    
    #第三页是从 20 条开始，显示 10 条
    SELECT * FROM LIMIT 20,10;

*  经验：在分页应用场景中，起始行是变化的，但是一页显示的条数是不变的

#### 2.11 查询总结 ####

--------------------

##### 2.11.1 SQL 语句编写顺序 #####

> SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列（asc|desc）LIMIT 起始行，总条数

##### 2.11.2 SQL 语句执行顺序 #####

1.FROM ：指定数据来源表
    2.WHERE : 对查询数据做第一次过滤
    3.GROUP BY ： 分组
    4.HAVING : 对分组后的数据第二次过滤
    5.SELECT : 查询各字段的值
    6.ORDER BY : 排序
    7.LIMIT : 限定查询结果

#### 2.12 子查询（作为条件判断） ####

> SELECT 列名 FROM 表名 Where 条件 (子查询结果)

2.12.1 查询工资大于Bruce 的员工信息

#1.先查询到 Bruce 的工资（一行一列）
    SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工资是 6000
    
    #2.查询工资大于 Bruce 的员工信息
    SELECT * FROM t_employees WHERE SALARY > 6000;
    
    #3.将 1、2 两条语句整合
    SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）;

*  注意：将子查询 ”一行一列“的结果作为外部查询的条件，做第二次查询
 *  子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

#### 2.13 子查询（作为枚举查询条件） ####

> SELECT 列名 FROM 表名 Where 列名 in(子查询结果);

2.13.1 查询与名为’King’同一部门的员工信息

#思路：
    #1. 先查询 'King' 所在的部门编号(多行单列)
    SELECT department_id
    FROM t_employees
    WHERE last_name = 'King'; //部门编号：80、90
    
    #2. 再查询80、90号部门的员工信息
    SELECT employee_id , first_name , salary , department_id
    FROM t_employees
    WHERE department_id in (80,90); 
    
    #3.SQL：合并
    SELECT employee_id , first_name , salary , department_id
    FROM t_employees
    WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列

*  将子查询 ”多行一列“的结果作为外部查询的枚举查询条件，做第二次查询

2.13.2 工资高于60部门所有人的信息

#1.查询 60 部门所有人的工资（多行多列）
    SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;
    
    #2.查询高于 60 部门所有人的工资的员工信息（高于所有）
    select * from t_employees where SALARY > ALL(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);
    
    #。查询高于 60 部门的工资的员工信息（高于部分）
    select * from t_employees where SALARY > ANY(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);

*  注意：当子查询结果集形式为多行单列时可以使用 ANY 或 ALL 关键字

#### 2.14 子查询（作为一张表） ####

> SELECT 列名 FROM（子查询的结果集）WHERE 条件;

2.14.1 查询员工表中工资排名前 5 名的员工信息

#思路：
    #1. 先对所有员工的薪资进行排序（排序后的临时表）
    select employee_id , first_name , salary
    from t_employees
    order by salary desc
    
    #2. 再查询临时表中前5行员工信息
    select employee_id , first_name , salary
    from (临时表) 
    limit 0,5;
    
    #SQL：合并
    select employee_id , first_name , salary
    from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
    limit 0,5;

*  将子查询 ”多行多列“的结果作为外部查询的一张表，做第二次查询。
 *  注意：子查询作为临时表，为其赋予一个临时表名

#### 2.15 合并查询（了解） ####

>  *  SELECT \* FROM 表名 1UNION SELECT \* FROM 表名 2
>  *  SELECT \* FROM 表名 1UNION ALL SELECT \* FROM 表名 2

2.15.1 合并两张表的结果（去除重复记录）

#合并两张表的结果，去除重复记录
    SELECT * FROM t1 UNION SELECT * FROM t2;

*  注意：合并结果的两张表，列数必须相同，列的数据类型可以不同

2.15.2 合并两张表的结果（保留重复记录）

#合并两张表的结果，不去除重复记录（显示所有）
    SELECT * FROM t1 UNION ALL SELECT * FROM t2;

*  经验：使用 UNION 合并结果集，会去除掉两张表中重复的数据

#### 2.16 表连接查询 ####

> SELECT 列名 FROM 表 1 连接方式 表 2 ON 连接条件

##### 2.16.1 内连接查询（INNER JOIN ON） #####

#1.查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
    SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID
    
    #2.查询所有有部门的员工信息（不包括没有部门的员工） MYSQL
    SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID

*  经验：在 MySql 中，第二种方式也可以作为内连接查询，但是不符合 SQL 标准
 *  而第一种属于 SQL 标准，与其他关系型数据库通用

##### 2.16.2 三表连接查询 #####

#查询所有员工工号、名字、部门名称、部门所在国家ID
    SELECT * FROM t_employees e 
    INNER JOIN t_departments d 
    on e.department_id = d.department_id
    INNER JOIN t_locations l
    ON d.location_id = l.location_id

##### 2.16.3 左外连接（LEFT JOIN ON） #####

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
    SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
    LEFT JOIN t_departments d 
    ON e.department_id = d.department_id;

*  注意：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果
 *  匹配不到，则返回 NULL 值填充

##### 2.16.4 右外连接（RIGHT JOIN ON） #####

#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL 填充）
    SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e 
    RIGHT JOIN t_departments d 
    ON e.department_id = d.department_id;

*  注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果
 *  匹配不到，则返回 NULL 值填充

--------------------

### 三、数据库高级 ###

#### 3.1 存储过程 ####

> MySQL 5.0 版本开始支持存储过程。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
> 
> 存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

>     DELIMITER $$ 或 DELIMITER //
>     这个代表当前sql语句的结尾符号不是 ; 而是变成了指定的 $$ 或者 //
>     因为在存储过程的创建过程中,我们会有;代表一行语句的结尾

##### 3.1.1 创建存储过程 #####

CREATE
        [DEFINER = { user | CURRENT_USER }]
    　PROCEDURE sp_name ([proc_parameter[,...]])
        [characteristic ...] routine_body
     
    proc_parameter:
        [ IN | OUT | INOUT ] param_name type
     
    characteristic:
        COMMENT 'string'
      | LANGUAGE SQL
      | [NOT] DETERMINISTIC
      | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
      | SQL SECURITY { DEFINER | INVOKER }
     
    routine_body:
    　　Valid SQL routine statement
     
    [begin_label:] BEGIN
    　　[statement_list]
    　　　　……
    END [end_label]

DELIMITER $$
    CREATE PROCEDURE pro_test1()
        BEGIN
    
            INSERT INTO t2(tname) VALUES('haha');
            UPDATE t1 SET tname='jack1' WHERE tid=1;
            SELECT * FROM t1;
        END $$
      
    
    call pro_test1()

##### 3.1.2 存储过程参数 #####

> IN | OUT | INOUT 类型 名称 类型

in参数的使用

DELIMITER $$
    CREATE PROCEDURE pro_test2(IN t2_name VARCHAR(20) , IN t1_id INT)
        BEGIN
    
            INSERT INTO t2(tname) VALUES(t2_name);
            UPDATE t1 SET tname='jack1' WHERE tid=t1_id;
            SELECT * FROM t1;
        END $$
      
    CALL pro_test2('hehe',2)

out参数的使用

DELIMITER $$
    CREATE PROCEDURE pro_test3(OUT tname VARCHAR(20))
        BEGIN
    
            SET tname = 'xixi';
          
        END $$
    
    CALL pro_test3(@tname);
    SELECT @tname;

inout参数的使用;尽量分开使用in和out,不使用inout

DELIMITER $$
    CREATE PROCEDURE pro_test4(INOUT tname VARCHAR(20))
        BEGIN
            SELECT tname;
            SELECT CONCAT(tname,"hello") INTO tname;
          
        END $$
    
    SET @tname='jack';
    CALL pro_test4(@tname);
    SELECT @tname;

##### 3.1.3 存储过程变量 #####

> 局部变量 *DECLARE var\_name\[, var\_name\] … type \[DEFAULT value\];*

**set和默认值**

declare num2 int default 100;
    select num2;
    
    DELIMITER $$
    CREATE PROCEDURE pro_test5()
        BEGIN
            DECLARE num INT DEFAULT 100;
            SELECT num;
            set num = 200;
            select num;
        END $$
      
    CALL pro_test5

**使用 into 进行赋值**

DELIMITER $$
    CREATE PROCEDURE pro_test6()
        BEGIN
            DECLARE num INT DEFAULT 100;
            SELECT num;
            SELECT tid INTO num FROM t1 WHERE tname='jack';
            SELECT num;
        END $$
    
    CALL pro_test6

> 用户变量 @变量名 不需要申明可直接使用;类似于Java全局变量

DELIMITER $$
    CREATE PROCEDURE pro_test7()
        BEGIN
    
            SET @param_t1 = 300;
            SELECT @param_t1;
          
            SELECT tid INTO @param_t1 FROM t1 WHERE tname='jack';
            SELECT @param_t1;
        END $$
    
    CALL pro_test7

> 系统变量 @@变量名

根据系统变量的作用域分为：全局变量与会话变量（两个@符号）
    
    　　全局变量(@@global.)
    　　　　在MySQL启动的时候由服务器自动将全局变量初始化为默认值；
    　　　　全局变量的默认值可以通过更改MySQL配置文件(my.ini、my.cnf)来更改。
    
    　　会话变量(@@session.)
    　　　　在每次建立一个新的连接的时候，由MySQL来初始化；
    　　　　
    MYSQL会将当前所有全局变量的值复制一份来做为会话变量（也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的）。
    
    全局变量与会话变量的区别：对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话。

##### 3.1.4 条件语句 #####

> if-then-else 语句

IF expression THEN 
       statements;
    END IF;
    ----------------------------------------
    
    IF expression THEN
       statements;
    ELSE
       else-statements;
    END IF;
    ----------------------------------------
    
    IF expression THEN
       statements;
    ELSEIF elseif-expression THEN
       elseif-statements;
    ...
    ELSE
       else-statements;
    END IF;

DELIMITER $$
    CREATE PROCEDURE pro_test8(IN tid INT)
        BEGIN
          
            IF tid = 1 THEN
                INSERT INTO t1(tname) VALUES('tom');
            END IF;
          
        END $$
      
    CALL pro_test8(1);
    
    -----------------------------------------------------------------
    
    DELIMITER $$
    CREATE PROCEDURE pro_test8(IN tid INT)
        BEGIN
          
            IF tid = 1 THEN
                INSERT INTO t1(tname) VALUES('tom');
            ELSE
                SELECT CONCAT(tid,'xixi');
            END IF;
          
        END $$
    CALL pro_test8(2);
    
    ---------------------------------------------------------------------
    
    DELIMITER $$
    CREATE PROCEDURE pro_test8(IN tid INT)
        BEGIN
          
            IF tid = 1 THEN
                INSERT INTO t1(tname) VALUES('tom');
            ELSEIF tid = 2 THEN
                SELECT CONCAT(tid,'xixi');
            ELSEIF tid = 3 THEN
                SELECT CONCAT(tid,'xxxxxx');
            END IF;
          
        END $$
      
    CALL pro_test8(3);

##### 3.1.5 循环语句 #####

> WHILE……DO……END WHILE  
> REPEAT……UNTIL END REPEAT  
> LOOP……END LOOP  
> GOTO 不建议用

**WHILE……DO……END WHILE**

DELIMITER $$
    CREATE PROCEDURE pro_test9(IN number INT)
        BEGIN
            -- 满足什么条件继续循环
            WHILE number > 0 DO
    
                SELECT number;
                SET number=number-1;
          
            END WHILE;
          
        END $$
    CALL pro_test9(6);

**REPEAT……UNTIL END REPEAT**

DELIMITER $$
    CREATE PROCEDURE pro_test10(IN number INT)
        BEGIN
          
            REPEAT 
                INSERT INTO t1(tname) VALUES(CONCAT('aa',number));
                SET number=number-1;
                UNTIL number < 0 -- 这里不需要加分号 这个是满足什么条件退出循环
            END REPEAT;
          
        END $$
      
    
    CALL pro_test10(6);

**LOOP…LEAVE…END LOOP**

DELIMITER $$
    CREATE PROCEDURE pro_test11()
        BEGIN
            BEGIN
                DECLARE i INT DEFAULT 0;
                loop_x : LOOP
                    INSERT INTO t1(tname) VALUES(CONCAT('bb',i));
                    SET i=i+1;
                    IF i > 5 THEN
                        LEAVE loop_x;
                    END IF;
                END LOOP;
            END;
        END $$
      
    
    CALL pro_test11

##### 3.1.6 分支语句 #####

DELIMITER $$
    CREATE PROCEDURE pro_test12(IN var INT)
        BEGIN
    
                CASE var
                    WHEN 1 THEN 
                        SELECT 'a';
                    WHEN 2 THEN
                        SELECT 'b';
                    ELSE
                        SELECT 'c';
                END CASE;
          
        END $$
      
    
    CALL pro_test12(2);

#### 3.2 函数（了解） ####

> 自定义函数,和concat等内置函数意义一致

CREATE FUNCTION func_name ([param_name type[,...]])
    RETURNS type
    [characteristic ...] 
    BEGIN
        routine_body
    END;
    
    （1）func_name ：存储函数的名称。
    （2）param_name type：可选项，指定存储函数的参数。
    （3）RETURNS type：指定返回值的类型。
    （4）characteristic：可选项，指定存储函数的特性。
    （5）routine_body：SQL代码内容。
    
    调用函数
    SELECT func_name([parameter[,…]]);

DELIMITER //
    
    CREATE FUNCTION fc_test(id INT)
    RETURNS VARCHAR(20)
    BEGIN
    
        SELECT tname INTO @name FROM t1 WHERE tid=id;
    
        RETURN @name;
    
    END //
    
    
    SELECT fc_test(2);

#### 3.3 触发器Trigger（了解） ####

##### 3.3.1 主外键级联操作 #####

![1607590968454.png][]

> Cascade 在主表上update/delete记录时，同步update/delete掉子表的匹配记录
> 
> No Action 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
> 
> Restrict 同no action, 都是立即检查外键约束
> 
> Set null 在主表上update/delete记录时，将子表上匹配记录的列设为null

*注: trigger不会受外键cascade行为的影响,即不会触发trigger*

NULL、RESTRICT、NO ACTION
    删除：从表记录不存在时，主表才可以删除。删除从表，主表不变
    更新：从表记录不存在时，主表才可以更新。更新从表，主表不变
     
    CASCADE
    删除：删除主表时自动删除从表。删除从表，主表不变
    更新：更新主表时自动更新从表。更新从表，主表不变
     
    SET NULL
    删除：删除主表时自动更新从表值为NULL。删除从表，主表不变
    更新：更新主表时自动更新从表值为NULL。更新从表，主表不变

##### 3.3.2 Trigger #####

> 触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
    trigger_name：触发器的名称
    tirgger_time：触发时机，为BEFORE或者AFTER
    trigger_event：触发事件，为INSERT、DELETE或者UPDATE
    tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
    trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
    
    所以可以说MySQL创建以下六种触发器：
    BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
    AFTER INSERT,AFTER DELETE,AFTER UPDATE
    
    BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后
    FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
    
    
    CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
    ON 表名 FOR EACH ROW
    BEGIN
        执行语句列表
    END

![1607591766008.png][]

DELIMITER $$
    CREATE TRIGGER tri_test1 AFTER DELETE
    ON t1 FOR EACH ROW
    BEGIN
    
        INSERT INTO t2(tname) VALUES('ssss');
    
    END$$
    
    SELECT * FROM t1;
    SELECT * FROM t2;
    
    UPDATE t1 SET tname = 'aaa' WHERE tid = 1;
    DELETE FROM t1 WHERE tid=1;

#### 3.4 视图（了解） ####

> 视图是一个**虚拟表**，是sql的查询结果，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图
> 
> 1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
> 
> 2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
> 
> 3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
> 
> 总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
        VIEW view_name [(column_list)]
        AS select_statement
       [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE VIEW view_test1
    AS
    SELECT * FROM t1;
    
    
    CREATE VIEW view_test2
    AS
    SELECT employee_id,first_name,manager_name FROM employee LEFT JOIN manager ON employee.`manager_id` = manager.`manager_id`;

#### 3.5 索引和约束 ####

##### 3.5.1 约束 #####

> 作用：是**为了保证数据的完整性**而实现的摘自一套机制，它具体的根据各个不同的数据库的实现而有不同的工具（约束）；

1、非空约束：not null; 指示某列不能存储 NULL 值
    
    2、唯一约束：unique(); unique约束的字段，要求必须是唯一的，但null除外；
    
    3、主键约束：primary key(); 主键约束=not  null + unique，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
    
    4、外键约束：foreign  key ;保证一个表中的数据匹配另一个表中的值的参照完整性。
    
    5、自增约束：auto_increment
    
    6、默认约束：default  给定默认的值
    
    7、检查性约束：check  保证列中的值符合指定的条件。

##### 3.5.2 索引 #####

> 作用: \*\*快速定位特定数据，提高查询效率，确保数据的唯一性，快速定位特定数据；\*\*可以加速表和表之间的连接，实现表与表之间的参照完整性，使用分组和排序语句进行数据检索时，可以显著减少分组和排序的时间全文检索字段进行搜索优化；

1、主键索引（primary key）;
    
    2、唯一索引（unique）;
    
    3、常规索引（index）;
    
    4、全文索引（full  text）;
    全文索引是MyISAM的一个特殊索引类型，它查找的是文本中的关键词，主要用于全文检索。
    MySQL InnoDB从5.6开始已经支持全文索引，但InnoDB内部并不支持中文、日文等，因为这些语言没有分隔符。可以使用插件辅助实现中文、日文等的全文索引。
    
    
    SHOW INDEX FROM table_name;
    
    索引字段尽量使用数字型（简单的数据类型）
    尽量不要让字段的默认值为NULL
    使用唯一索引
    使用组合索引代替多个列索引
    注意重复/冗余的索引、不使用的索引

**不使用索引**

1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
    2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
    3.定义为text和image和bit数据类型的列不应该增加索引,
    4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

搞定~

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5rOi5rOi54Ok6bit_size_20_color_FFFFFF_t_70_g_se_x_16_pic_center]: /images/20220828/d8a2aee50eba4433929f578c86a89191.png
[1607590968454.png]: /images/20220828/92c461a1c107435896bf77bb0d9e8546.png
[1607591766008.png]: /images/20220828/db2627f18fcd4e1cbe3f69cae60bf396.png