学习mysql数据库的第三天

Day03

多表查询

一.快速复制表

快速复制表结构(含约束): create table 新表名 like 就表名;

查询结果快速插入到表中: insert into 表名 select语句;

快速复制表结构和数据(丢失约束): create table 新表名 [as] select语句;

1.1快速复制表的两种方式

a）方式1：快速复制表结构
create table product like day02.product;
-- 快速插入数据到表中
insert into product select * from day02.product;

b）方式2：快速复制表结构和数据
create table product2 as select * from day02.product;

二.分组查询

关键字: group by

基础格式: select 分组字段名,聚合函数(字段名) from 表名 group by 分组字段名;

进阶格式: select 分组字段名,聚合函数(字段名) from 表名 [where 非聚合条件] 

                 group by 分组字段名 [having 聚合条件];

注意: select后的字段名要么在group by后面出现过,要么写到聚合函数中,否则报错

拓展

where和having的区别? 
    书写顺序: where在group by 前,having在group by后
    执行顺序: where在分组条件前,having在分组条件后
    分组函数: where后不能跟聚合条件,只能跟非聚合条件,having后可以使用聚合条件,也可以使用非聚合条件(不建议)
    应用场景: 建议大多数过滤数据都采用where,只有当遇到聚合条件的时候再使用having
    使用别名: where后不能使用别名,having后可以使用别名

三.分页查询

关键字: limit

基础格式: select 字段名 from 表名 limit x,y;
                 x: 起始索引,默认从0开始     x = (页数-1)*y
                 y: 本次查询的条数
        
注意: limit能完成topN需求,但是不能考虑到并列情况,此问题可以使用后期学习的开窗函数解决

四.多表查询外键

外键概念: 在从表(多方)创建一个字段，引用主表(一方)的主键,对应的这个字段就是外键。

外键特点：
    1:从表外键的值是对主表主键的引用。
    2:从表外键类型，必须与主表主键类型一致。

4.1外键约束（准备工作）

在开始外键约束的学习前需要更改小皮的存储引擎

注意：先把mysql服务关闭了再修改！！！！！！！！！

4.2关于储存引擎

myisam: 不支持事务,不支持外键                                                            

innodb: 支持事务,支持外键

用代码解释

#分类表
CREATE TABLE category
(
    cid   VARCHAR(32) PRIMARY KEY,
    cname VARCHAR(100) #分类名称
);

# 商品表
CREATE TABLE products
(
    pid         varchar(32) PRIMARY KEY,
    pname       VARCHAR(40),
    price       DOUBLE,
    category_id varchar(32)
);

# 添加外键约束
alter table products add FOREIGN KEY (category_id) REFERENCES category(cid);

a）注意: 上述看似加了外键约束,但是底层是myisam存储引擎,默认不支持外键约束,所以不会起作用

b） 注意: 如果修改了存储引擎,需要重新建表才能生效,否则还是原来的存储引擎

4.3外键约束（正式）

外键约束关键字: foreign key

外键约束作用: 
        限制从表插入数据: 如果从表插入的外键值,在主表中不存在,就插入失败
        限制主表删除数据: 如果主表的主键值已经被从表引用,在主表删除该数据的时候,就删除失败

4.4交叉连接（慎用）

交叉连接关键字: cross join

显式交叉连接格式: select * from 左表 cross join 右表;

隐式交叉连接格式: select * from 左表,右表;

注意: 交叉连接了解即可,因为它本质就是一个错误,又叫笛卡尔积(两个表记录数的乘积)

注意: 左表和右表没有特殊含义,对于左表和右表的定义是由自己决定

4.5内连接（常用）

内连接关键字: inner join ... on

显式内连接格式: select * from 左表 inner join 右表 on 关联条件;

隐式内连接格式:  select * from 左表 , 右表 where 关联条件;

4.6左外连接

内连接关键字: left outer join ... on

左外连接格式: select * from 左表 left outer join 右表 on 关联条件;

4.7右外连接

内连接关键字: right outer join ... on

左外连接格式: select * from 左表 right outer join 右表 on 关联条件;

4.8子查询

核心思路: 把一条完整的sql语句结果作为另外一条sql语句的一部分(表或者条件)

--思路1: 内连接查询 
        select p.*
        from category c join products p ON c.cid = p.category_id
        where cname = '化妆品';

-- 思路2: 子查询
        select *
        from products
        where category_id = (select cid from day03.category where cname = '化妆品');

4.9自连接

a）核心思想: 就是特殊的内外连接,特殊之处在于左表和右表是同一个表
b） 最大特点: 左右表是同一个表,那就必须要给他们起别名,才能区分
c） 应用场景: 自连接应用场景非常局限,一般都是省市县区域表或者上下级员工表