多表的查询，建⽴索引
1，多表连接查询
语法:select 表1 inner/left/right/ join 表2 on 表1.字段 =表2.字段;
1.1>外链接之内链接.
from department inner join employee onemployee.dep_id = department.id === from department, employee where employee.dep_id = department.id  1.2>外链接之左连接
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id
#这⾥是把department_name重命名为depart_name
1.3>外链接之右连接
select employee.id,employee.name,department.name
as depart_name from employee right join department
on employee.dep_id = department.id;
1.4>全外链接(显⽰左右两个表匹配成功的全部记录)
在内连接的基础上增加左边和右边没有的结果，在mysql中是没有着个指令的，只是⼀种外在的⼀种巧妙的⽅法
语法:
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id
;
2，符合条件查询
select employee.name,department.name from employee inner join department
  on employee.dep_id = department.id
  where age > 25;
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
3,⼦查询
3.1>带in的关键字查询
#1,⼦查询是将⼀个查询语句嵌套在另⼀个查询语句中,
#2，内层查询语句的查询结果，可以作为外层查询语句提供查询条件.
#3,⼦查询中可以包含:in,not,in,any,all,exists 和 not existsde 等关键字
#4，还可以包括⽐较运算符:=,!=,,>,<；等
#查询平均年龄⼤于25岁以上的部门名字
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看计数部员⼯姓名
select name from employee where dep_id in
(select id from department where name = "技术");
#查看不⾜1⼈的部门名
select name from department where id not in
(select dep_id from employee group by dep_id);
3.2>带⽐较运算符的⼦查询
##查询⼤于部门内平均年龄的员⼯名、年龄
select name,age from employee where age >
(select avg(age) from employee);
3.3>带exists关键字的⼦查询
select * from employee where exists
(select id from department where id= 200);
4，索引
4.1>索引的介绍
#数据库中专门⽤于帮助⽤户快速查数据的⼀种数据结构 .类似于字典中的⽬录，查字典内容时可以根据⽬录查到数据存放的位置，然后直接获取内容
4.2>索引的作⽤
约束数据并且快速查数据
4.3>常见的⼏种索引
-普通索引
-唯⼀索引
-主键索引
-联合索引(多列 )
-联合主键索引
-联合唯⼀索引
-联合普通索引
4.3.1>⽆索引和有索引的区别以及建⽴的⽬的
#⽆索引：从前往后,⼀条⼀条的查询
#有索引：创建索引的本质，就是创建额外的⽂件 (是某种格式的储存，查询的时候，先去格外的⽂件，
定好位置，然后再去原始表中直接查询，但是创建索引越多 ，对硬盘消耗越⼤，空间换时间) #建⽴索引的⽬的:
#a，额外的晚间保存特殊的数据结构
#b，查询快 ，但是插⼊更新删除依然慢
#c，创建索引后必须命中索引才有⽤
4.3.2>索引的种类
hash索引和Btree
(1)hash索引：查询倒要块，范围⽐较慢,(不适合范围查询)
(2)btree索引:b+树，层级越多，数据量指数级增长(innoDB默认⽀差这个)
4.4>索引的介绍
4.4.1>普通索引
#创建表及普通索引
create table userinfo(
nid int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
index ix_name(name)
);
普通索引的创建
  create index 索引的名字 on 表明(列名)
查看索引:
  show  index from 表名
删除索引 :
  drop index 索引名 on 表名
4.4.2>唯⼀索引：加速查和唯⼀约束
#创建表及唯⼀索引
create table userinfo(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique index ix_name(name)
);
创建唯⼀索引:drop删除表
create unique index 索引名 on 表名(列名)
删除唯⼀索引:
drop index 索引名 on 表名
4.4.3>主题索引：功能：加速查和唯⼀约束
create table userinfo(
id int not null auto_increment primary key,
name varchar(32) not null,
email varchar(64) not null,
unique  index  ix_name(name)
)
or
create table userinfo(
id int not null auto_increment,
name varchar(32) not null,
email varchar(64) not null,
primary key(nid),
unique  index  ix_name(name)
)
主键索引:
alter table 表名 add  primary key (列名)
删除主键索引
alter  table 表名 drop primary key;
alter  table 表名 modify 列名 int, drop  primary  key
4.4.4>组合索引：组合索引是将n个列组合成⼀个索引
其应⽤场景为：频繁的使⽤n列来进⾏查询，如: where name = "alex" and "email" = "alex@qq".
联合普通索引的创建:
create index 索引名 on 表名(列名1，列名2);
5，索引名词
覆盖索引：在索引⽂件中直接获取数据
列如:
select name from userinfo  where name = "alex50000";
索引合并 ：把多个单索引合并使⽤
列如:
select * from userinfo  where name ="alex13131" and id = 13131
6，正确使⽤索引
  数据库中添加索引后确实会让查询速度起飞，但前提必须使⽤索引来命名的字段来查询，如果不⽤，那么你给某⼀字段添加的字段的索引页毫⽆意义.使⽤索引的三要素:(1)创建索引  (2)命中索引  (3)正确使⽤索引
#创建表
create table userinfo(
id int ,
name varchar(20),
gender char(6),
email varchar(50)
);
#插⼊数据
delimiter $$ #声明存储存储过程的结束符好为$$
create procedure auto_insert()
begin
declare i int default 1;
while(i<3000000)double
insert into userinfo values(i,concat("alex",i),"male",concat("xuexue",i,"@qq"));
set i = i + 1;
end while;
end  #$$结束
delimiter; #重新声明分号为结束符号
插⼊数据需要⼀定的时间，我没有试出来...
查看存储过程:show create procedure auto_insert1\g
调⽤存储过程:call auto_insert1();
7，索引的最左前缀的特点:
#最左前缀的匹配:
create index in_name_email on userinfo(name,email);
select * from userinfo where name = "xuexue";
select * from userinfo where name = "xuexue" and  email="xuexue@qq";
select * from userinfo where email = "xuexue@qq";
#组合使⽤组合索引,name和email组合索引查询:
(1)name和email ----->使⽤索引
(2)name        ----->使⽤索引
(3)email      ----->不使⽤索引
对于同时搜索n个条件时，组合索引的性能好于单个索引
***********组合索引的性能>索引合并的性能********
8，索引的注意事项
(1)避免使⽤select * 这样的模糊查询
(2)count(1)或count(列)代替count(*)
(3)创建表时尽量使⽤插⼊代替varchar
(4)表的字段顺序固定长度的字段优先
(5)组合索引代替多个单列索引(经常使⽤多个条件查询)
(6)使⽤连接(join)代替⼦查询
(7)联表时注意条件类型需要⼀致
(8)尽量使⽤短索引
(9)索引散列(重复少)不适⽤于建⽴索引，列如：性别不适
9，分页性能
第⼀页:
select * from userinfo  limit 0,10;
第⼆页:
select * from userinfo  limit 10,10;
第三页:
select * from userinfo  limit 20,10;
第四页:
select * from userinfo  limit 30,10;
.
..
性能优化
(1)只有上⼀页和下⼀页
做⼀个记录：记录当前页⾯的最⼤id或最⼩id
下⼀页:
select  * from userinfo where id>max_id limit 10;
上⼀页:
select  * from userinfo where id<min_id order by id desc limit 10;
(2)中间有页码的情况下
select * from  userinfo where id  in(
select id from (select * from userinfo where id>pre_max_id limit  (cur_max_id-pre_max_id)*10) as A order by A.id desc limit 10);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。