解题09-12

• 找出通过率不是100% 的管理员并显示他的通过率

-- 可以先完成 显示0002号管理员的通过率，完成sql语句的结构后，在把0002号
替换成变量值
SELECT DISTINCT FLOOR(sc1.`adno`) AS '管理员',
  (SELECT COUNT(*) AS 'lessno' FROM sc sc2
   WHERE sc1.adno = sc2.adno AND score > 60)
   /
  (SELECT COUNT(*) AS 'countno' FROM sc sc2
  WHERE sc1.adno = sc2.adno) AS '通过率'  
  FROM sc sc1
WHERE 
  (SELECT COUNT(*) AS 'lessno' FROM sc sc2
  WHERE sc1.adno = sc2.adno AND score > 60) 
   /
  (SELECT COUNT(*) AS 'countno' FROM sc sc2
  WHERE sc1.adno = sc2.adno)  < 1

• 将管理员编号中开头的0去掉（0001-->1）

select distinct floor(adno) from sc

select distinct adno,sc.adno+0 as '管理员去0' from sc

select distinct adno,convert(adno,signed) from sc

select distinct adno,cast(adno as signed) from sc

select substring_index(adno,'0',-1) as 'adno' from sc

---

权限与视图

• view；视图是一种虚拟表,建立在基础表上，自身不保存数据，提升了数据的安全性，隐藏不需要不能够显示的数据，
  视图使得不同的用户在使用同一张表的时候，权限可以更精准的区别。
• 视图的作用：

1. 方便权限的细粒度管理
2. 简化了sql查询
3. 提高了数据的安全性，隐藏了不应该出现的数据（字段，行）
4. 减轻了网络传输

• 粒度 对数据库，表（对象），字段和行的控制

• 权限管理（sqlserver）：

1. login账号
   create login gxa with password='123'
2. user用户 一个账号可以对应多个用户，一个用户只能对应一个数据库

use gxa
create user gxa for login gxa

一个用户可以单独授予对象权限，也可以授予角色权限；
角色权限就是对象权限的集合。
如：一个人可以被授权查看财务部的考勤记录，也可以被安排总经理，
总经理拥有查看技术部工资，财务部工资，技术部考勤记录。
（这个人就有四种权限）

3. role角色（角色权限）

create role gxaadmin
grant update on student to gxaamdin
grant update on class to gxaadmin
grant update(sex) on teacher to gxaadmin

-- 收回权限
revoke create table from gxa
revoke all to gxa

4. object对象（对象权限）

grant select to gxa

-- 让用户担任角色，就获得了角色的权限
sp addrolemember gxaadmin 

5. schema架构

数据库>安全性>架构
-- 创建架构
create schema gxa
-- 修改用户默认架构和拥有架构（图形界面操作）
-- 检查用户是否有权创建对象
sp_helprotect @username='gxa'

grant create table to gxa
-- 在新建账号登录之后，创建表默认创建是自己架构的表（表的前缀是自己架构的
名字），所以查询的时候不带前缀查询的是自己架构的表，带了前缀就是其他架构的
表（类似于java的包）

• 总结实验步骤：

1. 创建gxa账号 gxa用户 gxa架构，gxa账号关联gxa用户（创建命令自动关联）
   gxa用户默认gxa架构，拥有gxa架构（图形界面设置）
2. 登录gxa账号，创建表，查询表（dbo架构和自己架构有同名表），查询结果为
   不带前缀默认查自己，带前缀查别人

---

• 给某个账号授予了视图的权限，该账号就只能查询这个视图

grant select table vw_student to gxa1
select * from vw_student

---

• 后勤管理员查看学生表

create view vw_student 
as
select sno,name,s.sex,c.cname,t.tno,t.tname from student s 
    join class c 
    on c.cno=s.cno 
    join teacher t 
    on t.tno=c.tno 

---

• 权限视图mysql版本

-- 创建用户
INSERT INTO mysql.user(HOST,USER,PASSWORD) VALUES('locahost','gxa','123')

-- 授权         数据库.对象    to  账号@主机                    密码
GRANT SELECT ON gxa.vw_student TO gxa@localhost IDENTIFIED BY '123'
-- 创建视图
CREATE VIEW vw_student
AS

SELECT sno,NAME,s.sex,c.cname,t.tno,t.tname FROM student s  
       JOIN class c
       ON c.cno = s.cno
       JOIN teacher t
       ON t.tno = c.tno 

• 树形结构：每一个元素只有唯一的父元素

