数据库阶段技术总结

xcxdyyds

已于 2023-05-24 11:40:56 修改

阅读量57

点赞数

文章标签：数据库 sql mysql

于 2022-12-29 17:51:36 首次发布

本文链接：https://blog.csdn.net/xcxdyyds/article/details/128471794

版权

行列转换

如现有表score

转换为

select stu_id,stu_name,
sum(if(c_name='高等数学',cj,null)) as '高等数学',
sum(if(c_name='思修',cj,null)) as '思修',
sum(if(c_name='大学英语',cj,null)) as '大学英语',
sum(if(c_name='大学体育',cj,null)) as '大学体育'
from score
group by stu_id

视图View

视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果，

之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句。

视图同时可以隐藏一些查询细节，定制查询数据。

创建视图

create view 视图名 as
查询的sql语句;

使用视图

select * from 视图名

修改视图中的数据，会直接修改原始表中的数据

删除视图

drop view 视图名;

-- 查询平均分最高的课程名及其授课教师

-- 创建视图
create view myview as
select s.c_id,avg(cj)avg_cj from score s,course c where s.c_id = c.c_id
group by s.c_id

-- 视图可以当做表使用
select c_name,t_name from teach t2,teacher t1,course c,
(select c_id from myview where avg_cj=(select max(avg_cj) from myview))t
where t1.t_id=t2.t_id and t.c_id=t2.c_id and c.c_id=t2.c_id

-- 删除视图
drop view myview;

事务transaction

事务是由一组sql语句组成的执行单元，这些sql之间一般都相互依赖。

这个执行单元要么全部执行，要么全部不执行。

转账

1.select * from 表 where id =1 and money>=1000

2.update 表 set money=money-1000 where id=1

3.update 表 set money=money+1000 where id=2

以上的所有sql组成了一个转账的事务。

事务的特性ACID

原子性Atomicity

事务是最小的执行单元

一致性Consistency

事务执行前后，必须让所有数据保持一致状态。(总体数据守恒)

隔离性Isolation

事务并发时相互隔离，互不影响

持久性Durability

事务一旦提交，对数据的改变是永久的

事务的使用

提交：commit

回滚：rollback

mysql中事务是默认自动提交的。

查看事务自动提交开启状态：select @@autocommit 1表示开启自动提交 0表示关闭自动提交

设置事务不自动提交：set @@autocommit=0

如果关闭了事务自动提交，在执行某个事务途中，如果出错，可以使用rollback进行回滚，让数据回到事务执行之前的状态。

如果不出错，通过commit提交事务，一旦提交事务，无法进行回滚。

手动提交/回滚事务

1.关闭事务自动提交：set @@autocommit=0

2.开启事务：start transaction;

3.事务要执行的sql；

4.没有提交之前，如果要回滚，使用rollback;

5.如果要提交，使用commit；一旦提交成功，无法rollback。

事务并发可能出现的问题

在同一时刻同时执行多个事务时，称为事务并发。

事务并发会有可能出现以下问题

问题	描述
脏读	事务A读取到了事务B未提交的数据
不可重复读	事务A中如果要读取两次数据，在这期间，事务B对数据进行了修改并提交，导致事务A读取两次的情况不一致
幻读	事务A读取id为1~10之间的数据，假如只有id为2和5的数据，在读取期间，事务B添加了一条id为3的数据，导致事务A多读到了事务B中的数据

事务隔离级别

为了防止事务并发时出现以上各种问题，数据库中设计了几种事务与事务之间的隔离级别。

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommitted未提交读RU	会	会	会
Read Committed已提交读RC(Oracle默认)	不会	会	会
Repeatable Read可重复读RR(MySQL默认)	不会	不会	会
Serializable可序列化	不会	不会	不会

查看事务隔离级别

select @@transatcion_isolation

设置事务隔离级别

set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]

触发器trigger

如果要在更新某张表之前或之后，自动执行另一组sql时，可以使用触发器实现。

如表A是客户表，表B是操作日志表，对表A进行更新操作时，将操作的记录保存到表B中。

慎用触发器，因为如果有10000条记录，在修改所有记录时，触发器就会执行10000次，会花费很多时间。

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表名 for each row
begin
    触发时执行的sql;
end

