七天玩转MySQL

查询

select ... from ....
where ...
group by ...
having ...
order by ...
limit ...;

执行顺序：

1 from
2 where
3 group by
4 having
5 select
6 order by
7 limit

group by

MySql从5.7版本开始默认开启only_full_group_by规则，规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql

order by后面的列必须是在select后面存在的
select、having或order by后面存在的非聚合列必须全部在group by中存在

count(*)、count(1)、count(主键)、count(字段)的区别

以下，基于 InnoDB

含义区别

count()是一个聚合函数，对于返回的结果集，会逐行判断，若返回的不是 NULL，就会加 1，否则不加。
因此，count(*)、count(主键 id)和count(1)都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

性能区别

分析性能，考虑以下几个原则：

server 层要什么就会返回什么；
InnoDB 只返回必要的值；
优化器只优化了count(*)

对于count(主键id)，InnoDB 会遍历全表，取每行的主键 id，返回给 server 层，server 层拿到数据后，进行判断累加。
对于count(1)，InnoDB 仍遍历全表，但是不取值，server 层对返回的每一行数据新增一个 1，然后进行判断累加；
因此，count(1)要更快些，因为无需取值。从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。
对于count(字段)：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是 null 才累加。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

结论

按照效率排序的话

1	count(字段) < count(主键 id) < count(1) ≈ count(*)

所以我建议你，尽量使用count(*)

MySQL服务的连接和退出

退出：
exit或ctrl+C

常见sql命令演示

命令不区分大小写，库名、表名、字段名建议大写，其它小写。

单行注释：#或--空格
多行注释：/*注释内容*/

show databases; // 展示数据库
use 数据库名;   //使用
show tables;    //展示表
show tables from 数据库名;  //查看其他数据库的表
select database();      //查看当前所在数据库
//建表
create table stuinfo(
    sutid   int,
    stuname varchar(20),
    gender  char,
    borndate    datetime
);
desc sutinfo;   //查看表结构
insert into stuinfo values(1,'张无忌','男','1998-3-3');     //插入数据
insert into stuinfo values(2,'张翠山','男','1998-3-3');     //插入数据
select * from stuinfo;      //查看数据
update stuinfo set borndate='1980-1-1' where stuid=2;   //修改
delete from stuinfo where stuid=1;      //删除
alter table stuinfo add column email varchar(20);       //增加表字段
drop table stuinfo;     //删表

SQL语言介绍

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

基础查询

语法

select 查询列表 from 表名；
查询列表的结果集是一个虚拟表，select后面的查询列表可以有多个部分组成（字段、表达式、常量、函数等），逗号隔开，如：
select 字段1，字段2，表达式 from 表名；

执行顺序

from子句
select子句

起别名

使用as关键字

1
2
3

SELECT USER() AS 用户名
SELECT USER() AS "用户名"
SELECT USER() AS '用户名'

使用空格

1
2
3

SELECT USER()  用户名
SELECT USER()  "用户名"
SELECT USER()  '用户名'

mysql中`+`作用

两个操作数都是数值型，做加法
其中一个操作数为字符型，将字符型数据强制转换成数值型，如无法转换则当做0处理
其中一个操作数为null，结果为null

+不能拼接2个字段，需使用concat拼接函数

1 2	SELECT CONCAT(first_name,last_name) AS "姓名" FROM employees;

distinct

查询员工涉及到的部门编号有哪些？（重复出现的显示1个）

1	SELECT DISTINCT department_id FROM employees;

查看表结构

1 2	DESC employess; SHOW COLUMNS FROM employees;

ifnull(表达式1,表达式2)

表达式1：可能为null的字段或表达式
表达式2：如果表达式1为null，则最终结果显示的值

功能：如果表达式1为null，则显示表达式2，否则显示

条件查询

语法：

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

select last_name,first_name 
from employees 
where salary>20000;

执行顺序：

from子句
where子句
select子句

特点：

按关系表达式筛选
关系运算符：>、<、>=、<=、=、<>(补充：也可以使用!=,但不建议)
按逻辑表达式筛选
逻辑运算符：and、or、not (补充：也可以使用&& || ! ，但不建议)
模糊查询
like、in、between and、is null

按关系表达式筛选

-- 案例1：查询部门编号不是100的员工信息
SELECT *
FROM employees
WHERE department_id <> 100;


-- 案例2：查询工资<15000的姓名、工资
SELECT last_name,salary
FROM employees
WHERE salary<15000;

按逻辑表达式筛选

-- 案例1：查询部门编号不是 50-100之间员工姓名、部门编号、邮箱
-- 方式1：
SELECT last_name,department_id,email
FROM employees
WHERE department_id <50 OR department_id>100;

-- 方式2：
SELECT last_name,department_id,email
FROM employees
WHERE NOT(department_id>=50 AND department_id<=100);

-- 案例2：查询奖金率>0.03 或者 员工编号在60-110之间的员工信息
SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id >=60 AND employee_id<=110);

模糊查询

功能：一般和通配符搭配使用，对字符型数据进行部分匹配查询。like/not like
常见的通配符：

_ 任意单个字符
% 任意多个字符,支持0-多个

-- 案例1：查询姓名中包含字符a的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%a%';

-- 案例2：查询姓名中包含最后一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '%e';

-- 案例3：查询姓名中包含第一个字符为e的员工信息
SELECT *
FROM employees
WHERE last_name LIKE 'e%';

-- 案例4：查询姓名中包含第三个字符为x的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '__x%';

-- 案例5：查询姓名中包含第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '_\_%';

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$'; -- 自定义转义字符为$

in查询

功能：查询某字段的值是否属于指定的列表之内
a in(常量值1,常量值2,常量值3,...)
a not in(常量值1,常量值2,常量值3,...)

-- 案例1：查询部门编号是30/50/90的员工名、部门编号
-- 方式1：
SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

