MySQL基础篇
一、SQL
1.1 DDL(Data Definition Language) —— 数据定义语言,用来定义数据库对象(数据库、表、字段)
1.1.1 数据库操作
# 查询
SHOW DATABASES; #查询所有数据库
SELECT DATABASE(); #查询当前数据库
# 创建
CREATE DATABE [IF NOT EXISTS] 数据库名 [DEFAULT CHARES 字符集] [COLLATE 排序规则];
# 删除
DROP DATABASE [IF EXISTS] 数据库名;
# 使用
USE 数据库名;
1.1.2 表操作
# 创建
CREATE TABLE 表名(
字段1 字段1的类型 [COMMENT 字段1的注释],
...,
字段n 字段n的类型 [COMMENT 字段n的注释]
) [COMMENT 表注释]
默认编码 utf8m64
示例:
# 查询
SHOW TABLES; #查询当前数据库所有表
DESC 表名; #查询表结构
SHOW CREATE TABLE 表名; #查询指定表的建表语句
# 修改
# 添加字段
ALTER TABLE 表名 ADD 字段名 类型 [COMMENT 注释] [约束];
# 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型;
# 移动字段到某字段的后面
ALTER TABLE 表名 MODIFY 字段名 字段类型 AFTER 字段;
# 移动字段到最前面
ALTER TABLE 表名 MODIFY 字段名 字段类型 FIRST;
# 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [COMMENT 注释];
# 删除字段
ALTER TABLE 表名 DROP 字段名;
# 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
# 删除
# 删除表
DROP TABLE [IF EXISTS] 表名;
# 删除指定表并重新创建该表
TRUNCATE TABLE 表名;
1.1.3 数据类型
age TINYINT UNSIGEND #设置age字段的类型:无符号的TINYINT(0, 255)
char(10)和varchar(10):
char(10)固定占用10个字符空间,超出10个字符会报错。性能好
varchar(10)会根据内容计算内存空间,如果只有一个字符只占用一个字符空间,超出10个字符会报错。性能较差
1.2 DML(Data Manipulation Language)—— 数据库操作语言,用来对数据库表中的数据进行增删改
1.2.1 增加(插入)数据:INSERT
- 给指定字段添加数据
INSERT INTO 表名(字段名1, 字段名2, ...) VALUES(值1, 值2, ...);
- 给全部字段添加数据
INSERT INTO 表名 VALUES(值1, 值2, ...);
- 批量给指定字段添加数据
INSERT INTO 表名(字段1, 字段2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), ...;
- 批量给全部字段添加数据
INSERT INTO 表名 VALUES(值1, 值2, ...), (值1, 值2, ...), ...;
1.2.2 修改数据:UPDATE
UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];
注意:如果不加条件,则会修改整张表的所有数据
1.2.3 删除数据:DELETE
DELETE FROM 表名 [WHERE 条件];
注意:- 不加条件则删除所有数据
- 不能删除某一个字段的值,可以使用UPDATE设置为NULL
1.3 DQL(Data Query Language) —— 数据查询语言,用来查询数据库中表的记录
1.3.1 查询关键字:SELECT
1.3.2 基本查询
- 查询多个字段
SELECT 字段1, 字段2, ... FROM 表名;
SELECT * FROM 表名;
- 设置别名,AS可以省略
SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], ... FROM 表名;
- 去重复记录
SELECT DISTINCT 字段列表 FROM 表名;
1.3.3 条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
1.3.4 聚合查询
聚合函数:将一列数据作为一个整体,进行纵向计算。
SELECT 聚合函数(字段列表) FROM 表名;
1.3.5 分组查询
SELECT 字段列表 [别名] FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后的过滤条件];
- where和having的区别
- 执行时机不同:where是分组之前进行过滤,不满足where条件的,不参与分组;而having是分组之后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
- 注意:
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
1.3.6 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
- ASC:升序(默认值)
- DESC:降序
- 如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
1.3.7 分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
1.3.8 执行顺序
1.4 DCL(Data Contorl Language) —— 数据控制语言,用来管理数据库用户、控制数据库访问权限
1.4.1 管理用户
# 查询用户
USE mysql; #用户信息存储在mysql数据库中user表中
SELECT * FROM user;
# 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; # '%' 表示任意主机
# 修改用户密码
ALTER USER '用户名'@'主机名' INDENTIFIED BY WHIT mysql_native_password BY '新密码';
# 删除用户
DROP USER '用户名'@'主机名';
1.4.2 权限控制
# 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
# 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
# 撤销权限
REMOVE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
- 注意
- 多个权限之间,可以用逗号分隔
- 授权时,数据库名和表名可以使用 * 进行通配,代表所有
二、函数
函数是指一段可以直接被另一段程序调用的程序或代码。
2.1 字符串函数
2.2 数值函数
#随机生成6位数:
select lpad(round(rand() * 1000000, 0), 6, '0');
2.3 日期函数
# date_add 从当前时间往后推70天
select date_add(now(), interval 70 day);
# datediff 两个日期之间的差值, 第一个时间 - 第二个时间
select datediff('2022-5-1', '2022-4-1');
2.4 流程函数
# 查询emp表中员工姓名和工作地址(北京/上海 --> 一线城市 ,其他 --> 二线城市)
select
name,
(case workaddress when '北京' than '一线城市' when '上海' than '一线城市' else '二线城市' end) as '工作地址'
from emp;
三、约束
概念:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确、有效性和完整性。
3.1 分类
# 自动增长:AUTO_INCREMENT
create table users(
id int primary key auto_increment comment '主键约束、自增',
name varchar(10) not null comment '非空约束、姓名',
age int check ( age > 0 and age < 150 ) comment '检查约束、年龄',
status char(1) default '1' comment '默认约束(默认值为1)、状态',
gender char(1) comment '性别'
)comment '用户表';
# 约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
3.2 外键约束
外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
具有外键的表称为子表(从表),外键所关联的表称为父表(主表)
- 添加外键
# 创建表的时候之间添加
CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名)
);
# 表结构创建好后额外增加
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名);
# 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
- 删除/更新行为
# 删除/更新语法
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 NO DELETE 行为;
# 外键的删除和更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade no delete cascade;
alter table emp add constraint fk_eml_dept_id foreign key (dept_id) references dept(id) on update set null no delete set null;
四、多表查询
4.1 多表关系
- 一对多(多对1)
- 关系:一个部门对应多个员工,一个员工对应一个部门
- 实现:在多的一方建立外键(员工),指向一的一方的主键(部门)
- 多对多
- 关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
- 实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
- 一对一
- 关系:用户和用户详情,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现:在任意一方加入外键,关联另一方的主键,并设置外键唯一的(UNIQUE)
4.2 多表查询
- 笛卡尔积:笛卡尔乘积是指在数学中,两个集合(A集合和B集合)所有的组合情况。在多表查询时,需要消除笛卡尔积
# 笛卡尔积
select * from 表1, 表2;
# 消除无效笛卡尔积
select * from 表1, 表2 where 表1.外键字段名 = 表2.主键字段名;
4.3 多表查询的分类(连接查询和子查询)
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
- 显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件 ...;
- 隐式内连接
- 外连接
- 左外连接:查询左表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件 ...;
- 右外连接:查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件 ...;
- 左外连接:查询左表所有数据,以及两张表交集部分数据
- 自连接:当前表与自身的连接查询,自连接必须使用别名
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
- 内连接:相当于查询A、B交集部分数据
- 子查询(嵌套查询)
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
- 子查询外边的语句可以是INSERT/UPDATE/DELETE/SELECT任何一个
- 根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
- 标量子查询(子查询结果为单个值)
- 根据子查询位置分为:WHERE之后、FROM之后、SELECT之后
4.4 联合查询-union,union all
# 对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...;
注意:
- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
- union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
五、事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
#查看事务
SELECT @@autocommit;
#事务控制
#设置事务提交方式,1为自动(默认) 0为手动
SET @@autocommit = 0
#开启事务
START TRANSACITON|BEGIN;
#提交事务
COMMIT;
#回滚事务
ROLLBACK;
5.1 事务的四大特性
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败
- 一致性(Consistency):事务完成时,必须使所有数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的
5.2 并发事务问题
5.3 事务的隔离级别
Oracle的默认事务隔离级别是 Read committed
#查看事务隔离级别
SELECT @@ TRANSACTION_ISOLATION;
#设置事务隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {隔离级别}
# SESSION:会话级别,针对当前客户端窗口有效
# GLOBAL:针对所有客户端窗口有效