MySQL表结构修改，truncate，约束引入

最新推荐文章于 2024-05-09 22:38:32 发布

码农C风

最新推荐文章于 2024-05-09 22:38:32 发布

阅读量1.1k

点赞数 1

分类专栏：数据库养成文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/a23452/article/details/121411449

版权

数据库养成专栏收录该内容

20 篇文章 3 订阅

订阅专栏

Java养成计划----学习打卡第七十天

内容导航

约束 constraint
- 非空约束NOT NULL
- UNIQUE 唯一性约束

Java（打卡第七十天)

MySQL的DML语句，约束

回顾以下之前的内容

基本的SELECT

DISTINCT 去重，可以只修饰一个字段，也可以放在所有要修饰的字段之前，这样是将字段连接之后再去重，可以使用COUNT(DISTINCT(col))来查询某种字段的个数
连接查询SQL92使用较少，没有ON限制，那么查询的结果为笛卡尔积，那么就要加限制或者筛选条件来限制笛卡尔积的出现【就是一个路径的，如果没有限制，所有的路均可选，但是加了限制就选择了具体的路，但是底层的匹配还是笛卡尔积，只是查询出来的结果变化】
内连接是平等关系，匹配不上的结果就不会显示；外连接是由主次关系，会显示主表中的所有记录，没有匹配上的也会显示
union显示数据，就是上下的简单拼接，最好数据类型相等
limit就是输出的限制的，分页的就是LIMIT startindex，size 这里index是从0开始，和之前的

昨天已经分享了DML中的INSERT语句，对于date类型已经提到和字符串类型的转换，特别需要注意的是，日期的标准格式中年Y是大写，其余的都是小写，接下来继续分享剩下的内容

mysql> INSERT INTO
    -> t_person
    -> (name,birth)
    -> VALUES
    -> ('liuli','2002-07-26');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t_person;
+-------+------------+
| name  | birth      |
+-------+------------+
| Linda | 1990-09-01 |
| Wang  | 1988-01-12 |
| liuli | 2002-07-26 |
+-------+------------+
3 rows in set (0.00 sec)

昨天已经知道STR_TO_DATE 可以将字符串类型转换为日期类型，当字符串的格式为%Y-%m-%d的时候就可以省略str_to_date

DATE_FORMAT 日期格式化

str_to_date可以将字符串格式化为日期来存储，同样就有一个函数date_format将日期转化为特定格式来显示

使用的方式为

DATE_FROMAT(date,'日期格式');
比如%Y/%m/%d

所以该函数经常使用在select查询显示的时候，而str_to_date经常使用在insert into中

mysql> SELECT
    -> name '姓名',DATE_FORMAT(birth,'%Y/%m/%d') AS '出生日期'
    -> FROM
    -> t_person;
+-------+------------+
| 姓名  | 出生日期   |
+-------+------------+
| Linda | 1990/09/01 |
| Wang  | 1988/01/12 |
| liuli | 2002/07/26 |
+-------+------------+
3 rows in set (0.01 sec)

SQL语句进行默认格式化，并且会将日期转化为varchar格式，同时是MySQL的标准日期格式%Y-%m-%d

date 和datetime【长日期】

date是短日期类型，只包括年月日，datetime是长日期类型，包括年月日时分秒

长日期的默认格式为： %Y-%m-%d %h:%i:%s

now() 获取当前时间的函数

now函数和java中的获取时间函数一样，可以获取当前时间，mysql中更智能一些，会自动获取当前日期的长日期类型，就是年月日时分秒

获取的日期格式就是标准的格式

所以创建的时候

