【JavaWeb】数据库基础复习

1 MySQL

数据库特点：

1. 持久化存储数据，数据库就是一个文件系统
2. 便于存储和管理数据
3. 使用统一的方式操作数据库

---

启动MySQL服务：
管理员cmd：net start mysql

停止MySQL服务：
管理员cmd：net stop mysql

打开服务窗口
win+R+services.msc

登录mysql：

1. mysql -u root -p
2. mysql -h[ip地址] -u root -p
3. mysql --host=ip --user=root --password=密码

mysql退出登录：exit / quit

---

mysql的目录结构

1. 安装目录：

• bin【可执行文件】
• data【日志文件、数据文件】
• include【头信息】
• lib【jar包 库文件】
• share【错误信息】
• my.ini：配置文件

2. 数据目录

• 数据库：文件夹
• 表：文件
• 数据：文件存储的内容

2 SQL

SQL：Structured Ouery Language：结构化查询语言

SQL分类：

• DDL：操作数据库、表
• DML：增删改表中数据
• DQL：查询表中数据
• DCL：授权

2.1 DDL：CRUD —> 数据库、表

操作数据库：CRUD —> 数据库

1. create：创建

• 创建数据库并设置字符集

mysql> create database db2 character set gbk;
mysql> create database if not exists db2 character set gbk;

2. retrieve：查询

• 查询所有数据库名称

mysql> show databases;

• 查询某个数据库的字符集；查询数据库的创建语句

mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+

3. update：修改

• 修改数据库的字符集

mysql> alter database db1 character set gbk;

4. delete：删除

• 删除数据库

mysql> drop database db3;
mysql> drop database if exists db3;

drop database if exists db3;

5. 使用数据库

• 查询正在使用的数据库

mysql> select database();

• 使用数据库

mysql> use db3;

---

操作表：CURD —> 表

1. Create

• 创建表
  常用数据类型int、double、date(yyyy-MM-dd)、datetime(yyyy-MM-dd HH:mm:ss)、timestamp(插入时间)、varchar(x[个字符])

mysql> create table student(
    -> id int,
    -> name varchar(255),
    -> age int,
    -> score double(4,1),
    -> birthday date,
    -> insert_time timestamp);

• 复制表

create table student_copy like student;

2. Retrieve

• 查询所有表名称

mysql> show tables;

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
+---------------+

• 查询表结构

desc student;

+-------------+--------------+------+-----+-------------------+-----------------------------+
| Field       | Type         | Null | Key | Default           | Extra                       |
+-------------+--------------+------+-----+-------------------+-----------------------------+
| id          | int(11)      | YES  |     | NULL              |                             |
| name        | varchar(255) | YES  |     | NULL              |                             |
| age         | int(11)      | YES  |     | NULL              |                             |
| score       | double(4,1)  | YES  |     | NULL              |                             |
| birthday    | date         | YES  |     | NULL              |                             |
| insert_time | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+--------------+------+-----+-------------------+-----------------------------+

3. Update

• 修改表名

 alter table student_copy rename to student2;

• 修改表的字符集

--mysql> show create table student;查看表的数据集
mysql> alter table student2 character set gbk;

• 添加一列

mysql> alter table student add gender varchar(2);

• 修改列名和类型

--列名和类型同时修改
mysql> alter table student change gender sex int(1);
--只修改类型
mysql> alter table student modify sex varchar(1);

• 删除列

4. Delete

--列名和类型同时修改
mysql> drop table student2;

• 删除表

mysql> drop table student_copy;
mysql> drop table if exists student_copy;

---

2.2 DML：CUD —> 数据

1. Create

• 插入语句

--插入全部字段 可以省略列名 但要给所有值添加值 否则报错
mysql> insert into student(id,name,age) values(1,"张三",20);

2. Update

• where条件修改

mysql> update student set name="王五" where id = 4;
mysql> update student set name="王五",age="19" where id = 4;
--如果没有where条件则会修改表中所有数据

