数据库操作笔记

这个笔记是在自己学习的过程中根据实际用到的和学到的整理出来的，可能会有缺失，错误等，主要是给激励自己学习，遇到写不下去的情况给自己一个参考，请各位大佬发现问题提出问题时能嘴下留情，也希望多提建议，谢谢。本笔记长期更新（更新日期2024年9月11日）

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

对数据库的操作

常用用法！！

创建数据库

中间一般省略

第一个中括号是判断是否存在，如果不存在则创建

第二个中括号是数据库的编码格式

create database 数据库名;
create database [if not exists] 数据库名 [default charset utf8mb4];

删除数据库

中间一般省略

中括号是判断是否存在，如果存在则删除

drop database;
drop database [if exists] 数据库名;

使用数据库

use 数据库名;

其他用法

查询所有数据库

cmd命令行中常用

show databases;

查询当前使用的数据库

cmd命令行中常用，需要使用数据库后才能使用

select databases();

数据库中的类型

数值类型

类型	大小	有符号(SIGNED)范围	无符号(UNSIGNED)范围	描述
TINYINT	1byte	(-128，127)	(0，255)	小整数值
SMALLINT	2bytes	(-32768，32767)	(0，65535)	大整数值
MEDIUMINT	3bytes	(-8388608，8388607)	(0，16777215)	大整数值
INT/INTEGER	4bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8bytes	(-263，263-1)	(0，2^64-1)	极大整数值
FLOAT	4bytes	(-3.402823466 E+38，3.402823466351 E+38)	0 和 (1.175494351 E-38，3.402823466 E+38)	单精度浮点数值
DOUBLE	8bytes	(-1.7976931348623157 E+308，1.7976931348623157 E+308)	0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308)	双精度浮点数值
DECIMAL		依赖于M(精度)和D(标度)的值	依赖于M(精度)和D(标度)的值	小数值(精确定点数)

字符串类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串(需要指定长度)
VARCHAR	0-65535 bytes	变长字符串(需要指定长度)
TINYBLOB	0-255 bytes	不超过255个字符的二进制数据
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

日期时间类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901 至 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 至 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:01 至 2038-01-19 03:14:07	YYYY-MM-DD HH:MM:SS	混合日期和时间值，时间戳

数据表操作

常用用法！！

新建数据表

参考

create table 表名(
	字段1 字段1类型 [约束] [comment 字段1注释],
    字段N 字段N类型 [约束] [comment 字段N注释]
)[comment 表注释]

约束名称	约束作用
not null	限制字段不能为null
null	允许字段空值，不写默认自带
unique	该字段不能有重复值
primary key	主键，并且有not null 和unique的作用
default ‘默认值’	指定默认值
forerign key	外键(不在字段后面写，在最后写，明明格式一般是fk _ 表名 _ 对应表名 _ 列名 )
unsigned	不算负数
auto_increment	自动增长

举个栗子

create table user(
    id int unsigned primary key auto_increment comment '用户id主键',
    username varchar(30) not null unique comment '用户登录名',
    name varchar(20) not null comment '用户姓名',
    password varchar(30) not null default '123456' comment '用户登录密码',
    dept_id int not null comment '用户单位',
    constraint fk_emp_dept_dept_id foreign key (dept_id) references dept(id)
) comment '用户信息表'

修改数据表

添加字段

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

例:

alter table user add qq varchar(13) not null comment '秋秋号';

修改数据类型

这里是两个哦，一个是不改名，一个改名

alter table 表名 modify 字段名 新数据类型(长度);
alter table 表名 change 字段名 新字段名 新数据类型(长度) [comment 注释] [约束];

例:

alter table user modify qq varchar(11) null comment '秋秋号';
alter table user change qq qq_num varchar(12) null comment '秋秋号';

删除字段

alter table 表名 drop 字段名;

例:

alter table user drop qq_num;

修改表名

rename table 表名 to 新表名

例:

rename table user to employee

删除表

中间一般省略

中括号是判断是否存在，如果存在则删除

drop table 表名;
drop table [if exists] 表名;

其他用法

查询当前数据库所有表

一般也是在cmd命令中使用（不会有人不用可视化软件吧）

show tables;

单表操作

