MySQL数据库高级操作-多表操作

最新推荐文章于 2024-07-30 19:26:47 发布
最新推荐文章于 2024-07-30 19:26:47 发布
阅读量452 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_50641264/article/details/122333454
版权

1.多表联查

1.两个表等值和非等值查询

  • 多表查询的时候必须要加条件
    • 等值
    • 非等值
# 直接使用表名查询
select * from emp,dept where emp.deptno = dept.deptno;
# 起个别名查询
select * from emp a,dept b where a.deptno = b.deptno;
  • 等值查询结果
mysql> select * from emp,dept where emp.deptno = dept.deptno;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800  | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 |     30 | SALES      | CHICAGO  |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 | SALES      | CHICAGO  |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 |     30 | SALES      | CHICAGO  |
|  7900 | JAM%ES | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 |     30 | SALES      | CHICAGO  |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
13 rows in set

mysql> select * from emp a,dept b where a.deptno = b.deptno;
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | deptno | dname      | loc      |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     10 | ACCOUNTING | NEW YORK |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800  | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 | RESEARCH   | DALLAS   |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |     30 | SALES      | CHICAGO  |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 |     30 | SALES      | CHICAGO  |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     30 | SALES      | CHICAGO  |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 | SALES      | CHICAGO  |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 |     30 | SALES      | CHICAGO  |
|  7900 | JAM%ES | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 |     30 | SALES      | CHICAGO  |
+-------+--------+-----------+------+------------+------+------+--------+--------+------------+----------+
13 rows in set
  • 非等值查询select * from emp e, salgrade s where e.sal between s.losal and s.hisal;
mysql> select * from emp e, salgrade s where e.sal between s.losal and s.hisal;
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | grade | losal | hisal |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800  | NULL |     20 |     1 |   700 |  1200 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |     3 |  1401 |  2000 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 |     2 |  1201 |  1400 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     4 |  2001 |  3000 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     2 |  1201 |  1400 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     4 |  2001 |  3000 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     4 |  2001 |  3000 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     5 |  3001 |  9999 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 |     3 |  1401 |  2000 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     1 |   700 |  1200 |
|  7900 | JAM%ES | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 |     1 |   700 |  1200 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
13 rows in set

mysql> select * from emp e, salgrade s ;

2.查询应用

  • 查询薪资大于2000的员工的办公地点
    • select e.ename,d.loc from emp e, dept d where e.deptno=d.deptno and sal>=2000;
mysql> select e.ename,d.loc from emp e, dept d where e.deptno=d.deptno and sal>=2000;
+-------+----------+
| ename | loc      |
+-------+----------+
| JONES | DALLAS   |
| BLAKE | CHICAGO  |
| CLARK | NEW YORK |
| SCOTT | DALLAS   |
| KING  | NEW YORK |
| FORD  | DALLAS   |
+-------+----------+
6 rows in set

3.表与表的关联方式（99语法）

  • 自然链接natural join
    • 会自动选择列明相同并且类型相同的列
    • select * from emp natural join dept;
  • using
    • 默认做等值链接
    • select * from emp join dept using(deptno) where sal>2000;
mysql> select * from emp join dept using(deptno) where sal>2000;
+--------+-------+-------+-----------+------+------------+------+------+------------+----------+
| deptno | empno | ename | job       | mgr  | hiredate   | sal  | comm | dname      | loc      |
+--------+-------+-------+-----------+------+------------+------+------+------------+----------+
|     20 |  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL | RESEARCH   | DALLAS   |
|     30 |  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL | SALES      | CHICAGO  |
|     10 |  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL | ACCOUNTING | NEW YORK |
|     20 |  7788 | SCOTT | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL | RESEARCH   | DALLAS   |
|     10 |  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | ACCOUNTING | NEW YORK |
|     20 |  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL | RESEARCH   | DALLAS   |
+--------+-------+-------+-----------+------+------------+------+------+------------+----------+
6 rows in set
  • on
    • 主要做非等值查询 也可以做等值查询
    • 非等值查询select * from emp e join salgrade s on(e.sal between s.losal and s.hisal);
    • 等值查询select * from emp e join dept d on(e.deptno=d.deptno) where sal >2000
