TOC
實驗3 資料庫的簡單查詢和連接查詢
- 實驗目的
- 掌握無條件查詢的使用方法，
- 掌握條件查詢的使用方法，
- 掌握庫函式及匯總查詢的使用方法，
- 掌握分組查詢的使用方法，
- 掌握查詢的排序方法，
- 掌握連接查詢的使用方法，
- 實驗內容 根據第一部分實驗中創建的學生作業管理資料庫以及其中的學生表、課程表和學生作業表，進行以下的查詢操作（每一個查詢都要給出SQL陳述句，列出查詢結果），
- 查詢各位學生的學號、班級和姓名，
- 查詢課程的全部資訊，
- 查詢資料庫中有哪些專業班級，
- 查詢學時數大于60的課程資訊，
- 查詢在1986年出生的學生的學號、姓名和出生日期，
- 查詢三次作業的成績都在80分以上的學號、課程號，
- 查詢姓張的學生的學號、姓名和專業班級，
- 查詢05級的男生資訊，
- 查詢沒有作業成績的學號和課程號，
- 查詢學號為0538的學生的作業1總分，
- 查詢選修了K001課程的學生人數，
- 查詢資料庫中共有多少個班級，
- 查詢選修三門以上（含三門）課程的學生的學號和作業1平均分、作業2平均分和作業3平均分，
- 查詢于蘭蘭的選課資訊，列出學號、姓名、課程名（使用兩種連接查詢的方式），
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 資料庫查詢和資料操縱
- 實驗目的
- 掌握各種查詢的使用方法，
- 掌握資料操縱的使用方法，
- 實驗內容 根據第一部分實驗中創建的學生作業管理資料庫以及其中的學生表、課程表和學生作業表，進行以下操作，
- 使用查詢陳述句完成以下任務（每一個查詢都要給出SQL陳述句，并且列出查詢結果），
- 查詢與“張志國”同一班級的學生資訊（使用連接查詢和子查詢方式），
- 查詢比“計算機應用基礎”學時多的課程資訊（使用連接查詢和子查詢方式），
- 查詢選修課程號為K002的學生的學號、姓名（使用連接查詢、普通子查詢、相關子查詢、使用exists關鍵字的相關子查詢），
- 查詢沒有選修K001和M001課程的學號、課程號和三次成績（使用子查詢），
- 使用資料操縱完成以下任務（每一個任務都要給出SQL陳述句，并且列出查詢結果），
- 在學生表中添加一條學生記錄，其中，學號為0593，姓名為張樂，性別為男，專業班級為電子05，
- 將所有課程的學分數變為原來的兩倍，
- 洗掉張樂的資訊，
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行所示）
*/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/2536.html
標籤：SQL Server