MySql

  • 语法
  • SQL常用语法
  • 内连接语法
  • 外连接语法
  • 子查询
  • 分页查询
  • 联合查询
  • DDL数据定义
  • 约束
  • 主键约束和唯一约束的区别
  • 列级约束和表级约束的区别？
  • 关于自增长的使用情况
  • delete和truncate和drop三者之间的区别
  • 事务
  • 事务的概念
  • 事务的特性&隔离级别
  • 一.事务的特性 ACID
  • 二.事务的隔离级别
  • （面试题）优化SQL-遵循原则
  • 面试题 - 在项目上线后的一段时间，用户反馈查询数据越来越慢？
  • 解决方案：


语法

SQL常用语法

#是否开启profiling
show VARIABLES like '%profiling%';
#开启profiling  ON开启  OFF关闭
set profiling = 1;

#第一次查看：查看girl表中的数据
select * from girl;
#第二次查看：查看girl表中的数据
select * from girl;

#查看近期执行的sql的情况
show profiles;

#查看是否开启缓存
show VARIABLES like '%cache%';
-- query_cache_type  ON  缓存已开启
-- query_cache_size  0   缓存的大小
-- 在全局设置缓存大小
set global query_cache_size = 1024*1024*24;

# 插入数据，会清空缓存
insert into girl(id,name,phone) values(null,'达达','12312312312');

#通过Query_ID，来查看sql执行步骤
show profile cpu,block io for query 87;


#查看MySQL支持的存储引擎
show ENGINES; 
-- Supports transactions, row-level locking, and foreign keys
-- InnoDB存储引擎是默认支持的，它支持事务、行锁、外键。

#查看当前MySQL版本默认支持的存储引擎
-- default_storage_engine  InnoDB
show VARIABLES like '%storage_engine%';
-- 查看当前MySQL支持的事务隔离级别
select @@tx_isolation;
-- 设置 当前会话 | 数据库系统的全局的 MySQL连接的隔离级别
set session|global transaction isolation level 隔离级别;
-- 关闭事务的自动提交
set autocommit = 0;
show VARIABLES like '%autocommit%';  -- OFF
-- 执行回滚
rollback;
-- 提交
commit;
-- 查看表中的索引
show index from customer;
--  执行删除表中所有索引的存储过程
call proc_drop_index('testdql','emp');

内连接语法

1.语法
-- SQL92
select 查询列表
from 表1 别名 , 表2 别名
where 连接条件  and   筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

-- SQL99
select 查询列表
from 表1 别名 [INNER] JOIN 表2 别名 
on 连接条件
where 筛选条件
group by 分组列表
having 分组后筛选
order by 排序列表;

2.执行顺序
from -> inner join on -> where -> group by -> having -> select -> order by

外连接语法

1.语法：
select 查询列表
from 表1 别名 left | right | full [outer] join 表2 别名
on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表

2.注意：
2.1 左外连接：查询左表中的全部数据。 left [outer] join
	左外连接的数据 = 等值连接数据 + 左表中有而右表中没有的数据
2.2 右外链接：查询右表中的全部数据。 right [outer] join
	右外连接的数据 = 等值连接数据 + 右表中有而做表中没有的数据
2.3 全外连接（MySQL不支持）：full [outer] join
	全外连接的数据 = 
	等值连接数据+左表中有而右表中没有的数据+右表中有而做表中没有的数据

子查询

按子查询出现的位置进行分类：
1、select后面
要求：子查询的结果为单行单列（标量子查询）
2、from后面
要求：子查询的结果可以为多行多列
3、where或having后面
要求：子查询的结果必须为单列；单行子查询；多行子查询
4、exists后面
要求：子查询结果必须为单列（相关子查询）其中“where或having后面”是重点：

单行子查询 特点：子查询的结果集只有一行一列

多行子查询 特点：子查询的结果集有多行一列

分页查询

1.语法
select 查询列表
from 表1 (left | right) join 表2 on 连接条件
where 筛选条件
group by 分组列表
having 分组后筛选条件
order by 排序列表
limit 起始条目数(默认从0开始) ,
显示的条目数;

2.执行顺序 from -> join on -> where -> group by -> having -> select -> order by -> limit

3.注意 ①起始条目索引如果不写，默认是0 ②limit后面支持两个参数 参数1：显示的起始条目索引 参数2：条目数

联合查询

