MySQL數(shù)據(jù)庫

sql語句分類

分類	全稱	說明
DDL		數(shù)據(jù)庫定義語言，用來定義數(shù)據(jù)庫（數(shù)據(jù)庫，表，字段）
DML		數(shù)據(jù)操作語言，用來對數(shù)據(jù)表中的數(shù)據(jù)進行增刪改
DQL		數(shù)據(jù)查詢語言，用來查詢數(shù)據(jù)庫中的表的記錄
DCL		數(shù)據(jù)控制語言，用來創(chuàng)建數(shù)據(jù)庫、控制數(shù)據(jù)庫的訪問權(quán)限

DDL語句

DDL操作數(shù)據(jù)庫

查詢：

查詢所有數(shù)據(jù)庫:

SHOW DATABASES;

查詢當前數(shù)據(jù)庫：

select database();

創(chuàng)建：

create database [if not exists]數(shù)據(jù)庫名 [default charset 字符集] [collate 排序規(guī)則];(字符集用utf8mb4)長度為4個字節(jié)

刪除：

drop database [if exists] 數(shù)據(jù)庫名;

使用：

use 數(shù)據(jù)庫名;

查詢當前數(shù)據(jù)庫所有表：

show tables;

查詢表的結(jié)構(gòu)：

desc 表名;

查詢指定表的建表語句：

show creata table 表名;

創(chuàng)建數(shù)據(jù)表：

create table 表名(
		字段1 類型[comment 字段注釋],
		字段2 類型,
		字段3 類型,
		......
		字段n 類型
)[comment 表注釋];

DDL-數(shù)據(jù)類型

MySQL中數(shù)據(jù)類型很多，主要分為3類：數(shù)值型、字符串型、日期時間類型

數(shù)值類型

MySQL 支持所有標準 SQL 數(shù)值數(shù)據(jù)類型。

這些類型包括嚴格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL 和 DOUBLE PRECISION)。

關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。

BIT數(shù)據(jù)類型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作為 SQL 標準的擴展，MySQL 也支持整數(shù)類型 TINYINT、MEDIUMINT 和 BIGINT。下面的表顯示了需要的每個整數(shù)類型的存儲和范圍。

類型	大小	范圍（有符號）	范圍（無符號）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整數(shù)值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整數(shù)值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整數(shù)值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整數(shù)值
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)	極大整數(shù)值
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)	單精度 浮點數(shù)值
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)	雙精度 浮點數(shù)值
DECIMAL	對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2	依賴于M和D的值	依賴于M和D的值	小數(shù)值

---

日期和時間類型

表示時間值的日期和時間類型為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	時間值或持續(xù)時間
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結(jié)束時間是第 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	二進制形式的長文本數(shù)據(jù)
TEXT	0-65 535 bytes	長文本數(shù)據(jù)
MEDIUMBLOB	0-16 777 215 bytes	二進制形式的中等長度文本數(shù)據(jù)
MEDIUMTEXT	0-16 777 215 bytes	中等長度文本數(shù)據(jù)
LONGBLOB	0-4 294 967 295 bytes	二進制形式的極大文本數(shù)據(jù)
LONGTEXT	0-4 294 967 295 bytes	極大文本數(shù)據(jù)

注意：char(n) 和 varchar(n) 中括號中 n 代表字符的個數(shù)，并不代表字節(jié)個數(shù)，比如 CHAR(30) 就可以存儲 30 個字符。

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉(zhuǎn)換。

char(10)最多只能放十個，不滿十個用空格補全，性能較好，定長字符（空間換時間）

varchar(10)最多只能放10個，存儲一個字符只占一個字符，性能較差，邊長字符在使用過程中需要計算長度，（時間換空間）

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進制字符串而不要非二進制字符串。也就是說，它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。

BLOB 是一個二進制大對象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲范圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應(yīng)的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據(jù)實際情況選擇。

易錯

1. 列名應(yīng)該在類型之前，不需要使用單引號： 列名（例如'編號'，'員工工號'）應(yīng)該在類型（例如 INT，VARCHAR）之前，而且不需要使用單引號。
2. CHAR 類型的長度不需要指定為 1： 當使用 CHAR 類型時，不需要指定長度為 1。如果你希望性別只存儲一個字符，可以保留 CHAR 類型而不用指定長度。
3. UNSIGNED 關(guān)鍵字放在 INT 后面： 如果你希望年齡是無符號整數(shù)，應(yīng)該將 UNSIGNED 放在 INT 后面。
4. 去掉最后一個逗號： 列定義的最后一個后面不需要逗號。

這個修改后的語句應(yīng)該能夠正確創(chuàng)建一個名為 renshi 的員工表。

MySQL中的主鍵

主鍵的定義

主鍵：表中經(jīng)常有一個列或多列的組合，其值能唯一地標識表中的每一行。這樣的一列或多列稱為表的主鍵，通過它可強制表的實體完整性。當創(chuàng)建或更改表時可通過定義 PRIMARY KEY 約束來創(chuàng)建主鍵。一個表只能有一個 PRIMARY KEY 約束，而且 PRIMARY KEY 約束中的列不能接受空值。由于 PRIMARY KEY 約束確保唯一數(shù)據(jù)，所以經(jīng)常用來定義標識列。

