重学JavaWeb（9）MySQL约束与多表查询

最新推荐文章于 2024-02-22 18:06:15 发布
最新推荐文章于 2024-02-22 18:06:15 发布
阅读量163 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nswzr/article/details/109457719
版权

1. 约束

1. 约束的作用：为了保证数据的有效性和完整性
2. MySQL中常用的约束：

1. 主键约束：primary key
2. 自增长约束：auto_incrment
3. 唯一约束：unique
4. 非空约束：not null
5. 外键约束：foreign key
6. 枚举约束：enum
7. 非负约束：unsigned

3. 主键约束
（1）主键约束的数据非空且唯一，一张表中只有一个主键
（2）添加主键的方式

-- 添加主键方式1：在建表的时候直接添加
create table test(
	username varchar(20) primary key,
	password varchar(20)
)
insert into test values('aaa','123456');
insert into test values('bbb','12345');

-- 添加主键方式2：表中添加
CREATE TABLE test2(
	username VARCHAR(20),
	PASSWORD VARCHAR(20),
	primary key(username)
);
INSERT INTO test2 VALUES('aaa','123456');
INSERT INTO test2 VALUES('bbb','12345');

-- 添加方式3：表外添加
create table test3(
	username varchar(20),
	password varchar(20)
);
alter table test3 add primary key(username);
insert into test3 values('aaa','123456');
insert into test3 values('bbb','12345');

-- 联合主键：把多个字段看成一个整体，设定为主键
-- 添加联合主键方式：表外添加
CREATE TABLE test4(
	username VARCHAR(20),
	PASSWORD VARCHAR(20)
);
alter table test4 add primary key(username,password);

4. 自增长约束
（1）如果某一列是数值类型的，使用auto_increment来完成自增长
（2）添加自增长的方式

-- 添加主键自增长
CREATE TABLE test5(
	id int primary key auto_increment,
	username VARCHAR(20),
	PASSWORD VARCHAR(20)
);

（3）注意：当你删除了一条数据时，下次再添加一条，不会再用之前删除的那条主键的值

5. 唯一约束
（1）unique，某一列的值不能重复
（2）注意：唯一约束可以有mull值，但是只能有一条记录为null
（3）添加方式

-- 添加唯一约束
CREATE TABLE test6(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20) UNIQUE,
	PASSWORD VARCHAR(20)
);

6. 非空约束
（1）not null，某一列的值不能为null
（2）添加方式

-- 添加非空约束
CREATE TABLE test6(
	id int primary key auto_increment,
	username VARCHAR(20) unique not null,  -- 唯一且非空
	PASSWORD VARCHAR(20) not null  -- 非空
);

7. 非负约束
（1）unsigned，某一列的值不能为负
（2）添加方式

-- 添加非负约束
CREATE TABLE test7(
	id INT PRIMARY KEY AUTO_INCREMENT,
	age TINYINT UNSIGNED  -- 非负约束
);

（3）注意：原本TINYINT这个数据类型可以表示的范围是-128–127，添加了非负约束后，TINYINT的范围就变成了0–255

8. 枚举约束
（1）enum，该列只能远枚举中出现的
（2）添加方式

-- 添加枚举约束
CREATE TABLE test8(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	sex ENUM('男','女','妖')  -- 枚举约束
);

2. 数据库的设计

1. 实体与实体之间的关系
（1）一对多：用户表(1) – 订单表(N)，我们把1这一方的表称为主表或者单表，把N这一方的表称为多表或者从表
（2）多对多：订单表(N) – 商品表(N)
（3）一对一

2. 一对多关系
（1）为了保证数据的有效性和完整性，可以去添加外键约束，让两张表之间形成外键约束，来避免数据的不合理
（2）一般会在多表的一方添加外键约束，去关联主表一方的主键
（3）如果添加了外键约束，则有如下特点

1. 主表一方，不能删除从表还在引用的数据
2. 从表一方，不能添加主表没有描述的数据

（4）级联删除 / 级联更新：ON DELETE CASCADE / ON UPDATE CASCADE
（5）代码示例

