只此一篇，让你掌握Mysql所有语法及用法_mysql 语法

一、Mysql总结思维导图：

二、SELECT

（一）基础语法

1.查询所有的列*

SELECT * FROM 表名;
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

2.查询指定的列

SELECT 列名1,列名2...列名N FROM 表名;
SELECT ename,job,sal FROM emp;
SELECT deptno,dname FROM dept;

3.使用算术运算符（+ - * /)

-- 给所有员工增加300的薪水

SELECT ename,sal,sal+300 FROM emp; 

-- 注意运算符的顺序，如果想要改变顺序使用()括号
-- 查询员工的年薪，且为每个人发500的过节费

SELECT ename,sal,sal*12+500 FROM emp;

-- 为每位员工涨薪 50 ，查询员工的年薪

SELECT ename,sal,(sal+50)*12 FROM emp;

4.空值NULL

-- 空值是指不可用，不知道，不适用的值
-- 空值不是0 也不是空格
、
-- 计算员工的总的佣金（薪水+提成）

SELECT ename,sal,comm,(sal+comm)*12 FROM emp;

-- 空值不参与运算，如果想要空值参与运算必须将其转化成具体的数据

5.别名

/*
列名 别名
列名 AS 别名
列名 “别名”  -- 别名含有特殊字符，或者空格，或者大小写敏感，使用“”
*/

SELECT ename,sal FROM emp;
SELECT ename AS `name`,sal AS salary  FROM emp;
SELECT ename `name`,sal  salary  FROM emp;
SELECT ename,sal,comm,(sal+comm)*12  "Nian Xian" FROM emp;
SELECT ename,sal,comm,(sal+comm)*12  'Nian Xian' FROM emp;

-- 别名用于识别列的结果

6.去重操作  DISTINCT 

-- 在emp表中，有多少个部门编号

SELECT * FROM emp;
SELECT deptno FROM emp;
SELECT DISTINCT deptno FROM emp;

-- 多列去重
-- 每个部门对应的职位信息

SELECT DISTINCT job,deptno FROM emp;

-- DISTINCT 只能放在列名开头的位置，DISTINCT对select子句中的列都生效

（二）限定和排序数据

/*
SELECT 列名1，列名2，...列名N
FROM 表名
WHERE  限定条件   -- where子句要放在from子句的后面
ORDER BY 排序
*/

1.WHERE子句

-- 获取部门编号为10的雇员信息

SELECT deptno,ename,empno,job,sal,comm 
FROM emp
WHERE deptno = 10;

-- 查询员工姓名，部门编号，职位是CLERK的员工

SELECT ename,deptno,job FROM emp
WHERE job = 'CLERK';-- MySQL大小写不敏感，支持混合查询，但只在windows中，linux不支持

