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;

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;

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;

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='';

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) ;

通配符’%’ 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;

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，想要獲取組內的其他相關信息，需要借助函數

# 按照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;

# 按照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}.*';