数据库简介
关系型数据库：Mysql 、Oracle 、SqlServer.... DB2 达梦
非关系型数据库：Redis 、MongoDB...
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
SQL(Structured Query Language)
结构化查询语言
SQL包括了所有对数据库的操作，主要是由4个部分组成：
1.数据定义：又称为“DDL语言”，定义数据库的逻辑结构，包括定义数据库、基本表、视图和索引4部分。
2.数据操纵：又称为“DML语言”，包括插入、删除和更新三种操作。
3.数据查询：又称为“DQL语言”，包括数据查询操作。
4.数据控制：又称为“DCL语言”，对用户访问数据的控制有基本表和视图的授权及回收。
5.事务控制：又称为“TCL语言”，包括事务的提交与回滚。
登录数据库
mysql -u root -p root
Mysql简单命令
查询所有数据库：
show databases;
选择使用数据库：
use 数据库名称;
创建数据库：
create database 数据库名称;
删除数据库：
drop database 数据库名称;
查看数据库编码
show create database 数据库名称;
查看当前数据库下面所有的表格：
show tables;
创建表格：
create table 表名称 (字段 字段类型(长度));
create table 表名称 (字段 字段类型(长度),字段 字段类型(长度),字段 字段类型(长度)........);
create table user (id int(3),name varchar(10));
create table person (id int(4),name varchar(10),phone varchar(11),address varchar(30));
查看表结构：
desc user;
desc 表名称;
删除表格：
drop table user;
drop table 表名称;
给表格中添加数据：
insert into person values (1,"admin","13988888888","taiyuan");//全字段
insert into person(id,name) values (1，“张三”)
若表名后面什么也不写，values后面则写全字段，按顺序来，字段类型不能乱
若指定添加，则在表名称后面写清楚要添加的字段，同时values后面要和字段相对应
insert into 表格名称 values (字段1,字段2....);
查询表格中所有数据
select * from person;
修改表结构：（选）
语法：（修改表字段名）
ALTER TABLE 表名称 CHANGE 原列名 想改成的列名 列类型;
ALTER TABLE test CHANGE id idd INT(10);
修改表名字：
语法：1
ALTER TABLE 原表名称 rename 想改成的表名称;
alter table test rename testt;
语法：2
RENAME TABLE 原表名称 TO 想改成的表名称;
RENAME TABLE test TO testt;
修改字段类型：（一般不修改字段类型）
如varchar 修改成 int
Update 表名称 set 要修改的字段 = null //一定要赋值为null 不能是空字符串
alter table 表名称modify column 修改的字段名称 要修改成的字段类型(长度) ;
update user set name=null
alter table user modify column name int(20) ;
增加字段
alter table 表名 add字段 类型(长度);
alter table user add phone int(11);
删除字段
alter table 表名 drop 字段名
alter table user drop phone;
MySQL是一种关系型数据库管理系统，它支持多种数据类型，包括整数、浮点数、定点数、字符串、二进制和日期/时间类型。在本文中，我们将列出MySQL中所有的字段类型，并解释每种类型的含义、所占用的字节、存储范围、最大存储值以及使用场景。
整数类型包含(TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT)，适用于存储整数，如用户ID、年龄等。
浮点数类型(FLOAT、DOUBLE)，适用于存储小数，如商品价格、订单金额等。
定点数类型(DECIMAL)，适用于存储精度要求高的数值，如货币金额等。
字符串类型(CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT)，适用于存储文本数据，如用户名、地址等。
二进制类型(BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB)，适用于存储二进制数据，如图片、音频等。
日期和时间类型(DATE、TIME、DATETIME、TIMESTAMP)，适用于存储日期和时间相关的数据，如订单创建时间、用户注册时间等。
CHAR与VARCHAR的区别
1.CHAR是定长的，范围是0~255，char会开辟固定的长度，值超出范围将被截短，值小于范围的用空格填充。
2.VARCHAR是可变长度，范围0~65535，varchar是数据占多长就多长，不会来填补，但是会多一个字节来存储其字符的长度
3.它们之间的不同处在于MySQL数据库处理指示器的方式，CHAR把这个大小视为值的大小，在长度不足的时用空格补足。而VARCHAR类型把它是为最大值，并且只存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储值，所以短于指示器长度的VARCHAR类型不会被空格填补，但长于指示器的值仍然会被截短。
4.VARCHAR类型可以根据实际内容动态改变存储值的长度，因此在不能确定字段需要多少字符时使用VARCHAR类型可以有效地节约磁盘空间、提高存储效率。
5.CHAR的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；
6.VARCHAR的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。
SQL中的增删改查
增加：
insert into person values (1,"admin","13988888888","taiyuan");
insert into person (id) values (1);
insert into 表格名称 values (字段1,字段2....);
删除：
delete from person where id = 1; 指定条件删除
delete from person; 清空表格
修改：
update person set name = "admin" where id = 3;
update person set name = "admin001";
查询：
select * from person;
select * from person where id = 4;
数据库中的约束
在创建表格的过程中可以给某些字段追加约束条件
非空约束 NOT NULL
NK
create table t_user (
id int(3) not null,
username varchar(10),
password varchar(15)
);
唯一约束 UNIQUE
UK
create table t_user (
id int(3) not null,
username varchar(10) unique,
password varchar(15)
);
create table t_user (
id int(3) not null,
username varchar(10),
password varchar(15),
constraint UK_username unique(username)
);
主键约束 PRIMARY KEY
主键（PRIMARY KEY），也称“主键约束”。
MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可以强制表的实体完整性。主键主要是用于其他表的外键关联，以及本记录的修改与删除。
主键（PRIMARY KEY）是 MySQL 中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。
PK 效果上 == 非空约束+唯一约束
create table t_user (
id int(3),
username varchar(10),
password varchar(15),
constraint pk_id primary key(id)
);
联合主键
MYSQL是一个关系型数据库管理系统，其中联合主键是一种非常重要的数据库设计概念。在MYSQL中，联合主键是指使用多个列值来唯一标识一个记录的主键。一个联合主键可能包含两个或更多个字段，而这些字段一起组成了一个唯一标识符。这样可以保证在数据表中每个记录都有唯一的标识。
外键约束 FOREIGN KEY（选修）
一对一 一对多 多对多
FK 用到两张表格
t_user （一）
t_book （多）
关系维护在（多）方
| uid | Username | password |
| 1 | Lufei | lufei |
| 2 | Qiaoba | qiaoba |
| bid | Bookname | author | u_id（外键） |
| 1 | 笑傲江湖 | 金庸 | 1 |
| 2 | 神雕侠侣 | 金庸 | 2 |
| 3 | 鹿鼎记 | 金庸 | 2 |
| 4 | 连城诀 | 金庸 | 2 |
创建过程中分两步进行：
1. 指定哪一个字段是外键 foreign key(u_id)
2. 外键需要引用哪一个表格的字段 references t_user(id)
id int(3),
password varchar(15),
);
create table t_book (
bid int(3),
bookname varchar(6),
author varchar(3),
u_id int(3),
constraint pk_bid primary key(bid),
constraint fk_u_id foreign key(u_id) references t_user(id)
);
一级菜单 二级菜单 自关联
设计一张菜单表：
| id | name | url | oneid |
| 1 | 用户管理 | XXX | |
| 2 | 用户添加 | XXX | 1 |
| 3 | 用户删除 | XXX | 1 |
| 4 | 用户查询 | XXX | 1 |
| 5 | 用户修改 | XXX | 1 |
| 6 | 商品管理 | XXX | |
| 7 | 商品上架 | XXX | 6 |
| 8 | 商品下架 | XXX | 6 |
create table t_menu (
id int(3),
name varchar(10),
url varchar(20),
oneid int(3),
constraint pk_id primary key(id),
constraint fk_oneid foreign key(oneid) references t_menu(id)
级联删除谨慎使用
bid int(3),
u_id int(3),
constraint fk_u_id foreign key(u_id) references t_user(id) on delete cascade
create table t_book (
bid int(3),
bookname varchar(6),
ahthor varchar(3),
u_id int(3),
constraint pk_bid primary key(bid),
constraint fk_u_id foreign key(u_id) references t_user(id) on delete set null
);
检查约束 CHECK
在一个范围中间
create table test32(
id number primary key,
age number check(age>0 and age
);
数据库三范式
第一范式(1NF)
· 第一范式主要是保证数据表中的每一个字段的值必须具有原子性，也就是数据表中的每个字段的值是不可再拆分的最小数据单元
第二范式(2NF）
· 第二范式要求在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的，而且所有的非主键字段，都必须完全依赖主键，不能只依赖主键的一部分。
第三范式(3NF）
· 第三范式建立在已经满足第二范式的基础上
· 数据表中的每一个非主键字段都和主键字段直接相关
查询机制
emp员工表
| 字段 | 描述 | 类型 |
| empno | 员工编号 | mediumint |
| ename | 员工姓名 | varchar |
| job | 职位 | varchar |
| mgr | 领导编号 | mediumint |
| hiredate | 入职日期 | date |
| sal | 工资 | decimal |
| comm | 奖金 | decimal |
| deptno | 部门编号 | mediumint |
dept部门表
| 字段 | 描述 | 类型 |
| deptno | 部门编号 | mediumint |
| dname | 部门名称 | varchar |
| loc | 部门位置 | varchar |
salgrade工资等级
| 字段 | 描述 | 类型 |
| grade | 等级 | mediumint |
| losal | 最低工资 | decimal |
| hisal | 最高工资 | decimal |
简单查询
查询所有员工信息
SELECT * from emp;
查询所有并起别名
SELECT empno '员工编号',ename '员工姓名',job '职位',mgr '领导编号' from emp;
SELECT e.empno,e.job,e.sal from emp e;
查询公司中的职位
SELECT DISTINCT job from emp;
DISTINCT ：去重关键字
限定查询
| SELECT * FROM 表名称 [限定条件] Where > < >= |
查询工资高于1500的所有员工信息
SELECT * from emp WHERE sal > 1500;
查询工资高于1500低于3000的所有员工信息
SELECT * from emp WHERE sal >= 1500 and sal
//between...and...取的区间值
SELECT * from emp WHERE sal BETWEEN 1500 and 3000;
查询没有奖金的所有员工信息 or或者
SELECT * from emp where comm = 0 or comm is NULL;
查询有奖金的员工信息
SELECT * from emp where comm > 0;
查询员工姓名中带有S的员工信息
SELECT * from emp where ename like '%S%';
模糊匹配：
%:占位符 代表0个或者多个字符
_：占位符 代表一个字符
查询入职日期在1981年的员工
SELECT * from emp where hiredate BETWEEN '1981-01-01' and '1981-12-31';
SELECT * from emp where hiredate LIKE '%1981%';
查询员工编号在7499 7521 7566 in包含
SELECT * from emp where empno in (7499,7521,7566);
排序查询
| SELECT * FROM 表名称 [限定条件][排序条件] Order by 字段 升序/降序 升序 asc 降序 desc |
根据工资由高到底进行排序查询
SELECT * from emp order by sal desc;
如果工资一样，那么按照入职日期升序
多表查询
查询所有员工的详细信息（包含部门信息）
select * from emp,dept; （有问题的查询）
带来的问题是，简单对两张表做乘积处理，形成大量冗余数据，这种现象叫做笛卡尔积效应
需要追加两张表的关联条件，从显示上消除笛卡尔积效应
select * from emp,dept where emp.deptno = dept.deptno;
查询员工编号，员工姓名，员工职位，员工工资，部门名称，部门位置
逐步分析：
先确定需要的表
emp e,dept d
确定需要的字段
e.empno,e.ename,e.job,e.sal,d.dname,d.loc
确定关联条件
e.deptno = d.deptno
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
查询所有的员工编号，员工姓名，员工职位，领导姓名，领导职位
先确定需要的表
emp e1,emp e2
确定需要的字段
e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.job 领导职位
确定关联条件
e1.mgr = e2.empno
select e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.job 领导职位 from emp e1,emp e2 where e1.mgr = e2.empno;
发现少一条记录，如果要解决这个问题，需要借助连接查询
连接查询
| SELECT * FROM 表名称 [连接查询][限定条件][排序条件] 左（外）连接 left (outer) join...on 条件 右（外）连接 right (outer) join...on 条件 |
查询所有的员工编号，员工姓名，员工职位，领导姓名，领导职位
select e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.job 领导职位 from emp e1 left join emp e2 on e1.mgr = e2.empno;
分组查询
分组前提：需要分组的列出现了重复数据
| SELECT * FROM 表名称 [连接查询][限定条件][分组查询][排序条件] Group by 字段 |
语法限制：
1. 一旦出现分组，那么select后边只允许出现统计函数以及分组字段
2. 统计函数可以单独使用
3. 如果出现统计函数的嵌套，那么select后边只允许出现统计函数
求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
select emp.deptno,dept.dname,avg(sal) from emp,dept where emp.deptno = dept.deptno group by emp.deptno,dept.dname;
通过右连接将没有展示的数据进行展示：
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno = dept.deptno group by emp.deptno,dept.deptno,dept.dname;
展示平均工资高于2000的部门信息
错误写法：
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno = dept.deptno group by emp.deptno,dept.deptno,dept.dname where avg(sal) > 2000;
解决办法：
如果在分组之后需要再次筛选，不可以使用where,需要使用Having
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno = dept.deptno group by emp.deptno,dept.deptno,dept.dname having avg(sal) > 2000;
HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异：
1.一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
2.WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
3.WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
4.WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
5.WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。
单行函数查询
在MySQL中，单行函数是一种对单个输入值执行操作的函数。它接受一个输入参数并返回一个值。单行函数可以用于处理数据，并在查询中进行数据转换、计算、日期和时间处理等操作。
以下是MySQL中常见的单行函数示例：
字符串函数：
CONCAT：连接两个或多个字符串。
SUBSTRING：返回字符串的子串。
LENGTH：返回字符串的长度。
LOWER：将字符串转换为小写。
UPPER：将字符串转换为大写。
数值函数：
ABS：返回数值的绝对值。
CEIL：返回大于或等于给定数值的最小整数。
FLOOR：返回小于或等于给定数值的最大整数。
RAND：返回一个随机数。
日期和时间函数：
CURDATE：返回当前日期。
CURTIME：返回当前时间。
NOW：返回当前日期和时间。
DATE_FORMAT：按照指定格式格式化日期或时间。
条件函数：
IF：根据条件返回不同的值。
CASE：根据条件执行不同的操作。
其他函数：
MD5：计算字符串的MD5哈希值。
SHA1：计算字符串的SHA1哈希值。
REVERSE：反转字符串的顺序。
TRIM：删除字符串开头和结尾的空格或指定字符。
子查询
Where子查询 单行单列 多行单列
From子查询 多行多列
查询比SMITH工资高的人
select * from emp where sal > (select sal from emp where ename = 'SMITH');
查询比平均工资高的人
select * from emp where sal > (select avg(sal) from emp);
查询和经理工资相同的员工信息
select * from emp where sal in (select sal from emp where job = 'MANAGER');
查询部门编号，部门位置，部门人数，部门平均工资
第一步：查询每个部门的编号，名称，位置
Dept d
第二步：统计部门人数，平均工资
emp e
e.deptno deptno, count(e.empno) num,avg(e.sal) sal
Group by e.deptno
select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno;
将查到的数据和dept组合
select temp.deptno,temp.num,temp.sal,d.dname,d.loc from (select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno) temp,dept d where temp.deptno = d.deptno;
通过右连接处理边界数据
select d.deptno,IFNULL(temp.num,0) 部门人数 ,IFNULL(temp.sal,0) 平均工资 ,d.dname,d.loc from (select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno) temp right join dept d on temp.deptno = d.deptno;
IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
分页查询
LIMIT 关键字 参数 参数1为索引值从哪开始，第二个参数为每页显示几个
SELECT * FROM product LIMIT 30;// 从0开始，显示30条
SELECT * FROM product LIMIT 10,10;//从10开始 显示10条
由此，我们可以分析出分页查询的SQL语句格式
select * from my_book limit (page-1)*pageSize,pageSize;
其中，page是页码，pageSize是每页显示的条数
当数据库中数据量较大时，使用limit函数会存在性能问题，我们可以用主键来代替limit函数实现数据分页查询
select * from my_book where id > (page-1)*pageSize limit pageSize;
select * from my_book where id > 10 limit 5;
事务处理
概念：在一个业务逻辑中包含多个步骤操作，需要被事务进行管理，要么同时成功，要么同时失败
A -500
//异常
B+500
默认提交事务
关闭默认提交事务：set autocommit = 0;
手动提交：commit
手动回滚：rollback
开启默认提交事务：set autocommit = 1;
事务的四大特性（掌握）
1、原子性：事务包含的所有数据库操作要么全部成功，要不全部失败回滚
2、一致性：一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
3、隔离性：一个事务未提交的业务结果是否对于其它事务可见。级别一般有：read_uncommit，read_commit，read_repeatable，串行化访问。
4、持久性：一个事务一旦被提交了，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。（持久化操作）
事务的隔离级别（了解）
Read uncommitted 读未提交
产生问题：脏读、不可重复读、幻读
提供了事务建最小限度的隔离。顾名思义，就是一个事务可以读取另一个未提交事务的数据。
示例：小明去商店买衣服，付款的时候，小明正常付款，钱已经打到商店老板账户，但是小明发起的事务还没有提交。就在这时，商店老板查看自己账户，发现钱已到账，于是小明正常离开。小明在走出商店后，马上回滚差点提交的事务，撤销了本次交易曹邹。
结果：小明未付钱买到了衣服，商店老板实际未收到小明的付款。
分析：商店老板查看自己的资金账户，这个时候看到的是小明还没有提交事务的付款。这就是脏读。
注意：处于该隔离级别的事务A与B，如果事务A使用事务B不提交的变化作为计算的基础，然后哪些未提交的变化被事务A撤销，这就导致了大量的数据错误变化。
Read committed 读已提交（Oracle数据库默认的隔离级别）
产生问题：不可重复读、幻读
处于Read committed （读已提交）级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的同一sql在其他事务执行前后返回的是不同的结果。一个事务要等另一个事务提交后才能读取数据。
示例：小明卡里有1000元，准备与几个朋友聚餐消费，消费1000元，当他买单时（事务开启），收费系统检测到他卡里有1000元。就在检测完毕的时候，小明女朋友发现小明有私房钱，全部转走并提交。当收费系统准备扣款时，再检查小明卡里的金额，发现已经没钱了，付款不成功。小明此时就会很纳闷，明明有钱的呀，钱呢？
分析：该示例中同一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。该隔离级别可以解决脏读问题。
Repeatable read 可重复读（Mysql数据库默认的隔离级别）
产生问题：幻读
在开始读取数据（事务开启）时，不再允许修改操作
示例：还是小明有1000元，准备跟朋友聚餐消费这个场景，当他买单（事务开启）时，收费系统检测到他卡里有1000元，这个时候，他的女朋友不能转出金额。接下来，收费系统就可以扣款成功了，小明醉醺醺的回家，准备跪脱衣板。
分析：重复读可以解决不可重复读的问题，这句话有些别扭，大家可以仔细品一下。
写到这里，大家可能会产生疑问，什么情况下产生幻读呢？
示例来了：
小明在公司上班，女朋友告诉他，拿着他的卡去逛街消费。花了一千元，然后小明去查看他银行卡的消费记录（事务开启），看到确实是花了一千元。就在这个时候，小明女朋友又花三千元买了一些化妆品和衣服，即新增了一些消费记录。当小明打印自己银行卡消费记录单的时候（女朋友事务提交），发现花了四千元，似乎出现了幻觉，小明很心疼。这就是幻读
Serializable 串行化
解决所有问题
隔离级别越高，性能越低
查询数据库的默认隔离级别：
select @@tx_isolation;
改变数据库的隔离级别：
修改全局
set global transaction isolation level 隔离级别关键字;
修改回话
//设置read uncommitted级别：
set session transaction isolation level read uncommitted;
//设置read committed级别：
set session transaction isolation level read committed;
//设置repeatable read级别：
set session transaction isolation level repeatable read;
//设置serializable级别：
set session transaction isolation level serializable;
开启事务：
Start transaction
Update person set sal = sal - 500 where id = 1;
Update person set sal = sal + 500 where id = 2;
标签：复习,sal,数据库,基础,查询,dept,emp,deptno,id From： https://blog.csdn.net/weixin_68489989/article/details/140272145