基本SQL语句(一篇就够了)

总记录数: count 59

每页显示 page 20

总页数 pageCount = cpimt%page ?count/page:count/page +1;

# 查询第一页:select * from stu limit 0,20;# 查询第二页select * from stu limit 20,20;# 查询第三页select * from stu limit 40,20;# 如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:select * from stu limit (pageNum-1)*pageSize,pageSize;

MySQL是一个关系型数据库,不仅可以存储数据,还可以维护数据与数据之间的关系–通过数据表中的数据字段的外键约束

一对一关联一对多关联多对一关联多对多关联

人–身份z

学生–学籍

用户–用户详情

方案一:主键关联–两张数据表中的主键相同的数据互相对应的数据

方案二:唯一外键–在任意一张表中添加外键约束与另一张表主键关联,并且将外键列添加唯一约束

班级—学生(一对多)一个班级可以包含多个学生

学生–班级(多对一) 多个学生可以属于同一个班级

图书–分类 商品–商品类别

学生–课程 一个学生可以选择多门课,一门课也可以由多个学生选择

会员–社团 一个会员

外键约束–将一个列添加外键约束与另一张表的主键(唯一列)进行关联之后,这个外键约束添加的数据必须要在关联的主键字段中存在

案例:学生表 与 班级表(在学生表中添加外键与班级表的主键进行关联)

先创建班级表

create table classes(	class_id int primary key auto_increment,	class_name varchar(40) not null unique,	class_remark varchar(200));

创建学生表(在学生表中添加外键与班级表主键进关联)

# 在创建表的时候,定义cid 字段,并添加外键约束# 由于cid列 要与classes表class_id进行关联,因此cid字段类型长度要与class_id一致create table students( 	stu_num char(8) primary key, 	stu_name varchar(30) not null unique, 	stu_gender char(2) not null, 	stu_age int not null, 	cid int, 	constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id));# 创建表之后为cid 添加外键约束alter table students add constraint FK_STUENTS_CLASSES foreign key(cid) classes(class_id);

向班级表添加班级信息

insert into classes(class_name,class_remark) values('Python','...'),('mysql','...'),('JAVA','...');

向学生表中添加学生信息

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)values('11111','杜东京','男',18,2);# 添加学生时,设置给cid外键列的值必须在其关联的主表classes的class_id列存在

当学生表中存在的学生信息关联班级的某条记录时,就不能对班级表的这条记录进行修改ID和删除 *** 作

delete from classes where class_id=2;update classes set class_id=8 where class_id=2;#SQL 错误 [1451] [23000]: Cannot delete or update a parent row: a foreign key constraint fails (`dp_test2`.`students`, CONSTRAINT `FK_STUDENTS_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))

如果一定要修改Java2104的班级ID,该如何实现?

将应用Python班级的id的学生记录的cid修改为null在修改班级信息中的Python记录的class_id讲学生表中cid为NULL的记录设重新修改为 Python这个班级的class_id

UPDATE students set cid=null where cid=2;UPDATE classes  set class_id=4 where class_id =2;UPDATE students set cid = 4 where cid  is null;

# 删除原有的外键alter table sutdents drop foreign key FK_STUDENTS_CLASSES;# 重新添加外键,并设置级联修改和级联删除alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) on update cascade on delete cascade;

通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:

在MySQL中可以使用join实现多表的联合查询–链接查询,

inner join 内连接left join 左连接right join 右连接

select ... from tableName inner join tableName2;

笛卡尔积(A集合&B集合):使用A中的每个记录依次关联B中每个记录,笛卡尔集的总数=A总数*B总数如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积(依次使用tableName1表中的每条数据 去 匹配tableName2的每条数据)

两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件

使用on设置两张表连接查询的匹配条件

-- 使用where设置过滤条件:先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)select * from students inner join classes where students.cid=classes.class_id;-- 使用ON设置连接查询条件:先判断连接条件是否成立,如果成立两张表的数据进行组合在生成一条结果记录select * from students inner join classes on students.cid=classes.class_id;

结果:只取两种表中匹配条件成立的数据,任何一张表在另一张表如果没有找到对应匹配则不会出现在查询结果中(例如:小红和小明没有对应的班级信息 python课没有被人选)

需求:查询出所有的学生信息,如果学生有对应的班级信息,则将对应的班级信息也查询出来.

左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null

select * from leftTable join rightTable on 匹配条件;

右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null

如果连接查询的多张表中存在相同名字的字段,我们可以使用表名.字段名来分区,如果表名太长则不便于SQL语句的编写,我们可以使用数据表别名

select s.*,c.class_name from students s inner join classes c  on s.cid=c.class_id;

子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源) (第二次查询是基于第一次查询的结果进行的)

-- a.查询mysql的班级的编号select class_id from classes where class_name='mysql';-- b.查询此班级编号的学生信息select * from students where cid=2

子查询单行单列

select * from students where cid=(select class_id from classes where class_name='mysql');

-- a查询所有Java班的班级编号select class_id from classes where class_name like 'Java%';-- b查询这些班级编号中的学生信息(union 将多个查询语句的结果整合到一起)select * from students where cid = 1unionselect * from students where cid = 2unionselect * from students where cid = 3

子查询多行单列

-- 如果查询返回的结果是多个值(单列多行),条件使用**in / not in**select * from students where cid in (select class_id from classes where class_name like 'mysql%');

子查询返回多个值,多行多列

-- 传统的多条件查询select * from students where cid=1 and stu_gender='男';-- 子查询(先查询cid=1班级中的所有信息,将这些信息作为一个整体虚拟表 在基于这个虚拟表查询性别为男的学生信息)select * from (select * from students where cid=1) t where t.stu_gender='男';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TK5Ucpho-1638636879577)(/home/ddj/图片/2021-11-27 00-21-24 的屏幕截图.png)]

从SQL执行的流程中我们分析存在的问题

SQL指令无需客户端编写,通过 网络传送,可以节省网络开销,同事避免SQL指令在网络传输过程中被恶意篡改保证安全性;存储过程经过编译创建并保存在数据库中,执行过程无需重复的进行编译 *** 作,对SQL指令的执行过程进行了性能提升;存储过程中多个SQL指令之间存在逻辑关系,支持流程控制语句(分支,循环),可以实现更为复杂的的业务;

存储过程是根据不同的数据库进行编译.创建并存储在数据库中;当我们需要切换到其他的数据库产品时,需要重写针对于新数据库的存储过程存储过程受限于数据库产品,如果需要提高性能的优化会成为一个问题在互联网项目中,如果需要数据库的高(连接)并发访问,使用存储过程会增加数据库的连接执行时间(因为我们将复杂的业务交给了数据库进行处理)