create table area(
  id int primary key,
  name varchar(20),
  level int,
  pid int
)
-- 省
create view province
as
select * from area 
where level  =1

select * from province
-- 市
create view city
as
select * from area 
where level  =2

-- 连接查询
select * from city c join province p 
    on c.pid=p.id and p.name='四川省'

• 视图使用时机：

1. 同一张表，不同的用户查询，要求不一样，而且数据安全的要求不一样，
   给不同的用户创建视图（给不同用户创建账号分配不同的权限）。
2. 当数据呈现树形结构，且数据量不大，使用 id name level pid树形结构表
   为每一级查询建立视图。

---

数据库编程

编程（SQLSERVER）

1. 如何定义变量（变量是常量的容器）

    declare @x int,@y int

2. 如何给变量赋值

    set @x = 172
    select @y = high from student where name='刘三'
    print @y

3. 变量如何运算

数学运算（加减乘除）
比较运算（> <）
逻辑运算（!）

if @y >= @x
begin
  print '这位同学比较高'
end
else
begin
  print '这位同学没有刚才的高'
end

4. 变量如何有规律的运算（重复，迭代，循环）

-- for循环（已知循环次数或可推导）和while循环（循环次数不可推导）

declare @i int =0 
while (@i < 10)
begin
  set @i = @i + 1;
  print @i
end

游标

• 利用游标逐个输出记录

1. 拿出游标

  declare getScoreAndName cursor for
  select name,avgscore from student 
  order by avgscore desc

2. 打开游标

  open getScoreAndName
  declare @name varchar(20),@score float,@count int = 0

3. 逐行读取

while(@count = 0 or @@fetch_status = 0)
begin
  fetch next from getScoreAndName into @name,@score
  print @name + convert(char(4),@score)
  set @count = @count + 1
end

4. 关闭游标

  close getScoreAndName

5. 释放游标

  deallocate getScoreAndName

• 能够使用最少人数达到400分的学生名单

-- 1、拿出游标
declare getName cursor for 
select name,avgscore from gxa.dbo.student 
  order by avgscore desc
-- 2、开启游标
open getName
declare @myname varchar(20),@myscore float,@mycount int = 0,@mysum float = 0
-- 3、逐行读取
while(@mysum <400 and (@mycount = 0 or @@fetch_status = 0))
begin
  fetch next from getName into @myname,@myscore
  select @mysum = @mysum +@myscore
  print @myname
  set @mycount = @mycount + 1
end
-- 4、关闭游标
close getName
-- 5、释放游标
deallocate getName

• 计算出运走全班同学最快需要几班电梯（电梯限重350）,以分数当体重

declare getWeightAndName cursor for
 select name,avgscore from student
 order by avgscore
-- 2.打开游标
open getWeightAndName
declare @tname varchar(20),@weight float,
        @tcount int = 0,@weightsum int = 0,-- tcount第几人 weightsum体重和
        @timesno int = 1,@flag int = 0 -- flag 0表示输出标题 1不输出
-- 3.逐行读取
while((@tcount = 0 or @@fetch_status = 0))
begin
  fetch next from getWeightAndName into @tname,@weight
  if @flag = 0
  begin
   print '乘坐第' + convert(char(2),@timesno) + '班电梯'
   set @flag = 1
  end

  set @tcount = @tcount + 1
  set @weightsum = @weightsum + @weight
  -- 如果这个人进入电梯就超过电梯要求的重量
  if @weightsum  > 350
  begin
    set @weightsum = @weight -- 满足条件说明这个人不能乘坐这班电梯，
                             -- 但是他下一班电梯的第一个
    set @timesno  = @timesno + 1
    print '乘坐第' + convert(char(2),@timesno) + '班电梯'
    print @tname + convert(char(4),@weight)
  end
  else
  begin
    print @tname + convert(char(4),@weight)
  end
end  
-- 4.关闭游标
close getWeightAndName
-- 5.释放游标
deallocate getWeightAndName

游标（MYSQL）

• 最快达到400分的学生名单

-- mysql使用游标必须创建存储过程
DROP PROCEDURE IF EXISTS `gxa`.`prc_student_score`;
DELIMITER//

CREATE PROCEDURE `gxa`.`prc_student_score`()
BEGIN
DECLARE done INT DEFAULT FALSE;-- 结束标志
DECLARE curscore FLOAT;-- 变量一行声明一个
DECLARE sumscore FLOAT DEFAULT 0;
DECLARE curname VARCHAR(20);
DECLARE cur_getSocreAndName CURSOR FOR SELECT NAME,avgscore FROM student
 ORDER BY avgscore DESC;
 -- 句柄 实际工作的电视机，但是用来控制的遥控器
 -- 找不到数据就结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur_getSocreAndName;
