sql/l12.txt

选择列查询
use teachingdb;
-- 查询出生年份为 2000 年的学生的学号和姓名，并按照学号排序
SELECT sno AS 学号, sname AS 姓名
FROM student
WHERE YEAR(birthday) = 2000
ORDER BY sno;

SELECT * FROM course;	查询整个表单

SELECT * 
from course
where ctime between 1 and 50;	查询学时在1-50的课程信息

select *
from student
where sdept not in('计算机','信息');	查询不是计算机或信息的学生

SELECT COUNT(*)
FROM student;		求学生总人数
select cno,AVG(grade),MAX(grade),MIN(grade)
from score
GROUP BY cno;		求选修各门课程的最高、最低与平均成绩。

-- 查询 tb_emp 表中 DeptId 为 NULL 的所有字段的内容
SELECT *
FROM tb_emp
WHERE DeptId IS NULL;
-- 查询 tb_emp 表中不重复的 Name 字段的内容
SELECT DISTINCT Name
FROM tb_emp;

-- 查询 tb_emp 表中 DeptId 为 301 并且薪水大于 3000 的所有字段的内容
SELECT *
FROM tb_emp
WHERE DeptId = '30I' AND Salary > 3000;
-- 查询 tb_emp 表中 DeptId 为 301 和 303 的所有字段的内容
SELECT *
FROM tb_emp
WHERE DeptId IN ('301', '303');

select *
from tb_score
where class_id='1'
order by score desc;		ASC 升序关键字
DESC 降序关键字

对班级名称进行分组查询
select stu_id,class_id,name
from tb_class
group by class_id;

select *
from tb_score
order by score DESC 
limit 1,4;

索引 完成索引的创建
#1.创建名为pk_student的主键索引
create table student(
    stu_id int not null,
    name varchar(25) not null,
    age int not null,
    sex char(2) not null,
    classes int not null,
    grade int not null,
    primary key(stu_id)
);
# 创建名为 idx_age 的普通索引
ALTER TABLE student ADD INDEX idx_age (age);

# 创建名为 uniq_classes 的唯一索引
ALTER TABLE student ADD UNIQUE INDEX uniq_classes (classes DESC);

# 创建名为 idx_group 的组合索引
ALTER TABLE student ADD INDEX idx_group (name, sex, grade);

use School;

视图：创建一个单表视图和一个多表视图
#1.创建单表视图
create view stu_view as select math,chinese,math+chinese from student;

#2.创建多表视图
create view stu_classes as select student.stu_id,student.name,stu_info.classes from student,stu_info where stu_info.stu_id=student.stu_id;

为 student 表按姓名升序建立索引，索引名为 idx_sname。
  create index idx_sname on student(sname asc);

删除索引 idx_sname。
   drop index idx_sname on student;

建立计算机系的学生的视图 student_cs。 student表结构 学生表
 create view student_cs as select * from student where sdept = '计算机' ;
创建由学号和平均成绩两个字段的视图 v_grade_avg
create view v_grade_avg 
as select sno as '学号',avg(grade) as '平均成绩' from score group by sno;
建立索引
第一题
在jdxx数据表根据省份sf、城市cs、区县qx和名称name建立主索引zsy
第二题
在jdxx数据表根据名称name建立普通索引 namesy
 use province;
 #代码开始
 alter table jdxx add constraint zxy primary key(sf,cs,qx,name);
 create index namesy on jdxx(name);
 #代码结束
 show index in jdxx\g;
建立视图并更新视图
建立视图csxx,显示jdxx数据表的cs长沙市的区县qx、名称mc。
根据视图csxx，将开福区qx的西湖街道name修改为月湖街道
use province;
#代码开始
create  view csxx as select qx,name from jdxx where cs='长沙市';
update csxx set name = '月湖街道' where qx='开福区' and name = '西湖街道';
#代码结束
select * from csxx;

根据jdxx数据表和qxyzbm数据表
建立视图csbm
视图中有长沙市的区县qx、名称name和区县编码qxbm字段
 use province
 #代码开始
 create view csbm 
 as select jdxx.qx,name,qxbm
 from jdxx,qxyzbm
 where jdxx.qx=qxyzbm.qx and jdxx.cs = '长沙市';

 #代码结束
 select * from csbm;

实训五：安全
创建用户
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
create user user1@localhost identified by 'user1'
 /**********End**********/

