参考数据如下表(score):
1.简单查询
- 查询整张表的数据
- SELECT * FROM score;
- 对某一列的列名进行重命名(例:将class重命名为“班级”)
- SELECT class as "班级" FROM score;
- 对某一列的数据去除重复项(例:去除chinese列的重复项)
- SELECT DISTINCT chinese FROM score;
- 对某一列进行排序,默认为升序,asc/desc表示升序/降序(对chinese进行升序排序,对math进行降序排序)
- SELECT chinese FROM score ORDER BY chinese ASC;/ SELECT chinese FROM score ORDER BY chinese;
- SELECT math FROM score ORDER BY math DESC;
- 对表格进行限制查询(例:查询前4行的数据/查询第5行到第8行的数据)
- SELECT * FROM score LIMIT 4;
- SELECT * FROM score LIMIT 4,4; -->第一个4表示从4+1行开始,第二个4表示读取4行
2.条件查询
- 例:查询chinese值超过75且math值超过80的数据
- SELECT name,chinese,math FROM score WHERE chinese > 75 AND math > 80;
- 例:查询chinese值超过75或math值超过80的数据
- SELECT name,chinese,math FROM score WHERE chinese > 75 OR math > 80;
- 例:查询chinese值为空的数据
- SELECT * FROM score WHERE chinese is null;
- 例:查询math值不为空的数据
- SELECT * FROM score WHERE math is not null;
3.复杂查询
- 将表格进行分组统计查询(例:对class列进行分组统计)
- SELECT class,COUNT(class) as 计数 FROM score GROUP BY class;
- 在分组的基础上对数据进行过滤(例:对class进行分组并筛选出math平均值大于80的class)
- SELECT class,AVG(math) FROM score GROUP BY class HAVING AVG(math);
4.创建视图
- 视图只是虚拟表格,并非真实存在;
- 若表格数据发生改变,视图内数据也会随机发生改变;
- 例:创建视图,呈现出每个班的chinese列的总分
- CREATE VIEW 视图 AS SELECT class,SUM(chinese) AS 总分 FROM score GROUP BY class;
5.连接
以下表格为辅助数据:左表为class表,右表为student表
- 内连接(INNER):
- SELECT * FROM class INNER JOIN student ON class.stuid=student.stuid;
- 左连接(LEFT):
- SELECT * FROM class LEFT JOIN student ON class.stuid=student.stuid;
- 右连接(RIGHT):
- SELECT * FROM class RIGHT JOIN student ON class.stuid=student.stuid;
- 全连接(UNION/FULL):
- SELECT * FROM class LEFT JOIN student ON class.stuid=student.stuid
UNION
SELECT * FROM class RIGHT JOIN student ON class.stuid=student.stuid; -->UNION去重,UNION ALL不去重
/ SELECT * FROM class FULL JOIN student ON class.stuid=student.stuid;-->MYSQL不支持 FULL