DROP TABLE IF EXISTS `gxa`.`tempstudent`;
CREATE TABLE gxa.tempstudent(NAME VARCHAR(20),score FLOAT);
 read_loop: LOOP
   FETCH cur_getSocreAndName INTO curname,curscore;
   IF done OR sumscore >= 400 THEN
     LEAVE read_loop;
   END IF;
   -- 正常的循环 查询数据插入临时表，将当前数据加入数据之和
   INSERT INTO tempstudent VALUES(curname,curscore);
   SET sumscore := sumscore + curscore;
 END LOOP;
    INSERT INTO tempstudent VALUES('总分',sumscore);

CLOSE cur_getSocreAndName;
  SELECT * FROM gxa.tempstudent;
  DROP TABLE gxa.tempstudent;
END//

CALL `gxa`.`prc_student_score`;

• mysql不能直接使用类似print语句的命令，因为mysql没有控制台，
  只能创建临时表，将数据都插入临时表，查询临时表时输出，然后丢
  弃临时表。

Example:
DROP PROCEDURE IF EXISTS `gxa`.`prc_test`;
DELIMITER//
CREATE PROCEDURE `gxa`.`prc_test`()
BEGIN
 DECLARE i INT DEFAULT 0;
 CREATE TABLE gxa.test(ino INT); -- 创建临时表
 read_loop: LOOP -- read_loop是循环的名字
   SET i := i + 1;
   IF i > 10 THEN 
     LEAVE  read_loop; -- leave循环 退出循环  
   END IF;
   INSERT INTO test VALUES(i); -- 插入临时表 
 END LOOP;
 SELECT * FROM gxa.test; -- 查询临时表
 DROP TABLE gxa.test; -- 丢弃临时表
END//

CALL `gxa`.`prc_test`;

• 计算出运走全班同学最快需要几班电梯（电梯限重350）,以分数当体重(MySql)

DROP PROCEDURE IF EXISTS `gxa`.`prc_stu_weight`;
DELIMITER//

CREATE PROCEDURE `gxa`.`prc_stu_weight`()
BEGIN
DECLARE tname VARCHAR(20);   
DECLARE weight FLOAT;
DECLARE tcount INT DEFAULT 0;    -- tcount第几人 
DECLARE weightsum INT DEFAULT 0;   -- weightsum体重和
DECLARE timesno INT DEFAULT 1;
DECLARE flag INT DEFAULT 0;
DECLARE done INT DEFAULT FALSE;-- 结束标志
DECLARE cur_getWeightAndName CURSOR

FOR

SELECT NAME , avgscore FROM student
ORDER BY avgscore;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur_getWeightAndName;
DROP TABLE IF EXISTS `gxa`.`countStr`;
CREATE TABLE `gxa`.`countStr`(
  NAME VARCHAR(20),
  cweight FLOAT,
  ccount INT
 );
 read_loop: LOOP
    FETCH NEXT FROM cur_getWeightAndName INTO tname,weight;
      IF done THEN
         LEAVE read_loop;
      END IF;
      IF flag = 0 THEN 
       BEGIN
       INSERT INTO gxa.countStr(ccount) VALUES(timesno);
       SET flag := 1;
       END;
      END IF;
    SET tcount := tcount + 1;
    SET weightsum := weightsum + weight;
  -- 如果这个人进入电梯就超过电梯要求的重量
    IF weightsum  > 350 THEN 
      BEGIN
      SET weightsum := weight; -- 满足条件说明这个人不能乘坐这班电梯，
                             -- 但是他下一班电梯的第一个
      SET timesno  := timesno + 1;
      INSERT INTO gxa.countStr(ccount) VALUES(timesno);
      INSERT INTO gxa.countStr(NAME,cweight) VALUES(tname,weight);
      END;
     ELSE IF weightsum  <= 350 THEN 
      BEGIN 
      INSERT INTO gxa.countStr(NAME,cweight) VALUES(tname,weight);
      END;
      END IF;
    END IF;
 END LOOP;
CLOSE cur_getWeightAndName;
SELECT * FROM gxa.countStr;
DROP TABLE gxa.countStr;
END//



CALL `gxa`.`prc_stu_weight`;