MYSQL实用教程（第二版）电子工业出版社

MYSQL代码（基本比较全）		
		数据库建表

第一章 绪论
show variables like’char%’;//改中文字符
set character_set_database=‘gbk’;
create table xs (
学号 char(6) not null primary key,
姓名 char(8) not null,
专业名 char(10) null,
性别 tinyint(1) not null,
出生日期 date
not null,
总学分 tinyint(1) null,
照片 blob null,
备注 text null
)

create table KC (
课程号 char(3) not null,
课程名 char(16) not null,
开课学期 tinyint(1) not null default 1,
学时 tinyint(1) not null,
学分 tinyint(1) null,
primary key(课程号)
)

create table XS_KC(
学号 char(6) not null,
课程号 char(3) not null,
成绩 tinyint(0) null,
学分 tinyint(1) null,
primary key (学号,课程号)
)
第三章
1.delete from table_name where 学号='00000’or 学号=‘00001’;//删除一个人的数据，and 有两个主键;
2.insert into xs//插入多条数据
values(‘091102’,‘程明’,‘计算机’,1,‘1991-02-01’,50,null,null),
(‘091103’,‘王燕’,‘计算机’,0,‘1989-10-06’,50,null,null)
3.alter table table-name
add 姓名 char(8) not null after 学号，
drop column；//插入删除一列数据
4.alter table table_name change a b integer//把列名a变为b
5.alter table table_name modify b bigint not null//改变列的数据类型
6.alter table a rename to b //把表名a改为b
7.rename table user1 to user2//把数据库user1重命名为user2
8 drop table if exists user1 //删除表user1
9.drop database if exits database_name;//删除数据库
10.update xs
set 总学分=总学分+10,备注=‘拂袖’;
where 姓名=‘张三’;
11.truncate table table-name//清除指定表的所有数据
12.desc xs 学号//看列信息
13.source 路径 文件；//文件导入
mysqldump -u dbuser -p dbname users> dbname_users.sql

14mysqldump -u root -p dbname > dbname.sql//导出整个数据库
mysqldump -u dbuser -p dbname users> dbname_users.sql//导出一个表

第四章
1.select 学号 as number, 姓名 as name from xs where 专业名=‘计算机’;
2.select 学号,姓名,
case
when 总学分 is null then ‘尚未选课’
when 总学分 <50 then ‘不及格’
when 总学分 >=50 and 总学分 <=52 then ‘合格’
else ‘优秀’
end as 等级
from xs
where 专业名=‘计算机’;
3.select 学号,课程号,成绩1.20 as 成绩120
from xs_kc //计算列值
where 学号=‘081101’;
4. select distinct 专业名,总学分 from xs;//消除重复行
5.聚合函数：
a. select count() as '学生总数’from xs; //求学生的总人数
b. select count(备注) as ‘备注不为空的学生数目’from xs;
c. select count(总学分) as’总学分50以上的人数’
from xs
where 总学分>50
d.select max(成绩),min(成绩)
from xs_kc
where 课程号=‘101’;
e.select sum(成绩) as ‘课程总成绩’
from xs_kc
where 学号=‘081101’;
f.select avg(成绩) as ‘课程101平均成绩’
from xs_kc
where 课程号 = ‘101’;
g.select variance(成绩)
from xs_kc
where 课程号 = ‘101’;//方差
h.select stddev(成绩)
from xs_kc
where 课程号 = ‘101’; //标准差
i.select group_concat(学号)
from xs_kc
where 课程号 = ‘206’;//求选修206课程的学生的学号
j. select bin(bit_or(bin_value)) from bits//或运算

6.多表连接
(1) 全连接
select distinct kc.课程名,xs_kc.课程号
from kc,xs_kc
where kc.课程号=xs_kc.课程号
(2)JOIN连接
a.内链接
select distinct 课程名,xs_kc.课程号
from kc inner join xs_kc
on (kc.课程号=xs_kc.课程号)

select 姓名,成绩
from xs join xs_kc on xs.学号=xs_kc.学号
where 课程号 = ‘206’ and 成绩>=80 //查找选修了206课程且成绩在80分以上的学生姓名及成绩

select xs.学号,姓名,成绩,课程名
from xs join xs_kc on xs.学号=xs_kc.学号
join kc on xs_kc.课程号=kc.课程号
where 课程名 = ‘计算机基础’ and 成绩>=80//查选修了“计算机基础”课程且成绩在80分以上的

