MySQL数据库基础之查询/多表查询（增删改查）

一：数据库查询

单表查询
简单查询
通过条件查询
查询排序
限制查询记录数
使用集合函数查询
分组查询
使用正则表达式查询

mysql> create database company;
Query OK, 1 row affected (0.00 sec)

mysql> use company;
Database changed

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 );
Query OK, 0 rows affected (0.00 sec)

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    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

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), ('','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);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

简单查询：

mysql> select 字段名称,字段名称2 from 表名 条件
mysql> select * from employee5;
mysql> select name, salary, dep_id from employee5 where id <=5;

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 |           | 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 name,salary,dep_id from employee5 where id <=5;
#select查询,你指定的字段,from employee5的表,并且where判断id小于等于5.只看符合条件并且id小于等于5的.
+-------+---------+--------+
| name  | salary  | dep_id |
+-------+---------+--------+
| jack  | 5000.00 |    100 |
| tom   | 5500.00 |    100 |
| robin | 8000.00 |    100 |
| alice | 7200.00 |    100 |
|       |  600.00 |    101 |
+-------+---------+--------+
5 rows in set (0.00 sec)

避免重复DISTINCT

SELECT post FROM employee5;
SELECT distinct post FROM employee5;
注：不能部分使用DISTINCT，通常仅用于某一字段。

mysql> select post from employee5;
#查询指顶字段的内容,但这里会出现很多重复的数据.
+------------+
| post       |
+------------+
| instructor |
| instructor |
| instructor |
| instructor |
| hr         |
| hr         |
| sale       |
| sale       |
| sale       |
| sale       |
+------------+
10 rows in set (0.00 sec)

mysql> select distinct post from employee5;
#如果只想查询某个字段的内容并且去重,用distinct.
+------------+
| post       |
+------------+
| instructor |
| hr         |
| sale       |
+------------+
3 rows in set (0.00 sec)

通过四则运算查询

SELECT name, salary, salary14 FROM employee5;
SELECT name, salary, salary14 AS Annual_salary FROM employee5;
SELECT name, salary, salary*14 Annual_salary FROM employee5;

mysql> select name,salary,salary*14 from employee5;
#这里查看指定字段name和salary,并且薪资运算乘以14模拟计算年薪.虽然在正常表格里没有,但可以这样运算显示.
+-----------+----------+-----------+
| name      | salary   | salary*14 |
+-----------+----------+-----------+
| jack      |  5000.00 |  70000.00 |
| tom       |  5500.00 |  77000.00 |
| robin     |  8000.00 | 112000.00 |
| alice     |  7200.00 | 100800.00 |
|           |   600.00 |   8400.00 |
| harry     |  6000.00 |  84000.00 |
| emma      | 20000.00 | 280000.00 |