Fork me on GitHub

day35

MySQL 数据行操作

1. 增

  • 增加一条数据

    # 插入完整数据
1. 
insert into 表名(字段1,字段2,...,字段n) values (值1,值2,...,字段n);  # 按照选定的字段依次传值

2.
insert into 表名 values (值1,值2,...,字段n); # 如果不选定字段，必须传入表内

# 指定字段插入数据
insert into 表名(字段1,字段2,...) values (值1,值2,...)

  • 增加多条数据

    # 插入完整数据
insert into 表名(字段1,字段2,...,字段n) values 
(值1,值2,...,字段n),
(值1,值2,...,字段n),
(值1,值2,...,字段n)
......
;

# 插入指定数据
insert into 表名(字段1,字段2,...) values
(值1,值2,...),
(值1,值2,...),
(值1,值2,...),
......
;

  • 插入查询结果

    # 复制别的表内数据
insert into 表名 (字段1,字段2,...字段n) select (字段1,字段2,...字段n) from 表2 where 条件;

2. 改

  • 修改数据

    # 将按照条件搜索出来的数据对应的列名的值改为新值
update 表名 set 列名1=新值1,列名2=新值2 ... where 条件;

3. 删

  • delete

    # 1. 删除所有数据，清空表
delete from 表名；

# 2. 删除表内指定的数据
delete from 表名 where 条件; # 删除根据条件筛选出来的数据

  • truncate

    # 删除所有数据，清空表
truncate 表名;

区别：

  1. 当表内有主键时，比如主键 id ，delete 之后，插入数据依然会从之前最大的主键 id+1，而 truncate 后，主键 id 会从 1 开始；
  2. delete 是将数据一行一行的删除，truncate 是将数据全选删除，所以 truncate 删除的速度是高于 delete 的。

4. 查

4.1 单表查询

查询关键字的使用顺序：

select 字段名 from 表名  where 条件 group by 条件 having 条件 order by 条件 limit 条件;

优先级：

where > group by > having > order by > limit

  • 关键字

    from	    # 后跟表名；指定从哪张表查询
where	    # 后跟条件；根据条件进行查询
group by	# 后跟字段名；按照字段名对数据排序
having	    # 后跟条件，与 group by 连用；对分组后的结果再进行二次筛选
select	    # 后跟字段名或 * ；表示要展示的字段信息，* 表示选择表内所有的字段
distinct	# 用在字段名前，对筛选出来的字段进行去重
order by	# 后跟 字段名 asc/desc；表示对字段进行排序，asc表示升序，desc表示降序，默认为升序
limit		# 限制结果的显示条数

    • select 字段名 from 表名

      1. 查询表内指定字段的信息
select 字段1,字段2,...,字段n from 表名;	# 查询对应表内的字段1，字段2，...,字段n的信息

2. 查询表内所有的信息
select * from 表名;	# 查询表内所有字段的信息

    • distinct

      select distinct 字段名 from 表名;	# 查询表内字段信息，如果字段信息有重复的，则只显示一个
例如：
select distinct name from goods；	# 查询 goods 表内所有商品的名称，并去重

    • where 条件

      select * from 表名 where 条件;
例如：
select * from goods where id < 10；	# 查询 goods 表内 id <10 的所有商品信息

      • 条件

        1. 比较运算符
大于：> ，大于等于：>= ，小于：< ，小于等于：<= ，不等于：!= 或 <>
例如：
id>10 , id>=10 , id<10 , id<=10 , id!=10 , id<>10 

2. between...and...
例如：
id between 10 and 20 	# id 在 10 到 20 之间的（包含 10 和 20 ） 

3. in（值1,值2）
例如：
id in(10,20)	# id 是 10 或者 20 的
name in('张三','李四') # name 是 张三 或者 李四 的

4. 模糊条件（实际使用中最好不要使用，）
只匹配给出的字符，加了 % 表示给定字符的前或后是什么内容都忽略，只要对应位置有给定字符就符合条件
	- 以...开始：like 'ghjk%'	# 查询以 ghjk 开头的信息
	- 以...结束：like '%ghjk'	# 查询以 ghjk 结尾的信息
	- 包含... :  like '%ghjk%'  # 查询包含 ghjk 的信息
	
5. is NULL , is not NULL	# 判断字段是不是 NULl 
例如：
name is NULL		# name 是 NUll 的
name is not NULL	# name 不是 NULL 的
注意：
name is ''			# ''表示空字符串，不是NULL

    • group by

      group by 必须跟在 where 条件 之后。

      group by 字段名	# 将所有记录按照某个相同字段进行分组，比如针对员工的职位分组，或者按照性别分组等

必须和聚合函数连用：
	- 聚合函数：
		- count(id)
		- max(salary)
		- min(salary)
		- avg(salary)
		- sum(salary)