-- 方式2：
SELECT last_name,department_id
FROM employees
WHERE department_id = 30
OR department_id = 50
OR department_id = 90;

-- 案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息
-- 方式1：
SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');

-- 方式2：
SELECT *
FROM employees
WHERE NOT(job_id ='SH_CLERK'
OR job_id = 'IT_PROG');

between and

功能：判断某个字段的值是否介于xx之间
between and/not between and

-- 案例1：查询部门编号是30-90之间的部门编号、员工姓名
-- 方式1：
SELECT department_id,last_name
FROM employees
WHERE department_id BETWEEN 30 AND 90;

-- 方式2：
SELECT department_id,last_name
FROM employees
WHERE department_id>=30 AND department_id<=90;


-- 案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪
-- 方式1：
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0))<100000 OR salary*12*(1+IFNULL(commission_pct,0))>200000;

-- 方式2：
SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

is null/is not null

=只能判断普通的内容
IS只能判断NULL值
<=>安全等于，既能判断普通内容，又能判断NULL值

-- 案例1：查询没有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NULL;

-- 案例2：查询有奖金的员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL;

-- 错误写法，报错
SELECT *
FROM employees
WHERE salary IS 10000;

-- 安全等于
SELECT *
FROM employees
WHERE salary <=> 10000;

-- 安全等于
SELECT *
FROM employees
WHERE commission_pct <=> NULL;

条件查询案例

-- 1. 查询工资大于 12000 的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary>12000;

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

-- 3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
-- 方式2

-- 4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT last_name,department_id
FROM employees
WHERE department_id IN (20,50);
-- 方式2

-- 5. 选择公司中没有管理者的员工姓名及 job_id
SELECT last_name,job_id
FROM employees 
WHERE manager_id IS NULL;

-- 6. 选择公司中有奖金的员工姓名，工资和奖金率
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;

-- 7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

-- 8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%'  AND  last_name LIKE '%e%';

-- 9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT *
FROM employees
WHERE first_name LIKE '%e';

-- 10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

-- 11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name,job_id
FROM employees
WHERE manager_id IN (100,101,110);

排序查询

语法

select 查询列表
from 表名
【where 筛选条件】
order by 排序列表

执行顺序：

① from 子句
② where 子句
③ select 子句
④ order by 子句

举例：

select last_name,salary
from employees
where salary>20000
order by salary ;

select * from employees;

特点：

排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合
升序，通过 asc ，默认行为
降序，通过 desc

案例说明

按单个字段排序

-- 案例1：将员工编号>120的员工信息进行工资的升序
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary ;

-- 案例1：将员工编号>120的员工信息进行工资的降序
SELECT * 
FROM employees 
WHERE employee_id>120 
ORDER BY salary DESC;

按表达式排序

-- 案例1：对有奖金的员工，按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

按别名排序

-- 案例1：对有奖金的员工，按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))  年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

按函数的结果排序

-- 案例1：按姓名的字数长度进行升序
SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

按多个字段排序

-- 案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序
SELECT last_name,salary,department_id
FROM employees
ORDER BY salary ASC,department_id DESC;

补充选学：按列数排序

SELECT * FROM employees 
ORDER BY 2 DESC;

SELECT * FROM employees 
ORDER BY first_name;

常见函数

字符函数
数学函数
日期函数
流程控制函数

函数：类似于java中学过的“方法”，为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用

自定义方法(函数)
调用方法(函数)
- 叫什么：函数名
- 干什么：函数功能

字符函数

CONCAT 拼接字符

1	SELECT CONCAT('hello,',first_name,last_name) 备注 FROM employees;

LENGTH 获取字节长度

1	SELECT LENGTH('hello,郭襄');

CHAR_LENGTH 获取字符个数

1	SELECT CHAR_LENGTH('hello,郭襄');

SUBSTRING 截取子串

/*
注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)
*/
SELECT SUBSTR('张三丰爱上了郭襄',1,3);
SELECT SUBSTR('张三丰爱上了郭襄',7);

INSTR获取字符第一次出现的索引

1	SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');

TRIM去前后指定的字符，默认是去空格

1 2	SELECT TRIM(' 虚竹 ') AS a; SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx') AS a;

LPAD/RPAD 左填充/右填充

1 2	SELECT LPAD('木婉清',10,'a'); SELECT RPAD('木婉清',10,'a');

UPPER/LOWER 变大写/变小写

-- 案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
SELECT UPPER(SUBSTR(first_name,1,1)),first_name FROM employees;
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees;
SELECT UPPER(last_name) FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT"
FROM employees;

STRCMP 比较两个字符大小

1	SELECT STRCMP('aec','aec');

LEFT/RIGHT 截取子串

1 2	SELECT LEFT('鸠摩智',1); SELECT RIGHT('鸠摩智',1);

数学函数

ABS 绝对值

1	SELECT ABS(-2.4);

CEIL 向上取整返回>=该参数的最小整数

1
2
3

SELECT CEIL(-1.09);
SELECT CEIL(0.09);
SELECT CEIL(1.00);

FLOOR 向下取整，返回<=该参数的最大整数

1
2
3

SELECT FLOOR(-1.09);
SELECT FLOOR(0.09);
SELECT FLOOR(1.00);

ROUND 四舍五入

1 2	SELECT ROUND(1.8712345); SELECT ROUND(1.8712345,2);

TRUNCATE 截断

1	SELECT TRUNCATE(1.8712345,1);

MOD 取余

SELECT MOD(-10,3);
a%b = a-(INT)a/b*b
-10%3 = -10 - (-10)/3*3   = -1

SELECT -10%3;
SELECT 10%3;
SELECT -10%-3;
SELECT 10%-3;

日期函数

1	SELECT NOW();

CURDATE

1	SELECT CURDATE();

CURTIME

1	SELECT CURTIME();

