JavaWeb第2篇数据库基础

三大范式

第一范式

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式

第二范式

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。如果一个关系属于第二范式,并且在两个(或多个)非主键属性之间不存在函数依赖。(非主键属性之间的函数依赖也称为传递依赖),那么这个关系属于第三范式。

BCNF范式
任何非主属性不能对主键子集依赖（即在3NF基础上，消除主属性对候选码的部分函数依赖和传递函数依赖）。BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的关系都必然满足第三范式。

数据类型

字符串存储：

CHAR(n)可以存储任意字符串，但是是固定长度为n，如果插入的长度小于定义长度时，则用空格填充
VARCHAR(n)也可以存储任意数量字符串，长度不固定，但不能超过n，不会用空格填充

存储数字：

SMALLINT用于存储小的整数，范围在 (-32768，32767)
INT用于存储一般的整数，范围在 (-2147483648，2147483647)
BIGINT用于存储大型整数，范围在 (-9,223,372,036,854,775,808，9,223,372,036,854,775,807)
FLOAT用于存储单精度小数
DOUBLE用于存储双精度的小数

存储时间：

DATE存储日期
TIME存储时间
YEAR存储年份
DATATIME用于混合存储日期+时间

约束条件

列级约束

主键 PRIMARY KEY、外键FOREIGN KEY

唯一 UNIQUE、检查 CHECK （ MySQL不支持）

默认 DEFAULT 、非空 /空值 NOT NULL / NULL

表级约束

表级约束有四种：主键、外键、唯一、检查

DDL

数据库模式定义语言DDL(Data Definition Language)，是用于描述数据库中要存储的现实世界实体的语言

定义数据库

定义数据库使用CREATE语句后指定为DATABASE，编码中需要设置为utf8，字符集设置为utf8_general_ci否则一些特殊字符会出问题

CREATE DATABASE 数据库名;
-- 设置编码
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
-- 删除数据库
DROP DATABASE 数据库名;

操作表（创建）

格式规范

CREATE TABLE 表名(列名 数据类型[列级约束条件],
             列名 数据类型[列级约束条件],
             ...
             [,表级约束条件])

使用示例

-- 创建学校表
CREATE TABLE IF NOT EXISTS `school` (
		`id` BIGINT ( 20 ) UNSIGNED NOT NULL auto_increment COMMENT '主键' PRIMARY KEY,
		`school_type` TINYINT ( 4 ) NULL COMMENT '学校类型ID',
		`school_code` INT ( 11 ) NULL COMMENT '学校编码',
		`school_name` VARCHAR ( 64 ) NULL COMMENT '学校名称',
	  `area_code` INT ( 11 ) NULL COMMENT '地区代码' 
) COMMENT '学校';

操作表（修改）

格式规范

1
2
3

ALTER TABLE 表名[ADD 新列名 数据类型[列级约束条件]]
							 [DROP COLUMN 列名[restrict|cascade]]
							 [ALTER COLUMN 列名 新数据类型]

使用示例

-- 添加列
ALTER TABLE school ADD school_city  VARCHAR ( 64 ) NULL COMMENT '学校城市';
-- 方式二
ALTER TABLE school ADD COLUMN school_website VARCHAR ( 64 ) NULL COMMENT '学校官网';

-- 修改列(存在方式二)
ALTER TABLE school CHANGE school_website school_site VARCHAR(64) NOT NULL COMMENT '学校官网';

-- 删除列(存在方式二)
ALTER TABLE school DROP school_site;

-- 删除表
ALTER TABLE school;

DML

是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

插入

格式规范

-- 数据与列一一对应
INSERT INTO 表名 VALUES(值1, 值2, 值3)

-- 指定列
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)

-- 插入多条
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)

使用示例

-- 数据与列一一对应
INSERT INTO school VALUES(1, 1, 1,'南京大学',1);

-- 指定列
INSERT INTO school(school_type, school_name) VALUES(2,'东南大学');

-- 插入多行
INSERT INTO school VALUES(3, 3, 3,'南京航空航天大学',3), (4, 4, 4,'南京理工大学',4);

修改

格式规范

1	`UPDATE 表名 SET 列名=值,... WHERE 条件`

警告：如果忘记添加WHERE字句来限定条件，将使得整个表中此列的所有数据都被修改

使用示例

1 2	`-- 修改示例 UPDATE school SET school_code=2 WHERE id=2;`

删除

格式规范

-- 清空表
DELETE FROM 表名

-- 删除指定
DELETE FROM 表名 WHERE 条件

使用示例

