資料庫實驗系列之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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類