数据库 基础知识

SQL分类：

1. DDL，数据定义语言：定义数据库对象

* 关键字：create，drop，alter等

2. DML，数据操作语言：对数据库中表的数据进行增删改

* 关键字：insert，delete，updater等

3. DQL，数据查询语言：查询数据库中表的记录/数据

* 关键字：select，where等

4. DCL，数据控制语言

---

DDL（Data Definition Language）：操作数据库、表

1. 操作数据库，CRUD，创查改删

1.1 Create，创建

create database 数据库名;
create database if not exists 数据库名;
create database 数据库名 character set gbk;
create database if not exists 数据库名 character set gbk;

1.2 Retrive,查询

show databases;
show create database 数据库名;-- 查看某个数据库的字符集 + 查看某个数据库的创建语句

1.3 Update，修改

alter database 数据库名 character set 字符集名;

1.4 Delete，删除

drop database 数据库名;
drop database if exists 数据库名;

1.5 使用数据库

use 数据库名;
select database();-- 查询当前正在使用的数据库

---

2. 操作数据表，CRUD，创查改删

1.1 Create，创建

use database 数据库名;-- 使用指定的数据库
create table 表名(
    列名1 数据类型1；
    列名2 数据类型2,
    ...
    列名n 数据类型n
);
-- int 整数类型 
-- double 小数类型 double(5,2) 共5位，小数点后保留2位
-- date 日期类型 年月日 yyyy-MM-dd
-- datetime 日期类型 年月日 时分秒 yyyy-MM-dd HH:mm:ss
-- timestamp 时间戳类型 若不赋值或赋值null，默认使用当前系统时间自动赋值
-- varchar 字符串 varchar(20) 最大20个字符

1.2 Retrive,查询

show tables;-- 查询所有表的名称
desc 表名;-- 查询表结构

1.3 Update，修改

alter table 表名 rename to 新表名; -- 修改表名
alter table 表名 character set 字符集名; -- 修改字符集
alter table 表名 add 列名 数据类型; -- 增加一列
alter table 表名 modify 列名 新数据类型; -- 修改数据类型
alter table 表名 change 列名 新列名 新数据类型; -- 修改列名和数据类型
alter table 表名 drop 列名; -- 删除列

1.4 Delete，删除

drop table 表名;
drop table if exists 表名;

---

DML（Data Manipulation Language）：增删改表中数据

关键字：insert，delete，update

1. 添加数据

• 语法：insert into 表名(列名1,列名2,…,列名n) values(值1,值2,…,值n);
• 注意：
  • 列名与值一一对应；
  • 若表名后，不定义列名，则默认给所有列添加值；
  • 除数字类型，其他类型需要使用引号（单双号都可）引起来，如日期为”2021-08-17“

2. 删除数据

• 语法：delete from 表名 [where 条件];
• 注意：
  • 如果不加条件，则删除表中所有记录
  • 删除所有记录：delete from 表名; – 不推荐使用，有多少记录会执行多少次删除操作
  • 删除所有记录：truncate table 表名; – 删除表，效率较高，先删除表，后创建一张对应的空表

3. 修改数据

• 语法：update 表名 set 列名1 = 值1,列名2 = 值2,…[where 条件];
• 注意：如果不添加任何条件，则会将表中所有记录全部修改

DQL（Data Query Language）：查询表中记录

1. 语法 select * from 表名;

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

2. 基础查询

• 多个字段的查询：select 字段1,字段2… from 表名; 查询所有字段，则可以使用" * "来代替字段列表
• 去除重复：select distinct 字段列表 from 表名; 只有当结果集数据完全一样才可以去重复
• 计算列：一般可以使用四则运算计算一些列的值
• ifnull(表达式1,表达式2) ：若表达式1不是NULL，则返回第一个参数， 否则，IFNULL函数返回表达式2
  表达式1：哪些字段需要判断是否为null
  表达式2：该字段为null后的替换指
  如果表达式1为NULL，则IFNULL函数将返回表达式2的值

-- 若学生电话为空，则使用紧急联系电话 
select name,ifnull(phone,urgentphone) from student; 

• 起别名：as，也可以省略，用空格隔开

select
	username as 账号,
	password as 密码,
	mingzi as 名字,
	zhengjianhao as 证件号,
    dianhua as 电话
from user

3. 条件查询

• where子句后跟条件
• 运算符
  • <，>，<=，>=，=，< >或！=
  • between…and
  • in(集合)
  • like：模糊查询
    • 占位符："_" 单个任意字符 “%” 多个任意字符
  • is null
  • and 或 &&
  • or 或 ||
  • not 或 !

