单表查询
准备工作
准备表company.employee5
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.24 sec)
插入数据
mysql> insert into company.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) value
-> ('jack','male','20180202','instructor','teach',5000,501,100),
-> ('tom','male','20180203','instructor','teach',5000,501,100),
-> ('robin','male','20180202','instructor','teach',8000,501,100),
-> ('alice','female','20180202','instructor','teach',7200,501,100),
-> ('tianyun','male','20180202','instructor','teach',600,502,100),
-> ('harry','male','20180202','hr',NULL,6000,502,101),
-> ('emma','female','20180206','sale','salecc',20000,503,102),
-> ('christine','female','20180205','sale',NULL,2200,503,102),
-> ('zhuzhu','male','20180205','sale',NULL,2200,503,102),
-> ('gougou','male','20180205','sale','',2200,503,102);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
简单查询
查询表全部内容
mysql> select * from employee5;
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
| 2 | jack | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 3 | tom | male | 2018-02-03 | instructor | teach | 5000.00 | 501 | 100 |
| 4 | robin | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 5 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 6 | tianyun | male | 2018-02-02 | instructor | teach | 600.00 | 502 | 100 |
| 7 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 8 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 9 | christine | female | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 11 | gougou | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+
10 rows in set (0.00 sec)
查询表的某几个字段
mysql> select name,post,salary from employee5;
+-----------+------------+----------+
| name | post | salary |
+-----------+------------+----------+
| jack | instructor | 5000.00 |
| tom | instructor | 5000.00 |
| robin | instructor | 8000.00 |
| alice | instructor | 7200.00 |
| tianyun | instructor | 600.00 |
| harry | hr | 6000.00 |
| emma | sale | 20000.00 |
| christine | sale | 2200.00 |
| zhuzhu | sale | 2200.00 |
| gougou | sale | 2200.00 |
+-----------+------------+----------+
10 rows in set (0.00 sec)
去重,仅用于单一字段
mysql> select distinct post from employee5;
+------------+
| post |
+------------+
| instructor |
| hr |
| sale |
+------------+
3 rows in set (0.00 sec)
计算出每个员工的年薪(14薪)
mysql> select name,salary,salary*14 from employee5;
+-----------+----------+-----------+
| name | salary | salary*14 |
+-----------+----------+-----------+
| jack | 5000.00 | 70000.00 |
| tom | 5000.00 | 70000.00 |
| robin | 8000.00 | 112000.00 |
| alice | 7200.00 | 100800.00 |
| tianyun | 600.00 | 8400.00 |
| harry | 6000.00 | 84000.00 |
| emma | 20000.00 | 280000.00 |
| christine | 2200.00 | 30800.00 |
| zhuzhu | 2200.00 | 30800.00 |
| gougou | 2200.00 | 30800.00 |
+-----------+----------+-----------+
10 rows in set (0.00 sec)
显示别名,将salary*14显示为annual_salary
mysql> select name,salary,salary*14 as annual_salary from employee5;
+-----------+----------+---------------+
| name | salary | annual_salary |
+-----------+----------+---------------+
| jack | 5000.00 | 70000.00 |
| tom | 5000.00 | 70000.00 |
| robin | 8000.00 | 112000.00 |
| alice | 7200.00 | 100800.00 |
| tianyun | 600.00 | 8400.00 |
| harry | 6000.00 | 84000.00 |
| emma | 20000.00 | 280000.00 |
| christine | 2200.00 | 30800.00 |
| zhuzhu | 2200.00 | 30800.00 |
| gougou | 2200.00 | 30800.00 |
+-----------+----------+---------------+
10 rows in set (0.00 sec)
定义显示格式
CONCAT()函数用来连接字符串
mysql> select concat(name,' annual salary: ',salary*14) as Annual_salary from employee5;
+------------------------------------+
| Annual_salary |
+------------------------------------+
| jack annual salary: 70000.00 |
| tom annual salary: 70000.00 |
| robin annual salary: 112000.00 |
| alice annual salary: 100800.00 |
| tianyun annual salary: 8400.00 |
| harry annual salary: 84000.00 |
| emma annual salary: 280000.00 |
| christine annual salary: 30800.00 |
| zhuzhu annual salary: 30800.00 |
| gougou annual salary: 30800.00 |
+------------------------------------+
10 rows in set (0.00 sec)
2条件查询
单条件查询
查询职位是hr的员工
mysql> select name,post from employee5 where post='hr';
+-------+------+
| name | post |
+-------+------+
| harry | hr |
+-------+------+
1 row in set (0.00 sec)
多条件查询
查看职位是"sale销售"的员工,但是薪水少于等于5000
mysql> select name,post,salary from employee5 where post='sale' and salary<=5000;
+-----------+------+---------+
| name | post | salary |
+-----------+------+---------+
| christine | sale | 2200.00 |
| zhuzhu | sale | 2200.00 |
| gougou | sale | 2200.00 |
+-----------+------+---------+
3 rows in set (0.00 sec)
关键字between and 和 not between and
查看工资5000~10000范围内的员工
mysql> select name,salary from employee5 where salary between 5000 and 10000;
+-------+---------+
| name | salary |
+-------+---------+
| jack | 5000.00 |
| tom | 5000.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
+-------+---------+
5 rows in set (0.00 sec)
查看不是这个范围的员工
mysql> select name,salary from employee5 where salary not between 5000 and 10000;
+-----------+----------+
| name | salary |
+-----------+----------+
| tianyun | 600.00 |
| emma | 20000.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
+-----------+----------+
5 rows in set (0.00 sec)
关键字is null和is not null
空 和 非空
查看job_description职位描述字段为空的员工
mysql> select name,job_description from employee5 where job_description is null;
+-----------+-----------------+
| name | job_description |
+-----------+-----------------+
| harry | NULL |
| christine | NULL |
| zhuzhu | NULL |
+-----------+-----------------+
3 rows in set (0.00 sec)
查看job_description职位描述字段不为空的员工
mysql> select name,job_description from employee5 where job_description is not null;
+---------+-----------------+
| name | job_description |
+---------+-----------------+
| jack | teach |
| tom | teach |
| robin | teach |
| alice | teach |
| tianyun | teach |
| emma | salecc |
| gougou | |
+---------+-----------------+
7 rows in set (0.00 sec)
集合查询 in和not in
mysql> select name,salary from employee5 where salary=2000 or salary=5000 or salary=6000 or salary=20000;
+-------+----------+
| name | salary |
+-------+----------+
| jack | 5000.00 |
| tom | 5000.00 |
| harry | 6000.00 |
| emma | 20000.00 |
+-------+----------+
4 rows in set (0.00 sec)
mysql> select name,salary from employee5 where salary in (2000,5000,6000,20000);
+-------+----------+
| name | salary |
+-------+----------+
| jack | 5000.00 |
| tom | 5000.00 |
| harry | 6000.00 |
| emma | 20000.00 |
+-------+----------+
4 rows in set (0.00 sec)
mysql> select name,salary from employee5 where salary not in (2000,5000,6000,20000);
+-----------+---------+
| name | salary |
+-----------+---------+
| robin | 8000.00 |
| alice | 7200.00 |
| tianyun | 600.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
+-----------+---------+
6 rows in set (0.00 sec)
模糊查询
- % ---- 任意多个字符
- _ ---- 任意单个字符
mysql> select name from employee5 where name like 'al%';
+-------+
| name |
+-------+
| alice |
+-------+
1 row in set (0.00 sec)
mysql> select name from employee5 where name like 'al_';
Empty set (0.00 sec)
mysql> select name from employee5 where name like 'al___';
+-------+
| name |
+-------+
| alice |
+-------+
1 row in set (0.00 sec)
3排序
单列排序
默认使用asc ,升序从小到大
mysql> select name,salary from employee5 order by salary;
+-----------+----------+
| name | salary |
+-----------+----------+
| tianyun | 600.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
| jack | 5000.00 |
| tom | 5000.00 |
| harry | 6000.00 |
| alice | 7200.00 |
| robin | 8000.00 |
| emma | 20000.00 |
+-----------+----------+
10 rows in set (0.00 sec)
降序从大到小,需要指定为desc
mysql> select name,salary from employee5 order by salary desc;
+-----------+----------+
| name | salary |
+-----------+----------+
| emma | 20000.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
| jack | 5000.00 |
| tom | 5000.00 |
| christine | 2200.00 |
| zhuzhu | 2200.00 |
| gougou | 2200.00 |
| tianyun | 600.00 |
+-----------+----------+
10 rows in set (0.00 sec)
多列排序
先按入职时间降序,再按薪资升序
mysql> mysql> select name,hire_date,salary from employee5 order by hire_date desc, salary;
+-----------+------------+----------+
| name | hire_date | salary |
+-----------+------------+----------+
| emma | 2018-02-06 | 20000.00 |
| christine | 2018-02-05 | 2200.00 |
| zhuzhu | 2018-02-05 | 2200.00 |
| gougou | 2018-02-05 | 2200.00 |
| tom | 2018-02-03 | 5000.00 |
| tianyun | 2018-02-02 | 600.00 |
| jack | 2018-02-02 | 5000.00 |
| harry | 2018-02-02 | 6000.00 |
| alice | 2018-02-02 | 7200.00 |
| robin | 2018-02-02 | 8000.00 |
+-----------+------------+----------+
10 rows in set (0.00 sec)
4限制查询的记录数
只显示排序后公司薪资最高的前5位员工
从0开始查询,共查询5条
mysql> mysql> select name,salary from employee5 order by salary desc limit 0,5;
+-------+----------+
| name | salary |
+-------+----------+
| emma | 20000.00 |
| robin | 8000.00 |
| alice | 7200.00 |
| harry | 6000.00 |
| jack | 5000.00 |
+-------+----------+
5 rows in set (0.00 sec)
5使用集合函数查询
| 函数 | 描述 |
|---|---|
| count() | 统计数量 |
| max() | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 总和值 |
mysql> select count(*) from employee5;
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from employee5 where post='hr' ;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select max(salary) from employee5;
+-------------+
| max(salary) |
+-------------+
| 20000.00 |
+-------------+
1 row in set (0.00 sec)
mysql> select max(salary) from employee5 where job_description='teach';
+-------------+
| max(salary) |
+-------------+
| 8000.00 |
+-------------+
1 row in set (0.00 sec)
mysql> select min(salary) from employee5 where job_description='teach';
+-------------+
| min(salary) |
+-------------+
| 600.00 |
+-------------+
1 row in set (0.00 sec)
mysql> select avg(salary) from employee5;
+-------------+
| avg(salary) |
+-------------+
| 5840.000000 |
+-------------+
1 row in set (0.00 sec)
mysql> select avg(salary) from employee5 where job_description='teach';
+-------------+
| avg(salary) |
+-------------+
| 5160.000000 |
+-------------+
1 row in set (0.00 sec)
mysql> select sum(salary) from employee5;
+-------------+
| sum(salary) |
+-------------+
| 58400.00 |
+-------------+
1 row in set (0.00 sec)
6分组查询
group_concat()函数和group by()函数通常一块使用
按照部门来分组
mysql> mysql> select post,group_concat(name) from employee5 group by post;
+------------+------------------------------+
| post | group_concat(name) |
+------------+------------------------------+
| hr | harry |
| instructor | jack,tom,robin,alice,tianyun |
| sale | emma,christine,zhuzhu,gougou |
+------------+------------------------------+
3 rows in set (0.00 sec)
mysql> select post,group_concat(salary) from employee5 group by post;
+------------+----------------------------------------+
| post | group_concat(salary) |
+------------+----------------------------------------+
| hr | 6000.00 |
| instructor | 5000.00,5000.00,8000.00,7200.00,600.00 |
| sale | 20000.00,2200.00,2200.00,2200.00 |
+------------+----------------------------------------+
3 rows in set (0.00 sec)
group by()函数和集合函数一起使用
mysql> select post,sum(salary),avg(salary),count(*) from employee5 group by post;
+------------+-------------+-------------+----------+
| post | sum(salary) | avg(salary) | count(*) |
+------------+-------------+-------------+----------+
| hr | 6000.00 | 6000.000000 | 1 |
| instructor | 25800.00 | 5160.000000 | 5 |
| sale | 26600.00 | 6650.000000 | 4 |
+------------+-------------+-------------+----------+
3 rows in set (0.00 sec)
7正则表达式查询
- '^ali'-----以ali开头
- 'yun$'----以yun结尾
- 'm{2}'----m出现的次数
mysql> select name,salary from employee5 where name regexp 'm{2}';
+------+----------+
| name | salary |
+------+----------+
| emma | 20000.00 |
+------+----------+
1 row in set (0.00 sec)
小结
对字符串匹配的方式
where name = 'tom';
where name like 'to%';
where name regexp 'yun$';
补充:嵌套查询
mysql> mysql> select * from employee5 where salary = (select max(salary) from employee5);
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
| 8 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
+----+------+--------+------------+------+-----------------+----------+--------+--------+
1 row in set (0.00 sec)
