目录
实训一:数据定义和操纵(4课时)
初识MySQL数据库
第1关：创建数据库
第2关：创建表
第3关：使用主键约束
第4关：外键约束
第5关：添加常用约束
DDL语言的使用
第1关：创建数据库
第2关：创建表
第3关：添加字段
第4关：删除字段
第5关：修改字段
第6关：添加唯一性约束
DML语言的使用
第1关：insert…into方式插入数据
第2关：更新数据
第3关：更新数据-练习
第4关：删除数据
第5关：创建表-练习
第6关：删除数据-练习1
第7关：删除数据-练习2
MySQL数据库 – 数据更新
第1关：插入数据
第2关：更新数据
第3关：删除数据
MySQL数据库 – 数据库和表的基本操作（一）
第1关：查看表结构与修改表名
第2关：修改字段名与字段数据类型
第3关：添加与删除字段
第4关：修改字段的排列位置
第5关：删除表的外键约束
实训二:单表查询(4课时)
单表查询（一）student表查询
第1关：选择列查询
第2关：去除重复结果
第3关：范围查询
第4关：带IN关键字的查询
第5关：匹配查询
第6关：范围查询-练习
第7关：where子句查询
单表查询（二）基本查询
第1关：基本查询语句
第2关：带 IN 关键字的查询
第3关：带 BETWEEN AND 的范围查询
单表查询（三）聚集函数
第1关：COUNT( )函数
第2关：COUNT( )函数-练习
第3关：AVG( )函数、MAX函数和MIN函数
第4关：分组查询
第5关：倒序排列
单表查询（四）-分组操作符与聚集函数
第1关：数据统计（初级）
第2关：数据统计初级应用
第3关：数据统计综合应用
单表查询（五）排序和分组
第1关：对查询结果进行排序
第2关：分组查询
第3关：使用 LIMIT 限制查询结果的数量
单表查询（六）综合查询
第1关：基本查询语句
第2关：带 IN 关键字的查询
第3关：带 BETWEEN AND 的范围查询
第4关：带 LIKE 的字符匹配查询
第5关：查询空值与去除重复结果实训三:多表查询(4课时)
第6关：带 AND 与 OR 的多条件查询
第7关：对查询结果进行排序
第8关：分组查询
第9关：使用 LIMIT 限制查询结果的数量
实训三:多表查询(4课时)
多表查询-子查询（一）
第1关：子查询
第2关：子查询-练习
第3关：子查询-练习一
第4关：子查询-练习二
第5关：子查询-练习三
第6关：子查询-练习四
多表查询 – 子查询（二）
第1关：带比较运算符的子查询
第2关：关键字子查询
多表查询-连接查询（一）
第1关：自然连接
第2关：等值连接
第3关：JOIN连接
第4关：自身连接查询
第5关：外部连接查询
多表查询- 连接查询（二）
第1关：内连接查询
第2关：外连接查询
第3关：复合条件连接查询
大学数据库创建与查询实战
第1关：数据库表设计
第2关：查询（一）
第3关：查询（二）
第4关：查询（三）
第5关：查询（四）
第6关：查询（五）
实训四:索引与视图(2课时)
MySQL开发技巧 – 索引第1关：索引
MySQL开发技巧 – 视图
第1关：视图
索引(teachingdb数据库）
第1关：索引
第2关：删除索引-练习
SQL视图（teachingdb数据库)
第1关：创建视图
第2关：创建视图-练习一
MySQL-索引和视图
第1关：建立索引
第2关：建立视图并更新视图
第3关：建立基于多表的视图
实训五:数据库安全控制(2课时）
创建用户
第1关：创建用户
第2关：创建用户-练习!!!
授权及回收权限
第1关：授权
第2关：授权-练习一
第3关：授权-练习二
第4关：授权-练习三
第5关：回收权限
MySQL-安全性控制
第1关：用户和权限
第2关：用户、角色与权限
MySQL数据库 – 授权与撤销授权
第1关：数据库授权
第2关：数据库撤销权限
实训六:数据完整性
实训七:存储过程与触发器(4课时）
存储过程、函数与触发器
第1关：创建存储过程
第2关：创建函数-count_credit
第3关：存储过程-调用函数count_credit
第4关：创建触发器-计算总学分!!!
第5关：创建触发器-练习级联删除操作
存储过程
第1关：建立和调用存储过程（不带输出参数的存储过程）
第2关：建立和调用存储过程（带输出参数）
第3关：建立和调用存储函数
第4关：修改多个数据表的存储过程
第5关：使用游标的存储过程
实训一:数据定义和操纵(4课时)
初识MySQL数据库
第1关：创建数据库
mysql -uroot -p123123 -h127.0.0.1create database MyDb;show databases;第2关：创建表
mysql -uroot -p123123 -h127.0.0.1create database TestDb; create table t_emp( id int, name varchar(32), deptId int , salary float);第3关：使用主键约束
mysql -uroot -p123123 -h127.0.0.1create database MyDb;use MyDb;create table t_user1(userId INT PRIMARY KEY,name VARCHAR(32),password VARCHAR(11),phone VARCHAR(11),email VARCHAR(32));create table t_user2(name VARCHAR(32),phone VARCHAR(11),email VARCHAR(32),PRIMARY KEY(name,phone));第4关：外键约束
mysql -uroot -p123123 -h127.0.0.1create database MyDb;use MyDb;CREATE TABLE t_class(id INTPRIMARY KEY,name VARCHAR(22) );CREATE TABLE t_student(id INTPRIMARY KEY,name VARCHAR(22) ,classId int,CONSTRAINT fk_stu_class1 FOREIGN KEY(classId) REFERENCES t_class(id));第5关：添加常用约束
mysql -uroot -p123123 -h127.0.0.1CREATE DATABASE MyDb;USE MyDb;CREATE TABLE t_user(id INTPRIMARY KEY AUTO_INCREMENT,username VARCHAR(32) NOT NULL UNIQUE,sex VARCHAR(4) DEFAULT '男')DEFAULT CHARSET=utf8;DDL语言的使用
第1关：创建数据库
create database teachingdb;第2关：创建表
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/create table student(sno char(5) primary key,sname varchar(20) NOT null,sdept varchar(20) not null,sclass char(2) not null,ssex char(1),birthday date,totalcredit decimal(4,1)); /**********End**********/第3关：添加字段
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/alter table student add nativeplace varchar(20);-- alter table student add nativeplace varchar(20); /**********End**********/第4关：删除字段
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ALTER TABLE student DROP nativeplace; /**********End**********/第5关：修改字段
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ ALTER TABLE student MODIFY ssex varchar(3); /**********End**********/第6关：添加唯一性约束
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ alter table course add constraint uk_cno unique(cname);/**********End**********/DML语言的使用
第1关：insert…into方式插入数据
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/INSERT INTO studentVALUES (11111,'马明','计算机','01','女','2000-01-02',null); /**********End**********/第2关：更新数据
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/UPDATE studentSET totalcredit = 2.0 /**********End**********/-- update student set totalcredit=2.0;第3关：更新数据-练习
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/UPDATE student SET birthday = '2000-01-22'WHERE sname='马小燕'; /**********End**********/第4关：删除数据
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ DELETE FROM student WHERE sno = '11111'; /**********End**********/第5关：创建表-练习
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/-- select * fromstudent ;create table s1 as select * from student; /**********End**********/第6关：删除数据-练习1
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/DELETE FROMs1 WHERE sdept='计算机'; /**********End**********/第7关：删除数据-练习2
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/drop table s1 ; /**********End**********/MySQL数据库 – 数据更新
第1关：插入数据
USE Company;#请在此处添加实现代码########## Begin #################### bundle insert the value #########INSERT INTO tb_emp(Id,Name,DeptId,Salary) VALUES (1,"Nancy",301,2300.00),(2,"Tod",303,5600.00),(3,"Carly",301,3200.00);########## End ##########SELECT * FROM tb_emp;########## End ##########第2关：更新数据
USE Company;#请在此处添加实现代码########## Begin #################### update the value ##########UPDATE tb_empSET Name="Tracy",DeptId=302,Salary=4300.00WHERE id=3;########## End ##########SELECT * FROM tb_emp;########## End ##########第3关：删除数据
USE Company;#请在此处添加实现代码########## Begin #################### delete the value ##########DELETE FROM tb_empWHERE Salary>3000;########## End ##########SELECT * FROM tb_emp;########## End ##########MySQL数据库 – 数据库和表的基本操作（一）
第1关：查看表结构与修改表名
USE Company;########## Begin #################### modify the table name ##########ALTER table tb_emp RENAME jd_emp;########## show tables in this database ##########show tables;########## describe the table ##########describe jd_emp;########## End ##########第2关：修改字段名与字段数据类型
USE Company;#请在此处添加实现代码########## Begin #################### change the column name ##########ALTER TABLE tb_emp change Id prod_id int(11);########## change the data type of column ##########ALTER TABLE tb_emp MODIFY Name varchar(30);########## End ##########DESCRIBE tb_emp;第3关：添加与删除字段
USE Company;#请在此处添加实现代码########## Begin #################### add the column ##########ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name; ########## delete the column ##########ALTER TABLE tb_emp DROP Salary;########## End ##########DESCRIBE tb_emp;第4关：修改字段的排列位置
USE Company;#请在此处添加实现代码########## Begin #################### modify the column to top ##########ALTER TABLE tb_emp MODIFY Name varchar(25) FIRST;########## modify the column to the rear of another column ##########ALTER TABLE tb_emp MODIFY DeptId int(11) AFTER Salary;########## End ##########DESCRIBE tb_emp;第5关：删除表的外键约束
USE Company;#请在此处添加实现代码########## Begin #################### delete the foreign key ##########ALTER TABLE tb_emp DROP FOREIGN KEY emp_dept;########## End ##########SHOW CREATE TABLE tb_emp \G;实训二:单表查询(4课时)
单表查询（一）student表查询
第1关：选择列查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select sno 学号, sname 姓名 from student where birthday >= "2000.1.1" and birthday <= "2000.12.31"; /**********End**********/第2关：去除重复结果
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/SELECT DISTINCT snoFROM score ; /**********End**********/第3关：范围查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/SELECT *FROM course WHERE ctime BETWEEN 1 AND 50; /**********End**********/第4关：带IN关键字的查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/SELECT *from studentwhere sdept!='计算机' and sdept!='信息'; /**********End**********/第5关：匹配查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select *from studentwhere sname like '%马__'; /**********End**********/第6关：范围查询-练习
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select *from scorewhere tno='052501'and grade>='80' and grade<='90' and sno like '96%'; /**********End**********/第7关：where子句查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select sno, cno from score where grade is null; /**********End**********/单表查询（二）基本查询
第1关：基本查询语句
USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary ##########select Name,Salary from tb_emp;########## retrieving all the table ##########select * from tb_emp;########## End ##########第2关：带 IN 关键字的查询
USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary with IN statement ##########SELECT Name,Salary FROM tb_emp WHERE Id !='1';########## End ##########第3关：带 BETWEEN AND 的范围查询
USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary with BETWEEN AND statement ##########SELECT Name,Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000;########## End ##########单表查询（三）聚集函数
第1关：COUNT( )函数
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select count(*) from student; /**********End**********/第2关：COUNT( )函数-练习
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select count(distinct sno)from score; /**********End**********/第3关：AVG( )函数、MAX函数和MIN函数
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select cno,AVG(grade),MAX(grade),MIN(grade) from score group by(cno); /**********End**********/第4关：分组查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select sdept,count(*) from student group by sdept having count(*)<3; /**********End**********/第5关：倒序排列
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select sdept,sclass,COUNT(*) from student group by sdept,sclass order by count(*) desc,sdept desc; /**********End**********/单表查询（四）-分组操作符与聚集函数
第1关：数据统计（初级）
USE test_wyy_db_guetGOSET NOCOUNT ON -- ********** Begin ********** ---- ********** 此处写第一题的SQL语句 ********** --select count(*) from course where credit>2;-- ********** End ********** --GO -- ********** Begin ********** ---- ********** 此处写第二题的SQL语句 ********** --select sum(credit) from course where cno like "BT%";-- ********** End ********** --GO -- ********** Begin ********** ---- ********** 此处写第三题的SQL语句 ********** --select left(cno,2),count(*)from course group by left(cno,2);-- ********** End ********** --GO第2关：数据统计初级应用
USE test_wyy_db_guetGoSET NOCOUNT ON-- ********** Begin ********** ------------ 第一题----------select count(model) from printer where color = "T" and type = "laser"; -- ********** End ********** --GO-- ********** Begin ********** ------------ 第二题----------select min(price) from printer;-- ********** End ********** --GO-- ********** Begin ********** ------------ 第三题----------select model,price from printer where price >=(select max(price) from printer);-- ********** End ********** --GO第3关：数据统计综合应用
USE test_wyy_db_guetGoSET NOCOUNT ON---------- 第1题 ------------ ********** Begin ********** --select hd from V_test group by hd having count(*) 1000 group by maker;-- ********** End ********** --GO---------- 第4题 ------------ ********** Begin ********** --select maker,type,AVG(price) from V_test group by maker,type;-- ********** End ********** --GO单表查询（五）排序和分组
第1关：对查询结果进行排序
USE School;#请在此处添加实现代码########## Begin #################### 查询1班同学的所有信息以成绩降序的方式显示结果 ##########select * from tb_score where class_id = 1 order by score desc;########## End ##########第2关：分组查询
USE School;#请在此处添加实现代码########## Begin #################### 对班级名称进行分组查询 ##########SELECT * FROM tb_class GROUP BY class_id;########## End ##########第3关：使用 LIMIT 限制查询结果的数量
USE School;#请在此处添加实现代码########## Begin #################### 查询班级中第2名到第5名的学生信息 ##########SELECT * FROM tb_score order by score desc LIMIT 1,4;########## End ##########单表查询（六）综合查询
第1关：基本查询语句
USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary ##########select Name,Salary from tb_emp;########## retrieving all the table ##########select * from tb_emp;########## End ##########第2关：带 IN 关键字的查询
USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary with IN statement ##########SELECT Name,Salary FROM tb_emp WHERE Id NOT IN (1);########## End ##########第3关：带 BETWEEN AND 的范围查询
USE Company;#请在此处添加实现代码########## Begin #################### retrieving the Name and Salary with BETWEEN AND statement ##########SELECT Name,Salary FROM tb_emp WHERE Salary BETWEEN 3000 AND 5000;########## End ##########第4关：带 LIKE 的字符匹配查询
USE Company;######### Begin #########SELECT Name,Salary FROM tb_emp WHERE Name LIKE "C%";######### End #########第5关：查询空值与去除重复结果实训三:多表查询(4课时)
USE Company;######### Begin #########SELECT * FROM tb_emp WHERE DeptId IS NULL;######### End ################## Begin #########SELECT DISTINCT Name FROM tb_emp;######### End #########第6关：带 AND 与 OR 的多条件查询
USE Company;######### Begin #########SELECT * FROM tb_emp WHERE DeptId=301 AND Salary > 3000;######### End ################## Begin #########SELECT * FROM tb_emp WHERE DeptId=301 OR DeptId=303;######### End #########第7关：对查询结果进行排序
USE School;#请在此处添加实现代码########## Begin #################### 查询1班同学的所有信息以成绩降序的方式显示结果 ##########select * from tb_score where class_id = 1 order by score desc;########## End ##########第8关：分组查询
USE School;#请在此处添加实现代码########## Begin #################### 对班级名称进行分组查询 ##########SELECT * FROM tb_class GROUP BY class_id;########## End ##########第9关：使用 LIMIT 限制查询结果的数量
USE School;#请在此处添加实现代码########## Begin #################### 查询班级中第2名到第5名的学生信息 ##########SELECT * FROM tb_score order by score desc LIMIT 1,4;########## End ##########实训三:多表查询(4课时)
多表查询-子查询（一）
第1关：子查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select sname, birthday from student where birthday < (select birthday from student where sname = "刘东明"); /**********End**********/第2关：子查询-练习
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select student.sno,student.sname from student left join score on student.sno=score.sno where score.sno is null; /**********End**********/第3关：子查询-练习一
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select sname,sdept,birthday from student where birthday < any(select birthday from student where sdept="数学") and sdept "数学"; /**********End**********/第4关：子查询-练习二
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select student.sname,student.sdept from student,score where student.sno=score.sno and score.cno="004"; /**********End**********/第5关：子查询-练习三
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select score.sno from score where cno in (select score.cno from score,student where student.sno=score.sno and student.sname='刘东明') and score.sno not in(select student.sno from student where student.sname='刘东明') group by sno having count(cno) >=(select count(cno) from score,student where score.sno=student.sno and student.sname='刘东明') /**********End**********/第6关：子查询-练习四
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select distinct score.sno from score group by sno having count(cno) >=(select count(cno) from course); /**********End**********/多表查询 – 子查询（二）
第1关：带比较运算符的子查询
USE Company;#请在此处添加实现代码########## Begin ###########1.查询大于所有平均年龄的员工姓名与年龄 selectname, age from tb_emp where age > (select avg(age) from tb_emp );第2关：关键字子查询
USE Company;#请在此处添加实现代码########## Begin ###########1.使用 ALL 关键字进行查询select position,salary from tb_salary where salary >(select max(salary) from tb_salary where position="Java");#2.使用 ANY 关键字进行查询select position,salary from tb_salary where salary >(select min(salary) from tb_salary where position="Java");#3.使用 IN 关键字进行查询select position,salary from tb_salary where position="Java";########## End ##########多表查询-连接查询（一）
第1关：自然连接
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select student.sname,course.cname,teach.tname,score.grade from student,score,course,teach where student.sno=score.sno and score.tno=teach.tno and score.cno=course.cno and teach.tname="严敏" and course.cname="数学分析"; /**********End**********/第2关：等值连接
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select student.sname,course.cname,teach.tname,score.grade from student,score,course,teach where student.sno=score.sno and score.tno=teach.tno and score.cno=course.cno and teach.tname="严敏" and course.cname="数学分析"; /**********End**********/第3关：JOIN连接
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select student.sname,course.cname,teach.tname,score.grade from student,score,course,teach where student.sno=score.sno and score.tno=teach.tno and score.cno=course.cno and teach.tname="严敏" and course.cname="数学分析"; /**********End**********/第4关：自身连接查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select s2.sname,s2.birthday from student s1,student s2 where s1.sname="刘东明" and s1.birthday>s2.birthday; /**********End**********/第5关：外部连接查询
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/select student.sno,student.snamefrom student left join score on student.sno=score.sno where score.sno is null;/**********End**********/多表查询- 连接查询（二）
第1关：内连接查询
USE School;########## 查询数据表中学生姓名和对应的班级 ###########请在此处添加实现代码########## Begin ##########select tb_student.name studentName,tb_class.name className from tb_student,tb_class where tb_student.class_id=tb_class.id ########## End ##########第2关：外连接查询
USE School;########## 使用左外连接查询所有学生姓名和对应的班级 ###########请在此处添加实现代码########## Begin ##########select s1.name studentName,s2.name className from tb_student s1 left join tb_class s2 on s1.class_id=s2.id;########## End #################### 使用右外连接查询所有学生姓名和对应的班级 ###########请在此处添加实现代码########## Begin ##########select s1.name studentName,s2.name className from tb_student s1 right join tb_class s2 on s1.class_id=s2.id; ########## End ##########第3关：复合条件连接查询
USE School;########## 查询所有班级里分数在90分以上的学生的姓名和学生的成绩以及学生所在的班级 ###########请在此处添加实现代码########## Begin ##########select tb_student.name studentName,tb_student.score,tb_class.name className from tb_student,tb_class where tb_student.class_id=tb_class.id and score>90;########## End ##########大学数据库创建与查询实战
第1关：数据库表设计
use universityDB;create table instructor(ID varchar(5) ,primary key(ID),name varchar(20) NOT NULL,dept_name varchar(20) DEFAULT NULL ,salary numeric(8,2)check(salary>29000),foreign key (dept_name) references department(dept_name)on delete set null);create table section(course_id varchar(8),sec_id varchar(8) ,semester varchar(6)check(semester in('Fall','Winter','Spring','Summer')),year numeric(4,0),building varchar(15),room_number varchar(7) ,time_slot_id varchar(4) ,primary key (course_id, sec_id, semester, year), foreign key (course_id) references course(course_id)on delete cascade, foreign key (building, room_number) references classroom(building, room_number));########## End ##########第2关：查询（一）
#********* Begin *********#echo "select namefrom student where dept_name='Biology'; select name from instructor where salary> any(select salary from instructor where dept_name='Biology'); select name,department.dept_name,building from instructor ,department where instructor.dept_name=department.dept_name; select distinct instructor.dept_namefrom instructor,department where instructor.dept_name=department.dept_name and building='Watson'; "#********* End *********#第3关：查询（二）
#********* Begin *********#echo "select count(distinct ID) from teaches where semester ='spring' and year='2010';select instructor.ID,name,dept_name from teaches,instructor where semester='Fall'and year='2009' and teaches.ID=instructor.ID group by ID having count(distinct course_id)=2;select dept_name, count(distinct instructor.ID) as instr_count from instructor,teaches where semester='Spring' and year='2010' and instructor.ID=teaches.ID group by dept_name having count(distinct course_id)>=1"#********* End *********#第4关：查询（三）
#********* Begin *********#echo "select instructor.* from instructor order by salary desc,name asc ;select max(salary) from instructor;select dept_name from instructor group by dept_name having avg(salary)=(select max(avgs) from(select avg(salary) avgs,dept_namefrom instructor group by dept_name) a);select dept_name, avg(salary) as avg_salary from instructor group by dept_name having avg(salary)>50000order by dept_name asc,avg(salary) desc;"#********* End *********#第5关：查询（四）
#********* Begin *********#echo "select distinct course_id from section where semester='Fall' and year=2009 andcourse_id not in (select course_id from section where semester='Spring' andyear=2010); select course_id from section as S where year=2009 and semester='Fall' and exists(select section.* from section as T where year=2010 and semester='Spring' andS.course_id=T.course_id); select course_id from section where year=2009 and semester='Fall' union all selectcourse_id from section where year=2010 and semester='Spring'; select course_id,semester,year,sec_id,avg(tot_cred) from takes join student join department on takes.ID = student.ID where year=2009 group by course_id,semester,year,sec_id having count(takes.ID)>=13;"#********* End *********#第6关：查询（五）
#********* Begin *********#echo "select instructor.name,teaches.course_id from instructor,teaches where instructor.ID=teaches.ID and dept_name='Physics'; select name from instructor where dept_name='Physics' and salary>80000;update instructor set salary = case when salary <= 100000 then salary*1.05 else salary*1.03 end;create view Physics_fall_2009 as select course.course_id,building,room_number from course,section where course.course_id = section.course_id and course.dept_name='Physics' and section.semester='Fall' and section.year='2009';"#********* End *********#实训四:索引与视图(2课时)
MySQL开发技巧 – 索引
第1关：索引
use School;#请在此处添加实现代码########## Begin ###########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) );#2.创建名为idx_age的普通索引create index idx_age on student(age);#3.创建名为uniq_classes的唯一索引create unique index uniq_classes on student(classes);#4.创建名为idx_group的组合索引create index idx_group on student(name,sex,grade);########## End ##########MySQL开发技巧 – 视图
第1关：视图
use School;#请在此处添加实现代码########## Begin ##########create view stu_view as select math,chinese,math+chinese from student;#1.创建单表视图create view stu_classes as select student.stu_id,name,classes from student,stu_info where student.stu_id=stu_info.stu_id;#2.创建多表视图########## End ##########索引(teachingdb数据库）
第1关：索引
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/create index idx_sname on student(sname); /**********End**********/第2关：删除索引-练习
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/drop index idx_sname; /**********End**********/SQL视图（teachingdb数据库)
第1关：创建视图
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/create view student_cs as select * from student where sdept="计算机"; /**********End**********/第2关：创建视图-练习一
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ create view v_grade_avg as select sno 学号, avg(grade) 平均成绩 from score group by sno; /**********End**********/MySQL-索引和视图
第1关：建立索引
use province; #代码开始alter table jdxx add primary key zsy(sf,cs,qx,name);create index namesy on jdxx(name);#代码结束 show index in jdxx\g;第2关：建立视图并更新视图
use province;#代码开始create view csxx as select qx,name from jdxx where qx in('天心区','宁乡县','岳麓区','开福区','望城县','浏阳市','芙蓉区','长沙县','雨花区');update csxx set name="月湖街道" where name="西湖街道" and qx='开福区';#代码结束select * from csxx;第3关：建立基于多表的视图
use province #代码开始 create view csbm as select jdxx.qx,jdxx.name,qxyzbm.qxbm from jdxx,qxyzbm where jdxx.qx=qxyzbm.qx and jdxx.cs='长沙市'; #代码结束 select * from csbm;实训五:数据库安全控制(2课时）
创建用户
第1关：创建用户
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/create user user1 @localhost identified by 'user1';/**********End**********/第2关：创建用户-练习!!!
/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/create user user2 identified by 'user2'; /**********End**********/授权及回收权限
第1关：授权
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/GRANT USAGE ON *.* TO 'user1'@'localhost' ;GRANT ALL PRIVILEGES ON `teachingdb2`.* TO 'user1'@'localhost'; /**********End**********/第2关：授权-练习一
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ grant select on teachingdb.* to user1@localhost with grant option;/**********End**********/第3关：授权-练习二
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/#grant all on teachingdb to user1@localhost, user2@localhost;grant all on student to user1@localhost, user2@localhost; /**********End**********/第4关：授权-练习三
use teachingdb;/****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/#grant update(grade) on score to user2@localhost; /**********End**********/第5关：回收权限
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/revoke select on teachingdb.* from user1@localhost; /**********End**********/MySQL-安全性控制
第1关：用户和权限
# 请填写语句，完成以下功能：#(1) 创建用户tom和jerry，初始密码均为'123456'；create user tom identified by '123456';create user jerry identified by '123456';#(2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限；grant 权限[,权限] ... on 数据库对象 to user|role,[user|role]... [with grant option]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;#revoke 权限[,权限]... on 数据库对象 from user|role[,user|role]... 第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;MySQL数据库 – 授权与撤销授权
第1关：数据库授权
##########开始编写 SQL####################开始编写 SQL##########-- set password for casual_user@localhost=password('123456');-- grant select,insert,update on *.* to-- casual_user@localhost identified by'123456';-- grant select,insert,updateon *.* to casual_user@'localhost' identified by "123123";set password for casual_user@'localhost' = Password('123456');grant select,insert,update on *.* to casual_user@'localhost' identified by "123456";第2关：数据库撤销权限
##########开始编写 SQL##########-- 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';-- revoke all on mydb1.table1 from 'user1'@'ip';revoke all on mydb1.table1 from user1@'localhost';revoke all on mydb1.table2 from user1@'localhost';revoke select,update,insert,create,delete,alter on mydb2.* from user2@'localhost';实训六:数据完整性
实训七:存储过程与触发器(4课时）
存储过程、函数与触发器
第1关：创建存储过程
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter $$create procedure pro_findname(in name char(3))Beginselect * from student where sname like concat ('%',name,'%');End$$delimiter ; /**********End**********/第2关：创建函数-count_credit
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter $create function count_credit(psno char(10)) returns intBegindeclare xx int;select sum(credit) from course where cno in(select cno from score where grade >=60 and sno=psno) into xx;return xx;end$delimiter ; /**********End**********/第3关：存储过程-调用函数count_credit
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/ delimiter // create procedure p_count_credit() Begin declare flag boolean default true; declare psno char(5); declare stu_cur cursor for select sno from student; declare continue handler for not found set flag=false; open stu_cur; while flag do fetch stu_cur into psno; update student set totalcredit=count_credit(psno) where sno=psno; end while; close stu_cur; end //delimiter ; /**********End**********/第4关：创建触发器-计算总学分!!!
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter $$drop trigger if exists sum_credit$$create trigger sum_credit after insert on scorefor each rowBeginupdate student set totalcredit=totalcredit(select credit from course where cno=new.cno)where sno=new.sno and new.grade>=60;end $$delimiter ; /**********End**********/第5关：创建触发器-练习级联删除操作
use teachingdb; /****请在此编写代码，操作完毕之后点击评测******//**********Begin**********/delimiter $$drop trigger if exists del_student_score$$create trigger del_student_score before deleteon studentfor each rowBegindelete from score where sno=old.sno;end$$delimiter ; /**********End**********/存储过程
第1关：建立和调用存储过程（不带输出参数的存储过程）
use province; #代码开始 #定义过程 #调用过程use province;#代码开始delimiter $$create procedure dqxx(in city varchar(10),in district varchar(10)) begin declare x int;declare jd int;declare z int;declare qt int;select count(name) from jdxx where cs = city and qxmc = district and name like "%乡" into x;select count(name) from jdxx where cs = city and qxmc = district and name like "%街道" into jd;select count(name) from jdxx where cs = city and qxmc = district and name like "%镇" into z;select count(name) from jdxx where cs = city and qxmc = district and name not like "%镇" and name not like "%街道" and name not like "%乡" into qt;select x 乡, jd 街道, z 镇, qt 其他;end $$delimiter ;call dqxx("长沙市", "开福区");call dqxx("厦门市", "同安区");#代码结束 #代码结束第2关：建立和调用存储过程（带输出参数）
use sale; #代码开始 #定义过程 #调用过程use sale;#代码开始delimiter $$create procedure ygyj(in nf int, in yf int, in xm varchar(10), out pj varchar(10))begindeclare jg int;select sum(sjfk) from xsd join gzry on gzry.gyh = xsd.gyh where year(xsrq) = nf and month(xsrq) = yf and gyxm = xm into jg;casewhen isnull(jg) then set pj = "无业绩";when jg < 5000 then set pj = "不达标";when jg < 10000 then set pj = "达标";else set pj = "优秀";end case;end $$delimiter ;call ygyj(2015, 7, "王雅静", @yj1);call ygyj(2015, 6, "廖秉娴", @yj2);call ygyj(2015, 7, "赵敏", @yj3);call ygyj(2015, 7, "章伟", @yj4);#代码结束select @yj1,@yj2,@yj3,@yj4;第3关：建立和调用存储函数
use sale;#代码开始#函数定义#调用函数use sale;#代码开始delimiter $$create function gkjb(nf int, xm varchar(10))returns varchar(10)DETERMINISTICbegindeclare jg int;declare pj varchar(10);select sum(sjfk)from xsd join gk on gk.hyh = xsd.hyh where name = xm and year(xsrq) = nf into jg;casewhen isnull(jg) thenset pj = "非会员";when jg < 5000 thenset pj = "一般会员";when jg < 10000 thenset pj = "vip";else set pj = "超级vip";end case;return pj;end $$delimiter ;select name 姓名, gkjb(2015, name) 等级 from gk;#代码结束第4关：修改多个数据表的存储过程
use library; #代码开始 #定义过程 #调用过程use library;#代码开始delimiter $$create procedure hs(in sh varchar(8), in dzbh varchar(3), in rq date, out zt varchar(12))begindeclare jywh int;select count(*) from borrow where txm=sh and dzzh=dzbh and isnull(hsrq) into jywh;if jywh=0 thenset zt = "没有该借阅";elseupdate borrow set hsrq=rq where dzzh=dzbh and txm=sh and isnull(hsrq);update book set zk=1 where txm=sh;set zt = "还书成功";end if;end $$delimiter ;call hs("P0000001", "001", "2022-5-1", @zt1);call hs("P0000001", "002", "2022-5-1", @zt2);#代码结束select @zt1,@zt2;select txm, sm, zk from book;select * from borrow;第5关：使用游标的存储过程
use province; #代码开始use province;#代码开始delimiter $$create procedure tjdq(in sm varchar(10))begindeclare flag int default 1;declare city varchar(10);declare qx varchar(10);declare jd int;declare x int;declare z int;declare qt int;declare dq cursor for select distinct cs, qxmc from jdxx where sf = sm;declare continue handler for not found set flag = 0;delete from dqtj;open dq;fetch dq into city, qx;while flag = 1 doselect count(*) from jdxx where cs = city and qxmc = qx and name like "%街道" into jd;select count(*) from jdxx where cs = city and qxmc = qx and name like "%乡" into x;select count(*) from jdxx where cs = city and qxmc = qx and name like "%镇" into z;select count(*) from jdxx where cs = city and qxmc = qx and name not like "%镇" and name not like "%街道" and name not like "%乡" into qt;insert into dqtj values(city, qx, x, jd, z, qt);fetch dq into city, qx;end while;close dq;end $$delimiter ;call tjdq("安徽省");#代码结束select * from dqtj;