计算机二级MySQL基本操作题(二）更新表，创建视图，grant创建用户并授予权限，revoke撤销权限，添加外键

第六题

图书借阅管理数据库 db_book
图书借阅信息表 tb_book(bno, bname, btype,publisher, ldate, price, desc1)

1. 上调“MySQL程序设计”的价格至42元
   use db_book;
   update tb_book set price=“42”
   where bname=“MySQL程序设计”;

update更新表数据语法
update 表名 set 字段名=“值” where 条件表达式
order by 排序字段名
limit 限制查询行数；
没有where子句表示更新所有行。

2. 创建出版社为"高等教育出版社"的视图v_book
   create view v_book
   as select * from tb_book
   where publisher=“高等教育出版社”;

创建视图语法
create view 视图名
as select 字段名1, 字段名2 from 表名 where 条件表达式；

3. 查询图书类型为“计算机”的图书名称、出版社和价格
   select bname, publisher, price
   from tb_book
   where btype=“计算机”;

查询语法
select distinct 字段名1, 字段名2 from 表名 where 条件表达式
group by 分组字段名
having 条件表达式
order by 排序字段名
limit 限制查询行数；
distinct 表示去重查询

order by 排序和 limit 限制行数是mysql查询优化的重点，所有查询都必须指定limit，限制用户查询的范围（例如按时间1周，1个月，3个月等等），避免全表查询，避免查询行数过多。

4. 删除图书简介 desc1 字段
   alter table db_book.tb_book drop desc1;

删除表的字段语法
alter table 表名 drop 字段名；

5. 新建用户client，主机名localhost，并授予借阅表select（bno,bname）权限
   grant select(bno, bname) on db_book.tb_book
   to “client”@“localhost”;
   flush privileges;

创建新用户并授予权限语法
grant 操作命令(字段名1，字段名2) on 表名 to “用户名”@“主机名” i
ndentified by “密码” with grant option;
操作命令包含：select, insert, update ,delete等，但不包含rename
indentified by 表示设置密码
with grant option 表示传递权限
flush privileges 表示刷新权限表

修改用户密码语法
set password for “用户名”@“主机名”=password(“新密码”);

授予所有权限:
grant all privileges on *.* to “用户名”@“主机名”;
撤销所有权限:
revoke all privileges on *.* from “用户名”@“主机名”;
查看权限
show grants for “用户名”@“主机名”;

第七题

学生社团数据库 db_association
学生表 tb_student(sno, sname, smajor)
社团表 tb_group(gno, gname, gdate)
学生参加社团表 tb_gs(gno, sno, grole)

1. 在tb_student 添加字段ssex
   use db_association;
   alter table tb_student add ssex char(2) default “女”;

添加表新字段并设置默认值语法
alter table 表名 add 字段名 数据类型 default “默认值”;
修改表已有字段的默认值语法
alter table 表名 alter 字段名 set default “默认值”;

2. 删除“计算机”专业的学生参加社团的信息
   delete from tb_gs
   where sno in (select sno from tb_student where smajor=“计算机”);

删除数据的语法
delete from 表名 where 字段名 in (子句)
in 表示匹配子句查询返回的结果集合

3. 创建按专业参加学生社团人数的视图 v_number
   create view v_number(smajor, snumber)
   as select smajor, count(*) as snumber
   from tb_student s, tb_gs g
   where s.sno=g.sno
   group by smajor;

4. 在tb_gs表建立外键，属性sno参考学术表sno
   alter table tb_gs add constraint fk_tb_gs
   foreign key(sno) references tb_student(sno);

添加表外键语法
alter table 参照表名 add constraint 外键名
foreign key(字段名) references 被参照表名(主键字段名);

5. 创建新用户tmpuser，并设置密码，并授权
   grant select,insert on tb_group to “tmpuser”@“localhost”
   identified by “a1234”;
   flush privileges;

第八题

图书借阅管理数据库 db_book
图书分类表 tb_class(classNo, className)
字段含义：分类号、分类名；
图书借阅信息表 tb_book(bookNo, classNo, bookName, authorName,pubName,ISBN, price, pubDate,shopDate,shopNum)
字段含义：图书编号、分类号、图书名称、作者姓名、出版书名称、出版号、单价、出版时间、入时间时间、入库数量；
读者表 tb_reader(readerNo, readerName,sex,age,department)
字段含义：读者编号、读者姓名、性别、年龄、所在院系；
借阅表 tb_borrow(readerNo, bookNo, borrowDate, shouldDate, returnDate)
字段含义：读者编号、图书编号、借阅日期、应还日期、归还日期

1. 删除图书表的“入库时间”字段
   use db_book;
   alter table tb_book drop shopDate;

2. 将“王依然”读者的性别改为“女”
   update tb_reader set sex=“女”
   where readerName=“王依然”;

3. 查询“借阅日期”在2014-01-01以来的读者姓名、图书编号
   select readerName, bookNo
   from tb_reader r, tb_borrow b
   where r.readerno=b.readerno and borrowdate>=“2014-01-01”;

或者
where r.readerno=b.readerno and year(borrowdate)>=2014;

4. 创建一个读者借书总数的视图v_borrow
   create view v_borrow
   as select readerno “读者编号”, count(*) “借书总册数”
   from tb_borrow
   group by readerno;

或者
create view v_borrow(读者编号, 借书总册数)
as select readerno,count(*)
from tb_borrow
group by readerno;

5. 导入backup.txt数据库
   load data infile “backup.txt” into table tb_reader1;

或者
load data infile “C:/EXAM/00000000/db_book/backup.txt” into table tb_reader1;

load data infile 导入表文本文件语法
load data infile "表文本文件 " into table 表名；
或者
load data infile "表文本文件所在目录用斜杠 / " into table 表名；
select … into outfile 导出表文本语法
select * from 表名 into outfile “表名.txt”;
导出的表文本文件在数据库文件夹里