-- 支持什么存储引擎
show engines;
-- 当前默认的存储引擎
show variables like '%storage_engine%';
select version();
window:my.ini
linux:my.cnf
[mysqld]
default-storage-engine=INNODB
#default-storage-engine=MYISAM
-- 查看当前会话事务级别
show variables like '%tx_isolation%';
-- OR
select @@tx_isolation;
-- 查看全局事务级别
SELECT @@global.tx_isolation;
-- 查看当前会话事务级别
SELECT @@session.tx_isolation;
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='用户转账记录表'
-- 手动开启事务
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
-- 手动开启事务
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
# READ-UNCOMMITTED
# READ-COMMITTED
# REPEATABLE-READ
# SERIALIZABLE
[mysqld]
transaction-isolation = READ-COMMITTED
-- 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;
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;
-- 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 ,用于全文索引。
-- SHOW INDEX FROM 表名
SHOW INDEX FROM tb_user
-- DROP INDEX 索引名 ON 表名
DROP INDEX user_money ON tb_user;
1.isnull(exper) 判断exper是否为空,是则返回1,否则返回0
2.ifnull(exper1,exper2)判断exper1是否为空,是则用exper2代替
3.nullif(exper1,exper2)如果expr1= expr2 成立,那么返回值为NULL,否则返回值为 expr1。
(1)学生表Student
学生编号SID | 学生姓名Sname | 出生年月Sage | 学生性别Ssex |
---|---|---|---|
(2)课程表Course
课程编号CID | 课程名称Cname | 教师编号TID |
---|---|---|
(3)教师表Teacher
教师编号TID | 教师姓名Tname |
---|---|
(4)成绩表SC
学生编号 | 课程编号 | 分数 |
---|---|---|
SID | CID | score |
(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);
(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
触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
监视某种情况,并触发某种操作,它是提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,例如当对一个表进行操作( insert,delete, update)时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素:
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
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
SHOW TRIGGERS;
DROP TRIGGER trigger_log