数据库：MySQL（Ⅱ）

最新推荐文章于 2024-11-06 10:59:12 发布

weixin_45919908

最新推荐文章于 2024-11-06 10:59:12 发布

阅读量141

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45919908/article/details/105294888

版权

文章目录

约束

SQL 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。
约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。
添加约束

-- 创建表时规定约束
-- CREATE TABLE 表名(列名1 数据类型 约束名,列名2 数据类型 约束名,...)
CREATE TABLE table_name
(
	column_name1 data_type(size) constraint_name,
	column_name2 data_type(size) constraint_name,
	column_name3 data_type(size) constraint_name,
	...
);

对于已经被创建的表，我们可以通过ALTER TABLE语句规定约束

PRIMARY KEY 主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

-- 在创建表时有第二种添加主键约束的方式
-- CREATE TABLE 表名(列名1 数据类型,列名2 数据类型,PRIMARY KEY(列名))
CREATE TABLE user_Info
(
	user_id INT,
	user_name VARCHAR(20),
	PRIMARY KEY(user_id)
);

-- 通过ALTER TABLE语句规定主键约束:ALTER TABLE 表 ADD PRIMARY KEY(列名)
ALTER TABLE user_Info ADD PRIMARY KEY(user_id)

-- 如果一个字段被设置为主键，那么该字段包含唯一的值且不能包含NULL值
INSERT INTO user_Info VALUES(1,'aaa') 	--成功
INSERT INTO user_Info VALUES(1,'bbb')	--失败
INSERT INTO user_Info VALUES(2,'bbb')	--成功

--联合主键:将多个字段看作整体设置为主键
-- 只能通过ALTER TABLE语句来设置联合主键
ALTER TABLE user_Info ADD PRIMARY KEY(user_id,user_name)
-- 作为联合主键的多个字段不能相同且不能包含NULL值
INSERT INTO user_Info VALUES(1,'aaa')	--成功
INSERT INTO user_Info VALUES(1,'bbb')	--成功
-- 会提示主键"1-aaa"的值重复
INSERT INTO user_Info VALUES(1,'aaa')	--失败

-- 删除主键约束:ALTER TABLE 表名 DROP PRIMARY KEY
ALTER TABLE user_Info DROP PRIMARY KEY
-- 删除主键约束的字段还存在非空约束，如果需要，还需要删除非空约束
-- ALTER TABLE 表名 MODIFY 列名 类型 NULL
ALTER TABLE user_Info MODIFY user_id INT NULL
-- 若该字段为INT类型，被规定了自增长约束和主键约束，需要先删除自增长约束再删除主键约束
ALTER TABLE table_name CHANGE column_name column_name data_type(size)

UNIQUE 唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE和 PRIMARY KEY约束均为列或列集合提供了唯一性的保证,PRIMARY KEY约束拥有自动定义的UNIQUE约束。

注意：每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARY KEY约束。

-- 命名UNIQUE约束，并定义多个列的联合UNIQUE约束
-- CONSTRAINT UNIQUE约束的名称 UNIQUE(列名1,列名2,...)
CREATE TABLE user_Info
(
	user_id INT,
	user_name VARCHAR(20),
	-- 当user_id和user_name的值都不相同，才能添加数据
	CONSTRAINT uc_userId UNIQUE(user_id,user_name)
);

-- 通过ALTER TABLE语句规定UNIQUE约束:ALTER TABLE 表名 ADD UNIQUE(列名)
ALTER TABLE user_Info ADD UNIQUE(user_id)
-- 通过ALTER TABLE语句规定多个列的联合UNIQUE约束
-- ALTER TABLE 表名 ADD UNIQUE(列名1,列名2,...)
-- 可以使用CONSTRAINT关键字命名UNIQUE约束
-- ALTER TABLE 表名 ADD CONSTRAINT UNIQUE约束的名称 UNIQUE(列名1,列名2,...)
ALTER TABLE user_Info ADD CONSTRAINT uc_userId UNIQUE(user_id,user_name)

-- 删除UNIQUE约束
-- ALTER TABLE 表名 DROP INDEX UNIQUE约束名称
-- 未命名的UNIQUE约束可以通过建表语句查询约束名称
ALTER TABLE user_Info DROP INDEX user_id
-- 如果联合UNIQUE约束没有命名，以其中一个列名为名称删除

UNIQUE约束对NULL值不起约束作用

-- 假设user_id被规定了UNIQUE约束
-- 以下三条添加记录的语句都能被执行成功
INSERT INTO user_Info VALUES(NULL,'aaa')
INSERT INTO user_Info VALUES(NULL,'bbb')
INSERT INTO user_Info VALUES(NULL,'ccc')

NOT NULL 非空约束

