柚子快報(bào)激活碼778899分享：【數(shù)據(jù)庫實(shí)驗(yàn)】重點(diǎn)語句匯總

http://yzkb.51969.com/

數(shù)據(jù)庫實(shí)驗(yàn)final

文章目錄

數(shù)據(jù)庫實(shí)驗(yàn)final實(shí)驗(yàn)1：安裝MySQL實(shí)驗(yàn)2：數(shù)據(jù)表的建立student-course-scs供應(yīng)商-p零件-j項(xiàng)目-spj

實(shí)驗(yàn)3：數(shù)據(jù)查詢1單表查詢查詢表中的若干列選擇表中的若干元組order by子句(按照該屬性升序或降序查詢)聚集函數(shù)（求和、平均值、最值等）group by子句（按照某個(gè)屬性計(jì)算聚集）

spj查詢

實(shí)驗(yàn)3：數(shù)據(jù)查詢2sc查詢等值與非等值連接**復(fù)合條件連接**自身連接外連接

spj查詢

實(shí)驗(yàn)3：數(shù)據(jù)查詢3sc查詢內(nèi)外層查詢不相關(guān)子查詢嵌套查詢

spj查詢嵌套查詢any和all謂詞查詢存在和全稱謂詞查詢

實(shí)驗(yàn)3：數(shù)據(jù)查詢4sc查詢派生表

spj查詢

實(shí)驗(yàn)3：數(shù)據(jù)查詢5sc查詢spj查詢

實(shí)驗(yàn)4：ODBC連接MySQL實(shí)驗(yàn)5：數(shù)據(jù)庫安全性和完整性sc查詢安全性實(shí)驗(yàn)完整性實(shí)驗(yàn)

實(shí)驗(yàn)6：索引及查詢優(yōu)化分析實(shí)驗(yàn)7：并發(fā)控制與數(shù)據(jù)庫恢復(fù)實(shí)驗(yàn)8：MySQL主從復(fù)制與集群技術(shù)實(shí)驗(yàn)9：MongoDB服務(wù)器搭建和簡單操作查詢刪除修改分組其他分組其他

實(shí)驗(yàn)1：安裝MySQL

實(shí)驗(yàn)2：數(shù)據(jù)表的建立

student-course-sc

建立sc表

create table ‘sc’(

‘sno’ varchar(10) not null,

‘cno’ varchar(10) not null,

‘grade’ int null,

primary key (‘sno’, ‘cno’));

在sc表中設(shè)置外鍵

alter table 'sc'

add constraint 'snp'

foreign key('sno')

references 'student'('sno')

on delete no action

on update no action;

向student表中增加入學(xué)時(shí)間列，類型為日期

alter table student add s_entrance date

將年齡的數(shù)據(jù)類型改為字符型

alter table student modify column sage varchar(10)

將course表的cname改為unique

alter table course add unique(cname)

s供應(yīng)商-p零件-j項(xiàng)目-spj

向spj表中插入數(shù)據(jù)

insert into 'spj'('sno','pno','jno','qty') values('s1','p1','j1',500)

實(shí)驗(yàn)3：數(shù)據(jù)查詢1

單表查詢

查詢表中的若干列

查詢?nèi)w學(xué)生的學(xué)號和姓名

select sno,sname from student

查詢?nèi)w學(xué)生的詳細(xì)記錄

select * from student

查詢?nèi)w學(xué)生的姓名、出生年份、所在院系（小寫字母表示系名）

此處列有4個(gè)，分別是姓名、year of birth字符串、出生年份（減法獲得）、小寫系名

select sname,'year of birth:',2023-sage,lower(sdept) from student

使用列的別名改變查詢結(jié)果的列標(biāo)題

select sname NAME,'year of birth:' BIRTH,2023-sage BIRTHDAY,lower(sdept) DEPARTMENT from student

選擇表中的若干元組

查詢選修了課程的學(xué)生學(xué)號

select sno from sc

select ALL sno from sc

去掉表中重復(fù)行

select distinct sno from sc

