1、union和union all的区别:
union和union all都可以将两边的查询结果合并,区别在于union的合并会去掉重复行,并且使用默认的排序规则。而union all返回所有行(包括重复行),且对结果不进行排序。
举例如现在创建2张表:
Create table Table1
(
id int not null,
name varchar(20) not null
);
Create table Table2
(
id int not null,
name varchar(20) not null
);
插入数据(数据来自网上):
Insert into Table1 values (1,'姚羽');
Insert into Table1 values (2,'边兵兵');
Insert into Table1 values (3,'袁磊');
Insert into Table2 values (1,'姚羽');
Insert into Table2 values (2,'柳春平');
Insert into Table2 values (3,'张永超');
Insert into Table2 values (4,'刘华健');
执行:select*from Table1 unionselect* from Table2;
执行:select*from Table1 union allselect* from Table2;
2、MySQL关联查询和子查询
关联查询:也成为跨表查询,需要关联多个表进行查询
子查询:select语句嵌套select语句,可以理解为子查询是一张表
关联查询包括内连接(等值连接、非等值连接、自连接)、外连接(左连接left join、右连接right join),内连接只匹配符合筛选条件的数据,外连接如果是左连接,左表会全部返回,右表中与左表匹配的数据也会返回,否则返回NULL。右连接则右表会全部返回,左表中与右表匹配的数据也会返回,否则返回NULL。
可以参考简书文章进行详细操练:
3、Mysql索引
常用的数据库表存储引擎:InnoDB、MyiSAM
常用的存储数据结构:B+Tree,Hash
索引的种类:1、单列索引(普通索引、唯一索引、主键索引) 2、组合索引 3、全文索引 4、空间索引
判断索引是否有效:最左匹配原则
聚簇索引和非聚簇索引:InnoDB的主键索引是聚簇索引,普通非主键索引是非聚簇索引。MyiSAM都是非聚簇索引。(聚簇索引的概念是索引和数据放在一起,如果是主键索引,对应着一颗主键索引树,InnoDb可以直接在主键索引树的叶子节点找到数据。如果是普通列的索引,对应一颗非主键索引树,在该树的叶子节点处存放的是主键的值,需要拿着主键的值到主键索引树中找数据。如果是MyISAM,不论是主键还是非主键索引,索引树的叶子节点存放的都是真实数据行所在的地址)
为什么不用其他数据结构(二叉查找树、AVL、B树):降低对磁盘IO的访问,树的深度应该尽可能低,首先应该考虑多路平衡查找树。与B树相比,B+树在非叶子节点不存数据,只存索引,因此能够存储的索引更多,并且数据都在叶子节点,中间指针相连,方便范围查找,整体效率更高。(空间换时间)
B+Tree与Hash对比:B+Tree叶子节点间通过指针相连,方便范围查找。Hash不适合范围查找。如果只是等值查找,Hash的时间复杂度最好,为O(1)。
为什么建议主键为int型、自增:1、int相互比较速度快 2、int相对省空间 3、如果主键不是自增,那么在插入数据时就可能会导致B+树的调整(节点分裂),降低效率。
常用的索引操作:创建索引(ALTER TABLE t ADD INDEX(a)、ALTER TABLE t ADD UNIQUE INDEX(a),ALTER TABLE t ADD PRIMARY KEY(a))、创建组合索引(ALTER TABLE t ADD INDEX(a,b,c...))、删除索引(ALTER TABLE t DROP INDEX)、查看索引(SHOW INDEX)
查看是否使用索引查询、使用的索引:Explain关键字
4、MySQL分页
使用limit语句, SELECT *FROM table LIMIT [offset,] rows | rows OFFSET offset
5、MySQL去重
给定employee_tbl表,初始数据如下:
id name date signin
1 小明 2016-04-22 15:25:33 1
2 小王 2016-04-20 15:25:47 3
3 小丽 2016-04-19 15:26:02 2
4 小王 2016-04-07 15:26:14 4
5 小明 2016-04-11 15:26:40 4
6 小明 2016-04-04 15:26:54 2
5.1、使用distinct去重
SELECT DISTINCT name from employee_tbl;
name
小明
小王
小丽
如果distinct后面跟着多个字段,则distinct会将后面的所有字段作为条件进行查询。只要有字段不同,就认为是不同的结果。
select DISTINCT id,name from employee_tbl;
id name
1 小明
2 小王
3 小丽
4 小王
5 小明
6 小明
5.2、使用group by去重
select `id`,`name` from employee_tbl GROUP BY `name`;
id name
1 小明
2 小王
3 小丽
6、MySQL分库分表