MySQL的再理解

1、sql规范

• 库名、表名、字段名，必须使用小写字母，不得超过30个字符
• 库名、表名、字段名，必须尽量见名知意，使用下划线分割，禁止使用mysql保留字
• 建表时表必须有主键，使用bigint unsigned类型，并使用auto_increment自增标记
• 所有字段及表都必须有注释，存储引擎必须使用InnoDB.
• 必须使用DECIMAL代替FLOAT和DOUBLE，以存储精确浮点数，例如支付相关数据
• 必须使用TINYINT系类型代替ENUM类型，前者只要自己定义，后者却要修改表。
• 禁止使用BLOB类型
• 所有字段必须定义为NOT NULL,定义为defaut 0或者defaut ""
• 表达是与否概念的字段，必须使用is_xxx的方式命名，数据类型是usingned tinyint
• 禁止隐式转换，数值类型禁止加引号，字符和日期类型必须加引号
• 只查询需要的字段，禁止使用select * ，子查询只允许返回主键和必须字段，禁止select *
• 统计行数时，使用count(*)或者count (1)，禁止使用count 字段名
• 禁止使用order by rand() 实现乱序效果，会导致CPU过高
• 禁止单条SQL语句同时更新多个表，拆分成多条SQL，放在一个事务里
• 禁止在SQL中进行算术和函数计算，应放置到应用服务器端
• 必须使用ISNULL（）来判断是否为NULL值。
• 在代码中写分页查询逻辑时，若count为0必须直接返回，避免执行后面的分页语句
• 需要join的字段，数据类型必须绝对一致，多表关联查询时，必须保证被关联的字段需要有索引

2、sql分类

1、DQL（数据查询语言）：查询语句，凡是select语句都是DQL

2、DML（数据操作语言）：insert delete update ，对表中的数据进行增删改

3、DDL （数据定义语言）：create drop alter ，对表结构的增删改

4、TCL（事务控制语言） ：commit 事务提交 ，rollback回滚事务。(TCL中的T是Transaction).

5、DCL (数据控制语言) ：grant授权、revoke撤销权限等。

3、常用基本命令

• select database(); 查看当前使用的数据库
• desc 表名 ；查看表结构
• select version(); 查看mysql的版本号
• show databases; 查看所有的数据库
• use 数据库名； 使用该数据库
• show tables； 查看当前库中的表
• show tables from <数据库名>; 查看其它库中的表
• desc 表名； 查看表的结构
• show create table 表名 ; 查看创建表的语句

4、简单的查询语句

语法格式：select 字段1，字段2，字段3... from 表名

提示：

​ 1、任何一条sql语句都是以"；"结尾的

​ 2、sql语句不区分大小写

​ 3、在sql语句中，字符串一般都是以单引号括起来的，虽然Mysql也支持双引号，但其他数据库不支持

​ 4、as 关键字可用于重命名，也可以省略，用空格代替

查询所有的字段 select * from 表名; //实际开发中不建议使用，效率较低

5、条件查询

语法格式：select 字段1，字段2... from 表名 where 条件；

执行顺序：先 from ，然后 where ，最后再 select

1、查询条件是一个区间的可以使用 between ...and ...，这里需要注意的是betwenn...and...是一个闭区间，也就是大于等于多少，小于等于多少，between...and...除了运用在数字方面，也可以运用于字符串方面。在字符方面就是一个左闭右开的一个区间。

2、is null 运算符的使用，就是查询一个字段是否为空，但is null不能这样写，如= null 会报错。

3、and 和or联合起来使用，当你优先级不确定的时候，加小括号。加小括号的先执行

4、in的作用相当于or一样，找出给定几个值，只要满足其中任何一个值都可以的数据。而not in的作用就是找出不是给定的这几个值的数据。

5、模糊查询like(在模糊查询中，必须掌握的两个特殊符号，一个是%，一个是下划线_);%代表任意多个字符，下划线_代表任意一个字符。

