MySQL中的约束+关系+多表查询+ER图

最新推荐文章于 2023-01-02 12:11:34 发布

pigpigpi

最新推荐文章于 2023-01-02 12:11:34 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签：数据库 mysql 数据分析 java oracle

本文链接：https://blog.csdn.net/qq_40733968/article/details/107891654

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一.约束

1.概述

作用:
		为了保证数据的有效性和完整性

	mysql中常用的约束:
			主键约束(primary key)  
			自增长约束 auto_incrment  加在整数型的字段配和主键约束来使用
			唯一约束(unique) 
			非空约束(not null) 
			外键约束(foreign key)  
			sex ENUM('男','女')  -- 把一个字段的数据类型设置为枚举类型 也可以起到一种约束的效果
			非负约束 UNSIGNED
			例如：这个数据类型 TINYINT 能表示的范围 -128---127   
                        我添加了这个约束  TINYINT  UNSIGNED    范围就是 0----255 也就是没有了负数值

2.主键约束

(1)添加主键约束

  主键约束:被修饰过的字段唯一非空
	注意:一张表只能有一个主键,这个主键可以包含多个字段,我们也建议你在表中提供主键。

  建立主键约束的三种方式:
  	方式1:建表的同时添加约束 格式: 字段名称 字段类型 primary key
	方式2:建表的同时在约束区域添加约束 
			所有的字段声明完成之后,就是约束区域了
			格式: primary key(字段1,字段2)
	方式3:建表之后,通过修改表结构添加约束
	  格式:alter table pk02 add primary key(字段名1,字段名2..);

 -- 创建主键的三种方式
 -- 	方式1:建表的同时添加约束 格式: 字段名称 字段类型 primary key
CREATE TABLE test(
  id INT(8) PRIMARY KEY,
  uname VARCHAR(10) NOT NULL UNIQUE,
  PASSWORD VARCHAR(10) NOT NULL
)

/* 方式2:建表的同时在约束区域添加约束 
			所有的字段声明完成之后,就是约束区域了
			格式: primary key(字段1,字段2) */
CREATE TABLE test2(
  id INT(8),
  uname VARCHAR(10) NOT NULL UNIQUE,
  PASSWORD VARCHAR(10) NOT NULL,
  PRIMARY KEY(id)  -- 指定主键
)

-- 方式3:建表之后,通过修改表结构添加约束
CREATE TABLE test3(
  id INT(8),
  uname VARCHAR(10) NOT NULL UNIQUE,
  PASSWORD VARCHAR(10) NOT NULL
)

ALTER TABLE test3 ADD PRIMARY KEY(id,uname,PASSWORD)

(2)联合主键

-- 联合主键：把多个字段看做一个整体，设定为主键
 
   CREATE TABLE test5(
	username VARCHAR(20),
	PASSWORD VARCHAR(30)
 
 )
 -- 采用修改表 的方式来设置联合主键
 ALTER TABLE test5 ADD PRIMARY KEY(username,PASSWORD);

(3)删除主键约束

删除主键约束:分两种情况
  情况1: 这个字段,只有主键约束,分两步来删除主键约束
	第一步:       alter table 表名 drop primary key;  -- 这样只删除了唯一,他还有个非空约束,所以得再删除非空约束
	第二步:       alter table [表名] modify [列名] varchar(20) null; --修改字段名还为原来的字段 加上null即可
	
  情况2: 这个字段,是一个int类型字段,既有主键约束,又有自增长约束,那么得先删除自增长约束,在删除主键约束
			   第一步:删除自增长约束,其实就是修改自增长字段名和数据类型还为原来的字段名和类型
				alter table 表名 change 字段名 字段名 数据类型; --删除自增长约束
                           第二步:删除主键约束
				alter table 表名 drop primary key;
			   第三步:删除非空约束
			    ALTER TABLE test3 MODIFY sid INT NULL;  -- 就是修改字段值可以为null

-- 删除主键约束