use scholldb;
create table student(
學(xué)號 char(10) not null primary key, //設(shè)置主鍵為學(xué)號，且是唯一主鍵
姓名 char(10) not null
);

聯(lián)合主鍵

關(guān)系數(shù)據(jù)庫實際上還允許通過多個字段唯一標識記錄，即兩個或更多的字段都設(shè)置為主鍵，這種主鍵被稱為聯(lián)合主鍵。對于聯(lián)合主鍵，允許一列有重復(fù)，只要不是所有主鍵列都重復(fù)即可。

use scholldb;
create table student(
學(xué)號 char(10) not null ，
課程號 char(10) not null ，
成績 float(5,2) ，
primary key(學(xué)號,課程號)   //聯(lián)合外鍵為學(xué)號課程號
);

查詢表中主鍵的命令：desc (表名);

MySQL中外鍵

保持數(shù)據(jù)的一致性、完整性。在一個庫中使用多表中的主鍵定義一個外鍵，保持數(shù)據(jù)的完整性。

引用全部在一個表時數(shù)據(jù)太多太雜，所以拆為多個表，在表中設(shè)置唯一關(guān)字，然后作為表的主鍵，作為其他表的外鍵，當多表聯(lián)合查詢時作為關(guān)鍵字段

為了一張表記錄的數(shù)據(jù)不要太過冗余。這和軟件project的模塊化思想差點兒相同類似，僅僅只是在數(shù)據(jù)庫中是對表關(guān)系進行解耦，盡量讓表 記錄的數(shù)據(jù)單一化。假如一張學(xué)生表中把成績和學(xué)生信息放在一張表中就太冗余了，成績?nèi)荒軌蛞詫W(xué)生的id作為區(qū)分標識。

列：在一個數(shù)據(jù)庫中定義A表的外鍵來自B表中的C字段，此時A表作為B表的父表

創(chuàng)建表時創(chuàng)建外鍵

