MySQL-自增、索引、外键、其他操作

1 自增

一些简单的SQL语句：

// 查看表的中变量的类型
desc 表名;

//查看表的创建方法
show create table 表名;
show create table 表名 \G;   //  \G是为了转化为可方便读取的信息

// 更改表的初始自增值
alter table 表名 atuo_increment=20;   // 设置表格下次键自增从多少开始

例如：



自增
MySQL: 自增步长
基于会话级别：一个用户的登录sql窗口就是一个会话，用户设置自增步长，不影响其他用户会话窗口设置。

SqlServer：自增步长：
基于表级别：数据表

会话级别：就是当前运行的会话窗口中执行操作。
全局级别：每次进行都是该方式

自增的设置有两种方式
1.基于会话级别（只影响自己）
show session variables like 'auto_inc%'  // 查看全局变量(起始自增值和步长)
set session auto_increment_increment=2   // 设置步长
set session auto_increment_offset=10;    // 设置起始自增值

2.基于全局级别（尽量不要用，在这里修改，以后登录用户会话，默认是global修改的）（影响所有人）
show global variables like 'auto_inc%'  // 查看全局变量
set global auto_increment_increment=2   // 设置步长
set global auto_increment_offset=10;    // 设置起始自增值

2 索引（加速查找的）

唯一索引：
MySQL 每张表只能有1个自动增长字段，这个自动增长字段即可作为主键，也可以用作非主键使用，但是请注意将自动增长字段当做非主键使用时必须必须为其添加唯一索引，否则系统将会报错。

索引中的值不能重复，

create table t1(
 	id int ....,
 	num int,
 	xx int,
 	unique 唯一索引名称 (列名,列名),   可以组合索引，也是不可相同
 	constraint ....
 )

PS:
唯一索引：约束不能重复（可以为空）
PS: 主键不能重复（不能为空）

3 外键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键 比如：

• 学生表(学号，姓名，性别，班级) ，其中每个学生的学号是唯一的，学号就可以设置为主键。
• 课程表(课程编号,课程名,学分) 其中课程编号是唯一的,课程编号就是一个主键。
• 成绩表(学号,课程编号,成绩) 成绩表中单一一个属性无法唯一标识一条记录，学号和课程号的组合才可以唯一标识一条记录，所以学号和课程号的属性组是一个主键。（后面的外键设置所以也都可以为组合的表示）

成绩表中的学号和学生表中的学号相对应，并且学生表中的学号是学生表的主键，则称成绩表中的学号是学生表的外键。同理，成绩表中的课程号是课程表的外键。

定义主键和外键主要是为了维护关系数据库的完整性，总结一下：
1.主键是能确定一条记录的唯一标识，比如，一条记录包括身份正号，姓名，年龄。身份证号是唯一能确定你这个人的，其他都可能有重复，所以，身份证号是主键。
2.外键用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。比如，A表中的一个字段，是B表的主键，那他就可以是A表的外键。

3.1外键的创建(在需求里，哪个字段需要用下拉框展示的，优先想到外键)

constraint 键名 foreign key (外键名) references 另一个表名(主键名)

3.2外键的变种：

a.一对一（加唯一索引）

create table stu_number(
iid int auto_increment primary key,
s_num int，
unique(iid)
)engine=innodb default charset=utf8;

create table student(
sid int auto_increment primary key,
name char(20) not null,
s_num_id int not null,
constraint fk_snumid_snum foreign key(s_num_id) references stu_number(iid)
)engine=innodb default charset=utf8;

b.一对多(对应多个class_id)

create table class(
cid int auto_increment primary key,
class char(10),
unique(class)
)engine=innodb default charset=utf8;

create student(
sid int auto_increment primary key,
name char(20),
class_id int,
constraint fk_claid_cid foreign key(class_id) references class(cid)
)engine=innodb default charset=utf8;

c.多对多

create table student(
sid int auto_increment primary key,
name char(20) not null
)engine=innodb default charset=utf8;

create table teacher(
tid int auto_increment primary key,
name char(20) not null
)engine=innodb default charset=utf8;

create table score(
tid int,
sid int,
score int not null,
primary key(tid,sid),
constraint fk_tid_tid foreign key(tid) references teacher(tid),
constraint fk_sid_sid foreign key(sid) references student(sid)
)engine=innodb default charset=utf8;

删学生表或教师表数据的时候也是先操作成绩表，修改约束，再删除学生表和教师表中数据。

4 SQL语句数据行补充操作（增删改查等）

4.1 增

// 一次性插单条数据
insert into student(name,age) values('alex','25');
// 一次性插入多条数据
insert into student(name,age) values('alex','25'),('hokwok','22');
// 复制另外一个数据表
insert into student(name,age) select name,age from student2;

4.2 删

where表选择条件，在很多方法里都可以使用

// 全部数据删除：
delete from student;
// 删除指定条件的数据
// 例如：删除 sid 小于10的
delect from student where sid < 10;

