mysql练手专用项目

来源：互联网发布：主题模型知乎编辑：程序博客网时间：2024/05/25 16:40

mysql练手专用项目，有需要的朋友可以参考下。

Sutdent表的定义

字段名

字段描述

数据类型

主键

外键

非空

唯一

自增

学号

INT(10)

是

否

是

Name

姓名

VARCHAR(20)

否

是

否

Sex

性别

VARCHAR(4)

否

Birth

出生年份

YEAR

否

Department

院系

VARCHAR(20)

否

是

否

Address

家庭住址

VARCHAR(50)

否

Score表的定义

字段名

字段描述

数据类型

主键

外键

非空

唯一

自增

编号

INT(10)

是

否

是

Stu_id

学号

INT(10)

否

是

否

C_name

课程名

VARCHAR(20)

否

Grade

分数

INT(10)

否

1.创建student和score表

CREATETABLEstudent(

idINT(10)NOTNULLUNIQUEPRIMARYKEY,

nameVARCHAR(20)NOTNULL,

sexVARCHAR(4),

birthYEAR,

departmentVARCHAR(20),

addressVARCHAR(50)

);

创建score表。SQL代码如下：

CREATETABLEscore(

idINT(10)NOTNULLUNIQUEPRIMARYKEYAUTO_INCREMENT,

stu_idINT(10)NOTNULL,

c_nameVARCHAR(20),

gradeINT(10)

);

2.为student表和score表增加记录

向student表插入记录的INSERT语句如下：

INSERTINTOstudentVALUES(901,'张老大','男',1985,'计算机系','北京市海淀区');

INSERTINTOstudentVALUES(902,'张老二','男',1986,'中文系','北京市昌平区');

INSERTINTOstudentVALUES(903,'张三','女',1990,'中文系','湖南省永州市');

INSERTINTOstudentVALUES(904,'李四','男',1990,'英语系','辽宁省阜新市');

INSERTINTOstudentVALUES(905,'王五','女',1991,'英语系','福建省厦门市');

INSERTINTOstudentVALUES(906,'王六','男',1988,'计算机系','湖南省衡阳市');

向score表插入记录的INSERT语句如下：

INSERTINTOscoreVALUES(NULL,901,'计算机',98);

INSERTINTOscoreVALUES(NULL,901,'英语',80);

INSERTINTOscoreVALUES(NULL,902,'计算机',65);

INSERTINTOscoreVALUES(NULL,902,'中文',88);

INSERTINTOscoreVALUES(NULL,903,'中文',95);

INSERTINTOscoreVALUES(NULL,904,'计算机',70);

INSERTINTOscoreVALUES(NULL,904,'英语',92);

INSERTINTOscoreVALUES(NULL,905,'英语',94);

INSERTINTOscoreVALUES(NULL,906,'计算机',90);

INSERTINTOscoreVALUES(NULL,906,'英语',85);

3.查询student表的所有记录

mysql>SELECT*FROMstudent;

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

|901|张老大|男|1985|计算机系|北京市海淀区|

|902|张老二|男|1986|中文系|北京市昌平区|

|903|张三|女|1990|中文系|湖南省永州市|

|904|李四|男|1990|英语系|辽宁省阜新市|

|905|王五|女|1991|英语系|福建省厦门市|

|906|王六|男|1988|计算机系|湖南省衡阳市|

+-----+--------+------+-------+------------+--------------+

4.查询student表的第2条到4条记录

mysql>SELECT*FROMstudentLIMIT1,3;

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

|902|张老二|男|1986|中文系|北京市昌平区|

|903|张三|女|1990|中文系|湖南省永州市|

|904|李四|男|1990|英语系|辽宁省阜新市|

+-----+--------+------+-------+------------+--------------+

5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息

mysql>SELECTid,name,departmentFROMstudent;

+-----+--------+------------+

|id|name|department|

+-----+--------+------------+

|901|张老大|计算机系|

|902|张老二|中文系|

|903|张三|中文系|

|904|李四|英语系|

|905|王五|英语系|

