Mysql查询及高级知识整理(上)

SQL:Structured Query Language，结构化查询语言。

从基础到高级复习下容易忘，容易忽略的知识，一个高效率，高性能的SQL，能决定查询结果，代码长度等，最重要的是会影响查询结果，另外如果查询时间过长，会引起不必要的麻烦。

Mysql基础

从查询开始：

SELECT 查询列表

FROM 表名或视图列表

【WHERE 条件表达式】

【GROUP BY 字段名 【HAVING 条件表达式】】

【ORDER BY 字段 【ASC|DESC】】

【LIMIT m,n】;

要想运行一条SQL，先要写的并不是select，而是from，先决定从哪一个表开始查，再筛选条件。

inner join 交集 inner 内连接

outer join 差集 outer 外连接

自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

SELECT emp.ename,mgr.ename

FROM t_employee AS emp, t_employee AS mgr

WHERE emp.mid = mgr.eid;

聚合函数

l AVG(【DISTINCT】 expr) 返回expr的平均值

l COUNT（【DISTINCT】 expr）返回expr的非NULL值的数目

l MIN(【DISTINCT】 expr)返回expr的最小值

l MAX(【DISTINCT】 expr)返回expr的最大值

l SUM（【DISTINCT】 expr）返回expr的总和

特别注意: Group By 语句

在SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的

测试验证

-- ----------------------------
-- Table structure for mytbl2
-- ----------------------------
DROP TABLE IF EXISTS `mytbl2`;
CREATE TABLE `mytbl2`  (
  `id` int(11) NULL DEFAULT NULL,
  `NAME` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `dept` int(11) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of mytbl2
-- ----------------------------
INSERT INTO `mytbl2` VALUES (1, 'zhangsan', 33, 101);
INSERT INTO `mytbl2` VALUES (2, 'li4', 34, 101);
INSERT INTO `mytbl2` VALUES (3, 'w5', 34, 102);
INSERT INTO `mytbl2` VALUES (4, 'zhao6', 34, 102);
INSERT INTO `mytbl2` VALUES (5, 't7', 36, 102);

目的：查询表中年龄最大的员工部门，名字

select `NAME`,dept,MAX(age)
from mytbl2 
GROUP BY dept;

查询结果：

从表中数据可得:101部门年龄最大应为li4，102部门年龄最大应为t7,这条SQL的查询是找出年龄最大，但查询名字是查询每个部门的第一个名字。

验证：

show VARIABLES like'sql_mode';

设定sql模式

set sql\_mode='ONLY\_FULL\_GROUP\_BY';

再次执行：

会提示name字段不在Group By 中；但要注意生产数据库不一定设置此项，默认为Null；

正确SQL：

分析：先找出表中最大年龄，作为临时表，再联查

SELECT *FROM mytbl2 m INNER JOIN 
(select dept,MAX(age) as maxage
from mytbl2 
GROUP BY dept)ab on ab.dept=m.dept and m.age=ab.maxage;

结果：

这个小点容易被忽视，如果第一行显示为li4，会错认为查询结果正确，导致不可估量的后果。

Mysql执行顺序

在第一次查询后，会将结果缓存至本地缓存，两次查询结果时间不一致。

Mysql事务

事务:事务就是保持数据一致性

特性：ACID，简称原子一致隔离持久。

原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事务的不可再分的原则即为原子性。

组成事务的所有查询必须：要么全部执行，要么全部取消（就像上面的银行例子）。

一致性(Consistency)：指数据的规则,在事务前/后应保持一致。

隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的.

持久性(Durability)：当事务提交完成后，其影响应该保留下来，不能撤消。

隔离级别

1.读未提交

2.读已提交(Mysql默认级别)

3.可重复读

4.串行化

脏读：已经更新 但未提交

不可重复读：两次读取结果不一致

幻读：读的同事另一个事务进行了写操作，导致两次查询结果不一致

查看当前的隔离级别：

SELECT @@tx_isolation;

    存储引擎  MyISAM  InnoDB

索引

是对列或多列进行排序的数据结构；

查看索引：select index from user;

创建索引：默认设置主键时是创建索引的，

Crete id int(60)AUTO_INCREMENT key；

CREATE INDEX 索引名 ON 表名称 (column_name,column_name…);

索引结构：BTree B+Tree B：balance

BTree：平衡二叉树

特点：1.具有数据节点

  2.指向下层指针

   3.指向数据指针

缺页查询,产生IO

B+Tree：

特点: 1.具有数据节点

     2.指向下层指针

命中数据3层查找后查询数据指针

加载更快，产生更少IO

效率：BTree更高，但从IO角度，Mysql选择B+Tree

时间复杂度：算法执行的复杂程度

空间复杂度：算法在运行过程中临时占用存储空间大小的量度

聚簇索引：数据存储方式，数据行和键值聚簇存储在一起

非聚簇索引：数据行和键值聚簇存储不在一起

什么情况需要索引：频繁作为查询条件的字段

什么情况不需要索引：经常update的字段

SQL性能分析

复杂业务中,一条SQL不单要达到准确性，还要考虑性能，通过查询时间，查询表数量等等去衡量。

关键字：Explain，模拟执行SQL。

目的：查看是否使用了索引

     使用了哪些索引

     物理扫描表行数

SQL书写能力是工作中不可或缺的,一条好的SQL可以节省代码，提高性能，不断的锻炼，书写各种场景SQL，才能提升能力。