免費(fèi)學(xué)習(xí)推薦:mysql視頻教程
文章目錄
- 一、DDL數(shù)據(jù)定義語句
- 二、數(shù)據(jù)類型
- 二、六大約束
- 四、標(biāo)識(shí)列
- 五、級(jí)聯(lián)刪除與置空
一、DDL數(shù)據(jù)定義語句
庫的管理
創(chuàng)建:create database [if not exists] 庫名;
修改:若需要修改庫名,直接修改文件夾
刪除:DROP DATABASE IF EXISTS 庫名;
表的管理
創(chuàng)建
create table IF NOT EXISTS 表名(
列名 列的類型[長(zhǎng)度、約束],
列名 列的類型[長(zhǎng)度、約束],
列名 列的類型[長(zhǎng)度、約束],
…
)
修改
alter table 表名 add|drop|modify|change column 列名 [列類型 約束];
刪除
drop table 表名;
【庫的管理】# 創(chuàng)建庫CREATE DATABASE IF NOT EXISTS books;# 修改庫的字符集ALTER DATABASE books charACTER SET gbk;# 刪除庫DROP DATABASE IF EXISTS books;【表的管理】# 1.創(chuàng)建表CREATE TABLE IF NOT EXISTS book( id int, bName VARCHAR(20), authorID INT, publishdate DATETIME);DESC book;CREATE TABLE author( id INT, au_name VARCHAR(20), nation VARCHAR(10));DESC author;# 2.修改表# 修改列名ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;# 修改列的類型或約束ALTER TABLE book MODIFY COLUMN pubdate timestamp;# 添加新列ALTER TABLE author ADD COLUMN annual double; # 添加新列作為第一個(gè)字段ALTER TABLE author ADD COLUMN newT1 INT FIRST;# 添加新列在指定的列后ALTER TABLE author ADD COLUMN newT2 INT AFTER newT1;# 刪除列ALTER TABLE author DROP COLUMN annual;# 修改表名ALTER TABLE author RENAME TO book_author;# 3.表的刪除DROP TABLE IF EXISTS book_author;SHOW TABLES;# 4.表的復(fù)制INSERT INTO author VALUES(1,'小櫻','日本'),(2,'悟空','中國(guó)'),(3,'綠巨人','美國(guó)'),(4,'哪吒','中國(guó)');# 僅復(fù)制表的結(jié)構(gòu)CREATE TABLE author2 LIKE author;# 復(fù)制表的結(jié)構(gòu)+數(shù)據(jù)CREATE TABLE author3 SELECT * FROM author;# 只復(fù)制部分?jǐn)?shù)據(jù)CREATE TABLE author4 SELECT id,au_name FROM author WHERE nation='中國(guó)';# 僅復(fù)制某些字段CREATE TABLE author5 SELECT id,au_name FROM author WHERE 1=2;
二、數(shù)據(jù)類型
2.1 整型
整數(shù)類型 | 別名 | 字節(jié) | 無符號(hào)范圍 | 有符號(hào)范圍 |
---|---|---|---|---|
Tinint | 微整型 | 1 | 0~255 | -128~127 |
Smallin | 小整型 | 2 | 0~65535 | -32768~32767 |
Mediumint | 中整型 | 3 | 0~1677215 | -8388608~8388607 |
Int或Integer | 整型 | 4 | 0~4294967295 | -2147483648~2147483647 |
Bigint | 大整型 | 8 | 0~9223372036854775807*2+1 | -9223372036854775808~9223372036854775807 |
【整型】# 1.默認(rèn)為有符號(hào),可以添加unsigned設(shè)置為無符號(hào)CREATE TABLE IF NOT EXISTS tab_int( t1 INT, # 有符號(hào) t2 INT UNSIGNED, # 無符號(hào) t3 INT ZEROFILL #添加zerofill后自動(dòng)變更為無符號(hào)整型,位數(shù)不夠0填充.);DESC tab_int;SELECT * FROM tab_int;# 2.如果插入的數(shù)值超出了整型的范圍,會(huì)報(bào)out of range異常INSERT INTO tab_int VALUES(2147483648,1);# 3.如果不設(shè)置長(zhǎng)度,會(huì)有默認(rèn)的長(zhǎng)度,位數(shù)不夠時(shí)0填充(前提是字段有ZEROFILL).
2.2 小數(shù)
浮點(diǎn)數(shù)類型 | 字節(jié) | 范圍 |
---|---|---|
Float(M,D) | 4 | -2^128 ~ +2^128 |
double(M,D) | 8 | -2^1024 ~ +2^1024 |
定點(diǎn)數(shù)類型 | ||
DEC(M,D) | M+2 | 最大取值范圍與double相同,給定decimal的有效范圍由M和D決定 |
注意:
M:整數(shù)部位+小數(shù)部位的總長(zhǎng)度
D:小數(shù)部位
D和M都省略時(shí):
1、如果是decimal類型,則M默認(rèn)為10,D默認(rèn)為0;
2、如果是floact和double,會(huì)根據(jù)插入的數(shù)值的精確度來決定精度。
3、定點(diǎn)型的精確度較高,如果要求插入數(shù)值的精確度較高如貨幣運(yùn)算則考慮使用。
2.3 字符型
字符串類型 | M是否可以省略 | 特點(diǎn) | 空間耗費(fèi) | 效率 | 范圍 |
---|---|---|---|---|---|
char(M) | M可以省略,默認(rèn)為1 | 定長(zhǎng) | 比較耗費(fèi) | 高 | M為0~255之間的整數(shù) |
varchar(M) | M不可以省略 | 可變長(zhǎng) | 比較節(jié)省 | 低 | M為0~65535之間的整數(shù) |
binary和varbinary類型,類似于char和varchar,不同的是它們包含二進(jìn)制字符而不包含非二進(jìn)制字符,即保存較短的二進(jìn)制。
Bit(M)類型,字節(jié)為1~8,范圍為Bit(1)~Bit(8)。
enum類型,即枚舉類型,要求插入的值必須屬于列表中指定的值之一,如果列成員為1~255,則需要1個(gè)字節(jié)存儲(chǔ);如果列成員為255~65535,則需要2個(gè)字節(jié)存儲(chǔ),最多需要65535個(gè)成員。
Set類型,和Enum類似,可以保存0~64個(gè)成員。和Enum最大的區(qū)別是:Set類型一次可以選取多個(gè)成員,而Enum只能選一個(gè),根據(jù)成員個(gè)數(shù)不同,存儲(chǔ)所占的字節(jié)也不同。
成員數(shù) | 字節(jié)數(shù) |
---|---|
1~8 | 1 |
9~16 | 2 |
17~24 | 3 |
25~32 | 4 |
33~64 | 8 |
【枚舉】CREATE TABLE tab_set( s1 SET('a','b','c'));INSERT INTO tab_set VALUES('a');INSERT INTO tab_set VALUES('c,a');INSERT INTO tab_set VALUES('a,b,c');# 插入后,內(nèi)部會(huì)進(jìn)行排序,如插入c,a會(huì)變成a,c
2.4 日期類型
日期和時(shí)間類型 | 特點(diǎn) | 字節(jié) | 最小值 | 最大值 |
---|---|---|---|---|
date | 只保存日期 | 4 | 1000-01-01 | 9999-12-31 |
datetime | 保存日期+時(shí)間 | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
timestamp(使用較多) | 保存日期+時(shí)間 | 4 | 187001080001 | 2038年的某個(gè)時(shí)刻 |
time | 只保存時(shí)間 | 3 | -838:59:59 | 838:59:59 |
year | 只保存年 | 1 | 1901 | 2155 |
timestamp和實(shí)際時(shí)區(qū)有關(guān),更能反映實(shí)際的日期;datetime則只能反映出插入時(shí)的當(dāng)?shù)貢r(shí)區(qū)。
timestamp的屬性受Mysql版本和SQLMode的影響很大。
【日期類型】CREATE TABLE tab_date( t1 DATETIME, # 不受時(shí)區(qū)影響 t2 TIMESTAMP # 受時(shí)區(qū)影響);INSERT INTO tab_date VALUES(NOW(),NOW());SET time_zon='+8:00';# 設(shè)置時(shí)區(qū)SHOW VARIABLES LIKE 'time_zone'; #顯示當(dāng)前時(shí)區(qū)
二、六大約束
含義:一種限制,用于限制表中的數(shù)據(jù),為了保證表中數(shù)據(jù)的準(zhǔn)確性和可靠性。
NOT NULL | 非空約束,規(guī)定某個(gè)字段不能為空 |
---|---|
UNIQUE | 唯一約束,規(guī)定某個(gè)字段在整個(gè)表匯中是唯一的 |
PRIMARY KEY | 主鍵(唯一且非空) |
FOREIGN KEY | 外鍵 |
CHECK | 檢查約束(mysql中不支持) |
default | 默認(rèn)值,保證該字段有默認(rèn)值 |
列級(jí)約束:六大約束語法上都支持,但外鍵約束沒有效果。
表級(jí)約束:除了非空、默認(rèn),其他都支持。
常用的做法是:其他約束都寫在列級(jí),外鍵約束寫在表級(jí)。
主鍵和唯一對(duì)比 | 是否保證唯一性 | 是否允許為空 | 允許有幾個(gè) | 是否允許組合(不推薦) |
---|---|---|---|---|
主鍵 | √ | × | 至多一個(gè)主鍵 | 允許組合主鍵 |
唯一 | √ | √(允許有一個(gè)null) | 可以有多個(gè)唯一 | 允許組合唯一 |
外鍵的特點(diǎn):
- 要求在從表設(shè)置外鍵關(guān)系。
- 從表的外鍵列的類型和主表的關(guān)聯(lián)列的類型要求一致或兼容,名稱無要求。
- 主表的關(guān)聯(lián)列必須是一個(gè)key(一般是主鍵或唯一)
- 插入數(shù)據(jù)時(shí),先插入主表再插入從表;刪除數(shù)據(jù)時(shí),先刪除從表再刪除主表。
列級(jí)約束和表級(jí)約束比較 | 位置 | 支持的約束類型 | 是否可以起別名 |
---|---|---|---|
列級(jí)約束 | 列的后面 | 語法都支持,但外鍵沒有效果 | 不可以 |
表級(jí)約束 | 所有列的下面 | 默認(rèn)和非空不支持,其他支持 | 可以(主鍵沒有效果) |
【列級(jí)約束】 直接在字段名和類型后面追加約束類型。 注意:只支持默認(rèn)、非空、主鍵、唯一,不支持外鍵約束CREATE DATABASE students;USE students;CREATE TABLE major( id INT PRIMARY KEY,# 主鍵 majorName VARCHAR(20));CREATE TABLE stuinfo( id INT PRIMARY KEY,# 主鍵 stuName VARCHAR(20) NOT NULL, #非空 gender CHAR(1) CHECK(gender='男' OR gender='女'),#檢查 seat INT UNIQUE,# 唯一 age INT DEFAULT 18, #默認(rèn)約束 majorId INT REFERENCES major(id) # 外鍵);DESC stuinfo;SHOW INDEX FROM stuinfo; # 查看表中所有的索引,外鍵,唯一【表級(jí)約束】# 語法:[CONSTRAINT 約束名 ] 約束類型(字段名)DROP TABLE IF EXISTS stuinfo;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), # 唯一 CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 檢查(不報(bào)錯(cuò),但無效) CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) # 外鍵);SHOW INDEX FROM stuinfo;【修改表時(shí)添加約束】# 添加非空約束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;# 添加默認(rèn)約束ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;# 添加主鍵ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;# 添加唯一鍵ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;# 添加外鍵ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);DESC stuinfo;SHOW INDEX FROM stuinfo;【修改表時(shí)刪除約束】# 刪除非空約束ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;# 刪除默認(rèn)約束ALTER TABLE stuinfo MODIFY COLUMN age INT;# 刪除主鍵ALTER TABLE stuinfo DROP PRIMARY KEY;# 刪除唯一ALTER TABLE stuinfo DROP INDEX seat;# 刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
學(xué)習(xí)了約束,嘗試完成秈米的測(cè)試題
1、列級(jí)約束:
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;# 列約束不支持起名字
表級(jí)約束:
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);# 實(shí)際上主鍵起了名字也沒效果
2、與1類似
3、
ALTER TABLE emp2 ADD COLUMN dept_id INT;
ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id);
四、標(biāo)識(shí)列
標(biāo)識(shí)列又稱為自增長(zhǎng)列,其實(shí)也可以將標(biāo)識(shí)列納入約束的范圍。
含義:可以不用手動(dòng)的插入值,系統(tǒng)提供默認(rèn)的序列值。
特點(diǎn):
1.標(biāo)識(shí)列不一定非要和主鍵搭配,但要求是一個(gè)key。
2.一個(gè)表至多可以有一個(gè)表示列。
3.表示列的類型只能為數(shù)值型。
4.標(biāo)識(shí)列可以通過SET auto_increment_increment=3;設(shè)置步長(zhǎng),也可以通過手動(dòng)插入值來設(shè)置起始值。
【創(chuàng)建表時(shí)設(shè)置標(biāo)識(shí)列】DROP TABLE IF EXISTS tab_identity;CREATE TABLE tab_identity( id INT PRIMARY KEY AUTO_INCREMENT, #設(shè)置自動(dòng)自增 NAME VARCHAR(20));INSERT INTO tab_identity VALUE(NULL,'花花');INSERT INTO tab_identity(NAME) VALUE('Hudie');SELECT * FROM tab_identity;SHOW VARIABLES LIKE '%auto_increment%';SET auto_increment_increment=3;#設(shè)置步長(zhǎng)為3# 起始值可以通過改變第一條記錄的值來更改TRUNCATE TABLE tab_identity;【修改表時(shí)設(shè)置標(biāo)識(shí)列】CREATE TABLE tab_identity( id INT, #設(shè)置自動(dòng)自增 NAME VARCHAR(20));# 設(shè)置主鍵和標(biāo)識(shí)列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;修改表時(shí)刪除標(biāo)識(shí)列【】ALTER TABLE tab_identity MODIFY COLUMN id INT;
五、級(jí)聯(lián)刪除與置空
級(jí)聯(lián)刪除:
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON delete CASCADE;
級(jí)聯(lián)置空:
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;
【演示級(jí)聯(lián)刪除、級(jí)聯(lián)置空】DROP TABLE major,stuinfoCREATE TABLE IF NOT EXISTS major( id INT PRIMARY KEY, majorName VARCHAR(20));INSERT INTO majorVALUES(1,'Java'),(2,'Python'),(3,'Go');CREATE TABLE IF NOT EXISTS stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT);INSERT INTO stuinfoSELECT 1,'join1','女',NULL,NULL,1 UNION ALLSELECT 2,'join2','女',NULL,NULL,1 UNION ALLSELECT 3,'join3','女',NULL,NULL,2 UNION ALLSELECT 4,'join4','女',NULL,NULL,2 UNION ALLSELECT 5,'join5','女',NULL,NULL,1 UNION ALLSELECT 6,'join6','女',NULL,NULL,3 UNION ALLSELECT 7,'join7','女',NULL,NULL,3 UNION ALLSELECT 8,'join8','女',NULL,NULL,1);SELECT * FROM major;SELECT * FROM stuinfo;# 傳統(tǒng)方式添加外鍵ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id);# 刪除major表的3號(hào)專業(yè)# 方式1:級(jí)聯(lián)刪除# 先刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外鍵時(shí)添加級(jí)聯(lián)刪除ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;DELETE FROM major WHERE id =3;# 方式2:級(jí)聯(lián)置空# 先刪除外鍵ALTER TABLE stuinfo DROP FOREIGN KEY fk_stu_major;# 添加外鍵時(shí)添加級(jí)聯(lián)置空ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;DELETE FROM major WHERE id =2;
更多相關(guān)免費(fèi)學(xué)習(xí)推薦:mysql教程(視頻)