大二資料庫實驗報告

實驗要求：

實驗一熟悉資料庫管理系統環境

實驗二SQL定義語言

實驗三使用SQL語言進行簡單查詢

實驗四使用SQL語言進行複雜查詢

實驗五 SQL常用資料更新操作

實驗六綜合應用\

實驗一：熟悉資料庫管理系統環境

實驗過程及分析：

首先建立一個資料庫和需要的表：

create database XSGL
GO
use XSGL
GO
Create table student      --建立學生表
(sno char(8) primary key,        --(實體完整性)學生姓名
 sname char(8) not null unique, --學生姓名
 ssex char(2) default '男' check(ssex='男' or ssex='女'),  --性别給定預設值為'男'，取值隻能取‘男’或‘女’
 sage tinyint check(sage>13 and sage<50),
 sdept char(20))


create table course       --建立課程表 
(cno char(2) PRimary key,        --課程編号
 cname varchar(50),  --課程名稱
 cpno char(2),       --先修課号
 ccredit tinyint)  --課程名

create table sc        --建立成績表
(sno char(8),           --學生學号
 cno char(2),         --課程編号
 grade tinyint,           --成績
 constraint pk_grade primary key(sno,cno),
 constraint fk_stuid foreign key(sno) references student(sno),
 constraint fk_course foreign key(cno) references course(cno),
 constraint ck_grade check(grade>=0 and grade<=100) )