-- 清空表
DELETE FROM school

-- 删除数据
DELETE FROM school WHERE id=4;

DQL

数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块

查询条件

一般的比较运算符，包括=、>、<、>=、<=、!=等。
是否在集合中：in、not in
字符模糊匹配：like，not like
多重条件连接查询：and、or、not

单表查询

格式规范

-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名

-- 会以别名显示此列
SELECT 列名 别名 FROM 表名

-- 查询所有的列数据
SELECT * FROM 表名

-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名

使用示例

-- 指定查询某一列数据
SELECT school_code,school_name FROM school;

-- 会以别名显示此列
SELECT school_code '编码', school_name '校名' FROM school;

-- 查询所有的列数据
SELECT * FROM school

-- 只查询不重复的值（单列）
SELECT DISTINCT school_type FROM school;

-- 只查询不重复的值（多列）
SELECT school_type,school_name FROM `school` GROUP BY `school_type`;

排序查询

格式规范

-- ASC升序，DESC降序
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC

-- 多个条件（按列来）
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC

使用示例

-- ASC升序，DESC降序
SELECT school_type,school_name FROM school ORDER BY school_type DESC

-- 多个条件（按列来）
SELECT * FROM school ORDER BY school_type DESC, school_type DESC

聚合函数

常用聚合函数

count([distinct]*)统计所有的行数（distinct表示去重再统计，下同）
count([distinct]列名)统计某列的值总和
sum([distinct]列名)求一列的和（注意必须是数字类型的）
avg([distinct]列名)求一列的平均值（注意必须是数字类型）
max([distinct]列名)求一列的最大值
min([distinct]列名)求一列的最小值

格式规范

1	`SELECT count(distinct 列名) FROM 表名 WHERE 条件`

使用示例

1	`SELECT count(school_type) '属性共有' FROM school`

分组分页

格式规范

-- 分组
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名

-- 限制分组条件
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

-- 限制查询数量
SELECT * FROM 表名 LIMIT 数量

-- 分页
SELECT * FROM 表名 LIMIT 起始位置,数量

分组查询

数据准备

drop table if exists t_order;

-- 创建订单表
create table t_order(
  id int not null AUTO_INCREMENT COMMENT '订单id',
  user_id bigint not null comment '下单人id',
  user_name varchar(16) not null default '' comment '用户名',
  price decimal(10,2) not null default 0 comment '订单金额',
  the_year SMALLINT not null comment '订单创建年份',
  PRIMARY KEY (id)
) comment '订单表';

-- 插入数据
insert into t_order(user_id,user_name,price,the_year) values
  (1001,'周润发',11.11,'2017'),
  (1001,'周润发',22.22,'2018'),
  (1001,'周润发',88.88,'2018'),
  (1002,'刘德华',33.33,'2018'),
  (1002,'刘德华',12.22,'2018'),
  (1002,'刘德华',16.66,'2018'),
  (1002,'刘德华',44.44,'2019'),
  (1003,'张学友',55.55,'2018'),
  (1003,'张学友',66.66,'2019');

查询每个用户下单数量

1	`SELECT user_id '用户ID' , user_name '用户名', COUNT(id) '下单数量' FROM t_order GROUP BY user_id;`

查询每个用户每年下单数量

1	`SELECT user_id '用户ID' , user_name '用户名', the_year '年份',COUNT(id) '下单数量' FROM t_order GROUP BY user_id,the_year;`

需要查询2018年每个用户下单数量

-- 分组前筛选数据 
SELECT user_id '用户ID' , user_name '用户名', COUNT(id) '下单数量' FROM t_order WHERE the_year=2018 GROUP BY user_id;

-- 分组后筛选数据（查询2018年订单数量大于1的用户）
SELECT user_id '用户ID' , user_name '用户名', COUNT(id) '下单数量' FROM t_order WHERE the_year=2018 GROUP BY user_id HAVING count(id)>=2;

获取每个用户最大金额

1	`SELECT user_id 用户id, max(price) 最大金额 FROM t_order GROUP BY user_id ORDER BY 最大金额 desc;`

顺序限制
where、group by、having、order by、limit这些关键字一起使用时，先后顺序有明确的限制，语法如下

select 列 from 
表名
where [查询条件]
group by [分组表达式]
having [分组过滤条件]
order by [排序条件]
limit [offset,] count;

获取每个用户下单的最大金额及下单的年份

1
2
3

SELECT user_id '用户id', price '最大金额', the_year '年份' FROM t_order t1, 
(SELECT t.user_id uid, MAX(t.price) pc FROM t_order t GROUP BY t.user_id) t2
WHERE t1.user_id = t2.uid AND  t1.price = t2.pc;

