mysql資料庫(三)：查詢的其他用法

標籤：拼接   均值   練習   最小值   image   sql語句   字串拼接   max   表達   

一. 查詢—IN的用法

文法：select ... from 表名 where 欄位 a in (值b, 值c, 值d...)

等價於 select ... from 表名 where 欄位a=值b or 欄位a=值c or 欄位a=值d;

例如，查詢學生表裡學生編號為1401001或者1401002或者1401003的學生資訊

select * from student where id=1401001 or id=1401002 or id=1401003;

select * from student where id in(1401001, 1401002, 1401003);

二. 查詢—模糊查詢(LIKE)

文法：select <欄位1, 欄位2, ...> from <表名> where <欄位名> like ‘%值a%‘;

樣本：

查詢學生表中姓名中有"小"的學生資訊

 

分析問題：

• 涉及表：學生表

• 要顯示的欄位：學生表中所有欄位

• 條件：學生姓名中有"小"字

• 編寫如下sql：

　　　　select * from student where name like ‘%小%‘;

 

注意：

%在不同位置所代表的意義  %值  值%  %值%

%A：以任一字元開頭，以A結尾的字串

A%：以A開頭，以任一字元結尾的字串

%A%：包含了A的任一字元串

 

練習：

1. 查詢科目表中，科目名稱有"語"的科目資訊

三. 查詢—統計(COUNT(*))

關鍵詞：count(*)

文法：select count(*) from <表名> where 條件運算式;

 

1. 統計一個表裡總共有多少條記錄

舉例：查詢學生表總共有多少學生

select count(*) from student;

2. 統計滿足某一條件的記錄數

舉例：查詢學生表的女生數量

select count(*) from student where sex=‘女‘;

3. 統計高一年級下共有多少學生

select count(*) from grade t1, class t2, student t3 where t3.class_id = t2.id and t2.grade_id=t1.id and t1.id=(select id from grade where name=‘高一年級‘);

 

select count(*) from student where class_id in (select t1.id from class t1, grade t2 where t1.grade_id=t2.id and t2.name=‘高一年級‘);

 

select count(*) from grade t1, class t2, student t3 where t3.class.class_id=t2.id and t2.grade_id=t1.id and t1.name=‘高一年級‘;

四. 查詢—分組(GROUP BY)

根據一個或多個列對結果集進行分組

文法：select 欄位1, 欄位2, 統計函數xx() from <表名> group by 欄位1, 欄位2

select

樣本：請按性別分組，統計學生表裡男生和女生各有多少人

 

分析：

涉及表：student

查詢欄位：sex, count(*)

 

selct sex, count(*) from student group by sex;

五. MySQL—BETWEEN的用法

找出score表成績在80和90之間的學生(包含邊界值80和90)

select * from score where score between 80 and 90;

六. MySQL分頁

文法：limit m,n;

m指的索引值是從m開始，n表示每頁要取多少條

假如每頁取十條展示，則第一頁為：limit 0,10表示取索引從0開始取10條記錄，第二頁為：limit 10,10 表示取索引從10開始取10條記錄，第三頁為：limit 20,10 表示索引從20開始取10條記錄

1) 請用sql查詢出student表的前10條記錄

2) 請用sql查出student表的第10到15條記錄

七. 常見MySQL函數

舉例mysql中常用的sql函數：

 

數值相關函數：

求欄位A的最小值：min(欄位A)

求欄位A的最大值：max(欄位A)

求欄位A的平均值：avg(欄位A)

求欄位A的和：sum(欄位A)

 

日期函數：

擷取系統當前日期時間：sysdate()

擷取系統當前日期：curdate()

擷取系統目前時間：curtime()

擷取date是一個月的第幾天：dayofmonth(date)

擷取當前月的最後一天：last_day(date)

為日期增加一個時間間隔：DATE_ADD(date, INTERVAL expr unit)，例如在當前日期上加一天：select DATE_ADD(CURDATE(), INTERVAL 1 day);

擷取當前月第一天：select date_add(curdate(), interval -day(curdate())+1 day);

字串函數：

字串拼接函數：concat(欄位A, 欄位B)， SUBSTR(欄位A, 截取開始的位置，截取字元個數), length(欄位A)

 

八. 課後作業

1. 查詢出"高一年級"下面的所有班級裡面的男學生資訊；

 

select t3.* from grade t1,class t2,student t3 where t1.id=t2.grade_id and t2.id=t3.class_id and t1.name=‘高一年級‘ and t3.sex=‘男‘;

 

2. 查詢成績小於等於90分的學生姓名、性別、科目名稱、分數(涉及表student、course、score)

select t1.name,t1.sex,t2.name,t3.score from student t1,course t2,score t3 where t1.id=t3.student_id and t2.id=t3.course_id and t3.score between 0 and 90;

3. 查詢高二年級下所有數學成績小於90分的同學的學號和姓名以及分數

九. 常見面試題

1. 圖書(圖書號，圖書名，作者編號，出版社，出版日期) 作者(作者姓名，作者編號，年齡，性別) 用SQL語句查詢年齡小於平均年齡的作者姓名、圖書名、出版社

2. 作者表：authors

作者編號：authorId  int(11)

作者姓名：authorName  varchar(50)

性別：sex  varchar(2)

年齡：age  int

居住城市：city  varchar(50)

聯絡電話：telephone  varchar(11)

銷量：sales  int(11)

最新出版日期：jsbn  datetime

1) 查詢姓張的作者資訊

2) 查詢聯絡電話第三位為8,9並以888結尾的作者資訊

3) 查詢年齡在20-50之間的男性作者資訊

4) 查詢顯示作者姓名的第二個字元

5) 查詢顯示作者姓名的長度

6) 查詢顯示最年輕的5位作者的平均銷量

7) 查詢顯示作者的姓名，出生年份，銷量，並按銷量降序排列

8) 查詢顯示最新出版日期在今年前半年的作者資訊

 

mysql資料庫(三)：查詢的其他用法