create procedure <procedure_name>([IN/OUT args])begin	-- SQLend;-- 示例create procedure prod_test(in a int,in b int,out c int)begin	set c = a+b;end;

create procedure proc_test1(in a int,in b int,out c int)begin	set c = a+b;end;-- 调用存储过程set @m;call proc_test1(3,200,@m);-- 显示变量表select @m from dual; -- dual表是数据库自带的用来存储标量的表

存储过程中的变量分为2种,局部变量 和 用户变量

局部变量:定义在存储过程中的变量,只能存储过程内部使用

-- 局部变量要定义在存储过程中,而且必须定义存储过程开始declare   [default value];-- 示例-- 创建一个存储过程:计算输入参数的平方 与输入参数/2 之和create procedure proc_test3(in a int,out r int)begin		declare x int default 0;		declare y int default 1;		set x = a/2;		set y =a/2;		set r = x+y;end;

用户变量:相当于全家变量,定义的用户变量可以通过 select @attrName dual进行查询

– 用户变量会存储在mysql数据库的字典中(dual)

– 用户变量定义使用set关键字直接定义,变量名以@开头

set @n=1;

将存储过程中使用select … into …给变量赋值

create procedure proc_test4(out c int)begin	select count(stu_name) into c from students;-- 查询到学生数量赋值给cend;CALL proc_test4(@ddj);SELECT @ddj from dual;

因为用户变量相当于全局变量,可以使用SQL指令以及多个存储过程中共享,在开发中建议尽量少使用用户变量,用户变量过多会导致程序不易理解,难以维护

MySQL存储过程的参数一共三种: IN \ OUT \ INOUT

输入参数 – 在调用过程中传递数据存储过程的参数(在调用过程必须具有实际值的变量 或者 字面值)

-- 创建存储过程:添加学生信息create procedure proc_test8(IN snum char(8) ,IN sname varchar(30) , IN gender varchar(3) ,IN age int , IN cid int)begin		insert into students (stu_num, stu_name, stu_gender,stu_age, cid)		values(snum,sname,gender,age,cid);end;call proc_test8('1324518','二宝','女',20,1);

输出参数 – 将存储过程中产生的数据返回给过程的调用者,相当于Java方法中的返回值,单不同的是一个存储过程可以有多个输出参数

-- 创建存储过程 根据学生学号, 查询学生姓名create procedure proc_test10(IN snum varchar(8),OUT sname varchar(30))begin	select stu_name into sname from students where stu_num = snum;end;set @name='';call proc_test10('11111211',@name);select @name from dual;

create procedure proc_test11(INOUT str varchar(30))begin		select stu_name into str from students where stu_num=str;end;set @name = '11111211';call proc_test11(@name);SELECT @name from dual;

在存储过程中支持流程控制语句用于实现显示实现逻辑的控制

if-then-else

-- 单分支:如果条件成立则执行SQL1,否则执行SQL2if conditions then-- SQLend if;-- 案例create procedure proc_test13(in a int)begin	if a=1 then	insert into classes(class_name,class_remark) values('go','test');	end if;end;-- 双分支:如果条件成立则执行SQL1,否则执行SQL2if conditions then 	-- SQL1else 	-- SQL2end if;-- 案例create procedure proc_test14(IN a int)begin	if a=1 then		insert into classes(class_name,class_remark) values('go','test');	else 		insert into students (stu_num,stu_name,stu_gender,stu_age,cid) values ('20201234','小熊','男',19,1);	end if;	end;

case

create procedure proc_test15(in a int)begin	case a		when 1 then			-- SQL1		when 2 then			-- SQL2		when 3 then			-- SQL3	end case;end;-- 案例create procedure proc_test15(in a int)begin	case a		when 1 then			-- SQL1		 	insert into classes(class_name) values('语文');		when 2 then			insert into classes(class_name) values('数学');			-- SQL2		else 			-- SQL(如果变量的值和所有when的值都不匹配,则执行else中的这个SQL)		insert into classes(class_name) values('英语');	end case;end;call proc_test15(1);call proc_test15(2);call proc_test15(100);

create procedure proc_test17(in num int)begin	declare i int default 0;	while i<num do        -- SQL        insert into classes (class_name) values (concat('java',i));        set i=i+1;	end while;end;call proc_test17(10);

create procedure proc_test18(in num int)begin	declare i int default 0;	repeat 		insert into classes (class_name) values(concat('python',i));		set i = i+1;	until  i >= num end repeat;end;call proc_test18(5);

create procedure proc_test20(in num int)begin	declare i int default 1;	myloop : loop		-- SQL		insert into classes(class_name) values(concat('html',i));		set i = i+1;		if i>=num then			leave myloop;		end if;	end loop;end;call proc_test20(5);

存储过程是隶属于某个数据库的,也就是说我们存储过程创建在某个数据库之后,只能在数据库中调用此存储过程

查询存储过程:查询某个数据库中,有哪些存储过程

-- 根据数据库名,查询当前数据库的存储过程show procedure status where db='dp_test2';-- 查询存储过程的创建细节show create procedure dp_test2.proc_test1; 

修改存储过程是指修改存储过程的特性

alter procedure <procedureName>特征1 [特征2] [特征3]

存储过程的特征参数:

-- 删除存储过程-- drop 删除数据库的对象 ,数据库 数据表 存储过程 视图 触发器 索引drop procedure proc_test1;

使用存储过程解决企业项目中开发过程中的问题

案例:使用存储过程完成借书 *** 作

-- 创建数据库create database db_test3 character set utf8 ;use db_test3;-- 创建图书信息表create table books(    book_id int primary key auto_increment,    book_name varchar(50) not null,    book_author varchar(20) not null,    book_price decimal(10,2) not null,    book_stock int not null,    book_decs varchar(200));-- 添加图书信息insert into books(book_name,book_author,book_price,book_stock,book_decs)values('Java程序设计','亮亮',38.80,12,'亮亮老师带你学Java');insert into books(book_name,book_author,book_price,book_stock,book_decs)values('Java王者之路','威哥',44.40,9,'千峰威哥,Java王者领路人');-- 创建学生表create table students(    stu_num char(8) primary key,    stu_name varchar(20) not null,    stu_gender char(2) not null,    stu_age int not null);-- 添加学生信息insert into students(stu_num,stu_name,stu_gender,stu_age)values('1001','张三','男',20);insert into students(stu_num,stu_name,stu_gender,stu_age)values('1002','李四','女',20);insert into students(stu_num,stu_name,stu_gender,stu_age)values('1001','王五','男',20);

业务分析

