溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

MySQL實(shí)現(xiàn)單表查詢的簡(jiǎn)單方法

發(fā)布時(shí)間：2020-06-04 14:33:29 來(lái)源：PHP中文網(wǎng) 閱讀：249 作者：三月 欄目：MySQL數(shù)據(jù)庫(kù)

本文主要給大家簡(jiǎn)單講講MySQL實(shí)現(xiàn)單表查詢的簡(jiǎn)單方法，相關(guān)專(zhuān)業(yè)術(shù)語(yǔ)大家可以上網(wǎng)查查或者找一些相關(guān)書(shū)籍補(bǔ)充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL實(shí)現(xiàn)單表查詢的簡(jiǎn)單方法這篇文章可以給大家?guī)?lái)一些實(shí)際幫助。                                                             

首先我們來(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}.*';

MySQL實(shí)現(xiàn)單表查詢的簡(jiǎn)單方法就先給大家講到這里，對(duì)于其它相關(guān)問(wèn)題大家想要了解的可以持續(xù)關(guān)注我們的行業(yè)資訊。我們的板塊內(nèi)容每天都會(huì)捕捉一些行業(yè)新聞及專(zhuān)業(yè)知識(shí)分享給大家的。

向AI問(wèn)一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手