查詢cs系全體學(xué)生名單

select sname from student where sdept='cs'

查詢考試成績有不及格的學(xué)生學(xué)號

select DISTINCT sno from sc where grade<60

查詢20~23歲的學(xué)生

select sname from student where sage BETWEEN 20 AND 30

查詢非20~23歲的學(xué)生

select sname from student where sage NOT between 20 and 30

查詢不在cs系的學(xué)生

select sname from student where sdept NOT IN ('CS')

查詢所有不姓劉的學(xué)生

select sname from student where sname NOT LIKE '劉%'

查詢姓歐陽且全名為3個(gè)字的學(xué)生

select sname from student where sname LIKE '歐陽_'

查詢名字中第二個(gè)字為陽的學(xué)生

select sname from student where sname LIKE '_陽%'

查詢以DB開頭，且倒數(shù)第三個(gè)字符為i的課程

select * from course where cname LIKE 'DB\%__' ESCAPE '\'

查詢DB_Design課程

select * from course where cname LIKE 'DB\_Design' ESCAPE '\'

查詢?nèi)鄙俪煽兊膶W(xué)生和課程

select sno,cno from sc where grade is null

查詢cs系和is系的學(xué)生

select * from student where sdept='cs' OR sdept='is'

order by子句(按照該屬性升序或降序查詢)

查看選修3號課程的學(xué)生和成績，按分?jǐn)?shù)降序

select sno,grade from sc where cno='3' ORDER BY grade DESC

查詢學(xué)生情況，系號升序，年齡降序

select * from student order by sdpet asc, sage desc

聚集函數(shù)（求和、平均值、最值等）

查詢學(xué)生總?cè)藬?shù)

select COUNT(*) from student

查詢選修了課程的人數(shù)

select COUNT(DISTINCT sno) from sc

查詢學(xué)生012選修課程的總學(xué)分

select SUM(credit) from course where sno='012' and sc.cno=course.cno

group by子句（按照某個(gè)屬性計(jì)算聚集）

求各個(gè)課程號及相應(yīng)的選課人數(shù)

select cno,count(sno) from sc group by cno

查詢選修了3門以上課程的學(xué)生

select sno from sc

group by sno

having count(*)>3

查詢平均成績≥90的學(xué)生學(xué)號和平均成績

select sno,avg(grade) from sc

group by sno

having avg(grade)>=90

spj查詢

求工程j1的紅色零件的供應(yīng)商代碼

select sno from spj,p

where spj.jno='j1' and p.color='red' and p.pno=spj.pno

實(shí)驗(yàn)3：數(shù)據(jù)查詢2

sc查詢

等值與非等值連接

查詢每個(gè)學(xué)生及其選修課程的情況

select student.* ,sc.* from student,sc

where student.sno=sc.sno

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

where student.sno=sc.sno

# 自然連接

復(fù)合條件連接

查詢選修2號課程且成績大于90的學(xué)生

select student.sno from student,sc

where student.sno=sc.sno and sc.cno='2' and sc.grade>90

自身連接

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

select first.cno, second.cno from course first, course second

where first.cpno=second.cno

外連接

select student.sno, sname, ssex, sage, sdept, cno, grade

from student

left outer join sc using(sno)

spj查詢

查詢零件名稱含有螺的零件號

select pno from p

where p.pname like '%螺%'

查詢滿足供應(yīng)商所供應(yīng)的總量大于1000的供應(yīng)商號及供應(yīng)總量

select sno, sum(qty) from spj

group by sno

where sum(qty)>1000

查詢給j1或j2供貨的供應(yīng)商名

select sname from spj,s

where (spj.jno='j1' or spj.jno='j2') and spj.sno=s.sno

group by sname

查詢供應(yīng)商在北京，并且項(xiàng)目也在北京的，供應(yīng)記錄（項(xiàng)目號），并且按照項(xiàng)目計(jì)算其供應(yīng)的零件總量

select j.jno, sum(spj.qty) from spj,s,j

where s.city='Beijing' and j.city='Beijing' and s.sno=spj.sno and j.jno=spj.jno