-- where子句中的限定条件的列，可以不出现在select子句的后面(不推荐）
-- 查询员工的姓名，职位，且仅展示年薪大于30000的员工

SELECT * FROM emp;
SELECT ename,job FROM emp
WHERE sal*12 > 30000;

SELECT ename,job,sal*12 nianxian FROM emp
WHERE sal*12 > 30000;

-- 注意：WHERE子句中不使用别名
-- 错误案例

SELECT ename,job,sal*12 nianxian FROM emp
WHERE nianxian > 30000;

2. 比较运算符 = <,>,<=,>=,!=,<>

-- 查询提成比薪水还高的员工姓名，薪水和提成

SELECT ename,sal,comm FROM emp
WHERE sal<=comm;

-- 查询出不在30号部门工作的员工姓名，薪水和员工编号

SELECT ename,sal,empno,deptno FROM emp
WHERE deptno <> 30;

3.其他的比较运算符

（1） BETWEEN...  AND ...  在两个值之间

-- 查询薪水在1500 到3000之间的所有员工

SELECT ename,sal FROM emp
WHERE sal BETWEEN 1500 AND 3000;

-- 查询结果为空

SELECT ename,sal FROM emp
WHERE sal BETWEEN 3000 AND 1500;

（2）IN 列出匹配的值

-- 查询职位是CLERK ，或者是salesman的所有员工信息

SELECT ename,job FROM emp
WHERE job IN('CLERK','SALESMAN');

（3） LIKE模糊匹配字符

%  表示匹配0个或者多个字符
_  表示匹配1个字符

-- 查询以字母S开头的所有员工姓名

SELECT ename FROM emp WHERE ename LIKE'S%';

-- 查询包含字母S的所有员工姓名

SELECT ename FROM emp WHERE ename LIKE'%S%';

-- 查询名字的第二个字符是C的所有员工姓名

SELECT ename FROM emp WHERE ename LIKE'_C%';

（4）IS NULL 匹配空值

-- 要求查询没有上级的员工

SELECT ename FROM emp WHERE mgr IS NULL;

-- 查询没有提成的所有员工信息

SELECT * FROM emp
WHERE comm IS NULL;

4.逻辑运算符 AND OR NOT

-- AND  表示所有条件都满足才返回结果
-- 查询薪水大于1100，且职位是CLERK的员工编号，员工姓名，职位，薪水

SELECT ename,job,sal FROM emp
WHERE sal> 1100
AND job ='CLERK';

-- OR 表示满足其中任意条件就返回结果
-- 查询薪水大于1100，或者职位是SALESMAN的员工编号，员工姓名，职位，薪水

SELECT ename,job,sal FROM emp
WHERE sal> 1100
OR job ='SALESMAN';

-- NOT 不包含
-- 查询职位不包含CLERK，SALESMAN，ANALYST的所有员工姓名，职位，薪水

SELECT ename,job,sal FROM emp
WHERE job  NOT IN('CLERK','SALESMAN','ANALYST');

-- 注意：运算符的优先级
-- 比较运算符 高于 逻辑运算符（NOT 高于AND 高于OR）
-- 查询职位是salesman 或者职位是president 且薪水大于1500的员工姓名，职位，薪水

SELECT ename,job,sal FROM emp
WHERE job = 'SALESMAN'
OR job = 'PRESIDENT'
AND sal> 1500;

-- 等同于

SELECT ename,job,sal FROM emp
WHERE job = 'SALESMAN'
OR (job = 'PRESIDENT'AND sal> 1500);

-- 正确的做法

SELECT ename,job,sal FROM emp
WHERE (job = 'SALESMAN' OR job = 'PRESIDENT')
AND sal> 1500;

5.排序 ORDER BY

-- ASC 升序 缺省  
-- DESC 降序
-- 按照员工的入职日期进行排序

SELECT ename,hiredate FROM emp 
ORDER BY hiredate ASC;

-- 等同于 

SELECT ename,hiredate FROM emp 
ORDER BY hiredate ;

-- 降序

SELECT ename,hiredate FROM emp 
ORDER BY hiredate DESC;

-- 同where子句，order by的列不一定出现在select子句的后面（不推荐）
-- 查询员工的入职日期和姓名，按薪水从高到低

SELECT ename,hiredate FROM emp 
ORDER BY sal DESC;

SELECT ename,hiredate,sal FROM emp 
ORDER BY sal DESC;

-- order by子句中可以使用别名
-- 按照年薪由高到低排序

SELECT ename,hiredate,sal*12 nianxian FROM emp 
ORDER BY nianxian DESC;

-- 多列排序
-- 查询员工姓名，部门编号，薪水，先按照部门编号升序排列，再按照薪水降序排列

SELECT ename,deptno,sal FROM emp 
ORDER BY deptno ASC,sal DESC;

-- 排序的列需要注意先后顺序

SELECT ename,deptno,sal FROM emp 
ORDER BY sal DESC,deptno ASC;

（三） 单行函数

什么是函数？
-- 在编程语言中，函数是为了实现特定功能的小程序，在很多高级语言中，都有一些给定的函数，也可以自己编写函数
-- 单行函数：函数对每行数据生效
-- MySQL函数大全
-- 常用：数学函数、字符函数、日期函数、条件判断函数

1：数学函数

ROUND,TRUNCATE,FLOOR,MOD 函数

（1） ROUND(m,n)

-- ROUND(m,n) 将数字m精确到小数点后n位（四舍五入），n如果不写，默认为0，表示取整

SELECT ROUND(4.333,2),ROUND(4.336,2),ROUND(4.333),ROUND(4.533) FROM DUAL;

-- dual是一张虚表，可以用来做临时的存储
-- 当n取负数，n表示精确到小数点左边第N位（四舍五入）

SELECT ROUND(127.333,-1),ROUND(127.333,-2),ROUND(127.333,-3), ROUND(527.333,-3) FROM DUAL;

（2） TRUNCATE(m,n)

-- TRUNCATE(m,n) 将数字m精确到小数点后n位（不支持四舍五入），不能省略N

SELECT TRUNCATE(4.333,2),TRUNCATE(4.336,2) FROM DUAL;

-- 取整需要将n设置为0

SELECT TRUNCATE(4.333,0),TRUNCATE(4.336,0) FROM DUAL;

-- n取负数，表示精确到小数点左边第N位（不支持四舍五入）

SELECT TRUNCATE(127.333,-1),TRUNCATE(127.336,-2),TRUNCATE(127.336,-3)  FROM DUAL;

（3） FLOOR(n)

-- FLOOR(n) 向下取整，返回小于等于n的最大整数（不支持四舍五入）

SELECT FLOOR(4.3),FLOOR(4.6) ,FLOOR(0),FLOOR(0.6)FROM DUAL;

（4） MOD(m,n)

-- MOD(m,n)返回m除以n的余数

SELECT MOD(11,3),MOD(9,3),MOD(23.45,7.8);

2.字符函数

（1）大小写转换LOWER，UPPER

-- LOWER(x) 将字符串x转换成小写

SELECT ename,deptno,job FROM emp
WHERE job='clerk';

SELECT ename,deptno,job FROM emp
WHERE LOWER(job)='clerk';

-- UPPER（x）将字符串x转换成大写

SELECT ename,deptno,job FROM emp
WHERE job= UPPER('clerk');

-- 以上哪种方式效率最高？ UPPER这种方式效率更高

（2）字符处理函数

a.  CONCAT(s1,s2，...)

返回连接参数产生的字符串，一个或多个待拼接的内容，任意一个为NULL则返回值为NULL
ename job

SELECT CONCAT(ename,' is a ',job) FROM emp;

b. CONCAT_WS(x,s1,s2,...)

返回多个字符串拼接之后的字符串，每个字符串之间有一个X

SELECT * FROM emp
SELECT CONCAT_WS('-',empno,ename,sal,'sal') FROM emp;

c. length(x)

length(x) 返回字符串的长度

SELECT LENGTH('abcdefg') FROM DUAL;
SELECT ename,LENGTH(ename) FROM emp;

-- 查询员工姓名只有5个字符的员工姓名，编号，职位，薪水

SELECT ename,empno,job,sal FROM emp
WHERE LENGTH(ename)=5;

d. SUBSTR(x,m,n)

-- SUBSTR(x,m,n) 从字符串x的第m位开始取值，取n位

SELECT SUBSTR('abcdefg',3,2) FROM DUAL;

-- n如果不写，表示取m位后面的所有字符

SELECT SUBSTR('abcdefg',3) FROM DUAL;

-- 获取每个员工ename的最后一个字符

SELECT ename,SUBSTR(ename,LENGTH(ename))FROM emp

-- 当m为负数时，则从右往左取M位

SELECT ename,SUBSTR(ename,-1)FROM emp

3.日期和时间函数

（1）NOW()

NOW() 返回当前日期和时间;

SELECT NOW();

-- 查询员工在公司工作了多少周，且结果向下取整？

SELECT ename, FLOOR((NOW()-hiredate)/7) AS weeks FROM emp; -- 错误案例
 
SELECT ename,hiredate FROM emp 

（2）curdate()

SELECT CURDATE();
SELECT ename, FLOOR((CURDATE()-hiredate)/7) AS weeks FROM emp;-- 错误案例,日期类型不能直接参与数学计算

-- 对于MySQL来讲，我们可以直接用字符串类型查询出日期的结果

SELECT ename,hiredate FROM emp WHERE hiredate='1980-12-17'; 

-- datediff(m,n) 计算日期m-n的差值

SELECT DATEDIFF(CURDATE(),hiredate) FROM emp
SELECT ename,hiredate,CURDATE(),
FLOOR(DATEDIFF(CURDATE(),hiredate)/7) AS weeks FROM emp

（3）YEAR(DATE)

-- YEAR(DATE) 返回日期所在的年份
-- month(date) 返回日期所在的月份
-- day（date） 返回日期所在的天

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE());

-- 查询1981年入职的员工

SELECT ename ,hiredate FROM emp
WHERE YEAR(hiredate)=1981;

4.条件判断函数

（1） IF

 IF（条件判断，value1，value2）如果条件判断为真，则返回value1的值，否则返回value2的值
-- 判断员工的薪水，如果薪水高于等于3000 显示高富帅，低于3000 显示 穷屌丝

SELECT ename,sal,IF(sal>=3000,'高富帅','穷屌丝') FROM emp;

（2）IF 嵌套

-- 判断员工的薪水，如果薪水高于等于3000 显示高富帅，在3000以下1000以上 显示 上班族  1000以下 穷屌丝

SELECT ename,sal,IF(sal>=3000,'高富帅',IF(sal<1000,'穷屌丝','上班族')) AS jibie FROM emp;
SELECT ename,sal,IF(sal>=3000,'高富帅',IF(sal>=1000,'上班族','穷屌丝')) AS jibie FROM emp;

