MySQL数据库学习II（基础）

备份、恢复数据库

备份数据库表中的数据

    •cmd> mysqldump -u 用户名 -p 数据库名> 文件名.sql

恢复数据库（前提创建空的数据库，并且use）

    •source 文件名.sql   // 在mysql内部使用

    •mysql –u 用户名 p 数据库名 <文件名.sql  // 在cmd下使用

MySQL数据库的备份和恢复

1、备份命令mysql/bin/mysqldump 将数据库SQL语句导出

    语法：mysqldump-u 用户名 -p 数据库名 > 磁盘SQL文件路径

            例如：备份day12数据库 --- c:\day12.sql

                    cmd> mysqldump -u root -p day12 > c:\day12.sql 回车输入密码

                    INSERTINTO `exam` VALUES(1,'关羽',85,76,70),(2,'张飞',70,75,70),(3,'赵云',90,65,95),(4,'刘备',NULL,55,38);

2、恢复命令mysql/bin/mysql 将sql文件导入到数据库

    语法：mysql -u 用户名 -p 数据库名 < 磁盘SQL文件路径

*****导入SQL 必须手动创建数据库 ，SQL不会创建数据库

例如：将c:\day12.sql导入 day12数据库

    cmd> mysql -u root -p day12 < c:\day12.sql 回车密码

补充知识：恢复SQL也可以在数据库内部执行source c:\day12.sql

多表操作

• 外键约束
• 有一个部门的表，还有一个员工表，

    create database day16;
    use day16;
    create table dept(
    did int primary key auto_increment,
    dname varchar(30)
    ); 
    create table emp(
    eid int primary key auto_increment,
    ename varchar(20),
    salaly double,
    dno int
    ); 
    insert into dept values(null,'研发部');
    insert into dept values(null,'销售部');
    insert into dept values(null,'人事部');
    insert into dept values(null,'扯淡部');
    insert into dept values(null,'牛宝宝部'); 
    insert into emp values(null,'班长',10000,1);
    insert into emp values(null,'美美',10000,2);
    insert into emp values(null,'小凤',10000,3);
    insert into emp values(null,'如花',10000,2);
    insert into emp values(null,'芙蓉',10000,1);
    insert into emp values(null,'东东',800,null);
    insert into emp values(null,'波波',1000,null); 
    update emp set salaly=2500 where eid = 5; 

• 把研发部删除？

• 研发部下有人员？该操作不合理。

• 引入外键约束？

  • 作用：保证数据的完整性。

定义外键约束

1，可以直接在create语句中定义外键

    •foreign key 当前表名 (字段名) references 目标表名 (目标表的主键)

2，创建完语句后，可以直接使用修改语句定义

    •alter table 表名 add foreign key 当前表名 (字段名) references 目标表名 (目标表的主键);

• 添加外键
  语法：alter table emp add foreign key 当前表名(dno) references 关联的表(did);
  alter table emp add foreign key emp(dno) references dept(did);
• 数据库的设计
• 一对多 生活中一个部门下有多个员工，一个员工属于一个部门。
  在多方需要添加一个字段，并且和一放主键的类型必须是相同的。
  把该字段作为外键指向一方的主键。
  一方部门
  多方员工
• 多对多
  学生可以选择多门课程，课程又可以被多名学生选择。
  建表原则：
• 拆开两个一对多的关系，中间创建一个中间表，至少有两个字段。作为外键指向两个多对多关系表的主键。
• 一对一（了解）
• 公司，地址，一个公司对应的是一个地址。 一张表包含公司名称、公司地址。
• 根据公司的业务需求，会把公司这张表拆开，形成一对一。
• 建表原则

建表原则：

•第一种是主键对应

    •将两个表的主键进行关联

•第二种是唯一外键对应

    •在任何一方加一个外键，但是需要设置成唯一（unique），指向另一方的主键

多表的查询

引入笛卡尔积的概念：
     笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X × Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

多表查询时，如果不指定表之间的连接条件，则连接变成笛卡尔乘积操作，笛卡尔积的效果如本页所示。

进行笛卡尔积后，查询结果中存在大量无意义的数据，示例中只有A表中的A_ID和B表中A_ID一致的行才有意义，这样我们通过加上WHERE过滤条件.