[CONSTRAINT <外鍵名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主鍵列1 [，主鍵列2，…]
//
1、
create table student(
學(xué)號 char(11) not null foreign key,   
//在定義主鍵時添加外鍵。（在添加外鍵約束之前，必須確保引用表中的主鍵列已經(jīng)定義）
姓名 char(10) not null 
);

2、
FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
create table student(
學(xué)號 char(11) not null,
姓名 char(10) not nll,
foreign key (學(xué)號) references course(學(xué)號)
//創(chuàng)建數(shù)據(jù)表student，并在表student上創(chuàng)建外鍵約束，讓它的鍵 學(xué)號 作為外鍵引表到 course表中 的學(xué)號字段
);

對已有表創(chuàng)建外鍵

單個主鍵

ALTER TABLE student
ADD FOREIGN KEY (班級編號) REFERENCES class(班級編號);
//將表student中的班級編號字段作為外鍵引用class中的班級編號作為主鍵

多個主鍵

alter table score
add foreign key (學(xué)號) references student(學(xué)號),
add foreign key (課程號) references student(課程號);

check完整性約束

Check完整性約束是一種用于限制表中數(shù)據(jù)值的約束。它定義了一個條件，該條件必須在插入或更新數(shù)據(jù)時得到滿足。如果數(shù)據(jù)不符合約束條件，操作將被拒絕并拋出錯誤。

通過使用Check完整性約束，可以確保表中的數(shù)據(jù)滿足特定的條件，例如范圍、數(shù)據(jù)類型、格式等。這樣可以防止非法或無效的數(shù)據(jù)進入表中，提高數(shù)據(jù)的準確性和一致性。

以下是創(chuàng)建Check完整性約束的一般語法：

ALTER TABLE 表名稱
ADD CONSTRAINT 約束名稱 CHECK (條件);

其中，"表名稱" 是要添加約束的表名，"約束名稱" 是約束的名稱（可以自定義），"條件" 是定義約束的條件表達式。

數(shù)據(jù)插入

INSERT...VLAUES語句

insert into 表名 vlaues (值);
insert into 表名(字段1,字段2,) values(值1,值2);

REPLACE INSERT語句

此語句的作用是當我們在插入一條數(shù)據(jù)時，如果此條已經(jīng)存在，那么先刪除原來存在的數(shù)據(jù)再添加插入的數(shù)據(jù)，如果不存在那么直接插入新的數(shù)據(jù)。注意：卻分是否存在是通過主鍵來確定的

replace into 表(字段) values(值);

INSERT IGNORE INTO 語句

此語句的作用是如果插入的數(shù)據(jù)已經(jīng)存在那么就忽略插入的數(shù)據(jù)（也就是不改變原來的數(shù)據(jù)），如果不存在則插入新的數(shù)據(jù)。注意：卻分是否存在是通過主鍵來確定的

insert ignore into 表(字段) values (值);

set插入

知道部分值插入數(shù)據(jù)，插入已知值，其他值為空（主鍵不能為空）

insert into 表 set 字段1=‘值1’ , 字段2=‘值2’ ...  ;

傳遞參數(shù)

MySQL中的limit

約束行數(shù)，輸出約束行

其次第一行的量為0，而不是1

SELECT 字段名 FROM 表名 LIMIT offset , 列數(shù);

mysql 中的concat函數(shù)

作用：將多個字符串連接成一個字符串

concat (str1 , str2, ...) ;

SELECT CONCAT(地區(qū), '省或市') FROM student;  #不添加新列查看
select concat（地區(qū)，'省或市'）as 新列名 from student ; #添加新列查看
UPDATE student SET 地區(qū) = CONCAT(地區(qū), '省或市');  # 直接更改數(shù)據(jù)

MySQL中數(shù)據(jù)更新/修改

只改想改的數(shù)據(jù)：

update 表名 set 字段名=值 where 條件子句;

update student set 出生日期='1998-02-10' where id = 1;
#在表student中把id=1的記錄字段為出生日期的數(shù)據(jù)改變?yōu)?998-01-10

修改全部數(shù)據(jù)：

update 表名 set 字段名1=值1,字段名2=值2...,字段名n=值n;

#對于NULL不能用=符號，要用is null

修改表結(jié)構(gòu)：

alter table 表名 修改的動作語法

列：alter table 表名 modify 字段名 數(shù)據(jù)類型 ; 

列：aller table student modify 姓名 varchar(20) not null ;
#修改student 中的姓名列的數(shù)據(jù)類型為varchar(20) 并設(shè)置為不允許包含空值

修改數(shù)據(jù)類型：

alter table 表名 modify 字段 新數(shù)據(jù)類型;

alter table c1 modify name varchar(20) not null;

修改字段名：

alter table 表名 change 舊字段名 新字段名 新數(shù)據(jù)類型;

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，對大部分數(shù)據(jù)庫有效但對于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關(guān)聯(lián)表名稱(列名稱);

alter	table stuInfo add foreign key (scode) references score(studentID);

MySQL中的數(shù)據(jù)刪除

Mysql刪除表中的數(shù)據(jù)有三種方法，分別是delete ，drop，truncate

delete刪除數(shù)據(jù)

delete from 表名 where 條件

一行一行的刪除，可以滾動，刪除 之后不釋放空間，保留表的數(shù)據(jù)結(jié)構(gòu)

列：delete from student where 出生日期<1997-01-01

drop刪除數(shù)據(jù)

drop table 表名

直接刪除全部刪除，不可找回，刪除完之后釋放空間

truncat刪除數(shù)據(jù)

truncate table 表名

刪除表的所有數(shù)據(jù)，刪除完之后釋放空間，保留數(shù)據(jù)結(jié)構(gòu)不可以回滾，不可以找回，不能與where連用

mysql中的數(shù)據(jù)查詢

單表查詢

選擇表中的若干列

指定查詢

select 列1,列2...form 表名;

經(jīng)過計算的查詢

MySQL中的視圖

什么是視圖：一個虛擬表，主要是select語句執(zhí)行返回的結(jié)果集。視圖結(jié)果動態(tài)生成主要是sql語句執(zhí)行結(jié)果與聯(lián)合，基于查詢結(jié)果。

視圖的創(chuàng)建

語法：create view 視圖名 [列表名] as select 語句

MySQL數(shù)據(jù)庫用戶管理

添加和刪除用戶

創(chuàng)建用戶

create user 用戶名 identified by '密碼';

添加一個新用戶user密碼為root

IDENTIFIED BY 用于設(shè)定密碼，MySQL 會先將密碼進行加密，在將其保存到 user 表

列：create user 'user'@'localhost' identified by 'root';

查看當前已有用戶

MySQL 中所有的用戶及權(quán)限信息都存儲在默認數(shù)據(jù)庫 mysql 的 user 表中。

進入 mysql 數(shù)據(jù)庫，通過 desc user; 可以查看 user 表的結(jié)構(gòu)。

use mysql;
desc user;    //查看user表的結(jié)構(gòu)

host: 允許訪問的主機地址，localhost 為本機，% 為任何主機。

user: 用戶名

authentication_string: 加密后的密碼值，哈希函數(shù)加密不可逆，在傳入密碼值時通過相同的加密方式加密對比加密值判斷

使用 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() 函數(shù)進行加密。

當不指定用戶名時， SET PASSWORD 會重置當前登錄用戶的密碼

SET PASSWORD = Password('password');

刪除用戶

drop user 用來刪除一個或多個MySQL用戶，并取消相關(guān)權(quán)限

Drop user 用戶1,用戶2,... ;

刪除用戶user

drop user user@localhost;

權(quán)限管理

用戶認證

形式：

用戶名+主機  遠程主機也可鏈接

授權(quán)

grant [權(quán)限1,權(quán)限2,權(quán)限3] on *.* to user@'host' identified by 'password';

查看當前權(quán)限

show grants;

查看指定用戶的權(quán)限

show grants for admin@localhost;