MySQL基础学习笔记
课程引入
为什么要学数据库
常见的数据库管理系统有MySQL、Oracle、DB2、SqlServer。程序员在客户端程序,通过数据库管理系统,对数据进行增删改查。其中,MySQL由于开源、免费等因素,是当前最受欢迎的数据库管理系统。淘宝、网易、百度、Facebook等众多互联网公司都在使用MySQL,学精了MySQL,就业市场广阔。
将数据存放在内存中不能持久化存储,存放在普通文件中不方便管理。
数据库管理系统的好处:
- 实现数据持久化
- 使用完整的管理系统统一管理,易于查询
数据库相关概念
DB:数据库(database),存储数据的“仓库”,他保存了一系列有组织的数据。
DBMS:数据库管理系统(database management system),数据库是通过DBMS进行管理的。
SQL:结构化查询语言(structure query language)。
SQL的优点
- 几乎所有的DBMS都支持SQL
- 简单易学
- 虽然简单,但是功能强大,灵活使用可以完成诸多复杂、高级的功能。
数据库是如何存储数据的
- 将数据放在表上,再将表放进库中。
- 一个数据库可以有多个表,每个表名称具有唯一性。
- 表有自己的特性,表示表中数据的属性,一张表就类似于C++中的一个类。
- 表由列组成,列也称为字段。每一个列就类似于C++中类的一个属性。
- 表中数据按行存储,每一行类似于C++中的一个对象。
MySQL服务的登陆和退出
1 | # MySQL服务的启动 |
1 | # MySQL服务的停止 |
1 | # 连接本机 |
1 | # 退出 |
MySQL常见命令
1 | # 查看当前所有数据库 |
1 | # 打开指定的库 |
1 | # 查看库中的表 |
1 | # 创建表 |
1 | # 查看表结构 |
MySQL语法规范
- 不区分大小写,但是建议关键字大写,其他小写
- 每条命令用分号结尾
- 命令可以缩进和换行
- 单行注释
1 | # 单行注释 |
- 多行注释
1 | /* |
SQL语言的细分
DQL语言(Data Query Language):查询功能
DML语言(Data Manipulation Language):增删改功能
tips:也有书籍将上面两种语言统称为DML,也即合并了增删改查四个功能。DDL语言(Data Define Language):数据定义语言
TCL语言(Transaction Control Language):事务控制语言
查询(DQL语言)
基础查询
1 | # 语法 |
1 | # 查询单个字段 |
1 | # 查询多个字段 逗号隔开 |
1 | # 查询所有字段 |
1 | # 起别名 AS关键字 (空格也行) |
1 | # 去重 Distinct关键字 |
条件查询
1 | # 语法 |
1 | # 条件运算符 |
1 | # 逻辑运算符 |
1 | # 按条件运算符查询 |
1 | # 逻辑运算符连接条件运算符查询 |
1 | # 模糊查询 |
1 | /* |
1 | # 转义字符:反斜杠 或 escape关键字 |
1 | # between and 查找区间范围内的数字 |
1 | # in 判断字段值是否属于列表中的某一项 |
1 | # is null 判断字段值是否为null |
排序查询
1 | # order by 排序列表(asc升序,desc降序) |
函数
1 | # 调用 |
常见的字符函数
注意,SQL语言中的索引都从1开始。
1 | # 获取字符串长度(字节) |
常见的数学函数
1 | # round 四舍五入 |
常见的日期函数
1 | # 返回当前系统时间 |
流程控制函数
1 | # if函数 类似于C语言中的三目运算 |
1 | # case函数 类似于C语言中的switch case |
分组(统计)函数
单行函数一般作用于一个字段的一个值,而分组函数一般作用于一个字段的所有值。
1 | # SUM(求和) |
分组查询
1 | # group by 按照字段值分组 |
1 | # 分组函数结合分组查询 |
1 | # 分组前筛选 |
1 | # 分组后筛选 |
1 | # 分组前筛选+分组后筛选 |
连接查询
1 | # 笛卡尔乘积现象:表1有m行,表2有n行,结果总共有n*m行。 |
为表起别名
1 | # 提高语句的简洁度 |
等值连接
只有两个表的字段值相同时才会被查询到。
1 | SELECT |
非等值连接
1 | # 不用等于号作为链接 |
自连接
1 | # 一张表连接自己 |
sql99语法_等值连接
1 | SELECT |
等值连接
1 | SELECT |
1 | SELECT |
非等值连接、自连接同理。
左(右)外连接
1 | /* |
全外连接
1 | /* |
交叉连接
1 | /* |
子查询
1 |
where后面的标量子查询
1 | SELECT |
1 | SELECT |
having后面的标量子查询
1 | SELECT |
where后面的列子查询
1 | SELECT |
where后面的行子查询
1 | SELECT |
select后面的标量子查询
1 | SELECT |
from后面的子查询
1 | SELECT |
exists后面的子查询(相关子查询)
1 | # exists(是否存在) 返回 0 或 1 |
1 | SELECT |
limit 分页查询
1 | # limit [begin] [size] |
union 联合查询
1 | /* |
增删改(DML语言)
插入记录
1 | /* |
写法一
1 | INSERT INTO boys(id,boyname,usercp) |
1 | # 列名可以省略,默认为表中的字段。 |
写法二:
1 | INSERT INTO boys |
写法三:
1 | INSERT INTO temp SELECT |
修改记录
1 | UPDATE boys |
1 | UPDATE boys |
删除记录
1 | # 删除一定是一行 |
1 | DELETE |
1 | # truncate删除记录,删除所有记录 |
delete删除记录可以回滚,truncate删除记录不可以回滚。
数据定义语言(DDL语言)
库的管理
1 | # 创建库 |
1 | # 没有才创建库 |
库的删除
1 | # 删除库 |
1 | # 没有才删除库 |
表的创建
1 | # 语法: |
1 | CREATE TABLE books ( |
表的修改
1 | ALTER TABLE 表名 [ADD/DROP/change/MODIFY] COLUMN 列名 |
修改列名
1 | # 语法: |
1 | ALTER TABLE books |
修改列的类型和约束
1 | # 语法: |
1 | ALTER TABLE books |
增加列
1 | # 语法: |
1 | ALTER TABLE books |
删除列
1 | # 语法: |
1 | ALTER TABLE books |
修改表名
1 | # 语法: |
1 | ALTER TABLE books |
表的复制
1 | # 只复制表结构 |
1 | # 复制结构及数据 |
表的删除
1 | # 删除表 |
1 | # 没有才删除表 |
数据类型
整形
类型 | 字节 | 范围 |
---|---|---|
Tinyint | 1 | -128~127 |
Smallint | 2 | -32768~32767 |
Mediumintint | 3 | -8388608~8388607 |
Int | 4 | -2147483648~2147483647 |
Bigint | 8 | -9223372036854775808~9223372036854775807 |
1 | CREATE TABLE temp ( |
小数
类型 | 字节 |
---|---|
Float | 1 |
Double | 2 |
1 | CREATE TABLE temp ( |
字符类型
|类型|最多字符数|描述及存储需求|
|–|–|–|–|
|Char(M)|M|M为0255的整数|65535的整数|
|Varchar(M)|M|M为0
Char为固定长度字符,Varchar为可变长度字符,因此Char更耗费空间,但是效率更高。
还有一些不太常用的字符类型:
Binary和Varbinary,类似于Char和Varchar,不过它们是用来存储二进制串。
Enum(‘a’,’b’,’c’),括号中是列表值,最终选择的值只能是在列表中的。
Set(‘a’,’b’,’c’),类似于Enum,不过Enum是单选,Set可以多选。
1 | CREATE TABLE temp (a CHAR(10)); |
1 | CREATE TABLE temp (a BINARY(10)); |
1 | CREATE TABLE temp (a SET('1', '2', '3')); |
日期型
数据类型 | 用处 |
---|---|
date | 保存日期 |
time | 保存时间 |
year | 保存年份 |
datetime | 保存日期和时间 |
timestamp | 保存日期和时间 |
约束
常见约束
约束类型 | 用处 |
---|---|
NOT NULL | 字段值非空 |
DEFAULT | 字段值有默认值 |
PRIMARY KEY | 主键,字段值具有唯一性,并且非空 |
UNIQUE | 字段值具有唯一性 |
FOREIGN KEY | 外键,改字段值必须来自于主表的关联列的值【比如学生的专业,必须来自于专业表】 |
创建约束
列级约束
1 | CREATE TABLE temp1 ( |
表级约束
1 | 语法格式: |
一般来说,外键使用表级约束,其他使用列级约束。
组合主键以及组合唯一键。功能就是当组合起来冲突时才算冲突。
1 | CREATE TABLE temp ( |
外键的特点
- 必须在从表创建外键。
- 主从表关联列的类型要一致。
- 主表中的关联列必须是key(一般是主键或唯一键)
- 插入数据时,先插入主表,再插入从表。删除数据时,先删除从表,再删除主表。
修改表时添加约束
1 | 语法: |
修改表时删除约束
1 | 语法: |
标识列
又称自增长列,可以不用手动插入值,系统提供默认的序列值,默认从1开始。
标识列的特点:
- 必须是一个key
- 一个表最多一个标识列
- 标识必须是数值类型(整数或小数)
添加标识列
1 | # 创建表时添加标识列 |
删除标识列
1 | ALTER TABLE t1 MODIFY COLUMN id INT; |
事务控制语言(TCL)
事务:一个或一组sql组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。
事务的属性(ACID)
原子性(Atomicity):事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性(consistency):事务必须使数据库从一个一致性状态变换到另一个一致性状态。
隔离性(Isolation):隔离性是当多个用户并发访问数据库时,比如同时操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。
持久性(Durability):持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务的创建
隐式事务:事务没有明显的开启和结束
比如insert、update、delete语句
显式事务:事务具有明显的开启和结束
前提:必须先设置自动提交功能为禁用
1 | SET autocommit = 0; |
事务并发可能导致的问题
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种并发问题:
问题 | 描述 |
---|---|
脏读 | 对于两个事务T1,T2,T1读取了已经被T2更新但还没被提交的字段之后,若T2回滚,T1读取的内容就是临时且无效的。 |
不可重复读 | 对于两个事务T1,T2,T1读取了一个字段,然后T2更新了该字段之后,T1再次读取同一个字段,值就不同了。 |
幻读 | 对于两个事务T1,T2,T1读取了一个字段,然后T2在该表中插入了一些新的行,T1再次读取同一个表,表就多了几行。 |
#### 事务的隔离性 |
数据库系统必须具有隔离并发运行各个事务的能力,使它们不会互相影响,避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱。
事务的隔离级别
隔离级别 | 描述 |
---|---|
READ UNCOMMITTED(读未提交数据) | 允许事务读取未被其他事务提交的变更。脏读、不可重复读、幻读都可能出现。 |
READ COMMITTED(读已提交数据) | 只允许事务读取已被其他事务提交的变更。可以避免脏读,但不可重复读、幻读仍可能出现。 |
REPEATABLE READ(可重复读) | 确保事务可以多次从一个字段读取相同的值。在这个事务持续期间,不允许其他事务对这个字段进行更新。可能出现幻读。 |
Serializable(串行化) | 在这个事务持续期间,不允许其他事务对这个字段进行更新、删除、插入。可以避免所有并发问题,但是性能十分低下。 |
mysql中默认第三种隔离级别:repeatable read
oracle中默认第二种隔离级别:read committed
查看隔离级别
1 | select @@transaction_isolation; |
设置隔离级别
1 | set session【global】 transaction isolation level 隔离级别; |
savepoint的使用
1 | SET autocommit = 0; |
视图
视图:一种虚拟存在的表,行和列的数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的,只保存了sql逻辑,不保存查询结果。
应用场景:
- 多个地方用到同样的查询结果
- 该查询结果使用的sql语句比较复杂
视图的创建
1 | CREATE VIEW V1 |
视图的修改
1 | # 方式一 CREATE OR REPLACE VIEW 视图名 |
1 | # 方式二 ALTER VIEW 视图名 |
视图的删除
1 | DROP VIEW 视图名; |
视图的查看
1 | DESC VIEW 视图名; |
视图的更新
对视图进行insert、update、delete等操作时,会在原表中做对应的更新,不在视图中的字段值为NULL。但是以下情况不能对视图进行更新:
- 包含以下关键字的sql语句:分组函数、distinct、group by、having、union、union all
- 常量视图
- select中包含子查询
- 连接查询
- from一个不能更新的表
- where子句中的子查询引用了from子句中的表
存储过程和函数
变量
一、系统变量:
- 全局变量
- 会话变量
二、自定义变量:
- 用户变量
- 局部变量
一、系统变量
系统变量由系统提供,不是用户定义,属于服务器层面使用的语法:
注意:如果是全局级别,使用关键字GLOBAL,如果是会话级别,使用关键字SESSION,都不使用默认SESSION。
查看所有系统变量
1
2SHOW GLOBAL VARIABLES; # 查看所有系统变量
SHOW SESSION VARIABLES; # 查看所有会话变量查看满足条件的部分系统变量
1
SHOW GLOBAL VARIABLES LIKE ‘%char%’; # 查看名称中包含‘char’的所有系统变量
查看指定的系统变量的值
1
2SELECT @@GLOBAL.系统变量名; # 查看系统变量名的值
SELECT @@global.autocommit;为指定的系统变量赋值
1
2
3
4# 方式一
SET global|SESSION 系统变量名 = 值;
# 方式二
SET @@global|SESSION.系统变量名 = 值;
二、自定义变量
用户变量
作用域:针对于当前会话有效,同于会话变量的作用域。
声明并初始化
1
2
3
4# 三种方式
SET @用户变量名 = 值;
SET @用户变量名 := 值;
SELECT @用户变量名 := 值;赋值
1 | # 方式一 |
1 | # 方式二 |
- 查看
1 | SELECT @用户变量名; |
局部变量
作用域:仅仅在定义它的begin end中有效。
- 声明
1
2DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值; - 赋值
1 | # 方式一 |
1 | # 方式二 |
- 查看
1 | SELECT 局部变量名; |
存储过程
含义:一组预先编译好的SQL语句的集合,理解成批处理语句。
优点:
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率。
存储过程的创建
1 | CREATE PROCEDURE 存储过程名(参数列表) |
注意:
参数列表包含三个部分:参数模式 参数名 参数类型
参数模式有三种:
IN:该参数作为输入
OUT:该参数可以作为输出(返回值)
INOUT:该参数即作为输入,又可以作为输出如果存储过程体只有一句话,可以省略BEGIN END。存储过程体中的每条SQL语句的结尾必须加分号;
默认情况下,delimiter是分号,可以用DELIMITER重新设置结尾符号。如果delimiter不重新设置,会和存储过程中的分号冲突,因此需要事先将delimiter设置成其他符号:
1
DELIMITER $$
存储过程的调用
1 | CALL 存储过程名(实参列表); |
存储过程使用实例
无参的存储过程实例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19CREATE TABLE
IF NOT EXISTS t1 (num INT);
DELIMITER $$
CREATE PROCEDURE ins1 ()
BEGIN
INSERT INTO t1
VALUES
(1),
(3) ;
END $$
CALL ins1 () $$
SELECT
*
FROM
t1 $$带IN模式参数的存储过程实例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18CREATE TABLE
IF NOT EXISTS t1 (num INT);
DELIMITER $$
CREATE PROCEDURE ins2 (IN val INT)
BEGIN
INSERT INTO t1
VALUES
(val);
END $$
CALL ins2 (10086) $$
SELECT
*
FROM
t1 $$带OUT模式参数的存储过程实例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20CREATE TABLE
IF NOT EXISTS t1 (num INT);
DELIMITER $$
CREATE PROCEDURE get_sum (OUT s INT)
BEGIN
SELECT
sum(num) INTO s
FROM
t1 ;
END$$
SET @answer = 0$$
CALL get_sum (@answer)$$
SELECT
@answer$$带INOUT模式参数的存储过程实例:
1 | # 使两个变量翻倍 |
存储过程的删除
1 | DROP PROCEDURE 存储过程名; |
存储过程的查看
1 | SHOW CREATE PROCEDURE 存储过程名; |
函数
函数与存储过程的区别:
存储过程:可以有0个返回值,也可以有多个返回值,适合做批量插入、更新。
函数:有且仅有1个返回值,适合做数据处理后返回一个结果。
函数的创建
1 | CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 |
注意:
- 参数列表只包含两个部分:参数名 参数类型
- 函数体内肯定有return语句,如果没有会报错
函数的调用
1 | SELECT 函数名(参数列表) |
函数使用实例
1 | CREATE TABLE |
函数的删除
1 | DROP FUNCTION 函数名; |
函数的查看
1 | SHOW CREATE FUNCTION 函数名; |
流程控制结构
顺序结构:程序从上往下依次执行。
分支结构:程序从两条或多条路径中选择一条去执行
循环结构:程序在满足一定条件的基础上,重复执行一段代码
分支结构
1.if函数
功能:实现简单的双分支
语法:
1 | if(表达式1,表达式2,表达式3) |
2.case结构
情况1:类似于C++中的switch语句,一般用于实现等值判断。
语法:
1 | CASE 变量|表达式|字段 |
情况2:类似于C++中的多重if语句,一般用于实现区间判断。
语法:
1 | CASE 变量|表达式|字段 |
如果WHEN中的值或条件成立,立即执行对应的THEN后面的语句,并且结束CASE
案例:
1 | delimiter $$ |
3.if结构
语法
1 | IF 条件1 THEN 语句1; |
案例:
1 | delimiter $$ |
循环结构
必须放在BEGIN END中,不可以单独使用。
分类:while、loop、repeat
循环控制:
iterate 类似于 C++中的continue,结束本次循环,继续下一次循环。
leave 类似于 C++中的break,跳出循环。
1.while
语法:
1 | 【标签:】WHILE 循环条件 DO |
案例:
1 | delimiter $$ |
2.loop
语法:
1 | 【标签:】LOOP |
案例:
1 | delimiter $$ |
3.repeat
语法:
1 | 【标签:】REPEAT |
案例:
1 | delimiter $$ |