DATEDIFF

1	SELECT DATEDIFF('1998-7-16','2019-7-13');

DATE_FORMAT

SELECT DATE_FORMAT('1998-7-16','%Y年%M月%d日 %H小时%i分钟%s秒') 出生日期;

SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期 
FROM employees;

STR_TO_DATE 按指定格式解析字符串为日期类型

1 2	SELECT * FROM employees WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');

流程控制函数

IF函数

SELECT IF(100>9,'好','坏');  -- 类似三目运算符

-- 需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金,commission_pct
FROM employees;

CASE函数

-- 情况1 ：类似于switch语句，可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END

/*
案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变
显示 部门编号，新工资，旧工资
*/

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE salary
END  newSalary
FROM employees;


-- 情况2：类似于多重IF语句，实现区间判断
CASE 
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
...
ELSE 结果n
END

/*
案例：如果工资>20000,显示级别A
      工资>15000,显示级别B
      工资>10000,显示级别C
      否则，显示D
*/

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END
AS  a
FROM employees;

案例讲解

-- 1. 显示系统时间(注：日期+时间)
SELECT NOW();

-- 2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）

SELECT employee_id,last_name,salary,salary*1.2 "new salary"
FROM employees;

-- 3. 将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT LENGTH(last_name) 长度
FROM employees
ORDER BY SUBSTR(last_name,1,1) ASC;

-- 4. 做一个查询，产生下面的结果
/*
<last_name> earns <salary> monthly but wants <salary*3>
          Dream Salary
King earns 24000 monthly but wants 72000
*/

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3)  "Dream Salary"
FROM employees;

-- 5. 使用 case-when，按照下面的条件：
/*
job     grade
AD_PRES  A
ST_MAN   B
IT_PROG  C
SA_REP   D
ST_CLERK E
产生下面的结果
Last_name Job_id    Grade
king      AD_PRES   A
*/

SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES'  THEN 'A'
WHEN 'ST_MAN'  THEN 'B'
WHEN 'IT_PROG'  THEN 'C'
WHEN 'SA_REP'  THEN 'D'
WHEN 'ST_CLERK'  THEN 'E'
END Grade
FROM employees;

聚合函数

说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数

分组函数清单：

sum(字段名)：求和，参数只能是数值型
avg(字段名)：求平均数，参数只能是数值型
max(字段名)：求最大值
min(字段名)：求最小值
count(字段名)：计算非空字段值的个数

-- 案例1 ：查询员工信息表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数
SELECT SUM(salary),AVG(salary),MIN(salary),MAX(salary),COUNT(salary) FROM employees;

-- 案例2：添加筛选条件
-- 查询emp表中记录数：
SELECT COUNT(employee_id) FROM employees;

-- 查询emp表中有佣金的人数：
SELECT COUNT(salary) FROM employees;

-- 查询emp表中月薪大于2500的人数：
SELECT COUNT(salary) FROM employees WHERE salary>2500;

-- 查询有领导的人数：
SELECT COUNT(manager_id) FROM employees;

-- count的补充介绍★

-- 1、统计结果集的行数，推荐使用count(*)

SELECT COUNT(*) FROM employees;
SELECT COUNT(*) FROM employees WHERE department_id = 30;

SELECT COUNT(1) FROM employees;
SELECT COUNT(1) FROM employees WHERE department_id = 30;


-- 2、搭配distinct实现去重的统计

-- 需求：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;
-- 思考：每个部门的总工资、平均工资？

SELECT SUM(salary)  FROM employees WHERE department_id = 30;
SELECT SUM(salary)  FROM employees WHERE department_id = 50;

SELECT SUM(salary) ,department_id
FROM employees
GROUP BY department_id;

分组查询

特点

查询列表往往是分组（聚合）函数和被分组的字段 ★
分组查询中的筛选分为两类

	筛选的基表	使用的关键词	位置
分组前筛选	原始表	where	group by 前面
分组后筛选	分组后的结果集	having	group by 后面

顺序：where —— group by —— having
分组函数做条件只可能放在having后面！！！

简单的分组

-- 案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

-- 案例2：查询每个领导的手下人数
SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

可以实现分组前的筛选

-- 案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT MAX(salary) 最高工资,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;


-- 案例2：查询每个领导手下有奖金的员工的平均工资
SELECT AVG(salary) 平均工资,manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

可以实现分组后的筛选

-- 案例1：查询哪个部门的员工个数>5
-- 分析1：查询每个部门的员工个数
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id

-- 分析2：在刚才的结果基础上，筛选哪个部门的员工个数>5
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING  COUNT(*)>5;

-- 案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct  IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

-- 案例3：领导编号>102的    每个领导手下的最低工资大于5000的最低工资
-- 分析1：查询每个领导手下员工的最低工资
SELECT MIN(salary) 最低工资,manager_id
FROM employees
GROUP BY manager_id;

-- 分析2：筛选刚才1的结果
SELECT MIN(salary) 最低工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000 ;

可以实现排序

-- 案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
-- 分析1：按工种分组，查询每个工种有奖金的员工的最高工资
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id

-- 分析2：筛选刚才的结果，看哪个最高工资>6000
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id
HAVING MAX(salary)>6000

-- 分析3：按最高工资升序
SELECT MAX(salary) 最高工资,job_id
FROM employees
WHERE commission_pct IS  NULL
GROUP BY job_id
HAVING MAX(salary)>6000
ORDER BY MAX(salary) ASC;

按多个字段分组

-- 案例：查询每个工种每个部门的最低工资,并按最低工资降序
-- 提示：工种和部门都一样，才是一组
SELECT MIN(salary) 最低工资,job_id,department_id
FROM employees
GROUP BY job_id,department_id;

连接查询

说明：又称多表查询，当查询语句涉及到的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

sql-92标准

内连接