（3） IFNULL(value1，value2)

-- IFNULL(value1，value2),如果value1不为空则返回自己，如果为空则返回value2
-- 计算年薪（薪水+提成）

SELECT ename,sal,comm,(sal+comm)*12 FROM emp; 
SELECT ename,sal,comm,(sal+IFNULL(comm,0))*12 FROM emp; 

（4） CASE 表达式

/*
CASE 表达式
WHEN value1 THEN result1
WHEN value2 THEN result2
...
WHEN valueN THEN resultN
ELSE resultN+1
END
*/
-- 公司计划给员工涨薪
-- clerk 涨200
-- salesman 涨50
-- manager 涨500
-- 其他人不变

SELECT ename,sal,job,deptno,
(CASE job
WHEN 'CLERK' THEN sal+200
WHEN 'SALESMAN' THEN sal+50
WHEN 'MANAGER' THEN sal+500
ELSE sal
END) AS zhangxin
FROM emp;

（四）多表查询

1.笛卡尔积-- 笛卡尔积
-- 集合A和集合B中所有元素组成的有序对叫做笛卡尔积，记做A*B
-- 查询emp表中员工姓名在dept表中所对应的部门名称
SELECT ename,dname FROM emp,dept; -- 56 row=14*4 全排列组合
-- 笛卡尔积是多表查询的理论基础
-- 多表查询在进行连接时，要避免出现笛卡尔积，语法如下：

SELECT table1.colunm,table2.column
FROM table1,table2
WHERE table1.column=table2.column
 
SELECT emp.ename,dept.dname FROM emp,dept
WHERE emp.deptno = dept.deptno;

-- WHERE子句中可以写连接条件（当连接N张表时，至少需要N-1个连接条件）
-- 多表查询的分类

2.等值连接（内连接）

-- 用等号连接多张表
-- 查询员工编号，员工部门，以及部门地址

SELECT emp.empno,emp.deptno,dept.deptno,dept.dname
FROM emp,dept
WHERE emp.deptno = dept.deptno;

-- 使用别名化简查询  

SELECT e.empno,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno;

-- 说明：别名的化简最好是有意义的单词或者字母
-- 使用AND运算符附加查询条件
-- 显示KING的员工编号，姓名，部门号，部门地址

SELECT e.empno,e.ename,d.deptno,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.ename='KING';

3.非等值连接（内连接）

-- 除等号以外的其他符号连接

SELECT * FROM salgrade;

-- 要求查询出每个员工薪水对应的级别（姓名，薪水，等级）,员工KING除外

SELECT e.ename,e.sal,s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND e.ename <> 'KING';

4.外连接

（1）左外连

-- 当需要显示某些不满足条件的连接条件时，可以使用外连接
-- 左外连接LEFT OUTER JOIN  ON  返回左边表中的所有数据

SELECT e.empno,d.deptno,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

（2）右外连

-- 右外连接RIGHT OUTER JOIN  ON  返回右边表中的所有数据

SELECT e.empno,d.deptno,d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

（3） 全外连

-- 全外连接FULL OUTER JOIN  左右两张表所有的数据都要展示（MYSQL中不支持）
-- 在Oracle还支持另外一种左右连接的语法

SELECT e.empno,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno(+); -- 左连接，右表补齐
SELECT e.empno,d.deptno,d.dname
FROM emp e,dept d
WHERE e.deptno(+) = d.deptno; -- 右连接，左表补齐

5.自连接

-- 把一张表看做两张表，在单张表内进行关联
-- 请将每个员工自己的名字和上级的名字都找出来  例如 smith  works for  ford

SELECT mgr FROM emp WHERE  ename='SMITH';   -- mgr7902
SELECT ename FROM emp WHERE  empno=7902;    -- ford

-- 把emp看做是两张表
员工表(empno,ename,job,mgr,hiredate,sal,comm,deptno)  emp w
经理表(empno,ename,job,hiredate,sal,comm,deptno)    emp m

SELECT w.ename,'works for', m.ename
FROM emp w,emp m
WHERE w.mgr=m.empno ; -- 员工表中的mgr，等于经理表中的empno

（五）多行函数

-- 多行函数：针对表中的多行数据做运算，得到一个或者一组结果
-- 常用的多行函数：聚合函数，分组函数

1.常用的聚合函数

-- 查询emp表中所有员工的平均工资，工资之和，最高工资和最低工资

SELECT  AVG(sal),SUM(sal),MAX(sal),MIN(sal) FROM emp;

-- 普通列不能和聚合函数一起作为select子句的条件，否则可能会报错，或者得到错误的结果

-- 聚合函数进行计算时会自动忽略空值，要空值参与运算需要用ifnull转化成0
-- 求emp表中提成的平均值

SELECT AVG(comm) ,COUNT(comm)FROM emp;
SELECT AVG(IFNULL(comm,0)) ,COUNT(IFNULL(comm,0))FROM emp;

-- 注意：聚合函数可以和普通的函数嵌套使用

SELECT ROUND(AVG(sal),2) FROM emp

-- 注意： 聚合函数不可以和聚合函数嵌套（MySQL不支持）

-- 计算每个职位的最高工资，然后再求和
-- 错误案例

SELECT SUM（MAX(sal)）FROM emp 
GROUP BY  job;

（1）AVG 求平均

-- 查询emp表中所有员工的平均工资，工资之和，最高工资和最低工资

SELECT  AVG(sal),SUM(sal),MAX(sal),MIN(sal) FROM emp;

-- 普通列不能和聚合函数一起作为select子句的条件，否则可能会报错，或者得到错误的结果

（2）SUM 求和

（3）MAX 求最大值

-- 查询emp表中SALES部门员工的平均工资，工资之和，最高工资和最低工资

SELECT  AVG(sal),SUM(sal),MAX(sal),MIN(sal) 
FROM emp 
WHERE job LIKE 'SALES%';

-- min,max 函数可以针对数字类型之外的其他类型
-- 查询最早入职的员工和最晚入职的员工信息

SELECT MIN(hiredate),MAX(hiredate) FROM emp;

（4）MIN 求最小值

（5）COUNT 计数

-- count() 返回表中的记录数

SELECT COUNT(*) FROM emp;  -- 不推荐使用

-- 推荐使用count（1）代替* 提高查询效率

SELECT * FROM emp;
SELECT 1 FROM emp;
SELECT empno FROM emp;
SELECT COUNT(1) FROM emp;  
SELECT COUNT(empno) FROM emp;  -- 也可以指定统计的某个列

2. GROUP BY 分组函数

/*
SELECT 列名1，列名2..列名N 
FROM 表名
WHERE 限定条件 
GROUP BY  分组条件
HAVING 分组限定
ORDER BY 排序
*/
-- 计算每个职位的最高工资