创建一个存储过程实现借书的 *** 作:哪个学生借哪本书,借了多少本?

*** 作:

保存借书记录修改图书库存

条件:

判断学生是否存在?判断图书是否存在,库存是否存在?

-- 借书记录表create table records(	rid int primary key auto_increment,	snum char(8) not null,	bid int not null,	borrow_num int not null,	is_return int not null,-- 0 表示未归还 1 表示已经归还	borrow_date date not null,	constraint FK_RECORDS_STUDENTS foreign key(snum) references students(stu_num),	constraint FK_RECORDS_BOOKS foreign key(bid) references books(book_id));

-- 实现借书业务:-- 参数1:a输入参数 学号-- 参数2:b输入参数 图书编号-- 参数3:m输入参数 借书的数量-- 参数4:state输入参数 借书的状态(1 借书成功,2学生学号不存在,3 图书不存在 4库存不足)create procedure proc_borrow_book(IN a char(8),in b int,in m int,out state int)begin	declare stu_count int;	declare book_count int;	declare stock int default 0 ;	-- 判断学号是否存在: 根据参数 a  到学生信息表查询是否存在stu_num = a的记录	select count(stu_num)into stu_count from students where stu_num=a;	if stu_count>0 then        -- 学号存在        -- 判断图书ID是否存在:根据参数b 查询图书记录总数        select count(book_id) into book_count from books where book_id = b;        if book_count>0 then        	-- 图书存在        	-- 检查图书库存是否够用: 查询图书库存,然后和参数m进行比较        	select book_stock into stock from books where book_id = b;        	if stock >= m then        		-- 库存足够 可以借出        		set state = 1;        		-- 执行借书        		--  *** 作1:在借书记录表中添加记录        		insert into records(snum,bid,borrow_num,is_return,borrow_date)values(a,b,m,0,sysdate());        		--  *** 作2:        		update books set book_stock = stock-m where book_id=b;        	else         		-- 库存不足        		set state = 4;        	end if;        else         	-- 图书不存在        	set state = 3;        end if;	else 	-- 学号不存在		set state = 2;	end if;end;

问题:如果我们要创建一个存储过程,需要返回查询语句查询到的多条数据,该如何实现呢?

游标可以用来依次取出查询结果集中的每一条数据–逐条读取查询结果集中的记录

声明游标

declare cursor_name cursor for slect_statement;

declare mycursor cursor for select book_name,book_author,book_price from books;

打开游标

open mycursor;

使用游标

fetch mycursor into bname,bauthor,bprice;

关闭游标

close mycursor;

​

案例

create procedure proc_test2(out result varchar(200))begin	declare bname varchar(20);	declare bauthor varchar(20);	declare bprice decimal(10,2);	declare num int;	declare i int;	declare str varchar(50);	-- 此查询语句执行之后返回的结果是一个结果集(多条记录)	declare mycursor cursor for select book_name,book_author,book_price from books;	select count(book_id) into num from books;	-- 打开游标	open mycursor;		-- 使用游标要结合循环语句	set i =0;	while i

触发器,就是一种特殊的存储过程.触发器和存储过程一样是一个能够完成特定功能,存储在数据库服务器上的SQL片段,但是触发器无需调用,当对数据表中执行DML *** 作时自动触发SQL片段的执行,无需手动调用.

在MySQL,只有执行insert\delete\update *** 作才能触发触发器的执行

语法:

create trigger tri_name 
<before|after> 			-- 定义触发时机
<insert|delete|update>  -- 定义DML类型
ON <table_name> 
for each row            -- 声明为杭集触发器(只要 *** 作一条记录 触发器就执行一次)
SQL                     -- 触发器 *** 作

-- 创建触发器:当学生信息表添加 *** 作时,则向日志信息表中记录一条rizhi
create trigger tri_test1
after insert on students
for each row 
insert into stulogs(time,log_text)values(now(),concat('添加',NEW.stu_num,'学生信息'));

show triggers;

drop trigger tri_test1;

触发器多用于监听对数据表中的数据insert ,delete,update *** 作,在触发时通常处理一些DML的关联 *** 作;我们可以使用NEW和OLD关键字在触发器中获取这个触发器的DML *** 作的数据

NEW:在触发器用于获取insert *** 作添加的数据,update *** 作修改后的记录OLD:在触发器中用于获取delete *** 作删除前的数据,update *** 作修改前的数据

insert *** 作:NEW表示添加的新记录

create trigger tri_test1
after insert on students
for each row 
insert into stulogs(time,log_text)values(now(),concat('添加',NEW.stu_num,'学生信息'));

update *** 作中:NEW表示修改后是数据

create trigger tri_test2
after update on students 
for each ROW 
INSERT into stulogs (time,log_text) values(now(),concat('修改学生信息为:',NEW.stu_num,NEW.stu_name));

UPDATE students set stu_name='李10'where stu_num ='1007';

delete:OLD表示删除的记录

-- 当从students表中删除学生信息时,同时要在stulogs中添加一条日志
create trigger tri_test3
after delete on students
for each ROW 
insert into stulogs (time,log_text)values(now(),concat('删除',OLD.stu_num,'学生信息'));

delete from students  where stu_num ='1007';

update: OLD表示修改前的记录

 create trigger tri_test5after update on students for each ROW insert into stulogs(time,log_text) VALUES (now(),concat('将学生姓名从[',OLD.stu_name,']','修改为','[',new.stu_name,']'));update students set stu_name='哈哈' where stu_num='1005';

视图,就是由数据库中的一张表或者多张表特定的条件查询出数据构造得到 得到虚拟表

create view <view_name>
as
select_statement

示例1

-- 创建视图示例1:将学生表中性别为男的学生生成一个视图
create view view_test1
as
select * from students where stu_gender='男';
-- 查询视图
select * from view_test1;

示例2

-- 创建视图示例2:查询学生借书的信息(学生名,图书名,借书数量)
create view view_test2
as 
select s.stu_name,b.book_name,r.borrow_num
from books b inner join records r inner join students s 
on b.book_id=r.bid and r.snum=s.stu_num ;
select * from view_test2 where stu_name !='张三';     

视图是虚拟表,查询视图的数据是源于数据表的,当对视图进行 *** 作时,对原数据表中的数据是否有影响呢?

**查询 *** 作:**如果在数据表中添加了新的数据,而且这个数据满足创建视图时查询语句的条件,通过查询视图也可以查询出新增数据,当删除原表中满足条件的数据时,也会葱视图中删除

新增数据:如果在视图中新增数据,数据会被添加到原始数据表

删除数据:如果视图删除数据,数据也会从原表中删除

