一，MYSQL入门

1.数据库相关概念

  1. DB：数据库：存储数据的仓库，保存了一系列有组织的数据。
  2. DBMS：数据库管理系统：数据库是通过DBMS创建和操作的容器。
  3. SQL：结构化查询语言：专门用来与数据库通信的语言。

2.数据库的好处

  1. 1.可以持久化数据到本地
  2. 2.可以实现结构化查询，方便管理

3.数据库存储数据特点

  1. 1.将数据放到表中，表放到库中。
  2. 2.一个数据库有多张表，每个表都有一个名字，用来标识自己。
  3. 表名具有唯一性。
  4. 3.表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中类的设计。
  5. 4.表有列组成，我们也称为字段。所有表都是由一个列或多个列组成的，
  6. 每一列类似Java中的属性。
  7. 5.表中的数据按照行来存储，每一行类似于Java中的对象。

4.mysql的安装与使用

参照mysql安装文档

5.Mysql常用命令

  1. 显示数据库----->show Databases;
  2. 使用数据库----->use 数据库名；
  3. 显示表---->show tables;
  4. 显式指定数据库的表---->show tables from 数据库名；
  5. 查看位于那个数据库---->select database();
  6. 显示表结构--->desc 表名；
  7. 查看数据库版本：--->select version();
  8. 查看数据库版本2:----->Dos:mysql --version;
  9. 查看数据库信息----->show CREATE DATABASE mydb1;
  10. 查看服务器中的数据库，并把mydb1的字符集修改为utf-8----->ALTER DATABASE mydb1character set utf8;
  11. 删除数据库----->drop database mydb1;
  12. 表中增加一栏信息----->alter table student add image blob;
  13. 删除表----->drop table student;
  14. 修改地址----->alter table student modify address varchar(100);
  15. 删除一个属性-----> alter table student drop image;
  16. 修改表名----->rename table student to students;
  17. 查看表的创建细节----->show create table students;
  18. 修改表的字符集为 gbk----->alter table students character set gbk;
  19. 列名name修改为studentname----->alter table students change name studentname varchar(100);

6.mysql语法规范

  1. 1.不区分大小写，建议关键字大写，表名列名小写。
  2. 2.每条命令最好用分号结尾。
  3. 3.每条语句可以缩进，换行。
  4. 4.注释
  5. 单行注释：#注释文字
  6.           -- 注释文字
  7. 多行注释：/* */

二，DQL查询语言

1.基础查询

  1. **语法： select 查询列表 from 表名**
  3. **查询列表：表中的字段，常量，表达式，函数**
  5. **查询的结果是张虚拟的表格**
  7. 1.查询表中的单个字段
  9. select last_name from employee;
  11. 2.查询表中的多个字段
  13. select last_name,salary,email from employee;
  15. 3.查询表中的所有字段
  17. select * from employee;
  19. 4.查询常量值
  21. select 100;
  23. select 'john';
  25. 5.查询表达式
  27. select 100*98;
  29. 6.查询函数
  31. select version();
  33. 7.起别名
  35. select last_name as name from employee;
  37. select last_name name from employee;
  39. 8.去重
  41. 查询员工表中涉及到的所有的部门编号
  43. select distinct department_id from employee;
  45. 9.+的作用
  47. #运算符：两个操作数都为数值型，则做加法运算；
  48. #其中一方为字符型，试图将字符型数值转换成数值型，
  49. #如果转换成功，继续做加法运算；否则，将字符型数值
  50. #转换为0；
  52. 10.使用concat实现连接
  54. #案例：查询员工名和性连接成一个字段
  56. SELECT CONCAT(username,PASSWORD) FROM USER;
  58. #任何数与null做运算结果都为null

2.条件查询

语法：

select 查询列表 from 表名 where 筛选条件

分类：

①按照条件表达式筛选

条件运算符：>,<,=,!=,>=,<=

  3. 查询员工工资>1w2的员工信息
  4. select * from employee where salary >12000;
  5. 查询部门编号！=90号的员工名和部门编号
  6. select name, dep_id from employee where  dep_id 1=90；

②按照逻辑表达式筛选

逻辑运算符：&&,||,!,AND,OR,NOT

  1. 查询工资在一万到两万之见的员工名，工资以及奖金。
  2. select name,salary ,jiangjin where salary between 10000 and 20000;
  3. 查询部门编号不在90-110之间，或者工资高于15000的员工信息。
  4. select  * from employee where department<90||department>110 ||salary >15000;

③模糊查询

like：一般和通配符搭配使用
通配符：
%任意多个字符，包含0个字符
_任意单个字符
BETWEEN AND:包含临界值
IN:判断某个字段的值是否属于in列表中的某一项
IS NULL,IS NOT NULL:=或者！=不能用来判断null
安全等于<=>可以判断null

  1. 查询员工名中包含a的员工信息
  2. select * from emp where name like %a%;
  3. 查询员工名中第三个字符为e第五个字符为a的员工名和工资
  4. select name ,salary from emp where name like %__e_a%;
  5. 员工名中第二个字符为_的员工名
  6. select name from emp where name like %_\_%;
  7. 查询员工编号在100到120之间的所有员工信息
  8. select * from emp where id between 100 and 120;
  9. 查询员工的工种编号是IT_PRIG,AD_PRES,AD_VP中的一个员
  10. 工名和工种编号；
  11. select name , id from emp where id in(IT_PRIG,AD_PRES,AD_VP);
  12. 查询没有奖金的员工名和奖金率
  13. select salary , jjl from emp where salary is Null;
  14. 查询有奖金的员工名和奖金率
  15. select salary ,jjl from emp where salary is not null;

④IF null的使用：
  1. 查询员工号为176的员工的姓名和部门号和年薪
  2. SELECT last_name ,department_id , salary*(1+IFNULL(commission_pct,0))*12 '年薪'
  3. FROM employees WHERE employee_id =176;

3.排序查询

语法：

select 查询列表

from 表

where 筛选条件

order by 排序列表 asc 或desc （升序或者降序，默认为升序）

  1. 查询员工信息，要求工资从高到低排序
  2. select * from emp order by salary desc;
  3. 查询部门编号大于等于90的员工信息，按照入职时间先后排序
  4. select * from emp where dep_id >=90 order by createtime asc;
  5. 按照员工年薪的高低显示员工的信息和年薪
  6. select * ,年薪 from emp  order by salary*(1+if null(jjl,0))*12 as 年薪 desc;
  7. 按姓名长度显示员工的姓名和工资
  8. select name ,salary from emp order by length(name) asc;
  9. 查询员工信息，先按照工资排序，再按照员工编号排序
  10. select * from emp  order by salary asc,id asc;

4.常见函数

功能：类似Java中的方法
分类：单行函数
分组函数

1.单行函数

