學(xué)生選課數(shù)據(jù)庫(kù)SQL語(yǔ)句45道練習(xí)題：

一、??????????? 設(shè)有一數(shù)據(jù)庫(kù)，包括四個(gè)表：學(xué)生表(Student)、課程表(Course)、成績(jī)表(Score)以及教師信息表(Teacher)。四個(gè)表的結(jié)構(gòu)分別如表1-1的表(一)~表(四)所示，數(shù)據(jù)如表1-2的表(一)~表(四)所示。用SQL語(yǔ)句創(chuàng)建四個(gè)表并完成相關(guān)題目。

表1-1數(shù)據(jù)庫(kù)的表結(jié)構(gòu)

表(一)Student (學(xué)生表)

屬性名

數(shù)據(jù)類(lèi)型

可否為空

含 義

Sno

varchar (20)

否

學(xué)號(hào)(主碼)

Sname

varchar (20)

否

學(xué)生姓名

Ssex

varchar (20)

否

學(xué)生性別

Sbirthday

datetime

可

學(xué)生出生年月

Class

varchar (20)

可

學(xué)生所在班級(jí)

表(二)Course(課程表)??? 主外碼(主外鍵)

屬性名

數(shù)據(jù)類(lèi)型

可否為空

含 義

Cno

varchar (20)

否

課程號(hào)(主碼)

Cname

varchar (20)

否

課程名稱(chēng)

Tno

varchar (20)

否

教工編號(hào)(外碼)

表(三)Score(成績(jī)表)

屬性名

數(shù)據(jù)類(lèi)型

可否為空

含 義

Sno

varchar (20)

否

學(xué)號(hào)(外碼)

Cno

varchar (20)

否

課程號(hào)(外碼)

Degree

Decimal(4,1)

可

成績(jī)

主碼：Sno+ Cno

表(四)Teacher(教師表)

屬性名

數(shù)據(jù)類(lèi)型

可否為空

含 義

Tno

varchar (20)

否

教工編號(hào)(主碼)

Tname

varchar (20)

否

教工姓名

Tsex

varchar (20)

否

教工性別

Tbirthday

datetime

可

教工出生年月

Prof

varchar (20)

可

職稱(chēng)

Depart

varchar (20)

否

教工所在部門(mén)

表1-2數(shù)據(jù)庫(kù)中的數(shù)據(jù)

表(一)Student

Sno

Sname

Ssex

Sbirthday

class

108

曾華

男

1977-09-01

95033

105

匡明

男

1975-10-02

95031

107

王麗

女

1976-01-23

95033

101

李軍

男

1976-02-20

95033

109

王芳

女

1975-02-10

95031

103

陸君

男

1974-06-03

95031

表(二)Course

Cno

Cname

Tno

3-105

計(jì)算機(jī)導(dǎo)論

825

3-245

操作系統(tǒng)

804

6-166

數(shù)字電路

856

9-888

高等數(shù)學(xué)

831

表(三)Score

Sno

Cno

Degree

103

3-245

86

105

3-245

75

109

3-245

68

103

3-105

92

105

3-105

88

109

3-105

76

101

3-105

64

107

3-105

91

108

3-105

78

101

6-166

85

107

6-166

79

108

6-166

81

表(四)Teacher

Tno

Tname

Tsex

Tbirthday

Prof

Depart

804

李誠(chéng)

男

1958-12-02

副教授

計(jì)算機(jī)系

856

張旭

男

1969-03-12

講師

電子工程系

825

王萍

女

1972-05-05

助教

計(jì)算機(jī)系

831

劉冰

女

1977-08-14

助教

電子工程系

1、 查詢(xún)Student表中的所有記錄的Sname、Ssex和Class列。

2、 查詢(xún)教師所有的單位即不重復(fù)的Depart列。

3、 查詢(xún)Student表的所有記錄。

4、 查詢(xún)Score表中成績(jī)?cè)?0到80之間的所有記錄。

5、 查詢(xún)Score表中成績(jī)?yōu)?5，86或88的記錄。

6、 查詢(xún)Student表中“95031”班或性別為“女”的同學(xué)記錄。

7、 以Class降序查詢(xún)Student表的所有記錄。

8、 以Cno升序、Degree降序查詢(xún)Score表的所有記錄。

9、 查詢(xún)“95031”班的學(xué)生人數(shù)。

