Java Web学习day12------mysql（约束、外键、表关系、多表查询）

最新推荐文章于 2024-08-19 22:21:26 发布

挨踢农民工dsh

最新推荐文章于 2024-08-19 22:21:26 发布

阅读量163

点赞数

分类专栏： Java Web学习文章标签： mysql sql

本文链接：https://blog.csdn.net/dsh789/article/details/110518449

版权

Java Web学习专栏收录该内容

27 篇文章 1 订阅

订阅专栏

约束、外键、表关系、多表查询

单表约束
多表约束
- 外键约束
- 外键示例
多表关系
多表查询

单表约束

主键约束

primary key：默认是非空并且是唯一的
建表后单独添加主键约束
ALTER TABLE 表名 MODIFY 列名数据类型 PRIMARY KEY;
auto_increment：自动增长
建表后单独添加主键自增约束
ALTER TABLE 表名 MODIFY 列名数据类型 AUTO_INCREMENT;

唯一约束

建表后单独添加唯一约束
ALTER TABLE 表名 MODIFY 列名数据类型 UNIQUE;

非空约束

建表后单独添加非空约束
ALTER TABLE 表名 MODIFY 列名数据类型 NOT NULL;

单表示例

#创建一个数据库
CREATE DATABASE db11;
#使用这个数据库
USE db11;
#创建一个用户表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20) NOT NULL UNIQUE,
	PASSWORD VARCHAR(20) NOT NULL
);
#添加两个用户
INSERT INTO USER VALUES(NULL,'张三','123');
INSERT INTO USER VALUES(NULL,'李四','124');

多表约束

外键约束

建表后单独添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

外键示例

-- 创建部门表(id,dep_name,dep_location)
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);

-- 创建员工表(id,name,age,dep_id)
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);

-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

#在员工表中中的设置dep_id为外键,指向部门表的主键(id)
ALTER TABLE employee ADD CONSTRAINT fk_001 FOREIGN KEY(dep_id) REFERENCES department(id);

多表关系

一对一

建表原则
在任意一方添加外键指向另一方的主键，并且让外键唯一
数据库的优化----分表
不常用的属性分出来，放在另外一张表里面，这两张表就是一对一的关系

一对多

建表原则
在多的一方添加外键，指向一的一方的主键

多对多

建表原则
需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

约束示例

完成一个学校的选课系统，在选课系统中包含班级，学生和课程这些实体。
班级和学生之间是有关系存在：
一个班级下包含多个学生，一个学生只能属于某一个班级（一对多的关系）。
学生和课程之间是有关系存在：
一个学生可以选择多门课程，一门课程也可以被多个学生所选择（多对多的关系）。
班级表(class)
cid 主键
cname 班级名称
cnum 班级人数

学生表(student)
sid 主键
sname 学生姓名
sex 性别
birthday生日
cno 来自那个班级(外键指向班级的主键)

课程表(course)
cid 主键
cname 课程名称

学生选课表(中间表)(stu_cour)
scid 主键
sno 学生编号(外键指向学生的主键)
cno 课程编号(外键指向课程的主键)
score 所选的这门课的分数
在这里插入图片描述

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.27-log : Database - db15
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db15` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `db15`;

/*Table structure for table `classes` */

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) DEFAULT NULL,
  `cnum` int(11) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Data for the table `classes` */

insert  into `classes`(`cid`,`cname`,`cnum`) values (1,'01班',20),(2,'02班',30),(3,'03班',32),(4,'04班',41);

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`cid`,`cname`) values (1,'Java'),(2,'PHP'),(3,'C++');

/*Table structure for table `stu_cour` */

DROP TABLE IF EXISTS `stu_cour`;

CREATE TABLE `stu_cour` (
  `scid` int(11) NOT NULL AUTO_INCREMENT,
  `sno` int(11) DEFAULT NULL,
  `cno` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`scid`),
  KEY `FK_stu_cour_001` (`sno`),
  KEY `FK_stu_cour_002` (`cno`),
  CONSTRAINT `stu_cour_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sid`),
  CONSTRAINT `stu_cour_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