mysql> select * from emp e join salgrade s on(e.sal between s.losal and s.hisal);
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno | grade | losal | hisal |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 | 800  | NULL |     20 |     1 |   700 |  1200 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |     3 |  1401 |  2000 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 |     2 |  1201 |  1400 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     4 |  2001 |  3000 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     2 |  1201 |  1400 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     4 |  2001 |  3000 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |     4 |  2001 |  3000 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     5 |  3001 |  9999 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 |     3 |  1401 |  2000 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |     1 |   700 |  1200 |
|  7900 | JAM%ES | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 |     1 |   700 |  1200 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     4 |  2001 |  3000 |
+-------+--------+-----------+------+------------+------+------+--------+-------+-------+-------+
13 rows in set

4.多表查询

  • 查询员工的部门名称和薪资等级
  • select * from emp e join dept d using(deptno) join salgrade s on(e.sal between s.losal and s.hisal);
mysql> select e.ename,d.dname,s.grade from emp e join dept d using(deptno) join salgrade s on(e.sal between s.losal and s.hisal);
+--------+------------+-------+
| ename  | dname      | grade |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAM%ES | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
+--------+------------+-------+
13 rows in set

5.多表联查左右外连接

  • 查询每个员工所属的部门
  • select e.ename, d.deptno from emp e left join dept d using(deptno);
mysql> select e.ename, d.deptno from emp e left join dept d using(deptno);
+--------+--------+
| ename  | deptno |
+--------+--------+
| SMITH  |     20 |
| ALLEN  |     30 |
| WARD   |     30 |
| JONES  |     20 |
| MARTIN |     30 |
| BLAKE  |     30 |
| CLARK  |     10 |
| SCOTT  |     20 |
| KING   |     10 |
| TURNER |     30 |
| ADAMS  |     20 |
| JAM%ES |     30 |
| FORD   |     20 |
| MILLER | NULL   |
+--------+--------+
14 rows in set
  • 查询每个部门分别有多少个员工
  • select d.deptno,d.dname,count(e.empno) from emp e right join dept d using(deptno) group by d.deptno ,d.dname;
mysql> select d.deptno,d.dname,count(e.empno) from emp e right join dept d using(deptno) group by d.deptno ,d.dname;
+--------+------------+----------------+
| deptno | dname      | count(e.empno) |
+--------+------------+----------------+
|     10 | ACCOUNTING |              2 |
|     20 | RESEARCH   |              5 |
|     30 | SALES      |              6 |
|     40 | OPERATIONS |              0 |
+--------+------------+----------------+
4 rows in set

6.union 链接（拼接）

  • 查询 员工姓名和员工薪资 在部门1并且名字中含有A的员工，和在部门2薪资>1000的员工，和在部门三薪资<2000的员工
  • sql语句三条查询
select ename,sal from emp where deptno = 10 and ename like '%A%';
select ename,sal from emp where deptno = 20 and sal>1000;
select ename,sal from emp where deptno=30 and sal<2000; 
# 使用union的语法
select ename,sal,deptno from emp where deptno = 10 and ename like '%A%' union select ename,sal,deptno from emp where deptno = 20 and sal>1000 union select ename,sal,deptno from emp where deptno=30 and sal<2000;
  • 使用union把查询的和累计起来结果，会自动去重复
  • union all 进行直接链接
mysql> select ename,sal,deptno from emp where deptno = 10 and ename like '%A%' union select ename,sal,deptno from emp where deptno = 20 and sal>1000 union select name,sal,deptno from emp where deptno=30 and sal<2000; 
+--------+------+--------+
| ename  | sal  | deptno |
+--------+------+--------+
| CLARK  | 2450 |     10 |
| JONES  | 2975 |     20 |
| SCOTT  | 3000 |     20 |
| ADAMS  | 1100 |     20 |
| FORD   | 3000 |     20 |
| ALLEN  | 1600 |     30 |
| WARD   | 1250 |     30 |
| MARTIN | 1250 |     30 |
| TURNER | 1500 |     30 |
| JAM%ES | 950  |     30 |
+--------+------+--------+
10 rows in set
  • 查询所有不符合连接条件的 员工和部门的名称
  • select e.ename,d.dname from emp e left join dept d using(deptno) union select e.ename ,d.dname from emp e right join dept d using(deptno);