10、?查詢(xún)Score表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)。(子查詢(xún)或者排序)

11、 查詢(xún)每門(mén)課的平均成績(jī)。

12、查詢(xún)Score表中至少有5名學(xué)生選修的并以3開(kāi)頭的課程的平均分?jǐn)?shù)。

13、查詢(xún)分?jǐn)?shù)大于70，小于90的Sno列。

14、查詢(xún)所有學(xué)生的Sname、Cno和Degree列。

15、查詢(xún)所有學(xué)生的Sno、Cname和Degree列。

16、查詢(xún)所有學(xué)生的Sname、Cname和Degree列。

17、?查詢(xún)“95033”班學(xué)生的平均分。

18、 假設(shè)使用如下命令建立了一個(gè)grade表：

create table grade(low? int(3),upp? int(3),rank? char(1))

insert into grade values(90,100,’A’)

insert into grade values(80,89,’B’)

insert into grade values(70,79,’C’)

insert into grade values(60,69,’D’)

insert into grade values(0,59,’E’)

現(xiàn)查詢(xún)所有同學(xué)的Sno、Cno和rank列。

19、? 查詢(xún)選修“3-105”課程的成績(jī)高于“109”號(hào)同學(xué)成績(jī)的所有同學(xué)的記錄。

20、查詢(xún)score中選學(xué)多門(mén)課程的同學(xué)中分?jǐn)?shù)為非最高分成績(jī)的記錄。

21、查詢(xún)成績(jī)高于學(xué)號(hào)為“109”、課程號(hào)為“3-105”的成績(jī)的所有記錄。

22、查詢(xún)和學(xué)號(hào)為108的同學(xué)同年出生的所有學(xué)生的Sno、Sname和Sbirthday列。

23、查詢(xún)“張旭“教師任課的學(xué)生成績(jī)。

24、查詢(xún)選修某課程的同學(xué)人數(shù)多于5人的教師姓名。

25、查詢(xún)95033班和95031班全體學(xué)生的記錄。

26、? 查詢(xún)存在有85分以上成績(jī)的課程Cno.

27、查詢(xún)出“計(jì)算機(jī)系“教師所教課程的成績(jī)表。

28、查詢(xún)“計(jì)算機(jī)系”與“電子工程系“不同職稱(chēng)的教師的Tname和Prof。

29、查詢(xún)選修編號(hào)為“3-105“課程且成績(jī)至少高于選修編號(hào)為“3-245”的同學(xué)的Cno、Sno和Degree,并按Degree從高到低次序排序。

30、查詢(xún)選修編號(hào)為“3-105”且成績(jī)高于選修編號(hào)為“3-245”課程的同學(xué)的Cno、Sno和Degree.

31、?查詢(xún)所有教師和同學(xué)的name、sex和birthday.

32、查詢(xún)所有“女”教師和“女”同學(xué)的name、sex和birthday.

33、?查詢(xún)成績(jī)比該課程平均成績(jī)低的同學(xué)的成績(jī)表。

34、 查詢(xún)所有任課教師的Tname和Depart.

35?、 查詢(xún)所有未講課的教師的Tname和Depart.

36、查詢(xún)至少有2名男生的班號(hào)。

37、查詢(xún)Student表中不姓“王”的同學(xué)記錄。

38、查詢(xún)Student表中每個(gè)學(xué)生的姓名和年齡。

39、查詢(xún)Student表中最大和最小的Sbirthday日期值。

40、以班號(hào)和年齡從大到小的順序查詢(xún)Student表中的全部記錄。

41、查詢(xún)“男”教師及其所上的課程。

42、查詢(xún)最高分同學(xué)的Sno、Cno和Degree列。

43、查詢(xún)和“李軍”同性別的所有同學(xué)的Sname.

44、查詢(xún)和“李軍”同性別并同班的同學(xué)Sname.

45、查詢(xún)所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績(jī)表。

答案：

1.select sname,ssex,class from student

2.select distinct depart from teacher

3.select * from student

4.select * from score where degree btween 60 and 80;

5.select * from score where degree in(85,86,88)

6.select * from student where class='95031' or ssex='女'

7.select * from student order by class desc

8.select * from score order by cno,degree desc

9.select count(*) from student where class='95031'

10.select sno,cno from score where degree=(select max(degree) from score)