1.字符函数
  1. 1.length 获取参数值的字节个数
  2. select * from emp order by length(name);
  3. 2.concat 拼接字符串
  4. select concat(last_name,first_name) as 姓名 from emp;
  5. 3.upper，lower 大小写转换函数
  6. 案例：将姓变大写，名字变小写，然后拼接
  7. SELECT 
  8. CONCAT(UPPER(last_name),LOWER(first_name))
  9. FROM employees;
  11. 4.substr,SUBSTRING 截取字符串
  12. SELECT SUBSTR('李莫愁',2);
  13. SELECT SUBSTR('李莫愁',2,3);
  14. 案例：姓名中首字符大写，其他的小写然后用_拼接显示出来
  15. SELECT 
  16.   CONCAT(
  17.     UPPER(SUBSTR(last_name, 1, 1)),
  18.     '_',
  19.     LOWER(SUBSTR(last_name, 2))
  20.   ) output 
  21. FROM
  22.   employees ;
  24. 5.instr:返回字串第一次出现的索引，如果找不到返回0
  25. SELECT INSTR('风急天高猿啸哀','天') AS out_put;
  27. 6.trim :去掉前后空格或前后指定字符
  28. SELECT LENGTH(TRIM('   张三丰   ')) AS out_put;
  29. SELECT TRIM('a' FROM 'aaaa1aa2aaa3aaa') AS out_put;
  31. 7.lpad :用指定字符填满指定长度（左填充）
  32. SELECT LPAD('苍老师',10,'*');
  34. 8.rpad:用指定字符填满指定长度（右填充）
  35. SELECT RPAD('苍老师',10,'*');
  37. 9.replace 替换
  38. SELECT REPLACE('千锋培训机构','千锋','尚硅谷');

2.数学函数
  1. 1.round:四舍五入
  2. SELECT ROUND(1.666);
  3. SELECT ROUND(1.567,2);
  4. 2.ceil 向上取整
  5. SELECT CEIL(1.52);
  6. 3.floor 向下取整
  7. SELECT FLOOR(1.52);
  8. 4.truncate:截断（小数点后保留几位）
  9. SELECT TRUNCATE(1.65,2);
  10. 5.mod:取余
  11. SELECT MOD(10,3);

3.日期函数
  1. 1.now:返回当前系统日期时间
  2. SELECT NOW();
  4. 2.curdate:返回当前系统日期
  5. SELECT CURDATE();
  7. 3.curtime:返回当前时间
  8. SELECT CURTIME();
  10. 4.获取指定部分的年月日时分秒
  11. SELECT YEAR(NOW());
  12. SELECT YEAR(hiredate) FROM employees;
  14. 5.str_to_date将字符通过指定的格式转化成日期
  15. SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
  16. 案例：查询入职时间为1992-4-3的员工信息
  17. SELECT * FROM employees
  18. WHERE hiredate=STR_TO_DATE('2016-3-3','%Y-%c-%d');
  20. 6.date_format 将日期转换成字符
  21. SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS 日期;
  22. 案例：查询有奖金的员工名和入职日期（xx月/xx日 xx年）
  23. SELECT 
  24.   last_name,
  25.   DATE_FORMAT(hiredate, '%c月/%d日 %y') 
  26. FROM
  27.   employees 
  28. WHERE commission_pct IS NOT NULL ;

4.其他函数
  1. SELECT VERSION();
  2. SELECT DATABASE();
  3. SELECT USER();

5.流程控制函数
  1. 1.if:IF else效果
  2. SELECT IF(10>5,'true','false');
  3. 案例：查询如果有奖金就备注有，没有就备注没有。
  4. SELECT 
  5.   last_name,
  6.   commission_pct,
  7.   IF(
  8.     commission_pct IS NULL,
  9.     '没奖金',
  10.     '有奖金'
  11.   ) AS 备注 
  12. FROM
  13.   employees ;
  15. 2.case函数
  16. 1)switch-CASE
  17. 语法:
  18. CASE 要判断的字段或者表达式
  19. WHEN 常量1 THEN 要显示的值1或者语句1
  20. WHEN 常量2 THEN 要显示的值2或者语句2
  21. ...
  22. ELSE 要显示的值n或者语句n；
  23. 案例：查询员工的工资，要求
  24. 部门号==30，显示的工资为1.1倍，
  25. 部门号==40，显示的工资为1.2倍，
  26. 部门号==50，显示的工资为1.3倍，
  27. 其他部门，显示原有工资。
  28. SELECT 
  29.   salary AS 原始工资,
  30.   department_id ,
  31.   CASE
  32.     department_id 
  33.     WHEN 30 
  34.     THEN salary * 1.1 
  35.     WHEN 40 
  36.     THEN salary * 1.2 
  37.     WHEN 50 
  38.     THEN salary * 1.3 
  39.     ELSE salary 
  40.   END AS 新工资 
  41. FROM
  42.   employees ;
  43. 2)CASE 使用2：
  44. 语法：
  45. CASE 
  46. WHEN 条件1 THEN 要显示的值1或语句1
  47. WHEN 条件2 THEN 要显示的值2或语句2
  48. ...
  49. ELSE 要显示的值n或语句n
  50. END
  51. 案例：查询员工的工资情况
  52. 如果>2w，显示A
  53. 如果>1.5w，显示B
  54. 如果>1w，显示C
  55. 否则，显示D
  56. SELECT 
  57.   salary,
  58.   CASE
  59.     WHEN salary > 20000 
  60.     THEN 'A' 
  61.     WHEN salary > 15000 
  62.     THEN 'B' 
  63.     WHEN salary > 10000 
  64.     THEN 'C' 
  65.     ELSE 'D' 
  66.   END AS 工资等级 FROM employees ;

2.分组函数

功能：用作统计使用

  1. 1.sum :求和
  2. SELECT SUM(salary) FROM employees;
  4. 2.avg：平均值
  5. SELECT AVG(salary) FROM employees;
  7. 3.max：最大值
  8. SELECT MAX(salary) FROM employees;
  10. 4.min：最小值
  11. SELECT MIN(salary) FROM employees;
  13. 5.count：计算个数
  14. SELECT COUNT(salary) FROM employees;
  16. 总结
  17. ①.sum,avg一般用于处理数值类型
  18. ②.max，min，count用来处理任何类型
  19. ③.以上分组函数都忽略null值
  20. ④.可以和distinct搭配
  21. SELECT SUM(DISTINCT salary) 纯净,SUM(salary) FROM employees;
  22. 6.count的详细介绍
  23. ①select COUNT(*) FROM employees;
  24. ②select COUNT(1) FROM employees;
  25. ③和分组函数一同查询的字段要求是group by后的字段。

5.分组查询

GROUP BY 和分组函数对应
分组查询中分组条件分为两类

数据源 位置 关键字
分组前筛选 原始表 GROUP BY 子句的前面 WHERE
分组后筛选 分组后的结果集 GROUP BY 子句的后面 HAVING

