文章目录
- 多表查询
- 创建练习用的数据库
- 链接/连接查询
- 交叉连接
- 自然连接
- 内连接(取交集)
- 外连接
- 左外连接/右外连接
- 自连接
- 子查询
- 联合查询
- 总结
- 数据库的备份和恢复
- 命令行操作
多表查询
-- 获得 alice的 部门所在城市
select * from staff where name='alice'; -- 获得dept_id=1
select city from dept where id = 1;mysql> select * from staff where name='alice';-- 获得dept_id=1
+----+-------+--------+------------+---------+-------+------+----------+----------+
| id | name | gender | birth | dept_id | level | mgr | salary | bonus |
+----+-------+--------+------------+---------+-------+------+----------+----------+
| 1 | alice | female | 1999-04-26 | 1 | L4 | NULL | 20000.00 | 50000.00 |
+----+-------+--------+------------+---------+-------+------+----------+----------+
1 row in set (0.00 sec)mysql> select city from dept where id = 1;
+--------+
| city |
+--------+
| 北京 |
+--------+
1 row in set (0.00 sec)创建练习用的数据库
-- 创建用于练习的数据库-- 班级
create table class(id int(11) NOT NULL primary key AUTO_INCREMENT,class_name varchar(10)
);-- 成绩
create table score(id int(11) NOT NULL primary key AUTO_INCREMENT,student_id int(11),class_id int(11),chinese float,english float
);
-- 学生信息
create table student(id int(11) NOT NULL primary key AUTO_INCREMENT,stduent_name varchar(20),mobile varchar(20)
);
-- 剧本
create table script(id int(11) NOT NULL primary key auto_increment,play_name varchar(20),play_position varchar(20)
);
-- 演出表
create table `show`(id int NOT NULL primary key auto_increment,student_id int,script_id int
);-- 班级插入数据
insert into class values(NULL, 1);
insert into class values(NULL, 2);-- 成绩表插入数据
insert into score values(NULL, 1, 1, 80, 60);
insert into score values(NULL, 2, 1, 70, 50);
insert into score values(NULL, 3, 2, 60, 90);
insert into score values(NULL, 4, 2, 80, 50);-- 学生信息插入数据
insert into student values(NULL, '小明', '111');
insert into student values(NULL, '小红', '222');
insert into student values(NULL, '小黑', '333');
insert into student values(NULL, '小百', '444');-- 剧本表插入数据
insert into script values(NULL, '体育', '操场');
insert into script values(NULL, '语文', '教室');-- 演出表插入数据
insert into `show` values(NULL, 1, 1);
insert into `show` values(NULL, 1, 2);
insert into `show` values(NULL, 2, 1);
insert into `show` values(NULL, 4, 1);
链接/连接查询
交叉连接
mysql> select * from student;
+----+--------------+--------+
| id | stduent_name | mobile |
+----+--------------+--------+
| 1 | 小明 | 111 |
| 2 | 小红 | 222 |
| 3 | 小黑 | 333 |
| 4 | 小百 | 444 |
+----+--------------+--------+
4 rows in set (0.00 sec)mysql> select * from score;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
| 1 | 1 | 1 | 80 | 60 |
| 2 | 2 | 1 | 70 | 50 |
| 3 | 3 | 2 | 60 | 90 |
| 4 | 4 | 2 | 80 | 50 |
+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)-- 交叉连接 会生成两个表数据相乘那么多条数据 4*4=16
mysql> select * from student cross join score;
+----+--------------+--------+----+------------+----------+---------+---------+
| id | stduent_name | mobile | id | student_id | class_id | chinese | english |
+----+--------------+--------+----+------------+----------+---------+---------+
| 4 | 小百 | 444 | 1 | 1 | 1 | 80 | 60 |
| 3 | 小黑 | 333 | 1 | 1 | 1 | 80 | 60 |
| 2 | 小红 | 222 | 1 | 1 | 1 | 80 | 60 |
| 1 | 小明 | 111 | 1 | 1 | 1 | 80 | 60 |
| 4 | 小百 | 444 | 2 | 2 | 1 | 70 | 50 |
| 3 | 小黑 | 333 | 2 | 2 | 1 | 70 | 50 |
| 2 | 小红 | 222 | 2 | 2 | 1 | 70 | 50 |
| 1 | 小明 | 111 | 2 | 2 | 1 | 70 | 50 |
| 4 | 小百 | 444 | 3 | 3 | 2 | 60 | 90 |
| 3 | 小黑 | 333 | 3 | 3 | 2 | 60 | 90 |
| 2 | 小红 | 222 | 3 | 3 | 2 | 60 | 90 |
| 1 | 小明 | 111 | 3 | 3 | 2 | 60 | 90 |
| 4 | 小百 | 444 | 4 | 4 | 2 | 80 | 50 |
| 3 | 小黑 | 333 | 4 | 4 | 2 | 80 | 50 |
| 2 | 小红 | 222 | 4 | 4 | 2 | 80 | 50 |
| 1 | 小明 | 111 | 4 | 4 | 2 | 80 | 50 |
+----+--------------+--------+----+------------+----------+---------+---------+
16 rows in set (0.00 sec)-- 加条件进行过滤 留下匹配的数据
mysql> select -> *-> from student -> cross join score -> where student.id = score.student_id;
+----+--------------+--------+----+------------+----------+---------+---------+
| id | stduent_name | mobile | id | student_id | class_id | chinese | english |
+----+--------------+--------+----+------------+----------+---------+---------+
| 1 | 小明 | 111 | 1 | 1 | 1 | 80 | 60 |
| 2 | 小红 | 222 | 2 | 2 | 1 | 70 | 50 |
| 3 | 小黑 | 333 | 3 | 3 | 2 | 60 | 90 |
| 4 | 小百 | 444 | 4 | 4 | 2 | 80 | 50 |
+----+--------------+--------+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)
语法上是会先产生笛卡尔集，然后再进行过滤。（如果数据较多会性能很差）
但是 服务器中会有优化器 会进行优化。
自然连接
按照两个表中具有相同名字的列，进行匹配的。
mysql> select * from class;
+----+------------+
| id | class_name |
+----+------------+
| 1 | 1 |
| 2 | 2 |
+----+------------+
2 rows in set (0.00 sec)mysql> select * from script;
+----+-----------+---------------+
| id | play_name | play_position |
+----+-----------+---------------+
| 1 | 体育 | 操场 |
| 2 | 语文 | 教室 |
+----+-----------+---------------+
2 rows in set (0.00 sec)-- 自然连接 会将两个表中具有相同的列的数据进行连接 都匹配一遍和交叉连接有点像
mysql> select * from class natrual join script;
+----+------------+----+-----------+---------------+
| id | class_name | id | play_name | play_position |
+----+------------+----+-----------+---------------+
| 2 | 2 | 1 | 体育 | 操场 |
| 1 | 1 | 1 | 体育 | 操场 |
| 2 | 2 | 2 | 语文 | 教室 |
| 1 | 1 | 2 | 语文 | 教室 |
+----+------------+----+-----------+---------------+
4 rows in set (0.00 sec)内连接(取交集)
内连接优化器会划分主表和副表。底层就是两个for循环 for(主表 for(副表) ) 但是在查询副表的时候有时候会使用3-4次io操作进行查找，又是会一个一个遍历，后面会详细讲。
内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接（隐式内连接），也是在开发过程中使用的最多的连接查询。
表1 inner join 表2 [on 表1和表2的关联条件] [代表可选]
mysql> -- 获取每个人的人名，以及这个人的语文成绩
mysql> select -> student.student_name, score.chinese-> from student -> inner join score-> on student.id = score.student_id;
+--------------+---------+
| student_name | chinese |
+--------------+---------+
| 小明 | 80 |
| 小红 | 70 |
| 小黑 | 60 |
| 小百 | 80 |
+--------------+---------+
4 rows in set (0.00 sec)-- 内连接也可以多表连接
-- 查询每个人的班级信息 姓名 语文 英语成绩
selectcl.class_name, st.student_name, sc.english, sc.chinese
from student st
inner join score sc on st.id = sc.student_id
inner join class cl on sc.class_id = cl.id;mysql> select-> cl.class_name, st.student_name, sc.english, sc.chinese-> from student st-> inner join score sc on st.id = sc.student_id-> inner join class cl on sc.class_id = cl.id;
+------------+--------------+---------+---------+
| class_name | student_name | english | chinese |
+------------+--------------+---------+---------+
| 1 | 小明 | 60 | 80 |
| 1 | 小红 | 50 | 70 |
| 2 | 小黑 | 90 | 60 |
| 2 | 小百 | 50 | 80 |
+------------+--------------+---------+---------+
4 rows in set (0.00 sec)外连接
外连接与内连接的区别
（1）在外连接中可以指明主副表（主表都会进行遍历，所以可以将数据多的表设置为副表，这样效率会提升，内连接不能指明主副表但是也不用担心，因为优化器会进行优化）
（2）外连接要求主表的数据无论和副表匹配与否，都要存在在结果集中。
主副表的判断
-- left outer join 左外连接 所以 左面的student就是主表
select st.student_name, sc.chinese, sc.english
from student st
left outer join score sc on st.id = sc.student_id; -- right outer join 右外连接 所以 右面的score就是主表
select st.student_name, sc.chinese, sc.english
from student st
right outer join score sc on st.id = sc.student_id;
左外连接/右外连接
必须加上on子句，否则会报错。
左外连接以左表为主表，当右表中没有记录的时候，会全部用NULL替代。
右外连接以右表为主表，当右表中没有记录的时候，会全部用NULL代替。
-- 查询一个人的人名 语文 英语成绩
-- 左外连接
select st.student_name, sc.chinese, sc.english
from student st
left outer join score sc on st.id = sc.student_id; mysql> -- 查询一个人的人名 语文 英语成绩
mysql> -- 左外连接
mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> left outer join score sc on st.id = sc.student_id;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明 | 80 | 60 |
| 小红 | 70 | 50 |
| 小黑 | 60 | 90 |
| 小百 | 80 | 50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)-- 右外连接
select st.student_name, sc.chinese, sc.english
from student st
right outer join score sc on st.id = sc.student_id;mysql> -- 右外连接
mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> right outer join score sc on st.id = sc.student_id;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明 | 80 | 60 |
| 小红 | 70 | 50 |
| 小黑 | 60 | 90 |
| 小百 | 80 | 50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)-- 从上面结果来看好像左外连接 与 右外连接好像没有什么区别-- 我在学生表中插入一条数据
insert into student values(NULL, '小绿', 555);
-- 再次左外连接 右外连接 查看现象
-- 会验证上面主表的数据无论与副表匹配与否都会出现在结果集中mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> left outer join score sc on st.id = sc.student_id;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明 | 80 | 60 |
| 小红 | 70 | 50 |
| 小黑 | 60 | 90 |
| 小百 | 80 | 50 |
| 小绿 | NULL | NULL |
+--------------+---------+---------+
5 rows in set (0.00 sec)mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> right outer join score sc on st.id = sc.student_id;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明 | 80 | 60 |
| 小红 | 70 | 50 |
| 小黑 | 60 | 90 |
| 小百 | 80 | 50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)-- 可以将左外连接结果集再使用条件进行过滤 或者 使用内连接匹配的才会显示
select st.student_name, sc.chinese, sc.english
from student st
left outer join score sc on st.id = sc.student_id
where sc.english is not null;mysql> select -> st.student_name, sc.chinese, sc.english-> from student st-> left outer join score sc on st.id = sc.student_id-> where sc.english is not null;
+--------------+---------+---------+
| student_name | chinese | english |
+--------------+---------+---------+
| 小明 | 80 | 60 |
| 小红 | 70 | 50 |
| 小黑 | 60 | 90 |
| 小百 | 80 | 50 |
+--------------+---------+---------+
4 rows in set (0.00 sec)日常写代码 我们都会将outer 关键字 进行省略 对内容没有影响 这是一种省略的写法。
自连接
-- 知道谁的英语成绩 小于 id为1的同学的成绩-- 可以怎么理解呢？
-- 就是可以把s1 s2 看做内外循环 第一次循环找到id=1的数据 第二次循环看那个条件满足 and后面的条件，然后保存。selects2.*
from score s1, score s2
where s1.student_id = 1 and s2.english < s1.english;mysql> select-> s2.*-> from score s1, score s2-> where s1.student_id = 1 and s2.english < s1.english;
+----+------------+----------+---- -----+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
| 2 | 2 | 1 | 70 | 50 |
| 4 | 4 | 2 | 80 | 50 |
+----+------------+----------+---------+---------+
2 rows in set (0.00 sec)子查询
又称为嵌套查询
子查询在删除/修改/查询SQL操作中，用到了另一个查询结果
-- 查询英语成绩小于 小明英语成绩 的同学的信息select * from student where student_name = '小明';
select * from score where student_id = 小明的id;
select * from score where english<60;-- 获得小明的英语成绩
select english
from score
where student_id = (select id from student where student_name = '小明'
);-- 一步一步查询
mysql> select * from student where student_name = '小明';
+----+--------------+--------+
| id | student_name | mobile |
+----+--------------+--------+
| 1 | 小明 | 111 |
+----+--------------+--------+
1 row in set (0.00 sec)mysql> select english from score where student_id = 1;
+---------+
| english |
+---------+
| 60 |
+---------+
1 row in set (0.00 sec)mysql> select * from score where english<60;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
| 2 | 2 | 1 | 70 | 50 |
| 4 | 4 | 2 | 80 | 50 |
+----+------------+----------+---------+---------+
2 rows in set (0.00 sec)-- 子查询
mysql> select -> english-> from score-> where student_id = (-> select id from student where student_name = '小明'-> );
+---------+
| english |
+---------+
| 60 |
+---------+
1 row in set (0.00 sec)-- 子查询 用在修改操作-- 将小于小明的英语成绩的同学的英语成绩都改为95
update score set english = 95
where english < (select * from (select englishfrom scorewhere student_id = (select id from student where student_name='小明')));
-- 会报错 因为服务器认为从一个表中查找数据然后对该表进行修改很危险
mysql> update score set english = 95-> where english < (-> select -> english-> from score-> where student_id = (-> select id from student where student_name='小明'-> )-> );
-- ERROR 1093 (HY000): You can't specify target table 'score' for update in FROM clause-- 欺骗服务器
-- 将子查询的结果起一个别名update score set english = 95
where english < (select * from (select englishfrom scorewhere student_id = (select id from student where student_name='小明')) as temp
);mysql> select * from score;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
| 1 | 1 | 1 | 80 | 60 |
| 2 | 2 | 1 | 70 | 50 |
| 3 | 3 | 2 | 60 | 90 |
| 4 | 4 | 2 | 80 | 50 |
+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)mysql> update score set english = 95-> where english < (-> select * from -> (-> select -> english-> from score-> where student_id = (-> select id from student where student_name='小明'-> )-> ) as temp-> );
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0mysql> select * from score;
+----+------------+----------+---------+---------+
| id | student_id | class_id | chinese | english |
+----+------------+----------+---------+---------+
| 1 | 1 | 1 | 80 | 60 |
| 2 | 2 | 1 | 70 | 95 |
| 3 | 3 | 2 | 60 | 90 |
| 4 | 4 | 2 | 80 | 95 |
+----+------------+----------+---------+---------+
4 rows in set (0.00 sec)联合查询
把两个sql语句查询的结果 合并到一起返回
将左查询和右查询进行并集操作，会去除重复的行。
select * from score where chinese >= 70;
select * from score where english >= 90;-- 联合查询
select * from score where chinese >= 70
union
select * from score where english >= 90;总结
数据库的备份和恢复
也可以理解为复制一个数据库。
比如：数据库的要备份的时候，有人也在使用数据库，所以要加锁，不用我们来进行加锁，服务器会自己进行加锁处理。
命令行操作
-- 数据库备份：cmd命令行下
mysqldump -u root -p 数据库名称>文件名.sql-- 数据库恢复
-- 1.创建数据库并选择该数据库
create database dbname;-- 恢复数据
-- 1.不用连接mysql服务器，直接使用命令行操作
mysql -u root -p 数据库<文件名.sql
-- 2.连接数据库服务器
use dbname;
source 文件名.sql;
)
