2020/6/3 周三
语法结构:
- 注意:
*号代表表中所有字段,这个不建议使用,会影响查询效率;
SELECT 字段名,字段名 FROM 表名 WHERE 筛选过滤条件
- 查询表中所有数据库
SELECT * FROM student;
- 指定字段查询
SELECT 'name',address FROM student;
- AS 起别名
SELECT name AS 姓名,address AS 地址 FROM student;
- LIMIT使用
注意:limit 0,2 逗号前面表示从哪一行开始取值,第一行是0,逗号右边值取多少行
SELECT * FROM student WHERE id=901 LIMIT 0,2
- DISTINCT排除重复
SELECT DISTINCT department AS 院系,address FROM student;
-
单条件查询:
= != < > >= <=
SELECT * FROM student WHERE id>=905
-
多条件查询:
AND OR NOT
SELECT * FROM student WHERE id>=905 AND sex='女';
SELECT * FROM student WHERE id>=905 OR sex='男'
- 范围选择:
方式一:
SELECT * FROM student WHERE (2010-birth)>=20 AND (2010-birth)<=28;
方式二:BETWEEN AND
SELECT * FROM student WHERE 2010-birth BETWEEN 20 AND 28;
方式三:NOT BETWEEN AND
SELECT * FROM student WHERE 2010-birth NOT BETWEEN 20 AND 28;
- 集合操作:
1、or
SELECT * FROM student WHERE department='计算机系' OR department='英语系' OR sex='女'
2、in
SELECT * FROM student WHERE department IN ('计算机系','中文系')
3、not in
- 模糊匹配LIKE
_ 一个字符
SELECT * FROM student WHERE name LIKE '_三'
%任意字符 可以是0个
SELECT * FROM student WHERE address LIKE '辽宁%'
- 查询空号 is NULL
SELECT * FROM student WHERE name is NULL
- 常量列:
SELECT name as 姓名,address AS 地址,'希望小学' AS 学校 FROM student
- 排序
升序 ASC、降序 DESC
SELECT * FROM score WHERE c_name='计算机' ORDER BY grade ASC /DESC
- 聚合函数:
count 计数:
SELECT COUNT(grade) FROM score
max、min、sum 求和、sum 求和
SELECT stu_id AS 学号, MAX(grade) AS 成绩 FROM score WHERE c_name='计算机'
- 分组
- WHERE子句:
从数据源中去掉不符合其搜索条件的数据,必须跟在表名后
- GROUP BY子句:
搜集数据行到各个组中,统计函数为各个组计算统计值,每个组输出一行结果
- HAVING子句:
从分组计算结果中进行过滤,去掉不符合其组搜索条件的各组数据行
- 子查询:sql里面嵌套sql
SELECT id,name FROM student
WHERE id=(
SELECT stu_id FROM score WHERE c_name='计算机' AND grade=70);
- IN 后面可以赋多个值
SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name='中文')
- EXISTS 如果子查询返回结果为真 则执行前面sql ,如果为假 则前半句sql执行结果为空
SELECT * FROM score WHERE EXISTS (SELECT stu_id FROM score WHERE c_name='中文111')
- UNION 合并表
all 数据会重复
SELECT * FROM student_copy UNION all SELECT * FROM student;
不加all会去重
SELECT * FROM student_copy UNION SELECT * FROM student;
ANY、ALL