文章目錄
- 查詢
-
- 1. 單表查詢
-
- 1.1基本資料查詢
- 1.2 條件查詢
- 1.3 統計函數和分組查詢
- 2. 多表查詢
-
- 2.1 内連接配接查詢(INNER JOIN ...ON)
- 2.2 外連接配接查詢(OUTER JOIN ...ON)
- 2.3 子查詢
-
- 2.3.1 為什麼使用子查詢
- 2.3.2 帶比較運算符的子查詢
- 2.3.3 帶關鍵字IN的子查詢
- 2.3.4 帶關鍵字EXISTS的子查詢
- 2.3.5 帶關鍵字ANY的子查詢
- 2.3.5 帶關鍵字ALL的子查詢
- AS子句作用(别名顯示)
- DISTINCT關鍵字(過濾重複)
- 查詢中的表達式
- 查詢中的符号
查詢
1. 單表查詢
1.1基本資料查詢
- 查詢所有字段
select * from 表名; #效率低,盡量減少使用
- 查詢指定字段資料
select 指定的字段名, 指定的字段名,…… from 表名;
- 避免重複資料查詢
select distinct 字段名, 字段名,…… from 表名;
- 設定顯示格式資料查詢
select concat(name,'學生的總分是:',chinese+english+math+chinese+physics) from s_score; #concat() 是系統函數,連接配接字元串
1.2 條件查詢
Mysql中,通過關鍵字
WHERE
對所查詢到的資料進行過濾,條件查詢文法如下:
select field1,field2,…… from tablename where condition;
condition 查詢條件種類:
查詢條件 | 符号或關鍵字 |
---|---|
比較 | =,<,<=<>,>=,!=,<>,!>,!< |
指定範圍 | between and,not between and |
指定集合 | in,not in |
比對字元 | like,not like |
是否為空 | is null,is not null |
多個查詢條件 | and,or |
- 對查詢結果進行排序(ORDER BY)
select field1,field2,…… from tablename order by fieldm [ASC|DESC] [, order by fieldm [ASC|DESC]];
1.3 統計函數和分組查詢
mysql支援的統計函數:
- count() :統計表中記錄的條數 count(*):表中記錄進行統計,不管字段中包含的是null還不是null;count(field):對指定字段記錄進行統計,忽略null值
- AVG() :計算字段值的平均值
- SUM() :計算字段值的總和
- MAX():查詢字段的最大值
- MIN():查詢字段的最小值
- 簡單的分組查詢
- group_concat() :顯示每個分組中的指定字段select * from tablename group by field; # 按照field屬性進行分組,且預設查詢出每個分組中的随機一條記錄,具有很大不确定性,是以常于統計函數一起使用
select subject,group_concat(name) name,count(name) number from tablename group by subject; # 會根據 sudject 分組,然後顯示每個sudject 下所有教師的名字,count(name) 會統計顯示有幾個教師
- 多個字段分組查詢
select * from tablename group by field1,field2,……,fieldn; # 先按照field1 進行分組,在對每個分組按照filed2分組,以此類推
-
HAVING 子句限定分組查詢
通過
關鍵字來指定分組後的條件。HAVING
select * from tablename where condition group by field1,field2,……,fieldn having condition ;
- limit 限制資料記錄的查詢數量
select field1,field2,……,fieldn from tablename
where condition limit [offset_start,]row_count;
關鍵字
limit
來限制查詢結果數量,其中offset_start表示查詢的起始偏移,預設為0,row_count表示顯示行數。
- 正規表達式查詢
-
REGEXP 運算符
正規表達式通過模式去比對一類字元串,MySQL支援的模式字元如下:
-
模式字元 | 含義 |
---|---|
^ | 比對字元串的開始部分 |
$ | 比對字元串的結束部分 |
. | 比對字元串的任意一個字元 |
[字元集合] | 比對字元集合中的任意一個字元 |
[^字元集合] | 比對字元集合外的任意一個字元 |
str1 str2 str3 | 比對str1、str2、str3中的任意一個字元串 |
* | 零個或多個前面字元 |
+ | 至少一次比對前面的字元 |
字元串{N} | 字元串出現n次 |
字元串{M,N} | 字元串至少出現M次,最多N次v |
2. 多表查詢
2.1 内連接配接查詢(INNER JOIN …ON)
- 自連接配接
- 等值連接配接
- 不等連接配接
- 文法格式
select field1,field2,...,fieldn from tablename1 inner join tablename2 on condition [ ,inner join tablenamen on conidition];
-
自連接配接
自連接配接是特殊的等值連接配接,所謂的自連接配接就是指表與其自身進行連接配接
例:`查詢學生‘Alicia Florric’所在班級的其他學生,SQL語句如下:
# 方法1:where 設定比對 條件 select t.stuid,t.name,t.classno from student as t ,student as t2 where t.classno = t2.classno and t2.name = 'Alicia Florric'; #方法2:ANSI連接配接文法 select t.stuid,t.name,t.classno from student as t inner join student t2 on t.classno = t2.classno and t2.name = 'Alicia Florric';
-
等值連接配接
内連接配接查詢中的等值連接配接,就是在關鍵字ON後的比對條件中通過等于關系運算符(=)來實作等值條件
例:
執行SQL語句‘INNER JOIN ...ON’,在資料庫school中,查詢每個學生的編号,姓名,年齡,班級号,班級名稱,班級位置,和班主任資訊。
#方式1:where 設定比對 條件 select s.stuid,s.name,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor from student s,class c where s.classno = c.classno; #方法2:ANSI連接配接文法 select s.stuid,s.name,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor from student s inner class c on s.classno = c.classno;
-
不等連接配接
内連接配接查詢中的不等連接配接,就是在關鍵字ON後的比對條件中除了等于關系運算符來實作的不等條件,可以使用的關系運算符包含“>,>=,<,<=,!=”等。
2.2 外連接配接查詢(OUTER JOIN …ON)
Mysql 中外連接配接查詢會傳回所操作表中至少一個表的所有資料記錄。文法如下:
select field1,field2,...fieldn
from tablename1 left|right|full [outer] join tablename2
on condition;
- 左外連接配接
- 右外連接配接
- 全外連接配接
-
左外連接配接
外連接配接查詢中的左外連接配接,就是指新關系中執行比對條件時,以關鍵字left outer join左邊的表為參考。左連接配接的結果包括left outer 子句中指定的左表的所有行,而不僅僅是連接配接列所比對的行,如果左表的某行在右表中沒有比對行,則在相關聯的結果行中,右表選擇清單均為空值。
-
右外連接配接
外連接配接查詢中的右外連接配接,就是指新關系中執行比對條件時,以關鍵字right outer join右邊的表為參考。右連接配接的結果包括right outer 子句中指定的右表的所有行,而不僅僅是連接配接列所比對的行,如果右表的某行在左表中沒有比對行,則在相關聯的結果行中,左表選擇清單均為空值。
-
合并查詢資料記錄
UNION來實作并操作,将多個select語句查詢結果合并到一起,文法如下:
select field1,field2,...fieldn
from tablename1
union | union all #union 查詢結果會去掉重複值,而union all 不去重
select field1,field2,...fieldn
from tablename2
union | union all
select field1,field2,...fieldn
from tablename1
union | union all
select field1,field2,...fieldn
from tablename3
...
2.3 子查詢
子查詢是将一個查詢嵌套到另一個查詢語句中,内層查詢結果,可以作為外層查詢語句提供查詢條件。子查詢可以分為以下幾類:
- 帶比較運算符的子查詢
- 帶關鍵字IN的子查詢
- 帶關鍵字EXISTS的子查詢
- 帶關鍵字ANY的子查詢
- 帶關鍵字ALL的子查詢
2.3.1 為什麼使用子查詢
這個問題需要層多表查詢資料記錄來說,例如:需要查詢部門表t_dept和員工表t_employee的資料記錄。
直接執行如下的sql語句進行查詢:
select * from t_dept dept,t_employee empl where dept.deptno = empl.deptno;
上述SQL語句在執行過程中,首先會對兩個表進行笛卡爾積操作,然後再選取符合比對條件的資料記錄。進行笛卡爾積操作時,會生成兩個表資料記錄的乘積的資料記錄,如果這倆個表的資料記錄比較大,則在進行笛卡爾積操作時會造成當機。
對于有經驗的使用者,首先會通過統計函數檢視操作笛卡爾積後的資料記錄數,然後在進行多表查詢,如果查詢到笛卡爾積後的資料記錄數遠遠大于MySql可以接受的範圍,否則,使用子查詢來實作多表查詢。
2.3.2 帶比較運算符的子查詢
例: 在資料庫compay 中,查詢薪資水準為進階的員工的編号、姓名、性别、年齡和工資,具體步驟如下:
- 建立資料庫,并使用該資料庫
create database company;
use company;
- 建立t_employee表和t_slevel表,并檢視建立成功
CREATE TABLE t_employee ( id INT(4), name VARCHAR(20), gender VARCHAR(6), age INT(4), salary INT(6), deptno INT(4) ); describe t_employee; CREATE TABLE t_slevel ( id INT(4), salary INT(6), level INT(4), description VARCHAR(20) ); DESCRIBE t_slevel;
- 插入資料
INSERT INTO t_employee(id,name,gender,age,salary,deptno) VALUES(1001,'Alicia Florric','Female',33,10000,1), (1002,'Kalinda Sharma','Female',31,9000,1), (1003,'Cary Agos','Male',27,8000,1), (1004,'Eli Gold','Male',44,20000,2), (1005,'Peter Florric','Male',34,30000,2), (1006,'Diane Lockhart','Female',43,50000,3), (1007,'Maia Rindell','Female',43,9000,3), (1008,'Will Gardner','Male',36,50000,3), (1009,'Jacquiline Florric','Female',57,9000,4), (1010,'Zach Florric','Female',17,5000,5), (1011,'Grace Florric','Female',14,4000,5); INSERT INTO t_slevel(id,salary,level,description) VALUES(1,3000,1,'初級'), (2,7000,2,'中級'), (3,10000,3,'進階'), (4,20000,4,'特級'), (5,30000,5,'高管');
- 查詢薪資水準為進階的所有員工的編号、姓名、年齡和工資。
select * from t_employee where salary >= (select salary from t_slevel where id = 3) and salary < (select salary from t_slevel where id = 4)
2.3.3 帶關鍵字IN的子查詢
一個查詢語句的條件可能落在另一個SELECT語句的查詢結果中,這可以通過IN關鍵字來判斷。
- 例1:
select * from t_employee where deptno IN (select deptno from t_dept);
- 例2:
select * from t_employee where deptno NOT IN (select deptno from t_dept);
2.3.4 帶關鍵字EXISTS的子查詢
關鍵字EXISTS表示存在,後面的參數是一個任意的子查詢,系統對子查詢進行運算以判斷它是否傳回行,如果至少傳回一行,那麼EXISTS的結果為true,此時進行外層語句進行查詢;如果子查詢沒有傳回任何行,那麼EXISTS的傳回結果為false,此時外層語句将不進行查詢。
- 例1:查詢資料庫company的表t_dept中是否存在deptno為4的部門,如果存在,在查詢t_employee的記錄。
select * from t_employee where EXISTS (select deptname from t_dept where deptno = 4);
- 例2:查詢資料庫company的表t_dept中是否存在deptno為4的部門,如果存在,在查詢表t_employee中字段age大于40的資料記錄。
select * from t_employee where age>40 and EXISTS (select deptname from t_dept where deptno = 4);
2.3.5 帶關鍵字ANY的子查詢
關鍵字any表示滿足其中任何一個條件。使用關鍵字ANY時,隻要滿足内層查詢語句傳回的結果中的任何一個,就可以通過該條件來執行外層查詢語句。
2.3.5 帶關鍵字ALL的子查詢
關鍵字ALL表示滿足所有條件。使用關鍵字ALL時,隻有滿足内層查詢語句傳回的所有結果,才可以執行外層查詢語句。
AS子句作用(别名顯示)
- 可以給資料列取一個新的别名
- 可以給表取一個新别名
- 可把經計算或總結的結果用另外一個新名稱來代替
如:
- 查詢結果以别名顯示
select 列名1 [as] 别名1[,列名2 [as] 别名2,列名3 [as] 别名3 ,....] from 表名;
-
select a.studentno from student [as] a;
-
select Phone + 1 [as] tel from student;
注:
- AS可省略不寫
DISTINCT關鍵字(過濾重複)
-
作用:
去掉SELECT查詢傳回的記錄結果中重複的記錄
-
文法:
SELECT DISTINCT 列名1,列名2,… FROM 表名
-
注:
ALL關鍵字是預設的,傳回所有的記錄
查詢中的表達式
select version();
select now() as ‘目前時間’;
select concat(‘每個同僚的郵箱:’,email,’@163.com’) as Email from student;
查詢中的符号
- ‘%’ 不限個數的字元
select * from student where studentname like '劉%'; # 所有名字以劉開頭的同學資訊将被顯示
- ‘_’ 單字元的通配符
select * from student where studentname like '劉_'; # 所有名字以劉開頭,且姓名為2個字的同學資訊将被顯示
select * from student where studentname like '劉__'; # 所有名字以劉開頭,且姓名為3個字的同學資訊将被顯示
-
in
`select * from student where gradeid in (2,3); # gradeid是 2或3的
- between and 判斷操作數是否在某個範圍内
select 27 between 8 and 30; #是傳回1,否則傳回0