group by j.jno

實(shí)驗(yàn)3：數(shù)據(jù)查詢3

sc查詢

內(nèi)外層查詢

選修2號課程的學(xué)生名

select sname from student

where sno in(

select sno from sc

where cno='2')

不相關(guān)子查詢

查詢和劉晨一個(gè)系的學(xué)生

select sname from student

where sdept in(

select sdept from student

where sname='劉晨')

# 自身連接

select s1.sname from student s1, student s2

where s1.sdept=s2.sdept and s2.sname='劉晨'

查詢其他系中比cs系其中某一個(gè)學(xué)生年齡小的學(xué)生姓名和年齡

select sname, sage from student

where sage

and sdept<>'cs'

# 聚集函數(shù)實(shí)現(xiàn)

select sname, sage from student

where sage<(select max(sage) from student where sdept='cs') and sdept<>'cs'

嵌套查詢

查詢所有選修1號課程的學(xué)生

select sname from student

where exists(select * from sc where sno=student.sno and cno='1')

# 連接運(yùn)算

select sname from student,sc where student.sno=sc.sno and sc.cno='1'

查詢選修全部課程的學(xué)生

# 不存在一門課程這個(gè)學(xué)生沒有選修（雙重否定）

select sname from student

where not exists(select * from course

where not exists(select * from sc

where sno=student.sno

and cno=course.cno))

查詢至少選修了學(xué)生122選修的全部課程的學(xué)生

# 不存在122的某一門課程，這個(gè)學(xué)生沒有選修

select distinct sno from sc scx

where not exists(select * from sc scy

where scy.sno='122' and

not exists(select * from sc scz

where scz.sno=scx.sno and

scz.cno=scy.cno))

spj查詢

嵌套查詢

查詢與j1在一個(gè)地方的項(xiàng)目號

select jno from j

where j.city in(select j.city from j where j.jno='j1')

any和all謂詞查詢

查詢跟紅色零件任意一個(gè)重量相同的非紅色零件的零件號

select distinct pno from p

where weight=any(select weight from p where color='red') and color!='red'

查詢比s2供應(yīng)商所有供應(yīng)量都多的非s2供應(yīng)記錄

select * from spj

group by sno,pno,jno,qty

having qty>all(select qty from spj where spj.sno='s2') and spj.sno!='s2'

存在和全稱謂詞查詢

查詢沒有采用s1的項(xiàng)目

select distinct j.jno from j

where not exists(select distinct * from spj where spj.jno=j.jno and sno='s1')

查詢至少使用了j1所用的全部零件的其他工程號

# 不存在有j1使用了的零件，這個(gè)工程沒使用

select distinct j.jno from j

where not exists(

select * from spj x

where x.jno='j1' and not exists(

select * from spj y

where y.jno=j.jno and y.pno=x.pno))

查詢使用所有北京供應(yīng)商供應(yīng)的零件的項(xiàng)目

# 一個(gè)項(xiàng)目的所有零件中，存在由北京供貨商提供的零件

select distinct jno from spj spjx

where exists(select * from s sx

where sx.city='Beijing' and

exists(select * from spj spjy

where spjy.jno=spjx.jno and

spjy.sno=sx.sno))

實(shí)驗(yàn)3：數(shù)據(jù)查詢4

sc查詢

派生表

查詢cs系中年齡不超過19的學(xué)生

select * from student where sdept='cs'

union

select * from student where sage<=19

order by 1

# 表示按照第一列進(jìn)行升序排序

查詢所有選修1號課程的學(xué)生

select sname from student,(selec sno from sc where cno='1')

as sc1 where student.sno=sc1.sno

spj查詢

查詢s2提供但s3不提供的項(xiàng)目號

select distinct jno from spj x

where x.sno='s2' and

jno not in (select jno from spj y where y.sno='s3')

查詢每個(gè)項(xiàng)目的零件平均供應(yīng)量，生成派生表

select * from spj,(select pno,avg(qty) from spj group by pno)

as avg_qty

