資料庫常用的sql語句大全--sql

前言

本片部落格使用mysql資料庫進行資料操作，使用Navicat for mysql 這個IDE進行視覺化操作。每個SQL語句都是親身實驗驗證的，並且經過自己的思考的。能夠保證sql語句的可執行性。
sql語句的命令不區分大小寫，但儲存的資料是區分大小寫的。在這裡我們統一使用英文小寫進行命令編輯。如果喜歡大寫的可以使用IDE編輯器的一鍵美化功能，可以統一轉化為大寫。並且會對你的sql語句進行美化，例如自動換行等。

建立刪除（資料庫、表）

---

• 建立資料庫

create database <資料庫名>;

-- 建立名為dbtest資料庫
create database dbtest;
use dbtest;

---

• 建立表
  create table 表名（欄位）；
  • comment 是註釋的意思
  • primary key(stu_id) 把stu_id設定為主鍵，主鍵的設定可以細分為三種方式，後面再寫
  • ENGINE=InnoDB 把儲存引擎設定為InnoDB
  • charset=utf8mb4 編碼格式設定utf8mb4，utf8mb4是超集合，完全相容utf8，不需要做特殊轉換

-- 建立表
create table `t_student`(
`stu_id` char(12) not null COMMENT '學生id',
`stu_name` varchar(12) not null comment '學生姓名',
`stu_sex` tinyint(2) unsigned default null comment '性別：0（男），1（女）',
`stu_age` tinyint(3) unsigned DEFAULT NULL COMMENT '年齡',
PRIMARY KEY (`stu_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生基本資訊表';

---

mysql中的資料型別

型別	包含
數值型別	整數型別（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）、浮點數型別（float、double）、定點數型別（decimal）
日期/時間型別	YEAR、TIME、DATE、DATETIME、TIMESTAMP
字串型別	CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等
二進位制型別	BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB

增加

插入資料

插入已知行（少數行資料

• 使用insert values 插入資料

-- 插入資料 insert  values
insert into `t_student` 
(`stu_id`,`stu_name`,`stu_sex`,`stu_age`)
values(1,"hjk","0",18);

• 使用 insert set 插入資料

-- insert set
insert into `t_student`
set `stu_id`=3,`stu_name`='hjk',`stu_sex`=1,`stu_age`=18

• 從另一個表查詢的資料插入新表 insert from，這個查詢出來的資料要與插入到表裡的欄位有對應關係，例如查尋出來的資料如果有欄位"stu_address"你如果直接插入就會不成功！

insert into `t_student` select `stu_id`,`stu_name`,`stu_sex`,`stu_age` from `t_student01`;

插入很多行資料（10萬條）

使用儲存過程插入資料，在我們做實驗時可能需要很多資料進行操作，但是一條一條手動加時不太容易實現的，我們可以使用其他方法插入資料（例如：連線jdbc，進行操作），但是這個插入的是幾乎相同的資料，在這裡我們使用儲存過程並通過呼叫儲存過程實現插入大量資料！

• 建立儲存過程
  • delimiter ## 定義結束符號，##是你自定義的符號可以是其他的符號（如：$、%、&),在最後end不要忘了寫。
  • 其實中間就是一個while迴圈，變數為i。
  • 可以在定義的時候輸入引數，這個我沒有定義。

---

-- 插入大量資料，使用儲存過程
delimiter ##
create procedure insert_pro()
begin
declare i int default 4;
while i <=100000 do
insert into `t_student` values(i,'hjk','0','20');
set i = i+1;
end while;
end ##

• 使用儲存過程，建立儲存過程後並沒有效果，只有使用後才有效果

---

-- 使用儲存過程
call insert_pro();

• 刪除儲存過程

---

-- 刪除儲存過程
DROP PROCEDURE IF EXISTS insert_pro;

刪除

這裡只記錄刪除表和刪除資料庫，其他的會在每個建立後面寫，例如給表新增欄位，那相應的會在後面寫如何刪除欄位

---

刪除表

-- 刪除表
drop table `t_student`;

---

刪除資料庫

-- 刪除dbtest資料庫
drop database dbtest;

刪除表資料

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
刪除stu_id為1的資料

delete from `t_student` where `stu_id`="1";

清空表中所有資料

truncate table `t_student`;

修改改

修改表結構（6種約束）

新增主鍵（三種方式）

• 建立表時，行級新增主鍵

create table `t_student`(
`stu_id` char(12) not null PRIMARY KEY COMMENT '學生id',
`stu_name` varchar(12) not null comment '學生姓名',
`stu_sex` tinyint(2) unsigned default null comment '性別：0（男），1（女）',
`stu_age` tinyint(3) unsigned DEFAULT NULL COMMENT '年齡'
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生基本資訊表';

• 建立表時表級新增主鍵 就是在文章剛開始的時候建立的那個表
• 表外新增主鍵

-- 最後新增主鍵
alter table `t_student` add primary key(`stu_id`);

• 刪除主鍵

-- 刪除主鍵約束
alter table `t_student` drop primary key;

新增外來鍵約束

外來鍵約束經常和主鍵約束一起使用，用來確保資料的一致性！
外來鍵需要用兩個表新增，一個是主表一個是從表。在這裡我們使用主表位t_student表，從表位t_coruse表;一種是建立表時建立外來鍵約束，一種時後面新增外來鍵約束；

第一種：
"constraint fk_course_id foreign key(stu_id) references t_student(stu_id)"建立名為fk_course_id的外來鍵，使t_course表裡的cou_id欄位參照t_student表裡的stu_id欄位，在這裡邏輯不對，重點是說語法。

create table t_course(
`cou_id` char(8) primary key,
`stu_id` char(12) not null,
`cou_name` varchar(12) not null,
`semester` smallint ,
`credit` smallint,
constraint fk_course_id foreign key(`stu_id`) references t_student(`stu_id`)
);

第二種：
新增外來鍵使表裡最好不要有資料，不然可能會建立失敗

alter table t_student
add constraint fk_student_id foreign key(`stu_id`) references t_course(`cou_id`);

• 刪除外來鍵約束
  在那個表新增的外來鍵，去哪個表刪除

alter table t_course
drop foreign key fk_course_id;

新增唯一檢查預設值非空約束

例如在t_student表的stu_name列新增唯一約束

• 建立表時新增唯一約束

-- 建立表
create table `t_student`(
`stu_id` char(12) not null PRIMARY KEY COMMENT '學生id',
`stu_name` varchar(12) unique,
`stu_sex` tinyint(2) unsigned default null comment '性別：0（男），1（女）',
`stu_age` tinyint(3) unsigned DEFAULT NULL COMMENT '年齡',
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='學生基本資訊表';

• 建表後新增唯一性約束

alter table `t_student`
add constraint un_age unique(`stu_age`);

• 刪除唯一性約束

alter table `t_student`
drop index un_age;

==其實寫道這裡應該也能看出來，他們的語法格式幾乎是一樣的都是可以直接在（1）、建立表時在欄位後面新增改約束的關鍵字，（2）、在建立表時在最後用constraint新增並命名、（3）、在建立表後使用alter add新增約束，所以後面的幾種約束就不詳細寫了。

• 檢查約束
  檢查年齡是否大於1小於150
  建表時在欄位後面直接新增

check(`stu_age`>0 and `stu_age`<150)

• 後續新增

alter TABLE `t_student` add constraint check_age check(`stu_age`<100);

• 刪除檢查約束

alter table `t_student` drop check check_age;

---

非空約束和預設值的約束脩改和上面的不太一樣，時使用change修改

• 預設值約束
  在最開始建立表的時候就有幾個欄位定義位預設值為null，這裡不重複了，定義其他預設值就替換null就行了,這個null就是沒有定義的意思。
• 修改預設值約束

alter TABLE	`t_student` change column `stu_sex` `stu_sex` tinyint(2) default '1';

• 刪除預設值約束，預設值改為null就行了

alter TABLE	`t_student` change column `stu_sex` `stu_sex` tinyint(2) default null;

• 非空約束
  在建立表的時候主鍵有一個not null約束就是非空約束了。
• 新增非空約束
  ALTER TABLE <資料表名> CHANGE COLUMN <欄位名> <欄位名> <資料型別> NOT NULL;
• 刪除非空約束
  ALTER TABLE <資料表名> CHANGE COLUMN <欄位名> <欄位名> <資料型別> NULL;

修改表資料

• 修改表資料
  UPDATE <表名> SET 欄位 1=值 1 [,欄位 2=值 2… ] [WHERE 子句 ] [ORDER BY 子句] [LIMIT 子句]

-- 沒有條件全部修改
update `t_student` set `stu_name` = 'kjh' ,`stu_age` = '19';

-- 有條件只修改符合條件的
update `t_student` set `stu_name` = 'kjh' ,`stu_age` = 25 
where `stu_id` = 10;

查詢

• 去重，例如某一列含有大量資料，我們需要統計都出現過那些資料，統計出所有的資料反而不方便我們只需要統計一次這樣的資料。
  統計資料表中出現的所有的年齡

select distinct `stu_age` from `t_student`;

對年齡和姓名去重

select distinct `stu_name`, `stu_age` from `t_student`;

• 查詢所有資料的所有欄位

select * from `t_student`;

• 條件查詢，查詢名字為hjk的

select * from `t_student` where `stu_name` = 'hjk';

• 使用count(*)統計名字為hjk的個數

select count(*) from `t_student` where `stu_name` = 'hjk';

• 子查詢
  子查詢操作符：操作符可以是比較運算子和 IN、NOT IN、EXISTS、NOT EXISTS 等關鍵字

select * from `t_student` where `stu_id` in 
(select `stu_id` from `t_student` where `stu_age`=18);

子查詢語句可以巢狀在 SQL 語句中任何表示式出現的位置
在 SELECT 語句中，子查詢可以被巢狀在 SELECT 語句的列、表和查詢條件中，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句。

SELECT (子查詢) FROM 表名;
SELECT * FROM (子查詢) AS 表的別名;
SELECT * FROM (SELECT * FROM result) AS Temp;

---

• 分頁查詢
  從第十的資料開始查，向後查出20個資料。

select * from `t_student` limit 10,20;

• 排序，按照年齡大小升序,預設是升序asc排列，降序需要在order by 欄位後新增 desc

select * from `t_student` order by `stu_age`;

• 模糊查詢
  查詢名字為h開頭的資料,預設匹配不區分大小寫，就是為H開頭的也能查出來，但是可以在like後面加binary區分大小寫

select * from `t_student` where `stu_name` like 'h%';

查詢名字不為h開頭的資料

select * from `t_student` where `stu_name` not like 'h%';

萬用字元%和_的區別：
%代表後面可以匹配任意個字元。
_僅替代一個字元
%”萬用字元可以到匹配任意字元，但是不能匹配 NULL。也就是說 “%”匹配不到資料表中值為 NULL 的記錄
如果查詢內容中包含萬用字元，可以使用“\”轉義符

• 範圍查詢 between

select * from `t_student` where `stu_age` between 17 and 19;

• 空值查詢 空值條件不時等於null而是is null進行判斷

select * from `t_student` where `stu_age` is null;

• 分組查詢
  單獨使用 GROUP BY 關鍵字時，查詢結果會只顯示每個分組的第一條記錄

select `stu_name`,`stu_sex` from `t_student`  group by `stu_age`;

和group_count(欄位)一起使用

select `stu_name`,GROUP_CONCAT(`stu_sex`) from `t_student`  group by `stu_age`;

• having條件查詢
  having和where區別
  一般情況下，WHERE 用於過濾資料行，而 HAVING 用於過濾分組。
  WHERE 查詢條件中不可以使用聚合函式，而 HAVING 查詢條件中可以使用聚合函式。
  WHERE 在資料分組前進行過濾，而 HAVING 在資料分組後進行過濾 。
  WHERE 針對資料庫檔案進行過濾，而 HAVING 針對查詢結果進行過濾。也就是說，WHERE 根據資料表中的欄位直接進行過濾，而 HAVING 是根據前面已經查詢出的欄位進行過濾。
  WHERE 查詢條件中不可以使用欄位別名，而 HAVING 查詢條件中可以使用欄位別名。

---

having查詢，這個是正確的，因為代表所有的欄位，那當然肯定包含這個having條件的這個欄位，但是如果這個地方換成具體的欄位，並且沒有stu_name那就會報錯了。
having通常和group by一起使用

select * from `t_student` having `stu_name` = 'hjk';

資料庫檢視和索引

檢視

MySQL 檢視（View）是一種虛擬存在的表，同真實表一樣，檢視也由列和行構成，但檢視並不實際存在於資料庫中。行和列的資料來自於定義檢視的查詢中所使用的表，並且還是在使用檢視時動態生成的。

資料庫中只存放了檢視的定義，並沒有存放檢視中的資料，這些資料都存放在定義檢視查詢所引用的真實表中。使用檢視查詢資料時，資料庫會從真實表中取出對應的資料。因此，檢視中的資料是依賴於真實表中的資料的。一旦真實表中的資料發生改變，顯示在檢視中的資料也會發生改變。

檢視可以從原有的表上選取對使用者有用的資訊，那些對使用者沒用，或者使用者沒有許可權瞭解的資訊，都可以直接遮蔽掉，作用類似於篩選。這樣做既使應用簡單化，也保證了系統的安全。

檢視並不同於資料表，它們的區別在於以下幾點：

• 檢視不是資料庫中真實的表，而是一張虛擬表，其結構和資料是建立在對資料中真實表的查詢基礎上的。
• 儲存在資料庫中的查詢操作 SQL 語句定義了檢視的內容，列資料和行資料來自於檢視查詢所引用的實際表，引用檢視時動態生成這些資料。
• 檢視沒有實際的物理記錄，不是以資料集的形式儲存在資料庫中的，它所對應的資料實際上是儲存在檢視所引用的真實表中的。
• 檢視是資料的視窗，而表是內容。表是實際資料的存放單位，而檢視只是以不同的顯示方式展示資料，其資料來源還是實際表。
• 檢視是檢視資料表的一種方法，可以查詢資料表中某些欄位構成的資料，只是一些 SQL 語句的集合。從安全的角度來看，檢視的資料安全性更高，使用檢視的使用者不接觸資料表，不知道表結構。
• 檢視的建立和刪除隻影響檢視本身，不影響對應的基本表.

為什麼建立檢視
例如學生表裡面有學生id、姓名等，課程表有學生所選課程、上課時間等。如果我們檢視課程表是是需要學生姓名和課程上課時間就行，不需要其他的一些沒有用的資訊。這樣我們就可以建一個關於這兩個表的檢視，我們可以直接根據這個檢視獲取資訊。

建立檢視

CREATE VIEW <檢視名> AS <SELECT語句>

• 建立一個關於學生名字的檢視

create view view_student_name
as select `stu_name` from `t_student`;

• 檢視檢視資訊，和查詢表是幾乎一樣的，我們可以把檢視看成不是表的表

select * from view_student_name;

• 檢視檢視結構

-- 以表的結構顯示
desc view_student_name;
-- 以sql語句顯示
SHOW CREATE VIEW 檢視名;

• 建立基於多表的檢視

-- 隨便創個表
create table `t_course`(
`cou_id` int(11) primary key,
`s_id` char(12)
);

-- 建立多表檢視
create view v_stu_cou (`stu_id`,`cou_id`)
as select `stu_id`,`cou_id` from `t_student` s,`t_course` c 
where s.stu_id=c.s_id;

-- 檢視檢視結構
desc v_stu_cou

• 修改檢視

ALTER VIEW <檢視名> AS <SELECT語句>

• 刪除檢視

-- 直接刪除，可能已經不存在，會報錯
drop view v_stu_cou;
-- 判斷是否存在，再刪除
drop view if exists v_stu_cou;

索引

索引是一種特殊的資料庫結構，由資料表中的一列或多列組合而成，可以用來快速查詢資料表中有某一特定值的記錄
通過索引，查詢資料時不用讀完記錄的所有資訊，而只是查詢索引列。否則，資料庫系統將讀取每條記錄的所有資訊進行匹配
可以把索引比作新華字典的音序表，例如，要查一個字，如果不使用音序，就需要從字典的全部頁碼中逐頁來找。但是，如果提取拼音出來，構成音序表，就只需要從音序表確定的那幾頁頁的音序表中直接查詢。這樣就可以大大節省時間。

---

索引的優缺點
索引有其明顯的優勢，也有其不可避免的缺點。
優點
索引的優點如下：
通過建立唯一索引可以保證資料庫表中每一行資料的唯一性。
可以給所有的 MySQL 列型別設定索引。
可以大大加快資料的查詢速度，這是使用索引最主要的原因。
在實現資料的參考完整性方面可以加速表與表之間的連線。
在使用分組和排序子句進行資料查詢時也可以顯著減少查詢中分組和排序的時間
缺點
增加索引也有許多不利的方面，主要如下：
建立和維護索引組要耗費時間，並且隨著資料量的增加所耗費的時間也會增加。
索引需要佔磁碟空間，除了資料表佔資料空間以外，每一個索引還要佔一定的物理空間。如果有大量的索引，索引檔案可能比資料檔案更快達到最大檔案尺寸。
當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，這樣就降低了資料的維護速度。

MySQL支援以下幾種型別的索引。

  （1）B-Tree索引

  （2）雜湊索引

  （3）空間資料索引（R-Tree）

  （4）全文索引

  （5）其他索引類別

建立索引

CREATE <索引名> ON <表名> (<列名> [<長度>] [ ASC | DESC])
一個表可以建立多個索引，但每個索引在該表中的名稱是唯一的

• 可以再建立表時建立索引
  直接在建立表時的欄位最後新增 index(欄位名)

CREATE TABLE `t_student` (
  `stu_id` char(12) NOT NULL COMMENT '學生id',
  `stu_name` varchar(12) NOT NULL COMMENT '學生姓名',
  `stu_sex` tinyint(2) unsigned DEFAULT NULL COMMENT '性別：0（男），1（女）',
  `stu_age` tinyint(3) unsigned DEFAULT NULL COMMENT '年齡',
  PRIMARY KEY (`stu_id`),
  KEY `stu_id` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='學生基本資訊表'

建立唯一索引，還是同樣的位置，新增成unique index（欄位）

• 通過show create table 表名 檢視會發現，我們寫sql那個位置變為 KEY stu_id (stu_id)；

檢視索引

show index from `t_student`;

• 索引引數說明

引數	說明
Table	表示建立索引的資料表名
Non_unique	表示該索引是否是唯一索引。若不是唯一索引，則該列的值為 1；若是唯一索引，則該列的值為 0。
Key_name	表示索引的名稱
Seq_in_index	表示該列在索引中的位置，如果索引是單列的，則該列的值為 1；如果索引是組合索引，則該列的值為每列在索引定義中的順序。
Column_name	表示定義索引的列欄位。
Collation	表示列以何種順序儲存在索引中。在 MySQL 中，升序顯示值“A”（升序），若顯示為 NULL，則表示無分類。
Cardinality	索引中唯一值數目的估計值。基數根據被儲存為整數的統計資料計數，所以即使對於小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL 使用該索引的機會就越大
Sub_part	表示列中被編入索引的字元的數量。若列只是部分被編入索引，則該列的值為被編入索引的字元的數目；若整列被編入索引，則該列的值為 NULL
Packed	指示關鍵字如何被壓縮。若沒有被壓縮，值為 NULL
Null	用於顯示索引列中是否包含 NULL。若列含有 NULL，該列的值為 YES。若沒有，則該列的值為 NO
Index_type	顯示索引使用的型別和方法（BTREE、FULLTEXT、HASH、RTREE）
Comment	顯示評註

刪除索引

DROP INDEX <索引名> ON <表名>

drop index stu_id on `t_student`;