/*Data for the table `stu_cour` */

insert  into `stu_cour`(`scid`,`sno`,`cno`,`score`) values (1,1,1,85),(2,1,3,72),(3,2,2,82),
	(4,2,3,65),(5,3,1,71),(6,3,2,75),
	(7,3,3,68),(8,4,1,72),(9,4,2,64),
	(10,5,2,91),(11,5,3,82),(12,6,1,74),
	(13,6,2,48),(14,7,2,73),(15,7,3,72),
	(16,8,1,65),(17,8,2,80),(18,9,1,81),
	(19,9,2,91),(20,9,3,78);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) DEFAULT NULL,
  `sex` varchar(10) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `cno` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_student_001` (`cno`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `classes` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`sid`,`sname`,`sex`,`birthday`,`cno`) values (1,'张三','男','1990-09-01',1),
	(2,'李四','女','1991-02-13',1),(3,'王五','男','1990-03-12',1),(4,'赵六','男','1992-02-12',2),
	(5,'田七','男','1994-05-21',2),(6,'张五','女','1990-06-17',2),(7,'张老七','女','1990-04-12',3),
	(8,'王老四','女','1990-07-16',3),(9,'李六','男','1990-09-12',NULL);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

多表查询

交叉查询

#交叉查询 -- 查询的是两张表的所有组合情况
SELECT * FROM department,employee;  # 是所有查询的基础

内连接查询

内连接查询：查的是两张表的交集

#隐式内连接(没有使用inner join关键字)
SELECT * FROM department,employee WHERE employee.`dep_id` = department.`id`
#显式内连接(使用inner join关键字)
SELECT * FROM department INNER JOIN employee ON employee.`dep_id` = department.`id`

外连接查询

#左外连接：左边表的全部和两张表的交集
SELECT * FROM department LEFT OUTER JOIN employee ON employee.`dep_id` = department.`id`
#右外连接：右边表的全部和两张表的交集
SELECT * FROM department RIGHT OUTER JOIN employee ON employee.`dep_id` = department.`id`

子查询

子查询：sql语句嵌套sql语句

#结果是单行单列(结果只有一个),子查询可以作为条件,用运算符去判断
#年龄最大的员工的信息
SELECT MAX(age) FROM employee;
SELECT * FROM employee WHERE age = (SELECT MAX(age) FROM employee);

#结果是多行单列,子查询可以作为条件,用运算符in来连接
#查询"研发部"和"学工部"所有的员工信息
#1,查询研发部和学工部的部门id
SELECT id FROM department WHERE dep_name = '研发部' OR  dep_name = '学工部';
#2,查询员工表中员工的id在上面的查到的id之中
SELECT * FROM employee WHERE dep_id IN (SELECT id FROM department WHERE dep_name = '研发部' OR  dep_name = '学工部')

#结果是多行多列,子查询可以作为一张虚拟的表来参与查询
#查询员工年龄大于20岁的员工信息和部门信息

#方式一:子查询
#1,查询员工年龄大于20岁的员工信息
SELECT * FROM employee WHERE age > 20;
#2,将第一步查询的结果作为一张虚拟的表 和 部门表一起进行查询
SELECT * FROM department t1,(SELECT * FROM employee WHERE age > 20) t2 WHERE t1.`id`=t2.dep_id

#方式二:普通的内连接查询
SELECT * FROM department,employee WHERE department.`id`=employee.`dep_id` AND employee.`age` > 20

自关联查询

其实就是将一张表一分为二，起不同的别名，用条件关联起来

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询
/*
分析
	员工信息 employee表
	条件：employee.mgr = employee.id
	查询左表的全部数据，和左右两张表有交集部分数据，左外连接
*/
SELECT
	e1.id,
	e1.name,
	e1.mgr,
	e2.id,
	e2.name
FROM
	employee e1
LEFT OUTER JOIN
	employee e2
ON
	e1.mgr = e2.id;