-- 创建操作日志表
create table log(
    log_id int not null primary key auto_increment,
    log_opt varchar(20) not null,
    log_time datetime not null
)

-- 创建触发器，在向客户表中添加记录后，自动向日志表中添加记录
create trigger mytrigger
after insert on customer for each row
begin
    insert into log values(null,'添加了数据',now())
end

使用触发器

一旦创建成功触发器，无需刻意调用，在执行相应的操作时，自动执行触发器

-- 只对customer表做插入操作，会自动向log表中添加记录
insert into customer values(null,'测试插入','123123',0,null);

删除触发器

drop trigger 触发器名;

存储过程procedure

类似于java中的方法，定义一组用于完成特定功能的sql语句。

定义存储过程后，通过调用存储过程名，就可以执行定义时的内容。

存储过程可以有参数。

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();
-- 调用输入型参数的存储过程
call 存储过程名('实参');
-- 调用输出型参数的存储过程
call 存储过程名(@变量);
-- 调用输入输出型参数的存储过程
set @变量
call 存储过程名(@变量)

定义存储过程

create procedure 存储过程名([参数类型 参数名 参数数据类型])-- 参数类型分为输入型/输出型/输入输出型
begin
   sql语句;
end

无参数

create procedure 存储过程名()
begin 
    sql语句
end

-- 创建存储过程，查询每本图书的书名、作者、类型
CREATE PROCEDURE myproc1 () BEGIN
    SELECT
        book_name,
        book_author,
        type_name 
    FROM
        book_info bi,
        book_type bt 
    WHERE
        bi.type_id = bt.type_id;
END
-- 调用存储过程
call myproc1();

输入型参数

create procedure 存储过程名(in 形参名 数据类型)
begin 
    sql语句;
end

-- 根据图书类型查询该类型下的所有图书
CREATE PROCEDURE myproc2 (IN lx VARCHAR ( 20 )) 
BEGIN
    SELECT
        * 
    FROM
        book_info bi,
        book_type bt 
    WHERE
        bi.type_id = bt.type_id 
        AND type_name = lx;
END

-- 调用
call myproc2('杂志')

输出型参数

类似于java中有返回值的方法

create procedure 存储过程名(out 形参名 数据类型)
begin 
    sql语句;
    -- 通常需要将查询出的结果通过into赋值给形参
end

-- 根据作者查询图书数量
CREATE PROCEDURE myproc3 ( IN zz VARCHAR (20), OUT book_count INT ) 
BEGIN
-- 将查询的结果into到参数book_count中
    SELECT
        count( book_id ) INTO book_count 
    FROM
        book_info 
    WHERE
        book_author = zz;

    END
-- 调用存储过程,@x表示将存储过程的输出型参数保存到变量x中
call myproc3('金庸',@x)
-- 查询参数中保存的数据
select @x

输入输出型参数

create procedure 存储过程名(inout 形参名 数据类型)
begin 
    sql语句;
end

-- 查询书名中带有指定文字的图书名、作者和类型
create procedure myproc4(inout keyword varchar(20))
begin 
    SELECT
        book_name,
        book_author,
        type_name 
    FROM
        book_info bi,
        book_type bt 
    WHERE
        bi.type_id = bt.type_id 
        AND book_name LIKE concat('%',keyword,'%');
end 
-- 调用存储过程
set @keyword='龙';
call myproc4(@keyword);
select @keyword;

删除存储过程

drop procedure 存储过程名;

MySQL编程

在定义存储过程中，可以定义变量、使用流程控制语句等。

定义变量

create procedure 存储过程名()
begin 
    -- declare 变量名 数据类型;
    declare num int;
    declare name varchar(20);
end

给变量赋值

create procedure 存储过程名()
begin 
    declare num int;
    declare name varchar(20);
    -- 给num赋值 
    -- select 字段/值 into 变量 [from 表];
    select 123 into num;
    select book_name into name from book_info where book_id=1;
end

读取变量的值

create procedure 存储过程名()
begin 
    declare num int;
    declare name varchar(20);
    select 123 into num;
    select book_name into name from book_info where book_id=1;
    -- select 变量;
    select num;
    select name;
end

-- 创建存储过程,查询所有图书总数，保存到变量中
create procedure myproc5()
begin 
    -- 定义变量
    declare sum_num int;
    -- 给变量赋值
    select sum(book_num) into sum_num from book_info ;
    -- 打印变量的值
    select sum_num;
