inner join(内连接)
表间的主键与外键连接,只取键值相同的,可以获取双方表中的数据连接方式。
语法:
SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
left join(左连接)
选择前面表的全部;左连接是以左表为标准,只查询在左边表中存在的数据,当然需要两个表中的键值一致。
语法:
SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
right join(右连接)
选择后面表的全部,进行检索
语法:
SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;
自连接
自己跟自己连接,参与连接的表都是同一张表。通过给表取别名实现
交叉连接
不适用任何匹配条件,生成笛卡尔积
练习1
CREATE TABLE student (
id INT(10) NOT NULL UNIQUE PRIMARY KEY ,
name VARCHAR(20) NOT NULL ,
sex VARCHAR(4) ,
birth YEAR,
department VARCHAR(20) ,
address VARCHAR(50)
);
CREATE TABLE score (
id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT ,
stu_id INT(10) NOT NULL ,
c_name VARCHAR(20) ,
grade INT(10)
);
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');
INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');
INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');
INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语', 80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902, '中文',88);
INSERT INTO score VALUES(NULL,903, '中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904, '英语',92);
INSERT INTO score VALUES(NULL,905, '英语',94);
INSERT INTO score VALUES(NULL,906, '计算机',90);
INSERT INTO score VALUES(NULL,906, '英语',85);
1.查询student表的所有记录
mysql> select * from student;
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-------+--------------+--------------------+
2.查询student表的第2条到4条记录
mysql> select *from student limit 1,3;
+-----+-----------+------+-------+------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+------------+--------------------+
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
+-----+-----------+------+-------+------------+--------------------+
3.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
mysql> select id,name,department
-> from student;
+-----+-----------+--------------+
| id | name | department |
+-----+-----------+--------------+
| 901 | 张老大 | 计算机系 |
| 902 | 张老二 | 中文系 |
| 903 | 张三 | 中文系 |
| 904 | 李四 | 英语系 |
| 905 | 王五 | 英语系 |
| 906 | 王六 | 计算机系 |
+-----+-----------+--------------+
4.从student表中查询计算机系和英语系的学生的信息
mysql> select *
-> from student
-> where department in ('计算机系','英语系');
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-------+--------------+--------------------+
5.从student表中查询年龄18~22岁的学生信息
mysql> select * from student where year(now())-birth>=18 and year(now())-birth<=22;
Empty set (0.01 sec)
mysql> select * from student where year(now())-birth between 18 and 22;
Empty set (0.00 sec)
6.从student表中查询每个院系有多少人
mysql> select department,count(1) from student group by department;
+--------------+-------------------+
| department | count(department) |
+--------------+-------------------+
| 中文系 | 2 |
| 英语系 | 2 |
| 计算机系 | 2 |
+--------------+-------------------+
7.从score表中查询每个科目的最高分
mysql> select c_name,max(grade) from score group by c_name;
+-----------+------------+
| c_name | max(grade) |
+-----------+------------+
| 中文 | 95 |
| 英语 | 94 |
| 计算机 | 98 |
+-----------+------------+
8.查询李四的考试科目(c_name)和考试成绩(grade)
mysql> select b.c_name,b.grade from student a inner join score b on a.id=b.stu_id where a.name='李四';
+-----------+-------+
| c_name | grade |
+-----------+-------+
| 计算机 | 70 |
| 英语 | 92 |
+-----------+-------+
9.用连接的方式查询所有学生的信息和考试信息
mysql> select * from student a inner join score b on a.id=b.stu_id;
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 1 | 901 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 2 | 901 | 英语 | 80 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 4 | 902 | 中文 | 88 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 5 | 903 | 中文 | 95 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 6 | 904 | 计算机 | 70 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 7 | 904 | 英语 | 92 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 8 | 905 | 英语 | 94 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 10 | 906 | 英语 | 85 |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
mysql> select *from student a left join score b on a.id=b.stu_id;
+-----+-----------+------+-------+--------------+--------------------+------+--------+-----------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+------+--------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 1 | 901 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 2 | 901 | 英语 | 80 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 4 | 902 | 中文 | 88 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 5 | 903 | 中文 | 95 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 6 | 904 | 计算机 | 70 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 7 | 904 | 英语 | 92 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 8 | 905 | 英语 | 94 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 10 | 906 | 英语 | 85 |
+-----+-----------+------+-------+--------------+--------------------+------+--------+-----------+-------+
10.计算每个学生的总成绩
mysql> select a.id,a.name,sum(b.grade) from student a inner join score b on a.id=b.stu_id
-> group by b.stu_id;
+-----+-----------+--------------+
| id | name | sum(b.grade) |
+-----+-----------+--------------+
| 901 | 张老大 | 178 |
| 902 | 张老二 | 153 |
| 903 | 张三 | 95 |
| 904 | 李四 | 162 |
| 905 | 王五 | 94 |
| 906 | 王六 | 175 |
+-----+-----------+--------------+
11.计算每个考试科目的平均成绩
mysql> select c_name,avg(grade) from score group by c_name;
+-----------+------------+
| c_name | avg(grade) |
+-----------+------------+
| 中文 | 91.5000 |
| 英语 | 87.7500 |
| 计算机 | 80.7500 |
+-----------+------------+
12.查询计算机成绩低于95的学生信息
mysql> select * from student a inner join score b on a.id=b.stu_id where b.c_name='计算机' and b.grade<95;
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 6 | 904 | 计算机 | 70 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 90 |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
mysql> select * from student where id in (select stu_id from score where c_name='计算机' and grade<95);
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-------+--------------+--------------------+
13.查询同时参加计算机和英语考试的学生的信息
mysql> select * from student where id in( select a.stu_id from score a inner join score b on a.stu_id=b.stu_id and a.c_name='计算机' and b.c_name='英语');
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-------+--------------+--------------------+
14.将计算机考试成绩按从高到低进行排序
mysql> select * from score where c_name='计算机' order by grade desc;
+----+--------+-----------+-------+
| id | stu_id | c_name | grade |
+----+--------+-----------+-------+
| 1 | 901 | 计算机 | 98 |
| 9 | 906 | 计算机 | 90 |
| 6 | 904 | 计算机 | 70 |
| 3 | 902 | 计算机 | 65 |
+----+--------+-----------+-------+
15.从student表和score表中查询出学生的学号,然后合并查询结果
mysql> select id from student
-> union
-> select stu_id from score;
+-----+
| id |
+-----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
+-----+
mysql> select id from student union all select stu_id from score;
+-----+
| id |
+-----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
| 901 |
| 901 |
| 902 |
| 902 |
| 903 |
| 904 |
| 904 |
| 905 |
| 906 |
| 906 |
+-----+
16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
mysql> select a.*,b.c_name,b.grade from student a inner join score b on a.id=b.stu_id where a.name like '张%' or a.name like'王%';
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 中文 | 88 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 英语 | 94 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
mysql> select a.name,a.department,b.c_name,b.grade from student a inner join score b on a.id=b.stu_id where a.name like '张%' or a.name like'王%';
+-----------+--------------+-----------+-------+
| name | department | c_name | grade |
+-----------+--------------+-----------+-------+
| 张老大 | 计算机系 | 计算机 | 98 |
| 张老大 | 计算机系 | 英语 | 80 |
| 张老二 | 中文系 | 计算机 | 65 |
| 张老二 | 中文系 | 中文 | 88 |
| 张三 | 中文系 | 中文 | 95 |
| 王五 | 英语系 | 英语 | 94 |
| 王六 | 计算机系 | 计算机 | 90 |
| 王六 | 计算机系 | 英语 | 85 |
+-----------+--------------+-----------+-------+
mysql> select a.*,b.c_name,b.grade from student a,score b where a.id=b.stu_id and (a.name like '张%' or a.name like'王%');
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 中文 | 88 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 英语 | 94 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
mysql> select a.*,b.c_name,b.grade from student a,score b where a.id=b.stu_id and name regexp '^[张王%]'';
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 中文 | 88 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 | 中文 | 95 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 | 英语 | 94 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 英语 | 85 |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
mysql> select a.name,(year(now())-a.birth),a.department,b.c_name,b.grade from student a inner join score b on a.id=b.stu_id where a.address like '湖南%';
+--------+-----------------------+--------------+-----------+-------+
| name | (year(now())-a.birth) | department | c_name | grade |
+--------+-----------------------+--------------+-----------+-------+
| 张三 | 31 | 中文系 | 中文 | 95 |
| 王六 | 33 | 计算机系 | 计算机 | 90 |
| 王六 | 33 | 计算机系 | 英语 | 85 |
+--------+-----------------------+--------------+-----------+-------+
练习2
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`mgr` int(4) NULL DEFAULT NULL,
`hiredate` date NOT NULL,
`sai` int(255) NOT NULL,
`comm` int(255) NULL DEFAULT NULL,
`deptno` int(2) NOT NULL,
PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);
-- 1. 查询出部门编号为30的所有员工
mysql> select *
-> from emp
-> where deptno=30;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
-- 2. 所有销售员的姓名、编号和部门编号。
mysql> select ename,empno,deptno from emp where job='销售员';
+-----------+-------+--------+
| ename | empno | deptno |
+-----------+-------+--------+
| 黛绮丝 | 1002 | 30 |
| 殷天正 | 1003 | 30 |
| 谢逊 | 1005 | 30 |
| 韦一笑 | 1010 | 30 |
+-----------+-------+--------+
-- 3. 找出奖金高于工资的员工。
mysql> select * from emp
-> where comm>sai;
+-------+--------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+-----------+------+------------+-------+-------+--------+
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
+-------+--------+-----------+------+------------+-------+-------+--------+
-- 4. 找出奖金高于工资60%的员工。
mysql> select * from emp where comm>sai*0.6;
+-------+--------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+-----------+------+------------+-------+-------+--------+
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
+-------+--------+-----------+------+------------+-------+-------+--------+
-- 5. 找出部门编号为10中所有经理,和部门编号为20中所有销售员的详细资料。
mysql> select * from emp
-> where (deptno=10 and job='经理') or (deptno=20 and job='销售员');
+-------+--------+--------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+--------+------+------------+-------+------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
+-------+--------+--------+------+------------+-------+------+--------+
-- 6. 找出部门编号为10中所有经理,部门编号为20中所有销售员,还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。
mysql> select * from emp where (deptno=10 and job='经理') or (deptno=20 and job='销售员') or ((job!='经 理' and job!='销售员') and sai>=20000);
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
-- 7. 无奖金或奖金低于1000的员工。
mysql> select * from emp
-> where comm is null or comm<1000;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
+-------+-----------+-----------+------+------------+-------+------+--------+
-- 8. 查询名字由三个字组成的员工。注意:一个汉字占三个字节
mysql> select * from emp where ename like '___';
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
mysql> select * from emp where length(ename)=9;
+-------+-----------+-----------+------+------------+-------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+------+--------+
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
+-------+-----------+-----------+------+------------+-------+------+--------+
-- 9.查询2000年入职的员工。
mysql> select * from emp
-> where year(hiredate)=2000
-> order by empno;
+-------+--------+--------+------+------------+------+------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+--------+--------+------+------------+------+------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
+-------+--------+--------+------+------------+------+------+--------+
-- 10. 查询所有员工详细信息,用编号升序排序
mysql> select * from emp
-> order by empno;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
-- 11. 查询所有员工详细信息,用工资降序排序,如果工资相同使用入职日期升序排序
mysql> select * from emp order by sai desc,hiredate;
+-------+-----------+-----------+------+------------+-------+-------+--------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-------+-----------+-----------+------+------------+-------+-------+--------+
| 1015 | 张三 | 保洁员 | 1001 | 2013-05-01 | 80000 | 50000 | 50 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
+-------+-----------+-----------+------+------------+-------+-------+--------+
-- 12.查询每个部门的平均工资
mysql> select deptno,avg(sai) from emp group by deptno;
+--------+------------+
| deptno | avg(sai) |
+--------+------------+
| 10 | 29166.6667 |
| 20 | 21750.0000 |
| 30 | 15666.6667 |
| 50 | 80000.0000 |
+--------+------------+
-- 13.查询每个部门的雇员数量
mysql> select deptno,count(1)
-> from emp
-> group by deptno;
+--------+----------+
| deptno | count(1) |
+--------+----------+
| 10 | 3 |
| 20 | 5 |
| 30 | 6 |
| 50 | 1 |
+--------+----------+
-- 14.查询每种工作的最高工资、最低工资、人数
mysql> select job,max(sai),min(sai),count(1) from emp group by job;
+-----------+----------+----------+----------+
| job | max(sai) | min(sai) | count(1) |
+-----------+----------+----------+----------+
| 保洁员 | 80000 | 80000 | 1 |
| 分析师 | 30000 | 30000 | 2 |
| 文员 | 13000 | 8000 | 4 |
| 经理 | 29750 | 24500 | 3 |
| 董事长 | 50000 | 50000 | 1 |
| 销售员 | 16000 | 12500 | 4 |
+-----------+----------+----------+----------+
CREATE TABLE `dept` (
`deptno` int NOT NULL ,
`dname` char(9) NOT NULL ,
`loc` char(6) NOT NULL
);
INSERT INTO `dept` VALUES (10, '教研部', '北京');
INSERT INTO `dept` VALUES (20, '学工部', '上海');
INSERT INTO `dept` VALUES (30, '销售部', '广州');
INSERT INTO `dept` VALUES (40, '财务部', '武汉');
1. 查出至少有一个员工的部门。
mysql> select a.*,b.total from dept a inner join (select deptno,count(1) total from emp group by deptno having count(1)>=1)b on a.deptno=b.deptno;
+--------+-----------+--------+-------+
| deptno | dname | loc | total |
+--------+-----------+--------+-------+
| 10 | 教研部 | 北京 | 3 |
| 20 | 学工部 | 上海 | 5 |
| 30 | 销售部 | 广州 | 6 |
+--------+-----------+--------+-------+
2. 列出所有员工的姓名及其直接上级的姓名。
mysql> select a.ename,b.ename from emp a left join emp b on a.mgr=b.empno;
+-----------+-----------+
| ename | ename |
+-----------+-----------+
| 甘宁 | 庞统 |
| 黛绮丝 | 关羽 |
| 殷天正 | 关羽 |
| 刘备 | 曾阿牛 |
| 谢逊 | 关羽 |
| 关羽 | 曾阿牛 |
| 张飞 | 曾阿牛 |
| 诸葛亮 | 刘备 |
| 曾阿牛 | NULL |
| 韦一笑 | 关羽 |
| 周泰 | 关羽 |
| 程普 | 关羽 |
| 庞统 | 刘备 |
| 黄盖 | 张飞 |
| 张三 | 甘宁 |
+-----------+-----------+
3.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
mysql> select a.dname,b.* from dept a left join emp b on a.deptno=b.deptno;
+-----------+-------+-----------+-----------+------+------------+-------+-------+--------+
| dname | empno | ename | job | mgr | hiredate | sai | comm | deptno |
+-----------+-------+-----------+-----------+------+------------+-------+-------+--------+
| 学工部 | 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 |
| 销售部 | 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 |
| 销售部 | 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 |
| 学工部 | 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 |
| 销售部 | 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 |
| 销售部 | 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 |
| 教研部 | 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 |
| 学工部 | 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 |
| 教研部 | 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 |
| 销售部 | 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 |
| 学工部 | 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 |
| 销售部 | 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 |
| 学工部 | 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 |
| 教研部 | 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 |
| 财务部 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+-------+-----------+-----------+------+------------+-------+-------+--------+
mysql> select a.*,b.dname from emp a right join dept b on a.deptno=b.deptno;
+-------+-----------+-----------+------+------------+-------+-------+--------+-----------+
| empno | ename | job | mgr | hiredate | sai | comm | deptno | dname |
+-------+-----------+-----------+------+------------+-------+-------+--------+-----------+
| 1001 | 甘宁 | 文员 | 1013 | 2000-12-17 | 8000 | NULL | 20 | 学工部 |
| 1002 | 黛绮丝 | 销售员 | 1006 | 2001-02-20 | 16000 | 3000 | 30 | 销售部 |
| 1003 | 殷天正 | 销售员 | 1006 | 2001-02-22 | 12500 | 5000 | 30 | 销售部 |
| 1004 | 刘备 | 经理 | 1009 | 2001-04-02 | 29750 | NULL | 20 | 学工部 |
| 1005 | 谢逊 | 销售员 | 1006 | 2001-09-28 | 12500 | 14000 | 30 | 销售部 |
| 1006 | 关羽 | 经理 | 1009 | 2001-05-01 | 28500 | NULL | 30 | 销售部 |
| 1007 | 张飞 | 经理 | 1009 | 2001-09-01 | 24500 | NULL | 10 | 教研部 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2007-04-19 | 30000 | NULL | 20 | 学工部 |
| 1009 | 曾阿牛 | 董事长 | NULL | 2001-11-17 | 50000 | NULL | 10 | 教研部 |
| 1010 | 韦一笑 | 销售员 | 1006 | 2001-09-08 | 15000 | 0 | 30 | 销售部 |
| 1011 | 周泰 | 文员 | 1006 | 2007-05-23 | 11000 | NULL | 20 | 学工部 |
| 1012 | 程普 | 文员 | 1006 | 2001-12-03 | 9500 | NULL | 30 | 销售部 |
| 1013 | 庞统 | 分析师 | 1004 | 2001-12-03 | 30000 | NULL | 20 | 学工部 |
| 1014 | 黄盖 | 文员 | 1007 | 2002-01-23 | 13000 | NULL | 10 | 教研部 |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 财务部 |
+-------+-----------+-----------+------+------------+-------+-------+--------+-----------+
4.列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
mysql> select a.empno,a.ename,c.dname
-> from emp a,emp b,dept c
-> where a.mgr=b.empno
-> and a.deptno=c.deptno
-> and b.deptno=c.deptno
-> and a.hiredate<b.hiredate;
+-------+-----------+-----------+
| empno | ename | dname |
+-------+-----------+-----------+
| 1001 | 甘宁 | 学工部 |
| 1002 | 黛绮丝 | 销售部 |
| 1003 | 殷天正 | 销售部 |
| 1007 | 张飞 | 教研部 |
+-------+-----------+-----------+
5.返回拥有员工的部门名、部门号
mysql> select dname,deptno from dept where deptno in (select deptno from emp group by deptno having count(1)>=1);
+-----------+--------+
| dname | deptno |
+-----------+--------+
| 教研部 | 10 |
| 学工部 | 20 |
| 销售部 | 30 |
+-----------+--------+