修改 *** 作:如果通过修改数据,则也将修改原数据表中的数据

-- 查询视图结构

-- 方式1create or replace view view_test1as select * from students where stu_gender='女';-- 方式2alter view view_test1as select * from students where stu_gender='男';

drop view view_test1;

数据库是用来存储数据的,在互联网应用中存储的数据可能会很多{大数据},数据表中的查询速度会随着数据量的增长逐渐变慢,从而导致响应用户请求的速度变慢–用户体验差 我们如何提高数据库的查询效率呢?

索引,就是用来提高数据表中数据的查询效率的.

索引,就是将数据表中的某一列/某几列的值取出来构造成便于查找的结构进行存储,生成数据表的目录 当前我们进行数据查询的时候,则先在目录 中进行查找得到对于的数据地址,然后在到数据表中根据地址快速的获取数据记录,避免全表扫描

MySQL中的索引,根据创建的所有的列不同,可以分为:

说明:

-- 创建唯一索引:创建唯一索引的列的值不能重复-- create unique index  on 表命(列名);create unique index index_test1 on tb_testindex(tid);

-- 创建普通索引-- create index  on 表名(列命);create index index_text2 on tb_testindex(name);

-- 创建组合索引-- create index  on 表明(列命1,列命2,列命3);create index index_test3 on tb_testindex(tid,name);

MySQL 5.6 版本新增加的索引,可以通过此索引进行全文检索 *** 作,因为MySQL全文检索不支持中文,因此这个全文索引不被开发者关注,在应用开发中通常是通过搜索引擎(数据库中间件)实现全文检索

create fulltext index <index_name> on 表名(字段名);

索引创建完成之后无需调用,当根据创建索引的列进行数据查询的时候,会自动使用索引;

组合索引需要根据创建索引的索引字段进行查询时触发

在命令行窗口中可以查看查询语句的查询规划

explain select * from tb_testindex where tid=250000\G;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CGXFLbyx-1638636879579)(/home/ddj/图片/2021-12-03 01-39-40 的屏幕截图.png)]

-- 命令行show create table tb_testindex\G;-- 查询数据表的索引show indexes from tb_testindex;-- 查询索引show keys from tb_testindex;

-- 删除索引: 索引是建立在表字段上的,不同的表中可能出现相同名称的索引-- 因此删除索引时需要指定表名drop index index_test3 on tb_testindex;

优点

缺点

注意事项

ACID特性,高频面试题

一致性(Consistency):事务执行之前和事务执行之后,数据库中的数据是一致的,完整性和一致性不能被破坏

隔离性:(lsolation):数据库允许多个事务同时执行(张三借Java书同时允许李四借Java书),多个并行的事务之间不能相互影响

持久性(Durabikity):事务完成之后,对数据的 *** 作是永久的

在MySQL中,默认DML指令的执行是自动提交的,当我们执行一个DML指令之后,自动同步到数据库中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rAffAaxE-1638636879580)(/home/ddj/图片/2021-12-03 17-09-59 的屏幕截图.png)]

开启事务,就是关闭自动提交

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8MBpgV8N-1638636879581)(/home/ddj/图片/2021-12-03 17-25-15 的屏幕截图.png)]

-- 开启事务start transaction;-- 借书业务--  *** 作1:在借书记录表中添加记录insert into records (snum,bid,borrow_num,is_return,borrow_date) values('1011',4,2,0,sysdate());SELECT  aaa;-- 事务回滚(清除连接缓存中的 *** 作,撤销当前事务已经执行的 *** 作)rollback;--  *** 作2:修改图书库存update books set book_stock=book_stock-2 where book_id=4;-- 提交事务(将连接缓存中的 *** 作写入数据文件)commit;

数据库允许多个事务并行,多个事务之间是隔离的,相互独立的;如果事务之间不相互隔离并且 *** 作同一数据时,数据的一致性可能被破坏

MySQL数据库事务隔离级别:

读未提交(read uncommitted):T2可以读取T1执行单未提交的数据;可能会导致出现脏读

脏读:一个事务读取到另一个事务中未提交的数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nT4UMwQE-1638636879582)(/home/ddj/图片/2021-12-03 17-45-54 的屏幕截图.png)]

读已提交(read committed):T2只能读取T1已经提交的数据;避免了脏读但可能会导致不可重复读(虚读)

不可重复读(虚读):在同一个事务中2次查询 *** 作读取到的数据不一致

例如:T2进行一次查询之后在第二次查询之前,T1修改并提交了数据,T2进行了第二次查询读取到的数据和第一次读取到数据不一致

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2gl80hq6-1638636879583)(/home/ddj/图片/2021-12-03 17-53-02 的屏幕截图.png)]

可重复读(repeatable read):T2执行第一次查询之后,在事务结束之前其他事务不能修改对应的数据;避免了不可重复读(虚读);但可能会导致幻读

幻读,T2对数据表中的数据进行修改然后查询,在查询之前向数据表中新增了一条数据,就导致T2以为修改了所有数据,但却查询出了与修改不一致的数据(T1事务新增的数据)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iYhRoTtT-1638636879584)(/home/ddj/图片/2021-12-03 19-59-44 的屏幕截图.png)]

我们可以通过设置数据库默认的事务的隔离界别来控制事务之间的隔离性;

也可以通过客户端与数据库的连接设置事务间的事务之间的隔离性;(在应用程序中设置–Spring)

MySQL数据库的默认的隔离级别为**‘可重复读’**

查看MySQL数据库默认的隔离级别

-- 在MySQL8.0.3之前select @@tx_isolation;-- 在MySQL8.0.3之后select @@transaction_isolation;

设置MySQL默认隔离级别

set session transaction isolation level <repeatable read>;

MySQL数据库作为数据存储的介质为应用系统提供数据存储的服务,我们如何设计出合理的数据库,数据表以满足应用系统的数据存储需求呢?

数据库是为应用系统服务的,数据库存储什么样的数据也是由应用系统来决定的.

当我们进行应用系统开发时,我们首先要明确应用应用系统功能需求------软件系统的需求分析

根据应用系统的功能,分析数据实体(实体,就是要存储的数据对象)

提取实体的数据项(数据项,就是实体的属性)

根据数据库设计三范式规范我们数据项:检查实体的数据项是否满足数据库设计三范式

绘制E-R图(实体关系图,直观的展示实体之间的关系)

数据库建模:

建库建表:编写SQL指令创建数据库,数据表

添加测试数据,SQL测试