select 查询列表
from 表1
where 筛选条件 union / unionall select 查询列表 from 表2 where 筛选条件

注意：

1.多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致
2.union实现去重查询 union all 实现全部查询，包含重复项

DDL数据定义

数据类型
数值型

int：整型，默认值是int(11)，此处11为允许写的数值长度 int占4个字节
double/float：浮点数类型，double(5,2) 有效位数为5位，小数点后允许有2位
decimal：定点数类型，在表示钱方面使用该类型，因为不会出现精度缺失问题；

总结：

1.浮点数和定点数都可以用类型名称后加“（M,D）”的方式来表示。 M：精度 ，该值的整数位+小数位一共显示M位数字 D：标度， 小数位数一共显示D位数字，如果不够后面用0补齐，如果超过，则四舍五入

2.浮点数和定点数的区别： a) 定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用于表示货币等精度高的数据； b) 在不指定精度时，浮点数默认会按照实际的精度来显示，而定点数在不指定精度时，默认M=10，D=0；

字符型

char：
1.固定长度字符类型
2.长度0-255 char(9)
3.按照声明时的分配长度，则占多长
4.直接使用char，可以不指定长度，默认是1
5.存储性别、标记等
6.char在查询数据时，删除末尾空格

varchar：
1.可变长度字符类型
2.长度0-65535
3.实际使用多少，则占多少
4.必须指定长度
5.存储姓名、密码、手机号、家庭地址等等
6.varchar在查询数据时，保留末尾空格

text：
1.字符类型，存储长文本
2.存储商品的描述、评价等长文本

时间类型

date:日期类型
time:时间类型
datetime:时间与日期
timestamp:时间戳 （除非自己填写数据，否则直接获取当前时间插入）

注意：时间格式问题
yyyy-MM-dd HH:mm:ss  或  yyyyMMddHHmmss

二进制类型

TinyBlob 255
Blob 65K
MediumBlob 16M
LongBlob 4G

Blob：字节类型；可以用来存储图片数据jpg、 音乐mp3 、 视频avi。

约束

约束条件

1.主键约束 primary key 标示当前的字段唯一且非空 ， 整张表中只允许有一个主键，可以有联合主键

2.外键约束 foreign key 当前字段的值一定来源于某张表中的主键值 ， 整张表允许设置多个外键约束

3.非空约束 not null 当前字段不能为空，必填，整张表允许设置多个非空约束

4.默认约束 default 当前字段可不填写值，会有默认值，需要插入时使用default关键字，允许设置多个

5.唯一约束 unique 当前字段不允许重复，用于昵称、手机号等唯一字段，整张表允许设置多个唯一约束

6.检查约束 check MySQL不支持,Oracle支持。插入的值必须在范围内

主键约束和唯一约束的区别

关键字            意义        出现的次数      联合键
主键约束      PRIMARY KEY     非空且唯一     仅有一个        支持
唯一约束  	  UNIQUE          唯一           允许有多个      支持

列级约束和表级约束的区别？

列级约束：
1.直接在声明字段之后 字段名 数据类型 约束条件
2.支持主键约束、默认约束、唯一约束、检查约束、非空约束
3.不能自定义约束名称，一般默认使用字段名作为约束名称

表级约束
1.在所有字段声明结束之后
2.支持主键约束、唯一约束、检查约束、外键约束
3.若使用constraint，自定义约束名称

关于自增长的使用情况

1.查看自增长的情况 show VARIABLES like ‘%auto_increment%’;

2.修改自增长的初始值及步长 set auto_increment_offset = 10; set auto_increment_increment = 10;

3.自增长只能搭配int数据类型使用么？ 不是的，auto_increment可以跟数值型（int、double）数据使用； 但是不可以搭配字符类型varchar使用，会报错1063 - Incorrect column specifier for
column ‘name’

4.自增长只能搭配primary key主键使用么？
auto_increment可以搭配key（主键、外键、唯一）一起使用，一般搭配主键较多；
而且必须搭配，否则报错1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

5.自增长能否在一张表中多次使用呢？
auto_increment在一张表中，至多只能搭配一个key进行使用。 否则报错1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

delete和truncate和drop三者之间的区别

