mysql实例练习.doc
《mysql实例练习.doc》由会员分享,可在线阅读,更多相关《mysql实例练习.doc(7页珍藏版)》请在冰点文库上搜索。
Sutdent表的定义
字段名
字段描述
数据类型
主键
外键
非空
唯一
自增
Id
学号
INT(10)
是
否
是
是
是
Name
姓名
VARCHAR(20)
否
否
是
否
否
Sex
性别
VARCHAR(4)
否
否
否
否
否
Birth
出生年份
YEAR
否
否
否
否
否
Department
院系
VARCHAR(20)
否
否
是
否
否
Address
家庭住址
VARCHAR(50)
否
否
否
否
否
Score表的定义
字段名
字段描述
数据类型
主键
外键
非空
唯一
自增
Id
编号
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;
+-----+--------+------+-------+------------+--------------+
|id|name|sex|birth|department|address|
+-----+--------+------+-------+------------+--------------+
|901|张老大|男|1985|计算机系|北京市海淀区|
|902|张老二|男|1986|中文系|北京市昌平区|
|903|张三|女|1990|中文系|湖南省永州市|
|904|李四|男|1990|英语系|辽宁省阜新市|
|905|王五|女|1991|英语系|福建省厦门市|
|906|王六|男|1988|计算机系|湖南省衡阳市|
+-----+--------+------+-------+------------+--------------+
4.查询student表的第2条到4条记录
mysql>SELECT*FROMstudentLIMIT1,3;
+-----+--------+------+-------+------------+--------------+
|id|name|sex|birth|department|address|
+-----+--------+------+-------+------------+--------------+
|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('计算机系','英语系');
+-----+--------+------+-------+------------+--------------+
|id|name|sex|birth|department|address|
+-----+--------+------+-------+------------+--------------+
|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;
+-----+--------+------+-------+------------+--------------+--------+-------+
|id|name|sex|birth|department|address|c_name|grade|
+-----+--------+------+-------+------------+--------------+--------+-------+
|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);
+-----+--------+------+-------+------------+--------------+
|id|name|sex|birth|department|address|
+-----+--------+------+-------+------------+--------------+
|902|张老二|男|1986|中文系|北京市昌平区|
|904|李四|男|1990|英语系|辽宁省阜新市|
|906|王六|男|1988|计算机系|湖南省衡阳市|
+-----+--------+------+-------+------------+--------------+
15.查询同时参加计算机和英语考试的学生的信息
mysql>SELECT*FROMstudent
->WHEREid=ANY
->(SELECTstu_idFROMscore
->WHEREstu_idIN(
->SELECTstu_idFROM
->scoreWHEREc_name='计算机')
->ANDc_name='英语');
+-----+--------+------+-------+------------+--------------+
|id|name|sex|birth|department|address|
+-----+--------+------+-------+------------+--------------+
|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='英语';
+-----+--------+------+-------+------------+--------------+
|id|name|sex|birth|department|address|
+-----+--------+------+-------+------------+--------------+
|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;
+-----+--------+------+-------+------------+--------------+--------+-------+
|id|name|sex|birth|department|address|c_name|grade|
+-----+--------+------+-------+------------+--------------+--------+-------+
|901|张老大|男|1985|计算机系|北京市海淀区|计算机|98|
|90