1. 基础查询
语法：
select 查询列表 from 表名；
- 查询列表：表的字段、常量、表达式、函数
- 查询结果是虚拟表格
#查询单字段
SELECT name FROM stu;
#查询多字段
SELECT id,name FROM stu;
#查询常量
SELECT 100;
SELECT 'json';
#查询别名,联表查询也可对表用别名
SELECT 100 AS “常量”;
#去重
SELECT DISTINCT class FROM stu;
#查询两字段拼接(+运算符)
SELECT CONCAT(last_name,first_name) AS "姓名" from stu;
2. 条件查询
语法：
select 查询列表
from 表名
where 筛选条件；
分类：
-
条件表达式。条件运算符：
- >
- <
- =
- !=
- <>
- >=
- <=
-
逻辑表达式(用于连接多个条件筛选)。逻辑运算符：
&&
||
!
and
or
not
-
模糊查询：
- like
- between and (包含临界值)
- in
- is null
- is not null
SELECT * FROM stu WHERE id > 100;
SELECT * FROM stu WHERE id <> 100;
SELECT * FROM stu WHERE id > 100 and id < 200;
SELECT * FROM stu WHERE name LIKE '%李%';# %通配符 _任意单个字符
SELECT * FROM stu WHERE name LIKE '\%李\%'';# 转义
SELECT * FROM stu WHERE name LIKE '李$_' ESCAPE '$';# 自定义转义符为$
SELECT * FROM stu WHERE name IN('zhangsan','lisi');
SELECT * FROM stu WHERE name IS NULL; # = <> 不能判断null值 <=>安全等于可以判断null
3. 排序查询
语法：
select 查询列表
from 表名
where 筛选条件
order by 排序列表【asc | desc】；
asc：升序（默认） desc：降序
SELECT * FROM stu WHERE id > 100 ORDER BY name DESC;
SELECT * FROM stu WHERE id > 100 ORDER BY name ASC;
SELECT * FROM stu WHERE id > 100 ORDER BY name;
#按别名排序
SELECT *, mouth_cost*12*(1+ IFNULL(other_cost,0)) AS cost FROM stu ORDER BY cost;
#按函数排序
SELECT * ,LENGTH(name) AS "长度" FROM stu ORDER BY LENGTH(name);
#多个字段排序
SELECT * FROM stu ORDER BY name ASC,mouth_cost DESC;
4. 常见函数
-
单行函数
-
字符函数
LENGTH：获取参数值字节个数
CONCAT：字符串拼接
UPPER：转大写字母
LOWER：转小写字母
SUBSTR/SUBSTRING：字符串截取，索引从1开始
INSTR：返回子串第一次出现索引，没有返回0
TRIM：去掉前后空格。也可指定去掉前后某参数 TRIM('a' FROM 'aaaaHELLOaaaa')
LPAD: 用指定的字符与长度左填充
RPAD: 用指定的字符与长度右填充
-
REPLACE: 替换
SELECT LENGTH('hello world'); #11 SELECT LENGTH('你好');#6 SELECT CONCAT('hello',' ','woeld');#hello world SELECT UPPER('hello world'); #HELLO WORLD SELECT LOWER('HELLO WORLD'); #hello world SELECT SUBSTR('hello world',6);#world SELECT SUBSTRING('hello world',6);#world SELECT SUBSTR('hello world',1,4);#hell SELECT INSTR('hello world','wo');#7 SELECT INSTR('hello world','wao');#0 SELECT TRIM(' hello world ');#hello world SELECT TRIM('a' FROM 'aaaahello worldaaaaaaa');#hello world SELECT LPAD('hello',10,'*');#*****hello SELECT RPAD('hello',10,'*');#hello***** SELECT REPLACE('aabbcc','aa','dd');#ddbbcc
-
-
数学函数
ROUND：四舍五入
CEIL：向上取整
FLOOR：向下取整
TRUNCATE：截断
-
MOD：取余
SELECT ROUND(1.2);#1 SELECT ROUND(1.5);#2 SELECT ROUND(1.2365,2);#1.24 SELECT CEIL(1.2);#2 SELECT FLOOR(1.5);#2 SELECT TRUNCATE(1.2399,2);#1.23 SELECT MOD(5,2);#1
-
日期函数
NOW：返回当前系统日期+时间
CURDATE：返回当前系统日期
CURTIME：返回当前系统时间
YEAR MONTH DAY HOUR MINUTE SECOND ：年月日时分秒
STR_TO_DATE：字符串转日期
DATE_FORMAT： 日期转字符串
-
DATEDIFF：日期差
SELECT NOW();#2021-08-18 14:31:41 SELECT CURDATE();#2021-08-18 SELECT CURTIME();#14:31:41 SELECT YEAR(NOW());#2021 SELECT MONTH(NOW());#8 SELECT MONTHNAME(NOW());#August SELECT DAY(NOW());#18 SELECT HOUR(NOW());#14 SELECT MINUTE(NOW());#31 SELECT SECOND(NOW());#41 SELECT STR_TO_DATE('2021-1-2','%Y-%c-%d');#2021-01-02 SELECT DATE_FORMAT('2021-01-02','%Y年%c月%d日');#2021年1月02日 SELECT DATEDIFF('2020-2-3','2020-2-1');#2
-
其他函数
VERSION：查询版本号
DATABASE：查询数据库
-
USER：查询用户
SELECT VERSION();#5.6.31 SELECT DATABASE();#mysql SELECT USER();#root@192.168.164.1
-
流程控制函数
if函数
-
case函数
SELECT IF(2 > 1,'2>1 is true','2>1 is false');#2>1 is true /* CASE 语法一 case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end CASE 语法二 case when 条件1 then 要显示的值1或语句1; when 条件2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end */ SELECT class_id,name, CASE class_id WHEN 1 THEN score + 1 WHEN 2 THEN score + 2 ELSE score END AS '最终成绩' FROM stu SELECT class_id,name, CASE WHEN score > 90 THEN 'A' WHEN score > 60 THEN 'c' ELSE 'd' END AS '最终成绩' FROM stu
-
分组函数（统计函数、聚合函数、组函数）（多行数据对应一行输出）
SUM：求和，处理数值型，null不参与运算
AVG：平均值，处理数值型，null不参与运算
MAX：最大值，处理任何数值，null不参与运算
MIN：最小值，处理任何数值，null不参与运算
COUNT：个数，处理任何数值，null不参与运算
SELECT SUM(salary) FROM employees; SELECT AVG(salary) FROM employees; SELECT MAX(salary) FROM employees; SELECT MIN(salary) FROM employees; SELECT COUNT(salary) FROM employees; SELECT COUNT(DISTINCT salary) FROM employees;特点：
* 可以和DISTINCT 配合使用 * null不参与运算 * COUNT(*) MYISAM 效率高，INNODB下和COUNT(1)效率差不多 * 和分组函数一同查询的字段要求是GROUP BY 后的字段5. 分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表名 【where 分组前筛选条件】 group by 分组列表/函数分组 【HAVING 分组后筛选条件】 【order by 排序列表【asc | desc】】；#查询各个部门的平均工资 SELECT AVG(salary),department_id FROM employees ORDER BY department_id; #查询各个部门的平均工资，且平均工资大于1000 #HAVING对分组后进行条件筛选 SELECT AVG(salary),department_id FROM employees HAVING AVG(salary) > 10000 ORDER BY department_id;链表查询
sql99语法：
select 查询列表 from 表名1 别名 join 表名2 别名 on 连接条件 【where 筛选条件】 【group by 分组列表/函数分组】 【HAVING 分组后筛选条件】 【order by 排序列表【asc | desc】】；内连接：inner 左外连接：left 【outer】 右外连接：right 【outer】 全外：full 【outer】 交叉：cross-
内连接
-
等值连接
#sql92 SELECT name,department_name FROM employees e,department d #笛卡尔积 WHERE e.department_id = d.id; #sql99 SELECT name,department_name FROM employees e INNER JOIN department d ON e.department_id = d.id; -
非等值连接
#sql92 #查询员工工资和工资级别 SELECT salary,grade_level FROM employees e,job_grade g WHERE e.salary BETWEEN g.lowest_sal and g.Highest_sal; #sql99 #查询员工工资和工资级别 SELECT salary,grade_level FROM employees e INNER JOIN job_grade g ON e.salary BETWEEN g.lowest_sal and g.Highest_sal; -
自连接
#sql92 # 查询员工和领导姓名 SELECT e.name,m.name FROM employees eemployees m WHERE e.manager_id = m.id; #sql99 # 查询员工和领导姓名 SELECT e.name,m.name FROM employees e INNER JOIN employees m ON e.manager_id = m.id;
-
-
外连接(sql99)
特点：
外连接的查询结果是为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值 如果从表中没有和它匹配的，则显示null 左右外连接查询结果=内连接查询结果+主表中有而从表没有的记录 全连接查询结果=内连接查询结果+表1中有但表2没有的记录+表2中有但表1没有的记录左外连接，left join左边的是主表
右外连接，right join右边的是主表
左外和右外交换两个表的顺序，可以实现同样的效果
-
左外连接
#查询哪个部门没有员工 SELECT d.*,e.employee_id FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.department_id IS NULL; -
右外连接
#查询哪个部门没有员工 SELECT d.*,e.employee_id FROM employees e RIGHT JOIN departments d ON d.department_id = e.department_id WHERE e.department_id IS NULL; 全外连接
-
交叉连接（笛卡尔积）
总结：
SQL连接查询
7.子查询
含义：出现在其他语句中的select语句，称为子查询或内查询。外部的查询语句，称为主查询或外查询
特点：子查询放在小括号内
子查询一般放在条件的右侧
标量子查询，一般搭配着单行操作符使用
列子查询，一般搭配着多行操作符使用(in any/some all)
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);
8.分页查询
#查询第11-15条
SELECT *
FROM employees
LIMIT 10,5;
9.联合查询
union 含义：将多条查询结果合并为一个结果
特点：
- 要求多条件查询语句的查询列数是一致
- 要求多条件查询语句的查询的每一列的类型和顺序最好一致
- union关键字默认去重，如果使用union all 可以包含重复项
SELECT * FROM employees WHERE id='1'
UNION
SELECT * FROM employees WHERE id='2'