select * from stu3 where age>20; -- 查询年龄大于20的人

-- 查询年龄大于20，小于等于30的人
SELECT * FROM stu3 WHERE age>=20 && age<=30;
SELECT * FROM stu3 WHERE age>=20 AND age<=30;
SELECT * FROM stu3 WHERE age BETWEEN 20 AND 30;

-- 查询年龄19岁，22岁，25岁的人
SELECT * FROM stu3 WHERE age=20 || age=19 || age=25;
SELECT * FROM stu3 WHERE age=20 OR age=19 OR age=25;
SELECT * FROM stu3 WHERE age IN (19,22,25);

-- 查询英语成绩为null
-- SELECT * FROM stu3 WHERE english = NULL;-- 不对，null不能使用= !=判断
SELECT * FROM stu3 WHERE english IS NULL;
-- 查询英语成绩不为null
SELECT * FROM stu3 WHERE english IS NOT NULL;

-- 查询姓马的有哪些？
SELECT * FROM stu3 WHERE NAME LIKE "马%";
-- 查询姓名第二个字是“化”的有哪些？
SELECT * FROM stu3 WHERE NAME LIKE "_化%";
-- 查询姓名三个字的人
SELECT * FROM stu3 WHERE NAME LIKE "___";
-- 查询姓名中包含“马”的人？
SELECT * FROM stu3 WHERE NAME LIKE "%德%";

DQL，数据查询语言

1. 排序查询

• 语法：order by 排序字段1 排序方式1,排序字段2 排序方式2,…
• 排序方式：ASC 升序，默认 DESC降序
• 注意：如果有多个排序条件，则当前边的条件一样，才会判断第二条件，依次类推

-- 按math分数升/降序来排列
select * from stu order by math asc;
select * from stu order by math desc;
-- 按照数学成绩从高到低排名，如果数学成绩一样，则按照英语成绩从高到低排名
select * from stu order by math desc,english desc;

2. 聚合函数

• 将一列数据作为一个整体，进行纵向操作

1. count()：计算个数，ui版不包括null的列：主键，如count(*)
2. max()：计算最大值
3. min()：计算最小值
4. sum()：计算和
5. avg()：计算平均值
* 注意：聚合函数的计算，排除null值
* 解决方案：
	1. 选择不包含null的列进行计算
	2. ifnull(表达式1,表达式2)函数替换null

select count(id) from stu; -- 查询stu中学生个数 
select max(math) from stu; -- 查询最大的math成绩
select min(math) from stu; -- 查询最小的math成绩
select sum(math) from stu; -- 查询math成绩和
select avg(math) from stu; -- 查询math成绩平均值

3. 分组查询

• 语法：group by 分组字段;
• 注意：
  1. z
  2. where和having的区别：
     1. where在分组之前进行限定，如果不满足条件，则不参与分组，having在分组之后进行限定，如果不满足结果，则不会被查询出来
     2. where后不可以跟聚合函数，having可以进行聚合函数的判断

-- 按照性别分组，分组查询男、女同学的数学平均分
select sex,avg(math) from stu group by sex;

-- 按照性别分组，分组查询男、女同学的平均分，人数
select sex,avg(math),count(id) from stu group by sex;

-- 按照性别分组，分组查询男、女同学的平均分，人数
-- 要求：分数低于70分的人，不参与分组（分组之前对条件进行限定）
select sex,avg(math),count(id) from stu where math>70 group by sex;

-- 按照性别分组，分别查询男、女同学的平均分，人数
-- 要求：分数低于70分的人，不参与分组，分组之后，显示人数大于2个人的组
select sex,avg(math),count(id) from stu where math>70 group by sex having count(id)>2;

4. 分页查询

• 语法：limit 开始的索引,每页查询的条数;

---

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

---

• limit是MySQL一个“方言”，智能在MySQL中用，在Oracle，SQL Server中都有自己的分页方式

-- 每页显示3条记录
-- 从0开始查3条记录（0，1，2）作为第一页
select * from stu limit 0,3;
-- 从3开始查6条记录（3，4，5）作为第二页
select * from stu limit 3,3;

约束

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

分类：

1. 主键约束：primary key
2. 非空约束：not null
3. 唯一约束：unique
4. 外键约束：foregin key

非空约束：not null 值不能为null