// 运算符
//			>  <   >= <= = !=

delect from student where sid=10;

// 逻辑关系
//			and or 
delect from student where sid < 10 or name='alex';

4.3 改

// 全部数据修改
update student set name='haohao';
// 修改指定条件的数据
update student set name='haohao' where id>10 and name='xxx';

// 这里条件也可以和删除一样运用运算符和逻辑关系等

4.4 查

// 查所有的数据
select * from student;
// 查指定某些列的数据
select name,age from student;
// 查指定条件数据
select name,age from student where id > 50;   // 条件使用和增删改一样
// 查找时也可指定别名
select name as cname,age from student where id > 50; // 将name列的表头名改为cname
// 查找时也可加常数列
select name as cname,age,1 from student where id > 50; // 加常数1，就会多一列，且该列所有数据都是1
// 查找的某个条件含不等于，如写成下面两种均可
select name,age from student where id != 1;
select name,age from student where id <> 1;

用in方法查找：

// 查id 为1,5,10（1或5或10）的数据（当然也可以用逻辑关系or去进行描述条件）
select * from student where id in(1,5,10);
// 除了id 为1 5 10 的数据
select * from studnet where id not in(1,5,10);
// 从其他表中取来查
select * from student where id in (select id from student2);
// 查找某个区间[5,12]中的数据
select * from student where id between 5 and 12;

通配符：

// 查找姓名开头为a的所有人，不限字符数（a,ab,abc,abcd都能查到）
select * from student where name like 'a%';

注意：
%a  结尾为a
a%  开头为a
%a% 包含a

// a_  两个字符，第一个字符为a，比如是abc则查找不到
select * from student where name like 'a_';

分页(limit)查找：

// 查找前十条信息
select * from student limit 10;
// 从第10条开始往后找10条
select * from student 10,10; //初始值，附加值
// 从10条后找20条
select * from student 10,20;
// 另外一种写法
select * from student 20 offset 10; // 从10条后找20条

// python实现分页的操作：
page = input('请输入要查看的页码')
page = int(page)
(page-1) * 10
select * from tb12 limit 0,10; // 第1页 
select * from tb12 limit 10,10; // 第2页

排序查找：

// 按照id进行排序
select * from student order by id desc;  // 按照id反序排列(从大到小排列)
select * from student order by id asc;   // 按照id正序排列(从小到大排列)
// 按照sid从大到小排列，如果相同则按cid从小到大排列
select * from student order by sid desc, cid asc;
// 先排序再取后10条数据
select * from student order by id desc limit 10;

分组查找：

// 以姓名为分组依据    count(age) 显示多少个（计数）   max(age)在检索中的内容显示的为最大的
select count(age),max(age) from student group by age;

// 默认都是一组，计算有多少条数据
select count(age) from student;

聚合函数：
count  // 计数
max  // 最大
min  // 最小
sum  // 求和
avg // 求平均值

**** 如果对于聚合函数结果进行二次筛选时？必须使用having，因为where是限制搜索条件的****
// 比如我们从筛选出的结果中，选出count(age)>20的
select count(age),max(age) from student group by part_id having count(age)>20;
// 从age大于15的里面 分组 然后 从分组后结果中 筛选出 数量大于20的那个(where条件里不能有聚合函数)
select count(age),part_id from userinfo5 where age > 15 group by part_id having count(age) > 20;

连表操作：

上表为例：

select * from student,class where student.class_id = class.cid;
推荐不要用*，如果两个表中有名称一样的会发生错误
select student.sid,student.name,class.class from student,class where student.class_id = class.cid;

过程和结果：

// 先创建两个表
// 1.创建班级表
create table class(cid int not null auto_increment primary key, class varchar(32) not null);
// 2.创建学生表
create table student(sid int not null auto_increment primary key, name varchar(32) not null, class_id int not null, constraint fk_classid_cid foreign key(class_id)references class(cid));
// 3.班级表中插入班级数据
insert into class(class) values('1班'),('2班'),('3班');
// 4.学生表中插入学生数据
insert into student(name,class_id) values('小明','1'),('小红','1'),('小华','3');

如下：

// 5.连表查询
select * from student,class where student.class_id = class.cid;
推荐不要用*，如果两个表中有名称一样的会发生错误
select student.sid,student.name,class.class from student,class where student.class_id = class.cid;

// 常用的连表方法为下面的方法：
// 左连接：左边的表student全部显示
select * from student left join class on student.class_id = class.cid;
// 右连接：右边的表class全部显示，此时会发现有NULL，如下图。因为右表对应的2班没有可以和左表匹配的
select * from student right join class on student.class_id = class.cid;
// inner 隐藏NULL 不显示
select * from student inner join class on student.class_id = class.cid;

补充：
左右连表： join
上下连表： union

# 自动去重
select id,name from tb1 union select num,sname from tb2
# 不去重
select sid,sname from student UNION ALL select sid,sname from student