例如：
select gender,count(id) from staff group by gender;	  # 按照性别分组，然后显示每个性别的人数
select gender,max(age) from staff group by gender;	  # 按照性别分组，然后显示每个性别年龄的最大值
select gender,min(age) from staff group by gender;	  # 按照性别分组，然后显示每个性别年龄的最小值
select gender,avg(age) from staff group by gender;	  # 按照性别分组，然后显示每个性别年龄的平均值
select gender,sum(age) from staff group by gender;	  # 按照性别分组，然后统计每个性别的年龄的综合

    • having

      having 必须跟在 group by 之后。

      having 条件 # 过滤，对 group by 之后的数据进行二次筛选

与 where 的区别
执行优先级从高到低：where > group by > having 
1. Where 发生在分组group by 之前，因而 Where 中可以有任意字段，但是绝对不能使用聚合函数
2. having 发生在分组 group by 之后，因而 having 中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

例如：
# 按照性别分组，然后显示平均年龄大于 26 的性别和平均年龄
select gender,avg(age) from staff group by gender having avg(age) >26;

    • order by

      order by 字段名 asc(升序) / desc(降序)	# 将字段排序

例如：
select * staff order by age asc;	# 将表 staff 内的信息按照年龄升序排列
select * staff order by age desc;	# 将表 staff 内的信息按照年龄降序排列

select * staff order by age desc , id asc;	# 将表 staff 内的信息按照年龄降序排列，如果年龄相同，再按照 id 升序排列

    • limit

      limit offset , size		# offset：行数据索引；size：取多少条数据

例如：
select * from staff limit 0,10;		# 将表 staff 的数据从第 1 条开始，取出10条信息
select * from staff limit 10,10;	# 将表 staff 的数据从第 11 条开始，取出10条信息

4.2 多表查询

4.2.1 表间关系

使用外键将表关联起来，使其用某种联系。

  • 一对多

    给 A 表内的字段增加约束，约束是表 B 内的一个字段，且 A 表内该字段的值可重复，B 表内该字段不可重复。

    关联方式：foreign key
创建方法：在被约束的表内添加
1. 创建表时创建：constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
2. 创建完成后添加：alter table 表名 constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

  • 多对多

    给 C 表内的多个字段增加约束，约束是 A 表内的字段和 B 表内的字段，且只有 C 表内的值可以重复。

    foreign key+一张新的表
创建方法：在新表内添加
1. 创建表时创建：constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
2. 创建完成后添加：alter table 表名 constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

  • 一对一

    给 A 表内的字段增加约束，约束是 B 表内的一个字段，且 A、B 两个字段都不可重复。

    关联方式：foreign key+unique
创建方法：在被约束的表内添加
- 创建表时创建：
	1. 先增加一个唯一字段：字段名 列类型 [约束条件] unique;
	2. constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
- 创建完成后添加：
	1. 先增加一个唯一字段：字段名 列类型 [约束条件] unique;
	2. alter table 表名 constraint 外键名 foreign key (被约束的字段) references 约束的表(约束的字段)

4.2.2 多表联查

  • 交叉连接

    # 直接将两张表的所有信息联合起来，生成笛卡尔积
select * from 表A,表B；

  • 左连接

    # 优先显示左表全部记录
select * from 表A left join 表B on A.被外键约束字段=B.约束字段 	# 将两张表通过外键关联到一起的字段将两张表连接起来，如果左边表的某些信息在右边没有，右边的表会显示 NULL

  • 右连接

    # 优先显示左表全部记录
select * from 表A right join 表B on A.被外键约束字段=B.约束字段 	# 将两张表通过外键关联到一起的字段将两张表连接起来，如果右边表的某些信息在左边没有，左边的表会显示 NULL

  • 内连接

    # 只显示匹配的行
select * from 表A inner join 表B on A.被外键约束字段=B.约束字段 	# 将两张表通过外键关联到一起的字段将两张表连接起来，只显示两张表都有内容的信息

    • 连接查询

      将表连接在一起后，就可以根据条件进行筛选想要的结果，方法和单表查询基本一致

select * from 表A inner join 表B on A.被外键约束字段=B.约束字段 where 条件 group by 条件 having 条件 order by 条件 limit 条件;

  • 子查询

    1. 子查询是将一个查询语句嵌套在另一个查询语句中；

    2. 内层查询语句的查询结果，可以为外层查询语句提供查询条件。

    3. 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字；

    4. 子查询中可以包含比较运算符：= 、 != 、> 、< 等；

5. 索引

优点：增加索引会极大的提高查询效率

缺点：创建索引会占用大量的磁盘空间

5.1 为什么使用索引

为了提高查询效率，例如字典中的目录。

5.2 索引的本质

一个特殊的文件。