select a.学号,a.课程号,b.课程号,a.成绩
from xs_kc as a join xs_kc as b
on a.成绩=b.成绩 and a.学号=b.学号 and a.课程号!=b.课程号//查找数据库中课程不同，成绩相同的学生的。。。。。

select 课程名
from kc inner join xs_kc
using (课程号); //查询kc表中所有学生选过的课程名
b.外连接
select xs.* , 课程号 //查找所有学生情况及他们选修的课程号，未选课也要包括
from xs left outer join xs_kc on xs.学号=xs_kc.学号

select xs_kc.* ,课程名 //查找被选修了的课程的选修情况和所有开设的课程名
from xs_kc right join kc on xs_kc.课程号=kc.课程号

select 课程名,课程号 from kc
where 课程号 in
( select distinct 课程号 from kc natural right outer join xs_kc);

c.交叉连接
select 学号,姓名,课程号,课程名
from xs cross join kc;
select distinct 课程名,xs_kc.课程号
from kc straight_join xs_kc
on (kc.课程号=xs_kc.课程号)
7.select 姓名,学号,性别,总学分
from xs
where 专业名=‘计算机’ and 备注<=>null

8.select 学号,姓名,性别
from xs
where 姓名 like ‘王%’;//查询姓王的学生的。。

select 学号,姓名,专业名
from xs
where 学号 like ‘%0_’;// 查询学号倒数第二个数字为0的学生的。。。

select 学号,姓名
from xs
where 学号 like ‘%#_%’ escape ‘#’;//查询名字包含下划线的学生的。。。

9.REGEXP运算符
select 学号,姓名,专业名
from xs
where 姓名 regexp ‘^李’;//查询姓李的同学的。。。

select 学号,姓名,专业名
from xs
where 学号 regexp ‘[4,5,6]’;//查询学号里包含4，5，6的学生的。。。

select 学号,姓名,专业名
from xs
where 学号 regexp ‘^08.*08$’;//查询学号以08开头，以08结尾的学生的。。。。

10.select 学号,姓名,专业名,出生日期
from xs
where 出生日期 not between ‘1993-1-1’ and ‘1993-12-31’;
//查询xs表中不在1993年出生的学生情况
11.select * from xs where 专业名 in(‘计算机’,‘通信工程’);
12.select * from xs where 总学分 is null;//查找总学分为空的学生
13.IN查询
select 姓名,学号
from xs
where 学号 in
( select 学号
from xs_kc
where 课程号 = ‘206’
);//查找选修了课程号为206的课程的学生的。。。

select 姓名,学号,专业名
from xs
where 学号 not in
( select 学号
from xs_kc
where 课程号 in
(
select 课程号
from kc
where 课程名=‘离散数学’
)
);//查找未选修离散数学的学生的。。。。
14.比较子查询
select 学号
from xs_kc
where 课程号=
(
select 课程号
from kc
where 课程名=‘离散数学’
)；//查询选修了离散数学的学生学号

select 学号,姓名,专业名,出生日期
from xs
where 出生日期<all
(
select 出生日期
from xs
where 专业名=‘计算机’
)//查询xs表中比所有计算机系的学生年龄都大的学生。。。

select 学号
from xs_kc
where 课程号=‘206’ and 成绩>=any
(
select 成绩
from xs_kc
where 课程号=‘101’
);//查找课程号206的成绩不低于课程号101的最低成绩的学生的。。。

15.Exists子查询
select 姓名
from xs
where exists
(
select *
from xs_kc
where 学号=xs.学号 and 课程号=‘206’
);//查找选修206号课程的学生姓名

select 姓名
from xs
where not exists
(
select *
from kc
where not exists
(
select *
from xs_kc
where 学号=xs.学号 and 课程号=kc.课程号
)
);//查询选修了全部课程的同学的姓名

select 姓名,学号,总学分
from(
select 姓名,学号,性别,总学分
from xs
where 总学分>50
)as student
where 性别=‘1’;//查找总学分大于50分的男同学的。。。

select 学号,姓名,year(出生日期)-year (
( select 出生日期
from xs
where 学号=‘081101’
))as 年龄差距
from xs
where 性别=‘0’;//查询所有女学生de学号姓名以及与081101号学生的年龄差距

select 学号,姓名
from xs
where(性别,总学分)=(select 性别,总学分
from xs
where 学号=‘081101’);//查找与081101号学生性别相同，总学分相同的学生的。。。
16.GROUP BY:
select 专业名
from xs
group by 专业名;//各专业名输出

select 专业名,count(*) as ‘学生数’
from xs
group by 专业名;//各专业的学生数