在这提一句，sql是支持运算的，毕竟它连sum,avg,max,min这些方法都提供了，肯定是支持加减乘除的

例:

select name, score + 10 from student;--查询出来的所有成绩会加10
insert into student (name, score) values("张三", 50 + 10);--会插入一个名字为张三，成绩为60的数据
update student set score = score + 10 where name = '张三';--会将张三的成绩+10

查询

全套语法

不一定都能用上，提一句，where和having生效位置不一样，注意使用

where是在group by前就生效了，having是在group by后在生效

where不能对聚合函数进行判断，而having可以

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数

基本查询

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

条件查询

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

常用的比较与逻辑运算符

运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
between … and …	在某个范围之内(含最小、最大值)
in(…)	在in之后的列表中的值，多选一
like 占位符	模糊匹配(_匹配单个字符, %匹配任意个字符)
is null	是null
and 或 &&	与
or 或 ||	或
not 或 !	非

聚合函数

select 聚合函数(字段列表) from 表名;

函数	功能
count	统计数量，但是不统计null
max	最大值
min	最小值
avg	平均值
sum	求和，但是如果不是数值类型计算结果为0

分组查询

select 字段列表 group by 分组字段名 [having 分组后的条件]

group by之后要注意，有几个种类就只显示几行，所以如果要看所有符合的信息，需要引入一个新的方法group_concat()

select group_concat(结果不唯一的字段) from 表名 group by 分组字段名

排序查询

可以写很多排序方式，但是在前面的会优先生效，前面对比不出来才会走后面

select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式...

分页查询

其实索引是从0开始，所以查询前五个是 limit 0,5

如果查询从第3个开始查询5个就是 limit 2,5

select 字段列表 from 表名 limit 起始索引,查询个数

增加

值可以用的函数	作用
now()	适用于类型为date,time,datetime类型的值，自动填充当前时间

增加指定字段的数据

使用制定字段有个好处，如果有的属性有默认值，或者主键自增这种，是可以不写，让数据库自动生成的

--增加一条
insert into 表名 (字段1,字段2...) value (值1,值2...);
--增加多条
insert into 表名 (字段1,字段2...) values (值1,值2...),(值1,值2...)....;

增加全部字段

增加全部字段的值必须全都写，如果有主键自增可以填null，但是有默认值的不能这么用

--增加一条
insert into 表名 value (值1,值2...);
--增加多条
insert into 表名 values (值1,值2...),(值1,值2...)....;

修改

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

删除

delete from 表名 where [条件];

多表操作

外键

forerign key 外键(不在字段后面写，在最后写，明明格式一般是fk _ 表名 _ 对应表名 _ 列名 )

在创建表时制定：参考请见4.1.1.2

create table 表名(
	字段名 数据类型 [约束] [解释],
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (主表列名)	
);

在创建表后添加外键:

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

举个栗子

alter table user add constraint fk_emp_dept_dept_id foreign key (dept_id) references dept(id);

内连接

隐式内连接

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

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

起别名查询

select 字段列表 from 表1 [as] 别名1，表2 [as] 别名2 where 条件...;

外连接

左外连接和右外连接是可以相互替换的，只需要调整连接查询时SQL语句中表的先后顺序就可以了。

左外连接

左外连接会查询左表的所有数据，也包含左表和右表有交集的数据，如果右表没有匹配的项目，会显示null

select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;

右外连接

右外连接会查询右表的所有数据，也包含左表和右表有交集的数据，如果左表没有匹配的项目，会显示null

select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;

子查询

子查询就是把多条语句合成一个语句

select 字段 from 表2 ...;  拿到这个结果
select 字段列表 from 表1 where 字段 多种逻辑(看下表) (上面拿到的结果)
就可以合并写成如下：
select 字段列表 from 表1 where 字段 多种逻辑(看下表) ( select 字段 from 表2 ... );

操作符	作用
= <> > >= < <=	字面意思(等于，不等于，大于，大于等于，小于，小于等于)
in	在指定范围之内
not in	不在指定范围之内

标量子查询

子查询结果为单个值[就一个值，一行一列，一个单元格那种]

select 字段列表 from 表1 where 字段 = ( select 字段 from 表2 ... );

列子查询

子查询结果为一列，但可以是多行

