3.表数据的CRUD

数据的CRUD操作

• 新增数据
• 修改数据
• 删除数据
• 查询数据

MySQL数据的命令不区分大小写

添加数据 INSERT INTO

• 方式一 （不推荐）

insert into <tableName>  values(val1, val2 , val3 ... ) ;

此操作要求 录入的 值的数量必须和 表中的字段数量保持完全一致

要求 录入值的顺序 和 表中字段定义的顺序 保持一致 (可以使用 DESC 命令查看表结构字段的顺序)

在录入数据的时候，字符串必须用 单引号 引起来

获取 当前系统时间 ，可以用 now() 函数 ，如果不需要录入，以 null 来标识即可

上述添加数据的方式不推荐使用

• 方式二 (推荐且常用)

insert into  <tableName>(col1, col2, col3, ....) values(val1, val2, val3, ...) ;

insert into t_book(id, price, author, book_name, create_time, status) values(2, 10, '古龙', '小李飞刀',  now(), '1');

推荐的写法

从 插入语句中能够直观的看到 要录入的字段有哪些，可以进行 部分字段值的录入

字段名在 定义的时候，不需要和数据库中定义的顺序保持一致

值的顺序 只需要 和 在 插入命令上的 字段顺序保持一致 即可

批量 添加数据

insert into  <tableName>(col1, col2, col3, ....) values 
(val1,  val2, val3, .... ),
(val1, val2, val3 , ....),
(val1, val2, val3, ...),
... ;

insert into t_book(id, price, author, book_name, create_time, status) values
(3, 30, '辰东', '神墓',  now(), '1'),
(4, 40, '辰东', '遮天', now(), '1');

简单查询数据

select * from <tableName> ;   -- 查询表中所有的数据

修改数据 UPDATE

• 全表数据进行修改 (不常使用)

update <tableName>  set <columnName> = <columnVal> , <columnName> = <columnVal> .... ;   -- 表中所有的数据都会被修改

-- 将 所有的书籍降价到 80% , 并 重置 更新时间
update t_book set price = price * 0.8 , update_time = now() ;

• 带 条件 的数据更新 (常用的操作)

update <tableName>  set <columnName> = <columnVal> , <columnName> = <columnVal> ....  where 条件 ;

-- 将 作者 `辰东`的书籍 全部涨价 50% 
update t_book set price = price * 1.5 , update_time = now()  where author = '辰东' ;

where 条件

适用于 修改、删除、和查询

• 关系条件 > , >= , < , <= = 和 != 或者 <>(不等号)

-- 将 价格 低于50的 书籍 全部 下架 
update t_book  set status = '0', update_time = now()  where price < 50 ;

• 逻辑条件 and (与) or（或）

-- 将 价格 在 30 以下 或者 作者 为 辰东 的书籍  全部 上架
update t_book status = '1' , update_time = now() where price < 40 or author = '辰东' ;

• 枚举条件 in ， not in

-- 修改 书籍编号  1， 3 ， 5 的书籍信息，将价格 + 10 元
update t_book set price = price + 10 where id in (1, 3, 5) ;

• 区间条件 between … and

-- 查看书籍价格在 30 ~ 50 之间的书籍信息
select * from t_book where price >=30 and price <=50 ;
select * from t_book where price between 30 and 50 ;  -- 前后都包含

• 空值查询 is null / is not null

-- 将 书籍作者为空的 书籍 作者 设置为 匿名
update t_book set author = '匿名' where author is null ;

• 模糊查询条件 like

  %（百分号）	_ (下划线)
  匹配 任意 0~ N个字符	匹配 任意 1个字符

-- 查询 图书名 以 神 开头的 书籍 
select * from t_book where book_name like '神%' ;
-- 查询 作者 以 ... 东结尾的 书籍
select * from t_book where author like '%东' ;
-- 查看 作者 中 包含 小东 的 书籍 
select * from t_book where author like '%小东%' ;

-- 查看 图书名以 神开头 、且 长度 为2 的数据
select * from t_book where book_name like '神_' ;

删除数据 DELETE

• 全表删除

delete from <tableName> ;   -- 删除表中所有数据 (慎用)

-- 假如 确定要删除表中所有的数据，那么也不推荐使用 delete , 因为 delete 只删除数据、而不删除 数据所占用的空间

truncate table <tableName> ;  -- 删除表中所有数据的 推荐写法， 原因是 不仅仅把数据删了，还把数据占用的空间也清除了

• 带条件删除

delete from <tableName> where 条件  ;  -- 条件 请参数 where 条件的用法

查询数据

• 简单查询
• 子查询
• 嵌套查询
• 集合查询
• 关联查询

简单查询

• SELECT 查询
• 基于表的查询
• where条件查询
• group by分组查询
• having 分组筛选查询
• order by排序
• limit 分页查询

