MySQL学习总结：约束、自增长序列、分页查询、视图、索引

约束

约束:constraint，数据库对表中的数据进行一件限制。

主键约束：primary key

非空约束：not null

唯一性约束：unique

检查型约束：check

外键约束：foreign key

1.主键约束

每一个表中只能设置一个主键约束；主键字段的值非空且唯一；因此，充当唯一标识符的字段可以设置主键约束。

create table t_100(
    idcard varchar(18) primary key,
    name varchar(20)
);

可以将表中多个字段看成一个组合设置成一个主键约束；组合不相同就可以插入数据，但是字段中不能有null。

create table t_101(
    idcard varchar(18),
    name varchar(20),
    primary key(idcard,name)
);

insert into t_101 values('123456789',null);会失败

2.非空约束

要求设置为非空约束的字段的值不能有null

create table t_102(
    name varchar(20) not null,
    age int
);

解除表的非空约束设置：

alter table t_102 modify name varchar(20) null;

建表后，再设置非空约束，有条件限制：表中相应字段的值不能有null值存在

alter table t_102 modify age int not null;

3.唯一性约束

有唯一性约束的字段的值不能重复，但是可以为null

create table t_103(
    empno varchar(10) unique,
    ename varchar(20) not null,
    idcard varchar(18)
);

建表后，设置唯一性约束,但表中不能存在重复的数据

alter table t_103 modify idcard varchar(18) unique;
alter table t_103 add CONSTRAINT id111 unique(idcard);

查看表中的唯一性约束名称

show keys from t_103;

删除唯一性约束

drop index id111 on t_103;

4.检查性约束

对设置检查型约束的字段的值的要求是必须符合检查性条件

4.1、如果是集合性质的条件，如 gender 必须('f','m')中的某一个值，那么就要用枚举enum('f','m')

create table t_104(
    name varchar(20) not null,
    age int,
    gender enum('f','m')
);

建表后，设置检查性约束

alter table t_104 add CONSTRAINT enum_age check(age>18);
alter table t_104 modify gender enum('f','m','n');

4.2、如果条件是范围性的条件，如 age>18 and age<100用check(条件)

create table t_105(
    name varchar(20) not null,
    age  int check(age>18 and age<100),
    gender enum('f','m')
);

5.外键约束

某一字段如果设置为外键约束，那么此字段值依赖于另外一个字段里已经存在的值，且是主键。但是此字段值可以为null；这个字段可以是依赖于本表中某一字段，也可以是依赖于其他表中的某一字段。

create table t_107(
    empno int primary key,
    ename varchar(20) not null,
    mgr int,
    FOREIGN key(mgr) REFERENCES t_107(empno)
);

建表后的写法：

create table t_200(
    deptno int primary key,
    dname varchar(20)
);
create table t_201(
    ename varchar(20),
    dno int
);

alter table t_201 add CONSTRAINT FK_dno FOREIGN key(dno)
REFERENCES t_200(deptno);

自增长"序列"

自增长序列：mysql不支持序列机制，但是mysql的auto_increment可以达到与序列机制一样的效果

auto_increment 关键字，用来对有主键约束的字段做自增操作；自增长序列从1开始，自增长序列的步数为1，可以设置起始数字

create table t_1000(
    tid int primary key auto_increment,
    tname varchar(20) not null,
    sal float(7,2)
);

设置开始数值：

create table t_1001(
    tid int auto_increment primary key,
    tname varchar(20)
);
-- 从100开始
alter table t_1001 auto_increment=100;

or

create table t_1002(
    tid int primary key auto_increment,
    tname varchar(20)
)auto_increment=200 ENGINE=innodb;

 

建表的默认值

当建表时，对某些字段设置了默认值后。如果在插入数据时，对此字段没有赋任何值时，那么此字段值为默认值。

create table emp_1(
    empno int primary key auto_increment,
    ename varchar(20) not null,
    mgr int,
    gender char(1) default 'f',
    age int default 18,
    constraint fk_mgr_empno foreign key(mgr) references emp_1(empno)
);

分页查询