分页查询

LIMIT和OFFSET应该设定的值：

LIMIT总是设定为pageSize
OFFSET计算公式为pageSize * (pageIndex - 1)

1 2	`-- 查询第三页数据 SELECT school_code '编码', school_name '校名' FROM school LIMIT 10 OFFSET 20;`

多表查询

一次查询两个表的数据，查询的结果也是一个二维表，它是department表和employee表的“乘积”，即students表的每一行与employee表的每一行都两两拼在一起返回。结果集的列数是department表和employee表的列数之和，行数是departments表和classes表的行数之积。

这种多表查询又称笛卡尔查询，使用笛卡尔查询时要非常小心，由于结果集是目标表的行数乘积，对两个各自有100行记录的表进行笛卡尔查询将返回1万条记录，对两个各自有1万行记录的表进行笛卡尔查询将返回1亿条记录。

数据准备

#建表
CREATE TABLE department ( id INT, name VARCHAR ( 20 ) );
CREATE TABLE employee (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR ( 20 ),
	sex enum ( 'male', 'female' ) NOT NULL DEFAULT 'male',
	age INT,
	dep_id INT 
);#插入数据
INSERT INTO department
VALUES
	( 200, '技术' ),
	( 201, '人力资源' ),
	( 202, '销售' ),
	( 203, '运营' );
INSERT INTO employee ( NAME, sex, age, dep_id )
VALUES
	( 'egon', 'male', 18, 200 ),
	( 'alex', 'female', 48, 201 ),
	( 'wupeiqi', 'male', 38, 201 ),
	( 'yuanhao', 'female', 28, 202 ),
	( 'liwenzhou', 'male', 18, 200 ),
	( 'jingliyang', 'female', 18, 204 );

使用示例

SELECT
    e.id eid,
    e.name,
    e.sex,
    e.age,
    d.id did,
    d.name dname
FROM employee e, department d
WHERE d.id = 200;

FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>

连接查询

连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。INNER JOIN是选出两张表都存在的记录。LEFT OUTER JOIN是选出左表存在的记录。RIGHT OUTER JOIN是选出右表存在的记录。FULL OUTER JOIN则是选出左右表都存在的记录

内连接
1.先确定主表，仍然使用FROM <表1>的语法
2.再确定需要连接的表，使用INNER JOIN <表2>的语法
3.然后确定连接条件，使用ON <条件…>，这里的条件是s.class_id = c.id，表示students表的class_id列与classes表的id列相同的行需要连接
4.可选：加上WHERE子句、ORDER BY等子句

使用示例

SELECT  e.id,
    e.name,
    e.sex,
		e.dep_id,
		d.name d_name,
    e.age
FROM employee e
INNER JOIN department d
ON e.dep_id = d.id;

外连接
1.RIGHT OUTER JOIN返回右表都存在的行。如果某一行仅在右表存在，那么结果集就会以NULL填充剩下的字段
2.LEFT OUTER JOIN则返回左表都存在的行。如果某一行仅在左表存在，那么结果集就会以NULL填充剩下的字段
3.FULL OUTER JOIN，它会把两张表的所有记录全部选择出来，并且，自动把对方不存在的列填充为NULL
使用示例

SELECT  e.id,
    e.name,
    e.sex,
		e.dep_id,
		d.name d_name,
    e.age
FROM employee e
RIGHT OUTER JOIN department d
ON e.dep_id = d.id;

嵌套查询

子查询常用在 WHERE 子句和 FROM 子句后边。当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询

使用示例

-- 查询年龄比yuanhao大的
SELECT * FROM employee WHERE age > (SELECT age
FROM employee WHERE NAME='yuanhao');

-- 查询年龄比yuanhao大并且年龄大于40的
SELECT * FROM employee WHERE age > (SELECT age
FROM employee WHERE NAME='yuanhao') AND age > 40;

-- 查询所有技术部男性
SELECT * FROM employee WHERE id IN (SELECT id
FROM employee WHERE sex='male') AND dep_id = 200;

DCL

主要用来创建用户，分配用户权限、决定该用户能否被远程访问等等

使用示例

-- 创建了一个名为：admin 密码为：123 的用户
create user 'admin'@'localhost' identified by '123';
/* 
此处的"localhost"，是指该用户只能在本地登录，不能在另外一台机器上远程登录。如果想远程登录的话，将"localhost"改为"%"，表示在任何一台电脑上都可以登录。也可以指定某台机器可以远程登录。
*/