|906|王六|计算机系|

+-----+--------+------------+

6.从student表中查询计算机系和英语系的学生的信息

mysql>SELECT*FROMstudentWHEREdepartmentIN('计算机系','英语系');

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

|901|张老大|男|1985|计算机系|北京市海淀区|

|904|李四|男|1990|英语系|辽宁省阜新市|

|905|王五|女|1991|英语系|福建省厦门市|

|906|王六|男|1988|计算机系|湖南省衡阳市|

+-----+--------+------+-------+------------+--------------+

7.从student表中查询年龄18~22岁的学生信息

mysql>SELECTid,name,sex,2013-birthASage,department,address

->FROMstudent

->WHERE2013-birthBETWEEN18AND22;

+-----+------+------+------+------------+--------------+

|id|name|sex|age|department|address|

+-----+------+------+------+------------+--------------+

|905|王五|女|22|英语系|福建省厦门市|

+-----+------+------+------+------------+--------------+

mysql>SELECTid,name,sex,2013-birthASage,department,address

->FROMstudent

->WHERE2013-birth>=18AND2013-birth<=22;

+-----+------+------+------+------------+--------------+

|id|name|sex|age|department|address|

+-----+------+------+------+------------+--------------+

|905|王五|女|22|英语系|福建省厦门市|

+-----+------+------+------+------------+--------------+

8.从student表中查询每个院系有多少人

mysql>SELECTdepartment,COUNT(id)FROMstudentGROUPBYdepartment;

+------------+-----------+

|department|COUNT(id)|

+------------+-----------+

|计算机系|2|

|英语系|2|

|中文系|2|

+------------+-----------+

9.从score表中查询每个科目的最高分

mysql>SELECTc_name,MAX(grade)FROMscoreGROUPBYc_name;

+--------+------------+

|c_name|MAX(grade)|

+--------+------------+

|计算机|98|

|英语|94|

|中文|95|

+--------+------------+

10.查询李四的考试科目（c_name）和考试成绩（grade）

mysql>SELECTc_name,grade

->FROMscoreWHEREstu_id=

->(SELECTidFROMstudent

->WHEREname='李四');

+--------+-------+

|c_name|grade|

+--------+-------+

|计算机|70|

|英语|92|

+--------+-------+

11.用连接的方式查询所有学生的信息和考试信息

mysql>SELECTstudent.id,name,sex,birth,department,address,c_name,grade

->FROMstudent,score

->WHEREstudent.id=score.stu_id;

+-----+--------+------+-------+------------+--------------+--------+-------+

+-----+--------+------+-------+------------+--------------+--------+-------+

|901|张老大|男|1985|计算机系|北京市海淀区|计算机|98|

|901|张老大|男|1985|计算机系|北京市海淀区|英语|80|

|902|张老二|男|1986|中文系|北京市昌平区|计算机|65|

|902|张老二|男|1986|中文系|北京市昌平区|中文|88|

|903|张三|女|1990|中文系|湖南省永州市|中文|95|

|904|李四|男|1990|英语系|辽宁省阜新市|计算机|70|

|904|李四|男|1990|英语系|辽宁省阜新市|英语|92|

|905|王五|女|1991|英语系|福建省厦门市|英语|94|

|906|王六|男|1988|计算机系|湖南省衡阳市|计算机|90|

|906|王六|男|1988|计算机系|湖南省衡阳市|英语|85|

+-----+--------+------+-------+------------+--------------+--------+-------+

12.计算每个学生的总成绩

mysql>SELECTstudent.id,name,SUM(grade)FROMstudent,score

->WHEREstudent.id=score.stu_id

->GROUPBYid;

+-----+--------+------------+

|id|name|SUM(grade)|

+-----+--------+------------+

|901|张老大|178|

|902|张老二|153|

|903|张三|95|

|904|李四|162|

|905|王五|94|

|906|王六|175|

+-----+--------+------------+

13.计算每个考试科目的平均成绩

mysql>SELECTc_name,AVG(grade)FROMscoreGROUPBYc_name;

+--------+------------+