SELECT job,MAX(sal) FROM emp  -- job是分组函数指定的列，可以和聚合函数一并查询
GROUP BY  job;

-- 按部门计算每个部门的平均工资

SELECT AVG(sal) FROM emp
GROUP BY deptno

-- 分组条件可以不写在select子句中（不推荐）

SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno

-- 分组函数也可以单独使用（不和聚合函数一起用）
-- 查看哪些部门的人数大于5人

SELECT deptno FROM emp 
GROUP BY deptno 
HAVING COUNT(1)>5

-- 多列分组
-- 按部门分组统计emp表中各个职位的工资总和

SELECT deptno,job , SUM(sal) FROM emp
GROUP BY  deptno,job;

-- 等同于

SELECT deptno,job , SUM(sal) FROM emp
GROUP BY  job,deptno;

3. HAVING 子句 （分组限定）

-- 求平均工资高于2000的部门
-- 错误案例

SELECT deptno,AVG(sal) FROM emp
WHERE AVG(sal) > 2000;
GROUP BY deptno

-- 不能使用where子句限定聚合函数，可以用having子句限定

SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno 
HAVING AVG(sal) > 2000;

-- 查询平均工资大于2900的部门，并展示出该部门的最高工资

SELECT deptno,MAX(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) >2900

-- 注意：select子句后面的聚合函数可以和having子句的聚合函数不同

-- 查询最高工资不低于3000的部门的人数

SELECT deptno,COUNT(1)
FROM emp
GROUP BY deptno
HAVING  MAX(sal) >= 3000;

4.where与having

-- 使用WHERE子句进行限定普通的列
-- 查询emp表中各个职位的薪水总和，只查询薪水总和大于5000的职位，且职位不是SALES开头的，并按照薪水总和降序排列

SELECT  job,SUM(sal) FROM emp
WHERE job NOT LIKE 'SALES%'
GROUP BY job 
HAVING SUM(sal)> 5000
ORDER BY SUM(sal) DESC;

-- HAVING子句可以支持普通的列和聚合函数的列，但having子句必须和group by一起使用，不能独立使用（group by可以不加having，但是having必须加group by）

SELECT  job,SUM(sal) FROM emp
GROUP BY job 
HAVING SUM(sal)> 5000 
AND job NOT LIKE 'SALES%'
ORDER BY SUM(sal) DESC;

-- 查询emp表中各个职位的薪水总和，只查询薪水总和大于5000的职位，且销售部（dept表）除外，并按照薪水总和降序排列

SELECT job, SUM(sal)
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND d.dname <> 'SALES'
GROUP BY job
HAVING SUM(sal) > 5000
ORDER BY SUM(sal) DESC;

（六）子查询

-- 找出工资高于JONES的员工信息

子查询：SELECT sal FROM emp WHERE ename='JONES';  -- 2975
主查询：SELECT * FROM emp WHERE sal > 2975;

-- 子查询的规则
/*
子查询要用（）括起来
习惯上子查询语句放在比较运算符的右边
子查询的语句一般不使用order by 排序
*/

1.单行子查询

-- 子查询的结果只返回一行数据，使用单行比较运算符 = <  > <= >= != <>

SELECT * FROM emp WHERE sal > 
(SELECT sal FROM emp WHERE ename='JONES');

-- 查询同ALLEN部门编号和职位都相同的员工 

SELECT deptno FROM emp WHERE ename='ALLEN';  -- 30
SELECT job FROM emp WHERE  ename='ALLEN'; -- SALESMAN
 
SELECT * FROM emp
WHERE deptno = (SELECT deptno FROM emp WHERE ename='ALLEN')
AND job = (SELECT job FROM emp WHERE  ename='ALLEN')

-- 子查询的数据可以和主查询不在同一张表中
-- 查询ALLEN所在的部门的名称
-- 子查询

SELECT dname FROM dept
WHERE deptno = 
(SELECT deptno FROM emp WHERE ename='ALLEN')

-- ename='ALLEN'   emp表中有14行数据 ename需要匹配14次
-- deptno=30       dept表中4行数据，depno需要匹配4次
-- 14+4=18次

-- 多表查询

SELECT d.dname,e.ename
FROM dept d,emp e
WHERE e.deptno=d.deptno
AND e.ename='ALLEN'

-- e.deptno=d.deptno   emp表中有14行数据，同d.deptno匹配需要14次
-- e.ename='ALLEN'     emp表中有14行数据 ename需要匹配14次
-- 14+14=28次

总结
-- 如果查询的最终结果来自一张表，用子查询和多表查询都可以实现，推荐子查询
-- 如果查询的最终结果来自多张表时，只能用多表查询

-- 子查询使用聚合函数
-- 查询薪水最高的员工的姓名，薪水，职位

SELECT ename,sal,job FROM emp
WHERE sal =
(SELECT MAX(sal) FROM emp);

-- 使用GROUP BY HAVING语句
-- 查询部门的平均工资，只显示高于所有员工平均工资的部门

SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno 
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);

-- 查询薪水高于30号部门最低工资的，其他部门的最低工资

SELECT deptno,MIN(sal)
FROM emp
GROUP BY  deptno
HAVING MIN(sal)>
(SELECT MIN(sal) FROM emp WHERE deptno = 30)

2.多行子查询

-- 子查询的结果返回多个记录，使用多行比较运算符 IN，ANY，ALL
-- 查询每个部门最低薪水的员工编号和姓名
-- IN 匹配列中的多个数据

SELECT empno,ename ,sal,deptno FROM emp
WHERE sal IN
(SELECT MIN(sal) FROM emp GROUP BY deptno);

-- ANY
-- 查询职位比CLERK 最高薪水还少（小于最大值）的其他职位的员工信息

SELECT ename,job,sal FROM emp
WHERE sal < ANY
(SELECT  sal FROM emp WHERE job='CLERK');

-- 等同于

SELECT ename,job,sal FROM emp
WHERE sal <
(SELECT MAX(sal) FROM emp WHERE job='CLERK');

-- < ANY 小于最大值，> ANY 大于最小值

SELECT ename,job,sal FROM emp
WHERE sal > ANY
(SELECT  sal FROM emp WHERE job='CLERK');

-- <ALL 小于最小值，> ALL 大于最大值

SELECT ename,job,sal FROM emp
WHERE sal < ALL
(SELECT  sal FROM emp WHERE job='CLERK');

SELECT ename,job,sal FROM emp
WHERE sal > ALL
(SELECT  sal FROM emp WHERE job='CLERK');

3.多列子查询（多行多列子查询）

-- 使用IN进行匹配
-- 查询同ALLEN部门编号和职位都相同的员工 

SELECT * FROM emp
WHERE (deptno,job) IN (SELECT deptno,job FROM emp WHERE ename='ALLEN')

