mysql高階查詢語句

weixin_49226813發表於2020-11-04

一、按關鍵字排序

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、常用萬用字元

  1. %:表示0個,1個或多個
  2. _:表示單個字元

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值和空值的區別

  1. 空值長度為0,不佔空間;NULL值的長度為NULL,佔用空間
  2. IS NULL無法判斷空值
  3. 空值使用“=”或者“<>”來處理
  4. 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,chengjifromtestwherexingmingregexpn’;

匹配單個字元(.)
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、比較運算子

  1. 字串的比較預設不區分大小寫,可使用binary來區分
  2. 常用比較運算子
    在這裡插入圖片描述在這裡插入圖片描述
    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>selectfromtestlimit3;>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

相關文章