mysql> select e.ename,d.dname from emp e left join dept d using(deptno) union select e.ename ,d.dname from emp e right join dept d using(deptno);
+--------+------------+
| ename  | dname      |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAM%ES | SALES      |
| FORD   | RESEARCH   |
| MILLER | NULL       |
| NULL   | OPERATIONS |
+--------+------------+
15 rows in set

7.表与表的自连接

  • 给表起两个不同的小名用on链接,当做两个不同意义的表来处理
  • 查询出员工员工id所对应的 领导和领导id
  • select e.empno,e.ename,m.empno '领导ID',m.ename from emp e join emp m on(e.mgr = m.empno);
mysql> select e.empno,e.ename,m.empno '领导ID',m.ename from emp e join emp m on(e.mgr = m.empno);
+-------+--------+--------+-------+
| empno | ename  | 领导ID | ename |
+-------+--------+--------+-------+
|  7369 | SMITH  |   7902 | FORD  |
|  7499 | ALLEN  |   7698 | BLAKE |
|  7521 | WARD   |   7698 | BLAKE |
|  7566 | JONES  |   7839 | KING  |
|  7654 | MARTIN |   7698 | BLAKE |
|  7698 | BLAKE  |   7839 | KING  |
|  7782 | CLARK  |   7839 | KING  |
|  7788 | SCOTT  |   7566 | JONES |
|  7844 | TURNER |   7698 | BLAKE |
|  7876 | ADAMS  |   7788 | SCOTT |
|  7900 | JAM%ES |   7698 | BLAKE |
|  7902 | FORD   |   7566 | JONES |
|  7934 | MILLER |   7782 | CLARK |
+-------+--------+--------+-------+
13 rows in set

8.表与表的子链接

  • 把一个sql的查询结果当做另一个sql的查询条件
  • 查询谁的工资比公司的平均工资高
  • select * from emp where sal >(select avg(sal) from emp);
mysql> select * from emp where sal >(select avg(sal) from emp);
+-------+-------+-----------+------+------------+------+------+--------+
| empno | ename | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+-------+-----------+------+------------+------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
+-------+-------+-----------+------+------------+------+------+--------+
6 rows in set
  • 查询谁的薪资低于30部门所有人的薪资
  • all返回多行结果
  • select * from emp where sal < (select min(sal) from emp where deptno= 30);等于select * from emp where sal < all(select sal from emp where deptno= 30);
mysql> select * from emp where sal < (select min(sal) from emp where  deptno= 30);
+-------+-------+-------+------+------------+-----+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal | comm | deptno |
+-------+-------+-------+------+------------+-----+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL |     20 |
+-------+-------+-------+------+------------+-----+------+--------+
1 row in set

mysql> select * from emp where sal < all(select sal from emp where  deptno= 30);
+-------+-------+-------+------+------------+-----+------+--------+
| empno | ename | job   | mgr  | hiredate   | sal | comm | deptno |
+-------+-------+-------+------+------------+-----+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL |     20 |
+-------+-------+-------+------+------------+-----+------+--------+
1 row in set
  • 查询谁的薪资低于30部门部分人的薪资
  • any返回多行结果
  • select * from emp where sal < any(select sal from emp where deptno= 30);
mysql> select * from emp where sal < any(select sal from emp where  deptno= 30);
+-------+--------+----------+------+------------+------+------+--------+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+----------+------+------------+------+------+--------+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 | 800  | NULL |     20 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 | 300  |     30 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250 | 500  |     30 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0    |     30 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-07-13 | 1100 | NULL |     20 |
|  7900 | JAM%ES | CLERK    | 7698 | 1981-12-03 | 950  | NULL |     30 |
|  7934 | MILLER | CLERK    | 7782 | 1982-01-23 | 1300 | NULL | NULL   |
+-------+--------+----------+------+------------+------+------+--------+
9 rows in set