ysql> INSERT INTO
    -> t_person
    -> (name,birth,create_time)
    -> VALUES
    -> ('Linda','1990-09-01',NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t_person;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 14:50:26 |
+-------+------------+---------------------+
1 row in set (0.00 sec)

可以看到日期非常精确，格式就是%Y-%m-%d %h:%i:%s

DML – UPDATE 修改

update就是对表中的数据进行修改的，修改的一个重要的关键字就是set，和insert中的value一样重要

update的标准格式为

UPDATE table_name SET col1 = value1,col2 = value2……  WHERE ……

注意：如果没有限制条件，那么所有的数据都会更新

mysql> SELECT * FROM t_person;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 14:50:26 |
| Liu   | 2004-09-30 | 2021-11-18 14:57:40 |
+-------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> UPDATE
    -> t_person
    -> SET
    -> name = '刘',birth = '1980-03-08'
    -> WHERE
    -> name = 'Liu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t_person;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 14:50:26 |
| 刘    | 1980-03-08 | 2021-11-18 14:57:40 |
+-------+------------+---------------------+
2 rows in set (0.00 sec)

如果这里不加WHERE条件，那么系统就不会筛选，那么就会将所有的row的数据全部修改

ysql> UPDATE
    -> t_person
    -> SET
    -> create_time = NOW();
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM t_person;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 15:01:55 |
| 刘    | 1980-03-08 | 2021-11-18 15:01:55 |
+-------+------------+---------------------+
2 rows in set (0.01 sec)

Rows matched: 2 Changed: 2 表明匹配到的数据有2条，2条都修改成功了，这就是不加过滤的结果

DML - DELETE 删除数据

上面已经分享了DML中的增加数据是insert，修改数据是update，那么删除数据是什么呢？
删除数据是DELETE

删除delete是按行处理的，就是直接删除匹配到的所有的行row，如果要对某个数据处理，那么就还是UPDATE

其语法格式为

DELETE FROM table_name WHERE ……

和上面的UPDATE一样，如果不过滤，那么会删除一张表中的所有数据

ysql> SELECT * FROM t_person;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 15:01:55 |
| 刘    | 1980-03-08 | 2021-11-18 15:01:55 |
+-------+------------+---------------------+
2 rows in set (0.01 sec)

mysql> DELETE
    -> FROM
    -> t_person
    -> WHERE
    -> name = '刘';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t_person;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 15:01:55 |
+-------+------------+---------------------+
1 row in set (0.00 sec)

但是需要注意的是DELECT FROM table_name 和 DROP TABLE IF EXISTS table_name不一样，前者是空表，后者空表都不是，没有了表的结构

INSERT插入多条记录

insert可以一行一行插入，当然也可以多行插入，多行插入的格式

INSERT INTO table_name (col……) VALUES(v1,……),(v2……),(v3……)……;

注意：这里与表结构的创建不同，这里不需要外面再有括号，因为这里每一对值都有括号

mysql> INSERT INTO
    -> t_person
    -> (name,birth,create_time)
    -> VALUES
    -> ('Wang','1998-09-23',NOW()),
    ->  ('LIU','2009-08-23',NOW());
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 15:01:55 |
| Wang  | 1998-09-23 | 2021-11-18 19:30:51 |
| LIU   | 2009-08-23 | 2021-11-18 19:30:51 |
+-------+------------+---------------------+
说明这里数据的创建是同时进行的，因为时间上没有差异

表的复制，快速创建表 [CREATE SELECT *]

使用CREATE和SELECT结合就可以快速创建复制表了，格式是

CREATE TABLE table_temp AS SELECT * FROM table_name;

其实就是将要复制的表的数据全部搜索出来放到table_temp中，创建表还是之前的格式

mysql> CREATE TABLE temp AS SELECT * FROM t_person;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM temp;
+-------+------------+---------------------+
| name  | birth      | create_time         |
+-------+------------+---------------------+
| Linda | 1990-09-01 | 2021-11-18 15:01:55 |
| Wang  | 1998-09-23 | 2021-11-18 19:30:51 |
| LIU   | 2009-08-23 | 2021-11-18 19:30:51 |
+-------+------------+---------------------+
3 rows in set (0.00 sec)

将一个查询结果当作一张表新建

所以其实就是之前的FROM的子查询所得到得临时表相似得效果，但是这里是将其显化

所以这里得真正得标准格式

CREATE TABLE table_name SELECT 语句

这里可以实验下只有其中的两列

mysql> CREATE TABLE temp
    -> SELECT name,birth FROM t_person;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM temp;
+-------+------------+
| name  | birth      |
+-------+------------+
| Linda | 1990-09-01 |
| Wang  | 1998-09-23 |
| LIU   | 2009-08-23 |
+-------+------------+
3 rows in set (0.00 sec)

简单来说，就是将临时表【视图】显化的结果

INSERT也可以插入SELECT视图

既然创建表可以直接使用SELECT将视图直接复制，那么当然就可以直接将INSERT INTO来将视图插入，

INSERT INTO table_name SELECT 语句

这里和上面不同的是这里是没有AS的,创建表有AS，插入表没有AS

mysql> INSERT INTO temp
    -> SELECT * FROM temp WHERE name = 'Linda';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

一次性删除所有的数据 truncate

之前分享DELETE语句和UPDATE语句时，当时分析一定要加上WHERE过滤条件，不然就是对整张表的数据的操作，比如删除就是对整行数据的操作，truncate–截断

但是DELETE FROM table_name 删除速率较慢

DELETE 删除的原理，给一张表，DELETE就是一个擦，一个一个删除表格中的数据，但是表格还在，也就是表格还在，数据没有了，这样删除的好处是可以反悔，可以使用事务回滚rollback来恢复
表中的数据被删除了，但是这个数据再硬盘上的空间没有被释放

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM temp;
Query OK, 4 rows affected (0.01 sec)

mysql> SELECT * FROM temp;
Empty set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM temp;
+-------+------------+
| name  | birth      |
+-------+------------+
| Linda | 1990-09-01 |
| Wang  | 1998-09-23 |
| LIU   | 2009-08-23 |
| Linda | 1990-09-01 |
+-------+------------+
4 rows in set (0.00 sec)

建立事务后，就可以使用事务回滚来恢复删除前的时候，所以DELETE FROM特点就是效率低，但是可以反悔，可以事务回滚

TRUNCATE是物理删除，可以一次性删除所有的数据，删除的效率较高，表被一次截断，结构没有了，物理删除，这种删除的缺点是不能够反悔，不支持事务回滚

TRUNCATE TABLE table_name;

所以重要的数据还是需要备份

mysql> TRUNCATE TABLE temp;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM temp;
Empty set (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM temp;
Empty set (0.00 sec)

TRUNCATE是属于截断表，改变的是结构，是DDL语句，非常适合删除大数据的表，效率很高 -------------删除只需要1s，但是TRUNCATE删除之前一定要询问客户是否确定删除，并警告删除后不可恢复

比如公司一个上一条记录的表，删除最好还是使用TRUNCATE，使用DELETE FROM，就会花费很多很多时间

删除表DROP TABLE表都没了，结构也没有

对表结构的增删改ALTER

对表结构的修改是DDL语句，比如删除字段，插入字段，修改一个字段

在实际的开发中，需求一旦确定之后，表一旦设计好后，很少的进行表的结构的修改，因为开发进行的时候，一旦进行表结构的修改，成本很高，java代码会进行大量的修改
修改表结构的操作很少

alter之后补充的时候再分享

后期当创建的语言过于复杂的时候，可以使用xxxx.sql脚本文件来执行SQL语句，当使用SOURCE 路径来执行的时候，脚本文件中的所有数据都会被执行

约束 constraint

在创建表的时候，可以给表中的字段加上一些约束，来保证这个表中数据的完整性，有效性

约束的作用就是保证表中数据的完整性，有效性

这里在之前workbench中创建表的时候就有这种选项

约束的分类
- 非空约束 NOT NULL
- 唯一性约束 unique
- 主键约束 primary key PK
- 外键约束 foreign key FK
- 检查约束 check

非空约束NOT NULL

非空约束not null constraint的字段是不能为NULL的

修饰的方式就是

col 数据类型  NOT NULL,

这里来看以下效果

mysql> CREATE TABLE emp(
    -> name VARCHAR(25) DEFAULT 'Linda' NOT NULL,
    -> sex  CHAR(1) NOT NULL,
    -> birth DATE
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO emp
    -> VALUES
    -> ('Wang','女','1997-07-24');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM emp;
+------+-----+------------+
| name | sex | birth      |
+------+-----+------------+
| Wang | 女  | 1997-07-24 |
+------+-----+------------+
1 row in set (0.01 sec)

mysql> INSERT INTO emp
    -> (sex,birth)
    -> VALUES
    -> ('男','2013-01-13');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM emp;
+-------+-----+------------+
| name  | sex | birth      |
+-------+-----+------------+
| Wang  | 女  | 1997-07-24 |
| Linda | 男  | 2013-01-13 |
+-------+-----+------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO emp
    -> (name,birth)
    -> VALUE
    -> ('LI','1098-09-13');
ERROR 1364 (HY000): Field 'sex' doesn't have a default value

ERROR 1364 (HY000): Field 'sex' doesn't have a default value

这里前两个字段都使用了非空约束NOT NULL，也就是数据不能为空，但是name设置了DEFALUT值，sex没有设置，所以name可以不填，但是sex不可以为空

UNIQUE 唯一性约束

唯一性约束的字段不能重复，但是可以为空

基本的格式和上面相似

col_name 数据类型  UNIQUE,

这里也实现一下

mysql>  CREATE TABLE t_person(
    -> name VARCHAR(45) NOT NULL,
    -> sex CHAR(1),
    -> birth DATE,
    -> enterno INT  UNIQUE
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO
    -> t_person
    -> VALUES
    -> ('LIU','男','1981-09-23',1);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t_person;
+------+------+------------+---------+
| name | sex  | birth      | enterno |
+------+------+------------+---------+
| LIU  | 男   | 1981-09-23 |       1 |
+------+------+------------+---------+
1 row in set (0.00 sec)

mysql> INSERT INTO
    -> t_person
    -> VALUES
    -> ('WANG','女','2001-01-01',1);
ERROR 1062 (23000): Duplicate entry '1' for key 't_person.enterno'

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘t_person.enterno’

重复的输入1给字段enterno ，这就是因为enterno使用了UNIQUE约束

但是UNIQUE 可以都为空，空不是值

码农C风

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL表结构修改，truncate，约束引入

Java养成计划----学习打卡第七十天内容导航DATE_FORMAT 日期格式化date 和datetime【长日期】now() 获取当前时间的函数DML -- UPDATE 修改DML - DELETE 删除数据INSERT插入多条记录表的复制，快速创建表 [CREATE SELECT *]INSERT也可以插入SELECT视图一次性删除所有的数据 truncate对表结构的增删改ALTER约束 constraint非空约束NOT NULLUNIQUE 唯一性约束Java（打卡
复制链接

扫一扫