DDL
1、庫的管理
建立、修改、删除
2、表的管理
建立、修改、删除
建立：create
修改：alter
删除：drop
3、資料類型
數值型、字元型、日期型
4、常見的限制
一、庫的管理
1、庫的建立
文法:
1.建立Books
2.更改庫的字元集
3.庫的删除
二、表的管理
1.表的建立 ★
CREATE TABLE 表名(
列名 列的類型【(長度)限制】,
列名 列的類型【(長度)限制】,
...
);
案例 ：建立表Book
CREATE TABLE IF NOT EXISTS book(
id INT, #編号
bname VARCHAR(20), #圖書
price DOUBLE, #價格
authorId INT, #作者編号
publishDate DATETIME, #出版日期
authorName VARCHAR(20)
);
新增内容
INSERT INTO book VALUES
(1,'《紅樓夢》',59.8,1,NULL,'曹雪芹')
(2,'《三國演義》',59.8,2,NULL,'羅貫中'),
(3,'《水浒傳》',69.5,3,NULL,'施耐庵 '),
(4,'《西遊記》',85,4,NULL,'吳承恩')
檢視表結構
DESC book
2.表的修改
文法：
①修改列名
②修改列的類型
③添加新列
④删除列
⑤修改表名
3.表的删除
通用的寫法
DROP DATABASE IF EXISTS 舊庫名;
CREATE DATABASE 新庫名;
DROP TABLE IF EXISTS 舊表名；
CREATE TABLE 新表名();
4.表的複制
1.僅僅複制表的結構
2.複制表的結構以及資料
CREATE TABLE copy_book
SELECT * FROM book;
3.隻複制部分資料
CREATE TABLE copy_part
SELECT id,bname,authorName FROM book;
三、資料類型
數值型:
整數
小數
定點型
浮點型
字元型：
較短的文本：char、varchar
較長的文本: text、blob（較長的二進制資料）
日期型：
整型
分類：
tinyint
、
smallint
、
mediumint
、
int/integer
、
bigint
特點：
①如果不設定無符号還是有符号，預設是有符号，如果想設定無符号，需要添加unsigned
②如果插入的數值超出了整型的範圍，會報 out of range異常，并且插入臨界值
③如果不設定長度，會有預設的長度
④長度代表了顯示的最大寬度，如果顯示不夠，0在左邊填充，搭配zerofill
如何設定無符号和有符号
DROP TABLE tab_int;
CREATE TABLE tab_int(
t1 INT,
t2 INT UNSIGNED
);
插入的數值超出了整型的範圍，會報 out of range異常
INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
使用zerofill
CREATE TABLE tab_int(
t1 INT(6) ZEROFILL,
t2 INT(6) ZEROFILL
);
長度限制，使用zerofill會在最左邊填充0
INSERT INTO tab_int VALUES(43,43),(431121,430922)
SELECT * FROM tab_int;--查詢
小數
1.浮點型
float (M,D)
double(M,D)
2.定點型
dec(M,D)
decimal(M,D)
特點：
①M和D
M:整數部位+小數部位
D:小數部位
如果超出範圍，則插入臨界值
②M和D都可以省略
但是如果是decimal,則M預設為10，D預設為0
如果是double和float，則會根據插入的數值精度來決定精度
③定點型的精度要求較高，如果要求插入數值的精度較高，如：貨币運算等則考慮使用
測試M和D
CREATE TABLE tab_float(
f1 FLOAT(6,2),
f2 DOUBLE(6,2),
f3 DECIMAL(6,2)
);
DROP TABLE tab_float; --清表
CREATE TABLE tab_float(
f1 FLOAT,
f2 DOUBLE,
f3 DECIMAL
);
精度取整
四舍五入
檢視表結構
DESC tab_float
超出範圍，插入臨界值
查詢
SELECT * FROM tab_float
原則:
所選擇的類型越簡單越好，能儲存數值的類型越小越好
三、字元型
字元型
char 代表固定長度的字元
varchar 代表可變長度的字元
其他：
binary 和varbinary 用于儲存較短的二進制
enum 用于儲存枚舉
set 用于儲存集合
char和varchar對比
| 字段類型 | 寫法 | M的意思 | 特點 | 空間的耗費 | 效率 |
|---|---|---|---|---|---|
| char | char(M) | 最大的字元數，可以省略，預設為1 | 固定長度的字元 | 比較耗費 | 高 |
| varchar | varchar(M) | 最大的字元數，不可以省略 | 可變長度的字元 | 比較節省 | 低 |
枚舉
CREATE TABLE tab_enum(
e1 ENUM('a','b','c')
);
示例：
INSERT INTO tab_enum VALUES('a');
INSERT INTO tab_enum VALUES('b');
INSERT INTO tab_enum VALUES('c');
INSERT INTO tab_enum VALUES('e');
INSERT INTO tab_enum VALUES('A');
--查詢
SELECT * FROM tab_enum;
set類型
CREATE TABLE tab_set(
s1 SET('a','b','c')
);
示例：
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,b,c');
--查詢
SELECT * FROM tab_set;
四、日期型
分類：
date隻儲存日期
time 隻儲存時間
year 隻儲存年
datetime 儲存日期+時間
timestamp 儲存日期+時間
特點：
| 字段類型 | 位元組 | 範圍 | 時區受的影響 |
|---|---|---|---|
| datetime | 8 | 1000-9999 | 不受 |
| timestamp | 4 | 1970-2038 | 受 |
示例：
CREATE TABLE tab_date(
t1 DATETIME,
t2 TIMESTAMP
);
INSERT INTO tab_date VALUES(NOW(),NOW());
--查詢
SELECT * FROM tab_date;
--檢視目前時區
SHOW VARIABLES LIKE 'time_zone';
--設定時區
SET time_zone='+9:00';
四、常見的限制
含義：一種限制，用于限制表中的資料，為了保證表中的資料準确和可靠性
分類：六大限制
not null:非空，用于保證該字段的值不能為空
比如：姓名、學号等
default：預設，用于保證該字段有預設值
比如:性别
primary key:主鍵，用于保證該字段的值具有唯一性，并且非空
比如：學号、員工編碼等
unique:唯一，用于保證該字段的值具有唯一性，可以為空
比如：座位号
check:檢查限制【mysql中不支援】
foreign key:外鍵，用于限制兩個表的關系，用于保證該字段值必須來自于主表的關聯列的值
在從表中添加外檢限制，用于引用主表中的某列的值
比如學生表的專業編号，員工表的部門編号，員工表的工種編号
添加限制的時機：
1.建立表時
2.修改表時
限制的添加分類：
列級限制：
六大限制文法上都支援，但外鍵限制沒有效果
表級限制：
除了非空、預設、其他都支援
create table 表名（
字段名 字段類型 限制
）;
主鍵和唯一的對比
| 保證唯一性 | 是否允許為空 | 一個表中可以有多少個 | 是否允許組合 | |
|---|---|---|---|---|
| 主鍵 | √ | × | 最多有1個 | √，但不推薦 |
| 唯一 | √ | √ | 可以有多個 | √，但不推薦 |
外鍵：
1、要求在從表設定外鍵關系
2、從表的外鍵列的類型和主表的關聯列的類型要求一緻或相容，名稱無要求
3、主表的關聯列必須是一個key(一般是主鍵、唯一)
4、插入資料庫時，先插入主表，再插入從表
删除資料時，先删除從表，再删除主表
CREATE TABLE 表名（
字段名 字段類型 列級限制，
字段名 字段類型 ，
表級限制
）;
一、建立表時添加限制
1.添加列級限制
文法：
直接在字段名和類型後面追加限制類型即可
隻支援：預設、非空、主鍵、唯一
CREATE TABLE shudent(
id INT PRIMARY KEY,#主鍵
stuName VARCHAR(20) NOT NULL,
gender CHAR(1) CHECK(gender='男' OR gender='女' ),#檢查
seat INT UNIQUE,#唯一
age INT DEFAULT 18, #預設限制
majorId INT REFERENCES major(id) #外鍵
);
CREATE TABLE major(
id INT PRIMARY KEY ,
majorName VARCHAR(20)
);
DESC shuinfo;
SHOW INDEX FROM shuinfo;
ALTER TABLE shudent RENAME TO shuinfo;
2.添加表級限制
文法：
在各個字段的最下面
【constraint 限制名】 限制類型（字段名）
清表
方式一：建立表添加表級限制
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
CONSTRAINT pk PRIMARY KEY (id),#主鍵
CONSTRAINT uq UNIQUE(seat)#唯一鍵
CONSISTENT fk_stuinfo_ major FOREIGN KEY(majorId) REFERENCES major(id) #外鍵
);
方式二：建立表添加表級限制
CREATE TABLE stuinfo(
id INT,
stuname VARCHAR(20),
gender CHAR(1),
seat INT,
age INT,
majorId INT,
PRIMARY KEY (id),#主鍵
UNIQUE(seat),#唯一鍵
FOREIGN KEY(majorId) REFERENCES major(id) #外鍵
);
顯示表索引
通用的寫法
CREATE TABLE IF NOT EXISTS stuinfo(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
gender CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorId INT,
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorId) REFERENCES major(id)
);
二、修改表時添加限制
1.添加列級限制
2.添加表級限制
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id INT ,
stuname VARCHAR(20) ,
gender CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorId INT
);
1.添加非空的限制
2.添加預設限制
3.添加主鍵
①列級限制
②表級限制
4.添加唯一
①列級限制
②表級限制
5.添加外鍵
DESC stuinfo;
SHOW INDEX FROM stuinfo;
三、修改表時删除限制
1.删除非空限制
2.删除預設限制
3.删除主鍵
4.删除唯一
5.删除外鍵
SHOW INDEX FROM sttuinfo;
ALTER TABLE emp2 COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT pk_my_emp_id PRIMARY KEY (id);
辨別列
又稱為自增長列
含義：可以不用手動的插入值，系統提供預設機關序列值
特點：
1、辨別列必須和主鍵搭配嗎？不一定，但要求是一個key
2、一個表可以有多個辨別列?至多一個
3、辨別列的類型隻能是數值型
4、辨別列可以通過set auto_increment_increment=3;設定步長
可以通過手動插入值，設定起始值
一、建立表時設定辨別列
TRUNCATE TABLE tab_identity;
CREATE TABLE IF NOT EXISTS tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)，
seat INT UNIQUE AUTO_INCREMENT
);
INSERT INTO tab_identity VALUES(NULL,'Tom'),(NULL,'join');
SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment = 3;
SELECT * FROM tab_identity;
二、修改表時設定辨別列
三、修改表時删除辨別列
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)