-- 子查询中的列必须和主查询中的列关系对应

4.将子查询的结果看作是一张表，再进行二次查询

-- 子查询语句直接放在FROM 的后面，当做一张表，取别名
-- 查询职位是CLERK的最低工作和最高工作之和

SELECT MIN(sal),MAX(sal) FROM emp
WHERE job='CLERK'

SELECT lo+hi FROM
(SELECT MIN(sal) lo ,MAX(sal) hi,job FROM emp
GROUP BY job) ssal
WHERE job='CLERK';

-- 等同于

SELECT MIN(sal)+MAX(sal) FROM emp
WHERE job='CLERK'

-- 查询每个部门的部门名称，所在地和人数，并且部门人数不低于5人

SELECT d.dname,d.loc,c.ct
FROM dept d, 
(SELECT deptno,COUNT(1) ct FROM emp
GROUP BY deptno) c
WHERE d.deptno=c.deptno
AND c.ct >= 5;

5.分页子查询

-- limit m,n 从第m+1行开始取数据，取n行结束
-- limit的位置是在整个select语句的最后

SELECT * FROM emp
LIMIT 3,4

-- 查询工资排名前三位的员工信息

SELECT * FROM emp
ORDER BY sal DESC
LIMIT 0,3   -- 0可以省略

-- 查询工资排名前三位的员工信息，如果有并列的也一并展示

SELECT * FROM emp
WHERE sal IN
(SELECT * FROM
(SELECT DISTINCT sal FROM emp
ORDER BY sal DESC
LIMIT 3) a);

（七）表的集合操作

-- 当查询条件比较复杂时，可以使用集合操作将多个查询语句进行组合
-- 将每个查询结果看作是多行数据的集合，集合与集合之间可以用UNION，UNION ALL连接

-- 查询薪水大于2500，或者职位是manager的员工

SELECT ename,sal,job FROM emp WHERE  sal > 2500
UNION
SELECT ename,sal,job FROM emp WHERE  job = 'MANAGER';

 
-- 等同于OR

SELECT ename,sal,job FROM emp WHERE  sal > 2500
OR job = 'MANAGER';

-- UNION ALL 展示两边查询的所有结果，不去重

SELECT ename,sal,job FROM emp WHERE  sal > 2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE  job = 'MANAGER';

-- 查询工资排名前三的员工或者查询高于自己部门平均工资的员工的姓名，部门和职位

SELECT ename,deptno,job FROM emp
WHERE sal IN
(SELECT * FROM
(SELECT DISTINCT sal FROM emp
ORDER BY sal DESC
LIMIT 3) a)
UNION
SELECT e.ename,e.deptno,e.job FROM  emp e,
(SELECT deptno,AVG(sal) asal FROM emp GROUP BY deptno) a
WHERE e.deptno=a.deptno
AND e.sal > a.asal;

-- UNION 在连接两个select语句时，以第一个查询语句的列为准
-- UNION可以连接N张不同的表，只要列的数据类型和大小相同即可
-- 查询所有“女”教师和“女”同学的NAME、sex和birthday.  （学生信息表）

SELECT Tname,Tsex,Tbirthday FROM Teacher WHERE Tsex = '女'
UNION
SELECT Sname,Ssex,Sbirthday FROM Student WHERE Ssex = '女';

-- MySQL不支持 交集（INTERSECT） 差集（minus） 
-- 交集：只取两个查询语句都满足的记录
-- 差集：查询出第一个查询条件中存在，但在第二个查询结果中不存在的数据

86期SQL语句板书（select部分）.sql

事务TCL

COMMIT 提交
ROLLBACK 回滚
SAVEPOINT  设置回滚点

SET autocommit = 0; 表示手动提交事务，=1则表示自动提交事务（默认值）

参见: 1.INSERT 插入 (扩展)

什么是事务？

-- 包含SQL语句的一个执行整体，对DML操作语句生效（对其他操作语句无效）
-- MySQL默认自动提交事务，可以通过命令来修改默认值

-- 实际应用场景
ATM转账   A卡向B卡转账，结果A卡扣款成功，B卡到账失败  ——回滚A卡的数据
电商订单  确认订单后，商品库存被扣减，如果下订单失败，则商品库存需要回滚到下订单之前

示例

-- 在emp表中插入一条记录

INSERT INTO employees.emp 
    (empno, 
    ename, 
    job, 
    mgr, 
    hiredate, 
    sal, 
    comm, 
    deptno
    )
    VALUES
    (6666, 
    'XIAOMING', 
    'DEVELOPMENT', 
    '7788', 
    CURDATE(), 
    '2000', 
    NULL, 
    50
    );

SELECT * FROM emp;

SAVEPOINT A;

INSERT INTO employees.emp 
    (empno, 
    ename, 
    job, 
    mgr, 
    hiredate, 
    sal, 
    comm, 
    deptno
    )
    VALUES
    (7777, 
    'XIAOMING', 
    'DEVELOPMENT', 
    '7788', 
    CURDATE(), 
    '2000', 
    NULL, 
    50
    );

-- 要求还原到上一组插入的数据，如何回滚？

ROLLBACK TO SAVEPOINT A;
SELECT * FROM emp;

ROLLBACK ;  -- 直接回滚则是回滚到最初设置事务的位置

SELECT * FROM emp;

-- 事务一旦提交，则直接对数据库服务器中的数据生效，不能再次回滚

三、非SELECT

（一）语言基础

1.分类

-- 按照SQL92、99标准 分为4大类
1：数据操作语言（DML） SELECT 查询，INSERT 插入，UPDATE 更新，DELETE 删除， 
2：数据定义语言（DDL） CREATE 创建，ALTER修改结构，DROP 删除 ,TRUNCATE 删除 等语句
3：事务处理语言（TCL） COMMIT 提交，ROLLBACK 回滚事务 等语句
4：数据控制语言（DCL） GRANT 授权，REVOKE 解除授权，

说明：也可以把SELECT 作为数据操作语言（DQL）

2.SQL语句的语法规范

- SQL语句的语法规范
 1. SQL语句的所有表名、字段名全部小写，系统保留字、内置函数名、SQL保留字大写。
 2. 连接符OR、IN、AND、以及＝、<=、>=等前后加上一个空格。
 3. 对较为复杂的SQL语句、存储过程、函数加上注释，说明算法、功能。
-- 单行注释
/*
多行注释
*/ 
 4. SQL语句的缩进风格
 1) 一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进 
 2) WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐。 
 5. 多表连接时，使用表的别名来引用列。

3.其他注意事项

1. SQL 命令大小写不敏感
2. SQL 命令可写成一行或多行
3. 一个关键字不能跨多行或缩写
4. 子句通常位于独立行，以便编辑，并易读 
5. SQL语句用分号作为分隔符，系统读取到分号才会执行语句