NOT NULL约束强制列不接受NULL值，如果不向字段添加值，就无法插入新记录或者更新记录。

-- 通过ALTER TABLE语句设置非空约束
ALTER TABLE user_Info MODIFY user_name VARCHAR(20) NOT NULL;

-- 删除非空约束
ALTER TABLE user_Info MODIFY user_name VARCHAR(20) NULL

E-R图

在介绍FOREIGN KEY约束前，需要了解以下E-R图
E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。
组成：

矩形框：表示实体，在框中记入实体名。
菱形框：表示联系，在框中记入联系名。
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型（（对于一对一联系，要在两个实体连线方向各写1；对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M）。

以一个简易的网上商城模型为例

实体：实体可以是具体的人和物，也可以是抽象的概念与联系。比如用户、订单、商品
属性：实体所具有的某一特性，一个实体可由若干个属性来刻画。比如用户的属性有用户名，密码等，订单的属性有订单号、订单金额、订单所属人等。
联系：联系也称关系，信息世界中反映实体内部或实体之间的关联。比如用户和订单之间是一对多的关系，一个用户可以支付多个订单，但一个订单只能有一个所属人；订单和商品是多对多的关系；例如夫妻就是一对一的关系

FOREIGN KEY外键约束

为了保证数据的有效性和完整性，需要添加外键约束。一个表中的FOREIGN KEY指向另一个表中的PRIMARY KEY，FOREIGN KEY所在的表称为从表（或多表），PRIMARY KEY所在的表称为主表（或一表）
添加外键约束之后的特点：
主表中不能删除从表中已引用的数据
从表中不能添加主表中不存在的数据

以刚才的网上商城为例

一对多关系的处理

-- 创建用户表
CREATE TABLE user_info(
	id INT PRIMARY KEY AUTO_INCREMENT,
	user_name VARCHAR(20)
);
-- 创建订单表
CREATE TABLE order_info(
	order_id INT PRIMARY KEY AUTO_INCREMENT,
	totalprice DOUBLE,
	user_id INT   
);

对于用户表和订单表来说，订单表中的user_id代表订单所属的用户的用户编号，如果订单属于一个用户表中的不存在的用户；亦或是我们删除用户表中某个用户时，但是如果该用户支付了订单在用户表中有他的数据。这些情况会导致数据的错乱。
所以我们需要为订单表中的user_id添加外键约束

-- 添加外键约束
-- ALTER TABLE 从表 ADD FOREIGN KEY(列名) REFERENCES 主表(列名)
ALTER TABLE order_info ADD FOREIGN KEY (user_id) REFERENCES user_info(id)

当添加了外键约束后，用户表中不能删除订单表中已引用的数据，订单表中不能添加用户表中不存在的数据

在建表时添加外键约束

CREATE TABLE order_info(
	order_id INT PRIMARY KEY AUTO_INCREMENT,
	totalprice DOUBLE,
	user_id INT FOREIGN KEY REFERENCES user_info(id)   
);

删除外键约束

-- 可以通过查看建表语句查询外键约束的名称
-- ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;
ALTER TABLE order_info DROP FOREIGN KEY order_info_ibfk_1;

级联删除与级联更新

级联删除
当删除主表中的数据时，会一并删除从表中引用该数据的数据
例如如果删除用户表中id为1的用户，订单表中user_id为1的订单也会被删除
如果不使用级联删除，我们需要先删除从表中引入的主表数据，再去删除主表数据，使用级联删除会方便数据的删除

-- 添加外键约束时设置级联删除
-- ALTER TABLE 从表 ADD FOREIGN KEY(列名) REFERENCES 主表(列名) ON DELETE CASCADE
ALTER TABLE order_info ADD FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE

级联更新
当更新主表中的主键数据时，会一并更新从表中引用该数据的数据
例如如果用户表中id为1的用户id改为了5，订单表中该用户支付的订单的uesr_id也会被更新为5

-- 添加外键约束时设置级联更新
-- ALTER TABLE 从表 ADD FOREIGN KEY(列名) REFERENCES 主表(列名) ON UPDATE CASCADE
ALTER TABLE order_info ADD FOREIGN KEY (user_id) REFERENCES user_info(id) ON UPDATE CASCADE

-- 添加外键约束时同时设置级联删除和级联更新
ALTER TABLE order_info ADD FOREIGN KEY (user_id) REFERENCES user_info(id) ON DELETE CASCADE ON UPDATE CASCADE

也可以在建表时添加级联删除和级联更新

CREATE TABLE order_info(
	order_id INT PRIMARY KEY AUTO_INCREMENT,
	totalprice DOUBLE,
	user_id INT,
	FOREIGN KEY(user_id) REFERENCES user_info(id) ON DELETE CASCADE ON UPDATE CASCADE   
);

