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

原文发布于微信公众号 - 赵KK日常技术记录(gh_cc4c9f1a9521)

原文发表时间:2019-08-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券