mysql的in和not in等的用法(特别注意not in结果集中不能有null)、表分区
mysql的not in的用法(特别注意not in结果集中不能有null,如果not in结果集中有Null,则查询结果直接为Null)select * from user where name not in ('科比', '乔丹');假如DB有100条数据(包含'科比', '乔丹'),则这条sql返回剩余98条记录select * from user where name not ...
- mysql的not in的用法(特别注意not in结果集中不能有null,如果not in结果集中有Null,则查询结果直接为Null)
select * from user where name not in ('科比', '乔丹'); 假如DB有100条数据(包含'科比', '乔丹'),则这条sql返回剩余98条记录 select * from user where name not in ('科比', '乔丹', null); 假如DB有100条数据(包含'科比', '乔丹'),则这条sql返回剩余0条记录
- mysql的in后面的List长度有没有限制?
默认没有限制,不像其他DB有的限制List长度不能超过比如1000,mysql这块没有限制,但是整个sql的大小有限制,限制设置如下: mysql --help | grep max-allowed-packet
-
inner join left join联查过滤条件放在on还是where中的问题?
join联查可以简单理解为以下过程: 1.首先两个表做一个笛卡尔积。 2.然后根据on后面的条件对这个笛卡尔积做一个过滤形成一张临时表。 3.如果有where就对上一步的临时表再进行过滤,进而得到最终的结果集。 也就是说如果inner join left join联查过滤条件放在on中就是在第二步进行的过滤;如果过滤条件放在where中就是在第三部进行的过滤。 - 对于inner join 两种写法在查询结果上没有区别。 - 对于left join和right join 两种写法在查询结果上是不一致的。 inner join 大家应该没有什么疑问,但对于left join想通俗的说明下: A left join B on condition,不管condition是什么都会包含所有的A的记录(当然前提where无过滤条件),理解这第一点,然后where条件很好理解,对前面查询的结果做过滤
- inner join
关于inner join 与 left join 之间的区别,以前以为自己搞懂了,今天从前端取参数的时候发现不是预想中的结果,才知道问题出在inner join 上了。
需求是从数据库查数据,在前端以柱形图的形式展现出来,查到的数据按行业分组,显示每个行业的户数及户数占比,涉及到的字段有A表的用户数、总用户数和B表的行业名称。本来是不管查不查的到数据,在X轴都应该显示行业名称的,结果是X、Y轴都没有任何数据显示。问题就是我用错了联结方式。
一、sql的left join 、right join 、inner join之间的区别
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行举例如下:
--------------------------------------------
表A记录如下:
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408--------------------------------------------
1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.bID结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL(所影响的行数为 5 行)
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
左表(A)的记录将会全部表示出来,并不是代表查询结果最多只有5条记录,也可能大于5条记录,只要B表如下,机会有6条记录
bID bName
1 2006032401
1 2006032401_1
2 2006032402
3 2006032403
4 2006032404
8 2006032408
--------------------------------------------
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408(所影响的行数为 5 行)
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
--------------------------------------------
3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
-------------------------------------------- -
分区
分区简介
分区是根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库应用而言,逻辑上就只有一个表或者一个索引,但实际上这个表可能有N个物理分区对象组成,每个分区都是一个独立的对象,可以独立处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。分区有利于管理非常大的表,它采用分而治之的逻辑,分区引入了分区键的概念,分区键用于根据某个区间值(或者范围值)、特定值列表或者hash函数值执行数据的聚集,让数据根据规则分布在不同的分区中,让一个大对象变成一些小对象。MySQL分区即可以对数据进行分区也可以对索引进行分区。分区类型
range分区:基于一个给定的连续区间范围(区间要求连续并且不能重叠),把数据分配到不同的分区
list分区:类似于range分区,区别在于list分区是居于枚举出的值列表分区,range是基于给定的连续区间范围分区
hash分区:基于给定的分区个数,把数据分配到不同的分区
key分区:类似于hash分区
注意:无论哪种分区,要么你分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包含分区键,也就是说不能使用主键/唯一键字段之外的其它字段分区。
MySQL分区的优点主要包括以下4个方面:
1、和单个磁盘或者文件系统分区相比,可以存储更多数据
2、优化查询。在where子句中包含分区条件时,可以只扫描必要的一个或者多个分区来提高查询效率;同时在涉及sum()和count()这类聚合函数的查询时,可以容易的在每个分区上并行处理,最终只需要汇总所有分区得到的结果
3、对于已经过期或者不需要保存的数据,可以通过删除与这些数据有关的分区来快速删除数据
4、跨多个磁盘来分散数据查询,以获得更大的查询吞吐量
分区和水平分表功能类似,将一个大表的数据分割到多张小表中去,由于查询不需要全表扫描了,只需要扫描某些分区,所以分区能提高查询速度。水平分表需要用户预先手动显式创建出多张分表(如tbl_user0, tbl_user1, tbl_user2),在物理上实实在在的创建多张表,通过客户端代理(Sharding-JDBC等)或者中间件代理(Mycat等)来实现分表逻辑。分区是MySQL的一个插件Plugin功能,将一张大表的数据在数据库底层分成多个分区文件(如tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P#p2.ibd),和水平分表不同的是分区不需要显式的创建“分表”,数据库会自动创建分区文件的,用户看到的只是一张普通的表,其实是对应的是多个分区,这个是对用户是屏蔽的、透明的,在使用上和使用一张表完全一样,不需要借助任何功能来实现。分区是一种逻辑上的水平分表,在物理层面还是一张表。
只聊range分区:给定一个连续区间的范围值进行分区,某个字段的值满足这个范围就会被分配到该分区。适用于字段的值是连续的区间的字段,如 日期范围, 连续的数字。注意:range 对应的分区键值必须是数字值,可以使用range columns(分区字段) 对非int型做分区,如字符串,对于日期类型的可以使用year()、to_days()、to_seconds()等函数
创建表的时候创建分区:create table emp_date( id int not null, separated date not null default '9999-12-31' ) partition by range columns(separated) ( partiontion p0 values less than ('1990-01-01'), partiontion p0 values less than ('2001-01-01'), partiontion p0 values less than ('2018-01-01') );
创建表后,单独加分区:
alter table <table> partition by RANGE(id) ( PARTITION p0 VALUES LESS THAN (1000000), PARTITION p1 VALUES LESS THAN (2000000), PARTITION p2 VALUES LESS THAN (3000000), PARTITION p3 VALUES LESS THAN (4000000), PARTITION p4 VALUES LESS THAN MAXVALUE );
-- 创建分区表 CREATE TABLE `tbl_user_part` ( `id` int(11) NOT NULL , `username` varchar(255) DEFAULT NULL, `email` varchar(20) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `type` int(11) DEFAULT NULL, `create_time` datetime DEFAULT CURRENT_TIMESTAMP -- PRIMARY KEY (`id`,`age`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 PARTITION BY RANGE (age) ( PARTITION p0 VALUES LESS THAN (20), PARTITION p1 VALUES LESS THAN (40), PARTITION p2 VALUES LESS THAN (60), PARTITION p3 VALUES LESS THAN (80), PARTITION p4 VALUES LESS THAN MAXVALUE );
在创建分区的时候经常会遇到这个错误:A PRIMARY KEY must include all columns in the table’s partitioning function。意思是说分区的字段必须是要包含在主键当中。 可以使用PRIMARY KEY (id,xxx)来将多个字段作为主键。在做分区表时,选择分区的依据字段时要谨慎,需要仔细斟酌这个字段拿来做为分区依据是否合适,这个字段加入到主键中做为复合主键是否适合。
管理分区:
-- 删除list或者range分区(同时删除分区对应的数据)
alter table <table> drop partition <分区名称>;
-- 新增分区
-- range添加新分区
alter table <table> add partition(partition p4 values less than MAXVALUE);
-- list添加新分区
alter table <table> add partition(partition p4 values in (25,26,28));
-- hash重新分区
alter table <table> add partition partitions 4;
-- key重新分区
alter table <table> add partition partitions 4;
-- 子分区添加新分区,虽然我没有指定子分区,但是系统会给子分区命名的
alter table <table> add partition(partition p3 values less than MAXVALUE);
-- range重新分区
ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
-- list重新分区
ALTER TABLE <table> REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
更多推荐
所有评论(0)