例如：查询名字当中带有“豪”的名字， select name from 表名 where name like '%豪%'；

​ 查询名字当中第二个字是“豪”的名字， select name from 表名 where name like '_豪'；

​ 注意： 查询名字中带有下划线的名字， select name from 表名 where name like '%\_%',这里需要注意，下划线_是特殊符号，不能直接使用，需要使用斜杠转义。

6、排序order by 字段名 asc(升序) ，desc（降序）；

例：写出一个查询语句，按照工资的降序排序，当工资相同时再按照名字来升序排序

select * from order by 工资字段名 desc , 名字 asc；

注意：排序时，越靠前的字段越能起到主导作用，只有当前面字段无法完成排序时（相同的时候），才会启用后面的字段。

​ 执行顺序

select 字段名 3

from 表名 1

where 条件 2

order by ... 4

order by永远是最后执行的

7、聚合函数（分组函数）

• count() 计数
• av() 平均值
• sum() 求和
• max() 最大值
• min（）最小值

这里需要注意的是：聚合函数是自动忽略null值的，为什么要这样提一下呢，在数据库中只要字段值跟null相加后一定为null，比如（200+null）,结果一定为null

但像聚合函数中的sum()也是求和，它会把null值当做一个0来处理。或者是自动忽略不加。

讲到这里，就不得不说个ifnull这个单行处理函数了，可以对Null进行空处理。

语法格式： ifnull(可能为null的数据，被当成什么来处理)；比如：select name,ifnull(age,0) from u_user; 如果age字段的值为null的话，就会被转换成0。

count(*)和count(字段)的区别：

• count(*)统计的是总记录条数，和某个字段无关。
• count(字段)统计的是该字段不为null的数据总量。

8、group by 和 having

group by:按照某个字段或者某些字段进行分组

hanving :在分组之后的数据在进行一些过滤

比如：找出每个工作岗位的最高薪资； select max(sal) from group by 工作岗位字段

注意：分组函数一般是联合group by使用的，并且任何一个分组函数都是在group by语句执行之后才会被执行的。当一条sql语句没有使用group by分组时，整张表会自成一组。

​ 执行顺序

select 查询字段 5

from 表名 1

where 条件 2

group by 分组字段 3

having 过滤条件 4

order by 排序字段 6

根据这个执行顺序可以看出，where查询条件是不能使用分组函数来判定的。

group by分组后面可以跟多个字段分组，比如：找出每个部门的每个工作岗位的最高薪资

select 工作部门字段，工作岗位字段，max(sal) from group by 工作部门字段，工作岗位字段

• having的使用

  having只有在group by分组之后才能使用的。不能随意添加使用

建议：我们在写sql语句的时候一定要考虑到sql的执行效率。在能使用where查询的时候，尽量不使用having。争取在where查询的时候就怕数据过滤掉，提高效率。

9、distinct关键字去除重复记录

注意点：distinct只能出现在所有字段的最前面。

关键案例：统计工作岗位的数量？ select count(distinct 岗位字段名) from 表名；

10、union的使用

可以将查询的结果集相加，可以将两张毫不相关的表数据显示在一起。

union跟unionall的区别：

• union只显示不重复的数据。
• unionall显示的是两个查询数据结果的交集，会显示一些重复的数据。

11、limit

语法格式：select * from 表 where 条件 order by 字段名 desc limit 查询的起始下标，每页显示的数量；

6、联表查询

笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

6.1、内连接中的等值连接

语法格式：select A.字段 ，B.字段 from A a inner join B b on a.字段=b.字段

6.2、内连接中的非等值连接

on后面的条件不是等于关系

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select A.员工名字段 ，A.工资字段，B.工资等级字段 from A a inner join B b on a.工资字段 between b.最低限制 and b.最高限制

6.3、自连接

最大的特点：就是把一张表看成两张表，自己连接自己

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名？

