Java小农养成记第二十六天

day26

今日内容

1. DQL查询语句
	1. 排序查询
	2. 聚合函数
	3. 分组查询
	4. 分页查询
2. 约束
3. 多表之间的关系
4. 范式
5. 数据库的备份和还原
#创建一张表;
USE db1;
CREATE TABLE student(
	id INT,
	NAME VARCHAR(32),
	age INT,
	sex VARCHAR(10),
	address VARCHAR(50),
	math DOUBLE(4, 1),
	english DOUBLE(4, 1)
);

INSERT INTO student VALUES
(1, "马云", 55, "男", "杭州", 66, 78),
(2, "马化腾", 45, "女", "深圳", 98, 87),
(3, "马景涛", 55, "男", "香港", 56, 77),
(4, "柳岩", 20, "女", "湖南", 76, 65),
(6, "刘德华", 57, "男", "香港", 99, 99),
(7, "马德", 22, "女", "香港", 99, 99),
(8, "德玛西亚", 18, "男", "南京", 56, 65);

DQL查询语句

排序查询

	* 语法:order by 子句
		order by 排序字段1 排序方式1, 排序字段2 排序方式2,……
	* 排序方式:
		ASC:升序,默认的
		DESC:降序
	* 注意:
		如果有多个排序条件,则当前面的条件值一样时,才会判断第二条件
SELECT * FROM student ORDER BY math; -- 默认是升序 ASC

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

-- 按照数学成绩排名,如果数学成绩一样,则按照英语成绩排名

SELECT * FROM student ORDER BY math ASC ,english ASC;

聚合函数

聚合函数:将一列数据作为一个整体,进行纵向的计算
	1. count:计算个数
		1. 一般选择非空的列:主键
		2. count(*):如果一行中有一个值不是NULL,则算一条记录
	2. max:计算最大值
	3. min: 计算最小值
	4. sum:计算和
	5. avg:计算平均值
	* 注意:聚合函数的计算,排除了NULL值
		解决方案:
	       	1. 选择不包含非空的列进行计算
	       	2. IFNULL函数
SELECT COUNT(NAME) FROM student;

SELECT COUNT(IFNULL(english,0)) FROM student; -- 如果English列中有NULL的值则替换为0

SELECT COUNT(*) FROM student; -- 如果一行中有一个值不是NULL,则算一条记录

SELECT MAX(math) FROM student; -- 最大值

SELECT MIN(math) FROM student; -- 最小值

SELECT SUM(math) FROM student; -- 求和

SELECT SUM(english) FROM student; -- 排除了NULL值

SELECT AVG(math) FROM student; -- 求平均值

SELECT AVG(english) FROM student; -- 排除了NULL值

分组查询

	1. 语法:group by 分组字段;
	2. 注意:
		1. 分组之后查询的字段:分组字段、聚合函数
		2. where和having的区别?
			1. where在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来。
			2. where后不可以进行聚合函数的判断,having可以进行聚合函数的判断。
-- 按照性别分组,分别查询男、女同学的平均分

SELECT	sex, AVG(math) FROM student GROUP BY sex;

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

SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;

-- 按照性别分组,分别查询男、女同学的平均分、人数 要求:分数低于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;

分页查询

	1. 语法:limit 开始的索引,每页查询的条数;
	2. 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数
	3. limit是一个MySQL“方言”
-- 每页显示3条记录

SELECT * FROM student LIMIT 0, 3; -- 第一页

SELECT * FROM student LIMIT 3, 3; -- 第二页

-- 公式:开始的索引 = (当前的页码 - 1) * 每页显示的条数

SELECT * FROM student LIMIT 6, 3; -- 第三页

约束

  • 概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。

  • 分类:

主键约束:PRIMARY KEY

NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。一张表只能有一个主键

-- 创建表时,添加主键约束

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

-- 删除主键
-- alter table stu modify id int;这样删除不了主键
ALTER TABLE stu DROP PRIMARY KEY;

