《MySQL數(shù)據(jù)庫(kù)應(yīng)用》源代碼

《MySQL數(shù)據(jù)庫(kù)應(yīng)用》源代碼

項(xiàng)目一：部署數(shù)據(jù)庫(kù)開發(fā)環(huán)境

1.以windows命令行方式登錄MySQL服務(wù)器：

mysql-h-uroot-p

項(xiàng)目二：數(shù)據(jù)庫(kù)設(shè)計(jì)

1.使用MySQL命令行工具創(chuàng)建一個(gè)“學(xué)生成績(jī)管理”數(shù)據(jù)庫(kù)，命名為XSCJ,指定字符集

為gb2312,默認(rèn)校對(duì)規(guī)則為gb2312_chinese_ci(簡(jiǎn)體中文,不區(qū)分大小寫)。

CREATEDATABASEIFNOTEXISTSXSCJ

DEFAULTCHARACTERSETgb2312

DEFAULTCOLLATEgb2312_chinese_ci;

2.在MySQL中創(chuàng)建一個(gè)名為XSCJ_db的用于測(cè)試的數(shù)據(jù)庫(kù)。

CREATEDATABASEXSCJ.db;

3.查看當(dāng)前服務(wù)器下有哪些數(shù)據(jù)庫(kù)

showdatabases;

4.打開數(shù)據(jù)庫(kù)XSCJ_db3

usexscj_db3;

5.刪除數(shù)據(jù)庫(kù)XSCJ_db2

Dropdatabasexscj_db2;

6.結(jié)構(gòu)創(chuàng)建XSQK1表。

createtableXSQK1(

學(xué)號(hào)char(10)primarykey,

姓名varchar(lO),

性別char(2));

或：

createtableXSQK1(

學(xué)號(hào)char(10),

姓名varchar(lO),

性別char(2),

constraintprimarykey(學(xué)號(hào)))；

7.創(chuàng)建CJ1表

createtableCJ1(

學(xué)號(hào)char(lO),

課程號(hào)char(3),

成績(jī)tinyint,

學(xué)分tinyint,

constraintprimarykey(學(xué)號(hào)，課程號(hào)));

8.設(shè)置XSQK1表中的姓名列不能取空值。

createtableXSQK1(

學(xué)號(hào)char(10)primarykey,

姓名varchar(lO)NOTNULL,

性別char(2));

9.設(shè)置XSQK1表中的性別列的默認(rèn)值為“男工

createtableXSQK1(

學(xué)號(hào)char(10)primarykey,

姓名varchar(lO)NOTNULL,

性別char⑵default男,);

10.定義數(shù)據(jù)表XSQK1,將姓名列指定為非空，并取唯一值。

createtableXSQK1(

學(xué)號(hào)char(10)primarykey,

姓名varchar(lO)NOTNULLUNIQUE,

性別char(2)default'^');

11.將CJ1表的“學(xué)號(hào)”列定義為外鍵，關(guān)聯(lián)到XSQK1表的“學(xué)號(hào)”列。

createtableCJ1(

學(xué)號(hào)char(10),

課程號(hào)char(3),

成績(jī)tinyint,

學(xué)分tinyint,

constraintprimarykey(學(xué)號(hào)，課程號(hào))，

constraintFK_XSQK1_XHforeignkey(學(xué)號(hào))referencesXSQK1(學(xué)號(hào)))；

12.創(chuàng)建CJ1表，并為成績(jī)列增加檢查約束，要求其取值在0至IJ100之間。

createtableCJ1(

學(xué)號(hào)char(10),

課程號(hào)char(3),

成績(jī)tinyintcheck(成績(jī)＞=0and成績(jī)＜二100),

學(xué)分tinyint,

constraintprimarykey(學(xué)號(hào)，課程號(hào)));

13.創(chuàng)建XSQK2數(shù)據(jù)表，設(shè)置序號(hào)列為自動(dòng)增長(zhǎng)。

createtableXSQK2(

序號(hào)intprimarykeyautojncrement,

姓名varchar(lO),

性別char(2));

14.學(xué)生情況表XSQk的創(chuàng)建