delete
	1.DML操作  表中数据
	2.删除时可携带where条件
	3.语法：delete from 表名 where 条件;
	4.删除后，返回受影响的行数  Affected rows: 3
	5.支持TCL操作，删除后的数据，可以通过事务回滚rollback，进行恢复
	6.删除所有数据后，自增长的值会紧接着继续往下增长 

truncate
	1.表中数据
	2.删除时不可以携带where条件
	3.语法：truncate table 表名;
	4.删除后，不返回受影响的行数
	5.不支持TCL操作
	6.删除所有数据后，自增长的值会恢复起始值继续往下增长 
	7.直接删除整张表的数据，不会影响表的结构
	8.truncate执行效率比delete高一些

drop
	1.DDL操作 表
	2.删除时不可以携带where条件
	3.语法：drop table [if EXISTS] 表名;
	4.删除后，不返回受影响的行数
	5.不支持TCL操作
	6.删除表结构，将同时释放创建表时所分配的一切资源

事务

事务的概念

事务的概念：
  一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句
		要么全部执行成功，要么全部执行失败！

事务的特性&隔离级别

一.事务的特性 ACID
1.原子性（Atomicity）：在事务执行过程中，所有的操作要么都做，要么都不做。
2.一致性（Consistency）：在事务执行过程中，数据的改变保持一致。
3.隔离性（Isolation）：允许多个事务并发访问数据库操作，防止在并发访问操作数据时，带来的干扰。
4.持久性（Durability）：事务结束后，操作是永久的，保存至磁盘上，即使数据库崩溃，也能恢复数据。
二.事务的隔离级别
2.1 事务的并发问题，即多个事务并发访问数据库带来的影响
	  
		脏读：用户读了修改后未提交的数据(rollback产生)
		不可重复读：读取同一条记录，但是两次数据不一样(两次提交同一条记录)
		幻读：读取同一张表，两次记录数不一样(两次提交不同的记录)
		
		解决方案：采取必要的隔离级别的设置，来解决多个事务并发访问数据库带来的影响。
		
2.2 事务的隔离级别

		读未提交READ UNCOMMITTED：脏读、不可重复读、幻读都是可能发生的。
		读提交READ COMMITTED：不可重复读、幻读都是可能发生的。
		可重复读REPEATABLE READ：幻读都是可能发生的。
		串行化读SERIALIZABLE：以上情况皆不会发生。
		
		MySQL：默认的隔离级别是 可重复读REPEATABLE READ，支持以上四种隔离级别。
		Oracle：默认的隔离级别是 读提交READ COMMITTED，支持读提交READ COMMITTED、串行化读SERIALIZABLE。

（面试题）优化SQL-遵循原则

1.全值匹配我最爱 （where筛选条件，建立复合索引）
2.最佳左前缀法则 （严格按照复合索引顺序）
3.索引列上不进行计算
4.where后的条件判断会使右侧索引失效，尽量将条件放置在最后
5.where后不能使用不等于（!= 或者 <>）
6.is not null也无法使用索引，但是is null可以使用索引的
7.like不能以通配符%开头
8.字符串不加单引号索引会失效
9.左外连接：尽量小表驱动大表，尽量在右表中建立索引
10.inner join时，mysql会自动帮助把小结果集的表选为驱动表，直接在大表中建立索引

11.排序分组，出现
		1）、无过滤，不索引
		2）、顺序错，必Using filesort
		3）、方向反，必Using filesort

12.不要使用select * 查询数据，一般策略，查询的字段可以尽量是索引
覆盖索引，即select 到 from 之间查询的列 <=使用的索引列+主键

如何优化？
尽量where后面的字段建立索引，并且若是复合索引则严格按照建立索引时的顺序写。

如何避免Using filesort？
		1）、尽量排序字段建立索引
		2）、若排序，顺序尽量一致（要么都降序要么都升序）

面试题 - 在项目上线后的一段时间，用户反馈查询数据越来越慢？

解决方案：

1.先开启慢日志查询，定阈值，让项目跑上一个月
set GLOBAL slow_query_log=1;
set session long_query_time=0.1;

2.拿到慢日志中，查询数据较慢的SQL
日志分析工具mysqldumpslow

3.EXPLAIN 查看执行计划
EXPLAIN  select  ....
参考重要指标：id  key  key_len  rows

4.合理建立索引
create index 索引名 on 表(字段,...);

#能干什么：查询所有用户正在干什么
SHOW PROCESSLIST;

#如果出现不顺眼的
kill [id] kill 733;