資料庫常用的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中的資料型別

增加

插入資料

插入已知行（少數行資料

• 使用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`;

• 索引引數說明

洗掉索引

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

drop index stu_id on `t_student`;

標籤：MySQL

上一篇：[MySQL]MySQL8.0的一些注意事項以及解決方案

下一篇：MySQL8.0其他新特性