|
|
关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库
SQL语句可以单行或者多行书写，默认分号结尾
可以使用空格和缩进
SQL语句不区分大小写，关键字建议大写
注释：单行：--注释内容或者#注释内容
多行：/*注释内容*/
DDL：数据定义语句
DML：数据操作语句
DQL：数据查询语句
DCL：数据控制语句
DDL
select database（）显示当前数据库
字符串类型用varchar（）
desc 查看表结构
show create table+表名 查询指定表的建表语句
create table 表名{数值 数据类型[comment注释]，。。。
}；
数值类型：（UNSIGNED）
TINIINT一字节
SMALLINT两字节
MEDIUMINT三字节
INT或INTEGER四字节
BIGINT八字节
FLOAT四字节
DOUBLE八字节需要注明精度和标度
DECIMAL取决于M（精度：全部位数）和D（标度：小数位数）的值
字符串类型：
char：定长字符串
varchar：变长字符串
blob：二进制。。。
text：文本。。。
日期时间类型：
time：时间值或者持续时间
date：日期值
year：年份值
datetime：混合日期和时间值
timestamp：混合日期和时间值，时间戳
添加字段
alter table 表名 add 字段名 类型[注释]；
修改字段
（修改数据类型）alter table 表名 modify 字段名 新数据类型；
（修改字段名和字段类型）alter table 表名 change 旧字段名 新字段名 类型[注释]；
删除字段
alter table 表名 drop 字段名；
修改表名
alter table 表名 rename to 新表名；
删除表
drop table[if exists]表名；
删除指定表并重新创建（清除表中数据）
truncate table 表名；
insert
insert into 表名（字段1，字段2，。。）values（值1，值2，。。），（值1，值2，。。），（值1，值2，。。）
不写字段名默认添加所有字段数据，值和字段必须一一对应
update
update 表名 set 字段名1=值1，字段名2=值2，....[where 条件]
不写条件默认修改整张表的数据
delete
delete from 表名[where 条件]
不带条件删除整张表的数据
删除只能删除整条记录，要删除字段数据用update更新为NULL
select
基本查询：
select 字段列表 from 表名列表
where 条件列表 group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数
select*代表查询返回所有字段 实际开发中尽量不要写*
as 设置别名
select distinct去除重复记录
聚合函数：将一列数据作为一个整体进行纵向计算
NULL不参与聚合函数运算
select 聚合函数（字段列表） from 表名
分组查询
select 字段列表 from 表名[where 条件]group by 分组字段名[having 分组后过滤条件]
where用于分组前过滤，having用于分组后过滤
where不能对聚合函数进行判断，但是having可以
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；
asc升序（可以省略，默认排序方式）
desc降序
多字段排序：如果第一个字段值相同，再按照第二个字段排序
分页查询：
select 字段列表 from 表名 limit 起始索引，查询记录数
起始索引=（查询页码-1）*每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit
查询的是第一页的话，起始索引可以省略
limi放在最后面，group by和order by放在limit前面
DCL：数据控制语言
用于管理数据库用户，控制数据库的访问权限
查询用户：
use mysql；
select * from user；
创建用户：
create user ’用户名‘@’主机名‘ identified by ’密码‘；
如果主机名写为%则代表任意主机都能访问这个用户
修改用户密码：
alter user ’用户名‘@’主机名‘ identified with mysql_native_password by ‘新密码’；
删除用户：
drop user‘用户名’@‘主机名’；
权限控制：
alter和drop是对于表或者数据库的操作
update和delete是对于数据的操作
查询权限：
show grants for ‘用户名’@‘主机名’；
授予权限：
grant 权限列表 on 数据库名，表名 to ‘用户名’@‘主机名’；
所有数据库和所有表用*.*
撤销权限：
revoke 权限列表 on 数据库名，表名 from ‘用户名’@‘主机名’；
函数：
字符串函数：
contat字符串拼接
lower字符串转小写
upper字符串转大写
lpad在左侧填充pad，达到n长度（n和pad为传递参数）
rpad在右侧填充pad，达到n长度（n和pad为传递参数）
trim去除字符串前面和后面的空格
substring字符串截取，传递开始索引值和截取长度
数值函数：
ceil向上取整
floor向下取整
mod取模
rand随机数（不传参数，0~1随机数）
round四舍五入（传递参数表示保留几位小数）
日期函数：
curdate当前日期
curtime当前时间
now当前日期加时间
year年份（需要传日期参数）
month月份（需要传日期参数）
day日期（需要传日期参数）
date_add（date,interval expr type）时间加上一个间隔，后一个参数为interval加上时间加单位
datediff两个时间相差的天数（返回前一个参数减去后一个参数的值）
流程函数：
if（value，t，f）value为真则t，否则f
ifnull（value1，value2）value1为空(null，如果是空格不算空)返回value1，否则返回value2
case when then else end
约束
非空约束not null
唯一约束unique
主键约束（主键是一行数据的唯一标识，要求非空且唯一）primary key
默认约束（保存数据时，没有指定采用默认值）default
检查约束（保证字段值满足某或某些条件）check
外键约束（用来让两张表的数据之间建立连接，保证数据的一致性和完整性）foreign key
auto_increment自动增长
外键约束
添加外键
create table 表名（
字段名 数据类型，
...
[constraint][外键名称] foreign key（外键字段名）reference 主表（主表列名）
）；
alter table 表名 add constraint 外键名称 foreign key（外键字段名）reference 主表（主表列名）；
前一个是从表，后一个是主表
删除外键
alter table 表名 drop foreign key 外键名称；
no action/restrict不允许删除或者更新父表记录（默认行为）
cascade允许删除或者更新父表记录，并同时删除或更新子表记录
set null父表删除，子表置null
set default父表删除，子表置默认值（Innodb不支持）
alter table 表名 add constraint 外键名称 foreign key（外键字段名）reference 主表（主表列名）on update 行为名 on delete 行为名；
多表查询
一对多：在多的一方建立外键，指向一的一方的主键（部门与员工）
多对多：建立第三张中间表，至少包含两个外键，分别关联双方主键（课程与学生）
一对一：在任意一方加入外键关联另一方的主键，并对外键添加unique约束（用户和用户详情）
多表查询分为连接查询和子查询
内连接：查询两张表中间交集的部分
select 字段列表 from 表一，表二 where 条件；（隐式内连接）
select 字段列表 from 表一[inner]join 表二 on 连接条件；（显式内连接）
如果为表取了别名，就不可以使用表名来限制字段
外连接
select 字段列表 from 表一 left [outer]join表二 on 条件；（左外连接）
查询左表中所有数据和包含表一表二交集部分的数据
select 字段列表 from 表一 right [outer]join表二 on 条件；（右外连接）
查询右表中所有数据和包含表一表二交集部分的数据
自连接（必须去别名）
select 字段列表 from 表A 别名A join 表A 别名B on 条件；
自连接查询可以是外连接也可以是内连接查询
联合查询
把多次查询的结果合并起来，形成一个新的查询结果集
select 字段列表 from 表A...
union[all]
select 字段列表 from 表B...；
不带all关键字可以去重，查询结果类型必须一致才可以联合查询
子查询
select * from 表一 where 字段列表1=（select 字段列表2 from 表二）
外部操作可以是select、insert、update、delete中的任意一个
标量子查询：查询返回的结果是单个的值
列子查询：查询返回的结果是一列（in、not in、any、some、all）
行子查询：查询返回的结果是一行（in、not in、=、！=）
可以使用（字段列表1，字段列表2...）=（子查询）
表子查询：查询返回结果是一张表（in）
事务是一系列操作的集合，要么全部执行成功，要么全部执行失败
默认每一条语句都是一个事务
方式1
查看/设置事物提交方式
select @@autocommit；
set @@autocommit=0 ； 0为手动提交，1为自动提交
提交事务（业务操作正常）
commit；
回滚事务（业务操作异常）
rollback；
方式2
开启事务
start transaction或begin
提交事务（业务操作正常）
commit；
回滚事务（业务操作异常）
rollback；
事物的四大特性（面试题）
原子性：事务中的操作不可分割
一致性：事务完成时必须使所有数据保持一致状态
隔离性：事务的执行依赖于数据库提供的隔离机制，事务执行在不受外部并发操作影响的环境下
持久性：事务一旦提交或者回滚，对数据库中数据的改变是永久的
并发事务问题
1、脏读
一个事务读到另一个事务没有提交的数据
2、不可重复读
一个事务先后读取同一条记录，但是两次读取的数据是不同的
3、幻读
一个事务按照条件查询数据时，没有对应的数据行，但在插入数据时，又发现这行数据已经存在
事务隔离级别（隔离级别由低到高，性能由高到低）
read uncommitted三个并发问题都会出现
read committed不会出现脏读
repeatable read（默认）只会出现幻读
serializable解决所有并发问题
select @@transaction_isolation @@代表系统参数
set [session|global] transaction isolation level{隔离级别}
session代表当前会话的隔离级别，global代表全局 |
|
发表于 2023-4-7 09:57:06