MySQL基础【事务、预编译、约束、分页】

1、事务

        将一个数据执行步骤的集合作为一个单元来处理，让一组逻辑操作单元当成一个单个的命令来执行，要么全部成功，要么全部失败，比如转账业务。

        事务的特点（ACID）：

        A 原子性 : 不可分割 
        C 一致性 : 数据前后是一致
        I 独立性 : 事务间 (独立性有等级)
        D 持久性 : 事务一旦提交, 数据持久化。所有客户端看到的数据一样。

为什么使用事务：保证数据的安全性。

开启事务的步骤：

设置提交状态：SET AUTOCOMMIT = FALSE;
    或者显式的执行 start transaction
            或 begin
			
以第一个 DML 语句的执行作为开始

当出现以下语句之一，事务的执行结束：
    COMMIT 或 ROLLBACK 语句
    DDL 语句（自动提交）
    用户会话正常结束, 提交
    系统异常终止      回滚（代表事务执行失败）

SET AUTOCOMMIT = true;

        事务的测试：在第一个客户端执行 start transaction; delete from chinaCity; 开启第二个客户端，查看库中数据 select * from chinaCity; 发现仍然可以查询到，但想要修改表中数据，在事务没有提交或回滚之前是无法进行的。此时相当于多线程，线程一在事务没有完成之前，会锁住当前资源，最后恢复数据库原始设置 set autocommit = true;

2、预编译

        客户端将把需要执行的sql语句发送给服务器，在服务器内部进行编译执行，浪费时间。提前把SQL编译成可执行的, 在执行时只需要在服务器调用它即可，可以提升性能。

        语句格式：prepare 预编译名 from 'SQL'; 

prepare s1 from 'select * from teachers';

执行预编译，在另一个客户端不能执行
execute s1; 

丢弃预编译
drop prepare s1;

        预编译隶属于会话。

        插入预编译语句

prepare i1
from
    'insert into teachers(
        name,
        age,
        mobile
    ) values (
        ?,
        ?,
        ?
    )';

    在执行时预编译时, 代替?的实参必须要用用户变量
    set @变量名 = 值, @变量名2 = 值2;
    execute 预编译 using @变量名, @变量名2;

set @name = '微微',@age = 23, @mobile = '12345678911';
execute i1 using @name,@age,@mobile;
select * from teachers;

        删除预编译语句

prepare d1 from
'delete from teachers
where id = ?';

set @id = 8;

execute d1 using @id;

        ? 只能代替值的部分, 表名, 列名绝不可以。

        编写多个同名的预编译语句不会报错，会将同名的预编译语句覆盖掉。

prepare i1
from
    'insert into teachers(
        name,
        age,
        mobile,
        gender
    ) values (
        ?,
        ?,
        ?,
        ?
    )';

        在SQL中要想使用', 必须再加一个', 起到转义的作用。        

prepare i2 from 
'
	insert into classes (
		name,
		student_count,
		room,
		master,
		begindate 
	) values (
		?,
		?,
		''606'',
		?,
		now() --函数调用
	)
';

        强烈依赖表结构的插入方式，不灵活，insert into classes values(值的列表);。

3、约束条件

        有以下六种约束:
                NOT NULL 非空约束，规定某个字段不能为空, 必须列级约束 
                UNIQUE  唯一约束，规定某个字段在整个表中是唯一的，推荐表级约束
                PRIMARY KEY  主键(非空且唯一)
                FOREIGN KEY  外键
                DEFAULT  默认值, 必须是列级

create table test(
	id int auto_increment, --自增的属性必须设置为主键
	--name vatchar(20) unique, 列级约束，不推荐
	name varchar(20),
	phone varchar(20) not null, -- 必须是列级约束
	unique(name), -- 优先表级约束，因为更加灵活
	primary key(id)
);

        丢弃唯一键约束

alter table test 
drop key name;

create table test2(
	id int auto_increment,
	name varchar(20),
	phone varchar(20) not null, -- 必须列级约束
	constraint myunique unique(name, phone), -- 可以表级约束, 联合键，两个都不能相同
	primary key(id)
);

        丢弃主键

alter table test2
drop primary key;

        外键： 让一个表中的记录的值要引用到另一张表中的数据。

        一旦有了外键, 子表中插入数据必须要引用到真实的父表中数据，父表中不存在的数据会无法插入。
        一旦父表中的记录被子表引用, 当删除父表中的相关记录时, 不允许删除。

        语法格式：foreign key(本表的外键列) references 父表(父表主键)

        将classes表中的master属性与teachers表中的主键联结起来。

--删除classes表
drop table if exists classes;
--新建classes表
create table if not exists classes(
	id int auto_increment,
	name varchar(30),
	student_count int,
	room char(3),
	master int, 
	begindate date,
	primary key(id),
	foreign key(master) references teachers(id)
);

        插入几条数据。 

insert into classes(
    name,
    student_count,
    room,
    master,
    begindate
) values (
    'MySQL',
    202201,
    '222',
    1,
    now()
);
insert into classes(
    name,
    student_count,
    room,
    master,
    begindate
) values (
    'MySQL1',
    202201,
    '222',
    2,
    now()
);
insert into classes(
    name,
    student_count,
    room,
    master,
    begindate
) values (
    'MySQL2',
    202201,
    '222',
    3,
    now()
);

        丢弃外键，必须要知道外键名。因为一个表中可能有多个外键。       

show create table classes;

alter table classes 
drop foreign key 外键名;

        添加外键的三种额外选项               

alter table classes 
add foreign key(master) references teachers(id) 额外选项;

on delete do nothing, 是默认选项, 在删除父表被引用的记录时不允许
on delete cascade 级联删除, 当删除父表中的相关记录时, 子表中引用此记录的所有记录也会被删除。
on delete set null 级联置空.当删除父表中的相关记录时, 子表中引用此记录的所有记录中的外键会置为null。

        如 

alter table classes 
add foreign key(master) references teachers(id) on delete cascade;

4、分页

        limit n, 把结果集截断成n条记录。

        limit m, n 把结果集中的m条略过, 再截断成n条记录。

        公式：
        （当前页数-1）*每页条数，每页条数
        SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

        注意：
        limit子句必须放在整个查询语句的最后！