语法:
select 查询列表
from 表1 别名,表2 别名
where 连接条件
and 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表

执行顺序：

1、from子句
2、where子句
3、and子句
4、group by子句
5、having子句
6、select子句
7、order by子句

等值连接

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

-- 案例1：查询女神名和对应的男神名
SELECT NAME,boyName 
FROM boys,beauty
WHERE beauty.boyfriend_id= boys.id;

-- 案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

为表起别名:
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

提高语句的简洁度
区分多个重名的字段

-- 查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees  e,jobs j
WHERE e.`job_id`=j.`job_id`;

两个表的顺序可以调换

-- 查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

可以加筛选

-- 案例：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND e.`commission_pct` IS NOT NULL;

-- 案例2：查询城市名中第二个字符为o的部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

可以加分组

-- 案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

-- 案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;

可以加排序

-- 案例：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

可以实现三表连接

-- 案例：查询员工名、部门名和所在的城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;

非等值连接

-- 案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

自连接

-- 案例：查询 员工名和上级的名称
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

sql-99标准

内连接

SQL92和SQL99的区别：SQL99，使用JOIN关键字代替了之前的逗号，并且将连接条件和筛选条件进行了分离，提高阅读性！！！

语法：

SELECT 查询列表
FROM 表名1 别名
【INNER】 JOIN  表名2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组列表
HAVING 分组后筛选
ORDER BY 排序列表;

等值连接

简单连接

-- 案例：查询员工名和部门名
SELECT last_name,department_name
FROM departments d 
JOIN  employees e 
ON e.department_id =d.department_id;

添加筛选条件

-- 案例1：查询部门编号>100的部门名和所在的城市名
SELECT department_name,city
FROM departments d
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_id`>100;

添加分组+筛选

-- 案例1：查询每个城市的部门个数
SELECT COUNT(*) 部门个数,l.`city`
FROM departments d
JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`;

添加分组+筛选+排序

-- 案例1：查询部门中员工个数>10的部门名，并按员工个数降序
SELECT COUNT(*) 员工个数,d.department_name
FROM employees e
JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.`department_id`
HAVING 员工个数>10
ORDER BY 员工个数 DESC;

非等值连接

-- 案例：查询部门编号在10-90之间的员工的工资级别，并按级别进行分组
SELECT COUNT(*) 个数,grade
FROM employees e
JOIN sal_grade g
ON e.`salary` BETWEEN g.`min_salary` AND g.`max_salary`
WHERE e.`department_id` BETWEEN 10 AND 90
GROUP BY g.grade;

自连接

-- 案例：查询员工名和对应的领导名
SELECT e.`last_name`,m.`last_name`
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

外连接

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null
应用场景：一般用于查询主表中有但从表没有的记录

特点：

外连接分主从表，两表的顺序不能任意调换
左连接的话，left join左边为主表
右连接的话，right join右边为主表

语法：

select 查询列表
from 表1 别名
left|right|full 【outer】 join 表2 别名
on 连接条件
where 筛选条件;

-- 案例1：查询所有女神记录，以及对应的男神名，如果没有对应的男神，则显示为null
-- 左连接
SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`;

-- 右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`;

-- 案例2：查哪个女神没有男朋友
-- 左连接
SELECT b.`name`
FROM beauty b
LEFT JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

-- 右连接
SELECT b.*,bo.*
FROM boys bo
RIGHT JOIN  beauty b ON b.`boyfriend_id` = bo.`id`
WHERE bo.`id`  IS NULL;

-- 案例3：查询哪个部门没有员工，并显示其部门编号和部门名
SELECT COUNT(*) 部门个数
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;

-- 案例
/*
一、查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
4   小红     大飞
5   小白     大黄
6   小绿     NULL
*/
SELECT b.id,b.name,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.boyfriend_id = bo.id
WHERE b.id>3;

-- 二、查询哪个城市没有部门
SELECT l.city
FROM departments d
RIGHT JOIN locations l ON l.location_id = d.location_id
WHERE d.`department_id` IS NULL;

-- 三、查询部门名为 SAL 或 IT 的员工信息
SELECT d.*,e.*
FROM departments d
LEFT JOIN employees e ON d.`department_id` = e.`department_id`
WHERE d.`department_name` = 'SAL' OR d.`department_name`='IT';

测试题3

-- 一、查询员工姓名、入职日期并按入职日期升序
select last_name,hiredate
from employees
order by hiredate asc;

-- 还可以用日期函数
date_formate(hiredate,'%Y')
year(hiredate)
month(hiredate)
day(hiredate)
hour(hiredate)
minute(hiredate)
second(hiredate)

-- 二、将当前日期显示成 xxxx年xx月xx日
select date_format(now(),'%Y年%m月%d日');

/*三、
已知学员信息表stuinfo
    stuId
    stuName 
    gender 
    majorId

已知专业表major
    id    
    majorName
已知成绩表result
    id成绩编号
    majorid
    stuid
    score
*/
-- 1、查询所有男生的姓名、专业名和成绩，使用SQL92和SQL99两种语法方式实现
-- SQL92
select stuname,majorname,score
from stuinfo s,major m,result r
where s.majorid = m.id and r.stuid = s.stuid
and s.gender = '男';

-- SQL99
select stuname,majorname,score
from stuinfo s
join major m on s.majorid = m.id 
join result r on r.stuid = s.stuid
where s.gender = '男';

-- 2、查询每个性别的每个专业的平均成绩，并按平均成绩降序
select avg(score) 平均成绩,gendeer,s.majorid
from stuinfo s
join result r on s.stuid = r.stuid
group by gender,s.majorid
order by 平均成绩 desc;

子查询

说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询
外面的select语句称为主查询或外查询。

按子查询出现的位置进行分类：

