目录
定义用户变量(或者赋值)
set @变量名=值
-- 创建自定义用户变量，当当前的用断开连接的时候就会消失 set @value=1;
查询用户变量值
select @变量
-- 查询定义的数据 select @value;
子查询
select型子查询、where型子查询、from型子查询、exists型子查询
标量子查询：返回单一值的标量，最简单的形式。
列子查询：返回的结果集是 N 行一列。
行子查询：返回的结果集是一行 N 列。
表子查询：返回的结果集是 N 行 N 列。
select型子查询
外语句先执行，后执行子语句（有遍历效果）
-- 查询课程表并显示课程老师的名称 select *, (select tname from teacher t where t.tid = c.teacher_id)name from course c limit 0,1;
-- 20、查询每门课程 成绩 最好的前两名学生姓名
select co.cid, co.cname,
(select st.sname from score sc
right join student st on st.sid = sc.student_id
where sc.course_id = co.cid order by sc.num desc limit 0,1)'第1名',
(select st.sname from score sc
right join student st on st.sid = sc.student_id
where sc.course_id = co.cid order by sc.num desc limit 1,1)'第2名'
from course co;
where型子语句
先执行子语句，后执行外查询
--查询学了体育课程的学生
select st.* from student st where st.sid in (
select sc.student_id from score sc
left join course co on co.cid = sc.course_id
where co.cname = '体育'
)
-- 任课最多的老师中学生单科成绩最高的学生
-- 找出任课最多的老师
select c.teacher_id
from course c
group by c.teacher_id HAVING count(*) = (
select count(*) n from course c
group by c.teacher_id order by n desc limit 1)
-- 找出学了这些老师课程的学生
select c.cid from course c
where c.teacher_id in (
select c.teacher_id
from course c
group by c.teacher_id HAVING count(*) = (
select count(*) n from course c
group by c.teacher_id order by n desc limit 1)
)
-- 找出这些课程中成绩最高的学生
-- n就是比当前行（课程相同）分数高的个数
select * from score s1
where (
select count(*) from score s2 where s1.course_id = s2.course_id and s1.num < s2.num
) < 1
and course_id in (
select c.cid from course c
where c.teacher_id in (
select c.teacher_id
from course c
group by c.teacher_id HAVING count(*) = (
select count(*) n from course c
group by c.teacher_id order by n desc limit 1)
)
)
order by s1.course_id
from型子语句
先执行子语句，后执行外查询
--列出三年二班学了体育的学生
select a.* from (select st.sname, cl.caption from score sc
left join course co on co.cid = sc.course_id
left join student st on st.sid = sc.student_id
left join class cl on cl.cid = st.class_id
where co.cname = '体育') a where a.caption = '三年二班';
exists型子查询
可以用来代替 in / not in
先执行外语句，再执行子查询，根据子查询是否返回结果行来决定外查询结果行是否保留
-- 查询学了课程id为1的学生的姓名 select * from student st where st.sid in ( select sc.student_id from score sc where sc.course_id='1' ) select * from student st where exists( select * from score sc where sc.course_id ='1'and sc.student_id = st.sid )
-- 22、查询没学过“李平”老师课程的学生姓名以及选修的课程名称；
--1.查询出学过李平老师课程的学生id
--2.再根据学生反取没学过的学生id
--3.根据学生id关联成绩表再关联课程表得出他们学习的课程名称
select st.sname, c.cname from student st
left join score sc on sc.student_id = st.sid
left join course c on c.cid = sc.course_id
where not exists (
select sc.student_id from score sc
left join course c on c.cid = sc.course_id
left join teacher t on t.tid = c.teacher_id
where t.tname = '李平老师' and sc.student_id = st.sid
)
any(some)、all
| 运算符\关键字 | any | all |
|---|---|---|
| >、>= | 最小值 | 最大值 |
| <、<= | 最大值 | 最小值 |
| = | 任意值 | |
| <>、!= | 任意值 |
注意：= any 等价于 in，!= all 等价于 not in，= all 是没有 意义的。
举例
-- 创建一个职位表
CREATE TABLE salary_table(
id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
position VARCHAR(40) NOT NULL,
salary INT);
insert salary_table(position,salary) values('JAVA',8000),('Java',8400),('Java',9000),('Python',6500),('Python',10000),('Python',8900);
select * from salary_table;
需求：查找出高于 Python 职位任一职位工资的其它职位
SELECT * FROM salary_table WHERE salary > ANY( SELECT salary FROM salary_table WHERE position = 'Python') and position not like '%Python%';
需求：查找出高于 Python 职位任何职位工资的其它职位
SELECT * FROM salary_table WHERE salary > all( SELECT salary FROM salary_table WHERE position = 'Python') and position not like '%Python%';
需求：查找出等于Python 职位任何职位工资的其它职位
SELECT * FROM salary_table WHERE salary = any( SELECT salary FROM salary_table WHERE position = 'Python') and position not like '%Python%';
数据库事务
事务指的是程序的某一操作的单元，通常来说，事务指的就是数据库的事务
数据库事物的特性
-
原子性(Atomicity)：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执。
-
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
-
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
-
持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。事务一旦提交，对数据做的更改就永久保存。
事务的实现方式
-
多线程。并发控制技术保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏
-
日志。日志恢复技术保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失,保证了事务的持久性。
事物的操作
INNODB是支持事务的引擎，而myisam引擎是不支持事务的。
-
开启 begin
-
提交 commit
-
回滚 rollback
事物的隔离级别
并发事务带来的问题
-
更新丢失：事务A和事务B同时操作，事务B覆盖了事务A做的操作，导致事务A的更新丢失了
-
脏读：事务A读取到事务B还未提交的事务
-
不可重读：事务A在同一事务的不同时间段内，读取同一数据，得到的值不一样（读取到 其它事务修改的值）
-
幻读：事务A在同一事务的不同时间段内，第1次读取的结果行与第2次读取的结果行数不一样（读取到其它事务新增的内容）
事务的隔离级别
-
未读提交：能读取到其它事务未提交的数据
-
读已提交：只能读取到其它事务已提交的数据
-
可重复读（默认隔离）：事务A在同一事务的不同时间段内，读取同一数据，得到的值一样（即便其它事务修改了该值）
-
串行化：事务依次执行，不存在并发问题
数据锁
-
共享锁/排他锁：数据访问
读锁/写锁
-
乐观锁/悲观锁
乐观锁：对事物保持乐观态度，认为不会有其他事务来修改数据，仅在更新前查询是否修改
悲观锁：对事务保持悲观态度，认为一定有其它事务来修改数据，所以操作时会加锁，不让其他事务操作
-
表锁/行锁
行锁：一次锁1~n行数据
表锁：一次锁整张表
行锁：开销大，加锁慢；容易出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
表锁：开销小，加锁快；较难出现死锁；锁定粒度大，发生锁冲突概率最高，并发度最低。
数据库表设计
数据库三大范式
-
第1范式：设计表时，应该尽量保证表中的属性不可再分割(设计单表)
-
第2范式：非主属性必须完全依赖主属性(多对多设计)
-
第3范式：属性不依赖于其他非主属性(一对多设计)
ER图与实体
ER图：ER(Entity,RelstionShip)图又称实体关联图、实体联系图，是用来描述实体本身以及其他实体关系一种图。
ER图核心要素
-
实体：长方形
-
属性：椭圆形
-
关系：菱形
弱实体和强实体:一个实体必须依赖于另一个实体存在，那么前者是弱实体，后者是强实体，弱实体必须依赖强实体存在。
复合实体：复合实体也称联合实体或桥接实体，常常用于实现两个或多个实体间的M：N联系，它由每个关联实体的主玛组成，用长方体内加一个菱形来表示。
数据建模
CREATE TABLE IF NOT EXISTS t_class (id INT AUTO_INCREMENT,
name VARCHAR(10) comment '班级名称'
, primary key(id) );
CREATE TABLE IF NOT EXISTS t_student (id INT AUTO_INCREMENT,
name VARCHAR(10) comment '学生姓名',
sex VARCHAR(3) comment '学生性别',
class_id INT NOT NULL comment '班级id'
, primary key(id) );
CREATE TABLE IF NOT EXISTS t_attendance (id INT AUTO_INCREMENT,
a_date DATE NOT NULL comment '考勤日期',
status VARCHAR(10) NOT NULL comment '考勤状态（早退/迟到/请假/正常）',
exception_time INT(3) NOT NULL default 0 comment '异常时间',
student_id INT(10) NOT NULL comment '学生id'
, primary key(id) );
alter table t_attendance add constraint t_attendance_student_id_fk0 foreign key (student_id) references t_student (id);
外键约束与级联操作
外键：用来建立两个表的关联关系的字段。
外键约束：用来约束表中的数据必须符合外键的相关条件，以及在更新数据，自动对关联表做出相关处理。
创建表时添加外键约束
[constraint <外键名>] foreign key 字段名1 [,字段名2,...] references <主表名> 主键列1 [,主键列2,...]
drop table 成绩;
drop table 学生;
drop table 科目;
CREATE TABLE IF NOT EXISTS 学生 ( id int PRIMARY KEY,
name varchar(10),
);
CREATE TABLE IF NOT EXISTS 科目 ( id int PRIMARY KEY,
name varchar(10)
);
CREATE TABLE IF NOT EXISTS 成绩 ( id int PRIMARY KEY,
学生ID INT(10),
科目ID INT(10),
成绩 DECIMAL(10,2),
CONSTRAINT fk_c_s FOREIGN KEY(学生ID) REFERENCES 学生(id),
CONSTRAINT fk_c_k FOREIGN KEY(科目ID) REFERENCES 科目(id)
);
show indexes from 成绩;
添加了外键约束后对数据的影响
-
添加时外键的值必须是在外键表存在的。
-
修改时外键的值必须是在外键表存在的。
-
删除时，不能先删除外键主表中外键所在行记录，只能先删除外键所在表的记录。
-
如果外键字段插入的值，在不为null时，必须在关联的主表字段中存在，才能插入成功，否则报错。
-
如果外键字段更新后的值，在不为null时，必须在关联的主表字段中存在时，才能更新成功，否则报错。
-
无法更新主表中，存在已关联到从表的字段数据，只能更无关联的数据。
-
如果存在关联到将被删除的主表记录的从表记录时，无法删除。必须先删除从表中对应记录后，才能删除主表记录。
在删除从表记录时：无影响
在删除主表时：
当外键关系还存在时，不能删除主表。必须先删除外键后，才能删除主表。
在删除从表时：无影响
外键约束的操作
-
建表后添加外键约束
alter table <数据表名> add constraint < 索引名> foreign key (<列名>) references <主表名>(<列名>)；
-
查看表中的外键
show indexes from 数据表名;
-
删除外键
alter table <数据表名> drop foreign key <外键约束名>;
CREATE TABLE IF NOT EXISTS 学生 ( id int PRIMARY KEY,
name varchar(10)
);
CREATE TABLE IF NOT EXISTS 科目 ( id int PRIMARY KEY,
name varchar(10)
);
CREATE TABLE IF NOT EXISTS 成绩 (
id int PRIMARY KEY,
学生ID INT(10),
科目ID INT(10),
成绩 DECIMAL(10,2),
-- [CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
-- REFERENCES <主表名> 主键列1 [，主键列2，…]
CONSTRAINT f1 FOREIGN key(学生ID) REFERENCES 学生(id),
CONSTRAINT f2 FOREIGN key(科目id) REFERENCES 科目(id)
);
#外键约束带来的影响
insert into 学生(id, name) values('2', '李四');
insert into 科目(id, name) values('1', '数学');
#新增时
insert into 成绩(id,学生id,科目id,成绩) values('1','1','1','80');
#修改时
update 成绩 set 学生id = '2' where id = '1';
#删除时
delete from 学生 where id = '1';
delete from 成绩 where id = '1';
#建表后添加外键约束
#ALTER TABLE <数据表名> ADD CONSTRAINT <索引名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);
alter table 成绩 add CONSTRAINT f1 FOREIGN key(学生id) REFERENCES 学生(id);
alter table 成绩 add CONSTRAINT f2 FOREIGN key(科目id) REFERENCES 科目(id);
#查看表中的外键（通过查看索引的方式）
show indexes from 成绩;
#删除外键
#ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;
alter table 成绩 drop FOREIGN key f1;
alter table 成绩 drop FOREIGN key f2;
#级联操作
alter table 成绩 add CONSTRAINT f1 FOREIGN key(学生id) REFERENCES 学生(id) ON delete set NULL ON update set NULL;
alter table 成绩 add CONSTRAINT f2 FOREIGN key(科目id) REFERENCES 科目(id) ON delete set NULL ON update set NULL;
级联操作
-
不做级联操作 restrict 、no action
-
级联更新 cascade
-
级联删除 cascade
-
设为NULL set null
视图
视图是一张虚拟表，本身没有数据。本质就是一段SQL，是一段保存在数据库，且可以重复利用的sql。
视图的优点
-
简单：不用关注视图背后的表结构和逻辑，就把视图当成一张表用。
-
安全：仅能访问到视图返回的数据。
视图的操作
#创建视图 create or replace view 视图名(命名) as select * from 表名; #使用视图 select * from 视图名; #修改视图 create or replace view 视图名(要修改的) as select * from 表名; #删除视图 drop view 视图名; #查看所有视图 select * from information_schema.views where table_schema='数据库名';
索引
索引类似书籍中的目录，本质是一张表。
索引的分类
-
主键索引：表中的主键字段就是一个索引。
-
唯一值索引：添加唯一值约束时就会添加唯一值索引。
-
普通索引：能快速检查想要的数据
-
全文索引：通常用于varchar、char、text类型的内容快速查找。
索引的底层实现原理
B+树
索引的操作
#创建普通索引 create index 索引名 on 数据表名(列名); #查看表中有哪些索引 show index from 数据表名; #删除索引 drop index 索引名 on 表名; #创建一个唯一值索引 create unique index 索引名 on 数据表名(列名);
索引的注意事项
-
索引能增加查询速度，但会影响CUD速度。
-
一张表最好不要超过3个索引。
-
一个字段重复率超过30%时，不建议添加索引。
触发器
触发器(trigger):监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器的4大要素
-
监视地点(table)
-
监视事件(insert/update/delete)
-
触发时间(after/before)
-
触发事件(内部执行逻辑)
触发器
#创建触发器
delimiter $ -- 设置MySQL执行结束标志，默认为;
create trigger tg_name -- 触发器名称
after | before -- 触发时间
insert | update | delete -- 监视事件
on table_name -- 监控表名
for each row -- 固定写法
begin -- 开始触发器内部逻辑
update goods set num=num-2 where gid = 1;
end$ -- 结束触发器内部逻辑
delimiter ; -- 重新将MySQL执行结束标志设置为;
#删除触发器
drop trigger 名称;
#查看触发器
show triggers;
select * from information_schema.TRIGGERS where trigger_schema='数据库名' and event_object_table='触发器名称';
#查看触发器的创建语法
show create TRIGGER 触发器名称;
存储过程
什么是存储过程？ 存储过程是数据库的一段SQL集合，是数据库的一个执行程序，也是一种数据库脚本。 存储过程的优势？ 存储过程可以写复杂的SQL语句，可以处理复杂的业务逻辑。 存储过程的语法
#简单写法(无参)
delimiter $
create PROCEDURE p1()
begin
select 666;
select 777;
end$
delimiter;
#调用存储过程
CALL p1();
#带入参的存储过程
delimiter $
create PROCEDURE p2(in n int) # n 变量名
BEGIN
select n; #显示参数值
set n = 66; #改变参数值
end$
delimiter;
set @n=5;
CALL p2(@n); #入参不能修改值
select @n;
#带出参的存储过程
delimiter $
create PROCEDURE p3(out n int)
BEGIN
select n; #显示参数值
set n = 88; #改变参数值
end$
delimiter;
set @n=8;
CALL p3(@n); #@n=8 写不进去
select @n;
#带出入参的存储过程
delimiter $
create PROCEDURE p4(inout n int)
BEGIN
select n; #显示参数值
set n = 88; #改变参数值
end$
delimiter;
set @n=4;
CALL p4(@n);
select @n;
#存储过程中的循环
delimiter $
create PROCEDURE p5(in n int)
begin
while(n>0) do
insert into 学生(id, name) values(n, CONCAT('姓名',n));
set n = n-1;
end while;
end$
delimiter;
call p5(100);
#查看存储过程
#查看当前数据库中的存储过程
show procedure status where db='db5';
select * from information_schema.routines
where routine_schema='db5' and routine_type='PROCEDURE';
#删除存储过程
drop PROCEDURE p2;
drop PROCEDURE p3;
drop PROCEDURE p4;
drop PROCEDURE p5;
8359
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
