MySQL查詢數(shù)據(jù)(單表查詢)詳解
1.單表查詢
語法:select {*| <字段列表> } from <表1>,<表2>... where <表達式> [GROUP BY] [HAVING] [ORDER BY] [LIMIT]
創(chuàng)建數(shù)據(jù)庫,準備表及數(shù)據(jù)
/*創(chuàng)建數(shù)據(jù)庫*/ create database if not EXISTS students character set utf8 collate utf8_general_ci; /*創(chuàng)建表*/ use students; create table if not EXISTS student ( stuID int(5) not null primary key, stuName varchar(50) not null, stuSex CHAR(10), stuAge smallint ); CREATE TABLE if not EXISTS courses( couID int not null primary key auto_increment COMMENT '學號', couName varchar(50) not null DEFAULT('大學英語'), couHours smallint UNSIGNED COMMENT '學時', couCredit float DEFAULT(2) COMMENT '學分' )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_0900_ai_ci; CREATE TABLE if not EXISTS stu_cou( ID int not null primary key auto_increment, stuID int(5) not null COMMENT '學號', couID int not null COMMENT '課程編號', time timestamp not null DEFAULT(now()) ); /*添加外鍵約束*/ alter table stu_cou add CONSTRAINT fk_stuid foreign key(stuID) REFERENCES student(stuID) ON UPDATE CASCADE ON DELETE CASCADE ; alter table stu_cou add CONSTRAINT fk_couid foreign key(couID) REFERENCES courses(couID) ON UPDATE CASCADE ON DELETE CASCADE ; /*插入數(shù)據(jù)*/ insert into student(stuID,stuName,stuSex,stuAge) values(1001,'張三','男',19),(1002,'李四','男',18),(1003,'王五','男',18),(1004,'黃麗麗','女',18),(1005,'李曉輝','女',19),(1006,'張敏','女',18); insert into student VALUES(1007,'五條人','男',20),(1008,'胡五伍','女',19); insert into courses(couID,couName,couHours,couCredit) values(50,'大學英語',64,2),(60,'計算機基礎',78,2.5),(70,'Java程序設計',108,6),(80,'數(shù)據(jù)庫應用',48,2.5); insert into stu_cou(stuID,couID) values(1001,50),(1001,60),(1001,70),(1001,80),(1002,50),(1002,60),(1002,70),(1002,80),(1003,50),(1003,60),(1003,70),(1003,80),(1004,50),(1004,60),(1004,70),(1004,80),(1005,50),(1005,60),(1005,70),(1005,80),(1006,50),(1006,60),(1006,70),(1006,80);
(1)簡單查詢
例1:查詢student表中所有數(shù)據(jù)。
use students; select * from student;
例2:查詢指定列的數(shù)據(jù),查詢student表中stuID,stuName兩列。
select stuID,stuName from student;
例3:給列指定別名,查詢student表中stuID列,指定別名為學號,stuName列,指定別名為姓名。
select stuID as '學號',stuName as '姓名' from student;
修改字段別名的語法:as+'名稱'
(2)條件查詢
條件:where 運算符
操作符 | 含義 | 范圍 | 結果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或者 != | 不等于 | 5<>6 | true |
> | 大于 | 5>6 | false |
< | 小于 | 5<6 | true |
>= | 大于等于 | 5>=6 | false |
<= | 小于等于 | 5<=6 | true |
between A and B | 在A和B之間 | between 1 and 10 | 在1~10之間,包括邊界值,相當于>=1 and <=10 |
not between A and B | 不在A和B之間 | not between 1 and 10 | 不在1~10之間,包括邊界值,相當于>=1 or <=10 |
AND | 連接條件&& | 條件1 和條件2都成立 | 都是true 才是true |
OR | 或者|| | 條件1 和條件2有一個成立即可 | 有一個true才是true |
IN | 以列表項的形式支持多個選擇 | IN (value1,value2,…)或者 NOT IN (value1,value2,…) | 與or操作符結果類似 |
例4:查詢student表中stuID為1001的記錄。
select * from student where stuID=1001;
例5:查詢student表中性別為男且年齡小于19歲的記錄。
select * from student where stuSex='男' and stuAge<19;
例6:查詢student表中性別為女或年齡等于18歲的記錄。
select * from student where stuSex='女' or stuAge=18;
例7:查詢student表中學號為1001和1004的記錄。
SELECT * from student where stuID=1001 or stuID=1004; SELECT * from student where stuID in(1001,1004);
例8:查詢student表中學號為1001到1004的記錄。
SELECT * from student where stuID BETWEEN 1001 and 1004;
例9:查詢student表中學號不包含1001到1004的記錄。
SELECT * from student where stuID not BETWEEN 1001 and 1004;
(3)模糊查詢
使用like關鍵字,通配符為
通配符 | 描述 |
百分號(%) | 替代0個、1個或多個字符 |
下劃線(_) | 僅替代一個字符 |
例9:查詢student表中所有姓張的同學的信息。
SELECT * from student where stuName like '張%';
例10:查詢student表中姓名包含“麗”字的同學的信息。
SELECT * from student where stuName like '%麗%';
例11:分別查詢student表中姓名包含“五”字的同學的信息。
SELECT * from student where stuName like '%五%';
'%五%'可以替代'五%'、'%五'兩種情況。
(4)排序
排序的關鍵字是order by 字段名稱,不使用where關鍵字,asc表示升序,desc表示降序,默認不寫就是升序排列。
例12:查詢student表中的記錄,根據(jù)年齡進行升序排列,降序排列。
SELECT * from student ORDER BY stuAge asc; SELECT * from student ORDER BY stuAge desc;
例13:查詢student表中的記錄,根據(jù)年齡進行升序排列,姓名進行降序排列。
SELECT * from student ORDER BY stuAge asc,stuName desc;
上述根據(jù)stuName字段排序的時候,字段內(nèi)容是漢字,所以排序失效了,解決辦法如下:
方法一:使用convert方法轉換字段的字符集為gbk。
select * from student ORDER BY stuAge asc,CONVERT(stuName using gbk) desc;
方法二:修改字段的字符集為gbk。
修改后運行結果為:
注意:在對多列進行排序的時候,首先第一列必須有相同的列值,才會對第二列進行排序呢。如果第一列都是唯一的值,將不會對第二列進行排序。
(5)聚合函數(shù)
常用的聚合函數(shù)
例14:使用聚合函數(shù)查詢學生總人數(shù)。
SELECT COUNT(*) as '學生總人數(shù)' from student; SELECT COUNT(stuID) as '學生總人數(shù)' from student;
例15:使用聚合函數(shù)查詢課程表中所有課程的學分和。
SELECT sum(couCredit) as '總學分' from courses;
例16:分別使用聚合函數(shù)查詢年齡最大的學生和年齡最小的學生的年齡。
SELECT max(stuAge) as '年齡最大的同學' from student; SELECT min(stuAge) as '年齡最小的同學' from student;
例17:使用聚合函數(shù)查詢課程表中所有課程的平均學時數(shù)。
SELECT avg(couHours) as '四門課程平均學時數(shù)' from courses;
(6)分組查詢
修改原數(shù)據(jù)庫和表,將courses表增加一列名為grade的成績字段,并插入隨機值,運行一下命令。
alter table stu_cou add COLUMN grade FLOAT null; UPDATE stu_cou set grade=(SELECT FLOOR(50 +RAND() * 50));
如何生成指定范圍的隨機數(shù)
生成0-10的隨機數(shù) SELECT FLOOR(RAND() * 10) 生成10-100的隨機數(shù) SELECT FLOOR(10 +RAND() * 90)
語法:
GROUP BY 字段名 [HAVING 條件表達式]
參數(shù):
1、字段名:是指按照該字段的值進行分組(分組是所依據(jù)的列名稱)
2、HAVING條件表達式:用來限制分組后的顯示,符合條件表達式的結果將被顯示
核心思想:在查詢SQL中指定分組的列名,然后根據(jù)該列的值(內(nèi)容)進行分組,值相等的為一組。
注意:group by通常與聚合函數(shù)一起結合使用。
例18:使用分組查詢輸出學生性別。
SELECT stuSex as '性別' from student GROUP BY stuSex;
例19:使用分組查詢輸出學生學號和姓名。
SELECT stuID,stuName from student GROUP BY stuID;
以上兩個例子中,group by后面的字段名稱必須是select關鍵字后面出現(xiàn)的字段名稱,除以主鍵字段分組以外,其他的任意兩個或者兩個以上的非主鍵字段無法使用group by進行分組,如:
SELECT stuName,stuSex from student GROUP BY stuSex;
錯誤提示如下:
1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'students.student.stuName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
以上兩個例子中group by單獨使用時,只顯示出每一組的第一條記錄,所以group by單獨使用時的實際意義不大,因此,一般在使用集合函數(shù)時才使用GROUP BY關鍵字。
例20:分組查詢學生表中男女同學的人數(shù)。
select stuSex,COUNT(stuID) as '人數(shù)' from student GROUP BY stuSex;
例21:分組查詢學生表中男女同學的平均年齡,人數(shù),男女同學年齡最大值,年齡最小值。
select stuSex,AVG(stuAge) as '平均年齡',COUNT(stuID) as '人數(shù)',MAX(stuAge) as '最大年齡',min(stuAge) as '最小年齡' from student GROUP BY stuSex;
注意:從以上兩個例子可知,除聚合函數(shù)之外,SELECT語句中的每個列都必須在GROUP BY子句中給出。如group by 中的stuSex列在select后必須出現(xiàn)。
例22:分組查詢學生表中男女同學各年齡段的人數(shù)。
select stuSex,stuAge,COUNT(stuID) as '人數(shù)' from student GROUP BY stuSex,stuAge;
例22是使用GROUP BY可以對多個字段進行分組,GROUP BY關鍵字后面跟需要分組的字段。
注意:(1)MYSQL根據(jù)多字段的值來進行層次分組,分組層次從左到右
(2)即先按第一個字段分組,然后在第一個字段值相同的記錄中,再根據(jù)第二個字段的值進行分組,以此類推。
多字段分組再舉一個例子,修改student表增加stuColleage字段表示學院,并插入值。
alter table student add COLUMN stuColleage varchar(100) null; update student set stuColleage='大數(shù)據(jù)學院' where stuID BETWEEN 1001 and 1003; update student set stuColleage='物流學院' where stuID BETWEEN 1004 and 1006; update student set stuColleage='康養(yǎng)學院' where stuID BETWEEN 1007 and 1008;
分組查詢學生表中各學院男女同學的人數(shù)。
select stuColleage,stuSex,COUNT(stuID) as '人數(shù)' from student GROUP BY stuColleage,stuSex;
先按照stuColleage分組,再按照stuSex字段分組,返回第一個分組字段stuColleage的第一個字段值后,實際與第二個分組字段stuSex返回的值(可能是多個)進行組合,成為新的記錄行。
如“康養(yǎng)學院”+“男”,“康養(yǎng)學院”+“女”,而大數(shù)據(jù)學院沒有stuSex值為“女”的記錄,物流學院沒有stuSex值為“男”的記錄,所以均只有一條記錄。
若增加一個學生住址字段stuAddress,以stuAddress字段作為第二個分組的字段,則stuAddress可能會返回幾十條不同信息與第一個分組的字段進行組合。
GROUP BY關鍵字可以和GROUP_CONCAT()函數(shù)一起使用,GROUP_CONCAT()函數(shù)會把每個分組的字段值都顯示出來。
例23:分組查詢學生表中各年齡段的學生姓名及人數(shù)。
select stuAge,GROUP_CONCAT(stuName) as '姓名列表',COUNT(stuID) as '人數(shù)' from student GROUP BY stuAge;
例24:按照學號進行分組,查詢選課表中各科目課程號,總成績及平均成績。
select stuID,GROUP_CONCAT(couID) as '課程號',sum(grade) as '總成績',avg(grade) as '平均分' from stu_cou GROUP BY stuID;
使用HAVING過濾分組,having會將分組后的數(shù)據(jù)按照分組條件進一步篩選。
例24:按照學號進行分組,查詢選課表中各科目課程號,總成績及平均成績大于75分的記錄。
select stuID,GROUP_CONCAT(couID) as '課程號',sum(grade) as '總成績',avg(grade) as '平均分' from stu_cou GROUP BY stuID HAVING avg(grade)>75;
GROUP BY子句允許添加WITH ROLLUP修飾符,該修飾符可以對分組后各組的某個列的結果值進行匯總,并在結果中輸出,即提供更高一級的聚合操作。(參考:http://www.dbjr.com.cn/article/90130.htm)
例24:按照性別進行分組,查詢學生表中男女同學的人數(shù)及總人數(shù)。
select stuSex,COUNT(stuID) as '人數(shù)' from student GROUP BY stuSex with rollup;
stuSex字段下的null代表高級別的聚合行。
(6)使用LIMIT限制查詢結果的數(shù)量
從某個值開始,取出之后的N條數(shù)據(jù),有兩種形式的用法:(http://www.dbjr.com.cn/article/226348.htm)
1.limit a,b 后綴兩個參數(shù)的時候(/*參數(shù)必須是一個整數(shù)常量*/),其中a是指記錄開始的偏移量,b是指從第a+1條開始,取b條記錄。
2.limit b 后綴一個參數(shù)的時候,是直接取值到第多少位,類似于:limit 0,b 。
例25:查詢學生表中從第2條記錄開始的3條記錄。
select * from student LIMIT 1,3;
例25:查詢學生表中的前4條記錄。
select * from student LIMIT 4;/*limit 0,4*/
(7)分頁查詢
http://www.dbjr.com.cn/article/248328.htm
(8)使用distinct去除重復記錄
select DISTINCT stuSex from student;
參考文章:
https://blog.csdn.net/ccx_nc/article/details/120922391
https://blog.csdn.net/DianLanHong/article/details/119788185 MySQL where in 用法詳解
https://www.cnblogs.com/yangjianbo/articles/15739877.html
https://blog.csdn.net/weixin_42352842/article/details/113635581
https://blog.csdn.net/Hudas/article/details/124370701 [Mysql] LIKE與通配符
https://www.cnblogs.com/bigbigbigo/p/10953037.html MySQL 聚合函數(shù)(二)Group By的修飾符——ROLLUP
到此這篇關于MySQL查詢數(shù)據(jù)(單表查詢)的文章就介紹到這了,更多相關mysql 查詢數(shù)據(jù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL gh-ost DDL 變更工具的實現(xiàn)
本文主要介紹了MySQL gh-ost DDL變更工具的實現(xiàn),文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2025-02-02Mysql中STR_TO_DATE函數(shù)使用(字符串轉為日期/時間值)
這篇文章主要給大家介紹了關于Mysql中STR_TO_DATE函數(shù)使用的相關資料,STR_TO_DATE函數(shù)的主要功能是字符串轉為日期/時間值,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下2022-09-09淺談MySQL安裝starting the server失敗的解決辦法
如果電腦是不是第一次安裝MySQL,一般會出現(xiàn)報錯情況,starting the server失敗,通常是因為上次安裝的該軟件未清除干凈,本文就詳細的介紹一下解決方法,感興趣的可以了解一下2021-09-09MYSQL插入數(shù)據(jù)時檢查字段值是否重復的方法詳解
這篇文章主要給大家介紹了關于MYSQL插入數(shù)據(jù)時檢查字段值是否重復的相關資料,文中通過實例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2022-01-01與MSSQL對比學習MYSQL的心得(四)--BLOB數(shù)據(jù)類型
在MYSQL中BLOB是一個二進制大對象,用來儲存可變數(shù)量的數(shù)據(jù),而MSSQL中并沒有BLOB數(shù)據(jù)類型,只有大型對象數(shù)據(jù)類型(LOB)2014-06-06