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，才能提升能力。