mysql.exe -h localhost -P 3306 -u root -p
use mydb;?????——???? 進(jìn)入數(shù)據(jù)庫(kù)
show tables;? ? ——? ? 查看所有表
查看：show index from 表名\G
desc：查看表結(jié)構(gòu)
select * from 表名：查詢所有數(shù)據(jù)
表創(chuàng)建的高級(jí)操作
從已有表創(chuàng)建新表（復(fù)制表結(jié)構(gòu)）：create table 表名 like 數(shù)據(jù)庫(kù).表名;
蠕蟲復(fù)制：
先查出數(shù)據(jù)，然后將查出的數(shù)據(jù)新增一遍
????????insert into 表名[(字段列表)] select 字段列表/* from 數(shù)據(jù)表名;
蠕蟲復(fù)制的意義
從已有表拷貝數(shù)據(jù)到新表中
可以迅速地讓表中的數(shù)據(jù)膨脹到一定的數(shù)量級(jí)，用來測(cè)試表的壓力以及效率
-- 復(fù)制創(chuàng)建表
create table my_copy like my_class;
-- 蠕蟲復(fù)制
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;
-- 刪除數(shù)據(jù)：限制記錄數(shù)為5
delete from my_copy where name='Python1903' limit 5;
-- 給my_student表增加主鍵
alter table my_student modify id int primary key auto_increment;
-- 清空表：重置自增長(zhǎng)
truncate my_student;
-- select 選項(xiàng)
select * from my_copy;
select all * from my_copy;
-- 去重
select distinct * from my_copy;
-- 向?qū)W生表插入數(shù)據(jù)
insert into my_student
values (null,'bc20200001','charry','女'),
(null,'bc20200002','marry','女'),
(null,'bc20200003','harry','女'),
(null,'bc20200004','karry','男'),
(null,'bc20200005','barry','男');
-- 字段別名
select id, number as 學(xué)號(hào), name as 姓名，
sex 性別 from my_student;
-- 多表數(shù)據(jù)源
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取得一個(gè)0~1之間的隨機(jī)數(shù)，floor向下取整
update my_student set age=floor(rand() *20+20),height=floor(rand() *20+170);
-- 找學(xué)生id為1、3、5的學(xué)生
select * from my_student where id=1 || id=3 || id=5;-- 邏輯判斷
-- 找身高在185~190之間的學(xué)生
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;? -- 所有條件都滿足
-- 根據(jù)性別分組
select * from my_student group by sex;
-- 分組統(tǒng)計(jì)：身高高矮、平均年齡、總年齡
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;
-- 刪除班級(jí)表主鍵
alter table my_class drop primary key;
-- 給班級(jí)表增加主鍵id
alter table my_class add id int primary key auto_increment;
-- 給學(xué)生表增加一個(gè)班級(jí)表的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;)
-- 多字段分組，先班級(jí)，后男女
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;