select 课程号,avg(成绩) as’平均成绩’,count(学号) as’选修人数’
from xs_kc
group by 课程号;//被选修的各门课程的平均成绩和选修该课程的人数

select 专业名,性别,count(*) as ‘人数’
from xs
group by 专业名,性别
with rollup;//每个专业的男生人数、女生人数、总人数，以及学生总人数
select 课程名,专业名,avg(成绩) as ‘平均成绩’
from xs_kc,xs,kc
where xs_kc.课程号=kc.课程号 and xs_kc.学号=xs.学号
group by 课程名,专业名
with rollup;//每门课程各专业的平均成绩，每门课程的总平均成绩和所有课程的总平均成绩

17.HAVING子句
select 学号,avg(成绩) as’平均成绩’
from xs_kc
group by 学号
having avg(成绩)>=85;//平均成绩在85分以上的学生的。。。

select 学号
from xs_kc
where 成绩 >=80
group by 学号
having count(*)>2;//查找选修课程超过2门且成绩都在80分以上的学生。。

select 学号,avg(成绩) as’平均成绩’
from xs_kc
where 学号 in
(
select 学号
from xs
where 专业名=‘通信工程’
)
group by 学号
having avg(成绩)>=85;//查找通信工程专业平均成绩在85分以上的学生的。。
18.ORDER BY子句
select 学号,姓名,专业名,出生日期
from xs
where 专业名=‘通信工程’
order by 出生日期;//按出生日期先后排序

select 姓名,课程名,成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号
and xs_kc.课程号=kc.课程号
and 课程名=‘计算机基础’
and 专业名=‘计算机’
order by 成绩 desc;//把计算机专业学生的“计算机基础”课程成绩按降序排列

select 学号,姓名,专业名
from xs
where 专业名=‘计算机’
order by(
select avg(成绩)
from xs_kc
group by xs_kc.学号
having xs.学号=xs_kc.学号
);//按平均成绩排序

19.LIMIT子句
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
order by 学号
limit 5;//查找学号最靠前的5位学生的信息

select 学号,姓名,专业名,性别,出生日期,总学分
from xs
order by 学号
limit 3,5;//查从第4位同学开始的5位学生的信息

20.UNION 语句
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号=‘081101’
union
select 学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号=‘081210’

21.HANDLER 语句
handler kc open;

handler kc read first
where 学分>4;

handler kc read next;

handler kc close;

MYSQL视图
22.创建视图
create or replace view test.cs_kc
as
select xs.学号,课程号,成绩
from test.xs,test.xs_kc
where xs.学号=xs_kc.学号 and xs.专业名=‘计算机’
with check option;//创建test数据库上的cs_kc视图，包括计算机专业各学生的学号、其选修的课程号及成绩

use test
create view cs_kc_avg(num,score_avg)
as
select 学号,avg(成绩)
from cs_kc
group by 学号;//创建test数据库上的计算机专业学生的平均成绩视图cs_kc_avg
23.查询视图
select 学号,课程号
from cs_kc;
create view xs_kc_avg(num,score_avg)
as
select 学号,avg(成绩)
from xs_kc
group by 学号;
select * from xs_kc_avg
where score_avg>=80;
24.更新视图
create or replace view cs_xs
as
select *
from xs
where 专业名=‘计算机’
with check option;
insert into cs_xs values(‘081255’,‘李牧’,‘计算机’,1,‘1994-10-14’,50,null,null);

update cs_xs
set 总学分=总学分+8;

update cs_kc
set 成绩=90
where 学号='081101’and 课程号=‘101’//若一个视图依赖于多个基本表，一次只能修改一个基本表的数据

delete from cs_xs where 学号=‘081255’;

25.修改视图
alter view cs_kc
as
select 学号,姓名,总学分
from xs
where 专业名=‘计算机’;//修改为只包含计算机专业的学生的。。。
26.删除视图
drop view cs_xs;

第五章 MYSQL索引与完整性约束

1. CREATE INDEX 语句创建
   create index xh_xs on xs(学号(5) asc);
   create index xskc_in on xs_kc(学号,课程号);
   2.ALTER TABLE 语句创建
   alter table xs
   add index xs_xm using btree(姓名);//在xs表的姓名列上创建一个非唯一的索引
   alter table xs
   add index mark(出生日期,性别);
   show index from xs;// 查看索引
   3.在建立表时创建索引
   create table xs_kc
   (
   学号 char(6) not null,
   课程号 char(3) not null,
   成绩 tinyint(1),
   学分 tinyint(1),
   primary key(学号,课程号),
   index cj(成绩)
   )
   4.删除索引
   drop index index_name on table_name;
   alter table xs drop index mark;
   5.索引的利弊 加快查询速度，占据磁盘空间 利大于弊
   6.主键约束
   create table xs1
   (
   学号 varchar(6) null,
   姓名 varchar(8) not null primary key,
   出生日期 datetime
   );

