一、建表

CREATE TABLE Student(

Sno CHAR(3) PRIMARY KEY COMMENT "学号（主码）",

Sname CHAR(8) NOT NULL COMMENT "学生姓名",

Ssex CHAR(2) COMMENT "学生性别",

Sbirthday DATETIME COMMENT "学生出生年月",

Class CHAR(5) COMMENT "学生所在班级"

)

CREATE TABLE Course(

Cno CHAR(5) PRIMARY KEY COMMENT "课程号（主码）",

Cname VARCHAR(10) NOT NULL COMMENT "课程名称",

Tno CHAR(3) NOT NULL COMMENT "教工编号（外码）"

)

CREATE TABLE Score(

Sno CHAR(3) COMMENT "学号（外码）",

Cno CHAR(5) COMMENT "课程号（外码）",

Degree DECIMAL(4,1) COMMENT "成绩",

-- primary key(Sno,Cno),

FOREIGN KEY(Sno) REFERENCES Student(Sno),

FOREIGN KEY(Cno) REFERENCES Course(Cno)

)

CREATE TABLE Teacher(

Tno CHAR(3) PRIMARY KEY COMMENT "教工编号（主码）",

Tname CHAR(4) NOT NULL COMMENT "教工姓名",

Tsex CHAR(2) NOT NULL COMMENT "教工性别",

Tbirthday DATETIME COMMENT "教工出生年月",

Prof CHAR(6) COMMENT "职称",

Depart VARCHAR(10) COMMENT "教工所在部门"

)

INSERT INTO Student VALUES ('108','曾华','男','1977-09-01','95033');

INSERT INTO Student VALUES ('105','匡明','男','1975-10-02','95031');

INSERT INTO Student VALUES ('107','王丽','女','1976-01-23','95033');

INSERT INTO Student VALUES ('101','李军','男','1976-02-20','95033');

INSERT INTO Student VALUES ('109','王芳','女','1975-02-10','95031');

INSERT INTO Student VALUES ('103','陆君','男','1974-06-03','95031');

INSERT INTO Course VALUES ('3-105','计算机导论','825'),

('3-245','操作系统','804'),

('6-166 ','数字电路','856'),

('9-888','高等数学','831')