-- 创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;

自动增长:
概念∶如果某一列是数值类型的,使用auto_increment可以来完成值得自动增长
注意 : 是以上一条记录的为标准,如果上一条为10,那么这条为11

-- 创建表时,添加主键约束,并且完成主键自增长

CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);

-- 删除自动增长

ALTER TABLE stu MODIFY id INT;

-- 添加自动增长

ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

非空约束:NOT NULL

指示某列不能存储 NULL 值。

-- 创建表添加非空约束

CREATE TABLE stu(
	id INT,
	NAME VARCHAR(20) NOT NULL -- name为非空
);

-- 删除name的非空约束

ALTER TABLE stu MODIFY NAME VARCHAR(20);

-- 创建表完后,添加非空约束

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

唯一约束:UNIQUE

保证某列的每行必须有唯一的值。

-- 创建表时,添加唯一约束

CREATE TABLE stu(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);

-- 注意mysql中,唯一约束限定的列值可以有多个null

-- 删除唯一约束
-- alter table stu modify phone_number varchar(20);这样删除不了唯一约束
ALTER TABLE stu DROP INDEX phone_number;

-- 在创建表后,添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

外键约束:FOREIGN KEY

让表与表之间产生关系,保证一个表中的数据匹配另一个表中的值的参照完整性。

语法:
	-- 在创建表时,可以添加外键
	create table 表名(
		.....
		外键列,
		constraint 外键名称 foreign key (外键列名称) references 主表名(主表列名称)
	);
	-- 删除外键

	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

	-- 添加外键

	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表列名称);
CREATE TABLE emp( -- 创建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, -- 外键对应主表的主键
	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;

-- 删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;

-- 添加外键

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

级联操作
添加级联操作
语法:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称 (主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
分类:

  1. 级联更新:ON UPDATE CASCADE
    department表中id为1更改为id为5,在employee表中的对应dep_id为1的也会级联更新为5
  2. 级联删除: ON DELETE CASCADE
    department表中id为1删除,在employee表中的对应dep_id为1的也会级联删除
-- 添加外键,设置级联更新,设置级联删除

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

数据库的设计

多表之间的关系

分类:

  1. 一对一:
  如 :人和身份证
  	分析:一个人只有一个身份证,一个身份证只能对应一个人
  1. 一对多(多对一):
  如 :部门和员工
  	分析:一个部门有多个员工,一个员工只能对应一个部门
  1. 多对多:
  如 :学生和课程
  	分析:一个学生可以选择很多门课程,一个课程也可以被很多学生选择

实现关系:

  1. 一对一
  如:学生和身份证
  	实现方式:一对―关系实现,可以在任意―方添加唯―外键指向另一方的主键。
  1. 一对多(多对一)
  如:部门和员工
  	实现方式:在多的一方建立外键,指向一的一方主键(员工->部门)
  1. 多对多
  如:学生和课程
  	实现方式:多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键

多表关系_案例

在这里插入图片描述

-- 创建旅游线路分类,tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串100
CREATE TABLE tab_category(
	cid INT PRIMARY KEY AUTO_INCREMENT,
	cname VARCHAR(100) NOT NULL UNIQUE
);

-- 创建旅游线路表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)
);

-- 创建用户表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)
);

