先準備測試數據,代碼如下:
-- 創建數據庫
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;-- 創建student表
CREATE TABLE student (sid CHAR(6),sname VARCHAR(50),age INT,gender VARCHAR(50) DEFAULT 'male'
);-- 向student表插入數據
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');
1.簡單查詢概述
簡單查詢即不含where的select語句。在此,我們講解簡單查詢中最常用的兩種查詢:查詢所有字段和查詢指定字段。
查詢所有字段(方法不唯一只是舉例)
select * from student;
2.查詢指定字段(sid、sname)
MySQL命令:
select sid,sname from student;
3.常數的查詢
在SELECT中除了書寫列名,還可以書寫常數。
可以用于標記
常數的查詢日期標記 MySQL命令:select sid,sname,'2021-03-02' from student;
4.從查詢結果中過濾重復數據
在使用DISTINCT 時需要注意:
在SELECT查詢語句中DISTINCT關鍵字只能用在第一個所查列名之前。
MySQL命令:
select distinct gender from student;
5.算術運算符(舉例加運算符)
在SELECT查詢語句中還可以使用加減乘除運算符。
查詢學生10年后的年齡 MySQL命令:
select sname,age+10 from student;
函數:
1.聚合函數
在開發中,我們常常有類似的需求:統計某個字段的最大值、最小值、 平均值等等。為此,MySQL中提供了聚合函數來實現這些功能。所謂聚合,就是將多行匯總成一行;其實,所有的聚合函數均如此——輸入多行,輸出一行。聚合函數具有自動濾空的功能,若某一個值為NULL,那么會自動將其過濾使其不參與運算。
聚合函數使用規則:
只有SELECT子句和HAVING子句、ORDER BY子句中能夠使用聚合函數。例如,在WHERE子句中使用聚合函數是錯誤的。
接下來,我們學習常用聚合函數。
1.1、count()
統計表中數據的行數或者統計指定列其值不為NULL的數據個數
查詢有多少該表中有多少人
MySQL命令:
select count(*) from student;
1.2、max()
計算指定列的最大值,如果指定列是字符串類型則使用字符串排序運算
查詢該學生表中年紀最大的學生
MySQL命令:
select max(age) from student;
1.3、min()
計算指定列的最小值,如果指定列是字符串類型則使用字符串排序運算
查詢該學生表中年紀最小的學生 MySQL命令:
select sname,min(age) from student;
1.4 sum()
計算指定列的數值和,如果指定列類型不是數值類型則計算結果為0
查詢該學生表中年紀的總和 MySQL命令:
select sum(age) from student;
1.5、avg()
計算指定列的平均值,如果指定列類型不是數值類型則計算結果為
查詢該學生表中年紀的平均數 MySQL命令:
select avg(age) from student;
條件查詢
數據庫中存有大量數據,我們可根據需求獲取指定的數據。此時,我們可在查詢語句中通過WHERE子句指定查詢條件對查詢結果進行過濾。
在開始學習條件查詢之前,我們先準備測試數據,代碼如下:
-- 創建數據庫
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;-- 創建student表
CREATE TABLE student (sid CHAR(6),sname VARCHAR(50),age INT,gender VARCHAR(50) DEFAULT 'male'
);-- 向student表插入數據
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1012', 'lili', 14, 'male');
INSERT INTO student (sid,sname,age,gender) VALUES ('S_1013', 'wang', 15, 'female');
1.使用關系運算符查詢
在WHERE中可使用關系運算符進行條件查詢,常用的關系運算符如下所示:
查詢年齡等于或大于17的學生的信息 MySQL命令:
select * from student where age>=17;
2.使用IN關鍵字查詢
IN關鍵字用于判斷某個字段的值是否在指定集合中。如果字段的值恰好在指定的集合中,則將字段所在的記錄將査詢出來。
查詢sid為S_1002和S_1003的學生信息 MySQL命令:
select * from student where sid in ('S_1002','S_1003');
查詢sid為S_1001以外的學生的信息 MySQL命令:
select * from student where sid not in ('S_1001');
3.使用BETWEEN AND關鍵字查詢
BETWEEN AND用于判斷某個字段的值是否在指定的范圍之內。如果字段的值在指定范圍內,則將所在的記錄將查詢出來
查詢15到18歲的學生信息 MySQL命令:
select * from student where age between 15 and 18;
查詢不是15到18歲的學生信息 MySQL命令:
select * from student where age not between 15 and 18;
4.使用空值查詢
在MySQL中,使用 IS NULL關鍵字判斷字段的值是否為空值。請注意:空值NULL不同于0,也不同于空字符串
由于student表沒有空值就不演示查詢空值的了
查詢sname不為空值的學生信息 MySQL命令:
select * from student where sname is not null;
5.使用AND關鍵字查詢
在MySQL中可使用AND關鍵字可以連接兩個或者多個查詢條件。
查詢年紀大于15且性別為male的學生信息 MySQL命令:
select * from student where age>15 and gender='male';
6.使用OR關鍵字查詢
在使用SELECT語句查詢數據時可使用OR關鍵字連接多個査詢條件。在使用OR關鍵字時,只要記錄滿足其中任意一個條件就會被查詢出來
查詢年紀大于15或者性別為male的學生信息 MySQL命令:
select * from student where age>15 or gender='male';
7.使用LIKE關鍵字查詢
MySQL中可使用LIKE關鍵字可以判斷兩個字符串是否相匹配
7.1 普通字符串
查詢sname中與wang匹配的學生信息 MySQL命令:
select * from student where sname like 'wang';
7.2 含有%通配的字符串
%用于匹配任意長度的字符串。例如,字符串“a%”匹配以字符a開始任意長度的字符串
查詢學生姓名以li開始的記錄 MySQL命令:
select * from student where sname like 'li%';
運行效果展示:
查詢學生姓名以g結尾的記錄 MySQL命令:
select * from student where sname like '%g';
運行效果展示:
查詢學生姓名包含s的記錄 MySQL命令:
select * from student where sname like '%s%';
運行效果展示
7.3 含有_通配的字符串
下劃線通配符只匹配單個字符,如果要匹配多個字符,需要連續使用多個下劃線通配符。例如,字符串“ab_”匹配以字符串“ab”開始長度為3的字符串,如abc、abp等等;字符串“a__d”匹配在字符“a”和“d”之間包含兩個字符的字符串,如"abcd"、"atud"等等。
查詢學生姓名以zx開頭且長度為4的記錄 MySQL命令:
select * from student where sname like 'zx__';
運行效果展示
查詢學生姓名以g結尾且長度為4的記錄 MySQL命令:
select * from student where sname like '___g';
運行效果展示
8.使用LIMIT限制查詢結果的數量
當執行查詢數據時可能會返回很多條記錄,而用戶需要的數據可能只是其中的一條或者幾條
查詢學生表中年紀最小的3位同學 MySQL命令:
select * from student order by age asc limit 3;
運行效果展示
9.使用GROUP BY進行分組查詢
GROUP BY 子句可像切蛋糕一樣將表中的數據進行分組,再進行查詢等操作。換言之,可通俗地理解為:通過GROUP BY將原來的表拆分成了幾張小表。
接下來,我們通過一個例子開始學習GROUP BY,代碼如下
-- 創建數據庫
DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;
USE mydb;-- 創建員工表
CREATE TABLE employee (id int,name varchar(50),salary int,departmentnumber int
);-- 向員工表中插入數據
INSERT INTO employee values(1,'tome',2000,1001);
INSERT INTO employee values(2,'lucy',9000,1002);
INSERT INTO employee values(3,'joke',5000,1003);
INSERT INTO employee values(4,'wang',3000,1004);
INSERT INTO employee values(5,'chen',3000,1001);
INSERT INTO employee values(6,'yukt',7000,1002);
INSERT INTO employee values(7,'rett',6000,1003);
INSERT INTO employee values(8,'mujk',4000,1004);
INSERT INTO employee values(9,'poik',3000,1001);
9.1 GROUP BY和聚合函數一起使用
統計各部門員工個數 MySQL命令:
select count(*), departmentnumber from employee group by departmentnumber;
運行效果展示
統計部門編號大于1001的各部門員工個數 MySQL命令:
select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;
運行效果展示
9.2 GROUP BY和聚合函數以及HAVING一起使用
統計工資總和大于8000的部門 MySQL命令:
select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;
運行效果展示
10.使用ORDER BY對查詢結果排序
從表中査詢出來的數據可能是無序的或者其排列順序不是我們期望的。為此,我們可以使用ORDER BY對查詢結果進行排序
其語法格式如下所示:
SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名1 [ASC 丨 DESC],字段名2 [ASC | DESC];
在該語法中:字段名1、字段名2是查詢結果排序的依據;參數 ASC表示按照升序排序,DESC表示按照降序排序;默認情況下,按照ASC方式排序。通常情況下,ORDER BY子句位于整個SELECT語句的末尾。
查詢所有學生并按照年紀大小升序排列 MySQL命令:
select * from student order by age asc;
運行效果展示
查詢所有學生并按照年紀大小降序排列 MySQL命令:
select * from student order by age desc;
運行效果展示
別名設置
在査詢數據時可為表和字段取別名,該別名代替表和字段的原名參與查詢操作。
操作的表事先已準備
1.為表取別名
在查詢操作時,假若表名很長使用起來就不太方便,此時可為表取一個別名,用該別名來代替表的名稱。語法格式如下所示:
SELECT * FROM 表名 [AS] 表的別名 WHERE .... ;
將student改為stu查詢整表 MySQL命令:
select * from student as stu;
運行效果展示
2.為字段取別名
在查詢操作時,假若字段名很長使用起來就不太方便,此時可該字段取一個別名,用該別名來代替字段的名稱。語法格式如下所示:
SELECT 字段名1 [AS] 別名1 , 字段名2 [AS] 別名2 , ... FROM 表名 WHERE ... ;
將student中的name取別名為“姓名” 查詢整表 MySQL命令:
select name as '姓名',id from student;
運行效果展示