数据库管理的艺术（MySQL）：DDL、DML、DQL、DCL及TPL的实战应用（上：数据定义与控制）

文章目录

• DDL数据定义语言
• • 1、创建数据库
  • 2、创建表
  • 3、修改表结构
  • 4、删除
  • 5、数据类型
• 列的约束
• • 主键约束（`primary key`）
  • 唯一约束（`unique key`）
  • 非空约束
  • 检查约束（`check`）
  • 外键约束（`foreign key`）
• DCL数据控制语言
• • 1、先创建一个新用户
  • 2、查看用户权限
  • 3、给用户赋予权限
  • 4、回收用户权限
  • 5、MySQL权限分类
  • • 数据库/数据表/数据列权限：
    • 全局管理MySQL用户权限：
    • 特别的权限：
  • 6、修改用户密码
  • 7、删除用户
• TPL事务处理语言
• • 事务理解
  • 事务的四大特性(ACID)
  • 事务操作
  • 事务的隔离级别
  • 隔离级别
• 常用SQL

MySQL基础知识：DDL、DML、DQL、DCL及TPL的使用
SQL分类

DDL数据定义语言

针对数据库对象进行操作，如数据库、表、视图等对象

1、创建数据库

create database 数据库名 [default charset utf8] ; 

数据库名要求：不区分大小写，英文，见名知义

数据库默认字符集：latin1

2、创建表

create table 表名(   列名 数据类型(长度),   ......   列名 数据类型(长度) ) [character set utf8 collate utf8_general_ci] ; 

3、修改表结构

修改表名

alter table 原表名 rename [to] 新表名; 

修改表中的列（列名、列类型、列长度）

alter table 表名 change 原列名 新列名 新类型(新长度); 

新增一个列

alter table 表名 add 新列名 新类型(新长度); 

删除一个列

alter table 表名 drop 原列名; 

4、删除

删除table

drop table 表名; 

删除数据库

drop database 数据库名; 

5、数据类型

列的约束

表格中列的约束全部都是在创建表格之后做的表格列结构的修改。

需要用到DDL语句进行操作（alter、drop）

主键约束（primary key）

1. 每一个表格内只能有一个列被设置为主键约束
2. 主键约束通常是用来标记表格中数据的唯一存在
3. 主键约束要求当前的列 不能为null值
4. 主键约束要求当前的列 值是唯一存在的 不能重复

添加主键约束：

语法：alter table 表名 add constraint 约束名字 约束类型 (列);

例：alter table myclass add constraint pk_myclass primary key (classid);

简写：alter table 表名 add primary key (列);

主键自增：

语法1：alter table 表名 modify 列名 字段类型 auto_increment;

例：alter table myclass modify classid int(4) auto_increment;

语法2：alter table 表名 change 列名 字段类型 auto_increment;

例：alter table myclass change classid int(4) auto_increment;

没有做起始值的说明，主键列的值会从1开始。

设置自增起始值：alter table 表名 auto_increment = 起始值;

删除主键约束：alter table 表名 drop primary key;

注意:删除主键约束以后，不重复的特性取消了，但非空特性还在。

需要手动取消非空：alter table 表名 modify 字段名 字段类型 null;

唯一约束（unique key）

1. 可以为表格中的某一个列添加唯一约束，唯一约束在表格中可以存在多个列
2. 唯一约束表示的是列的值不能重复，但可以为空

添加唯一约束：

语法：alter table 表名 add constraint 约束名 约束类型 (列);

例：alter table myclass add constraint uk_myclass unique [key] (loc);

简写：alter table 表名 add unique key(字段); 约束名为默认的列名

删除唯一约束：alter table 表名 drop index 约束名;

非空约束

1. 在表格中的某一个列上添加非空约束
2. 当前列的值不能为null

添加非空约束：
语法1：alter table 表名 modify 原列名 原类型 原长度 [not] null default xxx;

语法2：alter table 表名 change 原列名 原列名 原类型 原长度 [not] null default xxx;

删除非空约束：

alter table 表名字 modify 列 类型 长度 null;

alter table 表名字 change 列 列 类型 长度 null;

检查约束（check）

列在存值的时候做一个细致的检查

例如：范围是否合理

alter table student add constraint ck_sage check( sage>15 and sage<30);

外键约束（foreign key）

1. 约束自己表格内的信息不能随意填写
2. 受到另外一个表格某一个列的影响，当前列的值要去另外一张表格内寻找（另外一张表格的列是唯一的【主键、唯一】）
3. 表格中可以有多个列被设置为外键约束
4. 当前列的值可以为空，可以重复

添加外键约束：
alter table 表名字 add constraint fk_当前表_关联表 foreign key(列) references 另一个表(列);

简写：
alter table student add foreign key(列) references 另一个表(列);

注意: 如果是简写的形式添加外键，外键的名字不是默认列名

查看外键约束：

通过show keys from 表; 或 desc 表;或 show create table 表名;

PRI UNI MUL---->multiple(多样 并联)

删除外键约束：
alter table 表名字 drop foreign key 约束名字;

注意：通过上述语句其实已经将外键约束删掉了

但会自动在当前表格内添加一个新的key

需要再次手动将这个生成的key删掉，外键约束才真的删除干净

alter table 表名字 drop key 约束名字;

DCL数据控制语言

