MySQL（三）——DML、TCL和数据库的对象
1.DML
1.1INSERT
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
一般来说，一条INSERT只能插入一条数据，因为MYSQL没有batch操作，所以，MYSQL也支持在一条INSERT中插入多条数据
INSERT INTO table [(column [, column...])] VALUES (value [, value...]),(value [, value...]),...
当然，一条SQL的长度是有限的，可以通过调整max_allowed_packet参数；
1.2UPDATE
UPDATE table
SET column = value [, column = value] …
[WHERE condition];
UPDATE语句也可以使用表连接，子查询等多种方式执行；
1.3DELETE
DELETE [FROM] table
[WHERE condition];
在delete语句中，where子句是可选的部分，如果使用了where子句，则删除的数据是符合where条件的所有记录；如果省略了where子句，则全表的数据都会被删除，delete语句的where条件也同样支持子查询，但是一定注意，删除语句中的where条件不能是要删除的数据表中的数据；所以，在涉及到删除的数据是通过要删除的表中的数据查询出来的，一般会把查询结果临时保存到另一张表，再通过delete语句删除；
2.TCL
2.1数据库事务概念
在数据库中,所谓事务是指一组逻辑操作单元,使数据从一种状态变换到另一种状态。
为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。
说白了： 把多个操作看成是一个不可分割的整体(事务)：
- 如果多个操作都成功了,则认为成功. ------>提交事务
- 如果多个操作中有一个失败,则认为失败.------>回滚事务.
在MySQL中,InnoDB支持事务,MyISAM不支持.
事务的操作:先定义开始一个事务,然后对数据作修改操作,这时如果提交(COMMIT),这些修改就永久地保存下来,如果回退(ROLLBACK),数据库管理系统将放弃您所作的所有修改而回到开始事务时的状态。
2.2事务的ACID属性:
- 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
- 一致性（Consistency）事务必须使数据库从一个一致性状态变换到另外一个一致性状态。(数据不被破坏)
- 隔离性（Isolation）事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
- 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
2.3数据库的事务由下列语句组成：
一组DML语句，修改的数据在他们中保持一致
一个 DDL (Data Define Language) 语句
一个 DCL (Data Control Language)语句
- DCL(Grant（赋于权限 ） / Revoke（回收权限 ）)
2.4控制语句
COMMIT和 ROLLBACK可以显示的控制事务。
好处：
1、保证数据一致性,修改过的数据在没有提交之前是不能被其他用户看到的。
2、在数据永久性生效前重新查看修改的数据
3、将相关操作组织在一起，一个事务中相关的数据改变或者都成功，或者都失败。
1、开始于第一个执行的语句
2、结束于:
用户执行COMMIT 或 ROLLBACK
单个的DDL or DCL 语句
用户连接异常错误，或者用户断开连接
系统崩溃
image.png
image.png
2.4数据库的事务并发问题
image.png
image.png
image.png
image.png
image.png
image.png
为了解决这些问题:数据库提出了隔离级别:
image.png
SELECT * FROM dept for update;获取锁.
只能有一个线程去操作数据库,其他线程只能等着该线程提交或者回滚之后,才能继续操作.
3.数据库的对象
数据库对象:表，索引，视图，图表，缺省值，规则，触发器，语法，函数等。
1、对象名称必须以字母开头
2、有效的字符包括数字、字母和三个特殊字符（# _ $）
3、不要使用保留字作为对象名称
4、同一用户下的对象不能同名，即使是不同的对象类型
3.1表
3.1.1创建表:定义表的结构(有哪些列,每一列存储什么类型的数据)
create table 表名(
列名 类型 约束,
列名 类型 约束,
列名 类型 约束
);
简单的创建表语句
CREATE TABLE emp_copy AS SELECT * FROM emp
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE 1=2
使用select的方式拷贝表结构，不会拷贝表的索引等结构，
所以一般使用
CREATE TABLE emp_bak LIKE emp :来复制表结构（不会拷贝外键）;
3.1.2修改表结构
- 增加表字段
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
使用 ADD 子句增加字段，新的字段只能被加到整个表的最后,并且不能与表中原有的字段重名
alter table employee add column sex char(1);
- 修改表字段
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
可修改列的数据类型,大小
alter table employees modify sex tinyint;
不是任何情况都可以修改的，当字段只包含空值时，类型、大小都可以修改，否则修改可能不能成功
- 删除表字段
ALTER TABLE table
DROP column (columns);
可以从表中删除列:
ALTER TABLE employee DROP COLUMN sex;
从每行中删除掉字段占据的长度和数据,释放在数据块中占用的空间。删除大表中的字段将需要比较长的时间.
3.1.3删除表
drop table：删除表，但并不释放表所占空间；
TRUNCATE TABLE 语句
清除表中所有的记录，delete可以选择删除表中的一部分
是DDL语句，不可以回滚，delete可以使用rollback回滚，放弃修改。
释放表的存储空间，delete不释放空间
2、 是删除数据的方法之一
3、TRUNCATE TABLE table_name;
注意：
1、表中所有数据将被删除
2、没有完成的事务被提交
3、所有相关的索引被删除
4、这个删除操作不能回滚
3.2表的约束(针对于某一列):
- 1.非空约束：NOT NULL，不允许某列的内容为空。
- 2.设置列的默认值：DEFAULT。
- 3.唯一约束：UNIQUE，在该表中，该列的内容必须唯一。
- 4.主键约束：PRIMARY KEY， 非空且唯一。
- 5.主键自增长：AUTO_INCREMENT，从1开始，步长为1。
- 6.外键约束：FOREIGN KEY，A表中的外键列. A表中的外键列的值必须参照于B表中的某一列(B表主键)。
3.3索引
3.3.1原理
索引是:
一个数据库对象
用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放
由数据库自动维护
索引为什么能够加快查询？
3.3.2创建索引
自动:
当在表上定义一个PRIMARY KEY时，自动创建一个对应的唯一索引.
当在表上定义一个外键时，自动创建一个普通索引；
手动:
用户可以创建索引以加速查询.
在一列或者多列上创建索引.
CREATE INDEX index ON table (column[, column]...);
如果多列在一起，就叫做复合索引；在很多情况下，复合索引比单个索引更好（理解原理即可）；
3.3.3优化索引
哪些值可以创建索引？
1，外键一般要创建索引
2，经常使用的查询条件要创建索引。如果使用like ‘%’操作，不会使用索引。
3，索引不是越多越好
4，不要在可选值很少的属性上面创建索引
5，MySQL索引的使用，并不是所有情况下都会使用索引，只有当MySQL认为索引足够能够提升查询性能时才会使用；
3.4视图
3.4.1概念
视图也就是虚表，实际上视图就是一个命名的查询，用于改变基表数据的显示。
可以限制对数据的访问
可以使复杂的查询变的简单
提供了数据的独立性
提供了对相同数据的不同显示
3.4.2创建视图
语法:
在CREATE VIEW语句后加入子查询.
CREATE [OR REPLACE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH READ ONLY];
创建视图
CREATE OR REPLACE VIEW emp_v_30
AS SELECT empno, ename, sal
FROM emp
WHERE deptno =30;
在子查询中使用别名创建视图.
CREATE VIEW sal_v_10
AS SELECT employee_id ID, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id = 10;
在视图中的列名使用的是子查询中列的别名.
3.4.3使用视图
在查询时，不需要再写完全的Select查询语句，只需要简单的写上从视图中查询的语句就可以了
SELECT * FROM sal_v_10;
默认情况下，可以直接通过对视图的DML操作去修改视图对应表中的内容（前提是视图中没有通过公式导出的列）；
3.4.4删除视图
删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义.
DROP VIEW view_name;
