连表查询示例 连表查询详解 explain使用介绍 建表常用属性 表数据复制
- 2015-08-09 13:15:00
- admin
- 原创 1547
一、笛卡尔查询和子条件查询
笛卡尔查询：效率是m*n，数据量较少时可以使用。
select * from test as t1, test as t2 where t1.id=t2.id;
内连接查询：比笛卡尔查询效率高，平时推荐使用。
select * from test as t1 join test as t2 on t1.id=t2.id;
子条件查询：对编写者要求很高，性能优化时推荐使用。
单个值：where 字段 = 子查询;
多个值：where 字段 in (子查询);
多个字段值: where (字段1,字段2,...) = (子查询);
select * from test where id in (select id from test);
二、连表查询示例
嵌套查询：select * from (select * from User) as TempUser;
记录复制：insert into User(userId,create_time) select userId,create_time from User where userId='userId';
记录去重：
获取重复用户列表，select userId,count(*) as userCount from User group by userId having userCount>1;
去除重复用户语句，select concat('delete from User where userId="',userId,'" limit ',userCount-1,';') from UserList;
三、连表查询详解
连表查询种类：
1、内连接查询，生成两个表的交集，语法是[inner|cross] join [join_cond]；
2、左连接查询，生成两个表的交集，加上左表减右表且扩充字段填充NULL，语法是left [outer] join join_cond；
3、右连接查询，生成两个表的交集，加上右表减左表且扩充字段填充NULL，语法是right [outer] join join_cond；
4、union用于合并多个查询结果，all保留所有数据行，distinct去除重复行，默认是distinct；
5、column in (subquery)，子查询先执行且只执行一次，适合内表小的情况；
6、exists (subquery)，主查询先执行，子查询执行多次，适合外表小的情况；
连表查询详解：
1、数据库服务尽可能使用最新版本，因为有很多功能的优化，性能有非常大的提升；
2、数据库服务会给出连表查询最优执行方案，但存在误判，这时候需要人工优化SQL；
3、多表连接且有多个数据筛选条件，表连接顺序无法确定，数据筛选顺序无法确定；
4、连表查询且有数据筛选条件，如果数据筛选能过滤很多数据，先过滤数据再连接；
5、左连接比内连接更加安全，因为必须指定连接条件，可以防止错误的笛卡尔连接；
6、子查询是连表查询，则查询结果不能包含同名字段，或者使用as对同名字段改名；
7、更新操作可以使用连表语法，一行数据只会更新一次，即使一行数据被多次匹配；
8、更新操作使用连表语法时，不能使用关键字order和limit；
四、explain使用介绍
命令说明：
1、explain statement
2、explain format=json statement
3、explain语句实际在服务端解释执行；
select_type，查询类型：
1、SIMPLE，简单查询；
2、PRIMARY，查询包含子查询，最外层叫PRIMARY；
3、MATERIALIZED，查询结果被缓存，不用每次执行；
type，数据扫描类型，常用类型有ALL、index、ref，性能逐渐变好：
1、ALL，Full Table Scan， 全表扫描；
2、index，Full Index Scan，遍历索引表；
3、ref，常量或列值用于查找索引列；
Extra，其它执行信息：
1、Using index，使用索引表；
2、Using where，使用数据筛选；
其它字段说明：
1、id，查询标识，相同时执行顺序由上至下，子查询的id递增且先被执行；
2、table，访问的数据表；
3、possible_keys，可能使用的索引；
4、key，实际使用的索引；
4、key_len，索引长度；
6、ref，索引匹配条件；
7、rows，需要扫描的记录数；
五、建表常用属性
drop table if exists test;
create table test(
id int AUTO_INCREMENT PRIMARY KEY,
name varchar(20) DEFAULT 'feinen' COMMENT 'person name',
age int DEFAULT 21 COMMENT 'person age',
sex varchar(20)
) AUTO_INCREMENT=1;
insert into test values(1, 'nick', 10, 'man');
insert into test values(2, 'simon', 10, 'man');
insert into test(id) values(NULL);
insert into test() values();
数据行存储格式：
1、数据行头部存储普通字段内容，存储BLOB和TEXT前面内容；
2、ROW_FORMAT=FIXED，数据行头部长度固定，查找效率更高；
3、ROW_FORMAT=DYNAMIC，数据行头部长度无限制，存储效率更高；
4、ROW_FORMAT=COMPRESSED，数据行头部长度无限制，并且启用数据压缩；
六、表数据复制
表结构和数据复制：
create table Copy
(select *,
NULL fieldname1,
NULL fieldname2
from Source);
表数据复制：
insert into Copy(id,name) select id,name from Source;