SELECT 查询

select  1  ;   -- 一般是系统进行心跳检测使用的
select  now() ;  -- 获取当前系统时间
select version() ; -- 获取数据库版本号
select user() ;  -- 获取 当前登录的用户信息

基于表的查询

select <columnName>,  <columnName> , ...  from  <tableName> ; 

如果要查询 表中所有的列，可以是用 *来代替

不推荐使用 select * 来查询数据、因为这种查询 性能低

再查询列的时候，还可以给列 设置别名 select as , as 还可以省略不写

tableName 也可以 设置 别名

where条件查询

请参考 where条件

group by 分组查询

• 聚合函数 ：将多条记录进行一个聚合，返回一条记录， 聚合函数不会对空值参与统计

count :  统计个数

-- 查询 书籍表中 有多少 书籍
select count(id) from t_book ;
select count('*') from t_book ; 
select count(1) from t_book ; 

PS : 性能: count(id) >  count(1) > count('*') ,  如果统计字段，那么 空值不参与统计的

sum : 求和

-- 查看所有图书的总价,  sum 只能统计 字段
select sum(price) from t_book ;   

avg : 求平均值
max : 求最大值
min : 求最小值

• 分组 一般都需要配合 聚合函数实现数据的查询

-- 查询 书籍表 每个作者有多少本书
select count(1), author  from t_book where author is not null group by author ;

分组查询 对查询的字段 是有要求的

1. 查询的字段出现在 聚合函数中
2. 查询的字段出现在 group by 的后面

having 分组筛选

-- 查询 书籍表 中 价格 相同的 价格
select price from t_book  group by price having count(1) > 1

-- 查询 书籍表中， 同作者的 书籍平均价格 > 40 的作者信息
select author from t_book group by author having avg(price) >40;

where 和 having的区别

where 是对表中的每一条记录 进行筛选和过滤， having 是对 分组后的结果 进行筛选和过滤

where 比 having 先执行， 能用 where 进行过滤 就不要使用 having 过滤

order by 排序

asc	desc
升序，如果不指定排序规则， 默认升序	降序

-- 根据图书价格 降序排列
select * from t_book order by price desc ;
-- 多字段排序
select * from t_book order by price desc , create_time desc ;

当有多个字段进行排序的时候，只有当前面的字段 值相同的时候，才会使用 后面指定的字段进行排序。

limit 分页查询

select * from t_book order by price desc  limit  [<offset>, ]  <rows>

offset : 查询数据的偏移量，默认从0开始，可以省略，如果省略， 代表 值为 0

rows : 每页查询的条数

offset = (page - 1) * rows , page 代表页码

简单查询的完整SQL结构

select <coloumnName> ....  from <tableName>  where  <condition>  
group by column, ... having <condition> order by column <desc|asc>  limit offset , rows ;

子查询

• 基于列的子查询
• 基于条件的子查询

基于列的子查询

子查询 出现在 查询的列上

-- 查询 用户为1 的 手机号，邮箱和真实姓名
select tel ,email, 
   (select real_name from t_user_info where user_id = u.id ) as real_name
from t_user u where id = 1;

所有的子查询都必须用 括号 括起来

基于列的子查询 ， 子查询 必须返回的是 单列 单值

基于条件的子查询

子查询出现在 where 条件上

• 关系条件子查询

-- 查询 书籍表中 价格最高的 图书信息
select * from t_book where price = (select max(price) from t_book);
-- 查询 书籍表中 价格超过 所有书籍价格平均值的 图书信息
select * from t_book where price > (select avg(price) from t_book) ;
-- 查询 书籍表中 同一个作者所有书籍 超过他自己书籍的平均值的 图书信息
select * from t_book b where  b.price >
	(select avg(price) from t_book t where t.author = b.author)

关系条件子查询 返回的是 单列 单值

• in 子查询

-- 查询 一本书 以上的所有作者
select * from t_book where author in (
	select author from t_book group by author having count(1) > 1 
);

基于 in 条件子查询，返回的是 单列 多值

• exists 子查询

-- 查询 一本书 以上的所有作者
select * from t_book b where exists 
 ( select 1 from 
     (select author from t_book group by author having count(1) > 1 ) a 
		 
	  where b.author = a.author	 
 )

-- 查询 书籍表中 同一个作者所有书籍 超过他自己书籍的平均值的 图书信息
select * from t_book b  where exists (
    select 1 from 
		   (select avg(price) avg, author from t_book group by author) a
		where a.author = b.author and b.price > a.avg	 
);

子查询 和 主查询 在数据上有一定的关联关系

嵌套查询

• 将一个查询的结果、当作一张表、继续对这个查询的结果表 进行查询的过程

select a.* from (
	select author,  count(1) count from t_book group by author
) a ;

