MySQL基础知识与函数-优快云博客

数据库允许多个并发事务同时对其数据进行进行读写和修改的能力，隔离性可以防止多个事物并发执行时由于交叉执行而导致数据的不一致；
事物的隔离分为不同级别：包括读未提交(Read uncommitted)、读提交(Read committed)、可重复读(repeatable read)和串行化(Serializable)；

（4）持久性：

事物处理结束后，对数据的修改就是永久的，基本系统故障也不会丢失；

（5）事务的具体操作可以参考此博客：MySQL基础知识与命令-优快云博客

11.数据库中字段的类型

（1）tinyint --- 1个字节 --- byte

（2）smallint --- 2个字节 --- short

（3）int --- 4个字节 --- int

（4）bigint --- 8个字节 --- long

（5）double --- 8个字节 --- double

（6）decimal --- 4个字节 --- float

（7）char(1)：存储的是固定长度的字符，括号中为1就是可以存放1个字符，为2就是可以存放2个字符，以此类推

（8）varchar(65535)：存储的是可变长度的字符，最大为65535

（9）text：存储长文本

（10）日期类型：

date：存的是年月日 -- yyyy-mm-dd
time：存的是时间 --- hh:mm:ss
datetime：yyyy-MM--dd hh:mm:ss
timestamp：存的是时间戳(包含时区)

（11）二进制类型：

tinyblob
blob
longblob
上面的这个三个类型，可以存放图片，视频，音频等二进制的东西

二、MySQL增删改查

1.数据库的增删改查

（1）创建数据库

create database 数据库名

（2）删除数据库

drop database 数据库名

（3）创建数据表

create table 表名(
    列名 类型 约束,
    列名 类型 约束,
    ...
    列名 类型 约束
)

（4）约束：

主键(primary key)：每一张表只有一个主键(也可以是组合主键)；
- 主键不能为空，不能重复
自增(auto_increment)：自动增长；
非空(not null)：不能为空；
唯一(unique)：不能重复；
默认(default)：设置默认值；
外键(foreign key)：需要配合references来使用；
- 通过另一个表的字段来约束当前创建的表的字段
约束的具体操作可以参考此博客：MySQL基础知识与命令-优快云博客

（5）删除数据表

drop table 表名

（6）修改字段长度

alter table 表名 modify 字段名 字段类型

（7）修改字段名

alter table 表名 change 字段名 修改后的字段名 修改后的字段类型

（8）添加字段

-- 添加到哪个字段的后面
alter table 表名 add 要添加的字段 字段类型 after 字段名
-- 添加到哪个字段的前面
alter table 表名 add 要添加的字段 字段类型 before 字段名

（9）删除字段

alter table 表名 drop column 字段名

（10）截断表

-- 这个不但可以清除表中的数据，还可以将自增约束的值清空为0，一般不推荐使用
truncate table 表名

2.数据的增删改

（1）添加数据

-- 注意字段和值是对应的
insert into 表名(字段1,字段2,字段3...) values(数据1,数据2,数据3...);

（2）删除数据

-- 删除表中全部数据
delete from 表名
-- 删除表中指定数据
delete from 表名 where 条件

（3）修改数据

update 表名 set 字段名=值 where 条件

3.数据的查询

（1）查询需要使用的关键字

select 字段
from 表名
where 条件
group by 分组
having 筛选(第二次筛选，就是在group by之后再次筛选)
order by 排序
limit 限制行

（2）简单查询

-- 查询表中所有的数据
select * from 表名

（3）加条件查询

-- 查询符合条件的的数据
select * from 表名 where 条件

（4）MySQL中的关系符号

等于：=
不等于：<>
大于：>
小于：<
区间：between...and...
- 注意：在使用between...and...的时候，between后面要跟小值，and后面要跟大值
- 如：between min and max

（5）连接符

和：and

select * from 表名 where 条件1 and 条件2

