MySQL——索引，连表……

骑猪看日落 2021-12-05 13:17 255阅读 0赞

0. 唯一索引  
  
create table t1(  
id int ....,  
num int,  
xx int,  
unique 唯一索引名称 (列名,列名),  
constraint ....  
)  
\#  
1 1 1  
2 1 2  
PS:  
唯一：  
约束不能重复（可以为空）  
PS: 主键不能重复（不能为空）  
加速查找

1. 外键的变种  
  
a. 用户表和部门表  
  
用户：  
1 alex 1  
2 root 1  
3 egon 2  
4 laoyao 3  
  
部门:  
1 服务  
2 保安  
3 公关  
===》 一对多  
b. 用户表和博客表  
用户表：  
1 alex  
2 root  
3 egon  
4 laoyao  
博客表：  
FK() + 唯一  
1 /yuanchenqi/ 4  
2 /alex3714/ 1  
3 /asdfasdf/ 3  
4 /ffffffff/ 2  
  
===> 一对一  
  
create table userinfo1(  
id int auto\_increment primary key,  
name char(10),  
gender char(10),  
email varchar(64)  
)engine=innodb default charset=utf8;

create table admin(  
id int not null auto\_increment primary key,  
username varchar(64) not null,  
password VARCHAR(64) not null,  
user\_id int not null,  
unique uq\_u1 (user\_id),  
CONSTRAINT fk\_admin\_u1 FOREIGN key (user\_id) REFERENCES userinfo1(id)  
)engine=innodb default charset=utf8;

c. 用户表（百合网） 相亲记录表  
  
示例1：  
用户表  
相亲表  
  
示例2：  
用户表  
主机表  
用户主机关系表  
===》多对多  
  
create table userinfo2(  
id int auto\_increment primary key,  
name char(10),  
gender char(10),  
email varchar(64)  
)engine=innodb default charset=utf8;

create table host(  
id int auto\_increment primary key,  
hostname char(64)  
)engine=innodb default charset=utf8;

create table user2host(  
id int auto\_increment primary key,  
userid int not null,  
hostid int not null,  
unique uq\_user\_host (userid,hostid),  
CONSTRAINT fk\_u2h\_user FOREIGN key (userid) REFERENCES userinfo2(id),  
CONSTRAINT fk\_u2h\_host FOREIGN key (hostid) REFERENCES host(id)  
)engine=innodb default charset=utf8;  
  
  
2. SQL语句数据行操作补充  
create table tb12(  
id int auto\_increment primary key,  
name varchar(32),  
age int  
)engine=innodb default charset=utf8;  
  
增  
insert into tb11(name,age) values('alex',12);  
  
insert into tb11(name,age) values('alex',12),('root',18);  
  
insert into tb12(name,age) select name,age from tb11;  
删  
delete from tb12;  
delete from tb12 where id !=2  
delete from tb12 where id =2  
delete from tb12 where id > 2  
delete from tb12 where id >=2  
delete from tb12 where id >=2 or name='alex'  
  
改  
update tb12 set name='alex' where id>12 and name='xx'  
update tb12 set name='alex',age=19 where id>12 and name='xx'  
查  
  
select \* from tb12;  
  
select id,name from tb12;  
  
select id,name from tb12 where id > 10 or name ='xxx';  
  
select id,name as cname from tb12 where id > 10 or name ='xxx';  
  
select name,age,11 from tb12;  
  
其他：  
select \* from tb12 where id != 1  
select \* from tb12 where id in (1,5,12);  
select \* from tb12 where id not in (1,5,12);  
select \* from tb12 where id in (select id from tb11)  
select \* from tb12 where id between 5 and 12;  
  
  
通配符：  
  
select \* from tb12 where name like "a%"　　%表示任意  
select \* from tb12 where name like "a\_"　　\_表示一个  
  
  
分页：  
  
select \* from tb12 limit 10;　　　　前十个  
  
select \* from tb12 limit 0,10;　　从0向后取10  
select \* from tb12 limit 10,10;　　从10 向后取10  
select \* from tb12 limit 20,10;　　  
  
select \* from tb12 limit 10 offset 20;  
从第20行开始读取，读取10行；  
  
结合Python分页：  
\# page = input('请输入要查看的页码')  
\# page = int(page)  
\# (page-1) \* 10  
\# select \* from tb12 limit 0,10;  1 页  
\# select \* from tb12 limit 10,10; 2 页  
  
  
排序：  
select \* from tb12 order by id desc; 大到小  
select \* from tb12 order by id asc; 小到大  
select \* from tb12 order by age desc,id desc;　　先按age排序，如果age一样按id排序  
  
取后10条数据  
select \* from tb12 order by id desc limit 10;  
  
分组：  
  
select count(id),max(id),part\_id from userinfo5 group by part\_id;  
  
count　  
max　  
min  
sum  
avg  
  
\*\*\*\* 如果对于聚合函数结果进行二次筛选时？必须使用having \*\*\*\*  
select count(id),part\_id from userinfo5 group by part\_id having count(id) > 1;  
  
select count(id),part\_id from userinfo5 where id > 0 group by part\_id having count(id) > 1;  
  
  
连表操作：  
  
select \* from userinfo5,department5  
  
select \* from userinfo5,department5 where userinfo5.part\_id = department5.id

select \* from userinfo5 left join department5 on userinfo5.part\_id = department5.id  
select \* from department5 left join userinfo5 on userinfo5.part\_id = department5.id  
\# userinfo5左边全部显示  
  
  
\# select \* from userinfo5 right join department5 on userinfo5.part\_id = department5.id  
\# department5右边全部显示  
  
  
  
select \* from userinfo5 innder join department5 on userinfo5.part\_id = department5.id  
将出现null时一行隐藏  
  
  
  
  
  
  
select \* from  
department5  
left join userinfo5 on userinfo5.part\_id = department5.id  
left join userinfo6 on userinfo5.part\_id = department5.id  
  
  
select  
score.sid,  
student.sid  
from  
score

left join student on score.student\_id = student.sid

left join course on score.course\_id = course.cid

left join class on student.class\_id = class.cid

left join teacher on course.teacher\_id=teacher.tid

转载于:https://www.cnblogs.com/Mr-Feng/p/10916377.html