資料庫實驗系列之2資料庫上的基本操作實驗（針對錶、視圖及不同許可權用戶的增、刪、改、查及資料庫的備份和恢復）

来源:https://www.cnblogs.com/wanggang9968/archive/2020/05/18/12908741.html
-Advertisement-
Play Games

資料庫實驗系列之2資料庫上的基本操作實驗（針對錶、視圖及不同許可權用戶的增、刪、改、查及資料庫的備份和恢復） ...


目錄

實驗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課程的學號、課程號和三次成績（使用子查詢）。
  1. 使用數據操縱完成以下任務（每一個任務都要給出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行所示）
*/

在這裡插入圖片描述在這裡插入圖片描述在這裡插入圖片描述在這裡插入圖片描述在這裡插入圖片描述在這裡插入圖片描述在這裡插入圖片描述


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux 計劃任務摘要
    Linux計劃任務中對應的時間含義及指令 指令 : --linux定時任務 crontab -e # 創建自己的一個任務調度，此時會進入到vi編輯界面，來編寫我們要調度的任務 crontab -l # 列出定時的任務 時間對應關係 : 55 7 * * * csh -c "/home/dpower/ ...
  • 安裝和使用ArchLiunx超詳細教程
    安裝系統 一、下載介質 https://www.archlinux.org/download/ 二、啟動 ISO 到Live 環境 此步驟由很多種方式： 製作ISO為U盤啟動工具，可以使用 Ultra ISO 或 大白菜 有Linux/Unix系統的，可以硬碟寫入Grub，製作啟動項 三、安裝前的準 ...
  • Redis學習筆記（十） 客戶端
    Redis伺服器是典型的一對多伺服器程式：一個伺服器可以與多個客戶端建立網路連接，每個客戶端可以向伺服器發送命令請求，而伺服器則接收並處理客戶端發送的命令請求，並向客戶端返回命令回覆。 通過使用由I/O多路復用技術實現的文件事件處理器，Redis伺服器使用單線程單進程的方式處理命令請求，並於多個客戶 ...
  • 【漫畫】CAS原理分析！無鎖原子類也能解決併發問題！
    本文來源於微信公眾號【胖滾豬學編程】、轉載請註明出處 在漫畫併發編程系統博文中，我們講了N篇關於鎖的知識，確實，鎖是解決併發問題的萬能鑰匙，可是併發問題只有鎖能解決嗎？今天要出場一個大BOSS：CAS無鎖演算法，可謂是併發編程核心中的核心！ 溫故 首先我們再回顧一下原子性問題的原因，參考 "【漫畫】J ...
  • SQL子查詢的感悟
    今天在聽陳華軍老師的課時；感觸頗多。其中講到“不同執行計劃的選擇（子查詢）”這一欄。我們在平時工作也經常要用到子查詢。有哪些思路來優化這種子查詢呢？ 例如我們今天實驗的表結構 表T1 有10000條記錄;併在id欄位創建btree索引 表T2 有1000條記錄 postgres=# create t ...
  • 寶塔部署war包項目，出現SQLException: Access denied for user
    新公司使用的是寶塔來部署項目，war包。在部署運行時遇到了SQLException: Access denied for user 'xxx@xxxxx' (using password:yes) 重裝mysql無果之後。就只能慢慢解決，分享下我的解決過程。 # 一. 錯誤原因 正常來講，使用nav ...
  • mysql索引解析
    1、基本概念 數據讀寫性能主要是IO次數，單次從磁碟讀取單位是頁，即便只讀取一行記錄，從磁碟中也是會讀取一頁的（）單頁讀取代價高，一般都會進行預讀） （1）扇區是磁碟的最小存儲單元 （2）塊是文件系統的最小存儲單元,比如你保存一個記事本，即使只輸入一個字元，也要占用4KB的存儲，這就是最小存儲的意思 ...
  • 資料庫實驗系列之3存儲過程和觸發器實驗（存儲過程和觸發器）
    "TOC" 實驗9：存儲過程實驗 自擬題目完成8個存儲過程的編寫及調試，熟練掌握存儲過程的使用。也可採用下圖中作業上的題目。 SQL語句代碼 實驗10：觸發器實驗 自擬題目完成5個觸發器的編寫及調試，熟練掌握觸發器的使用。也可採用下圖中作業上的題目。 SQL語句代碼 ...
