mysql 查询操作实例

mysql 查询操作实例

前提

先建2个表

# 创建部门表
drop table if exists dept;
create table dept(
d_no int not null primary key auto_increment,
d_name varchar(50),
d_location varchar(100)
);

# 创建员工表
drop table if exists employee;
create table employee(
e_no int not null primary key,
e_name varchar(100) not null,
e_gender char(2) not null,
dept_no int not null,
e_job varchar(100) not null,
e_salary smallint not null,
hiredate date,
constraint dno_fk foreign key(dept_no) references dept(d_no)
);

insert into dept values
(10, 'accounting', 'shanghai'),
(20, 'research', 'beijing'),
(30, 'sales', 'shenzhen'),
(40, 'operations', 'fujian');

insert into employee values
(1001, 'smith', 'm', 20, 'clerk', 800, '2005-11-12'),
(1002, 'allen', 'f', 30, 'salesman', 1600, '2003-5-12'),
(1003, 'ward', 'f', 30, 'salesman', 1250, '2003-5-12'),
(1004, 'jones', 'm', 20, 'manager', 2975, '1998-5-18'),
(1005, 'martin', 'm', 30, 'salesman', 1250, '2001-6-12'),
(1006, 'blake', 'f', 30, 'manager', 2850, '1997-2-15'),
(1007, 'clark', 'm', 10, 'manager', 2450, '2002-9-12'),
(1008, 'scott', 'm', 20, 'analyst', 3000, '2003-5-12'),
(1009, 'king', 'f', 10, 'president', 5000, '1995-1-1'),
(1010, 'turner', 'f', 30, 'salesman', 1500, '1997-10-12'),
(1011, 'adams', 'm', 20, 'clerk', 1100, '1999-10-5'),
(1012, 'james', 'f', 30, 'clerk', 950, '2008-6-15');

综合查询

# 1)查询所有女员工进入公司多少年
mysql> select sum(year(curdate())-year(hiredate)) from employee where e_gender = 'f';
+-------------------------------------+
| sum(year(curdate())-year(hiredate)) |
+-------------------------------------+
|                                 129 |
+-------------------------------------+
1 row in set (0.00 sec)


# 2)使用limit查询从第3条记录开始到第6条记录
mysql> select * from employee limit 2,4;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   |
+------+--------+----------+---------+----------+----------+------------+
| 1003 | ward   | f        |      30 | salesman |     1250 | 2003-05-12 |
| 1004 | jones  | m        |      20 | manager  |     2975 | 1998-05-18 |
| 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
| 1006 | blake  | f        |      30 | manager  |     2850 | 1997-02-15 |
+------+--------+----------+---------+----------+----------+------------+
4 rows in set (0.00 sec)


# 3)查询销售人员最低工资
mysql> select min(e_salary) from employee where e_job='salesman';
+---------------+
| min(e_salary) |
+---------------+
|          1250 |
+---------------+
1 row in set (0.00 sec)

# 4)查询名字以字母n或者s结尾的记录
mysql> select * from employee where e_name regexp '[ns]$';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   |
+------+--------+----------+---------+----------+----------+------------+
| 1002 | allen  | f        |      30 | salesman |     1600 | 2003-05-12 |
| 1004 | jones  | m        |      20 | manager  |     2975 | 1998-05-18 |
| 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
| 1011 | james  | m        |      20 | clerk    |     1100 | 1999-10-05 |
| 1012 | james  | f        |      30 | clerk    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
5 rows in set (0.00 sec)

# 5)查询在beijing工作的员工的姓名和职务
mysql> select e_name,e_job from employee where dept_no in (select d_no from dept where d_location = 'beijing');
+--------+---------+
| e_name | e_job   |
+--------+---------+
| smith  | clerk   |
| jones  | manager |
| scott  | analyst |
| james  | clerk   |
+--------+---------+
4 rows in set (0.00 sec)

# 6)使用左连接查询employee表和dept表

