mysql 操作

-- 增加主键
create table mypri1(
name varchar(20) not null comment '姓名',
number char(10) primary key comment '学好:itcast +000,不能重复'
)charset utf8;


-- 复合主键
create table mypri2(
number char(10) comment '学好：itcast + 0000',
course char(10) comment '课程代码：3901 +000',
score tinyint unsigned default 60 comment '成绩',
primary key(number,course)-- 增加主键限制: 学号和课程号应该是个对应的,具有唯一性
)charset utf8;


-- 追加主键
create table mypri3(
course char(10) not null comment '课程编号：3901 +0000',
name varchar(10) not null comment'课程名字'
)charset utf8;


alter table mypri3 modify course char(10)
  primary key comment '课程编号：3901 +0000';
alter table mypri3 add primary key(course);


-- 向pri1表插入数据
insert into mypri1 values('美国','itcast0001'),('美国2','itcast0002');
-- 向pri2表插入数据
insert into mypri2 values('itcast0001','391',90),('itcast0001','392',85),('itcast0002','391',91);


-- 主键冲突(重复)
insert into mypri1 values('黎明','itcast0002');


-- 冲突处理
-- 更新姓名
insert into mypri1 values('巴黎','itcast0002')
on duplicate key update
name='巴黎';


-- 主键冲突:替换
replace into mypri1 values('中国','itcast0001');
replace into mypri1 values('香港','itcast0002');


-- 不可以: 主键冲突   itcast0001-391 for key primary
insert into mypri2 values('itcast0001','391',100);


-- 删除主键
alter table mypri3 drop primary key;


-- 自增长
create table myauto(
id int primary key auto_increment comment'自动增长',
name varchar(10) not null
)charset utf8;


-- 触发自增长
insert into myauto(name) values('李明');
insert into myauto values(null,'李明2');
insert into myauto values(default,'李明3');


-- 指定数据
insert into myauto values(7,'大宝');
insert into myauto values(null,'大宝2');


-- 修改表选项的值
alter table myauto auto_increment=5; -- 向下修改(小)
alter table myauto auto_increment=12; -- 向上修改 
insert into myauto values(null,'大宝3');


-- 查看自增长变量
show variables like 'auto_increment%';


-- 修改自增长步长
set auto_increment_increment=5;


-- 插入记录: 使用自增长
insert into myauto(name) values('lili');
insert into myauto(name) values('lilili');


-- 删除自增长
alter table myauto modify id int primary key;  -- 错误: 主键理论是单独存在
alter table myauto modify id int; -- 有主键的时候,千万不要再加主键


-- 唯一键
create table myuniquel(
number char(10) unique comment '学号',
name varchar(20) not null 
)charset utf8;
-- 增加唯一键
create table myunique2(
number char(10) not null comment '学号',
name varchar(20) not null,
unique key(number)
)charset utf8;
--之所以显示为pri,刚好是一个不为空的唯一键(主键性质一样)，原因是因为该表没有主键


-- 追加唯一键
create table myunique3(
id int primary key auto_increment,
number char(10) not null,
name varchar(20) not null
)charset utf8;
alter table myuniquel add unique key(number);


-- 插入数据
insert into myuniquel values(null,'李四'),('itcast0001','李五'),(null,'李六');
--插入有错，number唯一性
insert into myunique3 values('itcast0001','张五');


-- 删除唯一键
alter table myunique3 drop index number;


-- 插入数据
insert into 


--创建一个mygbk的表
create table mygbk(
name varchar(32) 
)charset utf8;


insert into mygbk values('a'),('A'),('B'),('b');
-- 复制创建表
create table mycopy like mygbk;


-- 蠕虫复制
insert into mycopy select * from mygbk;
insert into mycopy select * from mycopy;


-- 更新部分a变成c ,3个a(不论大小写)
update mycopy set name ='c' where name='a' limit 3;


-- 删除 b 数据:限制记录数为10
delete from mycopy where name='b' limit 10;


-- 清空表: 重置自增长
truncate mycopy;


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


-- 去重
select distinct * from mygbk;




create table car(
id int,
cname varchar(10),
ctype varchar(30),
cage int
)charset utf8;


insert into car(id,cname,ctype) 
values (3,'tom3','big3'),(4,'tom4','big4'),(5,'tom5','big5'),(6,'tom6','big6');


-- 字段别名
select 
id,
cname as 名称,
ctype as 类型 from car;


-- 多表数据源
select * from mygbk,car;
--从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留:(记录数和字段数),将这种结果成为: 笛卡尔积(交叉连接): 笛卡尔积没什么卵用, 所以应该尽量避免.
-- 子查询
select * from (select * from car) as mygbk;


-- 增加age和height字段
alter table mygbk add age int unsigned;
alter table mygbk add height int unsigned;


-- 增加值: rand取得一个0到1之间的随机数, floor向下取整
update mygbk set age=floor(rand()*20 + 20),height=floor(rand()*20 +170);


-- 找age为30,20的数据
select * from mygbk where age=30|| age=20;  -- 逻辑判断
select * from mygbk where age in (30,20); -- 落在集合中


--height为175-180的car
select * from mygbk where height>=175 and height <=180;
select * from mygbk where height between 175 and 180;


--Between本身是闭区间; between左边的值必须小于或者等于右边的值
select * from mygbk where height between 180 and 175;


--创建一个学生表
create table student(
id int,
name varchar(10),
ses varchar(2),
age int,
height int
)charset utf8;
insert into student values(1,'小明','男',18,188),(2,'小lu','女',12,156),(3,'小gh','女',23,165),(4,'小yt','男',41,178),(5,'小aa','女',26,158),(6,'小bb','男',9,168);
-- 根据性别分组
select * from student group by ses;


-- 分组统计: 身高高矮,年龄平均和总年龄 先女后男
select ses,count(*),max(height),min(height),avg(age),sum(age) from student group by ses;
select ses,count(*),count(age),max(height),min(height),avg(age),sum(age) from student group by ses;
--先男 后女
select ses,count(*),count(age),max(height),min(height),avg(age),sum(age) from student group by ses desc;


-- 统计
select id,count(*) from student group by id;
-- 回溯统计 最后一行有个总计
select id,count(*) from student group by id with rollup;


-- 多字段分组回溯统计 
select id,ses,count(*),group_concat(name) from student group by id,ses; -- 多字段排序
select id,ses,count(*),group_concat(name) from student group by id,ses with rollup;


--求出高度大于170的学生的人数
select id,count(*) from student group by id having count(*) >=2;


-- 排序
select * from student group by height;
select * from student order by height;


-- 多字段排序: 先班级排序,后性别排序
select * from student order by id, ses desc;


-- 查询学生: 前两个
select * from student limit 2;


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