果子日常笔记八（MYSQL查询语言）

*DQL数据查询语言
/
查询会产生一张虚拟表
语法结构：
注意：*号代表表中所有字段，这个不建议使用，会影响查询效率；
SELECT 字段名,字段名 FROM 表名 WHERE 筛选过滤条件

/
– 查询表中所有数据库
– SELECT * FROM student;
– 指定字段查询
– SELECT ‘name’,address FROM student;
– AS 起别名
– SELECT name AS 姓名,address AS 地址 FROM student;
– 不用as 起别名
– SELECT name 姓名,address 地址 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
SELECT * FROM student WHERE department 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
/
– SELECT * FROM score
/
排序
升序 ASC
SELECT * FROM score WHERE c_name=‘计算机’ ORDER BY grade ASC
降序 DESC
SELECT * FROM score WHERE c_name=‘计算机’ ORDER BY grade DESC
*/
聚合函数：
count 计数：
SELECT COUNT(grade) FROM score
max
求计算机系学生的最高分
SELECT stu_id AS 学号, MAX(grade) AS 成绩 FROM score WHERE c_name=‘计算机’
min 求计算机系学生的最低分
SELECT stu_id AS 学号, min(grade) AS 成绩 FROM score WHERE c_name=‘计算机’
sum 求和
SELECT stu_id AS 学号, SUM(grade) AS 成绩 FROM score WHERE c_name=‘计算机’
avg 平均值
SELECT stu_id AS 学号, AVG(grade) AS 成绩 FROM score WHERE c_name=‘计算机’
/
/
分组
WHERE子句：
从数据源中去掉不符合其搜索条件的数据
GROUP BY子句：
搜集数据行到各个组中，统计函数为各个组计算统计值，每个组输出一行结果
HAVING子句：
从分组计算结果中进行过滤，去掉不符合其组搜索条件的各组数据行
#求每个系的最高分
SELECT c_name,AVG(grade) FROM score GROUP BY c_name
多字段分组
SELECT stu_id,c_name,AVG(grade) FROM score GROUP BY c_name,stu_id
HAVING 对之前分组的结构进行过滤
SELECT c_name,AVG(grade) FROM score GROUP BY c_name HAVING AVG(grade)<=90;
/
子查询：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=‘中文’)
NOT IN
SELECT * FROM student WHERE id NOT 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’)
/
/
ALL 本质用and 取最大值
ANY OR 取最小值
/
/
UNION 合并表
all 数据会重复
SELECT * FROM student_copy UNION all SELECT * FROM student;
不加all会去重
SELECT * FROM student_copy UNION SELECT * FROM student;
/