分组函数做条件肯定是放在having子句中。
group BY 子句支持单个字段分组，多个字段分组
（多个字段之间用逗号隔开没有顺序要求），表达式或函数。
也可以添加排序，放在整个分组查询的最后。

  1. 案例：查询每个工种的最高工资
  2. SELECT 
  3.   MAX(salary),
  4.   job_id 
  5. FROM
  6.   employees 
  7. GROUP BY job_id 
  8. ORDER BY MAX(salary) ASC ;
  9. 案例：查询邮箱中包含a字符的，每个部门的平均工资
  10. SELECT 
  11.   AVG(salary),
  12.   department_id 
  13. FROM
  14.   employees 
  15. WHERE email LIKE '%a%' 
  16. GROUP BY department_id ;
  17. #select Avg(salary),dep_id from employee where email like %a% group by dep_id ;
  18. 案例：查询有奖金的每个领导手下员工的最高工资
  19. SELECT 
  20.   MAX(salary),
  21.   manager_id 
  22. FROM
  23.   employees 
  24. WHERE commission_pct IS NOT NULL 
  25. GROUP BY manager_id ;
  26. #select max(salary) ,manage_id from employees where commission_pct is not null group by manager_id;
  27. 案例：哪个部门的员工个数大于二？
  28. SELECT 
  29.   COUNT(*),
  30.   department_id 
  31. FROM
  32.   employees 
  33. GROUP BY department_id 
  34. HAVING COUNT(*) > 2 ;
  35. #select dep_id from emp group by dep_id having count(*)>2;
  36. 案例：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
  37. SELECT 
  38.   MAX(salary),
  39.   job_id 
  40. FROM
  41.   employees 
  42. WHERE commission_pct IS NOT NULL 
  43. GROUP BY job_id 
  44. HAVING MAX(salary) > 12000 ;
  45. #select job_id ,max(salary) from emp where commission_pct IS NOT NULL group by job_id having max(salary)>12000;
  46. 案例：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个？
  47. SELECT manager_id ,MIN(salary)
  48. FROM employees
  49. WHERE manager_id>102
  50. GROUP BY manager_id
  51. HAVING MIN(salary)>5000;
  52. #select manager_id from emp where manager_id>102 group by manager_id having min(salary)>5000;
  53. #按照员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些？
  54. SELECT COUNT(*) AS c
  55. FROM employees
  56. GROUP BY LENGTH(last_name) 
  57. HAVING c>5;
  58. # select count(*) from emp group by length(name) having count(*)>5;
  59. #查询每个部门每个工种的员工的平均工资
  60. SELECT AVG(salary),job_id
  61. FROM employees
  62. GROUP BY department_id,job_id;
  63. #select avg(salary) from emp group by dep_id,job_id;
  64. #查询每个部门每个工种的员工的平均工资并且按照平均工资的高低显示
  65. SELECT AVG(salary),job_id
  66. FROM employees
  67. GROUP BY department_id,job_id
  68. ORDER BY AVG(salary) ASC;
  69. #select avg(salary) from emp group by dep_id,job_id order by avg(salary) asc;

6.连接查询

又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。
笛卡尔乘积现象：表1有m行，表2有n行，结果：mn行
发生原因：没有有效的连接条件**

分类

①按年代分类
sql92:仅仅支持内连接
sql99：不支持全外连接

②按功能分类

内连接 外连接 交叉连接
等值连接 左外连接
非等值连接 右外连接
自连接 全外连接

1.等值连接

①多表等值连接的结果为多表的交集部分
②n表连接，至少需要n-1个连接条件
③多表的顺序没有要求
④一般需要为表起别名
⑤可以搭配前面介绍的所有子句使用，比如排序，分组，筛选

  1. #案例一：查询女优名对应的男优名
  2. SELECT 
  3.   NAME,
  4.   boyName 
  5. FROM
  6.   beauty,
  7.   boys 
  8. WHERE beauty.boyfriend_id = boys.`id` ;
  9. #select name, boyname from girl ,boy where girl.boyfriend_id=boy.id;
  10. #案例：查询员工名和对应的部门名
  11. SELECT 
  12.   last_name,
  13.   department_name 
  14. FROM
  15.   employees,
  16.   departments 
  17. WHERE employees.`department_id` = departments.`department_id` ;
  18. #select name ,dep_name from emp e,dep d where e.dep.id= d.id;
  19. #案例：查询员工名，工种号，工种名。
  20. SELECT 
  21.   last_name,
  22.   emp.`job_id`,
  23.   job_title 
  24. FROM
  25.   employees emp,
  26.   jobs job 
  27. WHERE emp.`job_id` = job.`job_id` ;
  28. #select name , e.job_id,job_title from emp e,job j where e.job_id=j.id;
  29. #案例：查询有奖金的员工名和部门名
  30. SELECT 
  31.   last_name,
  32.   department_name 
  33. FROM
  34.   employees emp,
  35.   departments dep 
  36. WHERE commission_pct IS NOT NULL && emp.`department_id` = dep.`department_id` ;
  37. #select name ,dep_name from emp e ,dep d where e.dep_id =d.id &&e.salary_pct is not null;
  38. #案例：查询城市名第二个字符为o的部门
  39. SELECT 
  40.   department_name 
  41. FROM
  42.   locations l,
  43.   departments d 
  44. WHERE l.`location_id` = d.`location_id` 
  45.   AND l.`city` LIKE '_o%' ;
  46. #select dep_name from location l , dep d where l.city like %_o% && l.id =d.location_id;
  47. #案例：查询每个城市的部门个数
  48. SELECT 
  49.   COUNT(*),
  50.   city 
  51. FROM
  52.   locations l,
  53.   departments d 
  54. WHERE l.`location_id` = d.`location_id` 
  55. GROUP BY l.`city` ;
  56. #select count(*),city from loca l,dep d where l.loc_id=d.loc_id group by count(*) asc;
  57. #案例：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
  58. SELECT 
  59.   d.`department_name`,
  60.   d.manager_id,
  61.   MIN(salary) 
  62. FROM
  63.   employees e,
  64.   departments d 
  65. WHERE e.`department_id` = d.`department_id` 
  66.   AND e.`commission_pct` IS NOT NULL 
  67. GROUP BY d.`department_id`,
  68.   d.`department_name` ;
  69. #select dep_name ,d.manager_id ,min(salary) from emp e ,dep d where e.`department_id` = d.`department_id` AND e.`commission_pct` IS NOT NULL GROUP BY d.`department_id`,d.`department_name` ;
  70. #案例：查询每个工种的工种名和员工的个数，并且按照员工个数降序排序
  71. SELECT 
  72.   j.job_title,
  73.   COUNT(*) 
  74. FROM
  75.   jobs j,
  76.   employees e 
  77. WHERE j.`job_id` = e.`job_id` 
  78. GROUP BY e.`job_id`,
  79.   j.`job_title` 
  80. ORDER BY COUNT(*) DESC ;
  82. #案例：查询员工名，部门名和所在城市
  83. SELECT 
  84.   last_name,
  85.   department_name,
  86.   city 
  87. FROM
  88.   employees e,
  89.   departments d,
  90.   locations l 
  91. WHERE e.`department_id` = d.`department_id` 
  92.   AND d.`location_id` = l.`location_id` ;

2.非等值连接
  1. #案例：查询员工的工资和工资级别
  2. SELECT DISTINCT 
  3.   salary,
  4.   grade_level 
  5. FROM
  6.   employees e,
  7.   job_grades j 
  8. WHERE e.salary >= j.lowest_sal && e.salary <= j.highest_sal 
  9. ORDER BY salary ASC ;

