MySQL之DDL&DCL&DML

Data Definition Language

1. 库定义

1.1 创建库

• 语法：CREATE DATABASE awei CHARSET utf8mb4;
• 规范
  • 库名：小写，业务有关，非数字开头，库名不能太长，不能使用保留字符串
  • 必须指定字符集

1.2 查库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| awei               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show create database awei;
+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| awei     | CREATE DATABASE `awei` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)

1.3 修改库

mysql> alter database awei charset utf8mb4;
Query OK, 1 row affected (0.00 sec)

1.4 删除库

危险，生产环境中，除了管理员，任何人没有删除权限

mysql> drop database awei;
Query OK, 0 rows affected (0.00 sec)

2. 表定义

2.1 创建表

• 规范
  • 表名：小写字母，非数字开头，表名与业务有关，表名不能太长，不能使用关键字
  • 必须设置存储引擎和字符集
  • 数据类型：合适、简短、足够
  • 必须有主键
  • 每个列尽量设置非空索引，不知道填啥就设定默认值
  • 每个列要有注释comment

2.2 查询表

mysql> show tables;
+----------------+
| Tables_in_awei |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)

mysql> desc user;
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
| Field    | Type                                                  | Null | Key | Default   | Extra          |
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
| id       | int(11)                                               | NO   | PRI | NULL      | auto_increment |
| name     | varchar(64)                                           | NO   |     | NULL      |                |
| age      | tinyint(3) unsigned                                   | NO   |     | 18        |                |
| gender   | char(1)                                               | NO   |     | F         |                |
| cometime | datetime                                              | NO   |     | NULL      |                |
| province | enum('北京市','上海市','广东省','湖南省')             | NO   |     | 广东省    |                |
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
6 rows in set (0.03 sec)

mysql> show create table user;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL DEFAULT '18',
  `gender` char(1) NOT NULL DEFAULT 'F',
  `cometime` datetime NOT NULL,
  `province` enum('北京市','上海市','广东省','湖南省') NOT NULL DEFAULT '广东省',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4                |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

2.3 修改表

• 添加手机号列

mysql> alter table user add column tel char(11) not null unique key comment '手机号';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
| Field    | Type                                                  | Null | Key | Default   | Extra          |
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
| id       | int(11)                                               | NO   | PRI | NULL      | auto_increment |
| name     | varchar(64)                                           | NO   |     | NULL      |                |
| age      | tinyint(3) unsigned                                   | NO   |     | 18        |                |
| gender   | char(1)                                               | NO   |     | F         |                |
| cometime | datetime                                              | NO   |     | NULL      |                |
| province | enum('北京市','上海市','广东省','湖南省')             | NO   |     | 广东省    |                |
| tel      | char(11)                                              | NO   | UNI | NULL      |                |
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
7 rows in set (0.00 sec)

• 将手机列的数据类型改成bigint

mysql> alter table user modify tel bigint not null unique key comment '手机号';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> desc user;
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
| Field    | Type                                                  | Null | Key | Default   | Extra          |
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
| id       | int(11)                                               | NO   | PRI | NULL      | auto_increment |
| name     | varchar(64)                                           | NO   |     | NULL      |                |
| age      | tinyint(3) unsigned                                   | NO   |     | 18        |                |
| gender   | char(1)                                               | NO   |     | F         |                |
| cometime | datetime                                              | NO   |     | NULL      |                |
| province | enum('北京市','上海市','广东省','湖南省')             | NO   |     | 广东省    |                |
| tel      | bigint(20)                                            | NO   | UNI | NULL      |                |
+----------+-------------------------------------------------------+------+-----+-----------+----------------+
7 rows in set (0.00 sec)

• 删除手机号列

mysql> alter table user drop tel;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

2.4 删除表

mysql> drop table user;

---

Data Control Language

MySQL之基础管理

---

Data Manager Language

1. 作用

对表中的数据行进行操作

2. insert

添加数据

-- 规范用法
DESC user;
INSERT INTO
user(id, sname, age, gender, addr, cometime, telnum)
VALUES(1, '张三', 18, 1, '北京市', '2020-06-13 16:37:00', 110);

-- 简约方法
INSERT INTO
user
VALUES(2, '李四', 20, 1, '北京市', '2020-06-13 16:37:00', 119);

-- 部分录入数据（使用默认值）
INSERT INTO
user(snametelnum)
VALUES('王五', 120);

-- 批量录入数据
INSERT INTO
user(snametelnum)
VALUES('张三', 110), VALUES('李四', 119), VALUES('王五', 120);

3. update

更新数据

-- 必须明确指定要改哪一行，一般update语句都有where条件
UPDATE student SET sname='阿玮' WHERE id = 1;

4. DELETE

删除数据

-- 必须明确指定要删哪一行，一般update语句都有where条件
DELETE from student WHERE id = 1;

4.1 伪删除

-- 修改表结构
ALTER TABLE user ADD COLUMN state TINYINT NOT NULL DEFAULT 1;

-- 删除数据改为update
UPDATE user SET state=0 where id=1;

-- 查询语句修改为
SELECT * FROM user WHERE state=1;

4.2 三种删除语句的区别

-- 以下三条命令都能删除全表数据

delete from user;
-- 逻辑上逐行删除，当数据行较多的时候操作很慢
-- 并没有真正从磁盘上删除，只是在存储层打标记，磁盘空间不立即释放，HWM高水平线不会降低
-- delete属于DML操作，只需要通过反转日志（binlog）就能恢复

drop table user;
-- 将表结构（元数据）和数据行物理层次删除
-- drop属于DDL操作，需要通过备份+日志恢复

truncate table user;
-- 清空表段中的所有数据页，物理层次删除全表数据，磁盘空间会立即释放，HWM高水平线会降低
-- truncate属于DDL操作，需要通过备份+日志恢复