【MySQL】唯一索引，加SQL操作补充

一 唯一索引创建方法

create table t1(
id int ....,
num int,
xx int,
unique 唯一索引名称 (列名,列名),

)

创建唯一索引的该列不能重复，但可以为空，但创建主键的列不能重复，也不能为空
。当对两列创建唯一索引时，称为联合索引。主要这两列的任意行与下一行不完全相同就行。如下的情况：
1 1
1 2

二 SQL语句数据行操作补充

1 创建数据表

create table tb12(
id int auto_increment primary key,
name varchar(32),
age int
)engine=innodb default charset=utf8;

2 添加数据

insert into tb11(name,age) values('alex',12);
向tb11数据表中的name与age列添加数据
insert into tb11(name,age) values('alex',12),('root',18);
同时添加两列的数据
insert into tb12(name,age) select name,age from tb11;
从另外一个数据的两列，直接添加到数据表tb12的两列中，相当于直接拷贝两列过来

3 删除数据

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'

4 修改数据

update tb12 set name='alex' where id>12 and name='xx'
update tb12 set name='alex',age=19 where id>12 and name='xx'

4 查询数据（方法最多）

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';
as 的作用，将查询结果中的name取一个别名cname再显示出来
select name,age,11 from tb12;

（1）在查找条件where中 in 和between的用法如下

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;

in后面可以跟数字的序列，也可直接接收另一个表select的查询结果，between则是跟一个连续的取值范围

（2）在查找条件where中，通配符的用法

select * from tb12 where name like "a%"
select * from tb12 where name like "a_"

上面命令是说明在tb12表中，查找条件为name列中，a%是以a开头的字符串，a_是指以a开头，再仅接1个字符的情况

(3) 分页

select * from tb12 limit 10; 从数据表tb12读取前10条数据

select * from tb12 limit 0,10; 从第一条数据开始（序号为0）从数据表tb12，往后取10条数据
select * from tb12 limit 10,10; 从第11条数据开始（序号为10）从数据表tb12，往后取10条数据
select * from tb12 limit 20,10; 从第21条数据开始（序号为20）从数据表tb12，往后取10条数据
select * from tb12 limit 10 offset 20; 与上条sql语句作用一样，offset是设置起始位置为20（第21条数据），向后取10条数据上述 limit格式
limit 起始位置,取值的数量

分页的应用，浏览器显示

page = input('请输入要查看的页码')
page = int(page)
# (page-1) * 10
# select * from tb12 limit 0,10; #读头10条数据
# select * from tb12 limit (page-1) * 10,10; #读指定页的十条数据

分页优秀方案

select * from userinfo3 where id in(select id from userinfo3 limit 200000,10) 这样查找不推荐，因为也会先扫到200000条，效率低

解决方案：
记录当前页最大或最小ID
1. 页面只有上一页，下一页

# max_id
# min_id
下一页：
select * from userinfo3 where id > max_id limit 10;
上一页：
select * from userinfo3 where id < min_id order by id desc limit 10;

2. 上一页 192 193 [196] 197 198 199（需要获得） 下一页
select * from userinfo3 where id in (
select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
)
原理是先拿到196页的最大id,假设每页有10条记录，先拿到后30条，及197，198，199的id，再从大到小排序，只取199页id的10条

（4）排序

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条数据（id从大到小排列）
select * from tb12 order by id desc limit 10;

（5）分组

select count(id),max(id),part_id from userinfo5 group by part_id;

将part_id相同的分为一组，可使用聚合函数对查询结果进行显示

count（xx） 统计分组中数据的个数
max （） 取一个分组中的数据的最大值
min （） 取最小值
sum（） 取分组中数据之和
avg（） 取分组中数据平均值

若不进行分组，直接使用聚合函数则默认为1组

select count(id) from userinfo5; 统计userinfo5表id列的总数

（6）连表操作（重点）

select * from userinfo5,department5 将两个表采用笛卡尔积的形式显示

select * from userinfo5,department5 where userinfo5.part_id = department5.id
将两表进行连接，当userinfo5表的part_id等于department5表的id时

也可使用left join 进行连表，right join 可被left join 代替，只需交换两个数据表的位置即可
select * from userinfo5 left join department5 on userinfo5.part_id = department5.id 左连接，userinfo5中所有满足条件的信息都会显示，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 userinfo6.part_id = department5.idselect 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

（7）临时表

select * from (select * from tb where id< 10) as B;
将从tb表中id<10的查询结果作为临时表B，再进行查询

（8）特殊用法

select count(id) from userinfo5;
select count(1) from userinfo5; 统计在userinfo5表中，总列数