MySQL基础第三课
标签：isp 文章 长度 gbk 创建 保存 复制 phi trunc
MySQL基础第三课
增加主键
create table my_pri1(
name varchar(20) not null comment ‘姓名‘,
number char(10) primary key comment ‘学号: itcast + 0000, 不能重复‘
)charset utf8;
-- 复合主键
create table my_pri2(
number char(10) comment ‘学号: itcast + 0000‘,
course char(10) comment ‘课程代码: 3901 + 0000‘,
score tinyint unsigned default 60 comment ‘成绩‘,
-- 增加主键限制: 学号和课程号应该是个对应的,具有唯一性
primary key(number,course)
)charset utf8;
-- 追加主键
create table my_pri3(
course char(10) not null comment ‘课程编号: 3901 + 0000‘,
name varchar(10) not null comment ‘课程名字‘
);
alter table my_pri3 modify course char(10) primary key comment ‘课程编号: 3901 + 0000‘;
alter table my_pri3 add primary key(course);
-- 向pri1表插入数据
insert into my_pri1 values(‘古学星‘,‘itcast0001‘),(‘蔡仁湾‘,‘itcast0002‘);
insert into my_pri2 values(‘itcast0001‘,‘39010001‘,90),(‘itcast0001‘,‘39010002‘,85),(‘itcast0002‘,‘39010001‘,92);
-- 主键冲突(重复)
insert into my_pri1 values(‘刘辉‘,‘itcast0002‘); -- 不可以: 主键冲突
insert into my_pri2 values(‘itcast0001‘,‘39010001‘,100); -- 不可以:冲突
-- 删除主键
alter table my_pri3 drop primary key;
-- 自增长
create table my_auto(
id int primary key auto_increment comment ‘自动增长‘,
name varchar(10) not null
)charset utf8;
-- 触发自增长
insert into my_auto(name) values(‘邓立军‘);
insert into my_auto values(null,‘龚森‘);
insert into my_auto values(default,‘张滔‘);
-- 指定数据
insert into my_auto values(6,‘何思华‘);
insert into my_auto values(null,‘陈少炼‘);
-- 修改表选项的值
alter table my_auto auto_increment = 4; -- 向下修改(小)
alter table my_auto auto_increment = 10; -- 向上修改
-- 查看自增长变量
show variables like ‘auto_increment%‘;
-- 修改自增长步长
set auto_increment_increment = 5;
-- 插入记录: 使用自增长
insert into my_auto values(null,‘刘阳‘);
insert into my_auto values(null,‘邓贤师‘);
-- 删除自增长
alter table my_auto modify id int primary key; -- 错误: 主键理论是单独存在
alter table my_auto modify id int; -- 有主键的时候,千万不要再加主键
-- 唯一键
create table my_unique1(
number char(10) unique comment ‘学号: 唯一,允许为空‘,
name varchar(20) not null
)charset utf8;
create table my_unique2(
number char(10) not null comment ‘学号‘,
name varchar(20) not null,
-- 增加唯一键
unique key(number)
)charset utf8;
create table my_unique3(
id int primary key auto_increment,
number char(10) not null,
name varchar(20) not null)charset utf8;
-- 追加唯一键
alter table my_unique3 add unique key(number);
-- 插入数据
insert into my_unique1 values(null,‘曾光‘),(‘itcast0001‘,‘晁松‘),(null,‘李帅‘);
insert into my_unique1 values(‘itcast0001‘,‘周江‘);
-- 删除唯一键
alter table my_unique3 drop index number;
-- 插入数据
insert into my_class values(‘PHP0810‘,‘B203‘);
insert into my_class values(‘PHP0810‘,‘B205‘);
insert into my_class values(‘PHP0710‘,‘B203‘);
-- 主键冲突: 更新
insert into my_class values(‘PHP0810‘,‘B205‘)
-- 冲突处理
on duplicate key update
-- 更新教室
room = ‘B205‘;
-- 主键冲突:替换
replace into my_class values(‘PHP0710‘,‘A203‘);
replace into my_class values(‘PHP0910‘,‘B207‘);
-- 复制创建表
create table my_copy like my_gbk;
-- 蠕虫复制
insert into my_copy select * from my_collate_bin;
insert into my_copy select * from my_copy;
-- 更新部分a变成c
update my_copy set name = ‘c‘ where name = ‘a‘ limit 3;
-- 删除数据:限制记录数为10
delete from my_copy where name = ‘b‘ limit 10;
-- 清空表: 重置自增长
truncate my_student;
-- select选项
select * from my_copy;
select all * from my_copy;
-- 去重
select distinct * from my_copy;
insert into my_student values(null,‘itcast0001‘,‘张三‘,‘男‘),
(null,‘itcast0002‘,‘李四‘,‘男‘),
(null,‘itcast0003‘,‘王五‘,‘女‘),
(null,‘itcast0004‘,‘赵六‘,‘男‘),
(null,‘itcast0005‘,‘小明‘,‘男‘);
-- 字段别名
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向下取整
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 190 and 180;
-- 根据性别分组
select * from my_student group by sex;
-- 分组统计: 身高高矮,年龄平均和总年龄
select sex,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;
select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by sex;
select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by sex desc;
-- 多字段分组: 先班级,后男女
select c_id,sex,count(*),group_concat(name) from my_student group by 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 group 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 ;
-- 排序
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;
View Code