create table course
(
学号 varchar(6) not null,
姓名 varchar(8) not null,
毕业日期 date not null,
课程号 varchar(3),
学分 tinyint,
primary key(课程号,学号,毕业日期)
);
create table course
(
学号 varchar(6) not null,
姓名 varchar(8) not null,
毕业日期 date not null,
课程号 varchar(3),
学分 tinyint,
primary key index_course(课程号,学号,毕业日期)
);//给索引赋名称
7.替代键约束
create table xs1
(
学号 varchar(6) not null,
姓名 varchar(8) null unique,
出生日期 datetime null,
primary key(学号)
);
//书上有错误
create table xs1
(
学号 varchar(6) not null,
姓名 varchar(8) not null,
出生日期 datetime null,
primary key(学号),
unique(姓名)
);
8.参照完整性约束
create table xs1
(
学号 varchar(6) null,
姓名 varchar(8) not null,
出生日期 datetime null,
primary key(姓名),
foreign key(学号)
references xs(学号)
on delete restrict
on update restrict
);

select * from xs1
where 学号 not in
(
select 学号 from xs
);

create table xs2
(
学号 varchar(6) not null,
姓名 varchar(8) not null,
出生日期 datetime null,
班长 varchar(6) not null,
primary key(学号),
foreign key(班长) references xs2(学号)
);//自参照表

create table xs1
(
学号 varchar(6) not null,
姓名 varchar(8) not null,
出生日期 datetime null,
primary key(学号),
foreign key(学号)
references xs(学号)
on update cascade
);//主表更新删除子表跟着

9.CHECK完整性约束
create table student
(
学号 char(6) not null,
性别 char(1) not null
check(性别 in (‘男’,‘女’))
);

create table student1
(
学号 char(6) not null,
出生日期 date not null
check(出生日期>‘1990-01-01’)
);

create table student2
(
学号 char(6) not null,
性别 char(1) not null
check(性别 in
(select 性别 from student))
);

create table student3
(
学号 char(6) not null,
最好成绩 int(1) not null,
平均成绩 int(1) not null,
check(最好成绩>平均成绩)
);
10.命名完整性约束
create table xs1
(
学号 varchar(6) null,
姓名 varchar(8) not null,
出生日期 datetime null,
constraint primary_key_xs1
primary key(姓名)
);
11.删除完整性约束
alter table xs1 drop primary key;
alter table table_name add constraint primary key();//增加主键
第六章MYSQL语言结构

第七章 MYSQL5过程式数据库对象

1.delimiter // 将MYSQL结束符修改为两个斜杠
2.创建储存过程
delimiter $$createproceduredelet{e}_{s}tudent(inxhchar(6))begindeletefromxswhere学号=xh;end$$
delimiter;
3.储存过程体
a.局部变量
declare num int(4);
declare str1,str2 varchar(6);
局部变量只能在begin。。。end语句块中声明

b.使用set语句赋值
set num=1,str1=‘hello’;

c.select…into语句
select 姓名，专业名 into name,project
from xs
where 学号=‘081101’;

4.流程控制语句
（1）if语句
delimiter $$createproceduretest.compar1(ink1integer,ink2integer,outk3char(6))beginifk1>k2thensetk3{=}^{\prime }大{于}^{\prime };elseifk1=k2thensetk3{=}^{\prime }等{于}^{\prime };elsesetk3{=}^{\prime }小{于}^{\prime };endif;end$$
delimiter ;

(2)case语句
delimiter $$createproceduretest.result(instrvarchar(4),outsexvarchar(4))begincasestrwhe{n}^{\prime }{m}^{\prime }thensetsex{=}^{\prime }{男}^{\prime };whe{n}^{\prime }{f}^{\prime }thensetsex{=}^{\prime }{女}^{\prime };elsesetsex{=}^{\prime }{无}^{\prime };endcase;end$$
delimiter ;

case
when str=‘m’ then set sex=‘男’;
when str=‘f’ then set sex=‘女’;
else set sex=‘无’
end case;

（3）循环语句
delimiter $$createproceduredowhile()begindeclarev1intdefault5;whilev1>0dosetv1=v1-1;endwhile;end$$
delimiter ; //while 循环