（二） 表管理DDL语句

数据定义语言（DDL） CREATE 创建，ALTER修改结构，DROP 删除 ,TRUNCATE 删除 等语句

1.新建表

CREATE TABLE 表名
(
列名1 数据类型 [DEFAULT 默认值][列级约束1,列级约束2 ……],
列名2 数据类型 [DEFAULT 默认值][列级约束1,列级约束2 ……],
...,
列名n 数据类型 [DEFAULT 默认值][列级约束1,列级约束2 ……],
[CONSTRAINT 约束名称 约束类型（列名）],
[CONSTRAINT 约束名称 约束类型（列名）],
...
);

表名的命名规则

1)必须以字母开头
2)可包括数字
3)只能包含A-Z, a-z, 0-9, _, $, AND #
4)不要使用MySQL的保留字
5)同一用户的表对象不能同名

数据类型

CHAR（10）: 定长字符串    输入LUCY ，结果将占有10个字符
VARCHAR（10）：变长字符串  输入LUCY，结果将占有4个字符
ENUM('男','女')

插入一条数据

CREATE TABLE student
(
学号 INT(5),
姓名 VARCHAR(20),
年龄 TINYINT(2),
性别 CHAR(1),
系名 VARCHAR(10),
身份证号 VARCHAR(18),
专业号 INT(4)
)
CHARACTER SET = utf8; -- 创建表时数据支持中文字符样式
 
SELECT * FROM student;-- 查看表数据
DESC student; -- 查看表结构

-- 插入一条数据

INSERT INTO student
VALUES(20204,'王小明',19,'女','经济系','510839199810106670',1001);
 
INSERT INTO student(学号,姓名,系名)
VALUES(20305,'黄大鹏','管理系');
 
INSERT INTO student
VALUES
(20506,'张文斌',18,'女','外语系','510639199905183452',1005),
(20507,'张文',18,'男','外语系','510639199905183433',1005),
(20508,'张文',19,'男','外语系','510639199905183499',1005);

2.修改表结构

-- 不推荐在建表完成后去修改表结构，特别是表中已经有数据的情况
-- 修改默认值
ALTER TABLE 表名 ALTER COLUMN 列名 SET DEFAULT 默认值;
-- 删除默认值
ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT 默认值;

ALTER TABLE student ALTER COLUMN 性别 SET DEFAULT '女';
 
INSERT INTO student(学号,姓名,系名)
VALUES(20601,'黄小明','表演系');
 
SELECT * FROM student;

添加/删除列

ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE 表名 DROP 列名;

ALTER TABLE student  ADD 联系电话 VARCHAR(11);
ALTER TABLE student  DROP 联系电话;

修改列名

ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新列的数据类型;-- 添加/修改列
ALTER TABLE 表名 MODIFY COLUMN 列名 数据类型;-- 修改数据类型

ALTER TABLE student CHANGE COLUMN 系名 院系名 VARCHAR(20);
ALTER TABLE student MODIFY COLUMN 姓名 VARCHAR(40);
DESC student;

3.删除表

TRUNCATE TABLE 表名;-- 删除表中所有的数据，不能删除部分数据，全部删除
DROP TABLE 表名;--  删除整张表结构，含数据

TRUNCATE TABLE student;
SELECT * FROM student;
DROP TABLE student;

（三）表的约束

-- 约束的种类
DEFAULT -- 默认值
PRIMARY KEY -- 主键约束
FOREIGN KEY -- 外键约束
NOT NULL -- 非空约束
UNIQUE -- 唯一约束
AUTO_INCREMENT  -- 自增约束

1.在创建表时增加约束

CREATE TABLE student2
(
学号 INT(5) PRIMARY KEY,
姓名 VARCHAR(20) NOT NULL,
年龄 TINYINT(2) DEFAULT 18,
性别 ENUM('男','女') NOT NULL  DEFAULT '女',
系名 VARCHAR(10),
身份证号 VARCHAR(18),
专业号 INT(4)
)
CHARACTER SET = utf8;
 
SELECT * FROM student2;
DESC student2;

-- 创建course表

CREATE TABLE course
(
专业号 INT(5) PRIMARY KEY AUTO_INCREMENT,
专业名称 VARCHAR(20) NOT NULL
)
CHARACTER SET = utf8;
 
SELECT * FROM course;

2.建表后增加约束

-- AUTO_INCREMENT默认从1开始自增，可以通过赋值改变初始值

ALTER TABLE  course AUTO_INCREMENT = 1001;
INSERT INTO  course(专业名称)
VALUES ('金融学');
INSERT INTO  course(专业名称)
VALUES ('国际金融与贸易');

-- 为student2表添加一个非空约束
ALTER TABLE 表名  MODIFY  列名 数据类型 NOT NULL;

ALTER TABLE student2 MODIFY 系名 VARCHAR(10) NOT NULL;
DESC student2

-- 为student2表添加一个唯一约束
ALTER TABLE 表名  ADD UNIQUE(列名);
ALTER TABLE student2  ADD UNIQUE(身份证号);

INSERT INTO student2
VALUES(20204,'王小明',19,'女','经济系','510839199810106670',1001);
 
INSERT INTO student2
VALUES(20205,'王小明',19,'女','经济系','510839199810106670',1001);
 
INSERT INTO student2
VALUES(20205,'王小明',19,'女','经济系','510839199810106671',1001);
 
INSERT INTO student2
VALUES(20205,NULL,19,'女','经济系','510839199810106671',1001);
 
SELECT * FROM student2;

-- 为student2表中的专业号设置外键约束
ALTER TABLE 从表名 ADD CONSTRAINT 外键名
FOREIGN KEY(从表的外键字段) REFERENCES 主表(主键字段);--  student2 表是从 ，course表是主表

ALTER TABLE student2 ADD CONSTRAINT FK_student2_course
FOREIGN KEY(专业号) REFERENCES course(专业号);
 
DESC student2

-- 查看表中的各种约束条件

SELECT * FROM information_schema.TABLE_CONSTRAINTS
WHERE constraint_schema = 'studentinfo2';

-- 查看创建的表的整体结构
SHOW CREATE TABLE 表名;