-- 外键与级联操作的添加方式
-- 用户表
create table users(
	id int primary key auto_increment,
	username varchar(20)
);
-- 订单表
create table orders(
	id int primary key auto_increment,
	totalprice double,
	users_id int,
	foreign key(users_id) references users(id) on delete cascade on update cascade
);

3. 多对多关系
（1）多对多关系需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别关联两张表的主键
（2）代码示例

-- 订单表
CREATE TABLE orders(
	id INT PRIMARY KEY AUTO_INCREMENT,
	totalprice DOUBLE,
	users_id INT,
	FOREIGN KEY(users_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- 商品表
CREATE TABLE product(
	id  INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	price DOUBLE
);

-- 中间表
CREATE TABLE orderitem(
	oid INT,
	pid INT,
	FOREIGN KEY(oid) REFERENCES orders(id),
	FOREIGN KEY(pid) REFERENCES product(id)
)

3. 多表查询

1. 概念：在进行多表查询时，要有关联条件，如果是N张表关联查询，至少需要N-1个关联条件
2. 查询语法

select
	列名列表
from
	表名列表
where
	...

3. 具体细节：
（1）内连接查询（隐式内连接）：使用where条件消除无用数据

-- 查询所有员工信息和对应的部门信息
select * from emp,dept where emp.'dept_id' = dept.'id'
-- 查询员工表的名称，性别。部门表的名称
select emp.name,emp.gender,dept.name from emp,dept where emp.'id' = dept.'id'

select
	t1.name,   -- 员工表的姓名
	t1.gender,  -- 员工表的性别
	t2.name   -- 部门表的名称
from
	emp t1,
	dept t2
where 
	t1.'dept_id' = t2.'id';

（2）外连接

1. 左外连接（常用）：
	（1）语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
	（2）查询的是左表所有数据以及其交集部分
	（3）例子
		-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门则不显示部门
		select t1.*,t2.'name' from emp t1 left join dept t2 on t1.'dept_id' = t2.'id';
2. 右外连接：
	（1）语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
	（2）查询的是右表所有数据已集齐交集部分
	（3）例子
		select * from dept t2 right join emp t1 on t1.'dept_id' = t2.'id'

（3）子查询

1. 概念：查询中嵌套查询，称嵌套查询为子查询
2. 例子
	-- 查询工资最高的员工信息
	-- 1.查询最高工资是多少 9000
	select max(salary) from emp;
	-- 2.查询员工信息，使工资等于9000
	select * from emp where emp.'salary' = 9000;

	-- 一条sql语句即可完成
	select * from emp where emp.'salary' = (select max(salary) from emp);
3. 子查询的不同情况
	（1）子查询的结果可以是单行单列的
		子查询可以作为条件，使用运算符去判断。（运算符：> < = >= <=）
		-- 查询员工工资小于平均工资的人
		select * from emp where emp.salary < (select avg(salary) from emp);
	（2）子查询的结果可以是多行单列的
		子查询可以作为条件，使用运算符in来判断
		-- 查询‘财务部’和‘市场部’所有员工的信息
		select id from dept where name = '财务部' or name = '市场部';
		select * from emp where dept_id = 3 or dept_id = 2;
		-- 子查询
		select * from emp where dept_id in (select id from dept where name = '财务部' or name = '市场部');
	（3）子查询的结果可以是多行多列的
		子查询可以作为一张虚拟表参与查询
		-- 查询员工入职日期是2011-11-11之后的员工信息和部门信息
		-- 子查询
		select 
			* 
		from 
			dept t1,(select * from emp where emp.'join_date' > '2011-11-11') t2
		where
			t1.id = t2.dept_id;

（4）多表查询练习

1. 找到需要使用的表
2. 在需要的表中找到表之间的联系

-- 部门表
CREATE TABLE dept (
	id INT PRIMARY KEY PRIMARY KEY, -- 部门id
	dname VARCHAR(50), -- 部门名称
	loc VARCHAR(50) -- 部门所在地
);
			
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
	(10,'教研部','北京'),
	(20,'学工部','上海'),
	(30,'销售部','广州'),
	(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
	id INT PRIMARY KEY,
	jname VARCHAR(20),
	description VARCHAR(50)
);				
	
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
	(1, '董事长', '管理整个公司，接单'),
	(2, '经理', '管理部门员工'),
	(3, '销售员', '向客人推销产品'),
	(4, '文员', '使用办公软件');
				
-- 员工表
CREATE TABLE emp (
​	id INT PRIMARY KEY, -- 员工id
​	ename VARCHAR(50), -- 员工姓名
​	job_id INT, -- 职务id
​	mgr INT , -- 上级领导
​	joindate DATE, -- 入职日期
​	salary DECIMAL(7,2), -- 工资
​	bonus DECIMAL(7,2), -- 奖金
​	dept_id INT, -- 所在部门编号
​	CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
​	CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);​				

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
	(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
	(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
	(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
	(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
	(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
	(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
	(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
	(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
	(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
	(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
	(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
	(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
	(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
	(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
	​				
-- 工资等级表
CREATE TABLE salarygrade (
​	grade INT PRIMARY KEY,   -- 级别
​	losalary INT,  -- 最低工资
​	hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
	(1,7000,12000),
	(2,12010,14000),
	(3,14010,20000),
	(4,20010,30000),
	(5,30010,99990);

-- 需求：

-- 1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
/*
分析：
	1. 员工编号，员工姓名，工资需要查询emp表，职务名称，职务描述需要查询job表
	2. 查询条件 emp.job_id = job.id
*/
select 
	t1.'id',   -- 员工编号
	t1.'ename',  -- 员工姓名
	t1.'salary',   -- 员工工资
	t2.'jname',   -- 职务名称
	t2.'description'   -- 职务描述
from
	emp t1,job t2
where
	t1.'job_id' = t2.'id';

-- 2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
分析：
	1. 员工编号、员工姓名、工资emp表，职务名称、职务描述job表，部门名称、部门位置dept表
	2. 条件：emp.job_id = job.id and emp.dept_id = dept.id
*/
select 
	t1.'id',   -- 员工编号
	t1,'ename',   -- 员工姓名
	t1.'salary',   -- 员工工资
	t2.'jname',   -- 职务名称
	t2.'description',   -- 职务描述
	t3.'dname',   -- 部门名称
	t3.'loc'   -- 部门位置
from
	emp t1,job t2,dept t3
where
	t1.'job_id' = t2.'id' and t1.'dept_id' = t3.'id';

-- 3. 查询员工姓名，工资，工资等级
/*
分析：
	1. 员工姓名、工资emp表，工资等级salarygrade表
	2. 条件 emp.salary >= salarygrde.losalary and emp.salary <= salarygrde.hisalary
			emp.salary between salarygrade.losalary and salarygrade.hisalary
*/
select
	t1.ename,
	t1.'salary',
	t2.*
from
	emp t1,salarygrade t2
where
	t1.'salary' between t2.'losalary' and t2.'hisalary';

-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
分析：
1. 员工姓名，工资 emp，职务名称，职务描述 job 部门名称，部门位置，dept  工资等级 salarygrade
2. 条件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT 
	t1.`ename`,
	t1.`salary`,
	t2.`jname`,
	t2.`description`,
	t3.`dname`,
	t3.`loc`,
	t4.`grade`
FROM 
	emp t1,job t2,dept t3,salarygrade t4
WHERE 
	t1.`job_id` = t2.`id` 
	AND t1.`dept_id` = t3.`id`
	AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;	

-- 5. 查询部门编号，部门名称，部门位置，部门人数
/*
分析：
	1. 部门编号、部门名称、部门位置 dept 表。 部门人数 emp表
	2. 使用分组查询。按照emp.dept_id完成分组，查询count(id)
	3. 使用子查询将第2步的查询结果和dept表进行关联查询
*/	
select
from
	dept t1,
	(select
		dept_id,count(id) total
	 from
	 	emp
	 group by 
	 	dept_id
	) t2
where
	t1.'id' = t2.dept_id;

4. MySQL中表的复制

-- 1. 把一张旧表中的数据，复制到一张新表中
create table 新表名 as select * from 旧表名;

-- 2. 只要想旧表的表头，不想要旧表的数据(不要表中的数据，需要写个条件为假的就行)
create table 新表名 as select * from 旧表名 where 1 = 2; 

-- 3. 复制一张表，想要个别字段
create table 新表名 as select 旧表名.字段名 from 旧表名 where 1 = 1;
wzr-xupt
关注
mysql笔记大全_MYSQL学习笔记整理
weixin_42463480的博客
01-28 1969
1、mysql -uroot -p 进入mysql，带权限2、建立一个数据库表(create)，删除一个数据库(drop)3、每一个sql语句后都要加；，可以用\g代替，可以用\G，会将数据逐条换行3、show databases； show tables；展示目录4、varchar 不定长度字符类型5、创建表：开始创建-输入字段名+字段类型+表名6、use 数据库名；进入数据库create ...
自整理! 最全mysql笔记
01-17
- 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择 - 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率 - 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果 - 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive
【史上最全MYSQL总结】MySQL超详细笔记
Willing卡卡的博客
12-22 6274
本文主要介绍了mysql是个啥、他的分类、SQL命令，MySQL的安装注意事项、分类以及在MySQL中常用的命令，包括：数据查询（基本查询、排序查询、条件查询、时间查询、字符串查询、聚合函数、分组查询、分组过滤、限定查询、子查询、合并查询、表连接查询）、DML操作（增删改）、库表操作、约束、事务、权限管理、视图等等；
关于MySQL的学习笔记（超详细超完整）
qq_46053369的博客
09-10 4142
SQL 1.什么是数据库? ​ 1)全称: DataBase 简称:DB ​ 2)数据库其实就是按照一定的格式进行存储数据的一些文件或者文件集合 ​ 理解:存储数据的仓库,而这些数据在实时存储的过程中是有特定格式的. 2.什么是数据库管理系统? ​ 1)全称: DataBaseManagement System 简称:DBMS 2).数据库管理系统专用来管理数据库中数据 可以对数据库中的数据进行增删改查操作 3)数据库管理系统类型: (1)关系型数据库 a.以行和列的方式进行存储 b.常见的关系型数据库:
数据库笔记整理
weixin_34366546的博客
11-06 199
参考文章： https://juejin.im/post/5a9ca0d6518825555c1d1acd Basic SQL Integrity Constraints: Referential Integrity Constraints: if a foreign key in Table 1 refers to the Primary Key of Table 2, then every v...
JavaWeb基础之MySQL约束.md
01-11
这是JavaWeb入门基础，数据库MySql基础的相关知识总结。之前自己学的和用的是SQL Server2008，对MySql掌握不够熟练，不过SQL语句都是差不多，所以上手比较容易。写博客就是为了方便自己复习查看，同时也将自己的学习...
JavaWeb员工工资管理系统包含MySQL数据库脚本文件
07-20
《JavaWeb员工工资管理系统与MySQL数据库的深度剖析》 在当今的企业管理中，员工工资管理系统是不可或缺的一部分，它能够高效、准确地处理员工的薪酬计算、发放等事务，减轻人力资源部门的工作负担。JavaWeb技术...
基于javaweb+servlet+mysql的学生信息管理系统
04-16
为了确保数据的一致性和完整性，可以使用主键和外键约束，例如，学生表的主键作为管理员表的外键，表示管理员可以管理多个学生。 **前端交互** 虽然题目未明确提及前端技术，但一个完整的Web应用通常会使用HTML、...
超市源码（javaweb项目,包含mysql文件）.zip
03-11
【标题】"超市源码（javaweb项目,包含mysql文件）.zip"是一个Java Web项目的源代码包，其中还包含了MySQL数据库的相关文件。这个项目可能是为了实现一个线上超市的模拟系统，允许用户浏览商品、添加到购物车、结算...
基于Javaweb+Tomcat+MySQL的大学生公寓管理系统.zip
最新发布
04-22
MySQL基于关系型数据库模型，数据以表格形式组织，并通过预定义的键（如主键、外键）在表之间建立关联。它完全支持结构化查询语言（SQL），允许用户进行数据查询、插入、更新、删除、创建和管理数据库结构等操作。...
MySQL数据库笔记
05-28
MySQL学习笔记，持续更新。
MySQL笔记汇总篇
weixin_44775434的博客
09-10 871
MySQL笔记汇总篇
超全MySQL学习笔记，416页满满知识，看完再也不怕学不会了
weixin_49889351的博客
07-07 474
全世界都在用MySQL，这句话毋庸置疑，数据库流行榜中揭示，MySQL排第二，仅次于Oracle，但排行榜上还有一个不容忽视的力量：MariaDB，如果把这支MySQL的独特力量与MySQL合并，MySQL将会超过Oracle，成为最流行的数据库。图源网络，侵删MySQL作为一款开源的数据库，相较于数据库应用更广泛，学习门槛也相对较低，成为了很多互联网爱好者和专业者必学的一门开发技术。MySQL学习并不难，难的是如何系统地学习，今天给大家分享一份超全MySQL学习笔记，416页27篇章，包括MySQL基础知
vs导入mysql.h_mysql的导入和导出
weixin_32520095的博客
02-22 374
mysql的导入和导出MySQL命令行导出数据库1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录如我输入的命令行：cd C:\Program Files\MySQL\MySQL Server 4.1\bin(或者直接将windows的环境变量path中添加该目录)2，导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名如我输入的命令行:...
Mysql 第一天
weixin_30237281的博客
11-18 149
数据库课程体系 在PHP阶段,将数据库分为三个阶段: 基础阶段(就业班第一个阶段): 6天, mysql数据库的基本操作(增删改查), 以及一些高级操作(视图, 触发器,函数,存储过程等), 和PHP操作mysql数据库 优化阶段(就业班后期): 如何提高数据库效率, 如索引, 分表等 部署阶段(最后): 如何搭建真实的环境系统, 如服务器集群, 负载均衡 数据库基础 什么是数据...
05_MySQL
依韵的博客
10-22 724
第一章 数据库概述 1.1 数据库概述 DBMS：数据库管理系统（Database Management System）是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。 DB：数据库（Database） SQL：结构化查询语言，（Structure Query Language），专门用来操作/访问数据库的通用语言。 MySQL：其中的一款关系型数据库管理系统 以下是2019年DB-...
深度干货 | 万字长文爆肝MySQL，超详细笔记整理
qq_45832461的博客
02-22 1526
万字长文详解MySQL基础，图文并茂，通俗易懂，超详细笔记整理！
2020最新超全MySQL笔记整理（面试题+思维导图+笔记）
weixin_48655626的博客
12-10 2561
作为一名编程人员，对MySQL一定不会陌生，尤其是互联网行业，对MySQL的使用是比较多的。对于求职者来说，MySQL又是面试中一定会问到的重点，很多人拥有大厂梦，却因为MySQL败下阵来。实际上，MySQL并不难，今天这份最全的MySQL总结，助你向大厂“开炮”，面试不再被MySQL难倒。面试加油！！！ 由于篇幅有限，以下只展示小部分面试题，有需要完整版的朋友可以点一点链接跳转领取：链接：点这里！！！暗号：CSDN 索引相关 1. 什么是索引? 索引是一种数据结构,可以帮助我们快速的进行数据的查.
学习MySQL的四万字 笔记总结【全面整理+详细解释】
探索技术与实践的旅程，分享编程经验与工具使用心得，助力开发者提升技能。
09-04 1675
文章目录1. 初始MySQL1.2 数据库分类1.3 MySQL 简介1.4 MySQL 安装1.5 MySQL(压缩包) 安装配置 1. 初始MySQL 程序员岗位必备技能：MySQL 数据库(DB,DataBase) 作用：存储数据，管理数据。 1.2 数据库分类 关系型数据库(SQL)：就像Excel表格一样，它是有行有列的。 Mysql, Oracle, Sql Server, DB2, SQLite 通过表和表之间，行和列之间的关系进行数据的存储。 非关系型数据库(Not Only SQL
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值