9.表与表的伪表查询

  • 如果我们所需要的查询条件需要别的SQL语句提供；

  • 如果只需要一个条件那么可以使用子查询来完成；

  • 如果需要多个查询条件，这是就要将所有的查询结果当做伪表进行管理；

  • 我们需要把一些含有特殊符号的列名设置别名，然后给伪表设置一个别名(见名知意)；

  • 查询员工信息 高于自己部门的平均薪资的员工

  • select * from emp e join (select deptno ,avg(sal) avgsal from emp group by deptno) da on(e.deptno =da.deptno and e.sal>da.avgsal) order by e.deptno;

mysql> select * from emp e join (select deptno ,avg(sal) avgsal from emp group by deptno) da on(e.deptno =da.deptno and e.sal>da.avgsal) order by e.deptno;
+-------+-------+-----------+------+------------+------+------+--------+--------+-------------+
| empno | ename | job       | mgr  | hiredate   | sal  | comm | deptno | deptno | avgsal      |
+-------+-------+-----------+------+------------+------+------+--------+--------+-------------+
|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |     10 | 3725        |
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 | 2175        |
|  7788 | SCOTT | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |     20 | 2175        |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 | 2175        |
|  7499 | ALLEN | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |     30 | 1566.666667 |
|  7698 | BLAKE | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 | 1566.666667 |
+-------+-------+-----------+------+------------+------+------+--------+--------+-------------+
6 rows in set

2.增删改操作

1.增操作

insert into 表名 values(10,'测试','测试');

2.改操作

  • update表名set列名=value ,列名=value
  • update salgrade set losal = 888 , hisal = 999;
  • update表名set列名=value ,列名=value where条件
  • update salgrade set losal = 666 ,hisal = 1888 where grade = 3;

3.删操作

  • delete from表名
  • delete from dept;
  • delete from表名where条件
  • delete from emp where comm is null;·这属于一种物理删除，删完之后理论上不能再找回

4.事务

  • 开始事务：BEGIN或者和start TRANSACTION
  • 结束事务：ROLLBACK;
  • 事务的特性：ACID原则 原子性,一致性,隔离性,持久性
  • 事务的隔离级别：读未提交,读已提交,可重复度,序列化
  • 脏读：读取别人为提交的数据可能是不安全的
  • 虚读：第一次读取的数据，第二次在读取的时候可能被修改了
  • 幻读：第一次读取的数据，第二次读取多了一条或者多了一条

3.作业练习

1.列出至少又三个员工的所有部门

  • select d.deptno,d.dname from emp e join dept d using(deptno) group by d.dname having count(e.ename)>=3;
mysql>  select d.deptno,d.dname from emp e join dept d using(deptno) group by d.dname having count(e.ename)>=3;
+--------+----------+
| deptno | dname    |
+--------+----------+
|     20 | RESEARCH |
|     30 | SALES    |
+--------+----------+
2 rows in set

1.1列出至少又三个员工的所有部门中的所有员工信息

  • select * from emp e right join (select e.deptno,count(e.empno) ce from emp e group by e.deptno) cep on(e.deptno= cep.deptno) where cep.ce>3;
mysql> select * from emp e right join (select e.deptno,count(e.empno) ce from emp e group by e.deptno) cep on(e.deptno= cep.deptno) where cep.ce>3;
 