select * from score order by degree desc limit 0,1

11.select cno,avg(degree) from score group by cno

12.select avg(degree) from score where cno like'3%' and cno in(select cno from score group by cno having count(*)>4)

select avg(degree) from score group by cno having count(*)>4 and cno like '3%'

13.select sno from score where degree>70 and degree<90

14.select student.sname,score.cno,score.degree from student,score where student.sno = score.sno

15.select score.sno,course.cname,score.degree from score,course where score.cno=course.cno

16.select student.sname,course.cname,degree from score,student,course where student.sno=score.sno and score.cno=course.cno

17.select avg(degree) from score where sno in(select sno from student where class='95033')

18.select sno,cno,rank from score,grade where degree between low and upp

19.(1)select * from score where cno = '3-105' and degree>(select max(degree) from score where sno='109')

(2)select * from score where cno = '3-105' and degree>(select max(degree) from score where sno='109' and cno='3-105')

20.(1)select * from score where sno in(select sno from score group by sno having count(*)>1) and degree

(2)select * from score a where sno in(select sno from score group by sno having count(*)>1) and degree

21.select * from score where degree>(select degree from score where sno='109' and cno='3-105')

22.select sno,sname,sbirthday from student where YEAR(sbirthday) = (select YEAR(sbirthday) from student where sno='108')

23.select * from score where cno in(select cno from course where tno =(select tno from teacher where tname='張旭'))

24.select tname from teacher where tno in(select tno from course where cno in(select cno from score group by cno having count(*)>5))

25.select * from student where class in('95033','95031')

26.select distinct cno from score where degree>85

27.select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='計(jì)算機(jī)系'))

28.select tname,prof from teacher where depart='計(jì)算機(jī)系' and prof not in(select prof from teacher where depart='電子工程系')

union

select tname,prof from teacher where depart='電子工程系' and prof not in(select prof from teacher where depart='計(jì)算機(jī)系')

select tname,prof from teacher where prof not in( select prof from teacher where depart='計(jì)算機(jī)系' and prof in(select prof from teacher where depart='電子工程系'))

29.select * from score where cno='3-105' and degree>any(select degree from score where cno='3-245')

30.select * from score where cno='3-105' and degree>all(select degree from score where cno='3-245')

31.select sname,ssex,sbirthday from student

union

select tname,tsex,tbirthday from teacher

32.select sname,ssex,sbirthday from student where ssex='女'

union

select tname,tsex,tbirthday from teacher where tsex='女'

33.select * from score a where degree

34.select tname,depart from teacher where tno in(select tno from course where cno in(select cno from score))

35.select tname,depart from teacher where tno in(select tno from course where cno not in(select cno from score))

36.select class from student where ssex='男' group by class having count(*)>1

37.select * from student where sname not like '王%'

38.select sname,year(now())-year(sbirthday) from student

39.select max(sbirthday),min(sbirthday) from student

40.select * from student order by class desc,sbirthday

41.select tname,cname from teacher,course where teacher.tno = course.tno and tsex='男'

42.select * from score where degree=(select max(degree) from score)

43.select sname from student where ssex=(select ssex from student where sname='李軍')

44.select sname from student where ssex=(select ssex from student where sname='李軍') and class=(select class from student where sname='李軍')

45.select * from score where sno in(select sno from student where ssex='男') and cno in(select cno from course where cname='計(jì)算機(jī)導(dǎo)論')

總結(jié)：

1.聯(lián)合主鍵：主碼：aa+bb? 代碼：primary key(aa,bb)

2.若插入數(shù)據(jù)后出現(xiàn)????? [Err] 1050 - Table 'student' already exists

把原來(lái)的表刪除再運(yùn)行代碼就行了

3.查詢(xún)不重復(fù)數(shù)據(jù)：select distinct a from b;

4.把一個(gè)表中Cno相同行分組并按Degree求平均值

select Cno,avg(Degree) from Score group by Cno having count(Cno)

5.至少高于：至少高于一個(gè)，用any

高于：高于所有，用all

mysql常用函數(shù)：

一、數(shù)學(xué)函數(shù)

數(shù)學(xué)函數(shù)主要用于處理數(shù)字，包括整型、浮點(diǎn)數(shù)等。

ABS(x)

返回x的絕對(duì)值