一周排行
    -Advertisement-
    Play Games
  • gRPC入門學習之旅（六）
    JWT（JSON Web Token）是一種用於在網路應用之間傳遞信息的開放標準（RFC 7519）。它使用 JSON 對象在安全可靠的方式下傳遞信息，通常用於身份驗證和信息交換。 在Web API中，JWT通常用於對用戶進行身份驗證和授權。當用戶登錄成功後，伺服器會生成一個Token並返回給客戶端 ...
  • [譯] .NET 8 中的硬體內在函數（支持 Wasm 和 AVX-512）
    老周在幾個世紀前曾寫過樹莓派相關的 iOT 水文，之所以沒寫 Nano Framework 相關的內容，是因為那時候這貨還不成熟，可玩性不高。不過，這貨現在已經相對完善，老周都把它用在項目上了——第一個是自製的智能插座，這個某寶上50多塊可以買到，搜“esp32 插座”就能找到。一種是 86 型盒子 ...
  • 實戰指南：使用 xUnit.DependencyInjection 在單元測試中實現依賴註入【完整教程】
    引言 上一篇我們創建了一個Sample.Api項目和Sample.Repository，並且帶大家熟悉了一下Moq的概念，這一章我們來實戰一下在xUnit項目使用依賴註入。 Xunit.DependencyInjection Xunit.DependencyInjection 是一個用於 xUnit ...
  • Avalonia 中的樣式和控制項主題
    在 Avalonia 中，樣式是定義控制項外觀的一種方式，而控制項主題則是一組樣式和資源，用於定義應用程式的整體外觀和感覺。本文將深入探討這些概念，並提供示例代碼以幫助您更好地理解它們。 樣式是什麼？ 樣式是一組屬性，用於定義控制項的外觀。它們可以包括背景色、邊框、字體樣式等。在 Avalonia 中，樣 ...
  • C# 凍結Excel視窗以鎖定行列、或解除凍結
    在處理大型Excel工作簿時，有時候我們需要在工作表中凍結窗格，這樣可以在滾動查看數據的同時保持某些行或列固定不動。凍結窗格可以幫助我們更容易地導航和理解複雜的數據集。相反，當你不需要凍結窗格時，你可能需要解凍它們以獲得完整的視野。 下麵將介紹如何使用免費.NET庫通過C#實現凍結Excel視窗以鎖 ...
  • .NET Core 8 部署在 IIS 的簡單三步
    .NET 部署 IIS 的簡單步驟一： 下載 dotnet-hosting-x.y.z-win.exe ，下載地址：.NET Downloads (Linux, macOS, and Windows) (microsoft.com) .NET 部署 IIS 的簡單步驟二： 選擇對應的版本，點擊進入詳 ...
  • PowerDesigner 業務流程/數據建模軟體和元數據管理解決方案
    拓展閱讀 資料庫設計工具-08-概覽 資料庫設計工具-08-powerdesigner 資料庫設計工具-09-mysql workbench 資料庫設計工具-10-dbdesign 資料庫設計工具-11-dbeaver 資料庫設計工具-12-pgmodeler 資料庫設計工具-13-erdplus ...
  • STL筆記 之 vector
    初識STL STL，(Standard Template Library)，即"標準模板庫"，由惠普實驗室開發，STL中提供了非常多對信息學奧賽很有用的東西。 vector vetor是STL中的一個容器，可以看作一個不定長的數組，其基本形式為: vector<數據類型> 名字; 如: vector ...
  • Flask 生產環境部署（Falsk + uWSGI + nginx）
    前言 最近自己做了個 Falsk 小項目，在部署上伺服器的時候，發現雖然不乏相關教程，但大多都是將自己項目代碼複製出來，不講核心邏輯，不太簡潔，於是將自己部署的經驗寫成內容分享出來。 uWSGI 簡介 uWSGI: 一種實現了多種協議（包括 uwsgi、http）並能提供伺服器搭建功能的 Pytho ...
  • 技術周刊的轉變：如何平衡熱愛與現實？
    1 文本Embedding 將整個文本轉化為實數向量的技術。 Embedding優點是可將離散的詞語或句子轉化為連續的向量，就可用數學方法來處理詞語或句子，捕捉到文本的語義信息，文本和文本的關係信息。 ◉ 優質的Embedding通常會讓語義相似的文本在空間中彼此接近 ◉ 優質的Embedding相 ...
所有分類