createtableXSQK(

學(xué)號(hào)char(10)primarykey,

姓名varchar(lO)notnull,

性別char⑵notnulldefault'男'check(性別=‘男’or性別='女)

出生日期datenotnull,

專業(yè)名varchar(20)notnull,

所在學(xué)院varchar(20)notnull,

聯(lián)系電話char(ll),

總學(xué)分tinyint,

備注varchar(50));

15.課程表KC的創(chuàng)建

createtablekc(

課程號(hào)char(3)primarykey,

課程名varcha「(20)notnull,

授課教師varchar(lO),

開課學(xué)期tinyintnotnulldefault1check(開課學(xué)期＞二1and開課學(xué)期＜二6),

學(xué)時(shí)tinyintnotnull,

學(xué)分tinyint);

16.成績(jī)表CJ的創(chuàng)建

createtableCJ(

學(xué)號(hào)char(10),

課程號(hào)char(3),

成績(jī)tinyintcheck(成績(jī)＞=0and成績(jī)＜二100),

學(xué)分tinyint,

constraintprimarykey(學(xué)號(hào)，課程號(hào)),

constraintFK_xsqk_XHforeignkey(學(xué)號(hào))referencesxsqk(學(xué)號(hào))，

constraintFK_kc_KCHforeignkey(課程號(hào))referencesxsqk(課程號(hào)))；

17.通過(guò)DESCRIBE查看數(shù)據(jù)表CJ的基本結(jié)構(gòu)

Desccj;

18.通過(guò)SHOWCREATETABLE查看表詳細(xì)結(jié)構(gòu)

SHOWCREATETABLECJ\G;

19.將XSCJ_db數(shù)據(jù)庫(kù)中的數(shù)據(jù)表xsqkl改名為xsqko

altertablexsqklrenamexsqk;

20.將XSCJ_db數(shù)據(jù)庫(kù)中的CJ1表的“課程號(hào)”字段名改為“課程編號(hào)二數(shù)據(jù)類型不變。

altertableCJ1change課程號(hào)課程編號(hào)char(3);

21.將XSCJ_db數(shù)據(jù)庫(kù)中的數(shù)據(jù)表CJ1中的學(xué)分字段的數(shù)據(jù)類型改為Int型。

altertablecjlmodify學(xué)分int;

22.將XSCJ_db數(shù)據(jù)庫(kù)中的數(shù)據(jù)表CJ1的“課程編號(hào)”字段后新加一個(gè)名為“課程名稱”的

字段，要求數(shù)據(jù)類型為varchar（20）,且不能取空值。

altertableCJ1add課程名稱varchar（20）notnullafter課程編號(hào)；

23.刪除CJ1表中的“課程名稱”字段。

altertablecjldrop課程名稱；

24.將CJ1表中的“學(xué)號(hào)”字段排列到“課程編號(hào)”的后面。

altertablecjlmodify學(xué)號(hào)char（10）after課程編號(hào)；

25.為表CJ1的學(xué)號(hào)列建立外鍵約束，參考的主鍵是xsqk表中的學(xué)號(hào)列。

altertablecjladdconstraintFK_cjl_xhforeignkey（學(xué)號(hào)）referencesxsqk（學(xué)號(hào)）；

26.刪除CJ1表中的外鍵約束。

showcreatetableCJ1\G;

27.刪除數(shù)據(jù)表CJ1的主鍵約束。

altertablecjldropprimarykey;

28.向數(shù)據(jù)表CJ1添加主鍵約束，主鍵由為“學(xué)號(hào)”和“課程編號(hào)”兩列聯(lián)合組成。

ALTERTABLECJ1ADDPRIMARYKEY（學(xué)號(hào)，課程編號(hào)）；

29.修改數(shù)據(jù)表CJ1,將其成績(jī)列的默認(rèn)值設(shè)置為Oo

altertablecjlalter成績(jī)setdefault0;

30.修改數(shù)據(jù)表KC_db,要求“開課學(xué)期”列的取值在1到6之間。

ALTERTABLEKC_dbaddCHECK（開課學(xué)期＞=1and開課學(xué)期＜=6）;

31.修改數(shù)據(jù)表KC_db,刪除“開課學(xué)期”列上設(shè)置的檢查約束。

showcreatetablekc_db\G;

32.刪除XSCJ_db數(shù)據(jù)庫(kù)中的xsqk2表。

droptablexsqk2;

33.修改XSCJ數(shù)據(jù)庫(kù)中的CJ表，為“成績(jī)”列添加默認(rèn)值0。

altertablecj

alter成績(jī)

setdefault0;

34.向數(shù)據(jù)庫(kù)XSCJ中的XSQK表添加數(shù)據(jù)。

insertintoxsqk（學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名，所在學(xué)院，聯(lián)系電話，總學(xué)分,備注）

values（'2020030101','王強(qiáng)'，'男','19980406','云計(jì)算計(jì)算機(jī)學(xué)院',,null,null）;

35.向數(shù)據(jù)庫(kù)XSCJ中的KC表添加數(shù)據(jù)。

insertintokc丫2解q'101','計(jì)算機(jī)文化基礎(chǔ)','李平',1,基,3）;

36.向數(shù)據(jù)庫(kù)XSCJ中的CJ表添加數(shù)據(jù)。

insertintoCJ（學(xué)號(hào)，課程號(hào),成績(jī)）

,'

values（202003010111,101,null）;

37.向XSQK表添加多條記錄。

insertintoxsqk（學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名，所在學(xué)院）

values（'2020020102','成剛'，'男','20020206','計(jì)算機(jī)信息管理計(jì)算機(jī)學(xué)院'）,（'2020030103','李

英‘，'女','20011011','信息安全計(jì)算機(jī)學(xué)院'）,（'2020030104','趙林’，'男','20011111',‘網(wǎng)絡(luò)技術(shù)

計(jì)算機(jī)學(xué)院'）；

38.將所有課程號(hào)為“101”的成績(jī)加上2分。

updateCJset成績(jī):成績(jī)+2where課程號(hào)=101;

39.假設(shè)學(xué)號(hào)為“2020030104”的同學(xué)退學(xué)，需要把學(xué)生情況表XSQK中的該生信息刪除。

DELETEFROMXSQKWHERE學(xué)號(hào)=20200301041;

40.向?qū)W生情況表XSQK插入學(xué)生基本信息。

insertintoxsqkvalues（'2020050102','王真'，'男'2002-09-06','云計(jì)算計(jì)算機(jī)學(xué)院

,,,135741125441,NULL,NULL）,（12020050202','王成'，'男','2002-09-061,'云計(jì)算計(jì)算機(jī)學(xué)院

,,,NULL,NULL）,（12020110101','朱博男'2002-10-15','云計(jì)算計(jì)算機(jī)學(xué)院

',,NULL,‘班長(zhǎng)'）,（'2020110102',‘龍婷婷女','2002-11-05','云計(jì)算計(jì)算機(jī)學(xué)院

,,,NULL,NULL）;

41.向課程表KC插入學(xué)校開設(shè)的課程信息。

insertintokcvalues（'102','計(jì)算機(jī)硬件基礎(chǔ)童華1,80,5）,（'103','程序設(shè)計(jì)基礎(chǔ)王印

,,2,64,4）;

項(xiàng)目三：數(shù)據(jù)查詢

1.查詢學(xué)生成績(jī)表CJ的全表數(shù)據(jù)。

select*fromcj;

2.在CJ表中查詢學(xué)號(hào)'課程號(hào)和成績(jī)。

select學(xué)號(hào),課程號(hào)，成績(jī)fromcj;

3.查看CJ表中，有哪些課程已有學(xué)生選修了，要求顯示出已有學(xué)生選修課程的課程號(hào)。

selectdistinct課程號(hào)fromcj;

4.查詢CJ表中的成績(jī)信息，并要求對(duì)成績(jī)?cè)黾觾蓚€(gè)更新列：一列是在原始成績(jī)上加5分,

另一列是原成績(jī)的1.1倍。

