0%

数据库

事务

事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行

特点(ACID)

  1. (Atomicity) 原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
  2. (Consistency) 一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态
  3. (Isolation) 隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的
  4. (Durability) 持久性:一个事务一旦提交了,则永久的持久化到本地

并发事务(重点)

1、事务的并发问题是如何发生的?

多个事务 同时 操作 同一个数据库的相同数据时

2、并发问题都有哪些?

1
2
3
脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据
不可重复读:一个事务多次读取,结果不一样
幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是 其他事务“插入”的数据

3、如何解决并发问题

通过设置隔离级别来解决并发问题

4、隔离级别

脏读 不可重复读 幻读 数据库默认隔离级别
read uncommitted:读未提交
read committed:读已提交 Oracle和SQL Server
repeatable read:可重复读 MySQL默认
serializable:串行化

事务的使用步骤

了解:
隐式(自动)事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete
显式事务:具有明显的开启和结束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
使用显式事务:
①开启事务
set autocommit=0;
start transaction;#可以省略

②编写一组逻辑sql语句
注意:sql语句支持的是insert、update、delete

设置回滚点:
savepoint 回滚点名;

③结束事务
提交:commit;
回滚:rollback;
回滚到指定的地方:rollback to 回滚点名;

隔离级别的演示

  • 事物的隔离级别(读未提交):出现脏读

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    #查看隔离级别
    select @@tx_isolation;
    #设置隔离级别
    set session|global transaction isolation level 隔离级别;
    eg:
    -- 设置隔离级别成为:读未提交。出现脏读(其他相对应改变)
     SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTE;
     
    #编写一组事务的语句
    UPDATE account SET balance = 1000 WHERE username='张无忌';
    UPDATE account SET balance = 1000 WHERE username='赵敏';

    #结束事务
    ROLLBACK;
    #commit;

    SELECT * FROM account;

    session|global 当前会话/全局 有效!建议使用session

    问题:出现脏读,一个事物读取到了其他事物未提交的数据。

  • 事物的隔离级别(读已提交):不会出现脏读,但是出现不可重复读

    1
    2
    -- 设置隔离级别成为:读可提交,不会出现脏读,但是会出现不可重复读。
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

    可以解决脏读问题:不会读取到其他事物未提交的数据。但是会出现不可重复读。
    问题:出现不可重复读,一次事物中,多次读取到的数据不一样,读取到了别人已提交的数据。

  • 事物的隔离级别(可重复读):可重复读演示:不会出现不可重复读,会出现幻读

    1
    SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    可以解决的问题:不会出现不可重复读,一次事物多次读取到数据是一样的。

    问题:出现了幻读

  • 事物的隔离级别(可串行化):serializable可串行化。所有问题都不会出现

    1
    SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SQL四种语言

  1. DDL(Data Definition Language)数据库定义语言

    DDL不需要commit

    1
    2
    3
    CREATE
    ALTER
    DROP
  2. DML(Data Manipulation Language 数据操纵语言

    需要commit

    1
    2
    3
    4
    SELECT
    INSERT
    UPDATE
    DELETE
  3. DCL(Data Control Language)数据库控制语言 授权,角色控制等

    1
    2
    GRANT 授权
    REVOKE 取消授权
  4. TCL(Transaction Control Language)事务控制语言

    1
    2
    3
    SAVEPOINT 设置保存点
    ROLLBACK 回滚
    SET TRANSACTION

内连接

内连接:内连接可整合多张表,获取相关关系或者公共部分的记录

内连接分为:隐式内连接,显式内连接

  • 隐式内连接

    1
    select 列名 from 左表,右表 where 主表.主键=从表.外键
  • 显式内连接

    1
    2
    3
    -使用INNER JOIN ... ON语句, 可以省略INNER
    select 列名 from 左表 inner join 右表 on 主表.主键=从表.外键 --显式内连接与隐式内连接查询结果是一样的**
    select * from dept d inner join emp e on d.id = e.dept_id;

外连接

左外连接

左外连接:在内连接的基础上,保证左表中所有的记录都出现。相应记录使用NULL和它匹配。

左外连接:使用LEFT OUTER JOIN … ON,OUTER可以省略

1
2
3
4
5
6
7
-- 需求:在部门表中增加一个销售部,需要查询所有的部门和员工,将部门表设置成左表,员工表设置成右表
select * from dept;
insert into dept values(null,'销售部');
-- 使用内连接查询
select * from employee e dept d inner join on d.id = e.dept_id;
-- 使用左外连接查询
select * from dept d left join employee e on d.id = e.dept_id;

右外连接

在内连接的基础上,保证右表中所有的数据都显示。左表中如果没有匹配的数据,使用NULL匹配。

1
2
3
4
5
6
7
-- 需求:在员工表中增加一个员工:'沙僧','男',6666,'2013-02-24',null
select * from emp;
insert into emp values(null,'沙僧','男',6666,'2013-02-24',null); -- 希望员工的信息全部显示出来
-- 使用内连接查询
select * from dept d inner join emp e on d.id = e.dept_id;
-- 使用右外连接查询
select * from dept d right join emp e on d.id = e.dept_id;

查询独有的数据

查询左边独有的数据

1
Select <select_list> from tableA A Left Join  tableB B  on A.Key = B.Key where B.key IS NULL

查询右表独有的数据

1
Select <select_list> from tableA A Right Join  tableB B  on A.Key = B.Key where A.key IS NULL

查询左右表各独有的数据

1
Select <select_list> from tableA A  Full Outter Join tableB B  on A.Key = B.Key where A.key = null or B.key=null

查询左右表全部的数据

1
2
 Select <select_list> from tableA A  Full Outter Join tableB B  on A.Key = B.Key
注:Mysql 默认不支持此种写法 Oracle支持

表的级联

在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作 ON UPDATE CASCADE

– 级联更新,主键发生更新时,外键也会更新 ON DELETE CASCADE – 级联删除,主键发生删除时,从表关联的全部数据都会被直接删除。

1
2
3
4
5
6
7
REATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30),
age INT,
dep_id INT,
-- 添加外键约束,并且添加级联更新和级联删除
CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);