where spj.pno=avg_qty.pno

求至少用來s1供應(yīng)的全部零件的工程項(xiàng)目

select distinct jno from spj spjx

where not exists(

select * from spj spjy

where spjy.sno='s1' and not exists(

select * from spj spjz

where spjz.jno=spjx.jno and spjz.pno=spjy.pno))

# 一個(gè)工程項(xiàng)目，不存在s1供應(yīng)的零件，他沒有使用

實(shí)驗(yàn)3：數(shù)據(jù)查詢5

sc查詢

建立is系學(xué)生的視圖，并修改和插入時(shí)該視圖只有信息系的學(xué)生

create view is_student as select * from student where sdept='is'

WITH CHECK OPTION

建立信息系選修了1號課程的學(xué)生視圖（包括學(xué)號、姓名、成績）

create view is_s1(sno,sname,grade)

as select student.sno, sname, grade from student,sc

where sdept='is' and student.sno=sc.sno and sc.cno='1'

建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖

create view is_s2 as select sno,sname,grade from is_s1 where grade>=90

將學(xué)生的學(xué)號及他的平均成績定義為一個(gè)視圖，假設(shè)SC表中“成績”列Grade為數(shù)字型

create view SG (sno,Gavg) as select sno,avg(grade) from sc group by sno

刪除視圖is_s1

drop is_s1 cascade

將信息系學(xué)生視圖IS_Student中學(xué)號”201215122”的學(xué)生姓名改為”劉辰”

update is_student set sname='劉晨' where sno='122'

向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄，其中學(xué)號為”129”，姓名為”趙新”，年齡為20歲

insert into is_student values ('129','趙新',20)

在視圖中刪除學(xué)號129的記錄

delete from is_student where sno='129'

spj查詢

建立視圖S-Qty，并在該視圖下計(jì)算每個(gè)供應(yīng)商的平均供應(yīng)量，并查詢平均供應(yīng)量在500以上的供應(yīng)商號及其平均供應(yīng)量

create view s_qty as select sno,avg(qyt) avg_qty from spj

group by sno,qty

havig avg(qty)>500

更新視圖Color-Red，將紅色零件的重量都加10

update color_red set weight=weight+10 where color='red'

實(shí)驗(yàn)4：ODBC連接MySQL

實(shí)驗(yàn)5：數(shù)據(jù)庫安全性和完整性

sc查詢

求信電男生的及格率，XGstudent為信電和工院的學(xué)生視圖

select sum(score>=60)/count(score) from sc, XGstudent

where sc.sno=XGstudent.sno and XGstudent.sdept='信電' and XGstudent.ssex='男'

安全性實(shí)驗(yàn)

給u02授權(quán)

grant create on 'test'.* to 'u02'@'localhost';

grant select,update on table 'test'.'stud' to 'u02'@'localhost';

flush privileges;

查看u02的權(quán)限

show grants for 'u02'@'localhost'

查看tx表

describe tx;

刪除u02

drop user 'u02'@'localhost';

flush privileges;

完整性實(shí)驗(yàn)

分別定義test數(shù)據(jù)庫中各基表的主鍵、外鍵，實(shí)現(xiàn)實(shí)體完整性約束和參照完整性約束

檢查是否設(shè)置成功主鍵：

select column_name from information_schema.key_column_usage

where table_name='stud' and constraint_name='primary'

定義存儲(chǔ)過程，實(shí)現(xiàn)按照學(xué)號條件來查詢某個(gè)學(xué)生的選課情況

# 定義存儲(chǔ)過程

delimiter //

create procedure getcno(in ssno char(10))

begin

select cno from sc where sc.sno=ssno;

end //

delimiter

# 按照學(xué)號查找選課

call getcno('219');

# 顯示：查詢結(jié)果為001課程

定義觸發(fā)器，向stud表插入新一條記錄時(shí)，實(shí)現(xiàn)兩個(gè)功能：

① 限定學(xué)生新紀(jì)錄年齡輸入值為15-35之間（含15和35），否則自動(dòng)賦值19

# define trigger