select學(xué)號(hào)，課程號(hào)成績(jī)成績(jī)+5咸績(jī)*1.1fromCJ;

5.把查詢結(jié)果中“成績(jī)”對(duì)應(yīng)的列名改為“原成績(jī)”，把“成績(jī)+5”對(duì)應(yīng)的列名改為“原成

績(jī)+5”，把“成績(jī)*1.1”對(duì)應(yīng)的列名改為“原成績(jī)*1.1”。

SELECT學(xué)號(hào),課程號(hào)，成績(jī)?cè)煽?jī)，成績(jī)+5'原成績(jī)+5’,成績(jī)*1.1'原成績(jī)*1.1'

FROMCJ;

6.在課程表KC中查詢課程號(hào)，課程名和開課學(xué)期。

select課程號(hào)課程名，開課學(xué)期fromkc;

7.查詢CJ表中成績(jī)不及格的學(xué)生記錄。

select*fromCJwhere成績(jī)＜60;

8.使用比較運(yùn)算符BETWEENAND查詢2003年出生的學(xué)生信息，要求顯示出學(xué)號(hào)、姓名、性

別、出生日期和專業(yè)名字段。

select學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名

fromxsqk

where出生日期between,20030101'and1200312311;

9.查詢專業(yè)名為“云計(jì)算”，性別為“男”的學(xué)生信息，要求顯示出學(xué)號(hào)、姓名、性別和專

業(yè)名字段。

select學(xué)號(hào)，姓名，性別，專業(yè)名

fromxsqk

where專業(yè)名='云計(jì)算'and性別='男'；

10.在CJ表中查詢課程號(hào)為“102”“105”“106”的學(xué)生成績(jī)信息。

select*fromCJ

where課程號(hào)='102'or課程號(hào)='103'or課程號(hào)='105';

11.查詢課程表KC中課程名中含有“計(jì)算”兩個(gè)字的課程信息。

select*fromKC

where課程名like'%計(jì)算%

12.查詢XSQK表中所有姓“張”的學(xué)生信息，要求顯示學(xué)號(hào)、姓名、性別、出生日期和專

業(yè)名五列數(shù)據(jù)。

select學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名

fromXSQK

where姓名like'張％

13.使用IN查詢，在CJ表中查詢課程號(hào)為“102”“105”“106”的學(xué)生成績(jī)信息。

select*fromcj

where課程號(hào)in('102',1105',,106,);

14.在XSQK表中，查詢所有班委的姓名、性別、專業(yè)名和班委職務(wù)。

select姓名，性別，專業(yè)名，備注班委

fromxsqk

where備注isnotnull;

15.查詢XSQK表中所有不姓“李”和“王”的學(xué)生的學(xué)號(hào)、姓名、性別、出生日期和專業(yè)

名。

select學(xué)號(hào)，姓各性別，出生日期,專業(yè)名

fromXSQK

where姓名notlike'李附and姓名notlike'王％

16.查詢XSQK表中在2002年9月1日以后出生的學(xué)生信息，要求顯示出學(xué)號(hào)、姓名、性

別、出生日期和專業(yè)名字段。

select學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名

fromxsqk

where出生日期＞='20020901;

17.查詢成績(jī)?cè)?0分到70分之間的學(xué)生信息。

select*fromCJ

where成績(jī)＞=60and成績(jī)＜二70;

18.查詢xsqk表的記錄，要求顯示出學(xué)號(hào)、姓名、性別、出生日期和專業(yè)名，并按出生日

期升序排列。

select學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名

fromXSQK

orderby4;

19.查詢CJ表中的記錄，并先按課程號(hào)升序排列，當(dāng)課程號(hào)相同時(shí)，再按成績(jī)降序排列。

select*fromCJ

orderby課程號(hào)，成績(jī)desc;

20.在CJ表中查詢選修了課程號(hào)為“101”的記錄，要求按成績(jī)進(jìn)行降序排列。

select*fromCJ

where課程號(hào)='101'

orderby成績(jī)desc;

21.查詢不在2002年出生的學(xué)生信息，要求顯示出學(xué)號(hào)、姓名、性別、出生日期和專業(yè)名

字段。

select學(xué)號(hào)，姓名，性別，出生日期,專業(yè)名

fromxsqk

where出生日期notbetween1200201011and,20021231,;

22.在工具軟件SQLyog中查詢學(xué)生情況表XSQK中所有在2002年以前出生的學(xué)生信息。

SELECT*FROMxsqk

WHERE出生日期＜’20020101’

23.在工具軟件SQLyog中，查詢XSQK表中所有電話號(hào)碼中第2、3位是“3”“8”的學(xué)生信

息。

SELECT*FROMxsqk

WHERE聯(lián)系電話LIKE'_38%'

24.計(jì)算CJ表中成績(jī)列的總和，并將查詢結(jié)果中的列名設(shè)為“總成績(jī)”。

selectsum（成績(jī)）總成績(jī)

fromCJ;

25.統(tǒng)計(jì)KC表中的課程數(shù)量。

selectcount（*）課程數(shù)量

fromKC;

26.統(tǒng)計(jì)XSQK表中班委的人數(shù)。

selectcount（備注）班委人數(shù)

fromXSQK;

27.計(jì)算CJ表中學(xué)號(hào)為"2020110101"的平均成績(jī)。

selectavg（成績(jī)）平均成績(jī)

fromCJ

where學(xué)號(hào)='2020110101';

28.查詢CJ表中課程號(hào)為“101”的學(xué)生最好成績(jī)。

selectmax（成績(jī)）最好成績(jī)

fromCJ

where課程號(hào)='101';

29.查詢CJ表中課程號(hào)為“101”的學(xué)生最低成績(jī)。

selectmin（成績(jī)）最低成績(jī)

fromCJ

where課程號(hào)='101';

30.計(jì)算CJ表中學(xué)號(hào)為2016110101的學(xué)生所選課程的成績(jī)總和，并將查詢結(jié)果中的列名設(shè)

為“學(xué)號(hào)2016110101總成績(jī)”。

