DML与DQL
DML 语言: 数据库操作语言
增
/*
方式一：
语法： insert into 表名（列名1，...） values(值1...);
注：
1. 插入的值的类型要与列的类型一致或兼容 2. 不可以为null的列必须插入值；可以为null的列有两种方式：1. 插入式加了列名，对应值写null;2. 插入时不加列名，对应value里面也不需要写，数据库默认为null或默认值; 3. 列的顺序可以调换，对应value也需要调换 4. 列数和值得个数必须一致 5. 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致 方式二：` 语法： insert into 表名 set 列名=值，列名=值...
方式一和方式二： 1. 方式一支持插入多行,方式二不支持 insert into 表名 VALUES(...),(...),... 2.方式一支持子查询,方式二不支持 INSERT INTO 表 SELECT value1,value2... UNION SELECT value1,value2...; */
删
/*
方式一： delete 单表删除： delete from 表名 where 筛选条件 多表删除 sql 92 delete 表1的别名,表2的别名 from 表 1 别名, 表2 别名 where 连接条件 and 筛选条件; sql99： delete 表1的别名,表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件; 案例： 删除Bob的女朋友的信息 DELETE g FROM girls g INNER JOIN boys b ON g.boyid=b.id WHERE b.name='Bob'; 案例： 删除Bob的信息以及他女朋友的信息 DELETE g,b FROM girls g INNER JOIN boys b ON g.boyid=b.id WHERE b.name='Bob'; 方式二： truncate，清空表 truncate table 表名; 注：
delete 和truncate
1. delete 可以加where条件， truncate不能加 2. truncate 删除，效率高一点 3. 假如要删除的表中有自增长列，使用delete删除胡，在插入数据，自增长列的值从断点开始；使用truncate删除后，在插入数据，自增长列的值从1开始。 4. truncate删除没有返回值，delete删除有返回值 5. truncate 删除不能回滚，delete删除可以回滚 */
改
/* 1.修改单表记录: 语法: update 表名 set 列=值，列=值... where 筛选条件; 2.修改多表记录: 语法： sql 92 update 表1 别名，表2 别名 set 列=值... where 连接条件 and 筛选条件; sql99： update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件; */
DQL 语言: 数据库查询语言
SELECT column_name,column_name FROM table_name [WHERE Clause] [OFFSET M ][LIMIT N]
- 查询语句中可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
- 你可以使用 LIMIT 属性来设定返回的记录数。
SELECT
/* show databases; use weekly; show tables;-- desc task; SELECT name FROM task; select * from weeklyinfo; SELECT id,name,owner,dueTime,'group' FROM task;
查询常量 SELECT 100; SELECT 'GARETH'; # 字符和日期必须用引号
查询表达式 SELECT 100*99;
查询函数 SELECT 函数名（实参列表） SELECT VERSION();
起别名： 便于理解，如果要查询的字段有重名的情况，使用别名可以区分开来:
两种方式: 使用AS 或者空格; 如果别名有空格则使用引号括起来; SELECT id, name AS 任务名, owner AS 责任人, 'group' AS 所属组 FROM task; SELECT id, name 任务名, owner 责任人, 'group' 所属组 FROM task; */
去重： DISTINCT
/* 去重： 查询任务表中所有责任人，责任人可能多条记录 SELECT DISTINCT owner FROM task; */
+号的作用
/* select 100+90: 两个操作数都为数值型，则做假发运算； select '123'+90: 其中一方为字符型，试图将字符型数值转换成数值型； 如果转换成功， 则继续做加法运算； select 'gareth'+90 如果转换失败，则将字符型数值转换成0 */
NULL 与 <=>
当where查询条件字段为NULL时，该命令可能无法正常工作，可采用如下运算符：
- IS NULL: 当列的值是NULL,此运算符返回true。
- IS NOT NULL: 当列的值不为NULL, 运算符返回true。
- <=>: 比较操作符，安全等于（不同于=运算符），当比较的的两个值为NULL时返回true。判断是否等于，如果等于则返True
- 关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值，即 NULL = NULL 返回false；等号不能判断NULL
- IS只能用于NULL 或 NOT NULL
# NULL 判断 SELECT CONCAT(owner, 'group', IFNULL('content', '空')) as 'out put' FROM task; # 案例： 查询content为NULL的记录 SELECT * FROM task WHERE content IS NULL; SELECT * FROM task WHERE content IS NOT NULL; SELECT * FROM task WHERE content<=>NULL; SELECT * FROM task WHERE id<=>10; /* is null 和 <=> is null： 仅仅可以判断NULL值; 可读性高 <=>： 既可以判断NULL值，又可以判断普通的值； 可读性低 */
条件查询
| 操作符 | 描述 | 实例 |
|---|---|---|
| = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 |
| <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 |
| > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 |
| < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 |
| >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 |
| <= | 小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |
使用主键来作为 WHERE 子句的条件查询是非常快速的。
/* 语法： SELECT 查询列表 FROM 表名 WHERE 筛选条件 分类： 按条件表达式筛选： > 、 < 、 = 、 != 、 <> 、>= 、 <=, 等号不能判断NULL值 逻辑表达式筛选： &&、 || 、 ！、 and or not 模糊查询： like/ between and / in / is null */
SELECT * FROM task WHERE id>10; SELECT name,owner FROM task WHERE owner='Gareth'; SELECT name,owner FROM task WHERE owner='Gareth' AND name='ddd';
#案例 查询 id 不在10到20之间，或者责任人是Gareth的记录 SELECT name,owner FROM task WHERE NOT(id>=10 AND id<=20) OR owner='Gareth';
模糊查询
/* like：通常与通配符搭配使用； like不能去匹配NULL，匹配失败;可以匹配字符型和数值型； %： 百分号，匹配任意多个字符，包含0个字符； _： 下划线，匹配任意单个字符 */ # 案例 查询任务名中包含 ‘模拟’ 的记录 SELECT * FROM task WHERE name LIKE '%模拟%'; SELECT * FROM task WHERE name LIKE "__模拟%"; # 转义 SELECT * FROM task WHERE name LIKE "_\_%"; # 匹配任务名第二个字符为下划线的记录 SELECT * FROM task WHERE name LIKE "_$_%" ESCAPE '$'; # 把$定义为转义符号，效果等同于上一条
between... and...
/* between and not between and 使用between and 可以提高语句的简洁度 查询结果包含临界值； 两个临界值不要调换顺序，等价于大于等于左边的值，小于等于右边的值 */ # 案例： 查询 id在10到20之间的记录 SELECT * FROM task WHERE id BETWEEN 10 AND 20;
in
/* in : 判断某字段的值是否属于in列表中的某一项; 提高语句简洁度; in列表的值必须一致或兼容（兼容 ('123',123)）; 列表内容不支持通配符表示 */ SELECT * FROM task WHERE name IN('ddddd','aaaaaa'); select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表)
常用函数：
/* concat函数： 拼接字符 ifnull函数： 判断某字段或表达式是否为null，如果为null返回指定的值，否则返回原本的值； ifnull(字段，指定值) isnull函数： 判断某字段或者表达式是否为null，如果是，则返回1，否则返回0 LENGTH函数： 调用： SELECT 函数名（实参列表） [ FROM 表] 分类： 单行函数： concat length ifnull 分组函数: 功能：做统计使用，又称为统计函数，聚合函数，组函数 */
CONCAT:
# 案例： 查询责任人和所属组连接成一个字段，并显示为 责任人组 SELECT CONCAT(owner,',','group') AS '责任人,组' FROM task;
字符函数
/* length:
select length('gareth') 获取参数值得字节数
concat:
select concat(name,'_',owner) from task; 字符拼接
upper, lower:
select concat(upper(name),'_',lower(owner)) from task; 大小写
substr: 索引从1开始, 截取字符 select substr('abcdefg', 3) out_put; # 截取指定索引处后面的所有字符（包括指定索引的字符），案例输出cdefg select substr('abcdefg',1,3) out_put # 截取从指定索引处指定字符长度的字符，案例输出：abc
instr: 返回子字符串第一次出现的索引，如果找不到返回0 select instr('str_sub_abcdefg', 'sub_abc') as out_put;
trim: 去掉字符串前后空格，也可以去掉指定字符 select trim(' abc ') as output; # 输出abc select trim('a' from 'aaaaabaaca') as out_put; 去电字符串前后的'a'， 输出baac select trim('aa' from 'aaaaabaaca) as out_put; 输出 abaaca ldap：用指定的字符实现左填充指定的长度, 如果字符串超过指定长度，则截断 rpad:用指定的字符实现右填充指定的长度 select lpad('strabcd', 10,'*') as out_put replace: 替换 select replace('abcdefg', 'abc','ggg') as output; 把abc替换成ggg */
数学函数
/* round() 四舍五入 select round(-1.55); select round(-1.555,2); 保留两位小数 ceil: 向上取整，返回大于或等于该参数的最小整数 floor: 向下取整 truncate: 截断 select truncate(1.6999,1); 返回1.6 mod 取余 mod(a,b) 等价于a-a/b*b select mod(10,3); select 10%3 */
日期函数
/* now() 返回当前系统日期+时间 curdate() 返回当前系统日期 curtime() 返回当前系统时间 DIFFRENCE('time1','time2') 返回两个日期相差的天数 select now(); 可以获取指定的部分，年(YEAR), 月(month, 或者 monthname)，日（），时（），分（），秒（） select YEAR(NOW()) 年; select YEAR("1993-09-01") 年; str_to_date 将字符串通过指定的格式转换成日期 select str_to_date('1998-01-02', '%Y-%c-%d'); date_format 将日期转换成字符 select date_format(now(), '%y年%m月%d日')； */
其他函数
version(); database(); user();
流程控制函数
/*
if函数， if else select task,owner,IF(coment is null,'值，正确', '值2，错误') 备注 FROM task;
case函数， 使用一： switch case 的效果 case 要判断的字段或表达式 WHEN 常量1 THEN 要显示的值1或语句1 WHEN 常量2 THEN 要显示的值2或语句2 ... ELSE 要显示的值n或语句n end
SELECT name,owner,group_id CASE group_id WHEN 1 THEN "AAAAA" WHEN 2 THEN "BBBBB" ELSE 'DDDDD' END AS 备注 FROM task;
使用二：相当于if elif .. else case when 条件1 then 要显示的值1或语句1 when 条件2 then 要显示值2或语句2 else 要显示的值n或语句n end
SELECT salary, CASE WHEN salary>2000 THEN 'A' WHEN salary>1500 THEN "B" WHEN salary>1000 THEN 'C' ELSE 'D' END CASE 工资级别 FROM employees; */
分组函数
/*
功能： 用作统计使用，又称为聚合函数或统计函数或组函数
sum() 求和 AVG MIN MAX COUNT
特点: sum 和 avg 一般用于处理数值型 max min count 可以处理任何类型
以上分组函数都忽略null值 COUNT函数单独介绍，一般使用COUNT(*) 统计行数 和分组函数一同查询的字段要求是group by后的字段
简单使用： select sum(salary) from tb; select avg(salary) FROM tb; SELECT MIN(salary) FROM tb; SELECT MAX(salary) FROM tb; SELECT COUNT(salary) FROM tb; SELECT SUM(salary) 和, AVG(salary) 平均,COUNT(salary) 个数 FROM tb;
参数支持哪些类型： sum('aaadsd') 返回0 count 计算不为none的个数 可以和distinct搭配：SELECT SUM(DISTINCT salary), SUM(salary) FROM tb; COUNT 详细说明 一般使用COUNT(*)统计行数 SELECT COUNT(salary) FROM tb; SELECT COUNT(*) FROM tb; SELECT COUNT(1) FROM tb;
效率问题： MYISAM 存储引擎下，COUNT(*) 的效率高 INNODB 存储引擎下， COUNT(1)和COUNT(*)效率差不多，比COUNT(字段)高 */
分组查询
/* 分组查询： 可以使用GROUP BY 字句将表中的数据分成若干组
分组数据： GROUP BY 字句语法 语法： SELECT column, group_function(column) FROM tb [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];
注意：
WHERE 一定放在FROM后面；查询列表必须特殊，要求是分组函数和group by后出现的字段
特点： 分组查询中的筛选条件分为两类： 分组前筛选 对原始表数据 GROUP BY 字句前面 WHERE 关键字 分组后筛选 对分组后的结果集 GROUP BY 子句后面 HAVING 关键字 分组函数做条件肯定是放在HAVING 字句中 能用分组前筛选的，优先考虑使用分组前筛选 GROUP BY 子句支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式或函数（用的较少） 也可以添加排序，排序放在最后 # 案例 查询每个工种的最高工资 SELECT MAX(salary), job_id FROM tb GROUP BY job_id; # 案例 查询每个位置上的部门的个数 SELECT COUNT(*), location_id FROM tb GROUP BY location_id;
添加分组前的筛选 # 案例 查询邮箱中包含a字符的，每个部门的平均工资 SELECT AVG(salary), department_id, department_id FROM employees WHERE email link '%a%' GROUP BY department_id;
添加分组后的筛选条件： HAVING # 案例 查询哪个部门的员工人数大于2 SELECT COUNT(*)，department_id FROM tb GROUP BY department_id HAVING COUNT(*)>2;
# 案例 查询每个工种有奖金的员工的最高工资大于12000 的工种编号和最高工资 SELECT MAX(salary),job_id FROM tb WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;
# 案例 查询领导编号大于100的每个领导手下员工的最低工资大于5000的领导编号以及其最低工资 SELECT MIN(salary), manager_id FROM tb WHERE manage_id>100 GROUP BY manager_id HAVING MIN(salary) > 5000;
按表达式或者函数分组 #案例 按员工姓名的长度分组，查询每一组员工的员工个数，筛选员工个数大于5的有哪些 SELECT COUNT(*), name FROM tb GROUP BY LENGTH(name) HAVING COUNT(*) > 5; SELECT COUNT(*) c, name FROM tb GROUP BY LENGTH(name) HAVING c> 5; 按多个字段分组 # 案例 查询每个部门每个工种的员工的平均工资 SELECT AVG(salary), department_id, job_id FROM tb GROUP BY department_id,job_id;
添加排序： # 案例 查询每个部门每个工种的员工的平均工资, 并且按平均工资的高低显示 SELECT AVG(salary), department_id, job_id FROM tb GROUP BY department_id,job_id ORDER BY AVG(salary) DESC; SELECT AVG(salary), department_id, job_id FROM tb WHERE department_id is not null GROUP BY department_id,job_id HAVING AVG(salary)>10000 ORDER BY AVG(salary) DESC; */
连接查询
/*
又称多表查询，当查询的字段来自多个表时，就会用到连接查询。 笛卡尔乘积： 表1 有m行，表2 有n行，结果=m*n行 发生原因： 没有有效的连接条件 如何避免： 添加有效的连接 添加有效的连接条件分类： 按年代分类： sql92标准： 仅仅支持内连接 sql99标准： 推荐 按功能分类： 内连接： 等值连接： 非等值连接： 自连接： 外连接： 左外连接： 右外连接： 全外连接： 交叉连接：
语法： SELECT name,boyName from beauty,boys; SELECT name,boyName from beauty,boys WHERE beauty.boys_id=boys.id; sql92标准 1.等值连接 多表等值连接的结果为多表的交集部分 n表连接，至少需要n-1个连接条件 多表的顺序没有要求 一般需要为表起别名 可以搭配前面介绍的所有子句使用，比如排序，分组等 1. 等值连接： SELECT name,boyName from beauty,boys WHERE beauty.boys_id=boys.id;
# 查询员工名和对应的部门名 SELECT name,department_name FROM employees,departments WHERE employees.department_id=departments.id;
2. 为表起别名 提高语句的简洁度，区分多个重名的字段 注意： 如果为表起了别名，则查询的字段就不能使用原来的表名去限定
# 查询员工名，工种号，工种名 SELECT e.name,e.job_id,j.job_name FROM employees e, jobs j WHERE e.job_id=j.id;
3. 两个表的顺序是可以调换的 SELECT e.name,e.job_id,j.job_name FROM jobs j, employees e WHERE e.job_id=j.id;
4. 可以加筛选 # 案例 查询有奖金的员工名，部门名 SELECT name, department_name FROM employees e, deportments d WHERE e.department_id=d.id AND e.commission_pct IS NOT NULL; # 案例 查询城市名中第二个字符为o的部门名或城市名 SELECT d.department_name,c.city_name FROM department d, city c WEHER city_name LIKE '_o%' AND d.city_id=c.id;
5. 可以加分组 # 查询每个城市的部门个数 SELECT count(*),city_name FROM departments d,citys c WHERE d.city_id=c.id GROUP BY city_name; # 案例 查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资。 SELECT department_name, d.manager_id,MIN(salary) FROM employees e,departments d WHERE e.commission_pct IS NOT NULL AND d.id=e.department_id GROUP BY d.department_name, manager_id; 注： GROUP BY后面加manage_id，是因为manage_id和部门不一定是一一对应。如果是不加也可以
6. 可以加排序 # 案例 查询每个工种的工种名和员工的个数，并且按员工的个数降序 SELECT job_name, COUNT(*) FROM job j, employees e WHERE e.job_id=j.id GROUP BY job_name ORDER BY COUNT(*) DESC； 7. 三表连接 # 查询员工名，部门名，和所在城市 SELECT name,department_name,city_name FROM employees e,departments d,city c WHERE e.department_id=d.id and d.city_id=c.id; 2。 非等值连接 # 案例 查询员工的工资和工资级别 SELECT salary, grade_level FROM employees e,job_grades g WHERE salary BETWEEN g.lowest_sal AND g.highest_sal; 3. 自连接 # 案例 查询员工名和上级的名称 SELECT e.employee_id, e.name,m.employee_id,m.name FROM emplyoees e, employees m WHERE e.employee_id=m.employee_id; sql99语法： 语法： select 查询列表 from 表1 别名 【连接类型】 join 表2 别名 on 连接条件 【WHERE】 【GROUP BY】 [HAVING] [ORDER BY] 分类： 内连接： inner 外连接 左外 left 【outer】 右外 right [outer] 全外 full [outer] 交叉连接 cross 内连接 语法： SELECT 查询列表 FROM 表1 别名 inner join 表2 别名 on 连接条件; 分类： 等值 非等值 自连接 特点： 添加排序，分组，筛选 inner可以省略 筛选条件放在WHERE后面，连接条件放在ON后面，提高分离性，便于阅读 inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集 案例： 等值连接 查询员工名、部门名 SELECT name,department_name FROM employees e INNER JOIN departments d ON e.department_id=d.id; 查询名字中包含e的员工名和工种名（添加筛选条件） SELECT name,job_name FROM emlopyees e INNER JOIN jobs j ON e.job_id=j.id WHERE e.name like '%e%'; 查询部门个数大于3的城市名和部门个数 SELECT city_name,COUNT(*) FROM depaments d INNER JOIN city c ON c.id=d.city_id GROUP BY city_name HAVING COUNT(*)>3; 查询哪个部门的员工个数大于3的部门名和员工个数，并按个数降序（添加排序） SELECT department_name,COUNT(*) FROM employees e INNER JOIN departments d ON e.department_id=d.id GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC; 查询员工名，部门名，工种名，并按部门名降序； SELECT name,d.name,j.name FROM employees e INNER JOIN departments d ON e.department_id=d.id INNER JOIN jobs j ON j.id=e.job_id ORDER BY department DESC; 案例： 非等值连接 查询员工的工资级别 和工资 SELECT salary, grade_level FROM employees e JOIN grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal; 查询工资级别的个数大于2，并且按工资级别降序； SELECT grade_level, COUNT(*) FROM emlopyees e JOIN grades g ON e.salary BETWEEN g.lowest_sal AND g.highest_sal GRADE BY grade_level HAVING COUNT(*)>2 ORDER BY grade_level DESC; 案例： 自连接 查询员工的名字，上级的名字 SELECT e.name, m.name FROM employees e JOIN employees m ON e.manage_id=m.id; 外连接 应用场景： 用于查询一个表中有，另一个表没有的记录 特点： 外连接的查询结果为主表中的所有记录： 如果从表中有和它匹配的，则显示匹配的值； 如果从表中没有和它匹配的，则显示null; 外连接查询结果=内连接结果+主表中有而从表中没有的记录 左外连接： left join, 左边的是主表 右外连接: right join, 右边的是主表 左外和右外交换两个表的顺序，可以实现同样的效果 全外连接=内连接的结果+表1中有但表2中没有 + 表2中有，但表1中没有 案例： SELECT g.name, b.* FROM girls g LEFT OUTER JOIN boys b ON g.boy_id=b.id; SELECT g.name, b.* FROM girls g LEFT OUTER JOIN boys b ON g.boy_id=b.id WHERE b.id IS NULL; 查询哪个部门没有员工 SELECT d.*,e.id FROM departments d LEFT OUTER JOIN employees e ON e.department_id=d.id WHERE e.id IS NULL; SELECT d.*,e.id FROM employees e RIGHT departments d ON e.department_id=d.id WHERE e.id IS NULL; 查询部门名为SAL或IT的员工信息 SELECT e.*,d.name FROM departments d LEFT JOIN employees e ON e.department_id=d.id WHERE d.name IN ['SAL', 'IT']; 全外连接 交叉连接： 结果是笛卡尔积 SELECT b.*,g.* FROM girls g CROSS JOIN boys b; sql99和sql92 PK 内连接： 交集 外连接： 查询主表的所有记录*/
子查询
/* 出现在其他语句内部的select语句，称为子查询或内查询 内部嵌套其他select语句的查询，称为外查询或主查询 分类： 按子查询出现的位置： SELECT 后面： 仅仅支持标量子查询 FROM 后面： 支持表子查询 WHERE 或HAVING 后面： 标量子查询（单行），列子查询（多行），行子查询 EXISTS 后面： 表子查询 按结果集的行列数不同： 标量子查询（结果集只有一行一列） 列子查询（结果集只有一列多行） 行子查询（结果集有一行多列） 表子查询（结果集一般为多行多列） 一、 WHERE 或 HAVING后面的 特点： 子查询放在小括号内； 子查询一般放在条件的右侧； 标量子查询，一般搭配着单行操作符使用： > < >= <= <> 列子查询：，一般搭配着多行操作符使用： in any/some all 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果 1. 标量子查询（单行子查询） 案例： 查询谁的工资比Abel高 SELECT * FROM employees WHERE salary>( SELECT salary FROM employees WHERE name=Abel )； 案例： 返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资 SELECT 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 ) 案例： 返回公司工资最少的员工的name,job_id和salary SELECT name,job_id,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); 案例： 查询最低工资大于50号部门最低工资的部门id和其最低工资 SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>( SELECT MIN(salary) FROM employees WHERE department_id=50); 非法使用标量子查询，结果是一行一列 2. 列子查询（多行子查询： IN 等价于 =ANY NOT IN 等价于 <>ALL ANY 可以和 MIN换， ALL可以和MAX换 案例： 返回location_id 是1400或1700的部门中的所有员工姓名 SELECT name, department_id FROM employees WHERE department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700) ) 或 SELECT name, department_id FROM employees WHERE department_id =ANY ( SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400, 1700) ) 案例： 返回其他工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工工号，姓名，job_id以及salary. SELECT name,employee_id,job_id，salary FROM employees WHERE salary<ANY( SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG'; 或 SELECT name,employee_id,job_id，salary FROM employees WHERE salary<( SELECT MAX(salary) FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG'; 案例： 返回其他工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工工号，姓名，job_id以及salary. SELECT name,employee_id,job_id，salary FROM employees WHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG'; 或 SELECT name,employee_id,job_id，salary FROM employees WHERE salary<( SELECT MIN(salary) FROM employees WHERE job_id='IT_PROG' ) AND job_id<>'IT_PROG'; 3. 行子查询（多列多行或一行多列） 案例： 查询员工编号最小并且工资最高的员工信息 SELECT * FROM employees WHERE (employee_id,salary)=( SELECT MIN(employee_id),MAX(salary) FROM employees ); 二、 SELECT 后面： 仅仅支持标量子查询 案例： 查询每个部门的员工个数 （内连接也可以实现） SELECT d.*,( SELECT COUNT(*) FROM employees WHERE e.department_id=d.id ) 个数 FROM department d； 案例： 查询员工号=102的部门名 SELECT ( SELECT department_name FROM departments d WHERE d.id=e.id) FROM employees e WHERE e.id=102; 或 SELECT department_name FROM department INNER JOIN employees e ON d.department_id=e.department_id WHERE e.id=102; 三、FROM 后面： 将子查询结果充当一张表，要求必须起别名 支持表子查询 案例： 查询每个部门的平均工资的工资等级 SELECT ag_dep.*,g.grade_level FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; 四、EXISTS 后面（相关子查询）： 表子查询 语法： exists (完整的语句) 结果： 0或1 案例： 查询有员工的部门名 SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.department_id=e.department_id ); 或 SELECT department_name FROM departments d WHERE d.department_id IN (SELECT DISTINCT department_id FROM employees e); 案例： 案例1： 查询和Bob 相同部门的员工姓名和工资 SELECT name,salary FROM employees e WHERE e.department_id=( SELECT department_id FROM employees WHERE name=Bob ); 案例2： 查询工资比公司平均工资高的员工的员工号，姓名和工资 SELECT id,name,salary FROM employees WHERE salary > ( SELECT avg(salary) FROM employees ); 案例3： 查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资 SELECT id,name,salar FROM employees e INNER JOIN (SELECT AVG(salary) ag,department_id FRMO employees GROUP BY departmen_id) ag_dep ON e.department_id=ag_dep.department_id WHERE e.salary>ag_dep.ag; 案例4： 查询在部门的location_id 为1700的部门工作的员工的员工号 SELECT id,name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location_id=1700 ); 案例5： 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名 SELECT id,name FROM employees WHERE department_id IN ( SELECT DISTINCT department_id FROM employees WHERE name like '%u%' ); 案例6： 查询管理者是Kin的员工姓名和工资 SELECT name,salary FROM employees WHERE manage_id IN ( SELECT id FROM employees WHERE name='Kin' ) 案例7： 查询工资最高的员工的姓名，要求姓和名显示为一列，列名为 姓.名 SELECT CONCAT(first_name,'.',last_name) '姓.名' FROM employees WHERE salary=( SELECT MAX(salary) FROM employees ); */
分页查询
/* 应用场景: 当要显示的数据，一页显示不全，需要分页提交sql请求 语法： SELECT 查询列表 FROM 表 [ join type join 表2 ON 连接条件 WHERE 筛选条件 GROUP BY 分组字段 HAVING 分组后筛选 ORDER BY 排序 ] LIMIT offset,size; offset： 要显示条目的其实索引，从0开始 size: 要显示的条目个数 特点： LIMIT 语句放在查询语句的最后； 要显示的页数page, 每页条目数size: SELECT 查询列表 FROM 表 LIMIT （page-1）*size,size; 案例： 查询前五条员工信息 SELECT * FROM employee LIMIT 0,5; 或 SELECT * FROM employees LIMIT 5; 查询第11条到第25条 SELECT * FROM employees LIMIT 10,15; 查询有奖金的员工信息，并且工资较高的前10名显示出来 SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10; */
联合查询
/* union 联合 合并：将多条查询语句的结果合并成一个结果 语法： 查询语句1 union 查询语句2 union ... 应用场景： 要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时 特点： 要求多条查询语句的查询列数是一致的 要求多条查询语句的查询的每一列的类型和顺序最好一致 union 关键字默认去重，如果使用union all 可以包含重复项 案例： 查询部门编号大于90或邮箱包含a的员工信息 SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90; SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90; */
排序查询
/* 语法： SELECT 查询列表 FROM 表 WHERE 筛选条件 ORDER BY 排序列表 [ASC|DESC] 排序列表：可以为字段，可以为表达式，也可以按别名排序，也可以是函数 DESC 降序 ASC 升序，默认是升序 */ SELECT * FROM task ORDER BY owner DESC; # 案例： 查询id大于等于90，按截止时间先后排序, 按字段排序 SELECT * FROM task WHERE id>=90 ORDER BY due_time ASC; # 案例： 查询id大于等于90，按截止时间先后排序, 按别名 SELECT owner '责任人' FROM task WHERE id>=90 ORDER BY '责任人' DESC; # 案例 按任务名的长度显示任务信息 SELECT LENGTH(name) 任务名长度,owner FROM task ORDER BY LENGTH(name) DESC; # 案例： 查询任务信息，先按owner排序，再按id排序，即多个字段排序 SELECT * FROM task ORDER BY owner ASC,id DESC;
实例
/* 1. 试问下面两条语句运行结果是否一样 SELECT * FROM task; 和 SELECT * FROM task WHERE content LIKE "%%" AND name like "%%"; 答： 不一样；如果判断的字段有NULL值，like匹配失败；如果没有NULL值，则结果一样。 2. 已知表stuinfo: 学号（id）、姓名（name）、邮箱（email）、gradeID(年级编号)、性别（sex）、age(年龄) grade: 年级编号（id）、年级名称（gradeName） 查询所有学员的邮箱的用户名（注：邮箱中@前面的字符） SELECT SUBSTR(email,1, INSTR(email,'@')-1) FROM stuinfo; 查询男生和女生的个数 SELECT COUNT(*), sex FROM stuinfo GROUP BY sex; 查询年龄大于18的所有学生的姓名和年级名称 SELECT naem, g.gradeName FROM stuinfo s INNER JOIN grade g ON s.gradeID=g.id WHERE s.age>18; 查询哪个年级的学生最小年龄大于20； SELECT MIN(AGE), gradeID FROM stuinfo group by gradeID having MIN(age) >20; 试说出查询语句中涉及到的所有关键字，以及执行先后顺序 SELECT 查询列表 7 FROM 表 1 join type join 表2 2 ON 连接条件 3 WHERE 筛选条件 4 GROUP BY 分组字段 5 HAVING 分组后筛选 6 ORDER BY 排序 8 LIMIT offset,size; 9 3. 查询工资最低的员工信息： name, salary SELECT name, salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); 4. 查询平均工资最低的部门信息 SELECT d.* FROM departments d WHERE d.department_id=( SELECT department_id FROM employees GROUP BY department HAVING AVG(salary)=( SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep) ); 或 SELECT d.* FROM department d WHERE d.department_id =( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1 ); 5. 查询平均工资最低的部门信息和该部门的平均工资 SELECT d.*,ag FROM department d JOIN ( SELECT AVG(salary), department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1 ) ag_dep ON d.department_id=ag_dep.department_id; 6. 查询平均工资最高的job信息 select * from jobs WHERE job_id=(SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) desc LIMIT 1); 7. 查询平均工资高于公司平均工资的部门有哪些 SELECT AVG(salary), department_id FROM employees GROUP BY dapartment_id HAVING AVG(salary) > ( SELECT AVG(salary) FROM employees ); 8. 查询公司中所有manager的信息 SELECT * FROM employees WHERE id= ANY (SELECT DISTINCT manage_id FROM employees); 9. 各部门中最高工资中最低的那个部门的最低工资是多少 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 ) 10. 查询平均工资最高的部门的manager的详细信息： name, id，salary SELECT name,id,salary FROM employees e INNER JOIN departments d ON d.manager_id=e.id WHERE d.department_id=( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) desc LIMIT 1); 11. 查询每个专业的学生人数 SELECT majorid,COUNT(*) FROM student GROUP BY majorid; 12. 查询参加考试的学生中，每个学生的平均分，最高分 SELECT AVG(score),MAX(score),studentno FROM result GROUP BY studentno; 13. 查询姓张的每个学生的最低分大于60的学号，姓名 SELECT s.studentno,s.name,MIN(score) FROM student s JOIN result r ON s.studentno=r.studentno WHERE s.name LIKE '张%' GROUP BY s.studentno HAVING MIN(score)>60; 14. 查询生日在1988-1-1后的学生姓名，专业名称 SELECT name,majorname FROM student s JOIN major m ON s.majorid=m.id WHERE DATEDIFF(borndate,'1988-1-1')>0; 15. 查询每个专业的男生人数和女生人数分别是多少 SELECT COUNT(*), sex, majorid FROM student GROUP BY sex,majorid; 或 SELECT majorid, ( SELECT COUNT(*) FROM stuent WHERE sex='男' AND majorid=s.majorid ) 男，( SELECT COUNT(*) FROM student WHERE sex='女' AND majorid=s.majorid )女 FROM student s GROUP BY majorid; 16. 查询专业和Bob一样的学生的最低分 SELECT MIN(score) FROM result WHERE studentno IN( SELECT studentno FROM student WHERE majorid=( SELECT majorid FROM student WHERE name = 'Bob' ) ); 17. 查询大于60分的学生姓名、密码、专业名 SELECT name,passwd,majorname FROM student s JOIN major m ON s.majorid=m.majorid JOIN result r ON s.studentno = r.studentno WHERE r.score>60; SELECT name,passwd,majorname FROM student s JOIN major m ON s.majorid=m.majoeid WHERE s.studentno IN (SELECT studentno FROM result WHERE score > 60); 18. 按邮箱位数分组，查询每组的学生个数 SELECT COUNT(*),LENGTH(email) FROM student GROUP BY LENGTH(email); 19. 查询学生名、专业名、分数 SELECT studentname,score,majorname FROM student s JOIN major m ON s.majorid=m.majorid LEFT JOIN result r ON s.studentno=r.studentno; 20. 查询哪个专业没有学生，分别用左连接和右连接实现 SELECT m.majorid,m.majorname FROM major m LEFT JOIN student s ON m.majorid=s.majoeid WHERE s.studentno IS NULL; SELECT m.majorid,m.majorname FROM student s RIGHT JOIN major m ON m.majorid=s.majoeid WHERE s.studentno IS NULL; 21. 查询没有乘积的学生人数。 SELECT COUNT(*) FROM student s LEFT JOIN result r ON s.studentinfo=r.studentinfo WHERE r.id IS NULL; */
