数据库杂记

最新推荐文章于 2020-03-23 23:58:58 发布

q1015189243

最新推荐文章于 2020-03-23 23:58:58 发布

阅读量169

点赞数

本文链接：https://blog.csdn.net/q1015189243/article/details/86481081

版权

一、事务的基本要素（ACID）
　　1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
　　 2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收
　　 3、隔离性（Isolation）：可能多个应用程序线程同时访问同一数据，这样数据库同样的数据就会在各个不同的事务中被访问，这样会产生丢失更新。为了压制丢失更新的产生，数据库定义了隔离级别的概念，通过它的选择，可以在不同程度上压制丢失更新的发生。
　　 4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
二、事务的并发问题
　　1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
　　2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
　　3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。当前事务读第一次取到的数据比后来读取到数据条目少。
　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
三、MySQL事务隔离级别
mysql默认的事务隔离级别为repeatable-read；oracle只支持read commited和serializable，默认为read commited
在这里插入图片描述

Mysql

单行注释：#注释文字 – 注释文字
多行注释：/* 注释文字 */
SQL
1、数据查询语言（DQL:Data Query Language）：其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其它类型的SQL语句一起使用。
2、数据操作语言（DML：Data Manipulation Language）：其语句包括动词INSERT、UPDATE和DELETE。它们分别用于添加、修改和删除。
3、事务控制语言（TCL）：它的语句能确保被DML语句影响的表的所有行及时得以更新。包括COMMIT（提交）命令、SAVEPOINT（保存点）命令、ROLLBACK（回滚）命令。
4、数据控制语言（DCL）：它的语句通过GRANT或REVOKE实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
5、数据定义语言（DDL）：其语句包括动词CREATE,ALTER和DROP。在数据库中创建新表或修改、删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。
6、指针控制语言（CCL）：它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。
列的 SQL 别名语法
1.SELECT column_name AS alias_name FROM table_name;
2.SELECT column_name alias_name FROM table_name;
表的 SQL 别名语法
SELECT column_name(s) FROM table_name AS alias_name;
mysql中的加号
只有一个功能，运算符
select 1+2 两个都是数值型，则做加法运算
select ‘123’ + 3只要有一方是字符型，则试图将字符型数值转为数值型，如果转换成功，则做加法运算，如果转换失败（select ‘sfdsfd’ + 3），则将字符型数值转换成0
select null + 10 只要其中一个是null,结果就是null
concat实现连接
select concat(‘a’,‘b’,‘c’) 结果abc
select concat(last_name, ‘-’ ,first_ name) AS 姓名 from …
IFNULL
IFNULL(expression, alt_value)如果第一个参数的表达式 expression 为 NULL，则返回第二个参数的备用值。
SELECT IFNULL(NULL, “RUNOOB”);
条件查询
1.条件表达式> < = != <> >= <=
2.逻辑表达式 && || ！ and or not
3.模糊查询 like[%,_] between…and in is (not) null
ESCAPE转义
如果要查找products表中productCode字段包含字符串_20的产品，则可以使用模式%_20%查询，或者也可以指定一个不同的转义字符。例如，通过ESCAPE子句使用 $/￥作为转义符：
SELECT productCode, productName FROM products WHERE productCode LIKE ‘% ￥_20%’ ESCAPE ‘￥’;
between…and注意事项：
1.包含临界值
2.select * from where id between 100 and 120
两数不可颠倒
安全等于<=>:既可以判断null，又可以判断普通的数值
order by子句中可以支持单个字段、多个字段、表达式、函数、别名，一般放在查询语句的最后面，但limit子句除外。
函数之单行函数
1.字符函数
length、concat、upper/lower、substr/substring、instr、trim、lpad/rpad(用指定字符实现左右填充至指定长度)、replace
2.数学函数
round（四舍五入）、ceil（向上取整）、floor(向下取整)、truncate(截断)、mod（取余)mod(a,b) a-a/b乘a
3.日期函数
now 返回当前系统日期加时间
curdate 返回日期，不包含日期
curtime 返回时间，不包含日期
year\month\monthname获取指定的部分，年月日小时分钟秒
year(now()):2019
str_to_date:将日期格式的字符转换成指定格式的日期
str_to_date(‘9-13-1999’,’%m-%d-%Y’)
date_format:将日期转换为字符
date_format(‘2018/9/9’,’%Y年%m月%d日’)
在这里插入图片描述
4.其他函数
version、database、user
5.流程控制函数
IF函数
SELECT IF(STRCMP(“hello”,“bye”) = 0, “YES”, “NO”);
case函数
使用一：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
.。。。
else 要显示的值n或语句n
end
在这里插入图片描述
使用二：类似多重if
case
when 条件1 then 要显示的值1或语句1；
when 条件2 then 要显示的值2或语句2；
.。。。
else 要显示的值n或语句n
end

在这里插入图片描述

函数之分组函数
sum、avg、 max 、min 、count
注意事项：
1.sum、avg一般只用于数值型
max、min、count、可以处理任何类型
2.以上函数都忽略null值
3.可以和distinct搭配使用
4.count函数
select count(字段) from xxx;
select count() from xxx;
select count(1) from xxx;
效率比较：
MYISAM存储引擎下，count()效率高
INNODB存储引擎下，count(*)和count(1)高，count(字段)低
5.和分组函数一同查询的字段有限制，一般要求是group by后的字段

分组查询
select 分组函数/列(要求出现在group by的后面) from xxx where xxx group by 分组的列表 order by xxx
执行顺序：
在这里插入图片描述
注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

添加分组后的筛选条件：查询哪个部门的员工个数>2

having和where:

group by还可以按表达式或函数分组，按姓名长度分组：

mysql中group by和having都支持别名：
在这里插入图片描述
按多个字段分组（多个字段用逗号隔开没有顺序要求)：

也可以添加排序，排序放在整个分组查询的最后

连接查询

1.内连接：
a.等值连接
n表连接，至少需要n-1个连接条件
在这里插入图片描述
如果为表起了别名，则查询的字段就不能使用原来的表名去限定
等值连接加分组：

b.非等值连接
在这里插入图片描述
c.自连接

2.外连接：左外连接、右外连接、全外连接
3.交叉连接

sql99中的内连接，inner可以省略

交叉连接就是笛卡儿积

子查询

含义：出现在其他语句中的select语句，成为子查询或内查询，外部的查询语句，成为主查询或外查询
分类：
在这里插入图片描述

放在where\having后面
在这里插入图片描述
标量子查询：
子查询的执行顺序优先于主查询
谁的工资比Abel高？

查询最低工资大于50号部门最低工资的部门id和其最低工资

列子查询(多行子查询)：
通常搭配多行比较操作符：

返回location_id是1400或1700的部门中的所有的员工姓名
在这里插入图片描述
行子查询(结果集一行多列或多行多列)：
查询员工编号最小并且工资最高的员工信息

放在select后面(只支持标量子查询)
查询每个部门员工个数

查询员工号=102的部门名

放在from后面
查询每个部门的平均工资的工资等级
在这里插入图片描述
放在exists后面(相关子查询)
exists(完整的查询语句)
结果：1或0
查询有员工的部门名

联合查询：
union联合合并，将多条查询语句合并成一个结果，自动去重
应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
在这里插入图片描述