3.自连接
  1. #案例：查询员工名和上级的名称
  2. SELECT 
  3.   e.last_name,
  4.   m.last_name 
  5. FROM
  6.   employees e,
  7.   employees m 
  8. WHERE e.manager_id = m.employee_id ;

4.内连接

INNER 可以省略

  1. #查询员工名，部门名
  2. SELECT 
  3.   last_name,
  4.   department_name 
  5. FROM
  6.   employees e 
  7.   INNER JOIN departments d 
  8.     ON e.department_id = d.department_id ;
  9. #查询名字中包含e的员工名和工种名
  10. SELECT 
  11.   last_name,
  12.   job_title 
  13. FROM
  14.   employees e 
  15.   INNER JOIN jobs j 
  16.     ON e.job_id = j.job_id 
  17. WHERE last_name LIKE '%e%' ;
  19. #查询部门个数>3的城市名和部门个数
  20. SELECT 
  21.   city,
  22.   COUNT(*) 
  23. FROM
  24.   departments d 
  25.   INNER JOIN locations l 
  26.     ON d.`location_id` = l.`location_id` 
  27. GROUP BY city
  28. HAVING COUNT(*) > 3 ;
  30. #查询哪个部门的部门员工个数>3的部门名和员工个数，并按照个数降序排序
  31. SELECT 
  32.   department_name,
  33.   COUNT(*) 
  34. FROM
  35.   employees e 
  36.   INNER JOIN departments d 
  37.     ON e.`department_id` = d.`department_id` 
  38. GROUP BY e.department_id 
  39. HAVING COUNT(*) > 3 
  40. ORDER BY COUNT(*) DESC ;
  42. #查询员工名，部门名，工种名，并按照部门名降序排序
  43. SELECT 
  44.   last_name,
  45.   department_name,
  46.   job_title 
  47. FROM
  48.   employees e 
  49.   INNER JOIN departments d 
  50.     ON e.`department_id` = d.`department_id` 
  51.   INNER JOIN jobs j 
  52.     ON e.`job_id` = j.`job_id` 
  53. ORDER BY department_name DESC ;
  54. #查询员工工资级别
  55. SELECT 
  56.   grade_level,
  57.   salary
  58. FROM
  59.   job_grades j 
  60.   INNER JOIN employees e 
  61.     ON e.`salary` BETWEEN j.`lowest_sal` 
  62.     AND j.`highest_sal` ;
  63. #查询每个工资级别的个数，并且降序排序
  64. SELECT grade_level,COUNT(*)
  65. FROM employees e
  66. INNER JOIN  job_grades j
  67. ON e.`salary` BETWEEN j.`lowest_sal` 
  68.     AND j.`highest_sal` 
  69. GROUP BY grade_level
  70. ORDER BY COUNT(*) DESC;
  71. #查询员工的名字和上级的名字
  72. SELECT e1.last_name, e2.last_name
  73. FROM employees e1
  74. INNER JOIN employees e2
  75. ON e1.`employee_id`=e2.`manager_id`;

5.左外连接

语法：
SELECT 查询列表
FROM 表1 【连接类型】
JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 筛选条件
ORDER BY 排序条件
连接类型：
内连接：inner
左外连接：left
右外连接：right
全外连接：full
交叉连接：cross
外连接
用于查询一个表中有，另一个表中没有的数据
左外连接，left左边是主表
右外连接，right右边是主表
Mysql不支持全外连接

  1. #没有男朋友的女生
  2. SELECT 
  3. g.`name`,b.`boyName`
  4. FROM beauty g
  5. LEFT JOIN boys b
  6. ON g.`boyfriend_id`=b.`id`
  7. WHERE b.`boyName` IS NULL;

6.交叉连接

笛卡尔乘积

7.子查询

出现在其它语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：

①按照子查询出现的位置：

select后面 from后面 where或having后面 exists后面
仅仅支持标量子查询 支持表子查询 标量子查询，列子查询 表子查询

②按照结果集的行列数不同：

标量子查询 列子查询 行子查询 表子查询
结果只有一行一列 结果一列多行 一行多列 多行多列

1）where或having后面

特点：
子查询一般放在小括号内
子查询一般放在条件的右边
标量子查询，一般搭配着单行操作符
列子查询：一般搭配多行操作符使用

  1. 1.标量子查询
  2. #谁的工资比Abel高
  3. SELECT 
  4.   last_name 
  5. FROM
  6.   employees 
  7. WHERE salary > 
  8.   (SELECT 
  9.     salary 
  10.   FROM
  11.     employees 
  12.   WHERE last_name = 'Abel') ;
  13. #返回job_id于141号员工相同，salary比143号员工多的员工 姓名，job_id和工资
  14. SELECT 
  15.   last_name,
  16.   job_id,
  17.   salary 
  18. FROM
  19.   employees 
  20. WHERE job_id = 
  21.   (SELECT 
  22.     job_id 
  23.   FROM
  24.     employees 
  25.   WHERE employee_id = 141) 
  26.   AND salary > 
  27.   (SELECT 
  28.     salary 
  29.   FROM
  30.     employees 
  31.   WHERE employee_id = 143)
  32. #返回公司工资工资最少的员工的姓名，job_id,salary
  33. SELECT 
  34.   last_name,
  35.   job_id,
  36.   salary 
  37. FROM
  38.   employees 
  39. WHERE salary = 
  40.   (SELECT 
  41.     MIN(salary) 
  42.   FROM
  43.     employees);
  44. #查询最低工资大于50号部门最低工资的部门id和其最低工资
  45. SELECT 
  46.   department_id,
  47.   MIN(salary) 
  48. FROM
  49.   employees 
  50. GROUP BY department_id 
  51. HAVING MIN(salary) > 
  52.   (SELECT 
  53.     MIN(salary) 
  54.   FROM
  55.     employees 
  56.   WHERE department_id = 50) ;
  57. 2.列子查询
  58. 多行操作符：
  59. IN / NOT in：等于列表中的任意一个
  60. ANY / SOME ：和子查询返回的某一个值比较
  61. ALL ：和子查询返回的所有值比较
  62. #返回location_id是1400或者1700的部门中的所有员工姓名
  63. SELECT 
  64.   last_name 
  65. FROM
  66.   employees 
  67. WHERE department_id IN
  68.   (SELECT DISTINCT
  69.     department_id 
  70.   FROM
  71.     departments 
  72.   WHERE location_id IN (1400, 1700)) ;
  73. #返回其他工种中比job_id为IT_PROG部门任意工资低的员工
  74. #工号，姓名，job_id以及salary
  75. SELECT 
  76.   employee_id,
  77.   last_name,
  78.   job_id,
  79.   salary 
  80. FROM
  81.   employees 
  82. WHERE salary < 
  83.   (SELECT 
  84.     MAX(salary) 
  85.   FROM
  86.     employees 
  87.   WHERE job_id = 'IT_PROG') 
  88.   AND job_id !='IT_PROG';
  89. #返回其他工种中比job_id为IT_PROG部门所有工资低的员工
  90. #工号，姓名，job_id以及salary
  91. SELECT 
  92.   employee_id,
  93.   last_name,
  94.   job_id,
  95.   salary 
  96. FROM
  97.   employees 
  98. WHERE salary < 
  99.   (SELECT 
  100.     MIN(salary) 
  101.   FROM
  102.     employees 
  103.   WHERE job_id = 'IT_PROG') 
  104.   AND job_id !='IT_PROG';
  105. *********************************
  106. 3.行子查询
  107. #查询员工编号最小并且工资最高的员工信息
  108. SELECT 
  109.   * 
  110. FROM
  111.   employees 
  112. WHERE employee_id = 
  113.   (SELECT 
  114.     MIN(employee_id) 
  115.   FROM
  116.     employees) 
  117.   AND salary = 
  118.   (SELECT 
  119.     MAX(salary) 
  120.   FROM
  121.     employees)