+-------+--------+----------+------+------------+------+------+--------+--------+----+
| empno | ename  | job      | mgr  | hiredate   | sal  | comm | deptno | deptno | ce |
+-------+--------+----------+------+------------+------+------+--------+--------+----+
|  7369 | SMITH  | CLERK    | 7902 | 1980-12-17 | 800  | NULL |     20 |     20 |  5 |
|  7566 | JONES  | MANAGER  | 7839 | 1981-04-02 | 2975 | NULL |     20 |     20 |  5 |
|  7788 | SCOTT  | ANALYST  | 7566 | 1987-07-13 | 3000 | NULL |     20 |     20 |  5 |
|  7876 | ADAMS  | CLERK    | 7788 | 1987-07-13 | 1100 | NULL |     20 |     20 |  5 |
|  7902 | FORD   | ANALYST  | 7566 | 1981-12-03 | 3000 | NULL |     20 |     20 |  5 |
|  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 | 1600 | 300  |     30 |     30 |  6 |
|  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 | 1250 | 500  |     30 |     30 |  6 |
|  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 |     30 |     30 |  6 |
|  7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 | 2850 | NULL |     30 |     30 |  6 |
|  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0    |     30 |     30 |  6 |
|  7900 | JAM%ES | CLERK    | 7698 | 1981-12-03 | 950  | NULL |     30 |     30 |  6 |
+-------+--------+----------+------+------------+------+------+--------+--------+----+
11 rows in set

2.列出薪资比“SMITH”多的所有员工

  • select * from emp where sal > (select sal from emp where ename='SMITH');
mysql> select * from emp where sal > (select sal from emp where ename='SMITH');
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal  | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 | 300  |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 | 500  |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 | 0    |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100 | NULL |     20 |
|  7900 | JAM%ES | CLERK     | 7698 | 1981-12-03 | 950  | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL | NULL   |
+-------+--------+-----------+------+------------+------+------+--------+
13 rows in set

3.列出所有员工的姓名及其直接上级的姓名

  • 自连接select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno;
mysql> select e.empno,e.ename,m.empno,m.ename from emp e,emp m where e.mgr=m.empno;
+-------+--------+-------+-------+
| empno | ename  | empno | ename |
+-------+--------+-------+-------+
|  7369 | SMITH  |  7902 | FORD  |
|  7499 | ALLEN  |  7698 | BLAKE |
|  7521 | WARD   |  7698 | BLAKE |
|  7566 | JONES  |  7839 | KING  |
|  7654 | MARTIN |  7698 | BLAKE |
|  7698 | BLAKE  |  7839 | KING  |
|  7782 | CLARK  |  7839 | KING  |
|  7788 | SCOTT  |  7566 | JONES |
|  7844 | TURNER |  7698 | BLAKE |
|  7876 | ADAMS  |  7788 | SCOTT |
|  7900 | JAM%ES |  7698 | BLAKE |
|  7902 | FORD   |  7566 | JONES |
|  7934 | MILLER |  7782 | CLARK |
+-------+--------+-------+-------+
13 rows in set

4.列出受雇日期早于其直接上级的所有员工

  • select e.ename en,m.ename mn ,e.hiredate eh ,m.hiredate mh from emp e,emp m where e.mgr=m.empno and e.hiredate<m.hiredate;
mysql> select e.ename en,m.ename mn ,e.hiredate eh ,m.hiredate mh from emp e,emp m where e.mgr=m.empno and e.hiredate<m.hiredate;
+-------+-------+------------+------------+
| en    | mn    | eh         | mh         |
+-------+-------+------------+------------+
| SMITH | FORD  | 1980-12-17 | 1981-12-03 |
| ALLEN | BLAKE | 1981-02-20 | 1981-05-01 |
| WARD  | BLAKE | 1981-02-22 | 1981-05-01 |
| JONES | KING  | 1981-04-02 | 1981-11-17 |
| BLAKE | KING  | 1981-05-01 | 1981-11-17 |
| CLARK | KING  | 1981-06-09 | 1981-11-17 |
+-------+-------+------------+------------+
6 rows in set

5.列出部门名称和这些部门的 员工信息，包括哪些没有员工的部门

  • select d.dname ,e.ename from emp e right join dept d using(deptno);
