技术教程 编程教程 JavaEE Day03 MySQL约束 正文

JavaEE Day03 MySQL约束

2023-02-13,,,

MySQL约束--今日内容
1. SQL-（DDL,DML,DQL），讲完DQL查询语句的使用

排序查询
聚合函数
分组查询
分页查询

2. 约束
3. 多表之间的关系
4. 范式（用于更好地设计表，规范）
5. 数据库的备份和还原
一、DQL查询语句
1.DQL-排序查询
            语法：order by 子句
                order by 排序字段1，排序方式1，排序字段1，排序方式1……
            排序方式：
                        ASC：升序，默认
                        DESC：降序 

SELECT * FROM student ORDER BY math ASC;-- 升序

SELECT * FROM student ORDER BY math DESC;-- 降序

SELECT * FROM student ORDER BY math;-- 默认升序

-- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名（升序）

SELECT * FROM student ORDER BY math ASC, english ASC;-- 默认升序

只有第一条件相同时，才看第二条件
注意：如果有多个排序条件，则当前边的条件一样时，才会去判断第二条件
2.DQL-聚合函数：将一列数据作为一个整体，进行纵向的计算【列的纵向计算】
如：查看数学成绩的平均分

count：计算个数
一般选择非空的列--主键
count(*)
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值

注意：聚合函数的计算，会排除null值
解决方案：

选择不包含非空的列进行计算
使用IFNULL函数

-- 聚合函数

SELECT COUNT(ENGLISH) FROM STUDENT;-- 7，单行单列

-- 排除null不够合理，可以将null值换成0

SELECT COUNT(IFNULL(ENGLISH,0)) FROM STUDENT;-- 8，单行单列

SELECT COUNT(*) FROM STUDENT;-- 一般不允许写*，而是写主键

SELECT COUNT(ID) FROM STUDENT;

SELECT MAX(MATH) FROM STUDENT;

SELECT MAX(MATH) FROM STUDENT;

SELECT SUM(MATH) FROM STUDENT;

SELECT AVG(MATH) FROM STUDENT;

3.DQL-分组查询

    语法：group by 分组字段;
    注意

      分组之后查询的字段：分组字段/聚合函数
      WHERE和HAVING的区别 ？

        where在分组之前进行限定，如果不满足条件，则不参与分组
        having在分组之后进行限定，如果不满足结果，则不会被查询出来
        where后不能跟聚合函数，having可以进行聚合函数的判断
-- 分组查询之后，不能加其他的字段

-- 按照性别分组，分别查询男、女同学的平均分

SELECT SEX, AVG(MATH) FROM STUDENT GROUP BY SEX;-- 不能出现个人信息，只能出现整体

SELECT NAME, AVG(MATH) FROM STUDENT GROUP BY SEX;-- 查第一条，没有意义

SELECT SEX, AVG(MATH) ,COUNT(ID) FROM STUDENT GROUP BY SEX;-- 人数

-- 分数低于70的，不进行分组

-- 分组前，限定分组的条件

-- 按照性别分组，分别查询男、女同学的平均分，分数低于70分的人，不参与分组

SELECT SEX, AVG(MATH) ,COUNT(ID) FROM STUDENT WHERE MATH>70 GROUP BY SEX;-- 人数

-- 分组后再进行限定

-- 按照性别分组，分别查询男、女同学的平均分，分数低于70分的人，不参与分组；分组之后，人数要大于2个人

SELECT SEX, AVG(MATH) ,COUNT(ID) FROM STUDENT WHERE MATH>70 GROUP BY SEX HAVING COUNT(ID)>2;-- 人数

-- 换写法，起别名

SELECT SEX, AVG(MATH) ,COUNT(ID) 人数 FROM STUDENT WHERE MATH>70 GROUP BY SEX HAVING 人数>2;-- 人数，一般写英文

4. DQL-分页查询

    语法：limit 开始的索引, 每页查询的条数;
    公式：开始的索引=（当前的页码-1）*每页显示的条数
    分页操作的limit是一个MySQL的“方言”【Oracle用的是rownumber】
-- 分页查询

-- 百度为您找到约3000000条记录

SELECT * FROM student;

-- 共8条，每页显示3条

SELECT * FROM STUDENT LIMIT 0,3;-- 第一页的前三条

SELECT * FROM STUDENT LIMIT 3,3;-- 编号为4记录开始的索引，每页显示的数据【第二页】

