66-数据库专题

1、数据库引擎

1）、查看数据库引擎

-- 支持什么存储引擎
show engines;

-- 当前默认的存储引擎
show variables like '%storage_engine%';

2）、查看版本

select version();

3）、修改默认引擎

window:my.ini

linux:my.cnf

[mysqld]
default-storage-engine=INNODB
#default-storage-engine=MYISAM

2、数据库事务

1）、查看事务隔离级别

-- 查看当前会话事务级别
show variables like '%tx_isolation%';
-- OR
select @@tx_isolation;

-- 查看全局事务级别
SELECT @@global.tx_isolation;

-- 查看当前会话事务级别
SELECT @@session.tx_isolation;

2）、演示数据

DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户昵称',
  `user_money` int(11) NULL DEFAULT 0 COMMENT '教师ID',
  `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户现金表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, '张三', 1000, '2020-10-28 07:18:02');
INSERT INTO `tb_user` VALUES (2, '李四', 0, '2020-10-28 07:18:14');

CREATE TABLE `tb_user_log` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '记录ID',
  `user_id` int(11) DEFAULT '0' COMMENT '用户ID',
  `user_money` int(11) DEFAULT '0' COMMENT '原始金额',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '转账时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户转账记录表'

3）、演示事务提交

-- 手动开启事务
start transaction;

update tb_user set user_money=user_money+100 where id=2
update tb_user set user_money=user_money-100 where id=1

-- 手动提交事务
commit;

select * from tb_user

4）、演示事务回滚

-- 手动开启事务
start transaction;

update tb_user set user_money=user_money+100 where id=2
update tb_user set user_money=user_money-100 where id=1

-- 手动事务回滚
rollback;

select * from tb_user

5）、配置文件修改默认事务隔离级别

# READ-UNCOMMITTED
# READ-COMMITTED
# REPEATABLE-READ
# SERIALIZABLE

[mysqld]
transaction-isolation = READ-COMMITTED

6）、动态设置隔离级别

-- SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
-- SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- 设置当前会话的事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置所有会话的事务隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

7）、演示脏读

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 会话1
start transaction;
select * from tb_user
update tb_user set user_money=user_money+100 where id=2
rollback;

-- 会话2
start transaction;
select * from tb_user
commit;

3、数据库索引

1）、创建索引

-- CREATE INDEX 索引名 ON 表名 (列名)
CREATE INDEX user_money ON tb_user (user_money)

-- 通过修改表命令来添加索引
ALTER table tb_user ADD INDEX user_money(user_money)

有以下四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

该语句指定了索引为 FULLTEXT ，用于全文索引。

2）、显示索引

-- SHOW INDEX FROM 表名
SHOW INDEX FROM tb_user

3）、删除索引

-- DROP INDEX 索引名 ON 表名
DROP INDEX user_money ON tb_user;

4、数据库函数

1）、判断函数

1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0

2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替

3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。

5、数据库SQL

1）、数据结构

（1）学生表Student

（2）课程表Course

（3）教师表Teacher

（4）成绩表SC

2）、测试数据

（1）学生表Student

create table Student(SID varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

（2）课程表Course

create table Course(CID varchar(10),Cname nvarchar(10),TID varchar(10));

insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

（3）教师表Teacher

create table Teacher(TID varchar(10),Tname nvarchar(10));

insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

（4）成绩表SC

create table SC(SID varchar(10),CID varchar(10),score decimal(18,1));

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

3）、SQL语句

（1）查询“01”课程比“02”课程成绩高的学生的信息及课程分数

第一种情况：查询同时存在“01”课程和“02”课程的情况

select * from 
(
	select Student.*,ifnull(sca.score,0) as sc1,ifnull(scb.score,0) as sc2  from Student
	left join SC sca on Student.SID=sca.SID and sca.CID='01'
	left join SC scb on Student.SID=scb.SID and scb.CID='02'
) as x
where x.sc1>x.sc2

	select Student.*,ifnull(sca.score,0) as sc1,ifnull(scb.score,0) as sc2  from Student
	left join SC sca on Student.SID=sca.SID and sca.CID='01'
	left join SC scb on Student.SID=scb.SID and scb.CID='02'
	having sc1>sc2
	
select * from Student as a, SC as b, SC as c
where a.SID=b.SID and a.SID=c.SID and b.CID='01' and c.CID='02' and b.score>c.score

select * from Student as a, SC as b, SC as c
where a.SID=b.SID and a.SID=c.SID and b.CID='01' and c.CID='02'
having b.score>c.score

select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a , SC b , SC c
where a.SID = b.SID and a.SID = c.SID and b.CID = '01' and c.CID = '02' and b.score > c.score

select a.*,b.score 课程01的分数,c.score 课程02的分数 from Student a
left join SC b on a.SID=b.SID
left join SC c on a.SID=c.SID
where b.CID = '01' and c.CID = '02' and b.score > c.score

第二种情况：查询同时存在“01”课程和“02”课程的情况和存在“01”课程但可能不存在“02”课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)

select a.* , b.score 课程01的分数,c.score 课程02的分数 from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where b.score > ifnull(c.score,0)

（2）查询“01”课程比“02”课程成绩低的学生的信息及课程分数

第一种情况：查询同时存在“01”课程和“02”课程的情况

select a.* , b.score 课程01的分数 ,c.score 课程02的分数 from Student a , SC b , SC c
where a.SID = b.SID and a.SID = c.SID and b.CID = '01' and c.CID = '02' and b.score < c.score

第二种情况：查询同时存在“01”课程和“02”课程的情况和不存在“01”课程但存在“02”课程的情况

select a.* , b.score 课程01的分数 ,c.score 课程02的分数
from Student a
left join SC b on a.SID = b.SID and b.CID = '01'
left join SC c on a.SID = c.SID and c.CID = '02'
where ifnull(b.score,0) < c.score

（3）查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select * from 
(
	select SID,avg(scores) as ss,sum(scores) cc,count(SID) dd from 
	(
		select Student.*,Course.*,ifnull(SC.score,0) as scores from Student
		NATURAL join Course
		left join SC on Student.SID=SC.SID and Course.CID=SC.CID
	) as x
	group by SID
	having ss>=60
) as y
left join Student on y.SID=Student.SID

select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , SC b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) >= 60
order by a.SID

（4）查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

情况一：查询在sc表存在成绩的学生信息的SQL语句。

select a.SID , a.Sname , cast(avg(b.score) as decimal(18,2)) avg_score
from Student a , SC b
where a.SID = b.SID
group by a.SID , a.Sname
having cast(avg(b.score) as decimal(18,2)) < 60
order by a.SID

情况二：查询在sc表中不存在成绩的学生信息的SQL语句。

select a.SID , a.Sname , ifnull(cast(avg(b.score) as decimal(18,2)),0) avg_score
from Student a left join SC b
on a.SID = b.SID
group by a.SID , a.Sname
having ifnull(cast(avg(b.score) as decimal(18,2)),0) < 60
order by a.SID

（5）查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

情况一：查询所有有成绩的SQL。

select a.SID 学生编号 , a.Sname 学生姓名 , count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a , SC b
where a.SID = b.SID
group by a.SID,a.Sname
order by a.SID

情况二：查询所有(包括有成绩和无成绩)的SQL。

select a.SID 学生编号 , a.Sname 学生姓名 , count(b.CID) 选课总数, sum(score) 所有课程的总成绩
from Student a left join SC b
on a.SID = b.SID
group by a.SID,a.Sname
order by a.SID

（6）查询“李”姓老师的数量

-- 方法1
select count(Tname) 李姓老师的数量 from Teacher where Tname like '李%'

-- 方法2
select count(Tname) 李姓老师的数量 from Teacher where left(Tname,1) = '李'

（7）查询学过“张三”老师授课的同学的信息

select distinct Student.* from Student , SC , Course , Teacher
where Student.SID = SC.SID and SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三'
order by Student.SID

（8）查询没学过“张三”老师授课的同学的信息

select m.* from Student m 
where SID not in (
	select distinct SC.SID from SC , Course , Teacher 
	where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三'
) 
order by m.SID

（9）查询学过编号为“01”并且也学过编号为“02”的课程的同学的信息

-- 方法1

select Student.* from Student , SC
where Student.SID = SC.SID and SC.CID = '01' 
and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') 
order by Student.SID

-- 方法2

select Student.* from Student , SC 
where Student.SID = SC.SID and SC.CID = '02' 
and exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '01') 
order by Student.SID

-- 方法3

select m.* from Student m where SID in
(
	select SID from
		(
			select distinct SID from SC where CID = '01'
			union all
			select distinct SID from SC where CID = '02'
		) t 
	group by SID having count(1) = 2

)
order by m.SID

（10）查询学过编号为“01”但是没有学过编号为“02”的课程的同学的信息

-- 方法1

select Student.* from Student , SC 
where Student.SID = SC.SID and SC.CID = '01' 
and not exists (Select 1 from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') 
order by Student.SID

-- 方法2

select Student.* from Student , SC 
where Student.SID = SC.SID and SC.CID = '01' 
and Student.SID not in (Select SC_2.SID from SC SC_2 where SC_2.SID = SC.SID and SC_2.CID = '02') 
order by Student.SID

（11）查询没有学全所有课程的同学的信息

select Student.*
from Student , SC
where Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex 
having count(CID) < (select count(CID) from Course)

select Student.*
from Student left join SC
on Student.SID = SC.SID
group by Student.SID , Student.Sname , Student.Sage , Student.Ssex 
having count(CID) < (select count(CID) from Course)

（12）查询至少有一门课与学号为“01”的同学所学相同的同学的信息

select distinct Student.* from Student , SC 
where Student.SID = SC.SID 
	and SC.CID in (select CID from SC where SID = '01') 
	and Student.SID <> '01'

（13）查询和“01”号的同学学习的课程完全相同的其他同学的信息

select Student.* from Student where SID in
(select distinct SC.SID from SC where SID <> '01' and SC.CID in (select distinct CID from SC where SID = '01')
group by SC.SID 
having count(1) = (select count(1) from SC where SID='01'))

（14）查询没学过“张三”老师讲授的任一门课程的学生姓名

select Student.* from Student where Student.SID not in
(
	select distinct SC.SID from SC , Course , Teacher 
	where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.tname = '张三'
)
order by Student.SID

（15）查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select Student.SID , Student.sname , cast(avg(score) as decimal(18,2)) avg_score from Student , SC
where Student.SID = SC.SID 
	and Student.SID in (select SID from SC where score < 60 group by SID having count(1) >= 2)
group by Student.SID , Student.sname

6、触发器

1）、什么是触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

2）、触发器的四要素

触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)

3）、创建触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END

注意：对同一个表相同触发时间的相同触发事件，只能定义一个触发器；可以使用old和new来引用触发器中发生变化的记录内容。

CREATE TRIGGER trigger_log BEFORE UPDATE
ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO tb_user_log(user_id,user_money) VALUES(old.id,old.user_money);
END

4）、查询触发器

SHOW TRIGGERS;

5）、删除触发器

DROP TRIGGER trigger_log

7、存储过程

https://www.cnblogs.com/luo813/p/8993727.html