
MySQL数据库基础篇详解
MySQL 基础篇
MySQL 基础学习笔记(纯文字版)。原文中的函数表、约束表、示意图、操作符表、隔离级别表均已转为文字与表格,内容与原文一致。
目录
一、SQL 概述
数据库:存储数据的仓库,数据是有组织地进行存储(DB)。
数据库管理系统(DBMS):管理和操纵数据库的大型软件,例如 MySQL。
SQL:操作关系型数据库的编程语言,定义了一套操作关系型数据库的统一标准。
主流的关系型数据库管理系统:Oracle、MySQL、Microsoft SQL Server、PostgreSQL、SQLite。
注意:SQL 为关系型数据库提供了一套统一标准。
MySQL 启动
net start mysql80
net stop mysql80MySQL 数据库客户端连接
方式一:系统自带的命令行执行指令(注意:使用此方法需要设置环境变量)—— 推荐。
mysql [-h 127.0.0.1] [-P 3306] -u root -p-h:指定主机连接地址-P:指定端口(大写P;小写-p表示输入密码)
方式二:MySQL 自带的客户端命令行。
数据模型
客户端 → DBMS → 多个数据库 → 表结构(多张)→ 二维表关系型数据库(RDBMS)
- 建立在关系模型基础上,由多张互相连接的二维表组成的数据库。
- 特点:使用表存储数据,格式统一;使用 SQL 语言,有统一标准。
二、SQL
通用语法
- 可以单行或者多行书写,以分号结尾。
- 注释:
- 单行注释:
-- 注释内容或# 注释内容 - 多行注释:
/* 注释内容 */
- 单行注释:
SQL 语句分类(基础也是重点)
| 分类 | 名称 | 说明 |
|---|---|---|
| DDL | 数据定义语言 | 定义/修改数据库对象结构(数据库、表、字段等) |
| DML | 数据操作语言 | 对数据库表中的数据进行增删改 |
| DQL | 数据查询语言 | 查询数据库表中的记录 |
| DCL | 数据控制语言 | 创建数据库用户、控制数据库的访问权限 |
DDL
数据库操作
show databases; -- 查询所有数据库
select database(); -- 查询当前数据库
-- 创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
drop database [if exists] 数据库名; -- 删除数据库
use 数据库名; -- 使用某个数据库表操作 —— 查询
show tables; -- 查询当前数据库所有表
desc 表名; -- 查询表结构
show create table 表名; -- 查询指定表的建表语句表操作 —— 创建
create table 表名 (
字段1 字段1类型 [comment 字段1注释],
字段2 字段2类型 [comment 字段2注释],
字段3 字段3类型 [comment 字段3注释],
字段4 字段4类型 [comment 字段4注释]
-- ……
) [comment 表注释];表操作 —— 数据类型
- 数值类型
- 字符串类型
char:定长字符串,性能高varchar:变长字符串,性能稍差,需要根据内容计算所占用的空间
- 日期时间类型:
date、time、year、datetime、timestamp
表操作 —— 修改
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]; -- 添加字段
alter table 表名 modify 字段名 新数据类型(长度); -- 修改数据类型
-- 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
alter table 表名 drop 字段名; -- 删除字段
alter table 表名 rename to 新表名; -- 修改表名表操作 —— 删除
drop table [if exists] 表名; -- 删除表
-- 删除指定表,并重新创建该表(空表)—— 此地空余黄鹤楼
truncate table 表名;MySQL 的图形化界面工具推荐:DataGrip。
DML
对数据库表中的数据记录进行增删改操作:
INSERTUPDATEDELETE
添加、修改、删除数据的具体语法见博客文章。
DQL
数据查询语言(使用频率最高)—— important。用来查询数据库中表的记录。
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数;注意点:where 与 having 的区别
- 执行时机不同:where 在分组之前进行过滤,不满足 where 条件的不参与分组;而 having 是分组之后对结果进行过滤。
- 判断条件不同:where 不能对聚合函数进行判断,而 having 可以。
排序查询
order by —— 支持多字段排序。
分页查询
limit 起始索引, 查询记录数;分页查询是数据库的方言,不同的数据库有不同的实现。
编写顺序与执行顺序
- 编写顺序:如上。
- 执行顺序:
from → where → group by → having → select → order by → limit
DCL
- 管理用户(注意:主机名可以使用
%通配) - 权限控制
三、函数
函数是一段可以直接被另一段程序调用的程序或者代码。
字符串函数
MySQL 中内置了很多字符串函数,常用的几个如下:
| 函数 | 功能 |
|---|---|
CONCAT(S1,S2,…Sn) |
字符串拼接,将 S1, S2, … Sn 拼接成一个字符串 |
LOWER(str) |
将字符串 str 全部转为小写 |
UPPER(str) |
将字符串 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 默认值 |
四、约束
约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确性、有效性、完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为 null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | DEFAULT |
| 检查约束(8.0.16 版本之后) | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | FOREIGN KEY |
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
外键约束:用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
五、多表查询
多表关系
实际项目开发中,会根据业务需求以及业务模块之间的关系,分析设计表结构。由于业务之间相互关联,所以各个表之间存在关系:
一对多:一个部门对应多个员工,一个员工对应一个部门。
多对多:学生与课程之间的关系,一个学生可以选择多门课程,一门课程也可以供多个学生选择。需要建立第三张中间表。
一对一:用户与用户详情的关系。一对一关系用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率。实现方式是在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的 UNIQUE。
多表查询概述
概述:从多张表中查询数据。
笛卡尔积:在数学中,两个集合 A 集合和 B 集合的所有组合情况。
举例说明:表 A 只有一列两行(A、B),表 B 只有一列四行(1、2、3、4),两表做笛卡尔积后得到 2 × 4 = 8 行结果:
| 表A | 表B |
|---|---|
| A | 1 |
| A | 2 |
| A | 3 |
| A | 4 |
| B | 1 |
| B | 2 |
| B | 3 |
| B | 4 |
提示:多表查询如果不加连接条件,得到的就是两张表的完整笛卡尔积,其中大部分组合是无意义的,因此需要用连接条件进行筛选。
多表查询分类
- 连接查询:内连接、外连接、自连接。
- 子查询(一张表会与自身进行查询)
内连接
查询的是两张表交集的部分。
- 隐式内连接
- 显式内连接
外连接
- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件 ...;左外连接相当于查询表1(左表)的所有数据,当然也包含表1和表2交集部分的数据。
- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;右外连接相当于查询表2(右表)的所有数据,当然也包含表1和表2交集部分的数据。
自连接
一张表自己连接自己。自连接可以是外连接,也可以是内连接。
select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;联合查询
对于 union 查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
select 字段列表 from 表A ...
union [all]
select 字段列表 from 表B ...;- 联合查询的多张表的列数必须保持一致,字段类型也需要保持一致。
union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
子查询
SQL 语句中嵌套 select 语句,称为嵌套查询,又称子查询。
select * from t1 where column1 = (select column1 from t2);根据子查询结果不同,分为:
- A. 标量子查询(子查询结果为单个值)
- B. 列子查询(子查询结果为一列)
- C. 行子查询(子查询结果为一行)
- D. 表子查询(子查询结果为多行多列)
1) 标量子查询
返回的结果是单个值,最简单的形式。
常用的操作符:=、<>、>=、>、<、<=
2) 列子查询
返回的是一列,可以是多行。
常用的操作符:
| 操作符 | 描述 |
|---|---|
IN |
在指定的集合范围之内,多选一 |
NOT IN |
不在指定的集合范围之内 |
ANY |
子查询返回列表中,有任意一个满足即可 |
SOME |
与 ANY 等同,使用 SOME 的地方都可以使用 ANY |
ALL |
子查询返回列表的所有值都必须满足 |
3) 行子查询
返回的结果是一行(可以是多列)。
常见的操作符:=、<>、IN、NOT IN
4) 表子查询
子查询返回的结果是多行多列,相当于一张表 —— 表子查询。
常用操作符:IN
六、事务
事务:一组操作的集合,它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,这些操作要么同时成功,要么同时失败。
事务操作
查看/设置事务提交方式:
select @@autocommit;
set @@autocommit = 0;提交事务:
commit;回滚事务:
rollback;开启事务:
start transaction;
-- 或
begin;注意:
start transaction是开启一个显式事务,在事务期间暂停 autocommit 的自动提交行为(不是把 autocommit 变量改成 0,而是当前事务内不再逐条自动提交)。
事务四大特性(面试题)—— ACID
- 原子性(Atomicity):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
- 一致性(Consistency):事务完成时,必须所有的数据都保持一致状态。
- 隔离性(Isolation):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability):事务一旦提交或回滚,它对数据库中的数据的改变是永久的。
并发事务问题(面试题)
- 脏读:一个事务读到另外一个事务还没有提交的数据。
- 不可重复读:一个事务先后读取同一条记录,但两次读取的数据不同。
- 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了“幻影”。
事务的隔离级别(面试题)
解决并发事务所引发的问题。既要权衡数据的安全性,又要考虑性能。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(默认) | × | × | √ |
| Serializable | × | × | × |
表中 √ 表示在该隔离级别下仍会出现对应问题,× 表示该问题已被解决。
查看事务隔离级别:
select @@transaction_isolation;设置事务隔离级别:
set [session | global] transaction isolation level
{ read uncommitted | read committed | repeatable read | serializable };注意:事务隔离级别越高,数据越安全,但是性能越低。 越低。
评论 (0)
还没有评论,来写第一条吧~