或者：or

select * from 表名 where 条件1 or 条件2

不：not
```
select * from 表名 where not 条件
```

（6）模糊查询

模糊查询需要使用关键字：like

select * from 表名 where 字段 like 要模糊查询的信息

模糊查询中给通配符有：%，_

%：匹配任意字符任意次数(0~N)

-- 比如要查询姓名中是杨XX的人
select * from 表名 where name like '杨%'

_：匹配任意字符一次

-- 比如要查询姓杨，姓名是两个字的人
select * from 表名 where name like '杨_'

（7）聚合函数

count：统计个数

-- 统计姓名个数
select count(name) from 表名

avg：求平均值

-- 求分数平均值
select avg(score) from 表名

sum：求和

-- 分数求和
select sum(score) from 表名

max：求最大值

-- 求分数最大值
select max(score) from 表名

min：求最小值

-- 求分数最小值
select min(score) from 表名

（8）分组查询

关键字：group by
语法：

-- 查出男生和女生的平均成绩
select avg(score) from 表名 group by sex;

（9）排序

升序：asc(默认)
降序：desc
语法：如果有多个要排序的字段，使用逗号隔开

-- 降序排序
select * from 表名 order by 要排序的字段名1 desc,要排序的字段名2 desc；

（10）限制行

关键字：limit n,m
- n代表下标索引，m代表要显示的个数
语法：

-- 显示前三行的信息
select * from 表名 limit 0,3;

（11）排序和限制行组合使用

-- 求班级分数前三名的学生信息
select * from 表名 order by score desc limit 0,3;

（12）查询某个字段值为null的数据

select * from 表名 where 字段名 is null

（13）having的用法

select 字段1,字段2 from 表名 group by 字段 having 要筛选的条件

（14）表连接

内连接：inner join

select * from 表1 inner join 表2 on 表1.字段=表2.字段

使用inner join

-- 假如有一个表emp 内有字段eid ename sal parentid
-- 自关联查询
select e1.*,e2.ename from emp e1 inner join emp e2 on e1.eid=e2.parentid

多表查询

select * from 表1,表2 where 表1.字段=表2.字段

外连接：

左外连接：left join

select * from　表1 left join 表2 on 表1.字段=表2.字段

右外连接：right join

select * from 表1 right join 表2 on 表1.字段=表2.字段

交叉链接：cross join

select * from 表1 cross join 表2 on 表1.字段=表2.字段

（15）子查询

查询表中分数为70，80，90分的信息

方法一：使用or

select * from 表名 where degree = 70 or degree = 80 or degree = 90;

方法二：使用in

select * from 表名 where degree in (70,80,90);

查询分数最高的学生信息

select * from 学生表 where sno = (select sno from 成绩表 order by degree desc limit 0,1)

总结：子查询就在一个查询里面嵌套了另一个查询

（16）子查询和表连接有时候可以相互转换

（17）取别名

格式：

select 别名1.字段 别名 from 表名1 别名1,表名2 别名2 where 别名1.字段=别名2.字段 order by 要排序的字段 limit 0,1

显示也可以取别名
注意：在取了别名之后，就不能使用原来的表名了

（18）优化建议

如果是多条件查询，应该是先精确查询在模糊查询；
如果在使用的条件的时候，小于和大于都可以得到你所要的结果，那么尽量使用小于作为条件；
在连续的区间字段上，应该使用between...and...；
一般情况下，最好使用原始态sql的like进行模糊查询(通用的)；
在查询所有列的时候也不要使用*来代表所有的列，而是要写一个一个的列名；

三、MySQL中的函数

1.字符函数

（1）concat：拼接字符串

select concat('123','abc','eee') -- 123abceeee

（2）concat_ws：将第一个字符当作连接字符，然后拼接字符串

select concat_ws(',','aaa','bbb','ccc') -- aaa,bbb,ccc

（3）left：从左边开始，取2个字符(截取字符串)