得到想要的数据。这种横跨多表的查询操作一般用连接完成。

表A            表B
aid aname bid bname
a1 aa1 b1 bb1
a2 aa2 b2 bb2
            b3 bb3

• 查询的语法
  select * from 表A,表B; 返回的结果就是笛卡尔积。
  结果：
  a1 aa1 b1 bb1
  a1 aa1 b2 bb2
  a1 aa1 b3 bb3
  a2 aa2 b1 bb1
  a2 aa2 b2 bb2
  a2 aa2 b3 bb3
  select * from dept,emp;
  多表查询
  内连接（用的比较多）

• 普通内连接
  前提条件：需要有外键的。
  提交关键字 inner join … on
  select * from dept inner join emp on dept.did = emp.dno;
  隐式内连接（用的是最多的）
  可以不使用inner join … on关键字
  select * from dept,emp where dept.did = emp.dno;
  外连接

• 左外链接（看左表，把左表所有的数据全部查询出来）
  前提条件：需要有外键的。
  语法： 使用关键字 left [outer] join … on
  select * from dept left outer join emp on dept.did = emp.dno;

• 右外链接（看右表，把右表所有的数据全部查询出来）
  前提条件：需要有外键的。
  语法： 使用关键字 right [outer] join … on

          select * from dept right join emp on dept.did = emp.dno
  

子查询

查询的内容需要另一个查询的结果。

select * from emp where 条件 > (select * from emp where 条件);
 
select * from emp where ename > (select * from emp where 条件);

any 任意
all 全部

any 大于结果的最小值

all 大于结果的最大值

create table dept(
    did int primary key auto_increment,
    dname varchar(30)
);
create table emp(
    eid int primary key auto_increment,
    ename varchar(20),
    salaly double,
    dno int
);

查看所有人所属的部门名称和员工名称?

select dept.dname,emp.ename from dept,emp where dept.did = emp.dno;
select d.dname,e.ename from dept d,emp e where d.did = e.dno;

统计每个部门的人数(按照部门名称统计，分组group by count)

select d.dname,count(*) from dept d,emp e where d.did = e.dno group by d.dname; ​​​​​​​

统计每个部门的人数(按照部门名称统计)

select d.did,d.dname,count(*) from dept d,emp e where d.did= e.dno group by d.did order by d.did asc;

统计部门的平均工资（按部门名称统计 ，分组group by avg）

select d.dname,avg(salaly) from dept d,emp e where d.did = e.dno group by d.dname;

统计部门的平均工资大于公司平均工资的部门（子查询）

• 公司的平均工资

select avg(salaly) from emp;

• 部门的平均工资

select d.dname,avg(e.salaly) as sa from dept d,emp e where d.did = e.dno group by d.dname having sa > (select avg(salaly) from emp);

+-----+-------+--------+------+
| eid | ename | salaly | dno  |
+-----+-------+--------+------+
|   1 | 班长  |  10000 |    1 |
|   2 | 美美  |  10000 |    2 |
|   3 | 小凤  |  10000 |    3 |
|   4 | 如花  |  10000 |    2 |
|   5 | 芙蓉  |  10000 |    1 |
+-----+-------+--------+------+
 
+-----+--------+
| did | dname  |
+-----+--------+
|   1 | 研发部 |
|   2 | 销售部 |
|   3 | 人事部 |
+-----+--------+
==============================================================================================
+-----+-------+--------+------+
| eid | ename | salaly | dno  |
+-----+-------+--------+------+
|   1 | 班长  |  10000 |    1 |
|   2 | 美美  |  10000 |    2 |
|   3 | 小凤  |  10000 |    3 |
|   4 | 如花  |  10000 |    2 |
|   5 | 芙蓉  |  10000 |    1 |
|   6 | 东东  |    800 | NULL |
|   7 | 波波  |   1000 | NULL |
+-----+-------+--------+------+
 
+-----+----------+
| did | dname    |
+-----+----------+
|   1 | 研发部   |
|   2 | 销售部   |
|   3 | 人事部   |
|   4 | 扯淡部   |
|   5 | 牛宝宝部 |
+-----+----------+