-- 删除用户“admin”
drop user admin@localhost;

-- 若创建的用户允许任何电脑登陆，删除用户如下
drop user admin@'%'

-- 修改用户“admin”的密码为“1234”
alter user 'admin'@'localhost' identified with mysql_native_password by '1234';
-- 刷新
flush privileges;

-- 授予用户admin通过外网IP对数据库“user_db”的全部权限
grant all privileges on user_db.* to 'admin'@'%';
--刷新权限
flush privileges; 

-- 授予用户“admin”通过外网IP对于该数据库“user_db”中表的创建、修改、删除权限,以及表数据的增删查改权限
grant create,alter,drop,select,insert,update,delete on admin.* to user_db@'%';

-- 查看用户“admin”权限
show grants for admin;

视图

视图本质就是一个查询的结果，不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果，那么它本身就是一个虚表，并不是真实存在的，数据实际上还是存放在原来的表中

基本语法

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

-- 常用参数
create view <视图名称> [(column_list)]
as select语句
with check option;

1）OR REPLACE：表示替换已有视图，如果该视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同
2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法；merge合并；temptable临时表，一般该参数不显式指定
3）DEFINER：指出谁是视图的创建者或定义者，如果不指定该选项，则创建视图的用户就是定义者
4）SQL SECURITY：SQL安全性，默认为DEFINER
5）select_statement：表示select语句，可以从基表或其他视图中进行选择
6）WITH CHECK OPTION：表示视图在更新时保证约束，默认是CASCADED

使用示例

CREATE VIEW v_dep(编号,姓名,性别,部门,年龄) AS
SELECT  e.id,
    e.name,
    e.sex,
		d.name d_name,
    e.age
FROM employee e
INNER JOIN department d
ON e.dep_id = d.id
WITH CHECK OPTION;

索引

在数据量变得非常庞大时，通过创建索引，能够大大提高我们的查询效率，就像Hash表一样，它能够快速地定位元素存放的位置。虽然添加索引后会使得查询效率更高，但是我们不能过度使用索引，索引为我们带来高速查询效率的同时，也会在数据更新时产生额外建立索引的开销，同时也会占用磁盘资源。

基本语法

-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)

-- 查看表中的索引
show INDEX FROM 表名

-- 删除索引
drop index 索引名称 on 表名

单值索引（单列索引）

单值索引即一个索引只包含单个列，一个表中可以有多个单列索引；语法如下

# 随表一起建立的索引 索引名同 列名(customer_name)
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name)  
);

# 单独建单值索引：
CREATE INDEX idx_customer_name ON customer(customer_name); 
# 删除索引：
DROP INDEX idx_customer_name ;

主键索引

# 随表一起建索引：
# 使用AUTO_INCREMENT关键字的列必须有索引(只要有索引就行)。
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id) 
);

CREATE TABLE customer2 (
    id INT(10) UNSIGNED,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id) 
);

# 单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
# 删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY ;  
# 修改建主键索引：
# 必须先删除掉(drop)原索引，再新建(add)索引

唯一索引

索引列的值必须唯一，但允许有空值，语法如下

# 随表一起建索引：
#建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错：
CREATE TABLE customer (
    id INT(10) UNSIGNED  AUTO_INCREMENT,
    customer_no VARCHAR(200),
    customer_name VARCHAR(200),
    PRIMARY KEY(id),
    KEY (customer_name),
    UNIQUE (customer_no)
);

# 单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

# 删除索引：
DROP INDEX idx_customer_no on customer ;

触发器

在某种条件下会自动触发，在select/update/delete时，会自动执行我们预先设定的内容，触发器通常用于检查内容的安全性，相比直接添加约束，触发器显得更加灵活

触发器所依附的表称为基本表，当触发器表上发生select/update/delete等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）

比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中

基本语法

1
2
3

CREATE TRIGGER 触发器名称 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno

-- FOR EACH ROW表示针对每一行都会生效，无论哪行进行指定操作都会执行触发器

事务

这种把多条语句作为一个整体进行操作的功能，被称为数据库事务。数据库事务可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

事务的特性:

Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

隔离级别:

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

基本语法

begin;   #开始事务
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
commit; #提交事务
-- 一旦提交，就无法再进行回滚了！

编程语言

#Java #JavaWeb

JavaWeb第2篇数据库基础

https://www.eldpepar.com/coding/60536/

作者

EldPepar

发布于

2022年10月27日

许可协议

JavaWeb第3篇JDBC 上一篇

JavaWeb第1篇网络编程下一篇