2）SELECT 后面
  1. #查询每个部门的员工个数
  2. SELECT 
  3.   d.*,
  4.   (SELECT 
  5.     COUNT(*) 
  6.   FROM
  7.     employees e 
  8.   WHERE e.department_id = d.department_id) 
  9. FROM
  10.   departments d ;
  11. #查询员工号等于102的部门名
  12. SELECT 
  13.   department_name 
  14. FROM
  15.   departments 
  16. WHERE department_id = 
  17.   (SELECT 
  18.     department_id 
  19.   FROM
  20.     employees 
  21.   WHERE employee_id = 102) ;

3）FROM 后面
  2. #查询每个部门平均工资的工资等级
  3. SELECT 
  4.   grade_level ,aa.department_id
  5. FROM
  6.   (SELECT 
  7.     AVG(salary) ag,
  8.     department_id 
  9.   FROM
  10.     employees 
  11.   GROUP BY department_id) aa 
  12.   INNER JOIN job_grades j 
  13.     ON aa.ag BETWEEN lowest_sal 
  14.     AND highest_sal ;

4）exists后面（相关子查询）
  1. #查询有员工的部门名
  2. SELECT department_name FROM departments d
  3. WHERE  EXISTS(
  4. SELECT * FROM employees  e WHERE d.department_id=e.department_id
  5. );
  6. #查询没有女朋友的男生信息
  7. SELECT bo.* FROM boys bo WHERE
  8. bo.`id` NOT IN(SELECT boyfriend_id FROM beauty);

5）子查询经典案例祥讲
  1. 1.查询工资最低的员工信息：last_name,salary
  2. SELECT last_name,salary FROM employees
  3. WHERE  salary=(SELECT MIN(salary) FROM employees);
  4. 2.查询平均工资最低的部门信息
  5. SELECT * FROM departments WHERE department_id=
  6. (SELECT department_id  FROM employees GROUP BY department_id  ORDER BY AVG(salary)
  7. LIMIT 1)
  8. 3.查询平均工资最低的部门信息和该部门的平均工资
  9. SELECT d.*,a1.ag FROM departments  d JOIN 
  10. (SELECT AVG(salary) ag,department_id  FROM employees GROUP BY department_id  ORDER BY AVG(salary)
  11. LIMIT 1) a1
  12. ON d.department_id=a1.department_id
  13. 4.查询平均工资最高的job信息
  14. SELECT j.* FROM jobs j WHERE j.job_id=
  15. (SELECT job_id FROM employees
  16. GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1)
  17. 5.查询平均工资高于公司平均工资的部门有哪些
  18. SELECT department_id FROM 
  19. (SELECT department_id ,AVG(salary) AS avg1 FROM employees GROUP BY  department_id) e1
  20. WHERE e1.avg1>(SELECT AVG(salary)  AS avg2 FROM employees) 
  21. 6.查询出公司中所有manager的详细信息
  22. SELECT * FROM employees
  23. WHERE employee_id IN(SELECT DISTINCT manager_id FROM employees);
  24. 7.各个部门中，最高工资中最低的那个部门的最低工资是多少
  25. SELECT MIN(salary) FROM employees GROUP BY department_id
  26. HAVING department_id=(
  27. SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary)
  28. LIMIT 1)
  29. 8.查询平均工资最高的部门的manager的详细信息：last_name,department_id,email,salary
  30. SELECT last_name,department_id,email,salary FROM employees WHERE employee_id=(
  31. SELECT manager_id FROM departments WHERE department_id=(
  32. SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary)
  33. DESC LIMIT 1))

8.分页查询

**语法：limit(currentPage-1)size,size

  1. #查询前五条员工信息
  2. SELECT * FROM employees LIMIT 0,5;
  3. #查询第11-25条员工信息
  4. SELECT * FROM employees LIMIT 10,15;
  5. #查询有奖金的员工，并且工资最高的前十名显示出来
  6. SELECT * FROM employees
  7. WHERE commission_pct IS NOT NULL
  8. ORDER BY salary DESC 
  9. LIMIT 0 ,10;

9.联合查询

要查询的结果来自于多个表，且多个表没有直接的连接关系，单查询的信息一致时
特点：
1.要求多条查询语句的查询列数是一致的
2.要求多条查询语句的查询的每一列的类型和顺序最好一致
3.union关键字默认去重，如果使用union all 可以不去除重复项

  1. 案例：查询员工部门编号大于90或邮箱包含a的员工信息
  2. SELECT * FROM employees WHERE department_id>90
  3. UNION
  4. SELECT * FROM employees WHERE email LIKE '%a%';

三，DML数据操作语言

插入insert

  1. 一：插入语句
  2. #插入beauty一行数据
  3. INSERT INTO beauty(NAME,sex,borndate,phone,photo,boyfriend_id)
  4. VALUES('波多野吉依','女','1998-11-11','13342969497',
  5. NULL,10)
  6. #可以为null的列如何不插入值
  7. 直接写null，或列名少写一列
  8. INSERT INTO beauty(NAME,sex,borndate,phone,photo,boyfriend_id)
  9. VALUES('小泽玛利亚','女','1999-11-11','13342456497',
  10. NULL,11)
  11. INSERT INTO beauty VALUES(15,'马蓉','女','1989-11-11','13342456123',
  12. NULL,12);
  13. INSERT INTO beauty SET id=16,NAME='刘亦菲', sex='女',borndate='1989-10-01',
  14. phone='15945231056',boyfriend_id=16;
  15. #insert 嵌套子查询，将一个表的数据插入另一张表
  16. INSERT INTO beauty (NAME,sex,borndate,phone,boyfriend_id)
  17. SELECT '妲己','女','1111-11-11','13146587954',0;

修改update

  1. 二，修改
  2.  UPDATE beauty SET phone='110' WHERE id=16;
  3. 多表修改：sql99
  4. UPDATE 表1 别名
  5. INNER|LEFT|RIGHT JOIN 表2 别名
  6. ON 连接条件
  7. SET 列=值
  8. WHERE 筛选条件
  9. #修改张无忌的女朋友手机号为114
  10. UPDATE beauty g
  11. INNER JOIN boys b
  12. ON g.boyfriend_id=b.id
  13. SET g.phone='114'
  14. WHERE b.boyName='张无忌';
  15. #修改没有男朋友的女生的男朋友编号都为4号
  16. UPDATE beauty g
  17. LEFT JOIN  boys b
  18. ON g.`boyfriend_id`=b.id
  19. SET g.`boyfriend_id`=4
  20. WHERE b.id=NULL;

