测试开发笔记-数据库

MYSQL前端笔记（三）
七、DQL数据查询语言
查询会产生一张虚拟表
语法结构：
注意：*号代表表中所有字段，这个不建议使用，会影响查询效率；
SELECT 字段名,字段名 FROM 表名 WHERE 筛选过滤条件

查询表中所有数据库

 SELECT * FROM student;

指定字段查询

SELECT 'name',address FROM student;

AS 起别名

SELECT name AS 姓名,address AS 地址 FROM student;

不用as 起别名

 SELECT name 姓名,address 地址 FROM student;

LIMIT使用
注意：limit 0,2 逗号前面表示从哪一行开始取值，第一行是0，逗号右边值取多少行

SELECT * FROM student WHERE id=901 LIMIT 0,2

DISTINCT排除重复

SELECT DISTINCT department AS 院系,address FROM student;

单条件查询：

					= != < > >=  <=
SELECT * FROM student WHERE id>=905

多条件查询：
AND OR NOT

SELECT * FROM student WHERE id>=905 AND sex='女';
SELECT * FROM student WHERE id>=905 OR sex='男'

范围选择：

方式一：
SELECT * FROM student WHERE (2010-birth)>20 AND (2010-birth)<28;
方式二：BETWEEN AND
SELECT * FROM student WHERE 2010-birth BETWEEN 20 AND 28;
方式三：NOT BETWEEN AND
SELECT * FROM student WHERE 2010-birth NOT BETWEEN 20 AND 28;

集合操作：

1、or
SELECT * FROM student WHERE department='计算机系' OR department='英语系' OR sex='女'
2、in
SELECT * FROM student WHERE department IN ('计算机系','中文系')
3、not in
SELECT * FROM student WHERE department NOT IN ('计算机系','中文系')

模糊匹配

LIKE
  _ 一个字符
	SELECT * FROM student WHERE name LIKE '_三'

%任意字符 可以是0个
SELECT * FROM student WHERE address LIKE '辽宁%'

查询空号 is NULL

SELECT * FROM student WHERE name is NULL

常量列：

SELECT name as 姓名,address AS 地址,'希望小学' AS 学校 FROM student

排序
升序 ASC

SELECT * FROM score WHERE c_name='计算机' ORDER BY grade ASC 

降序 DESC

SELECT * FROM score WHERE c_name='计算机' ORDER BY grade DESC

聚合函数：

count 计数：
SELECT COUNT(grade) FROM score
max 
	求计算机系学生的最高分
 SELECT stu_id AS 学号, MAX(grade) AS 成绩 FROM score WHERE c_name='计算机'
min 求计算机系学生的最低分
	SELECT stu_id AS 学号, min(grade) AS 成绩 FROM score WHERE c_name='计算机'
sum 求和
SELECT stu_id AS 学号, SUM(grade) AS 成绩 FROM score WHERE c_name='计算机'
avg 平均值
SELECT stu_id AS 学号, AVG(grade) AS 成绩 FROM score WHERE c_name='计算机'

分组
WHERE子句：
从数据源中去掉不符合其搜索条件的数据
GROUP BY子句：
搜集数据行到各个组中，统计函数为各个组计算统计值，每个组输出一行结果
HAVING子句：
从分组计算结果中进行过滤，去掉不符合其组搜索条件的各组数据行

求每个系的最高分
SELECT c_name,AVG(grade) FROM score GROUP BY c_name
多字段分组
SELECT stu_id,c_name,AVG(grade) FROM score GROUP BY c_name,stu_id
HAVING 对之前分组的结构进行过滤
SELECT c_name,AVG(grade) FROM score GROUP BY c_name HAVING AVG(grade)<=90;

子查询：sql里面嵌套sql

SELECT id,name FROM student 
WHERE id=(
SELECT stu_id FROM score WHERE c_name='计算机' AND grade=70);

IN 后面可以赋多个值，用括号

中文系的学生信息
SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c_name='中文') 

NOT IN

SELECT * FROM student WHERE id NOT IN (SELECT stu_id FROM score WHERE c_name='中文') 

EXISTS
如果子查询返回结果为真 则执行前面sql ，如果为假 则前半句sql执行结果为空

SELECT * FROM score WHERE EXISTS (SELECT stu_id FROM score WHERE c_name='中文111') 

ALL 本质用and 取最大值
ANY OR 取最小值

1.ANY子句表示父查询只需要任意匹配一条子查询结果就可执行
2.ANY子句中 = ANY(子查询) 与IN同意
3.ANY子句中 > ANY代表大于最小值
4.ANY子句中 < ANY代表小于最大值

1.ALL子句中 > ALL代表大于最大值
2.ALL子句中 < ALL代表小于最小值

UNION 合并表

all 数据会重复
SELECT * FROM student_copy UNION all SELECT * FROM student;
不加all会去重
SELECT * FROM student_copy UNION SELECT * FROM student;

内连接：求两张表交集的部分

SELECT * FROM a_table INNER JOIN b_table ON a_table.a_id = b_table.b_id

左连接： 以左表为基础，展示出左表全部数据和右表交集部分数据

SELECT * FROM a_table LEFT JOIN b_table ON a_table.a_id = b_table.b_id

右连接：以右表为基础，展示出右全部数据和左表交集部分数据

SELECT * FROM a_table RIGHT JOIN b_table ON a_table.a_id = b_table.b_id

八、事务
事务具有 4 个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这 4 个特性通常简称为 ACID。
1、 原子性
事务是一个完整的操作。事务的各元素是不可分的（原子的）。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。

以银行转账事务为例，如果该事务提交了，则这两个账户的数据将会更新。如果由于某种原因，事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改，事务不能部分提交。
2、一致性
当事务完成时，数据必须处于一致状态。也就是说，在事务开始之前，数据库中存储的数据处于一致状态。在正在进行的事务中. 数据可能处于不一致的状态，如数据可能有部分被修改。然而，当事务成功完成时，数据必须再次回到已知的一致状态。通过事务对数据所做的修改不能损坏数据，或者说事务不能使数据存储处于不稳定的状态。

以银行转账事务事务为例。在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，一个账户余额减少了，而另一个账户余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。
3、隔离性
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。

另外，当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后，对数据的修改才能生效。张三和李四之间的转账与王五和赵二之间的转账，永远是相互独立的。
4、 持久性
事务的持久性指不管系统是否发生了故障，事务处理的结果都是永久的。

一个事务成功完成之后，它对数据库所作的改变是永久性的，即使系统出现故障也是如此。也就是说，一旦事务被提交，事务对数据所做的任何变动都会被永久地保留在数据库中。