-- 1.创建表添加非空约束
create table stu(
id int,
name varchar(20) not null -- name为非空
);
-- 2.创建表后，添加非空约束(修改数据类型)
alter table stu modify id int not null;
-- 3.删除name的非空约束(修改数据类型)
alter table stu modify name carchar(20);

唯一约束：unique，值不能重复

-- 1.创建表添加唯一约束
create table stu(
id int,
identity_number varchar(20) unique -- 身份证号唯一 
)
-- 2.删除唯一约束
-- 不可以 alter table stu2 modify phone VARCHAR(20);
alter table stu drop index identity_number;
-- 3.创建表后，添加唯一约束
alter table stu modify identity_number varcgar(20) unique;

主键约束：primary key + 自动增长 auto_increment

• 注意：（1）含义：非空且唯一；（2）一张表只能有一个字段为主键；（3）主键就是表中记录的唯一标识

-- 1.创建表时，添加主键
create table stu(
id int primary key,-- 给id添加主键约束
name varchar(20)
);

-- 2.删除主键
-- 错误 alter table stu modify id int;
alter table stu drop primary key;

-- 3.创建表后，添加主键
alter table stu modify id int primary key;


-- 4.自动增长：只与上一条记录有关，会读取上一条记录的值，然后在其基础上+1
	-- * 如果某一列是数值型的，使用auto_increment可以完成值得自动增长
	
-- 4.1创建表时，添加主键约束，并且完成主键自动增长
create table stu(
id int primary key auto_increment,
name varchar(20)
);
-- 4.2删除自动增长
alter table stu modify id int;
-- 4.3添加自动增长
alter table stu modify id int auto_increment;

外键约束：foreign key 让表与表产生关系，从而保证数据得正确性

• 在创建表时可以添加外键，外键可以为null，但不可以为主表中不存在得值
• 语法：

create table 表名(
...
外键列
constrain 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)
);

-- 1.创建表，并创建外键
create table department(
id int primary key auto_increment,
dep_name varchar(30),
dep_location varchar(30)
);
create table employment(
id int primary key auto_increment,
name varchar(30),
age int,
dep_id int,-- 外键对应主表的主键
constraint emp_dep_id foreign key(dep_id) references department(id);
);-- emp_dep_id 外键名称

-- 2.删除外键
-- 语法：alter table 表名 drop foreign key 外键名称;
alater table employment drop foreign key emp_dep_id;

-- 3.创建表后，添加外键
-- 语法：alter table 表名 add constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称);
alter table employment add constraint emp_dept_id foreign key(dep_id) references department(id);

-- 4.级联操作，需要在添加外键时设置级联操作，实际开发中，谨慎使用
-- 语法：alter table 表名 add constraint 外键名 foreign key(外键列名) references 主表名(主表列名) on update cascade on update cascade on delete cascade;
-- 分类：（1）级联更新：on update cascade
--       (2) 级联删除：on delete cascade
-- 添加外键时，设置级联更新，设置级联删除
alter table employment add constarint emp_dept_id foreign key(dep_id) references department(id) on update cascade on delete cascade;

---

数据库的设计

一对一：可以在任意一方添加唯一外键指向另一方的主键

• 个人和身份证的关系，一个人对应一个身份证，其实可以将两张表合并成一张表

一对多：在“多”的一方建立外键，指向“一”的一方的主键

• 班级表class与学生表student的关系，在student表中建立外键calss_id，指向class表的主键

多对多：需要借助第三张表（中间表）

• 如学生与课程的关系，在学生表student和课程表class的基础上，建立一张student_class表，该表的每行记录至少包含学生id和包含课程id，分别指向student表和class表的主键

范式

• 概念：设计数据库时，需要遵守一些规范，要遵循后边的范式要求，必须先遵循前边的所有范式要求，各种范式呈递次规范，越高的范式数据冗余越小
• 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式（4NF）、第五范式（5NF，又称完美范式）。

第一范式（1NF）:每一项都是不可分割的原子数据项

• 存在的问题：存在非常严重的数据冗余、数据添加时存在问题、数据删除存在问题
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PQ0GkBTj-1629279301139)(C:\Users\11542\Desktop\Java学习\图片\1NF.png)]

第二范式（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
例如：学号-->系名，系名-->系主任

码：如果一张表中，一个属性或属性组，被其他所有属所完全依赖，则成这个属性/属性组为该表的码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T8TYAVQ3-1629279301141)(C:\Users\11542\Desktop\Java学习\图片\2NF.png)]

第三范式（3NF）:在2NF基础上，任何非主属性不依赖于其他非主属性

