mysql-高级操作
主键冲突
- 主键冲突的时候,可以选择更新或者替换进行处理
更新
- 主键冲突,更新操作、
- Insert into 表名[(字段列表:包含主键)] values(值列表) on duplicate key update 字段 = 新值;
-- 主键冲突: 更新
insert into my_class values('0810','B205')
-- 冲突处理
on duplicate key update
-- 更新教室
room = 'B205';
替换
- 主键冲突: 替换
- Replace into 表名 [(字段列表:包含主键)] values(值列表);
-- 主键冲突:替换
replace into my_class values('0710','A203');
replace into my_class values('0910','B207');
蠕虫复制
- 蠕虫复制: 从已有的数据中去获取数据,然后将数据又进行新增操作: 数据成倍的增加
- 表创建高级操作: 从已有表创建新表(复制表结构)
- Create table 表名 like 数据库.表名;
-- 复制创建表
create table my_copy like my_gbk;
-- 蠕虫复制
insert into my_copy select * from my_collate_bin;
insert into my_copy select * from my_copy;
- 蠕虫复制的意义
- 从已有表拷贝数据到新表中
- 可以迅速的让表中的数据膨胀到一定的数量级: 测试表的压力以及效率
更新数据
- 基本语法
- Update 表名 set 字段 = 值 [where条件];
- 高级新增语法
- Update 表名 set 字段 = 值 [where条件][limit 更新数量];
-- 更新部分a变成c
update my_copy set name = 'c' where name = 'a' limit 3;
删除数据
- 与更新类似: 可以通过limit来限制数量
- Delete from 表名 [where条件][limit 数量];
- 如果表中存在主键自增长,那么当删除之后, 自增长不会还原r
-- 删除数据:限制记录数为10
delete from my_copy where name = 'b' limit 10;
查询语句
- 基本语法
- Select 字段列表/* from 表名 [where条件]
- 完整语法
- Select [select选项] 字段列表[字段别名]/* from 数据源 [where条件子句][group by子句] [having子句][order by子句] [limit 子句];
select选项
- select选项是select对查出来的结果的处理方式
- all:默认的,保留所有的结果
- distinct:去重,查出来的结果,将重复的给去除,是所有字段都相同的才会被去除
-- select选项
select * from my_copy;
select all * from my_copy;
-- 去重
select distinct * from my_copy;
字段别名
- 字段别名: 当数据进行查询出来的时候, 有时候名字并一定就满足需求(多表查询的时候, 会有同名字段). 需要对字段名进行重命名: 别名
- 语法:字段名 [as] 别名;
-- 字段别名
select
id,
number as 学号,
name as 姓名,
sex 性别 from my_student;
数据源
- 数据源: 数据的来源, 关系型数据库的来源都是数据表: 本质上只要保证数据类似二维表,最终都可以作为数据源
- 数据源分为多种: 单表数据源, 多表数据源, 查询语句
单表数据源
- 单表数据源: select * from 表名;
select * from my_copy;
多表数据源
- 多表数据源: select* from 表名1,表名2...;
- 从一张表中取出一条记录,去另外一张表中匹配所有记录,而且全部保留:(记录数和字段数),将这种结果成为: 笛卡尔积(交叉连接): 笛卡尔积没什么卵用, 所以应该尽量避免.
-- 多表数据源
select * from my_student,my_class;
子查询
- 子查询: 数据的来源是一条查询语句(查询语句的结果是二维表)
- Select * from (select 语句) as 表名;
-- 子查询
select * from (select * from my_student) as s;
where子句
- Where子句: 用来判断数据,筛选数据.
- Where子句返回结果: 0或者1, 0代表false,1代表true
- 判断条件:
- 比较运算符: >, <, >=, <= ,!= ,<>, =, like, between and, in/not in
- 逻辑运算符: &&(and), ||(or), !(not)
- Where原理: where是唯一一个直接从磁盘获取数据的时候就开始判断的条件: 从磁盘取出一条记录, 开始进行where判断: 判断的结果如果成立保存到内存;如果失败直接放弃
-- 增加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;
--Between本身是闭区间; between左边的值必须小于或者等于右边的值,不然就会查找失败
select * from my_student where height between 190 and 180;
group by 子句
- Group by:分组的意思, 根据某个字段进行分组(相同的放一组,不同的分到不同的组)
- 基本语法: group by 字段名;
-- 根据性别分组
select * from my_student group by sex;
- 分组的意思: 是为了统计数据(按组统计: 按分组字段进行数据统计)
- SQL提供了一系列统计函数
- Count(): 统计分组后的记录数: 每一组有多少记录
Max(): 统计每组中最大的值 - Min(): 统计最小值
- Avg(): 统计平均值
- Sum(): 统计和
-- 分组统计: 身高高矮,年龄平均和总年龄
select sex,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;
- Count函数: 里面可以使用两种参数: *代表统计记录,字段名代表统计对应的字段(NULL不统计)
select sex,count(*),count(age),max(height),min(height),avg(age),sum(age) from my_student group by sex;
- 分组会自动排序: 根据分组字段:默认升序(asc)
Group by 字段 [asc|desc]; -- 对分组的结果然后合并之后的整个结果进行排序
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; -- 多字段排序
- 扩展--回溯统计
having 子句
- Having子句: 与where子句一样: 进行条件判断的.
- Where是针对磁盘数据进行判断: 进入到内存之后,会进行分组操作: 分组结果就需要having来处理.
- Having能做where能做的几乎所有事情, 但是where却不能做having能做的很多事情.
- 分组统计的结果或者说统计函数都只有having能够使用.
-- 求出所有班级人数大于等于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 ; --失败,where不能使用函数count等
- Having能够使用字段别名: where不能: where是从磁盘取数据,而名字只可能是字段名: 别名是在字段进入到内存后才会产生
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 ; -- 失败 where不能使用别名
order by子句
- Order by: 排序, 根据某个字段进行升序或者降序排序, 依赖校对集.
- 使用基本语法
- Order by 字段名 [asc|desc]; -- asc是升序(默认的),desc是降序
-- group by 排序
select * from my_student group by c_id;
-- order by 排序
select * from my_student order by c_id;
- 排序可以进行多字段排序: 先根据某个字段进行排序, 然后排序好的内部,再按照某个数据进行再次排序
-- 多字段排序: 先班级排序,后性别排序
select * from my_student order by c_id, sex desc;
limit子句
-
Limit子句是一种限制结果的语句: 限制数量.
-
Limit有两种使用方式
- 只用来限制长度(数据量): limit 数据量
-- 查询学生: 前两个 select * from my_student limit 2;
- 限制起始位置,限制数量: limit 起始位置,长度
-- 查询学生: 前两个 select * from my_student limit 0,2; -- 记录数是从0开始编号 select * from my_student limit 2,2; select * from my_student limit 4,2;
连接查询
-
连接查询是将多张表进行记录的连接,按照某个指定的条件进行数据的拼接
-
连接查询结果得到的记录数可能会有变化,字段数一定会增加
-
连接查询的意义:当用户查看数据的时候,需要显示的数据来自多张表
-
连接查询分类:内连接、外连接、自然连接、交叉连接
交叉连接
-
cross join:从一张表中循环取出每一条记录,每条记录都会去另外一张表进行匹配,因为没有匹配条件,所以匹配结果一定会保留,最终连接本身字段就会增加(保留),结果就是笛卡尔积,得到的数据没有什么意义
-
基本语法
- 左表 cross join 右表
- from 左表,右表 --就是多表查询
select * from my_pri1 cross join t2; select * from my_pri1,t2;
内连接
- inner join:从左表中取出每一条记录,去右表中与所有的记录进行匹配,匹配必须是某个条件,在左表中与右表中相同时,最终才会保留结果,否则不保留
- 基本语法
- 左表 [inner] join 右表 on 左表.字段 = 右表.字段
- on表示连接条件,条件子弹就是代表相同的业务含义,就是当 左表.字段 = 右表.字段时才会保留结果
select * from my_pri1 inner join t2 on my_pri1.pri_id = t2.id;
--简写,就是使用join,省略inner
select * from my_pri1 join t2 on my_pri1.pri_id = t2.id;
-- 使用别名
select * from my_pri1 as p join t2 as t on p.pri_id = t.id;
-- 省略 as
select * from my_pri1 p join t2 t on p.pri_id = t.id;
- 若是内连接不使用on之后的条件进行查询,就相当于交叉连接,得到的结果也是一个笛卡尔积
- 内连接可以使用where代替on关键字,但是where没有on效率高
select * from my_pri1 p join t2 t where p.pri_id = t.id;
外连接
-
outer join:以某张表为主(主表),取出主表中的每一条数据,然后每条记录与另外一张表(副表)进行连接,不管能不能匹配上条件,最终都会保留,能匹配,正确保留,不能正确匹配的,将副表的字段全部置空
-
外连接分类
- left join:左外连接(左连接),以左表为主表
- right join:右外连接(右连接),以右表为主表
-- 左连接 select * from my_pri1 p left join t2 t on p.pri_id = t.id; -- 右连接 select * from my_pri1 p right join t2 t on p.pri_id = t.id;
- 虽然左连接和右连接有主表差异, 但是显示的结果: 左表的数据在左边,右表数据在右边.
- 左连接和右连接可以互转.
自然连接
-
natural join:自然连接就是自动匹配连接条件,系统以字段名字作为匹配模式,同名字段就作为条件,多个同名字段都作为条件
-
自然连接分类
- 自然内连接:左表 natural join 右表
- 自然连接自动使用同名字段作为连接条件,连接之后合并同名字段
select * from my_pri1 natural join t2;
- 自然外连接: 左表 natural left/right join 右表
select * from my_pri1 natural left join t2;
外键
-
外键:foreign key,外面的键,键不在自己表中
-
如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键
增加外键
-
外键可以在创建表的时候或者创建表之后增加,但是要考虑数据的问题
-
一张表可以有多个外键
-
创建表的时候增加外键,在所有的表字段之后增加外键, foreign key(外键字段) references 外部表(主键字段)
create table my_foreign1(
id int primary key auto_increment,
name varchar(20) not null comment '学生姓名',
c_id int comment '班级id', -- 普通字段
-- 增加外键
foreign key(c_id) references my_class(id)
)charset utf8;
- 在新增表之后增加外键:修改表结构
- alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段)
-- 创建表
create table my_foreign2(
id int primary key auto_increment,
name varchar(20) not null comment '学生姓名',
c_id int comment '班级id' -- 普通字段
)charset utf8;
-- 增加外键
alter table my_foreign2 add
-- 指定外键名
constraint student_class_1
-- 指定外键字段
foreign key(c_id)
-- 引用父表主键
references my_class(id);
更新、删除外键
- 外键不能修改,只能先删除后新增
- alter table 表名 drop foreign key 外键名
- 一张表中可以有多个外键,但是名字不能重复,删除的时候必须指定外键的名字
-- 删除外键
alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;
外键作用
- 外键默认的作用有两种,一个是对父表,一个对字表(外键字段所在的表)
- 对子表约束,字表数据进行操作(增和改)的时候,如果对应的外键字段在父表找不到对应的匹配,则操作会失败(约束字表数据操作)
-- 插入数据:外键字段在父表中不存在
insert into my_foreign2 values(null,'张自忠',4); -- 没有4班级
insert into my_foreign2 values(null,'项羽',1);
insert into my_foreign2 values(null,'刘邦',2);
insert into my_foreign2 values(null,'韩信',2);
- 对父表约束:父表数据进行操作(删、改都必须涉及到主键本身),如果对应的主键在子表中已经被数据引用,那么就不允许操作
-- 更新父表记录
update my_class set id = 4 where id = 1; -- 失败: id=1记录已经被学生引用
update my_class set id = 4 where id = 3; -- 可以: 没有引用
外键条件
- 外键要存在:首先必须保证表的存储引擎是innodb(默认的存储引擎,如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果
- 外键字段的字段类型(列类型)必须与父表的主键类型完全一致
- 一张表中的外键名字不能重复
- 增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应
外键约束
- 外键约束就是外键的作用
- 外键约束有三种约束模式,都是针对父表的约束
- District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录
- Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除
- Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空
- 通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作指定模式的语法
- Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade
- 更新操作: 级联更新
- 删除操作: 置空