5.3 索引的底层原理

B+ 树

5.4 索引的种类

  • 主键索引：加速查找、不能重复、不能为空

    primary key ：

  • 唯一索引 ：加速查找，不能重复

    unique(添加索引的字段名)

    • 联合唯一索引

      unique(添加索引的字段名1,添加索引的字段名2)

  • 普通索引：加速查找

    index(添加索引的字段名)

    • 联合索引

      index(添加索引的字段名1,添加索引的字段名2)

5.5 索引的使用

5.5.1 索引的创建

  • 主键索引

    • 创建

      1. 创建表时添加
create table 表名(
id int auto_increment ,
primary key(id)
)charset utf8;
或
create table 表名(
id int auto_increment primary key
)charset utf8;

2. 增加
alter table 表名 change id id int auto_increment primary key;

3. 增加
alter table 表名 add primary key (id);

    • 删除

      alter table 表名 drop primary key;

  • 唯一索引

    • 创建

      1. 创建表时添加
create table t2(
id int auto_increment primary key,
name varchar(32) not null default '',
unique u_name (name)
)charset utf8

2. 增加
create index  索引名 ON 表名 (字段名) ;

3. 增加
alter table 表名 add index 索引名 (字段名)

    • 删除

      alter table 表名 drop index 索引名;

  • 普通索引

    • 创建

      1. 创建表时添加
create table t3(
id int auto_increment primary key,
name varchar(32) not null default '',
index u_name (name)
)charset utf8

2. 增加
create  index  索引名 ON 表名 (字段名) ;

3. 增加
alter table 表名 add index 索引名 (字段名);

    • 删除

      alter table 表名 drop index 索引名;

5.5.2 索引的使用

当使用 SQL 指令进行查询时，如果用到了索引，成为命中了索引。

  • 不会命中索引的几种情况

    • 不能在 SQL 指令中做四则运算，会降低查询效率

    • 使用函数

      例如：select * from 表名 where reverse(email) = 'tom';

    • 类型不一致

      如果列类型是字符串，传入查询条件时没有按照字符串的形式传入

      例如：select * from 表名 where name = tom;	# 应该使用 name = 'tom'

    • 排序条件为索引，则 select 字段必须也是索引字段，否则无法命中

      当根据索引排序时候，select 查询的字段如果不是索引，则速度仍然很慢
例如：select name from 表名 order by age		# 假设 age 字段有索引，name 字段没有索引

特别的：
如果是对主键排序，则不会影响
例如 select * from 表名 order by 主键字段;

    • 组合索引最左前缀

      使用联合索引的情况下：当最左边一个字段存在的时候，就可以命中，否则，不能命中
例如：
index 索引名(字段1,字段2,字段3,字段4)
可以命中的情况：
	where 字段1=值 and 字段2=值 and 字段3=值 and 字段4=值;		
	where 字段1=值 and 字段2=值 and 字段3=值;
	where 字段1=值 and 字段2=值;
	where 字段1=值;
不能命中的清空
	where 字段2=值 and 字段3=值 and 字段4=值;
	where 字段2=值 and 字段3=值;
	where 字段2=值;
	等等......

    • 查看 SQL 指令是否命中索引

      explain SQL指令 \G
例如：
mysql> explain select * from user where name='zekai' and email='zekai@qq.com'\G
*************************** 1. row ***************************
                id: 1
                select_type: SIMPLE
                table: user
                partitions: NULL
                type: ref       索引指向 all
                possible_keys: ix_name_email     可能用到的索引
                key: ix_name_email     确实用到的索引
                key_len: 214            索引长度
                ref: const,const
                rows: 1            扫描的长度
                filtered: 100.00
                Extra: Using index   使用到了索引

    • 慢查询日志

      使用 MySQL 提供的工具记录效率低的 SQL指令，后期进行优化

      • 查看慢SQL的相关变量

        mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------------+
| Variable_name             | Value                                         |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF                                           |
| log_slow_slave_statements | OFF                                           |
| slow_launch_time          | 2                                             |
| slow_query_log            | OFF   ### 默认关闭慢SQl查询日志， on                                          |
| slow_query_log_file       | D:\mysql-5.7.28\data\DESKTOP-910UNQE-slow.log | ## 慢SQL记录的位置
+---------------------------+-----------------------------------------------+
5 rows in set, 1 warning (0.08 sec)

mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |

      • 配置慢SQL变量

        set global 变量名 = 值	# 查询要配置的变量名
set global slow_query_log = on;	# 打开记录功能
set global slow_query_log_file="存储路径";
set global long_query_time=1;	# 设定记录的SQL指令执行时间阈值，超过记录
posted @ 2019-10-31 21:06  Yugaliii  阅读(134)  评论(0)  编辑  收藏  举报