mysql高階查詢語句
專案目錄
一、按關鍵字排序
1、使用ORDER BY語句來實現排序
2、排序可針對一個或多個欄位
3、ASC：升序，預設排序方式
4、DESC：降序
5、ORDER BY的語法結構
select column1，column2，… from 庫名 order by column1,column,… asc|desc;
語句使用
mysql -uroot -p123123
mysql> create database score;
mysql> use score;
mysql> create table test(xuehao int(6) not null primary key,nianling int(3) not null,xingming char(20) not null,chengji int(3) not null);
mysql> insert into test values(201001,17,‘zhangsan’,60),(201002,17,‘zhaoliu’,95),(201003,18,‘lisi’,70),(201004,18,‘wangwu’,80),(201005,19,‘tianqi’,55);
mysql> select * from test;
升序
mysql> select chengji from test order by chengji asc;
mysql> select chengji from test order by chengji; #預設是ASC
降序
mysql> select chengji from test order by chengji desc;
按單欄位排序
mysql> select xuehao,xingming,chengji from test order by chengji;
按多欄位排序
mysql> select xingming,chengji from test order by nianling desc,chengji desc;
二、對結果進行分組
1、使用GROUP BY語句來實現分組
2、通常結合聚合函式一起使用
3、可以按一個或多個欄位對結果進行分組
4、GROUP BY分組
mysql> insert into test values(201006,18,‘zhangsan’,80),(201007,19,‘lisi’,70);
mysql> select * from test;
mysql> select count(xingming),nianling from test group by nianling;
GROUP BY結合ORDER BY
mysql> select count(xingming),nianling from test group by nianling order by nianling desc;
三、限制結果條目
1、只返回select查詢結果的第一行或第幾行
2、使用limit語句限制條目
3、limit語法結構
select column1，column2，… from 庫名 limit 位置偏移量
mysql> select * from test limit 3;
mysql> select * from test limit 3,3;
四、設定別名
1、使用AS語句設定別名，關鍵字AS可省略
2、設定別名時，保證不能與庫中其他表或欄位名稱衝突
3、別名的語法結構
列的別名：
select 列名 as 列名別名 from 庫名；
表的別名：
select 列名 from 庫名 as 庫名別名；
mysql> select t.xuehao as 學號,t.nianling as 年齡,t.xingming as 姓名,t.chengji as 成績 from test as t;
4.4、as作為連線語句
mysql> create table test1 as select * from test;
mysql> select * from test1;
五、萬用字元的使用
1、用於替換字串中的部分字元
2、通常配合like一起使用，並協同where完成查詢
3、常用萬用字元
- %：表示0個，1個或多個
- _：表示單個字元
mysql> select xuehao,xingming from test where xingming like ‘z%’;
mysql> select xuehao,xingming from test where xingming like ‘lis_’;
六、子查詢
1、也稱作內查詢或者巢狀查詢
2、先於主查詢被執行，其結果將作為外層查詢的條件
3、在增刪改查中都可以使用子查詢
4、支援多層巢狀
5、IN語句是用來判斷某個值是否在給定的結果集中
6、子查詢的用法
查詢：
mysql> select xuehao as 學號,chengji as 成績 from test where chengji in (select chengji from test where chengji >=60);
查詢結合降序使用：
mysql> select xuehao as 學號,chengji as 成績 from test where chengji in (select chengji from test where chengji >=60) order by chengji desc;
插入：
mysql> create table test_ as select * from score;
mysql> delete from test_;
mysql> select * from test_;
mysql> insert into test_ select * from test where chengji in (select chengji from test where chengji >=80);
mysql> select * from test_;
修改：、
mysql> alter table test_ add column num int(3);
mysql> desc test_ ;
mysql> update test_ set num=101 where chengji in (select chengji from test where chengji >=80);
mysql> select * from test_;
刪除：
mysql> delete from test where chengji in(select chengji from (select *from test where chengji >=75)a);
mysql> select * from test;
七、NULL值
1、表示缺失的值
2、與數字0或者空白（spaces）是不同的
3、使用IS NULL或IS NOT NULL進行判斷
4、NULL值和空值的區別
- 空值長度為0，不佔空間；NULL值的長度為NULL，佔用空間
- IS NULL無法判斷空值
- 空值使用“=”或者“<>”來處理
- COUNT（）計算時，NULL會忽略，空值會加入計算
插入空值：
mysql> alter table test_ add column class varchar(16);
mysql> select * from test_;
mysql> insert into test_ values(201007,19,‘lisi’,80,102,’’);
mysql> select * from test_;
null的用法：
mysql> select * from test_ where class is null ;
八、正規表示式
1、根據指定的匹配模式匹配記錄中符合要求的特殊字元
2、使用REGEXP關鍵字指定匹配模式
3、常用匹配模式
字元 說明
^ 匹配開始字元
$ 匹配結束字元
. 匹配任意單個字元
以z開頭的姓名：(^)
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘^z’;
以n結尾的姓名：( ) m y s q l > s e l e c t x u e h a o , x i n g m i n g , c h e n g j i f r o m t e s t w h e r e x i n g m i n g r e g e x p ′ n ) mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'n )mysql>selectxuehao,xingming,chengjifromtestwherexingmingregexp′n’;
匹配單個字元（.）
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘lis.’;
匹配前面字元至少1次（+）
mysql> insert into test_ values(201008,20,‘lio’,75,103,’’),(201009,20,‘lioo’,85,104,’’),(201009,20,‘liooo’,55,105,’’);
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘lioo+’;
匹配任意個前面的字元()
mysql> select xuehao,xingming,chengji from test_ where xingming regexp 'lio’;
匹配p1或p2(p1|p2)
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘l|n’;
匹配字符集中括號內的任何字元([…])
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘[hz]’;
匹配前面的字串n次{n}
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘o{2}’;
匹配前面的字串至少n次，至多m次({n,m})
mysql> select xuehao,xingming,chengji from test_ where xingming regexp ‘o{2,3}’;
mysql> select 5+2 as addition,8-5 as subtraction,6*7 as multiplication,8/2 as division,9%4 as remainder;
九、算術運算子
1、MySQL支援的算術運算子
2、比較運算子
- 字串的比較預設不區分大小寫，可使用binary來區分
- 常用比較運算子
mysql> select 2=4,2=‘2’,‘e’=‘e’,(4+4)=(5+3),‘n’=NULL;
從以上查詢可以看出：
①如果兩者都是整數，則按整數值進行比較
②如果一個整數一個字串，則會自動將字串轉換為數字，再進行比較
③如果兩者都是字串，則按照字串進行比較
④如果兩者中至少有一個值是NULL，則比較的結果是NULL
mysql> select 2>4,2<‘2’, ‘e’>=‘e’,(4+4)<=(5+3);
mysql> select 2!=4,null is null,null is not null,2 between 1 and 4;
mysql> select greatest (5,8,12),least (1,5,4);
十、邏輯運算子
1、又稱為布林運算子
2、用來判斷表示式的真假
3、常用的邏輯運算子
邏輯非
mysql> select not 2,!3,not 0,!(4-4);
邏輯與
mysql> select 2 and 3,4 && 0,0 && NULL,1 and NULL;
十一、位運算子
1、對二進位制數進行計算的運算子
2、常用的位運算子
mysql> select 4&5,4|5,4&~3,3^4,2<<2,2>>1;
十二、連線查詢
MySQL的連線查詢，通常都是將來自兩個或多個表的行結合起來，基於這些表之間的共同欄位，進行資料的拼接，首先，要確定一個主表作為結果集，然後將其他表的行有選擇性的連線到選定的主表結果集上，使用較多的連線查詢包括：內連線、左連線和右連線
1、內連線
mysql> select t.xuehao,t.xingming,t.chengji from test_ t inner join test t1 on t.xingming=t1.xingming;
2、左連線
mysql> select t.xuehao,t.xingming,t.chengji from test_ t left join test t1 on t.xingming=t1.xingming;
3、右連線
mysql> select t.xuehao,t.xingming,t.chengji from test_ t right join test t1 on t.xingming=t1.xingming;
十三、函式
1、數學函式
常用的數學函式舉例說明 1 mysql> select abs(-12),rand(),rand(),mod(4,5),power(2,6);
mysql> select round(2.4),round(2.5),round(2.4235,2),sqrt(2),truncate(2.4652,2);
mysql> select ceil(2.2),floor(2.8),greatest(1,2,3,4,5),least(1,2,3,4,5);
2、聚合函式：對錶中資料記錄進行集中概括而設計的一類函式
聚合函式舉例
mysql> select avg(chengji) from test;
mysql> select sum(chengji) from test;
mysql> select min(chengji) from test;
mysql> select max(chengji) from test;
mysql> alter table test add sex char(2);
mysql> select * from test;
mysql> update test set sex=‘男’ where xingming=‘lisi’;
mysql> update test set sex=‘男’ where xingming=‘zhangsan’;
mysql> select * from test;
mysql> select count(sex) from test;
3、字串函式
舉例說明
mysql> select length(‘abc’),trim( ‘ab’ ),concat(‘ab’,‘cd’),upper(‘abc’),lower(‘ABC’);
mysql> select left(‘abcd’,2),right(‘abcd’,2),repeat(‘abc’,3);
mysql> select space(2),replace(‘abcde’,‘e’,‘g’),strcmp(‘a’,‘b’),strcmp(‘a’,‘a’),strcmp(‘b’,‘a’);
mysql> select substring(‘abcdefg’,3,3),reverse(‘abcdefg’);
4、日期時間函式
mysql> select curdate(),curtime(),now(),month(‘2020-08-15’),week(‘2020-08-15’);
mysql> select hour(‘20:15:45’),minute(‘20:15:45’),second(‘20:15:45’);
mysql> select dayofweek(‘2020-08-15’),dayofmonth(‘2020-08-15’),dayofyear(‘2020-08-15’);
十四、儲存過程
1、簡介
1.1、是一組為了完成特定功能的SQL語句集合
1.2、比傳統的SQL速度更快、執行效率更高
1.3、儲存過程的優點
①執行一次後，會將生成的二進位制程式碼駐留緩衝區，提高執行效率
②SQL語句加上控制語句的集合，靈活性高
③在伺服器端儲存，客戶端呼叫時，降低網路負載
④可多次重複被呼叫，可隨時修改，不影響客戶端呼叫
⑤可完成所有的資料庫操作，也可控制資料庫的資訊訪問許可權
2、建立儲存過程
2.1、使用CREATE PROCEDURE語句建立儲存過程
2.2、建立儲存過程的語法結構
CREATE PROCEDURE <過程名> （過程引數[…]）<過程體> [過程引數[…]] 格式 [IN|OUT|INOUT] <引數名> <型別>
3、引數分為
3.1、輸入引數：IN
3.2、輸出引數：OUT
3.3、輸入/輸出引數：INOUT
4、儲存過程的主體部分，被稱為過程體
5、以BEGIN開始，以END結束，若只有一條SQL語句
6、以DELIMITER開始和結束
7、儲存過程
mysql> delimiter m y s q l > c r e a t e p r o c e d u r e a ( ) − > b e g i n − > s e l e c t ∗ f r o m t e s t l i m i t 3 ; − > e n d mysql> create procedure a() -> begin -> select * from test limit 3; -> end mysql>createprocedurea()−>begin−>select∗fromtestlimit3;−>end
mysql> delimiter ;
mysql> call a();
mysql> use score;
mysql> set @num1=1,@num2=2,@num3=3;
mysql> delimiter
m
y
s
q
l
>
c
r
e
a
t
e
p
r
o
c
e
d
u
r
e
p
(
i
n
n
u
m
1
i
n
t
,
o
u
t
n
u
m
2
i
n
t
,
i
n
o
u
t
n
u
m
3
i
n
t
)
−
>
b
e
g
i
n
−
>
s
e
l
e
c
t
n
u
m
1
,
n
u
m
2
,
n
u
m
3
;
−
>
s
e
t
n
u
m
1
=
10
,
n
u
m
2
=
20
,
n
u
m
3
=
30
;
−
>
s
e
l
e
c
t
n
u
m
1
,
n
u
m
2
,
n
u
m
3
;
−
>
e
n
d
mysql> create procedure p(in num1 int,out num2 int,inout num3 int) -> begin -> select num1,num2,num3; -> set num1=10,num2=20,num3=30; -> select num1,num2,num3; -> end
mysql>createprocedurep(innum1int,outnum2int,inoutnum3int)−>begin−>selectnum1,num2,num3;−>setnum1=10,num2=20,num3=30;−>selectnum1,num2,num3;−>end
mysql> delimiter ;
mysql> call p(@num1,@num2,@num3);
總結1：in和inout引數會將全域性變數的值傳入儲存過程中，而out引數不會將全域性變數的值傳入儲存過程中，在全域性過程使用中，引數值in、out、inout都會發生改變
mysql> select @num1,@num2,@num3;
總結2：呼叫完儲存過程後，發現in引數不會對全域性變數的值引起變化，而out和inout引數呼叫完儲存過程後，會對全域性變數的值產生變化，會將儲存過程引用後的值賦值給全域性變數，in引數賦值型別可以是變數還有定值，而out和inout引數賦值型別必須是變數
8、修改儲存過程
8.1、儲存過程的修改分為特徵修改和內容修改
8.2、特徵修改的方法
ALTER PROCEDURE <過程名> [<特徵>…]
8.3、內容修改可先刪除原有儲存過程，之後再建立方法
9、刪除儲存過程
9.1、刪除儲存過程的語法
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <過程名>
9.2、刪除的過程
mysql> drop procedure a;
mysql> call a();
ERROR 1305 (42000): PROCEDURE score.a does not exist
相關文章
- MySQL語句第二高的薪水查詢MySql
- mysql查詢語句MySql
- [Mysql 查詢語句]——查詢欄位MySql
- mysql查詢語句集MySql
- MySQL查詢阻塞語句MySql
- Mysql之查詢語句MySql
- mysql查詢語句5：連線查詢MySql
- [Mysql 查詢語句]——分組查詢group byMySql
- [Mysql 查詢語句]——查詢指定記錄MySql
- MySQL基礎查詢語句MySql
- Mysql 資料庫 -------- SQL語句進階查詢 ------- 前部分MySql資料庫
- MySQL高階部分-建表語句MySql
- MySQL內連線查詢語句MySql
- MySQL的簡單查詢語句MySql
- mysql dba常用的查詢語句MySql
- [Mysql 查詢語句]——集合函式MySql函式
- mysql查詢語句優化工具MySql優化
- mysql 查詢建表語句sqlMySql
- 理解索引（中）：MySQL查詢過程和高階查詢索引MySql
- mysql查詢效率慢的SQL語句MySql
- 請教一個mysql查詢語句！！MySql
- 高階查詢
- SQL語言基礎(高階查詢)SQL
- linux下開啟mysql慢查詢，分析查詢語句LinuxMySql
- mysql多條件過濾查詢之mysq高階查詢MySql
- 在mysql查詢效率慢的SQL語句MySql
- Mysql 獲取表設計查詢語句MySql
- Hql查詢語句
- Mongodb高階查詢MongoDB
- SQL高階查詢SQL
- ❖ MongoDB 高階查詢MongoDB
- MySQL 查詢常用操作(0) —— 查詢語句的執行順序MySql
- MySQL SQL語句查詢執行過程的四個階段介紹MySql
- Google高階搜尋技巧之高階語法查詢指令Go
- Elasticsearch查詢語句語法Elasticsearch
- MySQL 查詢語句執行過程淺析MySql
- mysql常見的查詢語句的應用MySql
- MySQL資料庫：7、SQL常用查詢語句MySql資料庫