多对多关系的处理

对于多对多的关系，需要将多对多的关系拆分成两个一对多，可以引入一张中间表,存放两张表的主键,在中间表中，这两个字段要被设置为外键

-- 创建商品表
CREATE  TABLE pro_info(
	pro_id INT PRIMARY KEY AUTO_INCREMENT,
	price DOUBLE
)
-- 创建中间表
-- 商品表和订单表两者与中间表都是一对多的关系
CREATE TABLE order_product
(
-- o_id:订单表中的订单号
-- p_id:商品表中的商品编号
	o_id INT,
	p_id INT
)
-- 添加外键约束
ALTER TABLE order_product ADD FOREIGN KEY(o_id) REFERENCES order_info(order_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE order_product ADD FOREIGN KEY(o_id) REFERENCES pro_info(pro_id) ON DELETE CASCADE ON UPDATE CASCADE;

当添加外键约束后，就使订单表和商品表之间产生的多对多的关系

AUTO INCREMENT与UNSIGNED

AUTO INCREMENT

AUTO INCREMENT可以当作约束的一种，会在新记录插入表中时生成一个唯一的数字，一般用于INT类型的字段，并配合主键一起使用，否则会报错

CREATE TABLE user_Info
(
	user_id INT AUTO_INCREMENT PRIMARY KEY,
	user_name VARCHAR(20)
);
-- 默认地，AUTO_INCREMENT的开始值是1，每条新记录递增1。
/*如果不添加user_id的值，或者添加的值为NULL,那么该记录的user_id值为1，
之后添加记录时，如果不指定user_id的值或者其值为NULL，新记录user_id值递增1*/
INSERT INTO user_Info(user_name) VALUES('aaa');

-- 通过添加的第一条记录AUTO_INCREMENT的起始值
-- 该记录的user_id值为10，之后记录的user_id值从10开始自增
INSERT INTO user_Info VALUES(10,'ddd')
-- 通过ALTER TABLE语句指定初始值
ALTER TABLE user_Info AUTO_INCREMENT=100

需要注意以下一个现象

-- 添加记录，该记录的user_id值为1
INSERT INTO user_Info VALUES(NULL,'aaa')
-- 删除该条记录
DELETE FROM user_Info WHERE user_id=1
-- 再次添加记录，此时该记录的user_id值为2，表中不存在user_id值为1的记录
INSERT INTO user_Info VALUES(NULL,'ddd')

-- 删除AUTO INCREMENT约束:ALTER TABLE 表名 CHANGE 列名 列名 数据类型
ALTER TABLE user_Info CHANGE user_id user_id INT

UNSIGNED

UNSIGNED可被当作约束的一种，可以将数字类型无符号化

CREATE TABLE user_Info
(
	user_id TINYINT UNSIGNED,
	user_name VARCHAR(20)
);

user_id被加上UNSIGNED约束后，不允许添加user_id为负数的记录
TINYINT类型的范围为-128~127，被加上UNSIGNED约束后，它的范围变成0~255
因为会将TINYINT类型原有负数范围的数据长度添加给正数范围的数据长度，所以如果添加user_id的值为245的记录，会添加成功
总结：
字段被添加UNSIGNED约束后，该字段的数据不能为负数，且该字段的数据类型原有负数范围的数据长度添加给正数范围的数据长度

JOIN 连接

JOIN用于把来自两个或多个表的行结合起来实现多表查询

-- 创建用户表
CREATE TABLE users(                                  
	user_id INT AUTO_INCREMENT PRIMARY KEY,                
  	username VARCHAR(50)                                             
);
-- 创建订单表
 CREATE TABLE orders(                                                  
  	order_id INT  AUTO_INCREMENT PRIMARY KEY,                                  
  	price DOUBLE,                                           
  	u_id INT                                       
);
-- 插入数据
INSERT INTO users VALUES(1,'aaa');
INSERT INTO users VALUES(2,'bbb');
INSERT INTO users VALUES(3,'ccc');

INSERT INTO orders VALUES(1,1000,1);
INSERT INTO orders VALUES(2,500,1);
INSERT INTO orders VALUES(3,66,2);
INSERT INTO orders VALUES(4,800,2);

如果只是没有加入关联语句的查询两张表中的所有信息，查询出的结果只是两表的笛卡尔积，这样查询出的数据没有意义

SELECT users.*,orders.* FROM users,orders

如下图所示（图片来自百度百科）：
在这里插入图片描述

INNER JOIN 内连接

INNER JOIN 关键字在表中存在至少一个匹配时返回行
在这里插入图片描述

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

INNER JOIN与JOIN是相同的。

-- 查询用户信息和订单信息，如果某用户没有订单将不返回
-- 因为user_id为3的用户没有支付订单，所以将不被查询到
SELECT users.user_id,users.username,orders.order_id,orders.price 
FROM users 
INNER JOIN orders 
ON users.user_id=orders.u_id;

以上格式我们称之为显示内连接
隐式内连接

SELECT users.user_id,users.username,orders.order_id,orders.price 
FROM users,orders 
WHERE users.user_id=orders.u_id;

隐式内连接与显示内连接的查询结果是相同的

内连接查询多张表

-- 创建商品表
CREATE TABLE pros(                                  
  pro_id INT AUTO_INCREMENT PRIMARY KEY,                
  pro_price DOUBLE                                              
);
-- 创建订单和商品的中间表
CREATE TABLE order_pro(                                  
  o_id INT,                
  p_id INT                                                
);
-- 查询所有用户的订单信息，以及订单里面的商品信息
SELECT u.*,o.order_id,o.price,p.*
FROM users AS u
INNER JOIN orders AS o
ON u.user_id=o.u_id
INNER JOIN order_pro AS op
ON o.order_id=op.o_id
INNER JOIN pros AS p
ON op.p_id=p.pro_id;

自连接查询

-- 创建员工表，mgr代表上司的员工号
CREATE TABLE emp(
	empno int(11),
	ename varchar(50),
	job varchar(50),
	mgr int(11),
	deptno int(11)
);
-- 查询员工信息(员工号、员工姓名、上司的姓名)
-- 将表emp看作两张表，员工表e1，上司表e2，将这两张表进行内连接查询
SELECT e1.empno,e1.ename,e2.ename 
FROM emp AS e1 
INNER JOIN emp AS e2 
ON e1.mgr=e2.empno;

外连接

LEFT JOIN 左外连接

LEFT JOIN关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为NULL。
在这里插入图片描述

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

LEFT JOIN也可被写作LEFT OUT JOIN

-- 查询所有用户信息和订单信息，如果用户没有订单，订单信息为NULL
-- user_id为3的用户没有支付订单，所以订单号和订单金额都为NULL
SELECT users.user_id,users.username,orders.order_id,orders.price  
FROM users 
LEFT JOIN orders 
ON users.user_id=orders.u_id;

RIGHT JOIN 右外连接

RIGHT JOIN关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为NULL。
在这里插入图片描述

SELECT users.user_id,users.username,orders.order_id,orders.price  
FROM orders 
RIGHT JOIN users 
ON users.user_id=orders.u_id;

RIGHT JOIN也可被写作RIGHT OUTER JOIN

-- 将连接方式改为RIGHT JOIN，users表和orders表交换位置，查询结果与之前的LEFT JOIN相同
SELECT users.user_id,users.username,orders.order_id,orders.price 
FROM orders 
RIGHT JOIN users 
ON users.user_id=orders.u_id;

LEFT JOIN和RIGHT JOIN可以相互转换，查询同样的结果

子查询

子查询也称为内部查询、嵌套查询，指的是在WHERE子句中嵌入查询语句，子查询可以与SELECT、INSERT、UPDATE和DELETE语句一起使用。
嵌套的查询语句所查询出的结果作为外部查询语句的条件。

-- 查询用户名为'aaa'的订单信息
SELECT order_id,price 
FROM orders 
WHERE u_id=(SELECT user_id FROM users WHERE username='aaa');

-- 查询订单价格大于300的用户信息
-- 因为嵌套的查询语句查询出多条记录，所以需要使用IN关键字
SELECT user_id,username 
FROM users 
WHERE user_id IN(SELECT u_id FROM  orders WHERE price>300)

将子查询的结果作为临时表

-- 查询订单价格大于300的订单信息及用户信息
-- 使用内连接
SELECT users.user_id,users.username,orders.order_id,orders.price 
FROM users INNER JOIN orders 
ON users.user_id=orders.u_id AND orders.price>300;

--使用子查询
-- 将子查询结果作为临时表与users表进行内连接查询
SELECT users.*,temp.order_id,temp.price 
FROM users 
INNER JOIN (SELECT orders.* FROM orders WHERE price>300) AS temp 
ON temp.u_id=users.user_id;

复制表

-- 复制表且复制表中记录，where子句的条件为真，1代表真
-- CREATE TABLE 表名 AS SELECT 被复制表的列名 FROM 被复制的表 WHERE 1=1
CREATE TABLE users_demo AS SELECT * FROM users WHERE 1=1;

-- 复制表但不复制表中记录，where子句的条件为假，非1代表假
-- CREATE TABLE 表名 AS SELECT 被复制表的列名 FROM 被复制的表 WHERE 1=2
CREATE TABLE users_demo AS SELECT * FROM users WHERE 1=1;