MySQL

 

MySQL-多表

1、SQL约束与策略

　　　　对表中的数据进行进一步的限制，从而保证数据的正确性、完整性。

　　　　约束：

　　　　　　　　主键约束(primary key)、非空约束(not null)、唯一约束(unique)、外键约束(foreign key)、默认值(default)。

　　1.1、主键约束

　　　　1.1.1、添加主键约束

　　　　　　primary key

　　　　　　主键特点：唯一且不为空。

　　　　　　作用：唯一的标识每一条记录。

　　　　　　一个表只能有一个主键。

　　

　　　　　　方式一：

　　　　　　方式二：

　　　　　　方式三（了解）：

 

　　　　　　联合主键（了解）：联合主键也是一个主键，只不过将多个列联合在一起了。如：

　　

　　　　　　1.1.2、删除主键约束（了解）

　　　　alter table 表名 drop primary key.

　　　　1.2、非空约束(not null)

　　　　　　不能为空值

　　　　1.3、唯一约束（unique）

　　　　　　　特点;唯一但是值可以为空，可以多个值为空

　　　　　　

　　　　　　　　主键约束和唯一约束的区别：

　　　　　　　　1、主键约束唯一且不为空，唯一约束运行空值。

　　　　　　　　2、一个表中最多只有一个主键，但是可以有多个唯一约束。

　　　　　1.4、默认值（unique）

　 　　　　 　　　　　

　　　　　1.5、外键约束（foreign key）

　　　　　　poreign key表示外键约束，用于多表查询

　　　　　　1.6、自动增长-策略（auto-increment）

　　　　我们通常希望在每次插入新纪录时，数据库自动生成字段的值。

　　　　我们可以使用auto-increment（自动增长列）关键字，自动增长列类型必须是整数，自动增长列必须为键（可以是唯一约束，可以是主键约束）

 

Delete和truncate的区别（自动增长策略）：

　　delete删除数据后在插入数据会在原有自动增长的数据上去增加，

　　使用truncate删除后就相当于一张新表，自动增长从0开始。

2、多表操作

　　2.1、多表简述

　　实际开发中，一个项目通常需要很多张表才能完成。

　　例如：一个商城项目就需要分类表（category）、商品表（products）、订单表（orders）等多长表。且表的数据之间存在一定的关系。

　　　　好处：为了数据的使用和维护更为方便，我们将单表数据划分为多表。

　　　　　　

　

　　　2.2外键

　　　　外键作用;进行多表数据关联。

　　　　多表关系中，是存在主表、从表的：

　　　　　　多的一方为从表，少的一方为主表。

　　　　1、主表：数据来源表、主键ID所在表

　　　　　　　例如：分类表---是分类名称数据的来源表，主键cid所在表

　　　　2、从表：数据引用表、外键所在表

　　　　　　　例如：商品表--引用了分类表数据，外键所在表

　　　　外键特点：

　　　　1、从表的外键指向主表的主键

　　　　2、从表的外键的数据类型和长度，必须和主表 的主键的类型和长度一致。

　　　　2.3、外键约束

create database day02_2;
use day02_2;
#创建分类表：
CREATE TABLE category(
    cid varchar(32) PRIMARY KEY,#主键ID
    cname VARCHAR(20)#分类名称
);
INSERT INTO category VALUES('c001','电脑办公');
INSERT INTO category VALUES('c002','服装');
#创建商品表：
CREATE TABLE product(
    pid INT PRIMARY KEY,#主键ID
    pname VARCHAR(20),#商品名称
    price DOUBLE,#商品价格
    category_cid VARCHAR(32)#外键
);

#导入数据
INSERT INTO product(pid,pname,price,category_cid) VALUES(1,'联想电脑',5000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(2,'海尔电脑',3000,'c001');
INSERT INTO product(pid,pname,price,category_cid) VALUES(3,'雷神电脑',5000,'c001');

INSERT INTO product(pid,pname,price,category_cid) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_cid) VALUES(7,'劲霸',2000,'c002'); 

 

从表只能引入主表中存在的数据，要不就先添加主表中的数据再去引入

主表只能删除从表不存在的数据，要不就先删从表数据在删主表数据

 声明外键约束：（是为从表添加）

格式：

　　alter tabe 从表 add foreign key (从表外键字段名) references 主表（主表的主键）；

 删除外键约束：（了解，开发慎用）

　　alter table 从表 drop foreign key 外键名称

2.4、多表关系

 　　多表关系简述

　　2.4.1、一对多（最常用）

　　　　班级和学生、分类和商品、省份和城市

　　　　2.4.3、多对多（常用）

　　　　老师和学生、学生和课程、演员和角色

　　2.4.4、一对一（比较少）

　　　　在开发中应用不多，因为一对一可以合成一张表。

　　　　建表方式：

　　　　　　外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一unique

3、多表关系实战

3.1、实战1：省和市

方案1：多张表，一对多

代码准备：（请在准备代码中填入外键）

CREATE TABLE province(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    description VARCHAR(20)
);
CREATE TABLE city(
    id INT PRIMARY KEY,
    NAME VARCHAR(20),
    description VARCHAR(20)
);

ALTER TABLE city ADD FOREIGN KEY(pid) REFERENCES province(id)

3.2、实战2：演员和角色

　　多对多关系

代码准备：（请在准备代码中填入中间表及外键）

create table actor(
    aid int primary key,
    name varchar(30)
);
create table role(
    rid int primary key,
    name varchar(30)
);

4、多表查询

4.1、什么是多表查询

同时查询多长表获取到需要的数据

 

4.2多表查询的分类

4.3、笛卡尔积现象

多张表进行一一结合。

4.3.1、什么是笛卡尔积现象

需求：查询每个部门下有 哪些人

左表中的数据一一和右表中的数据结合了

 

4.3.2如何清除笛卡尔积

4.4内连接

用左边的记录去匹配右边的记录，如果符合条件的则显示

显示内连接：select * from A inner join B on 条件

隐式内连接： select * from A,B where 条件

多表查询的规律：

　　1、确定查询的数据涉及到那些表

　　2、查询的条件是什么

　　3、要查询哪些字段

4.5 外连接

左外连接：（以左表为基准，去匹配右表中的记录，如果匹配的到，显示对应的信息，如果匹配不到，左边的信息显示右边表显示为null）。

select * from A left join B on 条件

右外连接：（以右表为基准，和左外链接原理相同）

select * from A right join B on条件

4.6 子查询

概述：将上一条SELECT语句结果作为另一条SELECT语法一部分

子查询的三种情况：

1、子查询的结果是一个值的时候

 

需求：1、查询工资最高的员工是谁？

　　　2、查询工资小于平均工资的员工有哪些

 

2、子查询结果是单列多行的时候

需求：1、查询工资大于5000的员工，来自于那些部门的名字

　　　2、查询开发部与财务部所有员工的信息

3、子查询的结果是多行多列

需求：1、查询出2011年以后入职的员工信息，包括部门名称

结论：

　　子查询结果只要是单列，肯定在where后面作为条件

　　　　select 查询字段 from 表 where字段=（子查询）；

　　子查询结果只要是多列，肯定在from后面作为表

　　　　select 查询字段 from （子查询）表别名 where 条件