selectsum（成績(jī)）學(xué)號(hào)為2020110101總成績(jī)

fromCJ

where學(xué)號(hào)='2020110101';

31.統(tǒng)計(jì)XSQK表中男生的人數(shù)。

selectcount（*）男生人數(shù)

fromXSQK

where性別=身;

32.在CJ表中，查看選修了某門課程的學(xué)生人數(shù)。

select課程號(hào),count（*）選修人數(shù)

fromCJ

groupby課程號(hào)；

33.查詢CJ表，按課程號(hào)進(jìn)行分組，并顯示出選修該課程的學(xué)生學(xué)號(hào)及人數(shù)。

select課程號(hào),group_concat（學(xué)號(hào)）學(xué)號(hào),count（*）選修人數(shù)

fromCJ

groupby課程號(hào)；

34.查詢各專業(yè)男女生人數(shù)。

select性別，專業(yè)名,count（*）人數(shù)

fromXSQK

groupby性別，專業(yè)名;

35.在CJ表中統(tǒng)計(jì)平均成績(jī)大于等于60分的課程號(hào)，并要求顯示出該課程的平均成績(jī)并統(tǒng)

計(jì)出相應(yīng)的人數(shù)。

select課程號(hào),avg（成績(jī)）平均成績(jī),count（學(xué)號(hào)）人數(shù)

fromCJ

groupby課程號(hào)

havingavg（成績(jī)）＞=60;

36.在XSQK表中查詢不同性別的學(xué)生，在各專業(yè)中的學(xué)生人數(shù)，要求顯示的列有姓名、性

別、專業(yè)名和相應(yīng)的人數(shù)。

selectgroup_concat（姓名）姓名，性別，專業(yè)名,count（姓名）人數(shù)

fromxsqk

groupby性別，專業(yè)名；

37.在學(xué)生成績(jī)管理系統(tǒng)中，查看學(xué)生與課程所有可能的選課情況。

select學(xué)號(hào)，姓名，課程號(hào)課程名

fromXSQK.KC;

38.查詢不及格學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和成績(jī)信息。

selectxsqk.學(xué)號(hào)，姓名，課程號(hào)，成績(jī)

fromxsqk.cj

wherexsqk.學(xué)號(hào)=cj.學(xué)號(hào)and成績(jī)＜60;

ot/.

selectxsqk.學(xué)號(hào)，姓名，課程號(hào)，成績(jī)

fromxsqkinnerjoincj

onxsqk.學(xué)號(hào)二cj.學(xué)號(hào)

where成績(jī)＜60;

39.查詢CJ表，要求在同一行上顯示出每個(gè)學(xué)生兩門課程的課程號(hào)和成績(jī)。

selectA.學(xué)號(hào)A課程號(hào)A成績(jī)B課程號(hào)B成績(jī)

fromCJA,CJB

whereA.學(xué)號(hào)二B.學(xué)號(hào)andA.課程號(hào)＜B.課程號(hào)；

-□V■?

selectA.學(xué)號(hào),A.課程號(hào)A成績(jī)B課程號(hào)B成績(jī)

fromCJAinnerjoinCJB

onA.學(xué)號(hào)二B.學(xué)號(hào)andA.課程號(hào)＜B裸程號(hào)；

40.在學(xué)生成績(jī)管理系統(tǒng)中，查看哪些學(xué)生選修了課程以及取得的成績(jī)，同時(shí)還要查看哪些

學(xué)生還沒(méi)有選修課程。

selectxsqk.學(xué)號(hào)，姓名裸程號(hào)，成績(jī)

fromxsqkleftjoincj

onxsqk.學(xué)號(hào)二cj.學(xué)號(hào)；

41.在學(xué)生成績(jī)管理系統(tǒng)中，要查看學(xué)校開設(shè)課程以及哪些課程已有學(xué)生選修，并查看其學(xué)

號(hào)，課程號(hào)和成績(jī)，同時(shí)還要查看哪些課程還沒(méi)有學(xué)生選修，并查看其課程號(hào)和課程名。

selectkc.課程號(hào)開設(shè)課程號(hào)，課程名,學(xué)號(hào),cj.課程號(hào)已選修課程號(hào)，成績(jī)

fromcjrightjoinkc

oncj.課程號(hào)二kc.課程號(hào)；

42.查詢不及格學(xué)生的學(xué)號(hào)、姓名、課程號(hào)、授課教師和成績(jī)信息。

selectxsqk.學(xué)號(hào)，姓名,kc.課程號(hào)，授課教師，成績(jī)

fromxsqk,cj,kc

wherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)andkc.課程號(hào)二cj.課程號(hào)and成績(jī)＜60;

43.查詢平均成績(jī)及格學(xué)生的學(xué)號(hào)、姓名。

select學(xué)號(hào)，姓名

fromxsqk

where（selectavg（成績(jī)）fromcjwherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)）＞二60;

44.在KC表中查詢課程號(hào)、課程名、授課教師、開課學(xué)期和學(xué)時(shí)，要求查詢的課程必須已

有學(xué)生選修。

select課程號(hào)，課程名，授課教師，開課學(xué)期，學(xué)時(shí)

fromkc

where課程號(hào)in（

select課程號(hào)fromcj）;

45.查詢CJ表中成績(jī)高于課程號(hào)為103的任意一個(gè)學(xué)生的成績(jī)。

select*

fromcj

where成績(jī),any（select成績(jī)fromcjwhere課程號(hào)='103'）;

46.查詢CJ表中的記錄，要求這些記錄的成績(jī)高于課程號(hào)為103的所有學(xué)生的成績(jī)。

select*

fromcj

where成績(jī)〉all（select成績(jī)fromcjwhere課程號(hào)二'103）

47.在KC表中查詢已有學(xué)生選修的課程號(hào)和課程名。

select課程號(hào)，課程名

fromkc

whereexists（

select*fromcjwherekc.課程號(hào)二cj.課程號(hào)）；

48.使用XSQK表結(jié)合CJ表來(lái)查詢不及格學(xué)生的學(xué)號(hào)、姓名和專業(yè)名。