SELECT * FROM STUDENT LIMIT 6,3;-- 【第三页】

-- 公式：开始的索引=（当前的页码-1）*每页显示的条数

-- 公式计算 第三页=（3-1）*3

二、约束
1.概述

    对表中的数据进行限定，保证数据的正确性、有效性和完整性
    分类

      主键约束：primary key
      非空约束：not null
      唯一约束：unique
      外键约束：foreign key

2.非空约束--not null：某一列的值不能为null

创建表时添加约束

-- 约束

-- 1.创建表添加非空约束

CREATE TABLE stu(

	id INT,

	NAME VARCHAR(20) NOT NULL -- NAME为非空

);

创建表完之后，添加非空约束
删除name的非空约束

-- 删除name的非空约束

ALTER TABLE STU MODIFY NAME VARCHAR(20);

-- 2.创建表之后，添加非空约束

-- 添加非空约束

ALTER TABLE STU MODIFY NAME VARCHAR(20) NOT NULL;

SELECT * FROM STU;

2.约束-唯一约束：unique，某一列的值不能重复

    注意

      唯一约束可以有NULL值，但是只能有一条记录为null

    创建表时，创建唯一约束

    -- 在创建表时创建唯一约束
    
CREATE TABLE STU(
    
	 id INT,
    
	 phone_number VARCHAR(20) UNIQUE -- 手机号
    
);
    
-- 在表创建完之后添加唯一约束
    
ALTER TABLE stu MODIFY phone_number  VARCHAR(20) UNIQUE;

    删除唯一约束

ALTER TABLE stu DROP INDEX phone_number;

3.约束--主键约束primary key

    注意

      含义：非空且唯一
      一张表只能有一个字段为主键
      主键就是表中记录的唯一标识

    创建表时，添加主键约束

    CREATE TABLE STU(
    
	 id INT PRIMARY KEY, -- 给id添加主键约束
    
	 NAME VARCHAR(20)
    
);
    
SELECT * FROM STU;

    创建完表后，添加主键

    -- 删除主键
    
ALTER TABLE STU MODIFY ID INT;-- 执行成功但不会生效
    
-- 正确方式
    
ALTER TABLE STU DROP PRIMARY KEY;-- 表中只有一个主键
    
-- 创建完表之后，添加主键
    
ALTER TABLE STU MODIFY ID INT PRIMARY KEY;

4.约束_主键约束_自动增长【一般都是和主键一起】

概念：如果某一列是数值类型的，使用auto_increment可以来完成值的自动增长
在创建表时添加主键约束，并且完成 主键自增长

CREATE TABLE STU(

	 id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束

	 NAME VARCHAR(20)

);

SELECT * FROM STU;

INSERT INTO STU VALUES(10,'CCC');

INSERT INTO STU VALUES(NULL,'CCC');

根据上一次的id值自动增长

删除自动增长

-- 删除自动增长

ALTER TABLE STU MODIFY ID INT;

INSERT INTO STU VALUES(12,'CCC');-- 可以

INSERT INTO STU VALUES(NULL,'CCC');-- 不可以

-- 添加自动增长

ALTER TABLE STU MODIFY ID INT AUTO_INCREMENT;

INSERT INTO STU VALUES(NULL,'CCC');-- 可以

5. 约束_外键约束 foreign key
让表与表产生关系，从而保证数据的正确性
背景

CREATE TABLE emp (

id INT PRIMARY KEY AUTO_INCREMENT,-- 号码

NAME VARCHAR(30),

age INT,-- 年龄

dep_name VARCHAR(30),-- 部门名称

dep_location VARCHAR(30)-- 部门地址

);

-- 添加数据

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

SELECT * FROM EMP;

重复的数据，即冗余，增改都很麻烦
解决：

-- 解决方案：分成 2 张表

-- 创建部门表(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, '销售部', '深圳');

SELECT * FROM department;

-- 添加员工,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);

SELECT * FROM employee;

删除数据导致数据不正确
解决方案 ：让department的id与 dep_id产生关联关系

在创建表时，可以添加外键
语法：
create table 表名(
……
外键列
constraint 外键名称 foreign key 外键列名称 reference 主表名称（主表的列名称）
）;

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, -- 外键对应主表的主键

CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)

);

-- 添加 2 个部门

INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');

SELECT * FROM department;

-- 添加员工,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);

SELECT * FROM employee;

