哪里有做桥梁模型的网站,深圳高速建设有限公司,wordpress主题 说说,手机网站建设文章目录多表查询创建练习用的数据库链接/连接查询交叉连接自然连接内连接(取交集)外连接左外连接/右外连接自连接子查询联合查询总结数据库的备份和恢复命令行操作多表查询
-- 获得 alice的 部门所在城市
select * from staff where namealice; -- 获得dept_id1
select city …
文章目录多表查询创建练习用的数据库链接/连接查询交叉连接自然连接内连接(取交集)外连接左外连接/右外连接自连接子查询联合查询总结数据库的备份和恢复命令行操作多表查询
-- 获得 alice的 部门所在城市
select * from staff where namealice; -- 获得dept_id1
select city from dept where id 1;mysql select * from staff where namealice;-- 获得dept_id1
-------------------------------------------------------------------------
| 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*416
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 看做内外循环 第一次循环找到id1的数据 第二次循环看那个条件满足 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 english60;-- 获得小明的英语成绩
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 english60;
--------------------------------------------
| 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 cant 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;