学校图书管理系统(借书)

	1. 数据实体:       + 学生       + 图书       + 类别       + 借书记录       + 管理员	2. 提取数据项     + 学生(学号,姓名,性别,年龄,院系编号,院系名称,院系说明..)     + 图书(类别ID,类别名称,类别描述)     + 类别(图书ID,图书名称,图书作者,图书封面,图书价格,图书库存...)     + 借书记录(记录ID,学号,图书编号,数量,是否归还,还书日期)     + 管理员(管理员ID,登录名,登录密码,员工编号,员工姓名,员工编号)     + 员工(员工编号,员工姓名,手机,QQ,邮箱)

要求数据表的字段不可再分

​ 以下表不满足第一范式(在数据库中创建不出不满足第一范式的表)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RSCDc6aD-1638636879585)(/home/ddj/图片/2021-12-03 21-11-07 的屏幕截图.png)]

​ 将细分的列作为单独的一列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J4TXRVWe-1638636879586)(/home/ddj/图片/2021-12-03 21-11-44 的屏幕截图.png)]

不存在非关键字段对关键字段部分依赖

以下表不满足第二范式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TPHVHC7b-1638636879586)(/home/ddj/图片/2021-12-03 21-29-12 的屏幕截图.png)]

将每个关键字段列出来\关键字段的组合也列出来,依次检查每个非关键字段

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xc41FBdP-1638636879587)(/home/ddj/图片/2021-12-03 21-34-21 的屏幕截图.png)]

不存在非关键字段的之间的传递依赖

以下数据表不满足第三范式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EhhoDHcx-1638636879588)(/home/ddj/图片/2021-12-03 22-49-27 的屏幕截图.png)]

将关键字段和被依赖的关键字段分贝作为主键,依次检查所有非关键字段的依赖关系

E-R(Entity_Relationship) 实体关系图,用于直观的体现实体与实体之间的关联关系(一对一,一对多,多对一,多对多)

E-R图基本图例

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-znNJ5TyI-1638636879589)(/home/ddj/图片/2021-12-04 17-40-27 的屏幕截图.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8wIH1pSm-1638636879590)(/home/ddj/图片/2021-12-04 17-41-02 的屏幕截图.png)]

每个实体创建一张数据表

多堆多关联:额外创建一个数据表维护关系,关系表分别创建外键与两张表关联

一对多,多对一关联:在多的一端添加外键与一的一端主键建立外键约束

一对多关联:在任意一端创建外键与另一端关联主键关联,并将外键设置# 基本SQL语句(一篇就够了)

SQL(Structured Query Language)结构化查询语言，用于存取，查询，更新数据以及管理关系型数据库系统

SQL是1981年由IBM公司推出

根据SQL指令完成的数据库 *** 作的不同，可以将SQL指令分为四类

DDL Data Defintion language 数据库定义语言 用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改 DML Data Manipulation language 数据 *** 作语言 用于完成对数据表中的数据添加，删除，修改 DQL Data Query language 数据查询语言 用于将数据表中的数据查询出来 DCL Data Control Laguage 数据控制语言 用于完成事务管理等控制型 *** 作

SQL 基本语法

SQL指令不区分大小写

每条SQL表达式结束之后都以;结束

SQL关键字之间都以 空格进行分割

SQL之间可以不限制换行(可以有空格的地方就可以有换行)

使用DDL语句可以创建数据库，删除数据库，修改数据库

查询数据库

##显示mysql中所有是数据库

show databases;

##显示指定数据库的创建的SQL指令

show create database ;

创建数据库

##在mysql中创建一个数据库 dbName表示数据库名称

create database ;

##创建数据库，指定名称不存在的时候创建

create database if not exists ;

##在创建数据库的同时指定数据库的字符集(字符集：数据存储在数据库)

create database character set 字符集;

修改数据库

##修改数据库的字符集

alter database character set 字符集;

删除数据库

删除数据库时会删除数据库中所有的表以及数据库中的所有的数据

##删除数据库

drop database ;

##如果数据库存在则删除数据库

drop database if exists ;

使用/切换数据库

use ;

创建数据表

数据表实际上就是一个二维的表格，一个表格是由多列组成的表格[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DxyJRKz9-1638636880609)(/home/ddj/图片/2021-11-21 01-51-51 的屏幕截图.png)]

查询数据表

show tables;

查询表结构

desc ;

删除数据表

##删除数据表

drop table ;

##删除数据表如果表存在

drop table if exists ;

修改数据表

##修改表名

alter table rename to ;

##数据表也是有字符集的,默认字符集和数据库一致

alter table character set 字符集;

##添加字段(列)

alter table add ;

##修改的列表名和类型

alter table change ;

##只修改字段类型

alter table modify ;

##删除字段(列)

alter table drop ;

在MySQL中有多种数据类型可以有存放数值，不同类型存放的数值范围或者形式不同的

存储字符序列的类型

在MySQL数据库中,我们可以用字符串来存储时间,但是我们需要基于时间字段进行查询 *** 作(查询在某个时间段内的数据)就不便于查询实现

在创建数据表的时候,指定的对数据的列的数据限制性的要求(对表的列中的数据进行限制)

为什么给表中的数据添加数据呢?

保证数据的有效性保证数据的完整性保证数据的正确性

字段常见的约束有那些呢？

非空约束(not null):限制此列的值必须提供,不能null唯一约束(unique):在表中的多条数据,此列的值不能重复主键约束(primary key):非空+唯一,能够唯一标示数据表中的一条数据.外键约束(foreign key):建立不同表之间的关联关系.

主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)

当一个字段声明为主键之后，添加数据时;

此字段数据不能为null;此字段数据不能重复; 创建表时添加主键约束

create table books(
    book_isbn char(4),
    book_name varchar(20) primary key
);

或者

create table books(
    book_isbn char(4);
    book_name varchar(20),
    primary key(book_name)
);

删除数据表主键约束

alter table books drop primary key;

创建表之后添加主键约束

## 创建表时没有添加主键约束
create table books(
    book_isbn char(4),
    book_name varchar(10) 
);

## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;

主键自动增长

在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,

当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键

,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int ，同事设置为自动增长

当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.

定义主键自动增长(只能是数值类型)

auto_increment

create table types(
    type_id double primary key auto_increment,
    type_name varchar(20) not null,
    type_reamrk varchar(20)
);

注意：自动增长从1开始，每次添加一条数据，自动增长的列会+1，但是如果把某条数据删除，自动增长的数据也不会重复生产(自动增长的数据不会保证连续)

联合主键

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PZwNkKVA-1638636880610)(/home/ddj/图片/2021-11-21 15-18-40 的屏幕截图.png)]

定义联合主键

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)

);