select后面
要求：子查询的结果为单行单列（标量子查询）
from后面
要求：子查询的结果可以为多行多列
where或having后面 ★
要求：子查询的结果必须为单列
```
 单行子查询
 多行子查询
```
exists后面
要求：子查询结果必须为单列（相关子查询）

特点：

子查询放在条件中，要求必须放在条件的右侧
子查询一般放在小括号中
子查询的执行优先于主查询
单行子查询对应了单行操作符：> < >= <= = <>
多行子查询对应了多行操作符：any/some all in

单行子查询(放在where或having后面)

-- 案例1：谁的工资比 Abel 高?
-- （1）查询Abel的工资
SELECT salary
FROM employees
WHERE last_name  = 'Abel'
-- （2）查询salary>（1）的员工信息
SELECT last_name,salary
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name  <> 'Abel'
);

-- 案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
-- （1）查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141
-- （2）查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143
-- （3）查询job_id=（1） and salary>（2）的信息
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
    SELECT job_id
    FROM employees
    WHERE employee_id = 141
) AND salary>(
    SELECT salary
    FROM employees
    WHERE employee_id = 143
);

-- 1. 查询和 Zlotkey 相同部门的员工姓名和工资
-- (1)查询Zlotkey的部门编号
SELECT department_id
FROM employees
WHERE last_name = 'Zlotkey'
-- (2)查询department_id = (1)的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    WHERE last_name = 'Zlotkey'
);

-- 2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
-- (1)查询平均工资
SELECT AVG(salary)
FROM employees
-- (2)查询salary>(1)的信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
    SELECT AVG(salary)
    FROM employees
);

多行子查询

in：判断某字段是否在指定列表内

1	x in(10,30,50)

any/some：判断某字段的值是否满足其中任意一个

x>any(10,30,50)
x>min()

x=any(10,30,50)
x in(10,30,50)

all：判断某字段的值是否满足里面所有的

1 2	x >all(10,30,50) x >max()

-- 案例1：返回location_id是1400或1700的部门中的所有员工姓名
-- （1）查询location_id是1400或1700的部门
SELECT department_id
FROM departments
WHERE location_id IN(1400,1700)
-- （2）查询department_id = （1）的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

-- 题目：返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id 以及salary
-- （—1）查询job_id为‘IT_PROG’部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
-- （2）查询其他部门的工资<任意一个（1）的结果
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ANY(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);
-- 等价于
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
    SELECT MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
);

-- 案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary
-- （1）查询job_id为‘IT_PROG’部门的工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
-- （2）查询其他部门的工资<所有①的结果
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<ALL(
    SELECT DISTINCT salary
    FROM employees
    WHERE job_id = 'IT_PROG'
);
-- 等价于
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary<(
    SELECT MIN(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
);

放在select后面

-- 不知道什么场景用
-- 案例；查询部门编号是50的员工个数
SELECT 
(
    SELECT COUNT(*)
    FROM employees
    WHERE department_id = 50
)  个数;

放在from后面

-- 案例：查询每个部门的平均工资的工资级别
-- (1)查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
-- (2)将(1)和sal_grade两表连接查询
SELECT dep_ag.department_id,dep_ag.ag,g.grade
FROM sal_grade g
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
) dep_ag ON dep_ag.ag BETWEEN g.min_salary AND g.max_salary;

放在exists后面

-- 案例1 ：查询有无名字叫“张三丰”的员工信息
SELECT EXISTS(
    SELECT * 
    FROM employees
    WHERE last_name = 'Abel'

) 有无Abel;

-- 案例2：查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE bo.`id` NOT IN(
    SELECT boyfriend_id
    FROM beauty b
)

SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
    SELECT boyfriend_id
    FROM beauty b
    WHERE bo.id = b.boyfriend_id
);

联合查询

说明：当查询结果来自于多张表，但多张表之间没有关联，这个时候往往使用联合查询，也称为union查询

语法：

1
2
3

select 查询列表 from 表1  where 筛选条件  
    union
select 查询列表 from 表2  where 筛选条件

特点：

多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
union 实现去重查询
union all 实现全部查询，包含重复项

-- 案例：查询所有国家的年龄>20岁的用户信息
SELECT * FROM usa WHERE uage >20 
UNION
SELECT * FROM chinese WHERE age >20 ;

-- 案例2：查询所有国家的用户姓名和年龄
SELECT uname,uage FROM usa
UNION
SELECT age,`name` FROM chinese;

-- 案例3：union自动去重/union all 可以支持重复项
SELECT 1,'范冰冰' 
UNION ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' 
UNION  ALL
SELECT 1,'范冰冰' ;

分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示
分页查询的sql命令请求数据库服务器——>服务器响应查询到的多条数据——>前台页面

语法：

select 查询列表
from 表1 别名
join 表2 别名
on 连接条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序列表
limit 起始条目索引,显示的条目数

执行顺序：

1》from子句
2》join子句
3》on子句
4》where子句
5》group by子句
6》having子句
7》select子句
8》order by子句
9》limit子句

特点：

起始条目索引如果不写，默认是0
limit后面支持两个参数
参数1：显示的起始条目索引
参数2：条目数

公式：
假如要显示的页数是page，每页显示的条目数为size

select *
from employees
limit (page-1)*size,size;

page        size=10
1           limit 0,10
2            limit 10,10
3            limit 20,10
4            limit 30,10

子查询案例

-- 3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
-- （1）查询各部门的平均工资
SELECT department_id,AVG(salary) ag
FROM employees
GROUP BY department_id
-- （2）将（1）结果和employees表连接查询
SELECT employee_id,last_name,salary
FROM employees e
JOIN  (
    SELECT department_id,AVG(salary) ag
    FROM employees
    GROUP BY department_id
) dep_ag ON e.department_id = dep_ag.department_id
WHERE e.salary>dep_ag.ag;

