MySQL資料庫
sql語句分類
| 分類 | 全稱 | 說明 |
|---|---|---|
| DDL | 資料庫定義語言，用來定義資料庫（資料庫，表，欄位） | |
| DML | 資料操作語言，用來對資料表中的資料進行增刪改 | |
| DQL | 資料查詢語言，用來查詢資料庫中的表的記錄 | |
| DCL | 資料控制語言，用來建立資料庫、控制資料庫的訪問許可權 |
DDL語句
DDL運算元據庫
查詢：
查詢所有資料庫:
SHOW DATABASES;
查詢當前資料庫：
select database();
建立：
create database [if not exists]資料庫名 [default charset 字符集] [collate 排序規則];(字符集用utf8mb4)長度為4個位元組
刪除：
drop database [if exists] 資料庫名;
使用：
use 資料庫名;
查詢當前資料庫所有表：
show tables;
查詢表的結構：
desc 表名;
查詢指定表的建表語句：
show creata table 表名;
建立資料表：
create table 表名(
欄位1 型別[comment 欄位註釋],
欄位2 型別,
欄位3 型別,
......
欄位n 型別
)[comment 表註釋];
DDL-資料型別
MySQL中資料型別很多，主要分為3類：數值型、字串型、日期時間型別
數值型別
MySQL 支援所有標準 SQL 數值資料型別。
這些型別包括嚴格數值資料型別(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似數值資料型別(FLOAT、REAL 和 DOUBLE PRECISION)。
關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。
BIT資料型別儲存位欄位值，並且支援 MyISAM、MEMORY、InnoDB 和 BDB表。
作為 SQL 標準的擴充套件，MySQL 也支援整數型別 TINYINT、MEDIUMINT 和 BIGINT。下面的表顯示了需要的每個整數型別的儲存和範圍。
| 型別 | 大小 | 範圍（有符號） | 範圍（無符號） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整數值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整數值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整數值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整數值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 極大整數值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 單精度 浮點數值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 雙精度 浮點數值 |
| DECIMAL | 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 | 依賴於M和D的值 | 依賴於M和D的值 | 小數值 |
日期和時間型別
表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。
TIMESTAMP型別有專有的自動更新特性，將在後面描述。
| 型別 | 大小 ( bytes) | 範圍 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 時間值或持續時間 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和時間值 |
| TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和時間值，時間戳 |
字串型別
字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
| 型別 | 大小 | 用途 | |
|---|---|---|---|
| CHAR | 0-255 bytes | 定長字串 | |
| VARCHAR | 0-65535 bytes | 變長字串 | |
| TINYBLOB | 0-255 bytes | 不超過 255 個字元的二進位制字串 | |
| TINYTEXT | 0-255 bytes | 短文字字串 | |
| BLOB | 0-65 535 bytes | 二進位制形式的長文字資料 | |
| TEXT | 0-65 535 bytes | 長文字資料 | |
| MEDIUMBLOB | 0-16 777 215 bytes | 二進位制形式的中等長度文字資料 | |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等長度文字資料 | |
| LONGBLOB | 0-4 294 967 295 bytes | 二進位制形式的極大文字資料 | |
| LONGTEXT | 0-4 294 967 295 bytes | 極大文字資料 |
注意：char(n) 和 varchar(n) 中括號中 n 代表字元的個數，並不代表位元組個數，比如 CHAR(30) 就可以儲存 30 個字元。
CHAR 和 VARCHAR 型別類似，但它們儲存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在儲存或檢索過程中不進行大小寫轉換。
char(10)最多隻能放十個，不滿十個用空格補全，效能較好，定長字元（空間換時間）
varchar(10)最多隻能放10個，儲存一個字元只佔一個字元，效能較差，邊長字元在使用過程中需要計算長度，（時間換空間）
BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進位制字串而不要非二進位制字串。也就是說，它們包含位元組字串而不是字元字串。這說明它們沒有字符集，並且排序和比較基於列值位元組的數值值。
BLOB 是一個二進位制大物件，可以容納可變數量的資料。有 4 種 BLOB 型別：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納儲存範圍不同。
有 4 種 TEXT 型別：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 型別，可儲存的最大長度不同，可根據實際情況選擇。
易錯
- 列名應該在型別之前，不需要使用單引號： 列名（例如'編號'，'員工工號'）應該在型別（例如 INT，VARCHAR）之前，而且不需要使用單引號。
- CHAR 型別的長度不需要指定為 1： 當使用 CHAR 型別時，不需要指定長度為 1。如果你希望性別只儲存一個字元，可以保留 CHAR 型別而不用指定長度。
- UNSIGNED 關鍵字放在 INT 後面： 如果你希望年齡是無符號整數，應該將 UNSIGNED 放在 INT 後面。
- 去掉最後一個逗號： 列定義的最後一個後面不需要逗號。
這個修改後的語句應該能夠正確建立一個名為 renshi 的員工表。
MySQL中的主鍵
主鍵的定義
主鍵：表中經常有一個列或多列的組合，其值能唯一地標識表中的每一行。這樣的一列或多列稱為表的主鍵，透過它可強制表的實體完整性。當建立或更改表時可透過定義 PRIMARY KEY 約束來建立主鍵。一個表只能有一個 PRIMARY KEY 約束，而且 PRIMARY KEY 約束中的列不能接受空值。由於 PRIMARY KEY 約束確保唯一資料，所以經常用來定義標識列。
use scholldb;
create table student(
學號 char(10) not null primary key, //設定主鍵為學號，且是唯一主鍵
姓名 char(10) not null
);
聯合主鍵
關聯式資料庫實際上還允許透過多個欄位唯一標識記錄，即兩個或更多的欄位都設定為主鍵，這種主鍵被稱為聯合主鍵。對於聯合主鍵，允許一列有重複，只要不是所有主鍵列都重複即可。
use scholldb;
create table student(
學號 char(10) not null ，
課程號 char(10) not null ，
成績 float(5,2) ，
primary key(學號,課程號) //聯合外來鍵為學號課程號
);
查詢表中主鍵的命令：desc (表名);
MySQL中外來鍵
保持資料的一致性、完整性。在一個庫中使用多表中的主鍵定義一個外來鍵，保持資料的完整性。
引用全部在一個表時資料太多太雜，所以拆為多個表，在表中設定唯一關字，然後作為表的主鍵，作為其他表的外來鍵，當多表聯合查詢時作為關鍵欄位
為了一張表記錄的資料不要太過冗餘。這和軟體project的模組化思想差點兒相同類似，僅僅只是在資料庫中是對錶關係進行解耦，儘量讓表 記錄的資料單一化。假如一張學生表中把成績和學生資訊放在一張表中就太冗餘了，成績全然能夠以學生的id作為區分標識。
列：在一個資料庫中定義A表的外來鍵來自B表中的C欄位，此時A表作為B表的父表
建立表時建立外來鍵
[CONSTRAINT <外來鍵名>] FOREIGN KEY 欄位名 [，欄位名2，…]
REFERENCES <主表名> 主鍵列1 [，主鍵列2，…]
//
1、
create table student(
學號 char(11) not null foreign key,
//在定義主鍵時新增外來鍵。（在新增外來鍵約束之前，必須確保引用表中的主鍵列已經定義）
姓名 char(10) not null
);
2、
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
create table student(
學號 char(11) not null,
姓名 char(10) not nll,
foreign key (學號) references course(學號)
//建立資料表student，並在表student上建立外來鍵約束，讓它的鍵 學號 作為外來鍵引表到 course表中 的學號欄位
);
對已有表建立外來鍵
單個主鍵
ALTER TABLE student
ADD FOREIGN KEY (班級編號) REFERENCES class(班級編號);
//將表student中的班級編號欄位作為外來鍵引用class中的班級編號作為主鍵
多個主鍵
alter table score
add foreign key (學號) references student(學號),
add foreign key (課程號) references student(課程號);
check完整性約束
Check完整性約束是一種用於限制表中資料值的約束。它定義了一個條件，該條件必須在插入或更新資料時得到滿足。如果資料不符合約束條件，操作將被拒絕並丟擲錯誤。
透過使用Check完整性約束，可以確保表中的資料滿足特定的條件，例如範圍、資料型別、格式等。這樣可以防止非法或無效的資料進入表中，提高資料的準確性和一致性。
以下是建立Check完整性約束的一般語法：
ALTER TABLE 表名稱
ADD CONSTRAINT 約束名稱 CHECK (條件);
其中，"表名稱" 是要新增約束的表名，"約束名稱" 是約束的名稱（可以自定義），"條件" 是定義約束的條件表示式。
資料插入
INSERT...VLAUES語句
insert into 表名 vlaues (值);
insert into 表名(欄位1,欄位2,) values(值1,值2);
REPLACE INSERT語句
此語句的作用是當我們在插入一條資料時，如果此條已經存在，那麼先刪除原來存在的資料再新增插入的資料，如果不存在那麼直接插入新的資料。注意：卻分是否存在是透過主鍵來確定的
replace into 表(欄位) values(值);
INSERT IGNORE INTO 語句
此語句的作用是如果插入的資料已經存在那麼就忽略插入的資料（也就是不改變原來的資料），如果不存在則插入新的資料。注意：卻分是否存在是透過主鍵來確定的
insert ignore into 表(欄位) values (值);
set插入
知道部分值插入資料，插入已知值，其他值為空（主鍵不能為空）
insert into 表 set 欄位1=‘值1’ , 欄位2=‘值2’ ... ;
傳遞引數
MySQL中的limit
約束行數，輸出約束行
其次第一行的量為0，而不是1
SELECT 欄位名 FROM 表名 LIMIT offset , 列數;
mysql 中的concat函式
作用：將多個字串連線成一個字串
concat (str1 , str2, ...) ;
SELECT CONCAT(地區, '省或市') FROM student; #不新增新列檢視
select concat（地區，'省或市'）as 新列名 from student ; #新增新列檢視
UPDATE student SET 地區 = CONCAT(地區, '省或市'); # 直接更改資料
MySQL中資料更新/修改
只改想改的資料：
update 表名 set 欄位名=值 where 條件子句;
update student set 出生日期='1998-02-10' where id = 1;
#在表student中把id=1的記錄欄位為出生日期的資料改變為1998-01-10
修改全部資料：
update 表名 set 欄位名1=值1,欄位名2=值2...,欄位名n=值n;
#對於NULL不能用=符號，要用is null
修改表結構：
alter table 表名 修改的動作語法
列：alter table 表名 modify 欄位名 資料型別 ;
列：aller table student modify 姓名 varchar(20) not null ;
#修改student 中的姓名列的資料型別為varchar(20) 並設定為不允許包含空值
修改資料型別：
alter table 表名 modify 欄位 新資料型別;
alter table c1 modify name varchar(20) not null;
修改欄位名：
alter table 表名 change 舊欄位名 新欄位名 新資料型別;
alter table c3 change name name1 varchar(30) not null
修改表之增加主鍵：
alter table 表名 add constraint 約束名字 約束型別[欄位];
alter table c5 add constraint PK_c5_id primary key(id);
說明：PK_c5_id是約束名(指定主鍵約束為PK_c5_id，對大部分資料庫有效但對於MySql無效，此主鍵約束名仍為primary)
在建表時給主鍵個性化名字較好
修改表名：
rename table 舊錶名 to 新表名;
rename table c5 to cc55;
建表後新增唯一性約束：
alter table 表名 add unique(欄位名)
alter table c9 add unique(id);
建表後新增預設值約束：
alter table 表名 alter 列名 set default’預設值’;
alter table c11 alter name set default “歐”;
建表後新增非空約束：
alter 表名 modify 欄位名欄位型別not null;
alter table c12 modify id int not null;
建表以後新增外來鍵：
alter table 表名稱 add foreign key (列名稱) references關聯表名稱(列名稱);
alter table stuInfo add foreign key (scode) references score(studentID);
MySQL中的資料刪除
Mysql刪除表中的資料有三種方法，分別是delete ，drop，truncate
delete刪除資料
delete from 表名 where 條件
一行一行的刪除，可以滾動，刪除 之後不釋放空間，保留表的資料結構
列：delete from student where 出生日期<1997-01-01
drop刪除資料
drop table 表名
直接刪除全部刪除，不可找回，刪除完之後釋放空間
truncat刪除資料
truncate table 表名
刪除表的所有資料，刪除完之後釋放空間，保留資料結構不可以回滾，不可以找回，不能與where連用
mysql中的資料查詢
單表查詢
選擇表中的若干列
指定查詢
select 列1,列2...form 表名;
經過計算的查詢
MySQL中的檢視
什麼是檢視：一個虛擬表，主要是select語句執行返回的結果集。檢視結果動態生成主要是sql語句執行結果與聯合，基於查詢結果。
檢視的建立
語法：create view 檢視名 [列表名] as select 語句
MySQL資料庫使用者管理
新增和刪除使用者
建立使用者
create user 使用者名稱 identified by '密碼';
新增一個新使用者user密碼為root
IDENTIFIED BY 用於設定密碼，MySQL 會先將密碼進行加密，在將其儲存到 user 表
列：create user 'user'@'localhost' identified by 'root';
檢視當前已有使用者
MySQL 中所有的使用者及許可權資訊都儲存在預設資料庫 mysql 的 user 表中。
進入 mysql 資料庫，透過 desc user; 可以檢視 user 表的結構。
use mysql;
desc user; //檢視user表的結構
host: 允許訪問的主機地址，localhost 為本機，% 為任何主機。
user: 使用者名稱
authentication_string: 加密後的密碼值，雜湊函式加密不可逆，在傳入密碼值時透過相同的加密方式加密對比加密值判斷
使用 select * from user; 檢視 user 表中當前有哪些使用者。
select host,user,authentication_string from user;
賬號重新命名
rename user 原來使用者名稱 to 新的使用者名稱;
僅 MySQL 5及之後的版本支援 RENAME USER。
MySQL 5以前的版本，要重新命名一個使用者，可使用 UPDATE 直接更新 user 表（謹慎操作）。
修改密碼
set password for 使用者= password('password');
可以使用 SET PASSWORD 語句重置賬號密碼。
使用 SET PASSWORD 重置賬號密碼。新密碼必須透過 Password() 函式進行加密。
當不指定使用者名稱時， SET PASSWORD 會重置當前登入使用者的密碼
SET PASSWORD = Password('password');
刪除使用者
drop user 用來刪除一個或多個MySQL使用者，並取消相關許可權
Drop user 使用者1,使用者2,... ;
刪除使用者user
drop user user@localhost;
許可權管理
使用者認證
形式：
使用者名稱+主機 遠端主機也可連結
授權
grant [許可權1,許可權2,許可權3] on *.* to user@'host' identified by 'password';
檢視當前許可權
show grants;
檢視指定使用者的許可權
show grants for admin@localhost;