當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql 单表查询

發(fā)布時間：2025/3/15 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql 单表查询小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

一單表查詢的語法

SELECT 字段1,字段2... FROM 表名WHERE 條件GROUP BY fieldHAVING 篩選ORDER BY fieldLIMIT 限制條數(shù)

二關(guān)鍵字的執(zhí)行優(yōu)先級(重點)

重點中的重點：關(guān)鍵字的執(zhí)行優(yōu)先級 from where group by having select distinct order by limit

1.找到表:from

2.拿著where指定的約束條件，去文件/表中取出一條條記錄

3.將取出的一條條記錄進(jìn)行分組group by，如果沒有g(shù)roup by，則整體作為一組

4.將分組的結(jié)果進(jìn)行having過濾

5.執(zhí)行select

6.去重

7.將結(jié)果按條件排序：order by

8.限制結(jié)果的顯示條數(shù)

詳細(xì)見：http://www.cnblogs.com/linhaifeng/articles/7372774.html

三簡單查詢

company.employee員工id id int 姓名 emp_name varchar性別 sex enum年齡 age int入職日期 hire_date date崗位 post varchar職位描述 post_comment varchar薪水 salary double辦公室 office int部門編號 depart_id int#創(chuàng)建表 create table employee( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一個部門一個屋子 depart_id int );#查看表結(jié)構(gòu) mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+#插入記錄 #三個部門：教學(xué)，銷售，運(yùn)營 insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values ('egon','male',18,'20170301','老男孩駐沙河辦事處外交大使',7300.33,401,1), #以下是教學(xué)部 ('alex','male',78,'20150302','teacher',1000000.31,401,1), ('wupeiqi','male',81,'20130305','teacher',8300,401,1), ('yuanhao','male',73,'20140701','teacher',3500,401,1), ('liwenzhou','male',28,'20121101','teacher',2100,401,1), ('jingliyang','female',18,'20110211','teacher',9000,401,1), ('jinxin','male',18,'19000301','teacher',30000,401,1), ('成龍','male',48,'20101111','teacher',10000,401,1),('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門 ('丫丫','female',38,'20101101','sale',2000.35,402,2), ('丁丁','female',18,'20110312','sale',1000.37,402,2), ('星星','female',18,'20160513','sale',3000.29,402,2), ('格格','female',28,'20170127','sale',4000.33,402,2),('張野','male',28,'20160311','operation',10000.13,403,3), #以下是運(yùn)營部門 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬銀','female',18,'20130311','operation',19000,403,3), ('程咬銅','male',18,'20150411','operation',18000,403,3), ('程咬鐵','female',18,'20140512','operation',17000,403,3) ;#ps：如果在windows系統(tǒng)中，插入中文字符，select的結(jié)果為空白，可以將所有字符編碼統(tǒng)一設(shè)置成gbk #簡單查詢SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM employee;SELECT * FROM employee;SELECT name,salary FROM employee;#避免重復(fù)DISTINCTSELECT DISTINCT post FROM employee; #通過四則運(yùn)算查詢SELECT name, salary*12 FROM employee;SELECT name, salary*12 AS Annual_salary FROM employee;SELECT name, salary*12 Annual_salary FROM employee;#定義顯示格式CONCAT() 函數(shù)用于連接字符串SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;CONCAT_WS() 第一個參數(shù)為分隔符SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;結(jié)合CASE語句：SELECT(CASEWHEN NAME = 'egon' THENNAMEWHEN NAME = 'alex' THENCONCAT(name,'_BIGSB')ELSEconcat(NAME, 'SB')END) as new_nameFROMemp;

小練習(xí)：

1 查出所有員工的名字，薪資,格式為<名字:egon> <薪資:3000> 2 查出所有的崗位（去掉重復(fù)） 3 查出所有員工名字，以及他們的年薪,年薪的字段名為annual_year select concat('<名字:',name,'> ','<薪資:',salary,'>') from employee; select distinct depart_id from employee; select name,salary*12 annual_salary from employee; ?

四 WHERE約束

where字句中可以使用：

1. 比較運(yùn)算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之間
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
? ? pattern可以是%或_，
? ? %表示任意多字符
? ? _表示一個字符?
5. 邏輯運(yùn)算符：在多個條件直接可以使用邏輯運(yùn)算符 and or not

