一、基礎數據查詢 select語句用於從表中選取數據，結果被存儲在一個結果表中（稱為結果集）。 語法：select * from 表名稱 #查詢指定表中的所有數據 *為模糊匹配所有列 例： mysql> select * from person; + + + + + + + | id | name ...
mysql> select * from person; +------+--------+------+------------+--------+---------------------+ | id | name | age | salary | gender | regTime | +------+--------+------+------------+--------+---------------------+ | 3 | 蓋倫 | 78 | 2637363.45 | 0 | 2023-06-28 15:31:31 | | 4 | 艾希 | 25 | 900.00 | 0 | 2023-06-28 16:15:16 | | 5 | 瑞茲 | 25 | 900.00 | 0 | 2023-06-28 16:16:11 | +------+--------+------+------------+--------+---------------------+ 3 rows in set (0.00 sec)
mysql> select name,age, salary from person; +--------+------+------------+ | name | age | salary | +--------+------+------------+ | 蓋倫 | 78 | 2637363.45 | | 艾希 | 25 | 900.00 | | 瑞茲 | 25 | 900.00 | +--------+------+------------+
二、有條件的數據查詢
將where 關鍵加入到select語句中，可以進行有條件的數據查詢
1、條件查詢
select 列名稱 from 表名稱 where 列 運算符 值
例1：select * from person where id = 4;
例2：select * from person where id != 4;
例3：select * from person where age < 74;
例4：select * from person where age between 78 and 89;
例5：select * from person where age >=78 and age <= 89;
例6：select * from person where age >=78 or age <= 89;
2、模糊查詢
使用關鍵字like和%、_進行模糊查詢，%和_是占位符，%表示任意位字元，_表示1位字元。
例1：select * from person where name like "%袁%"; 查詢name例中的字元串中有“袁”這個字元的行的數據
例2：select * from person where name like "袁%"; 查詢name例中的字元串中以“袁”這個字元開頭的行的數據
例3：select * from person where name like "%袁"; 查詢name例中的字元串中以“袁”這個字元結尾的行的數據
例4：select * from person where name like "_袁%"; 查詢name例中的字元串中第二個字元是“袁”的行的數據
例5：select * from person where name like "袁_"; 查詢name例中的字元串中有兩位，且以“袁”這個字元開頭的行的數據
3、排序
order by關鍵字用於根據指定的列對結果集進行排序。 asc為預設，按升序排序；desc為降序。
例1：select * from person order by salary; #按照薪資進行排序 預設的是升序排
例2：select * from person order by salary asc; # asc 的話是預設升序的 是可以省略的
例3：select * from person order by salary desc; #按照薪資來排序，按照降序來排
例4：select * from person where age < 40 order by salary desc; #找出來年齡小於40歲的數據，然後降序來排
例5：select * from person order by salary asc, age desc; #先按照薪資排升序排，如果薪資一樣再按照年齡降序排
例6、mysql> select * from person where age < 40 order by salary asc, age desc; #先按照薪資排序，如果薪資一樣再按照年齡降序排，找出來年齡小於40歲的
#註：order by 只能跟在where的後面，不能放在where的前面，因為order by是對查詢出的結果集進行排序，所以只能先根據條件查詢出結果集，後進行排序。
4、limit關鍵字 限制輸出
語法：limit n 查詢數據輸出n條
limit m，n 從第m+1條開始，輸出n條
例：
mysql> select * from person; +------+--------+------+------------+--------+---------------------+ | id | name | age | salary | gender | regTime | +------+--------+------+------------+--------+---------------------+ | 3 | 蓋倫 | 78 | 2637363.45 | 0 | 2023-06-28 15:31:31 | | 4 | 艾希 | 25 | 90.00 | 0 | 2023-06-28 16:15:16 | | 5 | 瑞茲 | 22 | 900.00 | 0 | 2023-06-28 16:16:11 | | 6 | 潘森 | 56 | 8292.00 | 1 | 2023-06-28 17:01:13 | | 7 | 婕拉 | 29 | 289229.00 | 1 | 2023-06-29 09:36:16 | | 8 | 亞索 | 98 | 27783.00 | 1 | 2023-06-29 09:37:48 | | 9 | 提莫 | 29 | 900.00 | 0 | 2023-06-29 09:47:02 | | 10 | 努努 | 25 | 900.00 | 1 | 2023-06-29 09:47:57 | +------+--------+------+------------+--------+---------------------+ 8 rows in set (0.00 sec) #前三條 mysql> select * from person limit 3; +------+--------+------+------------+--------+---------------------+ | id | name | age | salary | gender | regTime | +------+--------+------+------------+--------+---------------------+ | 3 | 蓋倫 | 78 | 2637363.45 | 0 | 2023-06-28 15:31:31 | | 4 | 艾希 | 25 | 90.00 | 0 | 2023-06-28 16:15:16 | | 5 | 瑞茲 | 22 | 900.00 | 0 | 2023-06-28 16:16:11 | +------+--------+------+------------+--------+---------------------+ mysql> select * from person limit 0,3; +------+--------+------+------------+--------+---------------------+ | id | name | age | salary | gender | regTime | +------+--------+------+------------+--------+---------------------+ | 3 | 蓋倫 | 78 | 2637363.45 | 0 | 2023-06-28 15:31:31 | | 4 | 艾希 | 25 | 90.00 | 0 | 2023-06-28 16:15:16 | | 5 | 瑞茲 | 22 | 900.00 | 0 | 2023-06-28 16:16:11 | +------+--------+------+------------+--------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from person limit 3,3; +------+--------+------+-----------+--------+---------------------+ | id | name | age | salary | gender | regTime | +------+--------+------+-----------+--------+---------------------+ | 6 | 潘森 | 56 | 8292.00 | 1 | 2023-06-28 17:01:13 | | 7 | 婕拉 | 29 | 289229.00 | 1 | 2023-06-29 09:36:16 | | 8 | 亞索 | 98 | 27783.00 | 1 | 2023-06-29 09:37:48 | +------+--------+------+-----------+--------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from person limit 6,3; +------+--------+------+--------+--------+---------------------+ | id | name | age | salary | gender | regTime | +------+--------+------+--------+--------+---------------------+ | 9 | 提莫 | 29 | 900.00 | 0 | 2023-06-29 09:47:02 | | 10 | 努努 | 25 | 900.00 | 1 | 2023-06-29 09:47:57 | +------+--------+------+--------+--------+---------------------+ 2 rows in set (0.00 sec)
5、SQL的內建函數
語法：select sql函數 from person;
例1：select max(age) from person; #獲取年齡這一列的最大值
例2：select min(age) from person; #獲取年齡這一列最小值
例3：select avg(salary) from person; #求薪資這一類數據的平均值
例4：select sum(salary) from person; #求薪資這一列數據的和
例5：select count(*) from person; #用來統計資料庫裡面有多少條數據的 count(*)
6、嵌套SQL
將一個SQL語句結果當成另外一個SQL語句條件來使用，開發有這種寫法，但因效率太低，很少使用。
例：查詢年齡最大的英雄的全部信息
select max(age) from person; #結果為98 select * from person where age = 98;
可嵌套寫為：select * from person where age = (select max(age) from person);
7、分組統計
group by語句用於結合合計函數，根據一個或多個列對結果集進行分組。
例：
#按照性別進行分組統計 #select和from中間是查詢的欄位，如果用了group by 只能寫 #當前分組的欄位或者是SQL內置函數得到的結果 mysql> select gender from person group by gender; +--------+ | gender | +--------+ | 0 | | 1 | +--------+ #分組以後進行統計，統計出來 0 有幾個數據 1 有幾個數據 mysql> select gender, count(*) from person group by gender; +--------+----------+ | gender | count(*) | +--------+----------+ | 0 | 5 | | 1 | 4 | +--------+----------+ #按照性別進行分組，分組以後統計個數大於4的性別數 #註意事項：分組以後的條件語句用不能用where，要使用having關鍵字 #WHERE 關鍵字無法與合計函數一起使用。 mysql> select gender,count(*) from person group by gender having count(*) > 4; +--------+----------+ | gender | count(*) | +--------+----------+ | 0 | 5 | +--------+----------+ #找出來年齡小於30歲的信息，然後按照性別分組統計，統計出來的結果的個數大於2的數據 #先找年齡小於30歲的 mysql> select gender, count(*) #查什麼 -> from person #從哪查 -> where age < 30 #查詢條件是啥 -> group by gender #分組 -> having count(*) > 2; #分組之後的條件 +--------+----------+ | gender | count(*) | +--------+----------+ | 0 | 3 | +--------+----------+ 1 row in set (0.00 sec))
8、去重
關鍵字：distinct
語法：select distinct 列名稱 from 表名稱
例：select name from student; #結果：張三 李四 張三 趙二 王五 李四
select distinct from student #結果：張三 李四 趙二 王五