存储引擎

1
2
查看存储引擎
show engines;
1
2
查看系统当前支持的存储引擎,需要使用如下命令:
show variables like '%storage_engine%';

创建新表时,如果不指定存储引擎,那么系统就会使用默认的存储引擎,MySQL 5.5之前的默认存储引擎是MyISAM , 5.5之后改为了InnoDB,如果需要修改存储引擎可以在核心配置文件中配置如下操作

default-storage-engine=INNODB

在创建表的时候,通过增加ENGINE关键字设置新表的存储引擎

1
2
3
4
5
REATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM default CHARSET=utf8;

也可以把一个已经存在的表的存储引擎,修改成其他表的存储引擎,操作如下

1
2
alter table 表名 ENGINE = innoDB; 
show create table 表名;

常见存储引擎的区别

存储过程,函数

MySQL从5.0版本开始支持存储过程和函数。

存储过程和函数是事先经过编译和存储在数据库中的一段SQL语句的集合,然后直接通知调用执行即可, 所以调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。

存储过程和函数的区别在于函数必须有返回值,而存储过程没有,存储过程的参数可以使用IN,OUT,INOUT类型,而函数的参数只能是IN类型的.

创建,删除,修改存储过程或者函数都需要权限,例如创建存储过程或者函数需要CREATE ROUNTINE权限,修改或者删除存储过程或者函数需要ALTER ROUTINE权限,执行存储过程或者函数需要EXECUTE权限。

一个简单的存储过程

创建存储过程/函数

1
2
3
4
5
6
7
8
-- 创建存储过程
DELIMITER $$(可选,命令行必须!图形化就不用!)
CREATE PROCEDURE testa()
BEGIN
 SELECT * FROM student WHERE id=2;
END;
-- 调用存储过程
call testa();
  1. 创建格式:create procedure 存储过程名
    
    1. 包含一个以上代码块,代码块使用begin和end 之间
      
      1. 在命令行中创建需要定义分隔符 delimiter $$
        
        1. 存储过程调用使用call命令
          

存储过程的特点:

  1. 能完成较复杂的判断和运算,而且处理逻辑都封装在数据库端,调用者不需要自己处理业务逻辑,一旦逻辑发生变化,只需要修改存储过程即可,而对调用者程序完全没有影响。
    
    1. 可编程性强,灵活
      
      1. SQL编程的代码可重复使用
        
        1. 执行速度相对快一些
          
          1. 减少网络之间数据传输,节省开销
            