SELECT ABS(-1) -- 返回1

CEIL(x),CEILING(x)

返回大于或等于x的最小整數(shù)

SELECT CEIL(1.5) -- 返回2

FLOOR(x)

返回小于或等于x的最大整數(shù)

SELECT FLOOR(1.5) -- 返回1

RAND()

返回0->1的隨機(jī)數(shù)

SELECT RAND() --0.93099315644334

RAND(x)

返回0->1的隨機(jī)數(shù)，x值相同時(shí)返回的隨機(jī)數(shù)相同

SELECT RAND(2) --1.5865798029924

PI()

返回圓周率(3.141593)

SELECT PI() --3.141593

TRUNCATE(x,y)

返回?cái)?shù)值x保留到小數(shù)點(diǎn)后y位的值(與ROUND最大的區(qū)別是不會(huì)進(jìn)行四舍五入)

SELECT TRUNCATE(1.23456,3) -- 1.234

ROUND(x,y)

保留x小數(shù)點(diǎn)后y位的值，但截?cái)鄷r(shí)要進(jìn)行四舍五入

SELECT ROUND(1.23456,3) -- 1.235

POW(x,y).POWER(x,y)

返回x的y次方

SELECT POW(2,3) -- 8

SQRT(x)

返回x的平方根

SELECT SQRT(25) -- 5

EXP(x)

返回e的x次方

SELECT EXP(3) -- 20.085536923188

MOD(x,y)

返回x除以y以后的余數(shù)

SELECT MOD(5,2) -- 1

二、字符串函數(shù)

字符串函數(shù)是MySQL中最常用的一類(lèi)函數(shù)，字符串函數(shù)主要用于處理表中的字符串。

函數(shù)說(shuō)明

CHAR_LENGTH(s)

返回字符串s的字符數(shù)

SELECT CHAR_LENGTH('你好123') -- 5

CONCAT(s1,s2,...)

將字符串s1,s2等多個(gè)字符串合并為一個(gè)字符串

SELECT CONCAT('12','34') -- 1234

CONCAT_WS(x,s1,s2,...)

同CONCAT(s1,s2,...)函數(shù)，但是每個(gè)字符串直接要加上x(chóng)

SELECT CONCAT_WS('@','12','34') -- 12@34

INSERT(s1,x,len,s2)

將字符串s2替換s1的x位置開(kāi)始長(zhǎng)度為len的字符串

SELECT INSERT('12345',1,3,'abc') -- abc45

UPPER(s),UCAASE(S)

將字符串s的所有字母變成大寫(xiě)字母

SELECT UPPER('abc') -- ABC

LOWER(s),LCASE(s)

將字符串s的所有字母變成小寫(xiě)字母

SELECT LOWER('ABC') -- abc

LEFT(s,n)

返回字符串s的前n個(gè)字符

SELECT LEFT('abcde',2) -- ab

RIGHT(s,n)

返回字符串s的后n個(gè)字符

SELECT RIGHT('abcde',2) -- de

LTRIM(s)去掉字符串s開(kāi)始處的空格

RTRIM(s)去掉字符串s結(jié)尾處的空格

TRIM(s)去掉字符串s開(kāi)始和結(jié)尾處的空格

SELECT TRIM('@' FROM '@@abc@@') -- abc

REPEAT(s,n)

將字符串s重復(fù)n次

SELECT REPEAT('ab',3) -- ababab

SPACE(n)返回n個(gè)空格

REPLACE(s,s1,s2)

將字符串s2替代字符串s中的字符串s1

SELECT REPLACE('abca','a','x') --xbcx

STRCMP(s1,s2)比較字符串s1和s2

SUBSTRING(s,n,len)獲取從字符串s中的第n個(gè)位置開(kāi)始長(zhǎng)度為len的字符串

LOCATE(s1,s),POSITION(s1 IN s)

從字符串s中獲取s1的開(kāi)始位置

SELECT LOCATE('b', 'abc') -- 2

REVERSE(s)

將字符串s的順序反過(guò)來(lái)

SELECT REVERSE('abc') -- cba

FIELD(s,s1,s2...)

返回第一個(gè)與字符串s匹配的字符串位置

SELECT FIELD('c','a','b','c') -- 3

三、日期時(shí)間函數(shù)

MySQL的日期和時(shí)間函數(shù)主要用于處理日期時(shí)間。