SHOW CREATE TABLE student2;
CREATE TABLE `student2` (
  `学号` INT(5) NOT NULL,
  `姓名` VARCHAR(20) NOT NULL,
  `年龄` TINYINT(2) DEFAULT '18',
  `性别` ENUM('男','女') NOT NULL DEFAULT '女',
  `系名` VARCHAR(10) NOT NULL,
  `身份证号` VARCHAR(18) DEFAULT NULL,
  `专业号` INT(4) DEFAULT NULL,
  PRIMARY KEY (`学号`),
  UNIQUE KEY `身份证号` (`身份证号`),
  KEY `FK_student2_course` (`专业号`),
  CONSTRAINT `FK_student2_course` FOREIGN KEY (`专业号`) REFERENCES `course` (`专业号`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

（四）表的操作（DML语句）

1.INSERT 插入

-- 全量数据插入，插入的数据，顺序必须和建表时的顺序一致
INSERT INTO 表名
VALUES(value1,value2...valueN);
-- 按照自定义的列进行数据的插入
INSERT INTO 表名(列名1，列名2，列名3...列名N)
VALUES(value1,value2...valueN);
-- 批量插入数据，插入的数据，顺序必须和建表时的顺序一致
INSERT INTO 表名
VALUES
(value1,value2...valueN)，
(value1,value2...valueN)，
(value1,value2...valueN);

SELECT * FROM dept;
-- 要求在部门表中插入一条记录
INSERT INTO dept
VALUES(50,'DEVELOPMENT','BEIJING');
-- dept表按指定列再次插入一行数据
INSERT INTO dept(deptno,dname)
VALUES(60,'MIS');
-- 注意空值的处理
INSERT INTO dept
VALUES(70,'TESTTEAM',NULL);

参见: 事务TCL (扩展)

从其他表中插入数据到当前表insert + select

-- 把有提成的员工信息，导入到bonus表中

CREATE TABLE bonus
(
ename VARCHAR(10),
job VARCHAR(9),
sal FLOAT(7,2),
comm FLOAT(7,2)
)
SELECT * FROM bonus;

-- INSERT INTO 表名 SELECT 完整查询语句

INSERT INTO bonus
SELECT ename,job,sal,comm FROM emp 
WHERE comm IS NOT NULL;
 
DESC emp

2. UPDATE 修改

UPDATE 表名
SET 列名1=value1，列名2=value2....列名N=valueN
WHERE 限定条件

-- 调整dept表中50号部门的地址

UPDATE dept
SET loc='CHENGDU' WHERE deptno = 50;
SELECT * FROM dept

-- 想要给emp表中所有员工+200元 其中KING不加薪水

UPDATE emp
SET sal=sal+200 WHERE ename <> 'KING';
SELECT * FROM emp;

-- UPDATE语句中的set和where后面都可以使用子查询语句，但不能是对同一张表的操作
-- 将所有工资比ALLEN高的员工，薪水改成和ALLEN一样
-- 错误案例

UPDATE emp 
SET sal = (SELECT sal FROM emp WHERE ename = 'ALLEN') 
WHERE sal > (SELECT sal FROM emp WHERE ename = 'ALLEN');
 
UPDATE emp d,(SELECT e.sal FROM emp e WHERE e.ename='ALLEN') a SET d.sal = a.sal
WHERE d.sal >a.sal;

-- 将RESEARCH部门所有员工的上级改为King

UPDATE emp
SET mgr = (SELECT empno FROM (SELECT empno FROM emp WHERE ename='KING') a)
WHERE deptno= (SELECT deptno FROM dept WHERE dname= 'RESEARCH')

-- 另一种解法

UPDATE emp e,dept d SET mgr = 7902 WHERE e.deptno=d.deptno
AND d.dname='research';
 
SELECT * FROM emp WHERE deptno=20 
 
ROLLBACK;

3. DELETE 删除

DELETE [FROM] 表名
WHERE 限定条件 -- 如果delete不加where条件，则表示删除表中的所有数据

SELECT * FROM bonus;
DELETE FROM bonus;
COMMIT;

-- 删除dept表中50,60,70号部门

DELETE FROM dept WHERE deptno IN(50,60,70) ;
SELECT * FROM dept;
COMMIT;

-- 使用子查询删除数据
-- 删除sales部门的所有员工信息

DELETE FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname='SALES');
SELECT * FROM emp;
ROLLBACK;

注意：请先关闭自动提交事务功能 SET autocommit=0;

（五）常用数据库对象

1.视图

-- 视图是查询数据的快捷方式，视图不存储数据，存储的是一个查询语句
/*
CREATE VIEW 视图名称
（别名1，别名2，别名3...别名N）
AS
SELECT -- 一个完整的查询语句

1）新建视图

【用法一】针对不同的用户，提供不同的权限（权限控制）

-- HR，BOSS 想要查看每个员工的薪水

SELECT empno,ename,sal FROM emp;

-- 20号部门的经理，要求只能查询自己部门的员工薪水

SELECT empno,ename,sal FROM emp WHERE deptno = 20;
 
CREATE VIEW v_dept20
AS
(SELECT * FROM emp WHERE deptno = 20);
 
SELECT * FROM v_dept20   -- 视图和表的用法相同

-- 对视图进行操作，为SMITH涨薪水 200

UPDATE v_dept20
SET sal=sal+200
WHERE ename='SMITH';

Q: 请问emp表SMITH的薪水是多少？ -- 1000 修改视图的数据等于修改原表的数据 

SELECT empno,ename,sal FROM emp WHERE ename='SMITH'; 

【用法二】简化复杂的查询语句

-- 通过部门名称查询该部门的平均工资

SELECT AVG(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
AND d.dname='SALES';

-- 视图

CREATE VIEW v_avgsal
AS
(SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno);
 
SELECT * FROM v_avgsal;
SELECT AVG(sal) FROM v_avgsal WHERE dname='SALES'

2）删除视图

-- DROP VIEW 视图名称

DROP VIEW  v_dept20;  -- 删除视图，实际上只是删除了一个select语句，不会影响到原表的数据
DROP VIEW  v_avgsal;  
SELECT * FROM emp

2.索引 INDEX

Q:什么是索引？
-- 索引（INDEX）类似书的目录，是供服务器在表中快速查找和操作行的数据库结构
-- 索引是以列为单位建立的，经常作为查询条件的列，可以创建索引来提高查询效率

Q：索引设计原则
-- 经常出现在where，order by，group by后面的列可以创建索引
-- 有大量空值的列，数据频繁改动的列，不经常作为查询条件的列不要创建索引
-- 当某个数据具有唯一性特征时，可以指定唯一索引，以确保数据的完整性，提高查询速度

Q:优点
-- 可以大大提高数据的查询速度，这是建索引的主要原因
-- 索引的内部结构由系统自动创建，每次对数据的修改索引也会自动更新
-- 在创建主键约束和唯一约束时，会自动创建主键索引和唯一索引，保证每行数据的唯一性

Q:不足
-- 索引虽然提高了查询速度，同时却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
-- 建立索引会占用磁盘空间的索引文件，每个表至少支持16个索引（依据存储引擎的不同而有所区别）

1）普通索引

普通索引——允许在定义的索引列中插入重复的值/空值
-- 创建普通索引
CREATE INDEX 索引名称 ON 表名（列名(`length`)）; -- 如果是BLOB,TEXT数据类型，必须制定length
-- 为ename字段创建索引

CREATE INDEX ind_ename ON emp(ename);

-- 添加索引

ALTER TABLE emp ADD INDEX ind_mgr(mgr);

-- 查看索引

SHOW INDEX FROM emp

-- 在建表时添加索引

CREATE TABLE  mytable
(
id INT PRIMARY KEY,
myname VARCHAR(20),
INDEX ind_myname(myname)
);
 
SELECT * FROM mytable;

2）唯一索引唯一索引——索引的列数据必须唯一，但允许有空值NULL，如果是组合索引，则列值的组合必须唯一
-- 创建索引
CREATE UNIQUE INDEX 索引名称 ON 表名（列名(`length`)）;
-- 添加索引
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名称（列名(`length`)）;
-- 在建表时添加索引

CREATE TABLE  mytable1
(
id INT PRIMARY KEY,
myname VARCHAR(20),
UNIQUE ind_myname(myname)
);
 
SHOW INDEX FROM mytable1

-- 当经常使用多个组合条件进行查询时使用组合索引

CREATE INDEX ind_job_deptno ON emp(job,deptno);
SHOW INDEX FROM emp

-- 如果查询条件只有job，该索引生效
-- 如果查询条件是job和deptno 该索引生效
-- 如果查询条件是deptno 该索引不生效

3） 删除索引

DROP  INDEX 索引名称 ON 表名;

DROP  INDEX ind_job_deptno ON emp;
DROP  INDEX ind_mgr ON emp;
DROP  INDEX ind_ename ON emp;

【注意】索引的限制条件
A.MyISAM存储引擎支持的索引引键长度不超过1000字节
B.MySQL目前不支持函数索引
C.使用!= ,<> 不能使用索引

SELECT * FROM emp
WHERE ename <> 'ALLEN';
 
SELECT * FROM emp
WHERE ename < 'ALLEN'
OR ename > 'ALLEN'

D. LIKE不能以%开头，无法使用索引

（六）存储过程  Stored PROCEDURE （SP）

-- 简单来说，存储过程是一条或者多条SQL语句的集合
-- 存储过程能够实现较复杂的数据处理
-- MYSQL中的编程主要是在存储过程中实现的

-- 实际应用场景
-- 业务场景，部分业务逻辑被写入了SP中 举例：电商业务中，生成一张订单：插入数据到订单表；商品信息表修改商品数量，插入一条日志信息
-- 构建一整套测试数据  举例： 准备100个注册用户信息；准备N条日志测试翻页

DELIMITER //
CREATE PROCEDURE 存储过程名称（参数列表）
BEGIN 
程序主体;
END //
DELIMITER ;

-- DELIMITER //是告诉系统不要将分号作为分隔符，而是将//作为分隔符
-- DELIMITER ;是告诉系统请使用分号作为后续的分隔符

-- 参数列表
IN 参数名称 参数类型  -- 输入参数
OUT 参数名称 参数类型 -- 输出参数
INOUT 参数名称 参数类型 -- 也可以输入也可以输出的参数（不推荐）
-- 调用存储过程
CALL 存储过程名称（参数列表）

1.示例

-- 查看该表的存储过程

SELECT * FROM information_schema.ROUTINES

-- 删除存储过程
DROP PROCEDURE 存储过程名称

DROP PROCEDURE pro_empno;
DROP PROCEDURE pro_hello;
DROP PROCEDURE pro_empno_avgsal;

编写一个存储过程，输出 hello kitty

DELIMITER //
CREATE PROCEDURE pro_hello()
BEGIN 
SELECT "hello kitty";
END //
DELIMITER ;
 
CALL pro_hello();

编写一个带参数的存储过程

-- 输入一个员工编号，查询出该员工的薪水

DELIMITER //
CREATE PROCEDURE pro_empno(IN paramA INT)
BEGIN 
SELECT sal FROM emp
WHERE empno = paramA ;
END //
DELIMITER ;
CALL pro_empno(7839);

-- 输入一个员工编号，返回其所在部门的平均工资
-- SELECT ... INTO... 给变量/参数赋值

DELIMITER //
CREATE PROCEDURE pro_empno_avgsal(IN paramA INT,OUT paramB FLOAT)
BEGIN 
SELECT AVG(sal) INTO paramB FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE empno = paramA);
END //
DELIMITER ;

-- @+变量名称声明一个会话变量，作用域是整个会话

CALL pro_empno_avgsal(7788,@sal);
SELECT @sal;

2.控制结构

（1）IF 条件控制

/*
IF 条件判断1 then 执行语句1;
elseif  条件判断2 then 执行语句2;
...
elseif  条件判断N then 执行语句N;
else 执行语句N+1;
END IF;
*/

示例

-- 输入一个部门编号，计算出该部门的平均工资
-- 如果平均工资低于1800，给该部门的员工涨薪300

DELIMITER //
CREATE PROCEDURE pro_sal(IN paramA INT)
BEGIN 
DECLARE avgsal FLOAT(7,2); -- 声明一个变量
 
SELECT AVG(sal) INTO avgsal FROM emp
WHERE deptno = paramA;
 
IF avgsal < 1800 THEN  UPDATE emp SET sal=sal+300 WHERE deptno = paramA;
END IF;
 
END //
DELIMITER ;

-- DECLARE 变量名 数据类型 [default 默认值] 
-- 该变量值只能在存储过程中使用 ，称为存储过程变量

CALL pro_sal(30);
SELECT AVG(sal) FROM emp WHERE deptno = 30;
SELECT * FROM emp WHERE deptno = 30;
 
ROLLBACK;
-- DROP PROCEDURE pro_sal;

（2）循环控制

/*
while 进入循环的条件 DO
循环体;
end while;
*/

示例-- 创建一张表reg，用来存储用户的注册信息
-- 包含2个列 uname作为主键，upass 不能为空
-- 往表中插入100条数据

CREATE TABLE reg
(
uname VARCHAR(20) PRIMARY KEY,
upass VARCHAR(20) NOT NULL
);
 
SELECT * FROM reg
 
DELIMITER //
CREATE PROCEDURE pro_testdate(IN username VARCHAR(20),IN passwd VARCHAR(20), IN n INT)
BEGIN 
 
DECLARE varA INT DEFAULT 0;
 
WHILE varA < n DO
INSERT INTO reg
VALUES(CONCAT(username,varA),CONCAT(passwd,varA));
 
SET varA=varA+1;
END WHILE;
 
END //
DELIMITER ;
 
CALL pro_testdate('test','pass',100);
 
SELECT * FROM reg
 
-- drop table reg;
-- DROP PROCEDURE pro_testdate;
-- SELECT * FROM information_schema.ROUTINES

86期SQL语句板书（非select部分）.sql

四、函数大全

Mysql函数大全_进击的雷神的博客-CSDN博客

数据库中的中英文术语大全_进击的雷神的博客-CSDN博客