删除存储过程/函数

1
2
3
4
-- 删除存储过程
DROP PROCEDURE testa1;
-- 删除函数
DROP FUNCTION testa1;
1
2
3
4
-- 查看存储过程或者函数的状态
SHOW PROCEDURE STATUS LIKE 'testa';
-- 查看存储过程或者函数的定义
SHOW CREATE PROCEDURE testa;

存储过程的变量

语法

  • SET

    直接赋值使用 SET,可以赋常量或者赋表达式

    1
    SET var_name = expr [, var_name = expr] ...

    也可以通过select … into 方式进行赋值操作 :

1
2
3
4
5
6
CREATE  PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;
select countnum;
END
  • DECLARE

    通过 DECLARE 可以定义一个局部变量,该变量的作用范围只能在 BEGIN…END 块中。

    1
    DECLARE var_name[,...] type [DEFAULT value]

存储过程的变量

需求1: 编写存储过程,使用变量取id=2的用户名.

1
2
3
4
5
6
7
8
9
10
DELIMITER $$ 
CREATE PROCEDURE testa3()
BEGIN
DECLARE my_uname VARCHAR(32) DEFAULT '';
SET my_uname='itheima';
SELECT NAME INTO my_uname FROM student WHERE id=2;
SELECT my_uname;
END;

CALL testa3()
  1. 变量的声明使用declare,一句declare只声明一个变量,变量必须先声明后使用
    
    1. 变量具有数据类型和长度,与mysql的SQL数据类型保持一致,因此甚至还能指定默认值、字符集和排序规则等
      
      1. 变量可以通过set来赋值,也可以通过select into的方式赋值
        
        1. 变量需要返回,可以使用select语句,如:select 变量名
          

需求2:统计表users,student的行数量和student表中英语最高分,数学最高分的注册时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DELIMITER $$ 
CREATE PROCEDURE stats_users_students5()
BEGIN
EGIN
DECLARE users_sum INT DEFAULT 0;
DECLARE students_sum INT DEFAULT 0;
SELECT COUNT(*) INTO users_sum FROM users; SELECT COUNT(*) INTO students_sum FROM student; SELECT users_sum,students_sum;
END;

BEGIN
DECLARE max_math INT;
DECLARE max_english INT;
SELECT MAX(math),MAX(english) INTO max_math,max_english FROM student ;
SELECT users_sum,students_sum,max_math,max_english;
END;
END;

CALL stats_users_students5();
  1. 变量是有作用域的,作用范围在begin与end块之间,end结束变量的作用范围即结束。
    
    1. 需要多个块之间传递值,可以使用全局变量,即放在所有代码块之前。
      
      1. 传参变量是全局的,可以在多个块之间起作用
        

存储过程的传入参数IN

需求:编写存储过程,传入id,返回该用户的name

1
2
3
4
5
6
7
8
CREATE PROCEDURE getName(my_uid INT) 
BEGIN
DECLARE my_uname VARCHAR(32) DEFAULT '';
SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
SELECT my_uname;
END;

CALL getName(2);
  1. 传入参数:类型为IN,表示该参数的值必须在调用存储过程时指定,如果不显式指定为IN,那么默认就是IN类型。
    
    1. IN类型参数一般只用于传入,在调用存储过程中一般不作修改和返回
      
      1. 如果调用存储过程中需要修改和返回值,可以使用OUT类型参数
        

存储过程的传出参数OUT

需求:调用存储过程时,传入uid返回该用户的uname

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE getName22(IN my_uid INT,OUT my_uname VARCHAR(32)) 
BEGIN
SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
SELECT my_uname;
END;

SET @uname:='';
CALL getName22(2,@uname);
SELECT @uname AS myName;

1.传出参数:在调用存储过程中,可以改变其值,并可返回

2.OUT是传出参数,不能用于传入参数值

  1. 调用存储过程时,OUT参数也需要指定,但必须是变量,不能是常量
    
    1. 如果既需要传入,同时又需要传出,则可以使用INOUT类型参数
      

存储过程的可变参数INOUT

需求:调用存储过程时,参数my_uid和my_uname,既是传入,也是传出参数