delimiter //

create trigger age_check

before insert on stud for each row

begin

if new.sage>35 then set new.sage=19;

elseif new.sage<15 then set new.sage=19;

end if;

end //

delimiter;

# test trigger

insert into stud values('666','Morley','女',36,'信電',null,null,null,null);

# check the inserted information

select * from stud where sno='666';

② 在選課表sc中同步追加該學(xué)生“001”號課程的信息，成績值取“001”號課程的平均值

# define trigger

delimiter //

create trigger add_score

after insert on stud for each row

begin

insert into sc values(new.sno,'001',(select avg(sc1.score) from (select score from sc where sc.cno='001') as sc1));

end //

delimiter

實(shí)驗(yàn)6：索引及查詢優(yōu)化分析

實(shí)驗(yàn)7：并發(fā)控制與數(shù)據(jù)庫恢復(fù)

查看事務(wù)的隔離級別

SELECT @@global.transaction_isolation;

--查詢?nèi)质聞?wù)隔離級別

SELECT @@session.transaction_isolation;

--查詢會(huì)話事務(wù)隔離級別

SELECT @@transaction_isolation;

--查詢事務(wù)隔離級別，一般同會(huì)話級別

read-uncommitted級別，A、B為兩個(gè)終端

# A修改級別，查詢user

set session transaction isolation level read uncommitted;

select @@transaction_isolation;

start transaction;

select * from user;

# B更新一條記錄

start transaction;

update user set userAge=10 where userId=3;

select * from user;

# B事務(wù)未提交，A事務(wù)內(nèi)做一次查詢，結(jié)果已經(jīng)改變

select * from user;

# B rollback

rollback;

select * from user;

# A query again,the results rollback too

select * form user;

# A modify table user

update user set userAge=100 where userId=1;

# B重新開始事務(wù)后，對user表修改，修改被掛起直至超時(shí)，但對另一條數(shù)據(jù)的修改成功，說明A的修改對user表的數(shù)據(jù)行加【行共享鎖】

update user set userAge=111 where userId=1;

# error

update user set userAge=111 where userId=2;

# success

實(shí)驗(yàn)8：MySQL主從復(fù)制與集群技術(shù)

實(shí)驗(yàn)9：MongoDB服務(wù)器搭建和簡單操作

創(chuàng)建數(shù)據(jù)庫

use school

創(chuàng)建三個(gè)collection：grade_1_1、grade_1_2和grade_1_3，并插入數(shù)據(jù)

for(grade_index in (grade = ['grade_1_1', 'grade_1_2', 'grade_1_3'])) {

Hobbys=['draw', 'dance', 'running', 'sing', 'football', 'basketball', 'computer', 'python']

for (var i = 1; i <= 10; i++) {

db[grade[grade_index]].insert({

"name": "zhangsan" + i,

"sex": Math.round(Math.random() * 10) % 2,

"age": Math.round(Math.random() * 6) + 3,

"hobby": [Hobbys[Math.round(Math.random() * 6)]]

});

}

}　

查看數(shù)據(jù)庫和集合

show dbs

show collections

查詢

db.grade_1_2.find()

查看一年級二班grade_1_2中所有年齡大于 4 歲并且小于 7 歲的學(xué)生

db.grade_1_2.find({age:{$gt:4,$lt:7}})

查看一年級二班grade_1_2中所有年齡小于 4 歲以及大于 7 歲的學(xué)生

db.grade_1_2.find({$or:[{age:{$lt:4}},{age:{$gt:7}}]})

查看一年級二班grade_1_2中所有年齡大于 4 歲并且性別值為0的學(xué)生

db.grade_1_2.find({age:{$gt:4},sex:0})

查看一年級二班grade_1_2中所有姓名帶zhangsan1的學(xué)生

db.grade_1_2.find({name:"zhangsan1"})

查詢有三項(xiàng)愛好的學(xué)生

db.grade_1_2.find({hobby:{$size:3}})

查看一年級二班的學(xué)生，興趣覆蓋范圍有哪些