第1关：用户和权限
# 请填写语句，完成以下功能：
#(1) 创建用户tom和jerry，初始密码均为'123456'；
create user tom identified by '123456';
create user jerry identified by '123456';
#(2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限；
grant select(c_name,c_mail,c_phone) on client to tom with grant option;
#(3) 授予用户jerry修改银行卡余额的权限；
grant update(b_balance) on bank_card to jerry;
#(4) 收回用户Cindy查询银行卡信息的权限。
revoke select on bank_card from Cindy;

第2关：用户、角色与权限
# 请填写语句，完成以下功能：
# (1) 创建角色client_manager和fund_manager；
create role client_manager,fund_manager;
# (2) 授予client_manager对client表拥有select,insert,update的权限；
grant select ,insert ,update on client to client_manager;
# (3) 授予client_manager对bank_card表拥有查询除银行卡余额外的select权限；
grant select(b_number,b_type,b_c_id) on bank_card to client_manager; 
# (4) 授予fund_manager对fund表的select,insert,update权限；
grant select,insert,update on fund to fund_manager;
# (5) 将client_manager的权限授予用户tom和jerry；
grant client_manager to tom,jerry;
# (6) 将fund_manager权限授予用户Cindy.
grant fund_manager to Cindy;

根据提示，在右侧编辑器补充代码，系统用户 root 授权 user1 用户创建数据库 teachingdb2 及其所有对象的权限
 grant all on teachingdb2.* to 'user1'@'localhost'

给 user1 授予在 teachingdb 所有对象上的 SELECT 权限，并使该用户具有给其他用户授予相同权限的权限。
grant SELECT on teachingdb.* to 'user1'@'localhost' WITH GRANT OPTION

给 user1 和 user2 授予 student 表上的所有权限。
  GRANT ALL PRIVILEGES ON `teachingdb`.`student` TO 'user1'@'localhost'

给 user2 授予 score 表 grade 字段上的UPDATE权限。
GRANT USAGE ON *.* TO 'user2'@'localhost' IDENTIFIED BY PASSWORD '*B2B366CA5C4697F31D4C55D61F0B17E70E5664EC'

从 user1@localhost 用户收回查询 teachingdb 所有对象的SELECT权限。
  revoke select  on teachingdb.*  from user1@localhost

 在右侧编辑器中编写 SQL，将用户casual_user@'localhost'的密码修改为123456，并给其授所有数据库的“增改查”的权限。（连接数据库的用户名为：root，密码为：123123。）

##########  开始编写 SQL  ##########
set password for casual_user@'localhost' = password('123456');
-- grant SELECT,INSERT,UPDATE on . to casual_user@'localhost' identified by "123456";
grant SELECT ,INSERT, UPDATE on *.* to casual_user@'localhost';
FLUSH PRIVILEGES;

将用户user1对数据库mydb1中的table1和table2的所有权限收回
将用户user2对数据库mydb2中的所有表（mydb2.*）的增加、查询、更新、删除、创建、修改属性权限收回
revoke all on mydb1.table1 from 'user1'@'localhost';
revoke all on mydb1.table2 from 'user1'@'localhost';
revoke insert,select,update,create,delete,alter on mydb2.* from 'user2'@'localhost';

创建触发器
 use teachingdb;
 
 /**********Begin**********/
      delimiter $$

  drop trigger if exists sum_credit$$

  create trigger sum_credit after insert on score

  for each row

  Begin

  update student set totalcredit=totalcredit+

  (select credit from course where cno=new.cno)

  where sno=new.sno and new.grade>=60;

  end$$

  delimiter ;

 /**********End**********/

根据提示，在右侧编辑器补充代码，创建级联删除触发器 del_student_score，当删除 student 表中的学生时，也删除 score 表中的对应学号的学生成绩记录。
delimiter $$

  drop trigger if exists del_student_score$$

  create trigger del_student_score before delete

  on student

  for each row

  Begin

  delete from score where sno=old.sno;

  end$$

  delimiter ;

查看表结构与修改表名
ALTER TABLE tb_emp RENAME jd_emp;
########## show tables in this database ##########
SHOW TABLES;
########## describe the table ##########
DESCRIBE jd_emp;


查询数据表中学生姓名以及对应的班级名称，将其对应的列名分别另命名为studentName和className。
USE School;
select tb_student.name as studentName,tb_class.name as className from tb_student join tb_class 
on tb_class.id = tb_student.class_id;

分别使用左外连接和右外连接查询数据表中所有学生姓名和对应的班级名称，查询结果列分别另命名为studentName和className。
USE School;
########## 使用左外连接查询所有学生姓名和对应的班级 ##########
#请在此处添加实现代码
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className 
from tb_class right join tb_student on tb_class.id=tb_student.class_id;
########## 使用右外连接查询所有学生姓名和对应的班级 ##########
########## Begin ##########
select tb_student.name as studentName,tb_class.name as className 
from tb_class  left join tb_student on tb_class.id=tb_student.class_id;
########## End ##########