|c_name|AVG(grade)|

+--------+------------+

|计算机|80.7500|

|英语|87.7500|

|中文|91.5000|

+--------+------------+

14.查询计算机成绩低于95的学生信息

mysql>SELECT*FROMstudent

->WHEREidIN

->(SELECTstu_idFROMscore

->WHEREc_name="计算机"andgrade<95);

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

|902|张老二|男|1986|中文系|北京市昌平区|

|904|李四|男|1990|英语系|辽宁省阜新市|

|906|王六|男|1988|计算机系|湖南省衡阳市|

+-----+--------+------+-------+------------+--------------+

15.查询同时参加计算机和英语考试的学生的信息

mysql>SELECT*FROMstudent

->WHEREid=ANY

->(SELECTstu_idFROMscore

->WHEREstu_idIN(

->SELECTstu_idFROM

->scoreWHEREc_name='计算机')

->ANDc_name='英语');

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

|901|张老大|男|1985|计算机系|北京市海淀区|

|904|李四|男|1990|英语系|辽宁省阜新市|

|906|王六|男|1988|计算机系|湖南省衡阳市|

+-----+--------+------+-------+------------+--------------+

mysql>SELECTa.*FROMstudenta,scoreb,scorec

->WHEREa.id=b.stu_id

->ANDb.c_name='计算机'

->ANDa.id=c.stu_id

->ANDc.c_name='英语';

+-----+--------+------+-------+------------+--------------+

+-----+--------+------+-------+------------+--------------+

|901|张老大|男|1985|计算机系|北京市海淀区|

|904|李四|男|1990|英语系|辽宁省阜新市|

|906|王六|男|1988|计算机系|湖南省衡阳市|

+-----+--------+------+-------+------------+--------------+

16.将计算机考试成绩按从高到低进行排序

mysql>SELECTstu_id,grade

->FROMscoreWHEREc_name='计算机'

->ORDERBYgradeDESC;

+--------+-------+

|stu_id|grade|

+--------+-------+

|901|98|

|906|90|

|904|70|

|902|65|

+--------+-------+

17.从student表和score表中查询出学生的学号，然后合并查询结果

mysql>SELECTidFROMstudent

->UNION

->SELECTstu_idFROMscore;

+-----+

|id|

+-----+

|901|

|902|

|903|

|904|

|905|

|906|

+-----+

18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

mysql>SELECTstudent.id,name,sex,birth,department,address,c_name,grade

->FROMstudent,score

->WHERE

->(nameLIKE'张%'ORnameLIKE'王%')

->AND

->student.id=score.stu_id;

+-----+--------+------+-------+------------+--------------+--------+-------+

+-----+--------+------+-------+------------+--------------+--------+-------+

|901|张老大|男|1985|计算机系|北京市海淀区|计算机|98|

|901|张老大|男|1985|计算机系|北京市海淀区|英语|80|

|902|张老二|男|1986|中文系|北京市昌平区|计算机|65|

|902|张老二|男|1986|中文系|北京市昌平区|中文|88|

|903|张三|女|1990|中文系|湖南省永州市|中文|95|

|905|王五|女|1991|英语系|福建省厦门市|英语|94|

|906|王六|男|1988|计算机系|湖南省衡阳市|计算机|90|

|906|王六|男|1988|计算机系|湖南省衡阳市|英语|85|

+-----+--------+------+-------+------------+--------------+--------+-------+

19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

mysql>SELECTstudent.id,name,sex,birth,department,address,c_name,grade

->FROMstudent,score

->WHEREaddressLIKE'湖南%'AND

->student.id=score.stu_id;

+-----+------+------+-------+------------+--------------+--------+-------+

+-----+------+------+-------+------------+--------------+--------+-------+

|903|张三|女|1990|中文系|湖南省永州市|中文|95|

|906|王六|男|1988|计算机系|湖南省衡阳市|计算机|90|

|906|王六|男|1988|计算机系|湖南省衡阳市|英语|85|

+-----+------+------+-------+------------+--------------+--------+-------+

0 0