/*
创建收藏表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)
);

数据库设计的范式

概念:

设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小。

关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

基础概念:

实体:现实世界中客观存在并可以被区别的事物。比如“一个学生”、“一本书”、“一门课”等等。值得强调的是这里所说的“事物”不仅仅是看得见摸得着的“东西”,它也可以是虚拟的,不如说“老师与学校的关系”。

属性:教科书上解释为:“实体所具有的某一特性”,由此可见,属性一开始是个逻辑概念,比如说,“性别”是“人”的一个属性。在关系数据库中,属性又是个物理概念,属性可以看作是“表的一列”。

元组:表中的一行就是一个元组。

分量:元组的某个属性值。在一个关系数据库中,它是一个操作原子,即关系数据库在做任何操作的时候,属性是“不可分的”。否则就不是关系数据库了。

码:表中可以唯一确定一个元组的某个属性(或者属性组),如果这样的码有不止一个,那么大家都叫候选码,我们从候选码中挑一个出来做老大,它就叫主码。

全码:如果一个码包含了所有的属性,这个码就是全码。

主属性:一个属性只要在任何一个候选码中出现过,这个属性就是主属性。

非主属性:与上面相反,没有在任何候选码中出现过,这个属性就是非主属性。

外码:一个属性(或属性组),它不是码,但是它别的表的码,它就是外码。

函数依赖: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
例如:学号–>系名,系名–>系主任

各类范式:
1、第一范式(1NF):属性不可分。
所谓第一范式(1NF)是指在关系模型中,对于添加的一个规范要求,所有的域都应该是原子性的,即
数据库表的每一列都是不可分割的原子数据项
,而不能是集合,数组,记录等非原子数据项。即实体中的某个属性有多个值时,必须拆分为不同的属性。在符合第一范式(1NF)表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之,第一范式就是无重复的域。
说明:在任何一个关系数据库中,第一范式(1NF)是对关系模式的设计基本要求,一般设计中都必须满足第一范式(1NF)。不过有些关系模型中突破了1NF的限制,这种称为非1NF的关系模型。换句话说,是否必须满足1NF的最低要求,主要依赖于所使用的关系模型。
**2、第二范式(2NF):**符合1NF,并且,非主属性完全依赖于码。
在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组,作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分,该身份证号码即为候选键,任何一个候选键都可以被选作主键。在找不到候选键时,可额外增加属性以实现区分,如果在员工关系中,没有对其身份证号进行存储,而姓名可能会在数据库运行的某个时间重复,无法区分出实体时,设计辟如ID等不重复的编号以实现区分,被添加的编号或ID选作主键。(该主键的添加是在ER设计时添加,不是建库时随意添加)
第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。简而言之,第二范式就是在第一范式的基础上属性完全依赖于主键。
**3、第三范式(3NF):**符合2NF,并且,消除传递依赖
在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)
第三范式(3NF)是第二范式(2NF)的一个子集,即满足第三范式(3NF)必须满足第二范式(2NF)。简而言之,第三范式(3NF)要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性,也就是在满足2NF的基础上,任何非主属性不得传递依赖于主属性。
4、巴斯-科德范式(BCNF):
Boyce-Codd Normal Form(巴斯-科德范式)
在3NF基础上,任何非主属性不能对主键子集依赖(在3NF基础上消除对主码子集的依赖)
通常BC范式的条件有多种等价的表述:每个非平凡依赖的左边必须包含键码;每个决定因素必须包含键码。BC范式既检查非主属性,又检查主属性。当只检查非主属性时,就成了第三范式。满足BC范式的关系都必然满足第三范式。还可以这么说:若一个关系达到了第三范式,并且它只有一个候选码,或者它的每个候选码都是单属性,则该关系自然达到BC范式。
巴斯-科德范式(BCNF)是第三范式(3NF)的一个子集,即满足巴斯-科德范式(BCNF)必须满足第三范式(3NF)。通常情况下,巴斯-科德范式被认为没有新的设计规范加入,只是对第二范式与第三范式中设计规范要求更强,因而被认为是修正第三范式,也就是说,它事实上是对第三范式的修正,使数据库冗余度更小。这也是BCNF不被称为第四范式的原因。某些书上,根据范式要求的递增性将其称之为第四范式是不规范,也是更让人不容易理解的地方。而真正的第四范式,则是在设计规范中添加了对多值及依赖的要求。

数据库的备份和还原

1.命令行:

语法:
	备份:mysqldump -u用户名 -p密码 备份数据名称 > 保存的路径
	还原:
		1.登录数据库
		2.创建数据库
		3.使用数据库
		4.执行文件。source 文件路径

2.图形化工具:

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值