3. Delete

• where条件删除

mysql> delete from student where name="李四";

• 删除表中所有记录

delete from student;--效率低 不推荐使用有多少记录就会删除多少次 delete*n
truncate table student; --先删除表 再创建一个一模一样的空表 drop+create

2.3 DQL：R —> 数据

基础查询

1. 查询表中所有记录/某字段的记录

select * from tablename
select name, age from tablename

2. 完整的select语法

-select 字段 from 表 where 条件 group by分组 having 分组后的条件 order by排序 limit 分页限定

3. 查询并去重

mysql> select distinct address from scores;

4. 查询并计算列之和

IFNULL(需要判断null的字段, null的替换值)
AS 起别名 【AS可省略】

--如果null参与运算 运算结果是0
mysql> SELECT NAME, chinese, math, english, chinese+math+english FROM scores;
--如果出现null替换为*值
mysql> SELECT NAME, chinese, math, english, IFNULL(chinese,0)+math+english AS sumScore FROM scores;

条件查询

1. where子句+条件
2. 运算符：

• >、<、<=、>=、=、<>
  -select [查询字段] where [条件字段] [运算符] [比较条件]
• BETWEEN…AND
  -select [查询字段] where [条件字段] between [比较条件1] and [比较条件2]
• IS NULL
  -select [查询字段] where [条件字段] is null
• and 或 &&
• -select [查询字段] where [条件字段] [比较条件1] and [比较条件2]
• or 或 ||
• -select [查询字段] where [条件字段] [比较条件1] and [比较条件2]
• not 或 !
• -select [查询字段] where [条件字段] is not null
• LIKE 【模糊查询】：like的占位符 "_"单个字段 "%"多个字段
• mysql> select * from scores where name like "%张%"; 包含张
• mysql> select * from scores where name like "张%" ;第二个字是张
• mysql> select * from scores where name like "_张" ;第一个字是张

---

排序查询
order by [排序字段] [排序方法]
ASC：升序 默认 DESC：降序

