MySQL 语言的学习笔记,参考课程来自[https://www.bilibili.com/video/av49181542/]
目录
数据库好处
数据库是持久化数据的一种介质,用来存储和管理数据的仓库
- 持久化(persistence):把数据保存到可掉电式存储设备以供之后使用,大多数时候时间内存中的数据存储到数据库中,也可以存储在磁盘文件,XML数据文件中
- 保存数据的容器:数据,集合,文件
- 好处
- 持久化数据到本地,可将数据持久化硬盘
- 可以实现结构化查询,方便管理:方便检索、保证数据的一致性和完整性
- 安全可共享
- 通过组合分析可以产生新数据
数据库概念
DB:database,存储数据的仓库,保存了一系列有组织的数据
- DBMS:database management system, 数据库管理系统(数据库软件,管理工具),数据库是通过DBMS创建和操作的容器。常见的数据库管理系统:MySQL(Oracle公司)/Oracle(甲骨文Oracle公司,产品免费,但是服务收费,价格昂贵)/DB2(IBM公司,适合处理海量数据)/SqlServer(微软公司,只能安装在Windows操作系统中,现在好像可以兼容了)等
- SQL:structure query language, 结构化查询语言,专门用来与数据库通信的语言
1. 不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL
2. 语言简单易学,可以进行非常复杂和高级的数据库操作
数据库特点
将数据放入表中,表存入数据库
- 一个数据库中有多张表,每个表有对应名字,用于标识自己,表名具有唯一性
- 表具有特性,定义了数据在表中如何存储,如字段类型,名字等
- 表由列组成,称为字段,所有表都是由一个或多个列组成,每个列是一个属性
- 表中数据按行存储,每一行是个对象,om数据映射
MySQL软件
背景:
- 前身属于瑞典的一家公司,MySQL AB公司,是一种开放源代码的关系型数据库管理系统
- 08年被sun公司收购
- 09年Sun被Oracle收购
- monty :创始人
- 优点:
- 成本低:开放源代码,可免费使用
- 性能高:执行速度快
- 简单:易于安装和使用,体积小
- DBMS:
- 基于共享文件系统的DBMS(Access),安装access接口就可以了
- 基于客户机-服务器(C/S)的DBMS(MySQL、Oracle、SqlServer),安装数据库一般指的是安装数据库的服务端,企业版和社区版
MySQL的卸载安装
MySQL的数据库管理软件是C/S(客户端/服务端)架构,底层是TCP/IP 的程序
服务器端要先启动,通过端口号监听或等待客户端的连接
- 卸载
- 先停止MySQL服务:计算机–管理–服务–MySQL服务–停止
- 卸载: 控制面板、电脑管家或360安全卫士等第三方软件卸载
- 清除残余文件:找到之前安装的存储位置,把配置文件删除,默认在C:/programdata/mysql
- 清理注册表:regedit清理注册表
- 安装
- 选择64或32位安装
简单使用MySQL
-
启动和停止
-
命令提示符cmd(以管理员身份运行):
-
计算机-管理-服务-找到MySQL
#START net start mysql #STOP net stop mysql
-
-
登录和退出
- mysql自带的客户端,直接输入用户密码登录,
- cmd命令:mysql -h 主机名 -P 端口号 -u 用户名 -p密码
例子: mysql -h localhost -P 3306 -u root -p123456
-p123456 千万不能有空格,小写p
-P 3306 一定是大写P,代表端口port
适用于远程访问,如果是本机,可以省略 -h 主机名 -P 端口号 - 退出:直接输入exit或Ctrl+c退出
- mysql自带的客户端,直接输入用户密码登录,
-
MYSQL 的常见命令
-
查看当前所有的数据库
show databases; -
打开指定的数据库
use 库名 -
查看当前库的所有表
show tables; -
查看其他库的所有表
show tables from 库名; -
创建表
create table 表名( 列名 列类型, 列名 列类型, ...)
-
查看表结构
desc 表名; -
查看数据库服务器当前版本
方式一:登录到MySQL服务器 select version(); 方式二: 登录cmd命令提示符 mysql --version 或 mysql --V
-
-
MySQL的语法规范
-
不区分大小写,但建议关键字大写,表名,列名小写
-
每条命令最好用分号结尾
-
每条命令根据需要进行缩进或换行,f10格式化
-
注释
单行注释: #注释文字 --注释文字 多行注释: /* 注释文字 */
-
DQL(Data Query Language)
-
基础查询
use 表名 #先打开数据表 select 查询列表 from 表名; 查询列表可以使:表中字段、常量值、表达式、函数 查询结果是虚拟表格,不能保存
-
可以实现查询表中的单个,多个(逗号隔开),所有字段(*表示所有,顺序跟数据表一样)
-
查询常量值、表达式、函数,查询后的字段名(列名)就是其本身,字符型和日期型的常量值必须用单引号,数值型不需要
select 100; select `abc`; select 100*90; select version(); /*select 函数(实参列表)*/
-
给字段名起别名,便于理解,便于区分重名的字段名
#如果别名有特殊字符建议加上双引号或单引号 方式1:使用as select 表达式 as 列名, 表达式 as 列名 from 表名 方式2:使用空格 select 表达式 列名, 表达式 列名 from 表名
-
去重
select distinct 查询字段 from 表名 select distinct a,b from table #查询a不重复或者b不重复的并集
-
MySQL中的+
/* java中的+: 运算符,两个操作数都为数值型;连接符,只要有一个操作数为字符串 MySQL中的+:运算符 select 100+90; 两个操作数均为数值型,做加法运算 select '123'+90;有一方为字符型,则试图将字符型数值转换成数值型,若转换成成功则进行加法运算; 若失败,则将字符型数值转为0 select null+90:如果其中一方为控制,则返回null */
-
连接查询(连接的字段不能为null值,否则将返回null)
select ifnull (字段名,为空的返回值) as 别名 select concat ('属性1','连接符','属性2','属性3') as 别名; 可以将属性123 连接成完整的字符串
-
条件查询
#格式 select 查询列表 from 表名 where 筛选条件; 分类: 1. 按条件表达式筛选 条件运算符:> < = != >= <= <=>(安全等于,既可以判断null值,也可判断普通数值) <>(不等于的意思) 2. 按逻辑表达式筛选,用于连接条件表达式 逻辑运算符:&& || ! and or not 3. 模糊查询 like /* 一般和通配符搭配使用,可以判断字符型或数值型 %:任意多个字符,包含0个字符 # 中间包含a ID like '%a%' _:任意单个字符 # a在第二位 ID like '—a—%' #转移字符 ID LIKE '_\_a' 或者 ID LIKE '_$_a' escape '$' */ (not)between and /*两个临界值不能颠倒*/ in /* 判断某字段的值是否属于in列表中的某一项,提高语句简洁度,列表值类型必须统一火兼容 id in ('jobid','addressid','departid') 从这三个ID与id匹配就读取*/ is (not) null /*当=,<>不能用于判断值时,用is not null可以判断null值*/
-
排序查询
select 查询列表 from 表 where 筛选条件 order by 排序列表(支持多个排序,放在前面的就先排) asc/desc;(升序或降序,默认升序) ,一般放在查询语句最后,limit语句除外 例如:order by id asc,kind desc;
-
常见函数查询
/*功能: 类似于Java方法,将一组逻辑语句封装到方法体中,对外暴露方法名 好处:1. 隐藏了实现细节 2. 提高代码的重用性 调用: select 函数名(实参列表) from 表; 分类:1. 单行函数 concat(连接函数)、length(长度函数)、ifnull(判断函数)等 2. 分组函数(组函数、统计函数) */
1.单行函数
-
字符函数
获取参数值的字节个数 select length('张haha'); 值为7,一个汉字占三个字节 拼接字符串 select concat(a,'+',b); 值为a+b 大小写upper,lower select upper ('a'); 值为A 截取字符串,索引从1开始,截取从指定索引字符长度的字符substr=substring select substr('lisihid',2); 值为isihid select substr('lisihid',2,3); 值为isi 返回子串第一次出现的索引,如果找不到返回0 select instr("adribaihroqwrino","ri"); 值为3 去掉前后重复的空格或者字符 select trim('');去空格 select trim('a' from 'aaazhanng aa baoyu aaaa');返回zhanng aa baoyu 用指定的字符实现左填充指定长度,超出的话就截断,lpad,rpad select lpad('asb',10,'*'); 值为*******asb replace替换函数 select replace('adbudf','bu','df');职位addfdf
-
数学函数
四舍五入函数,绝对值四舍五入 round(-1.7880,2) 值为1.79,保留两位小数 向上取整,返回>=该参数的最小整数 ceil 向下取整,返回<=该参数的最大整数 floor 截断, truncate 取余,mod(a,b) 值为a-a/b*b rand(),取随机数,返回0-1之间的值
-
日期函数
返回当前系统日期+时间, now() 返回当前系统日期,不包含时间, curdate() 返回当前系统时间,不包含日期, curtime() 获取指定的部分,年月日,小时,分钟 year,month,monthname(以英文形式返回月份),date,hour,minute,second year(now()) str_to_date('9-19-1990','%m-%d-%Y') 将日期格式的字符解析转换数据库指定的格式,输出为1990-09-19 date_format('2019/2/27','%Y年%m月%d日') 将日期转换成字符,输出为2019年2月27日
-
其他函数
version() 数据库服务器版本 database() 数据库 user() 数据库用户 password('加密字符'); MD5('加密字符');
-
流程控制函数
if(10<5,big,small);
case语句,else可省略
case 查询的字段或表达式 when 常量1 then 要显示的值1或语句1; when 常量2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end 或者============================ case when 条件1 then 要显示的值1或语句1; when 条件2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end
2.分组函数
功能:用作统计使用,又称为聚合函数或统计函数或组函数 分类:求和sum,平均值avg,最大值max,最小值min,计算个数count 简单实用 例如:sum(字段); 特点:1.参数支持类型,sum,avg一般支持数值型,其他类型不报错但不建议 其他分组函数类型都支持,count只计算不为null的字段 所有分组函数都忽略null值 2.可以和diatinct搭配起到去重的效果 3.count( * ), count(常量值)统计行数 效率: MYISAM存储引擎下 count( * ) 效率高; INNODB存储引擎下,count(*) 和 count(常量值)效率差不多,比count(字段)要高一些 4. 和分组函数一同查询的字段要求是group by 后的字段 5. datediff(exp1,exp2)求相差天数
-
分组查询
语法: select 分组函数,列(要求出现在group by的后面) //查询列表特殊,要求是分组函数和group by 后出现的字段 from 表 【where 表里可以执行的筛选条件】 group by 分组的列表 【order by 子句】 having 分组后的筛选条件
特点:1. 分组查询中的筛选条件分为两类,能用分组前筛选的就放在前面
2. 可以按表达式或函数分组
3. 支持多个字段分组,多个字段之间用都逗号隔开,没有先后区别
4. 可以添加排序,放在整个查询最后
数据源 | 位置 | 关键字 | |
---|---|---|---|
分组前筛选 | 原始表 | group by 子句的前面 | where |
分组后筛选 | 分组后的结果表 | group by 子句的后面 | having |
-
连接查询
含义:又称多表查询,查询的字段来自多个表
笛卡尔乘积现象:表1:n行,表2:m行,结果展示为m*n行
发生原因:没有有效的连接条件
分类: 按年代分类:sql92标准,仅仅支持内连接;
sql99标准(推荐),支持内连接和交叉连接,左外连接,右外连接
按功能分类:内连接(等值连接、非等值连接和自连接)
外连接(左外连接、忧外连接和圈外连接)
交叉连接-
sql92标准
(1) 等值连接: 多表等值连接的结果为多表的交集部分;n表连接至少需要n-1个连接条件;多表的顺序没有要求;一般为表起别名;连接可以搭配排序、分组、筛选等其他查询
select a,b from c,d where c.e=d.f;为表起别名:提高语句简洁度,区别歧义;如果为表起了别名,则查询的字段不能使用原来的表名去限定 加筛选条件用and连接 分组筛选 可以加排序 可以实现多表连接
(2) 非等值连接 原理相同
(3) 自连接,实现单表多次查询select a.m,a.n,b,m,b.n from c a, c b where a.m=b.m
-
sql99标准
语法: select 查询列表 from 表1 别名【连接类型】 join 表2 别名 on 连接条件 【where 筛选条件】 【group by 排序列表】 分类: 内连接:inner 等值、非等值、自连接,可以添加排序分组筛选,inner可省略,连接条件放在ON后面,提高分离性,便于阅读,查询交集 外连接: 一般用于查询除了交集部分的剩余的不匹配行,查询结果为主表中的所有记录,如果从表中没有和它匹配的则显示null,查询结果=内连接+主表中有从表中没有的记录 左外 left【outer】,左边是主表 右外:right【outer】,右边是主表 全外:full【outer】,查询结果=内连接+表1有表2没有+表2有表1没有 交叉连接:cross 使用99标准实现的笛卡尔乘积 select 查询列表 from 表1 别名 cross join 表2 别名;
-
-
子查询
含义:出现在其他语句内部的select语句,内查询
内部嵌套其他的select语句的查询,为外查询或主查询
分类:1. 按子查询出现的位置:select后面,只支持标量子查询;from后面,一般支持表子查询;where和having后面;exists后面,相关子查询,一般支持表子查询
2. 按功能分类,子查询结果集的分类: 标量子查询,结果集只有一行一列;列子查询,结果集只有一列但多行;行子查询,结果集有一行多列;表子查询,结果集多行多列- where或having后面
特点:子查询放在小括号内
放在条件右侧
标量子查询一般搭配单行操作符使用(条件运算符)
列子查询一般搭配多行操作符使用(IN/not in,ANY,SOME,ALL,可以同义替换,in——=all;not in=<>all)
子查询的执行优先于主查询,主查询的条件用到子查询的结果
行子查询要求查询的字段有同样的筛选条件规律 - from后面
特点:将子查询结果充当一张表,要求必须起别名 - exists后面,相关子查询
特点:判断子查询结果有没有值,返回0或1
- where或having后面
-
分页查询
应用场景:当要显示的数据一页显示不全,需要分页提交sql请求
特点:limit语句放在查询语句最后
公式:页码page和条目数sizeselect 查询列表 from 表 limit (page-1)*size,size
语法:
select 查询列表 from 表 【join type join表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by排序后的字段】 limit offset,size;//offset要显示条目的起始索引,从0开始,size表示要显示的条目个数,如果从第一条开始,可省略offset
-
联合查询
联合合并,将多条查询语句的结果合并成一个结果
语法:查询语句1 union 查询语句2 union ...
应用场景:查询的结果来自多个表,多个表之间没有关联,但查询的信息一致
特点:多条查询语句的查询列表一致;查询的每一列类型和顺序最好一致;使用union关键字默认去重,使用union all 不去重
DML(data manage language)
-
插入语句
方式1:支持多行多条数据插入,支持子查询 insert into 表名(列名,列名,...) value(值1,值2,...); //插入的值的类型要与列的类型一致或兼容 //不可以为null的列必须插入值 //可以为null的列可以插入null值,或者省略列名和值 //列数和值的个数必须一致 //可以省略列名,默认所有列,顺序与表中列名顺序一致 方式2: insert into 表名 set 列名=值,列名=值,... //支持子查询示例 insert into 表名 select 值 union select 值
-
修改语句
修改单表中的记录 update 表名 set 列=新值,列=新值,... where 筛选条件; 修改多表的记录 1.sql92标准 update 表1 别名,表2 别名... set 列=值,... where 连接条件 and 筛选条件 2.99标准 update 表1 别名 inner|left|right| join 表2 别名 on 连接条件 set 列=值,... where 筛选条件
-
删除语句
方式1:
单表删除 delete from 表 where 筛选条件 limit 索引,长度; 多表删除 92标准 delete 表1的别名,【表2的别名】 from 表1 别名,表2 别名 where 连接条件 and 筛选条件; 99表中 delete 表1的别名,【表2的别名】 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件;
方式2: truncate 清空数据
truncate table 表名;//删除整个表,不能加筛选条件
区别:
delete可以加where条件,truncate不可以加筛选条件
truncate删除整个表效率高
假如要删除的表中有自增长列,用delete 删除后再插入,自增长列从断点(删除的那行)开始,而truncate从1开始
truncate删除没有返回值,而delete删除有返回值
truncate 删除不能回滚,delete可以回滚
DDL(data define language)
1.库的管理
-
创建库
create database 【if not exists】库名;//容错创建数据库,默认字符集为utf8
-
修改库
现在不能使用而以前可以进行修改库操作的语句,现在一般不对库修改,或者手动操作而不用语句修改 rename database 原始库名 to 新库名; 可以更改库使用的字符集 alter database 库名 character set 字符集类型;
-
删除库
drop database 【if exists】库名;
2.表的管理
-
创建表
create table 【if not exists】表名( 列名 列的类型【(长度) 列的约束】, 列名 列的类型【(长度) 列的约束】, ... 列名 列的类型【(长度) 列的约束】)
-
修改表
alter table 表名 add/drop/modify/change column 列名 列的类型【(长度) 列的约束】 【first|after 字段名】;//列里不能加 if exists
- 修改列名 alter table 表名 change 【column】 列名 新列名 列的类型【(长度) 列的约束】;
- 修改列的类型或约束 alter table 表名 modify column 列名 新类型;
- 添加列 alter table 表名 add column 新列名 列名类型;
- 删除列 alter table 表名 drop column 列名;
- 修改表名 alter table 表名 rename to 新表名;
-
删除表
drop table 表名;//查看表用show table;
-
复制表
- 仅仅复制表的结构 create table 表名 like 被复制的表;
- 复制表的结构和数据 create table 表名 select * from 被复制的表;
- 复制表的部分数据 create table 表名 select 复制部分 from 被复制的表 where 筛选条件;
- 仅仅复制表的某些字段 create table 表名 select 复制部分 from 被复制的表 where 无法满足的条件(如0);
3.通用的写法
drop database if exists 旧库名;
create database 新库名;
drop table if exists 旧表名;
create table 新表名(..);
数据类型
使用原则:所选择的数据类型越简单越好,能保存数值的类型越小越好
1.整型
整数类型 | 字节 |
tinyint | 1 |
smallint | 3 |
smallint | 3 |
int、interger | 4 |
Bigint | 8 |
特点:
- 如果不设置无符号还是有符号,默认为有符号,如果想设置无符号,需要添加关键字unsigned
- 如果插入的数值超出整型范围,out of range 并且自动插入临界值
- 如果不设置类型长度,会默认设置长度
- 长度代表了显示的最大宽度,如果不够会用0在左边填充,但必须搭配zerofill使用
2.小数
浮点型类型 | 字节 |
float | 4 |
double | 8 |
定点数类型 | 字节 |
DEC(M,D)/DECIMAL(M,D) | M+2 |
Bigint | 8 |
特点:
- M代表整数部位+小数部位的长度
- D代表小数部位长度
- M和D都可省略,如果是定点型的话,M默认为10,D默认为0;如果是float和double,则会根据插入的数值精度来决定
- 定点型精度较高,如果要求精度或进行货币运算优先使用decimal
3.字符型
较短的文本:char、varchar;binary和varbinary用于保存较短的二进制;enum用于保存枚举;set用于保存集合
写法 | M的意思 | 特点 | 耗费空间 | 效率 | |
char | char(M) | 最大字符数,可以省略,默认为1 | 固定长度的字符 | 比较耗费 | 高 |
varchar | varchar(M) | 最大字符数,不可以省略 | 可变长度的字符 | 比较节省 | 低 |
4.日期型
日期和时间类型 | 字节 |
date(只保存日期) | 4 |
datetime(只能反映出插入时的当地时区,保存日期+时间,范围1000~9999) | 8 |
timestamp(支持的时间范围较小,和实际时区有关,更能反映实际日期,受版本影响,保存日期+时间,范围1970-2038) | 4 |
time(只保存时间) | 3 |
year(只保存年) | 1 |
常见约束
含义:用于限制表中的数据,保证表中的数据准确可靠,可以对一个字段增加多个约束,用空格隔开
- 类型:NOT NULL,保证该字段值不能为空
DEFAULT 默认约束,保证该字段有默认值
PRIMARY KEY,主键,保证该字段值具有唯一性,并且为非空值
UNIQUE,唯一,保证该字段值具有唯一性,可以为空
CHECK, MySQL不支持,检查约束 check(a='1’or a=‘2’)
FOREIGN KEY,限制两个表的关系,保证该字段的值必须来自于主表的关联列的值,从表添加外键约束,用于引用主表的关联列值 语法:references 表(列) - 添加约束的时机:创建或修改表时
- 约束的添加分类:
列级约束:六大约束语法上都支持,但外键约束没有效果
表级约束:除了not null和default,其他都支持
show index from 表可以查看主键、外键、唯一等索引信息 - 添加列级约束
直接在字段名和类型后面追加约束类型,只支持默认,非空,主键,唯一,其他不生效 - 表级约束
直接在各个字段的最后加,【constraint 约束名】 约束类型(字段名)
例: constraint fk foreign key(majorID) reference major(ID)
1. 主键和唯一的对比
保证唯一性 | 是否允许为空 | 一个表中是否允许多个 | 是否允许组合,多个列组合成一个键 | |
主键 | 是 | 不允许 | 不允许 | 允许 |
唯一 | 是 | 允许 | 允许 | 允许 |
2. 外键
1)要求在从表设置外键关系
2)从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3)主表的关联列必须是一个key (一般是主键或唯一键)
4)插入数据是先插入主表,在插入从表
5)删除数据是先删除从表,再删除主表
级联删除:删除主表的内容对应的从表全部删除
ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (从表约束列) REFERENCES 主表(约束列) ON DELETE CASCADE;
级联置空:删除主表的内容对应的从表内容为空
ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (从表约束列) REFERENCES 主表(约束列) ON DELETE SET NULL;
通用:
-
创建表添加约束
create table 表( 字段名1 字段类型 列表约束, 字段名n 字段类型 列表约束, 表级约束 )
-
修改表添加约束
列级约束【位置:列的后面,支持的约束类型不包括外键,不能起约束名】 alter table 表名 modify column 字段名 字段类型 新约束; 表级约束【位置:所有列的下面,默认和非空不支持,其他支持,可以起约束名,但主键起了没有效果】 alter table 表名 add【constraint 约束名】 约束类型(字段名) 【外键的引用】;
-
修改表时删除约束
删除非空 alter table 表名 modify column 字段名 字段类型 null; 删除默认 alter table 表名 modify column 字段名 字段类型; 删除主键 alter table 表名 drop primary key; 删除唯一 alter table 表名 drop index 约束名; 删除外键 alter table 表名 drop foreign key 约束的外键名;
标识列
含义:自增长列,可以不用手动插入值,系统提供默认的序列值
- 特点:
标识列必须和一个key搭配,但不一定是主键;
一个表只能有一个标识列;
标识列的类型只能是数值型;
标识列可以通过set auto_increment_increment=增长步
也可以通过手动插入值设置增长起始值;
TCL(transaction control language)
存储引擎:在MySQL中的数据用各种不同的技术存储在文件或内存中
通过show engines来查看MySQL中支持的存储引擎
在MySQL中用的最多的存储引擎有innodb,myisam,memory等,其中innodb支持事务,而myiasm/memory等不支持事务。
事务:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全不执行
-
事务的ACID属性(特性)
- 原子性(automicity):事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
- 一致性(consistency):事务必须使数据库从一个一致性状态到另一个一致性状态
- 隔离性(isolation):一个事务的执行不能被其他事务干扰,一个事务的内部操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能相互干扰
- 持久性(durability):一个事务一旦被提交,他对数据库中数据的改变是永久性的,接下里的其他操作和数据库故障不应对其有任何影响
-
事务的创建
隐式(自动)事务:事务没有明显的开启和结束的标记
显示事务:具有明显的开启和结束标记,必须先设置自动提交autocommit功能为禁用
设置自动提交:set autocommit=0;禁用//开启事务 set autocommit=0; start transaction;//可选的 //编写事务中的SQL语句支持的是DML语句(select/delete/insert) 语句1; 语句n; //结束事务 commit;//提交事务 rollback;//回滚该事务,就是相当于如果没提交该事务,他就保存的是开始该事物的原始状态, //常常与savepoint搭配,savepoint是设置节点或回滚点,该节点后的事务与rollback搭配相当于未提交的事务,rollback to 回滚点名;回到回滚点
delete语句支持回滚,而truncate语句不支持,会直接清空
对于同时运行的多个事务,当这些事务访问数据库中相同的数据时,如果没有采取必要的隔离机制,就会导致各种问题:
- 脏读:对于两个事务t1,t2,t2更新了数据但没有提交,t1读取了这个数据,若t2回滚,t1读取的内容就是临时且无效的
- 不可重复读:对于两个事务t1,t2,t1读取了一个字段,然后t2更新了该字段,t1再次读取同一个字段值就不同了
- 幻读:对于两个事务t1,t2,t1从一个表中读取了一个字段,然后t2在该表中插入了一些新的行,之后如果t1再次读取同一个字段就会多几行
数据库事务的隔离性:数据库系统必须具有隔离并发运行各个事务的能力,使他们不会互相影响,避免各种并发问题。
一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性就越弱。
数据库提供的4种事务隔离级别
- read uncommitted:允许事务读取违背其他事务提交的变更。脏读、不可重复读和幻读的问题都会出现
- read committed:只允许事务读取已经被其他事务提交的变更。可以避免脏读,但不可重复读和幻读问题仍然会出现
- repeatable read:允许读取已经被其他事务提交的变更,可以避免脏读,不可重复读,但幻读问题依然会出现
- serializable(串行化):确保事务可以从一个表中读取相同的行,在这个事务持续期间禁止其他事务对该表执行插入、更新和删除操作,所有并发问题都可以避免,但性能十分低下
1.Oracle支持2种事务隔离级别:read committed和serializable,默认事务隔离级别:read committed
2.MySQL支持4种事务隔离级别,默认事务隔离级别为repeatable read
3.每启动一个MySQL程序,就会获得一个单独的数据库连接,每个数据库连接都有一个全局变量@@tx_isolation 表示当前的事务隔离级别
- 查看当前数据库事务隔离级别:select @@tx_isolation;
- 设置当前MySQL连接的隔离级别:set transaction isolation level 级别;
- 设置数据库系统的全局隔离级别:set global transaction isolation level 级别;
视图
含义:虚拟表,和普通表一样使用
-
MySQL5.1 版本出现的新特性,通过表动态生成的数据
-
使用视图时动态生成,只保存SQL逻辑,不保存查询结果
-
应用场景:多个地方用到同样的查询结果;该查询结果使用的sql语句较复杂
-
优点:重用SQL语句;简化复杂的SQL操作,不必知道它的查询细节;保护数据,提高安全性
#创建视图 create view 视图名 as 查询语句; #修改视图 #方式一 create or replace view 视图名 as 查询语句; #方式二 alter view 视图名 as 查询语句; #删除视图 drop view 视图名,视图名...; #查看视图 DESC 视图名; show create view 视图名;
具备以下特点的视图不允许更新
- 包含以下关键字的SQL语句: 分组函数、distinct、group by、having、union或者union all
- 常量视图
- select 中包含子查询
- join语句,能修改但不能插入
- from 一个不能更新的视图,即select查询的是一个不能更新的视图
- where子句的子查询引用了from子句中的表
变量
- 系统变量
-
含义:变量由系统提供,不是用户自定义,属于服务器层面
-
分类: 全局变量;会话变量
-
作用域:
- 全局变量:服务器每次启动时姜维所有为所有的全局变量赋初始值,针对于所有的会话(连接)有效,但不能跨重启
- 会话变量:仅仅针对于当前会话(连接)有效
-
使用语法:如果是全局级别需要加global,会话级别可加可不加session
1.查看所有的系统变量 show global/【session】 variables; 2.查看满足条件的部分系统变量 show global/【session】 variables like "%char%"; 3.查看指定的某个系统变量的值 select @@global.系统变量名; 4.给某个系统变量赋值 set global/【session】系统变量名= 值; set global/【session】.系统变量名= 值;
- 自定义变量:
- 用户变量:针对于当前会话(连接)有效,等同于会话变量的作用域。可以放在begin end中也可外放,不用限定类型
-
使用语法
1.声明并初始化 set @用户变量名=值; set @用户变量名:=值; select @用户变量名:=值 2.赋值(更新用户变量的值) 通过select或set set @用户变量名=值; set @用户变量名:=值; select @用户变量名:=值 通过select into select 字段 into 变量名 from 表; 3.查看 select @用户变量名;
-
- 局部变量:仅仅在定义他的begin end中有效,应用在begin end中的第一句
-
使用语法
1. 声明 declare 变量名 类型; declare 变量名 类型 default 值; 2. 赋值 通过select或set set 局部变量名=值; set 局部变量名:=值; select @局部变量名:=值 通过select into select 字段 into 局部变量名 from 表; 3. 使用 select 局部变量名;
-
存储过程和函数
类似于Java中的方法,提高代码的重用性,简化操作
- 存储过程:一组预先编译好的SQL语句的集合,批处理语句
-
好处:提高代码的重用性,简化操作,减少了编译次数并且减少了与数据库服务器的连接次数,提高效率
-
使用语法
1. 创建 create procedure 存储过程名(参数列表) begin 存储过程体(一组合法的SQL语句) end 结束标记 或 delimiter 结束标记 create procedure 存储过程名(参数列表) begin 存储过程体(一组合法的SQL语句) end 结束标记 2.调用 call 存储过程名(实参列表)结束标记 3. 删除存储过程 drop procedure 存储过程名 //一次只能删除一个,不能批量删除 4. 查看存储过程信息 show create procedure 存储过程名 5. 不能修改存储过程中的SQL语句
-
Attention
-
参数列表包含三部分
参数模式 参数名 参数类型
参数模式:默认参数模式为in- IN:该参数可以作为输入,该参数需要调用方传入值
- OUT:该参数可以作为输入,该参数可以作为返回值
- INOUT:既可作输入,也可作输出,该参数既需要调用方传入值,也可以作为返回,先要定义输入的用户变量值
-
如果存储过程中仅仅只有一句话,begin end 可以省略
-
存储过程体重的每天SQL语句结尾必须加分号,存储过程体的结尾可以使用delimiter重新设置,使用时加上结束标记即可结束使用
delimiter 结束标记 //在客户命令端使用有效 -
函数
一组预先编译好的SQL语句集合,理解成批处理语句,好处与存储过程一样
-
区别:
函数:有且仅有一个返回,适合做处理数据后返回一个结果
存储过程:可以有0个返回,也可有多个返回,适合做批量插入,批量更新
语法 -
使用语法
1. 创建 create function 函数名(参数列表)returns 返回类型 begin 函数体 return 值; end 2. 调用 select 函数名(参数列表) 3. 查看 show create function 函数名; 4. 删除 drop function 函数名;
Attention
- 参数列表包含两部分:参数名 参数类型
- 函数体:肯定会有return语句,如果没有会报错
- 如果return语句没有放在函数的最后也不会报错,但不建议
- 当函数体只有一句话,可以省略begin end
- 使用delimiter 语句设置结束标记
流程控制
- 顺序结构:程序从上往下依次执行
- 分支结构:程序从两条或多条路径中选择一条执行
-
语法:
//if 函数实现简单的双分支 if (表达式1,表达式2,表达式3) 执行顺序: 如果表达式1成立,则if 返回表达式2的值,否则返回表达式3的值 //case结构实现等值判断或区间判断 case 变量、表达式、字段 //实现等值判断 when 要判断的值1 then 返回值1 when 要判断的值2 then 返回值2 ... else 返回值n; end 或者============================ case //实现区间判断 when 条件1 then 要显示的值1或语句1; when 条件2 then 要显示的值2或语句2; ... else 要显示的值n或语句n; end //if结构,只能应用在begin end中 if 条件1 then 语句1; elseif 条件2 then 语句2; ... 【else 语句n】 end if;
-
case特点
- 可以作为表达式嵌套在其他语句中使用,可以放在任何地方
- 可以作为独立的语句使用,只能放在begin end 中
- 如果when 中的值满足或条件成立,则执行对应then 后面的语句,并结束case
- 如果都不满足,则执行else语句
- else可省略,如果else省略并且所有when条件不满足,则返回null
- 循环结构:程序在满足一定条件的基础上,重复执行一段代码,只能放在begin end中(存储过程)
循环控制: iterate类似于continue,继续,结束本次循环,继续下一次;leave 类似于break,跳出,结束当前所在的循环,二者使用后面必须要加标签-
while:先判断后执行
【标签:】while 循环条件 do 循环体; end while 【标签】;
-
loop:模拟死循环
【标签:】loop 循环体; end loop 【标签】; //可以模拟简单的死循环
-
repeat:先执行后判断
【标签:】repeat 循环体; until 结束循环的条件 end repeat 【标签】;
-
现等值判断或区间判断
case 变量、表达式、字段 //实现等值判断
when 要判断的值1 then 返回值1
when 要判断的值2 then 返回值2
…
else 返回值n;
end
或者============================
case //实现区间判断
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
//if结构,只能应用在begin end中
if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
【else 语句n】
end if;
case特点
- 可以作为表达式嵌套在其他语句中使用,可以放在任何地方
- 可以作为独立的语句使用,只能放在begin end 中
- 如果when 中的值满足或条件成立,则执行对应then 后面的语句,并结束case
- 如果都不满足,则执行else语句
- else可省略,如果else省略并且所有when条件不满足,则返回null
- 循环结构:程序在满足一定条件的基础上,重复执行一段代码,只能放在begin end中(存储过程)
循环控制: iterate类似于continue,继续,结束本次循环,继续下一次;leave 类似于break,跳出,结束当前所在的循环,二者使用后面必须要加标签-
while:先判断后执行
【标签:】while 循环条件 do 循环体; end while 【标签】;
-
loop:模拟死循环
【标签:】loop 循环体; end loop 【标签】; //可以模拟简单的死循环
-
repeat:先执行后判断
【标签:】repeat 循环体; until 结束循环的条件 end repeat 【标签】;
-