1
2
3
4
5
6
7
8
9
10
11
12
CREATE PROCEDURE getName33(INOUT my_uid INT,INOUT my_uname VARCHAR(32))
BEGIN
SET my_uid=2;
SET my_uname='hxf3';
SELECT id,NAME INTO my_uid,my_uname FROM student WHERE id=my_uid;
SELECT my_uid,my_uname;
END;

SET @uname:='';
SET @uid:=0;
CALL getName33(@uid,@uname);
SELECT @uname AS myName;
  1. 可变变量INOUT:调用时可传入值,在调用过程中,可修改其值,同时也可返回值。
    
    1. INOUT参数集合了IN和OUT类型的参数功能
      
      1. INOUT调用时传入的是变量,而不是常量
        

存储过程条件语句

需求:编写存储过程,如果用户uid是偶数则就给出uname,其它情况只返回uid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE PROCEDURE getName44(IN my_uid INT ) 
BEGIN
DECLARE my_uname VARCHAR(32) DEFAULT '';
IF(my_uid%2=0)
THEN
SELECT NAME INTO my_uname FROM student WHERE id=my_uid;
SELECT my_uname;
ELSE
SELECT my_uid;
END IF;
END;

CALL getName44(1);
CALL getName44(2);
  1. 条件语句最基本的结构:if() then …else …end if;
    
    1. If判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式
      

需求:根据用户传入的uid参数判断: (1)如果状态status为1,则给用户score加10分 (2)如果状态status为2,则给用户score加20分 (3)其它情况加30分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE PROCEDURE addscore1(IN my_uid INT )
BEGIN
DECLARE my_status INT DEFAULT 0;
SELECT STATUS INTO my_status FROM student WHERE id=my_uid;
IF(my_status =1)
THEN
UPDATE student SET math=math+10 , english=english+10  WHERE id=my_uid;
ELSEIF(my_status =2)
THEN
UPDATE student SET math=math+20 , english=english+20 WHERE id=my_uid;
ELSE
UPDATE student SET math=math+30 , english=english+30 WHERE id=my_uid;
END IF;
END;


CALL addscore1(1);

存储过程循环语句

while循环

需求:使用循环语句,向表uesrs中插入10条uid连续的记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE insertdata()
BEGIN
DECLARE i INT DEFAULT  0;
WHILE(i< 10) DO
BEGIN
SELECT i;
SET i=i+1;
INSERT INTO users(NAME , address) VALUES("孙悟空" , "广州");
END ;
END WHILE;
END;

CALL insertdata();
  1. while语句最基本的结构:while() do…end while;
    
    1. while判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式
      

repeat循环语句

需求:使用repeat循环向表users插入10条uid连续的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE insertdata2() BEGIN
DECLARE i INT DEFAULT  100;
REPEAT
BEGIN
SELECT i;
SET i=i+1;
INSERT INTO users(NAME) VALUES('黑马');
END ;
UNTIL i >= 110
END REPEAT;sql
END;

CALL insertdata3();
  1. repeat语句最基本的结构:repeat…until …end REPEAT;
    
    1. until判断返回逻辑真或者假,表达式可以是任意返回真或者假的表达式,只有当until语句为真时,循环结束
      

光标的使用

在存储过程和函数中,可以使用光标(有时也称为游标)对结果集进行循环的处理,光标的使用包括了:

需求:编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 编写存储过程,使用光标,把id为偶数的记录逐一更新用户名。
DELIMITER $$
CREATE PROCEDURE testcursor()
BEGIN
DECLARE stopflag INT DEFAULT  0;
DECLARE my_uname VARCHAR(20);
DECLARE uname_cur CURSOR FOR SELECT NAME FROM student WHERE id%2=0 ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1;
OPEN uname_cur; -- 打开游标
FETCH uname_cur INTO my_uname; -- 游标向前走一步,取出一条记录放到变量my_uname中。
WHILE( stopflag=0 ) DO  -- 如果游标还没有到结尾,就继续
BEGIN
UPDATE student SET NAME=CONCAT(my_uname,'_cur') WHERE NAME=my_uname; FETCH uname_cur INTO my_uname;
END ;
END WHILE;
CLOSE uname_cur;
END;


注意:变量,条件,处理程序,光标,都是通过DECLARE定义的,它们之间是有先后顺序要求的,变量和条件必须在最前面声明,然后才能是光标的申明,最后才可以是处理程序的申明。