注意:在实际企业项目的数据库设计中，联合主键使用频率不高，当一个数据表中没有明确的字段可以作为主键时，我们可以额外添加一个ID字段作为主键

pass

用于完成对数据表中的数据中插入，删除，修改

insert into <tableName>(colmnName,...)values(value1,....);

## 向数据表中指定的列添加数据(不允许为空的必须提供数据)
insert into stus(stu_num,stu_name,sut_gender)values('202012321','张三','男',21,'130313131');
## 数据表名后字段名列表顺序可以与表名不一致，但是values中的值的顺序必须与表明后字段名顺序对应
## 当要向表中的数据所有列添加数据的时候，表名后面的字段列表可以省略，但是values中的值顺序要于数据表中定义的字段保持一致
## 建议把字段写出了(增强字段的稳定性)

###　删除数据

从数据表中删除满足特定条件(所有)的数据

delete from <tableName> where conditions; 

## 删除学号为20210102的记录delete from stus where stu_num=220210102;## 删除年龄大于20岁的学生信息(如果多条数据满足情况则删除多条记录)delete from stus where stu_age>20;## 如果删除语句没有where子句，则表示删除当前数据表中的所有记录(敏感 *** 作)delete from stus;

对数据表中已经添加的记录进行修改

update <tableName> set columName=值 [where conditions]

示例

## 将学号为20210105的学生姓名修改为'孙七'
update stus set stu_name='孙七'　where stu_num='20210105';
## 将学号20210103的学生　性别修改为'男'，同事QQ修改为'77777' (修改多列)　
update stus set sut_age='男',stu_qq='77777' where stu_num='20210103';

## 如果update语句没有where子句,则表示修改当前表中的所有行(记录)
update stus set stu_name='Tom'

从数据表中提取满足特定条件的记录

单表查询多表联合查询

## select 关键字查询的是
select colnumName1,.. from <tableName>;
## 如果要显示查询到的的记录的所有列，可以使用*代替字段列表(在项目中不建议使用)
select * from <tableName>;

在删除，修改及修改的语句后都可以添加where子句(conditions)，用于筛选出满足特定的数据进行，删除，修改和查询 *** 作．

delete from tableName where conditions;
update <tableName> set ... where contitions;
select ... from <tableName> where contitions;

## ＝　等于
select * from stus where sut_num = '20202';
## != <>不等于
select * from stus where sut_num != '20202';
select * from stus where sut_num <> '20202';
## >大于
select * from stus where sut_age>18;
## <小于
select * from stus where sut_age<18;
## >=大于等于
select * from stus where sut_age>=18;
## <=小于等于
select * from stus where stu_age<=18;
## between and 区间查询
select * from stus where stu_age between 18 and 20;

在where子句中，可以将多个条件通过逻辑预算(and or not)进行连接,通过多个条件来筛选需要的 *** 作

## and 并且　筛选多个条件同时满足的记录select * from stus where stu_gender='女' and stu_age<21;## or　或者select * from stus where stu_gender='女'　or stu_gender='中间';## not 取反select * from stus where stu_age not between 18 and 21;

在where子句中可以使用like关键字来实现模糊查询

select * from tableName where columName like 'reg';

## 查询学生姓名包含字符o的学生信息select * from stus where stu_name like '%o%';## 查询学生姓名第一个字为张的学生信息select * from stus where stu_name like '张%';## 查询学生姓名最后一个字母为o的学生信息select * from stus where stu_name like '%o';##　查询学生姓名中第二个字母为o的学生信息select * from stus where stu_name like '_o％';

设置查询的列

声明显示查询结果的指定列

select colnumName,... from stus where stu_age>20;

对从数据表中查询的记录的列进行一定的运算之后显示出来.

## 出生年份　＝现在的年份-年龄select stu_name,2021-stu_age from stus;

我们可以为查询的结果的字段　取一个语义更强的别名

as 字段别名

##给2021-stu_age取一个别名(as　可以省略)select stu_name,2021-stu_age as 出生　from stus;

distinct

从查询的结果中将重复的记录消除

select distinct stu_age from stus;

将查询到的满足条件的记录按照指定的列的值升序/降序排列

select * from <tableName> where conditions by order by columnName asc|desc;

##单字段排序select * from stu order by sex desc;##多字段排序  首先性别降序　然后id升序select * from stu order by sex, id asc,

sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数

count() 统计函数,统计满足条件的指定字段的个数(记录数)

## 统计学生个数select (id) from stu;## 统计女生个数select count(sex) from stu WHERE sex='男';

max()记录指定列中的最大值

min()记录中指定列的最小值

sum()记录中指定列的和

avg()记录中指定记录的平均值

当我们向日期类型的列添加数据时,可以通过字符串类型(字符串格式必须是　yyyy-MM-dd hh:mm:ss)

如果我们要获取当前系统的时间添加到日期类型的列　可以使用now()或者sysdate()

curtime(); 返回当前时间
curdate();　返回当前日期

## upper(colynm)将字段的值转换成大写select upper(name) from stu;## lower(column)将指定列的值转换成小写select upper(name) from stu;## substring(column,start,len)从指定列中截取部分显示　start从1开始select id,substring(name,4,8);

select 分组字段/聚合函数　from 表名　[where 条件] group by 分组字段名;

# 先对查询的学生信息按性别进行分组(分成男,女),然后分别统计每组学生的个数select sex,count(sex)from stu group by sex;# 先对查询的学生信息按性别进行分组　然后计算select sex,avg(scroe) from stu group by sex;# 先对学生按照班级分组　然后统计各组的学生数量，在排序select age,count(stu) from stu group by age order by age asc;# 查询所有学生,按年龄进行分组,然后分别统计每组>1的人数,再筛选年龄升序 (having　隐藏记录　有group by 才能有　having)select age,count(num)from stugroup by agehaving count(num)>1order by age asc;

当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.

select ... from ... where ... limit param1,param2;

# 查询第一页:select * from stu limit 0,20;# 查询第二页select * from stu limit 20,20;# 查询第三页select * from stu limit 40,20;# 如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:select * from stu limit (pageNum-1)*pageSize,pageSize;

create table classes(	class_id int primary key auto_increment,	class_name varchar(40) not null unique,	class_remark varchar(200));

# 在创建表的时候,定义cid 字段,并添加外键约束# 由于cid列 要与classes表class_id进行关联,因此cid字段类型长度要与class_id一致create table students( 	stu_num char(8) primary key, 	stu_name varchar(30) not null unique, 	stu_gender char(2) not null, 	stu_age int not null, 	cid int, 	constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id));# 创建表之后为cid 添加外键约束alter table students add constraint FK_STUENTS_CLASSES foreign key(cid) classes(class_id);

