实验3 数据库的简单查询和连接查询

• 实验目的

1. 掌握无条件查询的使用方法。
2. 掌握条件查询的使用方法。
3. 掌握库函数及汇总查询的使用方法。
4. 掌握分组查询的使用方法。
5. 掌握查询的排序方法。
6. 掌握连接查询的使用方法。

• 实验内容
  根据第一部分实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下的查询操作（每一个查询都要给出SQL语句，列出查询结果）。

1. 查询各位学生的学号、班级和姓名。
2. 查询课程的全部信息。
3. 查询数据库中有哪些专业班级。
4. 查询学时数大于60的课程信息。
5. 查询在1986年出生的学生的学号、姓名和出生日期。
6. 查询三次作业的成绩都在80分以上的学号、课程号。
7. 查询姓张的学生的学号、姓名和专业班级。
8. 查询05级的男生信息。
9. 查询没有作业成绩的学号和课程号。
10. 查询学号为0538的学生的作业1总分。
11. 查询选修了K001课程的学生人数。
12. 查询数据库中共有多少个班级。
13. 查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。
14. 查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。

SQL语句代码

/* 1．查询各位学生的学号、班级和姓名。 2．查询课程的全部信息。 3．查询数据库中有哪些专业班级。 4．查询学时数大于60的课程信息。 5．查询在1986年出生的学生的学号、姓名和出生日期。 6．查询三次作业的成绩都在80分以上的学号、课程号。 7．查询姓张的学生的学号、姓名和专业班级。 8．查询05级的男生信息。 9．查询没有作业成绩的学号和课程号。 10．查询学号为0538的学生的作业1总分。 11．查询选修了K001课程的学生人数。 12．查询数据库中共有多少个班级。 13．查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。 14．查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。  */ select * from 学生表; select * from 课程表; select * from 学生作业表; --1.查询各位学生的学号、班级和姓名。 select distinct 学号,姓名,专业班级 from 学生表,课程表; --2.查询课程的全部信息。 select * from 课程表; --3．查询数据库中有哪些专业班级。 select 专业班级 from 学生表; --4．查询学时数大于60的课程信息。 select * from 课程表 where 学时数>60; --5．查询在1986年出生的学生的学号、姓名和出生日期。 select 学号,姓名,出生日期 from 学生表 where 出生日期 like '1986%'; --6．查询三次作业的成绩都在80分以上的学号、课程号。 select 学号,课程号 from 学生作业表 where 作业1成绩>=80 and 作业2成绩>=80 and 作业3成绩 >=80; --7．查询姓张的学生的学号、姓名和专业班级。 select 学号,姓名,专业班级 from 学生表 where 姓名 like '张%'; --8．查询05级的男生信息。 select * from 学生表 where 专业班级 like '%05' and 性别 in ('男'); --9．查询没有作业成绩的学号和课程号。 select 学号,课程号 from 学生作业表 where 作业1成绩 | 作业2成绩| 作业3成绩 is null; --10．查询学号为0538的学生的作业1总分。 select sum(作业1成绩) as '学号为0538的学生的作业1总分' from 学生作业表 where 学号 in (0538); --11．查询选修了K001课程的学生人数。 select distinct count(学号) as '选修了K001课程的学生人数' from 学生作业表  where 课程号 in ('K001'); --12．查询数据库中共有多少个班级。 select count(distinct 专业班级) as '数据库中共有多少个班级' from 学生表; --13．查询选修三门以上（含三门）课程的学生的学号和作业1平均分、作业2平均分和作业3平均分。 select 学号,avg(作业1成绩) as '作业1平均分',avg(作业2成绩) as '作业2平均分',avg(作业3成绩) as '作业3平均分' from 学生作业表    where 学号 in (select 学号 from 学生作业表 group by 学号 having (count(*)>2)) group by 学号; /* SELECT 学生作业表.学号,AVG(作业1成绩) as '作业1平均分',AVG(作业2成绩) as '作业2平均分',AVG(作业3成绩) as '作业3平均分' FROM 学生表,学生作业表 WHERE  学生表.学号 = 学生作业表.学号  GROUP BY 学生作业表.学号 HAVING COUNT(*) >2; */ --14．查询于兰兰的选课信息，列出学号、姓名、课程名（使用两种连接查询的方式）。  --第一种 select   学生表.学号,姓名,课程名 from   学生表,课程表,学生作业表   where 学生表.学号=学生作业表.学号 and   课程表.课程号=学生作业表.课程号 and   姓名 in ('于兰兰'); --第二种 select 学生表.学号,姓名,课程名 from 学生表 left join (select 学号,课程名 from 课程表 join 学生作业表 on 课程表.课程号=学生作业表.课程号) as 连接表  on  学生表.学号=连接表.学号 where 姓名='于兰兰' ; 