DROP TABLE DEPARTMENT;

不能添加也不能删除

删除外键

-- 删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

创建表之后，添加外键

-- 添加外键

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

如果表中有约束的数据，则添加失败。

6.约束_外键约束_级联操作

级联操作（联动操作）
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) 

SELECT * FROM department;

SELECT * FROM employee;

-- 想把研发部id改为5

-- 修改employee表，将原先id为1的置为空

UPDATE employee SET dep_id=NULL WHERE dep_id=1;

SELECT * FROM employee;

UPDATE employee SET dep_id=5 WHERE dep_id IS NULL;

-- 级联操作，修改apartment，自动生效

级联更新ON UPDATE CASCADE;

-- 添加外键，设置级联更新

-- 删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 查看是否删掉 ，可以使用架构设计器/数据实际测试

-- 添加外键，并设置级联更新

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;

级联删除 ON DELETE CASCADE;

-- 级联删除

-- 删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 添加外键，并设置级联更新

ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

级联删除很危险，谨慎使用
三、数据库的设计
1.多表之间的关系

    一对一：

      如：人和身份证【一一对应】
      分析：一个人只有一个身份证，一个身份证只能对应一个人

    一对多/多对一：

      如：部门和员工
      分析 ：一个部门有多个员工，一个员工只能对应一个部门

    多对多：

      如：学生和课程
      分析：一个学生可以选择多门课程，一个课程也可以被多个学生选择

    下一节学习如何表示这些关系

2.实现关系

    一对多（多对一）

      图示：部门和员工

      设计方案：在员工表添加外键指向部门表的主键（在多的一方建立指向一的一方主键的外键）

    多表关系_多对多关系实现

      图示：学生和课程

      实现方式（借助中间表）
      第三张表的两个字段作为外键，分别指向另外两张表的主键
      这两个字段可以作为联合主键

    多表关系_一对一关系实现

      图示

      实现方式
      一个学生对应一个身份证，一个身份证对应一个学生
      可以在任意一方添加外键，指向对方的主键
      如果有第三条记录，外键也指向1，是合法的，但这样就不是一对一关系了
      解决方式：对外键加unique约束
      问题：一对一的关系，如何保证多个外键值不指向同一个数据。
      答案：对外键那一列添加unique约束【可以合并为一张表】

3.多表关系_案例
途牛旅游网站的实体

旅游线路分类
旅游线路
用户

数据库实现

-- 创建旅游线路分类表 tab_category

-- cid 旅游线路分类主键，自动增长

-- cname 旅游线路分类名称非空，唯一，字符串 100

CREATE TABLE tab_category (

 cid INT PRIMARY KEY AUTO_INCREMENT,

 cname VARCHAR(100) NOT NULL UNIQUE

);

-- 添加旅游线路分类数据：

INSERT INTO tab_category (cname) VALUES ('周边游'), ('出境游'), ('国内游'), ('港澳游');

SELECT * FROM tab_category;

-- 创建旅游线路表 tab_route

/*

rid 旅游线路主键，自动增长

rname 旅游线路名称非空，唯一，字符串 100

price 价格

rdate 上架时间，日期类型

cid 外键，所属分类

*/

CREATE TABLE tab_route(

 rid INT PRIMARY KEY AUTO_INCREMENT,

 rname VARCHAR(100) NOT NULL UNIQUE,

 price DOUBLE,

 rdate DATE,

 cid INT,

 FOREIGN KEY (cid) REFERENCES tab_category(cid)-- 简化的书写方式，省略了CONSTRAINT 外键名称

 -- 系统会自动分配外键名称

);

-- 添加旅游线路数据

INSERT INTO tab_route VALUES

(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,'2018-01-27', 1),