-- 4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
-- （1）查询姓名中包含字母 u 的员工的部门编号
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
-- （2）查询部门号是①的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(
    SELECT DISTINCT department_id
    FROM employees
    WHERE last_name LIKE '%u%'
);

-- 6. 查询管理者是 King 的员工姓名和工资
-- （1）查询管理者是king的编号
SELECT employee_id
FROM employees
WHERE last_name  = 'k_ing'
-- （2）查询哪个员工的领导编号是（1）
SELECT last_name,salary
FROM employees
WHERE manager_id  IN(
    SELECT employee_id
    FROM employees
    WHERE last_name  = 'k_ing'
);

-- 3. 查询平均工资最低的部门信息和该部门的平均工资
-- （1）查询各部门的平均工资
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
-- （2）查询哪个部门的平均工资最低
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
ORDER BY ag 
LIMIT 1
-- （3）连接（2）和departments表
SELECT d.*,dep_ag.ag
FROM departments d
JOIN (
    SELECT AVG(salary) ag,department_id
    FROM employees
    GROUP BY department_id
    ORDER BY ag 
    LIMIT 1
) dep_ag ON d.department_id = dep_ag.department_id

-- 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
-- （1）查询各部门的最高工资
SELECT MAX(salary) mx,department_id
FROM employees
GROUP BY department_id
-- （2）查询各部门的最高工资最低的那个部门
SELECT department_id
FROM employees
GROUP BY department_id
ORDER BY MAX(salary) 
LIMIT 1
-- （3）查询部门编号是②的部门的最低工资
SELECT MIN(salary),department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM employees
    GROUP BY department_id
    ORDER BY MAX(salary) 
    LIMIT 1
);

DDL语言

说明：Data Define Language数据定义语言,用于对数据库和表的管理和操作

库的管理

创建数据库

1 2	CREATE DATABASE stuDB; CREATE DATABASE IF NOT EXISTS stuDB;

删除数据库

1 2	DROP DATABASE stuDB; DROP DATABASE IF EXISTS stuDB;

表的管理

创建表

-- 语法：
CREATE TABLE [IF NOT EXISTS] 表名(
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】,
    字段名  字段类型  【字段约束】
);


-- 案例：没有添加约束
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT ,
    stuname VARCHAR(20),
    stugender CHAR(1),
    email VARCHAR(20),
    borndate DATETIME
);

-- 案例：添加约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT PRIMARY KEY,                  -- 添加了主键约束
    stuname VARCHAR(20) UNIQUE NOT NULL, -- 添加了唯一约束+非空
    stugender CHAR(1) DEFAULT '男',      -- 添加了默认约束
    email VARCHAR(20) NOT NULL,
    age INT CHECK( age BETWEEN 0 AND 100), -- 添加了检查约束，mysql不支持
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id) -- 添加了外键约束
);

-- 支持表级约束：UNIQUE、PRIMARY KEY、FOREIGN KEY 
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
    stuid INT ,                  
    stuname VARCHAR(20) , 
    stugender CHAR(1) DEFAULT '男',      -- 添加了默认约束
    email VARCHAR(20) NOT NULL,
    age INT CHECK( age BETWEEN 0 AND 100), -- 添加了检查约束，mysql不支持
    majorid INT,
    PRIMARY KEY(id),                 -- 添加了主键约束
    CONSTRAINT uq unique(name),        -- 添加了唯一约束+非空
    CONSTRAINT fk_stuinfo_major FOREIGN KEY (majorid) REFERENCES major(id), -- 添加了外键约束
);

数据类型

数据类型
整型	TINYINT、SMALLINT、 INT、 BIGINT
浮点型	FLOAT(m,n)、DOUBLE(m,n) 、DECIMAL(m,n)，m和n可选，m：总位数，n：小数位数
字符型	CHAR(n)：n可选 VARCHAR(n)：n必选 TEXT n表示最多字符个数
日期型	DATE、TIME 、DATETIME、TIMESTAMP
二进制型	BLOB 存储图片数据

常见约束

说明：用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的！

NOT NULL 非空：用于限制该字段为必填项
DEFAULT 默认：用于限制该字段没有显式插入值，则直接显式默认值
PRIMARY KEY 主键：用于限制该字段值不能重复，设置为主键列的字段默认不能为空。一个表只能有一个主键，当然可以是组合主键
UNIQUE 唯一：用于限制该字段值不能重复
CHECK检查：用于限制该字段值必须满足指定条件。CHECK(age BETWEEN 1 AND 100)
FOREIGN KEY 外键：用于限制两个表的关系,要求外键列的值必须来自于主表的关联列。
要求：
1. 主表的关联列和从表的关联列的类型必须一致，意思一样，名称无要求
2. 主表的关联列要求必须是主键

修改表[了解]

语法：ALTER TABLE 表名 ADD|MODIFY|CHANGE|DROP COLUMN 字段名字段类型【字段约束】;

修改表名

1	ALTER TABLE stuinfo RENAME TO students;

添加字段

1 2	ALTER TABLE students ADD COLUMN borndate TIMESTAMP NOT NULL; DESC students;

修改字段名

1	ALTER TABLE students CHANGE COLUMN borndate birthday DATETIME NULL;

修改字段类型

1	ALTER TABLE students MODIFY COLUMN birthday TIMESTAMP ;

删除字段

1 2	ALTER TABLE students DROP COLUMN birthday; DESC students;

删除表

1	DROP TABLE IF EXISTS students;

复制表

-- 仅仅复制表的结构
CREATE TABLE newTable2 LIKE major;

-- 复制表的结构+数据
CREATE TABLE newTable3 SELECT * FROM girls.`beauty`;

-- 案例：复制employees表中的last_name,department_id,salary字段到新表 emp表，但不复制数据
CREATE TABLE emp 
SELECT last_name,department_id,salary 
FROM myemployees.`employees`
WHERE 1=2;

测试题4

