目录
查询所有数据 SELECT * FROM tb_student;
查询指定列(多列) SELECT id,NAME,chinese FROM tb_student;
查询指定别名 SELECT id AS ’编号’,Name AS ’姓名’ FROM tb_student AS s;
查询时合并列 SELECT id,NAME,(chinese+English) AS '总成绩' FROM student;
查询时去除重复记录 SELECT DISTINCT id FROM student;
条件查询
条件查询(where)
--逻辑条件: and(与) or(或)
SELECT * FROM student WHERE id=2 AND NAME='李进';
SELECT * FROM student WHERE id=2 OR NAME='李进';
--比较条件:> < >= <= = <>(不等于) between and(等价于>=且<=)
SELECT * FROM student WHERE chinese >70;
SELECT * FROM student WHERE chinese BETWEEN 75 AND 90;
SELECT * FROM student WHERE gender<>'男';
--判空条件(null 空字符串):is null/is not null 空字符串 ='' / <>''
SELECT * FROM student WHERE id IS NULL ;
SELECT * FROM student WHERE name ='';
SELECT * FROM student WHERE id IS NOT NULL AND name <>'';
--模糊条件: like
--% : 表示任意个字符
--_ : 表示一个字符
--需求: 查询姓‘张’的学生
SELECT * FROM student WHERE NAME LIKE '李%';
--需求: 查询姓‘李’,且姓名只有两个字的学生
SELECT * FROM student WHERE NAME LIKE '李_';
聚合查询
--常用的聚合函数: sum() avg() max() min() count()
SELECT SUM(chinese) AS ' chinese的总成绩' FROM student;
SELECT AVG(chinese) AS ' chinese的平均分' FROM student;
SELECT MAX(chinese) AS '最高分' FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(id) FROM student;
--注意:count()函数统计的数量不包含null的数据
分页查询
limit (起始行,查询几行)
--起始行从0开始
--分页查询当前页的数据的sql:
SELECT * FROM student LIMIT (当前页-1)*每页显示多少条,每页显示多少条;
--需求: 查询第1,2条记录(第1页的数据)
SELECT * FROM student LIMIT 0,2;
--查询第5,6条记录
SELECT * FROM student LIMIT 4,2;
查询排序(order by)
语法 :order by 字段 asc/desc
-- asc: 顺序,正序。数值:递增,字母:自然顺序(a-z)
-- desc: 倒序,反序。数值:递减,字母:自然反序(z-a)
-- 默认情况下,按照插入记录顺序排序
SELECT * FROM student;
-- 需求: 按照id顺序排序
SELECT * FROM student ORDER BY id ASC;
SELECT * FROM student ORDER BY id; -- 默认正序
SELECT * FROM student ORDER BY id DESC;-- 反序
-- 注意:多个排序条件
-- 需求: 按照id正序,按照chinese的倒序
SELECT * FROM student ORDER BY id ASC,chinese DESC;
分组查询(group by)
--需求:
-- 1) 把学生按照性别分组(GROUP BY gender)
-- 2) 统计每组的人数(COUNT(*))
SELECT gender,COUNT(*) FROM student GROUP BY gender;