MySQ–語句大全

• 2019 年 12 月 16 日
• 筆記

MySQl查詢語句大全

綜合使用 查詢 目錄：

#—-綜合使用 書寫順序 select distinct * from '表名' where '限制條件' group by '分組依據' having '過濾條件' order by limit '展示條數' 執行順序 from — 查詢 where — 限制條件 group by — 分組 having — 過濾條件 order by — 排序 limit — 展示條數 distinct — 去重 select — 查詢的結果 正則：select * from emp where name regexp '^j.*(n|y)$'; 集合查詢：max 、min 、avg 、sum 、count 、group_concat 。 內連接：inner join 左連接：left join 右連接：right join 全連接： 左連接 union 右連接 replace 替換

拼接：concat、concat_ws、group_concat

常規設置操作 1.伺服器設置遠程訪問 grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;

2.Linux中資料庫的基本操作命令 1.使用service 啟動：service mysql start 停止：service mysql stop 重啟：service mysql restart 2.清屏：clear,reset

3.備份資料庫 #mysqldump -uroot -p密碼 資料庫名 > D:/備份文件名.sql

4.恢復備份的資料庫 #首先在mysql里建好資料庫名 #mysql -uroot -p密碼 資料庫名 < D:/備份文件名.sql

5.查詢binlog日誌是否開啟 show variables like 'log_%';

基本操作： 1.單表約束

#主鍵約束：PRIMARY KEY 要求被裝飾的欄位：唯一和非空 #唯一約束：UNIQUE 要求被裝飾的欄位：唯一， # 聯合唯一：在結尾：unique(欄位1，欄位2) #非空約束：NOT NULL 要求被裝飾的欄位：非空 #外鍵約束：FOREIGN KEY 某主表的外鍵 #自動增加：AUTO_INCREMENT 自動增加(需要和主鍵 PRIMARY KEY 同時用) #設置默認值:DEFAULT 為該屬性設置默認值 # 在int、char中：zerofill 不足位數默認填充0

2.常用數據類型

int #整型，4個位元組 一般不需要指定寬度，（8）：只是顯示為8位，默認有負號設置無負號： unsigned double #浮點型，例如double(5,2),標識最多5位，其中2位為小數，即最大為999.99 varchar #可變長度字元串類型。例如：varchar(10) 'aaa' 佔3位 char #固定長度字元串類型。例如：char(10) 'aaa' 佔10位 text #大文本字元串類型。 blob #位元組類型。例如： datetime #日期時間類型。例如：datetime(yyyy-MM-dd hh:mm:ss) date #日期類型。例如：date(yyyy:MM:dd) time #時間類型。例如：time(hh:mm:ss) timestamp #時間戳類型。例如：timestamp(yyyy-MM-dd hh:mm:ss) 會自動賦值 enum #枚舉 多選一 enum('male','female')，default為默認值 例如：sex enum('male','female') not null default 'male' set #集合 多選多,可以選一個 set（'read','DJ','DBJ','run') 註：字元串類型和時間類型都要用單引號括起來，空值為null

3.查看數據列表 show databases; — 查看所有資料庫 show create table 表名; — 查看錶的創建細節 desc 表名; — 查看錶結構

4.進入資料庫 #use 數據名 use Python_7

5.創建資料庫 #CREATE DATABASE 資料庫名 CREATE DATABASE Python_7; CREATE DATABASE pyrhon_7 charset utf8

# 修改資料庫編碼 alter database db1 charset gbk;

6.刪除資料庫 #drop database 需要刪除的資料庫名 drop database Python_7;

7.查看錶 select database(); # 查看當前所在的庫 show tables; — 查看資料庫中所有表 desc 表名; — 查看錶結構 show create table 表名; — 查看錶的創建細節

8.創建表 # 創建新表 # create table 新建數據表名（ # 欄位名 類型(長度) [約束(具體見1)], # 欄位名 類型(長度) [約束(具體見1)] # ); create table class( id INT AUTO_INCREMENT, name varchar(32) UNIQUE, age varchar(32) NOT NULL );

#需要注意