在嵌套查询中，查询出来的 临时表 在使用的时候，必须设置 别名

with 临时表查询

with temp as (
    select author,  count(1) count from t_book group by author
),  temp2 as (
     select * from t_user where ..... ;
)
select * from temp , temp2 ;

MySQL 8.0 支持的写法

集合查询

• 并集 union 或者 union all

-- 查询 金庸 对应的 书籍个数 和 最高价格
select author, count(1) count , max(price) max from t_book where author = '金庸' 
union 
-- 查询 古龙 对应的 书籍个数 和最高价格
select author, count(1),  max(price) from t_book where author  = '古龙' ;

集合查询 最终查询的结果 字段名 以 第一个 SQL语句 为准

集合查询中 连接的多个 查询语句，查询的列的个数 和 列的含义 必须保持一一对应

union 会将多个查询的结果 合并到一块，自动去除重复的数据

Union all 会将多个查询的结果 合并到一块，不会 去重

• 交集 intersect
• 差集 minus

关联查询

• 表与表之间如果有关联关系、或者数据与数据之间有关联关系，那么可以采用关联查询进行数据的查询

关联关系

• 一对一
• 一对多/多对一
• 多对多

关系型数据库 是非常善于处理关联关系的，那么 关联关系 主要是通过 外键 实现的

一对一(one to one)的关联关系

一对一的关联关系 在 生活中，是比较少见的。例如 用户 和 用户信息 的关系

一对一的关联关系 让 两个表 形成 主表 和 附表 的关系，通常在 附表 中 维护 主表的关系

一对一的关系维护方式 通常由2种，

​ 第一种是 外键 + 唯一键 实现

​ 第二种是 主键共享 实现的

外键 + 唯一键 实现 一对一的关联关系 

create table t_user(
    id int primary key auto_increment , 
    tel varchar(11) unique not null comment '手机号', 
    password varchar(128) not null comment '密码' ,
    email varchar(50) not null comment '邮箱' ,
    create_time datetime comment '注册时间', 
    last_login_time datetime comment '最近登录时间', 
    status tinyint default 1 comment '账户状态, 0 拉黑  -1 删除'
);

create table t_user_info (
	id int primary key auto_increment , 
    real_name varchar(50) comment '真实姓名', 
    nick_name varchar(50) comment '昵称', 
    -- 维护关系的 外键
    user_id int unique comment '用户ID', 
    -- 通常需要给 外键 添加约束
    constraint t_user_info_user_fk_user_id foreign key(user_id) references t_user(id)
);

主键共享 实现 一对一的关联关系

create table t_user(
    id int primary key auto_increment , 
    tel varchar(11) unique not null comment '手机号', 
    password varchar(128) not null comment '密码' ,
    email varchar(50) not null comment '邮箱' ,
    create_time datetime comment '注册时间', 
    last_login_time datetime comment '最近登录时间', 
    status tinyint default 1 comment '账户状态, 0 拉黑  -1 删除'
);

create table t_user_info (
	id int primary key  ,   -- 不能使用自动增长
    real_name varchar(50) comment '真实姓名', 
    nick_name varchar(50) comment '昵称', 
    constraint t_user_info_fk_id foreign key (id) references t_user(id)
) ;

一对多（one to many）的关联关系

• 资源表 —> 资源 和 用户 是 多对一的 ，一个用户可以上传多个资源，而一个资源 一定属于 某一个用户上传的

create table t_resource(
   id int primary key auto_increment , 
    resource_name varchar(100) not null comment '资源名称', 
    resource_desc text comment '资源描述',
    keywords varchar(200) comment '关键字',
    score int comment '资源积分', 
    size bigint comment '资源大小' ,
    ext varchar(20) comment '资源后缀', 
    resource varchar(100) not null comment '资源', 
    type_id int comment '资源类型ID', 
    -- 关系属性 ？？？
    user_id int comment '上传的用户ID',
    foreign key(user_id) references t_user(id),
    foreign key(type_id) references t_resource_type(id)
);

在 一对多 的关系中， 在 多的一方，维护一 的 一方 的关系

多对多 (many to many)的 关联关系

• 用户 和 角色的关系 是 多对多的， 一个用户 可以安排多个角色， 一个角色下可以由多个用户

create table t_role (
    id int primary key auto_increment , 
    role_name varchar(50) comment '角色名', 
    role_desc varchar(200) comment '角色描述'
);

-- 第一种 中间表的 创建方式 (常用的方式)
create table t_role_user (
    id int primary key auto_increment , 
    role_id int , 
    user_id int ,
    foreign key (role_id) references t_role(id) ,
    foregin key(user_id) references t_user(id)
);

