-- 更新部分班级（前三个python101为java101）
update my_copy set name='java101' where
name='python101' limit 3;

-- 删除数据：限制记录数为10
delete from my_copy where name='python102' limit 10;
delete from my_student where id=5;
-- 给my_student表增加主键
alter table my_student modify id int primary key auto_increment:

-- 清空表：重置自增长
truncate my_student;

-- select 选项
select * from my_copy;
select all * from my_copy;

-- 去重
select distinct * from my_copy;

-- 向学生表插入数据
insert into my_student
values(null,'bc20190001','张三','男'),
(null,'bc20190001','李四','男'),
(null,'bc20190001','王五','男'),
(null,'bc20190001','赵六','男'),
(null,'bc20190001','麻子','女');

-- 字段别名
select id,number as 学号,
name as 姓名,
sex 性别 from my_student;

-- 多表数据源
select * from my_student,my_class;

-- 子查询
select * from (select * from my_student) as s;

-- 增加age年龄和height身高字段
alter table my_student add age tinyint unsigned;
alter table my_student add height tinyint unsigned;

-- 增加字段值：rand取得一个0-1之间的随机数，floor向下取整
--rand()*20 => 0-20 + 20 => 20-40
update my_student set age=floor(rand()*20+20),
height=floor(rand()*20+170)

-- 找学生id为1，3，5的学生
select * from my_student where
id=1 || id=3 || id=5;-- 逻辑判断
select * from my_student where id
in(1,3,5);-- 落在集合中

-- 找身高在180-190之间的学生
select * from my_student where
height>=180 and height<=190;
select * from my_student where
height between 180 and 190;
select * from my_student where
height between 180 and 190;
select * from my_student where 1;
-- 所有条件都满足

-- 根据性别分组
select * from my_student group by sex;

-- 分组统计：身高高矮，平均年龄，总年龄
select sex,count(*),max(height),
main(height),avg(age),sum(age) from
my_student group by sex;

-- 修改id为4的记录，八年零设置为NULL
update my_student set age=null where id=4;
select sex,count(*),count(age),max(height),
min(height),avg(age),sum(age) from my_student
group by sex;

-- 修改id为1的记录，性别设置为女
update my_student set sex='女' where id=1;

select sex,count(*),count(age),max(height),
min(height),avg(age),sum(age) from my_student
group by sex desc;

alter table my_class drop primary key;
alter table my_class add id int primary key
auto_increment;
alter table my_student add c_id int;
update my_student set c_id=ceil(rand()*3);

-- 多字段分组：先班级，后男女
select c_id,sex,count(*),group_concat(name)
from my_student group by c_id,sex;-- 多字段排序


-- 求出所有班级人数大于等于2的学生人数
select c_id,count(*) from my_student group
by c_id having count(*)>=2;

select c_id,count(*) from my_student where count(*)>=2
group by c_id;

select c_id,count(*) as total from my_student group
by c_id having total>=2;

select c_id,count(*) as total from my_student where
total>=2 group by c_id;

-- having子句进行条件查询
select name as 名字,number as 学号 from my_student
having 名字 like '张%';

-- 排序
select * from my_student group by c_id;
select * from my_student order by c_id;

-- 多字段排序：先班级排序，后性别排序
select * from my_student order by c_id,sex desc;


-- 查询学生：前两个
select * from my_student limit 2;

-- 查询学生：前两个
select * from my_student limit 0,2;-- 记录数是从0开始编号
select * from my_student limit 2,2;-- 记录数是从0开始编号
select * from my_student limit 4,2;

-- 更改id为班级表的第一列
alter table my_class chang id id int first;


-- 交叉连接
-- my_student cross join my_class是数据源
select * from my_student cross join my_class;

-- 内连接
select * from my_student inner join my_class on
my_student.c_id=my_class.id;

select * from my_student inner join my_class on
my_student.c_id=my_class.id;
select * from my_student inner join my_class on
my_student.c_id=id; -- 两张表都有id字段

-- 字段和表别名
select s.*,c.name as c_name,c.room
from my_student as s inner join my_class as
c on s.c_id=c.id;

-- 把学生id为5的c_id设置NULL
update my_student set c_id=null where id=5;

-- where代替
select s.*,c.name as c_name,c.room from my_student as
s inner join my_class as c where s.c_id=c.id;