Mysql基础笔记

原创已于 2024-12-30 20:21:00 修改 · 1.5k 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

于 2024-11-02 14:59:52 首次发布

Mysql基础笔记

1.数据库操作

连接数据库： mysql -uroot -p123456

mysql -hlocalhost -uroot -p123456

退出Mysql: exit

查看Mysql中所有数据库

show databases;  #展示当前Mysql中包含的所有数据库

创建自定义数据库

create database mydb1; # 创建mydb1数据库
create database mybd2 character set gbk; #创建mydb2数据库 并设置为gbk编码
create database if not exists mydb3; #如果mybd3数据库不存在，则创建；反之，不创建

查看创建的数据库信息

show create database mydb1

修改数据库

alter database mydb1 character set gbk; #修改数据库的字符集为gbk

删除数据库

drop database mydb1;

展示当前所用的数据库

select database();

使用数据库

use mybd2;

总结: 关于数据库的操作,重点记住

建库 create database 库名;
删库 drop database 库名;
使用库 use 库名;
查看所有数据库 show databases;
涉及的单词很重要

2.数据库表操作

查询当前库下的表

show tables;

创建数据库

create table 表名(
列名 数据类型(长度) [约束],
列名 数据类型(长度) [约束],
....
列名 数据类型(长度) [约束]    #最后一行不叫逗号
);

create table stu(
id int,
name varchar(25)
);

查看表结构

desc stu;

添加列名(字段)

alter table stu add sex int; #stu表添加sex列

修改列名(字段)

alter table stu sex change sex sid int;# 修改stu表sex字段为 int 类型的sex

删除列名

alter table stu drop sid; #删除stu表 sid字段

修改表名

alter table stu rename student; #将表stu 改为student

删除表

drop table student;

3.sql语句

3.1插入语句 insert into

insert into 表名(字段1，字段2...) values (值1,值2....);
insert into 表名 values(值1,值2....);   #值与原表字段顺序一一对应

#插入整条数据
insert into stu(sid,sname,age,score,birthday) values(1,"张三",19,88.8,"2020-01-01");

#插入全部数据   与原表字段一一对应
insert into stu values(2,"李四",20,12.21,"2021-02-02");

#按照字段 插入
insert into stu (sid,sname) values(3,"王五");

#插入多行（批量插入）
insert into stu values(4,"张5",19,88.8,"2020-01-01"),
(5,"张6",19,88.8,"2020-01-01"),
(6,"张7",19,88.8,"2020-01-01");

3.2更新修改 update

update 表名 set 字段 = 值 [,字段2 = 值2 , ....] [where 字段 = 值]

3.3 删除 delete

delete from 表名; #删除全表数据
delete from 表名 where 字段 = 值;#根据条件删除对应数据

3.4 查询 select

3.4.1基础查询

select 字段1,字段2,... from 表名

3.4.2 条件查询where

select 字段1,字段2,... from 表名 where 条件

3.4.3模糊查询 %作为占位符 like

#%三% 表示 查询结果包含有 三 的
#三% 表示 查询结果以 三 开头
#%三   以 三  结尾
# _ 表示一个占位符
select 字段1,字段2,... from 表名 where 字段 like "%值%"

3.4.4排序查询 order by

#desc 降序   asc 升序 默认
select * from 表名  order by 字段 desc | asc

3.4.5聚合函数

#聚合函数  不统计null 值
count()  计算指定字段 不为null 的行数
sum()   计算指定字段 的 和
max()   计算指定字段最大值
min()   计算指定字段最小值

3.4.5 去重 distinct

3.4.6 分组查询 group by having

select 分组字段,聚合函数(字段) from 表名 [where 条件] group by 分组字段  having 分组后条件

#大于等于三个员工的部门以及部门人数；
select dept.`deptno`,COUNT(dept.deptno) from dept 
left join emp on dept.`deptno` = emp.`deptno` 
group by dept.`deptno` having COUNT(dept.`deptno`)>3

