mysql基本查询语句review-2

最新推荐文章于 2023-09-11 21:10:07 发布

fly9006

最新推荐文章于 2023-09-11 21:10:07 发布

阅读量405

点赞数

分类专栏：日常随记 Database 文章标签： mysql

本文链接：https://blog.csdn.net/fly9006/article/details/55683339

版权

日常随记同时被 2 个专栏收录

17 篇文章 0 订阅

订阅专栏

Database

4 篇文章 0 订阅

订阅专栏

review-2为同样参考借鉴他人的练习题

1，创建表

mysql> CREATE TABLE dept(
    -> deptno INT(3) PRIMARY KEY,
    -> dname VARCHAR(15),
    -> loc VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE emp(
    -> empno int(3) PRIMARY KEY,
    -> ename VARCHAR(10),
    -> job VARCHAR(10),
    -> mgr INT(4),
    -> hiredate datetime,
    -> sal DOUBLE,
    -> comm DOUBLE,
    -> deptno INT(3),
    -> FOREIGN KEY(deptno) REFERENCES dept(deptno)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE salgrade(
    -> grade INT(3) PRIMARY KEY,
    -> losal INT(3),
    -> hisal INT(3)
    -> );
Query OK, 0 rows affected (0.03 sec)

2,插入数据

insert into dept values(10,'Accounting','New York');
insert into dept values(20,'Research','Dallas');
insert into dept values(30,'Sales','Chicago');
insert into dept values(40,'Operations','Boston');
insert into dept values(50,'Admin','Washing');

insert into emp values(7369,'Smith','Clerk',7902,'1980-12-17',800,0,20);
insert into emp values(7499,'Allen','Salesman',7698,'1981-2-20',1600,300,30);
insert into emp values(7844,'Turner','Salesman',7499,'1981-9-8',1500,0,30);
insert into emp values(7698,'Tom','Manager',0,'1981-9-8',6100,600,40);
insert into emp values(7876,'Adams','Clerk',7900,'1987-5-23',1100,0,20);
insert into emp values(7900,'James','Clerk',7698,'1981-12-3',2400,0,30);
insert into emp values(7902,'Ford','Analyst',7698,'1981-12-3',3000,null,20);
insert into emp values(7901,'Kik','Clerk',7900,'1981-12-3',1900,0,30);

insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,5000);
insert into salgrade values(6,5001,10000);

3，练习

3.1查询雇员表中工资最高的雇员的员工号、员工姓名、工资和部门号。

方法1 降序然后输出第一个emp

mysql> SELECT empno,ename,sal,deptno FROM emp ORDER BY sal DESC LIMIT 1;
+-------+-------+------+--------+
| empno | ename | sal  | deptno |
+-------+-------+------+--------+
|  7698 | Tom   | 6100 |     40 |
+-------+-------+------+--------+
1 row in set (0.01 sec)

方法2 单表嵌套查询

mysql> SELECT empno,ename,sal,deptno FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
+-------+-------+------+--------+
| empno | ename | sal  | deptno |
+-------+-------+------+--------+
|  7698 | Tom   | 6100 |     40 |
+-------+-------+------+--------+
1 row in set (0.02 sec)

3.2薪水大于1200的雇员，按照部门编号进行分组，分组后的平均薪水必须大于1500，查询各分组的平均工资，按照工资的倒序进行排列。

mysql> SELECT deptno, AVG(sal) AS avg FROM emp WHERE sal > 1200 GROUP BY deptno HAVING AVG(sal) > 1500 ORDER BY AVG(sal) DESC;
+--------+------+
| deptno | avg  |
+--------+------+
|     40 | 6100 |
|     20 | 3000 |
|     30 | 1850 |
+--------+------+
3 rows in set (0.00 sec)

3.3查询每个雇员和其所在的部门名
简单的两表连接查询

mysql> SELECT e.ename, e.deptno, d.dname FROM emp e JOIN dept d ON d.deptno=e.deptno;
+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| Smith  |     20 | Research   |
| Allen  |     30 | Sales      |
| Tom    |     40 | Operations |
| Turner |     30 | Sales      |
| Adams  |     20 | Research   |
| James  |     30 | Sales      |
| Kik    |     30 | Sales      |
| Ford   |     20 | Research   |
+--------+--------+------------+
8 rows in set (0.01 sec)

3.4查询每个雇员姓名及其工资所在的等级

mysql> SELECT e.ename, s.grade FROM emp e JOIN salgrade s ON (e.sal<s.hisal AND e.sal>s.losal);
+--------+-------+
| ename  | grade |
+--------+-------+
| Smith  |     1 |
| Allen  |     3 |
| Tom    |     6 |
| Turner |     3 |
| Adams  |     1 |
| James  |     4 |
| Kik    |     3 |
+--------+-------+
7 rows in set (0.00 sec)

3.5查询雇员名第2个字母不是a的雇员的姓名、所在的部门名、工资所在的等级。

mysql> SELECT e.ename, s.grade FROM emp e JOIN salgrade s ON (e.sal<s.hisal AND e.sal>s.losal) WHERE e.ename NOT LIKE '_a%';
+--------+-------+
| ename  | grade |
+--------+-------+
| Smith  |     1 |
| Allen  |     3 |
| Tom    |     6 |
| Turner |     3 |
| Adams  |     1 |
| Kik    |     3 |
+--------+-------+
6 rows in set (0.01 sec)

3.6查询每个雇员和其经理的姓名

mysql> SELECT e1.ename,e2.ename FROM emp e1 JOIN emp e2 ON e1.mgr=e2.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| Smith  | Ford  |
| Allen  | Tom   |
| Turner | Allen |
| Adams  | James |
| James  | Tom   |
| Kik    | James |
| Ford   | Tom   |
+--------+-------+
7 rows in set (0.00 sec)

3.7查询每个雇员和其经理的姓名（包括公司老板本身（他上面没有经理））

mysql> SELECT e1.ename,e2.ename FROM emp e1 LEFT JOIN emp e2 ON e1.mgr=e2.empno;
+--------+-------+
| ename  | ename |
+--------+-------+
| Smith  | Ford  |
| Allen  | Tom   |
| Tom    | NULL  |
| Turner | Allen |
| Adams  | James |
| James  | Tom   |
| Kik    | James |
| Ford   | Tom   |
+--------+-------+
8 rows in set (0.00 sec)

3.8查询每个雇员的姓名及其所在部门的部门名（包括没有雇员的部门）

mysql> SELECT e.ename,d.dname FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
+--------+------------+
| ename  | dname      |
+--------+------------+
| Smith  | Research   |
| Allen  | Sales      |
| Tom    | Operations |
| Turner | Sales      |
| Adams  | Research   |
| James  | Sales      |
| Kik    | Sales      |
| Ford   | Research   |
| NULL   | Admin      |
+--------+------------+
9 rows in set (0.00 sec)

3.9子查询1：查询每个部门中工资最高的人的姓名、薪水和部门编号

mysql> SELECT ename, sal, m.deptno FROM emp e JOIN (SELECT MAX(sal) maxsal, deptno FROM emp GROUP BY deptno) m ON (e.sal=m.maxsal AND e.deptno=m.deptno);
+-------+------+--------+
| ename | sal  | deptno |
+-------+------+--------+
| Tom   | 6100 |     40 |
| James | 2400 |     30 |
| Ford  | 3000 |     20 |
+-------+------+--------+
3 rows in set (0.00 sec)

3.10子查询2：查询每个部门平均工资所在的等级

mysql> SELECT deptno, avgsal, grade FROM (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno) e JOIN salgrade s ON (e.avgsal BETWEEN s.losal AND
s.hisal);
+--------+--------------------+-------+
| deptno | avgsal             | grade |
+--------+--------------------+-------+
|     20 | 1633.3333333333333 |     3 |
|     30 |               1850 |     3 |
|     40 |               6100 |     6 |
+--------+--------------------+-------+
3 rows in set (0.00 sec)

3.11子查询3：查询每个部门内平均的薪水等级

mysql> SELECT deptno, AVG(grade) avggrade FROM (SELECT e.ename, e.deptno,s.grade FROM emp e JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal)) t
GROUP BY deptno;
+--------+----------+
| deptno | avggrade |
+--------+----------+
|     20 |   2.0000 |
|     30 |   3.2500 |
|     40 |   6.0000 |
+--------+----------+
3 rows in set (0.00 sec)

总结：
Select //要取的字段
From tableName //单个表，或多张表，有时为嵌套查询语句提供的表！
Where //简单判断
Group By //用于分组
Having //用于对组函数进行判断
Order By //排序
Limit //分页

一般来说：select（要查询的字段）->from（从哪一张或哪几张表或视图）->where（过滤条件）->group by(having)（分组及条件）->order by（按哪个或哪几个字段进行升序或降序排列 ->limit限制数）

涉及到join on子句时，会有两种情况出现，如果表之间有等值关系则直接on后面接相等值，如果为不等值关系，则on后面接判断语句或区间语句等