MySQL8 常用命令之DDL、DML陳述句、約束-有解無憂

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 條件；

注：沒有條件限制會導致所有資料全部更新，

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)

6. 洗掉表資料

6.1 delete （DML）

delete from 表名 where 條件;

注：

沒有條件限制，整張表資料會全部洗掉！！！表依舊存在，
使用 delete 洗掉表資料，資料在硬碟上的真實存盤空間不會被釋放，
缺點：洗掉效率較低，
優點：支持事務回滾，刪錯可以恢復，

6.2 truncate （DDL）

truncate table 表名;

注：

不能洗掉單條資料，
物理洗掉，表被一次截斷，效率較高，但是不支持回滾，
在使用之前，要仔細確認資料是否可以被洗掉，洗掉后不可恢復，

二、修改表結構

三、表的洗掉

語法：

drop table 表名;	//如果表不存在會報錯

drop table if exists 表名;//表存在才洗掉，不存在什么都不做，

四、約束（重點）

1. 什么是約束？

約束關鍵字：constraint
在創建表的時候，可以給表中的欄位添加一些約束來保證表中資料的完整性和有效性，

列級約束：哪個欄位需要添加約束，直接將約束名稱寫到該欄位后面即可，
表級約束：約束沒有添加在列的后面，多個欄位聯和約束的時候使用，格式：約束名(欄位1,欄位2,...)

2. 約束的分類

約束名稱	含義
not null	非空約束，該欄位資料不能為NULL
unique	唯一約束，該欄位的資料是唯一的，不能重復
primary key	主鍵約束，該欄位資料唯一
foreign key	外鍵約束
check	檢查約束，mysql不支持，oracle支持

2.1 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.2 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.3 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.4 foreign key 外鍵約束（簡稱FK）

為了避免資料冗余和空間浪費，可以通過添加外鍵來保證資料的有效性，

create table 表名(
	欄位 資料型別 foreign key(外鍵欄位名) references 參考表(參考欄位名)
	);

注：

添加外鍵的表稱為子表，被參考的表稱為父表，
創建表的順序？先創建父表，再創建子表，
插入資料的順序？先插入父表，再插入子表，
洗掉表和表資料的順序？先刪子表，再刪父表，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/267105.html

標籤：其他

上一篇：資料結構

下一篇：Mybatis的基礎學習