3.4.7 限制查询 limit

limit n – 限制输出指定n条,从第一条开始

limit x,y – 限制输出,从x下标处输出y条,第一条的下标是0

分页公式：limit (pageNo-1)* pageSize, pageSize

4.sql顺序

4.1书写顺序【***】

select *
from 表名
where 条件
group by 分组
having 分组后条件
order by 排序
limit x,y

4.2 执行顺序【***】

from 表名
where 条件
group by 分组
having 分组后条件
select 查询展现字段数据，聚合也在这里
order by 排序
limit 限制

5.多表联查

表与表之间关系：

一对一：从左表往右表看是一对一，从右表往左表看也是一对一 eg：个人user表与个人详情表

一对多：从左表往右表看是一对多，但是从右表往左表看是一对一 eg：部门表与员工表

多对多：从左表往右表看是一对多，从右表往左表看还是一对多 eg: 学生表与课程表

5.1合并结果集 union | union all

# union  合并结果集  表中的字段名(数量，名称)必须保持一致
#union 合并结果集，将数据纵向拼接 若有重复数据 会 去重
select 字段1,字段2  from 表1
union
select 字段1,字段2  from 表2

#union  all 合并结果集，将数据纵向拼接 若有重复数据 不 会去重,重复数据保留
select 字段1,字段2  from 表1
union all
select 字段1,字段2  from 表2

5.2连接查询

5.2.1 内连接

select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段 where 条件

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

5.2.2 外连接（左右外连接）

-- 1) 外连接会保留不符合条件的数据
-- 2) 左外是以左表为主,左表中有不符合条件的数据也会保留并且赋 NULL值
--    右外相反...

select 字段 from 表1 left | right outer join 表2 表1.字段 = 表2.字段 where 条件

内外连接有什么区别?

内连接只查询符合关联条件的数据
外连接会保留不符合条件的数据

【注意】左外是以左表为主,左表中有不符合条件的数据也会保留并且赋 NULL值

右外相反…

5.3 子查询（嵌套查询）

将sql语句的结果当成一张新的表,写在from后面
将sql语句的结果当成条件,写在where后面

# - 子查询就是嵌套查询
# - 查询的结果是一张虚拟表

#子查询作为查询条件
select ename from emp where sal > 
(select sal from emp where ename = "SMITH")
-- 子查询当表
select * from 
(select sid,sname,age from stu where sex = '男') t
where t.age > 50

6.约束

primary key 主键不能为空不能重复
auto_increment 自增配合主键使用
unique 唯一
not null 不为空
default 默认值
constraint 外键名 foreign key (子表当前表中的列名) references 父表（列名）

7.函数

7.1 字符串函数

函数	解释	用例
concat(str1,str2)	字符串拼接	concat(“a”,“b”) 结果：ab
length(str)	字符串长度	LENGTH(“abc”) 结果：3
replace(str,search_srt,replace_str)	在str中用replace_str替换search_str	REPLACE(“Java”,“av”,“11”) 结果：J11a
substring(str,postion[,length])	从str的position开始,取length个字符	SUBSTRING(“abcd”,2) 结果：bcd SUBSTRING(“abcd”,2,2) 结果：bc
ltrim(str) rtrime(str) trim(str)	去掉左边空格去掉右边空格去掉左右两边空格
left(str,num)	从左边截取str num位	LEFT(“abcd”,1) 结果：a

7.2数学函数

函数	解释	用例
abs()	取平均值	ABS(-99) 结果:99
ceiling(num)	向上取整	CEILING(2.3) 结果：3
floor	向下取整	FLOOR(1.9) 结果:1
format (number,decimal_places )	四舍五入，保留小数位数	FORMAT(5.5183,2) 结果：5.52
rand([seed])	随机数
round(x[,d])	将x四舍五入，d是保留的位数可不写	ROUND(2.772) 结果：3 ROUND(2.772,1) 结果：2.8
truncate(x,d)	截取小数点后d位	TRUNCATE(99.666,2) 结果：99.66