mysql> select d.dname ,e.ename from emp e right join dept d using(deptno);
+------------+--------+
| dname      | ename  |
+------------+--------+
| ACCOUNTING | CLARK  |
| ACCOUNTING | KING   |
| RESEARCH   | SMITH  |
| RESEARCH   | JONES  |
| RESEARCH   | SCOTT  |
| RESEARCH   | ADAMS  |
| RESEARCH   | FORD   |
| SALES      | ALLEN  |
| SALES      | WARD   |
| SALES      | MARTIN |
| SALES      | BLAKE  |
| SALES      | TURNER |
| SALES      | JAM%ES |
| OPERATIONS | NULL   |
| 测试       | NULL   |
+------------+--------+
15 rows in set

6.列出所有job为“CLERK”（办事员）的姓名及其部门的名称

  • select d.dname,e.ename from emp e join dept d using(deptno) where e.job='CLERK';
mysql> select d.dname,e.ename from emp e join dept d using(deptno) where e.job='CLERK';
+----------+--------+
| dname    | ename  |
+----------+--------+
| RESEARCH | SMITH  |
| RESEARCH | ADAMS  |
| SALES    | JAM%ES |
+----------+--------+
3 rows in set

在这里插入图片描述在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

大白菜程序猿
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
4.2 图书借阅系统数据库设计 --MySQL
天罡gg的专栏
04-11 3万+
大家好，我是天罡gg，一个有十多年丰富经验的高级架构师，参与过很多系统的数据库设计，在数据库设计方面有相当丰富的经验。正赶上这篇实战专栏的数据库设计，所以今天让我们来一起做一下《图书借阅系统的数据库设计》，一篇既有理论知识，又能实战落地的数据库设计！本文从【需求分析】开始，到【概念结构设计】的画ER图, 再到【逻辑结构设计】的ER图转关系模型,最后到【物理结构设计】的MySQL表设计, 再加上【具体业务分析】的SQL实现，你是不是已经怀疑真有这么齐全吗？你没看错，全文HTML统计1万3千多字。
mysql刷新当前数据库_看看MySQL数据库高级操作
weixin_35755562的博客
02-18 3109
免费资源网 - https://freexyz.cn/文章目录数据表高级操作准备工作：安装MySQL数据库一、克隆表方法一方法二二、清空表，删除表内的所有数据方法一方法二三、创建临时表四、创建外键约束MySQL中6种常见的约束五、数据库用户管理1、新建用户2、查看用户信息3、重命名用户4、删除用户5、修改当前登录用户密码6、修改其他用户密码7、忘记 root 密码的解决办法六、数据库用户授权1、授...
MySQL数据库基本操作-DDL和DML
Starry__Sky222的博客
07-08 1740
DDL解释 对数据库的常用操作 对表结构的常用操作-创建表 对表结构的常用操作—其他操作 对表结构的常用操作-修改表结构格式 DML基本介绍 数据插入 数据修改 数据删除 MySQL的使用演示 MySQL的编码设置 MySQL图形化管理工具 MySQL目录结构与源码 主要目录结构 MySQL 源代码获取 常见问题的解决 重置密码 MySQL 字符集
MySQL数据库基本操作
Drw_Dcm的博客
09-09 5万+
MySQL数据库基本操作
数据库应用：MySQL数据库SQL高级语句与操作
cronaldo91的博客
07-11 1万+
克隆表将数据表的数据记录生成到新的表中.清空表删除表内的所有数据。group by查询结果进行汇总分组，having用来过滤由group by语句返回的记录集，通常与group by语句联合使用。
Linux系统——Mysql数据库操作
一坨小橙子的博客
03-20 3360
查看数据库/表和表结构、创建数据库/表和删除数据库/表、数据表的增删改查等操作、数据库表的清空表/临时表和克隆表等操作、数据库的用户授权相关操作（数据库用户管理、数据库授权）
MySQL数据库笔记--韩顺平老师课
qq_43688287的博客
01-21 4150
目录 MySQL基础 *Mysql5.7.19 解压版安装配置详细教程* 韩顺平老师： Mysql5.7.19 解压版安装配置详细教程 未经授权，禁止转载 - 转载请注明出处 l *软件下载* Mysql5.7地址：https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip l **特别说明* 如果安装过Mysql过程中，出错了或者想重新再来一把 sc delete mysql 【删除已经安装好的.
【Mysql】sql语法--创建数据库和表操作
weixin_63257947的博客
03-20 3281
这里我们创建了一个名字为my319的数据库。 注意，我们在命令行中输入sql有一些小技巧： 1.我们可以使用⬆/⬇切换到上一条或者下一条sql语句 2.如果有一条sql语句输入了一办不想执行了，按Ctrl+c来终止这条sql语句 就像这样：
数据库MySQL详解
热门推荐
卓越无关环境，保持空杯心态——靡不有初，鲜克有终
07-24 18万+
全网最详细MySQL教程，2023持续更新中
MySQL数据库高级操作（图文详解）
愿你成为自己喜欢的模样，不抱怨，不将就，有自由，有光芒！
01-26 2930
文章目录数据表高级操作准备工作：安装MySQL数据库一、克隆表方法一： 数据表高级操作 准备工作：安装MySQL数据库 一、克隆表 将数据表的数据记录生成到新的表中 方法一：
mysql基础操作-数据库备份文件
01-20
如果这个文件是MySQL数据库的一部分，你需要相应的工具或应用来读取和恢复数据。 对于MySQL的常规备份，有以下几种常见的方法： 1. **mysqldump**：这是MySQL官方提供的命令行工具，可以生成SQL脚本备份。例如，...
常用的Mysql数据库操作语句大全.doc
11-10
这些只是MySQL数据库操作的基础，实际上还有更多高级特性，如视图、触发器、存储过程、索引等，以及事务处理、并发控制等概念，都是数据库管理中的重要组成部分。熟悉并熟练运用这些语句，可以帮助你有效地管理和...
MySQL数据库高级工程师-面试题-MySQLDBA面试题03-风哥整理面试必过.rar
09-06
【MySQL数据库高级工程师面试知识点详解】 在MySQL数据库高级工程师的面试中，面试官通常会针对以下几个核心领域进行深度考察： 1. **SQL语言基础**：包括但不限于DML（数据操作语言）如INSERT、UPDATE、DELETE，...
MySQL操作数据库和表的常用命令新手教程
12-15
以上就是MySQL数据库和表的基本操作，包括查看、创建、使用、删除数据库，以及创建、复制、修改、删除和查询表。熟悉这些基本命令将有助于新手更好地理解和管理MySQL中的数据。在实际工作中，还会涉及更复杂的查询、...
MySQL数据库基础操作
09-13
MySQL数据库基础操作是数据库初学者入门的关键，它涵盖了数据库的基本概念、MySQL的启动与停止、客户端连接、SQL语法以及表的创建与管理等基础知识。在MySQL中，数据模型通常表现为DBMS（数据库管理系统）管理多个...
Apollo使用（3）：分布式docker部署
游王子的博客
07-24 725
Apollo 1.7.0版本开始会默认上传Docker镜像到，可以按照如下步骤获取。
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 368
图数据库查询与操作指南-以Nebula图数据库为例
二进制部署k8s集群之master节点和etcd数据库集群（上）
zx52306的博客
07-30 858
【代码】二进制部署k8s集群之master节点和etcd数据库集群（上）
【iOS】——持久化
最新发布
m0_73974920的博客
07-30 935
因为plist文件不能够保存自定义对象。如果一个字典中保存有自定义对象，如果把这个对象写入到文件当中，它是不会生成 plist文件的。如果对象是NSString、NSDictionary、NSArray、NSData、NSNumber等类型，可以直接用NSKeyedArchiver进行归档和恢复。
MySQL数据库详解：从基础到高级概念与操作
MySQL数据库概述 MySQL数据库是关系型数据库管理系统（Relational Database Management System, RDBMS）的一种，它...学习SQL语言，了解如何优化查询，以及如何设计和维护数据库模式，都是MySQL数据库操作的核心技能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

大白菜程序猿

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值