MySQL初 二

数据库的备份和恢复

备份
在mysql的安装目录的bin目录下有mysqldump命令，可以完成对数据库的备份。
--mysqldump -u 用户名 -p 数据库名 > 磁盘sql文件路径
--mysqldump命令不是sql命令，需要在dos窗口下使用。
---mysqldump -u root -p mysql2 >D:\mysql2.sql

注意：
在备份数据的时候，数据库不会被删除。可以手动删除数据库。
在恢复数据的时候，不会自动的给我们创建数据库，仅仅只会恢复数据库中的表和表中的数据。

恢复
先删除之前备份的数据库，再创建一个数据库，名字不限。
这里也是dos窗口
--mysql -u 用户名 -p 导入库名 < 硬盘SQL文件绝对路径 
---mysql -u root -p mysql22 < D:\mysql2.sql


使用navicat可视化工具备份和恢复
备份
选中要备份的数据库--->右击选中 转储SQL文件（结构和数据） -->选择存储位置

恢复（删除之前的）
新建一个数据库（仅写名字，名字不限）-->右击新建的数据库  运行SQL文件 -->选择之前备份的

多对多关系

说明：如果两张表是多对多的关系，需要创建第三张表，并在第三张表中增加两列，引入其他两张表的主键作为自己的外键。
中间表需要引入外键约束。
外键约束：
--foreign key(当前表中的列名) rederences 被引用表名（被引用表的列名）
---foreign key(coder_id) references coder(id);

给表添加外键约束的两种方式;
第一种：给已经存在表添加外键约束
alter table coder_project add foreign key(coder_id) references coder(id);
alter table coder_project add foreign key(project_id) references project(id);

第二种：建表时就添加约束
create table coder_project(
		coder_id int;
		project_id int,
		foreign key(coder_id) references coder(id),
		foreign key(project_id) references project(id)
);
 
 怎么测试外键约束是否起到作用？
 增加一条关联表中没有的数据；
 ---insert into coder_project values(30,20);
 	报错
 删除被关联表中的主键；
 ---delete from coder where id=1;
    报错
 说明主键约束起作用了

添加外键需要注意的问题：
1.如果从表要添加一个外键约束，要求主表被引用的字段是主键或者是唯一的。通常是主键。
2.如果要删除主表中的数据。要求在从表中，该数据没有被引用，才可以去删除。
3.如果要向从表中添加数据。要求主表中，要有对应的数据。
4.如果删除表。要先删除从表，然后删除主表。
5.新建表的时候。需要先去创建主表，然后去创建从表。

删除外键约束(待验证)
--alter table 表名 drop foreign key 外键名称;

一对多的关系

比如作者和书；
如果是一对多的关系，那么设计表的时候需要在多的一方增加一列，引入一的一方的主键作为自己的外键。

一对一的关系

比如老公和老婆的关系；
一对一的关系，在任意一方增加一列，引入另一方是主键作为外键。

多表连接查询

前置条件;a表和b表

笛卡尔积查询：select * from a,b;
--查出来的结果条数是（a表行数* b表行数）
--a表的每条记录和b表每条记录 组合
--缺点：有很多错误数据，必须过滤出正确的

内连接
作用：查询两边的公共部分
方式一：select * from a,b where a..关联字段 = b.关联字段;
方式二：select * from a inner join b on a.关联字段 =b.关联字段;

左连接
作用：左表全部显示，依据左表的显示右边没查到的显示为null;
select * from a left join b on a.关联字段 = b.关联字段。  a是左表

右连接
作用:右表全部显示，依据右表的显示左表没有查到的显示为null;
select * from a right join b on a.关联字段 = b.关联字段。   b是右表

多表查询

子查询
--select max(price) from b;
select id from b where price=3.5;
select name from a where id= 2;

--select name from a where id in
(select id from b where price=
(select max(price) from b))

in的用法
in 表示条件应该是在多个列值中。
in：使用在where后面，经常表示是一个列表中的数据，只要被查询的数据在这个列表中存在即可。
-- 需求：查询不及格的学生(使用in完成)。
--select * from student where id in
 (select student_id from studentcourse where score<60);

all的用法
注意：all使用 只能用于子查询 直接给出数字是不可以的(语法错误)
需求：查询年龄最大的学生的信息。
--select * from student where age=(
select max(age) from student
);

--select * from student where age >= all(
select age from student
);

any和some
需求：查询成绩是90的学生的信息
--select * from student where id in (
select student_id from studentcourse where score=90
);
--select * from student where id=any(
select student_id from studentcourse where score=90
);
--select * from student where id=some(
select student_id from studentcourse where score=90
);

as的用法
as:不仅可以用来做列的别名，还可以将查询结果通过as作为一张表来使用。
as  做一个别名 ,我们可以用过  别名.属性  表名.属性  将你的查询结果作为中间表查询
需求：查询不及格的学生信息和不及格分数
--select student.*,temp.score from student,
(select student_id,score from studentcourse where score<60)
as temp where student.id=temp.student_id;

limit的用法
作用:限制查询结果返回数量；即查询数据库中的一部分数据
mysql中limit的用法：返回前几条或者中间某几行数据
-select * from 表名 limit offset,row_count;
--select * from 表名 limit 1,4;
--1表示索引，这里索引从0开始。
--4表示查询记录数。
上述就表示从第2条记录开始查询，一共查询4条，即到第5条。


强化练习
-- 1、查询每个平均成绩大于70分的同学的学号和平均成绩。
select student_id,avg(score) from studentcourse group by student_id having avg(score)>70;

-- 2、查询每个同学的学号、姓名、选课数、总成绩。
思路
 select student_id,sum(score) from studentcourse group by student_id having sum(score)
 select student_id,count(course_id) from studentcourse group by student_id having count(course_id)
 select student_id,course_id from studentcourse group by student_id,course_id

答案
select student.*,temp.cou,temp.sumscore from student,
(select student_id,count(*)as cou,sum(score)as sumscore from studentcourse group by student_id)
as temp where student.id=temp.student_id
注意：student_id,sum(score) ,count(course_id)这三个字段放在一起报错，重点count(*)


-- 3、查询学过赵云老师所教课的同学的学号、姓名。
select student.id,student.name from student,
(select student_id from studentcourse where course_id in (
select id from course where teacher_id =(
select id from teacher where name='赵云'
)))as temp where temp.student_id=student.id