/*
删除主键约束:分两种情况
   情况1: 这个字段,只有主键约束,分两步来删除主键约束
          第一步:       alter table 表名 drop primary key;  -- 这样只删除了唯一,他还有个非空约束,所以得再删除非空约束
	  第二步:       alter table [表名] modify [列名] varchar(20) null; --修改字段名还为原来的字段 加上null即可
*/

-- 删除唯一约束
ALTER TABLE test DROP PRIMARY KEY
-- 删除非空约束
ALTER TABLE test MODIFY id INT(8) NULL 

/*
 情况2: 这个字段,是一个int类型字段,既有主键约束,又有自增长约束,那么得先删除自增长约束,在删除主键约束
   第一步:删除自增长约束,其实就是修改自增长字段名和数据类型还为原来的字段名和类型
	alter table 表名 change 字段名 字段名 数据类型; --删除自增长约束
   第二步:删除主键约束
	alter table 表名 drop primary key;
   第三步:删除非空约束
         ALTER TABLE test3 MODIFY sid INT NULL;  -- 就是修改字段值可以为null
*/

CREATE TABLE test4(
  id INT(8) PRIMARY KEY AUTO_INCREMENT,
  uname VARCHAR(10) NOT NULL UNIQUE,
  PASSWORD VARCHAR(10) NOT NULL
)

-- 1.删除自增长约束
ALTER TABLE test4 CHANGE id id INT(8);
-- 2.删除主键约束
ALTER TABLE test4 DROP PRIMARY KEY
-- 3.删除非空约束
ALTER TABLE test4 MODIFY id INT NULL

3.唯一约束(了解)

被修饰过的字段唯一,对null不起作用
		方式1:建表的同时添加约束 格式: 字段名称 字段类型 unique
			create table un(
				id int unique,
				username varchar(20) unique
			);
			
			insert into un value(10,'tom');-- 成功
			insert into un value(10,'jack');-- 错误 Duplicate entry '10' for key 'id'
			insert into un value(null,'jack');-- 成功
			insert into un value(null,'rose');-- 成功
			
		方式2:建表的同时在约束区域添加约束 
			所有的字段声明完成之后,就是约束区域了
			unique(字段1,字段值2...)
		方式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'

唯一约束   UNIQUE 注意 对 null值没有作用

     CREATE TABLE test7(
        id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键自增长
	username VARCHAR(20) UNIQUE, -- 唯一约束
	PASSWORD VARCHAR(30)
 
 )
 
  INSERT INTO test7 VALUES(NULL,'bbb','12345678')
  
  INSERT INTO test7 VALUES(NULL,NULL,'12345678')

4.非空约束(了解)

特点:被修饰过的字段非空
		方式:
			create table nn(
				id int not null,
				username varchar(20) not null
			);
			
			insert into nn values(null,'tom');--  错误的 Column 'id' cannot be null

5.清空表

truncate 清空表 ★
	格式:
		truncate 表名; 干掉表,重新创建一张空表
	和delete from 区别:
		delete属于DML语句  truncate属于DDL语句
		delete逐条删除	truncate干掉表,重新创建一张空表

6.自增约束

auto_increment 自增
	要求:
		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 key
	
	
注意事项:
        自增长约束，他是要配合这个主键一块来使用，而且针对的是整数型的字段。  


            CREATE TABLE test6(
                id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键自增长
            username VARCHAR(20),
            PASSWORD VARCHAR(30)

         )
         -- 当你删了一条数据，下次再增加一条时，不会再用之前你删过的那条的主键的值。
         INSERT INTO test6 VALUES(NULL,'bbb','12345678')

         DELETE FROM test6 WHERE id=10;

7.非负约束

非负约束:UNSIGNED

    CREATE TABLE test10( 
        id TINYINT UNSIGNED,  -- 非负
	username VARCHAR(20),
	PASSWORD VARCHAR(30)
 
 )

8.枚举类型

