单表查询

<meta http-equiv="content-type" content="text/html; charset=utf-8">  <meta name="generator" content="CherryTree"> <link rel="stylesheet" href="styles.css" type="text/css"> **## MySQL单表查询**  **## SELECT**  **## ： DQL** 
========================================================
简单查询
通过条件查询
查询排序
限制查询记录数
使用集合函数查询
分组查询
使用正则表达式查询 

# 表company.employee5

雇员编号 id int
雇员姓名 name varchar(30)
雇员性别 sex enum
雇用时期 hire_date date
职位 post varchar(50)
职位描述 job_description varchar(100)
薪水 salary double(15,2)
办公室 office int
部门编号 dep_id int

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

[图片上传失败...(image-d26167-1525777990063)]

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

[root@slave2 ~]# mysqldump -p"(tianyunTIANYUN123)" -B company --single-transaction > company.sql

[root@slave1 ~]# wget ftp://10.18.40.100/tianyun/company.sql
[root@slave1 ~]# mysql -p'TianYun520^&*' < company.sql

一、简单查询
简单查询
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 |

SELECT name, salary, dep_id FROM employee5;

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

通过四则运算查询
SELECT name, salary, salary*14 FROM employee5;
SELECT name, salary, salary*14 AS Annual_salary FROM employee5;
SELECT name, salary, salary*14 Annual_salary FROM employee5;

定义显示格式
CONCAT() 函数用于连接字符串
SELECT  CONCAT(name, ' annual salary: ', salary*14) AS Annual_salary FROM employee5;
+-----------------------------------+
| Annual_salary |
+-----------------------------------+
| jack annual salary: 70000.00 |
| tom annual salary: 77000.00 |

二、单条件查询
单条件查询
SELECT name,post
FROM employee5
WHERE post='hr';

多条件查询
SELECT name,salary
FROM employee5
WHERE post='hr' AND salary>10000;

关键字BETWEEN AND
SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000;

SELECT name,salary FROM employee5
WHERE salary NOT BETWEEN 5000 AND 15000;

关键字IS NULL
SELECT name,job_description FROM employee5
WHERE job_description IS NULL;

SELECT name,job_description FROM employee5
WHERE job_description IS NOT NULL;

SELECT name,job_description FROM employee5
WHERE job_description='';

关键字IN集合查询
SELECT name, salary FROM employee5
WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;

SELECT name, salary FROM employee5
WHERE salary IN (4000,5000,6000,9000) ;

SELECT name, salary FROM employee
WHERE salary NOT IN (4000,5000,6000,9000) ;

关键字LIKE模糊查询
通配符’%’
SELECT * FROM employee5 WHERE name LIKE 'al%';

通配符’_’
SELECT * FROM employee5 WHERE name LIKE 'al___';

三、查询排序
按单列排序
SELECT * FROM employee5 ORDER BY salary;
SELECT name, salary FROM employee5 ORDER BY salary ASC;
SELECT name, salary FROM employee5 ORDER BY salary DESC;

按多列排序
SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;

[图片上传失败...(image-7cf3c5-1525777990062)]

### 先按入职时间，再按薪水排序

[图片上传失败...(image-2f4883-1525777990062)]

### 先按职位，再按薪水排序

[图片上传失败...(image-e49982-1525777990062)]

四、限制查询的记录数
示例：
SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 5; //默认初始位置为0

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

SELECT * FROM employee5 ORDER BY salary DESC
LIMIT 3,5; //从第4条开始，共显示5条

五、使用集合函数查询
示例：
SELECT COUNT(*) FROM employee5;
SELECT COUNT(*) FROM employee5 WHERE dep_id=101;
SELECT MAX(salary) FROM employee5;
SELECT MIN(salary) FROM employee5;
SELECT AVG(salary) FROM employee5;
SELECT SUM(salary) FROM employee5;
SELECT SUM(salary) FROM employee5 WHERE dep_id=101;

[图片上传失败...(image-87dfb5-1525777990062)]

六、分组查询
GROUP BY和GROUP_CONCAT()函数一起使用
SELECT dep_id,GROUP_CONCAT(name) FROM employee5 GROUP BY dep_id;
SELECT dep_id,GROUP_CONCAT(name) as emp_members FROM employee5 GROUP BY dep_id;
[图片上传失败...(image-6f810c-1525777990062)]

[图片上传失败...(image-db8ffc-1525777990061)]

GROUP BY和集合函数一起使用
[图片上传失败...(image-186a56-1525777990061)]

七、使用正则表达式查询
SELECT * FROM employee5 WHERE name REGEXP '^ali';

SELECT * FROM employee5 WHERE name REGEXP 'yun$';

SELECT * FROM employee5 WHERE name REGEXP 'm{2}';

## 小结：对字符串匹配的方式

WHERE name = 'tom';
WHERE name LIKE 'to%';
WHERE name REGEXP 'yun$';