数据库基础
数据库相关的概念
DB
数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据
DBMS
数据库管理系统（Database Management System）数据库是通过DBMS创建和操作的容器，用户写入客户端程序通过DBMS对数据库进行相关操作。
常见的数据库管理系统：MySQL、Oracle、SqlServer等
SQL
结构化查询语言（Structure Query Language）:专门用来与数据库通信的语言。
数据库特点
1、整体数据结构化
2、数据的共享度高
3、数据的独立性高
4、高度的数据控制能力
MySQL的常见命令
1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名;
3.查看当前库的所有表
show tables;
4.查看其他库的所有表
show tables from 库名；
5.创建表
create table 表名（
列名 列类型，
列名 列类型，
……
）；
6.查看表结构
desc 表名；
DQL语言的学习
基础查询
语法：
select 查询列表 from 表名；
特点：
1.查询列表可以是表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟表格
例：
1.查询表中的单个字段
selsct name from students
2.查询表中多个字段
select id,name,class from students;
3.查询表中所有字段
select * from student;
4.查询常量值
select 100;
select '张三';
5.查询表达式
select 1+1;
6.查询函数
select version();
7.起别名
如果要查询的字段有重名，使用别名可以区分字段
方式一：
select 1+1 as sum;
select name as 姓名 from students;
方式二：
select name 姓名 from students;
如果别名中有特殊符号，需使用""
8.去重
查询学生表中的所有班级
select distinct classfrom students;
条件查询
语法：
select 查询列表 from 表名 where 条件；
分类：
1.按条件表达式筛选
| 条件运算符 | 含义 |
| > | 大于 |
| < | 小于 |
| = | 等于 |
| != | 不等于 |
| <> | 不等于 |
| >= | 大于等于 |
| <= |
小于等于 |
2.按逻辑表达式
| 逻辑运算符 | 含义 |
| and | 与（条件都为true,返回结果为true,反之，为false） |
| or | 或（条件都为false,返回结果为false,反之，为true） |
| not | 非 |
3.模糊查询
| 运算符 | 含义 |
| like |
搜索某种形式，通常搭配通配符使用如%（任意多个字符）,_（任意单个字符） |
| between and | 在某个范围内 |
| in | 某个列的多个可能值 |
| is null | 不为空 |
例：
1.查询成绩大于90的学生信息
select * from students where grade>90;
2.查询成绩不为60的学生姓名和班级
select name,class from students where grade<>60;
3.查询成绩在80和100之间的学生的信息
select * from students where grade>=80 and grade <=100;
4.查询成绩不在80到100之间或者成绩大于60的学生信息
方法1
select * from students where grade<80 or grade>100 or grade>60;
方法2
select * from students where not(grade>=80 and grade<=100) or grade>60;
方法3
select * from students where not(gradebetween 80 and 100) or grade>60;
5.查询姓张的学生信息
select * from students where name like '张%';
6.查询姓张并且姓名是两个字的学生信息
select * from students where name like '张_';
7.查询成绩为88,92,100的学生的信息
select * from students where grade in (88,99,100);
排序查询
语法：
select 查询列表 from 数据表 where 查询条件 order by 排序列表 asc|desc;
asc代表升序，desc代表降序
默认是升序
例：
1.查询学生信息，按成绩从高到低排序
select * from students order by grade desc;
2.查询姓张的学生的信息，按成绩从高到低排序
select * from students where name like '张%' order by grade desc;
3.按总分由高到底显示学生的信息和总分
select *,math+English+Chinese 总分 from students order bymath+English+Chinese desc;
或者
select *,math+English+Chinese 总分 from students order by 总分 desc;
4.按姓名长度显示学生的姓名和成绩（按函数排序）
select name,grade from students order by length(name) desc;
5.按成绩和id显示学生信息
select * from students order by grade desc,id asc;
常见函数
调用：
select 函数名(实参列表) from 表；
分类：
1.单行函数
如 concat、length、ifnull等
2.分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数
一.单行函数
（一）字符函数
1.length函数：获取参数值的字节长度
select length('sql');
2.concat函数：拼接字符串
select concat('abc','+','bcd');输出abc+bcd
3.upper/lower函数：将小/大写字母变换为大/小写
select upper('abc');
select lower('ABC');
4.substr（substring）函数：截取字符串
注意：SQL中的索引都是从1开始
select substr('abcde',3);# 输出cde
#截取从1开始长度为3的字符
select substr('abcde',1,3); # 输出abc
两个字符首字母大写，然后进行拼接
select concat(upper(substr('abc',1,1)),upper(substr('bcd',1,1)));# 输出AbcBcd
5.instr函数：返回子串在字符串中的起始索引
select instr('abc','b');# 输出2
6.trim函数：去除前后空格或字符
select trim(' 张三 ');# 输出张三
select trim('a' from 'aaaa张aaa三aaaa'); # 输出张aaa三
7.lpad/rpad函数：用指定的字符实现左/右填充得到指定长度的字符串
select lpad('张三',5, '!');# 输出！！！张三
8.replace函数：替换指定的字符
select replace('abcda','a','e');# 输出 ebcde
（二）.数学函数
1.round 四舍五入
select round(1.67);#输出2
select round(1,674,2);#输出1.67
2.ceil 向上取整，返回>=该参数的最小整数
select ceil(1.2);#输出2
3.floor 向下取整，返回<=该参数的最大整数
select floor(1.2);#输出1
4.truncate 截断
select trunate(1.66,1);#输出1.6
5.mod 取模（被除数为负，结果就为负）
select mod(10,3);#相当于10%3，输出1
（三）.日期函数
1.now 返回当前系统日期+时间
select now();
2.curdate 返回当前系统日期，不包含时间
select curdate();
3.curtime 返回当前的时间
select curtime();
4.获取指定的部分，如年、月、日、小时、分钟、秒
select year(now());
select month(now());#返回中文的月份
select monthname(now());#返回英文的月份
5.str_to_date 将字符通过指定的格式转换成日期
select str_to_date('4-3 2022','%c-%d %Y");#输出2022-04-03
6.date_format 将日期转换成字符
select date_format(now(),'%y年%m月%d日');#输出22年04月18日
（四）.其他函数
version() 版本号
database() 当前的数据库
user() 当前用户
（五）.流程控制函数
1.if函数：if else的效果
select id,name,if(grade>=60,'及格','不及格') from students;#输出id，姓名和及格情况
2.case函数：switch case是效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
……
else 要显示的值n或语句n
end
例:
SELECT NAME 姓名,
CASE grade
WHEN 90 THEN '优秀'
WHEN 80 THEN '良好'
ELSE '一般'
END AS 成绩
FROM students;
3.case函数的第二种用法：类似于多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n或语句n
end
例：
SELECT NAME 姓名,
CASE
WHEN grade>=90 THEN '优秀'
WHEN grade between 70 and 80 THEN '良好'
ELSE '一般'
END AS 成绩
FROM students;
二.分组函数
分类：sum 求和、avg 平均值、max 最大值、min最小值、count 计算非空个数
1.简单使用
select avg(grade) from students;
2.支持是参数类型
sum、avg一搬处理数值型
max、min、count数字和字符都可以
3.count函数的详细介绍
select count(*) from students;#用来统计总行数
分组查询
语法：
select 分组函数，列（要求出现在group by的后面）
from 表
(where 筛选条件）
group by 分组的列表;
例：
（1）查询每个班的最高成绩
select class,max(grade)
from students
group by class;
（2）查询每个班级姓张的学生个数
select class,count(name)
from students
where name like '张%'
group by class;
（3）查询最高成绩大于80 的班级
select class,max(grade)
from students
group by class
having max(grade)>70;
（4）查询每个班级，每个成绩的人的个数
select class,garde,count(name)
from students
group by class,grade;
多表查询（连接查询）
语法：
select 查询列表
from 表1 别名 连接类型
join 表2 别名
on 连接条件
(where 筛选条件);
分类：
1.按功能分类：
（1）内连接：inner（inner可以省略）
语法
select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件;
等值连接
例：查询每个科目对应的任课教师
select subject.name,teachers.name
from subject inner join teachers
on subject.'name'='teaching';
非等值连接
例：查询每个学生的成绩等级
select name,grade,grade_level
from students s inner join stu_grade g
on s.'grade' between g.'grade_low' and g.'grade_high';
自连接
（2）外连接：
应用场景：查询一个表中有，另一个表中没有的记录
特点：外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
左外连接：left （outer）（left join左边的是主表）
例：查询不授课的老师的信息
select t.*
from teacher t
left outer join class c
on t.teaching=c.name
where c.name=null;
右外连接：right（outer）（right join右边的主表）
例：查询不授课的老师的信息
select t.*
from class c
right outer join teacher t
on t.teaching=c.name
where c.name=null;
全外连接：full（outer）:结果=内连接的结果+主表中有的而从表中没有+从表中有的而主表中的没有的
（3）交叉连接：cross
子查询
含义：出现在其他语句的中select语句，称为子查询或内查询
分类：
按子查询出现的位置：
select后面：只支持标量子查询
from后面：支持表子查询
where或having后面：标量子查询（单行）、列子查询（多行）、行子查询
exists后面（相关子查询）：表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
一、where或having后面
特点：
1.子查询放在小括号内
2.子查询一般放在条件的右侧
3.标量子查询一般搭配着单行操作符使用
列子查询一般搭配着多行操作符使用
4.子查询的执行优先于主查询，主查询的条件用到了子查询的结果
分类：
1.标量子查询（单行子查询）
例：
（1）谁的成绩比张三的高
select * from studennts
where grade>(
select grade from students
where name='张三'
);
（2）查询与张三通一个班，单成绩比张三低的学生姓名与成绩
select name ,grade from students
where class=(
select class from students
where name='张三'
) and grade<(
select grade from students
where name='张三'
);
（3）查询成绩最低的学生的姓名和成绩
select name,grade from students
where grade=(
select min(grade) from students
);
（4）查询最低成绩大于1班最低成绩的班级和最低成绩
select min(grade),class from students
group by class
having min(grade)>(
select min(grade) from students
where class='1班'
);
2.列子查询（多行子查询）
| 操作符 | 含义 |
| in/not in | 等于列表中的任意一个 |
| any/some | 和子查询返回的某一个值比较 |
| all | 和子查询返回的所有值比较 |
例：
1.返回学生姓名是1班或3班中的所有学生id
select id from students
where name in (
select name from stuednts
where class in ('1班','3班')
);
3.行子查询（多行多列）（不常用）
例：查询id号最小，成绩最大的学生信息
select * from students
where (id, grade)=(
select min(id),max(grade) from students
);
二、select后面
例：查询每个班级的学生个数
select (
select count(*) from students s
where s.class=stu.class
) 个数
from students stu;
三、from后面
例：查询每个班级的平均成绩的等级
select avg_grade.*,g.level
from (
select avg(grade) av,class
from students
group by class
) avg_grade
inner join grade_level g
on avg_grade.av between lower and high;
四、exists后面
语法：exists（完整的查询语句）
结果：1/0
例：
查询有授课的老师姓名
select name from teachers t
where exists(
select * from subjects s
where t.teaching=s.name
);
分页查询
应用场景：当要显示的数据一页显示补全时，需要分页提交SQL请求
语法：
select 查询列表 from 表
（连接类型 join 表2 on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选条件
order by 排序字段）
limit 要显示内容的起始索引（从0开始）,要显示的条数
特点：
每页的起始索=（页数-1）*每页的条数
例：
1.查询前5条学生信息
select * from students limit 0,5;
2.查询第11条到第25条学生信息
select * from students limit 10,15;
3.查询成绩排名前10 名的学生信息
select * from students
order by grade desc
limit 10;
联合查询
union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1 union 查询语句2……
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1.要求多条查询语句的查询列表数是一致的
2.要求多条查询语句查询的每一列的类型和顺序最好一致
3.union关键字默认去重，如果使用union all可以包含重复项
DML语言
插入语句
语法：
方式一
insert into 表名（列名，……）values（值1，……）;
方式二
insert into 表名 set 列名=值，列名=值，……
注意：
1.插入的值的类型要与列的类型一致或兼容
2.列数和值的个数必须一致
3.可以省略列名，默认所有列，而且列的顺序与表中列的顺序一致
修改语句
1.修改单表记录
语法：
update 表名 set 列=新值，列=新值……
where 筛选条件;
例：
修改学号为10的学生的成绩为90
update students set grade=90
where id=10;
2.修改多表记录
语法：
update 表1 别名
连接类型 join 表2 别名
on 连接条件
set 列=新值，……
where 筛选条件
删除语句
方式一：delete
1.单表的删除
语法：
delete from 表名 where 筛选条件
例：删除没有成绩的学生信息
delete from students where grade is null;
2.多表的删除
语法：
delete 表1，表2
from 表1
连接类型 join 表2 on 连接条件
where 筛选条件
例：删除体育课的教师信息
delete t
from teacher
inner join subject s on t.teachingId=s.id
where s.name='体育';
方式二：truncate（清空整个表中数据）
语法：
truncate table 表名;
delete与truncate的区别
1.假如要删除的表中有自增长列，如果delete删除后，再插入数据，自增长列的值从断点开始，而truncate删除后，再插入数据，自增长列的值从1开始
2.delete删除可以返回受影响的行数，truncate删除没有返回值
3.truncate删除不能回滚，delete删除可以回滚
DDL语言的学习（数据定义语言）
库和表的管理
库的管理
1.库的创建
语法：
create database (if not exists) 库名;
例：
create database if not exists students;#如果学生库不存在则创建学生库
2.库的修改
更改库名
rename database 库名 to 新库名;
更改库的字符集
alter database 库名 character set 字符集;
3.库的删除
drop database 库名;
表的管理
1.表的创建
create table 表名(
列名 类型 约束,
列名 类型 约束,
列名 类型 约束,
……
列名 类型 约束
);
常见的数据类型
可以通过unsigned设置无符号
（1）数值型
| 分类 | 字节 | 范围 |
| tinyint | 1 |
有符号：-128~127 无符号：0~255 |
| smallint | 2 |
有符号：-32768~32767 无符号：0~65535 |
| mediumint | 3 |
很大 |
| int | 4 |
很大 |
| bigint | 4 |
很大 |
（2）小数
| 分类 | ||
| 浮点型 |
float(M,D) |
M,D可以省略 |
| double(M,D) | M,D可以省略 | |
| 定点型（精度较高） | dec(M,D) | M,D可以省略，则M默认10，D默认0 |
| decimal(M,D) | M,D可以省略，则M默认10，D默认0 |
M：整数部位+小数部位的总长度
D：小数部位的长度
超过范围，则插入临界值
（3）字符型
| 分类 | 特点 | |
| 较短的文本 | char | 固定长度的字符 |
| varchar | 可变长度的子符 | |
| 较长的文本 | text | 处保存较长的文本 |
| blob | 保存较大的二进制 |
（4）日期类型
| 类型 | 字节 | 特点 |
| date | 4 | 只保存年月日，如2022-01-01 |
| datetime | 8 |
年月日+时间，如2022-01-01 00:00:00，支持的时间范围较大 |
| timestamp | 4 | 年月日+时间，支持的时间范围较小，容易受时区等影响 |
| time | 3 | 只保存时间 |
| year | 1 | 只保存年 |
常见约束
1.分类：六大约束
not null 非空，用于保证该字段的值不为空
default 默认，用于保证该字段有默认值
primary key 主键，用于保证该字段的值具有唯一性，并且非空，一个表中至多有一个
unique 唯一，用于保证该字段的值具有唯一性，可以为空
check 检查约束（mysql中不支持）
foreign key 外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值。在从表中添加外键约束，用于引用主表中的某列的值。主表的关联列必须是一个key（一般是主键或唯一）
2.添加分类：
（1）列级约束：六大约束语法上都支持，但外键约束没有效果
例：
CREATE TABLE students(
id INT PRIMARY KEY,
NAME VARCHAR(20) NOT NULL,
gender CHAR(1) CHECK(gender='男' OR gender='女'),
seat INT UNIQUE,
age INT DEFAULT 18,
majorid INT REFERENCES major(id)
);
（2）表级约束：除了非空、默认，其他的都支持
语法：
（constraint 约束名） 约束类型（字段名）
3.修改表时添加约束
（1）添加列级约束
alter table 表名 modify column 字段名 类型 约束;
（2）添加表级约束
alter table 表名 add（constraint 约束名）约束类型（字段名）;
2.表的修改
（1）修改列名
alter table 表名 change column 列名 新列名 类型;
（2）修改列的类型或约束
alter table 表名 modify column 列名 新类型 约束;
（3）添加列
alter table 表名 add column 列名 类型 （first|after 字段名）;
（4）删除列
alter table 表名 drop column 列名;
（5）修改表名
alter table 表名 rename to 新表名;
3.表的删除
drop table 表名;
4.表的复制
（1）只复制表的结构
create table 表名 like 已有的表;
（2）复制表的结构+数据
create table 表名 select 需要复制的列 from 已有的表名;
只复制部分字段
create table 表名 select 需要复制的字段
from 已有的表名 where 0;
标识列（自增长列）
特点：
1.必须与key搭配
2.一个表至多可以有一个
3.标识列的类型只能是数值型
分类：
1.创建表时设置标识列
CREATE TABLE tab_identity(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
2.修改表时设置标识列
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
TCL（事务控制语言）
事务
1.事务：一个或一组SQL语句组成一个执行单元。这个执行单元要么全部执行，要么全部不执行。
2.事务的属性
（1）原子性
指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生
（2）一致性
事务必须使数据库从一个一致性状态变换到另外一个一致性状态
（3）隔离性
一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
（4）持久性
一个事务一旦被提交，他对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响
3.事务的创建
（1）隐式事务：事务没有明显的开启和结束标记，比如insert、update、delete语句
（2）显式事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit=0;
步骤1：开启事务
set autocommit=0;
start transaction;#可选的
步骤2：编辑写事务中的SQL语句（select insert update delete）
步骤3：结束事务
commit;#提交事务
rollback;#回滚事务
4.事务的隔离级别
| 级别 | 脏读 | 不可重复度 | 幻读 |
| read uncommitted | 不隔离 | 不隔离 | 不隔离 |
| read committed | 隔离 | 不隔离 | 不隔离 |
| repeatable read | 隔离 | 隔离 | 不隔离 |
| serializable | 隔离 | 隔离 | 隔离 |
mysql中默认的是第三个隔离级别repeatable read
Oracle中默认的是第二个隔离级别read committed
查看隔离级别
select @@tx_isolation;
设置隔离级别
set session|global transaction isolation level 隔离级别；
5.savepoint的使用
set autocommit=0;
start transaction;
delete from students where id=25;
savepoint a;#设置保存点
delete from students where id 29;
rollback to a;#回滚到保存点
最终结果是表中id=25号的行被删除了，id=29的删除后又被恢复
视图
视图：一种虚拟存在的表，行和列的数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的，只保存SQL逻辑，吧保存查询结果
1.应用场景：
--多个地方用到同样的查询结果
--该查询结果使用的SQL语句比较复杂
2.创建视图
语法：
create view 视图名 as 查询语句;
例：
查询个班级的平均成绩级别
(1)创建视图查看每个班级的平均成绩
create view avg_grade
as
select avg(grade) ag,class from students
group by class;
(2)使用视图
select a.*,g.grade_level from avg_grade a
join grades g
on a.ag between g.low and g.high;
3.视图的好处
--重用SQL语句
--简化SQL操作
--保护数据，提高安全性