实验4 数据库查询和数据操纵

• 实验目的

1. 掌握各种查询的使用方法。
2. 掌握数据操纵的使用方法。

• 实验内容
  根据第一部分实验中创建的学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作。

1. 使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。

1. 查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。
2. 查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。
3. 查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。
4. 查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。

2. 使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。

1. 在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。
2. 将所有课程的学分数变为原来的两倍。
3. 删除张乐的信息。

SQL语句代码

--1．使用查询语句完成以下任务（每一个查询都要给出SQL语句，并且列出查询结果）。 --（1）查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。 --子查询 select * from 学生表  WHERE 专业班级 = (SELECT 专业班级                FROM 学生表                 WHERE 姓名 = '张志国'); --连接查询 select 学生表1.* from  学生表 as 学生表1,学生表 as 学生表2 where 学生表2.姓名='张志国' AND 学生表1.专业班级=学生表2.专业班级; --（2）查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。 --连接查询 SELECT 课程表1.*  FROM 课程表 AS 课程表1,课程表 AS 课程表2  WHERE 课程表2.课程名 = '计算机应用基础' AND 课程表1.学时数>课程表2.学时数;  --子查询 SELECT * FROM 课程表   WHERE 学时数>(SELECT 学时数      FROM 课程表                 WHERE 课程名 = '计算机应用基础'); --（3）查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。 --连接查询1 select 学生表.学号,姓名 from 学生表 join (select  学号 from 学生作业表  join 课程表    on 课程表.课程号='K002'and 学生作业表.课程号='K002' ) as 新命名表  on 新命名表.学号=学生表.学号; --连接查询2 SELECT 学生表.学号,学生表.姓名 FROM 学生作业表,学生表  WHERE 学生作业表.课程号 = 'K002' AND 学生作业表.学号=学生表.学号; --普通子查询 select distinct 学生表.学号,学生表.姓名 from 学生作业表,学生表 where 学生表.学号 in ( select 学生表.学号  from 学生作业表  where 学生作业表.课程号='K002' and 学生作业表.学号=学生表.学号 ); --相关子查询 select distinct 学生表.学号,学生表.姓名 from 学生表,学生作业表 where 学生表.学号 in ( select 学生作业表.学号  from 学生作业表  where 学生作业表.课程号='K002' ); --使用exists关键字的相关子查询 select distinct 学生表.学号,学生表.姓名 from 学生作业表,学生表 where exists ( select * from 学生作业表  where 学生作业表.课程号='K002' and 学生作业表.学号=学生表.学号 ); --select * from 学生表 --select * from 课程表 --select * from 学生作业表 --（4）查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。 select distinct 学号,课程号,作业1成绩,作业2成绩,作业3成绩 from 学生作业表 where 学号 not in (select 学号 from 学生作业表 where 课程号='K001' OR 课程号='M001'); --2．使用数据操纵完成以下任务（每一个任务都要给出SQL语句，并且列出查询结果）。 --（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。 insert into 学生表(学号,姓名,性别,专业班级) values (0593,'张乐','男','电子05'); --（2）将所有课程的学分数变为原来的两倍。 update 课程表 SET  学分数=学分数*2; --（3）删除张乐的信息。 delete from  学生表 where 姓名='张乐'; --select * from 课程表; --select * from 学生表; 

实验8：数据库备份与恢复实验

