大数据WEB阶段（六）MySql详解（二）_web大数据查询-优快云博客

本文链接：https://blog.youkuaiyun.com/chou_out_man/article/details/78618238

本文深入讲解了MySql的分组查询、数据库备份与恢复、外键约束等高级特性，并通过实例演示了多表设计和多表查询的具体操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySql详解（二）

一、分组查询

语法：

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

练习：

执行下面的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;

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

二、数据库备份与恢复

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

三、外键约束

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

案例：

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 - 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不支持

笛卡尔积查询
1. 两张表相乘得出来的结果。如果左边表有m条记录，右边有n条记录，则查询出来的结果就是m*n条。这些查询结果中包含大量错误的结果，通常不会使用这种查询。
内连接查询：查询出左边表(dept)有且右边表(emp)也有的记录。
```
select * from emp inner join dept on dept.id=dept_id;
```
左外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录
1. **查询出部门所对应的员工信息, 同时列出那些没有员工的部门
```
select * from dept left join emp on dept.id=dept_id;
```
右外连接查询：在内连接查询的基础上，加上右边表有而左边表没有的记录。
1. **查询出部门所对应的员工信息, 同时列出那些没有部门的员工
```
select * from dept right join emp on dept.id=dept_id;
```
全外连接查询：在内连接查询的基础上，加上左边表有而右边表没有的记录和右边表有而左边表没有的记录。
1. **查询出部门所对应的员工信息,同时列出那些没有员工的部门及些没有部门的员工
```
select * from dept full join emp on dept.id=dept_id;###mysql不支持全外连接查询
```

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

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