SQL与编程题

亚米鸭的故事汇
276 阅读6分钟

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第25天，点击查看活动详情

sql应用题——学生社团

给定的学生社团数据库db_ society中包含学生信息表tb student.社团信息表tb_ group和学生参加社团信息表tb_ gs :

tb_student (sno,sname,sex, birth,native,lass),各字段的含义依次为:学号、姓名、性别、出生日期、民族、班级;

tb_group (gno,gname, buildup,memo),各字段的含义依次为:社团编号、社团名称、成立时间、简介;

tb_gs (gno,snojoindate,grole),各字段的含义依次为:社团编号、学号、参加社团时间、承担的工作。

1.用SQL语句将tb_student表中native字段的数据类型改为可变长的字符串类型,长度为20。

分析：这考察mysql数据定义语言 DDL 里面修改表

修改表使用的 SQL 语句是 ALTER TABLE，有 3 种形式，分别是修改字段、添加字段、删除字段。

其中修改字段分两种：改字段名和不改字段名。

  • 只改字段名：

    ALTER TABLE table CHANGE oldcolname newcolname type;

  • 不改字段名而只修改字段类型：

    ALTER TABLE tableName MODIFY colname newtype;

  • 添加字段语法如下：

    ALTER TABLE tableName ADD colname type;

  • 删除字段语法如下：

    ALTER TABLE tableName DROP colname;

那么在本题，我们使用 alter table tb_student modify native varchar(20); 进行修改。

2.用SQL语句在tb_ gs表上建立一个按gno升序、 sno降序的组合索引,索引名称为idx_ gs。

分析：本题考察索引的知识，已知添加索引有三种方式，第一个在建表时候直接create index 索引名字（字段名字），方式2 CREATE INDEX 索引名 ON 表名(字段名);方式三，修改表时添加索引ALTER TABLE 表名 ADD INDEX 索引名(字段名)。

如果多个字段索引，在字段里面用逗号隔开

本题表已经创建好了，我们可以使用后面两种方法，这里我选第三种 Alter table tb_gs add index idx_gs(gno asc,sno desc);

3.用SQL语句查询参加“话剧社”的学生姓名和班级。

分析，这是考察sql查询，这里我们知道学生姓名在学生信息里面，社团名字在社团信息里面，两个表通过学生参加社团那个表才能连接，而且连接条件是 学生表的sno=参加表sno and 社团表gno=参加表gno。

书写sql可以是 select 相关字段 from tb_student，tb_group，tb_gs where tb_student.sno=tb_gs.sno, tb_group.gno=tb_gs.gno and tb_group.gname="话剧社"

当然也可以使用子查询

Select sname,lass from student where sno in(

             select sno from tb_gs where gno in (

                select gno from tb_group where gname=”话剧社”));

4.用SQL语句建立一个名称为v _gs的视图用于统计每个社团的总人数,要求字段显示为”社团名”和"成员总数”, 并按“成员总数”降序排序。

分析：本题考察视图，创建视图方法CREATE VIEW viewname[column1, ……, columnn] AS SELECT 语句; 而且字段现实的名字考察字段别名。

要统计总人数，需要使用社团表和参加表，通过对社团进行分组，然后对分组后的学生进行统计

select gname '社团名字',count(sno) '学生人数' from 
     (select tb_group.*,joindate,grole from tb_gs,tb_group where tb_gs.gno=tb_group.gno) as a 
     group by a.gno order by 学生人数 desc

5.学生社团数据库db_ society中已经存在两个用户“李丽”和“王勇”, 两人均拥有对tb_ _gs表进行查询和修改的权限,请使用SQL语句撤销用户“王勇”在主机名为localhost的主机中所有权限(注意:不能删除用户“王勇”)。

分析：本题考察对数据库的权限管理 那么使用 CREATE USER 语句创建新用户

CREATE USER '账号' [IDENTIFIED BY '密码'];

其中，“账号”的格式为 user_name@host_name。user_name 是账号名称，host_name 为主机名称，即指定账户的同时又指定发起连接的客户端主机名字。也可以不指定主机名，默认为 %，表示匹配任意客户端主机。