用数据库备份语句完成数据库的完全备份，能用DTS工具对表的数据进行导出和导入，能用备份文件恢复数据库。

注意：为演示导入导出，备份恢复，特意创建一个aoe数据库（和之前的market数据库基本一致），用来演示

SQL语句代码

--先创建一个aoe数据库，用来演示导入导出，备份恢复 create database aoe; use aoe; create table Customers( --客户表  CustomerID int constraint pk_1 primary key, --客户编号为主键  Cname nvarchar(20) not null, --客户姓名  City nvarchar(20) --所在城市 ); create table Goods( --商品表  GoodID int constraint pk_2 primary key, --商品编号为主键   Gname nvarchar(20) not null, --商品名称  Price float not null, --单价  Provider nvarchar(20) not null,--供应商  Stocks int check(Stocks>=0), --库存量 Status nvarchar(20) --商品状态 ); create table Orders( --订单表  OrderID  int constraint  pk_3 primary key, --订单号  GoodID int constraint fk_1 foreign key references Goods(GoodID), --商品编号  CustomerID int constraint fk_2 foreign key references Customers(CustomerID),--客户编号  Quantity int check(Quantity>0),--订货数量  OrderSum float ,--订货金额 Date nvarchar(20) --日期 ); --插入两条数据 insert into Customers (CustomerID,Cname,City) values (2,'李勇','西安'); insert into Customers (CustomerID,Cname,City) values (1,'阿纲','西安'); --使用DTS     数据转换服务,导入导出数据（具体操作实验报告展示） /*可能遇到的问题： 1、没有数据源（重要）： 解决方法：去(控制面板)搜索(ODBC 数据源)然后新添加由SQLSERVER驱动程序组成的数据源（记得将自己设置的名称记下） 然后再导入导出向导的帮助下可以继续进行操作 2、关于数据源使用Microsoft OLE DB Driver for SQL Server还是Microsoft OLE DB Provider for SQL Server？ 区别是前者是 ODBC 连接，后者是OLE DB连接 --这个设计到一些历史沿革问题：最初每个厂商标准不一样，后来出现同一标准ODBC，微软又推出自己的Sql Server的oledb, 为了照顾使用者，微软给了个odbc接口，用户使用后内部进行OLE DC转化，再转化执行，因此多一道转化，耗时些 3、我第一次导出数据时，因不熟练导出的数据不知道位置 后来发现在系统数据库master中 */ select * from Customers; --查看发现导入数据成功 --补充知识点：ODBC：一般指开放数据库连接（Open Database Connectivity，ODBC）是为解决异构数据库间的数据共享而产生的。 --备份和恢复图形化界面操作方法（手动操作很简单，根据提示进行） --SQL语句方法展示 --1、备份： DECLARE @filename VARCHAR(255); --文件名 DECLARE @date DATETIME; --日期 DECLARE @path VARCHAR(255); --文件存放路径 SELECT @date = GETDATE(); --获取当前时间 SET @filename = CAST(DATEPART(yyyy, @date) AS VARCHAR) + '-' + CAST(DATEPART(mm, @date) AS VARCHAR) + '-' + CAST(DATEPART(dd, @date) AS VARCHAR) +'-' + CAST(DATEPART(hh,@date) as varchar)+CAST(DATEPART(mi,@date) as varchar)+'.bak'; --设置的文件名是用时间命名的（好处：下次备份名字不会重复） set @path='D:SqlServer备份文件示例'+@filename; backup database aoe to disk=@path; /*可能遇到的问题： 1.备份的磁盘显示操作系统权限不够5（重要） 解决办法1：去（计算机）右键点击选择（属性）选择（安全）修改用户的权限（这种办法比较耗时，且对磁盘不安全） 解决办法2：在盘中新建文件夹，查看属性中安全是否允许写入，如果允许，就用这个文件夹路径 */ --2、还原： drop database aoe; --先删除后还原 restore database aoe from disk='D:SqlServer备份文件示例2020-5-17-1416.bak' WITH REPLACE; /*可能遇到的问题：日志文件没有备份（重要） 解决方法：在还原语句后加WITH REPLACE（如56行所示） */