end
-- 调用存储过程
call myproc5()

条件语句

单分支if语句

if 条件
then 
    满足条件时执行的sql；
end if;

-- 根据作者查询图书库存，如果不足1000，输出'库存不足1000'
create procedure myproc6(in zz varchar(20))
begin
    -- 定义变量保存根据作者查询到的图书库存
    declare num int;
    -- 查询sql，将结果保存到变量中
    select sum(book_num) into num from book_info where book_author = zz ;
    -- 判断变量num 
    if num<1000
    then 
        select '库存不足1000';
    end if;
end

call myproc6('郭敬明')

双分支if语句

if 条件
then 
    满足条件时执行的sql;
else 
    不满足条件时执行的sql;
end if;

-- 根据图书类型查询图书数量，如果不足5，输出"不足5种图书"，如果足够5，输出详情
create procedure myproc7(in lx varchar(20))
begin 
    -- 定义变量保存图书数量
    declare num int;
    -- 给变量赋值
    select count(book_id) into num 
    from book_info bi inner join book_type bt on bi.type_id=bt.type_id
    where type_name = lx;
    -- 判断
    if num>=5
    then
        select * from book_info bi inner join book_type bt on bi.type_id=bt.type_id where type_name=lx;
    else
        select '不足5种图书';
    end if;
end
-- 调用存储过程
call myproc7('小说')
call myproc7('漫画')

case语句

CASE 变量
    WHEN 值 THEN
        满足该值时执行sql语句;
    WHEN 值 THEN
        满足该值时执行sql语句;
    ELSE
        没有任何值满足时sql语句;
END CASE;

-- case语句
create procedure myproc8(in num int)
begin
    CASE num
        WHEN 1 THEN
            select '1';
        WHEN 5 THEN
            select '5';
        ELSE
            select '都不是';
    END CASE;
end
-- 调用
call myproc8(6)

循环语句

while循环

while 条件 do
    满足条件时执行的内容;
end while;

-- 添加10个客户
create procedure myproc9()
begin 
    -- 定义循环变量
    declare i int;
    -- 初始化循环变量
    select 1 into i;
    -- while循环
    while i<=10 do
        insert into customer values(null,concat('测试用户',i),'123123',0,null);
        -- 更新循环变量
        set i=i+1;
    end while;
end

call myproc9()

repeat循环

repeat
    循环体;
until 条件 end repeat;

-- repeat循环
create procedure myproc10()
begin 
    declare num int;
    select 50 into num;
    repeat 
        insert into customer values(null,concat('测试用户',num),'123123',0,null);
        set num=num+1;
    until num=60 end repeat;
end
-- 调用
call myproc10()

loop循环

循环名:loop
    循环体;
    if 条件 then leave 循环名;
    end if;
end loop;

-- loop循环
create procedure myproc11()
begin 
    declare num int ;
    select 100 into num;
    test:loop
        insert into customer values(null,concat('测试用户',num),'123123',0,null);
        set num=num-1;
        if num=90 then leave test;
        end if;
    end loop;    
end
-- 调用
call myproc11()

MySQL核心内容

SQL语句

数据库和数据表的创建、修改、删除
数据完整性(约束)
单表增删改查CURD
函数
多表查询、嵌套查询

数据库设计

实体关系模型(ER)
ER图
范式

JDBC

连接MySQL所需jar文件
- 普通java工程需要手动导入jar文件
- maven项目需要使用依赖自动导入jar文件

MySQL驱动名

//mysql5.5之前版本
Class.forName("com.mysql.jdbc.Driver");
//mysql8之后版本
Class.forName("com.mysql.cj.jdbc.Driver");

连接数据库的字符串

String url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai";
String username="root";
String password="密码";

事务

事务的概念和特性
事务并发时出现的问题
- 脏读
- 不可重复读
- 幻读
事务的隔离级别
- read uncommitted 可能会出现脏读、不可重复读和幻读问题，效率最高
- read committed 解决了脏读问题，可能会出现不可重复读和幻读问题
- repeatable read MySQL默认 解决了脏读和不可重复读问题，可能会出现幻读问题
- serializable 解决了脏读、不可重复读和幻读问题，效率最低