delimiter $$createproceduredowhile()begindeclarev1intdefault0;repeatsetv1=v1-1;untilv1<1;endrepeatend$$
delimiter ; //repeat 循环

delimiter $$createproceduredoloop()beginset@a=10;label:loopset@a=@a-1;if@a<0thenleavelabel;endif;endlooplabel;end$$
delimiter ; //loop循环 允许某特定语句或语句群重复执行

call doloop();//调用存储过程
select @a; //查看用户变量的值
leave label //退出被标注的循环语句
iterate label //重新开始一次循环

5.处理程序和条件
use test;
delimiter $$createprocedurem{y}_{i}nsert1()begindeclarecontinuehandlerforsqlstat{e}^{\prime }23000^{\prime }set@x2=1;set@x=2;insertintoxsvalues{(}^{\prime }071101^{\prime }{,}^{\prime }王{民}^{\prime }{,}^{\prime }计算{机}^{\prime },1{,}^{\prime }1994-02-10^{\prime },50,null,null);set@x=3;end$$
delimiter ;

6.游标
delimiter $$createprocedurecompute(outnumberinteger)begindeclarexhchar(6);declarefoundbooleandefaulttrue;declarenumbe{r}_{x}scursorforselect学号fromxs;declarecontinuehandlerfornotfoundsetfound=false;setnumber=0;opennumbe{r}_{x}s;fetchnumbe{r}_{x}sintoxh;whilefounddosetnumber=number+1;fetchnumbe{r}_{x}sintoxh;endwhile;closenumbe{r}_{x}s;end$$
delimiter ;

7.13存储过程的调用、删除和修改
7.存储过程的调用
delimiter $$createproceduretest.d{o}_{u}pdate(intxhchar(6),intkcmchar(16))begindeclarekchchar(3);declarexftinyint;declarecjtinyint;select课程号,学分intokch,xffromkcwhere课程名=kcm;ifcj<60thenupdatex{s}_{k}cset学分=0where学号=xhand课程号=kch;elseupdatex{s}_{k}cset学分=xfwhere学号=xhand课程号=kch;endif;end$$
delimiter ;

insert into xs_kc values(‘081101’,‘208’,50,10);

call do_update(‘081101’,‘数据结构’);
select *from xs_kc where 学号=‘081101’ and 课程号=‘208’;
//当某学生某门课程的成绩小于60分时将其学分修改为零，大于等于60分时将学分修改为此课程的学分

create procedure test.do_insert1()
insert into xs values(‘091101’,‘陶伟’,‘软件工程’,1,‘1994-03-05’,50,null,null);

delimiter $$createproceduretest.d{o}_{i}nsert2(inxbit(1),outstrchar(8))begincalld{o}_{i}nsert1();ifx=0thenupdatexsset姓名{=}^{\prime }刘{英}^{\prime },性别=0where学号{=}^{\prime }091101^{\prime };setstr{=}^{\prime }修改成{功}^{\prime };elseifx=1thendeletefromxswhere学号{=}^{\prime }091101^{\prime };setstr{=}^{\prime }删除成{功}^{\prime };endif;end$$
delimiter ;

call do_insert2(1,@str);
select @str;

call do_inisert2(0,@str);
select @str;

8.储存过程的删除
drop procedure if exists dowhile;

9.存储过程的修改

delimiter $$dropprocedureifexistsd{o}_{q}uery;createprocedured{o}_{q}uery()beginselect\ast fromxs;end$$
delimiter ;

7.2存储函数
7.2.1 创建存储函数

SET GLOBAL log_bin_trust_function_creators = 1;//8.0版本要先加这句话
10.返回xs表中学生的数目作为结果
delimiter $$createfunctionnu{m}_o{f}_{x}s()returnsintbeginreturn(selectcount(\ast )fromxs);end$$
delimiter ;

11.返回某个同学的姓名
delimiter $$createfunctionnam{e}_o{f}_{s}tu(xhchar(6))returnschar(8)beginreturn(select姓名fromxswhere学号=xh);end$$
delimiter ;

12.删除xs_kc表中有，但xs表中不存在的学号
delimiter $$createfunctiondelet{e}_{s}tu(xhchar(6))returnsbooleanbegindeclarestuchar(6);select姓名intostufromxswhere学号=xh;ifstuisnullthendeletefromx{s}_{k}cwhere学号=xh;returntrue;elsereturnfalse;endif;end$$
delimiter ;

7.2.2存储函数的调用、删除和修改
13.select num_of_xs();
select name_of_stu(‘081106’);