数据控制语言Data Control Language：控制用户的权限

1、先创建一个新用户

语法：create user '用户名'@'IP' identified by '密码';

2、查看用户权限

语法：show grants for '用户名'@'IP';

用户被创建成功 (只有一个默认的权限 Usage 只允许登录 不允许做其他事情)

3、给用户赋予权限

语法：grant 权限 on 数据库名.表名 to '用户'@'IP';

赋予权限之后最好做一个刷新flush privileges;

4、回收用户权限

语法：revoke 权限 on 数据库名.表名 from '用户名'@'IP';

上述回收了用户删除数据表或数据库的权限。

5、MySQL权限分类

数据库/数据表/数据列权限：

Create： 建立新的数据库或数据表

Alter ： 修改已存在的数据表(例如增加/删除列)

Drop ： 删除数据表或数据库

Insert： 增加表的记录

Delete： 删除表的记录

Update： 修改表中已存在的记录

Select： 显示/搜索表的记录

References： 允许创建外键

Index ： 建立或删除索引

Create View： 允许创建视图

Create Routine： 允许创建存储过程和包

Execute： 允许执行存储过程和包

Trigger： 允许操作触发器

Create User： 允许更改、创建、删除、重命名用户和收回所有权限

全局管理MySQL用户权限：

Grant Option： 允许向其他用户授予或移除权限

Show View： 允许执行SHOW CREATE VIEW语句

Show Databases： 允许账户执行SHOW DATABASE语句来查看数据库

Lock Table： 允许执行LOCK TABLES语句来锁定表

File： 在MySQL服务器上读写文件

Process： 显示或杀死属于其它用户的服务线程

Reload： 重载访问控制表，刷新日志等

ShutDown： 关闭MySQL服务

特别的权限：

All： 允许做任何事(和root一样)

Usage： 只允许登录，其它什么也不允许做

6、修改用户密码

1. 查看用户密码
   select u.User, u.Host,u.authentication_string from mysql.user u where u.User = 'Lizi';

2. 修改用户密码

   update mysql.user set authentication_string = password('新密码') where user = '用户名';

   刷新：flush privileges;

7、删除用户

语法：drop user '用户名'@'IP';

TPL事务处理语言

事务理解

SQL语句是我们给数据库发送指令，让数据库帮我们做事情。

事务：可以理解为是让数据库做的事情，可能存在多个 SQL 操作。

所有的操作应该是统一的，要么都成功，要么都失败。

事务的本质可以理解成：多线程并发操作同一张表格可能带来的安全问题。

事务的四大特性(ACID)

1. Atomicity 原子性
   一个事务中的所有操作是一个整体，不可再分。事务中的所有操作要么都成功，要么都失败。

2. Consistency 一致性
   一个用户操作了数据，提交以后，另一个用户看到的数据效果是一致。

3. Isolation 隔离性

   • 多个用户并发访问数据库时，一个用户操作数据库，另一个用户不能有所干扰。

   • 多个用户之间的数据事务操作要互相隔离。

4. Durability 持久性
   一个用户操作数据的事务一旦被提交(缓存–>文件)，对数据库底层真实的改变是永久性的。

事务操作

1. 开启一个事务
   每一次执行的一条sql语句之前，mysql数据库都会默认的开启
   begin;或 start transaction;

2. 执行操作（执行SQL）

3. 事务处理
   mysql数据库会默认的执行提交事务
   提交 commit
   回滚 rollback
   保存还原点 save point xx

事务的隔离级别

事务的隔离性可能会产生多线程并发操作同一个数据库表格的问题，会带来数据的安全隐患。

1. 脏读：一个人读到了另外一个人还没有提交的数据。
   A、B在操作同一张表格
   A修改了数据，还没有提交，B读取到了
   A不提交了，回滚回来，B刚刚读取到的那些数据就是无用的----脏数据

2. 不可重复读
   A、B在操作同一个表格
   A先读取了一些数据，读完之后B此时将数据做了修改/删除
   A再按照之前的条件重新读一遍，与第一次读取的不一致

3. 幻读(虚读)
   A、B在操作同一个表格
   A先读取了一些数据，读完之后B此时将数据做了新增
   A再按照之前的条件重新读一遍，与第一次读取的不一致

隔离级别

1. Serializable 级别最高，可以避免所有出现的问题，性能很慢

2. Repeatable Read 可重复读 (避免脏读、不可重复读)

3. Read Committed 读已提交 (避免脏读)

4. Read UnCommitted 读未提交 (所有效果均无法保证)

MySQL数据库提供默认隔离级别 Repeatable Read
Oracle数据库提供默认隔离级别 Read Committed
查看数据库隔离级别：select @@tx_isolation;

修改隔离级别：set session transaction isolation level xxx;
一般情况下不需要改动。

常用SQL

# 显示有哪些database show databases;   #使用哪个数据库 use database名字;  # 显示当前数据库有哪些table show tables;  # 查询数据库的字符集 select schema_name, default_character_set_name from information_schema.schemata where schema_name = `查询的数据库名`;  # 查询表格信息 show table status from 数据库名 like `表名`;  #查看表有什么约束 desc 表名; show keys from 表名; show create table 表名; 

相关文章：
《数据库管理的艺术（MySQL）：DDL、DML、DQL、DCL及TPL的实战应用（下：数据操作与查询》）