MySQL—DDL、DCL、DML

kiround

已于 2022-10-28 17:16:28 修改

阅读量1.7k

点赞数 1

文章标签： mysql 数据库 sql

于 2022-10-28 17:13:29 首次发布

本文链接：https://blog.csdn.net/kiround/article/details/127574867

版权

前言

DDL（data definition language）数据定义语言，用户定义和管理sql数据库中所有对象的语言。
DML（data manipulation language）数据操作语言。
DCL（date control language）数据库控制功能。

DDL：

创建和管理数据库

1.创建数据库

方式1：创建数据库

CREATE DATABASE 数据库名;

方式2：创建数据库并指定字符集

CREATE DATABASE 数据库名 
CHARACTER SET 字符集;

方式3：判断数据库是否已经存在，不存在则创建数据库（推荐）

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。

注意：
DATABASE 不能改名。一些可视化工具可以改名，但它是建新库，把所有表复制到新库，再删旧库完成的。

2.使用数据库

查看当前所有的数据库：

SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库：

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下所有的表：

SHOW TABLES FROM 数据库名;

查看数据库的创建信息：

  SHOW CREATE DATABASE 数据库名;

或者：

  SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

注意：
要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。

3.修改数据库

更改数据库字符集：

ALTER DATABASE 数据库名   
 CHARACTER SET 字符集;   #比如：gbk、utf8等

4.删除数据库

方式1：删除指定的数据库

DROP DATABASE 数据库名;

方式2：删除指定的数据库（推荐）

DROP DATABASE IF EXISTS 数据库名;

创建表

1.创建方式1

前提条件：
- 具备CREATE TABLE权限
- 存储空间
语法格式：

CREATE TABLE [IF NOT EXISTS] 表名( 
    字段1, 数据类型 [约束条件] [默认值], 
    字段2, 数据类型 [约束条件] [默认值], 
    字段3, 数据类型 [约束条件] [默认值], 
    ……[表约束条件] 
);

必须指定：
- 表名（TABLE）
- 列名(或字段名)，数据类型，长度
可选指定：
- 约束条件
- 默认值
Tip：
- IF NOT EXISTS 关键字表示：
  - 如果当前数据库中不存在要创建的数据表，则创建数据表；
  - 如果当前数据库中已经存在要创建的数据表，则忽略建表语句，不再创建数据表。

2.创建方式2

使用 AS subquery 选项，将创建表和插入数据结合起来
指定的列和子查询中的列要一一对应
通过列名和默认值定义列
格式：

CREATE TABLE emp1 
AS 
SELECT * 
FROM employees;

3.查看数据表结构

MySQL支持使用 DESCRIBE/DESC 语句查看数据表结构，也支持使用 SHOW CREATE TABLE 语句查看数据表结构，
使用SHOW CREATE TABLE语句不仅可以查看表创建时的详细语句，还可以查看存储引擎和字符编码

语法格式：

SHOW CREATE TABLE 表名\G

修改表

修改数据库中已经存在的数据表的结构。

使用 ALTER TABLE 语句：

向已有的表中添加列

语法：

ALTER TABLE 表名 
ADD [COLUMN] 字段名 字段类型 [FIRST|AFTER 字段名];

修改现有表中的列
可以修改列的数据类型，长度、默认值和位置

语法：

ALTER TABLE 表名 
MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值][FIRST|AFTER 字段名 2];

删除现有表中的列

语法：

ALTER TABLE 表名 
DROP [COLUMN] 字段名

重命名现有表中的列

语法：

ALTER TABLE 表名 
CHANGE [column] 列名 新列名 新数据类型;

重命名表

语法：

方式一：使用RENAME

RENAME TABLE emp 
TO myemp;

方式二：

ALTER table dept 
RENAME [TO] detail_dept; （[TO]可以省略）

删除表：

删除：
在MySQL中，当一张数据表没有与其他任何数据表形成关联关系时，可以将当前数据表直接删除。

数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除

语法：

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

IF EXISTS ：
如果当前数据库中存在相应的数据表，则删除数据表；
如果当前数据库中不存在相应的数据表，则忽略删除语句，不执行删除数据表的操作。

注意：DROP TABLE 语句不能回滚

清空表

TRUNCATE TABLE：

删除表中所有的数据
释放表的存储空间
语法：

TRUNCATE TABLE 数据表;

TRUNCATE TABLE与DELETE FROM：
- 相同：
  TRUNCATE TABLE与DELETE FROM都可以删除所有数据，但保留表结构
- 不同：
  TRUNCATE语句不能回滚，
  DELETE 语句删除数据，可以回滚（默认不可以）

COMMIT和ROLLBACK（DCL）

COMMIT：

提交数据，一旦使用了COMMIT，数据便永久储存在数据库中了，不可回滚

ROLLBACK：

回滚数据，使用了ROLLBACK后，将会回滚到上一次COMMIT之后

数据处理（DML）

一.插入数据

1.VALUES的方式添加

使用这种语法一次只能向表中插入一条数据

为表的所有字段按默认顺序插入数据：

INSERT INTO 表名 
VALUES (value1,value2,....);

Tip：值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同

为表的指定字段插入数据：

 INSERT INTO 表名(column1 [, column2, …, columnn]) 
 VALUES (value1 [,value2, …, valuen]);

Tip：
只向部分字段中插入值，而其他字段的值为表定义时的默认值
VALUES中要插入的value1,…valuen需要与column1,…columnn列一一对应。如果类型不同，将无法插入，并且MySQL会产生错误

同时插入多条记录：

INSERT INTO table_name 
VALUES 
(value1 [,value2, …, valuen]), 
(value1 [,value2, …, valuen]), 
……
(value1 [,value2, …, valuen]);

Tip：

用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：
Records：插入的记录条数。
Duplicates：插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
Warnings：有问题的数据值，例如发生数据类型转换

MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时推荐使用单条INSERT语句
VALUES 也可以写成 VALUE ，但是VALUES是标准写法。
字符和日期型数据应包含在单引号中。

2.将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行

语法：

INSERT INTO 目标表名 
(tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT 
(src_column1 [, src_column2, …, src_columnn]) 
FROM 源表名 
[WHERE condition]

Tip：
不必书写 VALUES 子句。
子查询中的值列表应与 INSERT 子句中的列名对应

二.更新数据

1.使用 UPDATE 语句更新数据

语法：

UPDATE table_name 
SET column1=value1, column2=value2, … , column=valuen 
[WHERE condition]

Tip：
可以一次更新多条数据。
如果需要回滚数据，需要保证在DML前，进行设置：SET AUTOCOMMIT = FALSE;

2.使用 WHERE 子句指定需要更新的数据

语法：

UPDATE employees 
SET department_id = 70 
WHERE employee_id = 113;

Tip：若省略WHERE语句，则所有数据都将被更新

3.更新中的数据完整性错误

当有如下错误时，表示：不存在 SET 后的数据
在这里插入图片描述

三.删除数据

1.使用 DELETE 语句从表中删除数据

语法：

DELETE FROM table_name
[WHERE <condition>];

Tip：

table_name 指定要执行删除操作的表
WHERE 为可选参数，指定删除条件
如果没有WHERE子句，DELETE语句将删除表中的所有记录。

2.删除中的数据完整性错误

当有如下错误时，表示：
You cannot delete a row that contains a primary key that is used as a foreign key in another table.
——不能删除包含在另一个表中用作外键的主键的行。
在这里插入图片描述

DDL与DML的说明

DDL：一旦操作，不能回滚
DML：一旦操作，不能回滚。但在执行前，执行了：SET autocommit = FALSE，则执行的DML操作可以回滚