删除delete

  1. 三，删除
  2. DELETE 和 TRUNCATE 的区别：
  3. 1.delete可以加where条件，truncate不行
  4. 2.truncate删除效率高
  5. 3.加入要删除的表中有自增列，
  6. 用delete删除整个表后在插入数据，从断点处开始插入
  7. 用truncate删除后在插入数据，从1开始。
  8. 4.truncate删除没有返回值，delete有返回值
  9. 5.truncate删除不能回滚，delete删除可以回滚
  10. DELETE FROM beauty WHERE id=17;
  11. 语法：truncate TABLE 表名;
  12. #删除张无忌的女朋友的信息
  13. DELETE g FROM beauty g
  14. INNER JOIN boys b
  15. ON g.boyfriend_id=b.id
  16. WHERE b.id=1;
  17. #删除黄晓明以及他女朋友的信息
  18. DELETE b,g FROM beauty g
  19. INNER JOIN boys b
  20. ON b.`id`=g.`boyfriend_id`
  21. WHERE b.`boyName`='黄晓明';
  22. 多表删除 :TRUNCATE
  23. TRUNCATE TABLE boys

四，DDL数据定义语言

1.库和表的管理

  1. 一，库的管理
  2. 创建 CREATE
  3. CREATE DATABASE IF NOT EXISTS mydb1 ;
  4. 修改 ALTER
  5. 1.更改字符集
  6. ALTER DATABASE mydb1 CHARACTER SET utf8;
  7. 删除 DROP
  8. DROP DATABASE IF EXISTS school;
  9. 二，表的管理
  10. 创建 CREATE
  11. CREATE TABLE book(
  12. id INT PRIMARY KEY,
  13. b_name VARCHAR(30),
  14. price DOUBLE,
  15. author_id INT ,
  16. publishDate DATE
  17. );
  18. DESC book ;
  19. CREATE TABLE author(
  20. id INT PRIMARY KEY ,
  21. au_name VARCHAR(20),
  22. nation VARCHAR(10)
  23. );
  24. DESC author;
  25. 修改 ALTER
  26. 1.修改列名
  27. ALTER TABLE book CHANGE COLUMN publishDate pub_date DATETIME;
  28. 2.修改列的类型或约束
  29. ALTER TABLE book MODIFY COLUMN pub_date DATE;
  30. 3.添加新列
  31. ALTER TABLE author ADD COLUMN annual DOUBLE;
  32. 4.删除新列
  33. ALTER TABLE author DROP COLUMN annual;
  34. 5.修改表名
  35. ALTER TABLE author RENAME TO book_author;
  36. 删除  DROP
  37. DROP TABLE IF EXISTS my_employee;
  38. SHOW TABLES;
  39. 复制
  40. 1.仅仅复制表的结构
  41. CREATE TABLE copy LIKE book_author;
  42. 2.复制表的结构加数据
  43. CREATE TABLE copy2
  44. SELECT * FROM book_author;
  45. 3.复制部分结构
  46. CREATE TABLE copy3 
  47. SELECT id,au_name
  48. FROM book_author
  49. WHERE id=0;

2.数据类型

数值型
1.整型

TINYINT SMALLINT MEDIUMINT INT/INTEGER BIGINT
1 2 3 4 8 
  1. 如何设置无符号和有符号(默认有符号)
  2. DROP TABLE tab_int;
  3. CREATE TABLE tab_int(t1 INT,t2 INT UNSIGNED);
  4. INSERT INTO tab_int(t1,t2) VALUES(-1,1);
  5. DESC tab_int;

1）如果插入的数值超出了整形的范围，会报out of range异常，并且插入
临界值。
2）如果不设置长度，会有默认的长度。
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配
zerofill使用。
2.小数
①定点数
dec（M,D）
②浮点数
float（4） ，double（8）
M，D的意思：M指定一共多少位，D指定小数几位，超出会四舍五入。
MD都可以省略，
如果是dec，则M默认为10，D默认为0
如果是浮点数，则会根据插入数值的精度改变精度
定点型精度相对较高。
3.字符型
①较短的文本
CHAR(M)默认为1,VARCHAR(M)
M:字符数
char：固定长度字符，比较耗费空间，但是效率高。
varchar：可变长度字符

  1. ENUM 枚举类
  2. CREATE TABLE tab_char(
  3.    t1 ENUM('a','c','b')
  4. );
  5. SET 集合
  6. CREATE TABLE tab_set(
  7. s1 SET('a','b','c','d')
  8. );
  9. INSERT INTO tab_set(s1) VALUES('a,b');

BINARY:保存较短的二进制。
②较长的文本
text（文本）,BLOB(较大的二进制)
4.日期型
DATE:日期
DATETIME:日期加时间，8字节
timestamp：跟时区有关系，建议使用，4字节
time：时间
year：年

  1. CREATE TABLE tab_date(
  2. t1 DATETIME,
  3. t2 TIMESTAMP
  4. );
  5. INSERT INTO tab_date(t1,t2)
  6. VALUES(NOW(),NOW());
  7. SELECT * FROM tab_date;
  8. SET time_zone='+9:00';
  9. #设置时区为东9区

3.常见约束

含义：一种限制，用于限制表中的数据，保证数据的一致性。

  1. NOT NULL
  2. DEFAULT
  3. PRIMARY KEY 唯一，且不为空
  4. UNIQUE 唯一，可以为空
  5. CHECK Mysql不支持
  6. FOREIGN KEY 外键约束，用于限制两个表的关系，
    用于保证该字段的值必须来自于主表的关联列的值。
    约束的分类：
    列级约束：除外键约束
    表级约束：除了非空，默认。
    CREATE TABLE 表名(
    字段1 字段类型 列级约束,
    字段2 字段类型 列级约束,
    表级约束
    ); 
  1. #创建表时添加列级约束
  2. DROP TABLE tab_test;
  3. CREATE TABLE tab_test(
  4. id INT PRIMARY KEY,
  5. stu_name VARCHAR(20) NOT NULL,
  6. gender CHAR DEFAULT '男',
  7. seat_id INT UNIQUE, 
  8. major_id INT REFERENCES tab_major(id) 
  9. );
  10. CREATE TABLE tab_major(
  11. id INT PRIMARY KEY ,
  12. major_name VARCHAR(20) NOT NULL
  13. );
  14. DESC tab_test;
  15. SHOW INDEX FROM tab_test;
  16. #查看索引信息
  17. #添加表级约束
  18. CREATE TABLE tab_test(
  19. id INT PRIMARY KEY AUTO_INCREMENT,
  20. stu_name VARCHAR(20) NOT NULL,
  21. gender CHAR DEFAULT '男',
  22. seat_id INT UNIQUE, 
  23. major_id INT ,
  24. CONSTRAINT m_id FOREIGN KEY(major_id) REFERENCES tab_major(id) 
  25. );
  26. CONSTRAINT m_id 可以省略

