Linux

数据库管理、表管理、约束、事务控制语言TCL

2021-08-06  本文已影响0人  秋天丢了李姑娘

数据库管理

创建数据库

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

修改数据库

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

删除数据库

DROP DATABASE [ IF EXISTS ] <数据库名>

表管理

关系数据库的规范化

良好的数据库设计表现在以下几方面：

关系数据库的规范化理论为：关系数据库中的每一个关系都要满足一定的规范。根据满足规范的条件不同，可以分为6个等级：第一范式（1NF）、第二范式（2NF）……第五范式（5NF）。其中，NF是Normal Form的缩写。一般情况下，只要把数据规范到第三范式标准就可以满足需要了。

第一范式（1NF）
第二范式（2NF）
第三范式（3NF）

表管理语句

创建表
CREATE TABLE 表名称
(
列名称1 数据类型 [(长度) 约束],
列名称2 数据类型 [(长度) 约束],
列名称3 数据类型 [(长度) 约束],
....
)
数据类型 描述
tinyint(m) 1个字节 范围(-128~127)
smallint(m) 2个字节 范围(-32768~32767)
mediumint(m) 3个字节 范围(-8388608~8388607)
int(m) 4个字节 范围(-2147483648~2147483647)
bigint(m) 8个字节 范围(+-9.22*10的18次方)
float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
double(m,d) 双精度浮点型 16位精度(8字节) m总个数，d小数位
decimal(m,d) m表示十进制数字总的个数，d表示小数点后面数字的位数。常用于货币
char(n) 固定长度，最多255个字符
varchar(n) 不固定长度，最多65535个字符
tinytext 可变长度，最多255个字符
text 可变长度，最多65535个字符
mediumtext 可变长度，最多2的24次方-1个字符
longtext 可变长度，最多2的32次方-1个字符
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
enum(选项1, 选项2, ...) 单选字符串数据类型，适合存储表单界面中的“单选值”
set(选项1,选项2, ...) 多选字符串数据类型，适合存储表单界面的“多选值”。 
# 创建数据库mydb
mysql> create database mydb default charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed

# 创建部门表
mysql> create table departments (
    ->   id int,
    ->   dept_name varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

修改表

修改列名
ALTER TABLE 表
CHANGE [COLUMN] 列表 数据类型

mysql> alter table departments
    -> change id dept_id int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
修改列的类型或约束
ALTER TABLE 表
MODIFY [COLUMN] 列名 类型

mysql> alter table departments
    -> modify dept_name varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
添加新列
ALTER TABLE 表
ADD [COLUMN] 列名 类型

mysql> alter table departments
    -> add manager_id int;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
删除列
ALTER TABLE 表
DROP [COLUMN] 列名

mysql> alter table departments
    -> drop manager_id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
修改表名
ALTER TABLE 表名
RENAME TO 新表名

mysql> alter table departments
    -> rename to depts;
Query OK, 0 rows affected (0.00 sec)

删除表

DROP TABLE [IF EXISTS] 表名

mysql> drop table depts;
Query OK, 0 rows affected (0.01 sec)

表复制

仅复制表结构
CREATE TABLE 待创建的表名 LIKE 已有表名

mysql> create table departments like nsd2021.departments;
Query OK, 0 rows affected (0.01 sec)
复制表结构及数据
CREATE TABLE 待创建的表名
SELECT 字段, ... FROM 已有表名

mysql> create table departments2
    -> select * from nsd2021.departments;
Query OK, 13 rows affected (0.01 sec)
Records: 13  Duplicates: 0  Warnings: 0

约束

约束分类

约束可应用在列级或表级。列表所有约束均支持，但外键约束没有效果；表级约束可以支持主键、唯一、外键约束。

约束应用

列级应用
mysql> create table employees(
    ->   employee_id int primary key,       -- 主键约束
    ->   name varchar(20) not null,         -- 非空约束
    ->   gender enum('男', '女'),
    ->   email varchar(20) unique,          -- 唯一约束
    ->   nation varchar(10) default '汉族'  -- 默认值约束
    -> );
Query OK, 0 rows affected (0.00 sec)
表级约束
mysql> create table employees2 (
    ->   employee_id int,
    ->   name varchar(20),
    ->   email varchar(20),
    ->   dept_id int,
    ->   primary key (employee_id),   -- 主键
    ->   unique (email),              -- 唯一
    ->   foreign key (dept_id) references departments(dept_id)  -- 外键
    -> );
    
# 查看约束
mysql> select * from information_schema.table_constraints  where table_name='employees2' \G

mysql> create table employees3 (
    ->   employee_id int,
    ->   name varchar(20),
    ->   dept_id int,
    ->   constraint pk primary key(employee_id),   # 不报错，不生效
    ->   constraint fk_employees3_departments foreign key(dept_id) references departments(dept_id)
    -> );
删除约束
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

mysql> alter table employees3
    -> drop foreign key fk_employees3_departments;

例：创建员工数据库的三张表

# 创建部门表
create table departments(
    dept_id int AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(10) UNIQUE
);

# 创建员工表
create table employees(
    employee_id INT auto_increment primary key,
    name VARCHAR(10) not null,
    hire_date DATE,
    birth_date DATE,
    email varchar(25) UNIQUE,
    phone_number varchar(11),
    dept_id int,
    FOREIGN KEY(dept_id) references departments(dept_id)
);

# 创建工资表
create table salary(
    id int AUTO_INCREMENT PRIMARY KEY,
    date DATE,
    employee_id int,
    basic int,
    bonus int,
    FOREIGN KEY(employee_id) references employees(employee_id)
);

事务控制语言TCL

事务(要么全都执行，要么全不执行)

事务必须满足的4个条件

事务控制语句

MySQL事物处理的方法

事务的创建

隐式事务
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)
显式事务
mysql> set autocommit=0;  # 只对当前会话生效
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)
创建事务步骤
  1. 开启事务
