mysql 事务总结 存储过程 + 最近sql查询后的练习反思

首先

什么是事务：  一个事务包含多个操作，在mysql数据库中其实就是对数据库的一组操作，可以有一条或多条sql语句组成，同一个

事务具备同步的操作特点.   (也就是要么都执行，要么都不执行)

事物的目的：事务会把数据库从一种一致状态转换成另外一种状态。在数据库提交工作时，可以确保其要么所有修改都已经保存了，

要么所有修改都不保存。(其实说白了了，也就是同步的特点

事务的4个特性：ACID

mysql     lnnoDB 存储引擎中的事务完全符合ACID

原子性 (atomicity)：要么都执行成功，要么都不执行成功

一致性(consistency)：保证数据一致完整性的操作，误操作的事务会进行事务回滚，（违反约束的数据的操作）

隔离性 (isolation)：每个事务都应该是同时操作的，不管是否在并发状态（多用户同时访问数据库，每个用户开启的事务都不能被其他事务的操作影响）

持久性(durability) ：事务提交成功，修改的数据就会存储在数据库中，哪怕数据库出现问题

补充：关于事务，在面试中被问到的概率是很高的，可以问的问题也是很多的。首先需要知道的是，只有存在并发数据访问时才需要事务。

当多个事务访问同一数据时可能会存在5类问题，包括3类数据读取问题（脏读、不可重复读和幻读）和2类数据更新问题

（第1类丢失更新和第2类丢失更新）。

事务隔离的4种等级：

Read Uncommitted（读取未提交内容）：一般很少用于实际当中

Read Committed（读取提交内容）：大多数数据库的默认级别，满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）：是MySQL的默认事务隔离级别  ，会导致  幻读（Phantom Read）

Serializable（可串行化）：最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

脏读（Dirty Read）：事务A读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏读。

不可重复读（Unrepeatable Read）：事务A重新读取前面读取过的数据，发现该数据已经被另一个已提交的事务B修改过了。

幻读（Phantom Read）：事务A重新执行一个查询，返回一系列符合查询条件的行，发现其中插入了被事务B提交的行。

mysql中设置事务的隔离级别：

语法格式：  set session   TRANSACTION     ISOLATION     level    事务等级

eg:    set sessionTRANSACTION ISOLATION level read UNCOMMITTED;   (设置的是最低隔离等级 读取未提交内容)

存储过程：提高sql语句的重用，是一个sql语句的集合

说白了就是创建函数，提高sql语句的重用性

创建存储过程的语法：

create procedure 自定义名（[参数名]）     //   [中括号里面的东西，可以写，可以不写] 

begin

SELECT  *  from   users;

end

如何调用存储过程： 

call 函数名：表示调用sql函数

注意：

在 dos 里面写存储过程有所不同

delimiter // :表示将结束符号 “；“转换为普通字符，让//代替；作为结束符号

delimiter ; :恢复；（分号）的默认结束作用

存储过程的参数有三种类型：  in， out ，inout

 

in ：表示输入类型，从外部提供给存储过程使用的参数（最常用）

out：表示输出类型，存储过程内部使用的参数

inout：可以输入也可以输出类型

 

使用in参数定义语法：

参数类型    自定义参数名 数据类型

create procedure  three （in num int）

begin

select * from  Users where age >num;

end;

使用out 作为参数：out类型的参数不接收传值，默认是null，只能在存储过程代码中设置其值，外部不能设置

create procedure  three (in num int, out num1 VARCHAR(20))

begin

SELECT uname into  num1 from users where  age = num ;

set num2 =30； ----设置值

end;

 

select 列名 into 变量名   ：    将查询结果赋值

 

call three (20,@a); ------ @a 表示用来接受输出结果的，一般以@开头

select @a；

 

call three (20,@a，@cc);

select @cc；

最近sql查询后的练习反思

最近两天将网上的那个经典sql 50到题（这个比较难（我记得有一个数据叫做 ‘赵雷’），还有一个比较简单的sql查询50道题），码完了，深有感触，sql语句中的难点就是在 select 当中。 其中涉及的知识点也是非常的多，最常用到的也是

连接查询（内连接，外连接（左连接，右连接））

下面简单总结一下遇到的知识点和问题：

1.查询统计多少列的时候需要用到 count（）这个函数，网上有count(1)这总写法，平时我们都是count(*）这样写的。 遇见多了之后就查了一下他们的区别。

对于以后方向不是DBA 的人们只需要知道，就是查询速度的问题

count(1)与count(*)比较：

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

2.MySQL的左连接、右连接、等值连接    在sql查询   会遇到非常多的 这种连接，但是 我基本上都是用等值连接去做的，基本没有用到左右连接

后面和朋友讨论后发现，他也是（可能是我们的资历尚浅，还不能升入理解应用    left join right join ）

MySQL的左连接、右连接、等值连接 的区别可以参见 大牛写的       http://blog.csdn.net/wyzxg/article/details/7276979              

3. case  when 的用法

case when ：用于计算条件列表并返回多个可能结果表达式之一

po一段用到case when的代码仅供参考：

按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
-- select a.S 学生编号 , a.Sname 学生姓名 ,
-- max(case c.Cname when '语文' then b.score else null end) 语文,
-- max(case c.Cname when '数学' then b.score else null end) 数学,
-- max(case c.Cname when '英语' then b.score else null end) 英语,
-- cast(avg(b.score) as decimal(18,2)) 平均分   from Student a  
-- left join SC b on a.S = b.S
-- left join Course c on b.C = c.C
-- group by a.S
-- order by 平均分 desc;

4.mysql 给select 的结果添加一列序号。 有的时候我们查询总成绩然后进行排名，就需要在后面加上一列排名，

用法：

SET@newrow=0;

SELECT @newrow := @newrow +1 as newrow, a.*, b.* 

FROM a,b;

表中第一列即为newrow，从1开始计数。

po一段用到添加一列序号代码仅供参考：

查询学生的总成绩并进行排名
-- set @mycnt = 0;
-- select a.totalscore 总成绩, @mycnt := @mycnt +1 as 总成绩排名 from
-- (
-- SELECT sum(score) totalscore from sc
-- GROUP BY sc.s
-- ORDER BY sum(score) desc
-- ) a

5.    查询 第一二名  需要用到的  top（）  函数  在   mysql  里面不能用，  因为    mysql   里面   没有top（）  这个和函数  。  取而代之的是  可以用  limit（）。

但是有点不愉快的是    limit   好像不能有   in   等子查询一起连用。所有有点尴尬。    关于  limit 的用法    ，大家可以 百度一下， 上面一大把，我这里就不在重复了。

   补充：设计表，给表取表名，字段名的时候不能和关键字重复，否则后面的操作会出错

主要问题就是这些，有的问题暂时没有想起来，就先写到这里了。以后发现了在补充吧   ，谢谢看官，能看到这里，让您受惊了 嘿嘿、。