例如查询了ID为3和5的人的姓名

行子查询

子查询结果是一行，但可以是多列

例如查询了ID为3的人的姓名和手机号

表子查询

子查询结果为多行多列，和表一样

例如查询了ID为3和5的姓名和手机号

特殊操作

row_count()方法

row_count()用来检查上一个DML语句影响了几行数据，并且必定是上一个，不能跳过检查

假如现在有一个user表，主键是ID，表中有两个叫张三的

select * from user where id = 1;
--如果此时id为1存在，那么此时调用row_count()的值就是1；反之，如果id为1不存在，那么row_count()的值就是0

delete from user where name = '张三'
--表中有两个叫张三的，所以这个能删掉两个，调用row_count()的值就是2，不会收到上面select语句影响

事务

start transaction;--开始事务的一种方法
begin transaction;--开启事务的另一种方法

savapoint first_savepoint;--指定了一个名为"first_savepoint"的保存点，可以回滚到这里

commit;--提交事务，并关闭事务

rollback;--回滚事务，并关闭事务

rollback to savepoint first_savepoint;--回滚到第一个保存点

假设有两个表，一个表是Inventory，用来存储每种产品的数量，另一个表是Orders，用来记录客户的订单详情。

假设我们有一个事务要从Inventory中减去订购的数量，同时在Orders表中记录这个订单，如果减库存的操作成功，那么订单会被确认；反之，两个操作都会被取消。

start transaction;--(或者begin transaction)开启一个新的事物

--1.假设商品ID是1，并且我们要订购5个商品
update inventory set count = count - 5 where id = 1 and count >= 5;

--2.row_count()用来检查上一个DML语句影响了几行数据，并且必定是上一个，不能跳过检查
if row_count() = 1 then
	--记录订单
	insert into orders(id,pid,count) values(1,1,5);
	
	--再次检查是否更新成功
	if row_count() = 1 then
		commit; --提交事务
	else
		rollback; --更新订单失败，回滚事务
	end if;
else
	rollback; --没有足够库存，回滚事务
end if;

改变查询时返回的值

都是写在查询语句的字段列表中，和JAVA中的switch-case和if-else还是有一些区别的。

case

语法:

--语法1：
(case when 条件1 then 取值1 
[when 条件2 then 取值2...]
...
 else 取值 end;
)
--如果条件1成立，那么返回的就是取值1，如果条件2成立，返回取值2，如果所有条件都不满足，那么返回最终else的取值


--语法2:
(case 字段 when 值1 then 取值1
[when 值2 then 取值2]
...
else 取值 end)
--如果字段的值和值1相等，返回取值1，如果字段的值和值2相同，则返回取值2，如果都不相等，则返回else的取值

例子:

--统计每个性别的人数，但是表中gender存的是int类型，1是男性，2是女性
select (case gender
            when 1 then '男'
            when 2 then '女'
            else '其他' end) '性别',
       count(*)              '人数'
from user
group by gender;

if

语法:

if(条件,取值1，取值2)
--判断条件，如果条件成立true，返回取值1，如果条件不成立false，返回取值2

例子：

select if(gender = 1, '男', '女') '性别',
       count(*)                   '人数'
from user
group by gender;

ifnull

语法：

ifnull(字段名,取值)
--如果字段名为空，则取取值，如果字段名不为空，则用自身的值

排他锁

FOR UPDATE

这个锁和java中的synchronized ()有相似的地方，也有不同的地方。

这个锁用在事务中，事务A开始，当这个锁被开启后，如果这个事务没有结束，那么此时来了一个新事务B，事务B不能对被这个锁锁定的数据进行读取，更新，删除操作，直到事务A结束，释放该锁。

就类似于电影票卖票一样，一共就1000张票，但是有十个窗口卖，万一剩1张总不能卖到-9张一样，一个开始卖的时候，直到卖出这一张，更新完了仓库余量，才能让下一个接着卖。

语法：SQL语句最后加上for update
例:

select * from user where id = 1 FOR UPDATE;--锁定id为1的数据，释放前禁止其他数据增删改查
update user set name="李四" where name = "张三" FOR UPDATE;--锁定所有姓名为张三的数据，可能是一行也可能是多行也可能没有