select學(xué)號(hào)，姓名，性別，專業(yè)名

fromxsqk

where學(xué)號(hào)in（select學(xué)號(hào)fromcjwherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)and成績(jī)＜60）;

49.查詢選修了兩門及以上課程的學(xué)生學(xué)號(hào)和姓名。

select學(xué)號(hào)，姓名

fromxsqk

where（selectcount（課程號(hào)）fromcjwherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)）＞=2;

50.在CJ表中查詢成績(jī)?cè)?5分以上的學(xué)生學(xué)號(hào)、課程號(hào)和成績(jī)，并將查詢結(jié)果保存在新表

CJ1中。

createtablecjl

select學(xué)號(hào),課程號(hào)，成績(jī)

fromcj

where成績(jī)＞=75;

51.查詢成績(jī)小于60分的學(xué)生學(xué)號(hào)、課程號(hào)和成績(jī)，并將查詢結(jié)果保存到CJ1表中。

insertintocjl

select學(xué)號(hào),課程號(hào)，成績(jī)

fromcj

where成績(jī)＜60;

52.使用UNION合并查詢選修了課程號(hào)為101和102的學(xué)生學(xué)號(hào)，課程號(hào)和成績(jī)。

select學(xué)號(hào),課程號(hào)，成績(jī)fromcjwhere課程號(hào)='101'

union

select學(xué)號(hào),課程號(hào)，成績(jī)fromcjwhere課程號(hào)='101';

53.使用UNIONALL合并查詢選修了課程號(hào)為“101”和“102”的學(xué)生學(xué)號(hào)。

select學(xué)號(hào),課程號(hào)，成績(jī)fromcjwhere課程號(hào)二‘101’

unionall

select學(xué)號(hào),課程號(hào)，成績(jī)fromcjwhere課程號(hào)='101';

項(xiàng)目四：創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象

1.在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK1表并創(chuàng)建普通索引，相關(guān)列為“學(xué)號(hào)”。

createtablexsqkl（

學(xué)號(hào)char(10),

姓名varchar(lO),

性別char(2),

indexindex_xh(學(xué)號(hào)));

2.在XSCJ_db數(shù)據(jù)庫(kù)中的KC_db表的“課程名”上創(chuàng)建索引。

createindexindex_kcm

onkc_db(課程名);

3.在XSCJ_db數(shù)據(jù)庫(kù)中XSQK1表的“姓名”列上創(chuàng)建普通索引。

altertablexsqkl

addindexindex_xm(姓名)；

4.在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK2表，創(chuàng)建唯一索引,相關(guān)列為“學(xué)號(hào)”。

createtablexsqk2(

學(xué)號(hào)char(10),

姓名varchar(lO),

性別char(2),

uniqueindexindex_xh(學(xué)號(hào)))；

5.在XSCJ_db數(shù)據(jù)庫(kù)中的XSQK_db的姓名列上創(chuàng)建唯一索引。

createuniqueindexindex_kcm

onxsqk_db(姓名)；

6.在XSCJ_db數(shù)據(jù)庫(kù)中XSQK2表的“姓名”列上創(chuàng)建唯一索引。

altertablexsqk2

adduniqueindexindex_xm(姓名)；

7.在測(cè)試數(shù)據(jù)庫(kù)XSCJ_db中新建XSQK3表，并創(chuàng)建全文索引，相關(guān)列為“備注”列。

createtablexsqk3(

學(xué)號(hào)char(10),

姓名varchar(lO),

備注char(lOO),

Fulltextindexindex_bz(備注))；

8.在備注列上創(chuàng)建全文索引。

createfulltextindexindex_bz

onxsqk3(備注);

9.新建XSQK4表并創(chuàng)建多列索引，索引列為表中的“學(xué)號(hào)”、"姓名”歹IJ。

createtablexsqk4(

學(xué)號(hào)int,

姓名char(lO),

性別char(2),

專業(yè)名varchar（20）,

indexindex_xh_xm（學(xué)號(hào)，姓名））；

10.在XSQK4表上創(chuàng)建多列索引，索引列為表中的“學(xué)號(hào)”“姓名”歹I］。

createindexindex_xh_xm

onxsqk4（學(xué)號(hào)，姓名）;

11.通過(guò)ALTERTABLE語(yǔ)句在XSQK4表上創(chuàng)建多列索引，索引列為表中的“學(xué)號(hào)”“姓名”

列。

altertablexsqk4

addkeyindex_xh_xm（學(xué)號(hào)，姓名）；

12.查看測(cè)試數(shù)據(jù)庫(kù)XSCJ-db中XSQK3表上建立的索引。

showcreatetablexsqk3\G;

13.查看測(cè)試數(shù)據(jù)庫(kù)XSCJ-db中的XSQK1表上建立的索引。

showindexfromxsqkl;

14.使用DROP關(guān)鍵字刪除表XSQK3中的“index_bz”索引。

dropindexindex_bzonxsqk3;

15.使用ALTER關(guān)鍵字刪除表XSQK1中的“index_xm"索引。

altertablexsqkldropindexindex_xm;

16.在XSCJ數(shù)據(jù)庫(kù)的學(xué)生情況表XSQK的“姓名”列上建立普通索引index_xm。

createindexindex_xm

onxsqk（姓名）;

17.在XSCJ數(shù)據(jù)庫(kù)的學(xué)生情況表XSQK的“專業(yè)名”和“所在學(xué)院”列上建立多列索引

index_zym_xy。

altertablexsqk

addindexindex_zym_xy（專業(yè)名，所在學(xué)院）；

18.在XSCJ數(shù)據(jù)庫(kù)的課程表KC的“課程名”列上建立唯一索引index_kcm?

createuniqueindexindex_kcm

onkc（課程名）；

19.查看在課程表KC上建立了哪些索引。

showindexfromkc;

20.以成績(jī)表CJ為基表，創(chuàng)建視圖view_cj,要求該視圖中隱藏成績(jī)的數(shù)值。

createviewview_cj

as

select學(xué)號(hào),課程號(hào)，學(xué)分

fromcj;

21.創(chuàng)建視圖view_xsqk_cj,要求該視圖中包含不及格學(xué)生的學(xué)號(hào)、姓名、性別、專業(yè)名、

課程號(hào)、成績(jī)。

createviewview_xsqk_cj

as

selectxsqk.學(xué)號(hào)，姓名，性別，專業(yè)名,課程號(hào)，成績(jī)

fromxsqk,cj

wherexsqk.學(xué)號(hào)=cj.學(xué)號(hào)and成績(jī)＜60;

22.通過(guò)視圖view_xsqk_cj來(lái)查詢不及格學(xué)生的學(xué)號(hào)、姓名、課程號(hào)、成績(jī)。

select學(xué)號(hào)，姓名，課程號(hào)，成績(jī)fromview_xsqk_cj;

23.使用DESC語(yǔ)句查看視圖view_cj的字段信息。

descview_cj;

24.使用SHOWCREATEVIEW查看視圖view_xsqk_cj的定義及字符編碼等信息。

Showcreateviewview_xsqk_cj\G;

25.修改視圖view_xsqk_cj,要求該視圖中包含成績(jī)大于80分學(xué)生的學(xué)號(hào)、姓名、性別、

專業(yè)名、課程號(hào)、成績(jī)。

alterviewview_xsqk_cj

as

selectxsqk.學(xué)號(hào)，姓名，性別，專業(yè)各課程號(hào)，成績(jī)

fromxsqk,cj

wherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)and成績(jī)＞80;

