MySQL8 常用命令之DDL、DML语句（创建、增删改）及约束

乐温

已于 2023-04-27 15:10:29 修改

阅读量1.2k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql

于 2021-03-05 23:40:38 首次发布

本文链接：https://blog.youkuaiyun.com/zhengrong9/article/details/114411369

mysql 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了SQL的基础知识，包括数据定义语言(DDL)和数据操纵语言(DML)的使用方法，涵盖了表的创建、数据的插入与更新、表结构的修改等内容，并深入探讨了各种约束的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DDL：（Data Definition Language）数据定义语言
DML：（Data Manipulation Language）数据操纵语言

一、表的创建（DDL）

1. 语法

create table 表名 (
	字段名1 数据类型，
	字段名2 数据类型，
	字段名3 数据类型
);

2. mysql中的常见数据类型

数据类型	含义
char	固定长度字符串（最长255），存储空间固定，与实际数据无关。使用不当可能会导致空间浪费。
varchar	可变长度字符串（最长255），会根据实际数据长度动态分配存储空间，更节省空间。
int	整型（最长11）
bigint	长整型
float	浮点型（单精度）
double	浮点型（双精度）
date	短日期类型
datetime	长日期类型
clob	字符大对象（Character Larger Object）最大可以存储4G的字符串，比如文章、说明
blob	二进制大对象（Binary Larger Object）专门用来存储图片、声音、视频等流媒体数据。插入数据时需要使用IO流。

3. 插入数据（DML）

3.1 插入一条数据：

	insert into 表名(字段1,字段2,字段3...) values(值1,值2,值3...);

3.2 插入多条数据：

	insert into 表名(字段1,字段2,字段3...) values(值1,值2,值3...),(值1,值2,值3...),()...;

注：

字段名和值要一一对应。
只写表名表示添加所有字段的数据，顺序要保持和建表时字段顺序一致。
数量和数据类型要对应。
insert语句一旦执行成功，必然会多一条记录，未指定值的字段默认为NULL。

