六月婷婷综合激情-六月婷婷综合-六月婷婷在线观看-六月婷婷在线-亚洲黄色在线网站-亚洲黄色在线观看网站

明輝手游網中心：是一個免費提供流行視頻軟件教程、在線學習分享的學習平臺！

MySQL怎么進行單表查詢？單表查詢的語句

發表時間：2023-05-29 來源：明輝站整理相關軟件相關文章人氣：

[摘要]本篇文章給大家帶來的內容是介紹MySQL怎么進行單表查詢？單表查詢的語句。有一定的參考價值，有需要的朋友可以參考一下，希望對你們有所幫助。首先創建數據表# 創建表mysql> create t...
本篇文章給大家帶來的內容是介紹MySQL怎么進行單表查詢？單表查詢的語句。有一定的參考價值，有需要的朋友可以參考一下，希望對你們有所幫助。

首先創建數據表

# 創建表
    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
    );
    
# 插入數據
    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);
    
# 查看表結構
    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                      
+-----------------+-----------------------+------+-----+---------+----------------+

查詢語法

SELECT 字段1,字段2... FROM 表名
                  WHERE 條件
                  GROUP BY field
                  HAVING 篩選
                  ORDER BY field
                  LIMIT 限制條數；

查看表中所有數據

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)

簡單查詢

簡單查詢

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，通常僅用于某一字段。

通過四則運算查詢

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() 函數用于連接字符串

mysql> SELECT CONCAT(name, ' annual salary: ', salary*14)  AS Annual_salary FROM employee5;

條件查詢

a、語法
    select * from 表名 where 條件
    
b、比較運算符
    大于    小于    大于等于    小于等于    不等于    >        <        >=            <=            !=或<>

c、邏輯運算符
    并且    或者    非    and        or        not

d、模糊查詢
    like    %           表示任意多個任意字符
    _           表示一個任意字符

e、范圍查詢    in                  表示在一個非連續的范圍內
    between...and...    表示在一個連續的范圍內

f、空判斷
    判斷空：is null
    判斷非空：is not null 

g、優先級
    小括號,not 比較運算符， 邏輯運算符
    and比or優先級高，如果同時出現并希望先選or，需要結合()來使用

單條件查詢

mysql> SELECT name,post FROM employee5 WHERE post='hr';

多條件查詢

mysql> SELECT name,salary FROM employee5 WHERE post='hr' AND salary>5000;

關鍵字 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;

關鍵字 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='';

關鍵字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) ;

關鍵字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;

# 先按入職時間，再按薪水排序
mysql> SELECT * FROM employee5 ORDER BY hire_date DESC, salary DESC;

# 先按職位，再按薪水排序
mysql> SELECT * FROM employee5 ORDER BY post, salary DESC;

分頁查詢 limit

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5;      //默認初始位置為0

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0,5;

mysql> SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3,5;      //從第4條開始，共顯示5條

聚合函數查詢

    a、count(*)       表示計算總行數，括號中可以寫*和列名
    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;

分組查詢

單獨使用GROUP BY關鍵字分組
mysql> SELECT post FROM employee5 GROUP BY post;

注意：我們按照post字段分組，那么select查詢的字段只能是post，想要獲取組內的其他相關信息，需要借助函數

GROUP BY關鍵字和group_concat()函數一起使用

# 按照id分組，并查看組內成員
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和集合函數一起使用

# 按照dep_id 分組， 并計算組內成員工資總和
mysql> SELECT dep_id,SUM(salary) FROM employee5 GROUP BY dep_id;

# 按照dep_id分組，并計算組內成員工資平均值
mysql> SELECT dep_id,AVG(salary) FROM employee5 GROUP BY dep_id;

正則表達式查詢

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怎么進行單表查詢？單表查詢的語句的詳細內容，更多請關注php中文網其它相關文章！


學習教程快速掌握從入門到精通的SQL知識。
主站蜘蛛池模板： 日日干夜夜操 | 日韩中文字幕一区 | 色天使色婷婷丁香久久综合 | 午夜视频1000| 色综合综合在线 | 欧美一区二区三区久久综 | 亚洲线精品一区二区三区 | 午夜视频网站 | 羞羞色在线观看 | 亚洲欧洲日韩国产 | 日韩欧美在线观看 | 午夜精品福利视频 | 青春草国产成人精品久久 | 亚洲 欧美 在线观看 | 深爱五月婷婷 | 亚洲αv | 日韩一级在线 | 中文国产成人久久精品小说 | 人人cao | 青青青视频免费线看 视频 青青青视频免费观看 | 网站一级片 | 新版天堂中文资源8在线 | 日日日干| 日韩 亚洲 翔田千里 在线 | 日本gogo亚洲高清大胆 | 日韩一级在线播放免费观看 | 午夜爽视频| 五月婷婷精品 | 欧美一级三级在线观看 | 日本天堂视频在线观看 | 中文字幕专区在线亚洲 | 一区二区三区视频免费观看 | 人人澡人摸人人爽歪歪 | 日韩欧美综合在线 | 天天综合网站 | 日本网络视频www色高清免费 | 日本成a人片在线观看网址 日本草草影院 | 色橹橹高清视频在线播放 | 欧美一区二区三区四区五区六区 | 性刺激免费视频观看在线观看 | 日本伊人久久 |