6.4外连接

内连接和外连接的区别：

内连接：查询匹配的数据，两张表没有主副之分。

外连接：假设A和B进行外连接的话，AB表中就会是一张是主表，一张是副表。主要查询的是主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上的时候，副表会自动模拟出null与之匹配。

外连接的分类：

左连接：表示左边的这张表是主表。

右连接：表示右边的这张表是主表。

外连接最重要的特点：主表的数据无条件的全部查询出来。

7、索引

为什么要建立索引：

​ 索引是独立数据之外而存在的一种存储介质，并且在mysql启动的时候会默认加载到缓冲池中(内存)。相对于从磁盘中物理读取数据内容，从内存中读取索引数据的速度更快（10倍+）。

索引命名：

​ 1、使用小写字母，过长的字段名可以采用缩写的形式。

​ 2、普通索引按 “idx_字段名” 的格式命名 （idx_name）

​ 3、唯一索引按"uniq_字段名"的格式命名 （uniq_phone）

索引的选择性：

是指某个字段的不重复值的数量占总行数的比例，这个比例越接近一，则选择性越高，如果有该字段上的查询需求，更适合建立索引。

创建索引的语法格式：

• create (unique)index "要创建的索引名" on 表名 （要创建索引的字段）

• alter 表名 add (unique) index "要创建的索引名" (要创建索引的字段)

上面的是创建单列索引，也可以创建多列索引。

• create (unique)index "要创建的索引名" on 表名 （要创建索引的字段1,要创建索引的字段2，.....）
• alter 表名 add (unique) index "要创建的索引名" (要创建索引的字段1,要创建索引的字段2，.....)

删除索引：

• drop index 索引名 on 表名

查询索引：

• show index from 表名

使用explain查看执行计划，使用explain关键字可以模拟优化器执行sql语句，来分析你的查询语句或是表结构的性能瓶颈。

• explain结果参数--id

  • id相同，执行顺序由上而下。
  • id不同，id值越大，优先级越高越被先执行。

• explain结果参数--select_type ，为查询的类型，主要有simple,primary,subqery,union,derived

• explain结果参数--type ，显示的是访问类型，开发中常见的有，由好到坏，system>const>eq_ref>ref>range>index>all(最少达到range的结果，ref最理想)

• explain结果参数--Prossible_key/key

  • Prossible_key:显示可能应用在这张表中的索引，一个或多个查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
  • Key 是实际使用的索引

• explain结果参数--extra

  • using filesort :文件排序
  • using temporary:使用了临时表保存中间结果
  • using index:表示相应的select操作使用了覆盖索引
  • using where :表名了where过滤

** 如果是多列索引，要遵守最左前缀法则，指的是查询从索引的最左前列开始，不能跳过。**

常见的索引失效：

• 在索引列上做计算、函数、类型转换，会导致索引失效

• 存储引擎不能使用索引中范围条件右边的列，就是不能使用大于号>，只能使用<小于号

• 使用select *,会导致索引失效，尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））

• MySQL在使用不等于!=或者<>的时候无法使用索引，会导致全表扫描

• isNull和 is not Nu'll也无法使用索引

• like以通配符开头的（'%x'），会导致索引失效，变成全文搜索，使用这个（’x%‘）可以，索引不会失效

• 字符串不加单引号，会造成隐式转换，索引会失效

• 少用or，用它来连接时索引会失效

慢sql查询日志：

默认情况下，mysql是没有开启慢日志查询日志的，需要手动设置这个参数。

使用set global slow_query_log=1开启慢查询日志。只对当前数据库有效，如果重启的话就会失效， 想要永久有效地话，就要修改my.cnf文件

mysqldumpslow经常使用的参数：
-s，是order的顺序
----- al 平均锁定时间
-----ar 平均返回记录时间
-----at 平均查询时间（默认）
-----c 计数
-----l 锁定时间
-----r 返回记录
-----t 查询时间
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的