MySQL基础二

亦凉 2022-05-24 01:18 161阅读 0赞

### 四、DDL语言（Database Define Language） ###

数据库定义语言（关于表的）

一、库和表的管理

1、创建    create

2、修改    alter

3、删除    drop

1）库

创建

create database 【if not exists】库名；

修改

一般不修改库名

修改字符集

alter database 库名 character set 字符集名

删除

drop database 【if exists】 库名

2）表

创建

create table 【if not exists】 表名(
      列名 列的类型【（长度） 约束】
      列名 列的类型【（长度） 约束】
      。。。。
      列名 列的类型【（长度） 约束】
    );

修改（列名的增加和删除、列的类型和约束、表名）

*  修改列名

alter table 表名 change column 旧名 新名 类型

*  修改列的类型或约束

alter table 表名 modify column 列名 新类型

*  添加新列

alter table 表名 add column 列名 列类型

*  删除列

alter table 表名 drop column 列名

*  修改表名

alter table 旧表名 rename to 新表名;

删除

drop table 【if exists】 表名；

复制

*  仅复制表的结构

create table 新表名 like 被复制的表名;

*  复制表结构和数据(所有)

create table 新表名 select * from 被复制的表名;

*  复制某些字段

create table 新表名 select 字段1，字段2 from 被复制的表名 where 0;

二、数据类型

选择的原则：越简单越好，能保存数值的类型越小越好

1、数值类型

*  整型  
    ![70][]
 *  小数  
    ![70 1][]

2、字符类型

*  较短的文本

char：固定长度的字符

varchar：可变长度的字符

![70 2][]

*  较长的文本

text、blob（较大的二进制（图片））

3、日期类型

![70 3][]

三、常见约束

分类：六大约束

1.  NOT NULL：非空，用于保证该字段的值不能为空。如姓名、学号等
2.  DEFAULT：默认，用于保证该字段有默认值，如性别
3.  PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空，如学号、员工编号
4.  UNIQUE：唯一，用于保证字段的值唯一性，可以为空。如座位号
5.  CHECH：检查约束【MySQL不支持】、
6.  FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表添加外键约束，用于引用主表中的某列的值。如学生表的专业编号、员工表的部门编号

添加约束的时间：1、创建表的时候；2、修改表的时候

约束的添加分类：

*  列级约束  
    除了外键约束，其他都行
 *  表级约束  
    除了非空，默认，其他都支持

create table 表名(
     字段名 字段类型 列级约束,
     字段名 字段类型,
     表级约束
    )

CREATE TABLE IF NOT EXISTS stuinfo(
    	id INT PRIMARY KEY,
    	stuname VARCHAR(20) NOT NULL,
    	sex CHAR(1),
    	age INT DEFAULT 18,
    	seat INT UNIQUE,
    	majorId INT,
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
    )

主键和唯一对比：

<table style="width:591px;"> 
 <tbody> 
  <tr> 
   <td style="width:61.5px;">&nbsp;</td> 
   <td style="width:94px;">保证唯一性</td> 
   <td style="width:102px;">是否允许为空</td> 
   <td style="width:158px;">一个表中可以有多少个</td> 
   <td style="width:175px;">是否允许组合</td> 
  </tr> 
  <tr> 
   <td style="width:61.5px;">主键</td> 
   <td style="width:94px;">√</td> 
   <td style="width:102px;">×</td> 
   <td style="width:158px;">至多1个</td> 
   <td style="width:175px;">√，但是不推荐</td> 
  </tr> 
  <tr> 
   <td style="width:61.5px;">唯一</td> 
   <td style="width:94px;">√</td> 
   <td style="width:102px;">√</td> 
   <td style="width:158px;">可以有多个</td> 
   <td style="width:175px;">√，但是不推荐</td> 
  </tr> 
 </tbody> 
</table>

外键：