mysql> select * from employee left outer join dept on employee.dept_no = dept.d_no;
+------+--------+----------+---------+-----------+----------+------------+------+------------+------------+
| e_no | e_name | e_gender | dept_no | e_job     | e_salary | hiredate   | d_no | d_name     | d_location |
+------+--------+----------+---------+-----------+----------+------------+------+------------+------------+
| 1001 | smith  | m        |      20 | clerk     |      800 | 2005-11-12 |   20 | research   | beijing    |
| 1002 | allen  | f        |      30 | salesman  |     1600 | 2003-05-12 |   30 | sales      | shenzhen   |
| 1003 | ward   | f        |      30 | salesman  |     1250 | 2003-05-12 |   30 | sales      | shenzhen   |
| 1004 | jones  | m        |      20 | manager   |     2975 | 1998-05-18 |   20 | research   | beijing    |
| 1005 | martin | m        |      30 | salesman  |     1250 | 2001-06-12 |   30 | sales      | shenzhen   |
| 1006 | blake  | f        |      30 | manager   |     2850 | 1997-02-15 |   30 | sales      | shenzhen   |
| 1007 | clark  | m        |      10 | manager   |     2450 | 2002-09-12 |   10 | accounting | shanghai   |
| 1008 | scott  | m        |      20 | analyst   |     3000 | 2003-05-12 |   20 | research   | beijing    |
| 1009 | king   | f        |      10 | president |     5000 | 1995-01-01 |   10 | accounting | shanghai   |
| 1010 | turner | f        |      30 | salesman  |     1500 | 1997-10-12 |   30 | sales      | shenzhen   |
| 1011 | james  | m        |      20 | clerk     |     1100 | 1999-10-05 |   20 | research   | beijing    |
| 1012 | james  | f        |      30 | clerk     |      950 | 2008-06-15 |   30 | sales      | shenzhen   |
+------+--------+----------+---------+-----------+----------+------------+------+------------+------------+
12 rows in set (0.00 sec)


# 7)查询所有2001～2005年入职的员工信息，查询部门编号为20和30的员工信息并使用union 合并2个查询结果

mysql> select * from employee where year(hiredate) between '2001' and '2005' union all select * from employee where dept_no  = 20 or dept_no = 30;
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | smith  | m        |      20 | clerk    |      800 | 2005-11-12 |
| 1002 | allen  | f        |      30 | salesman |     1600 | 2003-05-12 |
| 1003 | ward   | f        |      30 | salesman |     1250 | 2003-05-12 |
| 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
| 1007 | clark  | m        |      10 | manager  |     2450 | 2002-09-12 |
| 1008 | scott  | m        |      20 | analyst  |     3000 | 2003-05-12 |
| 1001 | smith  | m        |      20 | clerk    |      800 | 2005-11-12 |
| 1002 | allen  | f        |      30 | salesman |     1600 | 2003-05-12 |
| 1003 | ward   | f        |      30 | salesman |     1250 | 2003-05-12 |
| 1004 | jones  | m        |      20 | manager  |     2975 | 1998-05-18 |
| 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
| 1006 | blake  | f        |      30 | manager  |     2850 | 1997-02-15 |
| 1008 | scott  | m        |      20 | analyst  |     3000 | 2003-05-12 |
| 1010 | turner | f        |      30 | salesman |     1500 | 1997-10-12 |
| 1011 | james  | m        |      20 | clerk    |     1100 | 1999-10-05 |
| 1012 | james  | f        |      30 | clerk    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
16 rows in set (0.01 sec)


# 8)使用like 查询员工姓名包含a字母的记录
mysql> select * from employee where e_name like '%a%';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   |
+------+--------+----------+---------+----------+----------+------------+
| 1002 | allen  | f        |      30 | salesman |     1600 | 2003-05-12 |
| 1003 | ward   | f        |      30 | salesman |     1250 | 2003-05-12 |
| 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
| 1006 | blake  | f        |      30 | manager  |     2850 | 1997-02-15 |
| 1007 | clark  | m        |      10 | manager  |     2450 | 2002-09-12 |
| 1011 | james  | m        |      20 | clerk    |     1100 | 1999-10-05 |
| 1012 | james  | f        |      30 | clerk    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
7 rows in set (0.00 sec)

# 9)使用regexp 查询员工姓名包含t c m 3个字母任意一个的记录
mysql> select * from employee where e_name regexp '[tcm]';
+------+--------+----------+---------+----------+----------+------------+
| e_no | e_name | e_gender | dept_no | e_job    | e_salary | hiredate   |
+------+--------+----------+---------+----------+----------+------------+
| 1001 | smith  | m        |      20 | clerk    |      800 | 2005-11-12 |
| 1005 | martin | m        |      30 | salesman |     1250 | 2001-06-12 |
| 1007 | clark  | m        |      10 | manager  |     2450 | 2002-09-12 |
| 1008 | scott  | m        |      20 | analyst  |     3000 | 2003-05-12 |
| 1010 | turner | f        |      30 | salesman |     1500 | 1997-10-12 |
| 1011 | james  | m        |      20 | clerk    |     1100 | 1999-10-05 |
| 1012 | james  | f        |      30 | clerk    |      950 | 2008-06-15 |
+------+--------+----------+---------+----------+----------+------------+
7 rows in set (0.01 sec)