26.修改視圖view_cj,要求該視圖中顯示成績(jī)列，隱藏學(xué)分列。

createorreplaceviewview_cj

as

select學(xué)號(hào),課程號(hào)，成績(jī)

fromcj;

27.通過(guò)視圖view_cj向表CJ添加一條新的記錄。

insertintoview_cj(學(xué)號(hào)，課程號(hào),成績(jī))

values('2020110401,,,lll1,69);

28.通過(guò)視圖將學(xué)號(hào)為2020110401,課程號(hào)為111的成績(jī)改為73。

updateview_cj

set成績(jī)二73

where學(xué)號(hào)='2020110401'and課程號(hào)二'111';

29.通過(guò)視圖刪除成績(jī)表CJ中的記錄。

deletefromview_cj

where學(xué)號(hào)='2020110401'and課程號(hào)二,11；

30.由于在學(xué)生成績(jī)管理系統(tǒng)的應(yīng)用中，經(jīng)常需要查詢“學(xué)生學(xué)號(hào)、姓名、課程號(hào)、授課教

師、成績(jī)”這幾列的信息，因此需要?jiǎng)?chuàng)建一個(gè)名為view_xscj的視圖，要求該視圖中包含選

修了課程號(hào)為“101”“102”的“學(xué)生學(xué)號(hào)、姓名、課程號(hào)、授課教師、成績(jī)”列以便于查

詢使用。

createviewview_xscj

as

selectxsqk.學(xué)號(hào)，姓名,kc.課程號(hào)，授課教師，成績(jī)

fromxsqk,kc,cj

wherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)andkc.課程號(hào)二cj.課程號(hào)andkc.課程號(hào)in(,1011,'1021);

31.通過(guò)視圖view_xscj來(lái)查詢不及格學(xué)生的學(xué)號(hào)、姓名、課程號(hào)和成績(jī)。

select學(xué)號(hào)，姓名，課程號(hào)，成績(jī)

fromview_xscj

where成績(jī)＜60;

32.使用DESC語(yǔ)句查看視圖view_xscj的字段信息。

descview_xscj;

33.由于在學(xué)生成績(jī)管理系統(tǒng)的應(yīng)用中，學(xué)生選課時(shí)還需要了解該課程的開課學(xué)期，因此,

需要向視圖view_xscj中添加“開課學(xué)期”歹IJ。

alterviewview_xscj

as

selectxsqk.學(xué)號(hào)，姓名,kc.課程號(hào)，開課學(xué)期，授課教師，成績(jī)

fromxsqk,kc,cj

wherexsqk.學(xué)號(hào)二cj.學(xué)號(hào)andkc.課程號(hào)二cj.課程號(hào)andkc.課程號(hào)in(,1011,11021);

35.刪除視圖view_xscj。

dropviewifexists

view_xscj;

36.編寫一個(gè)存儲(chǔ)存過(guò)程，其功能是刪除數(shù)據(jù)庫(kù)XSCJ的CJ1表中指定學(xué)號(hào)的學(xué)生成績(jī)。

delimiter##

createproceduredel_cj(inxhchar(10))

begin

deletefromcjlwhere學(xué)號(hào)二xh;

end##

delimiter;

37.編寫一個(gè)存儲(chǔ)過(guò)程sum_add,其功能是完成兩個(gè)整數(shù)相加。

DELIMITER//

createproceduresum_add(inxint.inyint)

begin

declarezintdefault0;

setz=x+y;

selectz;

end//

delimiter;

38.調(diào)用存儲(chǔ)過(guò)程proc_del_cj()。

callporc_del_cj('2020110401,);

39.調(diào)用存儲(chǔ)過(guò)程sum_addo

callsum_add(12,24);

40.在存儲(chǔ)過(guò)程proci中使用IF條件語(yǔ)句。

delimiter//

createprocedureprocl(inxhchar(10))

readssqldata

begin

if(select學(xué)號(hào)fromxsqkwherexsqk.學(xué)號(hào)二xh)isnullthen

select'無(wú)此學(xué)生信息'as學(xué)生信息；

else

select學(xué)號(hào)，姓名，性別，專業(yè)名fromxsqkwherexsqk.學(xué)號(hào)二xh;

endif;

end//

delimiter;

41.使用CASE結(jié)構(gòu)判斷學(xué)生成績(jī)等級(jí)。

createprocedureproc2(inxhchar(10),inkchchar(3))

begin

declarefstinyint;

if(select學(xué)號(hào)fromcjwhere學(xué)號(hào)二xhand課程號(hào)二kch)isnullthen

select'無(wú)此學(xué)生成績(jī)'as學(xué)生成績(jī)；

else

select成績(jī)intofsfromcjwhere學(xué)號(hào)二xhand課程號(hào)二kch;

setfs=floor(fs/10);

casefs

when9||10thenselect,優(yōu)秀，as'成績(jī)等級(jí)

when8thenselect'良好，as'成績(jī)等級(jí)’；

when7thenselect'中等，as'成績(jī)等級(jí)’；

when6thenselect'及格，as'成績(jī)等級(jí)’；

else

select'不及格'as'成績(jī)等級(jí)'；

endcase;

endif;

end//

42.使用LOOP循環(huán)語(yǔ)句，用于完成輸入一個(gè)正整數(shù)并求從1到該數(shù)的累加和。

createprocedureaddsum(inxint)

begin

set@i=l,@sum=0;

add_sum:loop

begin

set@sum=@sum+@i;

set@i=@i+l;

end;

if@i>xthen

leaveadd_sum;

endif;

endloop;

select@sumas累加和;

end//

43.使用WHILE循環(huán)語(yǔ)句求1+2+…+100的和.

createprocedureaddsumlQ

begin

declareiintdefault1;

declaresumintdefault0;

whilei<=100do

setsum=sum+i;

seti=i+l;

endwhile;

selectsum;

end//

44.創(chuàng)建一個(gè)存儲(chǔ)過(guò)程projxsqk,從數(shù)據(jù)庫(kù)XSCJ的XSQK表中查詢出所有專業(yè)名為“信息

安全”的人數(shù)。

createprocedureproc_xsqk()

readssqldata

begin

selectcount(*)信息安全專業(yè)人數(shù)fromxsqk

where專業(yè)名二信息安全附；

end//

45.調(diào)用存儲(chǔ)過(guò)程proc_xsqk()。

callproc_xsqk();

46.創(chuàng)建一個(gè)存儲(chǔ)過(guò)程proc_drop_cj,用于刪除成績(jī)表CJ中某個(gè)學(xué)生的所有成績(jī)信息。

createprocedureproc_drop_cj(inxhchar(10))

begin

deletefromcj

where學(xué)號(hào)二xh;

end//

47.調(diào)用存儲(chǔ)過(guò)程proc_drop_cjo

callproc_drop_cj('2020110401');

48.創(chuàng)建一個(gè)帶有輸入輸出參數(shù)的存儲(chǔ)過(guò)程proc_count,用于統(tǒng)計(jì)xsqk表中某個(gè)指定專業(yè)

名的學(xué)生人數(shù)。

createprocedureproc_count(inZYMvarchar(20),outcount_numint)

readssqldata

begin

selectcount(*)intocount_numfromxsqk

where專業(yè)名二ZYM;

end//

49.調(diào)用存儲(chǔ)過(guò)程projcount,并顯示指定專業(yè)名的學(xué)生人數(shù)。

callproc_count('信息安全',@num);

50.創(chuàng)建一個(gè)存儲(chǔ)函數(shù)func_xs,要求輸入學(xué)生的學(xué)號(hào)，返回該學(xué)生的姓名。

CREATEFUNCTIONfunc_xs(xhCHAR(10))

RETURNSVARCHAR(IO)

READSSQLDATA

BEGIN

RETURN(SELECT姓名

FROMxsqk

WHERE學(xué)號(hào)=xh);

END//

51.調(diào)用存儲(chǔ)函數(shù)func_xso

Selectfunc_xs(2020110101))姓名；

52.刪除存儲(chǔ)函數(shù)func_xso

dropfunctionifexistsfunc_xs;

53.創(chuàng)建存儲(chǔ)函數(shù)func_cj,要求輸入該學(xué)生的學(xué)號(hào)和課程號(hào)后，返回該學(xué)生這門課的成績(jī)。

CREATEFUNCTIONfunc.cj(xhCHAR(10),kchVARCHAR(3))

RETURNSDOUBLE(5,1)

READSSQLDATA

BEGIN

RETURN(SELECT成績(jī)

FROMcj

WHEREcj.學(xué)號(hào)二xhANDcj.課程號(hào)=kch);

END$$

54.調(diào)用存儲(chǔ)函數(shù)func_cj。

Selectfunc_cj(,2020110101,/101')成績(jī)；

55.創(chuàng)建一個(gè)函數(shù)funjcount,用于統(tǒng)計(jì)選了某門課程的學(xué)生人數(shù)。

CREATEFUNCTIONfunc_count(kchCHAR(3))

RETURNSINT

READSSQLDATA

BEGIN

RETURN(SELECTCOUNT(*)

FROMcj

WHERE課程號(hào)二kch);

END//

56.調(diào)用存儲(chǔ)函數(shù)func_counto

Selectfunc_count(1101')選課人數(shù)；

57.在XSCJ數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)number表，用于統(tǒng)計(jì)選修了各門課程的學(xué)生人數(shù)，要求在CJ

表中添加學(xué)生的選課信息時(shí)，在number表中該門課程的選課人數(shù)會(huì)自動(dòng)增加。

1)創(chuàng)建一個(gè)number。表，用于存放每門課已有的選課人數(shù)：

createtablenumberO

select課程號(hào),count(*)選課人數(shù)

fromcj

groupby課程號(hào)；

2)創(chuàng)建number表，用于存放所有課程的選課人數(shù)。

createtablenumber

selectkc.課程號(hào)，選課人數(shù)

fromkcleftjoinnumberO

onkc.課程號(hào)二numberO.課程號(hào)；

3)把number表中選課人數(shù)二NULL的改為選課人數(shù)二0:

updatenumberset選課人數(shù)二0where選課人數(shù)isnull;

4)創(chuàng)建觸發(fā)器：

createtriggerinsert_cjafterinsert

oncj

foreachrow

begin

updatenumberset選課人數(shù)=選課人數(shù)+1where課程號(hào)二new.課程號(hào)；

end//

5)向CJ表中插入數(shù)據(jù)，驗(yàn)證觸發(fā)器功能：

insertintocjvalues('2020110106,,1114,,67,0),(,2020110201','HO',93,0);