#1:單條件查詢SELECT name FROM employeeWHERE post='sale';#2:多條件查詢SELECT name,salary FROM employeeWHERE post='teacher' AND salary>10000;#3:關(guān)鍵字BETWEEN ANDSELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000;SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000;#4:關(guān)鍵字IS NULL(判斷某個字段是否為NULL不能用等號，需要用IS)SELECT name,post_comment FROM employee WHERE post_comment IS NULL;SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL;SELECT name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是nullps：執(zhí)行update employee set post_comment='' where id=2;再用上條查看，就會有結(jié)果了#5:關(guān)鍵字IN集合查詢SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ;SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ;#6:關(guān)鍵字LIKE模糊查詢通配符’%’SELECT * FROM employee WHERE name LIKE 'eg%';通配符’_’SELECT * FROM employee WHERE name LIKE 'al__';

小練習(xí)：

1. 查看崗位是teacher的員工姓名、年齡 2. 查看崗位是teacher且年齡大于30歲的員工姓名、年齡 3. 查看崗位是teacher且薪資在9000-1000范圍內(nèi)的員工姓名、年齡、薪資 4. 查看崗位描述不為NULL的員工信息 5. 查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資 6. 查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資 7. 查看崗位是teacher且名字是jin開頭的員工姓名、年薪 select name,age from employee where post = 'teacher'; select name,age from employee where post='teacher' and age > 30; select name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select name,salary*12 from employee where post='teacher' and name like 'jin%'; ?

五分組查詢:GROUP BY

一什么是分組？為什么要分組？

#1、首先明確一點：分組發(fā)生在where之后，即分組是基于where之后得到的記錄而進(jìn)行的#2、分組指的是：將所有記錄按照某個相同字段進(jìn)行歸類，比如針對員工信息表的職位分組，或者按照性別進(jìn)行分組等#3、為何要分組呢？取每個部門的最高工資取每個部門的員工數(shù)取男人數(shù)和女人數(shù)小竅門：‘每’這個字后面的字段，就是我們分組的依據(jù)#4、大前提：可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內(nèi)信息，需要借助于聚合函數(shù)

二 ONLY_FULL_GROUP_BY

#查看MySQL 5.7默認(rèn)的sql_mode如下： mysql> select @@global.sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION#！！！注意 ONLY_FULL_GROUP_BY的語義就是確定select target list中的所有列的值都是明確語義，簡單的說來，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是來自于聚集函數(shù)的結(jié)果，要么是來自于group by list中的表達(dá)式的值。#設(shè)置sql_mole如下操作(我們可以去掉ONLY_FULL_GROUP_BY模式)： mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; mysql> select @@global.sql_mode; +-------------------+ | @@global.sql_mode | +-------------------+ | | +-------------------+ 1 row in set (0.00 sec)mysql> select * from emp group by post; +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ | 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 | +----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec)#由于沒有設(shè)置ONLY_FULL_GROUP_BY,于是也可以有結(jié)果，默認(rèn)都是組內(nèi)的第一條記錄，但其實這是沒有意義的mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec)mysql> quit #設(shè)置成功后，一定要退出，然后重新登錄方可生效 Byemysql> use db1; Database changed mysql> select * from emp group by post; #報錯 ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY mysql> select post,count(id) from emp group by post; #只能查看分組依據(jù)和使用聚合函數(shù) +----------------------------+-----------+ | post | count(id) | +----------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | 老男孩駐沙河辦事處外交大使 | 1 | +----------------------------+-----------+ 4 rows in set (0.00 sec)

三 GROUP BY

單獨(dú)使用GROUP BY關(guān)鍵字分組SELECT post FROM employee GROUP BY post;注意：我們按照post字段分組，那么select查詢的字段只能是post，想要獲取組內(nèi)的其他相關(guān)信息，需要借助函數(shù)GROUP BY關(guān)鍵字和GROUP_CONCAT()函數(shù)一起使用SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照崗位分組，并查看組內(nèi)成員名SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;GROUP BY與聚合函數(shù)一起使用select post,count(id) as count from employee group by post;#按照崗位分組，并查看每個組有多少人

強(qiáng)調(diào)：

如果我們用unique的字段作為分組的依據(jù)，則每一條記錄自成一組，這種分組沒有意義多條記錄之間的某個字段值相同，該字段通常用來作為分組的依據(jù)

四聚合函數(shù)

#強(qiáng)調(diào)：聚合函數(shù)聚合的是組的內(nèi)容，若是沒有分組，則默認(rèn)一組示例：SELECT COUNT(*) FROM employee;SELECT COUNT(*) FROM employee WHERE depart_id=1;SELECT MAX(salary) FROM employee;SELECT MIN(salary) FROM employee;SELECT AVG(salary) FROM employee;SELECT SUM(salary) FROM employee;SELECT SUM(salary) FROM employee WHERE depart_id=3;