go
insert into student(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS')
insert into student(sno,sname, ssex,sage,sdept) values('95002', '劉晨', '女', 19, 'IS')
insert into student(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA')
insert into student(sno,sname, ssex,sage,sdept) values('95004', '張立', '男', 19, 'IS')
insert into student(sno,sname, ssex,sage,sdept) values('95005', '劉雲', '女', 18, 'CS ')
insert into course(cno, cname,ccredit,cpno) values('1', '資料庫', 4, '5')
insert into course(cno, cname,ccredit,cpno) values('2', '數學', 6, null)
insert into course(cno, cname,ccredit,cpno) values('3', '資訊系統', 3, '1')
insert into course(cno, cname,ccredit,cpno) values('4', '作業系統', 4, '6')
insert into course(cno, cname,ccredit,cpno) values('5', '資料結構', 4, '7')
insert into course(cno, cname,ccredit,cpno) values('6', '資料處理', 3, null)
insert into course(cno, cname,ccredit,cpno) values('7', 'PASCAL語言', 4, '6')
insert into sc(sno,cno,grade) values('95001', '1' ,92)
insert into sc(sno,cno,grade) values('95001', '2' ,85)
insert into sc(sno,cno,grade) values('95001', '3' ,88)
insert into sc(sno,cno,grade) values('95002', '2' ,90)
insert into sc(sno,cno,grade) values('95002', '3' ,80)
insert into sc(sno,cno,grade) values('95003', '2' ,85)
insert into sc(sno,cno,grade) values('95004', '1' ,58)
insert into sc(sno,cno,grade) values('95004', '2' ,85)

1）STUDENT表中增加一個字段入學時間scome，

alter table student 
add scome date

2）删除STUDENT表中sdept字段；

alter table student
drop column sdept

3）删除建立的SC表中cno字段和COURSE表cno字段之間的外鍵限制；

alter table sc
DROP fk_course

4）重建3）中删除的限制

alter table sc
add constraint fk_course foreign key(cno) references course(cno)

5、重新定義一個簡單表，然後用SQL語言DROP語句删除該表結構;

drop table sc

6、用SQL語言CREATE INDEX語句定義表STUDENT的SNAME字段的降序唯一索引;

create index index_sname
on student(sname desc)

7、用SQL語言DROP語句删除索引;

drop index index_sname on student

8.我覺得沒有錯誤……

實驗總結：

1. 建立表的時候可以添加限制

2. 可以添加主鍵唯一辨別用primary key

3. 可以使用預設值是 default

4. 可以使用外鍵來限制取值範圍、

5. 使用alter添加，修改列；還可以删除表中限制如索引 index

6. 使用DROP 可以直接删除表删除的時候先要删除外鍵表後才可以删除主鍵表

實驗二： SQL定義語言

實驗過程及分析：

1.首先建立伺服器連接配接如果連接配接無法連接配接到本地local可以參考

sqlserver2008無法連接配接到local解決方案

2.然後打開建立查詢選擇資料庫

3.建立表用SQL語言，具體的過程實驗一已經有了

用SQL語言ALTER語句修改表結構;

（1）STUDENT表中增加一個字段入學時間scome，

（2）删除STUDENT表中sdept字段；

（3）删除建立的SC表中cno字段和COURSE表cno字段之間的外鍵限制；

（4）重建（3）中删除的限制

5、重新定義一個簡單表，然後用SQL語言DROP語句删除該表結構;

6、用SQL語言CREATE INDEX語句定義表STUDENT的SNAME字段的降序唯一索引;

有沒有發現和實驗一重複了…………

沒錯是以為了湊字數我再抄了一遍……

1. 删除外鍵隻能用alter 指定表而不能用on來選擇表……原因不明

2. 删除索引不能用alter 直接用DROP INDEX 索引 ON 表原因不明

實驗三：使用SQL語言進行簡單查詢

實驗過程及分析：

還是使用實驗一的表

（1）查詢全體學生的學号和姓名

select sno,sname 
from student;

（2）查詢全體學生的詳細記錄

select * 
from student;

（3）查詢軟體學院的學生姓名、年齡、系别

select sname,sage,sdept
from student where sdept='MA';

（4）查詢所有選修過課程的學生學号（不重複）

select distinct sno 
from sc;

（5）查詢考試不及格的學生學号（不重複）

select distinct sno
from sc
where grade<60;

（6）查詢不是軟體學院、計算機系的學生性别、年齡、系别

select ssex,sage,sdept
from student 
where sdept not in('CS','MA');

（7）查詢年齡18－20歲的學生學号、姓名、系别、年齡；

select sno,sname,sdept,sage 
from student
where sage>=18 and sage<=20;

（8）查詢姓劉的學生情況

select * 
from student
where sname like '劉%';

（9）查詢姓劉或姓李的學生情況

select * from student where sname like '劉%' or sname like '李%'

（10）查詢姓劉且名字為兩個字的學生情況

select * 
from student
where sname like '劉_';

（11）查詢1983年以後出生的學生姓名。

select sname from student where sage < 2018-1983

（12）建立表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)

計算學生各科總成績并賦予别名

Create table studentgrad(
    Sno char(8) ,
    mathgradeint,
    englishigradeint,
    chinesegradeint
)
Select sum(mathgrade+chinesegrade+englishigrade) '學生總成績' from studentgrad

（13）利用内部函數 year()查找軟體學院學生的出生年份

select (year(getdate())-student.sage+1) 
from student 
where sdept='MA';

（14）利用字元轉換函數實作字元聯接。

select sname + '年齡為'+cast(sage as char(2))+'歲'
from student;
Select sname + ‘年齡為’+cast(sage as char(2))+’歲’
From student

（15）查詢全體學生情況，查詢結果按所在系升序排列，對同一系中的學生按年齡降序排列。

select*
from student order by sdept,sage desc;

（16）查詢學生總人數。

select count(*) 
from student;

（17）查詢選修了課程的學生人數。

select count(distinct sno) 
from sc;

（18）查詢選修了7号課程的學生總人數和平均成績

select count(*),avg(grade)as avggrade 
from student ,sc 
where student.sno=sc.sno and sc.cno='1';

（19）查詢選修6号課程學生的最好成績

select max(grade) as maxgrade 
from sc
where cno='2';

（20）查詢每個系的系名及學生人數。

select sdept,count(*) 
from student group by sdept;

（21）查找每門課的選修人數及平均成績

select cno,count(*),avg(grade) as avggrade 
from sc group by cno;

（22）查找沒有先修課的課程情況

select * 
from course 
where cpno is null;

1. 函數year（），count（），max（）可以友善查詢

2. 模糊查詢法要% 如like ‘劉%’

3. group by 可以分組查詢

實驗四：使用SQL語言進行複雜查詢

實驗過程及分析：

1、實驗一中的資料為基礎

2、對各表中的資料進行不同條件的連接配接查詢和嵌套查詢；

（1）查詢每個學生及其選課情況；

select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno=sc.sno

（2）查詢每門課的間接先修課

select first.cno,second.cpno
from course first,course second
where first.cpno=second.cno

（3）将STUDENT,SC進行右連接配接

select student.sno,sname,ssex,sage,sdept,cno,grade
from student right outer join sc on student.sno=sc.sno

（4）查詢既選修了2号課程又選修了3号課程的學生姓名、學号；

select student.sno,sname
from student inner join sc on student.sno=sc.sno
where cno='3' and sc.sno in
(select sno
from sc
where cno='2')

（5）查詢和劉晨同一年齡的學生

select student.sno,sname
from student
where sname!='劉晨' and sage=
(select sage 
from student
where sname='劉晨')

（6）選修了課程名為“資料庫”的學生姓名和年齡

select sname,sage
from student
where sno in
(select sno
from sc
where cno in
(select cno
from course 
where cname='資料庫'))

（7）查詢其他系比IS系任一學生年齡小的學生名單

select student.sno,sname
from student
where sdept<>'IS' and
sage<any
(select sage 
from student
where sdept='IS')

（8）查詢其他系中比IS系所有學生年齡都小的學生名單

select student.sno,sname
from student
where sdept<>'IS' and 
sage<all
(select sage 
from student 
where sdept='IS')

（9）查詢選修了全部課程的學生姓名

select sname
from student
where Sno in
(select Sno from SC
group by Sno
having count(*) = (select count(*) from course ))

（10）查詢計算機系學生及其性别是男的學生

select student.sno,sname
from student
where sdept='IS' and ssex='男'

（11）查詢選修課程1的學生集合和選修2号課程學生集合的差集

select sno
from sc 
where cno='1' except 
select sno
from sc
where cno='2'

（12）查詢李麗同學不學的課程的課程号

select cno
from course
where cno not in
(select cno
from sc
where sno in
(select sno
from student
where sname='李麗'))

（13）查詢選修了3号課程的學生平均年齡

select AVG(sage) as avgsage
from student inner join sc on student.sno=sc.sno
where cno='3'

（14）求每門課程學生的平均成績

select cno,AVG(grade) as avggrade
from sc
group by cno

（15）統計每門課程的學生選修人數（超過3人的才統計）。要求輸出課程号和選修人數，結果按人數降序排列，若人數相同，按課程号升序排列

select course.cno '課程号', count(sc.sno) '人數'
from course,sc 
where course.cno=sc.cno 
group by course.cno having count(sc.sno)>3 order by COUNT(sc.sno) desc,course.cno asc

（16）查詢學号比劉晨大，而年齡比他小的學生姓名。

select sname
from student
where sno>
(select sno from student where sname='劉晨')and
sage<(select sage from student where sname='劉晨')

（17）求年齡大于所有女同學年齡的男同學姓名和年齡

select sname,sage
from student
where ssex='男'and sage>
(select MAX(sage) from student where ssex='女')

1. 求總數可以用COUNT（）函數

2. 分組group by 要用having來限制條件

3. order by是排序要求 desc是降序，asc是升序

4. any（）函數是任意的意思，all（）是所有

實驗五：SQL的常用資料更新操作

實驗過程及分析：

1、應用INSERT,UPDATE,DELETE語句進行更新操作；

（1）插入如下學生記錄（學号：95030，姓名：李莉，年齡：18）

insert into student(sno,sname,sage)
values ('95030','李莉',18)

（2）插入如下選課記錄（95030，1）

insert into sc(sno,cno)
values('95030',1)

（3）計算機系學生年齡改成20

update student
set sage=20
where sdept='CS'

（4）把數學系所有學生成績改成0

update sc
set grade=0
where 'MA'=
(select sdept
from student
where student.sno=sc.sno)

（5）把低于總平均成績的女同學成績提高5分

update sc 
set grade+=5
where grade<
(select avg(grade) 
from sc inner join student
on student.sno=sc.sno
where ssex='女')

（6）删除95030學生資訊

delete
from student
where sno='95030'

（7）删除SC表中無成績的記錄

delete 
from sc
where grade is null;

（8）删除張娜的選課記錄

delete
from sc
where sno=(select sno from student 
where sname='張娜')

（9）删除不及格的學生選課記錄

delete
from sc
where grade<60

（10）删除數學系所有學生選課記錄

delete
from sc
where sno in (select sno from student where sdept='MA')

（11）删除所有未被選修的課程

delete
from course
where cno not in (select cno from sc)

（12）查詢每一門課程成績都大于等于80分的學生學号、姓名和性别，把值送往另一個已經存在的基本表STU(SNO,SNAME,SSEX)中

Create table STU 
(sno char(8), 
sname char(8), 
ssex char(2) 
)

insert into STU(sno,sname,ssex)
select distinct student.sno,sname,ssex
from student,sc 
where student.sno not in
(select sno from sc where grade<80) and student.sno=sc.sno

（13）建立一個sdeptgrade 表，包含（sdept,avggrade）字段，對每一個系，求學生的成績，并把結果存入sdeptgrade

Create table sdeptgrade 
(sdept char(8) primary key, 
avggrade int; ) 

insert into sdeptgrade 
select student.sdept, avg(sc.grade) 
from student inner join SC on 
(student.sno = SC.sno) group by student.sdept;

1. 删除主鍵表資料如果有外鍵限制就會報錯

2. 插入資料用insert into 表直接+表

3. 更新用update

4. 删除直接用delete 可以直接删除一行資料

實驗六：綜合應用**

實驗過程及分析：

建立一個資料庫和五張表的表結構

首先建立表：

create database Person
GO 
use person
GO
Create table employee
(
emp_no char(5) primary key,
emp_name char(10) not null,
Sex char(1) not null,
Dept char(4) not null,
Title char(6) not null,
data_hired datetime not null,
birthday datetime null,
salary int not null,
Addr char(50) null,
Mod_date datetime Default(getdate())
)
create table customer
(
cust_id char(5) primary key,
cust_name char(20) not null,
Addr char(40) not null,
tel_no char(10) not null,
Zip char(6) null
)
create table sales
(
order_no int primary key,
cust_id char(5) not null,
sale_id char(5) not null,
tot_amt numeric(9,2) not null,
order_date datetime not null,
ship_date datetime not null,
incoice_no char(10) not null
)
create table sale_item
(
order_no int not null,
prod_id char(5) not null,
Qty int not null,
unit_price numeric(9,2) not null,
order_date datetime null
constraint primary_sale primary key(order_no,prod_id)
)
create table product
(
prod_id char(5) not null primary key,
prod_naem char(20) not null
)

3、錄入資料并實作實作如下查詢

（1）查找定單金額高于20000的客戶編号；

select cust_id from sales where tot_amt>2000

（2）選取銷售數量最多的前5條訂單訂單号、數量；

select top 5 order_no,Qty from sale_item order by Qty DESC

（3）顯示sale_item表中每種個别産品的訂購金額總和，并且依據銷售金額由大到小排

來顯示出每一種産品的排行榜；

select prod_id, sum(Qty*unit_price) '金額' from sale_item group by prod_id order by '金額' DESC

（5）計算每一産品每月的銷售金額總和，并将結果按銷售（月份，産品編号）排序；

select "s2".月份,SUM("s2".tot_amt) '銷售金額總和',"s1".prod_id '産品編号'
from sale_item "s1"
join (select MONTH(order_date) '月份',order_no,tot_amt from sales) "s2"
on "s1".order_no="s2".order_no
group by "s2".月份,"s1".prod_id
order by "s2".月份,"s1".prod_id

（6）檢索單價高于2400元的的産品編号、産品名稱、數量、單價及所在訂單号；

select s.prod_id '産品編号',product.prod_name '産品名稱',s.Qty '數量',s.unit_price '單價',s.order_no 
from product INNER JOIN 
(select order_no,prod_id,Qty,unit_price from sale_item where unit_price>2400) s
on product.prod_id=s.prod_id

（7）計算每一産品銷售數量總和與平均銷售單價；

select "s1".銷售總額,"s2".unit_price '平均銷售單價'
from(select SUM(Qty*unit_price) '銷售總額',prod_id from sale_item group by prod_id) "s1"
join sale_item "s2"
on "s1".prod_id="s2" .prod_id

（8）建立一個視圖，該視圖隻含上海客戶資訊，即客戶号、客戶姓名、住址。

CREATE VIEW view_name AS
select cust_id,cust_name,Addr from customer where Addr='上海'

1. 設定主鍵，自動為 not null

2. unique和主鍵差別：

unique：唯一并且一張表可設定多個unique 可空但是隻能有一行資料空

主鍵：唯一并且一張表隻能有一個主鍵

何時用到主鍵？

設定外鍵的時候需要主鍵還有唯一辨別一列的時候比如身份證

3. 主鍵可通過 constraint 主鍵名 primary key（列，列）來設定*組合鍵*

4. 給表取别名的時候不能用單引号，要用雙引号或者不用引号而給列取别名的時候可以選擇單引号或者 as 連接配接詞或者不用引号

5. where之類的範圍時列=單引号内容時值雙引号為列名

6. top 5表示取前5名

7. 視圖是為了儲存一張表下次查找該表可直接使用如本實驗中：

select * from view_name

即可檢視視圖

大二資料庫實驗報告

實驗一：熟悉資料庫管理系統環境

實驗二： SQL定義語言

實驗三：使用SQL語言進行簡單查詢

實驗四：使用SQL語言進行複雜查詢

實驗五：SQL的常用資料更新操作

實驗六：綜合應用**

繼續閱讀

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

Cloud Studio初體驗

使用 ctypes 進行 Python 和 C 的混合程式設計

【python】【資料處理】畫多元資料分布圖

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

【python】netconf協定對接管理裝置

「Python 網絡自動化」NETCONF —— Python 使用 NETCONF 管理配置 H3C 網絡裝置

在python中建立excel并寫入