• 在2NF基础上消除传递依赖

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rh1ttd0B-1629279301143)(C:\Users\11542\Desktop\Java学习\图片\3NF.png)]

数据库的备份和还原

• 命令行

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

* 图形化工具 备份+还原

多表查询

• 语法

select
	列名列表
from
	表名列表
where
	...

• 笛卡尔积：有两个集合A、B，取这两个集合的所有组成情况

要完成多表查询，需要消除无用的数据

• 多表查询的分类：

1. 内连接查询：从那些表查询数据，条件是什么，查询哪些字段

1. 隐式内连接：使用where消除无用数据
-- 查询所有员工信息和对应的部门信息，条件：emp.'dept_id' = dept.'id'
select * from emp,dept where emp.'dept_id' = dept.'id';
-- 查询员工表的名称，性别，部门表的名称，条件同上
select emp.name,emp.gender,dept.name from emp,dept where emp.'dept_id' = dept.'id';
-- ***用别名代替表名，方便书写***
select
	t1.name,
	t1.gender,
	t2.name
from
	emp t1,
	dept t2
where
	t1.'dept_id'=t2.'id';
	
2. 显示内连接：语法 select 字段列表 from 表名1 [inner] join 表名2 on 条件
-- 查询所有员工信息和对应的部门信息
select * from emp inner join dept on emp.'dept_id' = dept.'id';
select * from emp join dept on emp.'dept_id' = dept.'id';

2. 外连接查询

1. 左外连接
-- 语法：select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 查询的是左表所有数据以及其交集部分
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
select t1.*,t2.name
from emp t1 left join dept t2
on t1.'dept_id'=t2.'id'; 
    
2. 右外连接
-- 语法：select 字段列表 from 表1 right [outer] join 表2 on 条件;
-- 查询的是右表所有数据以及其交集部分
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
select t1.*,t2.name
from dept t2 right join emp t1
on t1.'dept'=t2.'id'

3. 子查询：查询中嵌套查询，称嵌套查询为子查询
   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DKIaPQ8V-1629279301145)(C:\Users\11542\Desktop\Java学习\图片\练习-1.png)]

子查询不同情况
1. 子查询结果是单行单列
	* 子查询结果参与条件判断，使用运算符去判断。
	* 运算符：> >= < <= =
-- 查询平均工资是“9000”
select max(salary) from emp;
-- 查询员工信息，并且工资等于最高工资的
select * from emp where emp.'salary' < 9000;

-- 一条sql语句完成上述操作
select * from emp where emp.'salary' < (select avg(salary) from emp);

2. 子查询结果是多行单列
	* 子查询可以作为条件（集合），使用运算符in来判断
-- 查询'财务部'和'市场部'所有员工信息
select id from dept where name='财务部' or name='市场部';
select * from emp where dept_id in (2,3);
-- 一条sql语句完成上述操作
select * from emp where depy_id in (select id from dept where name='财务部' or name='市场部');
	
3. 子查询结果是多行多列
	* 子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
select * 
from dept t1,(select * from emp where emp.'join_date' > '2011-11-11') t2
where t1.id=t2.dept_id;

-- 普通内连接
select * 
from emp t1,dept t2
where t1.'dept_id'=t2.'id' and t1.'joindate' > '2011-11-11';

事务

概念

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

操作

1. 开始事务 start transaction;
start transaction; -- 开启事务：张三向李四转账500元
update account set balance=balance-500 where name='张三';
-- 出错了... （人为制造错误）
update account set balance=balance+500 where name='李四';

2. 回滚 rollback;
rollback;	-- 出现问题了，回滚事务

3. 提交 commit;
commit;		-- 发现执行没有问题，提交事务

4. MySQL数据库中事务默认自动提交
* 事务提交的两种方式
	* 自动提交，MySQL默认自动提交，一条DML（insert，delete，update）语句会自动提交一次事务
	* 手动提交，Oracle默认手动提交，先开启事务，再提交
* 修改事务的默认提交方式
	* 查看事务的默认提交方式
		* select @@autocommit; -- 0手动 1自动 
	* 修改事务默认提交方式
		* set @@autocommit = 0; -- 设置为默认手动提交

事务四大特征

1. 原子性：不可分割的最小操作单位，要么同时成功，要么同时失败
2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据
3. 隔离性：多个事务之间，相互独立
4. 一致性：事务操作后，数据总量不变

事务的隔离级别（了解）

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

• 存在的问题：