--如果字段1一样，则比较字段2。`
mysql> select * from scores order by math DESC,chinese DESC;

---

聚合函数
count：计算个数
max：最大值
min：最小值
sum：计算和
avg：计算平均值
将一列作为整体进行纵向运算，会排除null值

mysql> select count(name) from scores;
--select max/min/sum/avg(name) from scores;

---

分组查询
分组之后查询的字段只能是【分组字段】或者【聚合函数】

mysql> select gender,avg(math),count(id) from scores group by gender;
+--------+-----------+-----------+
| gender | avg(math) | count(id) |
+--------+-----------+-----------+
| 女     |  89.20000 |         6 |
| 男     |  93.00000 |         3 |
+--------+-----------+-----------+
-- where在分组之前加条件进行限定
-- 成绩大于 90才参与分组
mysql> select gender,avg(math),count(id) from scores where math > 90 group by gender;
+--------+-----------+-----------+
| gender | avg(math) | count(id) |
+--------+-----------+-----------+
| 女     |  96.50000 |         2 |
| 男     |  97.00000 |         2 |
+--------+-----------+-----------+
--having分组之后进行限定 不满足则不会被查询出来
--人数要大于2人
mysql> select gender,avg(english) avg,count(id) count from scores where english > 80 group by gender having count > 3;
+--------+--------------+-----------+
| gender | avg(english) | count(id) |
+--------+--------------+-----------+
| 女     |     90.60000 |         5 |
+--------+--------------+-----------+
--where后不可以加聚合函数 having后可以加聚合函数

---

分页查询
limit [开始索引] [每页条数] 只能用在mysql里
开始索引 =（当前页码 - 1）* 每页显示条数

2.4 DCL：管理用户授权

1. 添加用户
2. 删除用户
3. 修改密码
4. 查询用户

-- 1. 切换到mysql数据库
USE mysql;
-- 2. 查询user表
SELECT * FROM `user`;
-- 通配符%表示可以在任意主机使用用户登录数据库
-- 3.创建用户
CREATE USER "zhangsan"@"localhost" IDENTIFIED BY "password";
CREATE USER "zhangsan"@"%" IDENTIFIED BY "password";
SELECT * FROM `user`;
-- 4.删除用户
DROP USER "zhangsan"@"localhost";
DROP USER "zhangsan"@"%";
SELECT * FROM `user`;
-- 5.修改密码
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER='用户名';
SET PASSWORD FOR "zhangsan"@"locahost" = PASSWORD("123");

如果忘记了root用户密码
解决方案：

1. cmd —> net stop mysql
2. 使用无验证方法启动mysql：mysqld --skip-grant-tables
3. cmd输入mysql进入数据库修改密码
4. 打开任务管理器 结束mysqld服务
5. cmd —> net start mysql

用户权限管理

• 授予权限
• 查看权限
• 撤销权限

-- CREATE USER "zhangsan"@"%" IDENTIFIED BY "password";
SHOW GRANTS FOR "zhangsan"@"%";

GRANT SELECT,UPDATE ON db2.account TO "zhangsan"@"%";
SHOW GRANTS FOR "zhangsan"@"%";

-- 所有权限 ALL 和所有表 *.* 使用通配符
GRANT ALL ON *.* TO "zhangsan"@"%";
SHOW GRANTS FOR "zhangsan"@"%";

-- 撤销权限
REVOKE UPDATE ON db2.account FROM "zhangsan"@"%";
REVOKE ALL ON *.* FROM "zhangsan"@"%";
SHOW GRANTS FOR "zhangsan"@"%";

3 约束

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

3.1 非空约束

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

mysql> create table stu(
    -> id int,
    -> name varchar(20) not null);
--删除非空约束alter+modify
mysql> alter table stu modify name varchar(20);
--创建表后添加非空约束
mysql> alter table stu modify name varchar(20) not null;

3.2 唯一约束

2. 唯一约束unique

可以有null值 但null值只能在一条里存在

mysql> create table stu(
    -> id int,
    -> phone varchar(20) unique);

--删除唯一约束alter+drop index
mysql> alter table stu drop index phone;
-- 建表后添加唯一约束
mysql> alter table stu modify phone int(20) unique;

3.3 主键约束

3. 主键约束
   非空且唯一

mysql> create table stu(
    -> id int primary key,
    -> name varchar(20));
--删除逐渐约束alter+drop
alter table stu drop primary key;
-- 建表后添加主键约束
mysql> alter table stu modify phone int(20) primary key;

【补充】自动增长
如果某一列是数值类型，使用auto_increment可以完成值的自动增长

--创建表时添加主键约束并完成主键的自动增长
mysql> create table stu(
    -> id int primary key auto_increment,
    -> name varchar(20));
--删除自动增长
mysql> alter table stu modify id int;--主键还存在
--建表后添加自动增长
mysql> alter table stu modify id int auto_increment;

3.4 外键约束+级联操作

4. 外键约束+级联操作
   减少数据冗余，进行表的拆分
   原表：员工表（工号，姓名，年龄，部门，部门地址）
   部门和部门地址会出现冗余
   拆分：
   员工表员工表（工号，姓名，年龄，部门号）
   【级联操作谨慎使用】

mysql> create table emp(
    -> emp_id int primary key auto_increment,
    -> name varchar(255) not null,
    -> age int not null,
    -> dep_id int,
    -> constraint emp_dep_fk foreign key (dep_id) references dep(dep_id));

--删除外键
alter table emp drop foreign key emp_dep_fk;
--添加外键
alter table emp add constraint emp_dep_fk foreign key (dep_id) references dep(dep_id);

--添加外键 级联更新
alter table emp add constraint emp_dep_fk foreign key (dep_id) references dep(dep_id) on update cascade;

--添加外键 级联删除
alter table emp add constraint emp_dep_fk foreign key (dep_id) references dep(dep_id) on delete cascade;

--同时添加
alter table emp add constraint emp_dep_fk foreign key (dep_id) references dep(dep_id) on update cascade on delete cascade;

部门表（部门号，部门，部门地址）

mysql> create table dep(
    -> dep_id int primary key auto_increment,
    -> name varchar(255) not null,
    -> address varchar(255));

存在外键约束，删除外键关联表中的记录会报错：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
key constraint fails (db1.emp, CONSTRAINT emp_dep_fk FOREIGN KEY
(dep_id) REFERENCES dep (dep_id))

存在外键约束，在主表中插入关联表中不存在的记录会报错

ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails (db1.emp, CONSTRAINT emp_dep_fk FOREIGN KEY
(dep_id) REFERENCES dep (dep_id))

4 数据库的设计

4.1 多表之间的关系

1. 一对一
   身份证和人：一人只有一个身份证 一个身份证只属于一个人
2. 一对多
   部门和员工：一个员工只属于一个部门 一个部门有多个员工
3. 多对多
   一个学生可以选择很多课程，一个课程可以被很多学生选择

一对多关系的实现
在多的一方（员工）建立外键，指向一的一方（部门）的主键
多对多关系的实现
需要建立中间表记录两张表的主键，这两个字段作为外键指向两张表
一对一关系的实现：任意一方添加唯一外键，指向另一方的主键。或者让两表的主键相同，一般情况合成一张表。

4.2 范式

数据库设计时遵循的规范

1. 第一范式（1NF）：每一列都是不可分割的原子项
2. 第二范式（2NF）：在1NF的基础上，消除非主属性对主属性的部分依赖

• 函数依赖：A—>B：如果通过A属性（属性组）的值，可以唯一确定B属性组中的值。
  【学号 —> 姓名】【学号+课程名称 —> 分数】
• 完全函数依赖：A—>B：如果A是一个属性组，则B属性值的确定需要依赖于A属性的所有属性值。【学号+课程名称 —> 分数】
• 部分属性依赖：A—>B：如果A是一个属性组，则B属性值的确定只需要依赖于A属性的部分属性值。【学号+课程名称 —> 姓名】
• 传递函数依赖：A—>B—>C：通过A可以确定B，通过B可以确定C，则称C传递依赖于A。【学号 —> 系名 系名 —> 系主任】
• 码：如果表中一个属性或属性组，被其他属性完全依赖则称这个属性为该表的码。
• 主属性：码属性组中的所有属性
• 非主属性：除了码属性组的属性

4. 第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其它主属性（消除传递依赖）

5 数据库的备份和还原

1. 命令行

• 备份： mysqldump -u用户名 -p密码 数据库名称> 保存路径
• 还原：
  a.登录数据库
  b.创建数据库
  c.使用数据库
  d.执行文件 source 文件路径

2. 图形化工具

6 多表查询

6.1 笛卡尔积

有两个集合A和B，取两个集合的所有组合情况，多表查询需要消除无用数据

mysql> select * from emp,dep;

6.2 内连接查询

从哪些表中查询，条件是什么，查询哪些字段

• 隐式内连接：

mysql> select * from emp,dep where emp.dep_id=dep.dep_id;

mysql> select t1.name, t1.gender, t2.name
    -> from emp t1, dep t2
    -> where t1.dep_id = t2.dep_id;

• 显式内连接：

--结果同隐式内连接
mysql> select * from emp inner join dep on emp.dep_id=dep.dep_id;

6.3 外连接查询

• 左外连接：左表所有数据 以及 两张表的交集部分
• 右外连接：右表所有数据 以及 两张表的交集部分

mysql> select * from dep left outer join emp on dep.dep_id=emp.dep_id;
+--------+-----------+--------+-----------+--------+--------+---------+------------+
| dep_id | name      | emp_id | name      | gender | dep_id | salary  | join_data  |
+--------+-----------+--------+-----------+--------+--------+---------+------------+
|      1 | 开发部    |      1 | 孙悟空    | 男     |      1 | 7200.00 | 2013-02-24 |
|      1 | 开发部    |      2 | 赵六      | 男     |      1 | 3600.00 | 2018-01-02 |
|      2 | 市场部    |      3 | 唐僧      | 男     |      2 | 8999.00 | 2019-01-23 |
|      2 | 市场部    |      4 | 张三      | 男     |      2 | 8000.00 | 2019-01-22 |
|      3 | 财务部    |      5 | 蜘蛛精    | 女     |      3 | 9000.00 | 2018-12-12 |
|      3 | 财务部    |      6 | 白骨精    | 女     |      3 | 8222.00 | 2017-12-10 |
+--------+-----------+--------+-----------+--------+--------+---------+------------+
6 rows in set (0.00 sec)

mysql> select * from dep right outer join emp on dep.dep_id=emp.dep_id;
+--------+-----------+--------+-----------+--------+--------+----------+------------+
| dep_id | name      | emp_id | name      | gender | dep_id | salary   | join_data  |
+--------+-----------+--------+-----------+--------+--------+----------+------------+
|      1 | 开发部    |      1 | 孙悟空    | 男     |      1 |  7200.00 | 2013-02-24 |
|      1 | 开发部    |      2 | 赵六      | 男     |      1 |  3600.00 | 2018-01-02 |
|      2 | 市场部    |      3 | 唐僧      | 男     |      2 |  8999.00 | 2019-01-23 |
|      2 | 市场部    |      4 | 张三      | 男     |      2 |  8000.00 | 2019-01-22 |
|      3 | 财务部    |      5 | 蜘蛛精    | 女     |      3 |  9000.00 | 2018-12-12 |
|      3 | 财务部    |      6 | 白骨精    | 女     |      3 |  8222.00 | 2017-12-10 |
|   NULL | NULL      |      7 | 小白龙    | 男     |   NULL | 10000.00 | NULL       |
+--------+-----------+--------+-----------+--------+--------+----------+------------+

6.4 子查询

嵌套的select就是子查询

子查询的不同情况：
单行单列

--子查询作为条件 用运算符去判断
mysql> select * from emp where emp.salary = (select max(salary) from emp);
mysql> select * from emp where emp.salary < (select avg(salary) from emp);

多行单列

-- 单列dep_id 但是存在多个 即多行 可以用 【字段 in (子查询)】
mysql> select * from emp where dep_id in (select dep_id from dep where name="财务部" or name="市场部");

多行多列

-- 子查询的结果是多行多列的情况下 ，子查询可以作为一张虚拟表进行表的查询
mysql> select * from dep t1,(select * from emp where emp.join_date>"2018-01-01") t2
    -> where t1.dep_id = t2.dep_id;
--相当于普通的内连接
mysql> select * from dep inner join emp on dep.dep_id=emp.dep_id where join_date>"2018-01-01";

6.5 综合练习

-- 练习1
SELECT 
	emp.id,-- 员工号
	emp.ename,-- 员工名
	emp.salary,-- 员工工资
	job.jname,-- 职务名
	job.description -- 职务描述
FROM 
	emp,-- 员工表
	job -- 职务表
WHERE emp.job_id=job.id;

-- 练习2
SELECT 
	emp.ename,-- 员工名
	emp.salary,-- 员工工资
	job.jname,-- 职务名
	job.description,-- 职务描述
	dept.dname,-- 部门名
	dept.loc -- 部门位置
FROM 
	emp,-- 员工表
	job,-- 职务表
	dept -- 部门表
WHERE 
	emp.job_id=job.id AND emp.dept_id=dept.id;

-- 练习3
SELECT
	emp.ename,-- 员工名
	emp.salary,-- 员工薪资
	salarygrade.grade-- 员工薪资等级
FROM
	emp,-- 员工表
	salarygrade-- 薪资登记表
WHERE
	emp.salary>=salarygrade.losalary AND emp.salary<=salarygrade.hisalary;

-- 练习4
SELECT
	emp.ename,
	job.jname,
	job.description,
	dept.dname,
	dept.loc,
	salarygrade.grade
FROM
	emp,
	job,
	dept,
	salarygrade
WHERE
	emp.dept_id=dept.id AND
	emp.job_id=job.id AND
	emp.salary BETWEEN salarygrade.losalary AND salarygrade.hisalary;

-- 练习5 重点！ 分组查询 + 子查询多列多行
SELECT
	t1.id,t1.dname,t1.loc,t2.totalnum
FROM
	dept t1,
	(SELECT
			emp.dept_id, COUNT(id) totalnum
	FROM
			emp
	GROUP BY dept_id) t2
	
WHERE t1.id=t2.dept_id;

-- 练习6 一张表的自关联 可以用重命名 用两次
SELECT
	t1.ename employee,
	t2.ename manager
FROM
	emp t1, emp t2
WHERE
	t1.mgr=t2.id

-- 练习6的升级版 没有管理者的也要在表中
-- 左表为员工 右边用来找管理者 显示所有员工的管理者 为空则null显示
SELECT
	t1.ename employee,
	t2.ename manager
FROM
	emp t1
LEFT JOIN 
	emp t2
ON
	t1.mgr=t2.id;

7 事务

7.1 事务的基本介绍

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

操作：

1. 开启事务 start transaction
2. 回滚 rollback
3. 提交 commit

MySQL数据库中事务默认自动提交，一条DML提交一次
Oracle默认手动提交
开启事务 则为手动提交

修改事务的默认提交方式

SELECT @@autocommit -- 1代表自动提交 -- 0代表手动提交
SET @@autocommit=1;

7.2 事务的四大特征

ACID
原子性：事务不可分割 要么同时成功 要么同时失败
持久性：事务一旦提交或回滚 数据库会持久化的保存数据
隔离性：多个事务之间相互独立
一致性：事务操作前后数据总量不变

7.3 事务的隔离级别

多个事务之间是隔离的，相互独立
如果多个事务操作同一批数据则会引发一些问题
设置不同的隔离级别可以解决这些问题

存在问题

• 脏读：一个事务读到另一个事务中没有提交的数据
• 不可重复读：在同一个事务中，两次读取的数据不一样
• 幻读：一个事务操作（DML）数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别：级别越高 安全性越高 效率越低

• read uncommitted：读未提交：脏读、不可重复读、幻读
• read committed：读已提交：不可重复读、幻读【Oracle默认的】
• repeatable read：可重复度：幻读【MySQL默认的】
• serializable：串行化：可以解决所有问题

选择合适的隔离级别
设置隔离级别

select @@tx_isolation;
set global transaction isolation level [级别字符串];

脏读和不可重复读的演示
设置隔离级别为 读未提交
set global transaction isolation level read uncommitted;
开启事务
start transaction;
转账：
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

窗口1 执行事务 但未提交

窗口2 读到脏数据

在窗口1 回滚后

窗口2 显示 【同时出现了不可重复读的问题】

设置隔离级别为 读已提交
set global transaction isolation level read committed;
在commit之后可以才在窗口2读到更改
但是对于同一个事务两次查询结果不一致 出现不可重复读的问题

---

设置隔离级别为可重复读
窗口1 开启事务
窗口2 开启事务
窗口1 转账 不提交
窗口2 查询账务 结果1
窗口1 提交
窗口2 查询账务 结果2
窗口2 提交
窗口2 查询账务 结果3

结果1 和 结果2 一致 在窗口2的一次事务中可重复度
结果3和结果1和不一致 因为窗口2的事务已提交

---

设置隔离级别为串行化
类似于锁机制
只有在窗口1提交之后 窗口2才可以查询