mysql.exe -h localhost -P 3306 -u root -p
use mydb; —— 进入数据库
show tables; —— 查看所有表
查看：show index from 表名\G
desc：查看表结构
select * from 表名：查询所有数据
表创建的高级操作
从已有表创建新表（复制表结构）：create table 表名 like 数据库.表名;
蠕虫复制：
先查出数据，然后将查出的数据新增一遍
insert into 表名[(字段列表)] select 字段列表/* from 数据表名;
蠕虫复制的意义
从已有表拷贝数据到新表中
可以迅速地让表中的数据膨胀到一定的数量级，用来测试表的压力以及效率
-- 复制创建表
create table my_copy like my_class;
-- 蠕虫复制
insert into my_copy selsct * from my_class;
-- 删除主键
alter table my_copy drop primary key;
insert into my_copy selsct * from my_copy;
-- 更新部分1811变成1911
update my_copy set name='Python1911' where name='Python1811' limit 3;
-- 删除数据：限制记录数为5
delete from my_copy where name='Python1903' limit 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,'bc20200001','charry','女'),
(null,'bc20200002','marry','女'),
(null,'bc20200003','harry','女'),
(null,'bc20200004','karry','男'),
(null,'bc20200005','barry','男');
-- 字段别名
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 unsigend;
-- 增加字段值：rend取得一个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;-- 逻辑判断
-- 找身高在185~190之间的学生
select * from my_student where height>=185 and height<=190;
select * from my_student where height between 185 and 190;
select * from my_student where height between 190 and 185;
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_student group by sex;
-- 修改id为4的记录，把年龄置位NULL
update my_student set age=null where id=4;
select sex,count(*),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_class drop primary key;
-- 给班级表增加主键id
alter table my_class add id int primary key auto_increment;
-- 给学生表增加一个班级表的id
alter table my_student add c_id int;
update my_student set c_id=ceil(rand() *4);
insert into my_student values(6,'bc20200006','小芳','女',18,160,2;)
-- 多字段分组，先班级，后男女
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 by c_id,sex;