面试题：主键约束和唯一约束的区别：
都可以保证唯一性，
主键不能为空 ，unique 能为空，但是只能有一个null。
主键只能有1个，unique可以有多个。
都允许两个列组合成一个约束。
面试题：外键：
要求在从表设置外键关系
从表的外键列类型和主表的关联列类型一致，名称无要求
要求主表的关联列必须是主键或者唯一键
插入数据应该先插入主表再插入从表
删除数据应该先删除从表，在删除主表
二，修改表时添加约束

  1. CREATE TABLE tab_test2(
  2. id INT ,
  3. stu_name VARCHAR(20) ,
  4. gender CHAR ,
  5. seat_id INT , 
  6. major_id INT  
  7. );
  8. ALTER TABLE tab_test2 MODIFY COLUMN stu_name  VARCHAR(20) NOT NULL ;
  9. ALTER TABLE tab_test2 MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
  10. #添加外键
  11. ALTER TABLE tab_test2 ADD FOREIGN KEY(major_id) REFERENCES tab_major(id);

4.标识列

自增长列 AUTO_INCREMENT
特点：
1.表示必须和一个key搭配
2.一个表最多一个标识列
3.标识列类型只能是数值型
4.标识列可以通过set auto_increment_increment=3;设置步长

  1. CREATE tab_auto(
  2. id INT PRIMARY KEY AUTO_INCREMENT,
  3. NAME VARCHAR(20) NOT NULL
  4. );

五，TCL语言：事务控制语言

事务：一个或一组sql语句组成的执行单元，

要么全部执行,要么都不执行。
存储引擎:在MySQL中的数据用各种不同的技术存储在文件中。
通过show ENGINES;来查看mysql支持的存储引擎。
innodb引擎支持事务。
事务的ACID属性：
1.原子性:事务是一个不可分割的工作单位，要么都发生，要么都不发生。
2.一致性：事务必须使数据库从一个一致性状态变为另一个一致性状态。
3.隔离性：一个事务的执行不能被另一个事务干扰。
4.持久性：事务一旦被提交，对数据库事务的改变就是永久性的。

DELETE 和 TRUNCATE 在事务中的区别：

  1. 演示delete
  2. SET autocommit=0;
  3. START TRANSACTION;
  4. DELETE FROM tab_teacher;
  5. ROLLBACK;
  6. 演示 TRUNCATE
  7. SET autocommit=0;
  8. START TRANSACTION;
  9. TRUNCATE  TABLE tab_teacher;
  10. ROLLBACK;
  11. DELETE 是直接删除表中数据，truncate是江表删除，创建一张与原来一样的空表。

六，视图

含义：虚拟表，和普通表格一样使用
通过表动态生成的数据

1.创建视图

语法：
CREATE VIEW 视图名
AS
查询语句 ;

  1. # 案例：查询姓名中包含a字符的员工名，部门名和工种信息
  2. create view view1 as
  3.     select e.last_name,d.department_name ,j.job_title from employees e
  4. inner join departments d on e.department_id = d.department_id
  5.     inner join jobs j on e.job_id = j.job_id
  6. where e.last_name like '%a%';
  7. select * from view1;
  8. # 案例：查询各个部门的平均工资级别
  9. create view view2 as
  10. select j.grade_level ,aa.department_id
  11.     from job_grades j
  12. inner join (select avg(salary) avg_s,department_id from employees  group by department_id) aa
  13.     on aa.avg_s between j.lowest_sal and j.highest_sal;
  14. select * from view2;
  15. # 案例：查询平均工资最低的部门信息
  16. create view view3 as
  17. select avg(salary) avg_s ,department_id
  18. from employees
  19. group by department_id
  20. order by avg_s asc
  21. limit 1;
  22. select * from view3;

2.视图修改

①create OR REPLACE VIEW 视图名 AS 查询语句;
②alter VIEW 视图名 AS 查询语句;

3.删除视图

DROP VIEW v1,v2;

4.查看视图

DESC v1;

  1. #创建视图emp_v1，要求查询电话号码以011开头的员工姓名和工资，邮箱
  2. CREATE VIEW emp_v1 AS
  3. SELECT last_name ,salary,email FROM employees WHERE
  4. phone_number LIKE '%011';
  5. #创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息
  6. CREATE VIEW v4 AS
  7. SELECT department_id FROM employees GROUP BY department_id
  8. HAVING MAX(salary)> 12000;
  9. CREATE VIEW emp_v2 AS
  10. SELECT * FROM departments WHERE department_id IN(SELECT * FROM v4);

5.视图的更新

视图的可更新性和视图中查询的定义有关，以下类型的视图是不能更新的。
1.包含以下关键字的sql语句：分组函数，distinct，group by，having，union
2.常量视图
3.select中包含子查询的
4.join
5.from 一个不能更新的视图
6.where子句的子查询引用了from子句的表

6.视图和表的对比：

创建语法的关键字 是否实际占用物理空间 使用
视图 CREATE VIEW 只是保存了sql逻辑 增删改查，一般不能增删改
表 CREATE TABLE 占用 增删改查

七，变量

系统变量 ：变量由系统提供，不是用户自定义，属于服务器层面。
查看系统所有变量：show GLOBAL VARIABLES;
查看满足条件的部分系统变量： SHOW GLOBAL VARIABLES LIKE ‘%char%’;
查看指定的某个系统变量的值： SELECT @@global.autocommit;
为某个系统变量赋值：set @@global.系统变量名=值;
全局变量:GLOBAL
作用域：服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话有效，但不能
跨重启。
会话变量:SESSION
作用域：针对当前的会话有效。
用户自定义变量
用户变量
声明： SET/SELECT @用户变量名 :=值;
赋值：通过 SELECT 字段 INTO 变量名;或 SET/SELECT @用户变量名 :=值;
使用：select @用户变量名;
应用在任何地方。
作用域：针对当前会话和连接有效。
局部变量
作用域：作用在定义它的begin END 块中。
声明： DECLARE 变量名 类型 （default 值）;
赋值：通过 SELECT 字段 INTO 变量名;或 SET/SELECT @变量名 :=值;
使用：select @变量名;
只能放在begin END 中的第一句话

八，存储过程和函数

存储过程：

一组预先定义好的sql语句集合，理解成批处理语句。
1.提高代码的重用性
2.简化操作
3.减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

1.创建语法：

CREATE PROCEDURE 存储过程名（参数列表）
BEGIN
一组合法的sql语句;
END
参数列表：参数模式 参数名 参数类型

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
OUT ：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出

如果存储过程只有一句话，begin END 可以省略

存储过程体中的每条sql语句的结尾需要必须加分号，
存储过程的结尾可以使用 DELIMITER 重新设置。

2.调用

CALL 存储过程名（实参列表）;