当每次查询的记录数比较大，通常一页显示不下，可以进行分页查询；关键字为limit；

语法：select * from tableName limit [begin,] count；

begin：记录的开始行数. 即偏移量；可以不写，默认从0开始，而不是1.

count：每页的最大记录数。

查询第page页的数据，每页size条记录那么偏移量：(page-1)*size

分页查询优化

1、直接使用数据库提供的SQL语句

select * from tableName limit m,n

适应场景:数据量较少的情况(元组百/千级)

缺点：全表扫描，速度慢，且有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)；Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。

查询员工表的第二页数据，每页5条数据：

select * from emp limit 5,5;

2、建立主键或唯一索引, 利用索引

适应场景: 数据量多的情况(元组数上万)

索引扫描，速度快。但是数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况。

alter table emp  modify  empno int primary key;
select * from emp limit 5,5;

3、基于索引再排序

语法：SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M

适应场景: 数据量多的情况(元组数上万)。最好ORDEBY后的列对象是主键或唯一，使得ORDERBY操作能利用索引，结果集是稳定的。

索引扫描，速度快。但MySQL的排序操作,只有ASC没有DESC。

select * from (select * from emp order by empno) a limit 5,5;  -- 使用子查询进行分页
select * from emp order by empno limit 5,5;  --使用order by分页

4、使用prepare进行预编译语句格式

语法：PREPARE stmt_name FROM 'SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M'

适应场景：大数据量

索引扫描，速度很快.。prepare语句又比一般的查询语句更快。因为DBMS系统在执行SQL语句的 每次都会检查语法，解析

语句，执行语句。如果使用了prepare语句，则只在第一次解析，之后的多次使用都不需要再解析，提高了查询效率。

prepare preName from 'select * from testpage1 where tid >? order by tid limit ?'


select * from testpage1 where tid >40000 order by tid limit 100;

视图

视图被称之为虚表，即虚拟的表，本身并不包含任何数据，它只包含映射到基表的一个查询语句。当基表的数据发生变化，视图的逻辑数据可能会产生变化，也就是表的部分投影。

语法：create [or replace] view ViewName as select语句;

删除：drop view viewName;

特点

a、简单化。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

b、安全化。使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现(可以隐藏基表中的某些字段)。

c、数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

分类

a、简单视图：映射的select语句基于单表查询，不包含任何函数运算的；

b、复杂视图：映射的select语句基于单表查询，但是包含函数运算、分组查询等复杂操作，叫复杂视图；

c、连接视图：映射的select语句基于多表查询。

视图的DQL操作与查询表操作一致；

只能对简单视图进行DML操作。对视图进行DML操作时，实际上是对基表进行DML操作，因此需要注意基表的相关约束条件限制。比如当基表中有非空约束的字段，但是这个字段对视图不可见，那么就不能对视图进行insert操作；如做delete操作时,只能对视图中可见的数据进行操作。

对视图DML操作的影响

a、通过视图进行DML操作，一定会对基表有影响。

b、通过对基表进行DML操作，可能会对视图有影响。

with check option：要求除了DQL操作外，只能对视图中可见的数据进行DML操作

create view v_t_x as select * from t_v where tdeptno=30 with check option;
insert into v_t_x values (null,'lilei','f','13911111111',30);

索引

索引是允许直接访问数据表中某一记录的树形结构，是为了提高查询效率而引入，是独立于表的对象。索引记录中存有索引

关键字和指向表中数据的指针(地址)。

特点：

a、索引一旦被创建，将由数据库自动维护，查询语句中不需要指定使用哪个索引；

b、表中的每个字段都可以设置相应的索引。

优点：查询速度高；

缺点：索引占空间，每次进行DML操作时，数据库都要(自动)重新维护索引，降低效率。

适合被设置为索引的字段：字段内的值的种类很多；经常出现在where子句中的字段。

索引的创建

语法：create index indexName on tableName(fieldName);

or

建表时指定索引

create table t_301(
	tid int,
	tname varchar(20),
	gender varchar(1),
   	index [indexName] (fieldName)
);

alter table tableName add unique index indexName (fieldName);