MySQL(基础)

一. 初识数据库

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

image.png

image.png

image.png

这里需要注意设置登录密码(尽量简单)

image.png

下载好之后再进行环境的配置就可以通过命令行启动MySQL

image.png

image.png

最后将安装的MySQL路径添加进去即可

image.png

二. 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 表注释]；接下来我创建一个员工表并且演示一些相关操作查看当前所有数据库

image.png

创建一个work数据库，并且查看所有表和表结构发现都是空

image.png

创建一个员工表

image.png

查看表结构

image.png

查看建立表语句

image.png

数据类型

•数值类型

image.png

•字符串类型

image.png

其中需要注意的是char 和 varchar类型的区别

•char：定长字符串就是定死的长度，如果你设置的是10个字符，那么就算使用的只有一个字符那么也需要使用10个字符的资源•varchar：变长字符串，会根据使用的字符长度进行变化，如果设置10个字符，只需要用1个字符的长度，那么就会计算出来并且只使用1个字符的资源

所以char的效率是比varchar高的，因为varchar多了需要计算的一步操作。

•时间和日期类型

image.png

表操作--修改

•添加新字段

alter table 表名 add 字段名 类型(长度) [comment 注释];

image.png

•修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

image.png

•修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释];

image.png

•删除字段

alter table 表名 drop 字段名;

image.png

•修改表名

alter table 表名 rename to 新表名;

image.png

•删除表

drop table [if exists] 表名;

image.png

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，..)... ;

需要注意的是：

添加数据时，第一个值对应第一个字段，顺序必须一一对应。

当添加的字段值数据类型为字符串类型和时间日期类型时，需要在引号中。

插入的数据大小需要在字段规定范围内。

image.png

image.png

image.png

先使用后面内容查看一下表

image.png

修改数据

update 表名 set 字段1 = 值1，字段2 = 值2，...[where 条件];

需要注意的是这里的where条件可以没有，如果没用那么修改的就是整个表的数据

image.png

删除数据

delete from 表名 [where 条件]

和修改数据一样，这里的where条件可以没有，如果没有那么就是删除整个表的数据

image.png

2.3 DQL

基础查询

•查询多个字段-->select 字段1，字段2，... from 表名;•查询全部字段-->select * from 表名•设置别名-->select 字段1 [as 别名1],字段2 [as 别名2],... from 表名;•去除重复记录-->select distinct from 表名;

条件查询

select 字段列表 from 表名 where 条件列表;

条件有以下这些

image.png

还有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 外键约束-->用来让两张表的数据之间建立连接，保证数据的一致性和完整性

外键约束

为什么需要外键约束?外键是用来建立两张表之间的联系，从而保证数据的一致性和完整性。

image.png

比如这个例子如果我们没有使用外键，那么在部门表删除了id为1的研发部后，员工表不会受到任何影响，但是此时很明显已经不符合我们的预期了。

若使用了外键，那么在进行同样的操作的时候，会报出错误，使我们无法删除，从而保证数据的一致性和完整性。

•添加外键：

image.png

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

有外键的表一般我们叫作子表，而对应的表我们叫作夫表。

我们添加了外键之后，如何改变表中的数据呢，这就需要外键的删除/更新操作

•no action-->在夫表中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除和更新(默认)•restrict-->在夫表中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有则不允许删除和更新(同no action)•cascade-->当父类中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有，则也删除和更新外键在子表中的记录•set null-->当父类中删除和更新对应记录时，首先检查该记录是否有对应的外键，如果有则设置子表中该外键的值为null(前提是允许为null)•set default-->夫表有变更时，子表将外键列设置成一个默认的值(innodb不支持)

五.多表查询

在业务中，难以避免多张表产生联系，那么多表的关系又是怎么样的呢。

多表关系

•一对多(多对一)

例如:部门和员工的关系，一个员工只能有一个部门，而一个部门可以有多个员工，那么我们就可以在多的一方设置外键，指向另一方的主键

image.png

image.png

•多对多

例如:学生和课程的关系，一个学生可以有多个课程，一个课程页可以有多个学生，我们可以使用第三张表将其联系起来，新表至少使用两个外键

image.png

•一对一

例如:用户和用户详情的关系，一对一的关系，多用于单表拆分，将基础字段放在一张表中，其他详情字段放在一张表中，以提升操作效率

image.png

image.png

注意，这里在任意一方加入外键关联另一张表的主键，并且设置外键约束为唯一的(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之后

标量子查询

子查询返回的结果是单个值

image.png

在这个例子中子查询查询的结果就是最便宜的价格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看来就好像出现了一个“幻影”，这就是幻读

事务的隔离级别

为了解决事务的并发问题

image.png

•select @@transaction_isolation-->查看事务的隔离级别•set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]-->设置事务隔离级别