(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-22', 3),

(NULL, '【爆款￥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州往返 特价团】', 1699, '2018-01-27', 2),

(NULL, '【经典•狮航 ￥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',2),

(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);

SELECT * FROM tab_route;

用户表

-- 用户表

/*

创建用户表 tab_user

uid 用户主键，自增长

username 用户名长度 100，唯一，非空

password 密码长度 30，非空

name 真实姓名长度 100

birthday 生日

sex 性别，定长字符串 1

telephone 手机号，字符串 11

email 邮箱，字符串长度 100

*/

CREATE TABLE tab_user (

 uid INT PRIMARY KEY AUTO_INCREMENT,

 username VARCHAR(100) UNIQUE NOT NULL,

 PASSWORD VARCHAR(30) NOT NULL,

 NAME VARCHAR(100),

 birthday DATE,

 sex CHAR(1) DEFAULT '男',

 telephone VARCHAR(11),

 email VARCHAR(100)

);

-- 添加用户数据

INSERT INTO tab_user VALUES

(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),

(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');

SELECT * FROM tab_user;

/*

中间表：关联了用户表和线路表

创建收藏表 tab_favorite

rid 旅游线路 id，外键

date 收藏时间

uid 用户 id，外键

rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次

*/

CREATE TABLE tab_favorite (

 rid INT,

 DATE DATETIME,

 uid INT,

 -- 创建复合主键

 PRIMARY KEY(rid,uid),

 FOREIGN KEY (rid) REFERENCES tab_route(rid),

 FOREIGN KEY(uid) REFERENCES tab_user(uid)

);

-- 增加收藏表数据

INSERT INTO tab_favorite VALUES (1, '2018-01-01', 1), -- 老王选择厦门

(2, '2018-02-11', 1), -- 老王选择桂林

(3, '2018-03-21', 1), -- 老王选择泰国

(2, '2018-04-21', 2), -- 小王选择桂林

(3, '2018-05-08', 2), -- 小王选择泰国

(5, '2018-06-02', 2); -- 小王选择迪士尼

SELECT * FROM tab_favorite;

架构

四、数据库设计的范式
1、概述：设计数据库时，需要遵循的一些规范。
要遵循后面的范式要求，必须先遵循前边的所有范式要求
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
2. 分类

第一范式（1NF）：每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

3.三大范式详解
学生信息表

第一范式（1NF）：每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。
系这一列不是原子项，不满足1NF，应将其拆成两列

所有的数据库表都会满足1NF，无法创建复合

存在的问题

存在严重的数据冗余（重复）：姓名、系名、系主任
数据添加存在问题：添加新开设的系和系主任时，数据不合法
数据删除存在问题：张无忌同学毕业了，删除数据会将系的数据 一起删除
第二范式（2NF）：在1NF的基础上，非主属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）
几个概念
函数依赖：A--->B，如果通过A属性或者属性组的值可以确定唯一B属性的值，则称B依赖于A
                 例如：学号--->姓名，姓名依赖于学号。学号确定不了分数（分数 不被学号依赖）
                          学号+课程名称--->分数，分数依赖于学号和课程名称构成的属性组
完全函数依赖：A-->B，如果A是一个属性组，则B属性值需要依赖于A属性组中所有的属性值
例如：（学号，课程名称）--->分数
部分函数依赖：A-->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可
例如：（学号，课程名称）-->姓名
传递函数依赖：A-->B，B-->C，如果通过A属性或者属性组的值可以确定唯一B属性的值，再通过B属性的值可以唯一确定C属性的值，则称C传递依赖于A
例如：学号-->系名-->系主任
码：如果在一张表中，一个属性或属性组被其他所有属性完全依赖，则称这个属性或属性组为该表的码
例如：学生信息表中的学号，并未被其他所有属性完全依赖
           通过学号和课程名称，可以确定其他属性
           （学号，课程名称）为该表中的码
主属性：码属性组中的所有属性
非主属性：除去码属性组的属性
消除部分依赖，实现完全依赖：做表的拆分
（学号，课程名称）是主属性组/码
（学号）是主属性/码

仍存在问题：
.数据添加存在问题：添加新开设的系和系主任时，数据不合法
数据删除存在问题：张无忌同学毕业了，删除数据，会将系的数据一起删除。
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
是否有传递依赖：学号-->系名-->系主任
解决：

消除了传递依赖 ，消除了上述添加和删除问题
总结
1NF：原子项
2NF：消除非主属性的部分依赖
3NF：消除传递依赖

四、数据库的备份和还原
通常由DBA数据库管理员完成，防止硬件出问题产生的数据丢失现象
1.命令行

语法：
备份：mysqldum -u用户名 -p密码  （数据库名）> 保存的路径

还原
登录数据库
创建数据库
使用数据库

执行文件：source 文件路径

2.图形化工具

来自为知笔记(Wiz)

JavaEE Day03 MySQL约束的相关教程结束。

《JavaEE Day03 MySQL约束.doc》

下载本文的Word格式文档，以方便收藏与打印。

Copyright 2023. 昆居客 - IT编程,互联网信息技术文档大全!