-- 第二种 中间表的 创建方式 (采用 联合主键的方式)
create table t_role_user (
    role_id int ,
    user_id int ,
    foreign key (role_id) references t_role(id) ,
    foreign key(user_id) references t_user(id) ,
    -- 联合主键
    primary key(role_id,  user_id)
); 

在 多对多的关系中， 通常使用 中间表 来维护关系

数据与数据间的关系

create table t_resource_type(
	id int primary key auto_increment , 
    type_name varchar(50) comment '资源类型名' ,
	pid int comment '上一级资源ID' , 
    foreign key(pid) references t_resource_type(id)
) ;

关联查询

• 左外连接 left [outer] join … on
• 右外连接 right [outer] join … on
• 内连接 [inner] join … on

-- 查询 管理员 上传的所有资源 
select t.*, e.role_name, e.role_desc from t_resource t 
inner join t_role_user r on t.user_id = r.user_id 
inner join t_role e on e.id = r.role_id 
where e.role_name = '管理员' 

左连接: 以左表为主表，将相关的数据查询出来，如果右表没有相关连的数据，则以 空表示

右连接：以右表为主表，将相关的数据查询出来，如果左表没有相关联数据，则以空表示

内连接：只查询两表 具有关联关系的数据

函数

• 字符串函数

函数名	示例	说明
char_length	char_length(‘完美世界’) // 4	获取字符串长度
concat	concat(‘a’ , ‘b’, ‘c’) // abc	拼接多个字符串，如果有为 null 的值，结果为 null
concat_ws	concat(’-’ , ‘a’, ‘b’, ‘c’) // a-b-c	以某一个特定分隔符，拼接多个字符串
insert	insert(‘guest’ , 2, 1, ‘wh’) // gwhest	insert(str, pos , len , newstr) 从 pos位置替换 len长度，内容为 newstr
lower/lcase	lower(‘ABC’) // abc	转小写
upper / ucase	upper(‘abc’) // ABC	转大写
substr/substring	substr(‘abcd’, 2, 3) // ubs	从 指定位置，截取多长，如果没有设置长度，代表截取到尾部
trim/ltrim/rtrim	trim(’ abc ') // abc	去除前后空格
reverse	reverse(‘abc’) // cba	反转字符串

• 数学函数

函数名	示例	说明
abs		求绝对值
ceil		向上取整
floor		向下取整
round(x, y)		四舍五入，保留y 位小数

• 日期函数

函数名	示例	说明
now()		获取当前系统时间
month(date)		获取月份
year(date)		获取年份
dayofmonth(date)		获取天
DATE_ADD	DATE_ADD(now() , interval 1 year)	添加1年
DATE_SUB	DATE_SUB(now() , interval 1 hour)	减少1小时
DATEDIFF	DATEDIFF(now(), date)	计算两个日期间隔的天数
DATE_FORMAT	DATE_FORMAT(date, ‘%Y-%m-%d %H:%i:%s’)	日期格式化

• 条件函数

函数名	示例	说明
if	if(expr , v1, v2)	expr 如果返回true, 取 v1, 否则 取 v2
ifnull	ifnull(expr , v1)	expr如果返回 null, 取 v1, 否则 取 expr
case … when		相当与 多分支条件判断

• 加密函数

函数名	示例	说明
md5	md5(str)	对字符串进行 MD5加密

视图View

• 是一个虚拟的表，本质上是一个 SQL查询语句，是真实表的一个数据映射
• 作用: 1. 简化查询， 2. 提高数据的安全性( 和 授权有关 )

视图的创建

create  [or replace]  view <viewName>  as select .... ;

视图 一般不推荐 删除数据、修改数据、新增数据

视图是一个真是表的数据映射，操作数据本质上还是操作 真实的表，

视图的结果有可能来自 聚合查询，所以无法进行删除和修改等操作

删除视图

drop view [if exists] <viewName>

索引 Index

如果表中的数据过大、那么查询的速度就会逐渐变慢，所以需要对查询做优化，

那么索引 是 提高查询效率的 有效手段之一

索引 是 基于字段的、一张表中索引不是越多越好，因为索引过多，会导致索引维护困难

索引的种类

• 唯一索引 unique
• 普通索引 normal
• 倒排索引 fulltext （全文检索）

什么样的字段适合添加索引

• 主键 自带索引
• 唯一键自带 唯一索引
• 外键 自带索引
• 字段 的值 在整个表中 重复概率 很低、适合添加索引
• 经常会出现在 where 条件上的字段 适合添加索引

创建索引

create  [unique|fulltext]  index <indexName> on <tableName>(columName, ...) ;

删除索引

drop index 索引名  on <tableName> ;  
alter table <tableName>  drop index 索引名 ;

分析 SQL 语句

explain select .... 

索引失效

• 模糊查询 — 前模糊 会导致 索引失效
• 条件字段上使用 函数 ，会导致索引失效
• != 查询 也会导致索引失效