insert into classes(class_name,class_remark) values('Python','...'),('mysql','...'),('JAVA','...');

insert into students (stu_num,stu_name,stu_gender,stu_age,cid)values('11111','杜东京','男',18,2);# 添加学生时,设置给cid外键列的值必须在其关联的主表classes的class_id列存在

delete from classes where class_id=2;update classes set class_id=8 where class_id=2;#SQL 错误 [1451] [23000]: Cannot delete or update a parent row: a foreign key constraint fails (`dp_test2`.`students`, CONSTRAINT `FK_STUDENTS_CLASSES` FOREIGN KEY (`cid`) REFERENCES `classes` (`class_id`))

UPDATE students set cid=null where cid=2;UPDATE classes  set class_id=4 where class_id =2;UPDATE students set cid = 4 where cid  is null;

# 删除原有的外键alter table sutdents drop foreign key FK_STUDENTS_CLASSES;# 重新添加外键,并设置级联修改和级联删除alter table students add constraint FK_STUDENTS_CLASSES foreign key(cid) references classes(class_id) on update cascade on delete cascade;

select ... from tableName inner join tableName2;

-- 使用where设置过滤条件:先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)select * from students inner join classes where students.cid=classes.class_id;-- 使用ON设置连接查询条件:先判断连接条件是否成立,如果成立两张表的数据进行组合在生成一条结果记录select * from students inner join classes on students.cid=classes.class_id;

select * from leftTable join rightTable on 匹配条件;

select s.*,c.class_name from students s inner join classes c  on s.cid=c.class_id;

-- a.查询mysql的班级的编号select class_id from classes where class_name='mysql';-- b.查询此班级编号的学生信息select * from students where cid=2

select * from students where cid=(select class_id from classes where class_name='mysql');

-- a查询所有Java班的班级编号select class_id from classes where class_name like 'Java%';-- b查询这些班级编号中的学生信息(union 将多个查询语句的结果整合到一起)select * from students where cid = 1unionselect * from students where cid = 2unionselect * from students where cid = 3

-- 如果查询返回的结果是多个值(单列多行),条件使用**in / not in**select * from students where cid in (select class_id from classes where class_name like 'mysql%');

-- 传统的多条件查询select * from students where cid=1 and stu_gender='男';-- 子查询(先查询cid=1班级中的所有信息,将这些信息作为一个整体虚拟表 在基于这个虚拟表查询性别为男的学生信息)select * from (select * from students where cid=1) t where t.stu_gender='男';

create procedure <procedure_name>([IN/OUT args])begin	-- SQLend;-- 示例create procedure prod_test(in a int,in b int,out c int)begin	set c = a+b;end;

create procedure proc_test1(in a int,in b int,out c int)begin	set c = a+b;end;-- 调用存储过程set @m;call proc_test1(3,200,@m);-- 显示变量表select @m from dual; -- dual表是数据库自带的用来存储标量的表

-- 局部变量要定义在存储过程中,而且必须定义存储过程开始declare   [default value];-- 示例-- 创建一个存储过程:计算输入参数的平方 与输入参数/2 之和create procedure proc_test3(in a int,out r int)begin		declare x int default 0;		declare y int default 1;		set x = a/2;		set y =a/2;		set r = x+y;end;

create procedure proc_test4(out c int)begin	select count(stu_name) into c from students;-- 查询到学生数量赋值给cend;CALL proc_test4(@ddj);SELECT @ddj from dual;

-- 创建存储过程:添加学生信息create procedure proc_test8(IN snum char(8) ,IN sname varchar(30) , IN gender varchar(3) ,IN age int , IN cid int)begin		insert into students (stu_num, stu_name, stu_gender,stu_age, cid)		values(snum,sname,gender,age,cid);end;call proc_test8('1324518','二宝','女',20,1);

-- 创建存储过程 根据学生学号, 查询学生姓名create procedure proc_test10(IN snum varchar(8),OUT sname varchar(30))begin	select stu_name into sname from students where stu_num = snum;end;set @name='';call proc_test10('11111211',@name);select @name from dual;

create procedure proc_test11(INOUT str varchar(30))begin		select stu_name into str from students where stu_num=str;end;set @name = '11111211';call proc_test11(@name);SELECT @name from dual;

create procedure proc_test17(in num int)begin	declare i int default 0;	while i<num do        -- SQL        insert into classes (class_name) values (concat('java',i));        set i=i+1;	end while;end;call proc_test17(10);

create procedure proc_test18(in num int)begin	declare i int default 0;	repeat 		insert into classes (class_name) values(concat('python',i));		set i = i+1;	until  i >= num end repeat;end;call proc_test18(5);

create procedure proc_test20(in num int)begin	declare i int default 1;	myloop : loop		-- SQL		insert into classes(class_name) values(concat('html',i));		set i = i+1;		if i>=num then			leave myloop;		end if;	end loop;end;call proc_test20(5);

-- 根据数据库名,查询当前数据库的存储过程show procedure status where db='dp_test2';-- 查询存储过程的创建细节show create procedure dp_test2.proc_test1; 

alter procedure <procedureName>特征1 [特征2] [特征3]

-- 删除存储过程-- drop 删除数据库的对象 ,数据库 数据表 存储过程 视图 触发器 索引drop procedure proc_test1;

-- 创建数据库create database db_test3 character set utf8 ;use db_test3;-- 创建图书信息表create table books(    book_id int primary key auto_increment,    book_name varchar(50) not null,    book_author varchar(20) not null,    book_price decimal(10,2) not null,    book_stock int not null,    book_decs varchar(200));-- 添加图书信息insert into books(book_name,book_author,book_price,book_stock,book_decs)values('Java程序设计','亮亮',38.80,12,'亮亮老师带你学Java');insert into books(book_name,book_author,book_price,book_stock,book_decs)values('Java王者之路','威哥',44.40,9,'千峰威哥,Java王者领路人');-- 创建学生表create table students(    stu_num char(8) primary key,    stu_name varchar(20) not null,    stu_gender char(2) not null,    stu_age int not null);-- 添加学生信息insert into students(stu_num,stu_name,stu_gender,stu_age)values('1001','张三','男',20);insert into students(stu_num,stu_name,stu_gender,stu_age)values('1002','李四','女',20);insert into students(stu_num,stu_name,stu_gender,stu_age)values('1001','王五','男',20);

-- 借书记录表create table records(	rid int primary key auto_increment,	snum char(8) not null,	bid int not null,	borrow_num int not null,	is_return int not null,-- 0 表示未归还 1 表示已经归还	borrow_date date not null,	constraint FK_RECORDS_STUDENTS foreign key(snum) references students(stu_num),	constraint FK_RECORDS_BOOKS foreign key(bid) references books(book_id));