db.getCollection('grade_1_2').distinct('hobby')

查看一年級二班的學(xué)生，按年紀(jì)降序

db.grade_1_2.find().sort({age:-1})

查看一年級二班grade_1_2中所有興趣愛好有三項(xiàng)的學(xué)生的學(xué)生數(shù)目

db.grade_1_2.find({hobby:{$size:3}}).count()

查看一年級二班的第二位學(xué)生

db.grade_1_2.find().limit(1).skip(1)

刪除

grade_1_2， 刪除第一位 6 歲的學(xué)生

db.grade_1_2.remove({age:6},1)

修改

grade_1_2中，修改名為zhangsan7的學(xué)生，年齡為 8 歲，興趣愛好為 跳舞和畫畫

set

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$set:{"age":8,"hobby":["dance","draw"]}})

追加zhangsan7學(xué)生興趣愛好唱歌

push

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$push:{"hobby":"sing"}})

追加zhangsan7學(xué)生興趣愛好吹牛和打籃球

each

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$push:{"hobby":{$each:["brag","basketball"]}}})

追加zhangsan7學(xué)生興趣愛好唱歌和打籃球，要保證hobby數(shù)組不重復(fù)

addToSet

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$addToSet:{"hobby":{$each:["sing","basketball"]}}})

給一年級二班所有學(xué)生的年齡都增加一歲

inc / multi

db.getCollection('grade_1_2').update({},{$inc:{"age":1}},{multi:true})

刪除zhangsan7學(xué)生的sex屬性

unset

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$unset:{"sex":1}})

刪除zhangsan7學(xué)生的hobby數(shù)組中的頭元素

pop

-1：頭元素；1：尾元素

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$pop:{"hobby":-1}})

刪除zhangsan7學(xué)生的hobby數(shù)組中的sing元素

pull

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$pull:{"hobby":"sing"}})

分組

統(tǒng)計(jì)該集合男女學(xué)生的各自平均年齡

aggregate

group

db.grade_1_4.aggregate([{$group:{_id:"$sex",average_age:{$avg:"$age"}}}])

統(tǒng)計(jì)該集合每名學(xué)生的總分

db.grade_1_4.aggregate([{$group:{_id:"$name",score:{$sum:{$sum:["$score.chinese","$score.english","$score.math"]}}}}])

其他

刪除collection中的所有記錄

db.grade_1_1.remove({})

刪除collection

db.grade_1_1.drop()

刪除當(dāng)前數(shù)據(jù)庫

db.dropDatabase()

顯示mongodb當(dāng)前版本號

db.version()

nc / multi

db.getCollection('grade_1_2').update({},{$inc:{"age":1}},{multi:true})

刪除zhangsan7學(xué)生的sex屬性

unset

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$unset:{"sex":1}})

刪除zhangsan7學(xué)生的hobby數(shù)組中的頭元素

pop

-1：頭元素；1：尾元素

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$pop:{"hobby":-1}})

刪除zhangsan7學(xué)生的hobby數(shù)組中的sing元素

pull

db.getCollection('grade_1_2').update({"name":"zhangsan7"},{$pull:{"hobby":"sing"}})

分組

統(tǒng)計(jì)該集合男女學(xué)生的各自平均年齡

aggregate

group

db.grade_1_4.aggregate([{$group:{_id:"$sex",average_age:{$avg:"$age"}}}])

統(tǒng)計(jì)該集合每名學(xué)生的總分

db.grade_1_4.aggregate([{$group:{_id:"$name",score:{$sum:{$sum:["$score.chinese","$score.english","$score.math"]}}}}])

其他

刪除collection中的所有記錄

db.grade_1_1.remove({})

刪除collection

db.grade_1_1.drop()

刪除當(dāng)前數(shù)據(jù)庫

db.dropDatabase()

顯示mongodb當(dāng)前版本號

db.version()

柚子快報(bào)激活碼778899分享：【數(shù)據(jù)庫實(shí)驗(yàn)】重點(diǎn)語句匯總

http://yzkb.51969.com/

精彩內(nèi)容