delimiter $$createfunctioni{s}_{s}tu(xhchar(6))returnschar(10)begindeclarenamechar(8);selectnam{e}_o{f}_{s}tu(xh)intoname;ifname{=}^{\prime }王{林}^{\prime }thenreturn(select出生日期fromxswhere学号=xh);elseretur{n}^{\prime }fals{e}^{\prime };endif;end$$
delimiter ;

select is_stu(‘081102’);
select is_stu(‘081101’);

drop function if exists num_of_xs;

7.3 触发器
14.在表上创建一个触发器，每次插入操作时，将用户变量str的值设为“trigger is working”
create table tablel(a integer);
create trigger tablel_insert after insert
on tablel for each row
set @str=‘trigger is working’;

insert into tablel values(10);

select @str;

15.当删除表xs中某个学生的信息时，同时将xs_kc表中与该学生有关的数据全部删除
delimiter $$createtriggerx{s}_{d}eleteafterdeleteonxsforeachrowbegindeletefromx{s}_{k}cwhere学号=old.学号;end$$
delimiter ;

delete from xs where 学号=‘081101’;
select * from xs_kc;

16.当修改xs_kc表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分
delimiter $$createtriggerx{s}_k{c}_{u}pdatebeforeupdateonx{s}_{k}cforeachrowbegindeclarexfint(1);select学分intoxffromkcwhere课程号=new.课程号;ifnew.成绩<60thensetnew.学分=0;elsesetnew.学分=xf;endif;end$$
delimiter ;

17.当向xs_kc表中插入一行数据时，根据成绩对xs表的总学分进行修改。如果成绩>=60,总学分加上该课程的学分，否则总学分不变
delimiter $$createtriggerx{s}_k{c}_{z}xfafterinsertonx{s}_{k}cforeachrowbegindeclarexsint(1);select学分intoxsfromkcwhere课程号=new.课程号;ifnew.成绩>=60thenupdatexsset总学分=总学分+xfwhere学号=new.学号;endif;end$$
delimiter ;

18.student 表中的数据与xs表同步
a.定义储存过程
delimiter $$createprocedurechanges()beginreplaceintostudentselect\ast fromxs;end$$
delimiter ;

b.创建触发器
create trigger student_change after insert
on xs for each row
call changes();

c.验证
insert into xs values
(‘091102’,‘王大庆’,‘计算机’,1,‘1994-08-14’,48,null,null);
select * from student;

19.删除触发器
drop trigger xs_delete;

7.4事件
7.4.1 创建事件
20.创建一个立即启动的事件
create event direct
on schedule at now()
do insert into xs values
(‘091103’,‘张建’,‘软件工程’,1,‘1994-06-05’,50,null,null)

21.创建一个30秒后启动的事件
create event thrityseconds
on schedule at now()+interval 30 second
do insert into xs values
(‘091104’,‘陈建’,‘软件工程’,1,‘1994-08-16’,50,null,null)

22.每个月启动一次，开始于下个月并且在2018年的12月31号结束
delimiter $$createeventstartmonthonscheduleevery1monthstartscurdate()+interval1monthend{s}^{\prime }2019-12-31^{\prime }dobeginifyear(curdate())<2018theninsertintoxsvalues{(}^{\prime }091105^{\prime }{,}^{\prime }王{建}^{\prime }{,}^{\prime }软件工{程}^{\prime },1{,}^{\prime }1994-03-16^{\prime },48,null,null);endif;end$$
delimiter ;

7.4.2 修改和删除事件
23.修改事件名字
alter event startmonth rename to firstmonth;
24.查看修改结果
show events;
25.删除事件
drop event event_name;

第八章 MYSQL数据库备份与恢复

8.3日志文件
8.3.1启用日志
1.log-bin=E;/Program Files/MYSQL/MYSQL Server 8.0/bin/bin_log;

2.net stop mysql
3.net start mysql

8.3.2用mysqlbinlog处理日志
4.查看日志内容
mysqlbinlog bin_log.000001
5.将日志保存到文本文件中
mysqlbinlog bin_log.000001>路径
6.使用日志恢复数据
p194 书上
7.清除所有日志
reset master
第九章 MYSQL安全管理

9.1用户管理
1.添加两个用户
create user
‘king’@'localhost’identified by ‘queen’,
‘palo’@'localhost’identified by ‘530415’;
use mysql
show tables;
select * from user;

2.删除用户
drop user palo@localhost;

3.修改用户名
rename user
‘king’@‘localhost’ to ‘ken’@‘localhost’;
4.修改密码
set password for ‘ken’@‘localhost’ = password(‘qen’);

9.2权限控制
9.2.1修改用户名密码
5.授予用户ken在xs表上的SELECT权限
grant select
on xs
to ken@localhost;

6.用户liu和zhang不存在，授予他们在xs表上的SELECT和UPDATE权限
grant select,update
on xs
to liu@localhost identified by ‘lpwd’,
zhang@localhost identified by ‘zpwd’;

7.授权ken在xs表上的学号列和姓名列的update权限
grant update(姓名,学号)
on xs
to ken@localhost;
8.授予ken在数据库中的所有表的SELECT权限
grant select on test,* to ken@localhost;
9.授予ken在数据库中所有的数据库权限
grant all
on *
to ken@localhost;

10.授予peter对所有数据库中的所有表的CREATE、ALTERT和DROP权限
grant create,alter,drop
on .
to peter@localhost identified by ‘ppwd’;

11.授予peter创建新用户的权利
grant create user
on .
to peter@localhost;

9.2.2权限转移和限制
12.在root用户下授予caddy用户SELECT权限
grant select
on test.xs
to caddy@localhost identified by ‘19830925’
with grant option;

mysql -hlocalhost -ucaddy -p19830925

grant select
on test.xs
to Jim@localhost;

13. 授予Jim每小时只能处理一条SELECT语句的权限
    grant select
    on xs
    to Jim@localhost
    with max_queries_per-hour 1;
    9.2.3权限回收
    14.回收用户caddy在xs表上的SELECT
    revoke select
    on xs
    from caddy@localhost;

9.3表维护语句
15.ANALYZE TABLE语句
//更新表xs的索引的可压缩性，并随后显示
analyze table xs;
show index from xs;

16.CHECK TABLE语句
//检查xs表是否正确
check table xs;

17.CHECKSUM TABLE语句
获得表xs的效验和的值
checksum table xs;

18.OPTIMIZE TABLE语句
//优化xs表
optimize table kc;

19.REPAIR TABLE语句
//如果一个表或索引已经损坏，可以使用REPAIR TABLE语句尝试去修复它
只对MyISAM和ARCHIVE表起作用

还有两个表维护语句：BACKUP TABLE和RESTOTRE TABLE语句，但是效果不理想，不推荐使用
CREATE TABLE XS
(sno char(6) not null primary key,
sname char(8) not null,
zuanyemin char(10) null,
ssex tinyint(1) not null default 1,
chushengriiqii date not null,
zongxuefen tinyint(1) null,
zhaopian blob null,
beizu text null
)engine = innodb;

CREATE TABLE KC
(kechenghao char(3) not null primary key,
kechengming char(16) not null,
kaikexueqi tinyint(1) not null default 1,
xueshi tinyint(1) not null,
xuefen tinyint(1) null
);

CREATE TABLE XC_KC
(sno char(6) not null ,
kechenghao char(3) not null ,
chengji tinyint(1) null,
xuefen tinyint(1) null,
primary key(sno,kechenghao)
);

use XSCJ

insert into XS
values(‘081101’,‘王林’,‘计算机’,1,‘1994-02-10’,50,null,null);
insert into XS
values(‘081102’,‘程明’,‘计算机’,1,‘1991-02-01’,50,null,null);
insert into XS
values(‘081103’,‘王燕’,‘计算机’,0,‘1989-10-06’,50,null,null);
insert into XS
values(‘081104’,‘韦严平’,‘计算机’,1,‘1990-08-26’,50,null,null);
insert into XS
values(‘081106’,‘李方方’,‘计算机’,1,‘1990-11-20’,50,null,null);
insert into XS
values(‘081107’,‘李明’,‘计算机’,1,‘1990-05-01’,54,null,‘提起修完《数据结构》，并获学分’);
insert into XS
values(‘081108’,‘林一凡’,‘计算机’,1,‘1989-08-05’,52,null,‘已提前修完一门课’);
insert into XS
values(‘081109’,‘张强民’,‘计算机’,1,‘1989-08-11’,50,null,null);
insert into XS
values(‘081110’,‘张蔚’,‘计算机’,0,‘1991-07-22’,50,null,‘三好生’);
insert into XS
values(‘081111’,‘赵琳’,‘计算机’,0,‘1990-03-18’,50,null,null);
insert into XS
values(‘081113’,‘严红’,‘计算机’,0,‘1989-08-11’,48,null,‘有一门课不及格，待补考’);
insert into XS
values(‘081201’,‘王敏’,‘通信工程’,1,‘1989-06-10’,42,null,null);
insert into XS
values(‘081202’,‘王林’,‘通信工程’,1,‘1989-01-29’,40,null,‘有一门课不及格，待补考’);
insert into XS
values(‘081204’,‘马琳琳’,‘通信工程’,0,‘1989-02-10’,42,null,null);
insert into xs
values(‘081206’,‘李计’,‘通信工程’,1,‘1989-09-20’,42,null,null);
insert into xs
values(‘081210’,‘李红庆’,‘通信工程’,1,‘1989-05-01’,44,null,‘已提前修完一门课，并获得学分’);
insert into xs
values(‘081216’,‘孙祥欣’,‘通信工程’,1,‘1989-03-09’,42,null,null);
insert into xs
values(‘081218’,‘孙研’,‘通信工程’,1,‘1990-10-09’,42,null,null);
insert into xs
values(‘081220’,‘吴薇华’,‘通信工程’,0,‘1990-03-18’,42,null,null);
insert into xs
values(‘081221’,‘刘燕敏’,‘通信工程’,0,‘1989-11-12’,42,null,null);
insert into xs
values(‘081241’,‘罗林琳’,‘通信工程’,0,‘1990-01-30’,50,null,‘转专业学习’);

//kc表
insert into kc
values(‘101’,‘计算基础’,1,80,5);
insert into kc
values(‘102’,‘程序设计语言’,2,68,4);
insert into kc
values(‘206’,‘离散数学’,4,68,4);
insert into kc
values(‘208’,‘数据结构’,5,68,4);
insert into kc
values(‘209’,‘操作系统’,6,68,4);
insert into kc
values(‘210’,‘计算机原理’,5,85,5);
insert into kc
values(‘212’,‘数据库原理’,7,68,4);
insert into kc
values(‘301’,‘计算机网络’,7,51,3);
insert into kc
values(‘302’,‘软件工程’,7,51,3);

//xs_kc表
insert into xs_kc
values(‘081101’,101,80,5);
insert into xs_kc
values(‘081101’,102,78,4);
insert into xs_kc
values(‘081101’,206,76,4);
insert into xs_kc
values(‘081103’,101,62,5);
insert into xs_kc
values(‘081103’,102,70,4);
insert into xs_kc
values(‘081103’,206,81,4);
insert into xs_kc
values(‘081104’,101,90,5);
insert into xs_kc
values(‘081104’,102,84,4);
insert into xs_kc
values(‘081104’,206,65,4);
insert into xs_kc
values(‘081102’,102,78,4);
insert into xs_kc
values(‘081102’,206,78,4);
insert into xs_kc
values(‘081106’,101,65,5);
insert into xs_kc
values(‘081106’,102,71,4);
insert into xs_kc
values(‘081106’,206,80,4);
insert into xs_kc
values(‘081107’,101,78,5);
insert into xs_kc
values(‘081107’,102,80,4);
insert into xs_kc
values(‘081107’,206,68,4);
insert into xs_kc
values(‘081108’,101,85,5);
insert into xs_kc
values(‘081108’,102,64,4);
insert into xs_kc
values(‘081108’,206,87,4);

insert into xs_kc
values(‘081109’,101,66,5);
insert into xs_kc
values(‘081109’,102,83,4);
insert into xs_kc
values(‘081109’,206,70,4);

insert into xs_kc
values(‘081110’,101,95,5);
insert into xs_kc
values(‘081110’,102,90,4);
insert into xs_kc
values(‘081110’,206,89,4);

insert into xs_kc
values(‘081111’,101,91,5);
insert into xs_kc
values(‘081111’,102,70,4);
insert into xs_kc
values(‘081111’,206,76,4);

insert into xs_kc
values(‘081113’,101,63,5);
insert into xs_kc
values(‘081113’,102,79,4);
insert into xs_kc
values(‘081113’,206,60,4);

insert into xs_kc
values(‘081201’,101,80,5);
insert into xs_kc
values(‘081202’,101,65,5);
insert into xs_kc
values(‘081203’,101,87,5);

insert into xs_kc
values(‘081204’,101,91,5);
insert into xs_kc
values(‘081210’,101,76,5);
insert into xs_kc
values(‘081216’,101,81,5);

insert into xs_kc
values(‘081218’,101,70,5);
insert into xs_kc
values(‘081220’,101,82,5);
insert into xs_kc
values(‘081221’,101,76,5);
insert into xs_kc
values(‘081241’,101,90,5);

函数不能创建 错误代码：1418
SET @@global.log_bin_trust_function_creators=‘ON’;
SHOW VARIABLES LIKE ‘log_bin_trust_function_creators’;