函數(shù)說(shuō)明

CURDATE(),CURRENT_DATE()

返回當(dāng)前日期

SELECT CURDATE()

->2014-12-17

CURTIME(),CURRENT_TIME

返回當(dāng)前時(shí)間

SELECT CURTIME()

->15:59:02

NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),

SYSDATE(),LOCALTIMESTAMP()

返回當(dāng)前日期和時(shí)間

SELECT NOW()

->2014-12-17 15:59:02

YEAR(d),

MONTH(d)

DAY(d)

返回日期d中的月份值，1->12

SELECT MONTH('2011-11-11 11:11:11')

->11

MONTHNAME(d)

返回日期當(dāng)中的月份名稱(chēng)，如Janyary

SELECT MONTHNAME('2011-11-11 11:11:11')

->November

DAYNAME(d)

返回日期d是星期幾，如Monday,Tuesday

SELECT DAYNAME('2011-11-11 11:11:11')

->Friday

DAYOFWEEK(d)

日期d今天是星期幾，1星期日，2星期一

SELECT DAYOFWEEK('2011-11-11 11:11:11')

->6

WEEKDAY(d)

日期d今天是星期幾，

0表示星期一，1表示星期二

WEEK(d)，WEEKOFYEAR(d)

計(jì)算日期d是本年的第幾個(gè)星期，范圍是0->53

SELECT WEEK('2011-11-11 11:11:11')

->45

DAYOFYEAR(d)

計(jì)算日期d是本年的第幾天

SELECT DAYOFYEAR('2011-11-11 11:11:11')

->315

DAYOFMONTH(d)

計(jì)算日期d是本月的第幾天

SELECT DAYOFMONTH('2011-11-11 11:11:11')

->11

QUARTER(d)

返回日期d是第幾季節(jié)，返回1->4

SELECT QUARTER('2011-11-11 11:11:11')

->4

HOUR(t)

返回t中的小時(shí)值

SELECT HOUR('1:2:3')

->1

MINUTE(t)

返回t中的分鐘值

SELECT MINUTE('1:2:3')

->2

SECOND(t)

返回t中的秒鐘值

SELECT SECOND('1:2:3')

->3

四、系統(tǒng)信息函數(shù)

系統(tǒng)信息函數(shù)用來(lái)查詢(xún)MySQL數(shù)據(jù)庫(kù)的系統(tǒng)信息。

函數(shù)作用

VERSION()

返回?cái)?shù)據(jù)庫(kù)的版本號(hào)

SELECT VERSION()

->5.0.67-community-nt

CONNECTION_ID() 返回服務(wù)器的連接數(shù)

DATABASE()、SCHEMA返回當(dāng)前數(shù)據(jù)庫(kù)名

USER()、SYSTEM_USER()

返回當(dāng)前用戶

五、加密函數(shù)

加密函數(shù)是MySQL用來(lái)對(duì)數(shù)據(jù)進(jìn)行加密的函數(shù)。

1、PASSWORD(str)

該函數(shù)可以對(duì)字符串str進(jìn)行加密，一般情況下，PASSWORD(str)用于給用戶的密碼加密。

SELECT PASSWORD('123')

->*23AE809DDACAF96AF0FD78ED04B6A265E05AA257

2、MD5

MD5(str)函數(shù)可以對(duì)字符串str進(jìn)行散列，可以用于一些普通的不需要解密的數(shù)據(jù)加密。

SELECT md5('123')

->202cb962ac59075b964b07152d234b70

3、ENCODE(str,pswd_str)與DECODE(crypt_str,pswd_str)

ENCODE函數(shù)可以使用加密密碼pswd_str來(lái)加密字符串str，加密結(jié)果是二進(jìn)制數(shù)，需要使用BLOB類(lèi)型的字段保存。該函數(shù)與DECODE是一對(duì)，需要同樣的密碼才能夠解密。

SELECT ENCODE('123','xxoo')

->;vx

SELECT DECODE(';vx','xxoo')

->123

insert into login values('lch','alvin',encode('123','xxoo'),'50')

select Name,decode(Password,'xxoo') from login where username='lch'

總結(jié)

以上是生活随笔為你收集整理的建立学生选课表 mysql 语句_学生选课数据库SQL语句45道练习题整理及mysql常用函数（20161019）...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。