修改用户密码使用 ALTER 子句，其语法如下：

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

使用 DROP 子句删除用户，其语法如下：

DROP USER 用户名@主机名;

每创建一个用户，在user表里面就会多出一个记录，通过SELECT user, host FROM user; 查看用户记录。通过DELETE FROM USER WHERE USER = '用户名' AND host = '主机名';对USER 表的记录进行删除

查看用户权限语法为：

SHOW GRANTS FOR 用户名@主机名;

授予权限：使用关键字 GRANT

GRANT 权限名称[(列名)][, 权限名称(列名)] ON 权限级别 TO 用户 [with option]

如果想给某个用户所有权限，将 权限名称[(列名)][, 权限名称(列名)] ON 权限级别 替换为 ALL PRIVILEGES。

例如：给“zhangsan”用户赋予“mysql 数据库中所有表的 SELECT 权限”。

GRANT SELECT ON mysql.* TO zhangsan@localhost;

权限限制与撤销

回收某个用户的特定权限

REVOKE 权限名称[(列名)] ON 权限级别 FROM 用户;

回收某个用户的所有权限

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户;

在本题里面，我们需要回收王勇的全部权限即Revoke all privileges,grant option from ‘王勇’@’localhost’;

6.创建一个名称为sp_gs的存储过程,要求根据输入的班级信息统计该班级参加社团的总人数,并返回总人数。

分析，因为每个班参加社团不唯一，所以结果不唯一，所以我们需要使用游标来完成，并且这个游标需要循环。那么创建存储过程的格式为

CREATE PROCEDURE 存储过程名([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
……
END

调用：call 存储过程名字（参数） 创建游标格式为：

定义游标：
DECLARE 游标名字 CURSOR FOR sql语句;
打开
OPEN 游标名字;
从游标里面取值
FETCH 游标名字 INTO 自己定义名字;
关闭
close 游标名字

那么本题解答为：

delimiter $$ #更换结束符
#创建存储过程
create procedure sp_gs(in classname char(10),out total smallint)
begin

#定义变量
declare xh char(10);

#定义循环标志
declare f boolean default true;

#定义游标
declare number_gs cursor for
  select tb_student.sno from tb_student,tb_gs where tb_student.sno=tb_gs.sno and tb_gs.gno =(select gno from tb_group where gname=classname);
 #定义循环结束标志
declare continue handler for not found set f=false;
#给输出变量赋值
set total=0;
#打开
open number_gs;
#取值
fetch number_gs into xh;
#循环
while f do
   set total=total+1;
   #循环一次，total++；如果有n条数据，循环需要n次，可以直接输出total表示数据数量
   fetch number_gs into xh;
end while
#关闭
close number_gs;

end $$
delimiter ;

call sp_gs("社团名字",@resule)
select @resule

7.创建一个名称为tr. gs的触发器,要求当向学生参加社团表tb_ gs中插入社团的“社长"记录之前,检查该社团是否已经有社长,若已有社长,自动将该记录添加为该社团的成员,并用变量str提示“每个社团只能有一个社长 !”。

分析：触发器创建


CREATE TRIGGER 触发器名字 触发时间 触发事件 ON 建立触发器的表名 FOR EACH ROW 触发器程序体

触发时间有：before（某事件之前），after（某事件之后）；

触发事件有：取值 INSERT（插入）、UPDATE（更新）、DELETE（删除）

触发器程序体，一条 SQL 语句或存储过程等；

delimiter //
create trigger tr_gs before insert
on tb_gs for each row
begin

#定义变量
declare times int;
#判断条件
if(NEW.grole="社长")
   set times=0;
     begin
     set times=(select count(*) from tb_gs where gno=new.gno and grole="社长")
     if(times!=0) then
       begin set new.frole="成员";
       SET @str='每个社团只能有一个社长！';
       end;
     end if
end;
end if;
end//
delimiter;     
/*激活触发器*/

INSERT INTO tb_gs VALUES('g100','s101','2017-04-18','社长');

INSERT INTO tb_gs VALUES('g102','s101','2017-04-18','社长');
avatar
文章
阅读
粉丝