Chenpeel

Appearance

写在前面✍️ ​

本篇是学习数据库的基本内容的信息，作为速览List

SQL分类： ​

Table ​

分类全称说明
DDLData Definition Language数据定义语言，用来定义数据库中的对象（数据库、表、字段）
DMLData Manipulation Language数据操作语言，用来对数据库表中的数据进行增删改
DQLData Query Language数据查询语言，用来查询数据库中表的记录
DCLData Control Language数据控制语言，用来创建数据库用户、控制数据库的访问权限

SQL语法： ​

DDL ​

数据库 ​
  • 查询
show databases;		--显示所有库
select database;	--当前数据库
  • 创建
create database ithema default charset utf8mb4 if not exists;
  • 删除
drop database if exists test
  • 使用
use database
查询创建 ​
  • 查询所有表
show tables
  • 查询表结构
desc table_name
  • 查询指定表的建表语句
show create table table_name
  • 创建表
create table test_user( 
id int comment '编号', 
name varchar(50) comment '姓名', 
age tinyint unsigned comment 'age',
gender varchar(1) comment 'sex' ,
entrydate date comment '入职时间'
)comment 'user_table';
表操作-修改 ​
  • 添加字段

alter table table_name add nick_name varchar(20) comment '昵称';
  • 修改字段

alter table table_name modify nick_name varchar(21);
  • 修改字段名和字段类型

alter table table_name change nick_name nickname varchar(20) ;
  • 删除字段

alter table table_name drop nickname;
  • 修改表名

alter table old_table_name rename to new_table_name;
表操作-删除 ​
  • 删除表
drop table if exists table_name;
  • 删除并重新创建
truncate table table_name;

DML ​

添加数据 ​
  • 给指定字段添加数据
insert into table_name (field1,field2,...) values(value1,value2,...);
  • 给全部字段添加数据
insert into table_name values(value1,value2,...);
  • 批量添加数据
insert into table_name (field1,field2,...) values(value1,value2,...),(value1,value2,....),...;
insert into table_name values(value1,value2,...),(value1,value2,..);
修改数据 ​
  • 修改数据
update table_name set colum1 = value1,colum2 = value2,...[where confition];
删除数据 ​
  • 删除数据
delete from table_name [where condition];

DQL ​

语法 ​
select  	--基础查询 
from 			--条件查询
where   	--聚合查询
group by 	--分组查询
having 		
order by 	--排序查询
limit			--分页查询
基本查询 ​
  • 查询多个字段
select field1,field2,... from table_name;
select * from table_name;
  • 设置别名
select field1 [as new_name1],field2 [as new_name2],... from table_name;
  • 去重复记录
select distinct field_list from table_name;
条件查询 ​
  • 语法
select field_list from table_name where conditions_list;
  • 条件
比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或 !=不等于
BETWEEN ... AND ...在某个范围之内（包括min、max）
IN(...)在in之后的列表中的值，多选一
LIKE模糊匹配(_匹配单个字符，%匹配任意个字符，可组合使用)
IS NULL是NULL（IS NOT NULL）
AND 或 &&且
OR 或 ||或
NOT 或 !非
聚合函数 ​

  • 将一列数据作为一个整体进行纵向计算

  • 常见的聚合函数 (所有的NULL不参与聚合函数查询)

函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和
分组查询 ​
  • 语法
select field_list from table_name [where condition] group by group_field_name [having condition]

  • where 和 having 区别

    • 判断时机不同: where 是分组之前进行过滤，不满足where条件，不参与分组，而having 是分组之后进行过滤
    • 判断条件不同: where 不能对聚合函数进行判断，而having可以

  • 执行顺序： where > 聚合函数 > having

  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

排序查询 ​
  • 语法
SELECT * FROM table_name ORDER BY age asc; --升序 可省略
SELECT * FROM table_name ORDER BY age DESC;    --降序
SELECT * FROM table_name ORDER BY age asc , entrydate ASC; --先按age 再按entrydate
分页查询 ​
  • 语法