-- 1.使用分页查询实现，查询员工信息表中部门为50号的工资最低的5名员工信息
select *
from employees
where deparrment_id = 50
order by salary asc
limit 0,5;

-- 2.使用子查询实现城市为Toroto的，且工资>10000的员工姓名
-- (1)查询城市为Toroto的部门编号
select department_id
from departments d
join locations l on d.location_id = l.location_id
where city = 'Toroto'
-- (2)查询部门号在(1)里面的员工姓名
select last_name
from employees
where salary>10000 and department_id in(
    select department_id
    from departments d
    join locations l on d.location_id = l.location_id
    where city = 'Toroto'
);

-- 3.创建表qqinfo，里面包含qqid，添加主键约束、昵称nickname，添加唯一约束、邮箱email（添加非空约束）、性别gender
create table if not exists qqinfo(
    qqid int primary key,
    nickname varchar(20) unique,
    email varchar(20) not null,
    gender char
);

-- 4.删除表qqinfo
drop table if exists qqinfo;

-- 5.试写出sql查询语句的定义顺序和执行顺序
1》定义顺序（书写顺序）
select distinct  查询列表
from 表名 别名
join 表名 别名 
on 连接条件
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表
limit 条目数;

2》执行顺序
from子句
join子句
on子句
where子句
group by子句
having子句
select 子句
order by子句
limit子句

DML(Data Manipulation Language)数据操纵语言

insert update delete,对表中的数据的增删改

插入数据

语法

-- 插入单行：
    insert into 表名(字段名1,字段名2 ,...) values (值1，值2,...);
-- 插入多行：
    insert into 表名(字段名1,字段名2 ,...) values
     (值1，值2,...),(值1，值2,...),(值1，值2,...);

特点

字段和值列表一一对应。包含类型、约束等必须匹配
数值型的值，不用单引号。非数值型的值，必须使用单引号
字段顺序无要求

向表中的全部字段添加值

向表中所有字段插入值的方法有两种：一种是指定所有字段名；另一种是完全不指定字段名。
INSERT 语句后面的列名称顺序可以不是 tb_courses 表定义时的顺序，即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以。

【实例 1】在 tb_courses 表中插入一条新记录，course_id 值为 1，course_name 值为“Network”，course_grade 值为 3，info 值为“Computer Network”

mysql> INSERT INTO tb_courses
    -> (course_id,course_name,course_grade,course_info)
    -> VALUES(1,'Network',3,'Computer Network');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
+-----------+-------------+--------------+------------------+
1 row in set (0.00 sec)

【实例 2】在 tb_courses 表中插入一条新记录，course_id 值为 2，course_name 值为“Database”，course_grade 值为 3，info值为“MySQL”。输入的 SQL 语句和执行结果如下所示

mysql> INSERT INTO tb_courses
    -> (course_name,course_info,course_id,course_grade)
    -> VALUES('Database','MySQL',2,3);
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
+-----------+-------------+--------------+------------------+
2 rows in set (0.00 sec)

使用 INSERT 插入数据时，允许列名称列表 column_list 为空，此时值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

【实例 3】在 tb_courses 表中插入一条新记录，course_id 值为 3，course_name 值为“Java”，course_grade 值为 4，info 值为“Jave EE”。输入的 SQL 语句和执行结果如下所示。

mysql> INSERT INTO tb_courses
    -> VLAUES(3,'Java',4,'Java EE');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
+-----------+-------------+--------------+------------------+
3 rows in set (0.00 sec)

INSERT 语句中没有指定插入列表，只有一个值列表。在这种情况下，值列表为每一个字段列指定插入的值，并且这些值的顺序必须和 tb_courses 表中字段定义的顺序相同。

向表中指定字段添加值

为表的指定字段插入数据，是在 INSERT 语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。

【实例 4】在 tb_courses 表中插入一条新记录，course_name 值为“System”，course_grade 值为 3，course_info 值为“Operating System”，输入的 SQL 语句和执行结果如下所示。

mysql> INSERT INTO tb_courses
    -> (course_name,course_grade,course_info)
    -> VALUES('System',3,'Operation System');
Query OK, 1 rows affected (0.08 sec)
mysql> SELECT * FROM tb_courses;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            3 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

可以看到插入记录成功。如查询结果显示，这里的 course_id 字段自动添加了一个整数值 4。这时的 course_id 字段为表的主键，不能为空，系统自动为该字段插入自增的序列值。在插入记录时，如果某些字段没有指定插入值，MySQL 将插入该字段定义时的默认值。

使用 INSERT INTO…FROM 语句复制表数据

INSERT INTO…SELECT…FROM 语句用于快速地从一个或多个表中取出数据，并将这些数据作为行数据插入另一个表中。
SELECT 子句返回的是一个查询到的结果集，INSERT 语句将这个结果集插入指定表中，结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

【实例 5】从 tb_courses 表中查询所有的记录，并将其插入 tb_courses_new 表中。输入的 SQL 语句和执行结果如下所示。

mysql> INSERT INTO tb_courses_new
    -> (course_id,course_name,course_grade,course_info)
    -> SELECT course_id,course_name,course_grade,course_info
    -> FROM tb_courses;
Query OK, 4 rows affected (0.17 sec)
Records: 4  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM tb_courses_new;
+-----------+-------------+--------------+------------------+
| course_id | course_name | course_grade | course_info      |
+-----------+-------------+--------------+------------------+
|         1 | Network     |            3 | Computer Network |
|         2 | Database    |            3 | MySQL            |
|         3 | Java        |            4 | Java EE          |
|         4 | System      |            3 | Operating System |
+-----------+-------------+--------------+------------------+
4 rows in set (0.00 sec)

课程的案例

-- 案例1：要求字段和值列表一一对应，且遵循类型和约束的限制
INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(1,'吴倩','男','wuqian@qq.com',12,1); 

INSERT INTO stuinfo(stuid,stuname,stugender,email,age,majorid)
VALUES(6,'李宗盛2','女','wuqian@qq.com',45,2); 

-- 案例2：可以为空字段如何插入
-- 方案1：字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(5,'齐鱼','qiqin@qq.com',2); 
-- 方案2：字段名写上，值使用null
INSERT INTO stuinfo(stuid,stuname,email,age,majorid)
VALUES(5,'齐鱼','qiqin@qq.com',NULL,2); 

-- 案例3：默认字段如何插入
-- 方案1：字段名写上，值使用default
INSERT INTO stuinfo(stuid,stuname,email,stugender,majorid)
VALUES(7,'齐小鱼','qiqin@qq.com',DEFAULT,2); 
-- 方案2：字段名和值都不写
INSERT INTO stuinfo(stuid,stuname,email,majorid)
VALUES(7,'齐小鱼','qiqin@qq.com',2); 

-- 案例4：可以省略字段列表，默认所有字段
INSERT INTO stuinfo VALUES(8,'林忆莲','女','lin@126.com',12,3);
INSERT INTO stuinfo VALUES(NULL,'小黄','男','dd@12.com',12,3);

设置自增长列

自增长列要求必须设置在一个键上，比如主键或唯一键
自增长列要求数据类型为数值型
一个表至多有一个自增长列

CREATE TABLE gradeinfo(
    gradeID INT PRIMARY KEY AUTO_INCREMENT,
    gradeName VARCHAR(20)
);

INSERT INTO gradeinfo VALUES(NULL,'一年级'),(NULL,'2年级'),(NULL,'3年级');
INSERT INTO gradeinfo(gradename)VALUES('一年级'),('2年级'),('3年级');

修改数据

修改单表的记录

-- 语法
update 表名
set 列=新值,列=新值,...
where 筛选条件;

-- 案例1
UPDATE beauty 
set phone='138999999'
WHERE name like '唐%';

-- 案例2
UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;

修改多表的记录

-- sql92语法
update 表1 别名,表2 别名
set 列=值,....
WHERE 连接条件
and 筛选条件;

-- sql99语法
update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,....
WHERE 筛选条件;

-- 案例1
UPDATE boys bo
INNER JOIN beauty b on bo.id = b.boyfriend_id
SET b.phone=114
WHERE bo.boyName='张无忌';

删除数据

delete语句：语法：delete from 表名 where 筛选条件;
truncate语句：语法：truncate table 表名;

-- 案例1：删除姓李所有信息
DELETE FROM stuinfo WHERE stuname LIKE '李%';

-- 案例2：删除表中所有数据
TRUNCATE TABLE stuinfo ;

【面试题】delete和truncate的区别

delete可以添加WHERE条件
TRUNCATE不能添加WHERE条件，一次性清除所有数据
truncate的效率较高
如果删除带自增长列的表，
使用DELETE删除后，重新插入数据，记录从断点处开始
使用TRUNCATE删除后，重新插入数据，记录从1开始
delete 删除数据，会返回受影响的行数
TRUNCATE删除数据，不返回受影响的行数
delete删除数据，可以支持事务回滚
TRUNCATE删除数据，不支持事务回滚

事务

概念：由一条或多条sql语句组成，要么都成功，要么都失败

特性：ACID：原子性、一致性、隔离性、持久性

分类：

隐式事务：没有明显的开启和结束标记

     比如dml语句的insert、update、delete语句本身就是一条事务
     `insert into stuinfo values(1,'john','男','ert@dd.com',12);`

显式事务：具有明显的开启和结束标记

     一般由多条sql语句组成，必须具有明显的开启和结束标记

步骤：取消隐式事务自动开启的功能

开启事务
编写事务需要的sql语句（1条或多条）
insert into stuinfo values(1,'john','男','ert@dd.com',12);
insert into stuinfo values(1,'john','男','ert@dd.com',12);
结束事务

SHOW VARIABLES LIKE '%auto%'

-- 演示事务的使用步骤
-- 1、取消事务自动开启
SET autocommit = 0;

-- 2、开启事务
START TRANSACTION;

-- 3、编写事务的sql语句
-- 将张三丰的钱-5000
UPDATE stuinfo SET balance=balance-5000 WHERE stuid = 1;
-- 将灭绝的钱+5000
UPDATE stuinfo SET balance=balance+5000 WHERE stuid = 2;

-- 4、结束事务
-- 提交
commit;
-- 回滚
ROLLBACK;

SELECT * FROM stuinfo;

七天玩转MySQL

查询

group by

count(*)、count(1)、count(主键)、count(字段)的区别

含义区别

性能区别

结论

MySQL服务的连接和退出

常见sql命令演示

SQL语言介绍

基础查询

语法

执行顺序

起别名

mysql中+作用

distinct

查看表结构

ifnull(表达式1,表达式2)

条件查询

按关系表达式筛选

按逻辑表达式筛选

模糊查询

in查询

between and

is null/is not null

条件查询案例

排序查询

语法

执行顺序：

举例：

特点：

案例说明

按单个字段排序

按表达式排序

按别名排序

按函数的结果排序

按多个字段排序

补充选学：按列数排序

常见函数

字符函数

数学函数

日期函数

流程控制函数

案例讲解

聚合函数

分组查询

特点

简单的分组

可以实现分组前的筛选

可以实现分组后的筛选

可以实现排序

按多个字段分组

连接查询

分类：

按年代分类：

按功能分类：

sql-92标准

内连接

等值连接

非等值连接

自连接

sql-99标准

内连接

等值连接

非等值连接

自连接

外连接

测试题3

子查询

单行子查询(放在where或having后面)

多行子查询

放在select后面

放在from后面

放在exists后面

联合查询

分页查询

子查询案例

DDL语言

库的管理

创建数据库

mysql中`+`作用