二、数据库的使用Mysql
2018-10-16 本文已影响0人
WenErone
一、上堂回顾
1.概念
数据库管理系统，数据库，表
SQL的分类：DDL、DML、DQL、DCL
2.数据库的使用
DDL:create【创建数据库和创建表】 alter【操作表】 drop【删除表】
DML:insert【给表中插入数据】 update【需要结合where使用】 delete、truncate【表中的数据】
DQL：select
where
where like--------->_ %
as ifnull distinct
order by:asc desc
聚合函数：count sum max min avg
二、数据库操作
3.DQL
3.7分组查询
group by：分组查询 将字段中相同值归为一组
having：有...,表示条件，类似于where的用法
演示：
创建emp员工表: 员工号 员工姓名 工作 上级编号 受雇日期 薪水 佣金 部门编号 +-------+--------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+------+------+--------+ | 7369 | smith | clark | 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 | | 7566 | jones | managen | 7839 | 1981-04-02 | 2975 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7782 | clark | manageer | 7839 | 1980-06-17 | 2450 | NULL | 10 | | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000 | NULL | 20 | | 7839 | king | president | NULL | 1987-02-20 | 5000 | NULL | 10 | +-------+--------+-----------+------+------------+------+------+--------+ #1.查询各个部门的人数 mysql> select count(*) from emp group by deptno; +----------+ | count(*) | +----------+ | 2 | | 2 | | 5 | +----------+ 3 rows in set (0.00 sec) #2.查询每个部门的部门编号和每个部门的工资和 mysql> select deptno,sum(sal) from emp group by deptno; +--------+----------+ | deptno | sum(sal) | +--------+----------+ | 10 | 7450.00 | | 20 | 3800.00 | | 30 | 8675.00 | +--------+----------+ 3 rows in set (0.00 sec) #3.查询每个部门的部门编号和每个部门的人数 mysql> select deptno,count(*) from emp group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 2 | | 20 | 2 | | 30 | 4 | +--------+----------+ 3 rows in set (0.00 sec) #4.查询每个部门的部门编号和每个部门工资大于1500的人数 mysql> select deptno,count(*) from emp where sal>1500 group by deptno; +--------+----------+ | deptno | count(*) | +--------+----------+ | 10 | 2 | | 20 | 1 | | 30 | 3 | +--------+----------+ 3 rows in set (0.01 sec) #5.查询工资总和大于7000的部门编号以及工资和 #执行顺序 from em->group by deptno-->having sum(sal)>7000-->select mysql> select deptno,sum(sal) from emp group by deptno having sum(sal)>7000; +--------+----------+ | deptno | sum(sal) | +--------+----------+ | 10 | 7450.00 | | 30 | 8675.00 | +--------+----------+ 2 rows in set (0.00 sec)总结：
having和where的区别
a.二者都表示对数据执行条件
b.having是在分组之后对数据进行过滤
where是在分组之前对数据进行过滤
c.having后面可以使用聚合函数
where后面不可以使用聚合函数
演示：
#查询工资大于1500，工资总和大于6000的部门编号和工资和 mysql> select deptno,sum(sal) from emp where sal>1500 group by deptno having sum(sal)>6000; +--------+----------+ | deptno | sum(sal) | +--------+----------+ | 10 | 7450.00 | | 30 | 7425.00 | +--------+----------+ 2 rows in set (0.00 sec)
3.8分页查询
limit：用来限定查询的起始行，以及总行数
演示：
#LIMIT [offset,] rows #offset指定要返回的第一行的偏移量，rows第二个指定返回行的最大数目。初始行的偏移量是0(不是1)。 #1.查询4行记录，起始行从0开始 mysql> select * from emp limit 0,4; +-------+--------+----------+------+------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+---------+---------+--------+ | 7369 | smith | clark | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7566 | jones | managen | 7839 | 1981-04-02 | 2975.00 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 4 rows in set (0.00 sec) mysql> select * from emp limit 2,3; +-------+--------+----------+------+------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+---------+---------+--------+ | 7566 | jones | managen | 7839 | 1981-04-02 | 2975.00 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 3 rows in set (0.01 sec)总结：
查询语句书写顺序：select----》from---》where---》group by-----》having-----》order by----->limit
查询语句的执行顺序：from----》where-----》group by----》having----》select-----》order by----》limit
三、数据的完整性
作用：保证用户输入的数据保存到数据库中是正确的
实质：创建表的时候给表中的字段添加约束
1.实体完整性
实体：表中的一行或者一条记录代表一个实体
实体完整性的作用：标识每一行数据不重复
约束类型：
主键约束【primary key】
唯一约束【unique】
自动增长列【auto_increment】
1.1主键约束【primary key】
特点：数据唯一，且不能为null
主关键字可以是表中的一个字段或者多个字段，它的值用来唯一标识表中的某一条记录
场景：在多个表的关联关系中
演示：
mysql> create table stu1( -> id int primary key, -> name varchar(50) -> ); Query OK, 0 rows affected (0.06 sec) mysql> create table stu2( -> id int, -> name varchar(50), -> primary key(id) -> ); Query OK, 0 rows affected (0.03 sec) mysql> create table stu3( -> id int, -> name varchar(50) -> ); Query OK, 0 rows affected (0.03 sec) mysql> alter table stu3 add constraint stu3_id primary key(id); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0
1.2唯一约束
作用：在非主键列中不能输入重复的值
演示：
mysql> create table stu4( -> id int primary key, -> name varchar(50) unique -> ); Query OK, 0 rows affected (0.04 sec) #primary key和unique之间的区别 a.二者都强调的是唯一性 b.在同一个表中，只能出现一个primary key，可以出现多个unique c.primary key不允许为null，但是unique是允许的
1.3自动增长列
给主键添加添加自动增长性，列只能是整数类型
场景：一般添加给主键
演示：
mysql> create table stu5( -> id int primary key auto_increment, -> name varchar(50) unique -> ); Query OK, 0 rows affected (0.04 sec)
2.域完整性
作用：限制单元格数据的正确性，
域代表当前单元格
约束类型：
数据类型
非空约束【not null】
默认值约束【default】
2.1数据类型
数字类型：int float doule decimal
日期类型：date
字符串类型：char varchar
2.2非空约束【not null】
演示：
mysql> create table stu6( id int primary key auto_increment, name varchar(50) unique not null); Query OK, 0 rows affected (0.03 sec) #注意：name被约束为not null，插入数据的时候，name坚决不能为null，如果未null，数据库立马报错
2.3默认值约束
演示：
mysql> create table stu7( -> id int primary key auto_increment, -> name varchar(50) unique not null, -> address varchar(50) default "beijing" -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into stu7 (id,name,address) values(1,'aaa','fff'); Query OK, 1 row affected (0.02 sec) mysql> insert into stu7 (id,name,address) values(2,'bbb',default); Query OK, 1 row affected (0.01 sec) mysql> select * from stu7; +----+------+---------+ | id | name | address | +----+------+---------+ | 1 | aaa | fff | | 2 | bbb | beijing | +----+------+---------+ 2 rows in set (0.00 sec) 练习: 创建一个学生表. id int 主键 自动增长. name varchar(20) 不能重复 不能为空 address varchar(20) 默认为广东 按照上面的约束,插入5条数据.
3.引用完整性
添加外键约束：foreign key
注意：添加外键必须先有主键，主键和外键的类型必须保持一致
举例：学生表，成绩表
作用：将两个甚至多个毫无关联的表产生联系
演示：
#创建表 #学生表 create table student( stuid varchar(10) primary key, stuname varchar(50) ); Query OK, 0 rows affected (0.01 sec) #成绩表 create table score( stuid varchar(10), score int, courseid int ); Query OK, 0 rows affected (0.00 sec) #插入数据 insert into student values('1001','zhangsan'); insert into student values('1002','xiaoming'); insert into student values('1003','jack'); insert into student values('1004','tom'); insert into score values('1001',98,1); insert into score values('1002',95,1); insert into score values('1003',67,2); insert into score values('1004',83,2); insert into score values('1004',70,1); #查询 mysql> select * from student; +-------+----------+ | stuid | stuname | +-------+----------+ | 1001 | zhangsan | | 1002 | lisi | | 1003 | jack | | 1004 | tom | +-------+----------+ 4 rows in set (0.00 sec) mysql> select * from score; +-------+-------+----------+ | stuid | score | courseid | +-------+-------+----------+ | 1001 | 98 | 1 | | 1002 | 80 | 2 | | 1003 | 70 | 1 | | 1004 | 60 | 2 | | 1002 | 75 | 3 | +-------+-------+----------+ 5 rows in set (0.00 sec) #方式一 mysql> create table score1( score int, courseid int,stuid varchar(10), constraint stu_sco_id foreign key(stuid) references student(stuid) ); Query OK, 0 rows affected (0.05 sec) #注意：stu_sco_id是给约束起的名字，可以自定义 #方式二 mysql> create table score2( -> score int, -> courseid int, -> stuid varchar(10) -> ); Query OK, 0 rows affected (0.04 sec) mysql> alter table score2 add constraint stu_sco_id foreign key(stuid) references student(stuid); #注意：主键(主表)和外键(从表)的类型必须保持一致 #1.从表中外键的字段必须来源于主表. #验证: score1中插入的记录,stuid字段必须来自student表 #2.如果1001这个stuid在score1中使用了,那么不能再主表student中将其删除. #验证: 对主表student执行删除操作时，如果删除的主键值在子表score1中出现，那么就删除失败
四、多表查询
1.表与表之间的关系
一对一
通过嵌套的方式
一对多【多对一】
添加外键
多对多
单独创建一张新的表
2.合并结果集
作用：将两个select语句的查询结果合并到一起
两种方式：
union：去除重复记录【并集】
union all；获取所有的结果
演示：
#创建表 mysql> create table A( -> name varchar(10), -> score int -> ); Query OK, 0 rows affected (0.02 sec) mysql> create table B( name varchar(10), score int ); Query OK, 0 rows affected (0.02 sec) #批量插入数据 mysql> insert into A values('a',10),('b',20),('c',30); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into B values('a',10),('d',40),('c',30); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 #查询结果 mysql> select * from A; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | +------+-------+ 3 rows in set (0.00 sec) mysql> select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | d | 40 | | c | 30 | +------+-------+ 3 rows in set (0.00 sec) #合并结果集 mysql> select * from A -> union -> select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | d | 40 | +------+-------+ 4 rows in set (0.00 sec) mysql> select * from A -> union all -> select * from B; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | a | 10 | | d | 40 | | c | 30 | +------+-------+ 6 rows in set (0.00 sec)注意：被合并的两个结果，列数、列类型必须相同
如果遇到列数不相同的情况，如下的解决办法：
mysql> insert into C values('a',10,29),('e',20,45),('c',30,10); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from A -> union -> select name,score from C; +------+-------+ | name | score | +------+-------+ | a | 10 | | b | 20 | | c | 30 | | e | 20 | +------+-------+ 4 rows in set (0.00 sec)
3.连接查询
作用：求出多个表的乘积，例如t1和t2，如果采用了连接查询，得到的结果是t1*t2
演示：
mysql> select * from student,score; +-------+----------+-------+-------+----------+ | stuid | stuname | stuid | score | courseid | +-------+----------+-------+-------+----------+ | 1001 | zhangsan | 1001 | 98 | 1 | | 1002 | lisi | 1001 | 98 | 1 | | 1003 | jack | 1001 | 98 | 1 | | 1004 | tom | 1001 | 98 | 1 | | 1001 | zhangsan | 1002 | 80 | 2 | | 1002 | lisi | 1002 | 80 | 2 | | 1003 | jack | 1002 | 80 | 2 | | 1004 | tom | 1002 | 80 | 2 | | 1001 | zhangsan | 1003 | 70 | 1 | | 1002 | lisi | 1003 | 70 | 1 | | 1003 | jack | 1003 | 70 | 1 | | 1004 | tom | 1003 | 70 | 1 | | 1001 | zhangsan | 1004 | 60 | 2 | | 1002 | lisi | 1004 | 60 | 2 | | 1003 | jack | 1004 | 60 | 2 | | 1004 | tom | 1004 | 60 | 2 | | 1001 | zhangsan | 1002 | 75 | 3 | | 1002 | lisi | 1002 | 75 | 3 | | 1003 | jack | 1002 | 75 | 3 | | 1004 | tom | 1002 | 75 | 3 | +-------+----------+-------+-------+----------+ 20 rows in set (0.01 sec) #问题：进行连接查询，会产生笛卡尔积 #笛卡尔积：两个集合相乘的结果 #解释：假设集合A={a,b},集合B={0,1,2},则笛卡尔积的结果{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)} #解决办法：在实际应用中，需要去除重复记录，则需要通过条件进行过滤 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 5 rows in set (0.00 sec)
3.1内连接-inner join on
内连接的特点：查询结果必须满足条件
演示：
#内连接 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 5 rows in set (0.00 sec) #等价写法 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 5 rows in set (0.00 sec) #练习：查询成绩大于70的学生记录 #方式一 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s,score c where s.stuid=c.stuid and c.score>70; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 3 rows in set (0.00 sec) #方式二 #也是内连接，只不过相当于是方言，join on相当于是普通话 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid where score>70; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 3 rows in set (0.00 sec)
3.2外连接-outer join on
特点：以其中一个表作为参照连接另外一个表
分类：
左外连接：left join on
右外连接：right join on
全外连接：full join【MySQL不支持】
演示：
#左外连接 #以左侧连接的条件为准,右侧如果没有对应的值,就会填充null mysql> select s.stuid,s.stuname,c.score,c.courseid from student s left join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 5 rows in set (0.01 sec) #内连接 mysql> select s.stuid,s.stuname,c.score,c.courseid from student s join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 5 rows in set (0.00 sec) #右外连接 #以右侧连接的条件为准,左侧如果没有对应的值,就会填充null #参照为c mysql> select s.stuid,s.stuname,c.score,c.courseid from student s right join score c on s.stuid=c.stuid; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1002 | lisi | 75 | 3 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | +-------+----------+-------+----------+ 5 rows in set (0.01 sec)
3.3自然连接-natural join
自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。
演示：
mysql> select * from student natural join score; +-------+----------+-------+----------+ | stuid | stuname | score | courseid | +-------+----------+-------+----------+ | 1001 | zhangsan | 98 | 1 | | 1002 | lisi | 80 | 2 | | 1003 | jack | 70 | 1 | | 1004 | tom | 60 | 2 | | 1002 | lisi | 75 | 3 | +-------+----------+-------+----------+ 5 rows in set (0.00 sec) mysql> select student.stuid,student.stuname,score.score from student natural join score; +-------+----------+-------+ | stuid | stuname | score | +-------+----------+-------+ | 1001 | zhangsan | 98 | | 1002 | lisi | 80 | | 1003 | jack | 70 | | 1004 | tom | 60 | | 1002 | lisi | 75 | +-------+----------+-------+ 5 rows in set (0.00 sec)总结：
连接查询会产生一些无用笛卡尔积，通常需要使用外键之间的关系去除重复记录，而自然连接无需给给出主外键之间的关系，会自动找到这个等式
4.子查询
在一个select语句中包含另外一个完整的select语句【select语句的嵌套】
注意：
a.子查询出现的位置：
from后
where子句的后面，作为条件的一部分被查询
b。当子查询出现在where后面作为条件时，可以使用关键字：any、all
c.子查询结果集的形式
单行单列
单行多列
多行多列
多行单列
演示：
#1.查询和scott在同一个部门的员工 #思路：先查询scott所在的部门，然后根据部门查找所有的信息 mysql> select deptno from emp where enname='scott'; +--------+ | deptno | +--------+ | 20 | +--------+ 1 row in set (0.00 sec) mysql> select * from emp where deptno=(select deptno from emp where enname='scott'); +-------+--------+---------+------+------------+---------+------+--------+ | empno | enname | job | mgr | hiredate | sal | comm | deptno | +-------+--------+---------+------+------------+---------+------+--------+ | 7369 | smith | clark | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 | +-------+--------+---------+------+------------+---------+------+--------+ 2 rows in set (0.00 sec) #2.查询工资高于joens的员工信息 #思路：先查询jones的工资，然后根据jones查询其他的员工信息 mysql> select * from emp where sal>(select sal from emp where enname='jones'); +-------+--------+-----------+------+------------+---------+------+--------+ | empno | enname | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+---------+------+--------+ | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 | | 7839 | king | president | NULL | 1987-02-20 | 5000.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+------+--------+ 2 rows in set (0.00 sec) #3.查询工资高于30号部门所有人的员工信息 #思路：先查询30号部门中的最高工资，根据最高工资查询其他的员工信息 mysql> select * from emp where deptno=30; +-------+--------+----------+------+------------+---------+---------+--------+ | empno | enname | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+---------+---------+--------+ | 7499 | allen | salesman | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7566 | jones | managen | 7839 | 1981-04-02 | 2975.00 | NULL | 30 | | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | blake | manager | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 4 rows in set (0.00 sec) mysql> select max(sal) from emp where deptno=30; +----------+ | max(sal) | +----------+ | 2975.00 | +----------+ 1 row in set (0.01 sec) mysql> select * from emp where sal>(select max(sal) from emp where deptno=30); +-------+--------+-----------+------+------------+---------+------+--------+ | empno | enname | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+---------+------+--------+ | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 | | 7839 | king | president | NULL | 1987-02-20 | 5000.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+------+--------+ 2 rows in set (0.00 sec) #4.查询工作类型和工资与martin完全相同的员工信息 #思路：先查询martin的工作类型和工资，然后再查询其他的员工信息 mysql> select * from emp where (job,sal) in(select job,sal from emp where enname='martin'); +-------+--------+----------+------+------------+---------+---------+--------+ | empno | enname | job | mgr | hiredate | sal | comm | deptno | +-------+--------+----------+------+------------+---------+---------+--------+ | 7654 | martin | salesman | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +-------+--------+----------+------+------------+---------+---------+--------+ 1 row in set (0.00 sec) #5.查询有2个以上下属的员工信息 mysql> select * from emp where empno in (select mgr from emp group by mgr having count(*)>2); +-------+-------+-----------+------+------------+------+------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+-------+-----------+------+------------+------+------+--------+ | 7698 | blake | manager | 7839 | 1981-05-01 | 2850 | NULL | 30 | | 7839 | king | president | NULL | 1987-02-20 | 5000 | NULL | 10 | +-------+-------+-----------+------+------------+------+------+--------+ 2 rows in set (0.00 sec) #6.求各个部门中薪水最高的员工信息 mysql> select * from emp where sal in(select max(sal) from emp group by deptno); +-------+--------+-----------+------+------------+---------+------+--------+ | empno | enname | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+---------+------+--------+ | 7566 | jones | managen | 7839 | 1981-04-02 | 2975.00 | NULL | 30 | | 7788 | scott | analyst | 7566 | 1987-02-20 | 3000.00 | NULL | 20 | | 7839 | king | president | NULL | 1987-02-20 | 5000.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+------+--------+ 3 rows in set (0.00 sec)
5.自连接
自己连接自己
演示：
#1.查询7654的员工姓名，经理编号和经理姓名 select m.empno,m.ename,n.ename from emp m join emp n on m.empno = n.mgr where n.empno = 7654; +-------+-------+--------+ | empno | ename | ename | +-------+-------+--------+ | 7698 | blake | martin | +-------+-------+--------+ 1 row in set (0.00 sec)
五、数据库的备份和恢复
1.备份
生成SQL脚本，导出数据
命令：mysqldump -u root -p 数据库名>生成sql脚本的路径
注意：可以不需要登录数据库
演示：
rock@rockrong:~$ mysqldump -u root -p mydb1>/home/rock/Desktop/mydb1.sql Enter password:
2.恢复
执行sql脚本，恢复数据
前提：必须先创建数据库【空的】
注意：需要先登录数据库，然后进入指定的数据库，执行sql脚本
演示：
rock@rockrong:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> show tables; ERROR 1046 (3D000): No database selected mysql> use test; Database changed mysql> show tables; Empty set (0.00 sec) mysql> source /home/rock/Desktop/mydb1.sql; Query OK, 0 rows affected (0.00 sec)