select * from table_name LIMIT begin , query_num_mark
  • 不同的SQL有不同的此语法
执行顺序 ​
select 						--4
from							--1
where 						--2
group by	having	--3
order by					--5
limit							--6

DCL ​

管理用户 ​
  • 查询用户
use ;
select * from user;
  • 创建用户
create user 'user_name'@'host_name' IDENTIFIED BY 'password';
  • 修改用户密码
alter user 'user_name'@'host_name' IDENTIFIED WITH _native_password BY 'password';
  • 删除用户
drop user 'user_name'@'host_name';
权限控制 ​
-权限说明
ALL, ALL PRIVILEGES所有权限包括查询、插入、修改、删除、修改表、删除数据库/表/视图、创建数据库/表等所有权限
SELECT查询数据
INSERT插入数据
UPDATE修改数据
DELETE删除数据
ALTER修改表
DROP删除数据库/表/视图
CREATE创建数据库/表

查询权限

show grants for 'user_name'@'host_name';
  • 授予权限
grant 权限列表 ON database_name.table_name from 'user_name'@'host_name';
  • 撤销权限
revoke 权限列表 ON database_name.table_name from 'user_name'@'host_name';

函数： ​

字符串函数 ​
函数功能
CONCAT(S1,S2,...Sn)字符串拼接，将S1,S2,...Sn拼接成一个字符串
LOWER(str)转小写
UPPER(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)向上取整
FLOOR(x)向下取整
MOD(x, y)返回x/y的模
RAND()返回0～1内的随机数
ROUND(x, y)求参数x的四舍五入值，保留y位小数
日期函数 ​
函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定date的日期
DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数
流程控制函数 ​
函数功能
IF(value,t,f)如果value为true，返回t，否则返回f
IFNULL(value1,value2)如果value1不为空，返回value1，否则value2
CASE WHEN [val1] THEN [res1]...ELSE [default] END如果val1为true，返回res1,...，否则返回default
CASE [expr] WHEN [val1] THEN [res1]...ELSE [default] END如果expr值等于val1，返回res1,...,否则返回default

约束 ： ​

概念 ​
  • 约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的 ​
  • 保证数据库中数据的正确、有效性和完整性
分类 ​
约束描述关键字
非空约束限制该字段的数据不能为nullNOT NULL
唯一约束保证该字段的所有数据都是唯一、不重复的UNIQUE
主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARY KEY
默认约束保存数据时，如果未指定该字段的值，则采用默认值DEFAULT
检查约束保证字段值满足某一个条件CHECK
外键约束用来让两张表之间建立连接，保证数据的一致性和完整性FOREIGN KEY
一般约束 ​
CREATE Table key_user (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID 主键',
  name VARCHAR(10) UNIQUE NOT NULL COMMENT '姓名 非空唯一',
  age INT check(
      age > 0
      and age < 120
  ) COMMENT '年龄 0-120',
  status char(1) DEFAULT '1' COMMENT '状态',
  gender CHAR(1) COMMENT '性别'
) COMMENT '用户表';

INSERT INTO
  key_user(name, age, status, gender)
VALUES
  ('Tom', 19, '1', 'm'),
  ('Jack', 22, '1', 'm'),
  ('Tomas', 21, '0', 'm');

INSERT INTO
  key_user (name, age, status, gender)
VALUES
  ('Mina', 22, '1', 'f'),
  ('Meys', 21, '1', 'f');
  • 主键自增的正确性怎么保证？
外键约束 ​

  • 用来建立两表之间的联系

  • 语法

create table table_name (
    name varchar(10),
    ......
    [constraint] [foreign_key_name] foreign key (外键字段名) references 主表(主表列名)
);
--添加外键
alter table table_name add constraint foreign_key_name foreign key (外键字段名) references 主表(主表列名);
--删除外键
alter table table_name drop foreign key foreign_key_name;
  • 删除、更新行为