-- 实现借书业务:-- 参数1:a输入参数 学号-- 参数2:b输入参数 图书编号-- 参数3:m输入参数 借书的数量-- 参数4:state输入参数 借书的状态(1 借书成功,2学生学号不存在,3 图书不存在 4库存不足)create procedure proc_borrow_book(IN a char(8),in b int,in m int,out state int)begin	declare stu_count int;	declare book_count int;	declare stock int default 0 ;	-- 判断学号是否存在: 根据参数 a  到学生信息表查询是否存在stu_num = a的记录	select count(stu_num)into stu_count from students where stu_num=a;	if stu_count>0 then        -- 学号存在        -- 判断图书ID是否存在:根据参数b 查询图书记录总数        select count(book_id) into book_count from books where book_id = b;        if book_count>0 then        	-- 图书存在        	-- 检查图书库存是否够用: 查询图书库存,然后和参数m进行比较        	select book_stock into stock from books where book_id = b;        	if stock >= m then        		-- 库存足够 可以借出        		set state = 1;        		-- 执行借书        		--  *** 作1:在借书记录表中添加记录        		insert into records(snum,bid,borrow_num,is_return,borrow_date)values(a,b,m,0,sysdate());        		--  *** 作2:        		update books set book_stock = stock-m where book_id=b;        	else         		-- 库存不足        		set state = 4;        	end if;        else         	-- 图书不存在        	set state = 3;        end if;	else 	-- 学号不存在		set state = 2;	end if;end;

declare cursor_name cursor for slect_statement;

declare mycursor cursor for select book_name,book_author,book_price from books;

open mycursor;

fetch mycursor into bname,bauthor,bprice;

close mycursor;

create procedure proc_test2(out result varchar(200))begin	declare bname varchar(20);	declare bauthor varchar(20);	declare bprice decimal(10,2);	declare num int;	declare i int;	declare str varchar(50);	-- 此查询语句执行之后返回的结果是一个结果集(多条记录)	declare mycursor cursor for select book_name,book_author,book_price from books;	select count(book_id) into num from books;	-- 打开游标	open mycursor;		-- 使用游标要结合循环语句	set i =0;	while i

create trigger tri_name 
<before|after> 			-- 定义触发时机
<insert|delete|update>  -- 定义DML类型
ON <table_name> 
for each row            -- 声明为杭集触发器(只要 *** 作一条记录 触发器就执行一次)
SQL                     -- 触发器 *** 作

-- 创建触发器:当学生信息表添加 *** 作时,则向日志信息表中记录一条rizhi
create trigger tri_test1
after insert on students
for each row 
insert into stulogs(time,log_text)values(now(),concat('添加',NEW.stu_num,'学生信息'));

show triggers;

drop trigger tri_test1;

create trigger tri_test1
after insert on students
for each row 
insert into stulogs(time,log_text)values(now(),concat('添加',NEW.stu_num,'学生信息'));

create trigger tri_test2
after update on students 
for each ROW 
INSERT into stulogs (time,log_text) values(now(),concat('修改学生信息为:',NEW.stu_num,NEW.stu_name));

UPDATE students set stu_name='李10'where stu_num ='1007';

-- 当从students表中删除学生信息时,同时要在stulogs中添加一条日志
create trigger tri_test3
after delete on students
for each ROW 
insert into stulogs (time,log_text)values(now(),concat('删除',OLD.stu_num,'学生信息'));

delete from students  where stu_num ='1007';

 create trigger tri_test5after update on students for each ROW insert into stulogs(time,log_text) VALUES (now(),concat('将学生姓名从[',OLD.stu_name,']','修改为','[',new.stu_name,']'));update students set stu_name='哈哈' where stu_num='1005';

create view <view_name>
as
select_statement

-- 创建视图示例1:将学生表中性别为男的学生生成一个视图
create view view_test1
as
select * from students where stu_gender='男';
-- 查询视图
select * from view_test1;

-- 创建视图示例2:查询学生借书的信息(学生名,图书名,借书数量)
create view view_test2
as 
select s.stu_name,b.book_name,r.borrow_num
from books b inner join records r inner join students s 
on b.book_id=r.bid and r.snum=s.stu_num ;
select * from view_test2 where stu_name !='张三';     

-- 查询视图结构

-- 方式1create or replace view view_test1as select * from students where stu_gender='女';-- 方式2alter view view_test1as select * from students where stu_gender='男';

drop view view_test1;

-- 创建唯一索引:创建唯一索引的列的值不能重复-- create unique index  on 表命(列名);create unique index index_test1 on tb_testindex(tid);

-- 创建普通索引-- create index  on 表名(列命);create index index_text2 on tb_testindex(name);

-- 创建组合索引-- create index  on 表明(列命1,列命2,列命3);create index index_test3 on tb_testindex(tid,name);

create fulltext index <index_name> on 表名(字段名);

explain select * from tb_testindex where tid=250000\G;

-- 命令行show create table tb_testindex\G;-- 查询数据表的索引show indexes from tb_testindex;-- 查询索引show keys from tb_testindex;

-- 删除索引: 索引是建立在表字段上的,不同的表中可能出现相同名称的索引-- 因此删除索引时需要指定表名drop index index_test3 on tb_testindex;

-- 开启事务start transaction;-- 借书业务--  *** 作1:在借书记录表中添加记录insert into records (snum,bid,borrow_num,is_return,borrow_date) values('1011',4,2,0,sysdate());SELECT  aaa;-- 事务回滚(清除连接缓存中的 *** 作,撤销当前事务已经执行的 *** 作)rollback;--  *** 作2:修改图书库存update books set book_stock=book_stock-2 where book_id=4;-- 提交事务(将连接缓存中的 *** 作写入数据文件)commit;

-- 在MySQL8.0.3之前select @@tx_isolation;-- 在MySQL8.0.3之后select @@transaction_isolation;

set session transaction isolation level <repeatable read>;

	1. 数据实体:       + 学生       + 图书       + 类别       + 借书记录       + 管理员	2. 提取数据项     + 学生(学号,姓名,性别,年龄,院系编号,院系名称,院系说明..)     + 图书(类别ID,类别名称,类别描述)     + 类别(图书ID,图书名称,图书作者,图书封面,图书价格,图书库存...)     + 借书记录(记录ID,学号,图书编号,数量,是否归还,还书日期)     + 管理员(管理员ID,登录名,登录密码,员工编号,员工姓名,员工编号)     + 员工(员工编号,员工姓名,手机,QQ,邮箱)