3.案例
  1. #插入到admin表中五条记录
  2. DELIMITER $
  3. CREATE PROCEDURE my_a()
  4. BEGIN
  5. INSERT INTO admin(username,PASSWORD) VALUES('yin','666');
  6. INSERT INTO admin(username,PASSWORD) VALUES('aa','123');
  7. INSERT INTO admin(username,PASSWORD) VALUES('bb','666');
  8. INSERT INTO admin(username,PASSWORD) VALUES('cc','123');
  9. INSERT INTO admin(username,PASSWORD) VALUES('dd','666');
  11. END $
  12. #创建存储过程实现 根据女生名查询对应的男生信息
  13. DELIMITER $
  14. CREATE PROCEDURE my_b(IN beauty_name VARCHAR(20))
  15. BEGIN 
  16.       SELECT bo.*
  17.       FROM boys bo
  18.       RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
  19.       WHERE b.name=beauty_name;
  20. END $
  21. CALL my_b('热巴');
  22. #根据女生名返回他的男朋友名
  23. DELIMITER $
  24. CREATE PROCEDURE my_d(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20))
  25. BEGIN
  26.         SELECT bo.boyName INTO boyName
  27.         FROM boys bo
  28.         INNER JOIN beauty b ON bo.id=b.boyfriend_id
  29.         WHERE b.name=beautyName;
  30. END $
  32. CALL my_d('小昭',@b_name);
  33. SELECT @b_name;
  34. #传入两个值a，b，最终翻倍返回a和b
  35. DELIMITER $
  36. CREATE PROCEDURE my_e(INOUT a INT ,INOUT b INT )
  37. BEGIN
  38.         SET a=a*2;
  39.         SET b=b*2;
  40. END $
  41. SET @m=10;
  42. SET @n=20;
  43. CALL my_e(@m,@n);
  44. SELECT @m,@n;

4.删除存储过程
  1. DROP PROCEDURE 存储过程名
  2. DROP PROCEDURE my_a;

5.查看存储过程的信息
  1. SHOW CREATE PROCEDURE  my_b;

函数

存储过程可以有0/n个返回值：适合批量增删改
函数有且仅有一个返回值：适合查询

1.创建
  1. DELIMITER $
  2. CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
  3. BEGIN
  5. END

注意：
参数列表：参数名，参数类型
一定会有return语句

2.使用

SELECT 函数名(参数列表)

  1. #返回公司员工个数
  2. DELIMITER $
  3. CREATE FUNCTION my_f1() RETURNS INT
  4. BEGIN
  5. DECLARE c INT DEFAULT 0 ;
  6.     SELECT COUNT(*) INTO c FROM employees;
  7.     RETURN c;
  8. END $
  9. SELECT my_f1();
  10. #根据员工名返回他的工资
  11. DELIMITER $
  12. CREATE FUNCTION my_f2(NAME VARCHAR(20)) RETURNS DOUBLE
  13. BEGIN 
  14. DECLARE c DOUBLE;
  15.      SELECT salary INTO c FROM employees WHERE last_name=NAME;
  16.      RETURN c;
  17. END $
  18. SET @a='Hunold';
  19. SELECT my_f2(@a);

3.查看
  1. SHOW CREATE FUNCTION my_f2;

4.删除
  1. DROP FUNCTION my_f2;

九，流程控制

分支结构

1.if （表达式1，表达式2，表达式3）

如果表达式1成立，就返回表达式2的值，否则返回表达式3的值。
应用在任何地方

2.case

1)switch-CASE

语法:
CASE 要判断的字段或者表达式
WHEN 常量1 THEN 要显示的值1或者语句1
WHEN 常量2 THEN 要显示的值2或者语句2
…
ELSE 要显示的值n或者语句n；

  1. 案例：查询员工的工资，要求
  2. 部门号==30，显示的工资为1.1倍，
  3. 部门号==40，显示的工资为1.2倍，
  4. 部门号==50，显示的工资为1.3倍，
  5. 其他部门，显示原有工资。
  6. SELECT 
  7.   salary AS 原始工资,
  8.   department_id ,
  9.   CASE
  10.     department_id 
  11.     WHEN 30 
  12.     THEN salary * 1.1 
  13.     WHEN 40 
  14.     THEN salary * 1.2 
  15.     WHEN 50 
  16.     THEN salary * 1.3 
  17.     ELSE salary 
  18.   END AS 新工资 
  19. FROM
  20.   employees ;

2)CASE 使用2：

语法：
CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件2 THEN 要显示的值2或语句2
…
ELSE 要显示的值n或语句n
END

  1. 案例：查询员工的工资情况
  2. 如果>2w，显示A
  3. 如果>1.5w，显示B
  4. 如果>1w，显示C
  5. 否则，显示D
  6. SELECT 
  7.   salary,
  8.   CASE
  9.     WHEN salary > 20000 
  10.     THEN 'A' 
  11.     WHEN salary > 15000 
  12.     THEN 'B' 
  13.     WHEN salary > 10000 
  14.     THEN 'C' 
  15.     ELSE 'D' 
  16.   END AS 工资等级 FROM employees 
  17.  可以放在任何地方
  18.  #创建存储过程，根据传入的成绩，显示等级，90A,80B，70C，60D ，F
  19.  DELIMITER $
  20.  CREATE PROCEDURE my_1(IN score INT)
  21.  BEGIN
  22.  CASE 
  23.  WHEN  score BETWEEN 90 AND 100
  24.  THEN  SELECT 'A';
  25.   WHEN  score BETWEEN 80 AND 90
  26.  THEN  SELECT 'B';
  27.   WHEN  score BETWEEN 70 AND 80
  28.  THEN  SELECT 'C';
  29.   WHEN  score BETWEEN 70 AND 60
  30.  THEN  SELECT 'D';
  31.  ELSE SELECT 'E';
  32.  END CASE;
  33.  END $
  34. CALL my_1(95);

3.if

语法：
IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
…
ELSE 语句n;
END IF;
只能用在begin end中

  1. #创建存储过程，根据传入的成绩，返回等级，90A,80B，70C，60D ，F
  2. DELIMITER $
  3.  CREATE FUNCTION my_2( score INT) RETURNS CHAR
  4.  BEGIN 
  6.    IF score >=90 THEN RETURN'A';
  7.    ELSEIF score >=80 THEN RETURN'B';
  8.    ELSEIF score >=70 THEN RETURN'C';
  9.    ELSEIF score >=60 THEN RETURN'D';
  10.    ELSE RETURN'E';
  11.    END IF;
  12.  END $
  13.  SELECT  my_2(85);

循环结构

在存储过程或函数里面使用

1.while

语法：
标签:WHILE 循环条件 DO
循环体;
END WHILE 标签;
循环控制和标签搭配使用

2.loop

语法：
标签： LOOP
循环体;
END LOOP 标签;

3.repeat

语法：
标签： REPEAT
循环体;
UNTIL 结束循环的条件
END REPEAT 标签;

循环控制
ITERATE 类似continue
LEAVE 类似break

left join==left outer join

a left join b 就是取a和b的交集加a剩下的部分

inner join

a inner join b就是取交集

夜拍.jpeg