-- 枚举类型
CREATE TABLE tt(
  id INT PRIMARY KEY,
  uname VARCHAR(20) NOT NULL UNIQUE,
  upassword VARCHAR(30) NOT NULL,
  sex ENUM('男','女','妖')  -- 只有这三个可以选择
)

二.关系

1.概述

用户表 订单表  商品表
 一对多    用户表1--订单表N  我们把一方这张表一般会成为 主表或单表或一表 把多方这张表称为 多表或从表。
 多对多    订单表N----商品表N
 一对一

2.外键约束

为了保证数据的有效性和完整性,添加约束(外键约束).
		在多表的一方添加外键约束
			格式:
				alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
			例如:
				alter table orders add foreign key(user_id) references user(id);
	添加了外键约束之后有如下特点:★
		1.主表中不能删除从表中已引用的数据
		2.从表中不能添加主表中不存在的数据
	开发中处理一对多:★
		在多表中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致,
		为了保证数据的有效性和完整性,在多表的外键上添加外键约束即可.
	那如果添加了外键后我想删除主表中的数据 怎么办 ?
		方式1: 级联删除
			ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE; 
	        	          	然后你就可以删除主表中的数据了
			当然你可以级联删除和级联更新 都加上 FOREIGN KEY (sid) REFERENCES stu(sid) ON DELETE CASCADE ON UPDATE CASCADE)
		方式2:先把带有外键的多表的数据删除,再删除一表中的数据

-- 外键约束
-- 创建订单表
-- 在从表添加外键约束
CREATE TABLE orders(
  id INT PRIMARY KEY AUTO_INCREMENT,
  totalprice INT(10) DEFAULT NULL,
  user_id INT
  -- 方式1: 
   -- FOREIGN KEY(user_id) REFERENCES USER(id)   
)
-- 方式2:
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES USER(id)

CREATE TABLE USER(
  id INT PRIMARY KEY,
  uname VARCHAR(10) NOT NULL,
  upassword VARCHAR(10) NOT NULL
)

## 3.级联更新和级联删除

那如果添加了外键后我想删除主表中的数据 怎么办 ?
		方式1: 级联删除
			ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE; 
 
 
 方式2演示级联删除和级联更新：建表的时候就加上了级联更新和级联删除 -- 建表的时候就加上了级联更新和级联删除 
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,
 -- 方式2：建表的时候就加上了级联更新和级联删除 
    FOREIGN KEY(zzid) REFERENCES zhu(zid) ON DELETE CASCADE ON UPDATE CASCADE
);

4.多对多关系

例子:商品和订单
		-- 创建商品表
	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);
	
	开发中处理多对多:★
		引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分
		成两个一对多了
		为了保证数据的有效性和完整性
			需要在中间表上添加两个外键约束即可.

三.多表查询

在这里插入图片描述

1.笛卡尔积

 多表查询：多张表联合起来查询。
 多表联合
  我要查询每个人的订单信息。
  多张表没有关联条件的查询，查询出的结果是没有意义的。
 像这种把多张表简单关联起来查询，查询出来 称之为笛卡尔积。
 他就是把多张表的列数相加，行数相乘，这样得出的查询结果
SELECT user.*,`orders`.* FROM USER,orders;  -- 多表联合没有条件查询

2.条件

 我们在进行多张表查询的时候，要有关联条件，
 如果是N张表关联查询，至少需要 N-1 个关联条件

 我要查询每个人的订单信息。

SELECT user.*,`orders`.* FROM USER,orders WHERE user.`id`=`orders`.`user_id`

SELECT user.id,user.`username`,`orders`.`id` AS uid,orders.`totalprice`,orders.`user_id` 
FROM USER,orders 
WHERE user.`id`=`orders`.`user_id`

 给表起别名 as  as 可以省略不写
SELECT u.*,o.* FROM USER u,orders o WHERE u.id=o.user_id;

用户表:
在这里插入图片描述
订单表:

中间表:

商品表:
在这里插入图片描述

3.内连接

内连接：不符合条件的数据是不展示出来的