7.3日期函数【***】

函数	解释	用例
sysdate()	当前系统时间	SYSDATE() 结果:2024-11-02 14:22:17
now()	当前时间	NOW() 结果:2024-11-02 14:22:17
current_time()	当前时间	CURRENT_TIME() 结果：14:22:17
current_timestamp()	当前时间戳	CURRENT_TIMESTAMP() 结果：2024-11-02 14:22:17
date(datetime)	返回datetime的日期部分	DATE(“2024-11-02 14:22:17”) 结果：2024-11-02
datediff(date1,date2)	两个日期差	DATEDIFF(“2024-11-02”,“2024-11-01”) 结果：1

#单独获取 年  月  日
year()
select year("2020-01-01")   #2020

month()
select month("2020-01-01") #1

day()
select day("2020-10-12") #12

7.4日期字符串转换函数【***】

函数	解释	用例
date_format(日期，模板)	日期–>字符串	DATE_FORMAT(NOW(),‘%Y年%m月%d日’) 结果：2024年11月02日
str_to_date(字符串,模板)	字符串–>日期	STR_TO_DATE(‘2022年11月18日’,‘%Y年%m月%d日’) 结果：2022-11-18

日期 --> 字符串 date_format(date,‘%Y-%m-%d’)

字符串 --> 日期 str_to_date(‘datestr’,‘%Y-%m-%d’)

日期模板
%Y年 %m月 %d日
%H时 %i分钟 %S秒

7.5流程函数【***】

函数	解释	用例
if(expr1,expr2,expr3)	如果expr1为真，则返回expr2；如果expr1为假，则返回expr3	IF(5>3,“good”,“bad”) 结果：good
ifnull(expr1,expr2)	如果expr1是null值，返回expr2;如果expr1不是null值，返回expr1。一般用来替换NULL值
case when [expr1] then [result1] … else [default] end	如果expr是真, 返回result1,否则返回default

-- 查询学生的成绩，若成绩为null  则赋值为 缺考
select name,ifnull(score,"缺考")  from stu;

-- 查询学生id,姓名,成绩,及等级(60以下不及格,60-70,及格,71-80,中等,81-90良好,91-100优秀)
select sid,sname,case
when score < 60 then "不及格"
when score < 70 then "及格"
when score < 80 then "中等"
when score < 90 then "良好"
else "优秀"
end as 等级
from stu;

Mysql 事务

1.事务介绍

是什么是事务？

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。(一组操作同时成功,或同时失败)

场景：

银行转账操作,A账号要给B账户转钱. A原有1000块,B原有1000块.A转账给B 100元

A的钱要减少,B的钱要增多
update A set money = money - 100 where id = a

-- 服务器出现异常,导致后面的sql没有执行

update B set money = money + 100 where id = b

2.事务特性【***】

事务的特性(ACID)

原子性（Atomicity）：指事务的整个操作是一个整体，要么都成功,要么都失败
一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。
隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。回滚不了的

3.事务的隔离机制【***】

数据库有不同的隔离机制/隔离级别

读未提交-READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。

读已提交-READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。

（oracle 默认的）

**可重复读-**REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。

（mysql 默认）,行锁

**串行化-**SERIALIZABLE: 避免赃读、不可重复读、虚读。

串行化,其实是表锁

查看当前数据库的隔离级别:select @@transaction_isolation;

更改当前的事务隔离级别：

SET [glogal | session] transaction_isolation = 四个级别之一。

赃读：指一个事务读取了另一个事务未提交的数据。
对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.  之后, 若 T2 回滚, T1读取的内容就是临时且无效的.  
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务提交后的数据。（update）
对于两个事物 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.  之后, T1再次读取同一个字段, 值就不同了.  
虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 (insert)
对于两个事物 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中  插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行