1. 脏读：一个事务，读取到了另一个事务中没有提交的数据
2. 不可重复读(虚读):在同一个事务中，两次读取到的数据不一样
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则另一个事务查询不到自己的修改

• 隔离级别，从小到大，性能越来越高，但效率越来越低

1. read uncommitted 读未提交
	* 产生的问题：脏读，不可重复读，幻读
2. read committed 读已提交
	* 产生的问题：不可重复读，幻读
3. repeatable read 可重复度
	* 产生的问题：幻读
4. serializable 串行化
	* 可解决所有的问题
* 查询隔离级别 select @@tx_isolation;
* 设置隔离级别 set gloable transaction isolation level 级别字符串;

• 演示

-- 设置隔离级别为“读未提交”
set global transaction isolation level read uncommited;
start transaction; -- 开始事务
-- 转账操作
update account set balance = balance-500 where id = 1;
update account set balance = balance+500 where id = 2;
-- 不提交，在另一个窗口的事务里，可以读取到数据已修改

DCL

SQL分类：
1. DDL：操作数据库和表
2. DML：增删改表中的数据
3. DQL：查询表中数据
4. DCL：管理用户，授权

• DBA：Database Administor 数据库管理员
• 管理用户

1. 添加用户
	* 语法：create user '用户名'@'主机名' identified by '密码';
-- 创建用户
create user 'zhangsan'@'localhost' identified by '123';
create user 'lisi'@'%' identify by '123';

2. 删除用户
	* 语法：drop user '用户名'@'主机名';
-- 删除用户
drop user 'zhangsan'@'localhost';
drop user 'lisi'@'%';

3. 修改用户密码
	* 语法1：update user set password=password('新密码') where user='用户名';
	* 语法2：set password for '用户名'@'主机名'  = password('新密码');
-- 修改用户密码
update user set password=password('abc') where user='zhangsan';
set password for 'zhangsan'@'localhost'=password('abc');
	* MySQL中忘记了root用户的面膜，该怎么办？
		1. 关闭mysql服务：(管理员身份运行) cmd--> net stop mysql;
		2. 使用无验证方式启动MySQL服务：cmd--> mysql skip-grant-tables
		3. 打开新的cmd窗口，直接输入mysql命令，敲回车，就可登录成功
		4. 进入mysql数据库：user mysql;
		5. 修改密码：update user set password=password('root') where user='root';
		6. 关闭两个窗口
		7. 打开任务管理器，手动结束'mysql.exe'
		8. 启动mysql服务：(管理员身份运行) cmd-->net start mysql;
		9. 使用新密码登录
	
4. 查询用户
-- 1.切换到mysql表
use mysql;
-- 2.查询user表
select * from user;

* 通配符：% 表示可以在任意主机上使用用户登录数据库

• 权限管理

1. 查询权限
	* 语法：show grants for '用户名'@'主机名';
-- 查询权限
show grants for 'lisi'@'%';
show grants for 'root'@'%';

2. 授予权限
-- 授予select权限给李四（只能查询db3的account表，且不可增删改）
grant select on db3.account to 'lisi'@'%';
-- 使用lisi登录，智能看到account表

-- 授予db3的account表的多项权限给lisi
grant select,delete,update on db3.account to 'lisi'@'%';

-- 给zhangsan授予所有权限（all），在任意数据库任意表（*.*）上 <和root用户一样>
grant all on *.* to 'zhangsan'@'localhost';

3. 撤销权限
	* 语法：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-- 撤销李四的db3.account表的更新权限
revoke undate on db3.account from 'lisi'@'%';
换到mysql表
use mysql;
-- 2.查询user表
select * from user;

* 通配符：% 表示可以在任意主机上使用用户登录数据库

• 权限管理

1. 查询权限
	* 语法：show grants for '用户名'@'主机名';
-- 查询权限
show grants for 'lisi'@'%';
show grants for 'root'@'%';

2. 授予权限
-- 授予select权限给李四（只能查询db3的account表，且不可增删改）
grant select on db3.account to 'lisi'@'%';
-- 使用lisi登录，智能看到account表

-- 授予db3的account表的多项权限给lisi
grant select,delete,update on db3.account to 'lisi'@'%';

-- 给zhangsan授予所有权限（all），在任意数据库任意表（*.*）上 <和root用户一样>
grant all on *.* to 'zhangsan'@'localhost';

3. 撤销权限
	* 语法：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
-- 撤销李四的db3.account表的更新权限
revoke undate on db3.account from 'lisi'@'%';