格式1:显式的内连接
		select a.*,b.* from a [inner] join b on ab的连接条件
	格式2:隐式的内连接
		select a.*,b.* from a,b where ab的连接条件

注意事项:内连接不符合条件的数据不展示

-- 内连接：不符合条件的数据是不展示出来的
-- 隐式内连接
-- 我要查询每个人的订单信息。

-- 隐式示内连接
SELECT user.*,orders.* FROM USER,orders WHERE user.`id`=orders.`uid`

-- 显示内连接  INNER JOIN ON  inner 可以省略不写
SELECT user.*,orders.* FROM USER INNER JOIN orders ON user.`id`=orders.`uid`

在这里插入图片描述

4.外连接

外连接:★
	左外连接:★
		select a.*,b.* from a left [outer] join b on 连接条件;  outer 可以不写
		意思:
			先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.
	右外连接:★
		select a.*,b.* from b right [outer] join a on 连接条件;  outer 可以不写
		意思:
			先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.

注意事项:
   外连接会将不符合条件的也展示出来,以null值展示

-- 左外连接
-- 先展示用户,再展示订单
SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=`orders`.`uid`

-- 右外连接
-- 先展示订单,再展示用户
SELECT user.*,orders.* FROM USER RIGHT OUTER JOIN orders ON user.`id`=`orders`.`uid`

左外连接:
在这里插入图片描述
右外连接:

5.子查询

子查询:★
	一个查询依赖另一个查询.

-- 子连接
-- 一. 查看用户为张三的订单详情

SELECT user.`id` FROM USER WHERE user.`username`='张三';

SELECT orders.* FROM orders WHERE orders.`uid`=(SELECT user.`id` FROM USER WHERE user.`username`='张三');

-- 查询订单价格大于300的订单信息及相关用户的信息。


SELECT * FROM USER WHERE id IN(SELECT uid FROM orders WHERE totalPrice >300);

6.自连接查询

自连接查询---通过表的别名,给一张表起两个别名,将他视为两张表,来进行查询

 		 比如:我要查询emp表中 员工姓名 所对应的 老板姓名
	
 		 因为这些信息都在一张表 emp中 
  		比如 员工号7369 的 SMITH 他对应的老板编号是(MGR) 7902 而7902 又是员工FORD(7902) 那FORD 对应的老板编号又是 7566
  		所以说 一个员工既是某几个员工的老板,他也有自己的老板
  		所以我要查询这个员工的所对应的老板 就可以使用自连接查询

		我们假设有两张表一张员工表,一张老板表,如果员工的老板号=老板的员工号 就表示这个员工是另外一个员工的老板
		 select e.ename as 员工姓名,b.ename as 老板姓名 from emp e,emp b where e.mgr=b.empno;

7.复制数据

 --  把一张旧表中的数据，复制到一张新表中
CREATE TABLE orders2 AS SELECT * FROM orders

-- 我只想要旧表中的表头，不要表中的数据 你需要写个条件让条件为 假就行
CREATE TABLE orders3 AS SELECT * FROM orders WHERE 1=0

CREATE TABLE orders4 AS SELECT * FROM orders WHERE 1=1

四.ER图

网上商城的实体:
	用户 订单 商品 分类
常见关系:
	一对多. 用户和订单  分类和商品
	多对多. 订单和商品	学生和课程
	一对一. 丈夫和妻子	
ER图可以描述实体于实体之间的关系
	实体用矩形表示
	属性用椭圆表示
	关系用菱形表示

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

在这里插入图片描述

pigpigpi

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
MySQL中的约束+关系+多表查询+ER图

一.约束1.概述作用: 为了保证数据的有效性和完整性 mysql中常用的约束: 主键约束(primary key) 自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用唯一约束(unique) 非空约束(not null) 外键约束(foreign key) sex ENUM('男','女') -- 把一个字段的数据类型设置为枚举类型也可以起到一种约束的效果非负约束 UNSIGNED 例如：这个数据类型
复制链接

扫一扫