INSERT INTO Score VALUES ('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)

INSERT INTO Teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系'),

('856','张旭','男','1969-03-12','讲师','电子工程系'),

('825','王萍','女','1972-05-05','助教','计算机系'),

('831','刘冰','女','1977-08-14','助教','电子工程系')

   1

   2

   3

   4

   5

   6

   7

   8

   9

   10

   11

   12

   13

   14

   15

   16

   17

   18

   19

   20

   21

   22

   23

   24

   25

   26

   27

   28

   29

   30

   31

   32

   33

   34

   35

   36

   37

   38

   39

   40

   41

   42

   43

   44

   45

   46

   47

   48

   49

   50

   51

   52

   53

   54

   55

   56

   57

   58

   59

   60

   61

   62

   63

   64

   65

二、练习

1、查询Student表中的所有记录的Sname、Ssex和Class列。

SELECT Sname,Ssex,Class

FROM student

2、 查询教师所有的单位即不重复的Depart列。

SELECT DISTINCT Depart

FROM teacher

3、 查询Student表的所有记录。

SELECT *

FROM `student`

4、 查询Score表中成绩在60到80之间的所有记录。

SELECT *

FROM Score

WHERE Degree BETWEEN 60 AND 80

5、 查询Score表中成绩为85，86或88的记录。

SELECT *

FROM Score

WHERE Degree IN (85,86,88)

6、 查询Student表中“95031”班或性别为“女”的同学记录。

SELECT *

FROM student

WHERE Class="95031" OR Ssex="女"

7、 以Class降序查询Student表的所有记录。

SELECT *

FROM Student

ORDER BY Class DESC

8、 以Cno升序、Degree降序查询Score表的所有记录。

SELECT *

FROM score

ORDER BY Cno,Degree DESC

9、 查询“95031”班的学生人数。

SELECT COUNT(Class)

FROM Student

GROUP BY Class

HAVING Class=95031

10、  查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

SELECT st.sno,sc.cno,degree

FROM student st JOIN score sc

ON st.sno=sc.sno

ORDER BY Degree DESC

LIMIT 1

11、 查询每门课的平均成绩。

SELECT cno,AVG(IFNULL(degree,0))

FROM score

GROUP BY cno

12、 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT sc.cno,AVG(IFNULL(sc.degree,0))

FROM student st JOIN score sc

ON st.sno=sc.sno

GROUP BY sc.cno

HAVING COUNT(sc.cno)>=5 AND sc.cno LIKE "3%"

13、 查询分数大于70，小于90的Sno列。

SELECT sc.sno

FROM student st JOIN score sc

ON st.sno=sc.sno

WHERE degree>70 AND degree<90

14、 查询所有学生的Sname、Cno和Degree列。

SELECT st.sname,cno,degree

FROM student st JOIN score sc

ON st.sno=sc.sno

15、 查询所有学生的Sno、Cname和Degree列。

SELECT st.sno,cno,degree

FROM student st JOIN score sc

ON st.sno=sc.sno

16、 查询所有学生的Sname、Cname和Degree列。

SELECT st.sname,cname,degree

FROM student st JOIN score sc

ON st.sno=sc.sno

JOIN course co

ON sc.cno=co.cno

17、  查询“95033”班学生的平均分。

SELECT AVG(IFNULL(degree,0)) 平均分

FROM student st JOIN score sc

ON st.sno=sc.sno

GROUP BY class

HAVING class="95033"

18、 假设使用如下命令建立了一个grade表：

CREATE TABLE grade(

low INT(3),

upp INT(3),

ranks 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');

现查询所有同学的Sno、Cno和ranks列。

SELECT Sno,Cno,ranks

FROM score t1,(

     SELECT low,upp,ranks

  FROM grade

    )t2

WHERE degree BETWEEN t2.low AND t2.upp

19、  查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT *

FROM student st JOIN score sc

ON st.sno=sc.sno

WHERE sc.cno="3-105" AND degree > ALL(

  SELECT degree

  FROM student st JOIN score sc

  ON st.sno=sc.sno

  WHERE st.sno="109"

  )

20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。

SELECT t1.sno,t2.cno,t2.degree

FROM score t2,(

SELECT sno,MAX(degree) degree  

FROM score

GROUP BY sno

HAVING COUNT(sno)>1

)t1

WHERE t1.sno=t2.sno AND t2.degree<t1.degree

21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT st.*,sc.cno,degree

FROM student st JOIN score sc

ON st.sno=sc.sno

WHERE degree>(

SELECT degree

FROM score

WHERE cno='3-105' AND sno=109

)

22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sno,sname,Sbirthday

FROM student

WHERE YEAR(sbirthday)=(

 SELECT YEAR(sbirthday)

 FROM student

 WHERE sno=108

 )

 

 

23、查询“张旭“教师任课的学生成绩。

SELECT st.*,sc.degree

FROM student st JOIN score sc

ON st.sno=sc.sno

JOIN course co

ON sc.cno=co.cno

JOIN teacher te

ON co.tno=te.tno

WHERE te.tname='张旭'

24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT te.tname

FROM teacher te,course co,score sc,student st

WHERE te.tno=co.tno AND co.cno=sc.cno AND sc.sno=st.sno

GROUP BY te.tno

HAVING COUNT(st.sno)>5

25、查询95033班和95031班全体学生的记录。

SELECT *

FROM student

WHERE class IN(95033,95031)

26、  查询存在有85分以上成绩的课程Cno.

SELECT co.cno

FROM course co JOIN score sc

ON co.cno = sc.cno

WHERE degree>85

27、查询出“计算机系“教师所教课程的成绩表。

SELECT *

FROM teacher te JOIN course co

ON te.tno=co.tno

JOIN score sc

co.cno=sc.cno

WHERE cname='计算机系'

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT tname,prof

FROM teacher

WHERE prof NOT IN (

SELECT prof

FROM teacher

WHERE depart IN ('计算机系','电子工程系')

)

29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,

并按Degree从高到低次序排序。

SELECT st.sno,sc.cno,sc.degree

FROM(

SELECT sno,cno,degree

FROM score

WHERE cno='3-105' AND degree>(

   SELECT MAX(degree)

   FROM score

   WHERE cno='3-245'

   )

   )sc

JOIN student st

ON sc.sno=st.sno

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

SELECT st.sno,sc.cno,sc.degree

FROM(

SELECT sno,cno,degree

FROM score

WHERE cno='3-105' AND degree>(

   SELECT MAX(degree)

   FROM score

   WHERE cno='3-245'

   )

   )sc

JOIN student st

ON sc.sno=st.sno

31、 查询所有教师和同学的name、sex和birthday.

SELECT sname NAME, ssex sex,sbirthday birthday

FROM student

UNION

SELECT tname NAME, tsex sex,tbirthday birthday

FROM teacher

-- 下面结果和上面的查询结果是一样的，这是因为union查询只会使用第一个查询的字段名

SELECT sname NAME, ssex sex,sbirthday birthday

FROM student

UNION

SELECT tname, tsex,tbirthday

FROM teacher

32、查询所有“女”教师和“女”同学的name、sex和birthday.

SELECT sname NAME,ssex sex,sbirthday birthday

FROM student

WHERE ssex='女'

UNION

SELECT tname,tsex,tbirthday birthday

FROM teacher

WHERE tsex='女'

33、 查询成绩比该课程平均成绩低的同学的成绩表。

SELECT s2.*

FROM (

SELECT AVG(IFNULL(degree,0)) avg_score

FROM score

    )s1,score s2

WHERE s1.avg_score>s2.degree

34、查询所有任课教师的Tname和Depart.

SELECT tname,depart

FROM teacher

35 、 查询所有未讲课的教师的Tname和Depart.

SELECT tname,depart

FROM teacher te

WHERE NOT EXISTS(

SELECT 1

FROM course co

WHERE te.tno=co.tno

)

36、查询至少有2名男生的班号。

SELECT class

FROM student

GROUP BY ssex

HAVING COUNT(ssex)>1

37、查询Student表中不姓“王”的同学记录。

SELECT *

FROM student

WHERE sname NOT LIKE '王%'

38、查询Student表中每个学生的姓名和年龄。

SELECT sname,YEAR(NOW())-YEAR(sbirthday) age

FROM student

39、查询Student表中最大和最小的Sbirthday日期值。

SELECT MAX(sbirthday),MIN(sbirthday)

FROM student

40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

SELECT st1.*,st2.age

FROM student st1 JOIN (

 SELECT sno,YEAR(NOW())-YEAR(sbirthday) age

 FROM student

 )st2

ON st1.sno=st2.sno

ORDER BY class DESC,st2.age DESC

41、查询“男”教师及其所上的课程。

SELECT te.*,co.cno,co.cname

FROM teacher te JOIN course co

ON te.tno=co.tno

WHERE te.tsex='男'

42、查询最高分同学的Sno、Cno和Degree列。

SELECT sno,cno,MAX(degree)

FROM score

43、查询和“李军”同性别的所有同学的Sname.

SELECT sname

FROM student s1,(

 SELECT ssex

 FROM student

 WHERE sname='李军'

 )s2

WHERE s1.ssex=s2.ssex

44、查询和“李军”同性别并同班的同学Sname.

SELECT sname

FROM student s1,(

 SELECT ssex,class

 FROM student

 WHERE sname='李军'

 )s2

WHERE s1.ssex=s2.ssex AND s1.class=s2.class

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

SELECT st.sname,st.ssex,degree

FROM student st JOIN score sc

ON st.sno=sc.sno

JOIN course co

ON sc.cno=co.cno

WHERE co.cname='计算机导论' AND st.ssex='男'

   1

   2

   3

   4

   5

   6

   7

   8

   9

   10

   11

   12

   13

   14

   15

   16

   17

   18

   19

   20

   21

   22

   23

   24

   25

   26

   27

   28

   29

   30

   31

   32

   33

   34

   35

   36

   37

   38

   39

   40

   41

   42

   43

   44

   45

   46

   47

   48

   49

   50

   51

   52

   53

   54

   55

   56

   57

   58

   59

   60

   61

   62

   63

   64

   65

   66

   67

   68

   69

   70

   71

   72

   73

   74

   75

   76

   77

   78

   79

   80

   81

   82

   83

   84

   85

   86

   87

   88

   89

   90

   91

   92

   93

   94

   95

   96

   97

   98

   99

   100

   101

   102

   103

   104

   105

   106

   107

   108

   109

   110

   111

   112

   113

   114

   115

   116

   117

   118

   119

   120

   121

   122

   123

   124

   125

   126

   127

   128

   129

   130

   131

   132

   133

   134

   135

   136

   137

   138

   139

   140

   141

   142

   143

   144

   145

   146

   147

   148

   149

   150

   151

   152

   153

   154

   155

   156

   157

   158

   159

   160

   161

   162

   163

   164

   165

   166

   167

   168

   169

   170

   171

   172

   173

   174

   175

   176

   177

   178

   179

   180

   181

   182

   183

   184

   185

   186

   187

   188

   189

   190

   191

   192

   193

   194

   195

   196

   197

   198

   199

   200

   201

   202

   203

   204

   205

   206

   207

   208

   209

   210

   211

   212

   213

   214

   215

   216

   217

   218

   219

   220

   221

   222

   223

   224

   225

   226

   227

   228

   229

   230

   231

   232

   233

   234

   235

   236

   237

   238

   239

   240

   241

   242

   243

   244

   245

   246

   247

   248

   249

   250

   251

   252

   253

   254

   255

   256

   257

   258

   259

   260

   261

   262

   263

   264

   265

   266

   267

   268

   269

   270

   271

   272

   273

   274

   275

   276

   277

   278

   279

   280

   281

   282

   283

   284

   285

   286

   287

   288

   289

   290

   291

   292

   293

   294

   295

   296

   297

   298

   299

   300

   301

   302

   303

   304

   305

   306

   307

   308

   309

   310

   311

   312

   313

   314

   315

   316

   317

   318

   319

   320

   321

   322

   323

   324

   325

   326

   327

   328

   329

   330

   331

   332

   333

   334

   335

   336

   337

   338

   339

   340

   341

   342

   343

   344

   345

   346

   347

   348

   349

   350

   351

   352

   353

   354

   355

   356

   357

   358

   359

   360

   361

   362

   363

   364

   365

   366

   367

   368

   369

   370

   371

   372

   373

   374

   375

   376

   377

   378

   379

   380

   381

   382

   383

   384

   385

   386

   387

   388

   389

   390

   391