set aotocommit=0;
start transaction;    # 可选

  1. 编写事务语句：INSERT、UPDATE、DELETE语句

  2. 结束事务

commit | rollback;
事务示例
mysql> use mydb;
mysql> create table bank(
    ->   id int primary key,
    ->   name varchar(20),
    ->   balance int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into bank values
    -> (1, '牛犇', 10000), (2, '张志刚', 10000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set autocommit=0;
mysql> update bank set balance=balance-1000 where name='牛犇';
mysql> update bank set balance=balance+1000 where name='张志刚';
# 此时在另一终端查看bank表，数据并未改变
mysql> commit;

mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';
mysql> update bank set balance=balance-1000 where name='张志刚';
# 此时在另一终端查看bank表，数据并未改变
mysql> rollback;

事务隔离

事务隔离要解决的问题
事务隔离级别

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题。只有串行化的隔离级别解决了全部这 3 个问题，其他的 3 个隔离级别都有缺陷。

隔离级别 出现脏读 出现不可重读 出现幻读
读未提交 可能 可能 可能
读提交 不可能 可能 可能
可重复读 不可能 不可能 可能
串行化 不可能 不可能 不可能
设置事务隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

# 在第一个终端上执行以下2条语句
mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';

# 以下3条语句在第二个终端上执行
mysql> set session transaction isolation level read uncommitted;
mysql> set autocommit=0;
mysql> select * from bank;   # 此时牛犇账户上已经增加1000

# 回到第一个终端回滚
mysql> rollback;

# 在第2个终端上重新查询
mysql> select * from bank;   # 此时牛犇账户上又减少了1000

SAVEPOINT应用

基本用法
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
SAVEPOINT示例
mysql> set autocommit=0;
mysql> update bank set balance=balance+1000 where name='牛犇';
mysql> savepoint aaa;    # 创建保存点
mysql> select * from bank;   # 牛老师账号已增加1000
mysql> update bank set balance=balance-1000 where name='张志刚';
mysql> select * from bank;   # 张志刚账号已减少1000
mysql> rollback to aaa;  # 回滚到保存点aaa
mysql> select * from bank;   # 牛老师账号已增加1000，张志刚账号未改变
mysql> exit;  # 退出
# 再连入之后查询
mysql> select * from bank;  # 因为从未执行过commit。所以查到的结果与执行事务之前查到的结果一样。
上一篇 下一篇

猜你喜欢

热点阅读