大数据WEB阶段（六）MySql详解（二）

MySql详解（二）

一、分组查询

1. 语法：

   select col_name1,col_name2... from tb_name group by having ...;
   

2. 练习：

   执行下面的SQL，创建orders表并插入数据
           create table orders(
               id int,
               product varchar(20),
               price float
           );
   
           insert into orders(id,product,price) values(1,'小米手机',900);
           insert into orders(id,product,price) values(2,'奥妙洗衣液',60);
           insert into orders(id,product,price) values(3,'乐视TV',90);
           insert into orders(id,product,price) values(4,'联想键盘',80);
           insert into orders(id,product,price) values(5,'奥妙洗衣液',60);
           insert into orders(id,product,price) values(6,'小米手机',900);
   
       ~对订单表中商品归类后，显示每一类商品的总价
           select * from orders group by product;
           select count(*) from orders group by product;
           select sum(price) from orders group by product;
   
       ~查询购买了几类商品，并且每类总价大于100的商品
           select product,sum(price) 总价 from orders group by product having        sum(price)>100;
   
       ~查询单价小于100而总价大于100的商品的名称.
   select * from orders where price<100 group by product having sum(price) > 100;
   

3. where和having的区别
   
   1. where和having子句都可以进行过滤 ， 但是使用场景有所不同 。
   2. where对分组之前的数据进行过滤，不能使用聚合函数和别名
   3. having子句对分组之后的数据进行过滤 ， 可以使用聚合函数和别名 。
   4. 使用where子句的地方一般都可以用having代替 ， 但是使用having的地方一般不能使用where代替。

二、数据库备份与恢复

1. 备份数据库
   
   1. 注意在新开的cmd窗口中 ， 不要登录mysql
   2. 备份命令： mysqldump -u用户名 -p >备份数据存放位置
2. 恢复数据
   
   1. 方式一： 在cmd中（不登录mysql）
      
      1. 恢复命令： mysql -u用户名 -p <数据存放位置
   2. 方式二： 在mysql客户端中
      
      1. 恢复命令： source 数据存放位置

三、外键约束

1. 外键：唯一标识其他表中的一条记录，用来通知数据库两张表字段之间的对应关系, 并让数据库帮我们维护这样关系的键就叫做外键
2. 外键的作用： 确保数据库数据的完整性和一致性
3. 添加外键：foreign key(外键) reference 其他表（关联的键）

4. 案例：

   create table dept(
               id int primary key auto_increment,
               name varchar(20)
           );
   
           insert into dept values(null, '财务部');
           insert into dept values(null, '人事部');
           insert into dept values(null, '科技部');
           insert into dept values(null, '销售部');
   
           create table emp(
               id int primary key auto_increment,
               name varchar(20),
               dept_id int,
               foreign key(dept_id) references dept(id)
           );
   
           insert into emp values(null, '张三', 1);
           insert into emp values(null, '李四', 2);
           insert into emp values(null, '老王', 3);
           insert into emp values(null, '赵四', 4);
           insert into emp values(null, '刘能', 4);
   
   要求：
   （1）执行上面的SQL语句，创建两张表（dept和emp），在创建时先不指定dept_id为外键，尝试删除部门表中的某一个部门。
   （2）将dept和emp表删除重建，在创建时指定dept_id为外键，再次尝试删除部门表中的某一个部门。
   

四、多表设计

1. 1 - *（一对多）：在多的一方添加列保存一的一方的主键来作为外键, 来保存两张表之间的关系
2. 1 - 1（一对一）：在任意一方添加列保存另一方的主键作为外键, 来保存两张表之间的关系
3. • • *（多对多）：在一张第三方的表中分别保存两张表的主键作为外键, 来保存两张表之间的关系，可以把多对多的关系拆分成两个一对多的关系来理解

五、多表查询

1. 案例：

           create table dept(
               id int primary key auto_increment,
               name varchar(20)
           );
   
           insert into dept values(null, '财务部');
           insert into dept values(null, '人事部');
           insert into dept values(null, '科技部');
           insert into dept values(null, '销售部');
   
           create table emp(
               id int primary key auto_increment,
               name varchar(20),
               dept_id int
           );
   
           insert into emp values(null, '张三', 1);
           insert into emp values(null, '李四', 2);
           insert into emp values(null, '老王', 3);
           insert into emp values(null, '刘能', 5);
   
   需求1：查询出部门表和员工表，同时列出部门信息和员工信息。
       select * from dept,emp;（笛卡尔积查询）
   
       select * from dept, emp where emp.dept_id=dept.id;
       或者
       select * from dept inner join emp on emp.dept_id=dept.id;
   需求2：查询出部门信息和部门所对应的员工信息，同时列出那些没有员工的部门
           select * from dept left join emp on emp.dept_id=dept.id;
   需求3：查询出部门信息和部门所对应的员工信息，同时列出那些没有部门的员工
           select * from dept right join emp on emp.dept_id=dept.id;
   需求4：查询出部门信息和部门所对应的员工信息, 同时列出没有员工的部门和那些没有部门的员工
           select * from dept full join emp on emp.dept_id=dept.id;###mysql不支持
   

2. 笛卡尔积查询

   1. 两张表相乘得出来的结果。如果左边表有m条记录，右边有n条记录，则查询出来的结果就是m*n条。这些查询结果中包含大量错误的结果，通常不会使用这种查询。

3. 内连接查询：查询出左边表(dept)有且右边表(emp)也有的记录。

   select * from emp inner join dept on dept.id=dept_id;
   

4. 左外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录

   1. **查询出部门所对应的员工信息, 同时列出那些没有员工的部门

      select * from dept left join emp on dept.id=dept_id;
      

5. 右外连接查询：在内连接查询的基础上，加上右边表有而左边表没有的记录。

   1. **查询出部门所对应的员工信息, 同时列出那些没有部门的员工

      select * from dept right join emp on dept.id=dept_id;
      

6. 全外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录 和 右边表有而左边表没有的记录。

   1. **查询出部门所对应的员工信息,同时列出那些没有员工的部门及些没有部门的员工

      select * from dept full join emp on dept.id=dept_id;###mysql不支持全外连接查询
      

7. 使用union模拟全外连接查询：

   select * from dept left join emp on dept.id=dept_id
   union
   select * from dept right join emp on dept.id=dept_id;