#根據已有的表創建新表 create table 新表 like 舊錶 — 使用舊錶創建新表 create table 新表 as select 欄位1 欄位2… from definition only — 使用自定義值去新建表

9.刪除表 #drop table 資料庫表名 drop table Python

10.修改表 alter table 表名 add 欄位名 類型（長度） [約束]; — 添加列 alter table 表名 modify 欄位名 類型（長度） [約束]; — 修改列的類型長度及約束 alter table 表名 change 舊欄位名 新欄位名 類型（長度） [約束]; — 修改列表名 alter table 表名 drop 欄位名; — 刪除列 alter table 表名 character set 字符集; — 修改表的字符集 rename table 表名 to 新表名; — 修改表名

11.增加數據 insert into 表(欄位名1，欄位名2..) values(值1，值2..);– 向表中插入某些列 insert into 表 values(值1，值2，值3..); — 向表中插入所有列

12.修改數據 update 表名 set 欄位名=值,欄位名=值…; — 這個會修改所有的數據，把一列的值都變了 update 表名 set 欄位名=值,欄位名=值… where 條件; — 只改符合where條件的行

13.刪除數據 delete from 表名 — 刪除表中所有記錄 delete from 表名 where 條件 — 刪除符合 where條件的數據 truncate table 表名; — 把表直接drop掉，重新建表，auto_increment將置為零。刪除的數據不能找回。執行速度比delete快

14.數據的簡單查詢 select * from 表名; — 查詢所有列 select 欄位名1,欄位名2,欄位名3.. from 表名; — 查詢指定列

15.幾個簡單的基本的sql語句 select * from 表名 where 範圍 — 選擇查詢 insert into 表名(field1,field2) values(value1,value2) — 插入 delete from 表名 where 範圍 — 刪除 update 表名 set field1=value1 where 範圍 — 更新 select * from 表名 where field1 like 』%value1%』 — 查找 select * from 表名 order by field1,field2 [desc] — 排序： select count as 需要統計總數的欄位名 from 表名 — 總數 select sum(field1) as sumvalue from 表名 — 求和 select avg(field1) as avgvalue from 表名 — 平均 select max(field1) as maxvalue from 表名 — 最大 select min(field1) as minvalue from 表名 — 最小

16.存儲引擎 # 查看所有的存儲引擎 show engines;

# 查看不同存儲引擎存儲表結構文件特點 create table t1(id int)engine=innodb; — MySQL默認的存儲引擎,支援事務,支援行鎖，支援外鍵。有且只有一個主鍵，用來組織數據的依據 create table t2(id int)engine=myisam; — 不支援事務，不支援外鍵，支援全文索引，處理速度快。 create table t3(id int)engine=blackhole; — 黑洞，寫入它的任何內容都會消失 create table t4(id int)engine=memory;– 將表中的數據存儲在記憶體中。表結構以文件存儲於磁碟。

insert into t1 values(1); insert into t2 values(1); insert into t3 values(1); insert into t4 values(1);

17.設置嚴格模式 # 查詢 show variables like '%mode%'; # 設置 set session — 設置當前窗口下有效 set global — 全局有效，終身受用 set global sql_mode = "STRICT_TRANS_TABLES"; # 設置完成後需要退出客戶端，重新登錄客戶端即可，不需要重啟服務端

