-- 创建数据库
create database python_test_1 charset=utf8;

-- 使用数据库
use python_test_1;

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

-- 向students表中插入数据
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭于晏',29,185.00,1,1,0),
(0,'刘德华',59,175.00,1,2,1),
(0,'黄蓉',38,160.00,2,1,0),
(0,'凤姐',28,150.00,4,2,1),
(0,'王祖贤',18,172.00,2,1,1),
(0,'周杰伦',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'刘亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'静香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周杰',34,176.00,2,5,0);

-- 向classes表中插入数据
insert into classes values (0, "python_01期"), (0, "python_02期");

-- 基本查询
	-- 查询所有字段
	--select * from 表名;
	select * from students;
	select * from classes;
	
	-- 查询指定字段
	--select 列1,列2,... from 表名;
	select name, age from students;

	-- 使用 as 给字段起别名
	-- select 字段 as 名字... from 表名;
	select name as 姓名, age as 年龄 from students;

	-- select 表名.字段 ... from 表名;
	select students.name, students.age from students;

	--可以通过 as 给表起别名
	-- select 别名.字段 ... from 表名 as 别名;
	select students.name, students.age from students;
	select s.name, s.age from students as s;

	-- 去重
	-- distinct 字段
	select distinct gender from students;  -- 查询性别，一种性别只显示一次

-- 条件查询
	-- 比较运算符
		-- select ... from 表名 where ...
		-- 查询大于18岁的学生信息  > < = >= <= !=
		select * from students where age>18;

	-- 逻辑运算符
		-- and   or
		select * from students where age>18 and age<20;

		-- not
		-- 不在 18 岁以上的女性
		select * from students where not (age>18 and gender=2);

	-- 模糊查询
		-- like  (效率比较低，用得较少 )
		-- % 替换 1 个或者多个
		-- _ 替换一个
		-- 查询姓名中 以小"小"开始的
		select name	from students where name like "小%";

		-- rlike 正则
		-- 查询 以周开始的姓名
		select name from students where name rlike "^周.*"

	-- 范围查询
		-- in (1, 3, 4)
		-- not in (1, 3, 8)
		select name,age from students where age in (18, 25, 22)

		-- between ... and ... 表示在一个连续的范围内
		select name,age from students where age between 18 and 24;
		
		-- not between ... and ... 不在
		select name,age from students where age not between 18 and 24;

	-- 空判断
		-- is null   /  is not null
		select * from students where height is null; 

-- 排序
	-- order by 字段
	-- asc 从小到大
	-- desc 从大到小

	select * from students where (age between 18 and 25) and gender=1 order by age; -- 默认为asc
	select * from students where (age between 18 and 25) and gender=1 order by age desc;

	-- order by 多个字段
	-- 查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从小到大排
	select * from students where (age between 18 and 25) and gender=2 by height desc,age asc;

-- 聚合函数
	-- 总数
	-- count
	-- 查询女性有多少人
	select * from students where gender=2;
	select count(*) from students where gender=2;
	select count(*) as 男性的人数 from students where gender=2;

	-- 最值
	-- max    / min
	select max(age) from students;

	-- 求和
	--sum
	se sum(age) from students;

	-- 平均值
	-- avg
	select avg(age) from students;
	select sum(age)/sum(age) from students;

	-- 四舍五入 round(123.33, 1) 保留一位小数
	select round(avg(age)) from students; 

-- 分组 (基本是和聚合函数一起用)
	-- group by
	-- 按照性别分组，查询所有的性别
	select gender from students group by gender;

	-- 计算每种性别中的人数
	select gender,count(*) from students group by gender;

	-- 计算男性的人数
	select gender,count(*) from students where gender=1 group by gender;

	-- group_concat(...)
	-- 查看同种性别中的姓名
	select gender,group_concat(name, age, id) from students where gender=1 group by gender;
	select gender,group_concat(name, "_", age, "_", id) from students where gender=1 group by gender;

	-- having (对分组进行条件判断)
	-- 查询平均年龄超过30岁的性别，及姓名
	select gender, group_concat(name),avg(age) from students group by gender having avg(age>30);

-- 分页（需放在后面）
	-- limit start, count  从 start 开始，显示 count 个
	
	-- 限制显示查询出来的数据的个数
	select * from students limit 5;

	-- 查询前5个数据
	select * from students limit 0, 5;

	-- 每页2个，第1页
	select * from students limit 0, 2;

	-- 每页2个，第2页
	select * from students limit 2, 2;

	-- 每页2个，第3页
	select * from students limit 4, 2;

	-- 每页2个，第4页
	select * from students limit 6, 2;  -- ===> limit (第n页-1)**每页的个数, 每页的个数

-- 连接查询
	-- inner join ... on

	-- select ...from 表A inner join 表B;
	select * from students inner join classes;

	-- 查询 有能够对应班级的学生以及班级信息
	select * from students inner join classes on students.cls_id=classes.id

	-- 按照要求显示姓名、班级
	select students.*,classes.name from students inner join classes on students.cls_id=classes.id;

	-- 给数据表起名字
	select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;

	-- 查询 有能够对应班级的学生以及班级信息，按照班级排序
	select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id order by c.name;

	-- 同一班级时，按照学生的id进行从小到大排序
	select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id;

	-- left join (左连接)
	-- 查询每位学生对应的班级信息
	select * from students as s left join classes as c on s.cls_id=c.id;
	-- 以 left join 左边的表为基准去对应右边的表，对应到了就显示，没找到就显示NULL

	-- 查询没有对应班级信息的学生
	-- select ... from xxx as s left join xxx as c on ... where ...  从原表中
	-- select ... from xxx as s left join xxx as c on ... having ...  从新的表中
	select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;