Java养成计划----学习打卡第七十天
内容导航
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 可以都为空,空不是值