查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级，其中学生的姓名和学生所在班级分别另命名为studentName和className。
USE School;
 
########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ##########
#请在此处添加实现代码
########## Begin ##########
 
select s1.name as studentName,score,s2.name as className
from tb_student as s1,tb_class as s2
where s1.class_id=s2.id
and s1.score>90
order by score desc;
 
查询大于所有平均年龄的员工姓名与年龄。我们为你提供了tb_emp表
USE Company;
 
#请在此处添加实现代码
########## Begin ##########
#1.查询大于所有平均年龄的员工姓名与年龄
select name,age from tb_emp 
where age>#嵌套内得到的是表中人员的平均年龄，把返回的作为一个条件
(
select avg(age)
from tb_emp
);
########## End ##########

根据提供的数据，在右侧编辑器中补充代码：查询薪资表中比Java最高工资高的所有员工职位名称和薪资；查询薪资表中比Java最低工资高的所有员工职位名称和薪资；查询薪资表中职位为Java的所有员工职位名称和薪资。

USE Company;
#请在此处添加实现代码
########## Begin ##########
 
#1.使用 ALL 关键字进行查询
select position,salary 
from tb_salary 
where salary >ALL//选择比java的薪资最低的人高
(
SELECT salary 
from  tb_salary 
where position ="java"
);
 
#2.使用 ANY 关键字进行查询
select position,salary //选择比任何java的人的薪资都高
from tb_salary 
where salary> ANY
(
select 
min(salary) 
from tb_salary 
where position="java"
);
#3.使用 IN 关键字进行查询
select position,salary 
from tb_salary 
where position in("java");//选择职位为java的人
########## End ##########

使用自然连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩。

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
select sname,cname,tname,grade 
from course natural join score natural join student natural join teach
where tname = "严敏" and cname = "数学分析" ;
  
 /**********End**********/
 
使用等值连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩。
 
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 select sname,cname,tname,grade 
from course,score,student,teach
where teach.tname = "严敏" and course.cname = "数学分析" and teach.tno = score.tno and course.cno = score.cno and score.sno = student.sno;
 /**********End**********/
 
 使用 JOIN 连接求选修了严敏老师的数学分析课程的学生的姓名、课程名称、教师名和成绩。
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,cname,tname,grade 
from course inner join score inner join student inner join teach
on tname = "严敏" and cname = "数学分析" and teach.tno = score.tno and course.cno = score.cno and score.sno = student.sno;
  
 /**********End**********/
 
 使用自身连接查询求年龄大于'刘东明' 的所有学生的姓名与出生日期。
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,birthday
  from student s1
  where s1.birthday < (select birthday from student where sname = '刘东明');
 /**********End**********/
 
 使用外部连接查询求未选修任何课程的学生的学号和姓名。 --学生表 student--成绩表score --课程表cours
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 select sno,sname from student 
 where sno not in(select sno from score);
  
 /**********End**********/
 
 求年龄大于'刘东明' 的所有学生的姓名与出生日期。 --学生表student
  use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,birthday from student where birthday < (select birthday from student where sname = "刘东明");
  
 /**********End**********/
 
 求未选修任何课程的学生的学号和姓名。 --学生表student --成绩表score
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sno,sname from student
  where sno not in(select sno from score);
  
 /**********End**********/

 求比数学系中全体学生年龄大的学生的姓名和系。 --学生表student
  use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,sdept,birthday from student
  where birthday < all(select birthday from student where sdept = '数学');
  
 /**********End**********/

，求选修了004课程的学生的姓名和系。 --学生表student --成绩表score
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,sdept from student
  where sno in (select sno from score where cno = '004');
 
 /**********End**********/
 
 求选修了'刘东明' 同学选修的全部课程的学生的学号。 --学生表student --成绩表score
 
 
 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 select sno from score where cno in(select cno from score,student where score.sno = student.sno and sname = '刘东明') and score.sno!= (select sno from student where sname = '刘东明') group by sno having count(cno)>=2 ;
  
 /**********End**********/

 求选修了全部课程的学生的学号。 --学生表student --成绩表score --课程表course use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
/*  select sno from student where not exists(select * from course where not exists(select * from score where score.sno = student.sno and score.cno = course.cno));*/
select sno from score group by sno having count(*)=
(select count(*) from course);
  
 /**********End**********/