MySQL怎么完成單表查詢?MySQL單表查詢語(yǔ)句
發(fā)表時(shí)間:2023-07-10 來(lái)源:明輝站整理相關(guān)軟件相關(guān)文章人氣:
[摘要]MySQL如何實(shí)現(xiàn)單表查詢?本篇文章就給大家介紹MySQL單表查詢的方法。有一定的參考價(jià)值,有需要的朋友可以參考一下,希望對(duì)你們有所幫助。首先我們來(lái)創(chuàng)建表、插入數(shù)據(jù):# 創(chuàng)建表
mysql> ...
MySQL如何實(shí)現(xiàn)單表查詢?本篇文章就給大家介紹MySQL單表查詢的方法。有一定的參考價(jià)值,有需要的朋友可以參考一下,希望對(duì)你們有所幫助。
首先我們來(lái)創(chuàng)建表、插入數(shù)據(jù):
# 創(chuàng)建表
mysql> create table company.employee5(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);
# 插入數(shù)據(jù)
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);
# 查看表結(jié)構(gòu)
mysql> desc employee5;
+-----------------+-----------------------+------+-----+---------+----------------+
Field Type Null Key Default Extra
+-----------------+-----------------------+------+-----+---------+----------------+
id int(11) NO PRI NULL auto_increment
name varchar(30) NO NULL
sex enum('male','female') NO male
hire_date date NO NULL
post varchar(50) NO NULL
job_description varchar(100) YES NULL
salary double(15,2) NO NULL
office int(11) YES NULL
dep_id int(11) YES NULL
+-----------------+-----------------------+------+-----+---------+----------------+
查詢語(yǔ)法
SELECT 字段1,字段2... FROM 表名
WHERE 條件
GROUP BY field
HAVING 篩選
ORDER BY field
LIMIT 限制條數(shù);
查看表中所有數(shù)據(jù)
mysql> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
id name sex hire_date post job_description salary office dep_id
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
1 jack male 2018-02-02 instructor teach 5000.00 501 100
2 tom male 2018-02-03 instructor teach 5500.00 501 100
3 robin male 2018-02-02 instructor teach 8000.00 501 100
4 alice female 2018-02-02 instructor teach 7200.00 501 100
5 tianyun male 2018-02-02 hr hrcc 600.00 502 101
6 harry male 2018-02-02 hr NULL 6000.00 502 101
7 emma female 2018-02-06 sale salecc 20000.00 503 102
8 christine female 2018-02-05 sale salecc 2200.00 503 102
9 zhuzhu male 2018-02-05 sale NULL 2200.00 503 102
10 gougou male 2018-02-05 sale 2200.00 503 102
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)
簡(jiǎn)單查詢
簡(jiǎn)單查詢
mysql> SELECT * FROM employee5;
mysql> SELECT name, salary, dep_id FROM employee5;
去重DISTINCT
mysql> SELECT post FROM employee5;
mysql> SELECT DISTINCT post FROM employee5;
注:不能部分使用DISTINCT,通常僅用于某一字段。
通過(guò)四則運(yùn)算查詢
mysql> SELECT name, salary, salary*14 FROM employee5;
mysql> SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
mysql> SELECT name, salary, salary*14 Annual_salary FROM employee5;
定義顯示格式
CONCAT() 函數(shù)用于連接字符串
mysql> SELECT CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5;
條件查詢
a、語(yǔ)法
select * from 表名 where 條件
b、比較運(yùn)算符
大于 小于 大于等于 小于等于 不等于 > < >= <=
!=或<>
c、邏輯運(yùn)算符
并且 或者 非 and or notd、模糊查詢
like % 表示任意多個(gè)任意字符
_ 表示一個(gè)任意字符
e、范圍查詢 in 表示在一個(gè)非連續(xù)的范圍內(nèi)
between...and... 表示在一個(gè)連續(xù)的范圍內(nèi)
f、空判斷
判斷空:is null
判斷非空:is not null
g、優(yōu)先級(jí)
小括號(hào),not 比較運(yùn)算符, 邏輯運(yùn)算符
and比or優(yōu)先級(jí)高,如果同時(shí)出現(xiàn)并希望先選or,需要結(jié)合()來(lái)使用
單條件查詢
mysql> SELECT name,post FROM employee5 WHERE post='hr';
多條件查詢
mysql> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>5000;
關(guān)鍵字 BETWEEN AND查詢
mysql> SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;
mysql> SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000;
關(guān)鍵字 IS NULL 查詢
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NULL;
mysql> SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;
mysql> SELECT name,job_description FROM employee5 WHERE job_description='';
關(guān)鍵字IN集合查詢
mysql> SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;
mysql> SELECT name, salary FROM employee5 WHERE salary IN (4000,5000,6000,9000) ;
mysql> SELECT name, salary FROM employee5 WHERE salary NOT IN (4000,5000,6000,9000) ;
關(guān)鍵字LIKE模糊查詢
通配符’%’
mysql> SELECT * FROM employee5 WHERE name LIKE 'al%';
通配符’_’
mysql> SELECT * FROM employee5 WHERE name LIKE 'al___';
查詢排序
按單列排序
mysql> SELECT * FROM employee5 ORDER BY salary;
mysql> SELECT name, salary FROM employee5 ORDER BY salary ASC;
mysql> SELECT name, salary FROM employee5 ORDER BY salary DESC;
按多列排序
mysql> SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;
# 先按入職時(shí)間,再按薪水排序
mysql> SELECT * FROM employee5 ORDER BY hire_date DESC, salary DESC;
# 先按職位,再按薪水排序
mysql> SELECT * FROM employee5 ORDER BY post, salary DESC;
分頁(yè)查詢 limit
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5; //默認(rèn)初始位置為0
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5;
mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5; //從第4條開(kāi)始,共顯示5條
聚合函數(shù)查詢
a、count(*) 表示計(jì)算總行數(shù),括號(hào)中可以寫(xiě)*和列名
b、max(列) 表示求此列的最大值
c、min(列) 表示求此列的最小值
d、sun(列) 表示求此列的和
e、avg(列) 表示求此列的平均值
mysql> SELECT COUNT(*) FROM employee5;
mysql> SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
mysql> SELECT MAX(salary) FROM employee5;
mysql> SELECT MIN(salary) FROM employee5;
mysql> SELECT AVG(salary) FROM employee5;
mysql> SELECT SUM(salary) FROM employee5;
mysql> SELECT SUM(salary) FROM employee5 WHERE dep_id=101;
分組查詢
# 單獨(dú)使用GROUP BY關(guān)鍵字分組
mysql> SELECT post FROM employee5 GROUP BY post;
注意:我們按照post字段分組,那么select查詢的字段只能是post,想要獲取組內(nèi)的其他相關(guān)信息,需要借助函數(shù)
GROUP BY關(guān)鍵字和group_concat()函數(shù)一起使用
# 按照id分組,并查看組內(nèi)成員
mysql> SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
mysql> SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
GROUP BY和集合函數(shù)一起使用
# 按照dep_id 分組, 并計(jì)算組內(nèi)成員工資總和
mysql> SELECT dep_id,SUM(salary) FROM employee5 GROUP BY dep_id;
# 按照dep_id分組,并計(jì)算組內(nèi)成員工資平均值
mysql> SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;
正則表達(dá)式查詢
mysql> SELECT * FROM employee5 WHERE name REGEXP '^j';
mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]+.*';
mysql> SELECT * FROM employee5 WHERE salary REGEXP '[5]{2}.*';
相關(guān)推薦:
mysql數(shù)據(jù)庫(kù)圖文教程
MySQL視頻教程
bootstrap視頻教程
以上就是MySQL如何實(shí)現(xiàn)單表查詢?MySQL單表查詢語(yǔ)句的詳細(xì)內(nèi)容,更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章!
學(xué)習(xí)教程快速掌握從入門(mén)到精通的SQL知識(shí)。