05.1 查询-单表查询

-- 4.1 查询所有列
SELECT * FROM student;
-- 4.2 查询指定字段
SELECT id,stuName FROM student;
-- 4.3 指定别名
SELECT id AS '学号',stuName AS '姓名' FROM student AS s; -- 表的别名在多表查询时会用到
-- 4.4 添加一个常量列
SELECT id AS '学号',stuName AS '姓名', 'Java班' AS '班级' FROM student;
-- 4.5 合并列
SELECT id,stuName,(chinese+math+english) AS '总成绩' FROM student;
-- 4.6 去重复
SELECT DISTINCT gender FROM student; -- 相同性别只会显示一个
SELECT DISTINCT(gender) FROM student;
SELECT DISTINCT gender,stuName FROM student; -- 如果同名同姓 且都是同一性别
 
-- 4.7 条件查询 WHERE
-- 4.7.1 AND OR
SELECT * FROM student WHERE stuName = '张三' AND id = 6;
SELECT * FROM student WHERE stuName = '张三' OR id = 5; -- 有三条记录
-- 4.7.2 比较 > < <= >= = <> between and
SELECT * FROM student WHERE chinese > 70;
SELECT * FROM student WHERE chinese > 70 AND english > 90;
SELECT * FROM student WHERE chinese BETWEEN 85 AND 100;  -- 包含两端
-- 4.7.3 is null, is not null, = ''
SELECT * FROM student WHERE english IS NULL;
SELECT * FROM student WHERE stuName = '';
-- 4.7.4 模糊查询
SELECT * FROM student WHERE stuName LIKE '张%'; -- %任意字符
SELECT * FROM student WHERE stuName LIKE '张_'; -- _单个字符
SELECT * FROM student WHERE stuName LIKE '%张%';
 
-- 4.8 聚合查询
-- 聚合函数 sum() avg() max() min() count()
SELECT SUM(english) AS '全班英语总成绩' FROM student;
SELECT AVG(english) AS '全班英语平均成绩' FROM student;
SELECT COUNT(id) AS '总数'  FROM student; -- 会忽略掉null
SELECT COUNT(english) AS '参加英语考试人数'  FROM student;
 
-- 9. 分页查询limit 第几条数据,查询几条
-- SELECT * FROM student limit ((当前页数 - 1) * 每页行数), 行数
SELECT * FROM student LIMIT 0,2; -- 第一页 1,2
SELECT * FROM student LIMIT 2,2; --        3,4
SELECT * FROM student LIMIT 4,2; --        5,6
 
-- 10. 排序 order by
SELECT * FROM student ORDER BY id;
SELECT * FROM student ORDER BY id ASC;
SELECT * FROM student ORDER BY id DESC;
 
SELECT * FROM student ORDER BY chinese DESC;
SELECT * FROM student ORDER BY chinese DESC,math DESC,english DESC;
 
-- 11. 分组查询
SELECT gender,COUNT(*) FROM student GROUP BY gender;
SELECT gender,COUNT(*) FROM student GROUP BY gender HAVING COUNT(*) > 3;
 
-- 12. 正则
SELECT * FROM city WHERE NAME REGEXP '^A';
-- 类似 SELECT * FROM city WHERE NAME LIKE 'A%';
 
-- 再添加一些数据:
INSERT INTO student(id,stuName,chinese,english,math) VALUES(6,'郭靖',89,78,90);
INSERT INTO student(id,stuName,chinese,english,math) VALUES(7,'杨康',67,53,95);
INSERT INTO student(id,stuName,chinese,english,math) VALUES(8,'黄药师',87,78,77);
INSERT INTO student(id,stuName,chinese,english,math) VALUES(9,'老顽童',88,98,92);
INSERT INTO student(id,stuName,chinese,english,math) VALUES(10,'段玉',82,84,67);
INSERT INTO student(id,stuName,chinese,english,math) VALUES(11,'段王爷',55,85,45);
INSERT INTO student(id,stuName,chinese,english,math) VALUES(12,'黄蓉',75,65,30);
-- 查询表中所有学生的信息。
-- 查询表中所有学生的姓名和对应的英语成绩。
-- 过滤表中英语成绩的重复数据
-- 使用别名表示学生分数。
 
-- 查询所有姓名为杨的学生成绩
-- 查询英语成绩大于等于90分的同学
-- 查询总分大于200分的所有同学
-- 查询所有姓段的学生英语成绩。
-- 查询英语>80或者总分>200的同学
 
-- 统计每个学生的总分。
-- 在所有学生总分数上加10分特长分。