1.  要求在从表设置外键
2.  从表的外键列的类型和主表的关联列的类型要求一直或兼容，名称无要求
3.  主表的关联列必须是一个key（一般是主键或唯一）
4.  插入数据时，先插入主表再插入从表；删除时，先删除从表，再删除主表

修改表时添加约束：

列级：

alter table 表名 modify column 列名 类名 约束

表级：

alter table 表名 add 约束（字段名）

四、标识列

auto\_increment

### 五、TCL语言（Transaction Control Language） ###

事务控制语言

1、概念

事务：一个或一组SQL语句组成呢给一个执行单元，这个执行单元要么全部执行，要么全部不执行。

事务的ACID属性：

*  **原子性**：原子性是指事务是一个不可分隔的工作单元，事务中的操作要么都发生，要么都不发生。
 *  **一致性**：事务必须使数据库从一个一致性状态变化到另一个一致性状态
 *  **隔离性**：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务的内部操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
 *  **持久性**：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是持久的，接下来的其他操作和数据故障不应该对其具有任何影响。

2、事务的创建

隐式事务：事务没有明显的开启和结束标记。如insert、update、delete等

显示事务：事务具有明显的开启和结束标记。前提：必须设置自动提交功能为禁用

set autocommit = 0

步骤：

1）开启事务

set autocommit=0;
    start transaction;(可选的)

2）编写事务中的SQL语句（select、insert、update、delete）

3）结束事务

commit;
    或
    rollback;

3、事务的隔离级别

1）对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必须要的隔离机制，就会导致各种并发问题：

*  **脏读**：对于两个事务T1、T2，T1读取了已经被T2更新但是还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的。
 *  **不可重复读**：对于两个事务T1、T2，T1读取了一个字段，然后T2更新了该字段之后，T1再次读取同一个字段，值就不同了。
 *  **幻读**：对于两个事务T1、T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行之后，如果T1再次读取同一个表，就会多出几行。

2）**数据库事务的隔离性**：数据库系统必须具有隔离并发运行各个事务的能力，使他们不会互相影响，避免各种并发问题。

*  一个事务的与其他事务隔离的程度称为隔离级别。数据库规定了多种数据库隔离级别，不同的隔离级别对应不同的干扰程度，隔离级别越高，数据一致性越好，但并发越弱。

3）数据提供的4种事务隔离级别：

![70 4][]

Mysql支持4种事务隔离级别，默认的事务隔离级别为：REPEATABE READ

查看当前的隔离级别：

select @@tx_isolation;

设置当前mysql连接的隔离级别：

set session transaction isolation level +隔离级别

![70 5][]

**脏读**：

![70 6][]

**不可重复读**：

![70 7][]

**幻读**：

![70 8][]

![70 9][]

但是当隔离级别为serializable时，在其他事务中操作就会出现阻塞，避免了幻读。

4、savepoint——节点名：设置保存点

set autocommit=0;
    start transaction;
    delete from t_user where id=1;
    savepoint a;#设置保存点
    delete from t_user where id=2;
    rollback to a;#回滚到保存点

[70]: /images/20220524/e67e964a2a164ac791e1f1a2f8f3eea4.png
[70 1]: /images/20220524/b9fc393a5e074681afee8d65932d3500.png
[70 2]: /images/20220524/bc2c2331c2a946ef8dc5690a075feee4.png
[70 3]: /images/20220524/06ed63daf38b4aeb9a360ac20631db7c.png
[70 4]: /images/20220524/4f4cce8ec59e4b3c89883313ddb21568.png
[70 5]: /images/20220524/43ef898e6f9841fabc0b4305047e0bf4.png
[70 6]: /images/20220524/7cdaebbd28374bd1bcec3d8aa5d93c94.png
[70 7]: /images/20220524/c5b8b1da363c48d698e0a12629769617.png
[70 8]: /images/20220524/7e0c810c0d0b4a7cb5f70e744fd2a6fa.png
[70 9]: /images/20220524/74bac56b8df943a9a1e92071eb024fff.png