一. 初识数据库
1.1 什么是数据库
要学习数据库首先要先搞清楚三个概念
- 数据库(DB)：是存储数据的仓库
- 数据库管理系统(DBMS)：管理数据库的大型软件
- SQL：通过SQL操作数据库管理系统操作数据库，对数据库进行增删改查等
由此我们可以知道数据库就是安装在操作系统之上的数据仓库，用于存储数据。
1.2数据库分类
我们也先认识一个概念->关系型数据库
关系型数据库：通过二维表对数据进行存储的数据库
数据库分为关系型数据库和非关系型数据库
- 关系型数据库SQL(Structured Query Language)：Oracle、MySQL、Sql Serves、DB2、SQLlite
- 非关系型数据库NoSQL(Not Only SQL)：Redis、MongoDB
1.3如何下载和安装MySQL
通过MySQL的官网进行下载：点击跳转MySQL
这里需要注意设置登录密码(尽量简单)
下载好之后再进行环境的配置就可以通过命令行启动MySQL
最后将安装的MySQL路径添加进去即可
二. SQL语句
SQL语句有四大分类，并且不区分大小写，接下来我们就逐一进行介绍
- DDL：数据定义语言，用来定义数据库对象
- DML：数据操控语言，用来对数据库中的表进行增删改查
- DQL：数据查询语言，用来查询数据库中表的记录
- DCL：数据控制语言，用来控制数据库用户，控制数据库的访问权限
2.1 DDL
数据库操作
- 查询所有数据库-->show databases;
- 查询当前数据库-->select database();
- 创建数据库-->create database [if not exists] 数据库名;
- 删除数据库-->drop database [if exists] 数据库名;
- 使用数据库-->use 数据库名;
表操作
表操作的前提是你已经[use 数据库名]了
-
查看当前数据库的所有表-->show tables;
-
查询表结构-->desc 表名;
-
查看表的建表语句-->show create table 表名;
-
创建表-->create table 表名(字段1 字段1类型[comment 字段1注释]，
字段2 字段2类型[comment 字段2注释]，
字段3 字段3类型[comment 字段3注释]，
...
字段n 字段n类型[comment 字段n注释]
)[comment 表注释]；
接下来我创建一个员工表并且演示一些相关操作
查看当前所有数据库
创建一个work数据库，并且查看所有表和表结构发现都是空
创建一个员工表
查看表结构
查看建立表语句
数据类型
- 数值类型
- 字符串类型
其中需要注意的是char 和 varchar类型的区别
- char：定长字符串就是定死的长度，如果你设置的是10个字符，那么就算使用的只有一个字符那么也需要使用10个字符的资源
- varchar：变长字符串，会根据使用的字符长度进行变化，如果设置10个字符，只需要用1个字符的长度，那么就会计算出来并且只使用1个字符的资源
所以char的效率是比varchar高的，因为varchar多了需要计算的一步操作。
- 时间和日期类型
表操作--修改
- 添加新字段
alter table 表名 add 字段名 类型(长度) [comment 注释];
- 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释];
- 删除字段
alter table 表名 drop 字段名;
- 修改表名
alter table 表名 rename to 新表名;
- 删除表
drop table [if exists] 表名;
2.2 DML
数据操作语言，用来对数据进行增删改的操作
添加数据
- 给指定字段添加数据-->insert into 表名(字段1，字段2，...) values(值1，值2，...);
- 给全部字段添加数据-->insert into 表名 values(值1，值2，....);
- 批量给指定字段添加数据-->insert into 表名(字段1，字段2，...) values(值1，值2，...),(值1，值2，..)... ;
- 批量给全部字段添加数据-->insert into 表名 values(值1，值2，..),(值1，值2，..)... ;
需要注意的是：
添加数据时，第一个值对应第一个字段，顺序必须一一对应。
当添加的字段值数据类型为字符串类型和时间日期类型时，需要在引号中。
插入的数据大小需要在字段规定范围内。
先使用后面内容查看一下表
修改数据
update 表名 set 字段1 = 值1，字段2 = 值2，...[where 条件];
需要注意的是这里的where条件可以没有，如果没用那么修改的就是整个表的数据
删除数据
delete from 表名 [where 条件]
和修改数据一样，这里的where条件可以没有，如果没有那么就是删除整个表的数据
2.3 DQL
基础查询
- 查询多个字段-->select 字段1，字段2，... from 表名;
- 查询全部字段-->select * from 表名
- 设置别名-->select 字段1 [as 别名1],字段2 [as 别名2],... from 表名;
- 去除重复记录-->select distinct from 表名;
条件查询
select 字段列表 from 表名 where 条件列表;
条件有以下这些
还有and、or、||、&&。
其中like模糊查询中 _ 为一个字符 % 为任意字符。
聚合函数
聚合函数是将一列作为一个整体，进行纵向计算的。
常见聚合函数有
- count 统计数量
- max 最大值
- min 最小值
- avg 平均值
- sum 求和
select 聚合函数(字段列表) from 表名;
需要注意的是，进行聚合函数计算时，表中所有的null值是不参与聚合函数计算的。
分组查询
select 字段名 from 表名 [where 条件] group by 分组字段名 [having 分组过滤条件];
需要注意的是，where和having都为条件，那么他们有什么不同呢？
where是在分组之前进行条件筛选，而having是在分组之后进行过滤-->时机不同
where不能对聚合函数进行判断，而having可以
注意执行顺序 ： where>聚合函数>having
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2;
排序方式：
- ASC:升序
- DESC:降序
需要注意的是，如果有多字段排序，那么第一个字段相同时，才会根据第二个字段进行排序。
分页查询
select 字段列表 from 表名 limit 起始索引，查询记录数;
起始索引=(查询页码-1)*每页显示记录数
例如需要查询第二页数据，每页显示10条数据，那么代码为
select * from 表名 limit (2-1)*10,10;
DQL语句就是这几种，是不是有点小乱，接下来我们就来总结一下：
select 字段列表 from 表名列表 where 条件 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数。
第一步是执行from表明是在哪张表中，第二步是执行where指定查询的条件，第三步执行group by和having进行分组以及分组之后的条件，第四步执行select指明要查询的字段，第五步执行order by和limit
简单来记忆就是：条件、分组、查询、排序、分页
2.4 DCL
我们之前也说了，DCL是数据控制语言，用来控制数据库用户，控制用户访问权限的，接下来就具体看看代码
管理用户
- 查询用户-->use mysql; select * from user;
- 创建用户-->create user '用户名'@'主机名' identified by '密码';
- 修改用户密码-->alter user '用户名'@'，'主机名' identified with mysql_native_password by '新密码';
- 删除用户-->drop user '用户名'@'主机名';
权限控制
- 查询权限-->show grants for '用户名'@'主机名';
- 授予权限-->grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
- 撤销权限-->revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
三. 函数
函数是指一段可以直接被另一段程序调用的程序或代码。
字符串函数
- concat(s1,s2,....sn) 进行字符串拼接，将s1,s2,..sn拼接成一个字符串
- lower(str) 将字符串str全部转为小写
- upper(str) 将字符串str全部转为大写
- lpad(str,n,pad) 左填充，用字符串pad对str左边进行填充，直到达到n个字符
- rpad(str,n,pad) 右填充，用字符串pad对str右边进行填充，直到达到n个字符
- trim(str) 去掉字符串头部和尾部的空格(中间不去除)
- substring(str,start,len) 返回从字符串str从start位置起的len个长度的字符串
数值函数
- ceil(x) 向上取整-->大于x的最小整数
- floor(x) 向下取整-->小于x的最大整数
- mod(x,y) 返回x/y的模
- rand() 返回0~1随机数
- round(x,y) 求参数x四舍五入的值，保留y位小数
日期函数
- curdate() 返回当前日期
- curtime() 返回当前时间
- now() 返回当前的日期和时间
- year(date) 获取指定date的年份 (year(now());)
- month(date) 获取指定date的月份
- day(date) 获取指定date的日期
- date_add(date,interval_expr type) 返回一个指定日期加上一个时间为type的日期
- datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
流程控制函数
- if(value,t,f) 如果value为空，返回t，否则返回f
- ifnull(value1,value2) 如果value不为空，返回value1，否则返回value2
- case when [val1] then [res1]...else[default] end 如果val1为true，返回res1,..否则返回default默认值 (when then 可以写多个)
- case [expr] when [val1] then [res1]...else[default] end 如果expr的值等于val1，返回res1，否则返回default默认值(when then 可以写多个)
四. 约束
约束是作用在表中的字段上的，为了保证表中数据的正确、有效性和完整性。
约束是可以添加多个的，约束之间用空格隔开。
- not null 非空约束-->限制该字段的数据不能为null
- unique 唯一约束-->保证该字段的所有数据都是唯一的，不重复的
- primary key 主键约束-->主键是一行数据的唯一标识，要求非空且唯一
- default 默认约束-->保持数据时，如果没有指定该字段的值，那么就使用默认值
- check 检查约束-->保证字段满足某个条件
- foreign key 外键约束-->用来让两张表的数据之间建立连接，保证数据的一致性和完整性
外键约束
为什么需要外键约束?外键是用来建立两张表之间的联系，从而保证数据的一致性和完整性。
比如这个例子如果我们没有使用外键，那么在部门表删除了id为1的研发部后，员工表不会受到任何影响，但是此时很明显已经不符合我们的预期了。
若使用了外键，那么在进行同样的操作的时候，会报出错误，使我们无法删除，从而保证数据的一致性和完整性。
- 添加外键：
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
有外键的表一般我们叫作子表，而对应的表我们叫作夫表。
我们添加了外键之后，如何改变表中的数据呢，这就需要外键的删除/更新操作
- no action-->在夫表中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除和更新(默认)
- restrict-->在夫表中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除和更新(同no action)
- cascade-->当父类中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有，则也删除和更新外键在子表中的记录
- set null-->当父类中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有则设置子表中该外键的值为null(前提是允许为null)
- set default-->夫表有变更时，子表将外键列设置成一个默认的值(innodb不支持)
五.多表查询
在业务中，难以避免多张表产生联系，那么多表的关系又是怎么样的呢。
多表关系
- 一对多(多对一)
例如:部门和员工的关系，一个员工只能有一个部门，而一个部门可以有多个员工，那么我们就可以在多的一方设置外键，指向另一方的主键
- 多对多
例如:学生和课程的关系，一个学生可以有多个课程，一个课程页可以有多个学生，我们可以使用第三张表将其联系起来，新表至少使用两个外键
- 一对一
例如:用户和用户详情的关系，一对一的关系，多用于单表拆分，将基础字段放在一张表中，其他详情字段放在一张表中，以提升操作效率
注意，这里在任意一方加入外键关联另一张表的主键，并且设置外键约束为唯一的(unique)
多表查询-笛卡尔积
多表查询-->select * from 表1，表2;
笛卡尔积-->是指在数学中，两个集合，集合A和集合B的所有组合情况(在进行多表查询的时候要去除无效数据)-->加条件
内连接
内连接查询的是两张表交集的部分
- 显示内连接
select 字段列表 from 表1，表2 where 条件...;
- 隐式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
外连接
- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
左外连接是查询的左表所有的数据和左表和右表交集的数据
- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
右外连接是查询右表所有的数据和左边和右表交集的数据
自连接
select 字段列表 from 表1 别名1 join 表2 别名2 on 条件..;
自连接查询可以是内连接查询，也可以是外连接查询，重点在于将其看成两张表，必须要起别名
联合查询
union查询，就是把多次的查询结果合并起来，形成一个新的查询结果集
select 字段列表 from 表A....
union[all]
select 字段列表 from 表B....;
需要注意的是，联合查询多张表的列数必须保持一致，字段类型也必须相同
子查询
概念:SQL语句中嵌套select语句，称为嵌套查询，也叫子查询
select * from t1 where 字段 = (select 字段 from t2);
子查询的外部语句可以是insert、update、delete、select的任何一个
并且根据子查询的结果不同又分为:
- 标量子查询(查询结果为单个的值-->一行一列)
- 列子查询(查询的结果为一列-->多行一列)
- 行子查询(查询的结果为一行-->多列一行)
- 表子查询(查询的结果为多行多列)
根据子查询的位置，分为:where之后、from之后、select之后
标量子查询
子查询返回的结果是单个值
在这个例子中子查询查询的结果就是最便宜的价格price，再过查询找到最便宜的商品名字
列子查询
子查询返回的结果是一列，常用的操作符有:
- in-->在指定集合范围内，多选一
- not in-->不在指定的范围之内
- any-->子查询返回的列表中，有任意一个满足即可
- some-->与any一样
- all-->子查询返回列表所有值都必须满足
行子查询
子查询返回的结果是一行
常用的操作符有:=、、in、not in
表子查询
子查询返回的结果是多行多列的数据
常用的操作符有:in
六. 事务
事务是一组操作的集合，是不可分割的工作单位，事务会将所有操作作为一个整体一起向操作系统提交或者撤销，即这些操作要么同时成功，要么同时失败
例如:去银行转账的操作，需要分为三步，1.查询金额是否够转账金额 2.转账人余额减少 3.收取方余额增加
这三个操作就需要是整体的，不然如果在收取方余额增加这一步出现了异常，那么转账人的余额减少，但是收取方的余额却没有增加，这就麻烦了。所以我们需要将这三个操作控制在一个事务的范围之内，要么全部成功，要么全部失败
事务操作
- select @@autocommit;-->查看事务提交方式(1为自动提交，0为手动提交)
- set @@autocommit;-->设置事务提交方式
- start transaction 或者 begin-->开启事务;
- commit;-->提交事务
- rollback;回滚事务
事务的四大特性
- 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性(Consistency)：事务完成时，必须使所有数据都保持一致的状态。
- 隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据改变就是永久的。
并发事务问题
- 脏读
一个事务读取到另一个事务还没有提交的数据就是脏读
例如:事务A有三个操作先select查询了一下，再update修改了数据，接下来应该需要提交数据，但是事务B此时也在操作这个数据库(并发),他执行的是select查询操作，但是他查询的是事务A还没有提交的数据，这就是脏读
- 不可重复读
一个事务先后读取同一条记录，但是两次读取的数据不同，就是不可重复读
例如:事务A先select了一下得到一个数据，还没有提交，在这之后事务B也在操作这个数据库，并且执行了update操作提交了，那么在事务A再进行select的同样操作之后，发现两次读取的数据不一样，这就是不可重复读
- 幻读
一个事务先查询数据时，发现没有对应的数据，但是在插入数据时，又发现这条数据已经存在，好处出现了“幻影”
例如:事务A先select了一个id为1的数据，发现没有这条数据，在这之后事务B也在操作这个数据库，并且执行了insert id为1的数据这个操作并且提交了，接着在事务A执行insert id为1这个数据的时候，又发现这个数据已经存在了，在事务A看来就好像出现了一个“幻影”，这就是幻读
事务的隔离级别
为了解决事务的并发问题
-
select @@transaction_isolation-->查看事务的隔离级别
-
set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]-->设置事务隔离级别