58.在XSCJ數(shù)據(jù)庫(kù)中，創(chuàng)建Delete觸發(fā)器，要求實(shí)現(xiàn)在XSQK表中刪除某個(gè)學(xué)生信息時(shí)(以

主鍵學(xué)號(hào)作為刪除條件)，同時(shí)在CJ表中也將該學(xué)生的選課信息刪除。

createtriggerdelete_xsbeforedelete

onxsqk

foreachrow

begin

deletefromcjwhere學(xué)號(hào)二old.學(xué)號(hào)；

end//

59.在XSCJ數(shù)據(jù)庫(kù)中，創(chuàng)建Update觸發(fā)器，要求實(shí)現(xiàn)在XSQK表中修改某個(gè)學(xué)生的學(xué)號(hào)時(shí),

同時(shí)在CJ表中也同步完成該學(xué)生學(xué)號(hào)的修改。

createtriggerupdate_xsqkafterupdate

onxsqk

foreachrow

begin

ifnew.學(xué)號(hào)!二old.學(xué)號(hào)then

updatecjset學(xué)號(hào)二new.學(xué)號(hào)where學(xué)號(hào)二old.學(xué)號(hào)；

endif;

end//

60.查看在XSCJ數(shù)據(jù)庫(kù)中的觸發(fā)器。

showtriggers\G;

61.刪除XSCJ數(shù)據(jù)庫(kù)中的insert_kc觸發(fā)器。

droptriggerinsert_kc;

項(xiàng)目5：數(shù)據(jù)庫(kù)安全管理

1.使用CREATEUSER創(chuàng)建一個(gè)名為useri的用戶，密碼是123456,主機(jī)名是localhost。

createuseruserl^'localhost'identifiedby1234561;

2.將用戶名useri改為myname。

renameuseruserl@localhosttomyname@localhost;

3.刪除用戶user2o

dropuseruser2@localhost;

4.授予用戶useri在XSCJ.xsqk表上的select權(quán)限。

grantselectonxsqktouserl@localhost;

5.授予useri用戶在xsqk表的“姓名”列和“學(xué)號(hào)”列的update權(quán)限。

grantupdate（學(xué)號(hào)，姓名）onxsqktouserl@localhost;

6.授予useC用戶在XSCJ數(shù)據(jù)庫(kù)中所有表的查詢權(quán)限。

grantselectonxscj*touserl@localhost;

7.授予useri用戶在XSCJ數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)庫(kù)權(quán)限。

grantallon*touserl@localhost;

8.授予useri用戶創(chuàng)建新用戶的權(quán)限。

grantcreateuseron**touserl@localhost;

9.回收useri用戶修改xsqk表的權(quán)限。

revokeupdateonxsqkfromuserl@localhost;

10.查看use門權(quán)限。

SHOWGRANTSFORuserl@localhost;

11.備份XSCJ數(shù)據(jù)庫(kù)中的kc表，備份文件為xscj_kc.sql,存放到D:\mysqIback文件夾

中。

mysqldump-uroot-pxscjkc>d:\mysqlback\xscj_kc.sql

12.使用mysqIdump備份XSCJ數(shù)據(jù)庫(kù)，備份文件為xscj.sqI,存放到D:\mysqIback文件

夾中。

mysqldump-uroot-pxscj>d:\mysqIback\xscj.sqI

13.使用mysqldump備份MySQL服務(wù)器中的所有數(shù)據(jù)庫(kù)，存放到D:\mysqIback文件夾中。

mysqldump-uroot-p--all-databases>d:\mysqlback\xscj2.sql

14.使用MySQL命令將備份文件“D:\Mysqlback\xscj_kc.sql”還原到數(shù)據(jù)庫(kù)XSCJ中。

mysql-uroot-pxscj<D:\Mysqlback\xscj_kc.sql

15.使用source導(dǎo)入備份文件D:\Mysqlback\xscj_kc.sql°

sourceD:\MysqIback\xscj_kc.sqI

項(xiàng)目6：數(shù)據(jù)庫(kù)綜合應(yīng)用

1.配置Apache支持PHP

LoadModulephp5_module"C:\PHP\php5apache2_2.dll"

AddTypeapplication/x-httpd-php.php

PHPIniDir"C:\php"

2.創(chuàng)建數(shù)據(jù)庫(kù)Message和設(shè)計(jì)數(shù)據(jù)表info。

1)創(chuàng)建Message數(shù)據(jù)庫(kù)：

createdatabasemessage;

2)在Message數(shù)據(jù)庫(kù)中創(chuàng)建info表：

createtableinfo(

idintprimarykeyautojncrement,

authorvarchar(20)notnull,

subjecttinytext,

contenttextnotnull,

datedatetimenotnull);

3.訪客留言板的主程序index,php的設(shè)計(jì)

<doctypehtml

<htm|>

<head>

<metacharset="utf-8">

<title>訪客留言表</title>

:<scripttype="text/javascriptn>

functioncheck_data()

(

if(document.myForm,authorvaluelength==0)

altert(”作者字段不可以空白哦廣);

elseif(document.myFormcontentvaluelength==0)

alert(“內(nèi)容字段不可以空白哦!)

else

myform.submit();

)

</script>

</head>

<body>

<palign=center"><imgsrc="fig.jpg"></p>

<?php

requireonce("dbtoolsinc.php");

〃指定每頁(yè)顯示幾行記錄

$Recordsper_page=5;

〃顯示第幾頁(yè)的記錄

if(isset($_GET["page"]))

$page=$_GET["page"];

else

$page=1;

〃建立數(shù)據(jù)連接

$link=createconnection();

〃執(zhí)行SQL命令，按降序日期方式排序

$sql="SELECT*FROMinfoORDERBYdateDESC";

$Resultexecutesql(slink,"message",$sql);

〃獲取記錄數(shù)

$total_records=mysqli_numrows(Sresult);

〃計(jì)算總頁(yè)數(shù)

$total_pages=ceil(Stotalrecords/Records_per_page);

〃計(jì)算本頁(yè)第一個(gè)記錄的序號(hào)

Startedrecor