mysql> insert into t_stu values(1,'zhangsan',21,'zhangsan@163.com','1999-9-9');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_stu(num,name,age,birth,email) values(2,'lisi',21,'1999-10-10','lisi@163.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_stu(num) values(3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_stu;
+------+----------+------+------------------+------------+
| num  | name     | age  | email            | birth      |
+------+----------+------+------------------+------------+
|    1 | zhangsan |   21 | zhangsan@163.com | 1999-09-09 |
|    2 | lisi     |   21 | lisi@163.com     | 1999-10-10 |
|    3 | NULL     | NULL | NULL             | NULL       |
+------+----------+------+------------------+------------+
3 rows in set (0.00 sec)

4. 插入日期

4.1 mysql 日期格式：

%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒

4.2 日期转换函数

str_to_date：将非正常日期字符串转化成日期类型，通常用于insert语句。

mysql> insert into user values(1,'zhangsan',str_to_date('01-01-1995','%d-%m-%Y'));
Query OK, 1 row affected (0.01 sec)

date_format：格式化日期，将日期转化为具有特定格式的字符串，通常用于select语句，设置展示的日期格式。

mysql> select id,name,date_format(birth,'%m/%d/%Y') birth from user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 01/01/1995 |
|    2 | lisi     | 03/03/1996 |
+------+----------+------------+
2 rows in set (0.00 sec)

4.3 date 和 datetime 的区别

mysql> insert into user values (1,'zhangsan','1999-09-09','2021-03-05 20:58:10');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(2,'lisi','1999-10-10',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1999-09-09 | 2021-03-05 20:58:10 |
|    2 | lisi     | 1999-10-10 | 2021-03-05 20:59:51 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)

注：now()可以获取当前系统时间，精确到秒。

4.4 将查询结果插入到表中

insert into 表1 select 字段 from 表2;

5. 快速创建表（复制表结构及数据）

create table 新表 as select * from 原表;

6. 修改表数据（DML）

update 表名 set 字段1=值1,字段2=值2,... where 条件；

注：

没有条件限制会导致所有数据全部更新。
如果在update时使用了子查询，需要当心，最好是先select一下子查询的结果，在进行update，以确保不会出错。

mysql> update user set name='jack',birth='2000-10-10' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1999-09-09 | 2021-03-05 20:58:10 |
|    2 | jack     | 2000-10-10 | 2021-03-05 20:59:51 |
+------+----------+------------+---------------------+
2 rows in set (0.01 sec)

# 练习：给所有的经理涨10%的工资，需要先知道经理的员工号
mysql> select name,salary from employees where empno in (
    -> select managerno from departments);
+--------+----------+
| name   | salary   |
+--------+----------+
| 王五   |  9100.00 |
| 李四   | 13900.00 |
| 程娟   | 14900.00 |
+--------+----------+
3 rows in set (0.00 sec)

mysql> update employees set salary=salary*1.1 where empno in (
    -> select managerno from departments);
Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0
mysql> select name,salary from employees where empno in (
    -> select managerno from departments);
+--------+----------+
| name   | salary   |
+--------+----------+
| 王五   | 10010.00 |
| 李四   | 15290.00 |
| 程娟   | 16390.00 |
+--------+----------+
3 rows in set (0.00 sec)

7. 删除表数据

7.1 delete （DML）

delete from 表名 where 条件;

注：

没有条件限制，整张表数据会全部删除！！！表依旧存在。
使用 delete 删除表数据，数据在硬盘上的真实存储空间不会被释放。
缺点：删除效率较低。
优点：支持事务回滚，删错可以恢复。

7.2 truncate （DDL）

truncate table 表名;

注：

不能删除单条数据。
物理删除，表被一次截断。效率较高，但是不支持回滚。
在使用之前，要仔细确认数据是否可以被删除，删除后不可恢复。

二、修改表结构

数据表在创建表之前就应该确认好表结构，一旦创建完成，最好不要修改表结构。实际开发中修改表结构会引发大麻烦。

1. 添加字段

alter table 表名 add 字段名 数据类型 约束;

新字段默认添加到数据表的末尾，可以使用 FIRST (设为第一列) 或 AFTER 字段名（设定在某个字段之后）来指定字段位置。

2. 修改字段

2.1 修改字段数据类型

alter table 表名 modify 字段名 数据类型;

2.2 修改字段名

alter table 表名 change 字段名 新字段名 数据类型;

3. 删除字段

alter table 表名 drop 字段名;

4. 修改表名

alter table 表名 rename to 新表名;

5.添加约束

alter table 表名称
	add contraint 自定义的约束名称 
	foreign key (当前表的字段名称) 
    references  其他关联表(字段名称);

三、表的删除

语法：

drop table 表名;	//如果表不存在会报错

drop table if exists 表名;//表存在才删除，不存在什么都不做。

四、约束（重点）

1. 什么是约束？

数据库是用于存储数据的容器，具体数据是存储在数据表中的，数据主要体现在数据表中的数据列上，通过数据列描述了具体的某个数据。
指定的数据列具体能怎么样存储数据，应该按照什么方式存储数据都是有一定的约束规则的，这样的约束规则就是数据表约束。约束关键字：constraint
在创建表的时候，可以给表中的字段添加一些约束来保证表中数据的完整性和有效性。

列级约束：哪个字段需要添加约束，直接将约束名称写到该字段后面即可。
表级约束：约束没有添加在列的后面，多个字段联和约束的时候使用。格式：约束名(字段1,字段2,...)

2. 约束的分类

约束名称	含义
default	默认约束，该字段不指定数据时，数据为默认值
not null	非空约束，该字段数据不能为NULL
unique	唯一约束，该字段的数据是唯一的，不能重复
primary key	主键约束，该字段数据唯一
foreign key	外键约束
check	检查约束，mysql不支持，oracle支持

2.1 `default` 默认值约束

数据INSERT操作时，如果该列不指定数据，就使用默认值插入。

mysql> create table vip(
    -> id int,
    -> name varchar(10),
    -> gender char(4) default '男'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into vip(id,name) values (1,'liming');
Query OK, 1 row affected (0.01 sec)
mysql> insert into vip(id,name,gender) values (2,'wangqiang','男');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip(id,name,gender) values (3,'liumei','女');
Query OK, 1 row affected (0.01 sec)

mysql> select * from vip;
+------+-----------+--------+
| id   | name      | gender |
+------+-----------+--------+
|    1 | liming    | 男     |	-- 在插入该条数据时，并没有指定性别
|    2 | wangqiang | 男     |
|    3 | liumei    | 女     |
+------+-----------+--------+
3 rows in set (0.01 sec)

2.2 `not null` 非空约束

非空约束的字段必须传值且不能传null。not null只有列级约束。

mysql> create table vip(
    -> id int,
    -> name varchar(10) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into vip values (1,'zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip(id) values (3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql> insert into vip values (3,null);
ERROR 1048 (23000): Column 'name' cannot be null

2.3 `unique` 唯一约束

唯一约束的字段不能重复，但是可以为NULL，NULL可以有多个。

mysql> create table vip(
    -> id int,
    -> name varchar(10) not null,
    -> email varchar(255) unique);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into vip values(1,'zhangsan','zhangsan@123.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip values(2,'lisi',null);
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip values(3,'wangwu','zhangsan@123.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan@123.com' for key 'vip.email'

多个字段联合唯一，将多个字段看成一个整体，全部一样视为相同，部分一样依旧可以插入。

mysql> create table vip(
    -> id int,
    -> name varchar(10) not null,
    -> email varchar(255),
    -> unique(name,email));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into vip values(1,'zhangsan','zs@123.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip values(2,'zhangsan','zs@sina.com');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip values(3,'zhangsan','zs@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zs@sina.com' for key 'vip.name'

2.4 `primary key` 主键约束（简称PK）

主键值是每行记录的唯一标识，一张表只能有一个主键。
任何一张表都应该有主键，没有主键，表无效！

mysql> create table vip(
    -> id int primary key,
    -> name varchar(10));
Query OK, 0 rows affected (0.02 sec)

主键的特征：not null + unique（主键值不能为空且不能重复）

mysql> insert into vip values(1,'zs');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip values(2,'ls');
Query OK, 1 row affected (0.01 sec)

mysql> insert into vip values(2,'ww');
ERROR 1062 (23000): Duplicate entry '2' for key 'vip.PRIMARY'
mysql> insert into vip(name) values('zl');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

主键可以使用列级约束添加，也可以使用表级约束添加。

mysql> create table vip(
    -> id int,
    -> name varchar(10),
    -> primary key(id));
Query OK, 0 rows affected (0.03 sec)

多个字段可以联合添加主键，称为复合主键，不建议使用。

mysql> create table vip(
    -> id int,
    -> name varchar(10),
    -> primary key(id,name));
Query OK, 0 rows affected (0.03 sec)

主键除了单一主键和复合主键以外，还可以分为自然主键和业务主键。
自然主键：主键值是一个自然数，与业务无关。
业务主键：主键值与业务紧密关联，例如用银行卡号做主键值。
在实际开发中，自然主键使用更多，因为主键只要做到不重复即可，无实际意义。使用业务主键，当业务发生变动时没可能会影响到主键值。
在mysql中可以使用auto_increment自动维护主键值，表示自增，从1开始，递增1。

2.5 `foreign key` 外键约束（简称FK）

为了避免数据冗余和空间浪费，可以通过添加外键来保证数据的有效性，减少垃圾数据的处理。

create table 表名(
	字段 数据类型,
	foreign key(外键字段名) references 引用表(引用字段名)
	);

注：

添加外键的表称为子表，被引用的表称为父表。
引用字段不一定是主键字段，但至少要有unique约束。
子表中外键约束的字段值只能是父表中引用字段的数据，父表中没有的不能插入。
创建表的顺序？先创建父表，再创建子表。
插入数据的顺序？先插入父表，再插入子表。
删除表和表数据的顺序？先删子表，再删父表。

-- 父表
mysql> create table t_class(
    -> cid int primary key,
    -> cname varchar(30)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 子表
mysql> create table t_stu(
    -> sid int primary key auto_increment,
    -> sname varchar(10),
    -> cid int, foreign key(cid) references t_class(cid)
    -> );
Query OK, 0 rows affected (0.02 sec)

-- 先向父表中插入数据
mysql> insert into t_class values(101,'python1班');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_class values(102,'python2班');
Query OK, 1 row affected (0.00 sec)

-- 再向子表插入数据
mysql> insert into t_stu values(1,'zhangsan',101);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t_stu values(2,'liming',102);
Query OK, 1 row affected (0.01 sec)
-- 父表中没有编号103的班级，子表插入数据时失败
mysql> insert into t_stu values(3,'sudaqiang',103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`power`.`t_stu`, CONSTRAINT `t_stu_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `t_class` (`cid`))