group by分組涉及到的模式： 設置sql_mode為only_full_group_by，意味著以後但凡分組，只能取到分組的依據， 不應該在去取組裡面的單個元素的值，那樣的話分組就沒有意義了，因為不分組就是對單個元素資訊的隨意獲取 """ set global sql_mode="strict_trans_tables,only_full_group_by"; # 重新鏈接客戶端

18.like 的用法 A:% 包含零個或多個字元的任意字元串： 1、like'Mc%' 將搜索以字母 Mc 開頭的所有字元串（如 McBadden）。 2、like'%inger' 將搜索以字母 inger 結尾的所有字元串（如 Ringer、Stringer）。 3、like'%en%' 將搜索在任何位置包含字母 en 的所有字元串（如 Bennet、Green、McBadden）。

B:_（下劃線） 任何單個字元： like'_heryl' 將搜索以字母 heryl 結尾的所有六個字母的名稱（如 Cheryl、Sheryl）。

C：[ ] 指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單個字元： 1，like'[CK]ars[eo]n' 將搜索下列字元串：Carsen、Karsen、Carson 和 Karson（如 Carson）。 2、like'[M-Z]inger' 將搜索以字元串 inger 結尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer）。

D：[^] 不屬於指定範圍 ([a-f]) 或集合 ([abcdef]) 的任何單個字元： like'M[^c]%' 將搜索以字母 M 開頭，並且第二個字母不是 c 的所有名稱（如MacFeather）。

E：* 它同於DOS命令中的通配符，代表多個字元： c*c代表cc,cBc,cbc,cabdfec等多個字元。

F：？同於DOS命令中的？通配符，代表單個字元 : b?b代表brb,bFb等

G：# 大致同上，不同的是代只能代表單個數字。k#k代表k1k,k8k,k0k 。

下面我們來舉例說明一下：

例1，查詢name欄位中包含有「明」字的。 select * from table1 where name like '%明%'

例2，查詢name欄位中以「李」字開頭。 select * from table1 where name like '李*'

例3，查詢name欄位中含有數字的。 select * from table1 where name like '%[0-9]%'

例4，查詢name欄位中含有小寫字母的。 select * from table1 where name like '%[a-z]%'

例5，查詢name欄位中不含有數字的。 select * from table1 where name like '%[!0-9]%'

以上例子能列出什麼值來顯而易見。但在這裡，我們著重要說明的是通配符「*」與「%」的區別。

很多朋友會問，為什麼我在以上查詢時有個別的表示所有字元的時候用"%"而不用「*」？先看看下面的例子能分別出現什麼結果： select * from table1 where name like '*明*' select * from table1 where name like '%明%' 大家會看到，前一條語句列出來的是所有的記錄，而後一條記錄列出來的是name欄位中含有「明」的記錄，所以說，當我們作字元型欄位包含一個子串的查詢時最好採用「%」而不用「*」,用「*」的時候只在開頭或者只在結尾時，而不能兩端全由「*」代替任意字元的情況下。

高級查詢操作 1、外鍵表創建 一對多（Foreign Key） # foreign key(需要關聯的本欄位) references 需要關聯對錶的表(需要關聯對錶的欄位) 例如： 創建dep foreign key(dep_id) references dep(id) # 同步更新，同步刪除 on update cascade #同步更新 on delete cascade #同步刪除 2、表複製 複製表 create table t1 select * from test; 只複製表結構 create table t1 select * from test where 1=2; 3、單表查詢查詢 0.綜合使用 #—-綜合使用 書寫順序 select distinct * from '表名' where '限制條件' group by '分組依據' having '過濾條件' order by limit '展示條數' 執行順序 from — 查詢 where — 限制條件 group by — 分組 having — 過濾條件 order by — 排序 limit — 展示條數 distinct — 去重 select — 查詢的結果

正則：select * from emp where name regexp '^j.*(n|y)$'; 集合查詢：max 、min 、avg 、sum 、count 、group_concat 。 拼接：concat、concat_ws、group_concat 內連接：inner join 左連接：left join 右連接：right join 全連接： 左連接 union 右連接 1.where 查詢 # between 在…之間 select id,name from emp where id >= 3 and id <= 6; 相當於： select * from emp where id between 3 and 6;

# or 或者 select * from emp where id >= 3 or id <= 6;

# in，後面可以跟多個值 select * from 表名 where 欄位名 in (條件1,條件2,條件三);

# like （見上18） # char——length() 取字元長度 select * from 表名 where char_length(需要獲取長度的欄位名) = 4;

not 配合使用 注意：判斷空不能用 = ，只能用 is 2.group by 分組 select 查詢欄位1,查詢欄位2,… from 表名 where 過濾條件 group by分組依據 # 分組後取出的是每個組的第一條數據 3.聚合查詢 ：以組為單位統計組內數據>>>聚合查詢(聚集到一起合成為一個結果) # max 最大值 # 每個部門的最高工資 select post,max(salary) from emp group by post;

# min 最小值 # 每個部門的最低工資 select post,min(salary) from emp group by post;

# avg 平均值 # 每個部門的平均工資 select post,avg(salary) from emp group by post; # 每個部門的工資總和

# sum 求和 select post,sum(salary) from emp group by post;

# count（需要計數欄位） 計數 # 每個部門的人數 select post,count(id) from emp group by post;

# group_concat(需要分組後的欄位) # 不僅可以用來顯示除分組外欄位還有拼接字元串的作用 select post,group_concat(name) from emp group by post; — post：分組欄位，name 需要分組後顯示的欄位 拼接： concat（不分組時用）拼接字元串達到更好的顯示效果 as語法使用 舉例： select concat("NAME: ",name) as 姓名 from emp; concat_ws: 如果拼接的符號是統一的可以用 舉例： select concat_ws(':',name,age,sex) as info from emp; group_concat： 舉例： select post,group_concat(name,'DSB') from emp group by post; 補充：as語法 起別名 select name as 姓名,salary as 薪資 from emp; 4.having 過濾查詢 # having的語法格式與where一致，只不過having是在分組之後進行的過濾，即where雖然不能用聚合函數，但是having可以！

# 用法 select 查詢欄位1,查詢欄位2,… from 表名 where 過濾條件1 group by分組依據 having avg(過濾條件2) > 10000; 5.distinct 去重 # 對有重複的展示數據進行去重操作 select distinct 需取重欄位 from 表名; 6.order by 排序 select * from emp order by salary asc; #默認升序排 select * from emp order by salary desc; #降序排

# 多條件排序 #先按照age降序排，在年輕相同的情況下再按照薪資升序排 select * from emp order by age desc,salary asc; 7.limit 限制展示條數 # 限制展示條數 select * from emp limit 3; # 查詢工資最高的人的詳細資訊 select * from emp order by salary desc limit 1;

# 分頁顯示 select * from emp limit 0,5; # 第一個參數表示起始位置，第二個參數表示的是條數，不是索引位置 select * from emp limit 5,5; 8.正則 select * from emp where name regexp '^j.*(n|y)$'; 9.replace 替換 replace(str1,old,new) — str1：需要替換的欄位名 update gd_km set mc=replace（mc,'土地','房子'） 說明：new替換str1中出現的所有old，返回新的字元串，如果有某個參數為NULL，此函數返回NULL 該函數可以多次替換，只要str1中還有old存在，最後都被替換成new 若new為空，則刪除old 四、多表查詢 1.內連接、左連接、右連接、全連接 1、內連接：只取兩張表有對應關係的記錄（只拼兩個表共有的） 左表 inner join 右表 on 條件 select * from emp inner join dep on emp.dep_id = dep.id where dep.name = "技術";

2、左連接：在內連接的基礎上，保留左邊的數據，右邊沒有就為空 左表 inner left 右表 on 條件 3、右連接：在內連接的基礎上，保留右邊的數據，左邊沒有就為空 左表 inner right 右表 on 條件 4、全連接：左右連接都有，用union連接 左表 inner left 右表 on 條件 union 左表 inner right 右表 on 條件 select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id; 2.子查詢 # 就是將一個查詢語句的結果用括弧括起來當作另外一個查詢語句的條件去用 select name from where id in(select dep_id from emp where age>25);

mysql中用命令行複製表結構的方法主要有一下幾種:

1.只複製表結構到新表 1 CREATE TABLE 新表 SELECT * FROM 舊錶 WHERE 1=2; 或

1 CREATE TABLE 新表 LIKE 舊錶 ; 注意上面兩種方式，前一種方式是不會複製時的主鍵類型和自增方式是不會複製過去的，而後一種方式是把舊錶的所有欄位類型都複製到新表。

2.複製表結構及數據到新表 1 CREATE TABLE 新表 SELECT * FROM 舊錶 3.複製舊錶的數據到新表(假設兩個表結構一樣) 1 INSERT INTO 新表 SELECT * FROM 舊錶 4.複製舊錶的數據到新表(假設兩個表結構不一樣) 1 INSERT INTO 新表(欄位1,欄位2,…….) SELECT 欄位1,欄位2,…… FROM 舊錶