行为说明
NO ACTION当在父表中删除/更新对应的记录时，首先检查记录是否有对应的外键，有则不允许删除/更新（= restrict）
RESTRICT当在父表中删除/更新对应的记录时，首先检查记录是否有对应的外键，有则不允许删除/更新（=no action）
CASCADE当在父表中删除/更新对应的记录时，首先检查记录是否有对应的外键，有则也删除/更新外键在子表中的记录
SET NULL当在父表中删除对应的记录时，首先检查记录是否有对应的外键，有则设置子表中的外键为null
SET DEFAULT父表中有变更时，子表将外键列设置成一个默认值
alter table table_name add constraint

多表查询： ​

多表关系 ​
  • 一对多（多对一）
  • 部门和员工
  • 多对多
  • 学生和课程（中间表 n×m）
  • 一对一
  • 多用于单表的拆分（提升操作效率）
多表查询概述 ​
  • 从多张表中查询数据
  • 笛卡尔积
  • 数学中两个集合的组合总数
  • 分类
  • 连接查询
    • 内连接：相当于查询集合A、B的交集部分数据
    • 外连接：
      • 左外连接：查询左表所有数据，以及两表交集部分
      • 右外连接：查询右表所有数据，以及两表交集部分
    • 自连接：当前表与自身的连接查询，自连接必须使用表的别名
  • 子查询
内连接 ​
  • 隐式内连接
select 字段列表 from 表1，表2 where conditions;
  • 显式内连接

select 字段列表 from 表1 [inner] join 表2  on 连接条件;
外连接 ​
  • 左外连接
select 字段列表 from 表1 left [outer] join 表2 on conditions;
  • 右外连接
select 字段列表 from 表1 right [outer] join 表2 on conditions;
自连接 ​
select colums from table_name join table_name  on conditions
子查询 ​

  • SQL语句中嵌套select语句，为嵌套查询（子查询）

  • 结果分类

  • 标量子查询 ​
    • 返回单值（数字、日期、字符串等等）
    • 常见操作符 =,<>,>,>=,<,<=
  • 列子查询 ​

    • 返回的结果是一列（可以多行）

    • 操作符：IN NOT IN ANY SOME ALL

​

  • 行子查询 ​
    • 返回结果是一行（可多列）
    • 常用操作符：= 、<> 、IN、NOT IN
  • 表子查询 ​
    • 多行多列
    • 常用操作符：IN

  • 位置分类

操作符描述
IN在指定的集合范围之内，多选一
NOT IN不在指定的集合范围内
ANY子查询返回列表中，任意一个满足即可
SOME与`ANY` 等同，使用`SOME` 的地方都可以使用`ANY`
ALL子查询返回列表所有值必须满足
  • where 、from 、select之后

事务： ​

简介：事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，同时成功同时失败

事务操作 ​
  • 查看/设置事务提交方式
select @@autocommit;
set @@autocommit = 0;

  • 提交事务

  • commit

  • 回滚事务

  • rollback

事务的四大特性 ​
  • 原子性(Atomicity)
  • 事务是不可分割的最小操作单元
  • 一致性(Consistency)
  • 事务完成时，数据要保持一致
  • 隔离性(Isolation)
  • 并发事务不会相互干扰
  • 持久性(Durability)
  • 事务一旦提交或回滚，对数据库中的数据的改变就是永久的
并发事务的问题 ​
问题描述
脏读一个事务读到另外一个事务还没有提交的数据
不可重复读一个事务先后读取同一记录，但是两次读取的数据不同
幻读一个事务按照条件查询数据时，没有对应的数据行，但是在插入时，但发现这行数据已经存在了
##### 事务的隔离级别
隔离级别脏读不可重复读幻读
read uncommittedyyy
read committednyy
repeatable readnny
serializablennn
- 查看事务隔离级别： 
select @@transaction_isolation;
  • 设置事务隔离级别：
set [session global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable }