3 7 补充

drop table if exists students;
create table students (
studentNo varchar(10) primary key,
name varchar(10),
sex varchar(1),
hometown varchar(20),
age tinyint(4),
class varchar(10),
card varchar(20)
);

insert into students values
('001', '王昭君', '女', '北京', '20', '1班', '340322199001247654'),
('002', '诸葛亮', '男', '上海', '18', '2班', '340322199002242354'),
('003', '张飞', '男', '南京', '24', '3班', '340322199003247654'),
('004', '白起', '男', '安徽', '22', '4班', '340322199005247654'),
('005', '大乔', '女', '天津', '19', '3班', '340322199004247654'),
('006', '孙尚香', '女', '河北', '18', '1班', '340322199006247654'),
('007', '百里玄策', '男', '山西', '20', '2班', '340322199007247654'),
('008', '小乔', '女', '河南', '15', '3班', null),
('009', '百里守约', '男', '湖南', '21', '1班', ''),
('010', '妲己', '女', '广东', '26', '2班', '340322199607247654'),
('011', '李白', '男', '北京', '30', '4班', '340322199005267754'),
('012', '孙膑', '男', '新疆', '26', '3班', '340322199000297655');
SELECT*FROM students;

-- 查询部分字段数据：
-- select 字段1,字段2,... from 表名
select name,age,sex from students;

-- 起别名: 为第5天的关联查询做铺垫
-- select 别名.字段1,别名.字段2,... from 表名 as 别名
-- 把students改为名s, as可以省略
select s.name,s.sex,s.age from students s

-- select 字段1 as 别名1,字段2 as 别名2,... from 表名

select name as 姓名, sex as 性别, age as 年龄 from students;

-- 去重:
-- select distinct 字段1,... from 表名

-- 例1：查询小乔的年龄
SELECT age FROM students WHERE name='小乔';

-- 例2：查询20岁以下的学生
SELECT * FROM students WHERE age < 20;

-- 例3：查询家乡不在北京的学生
SELECT * FROM students WHERE hometown != "北京";
SELECT * FROM students WHERE hometown <> "北京";

-- 1、查询学号是'007'的学生的身份证号
select * from students where studentNo="007";

-- 2、查询'1班'以外的学生信息
SELECT *from students where class != "1班";

-- 3、查询年龄大于20的学生的姓名和性别
select name,sex from students where age > "20";

-- 条件查询-模糊查询
-- 关键字: like
-- % ：匹配任意多个字符
-- _ : 匹配一个任意字符

-- 例1：查询姓孙的学生
SELECT * FROM students WHERE name like '孙%';

-- 例2：查询姓孙且名字是一个字的学生
SELECT * FROM students WHERE name LIKE '孙_';

-- 例3：查询姓名以‘乔’结尾的学生
SELECT * FROM students WHERE name LIKE '%乔';

-- 例4：查询姓名中包含‘白’的学生
SELECT * FROM students WHERE name LIKE '%白%';

-- 1、查询姓名为两个字的学生
select *from students where name like "__";

-- 2、查询姓‘百’且年龄大于20的学生
SELECT * FROM students WHERE name like '百%'and age > 20;
-- 3、查询学号以1结尾的学生‘
select * from students where studentNo like "%1";

-- in表示在一个非连续的范围内
-- 例:查询家乡是北京或上海或广东的学生
SELECT * FROM students WHERE hometown = '北京' or hometown = '上海' or hometown = '广东';
SELECT * FROM students WHERE hometown in ('北京', '上海', '广东');

-- between ... and ...表示在一个连续的范围内
-- 例：查询年龄为18至20的学生
SELECT * FROM students WHERE age >= 18 and age <=20;
SELECT * FROM students WHERE age BETWEEN 18 and 20;

 

-- 1、查询年龄为18或19或22的女生

SELECT * FROM students WHERE age in (18, 19, 22) and sex = '女';

-- 2、查询年龄在20到25以外的学生
select * from students where age BETWEEN 20 and 25; -- 20 到 25 以内
SELECT * FROM students WHERE age not BETWEEN 20 AND 25; -- 20到25以外

-- or and between or 或 and 和 between 之间的 not between 取反
-- between 10 and 20; 10到20之间的
-- not between 10 and 20; 10到20之外的

-- 条件查询-空判断
-- 注意:Mysql中空表示null，与''(空)是不一样的。
-- 判断为空: is null
-- 例：查询没有填写身份证的学生
SELECT * FROM students WHERE card is NULL;

-- 判断非空: is not null
-- 例：查询填写了身份证的学生
SELECT * FROM students WHERE card is not NULL;

‘-- select * from 表名 order by 字段名1 asc|desc, 字段名2 asc|desc,...
-- 将行数据按照字段1进行排序，如果某些字段1的值相同时，则按照字段2排序，以此类推
-- 默认按照列值从小到大排列
-- asc从小到大排列，即升序
-- desc从大到小排序，即降序

-- 例1：查询所有学生信息，按年龄从小到大排序(升序)
select * FROM students order by age asc;
select * FROM students order by age; -- 默认升序 asc省略
select * FROM students order by age desc; -- 降序

-- 例2：查询所有学生信息，按年龄从大到小排序(降序)，年龄相同时，再按学号从小到大排序(升序)
SELECT * FROM students order by age desc, studentNo;

-- 例2：查询所有学生信息，按年龄从大到小排序(降序)，年龄相同时，再按学号从大到小排序(降序)
SELECT * FROM students order by age desc, studentNo desc;

-- 1、查询所有学生信息，按班级从小到大排序，班级相同时，再按学号从小到大排序
SELECT * FROM students order by CLASS, studentNO;

 

-- 聚合函数: 用于统计，一般配合分组使用
-- 例1：查询学生总数 count(*)
SELECT COUNT(*) from students;

-- 例2：查询女生的最大年龄 max()
SELECT MAX(age) from students WHERE sex = '女';

-- 例：查询1班的最小年龄 min()
SELECT MIN(age) from students WHERE class = '1班';

-- 示例：查询北京学生的年龄总和 sum()
SELECT SUM(age) from students WHERE hometown = '北京';

-- 例：查询女生的平均年龄 avg()
SELECT avg(age) from students WHERE sex = '女';

-- 1、查询所有学生的最大年龄、最小年龄、平均年龄
select MAX(age), min(age),avg(age) from students ;

-- 2、一班共有多少个学生
select COUNT(*) from students where class = '1班'

-- 3、查询3班年龄小于18岁的同学有几个
select COUNT(*) from students where class = '3班' and age < "18"

 

-- 例1：查询各种性别的人数
-- 查看分组后的性别
SELECT sex from students group by sex;
-- 查询各种性别的人数
SELECT sex, count(*) from students group by sex;
-- 查询各种性别的人数, 每组最大年龄, 每组最小年龄
SELECT sex, count(*), max(age), min(age) from students group by sex;

-- 例2：查询每个班级中各种性别的人数, 各种性别的最小年龄
SELECT class, sex, COUNT(*), min(age) from students group by class, sex;

 

-- 说明: 关键字having后面的条件运算符与where的相同
-- 例1：查询男生总人数(使用了分组)
SELECT sex, COUNT(*) from students group by sex; -- 没有过滤
-- 分组过滤，一定需要使用having，不能用where
-- SELECT sex, COUNT(*) from students group by sex where sex = '男'; -- err, 使用where
SELECT sex, COUNT(*) from students group by sex HAVING sex = '男';

-- 例2：查询男生总人数(不使用了分组)
-- 没有使用分组，过滤使用where
SELECT COUNT(*) from students WHERE sex ='男';

 

-- 1、查询各个班级学生的平均年龄、最大年龄、最小年龄
SELECT class, avg(age), max(age), min(age) from students group by class;

-- 2、查询1班除外其他班级学生的平均年龄、最大年龄、最小年龄
SELECT class, avg(age), max(age), min(age) from students group by class HAVING class != '1班'
-- where 对不分组 from 过滤
-- having 对分组 group by 过滤
-- where后面不能用聚合函数 having可以
-- 查询所有班级中不同性别的记录数（个数）大于1 的信息

-- 3、查询所有班级中不同性别的记录数大于1的信息
select class, sex, count(*) from students group by class, sex HAVING COUNT(*) > 1;

-- 说明:
-- 从start开始，获取count条数据
-- start索引从0开始

-- 例1：查询前3行学生信息
-- limit 起点， 每页显示的数据
SELECT * from students limit 0, 3; -- 第1页
-- 如果起点为0， 这个0可以不写
SELECT * from students limit 3; -- 第1页

SELECT * from students limit 3, 3; -- 第2页

SELECT * from students limit 6, 3; -- 第3页

SELECT * from students limit 9, 3; -- 第4页

-- 每一页显示4个数据
SELECT * from students limit 0, 4;
SELECT * from students limit 4, 4;
SELECT * from students limit 8, 4;

-- 每一页显示5个数据
SELECT * from students limit 0, 5;
SELECT * from students limit 5, 5;
SELECT * from students limit 10, 5; -- 不够5个只取实际能取的

-- 1、查询第4到第6行学生信息

SELECT * from students limit 3, 3;
-- 每页显示5条数据，显示每一页的数据
SELECT * from students limit 0, 5;
SELECT * from students limit 5, 5;
SELECT * from students limit 10, 5;

-- 3、已知总记录数和每页显示条数，求总页数？