MySQL|约束、多表查询
- 1.约束
- 1.1 主键约束
- 1.1.1 添加主键约束的三种方法
- 1.1.2 删除主键约束
- 1.2 唯一约束
- 1.3 非空约束
- 1.4 自增约束
- 2.清空表格
- 3.多表查询
- 3.1一对多表格创建、修改与删除
- 3.2 多对多表格创建
- 3.3 多表查询
- 3.3.1 内连接
- 3.3.2 外连接
- 3.3.3 子查询
- 3.3.4 自连接查询
1.约束
数据库中约束的作用是为了保证数据的有效性和完整性。
mySQL中常用的约束有：
约束 | 补充说明 |
主键约束(primary key) | |
自增长约束 auto_incrment | 加在整数型的字段配和主键约束来使用 |
唯一约束(unique) | |
非空约束(not null) | |
外键约束(foreign key) | |
枚举(ENUM) | 把一个字段的数据类型设置为枚举类型 也可以起到一种约束的效果 |
非负约束 UNSIGNED | TINYINT 表示的范围 :-128~127 TINYINT UNSIGNED 表示的范围: 0~255 |
1.1 主键约束
定义：被修饰过的字段唯一非空
注意:一张表只能有一个主键,这个主键可以包含多个字段
1.1.1 添加主键约束的三种方法
方法1：建表的同时添加约束
格式: 字段名称 字段类型 primary key
方法2：建表的同时在约束区域添加约束
约束区域：所有的字段声明完成之后的区域。
格式: primary key(字段1,字段2)
create table pk01(
id int,
username varchar(20),
primary key (id)
);
insert into pk01 values(1,'tom');
-- 成功
insert into pk01 values(1,'tom');
-- 失败 Duplicate entry '1' for key 'PRIMARY'
insert into pk01 values(null,'tom');
-- 失败 Column 'id' cannot be null方法3：建表之后,通过修改表结构添加约束
格式：alter table 表名 add primary key(字段名1,字段名2…);
create table pk02(
id int,
username varchar(20)
);
alter table pk02 add primary key(id,username);1.1.2 删除主键约束
删除主键约束分两种情况：
情况1: 该字段只有主键约束,分两步
- alter table 表名 drop primary key; – 这样只删除了唯一,他还有个非空约束,所以得再删除非空约束
- alter table 表名 modify [列名] varchar(20) null; --修改字段名还为原来的字段 加上null即可
情况2: 该字段是一个主键自增长约束，分三步
- 删除自增长约束,其实就是修改自增长字段名和数据类型还为原来的字段名和类型
alter table 表名 change 字段名 字段名 数据类型; --删除自增长约束 - 删除主键约束
alter table 表名 drop primary key; - 删除非空约束
ALTER TABLE test3 MODIFY 字段名 INT NULL; – 就是修改字段值可以为null
1.2 唯一约束
被修饰过的字段唯一,但对null不起作用。
方式1:建表的同时添加约束 格式: 字段名称 字段类型 unique
create table test(
id int unique,
username varchar(20) unique
);方式2:建表的同时在约束区域添加约束
create table test(
id int,
username varchar(20)，
unique(id)
);方式3:建表之后,通过修改表结构添加约束
alter table 表名 add unique(字段1,字段2);-- 添加的联合唯一
alter table 表名 add unique(字段1);-- 给一个添加唯一
alter table 表名 add unique(字段2);-- 给另一个添加唯一
示例：
create table un01(
id int,
username varchar(20)
);
alter table un01 add unique(id,username);
insert into un01 values(1,'tom'); -- 成功
insert into un01 values(1,'jack'); -- 成功
insert into un01 values(1,'tom'); -- 失败 Duplicate entry '1-tom' for key 'id'1.3 非空约束
被修饰过的字段非空
create table nn(
id int not null,
username varchar(20) not null
);
insert into nn values(null,'tom'); -- 错误的 Column 'id' cannot be null1.4 自增约束
要求：
1.被修饰的字段类型支持自增. 一般int
2.被修饰的字段必须是一个key 一般是primary key
create table ai01(
id varchar(10) auto_increment
);-- 错误 Incorrect column specifier for column 'id'
create table ai01(
id int auto_increment
);-- 错误 Incorrect table definition; there can be only one auto column and it must be defined as a key2.清空表格
格式:
truncate 表名;特点：
清空表格后，重新创建一张空表
truncate和delete from 区别:
delete属于DML语句 , truncate属于DDL语句;
delete逐条删除 , truncate干掉表,重新创建一张空表。
3.多表查询
以网上商城为例学习多表查询：
实体：
- 用户
- 订单
- 商品
常见关系:
- 一对多. 用户和订单
- 多对多. 订单和商品
- 一对一. 丈夫和妻子
3.1一对多表格创建、修改与删除
一对多的定义：
- 在开发中,关系中的一方称之为主表或者一表,关系中的多方称之为多表或者从表。
- 为了表示一对多的关系,一般会在多表的一方添加一个字段,字段名称自定义(建议:主表的名称_id)
- 字段类型一般和主表的主键的类型保持一致,我们称这个字段为外键
示例（用户表与订单表）：
用户表是主表，订单表是从表。
-- 创建用户表
create table user(
id int primary key auto_increment,
username varchar(20)
);
-- 创建订单表
create table orders(
id int primary key auto_increment,
totalprice double,
user_id int
);为了保证数据的有效性和完整性,添加约束(外键约束).
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);外键约束的格式：
alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
添加了外键约束之后有如下特点:
- 主表中不能删除从表中已引用的数据
- 从表中不能添加主表中不存在的数据
开发中处理一对多：
在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可。
添加外键后，如何删除和修改主表中的数据？
级联删除：删除主表中的某一元素，从表中关联的元素会被自动删除。
示例：删除用户表中lily这个用户，在级联删除的条件下，订单表中与lily相关联的订单被自动删除
格式：
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id) ON`user` DELETE CASCADE;
DELETE FROM USER WHERE username='lily';级联更新：修改主表中的某一元素，从表中关联的元素会被自动修改。
格式：
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE;
一般情况下，我们在建表时，就会加上级联更新和级联删除。
CREATE TABLE zhu(
zid INT PRIMARY KEY AUTO_INCREMENT,
zname VARCHAR(20)
);
CREATE TABLE cong(
zid INT PRIMARY KEY AUTO_INCREMENT,
zscore INT,
zzid INT,
-- 建表的时候就加上了级联更新和级联删除
FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE ON UPDATE CASCADE
);3.2 多对多表格创建
示例：商品和订单
一个商品可能存在于多个订单中，一个订单也可能包含多个商品，对于这种情况，我们一般引入一张中间表，用于存放两张表的主键。一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多。
为了保证数据的有效性和完整性，需要在中间表上添加两个外键约束即可.
-- 创建订单表
create table orders(
id int primary key auto_increment,
totalprice double,
user_id int
);
-- 创建商品表
create table product(
id int primary key auto_increment,
name varchar(20),
price double
);
-- 创建中间表
create table orderitem(
oid int,
pid int
);
-- 添加外键约束
alter table orderitem add foreign key(oid) references orders(id);
alter table orderitem add foreign key(pid) references product(id);3.3 多表查询
在多表查询时，我们需要关联信息。
3.3.1 内连接
内连接：符合条件的进行展示，不符合条件的不展示
格式1:显式的内连接
select a.* ,b.* from a [inner] join b on ab的连接条件
格式2:隐式的内连接：
select a. *,b. * from a,b where （ab的连接条件）
SELECT user.`id`, user.`username`, orders.* FROM USER,orders WHERE user.`id`=orders.`user_id`;
SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`user_id`;输出结果：
3.3.2 外连接
左外连接:
select a.,b. from a left [outer] join b on 连接条件
先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.
右外连接:
select a.,b. from b right [outer] join a on 连接条件
先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.
SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=orders.`user_id`;
SELECT user.*,orders.* FROM orders RIGHT OUTER JOIN USER ON user.`id`=orders.`user_id`;user表格：
输出结果：
3.3.3 子查询
子查询：一个查询依赖另一个查询.
示例用表：
-- 用户表(user)
CREATE TABLE `user` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`username` VARCHAR(50) -- 用户姓名
);
-- 订单表(orders)
CREATE TABLE `orders` (
`id` INT AUTO_INCREMENT PRIMARY KEY,
`price` DOUBLE,
`user_id` INT
);
-- 给订单表添加外键约束
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);
-- 向user表中添加数据
INSERT INTO USER VALUES(3,'张三');
INSERT INTO USER VALUES(4,'李四');
INSERT INTO USER VALUES(5,'王五');
INSERT INTO USER VALUES(6,'赵六');
-- 向orders 表中插入数据
INSERT INTO orders VALUES(1,1314,3);
INSERT INTO orders VALUES(2,1314,3);
INSERT INTO orders VALUES(3,15,4);
INSERT INTO orders VALUES(4,315,5);
INSERT INTO orders VALUES(5,1014,NULL);三个例子：
-- 一. 查看用户为张三的订单详情
SELECT * FROM orders WHERE user_id=(SELECT id FROM USER WHERE username='张三');
-- 二. 查询出订单的价格大于300的所有用户信息
SELECT * FROM USER WHERE user.id IN(SELECT user_id FROM orders WHERE price>300);
-- 三. 查询订单价格大于300的订单信息及相关用户的信息
SELECT temp.*,user.* FROM (SELECT orders.* FROM orders WHERE price>300)AS temp LEFT OUTER JOIN USER ON user.`id`=temp.user_id;
-- 方法2
SELECT orders.*,user.* FROM orders,USER WHERE price>300 AND user.id=orders.user_id;3.3.4 自连接查询
通过表的别名,给一张表起两个别名,将他视为两张表,来进行查询。
示例用表：
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);创建的表格如下：
在这个表格中，每一个员工都有一个员工姓名和上司姓名，现在要求：根据这张表格，将每个员工与其对应的直接上司查出来。
思路：将这张表格看作是两张表，一张是员工表，一张是上司表，查找条件是员工表.上司ID=上司表.上司姓名
SELECT ee.`ename` AS 员工,er.`ename` 上司 FROM emp AS ee,emp AS er WHERE ee.`mgr`=er.`empno`;查询结果：
