# --给班级表增加主键
# alter table my_class add primary key(name);
# --插入数据
# insert into my_class values(
# 'Python1907','B408');
# insert into my_class values(
# 'Python1907','B407');
# insert into my_class values(
# 'Python1907','B407');
# --冲突处理
# on duplicate key update
# --更新教室
# room='B407';
# insert into my_class values('Python1903','B408');
# --主键冲突：替换
# replace into my_class values(
# 'Python1903','B406');
# replace into my_class values(
# 'Python1910','B409');
# --复制创建表
# create table my_copy like
# my_class;
# --删除主键
# alter table my_copy drop primary
# key;
# --蠕虫复制
# insert into my_copy select * from my_class;
# insert into my_copy select * from my_copy;
# --更新部分B406变成A406
# update my_copy set room='A406'
# where room='B406' limit 3;
# --删除数据：限制记录数为5
# delete from my_copy where room='B409' limit 5;
# --给学生表增加主键
# alter table my_student modify id int primary key auto_increment;
# --清空表，重置自增长
# truncate my_student;
# --以下三句有什么区别？
# --delete from 表名;
# --truncate 表名；
# --drop table 表名;
# --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 unsigend;
# alter table my_student add height tinyint unsigned;
# --增加字段值：rand取得一个0-1之间的随机数，floor向下取整
# 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);--in 表示再集合中
# --找出身高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 190 and 180;
# 不成立，相当于height>=190 and height<=180
# select * from my_student where 1;
# 所有条件都满足
# 根据性别分组
# select * from my_student group by sex;
# 分组统计：身高高矮 平均年龄 总年龄
# select sex,count(*),max(height),
# min(height),avg(age),sum(age)
# from my_stydent group by sex;
# --修改id为4的记录，把年龄置为NULL
# update my_student set age-nulll
# 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;
# --nan
# --nv
# select sex,count(*),count(age),
# max(height),min(height),avg(age),
# sum(age) from my_student group by
# sex desc;
# --删除班级表原主键
# alter table my_calss drop primary
# key
# --给班级表增加主键
# alter table my_class add id int
# primary key auto_increment;
# --给学生表增加班级ID
# ater table my_student add c_id int;
# update my_student set c_id=ceil(rand()*3);
# --多字段分组：先班组，后男女
# select c_id,sex,count(*) from
# my_student group by c_id,sex;
# --多字段排序
# select c_id,sex,count(*),
# group_concat(name)from
# my_student group dy c_id,sex;
# --多字段排序
# --统计
# select c_id,count(*) from
# my_student group by c_id;
# --回溯统计
# select c_id,count(*) from
# my_student group by c_id with
# rollup;
# --多字段分组回溯统计
# select c_id,sex,count(*),
# group_concat(name) from
# my_student grounp by c_id,sex;
# --多字段排序
# select c_id,sex,count(*),
# group_concat(name)from
# my_student group by c_id,sex with
# rollup;
# --求出所有班级人数大于等于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 where 名字
# 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;
# select * from my_student limit 4,
#2；