select left('abcd',2) -- ab

（4）right：从右边开始，取3个字符(截取字符串)

select right('abcd',3) -- bcd

（5）upper：将字母全部转换为大写(转换大小写)

select upper('abcdefg') -- ABCDEFG

（6）lower：将字母全部转换为小写(转换大小写)

select lower('ABCDEFG') -- abcdefg

（7）trim：将第一个字符串从第二个字符串的首尾去除

select trim('9' from '99123923567890999') -- 123923567890

（8）lpad：通过指定的字符补足位数(从左边开始补齐)

select lpad('888','9','=') -- ======888

（9）rpad：通过指定的字符补足位数(从右边开始补齐)

select rpad('888','9','=') -- 888======

（10）strcmp：比较字符串的大小

select strcmp('yddd','dddccc') -- -1

（11）replace：将字符串中的某个字符替换为另一个字符

select replace('99123923567890999','9','') -- 1232356780

（12）length：获取字符串的长度

注意：在数据库中，一个中文字符总共占三个位置，其中第一个位置保存的是编码格式，剩下的两个位置保存的是这个中文字符的具体数据

select length('123') -- 3
select length('中国') -- 6

（13）char_length：获取字符串长度

注意：在数据库中，此函数获取的一个字符的长度为1，这个函数是用来获取字符长度的，一个字符占一个位置

select length('123') -- 3
select length('中国') -- 2

2.数字函数

（1）abs：求绝对值

select abs(-9) -- 9

（2）ceil：不进行四舍五入，只要有小数，便向上取整(天花板)

select ceil(3.1415926) -- 4

（3）floor：不进行四舍五入，只要有小数，便向下取整(地板)

select floor(3.9999999) -- 3

（4）round：四舍五入

无参数(正常取整)

select round(3.14) -- 3
select round(3.54) -- 4

有参数(保留小数)

select round(3.1415926,2) -- 3.14
select round(3.14526,2) -- 3.15

（5）mod：取余

select mod(5,3) -- 2

（6）rand：随机数(0-1)

select rand() -- 会生成一个随机数

（7）truncate：截断小数点后面的位数，不进行四舍五入

select truncate(34.123456789,3) -- 34.123
select truncate(34.12356789,3) -- 34.123

（8）sqrt：开平方

select sqrt(4) -- 2
select sqrt(16) -- 4

3.时间函数

（1）now：获取当前系统日期和时间

select now()

（2）curdate：获取当前系统的日期

select curdate() -- 20211002
select curdate()+1 -- 20211003

（3）curtime：获取当前系统的时间

select curtime()

（4）unix_timestamp：获取1970年到当前系统时间之间的秒数

select unix_timestamp() -- 时间戳/365/24/60/60 + 1970 = 当前年份

（5）year：获取到当前系统的年份

select year(now())

（6）month：获取到当前系统的月份

select month(now())

（7）day：获取到当前系统的天数

select day(now())

（8）date_format：格式化输出日期和时间

select date_format(now(),'%y年%m月%d日'); -- 21年10月02日
select date_format(now(),'%Y年%m月%d日'); -- 2021年10月02日

（9）datediff：日期的相减

select datediff(date('2022-09-09'),now()) -- 用第一个日期减第二个日期

（10）date_add：日期的相加

select date_add(now(),interval 2 day) -- 加2天
select date_add(now(),interval 2 month) -- 加2月
select date_add(now(),interval 2 year) -- 加2年

4.高级函数

（1）if：判断

select if(1>2,'true','false');

（2）isnull：判断是否为null

select isnull(null); -- 1
select isnull('abc'); -- 0

（3）case、when、then、end：

-- 假设有一个分数表
select degree,case 
when degree >= 90 then '优秀' 
when degree >= 80 then '良好' 
when degree >= 60 then '及格' 
when degree < 60 then '不及格' 
end as '评价' from score;