五小練習(xí)：

六 HAVING過濾

HAVING與WHERE不一樣的地方在于!!!!!!

#！！！執(zhí)行優(yōu)先級從高到低：where > group by > having #1. Where 發(fā)生在分組group by之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數(shù)。#2. Having發(fā)生在分組group by之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數(shù) mysql> select @@sql_mode; +--------------------+ | @@sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec)mysql> select * from emp where salary > 100000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ 1 row in set (0.00 sec)mysql> select * from emp having salary > 100000; ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clausemysql> select post,group_concat(name) from emp group by post having salary > 10000;#錯誤，分組后無法直接取到salary字段 ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000; +-----------+-------------------------------------------------------+ | post | group_concat(name) | +-----------+-------------------------------------------------------+ | operation | 程咬鐵,程咬銅,程咬銀,程咬金,張野 | | teacher | 成龍,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | +-----------+-------------------------------------------------------+ 2 rows in set (0.00 sec)

小練習(xí)：

1. 查詢各崗位內(nèi)包含的員工個數(shù)小于2的崗位名、崗位內(nèi)包含員工名字、個數(shù) 3. 查詢各崗位平均薪資大于10000的崗位名、平均工資 4. 查詢各崗位平均薪資大于10000且小于20000的崗位名、平均工資 #題1： mysql> select post,group_concat(name),count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 老男孩駐沙河辦事處外交大使 | egon | 1 | +-----------------------------------------+--------------------+-----------+#題目2： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+#題目3： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ ?

七查詢排序:ORDER BY

按單列排序SELECT * FROM employee ORDER BY salary;SELECT * FROM employee ORDER BY salary ASC;SELECT * FROM employee ORDER BY salary DESC;按多列排序:先按照age排序，如果年紀(jì)相同，則按照薪資排序SELECT * from employeeORDER BY age,salary DESC;

小練習(xí)：

1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序 2. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資升序排列 3. 查詢各崗位平均薪資大于10000的崗位名、平均工資,結(jié)果按平均薪資降序排列 #題目1 mysql> select * from employee ORDER BY age asc,hire_date desc;#題目2 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+#題目3 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+

八限制查詢的記錄數(shù):LIMIT

示例：SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默認(rèn)初始位置為0 SELECT * FROM employee ORDER BY salary DESCLIMIT 0,5; #從第0開始，即先查詢出第一條，然后包含這一條在內(nèi)往后查5條SELECT * FROM employee ORDER BY salary DESCLIMIT 5,5; #從第5開始，即先查詢出第6條，然后包含這一條在內(nèi)往后查5條

小練習(xí)：

1. 分頁顯示，每頁5條 mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | 老男孩駐沙河辦事處外交大使 | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec)mysql> select * from employee limit 5,5; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | 成龍 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec)mysql> select * from employee limit 10,5; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | 張野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) ?

九使用正則表達(dá)式查詢

SELECT * FROM employee WHERE name REGEXP '^ale';SELECT * FROM employee WHERE name REGEXP 'on$';SELECT * FROM employee WHERE name REGEXP 'm{2}';小結(jié)：對字符串匹配的方式 WHERE name = 'egon'; WHERE name LIKE 'yua%'; WHERE name REGEXP 'on$';

小練習(xí)：

查看所有員工中名字是jin開頭，n或者g結(jié)果的員工信息 select * from employee where name regexp '^jin.*[gn]$';

轉(zhuǎn)載于:https://www.cnblogs.com/yanxiatingyu/p/9334387.html

新人創(chuàng)作打卡挑戰(zhàn)賽發(fā)博客就能抽獎！定制產(chǎn)品紅包拿不停！

總結(jié)

以上是生活随笔為你收集整理的mysql 单表查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

MySQL

数据库

mysql 单表查询

一 單表查詢的語法

二 關(guān)鍵字的執(zhí)行優(yōu)先級(重點)

三 簡單查詢

四 WHERE約束

五 分組查詢:GROUP BY

六 HAVING過濾

七 查詢排序:ORDER BY

八 限制查詢的記錄數(shù):LIMIT

九 使用正則表達(dá)式查詢

總結(jié)

一單表查詢的語法

二關(guān)鍵字的執(zhí)行優(yōu)先級(重點)

三簡單查詢

五分組查詢:GROUP BY

七查詢排序:ORDER BY

八限制查詢的記錄數(shù):LIMIT

九使用正則表達(dá)式查詢