实验1 操作数据库 　　知识点回顾 　　创建数据库是指在数据库系统中划分出一块空间，用来存储相应的数据。在MySQL中，创建数据库须通过SQL语句create database实现。语法格式如下： create database 数据库名; 　　命令操作 　　完成如下操作。 　　（1）查看数据库系统中已经存在的数据库。 　　（2）创建st数据库和ht数据库。 　　（3）再次查看数据库系统中已经存在的数据库。 　　（4）删除ht数据库。 　　（5）再次查看数据库系统中已经存在的数据库，确保ht数据库已经删除。 实验2 操作表及设置约束 　　知识点回顾 　　表是数据库中用来存储数据的最基本的单位，一个数据表称为一个关系。一个表包含若干条记录。创建数据表的基本语法格式如下： create table tb_name（ column_name1 datatype [列级别约束条件], column_name2 datatype [列级别约束条件], ...[表级别约束条件]）; 　　其中，tb_name是创建的数据表名；column_name1等是表中的列名；datatype是表中列的数据类型。 　　命令操作 　　完成如下操作。 　　（1）创建学生信息表student。其中，设置st_id字段为主键，设置st_id和st_nm字段为非空约束。各个字段的长度如表2-1所示。 表2-1 学生信息表student 属性列 数据类型 长度 空值 列约束 说明 st_id varchar 10 not null PK 学生学号 st_nm varchar 10 not null 学生姓名 st_sex char 2 null 学生性别 st_birth date null 出生日期 st_score int null 入学成绩 st_date date null 入学日期 st_from varchar 50 null 学生来源 st_dpid char 6 null 所在系编号 st_mnt varchar 20 null 学生职务 　　（2）创建课程信息表course。其中，设置cs_id字段为主键，设置cs_id和cs_nm 字段为非空约束。各个字段的长度如表2-2所示。 表2-2 课程信息表course 属性列 数据类型 长度 空值 列约束 说明 cs_id varchar 10 not null PK 课程编号 cs_nm varchar 10 not null 课程名称 cs_tm int null 课程学时 cs_sc int null 课程学分 　　（3）创建选课信息表slt_course。其中，设置cs_id和st_id为外键，设置cs_id和st_id字段为非空约束。各个字段的长度如表2-3所示。 表2-3 选课信息表slt_course 属性列 数据类型 长度 空值 列约束 说明 cs_id char 4 not null FK 课程编号 st_id char 9 not null FK 学生学号 score int null 课程成绩 sltdate date null 选课日期 　　（4）创建院系信息表dept。设置dp_id和dp_nm字段为非空约束，各个字段的长度如表2-4所示。 表2-4 院系信息表dept 属性列 数据类型 长度 空值 列约束 说明 dp_id char 2 not null 院系编号 dp_nm varchar 20 not null 院系名称 dp_drt char 10 null 院系主任 dp_tel char 12 null 联系电话 　　（5）将dept表中dp_id列设置为主键。 　　（6）将dept表中dp_nm列设置为唯一性约束。 　　（7）将student表中st_sex字段的默认值设置为“男”。 　　（8）将student表中的st_sex字段的属性改为not null。 　　（9）向slt_course表中添加一个字段id，设置id字段的属性值自动增加。第一行的默认值为1，相邻两个标识之间的增量为1。 实验3 修改数据表 　　知识点回顾 　　修改数据表是指修改数据库中已经定义好的表结构。MySQL中使用alter table语句修改数据表。基本语法如下： alter table tb_namerename [as] new_tb_name //修改表名add [column] create_definition [first|after col_name] //添加新字段add index [index_name] （index_col_name,...） //添加索引名称add primary key （index_col_name,...） //添加主键名称add unique [index_name] （index_col_name,...） //添加唯一索引alter [column] col_name {set default literal|drop default} //修改默认值change [column] old_col_name create_definition //修改字段名和类型modify [column] create_definition //修改字段类型drop [column] col_name //删除字段名称drop primary key //删除主键名称drop index index_name //删除索引名称table_options 　　命令操作 　　完成如下操作。 　　（1）查看dept表的表结构。 　　（2）为dept表添加dp_count字段，数据类型为varchar，长度为3，允许为空。 　　（3）修改dept表的dp_count字段数据类型为int。 　　（4）将dp_count字段的位置调整到dp_id字段的后面。 　　（5）将dp_count字段的字段名改为dp_cnt。 　　（6）删除dept表的dp_cnt字段。 　　（7）再次查看dept表的表结构。 　　（8）将dept表改名为department。 　　（9）将slt_course表的字段id删除。 实验4 更新数据 　　知识点回顾 　　在数据库表建立起来之后，数据库中其实只存在数据表的表结构，而没有任何的数据存在，我们需要往数据表中插入大量的数据记录。在MySQL中，可以通过insert语句来插入数据。第一种是不指定具体的字段名；第二种是给出要插入记录的字段名。具体语法格式如下： insert into 表名 values（值1,值2,...,值n）; 　　在MySQL中，可以列出所有或者部分的字段名来为表插入记录。其基本语法形式如下： insert into 表名（属性1,属性2,...,属性n）values（值1,值2,...,值n）; 　　在MySQL中，可以用一个insert语句同时插入多条记录，其基本语法形式如下： insert into 表名[（属性列表）]values（取值列表1）,（取值列表2）...,（取值列表n）; 　　当数据库表中的数据发生改变时，需要对数据进行修改。在MySQL中，使用update语句来更新数据的基本语法如下： update 表名set 属性名1=值1,属性名2=值2,...,属性名n=值nwhere 条件表达式； 　　命令操作 　　完成如下操作。 　　（1）向student表中插入记录。 1901001,张晓明,男,2000/2/5,450,2019/9/1,四川成都,11,班长1902105,马丽,女,1999/5/16,420,2019/9/1,四川巴中,13,无1803006,陈兵,男,1999/8/3,412,2018/9/1,四川宜宾,12,学习委员1702103,李云霞,女,2020/10/8,390,2019/9/1,陕西渭南,13,无1901003,张飞,男,2020/3/9,432,2019/9/1,四川达州,11,无1704007,赵勇,男,2020/6/1,425,2019/9/1,四川阿坝州,15,无1905006,张明敏,女,2020/5/26,395,2019/9/1,四川达州,16,无1904108,王伶俐,女,2020/5/9,411,2019/9/1,四川巴中,15,无 　　（2）向course表中插入记录。 101,Java程序设计,56,2102,MySQL数据库,64,3103,数据结构,72,2104,计算机网络基础,48,2105,计算机基础,48,2 　　（3）向slt_course表中插入记录。 101,1901001,86,2020/9/9102,1902105,78,2020/9/8101,1905006,82,2020/9/9103,1803006,85,2020/9/8101,1704007,76,2020/9/9104,1901001,75,2020/9/9101,1902105,83,2020/9/8102,1704007,,2020/9/8 　　（4）向dept表中插入记录。 11,计科系,李琦宇,8123456712,电子系,王峰,8765432113,管理系,柳思,8562314711,计科系,李琦宇,8123456711,计科系,李琦宇,81234567 　　（5）修改student表的记录，将马丽的入学成绩改为422。 　　（6）将course表中所有课程的学时改为64，学分改为4。 　　（7）将slt_course表中学号为1901001的学生的101号课程的成绩改为 87。 　　（8）修改管理系主任的联系电话为86677223。 　　（9）删除“张飞”的相关记录。 实验5 数据查询——单表查询 　　知识点回顾 　　查询是针对表中存储的数据进行“筛选”，把符合条件的行和列组织起来，形成一个类似表的结构，也就是查询的结果。 　　MySQL中查询使用select语句，语法格式如下: select [all | distinct] 属性列表from 表名或视图列表[where 条件表达式][group by 属性名] [having条件表达式][order by 属性名] [asc | desc][limit [,]] 　　其中，select...from...是一个查询语句必须包含的部分。[]为可选项。 　　（1）select：用于显示查询返回的列。 　　（2）from：用于指定引用的列所在的表或视图。 　　（3）where：用于指定限制返回的行的搜索条件。 　　（4）group by：用于对查询数据进行分组。 　　（5）having：用于对分组或聚合后的函数进行分组。 　　（6）order by：用于对查询结果进行排序，asc表示升序，desc表示降序。 　　（7）limit：用于限制该子句查询出来的数据数量。 　　命令操作 　　写出如下查询命令。 　　（1）查询student表中所有学生的信息。 　　（2）查询所有学生的学号、姓名和来源。 　　（3）查询所有课程的信息，给每门课程的学分加1。 　　（4）查询所有学生的成绩信息，成绩以score*0.7显示出来。 　　（5）重命名查询结果。 　　① 使用as关键字为dept表中如下属性指定字段名：系号、系名、系主任、联系电话。 　　② 使用空格为dept表中如下属性指定字段名：系号、系名、系主任、联系电话。 　　（6）查询所有学生的学号、姓名、性别、班级（学号前两位为班级号）。 　　（7）查询所有男生的信息。 　　（8）查询来自四川达州的女生的信息。 　　（9）查询学分为3的课程的信息。 　　（10）查询成绩在80分以上的学生的学号。 　　（11）查询来自四川达州和四川巴中的学生的信息。 　　（12）查询来自四川达州和四川巴中的女生的信息。 　　（13）使用like关键字查询。 　　① 查询“计算机”开头的课程的信息。 　　② 查询所有姓“张”的学生的信息。 　　③ 查询所有名字为三个字且姓“张”的学生的信息。 　　④ 查询所有课程中包含“数据”的课程的信息。 　　（14）使用in关键字查询。 　　① 查询11系、12系、13系学生的信息。 　　② 查询所有姓“张”、姓“王”、姓“李”、姓“赵”的学生的信息。（答案不唯一） 　　（15）使用between...and...查询。 　　① 查询成绩为60~80的学生的学号。 　　② 查询选修了101课程，成绩为60~80的学生的学号。 　　③ 查询在1999.7.1至2000.7.1出生的学生的信息。 　　（16）使用[not] null关键字查询。 　　① 查询所有生源地为空的学生的信息。 　　② 查询所有生源地非空的学生的信息。 　　③ 查询成绩为空的学生的学号。 　　④ 查询选修了102号课程且成绩为空的学生的信息。 实验6 查询排序 　　知识点回顾 　　select子句的查询结果是从表中通过行列的条件限制筛选出来的一部分数据。如果我们想让数据按照某种顺序显示出来，可以使用order by关键字进行排序。order by关键字可以实现升序或者降序排序。基本语法格式如下： select [all | distinct] 属性列表from 表名或视图列表[where 条件表达式][order by 属性名] [asc | desc] 　　其中，“属性名”参数表示按照该字段进行排序；asc参数表示按照升序排序；desc参数表示按照降序排序。asc和desc是可选项，在不写的情况下，默认是升序排序。 　　多重排序是指按照多个字段进行排序，order by子句后列举排序的多个字段中间用逗号分隔。 　　命令操作 　　写出以下查询命令。 　　（1）按照年龄由小到大的顺序显示学生的信息。 　　（2）查询课程信息，按照课程名称降序显示。 　　（3）查询选修了101课程的学生的学号，按成绩降序显示。 　　（4）查询11系学生的学号、姓名、年龄，按照年龄升序显示。 　　（5）查询课程信息，按照课程的学分降序排列，再按照课程名称升序排列。 实验7 查询统计与汇总 　　知识点回顾 　　group by 关键字可以将查询结果按某个字段或多个字段进行分组，字段中值相等的为一组。基本语法格式如下： group by 属性名 [having 条件表达式] [with rollup] 　　其中，“属性名”是指按照该字段不同的值进行分组。“having 条件表达式”的作用可以类比where子句，当分组后的查询结果满足having条件表达式，结果才被显示。with rollup关键字将会在所有记录的最后加上一条记录，该记录是上面所有记录的总和。 　　在MySQL中也有一些常用的函数，可以对一组值进行计算，返回单个值，我们称为聚合函数。 　　部分常用聚合函数及说明如表7-1所示。 表7-1 部分常用的聚合函数 函数名称 说明 SUM（） 选取结果集中所有值的总和 COUNT（） 选取结果集中所有记录行的数目 MAX（） 选取结果集中所有值的最大值 MIN（） 选取结果集中所有值的最小值 AVG（） 选取结果集中所有值的平均值 　　命令操作 　　写出如下查询命令。 　　（1）查询学生总人数。 　　（2）查询来自四川达州的学生的总数。 　　（3）查询选修101号课程的人数。 　　（4）查询被选修课程的数量。 　　（5）查询11班学生的平均入学成绩。 　　（6）查询学号为1901001的学生选修课程的数量、总分及平均分（为选课数量、总分、平均分重命名）。 　　（7）求各个课程号和相应的选课人数。 　　（8）统计各班人数。 　　（9）依次按班级、系号分类统计学生的人数、入学平均分。 　　（10）查询被选修的课程中平均分在75分以上的课程号和平均分。 　　（11）查询选修了两门以上（含两门）课程的学生的学号。 实验8 数据查询——多表连接查询 　　知识点回顾 　　在进行数据库设计时，要求每个表尽量简单，存放不同的数据，最大限度地减少数据冗余。而在实际查询过程中，往往需要从两个以上的表中选取数据，生成临时的查询结果。这就需要我们通过不同表中存在的意义相同的字段来建立多表连接查询。 　　内连接是当两个表中有相同意义的字段时，就可以通过该字段来连接两个表。内连接通常使用“=”比较运算符来判断两个表的列数据是否相同。内连接可以查询两个及两个以上的表。 　　外连接虽然也需要通过多表中意义相同的字段建立连接，但它不仅在字段值相等时可以查询出相应的记录，当该字段值不相等时也可以查询出相应的记录。外连接分为左外连接和右外连接。使用outer join关键字，关键字outer可以省略。其基本格式语法如下： select 属性名列表from 表1 left | right join 表2on 表1.属性名1=表2.属性名2; 　　其中，“表1”“表2”是要进行外连接的两个表；on后面是两个表的意义相同的字段，同内连接。 　　命令操作 　　写出以下查询命令。 　　（1）查询学生的学号、姓名、系别及所选课程编号。 　　（2）查询学生的学号、姓名、所选课程名称。 　　（3）查询学生的学号、姓名、所选课程名称及成绩。 　　（4）查询学生的学号、姓名、所在系别的名称。 　　（5）查询学生的学号、姓名、所在系别的名称及联系电话。 　　（6）查询选修了101号课程的学生的学号、姓名、成绩。 　　（7）查询选修了“数据结构”课程的学生的姓名、学号、成绩。 　　（8）查询没有选修任何课程的学生的学号、姓名。 　　（9）查询不及格的学生的姓名、学号。 　　（10）查询入学成绩在420分以上的学生的学号、姓名、所在系的名称及联系电话。 　　（11）查询计科系学生的信息。 　　（12）查询计科系学生的选修课成绩。 实验9 数据查询——子查询 　　知识点回顾 　　子查询也叫嵌套查询，是指查询语句里面嵌套另外的查询语句，也就是一条语句中至少有两个select语句。内层查询语句的查询结果可以作为外层查询的条件使用。子查询中可以包括in、not in、any、all、exists等关键字，还可以包括运算符，如“=”“!=”“>”“<”等。 　　命令操作 　　写出以下查询命令。 　　（1）查询计科系学生的信息。 　　（2）查询不及格的学生的姓名、学号。 　　（3）查询选修了课程的学生的姓名、学号、性别。 　　（4）查询“张飞”同学所在班级的学生的姓名、学号。 　　（5）查询101号课程选课成绩比所有科目的平均成绩高的学生的学号、姓名。 　　（6）查询姓“张”的学生所在的系的名称和联系电话。 　　（7）查询女生的选修课的平均成绩。 　　（8）查询班长的选修课成绩。 　　（9）查询班长的选修课名称、学分和成绩。 　　（10）查询入学成绩在420分以上的学生所在系的名称及联系电话。 　　（11）查询入学成绩最高的学生所在系的名称及联系电话。 　　（12）查询计科系学生选修课的平均成绩。 实验10 查询综合练习（1） 　　知识点回顾 　　查询是针对表中存储的数据进行“筛选”，把符合条件的行和列组织起来，形成一个类似表的结构，也就是查询的结果。 　　MySQL中查询使用select语句的语法格式见实验5。 　　命令操作 　　新建数据库，命令如下： use 数据库名称; 　　完成如下操作。 create table student（ sno varchar（3） not null, sname varchar（4） not null, ssex varchar（2） not null, sbirthday datetime, class varchar（5）） collate='utf8_bin' engine=innodb;create table course（ cno varchar（5） not null, cname varchar（10） not null, tno varchar（10） not null） collate='utf8_bin' engine=innodb;create table score（ sno varchar（3） not null, cno varchar（5） not null, degree numeric（10, 1） not null） collate='utf8_bin' engine=innodb;create table teacher（ tno varchar（3） not null, tname varchar（4） not null, tsex varchar（2） not null, tbirthday datetime not null, prof varchar（6）, depart varchar（10） not null）;insert into student （sno,sname,ssex,sbirthday,class） values （'108','曾华','男','1977-09-01',95033）;insert into student （sno,sname,ssex,sbirthday,class） values （'105','匡明','男','1975-10-02',95031）;insert into student （sno,sname,ssex,sbirthday,class） values （'107','王丽','女','1976-01-23',95033）;insert into student （sno,sname,ssex,sbirthday,class） values （'101','李军','男','1976-02-20',95033）;insert into student （sno,sname,ssex,sbirthday,class） values （'109','王芳','女','1975-02-10',95031）;insert into student （sno,sname,ssex,sbirthday,class） values （'103','陆君','男','1974-06-03',95031）;insert into course（cno,cname,tno） values （'3_105','计算机导论',825）;insert into course（cno,cname,tno） values （'3_245','操作系统',804）;insert into course（cno,cname,tno） values （'6_166','数据电路',856）;insert into course（cno,cname,tno） values （'9_888','高等数学',100）;insert into score（sno,cno,degree） values （'103','3_245',86）;insert into score（sno,cno,degree） values （'105','3_245',75）;insert into score（sno,cno,degree） values （'109','3_245',68）;insert into score（sno,cno,degree） values （'103','3_105',92）;insert into score（sno,cno,degree） values （'105','3_105',88）;insert into score（sno,cno,degree） values （'109','3_105',76）;insert into score（sno,cno,degree） values （'101','3_105',64）;insert into score（sno,cno,degree） values （'107','3_105',91）;insert into score（sno,cno,degree） values （'108','3_105',78）;insert into score（sno,cno,degree） values （'101','6_166',85）;insert into score（sno,cno,degree） values （'107','6_106',79）;insert into score（sno,cno,degree） values （'108','6_166',81）;insert into teacher（tno,tname,tsex,tbirthday,prof,depart） values （'804','李诚','男','1958-12-02','副教授','计算机系'）;insert into teacher（tno,tname,tsex,tbirthday,prof,depart） values （'856','张旭','男','1969-03-12','讲师','电子工程系'）;insert into teacher（tno,tname,tsex,tbirthday,prof,depart） values （'825','王萍','女','1972-05-05','助教','计算机系'）;insert into teacher（tno,tname,tsex,tbirthday,prof,depart） values （'831','刘冰','女','1977-08-14','助教','电子工程系'）; 　　1. 根据数据表写出查询语句 　　（1）查询student表中的所有记录的sname、ssex和class列。 　　（2）查询教师所在的单位，即查询不重复的depart列。 　　（3）查询student表的所有记录。 　　（4）查询score表中成绩为60~80的所有记录。 　　（5）查询score表中成绩为85、86或88的记录。 　　（6）查询student表中95031班或性别为“女”的学生的记录。 　　（7）以class降序查询student表的所有记录。 　　（8）以cno升序、degree降序查询score表的所有记录。 　　（9）查询95031班的学生人数。 　　（10）查询score表中最高分的学生学号和课程号。 　　（11）查询3_105号课程的平均分。 　　（12）查询score表中至少有5名学生选修并以3开头的课程的平均分数。 　　（13）查询最低分大于70且最高分小于90的sno列。 　　（14）查询所有学生的sname、cno和degree列。 　　（15）查询所有学生的sno、cname和degree列。 　　（16）查询95033班所选课程的平均分。 　　2. 使用如下命令建立一个grade表并完成查询 create table grade（low numeric（10,0）,upp numeric（10,0）,rank varchar（1）） collate='utf8_bin' engine=innodb;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'）;commit; 　　（1）查询选修3_105课程且成绩高于109号学生成绩的所有学生的记录。 　　（2）查询score中选学一门以上课程的学生中分数为非最高分成绩的记录。 　　（3）查询成绩高于学号为109、课程号为3_105的课程成绩的所有记录。 　　（4）查询和学号为108的学生同年出生的所有学生的sno、sname和sbirthday列。 　　（5）查询“张旭”教师任课的学生成绩。 　　（6）查询选修某课程的学生人数多于5人的教师姓名。 　　（7）查询95033班和95031班全体学生的记录。 　　（8）查询存在有85分以上成绩的课程cno。 　　（9）查询出“计算机系”教师所教课程的成绩表。 　　（10）查询“计算机系”与“电子工程系”不同职称的教师的tname和prof。 　　（11）查询选修编号为3_105课程且成绩高于选修编号为3_245的学生的cno、sno和degree，并按degree列从高到低次序排序。 　　（12）查询选修编号为3_105且成绩高于选修编号为3_245课程的学生的cno、sno和degree。 　　（13）查询所有教师和学生的name、sex和birthday。 　　（14）查询所有“女”教师和“女”学生的name、sex和birthday。 　　（15）查询成绩比该课程平均成绩低的学生的成绩表。 　　（16）查询所有任课教师的tname和depart。 　　（17）查询所有未讲课的教师的tname和depart。 　　（18）查询至少有2名男生的班号。 　　（19）查询student表中不姓“王”的学生记录。 　　（20）查询student表中每个学生的姓名和年龄。 　　（21）查询student表中最大和最小的sbirthday日期值。 　　（22）以班号和年龄从大到小的顺序查询student表中的全部记录。 　　（23）查询“男”教师及其所上的课程。 　　（24）查询最高分学生的sno、cno和degree列。 　　（25）查询和“李军”同性别的所有学生的sname。 　　（26）查询所有选修“计算机导论”课程的“男”学生的成绩表。 实验11 查询综合练习（2） 　　知识点回顾 　　MySQL中查询使用select语句，语法格式见实验5。 　　命令操作 　　完成如下操作。 create table dept（ deptno varchar（10） primary key, dname varchar（10））;create table emp（ empno varchar（10） primary key, ename varchar（10）, job varchar（10）, mgr varchar（10）, sal varchar（10）, deptno varchar（10） references dept（deptno））;#drop table dept;#drop table emp;insert into dept values （'1','事业部'）;insert into dept values （'2','销售部'）;insert into dept values （'3','技术部'）;insert into emp values （'01','jacky','clerk','tom','1000','1'）;insert into emp values （'02','tom','clerk','','2000','1'）;insert into emp values （'07','biddy','clerk','','2000','1'）;insert into emp values （'03','jenny','sales','pretty','600','2'）;insert into emp values （'04','pretty','sales','','800','2'）;insert into emp values （'05','buddy','jishu','canndy','1000','3'）;insert into emp values （'06','canndy','jishu','','1500','3'）;select * from dept;select * from emp; 　　（1）列出emp表中各部门的部门号、最高工资、最低工资。 　　（2）列出emp表中各部门job为clerk的员工的最高工资、最低工资和部门号。 　　（3）对于emp表中最低工资小于2000的部门，列出job为clerk的员工的部门号、最高工资、最低工资。 　　（4）根据部门号由高到低、工资由低到高列出每个员工的姓名、部门号、工资。 　　（5）列出buddy所在部门中每个员工的姓名与部门号。 　　（6）列出每个员工的姓名、工作、部门号、部门名。 　　（7）列出emp表中工作为clerk的员工的姓名、工作、部门号、部门名。 　　（8）如果emp表中有管理者，列出管理者姓名（管理者外键为mgr）。 　　（9）对于dept表，列出所有部门号、部门名，同时列出各部门工作为clerk的员工名与工作。 　　（10）对于工资高于本部门平均水平的员工，列出部门号、姓名、工资，并按部门号排序。 　　（11）对于emp表，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序。 　　（12）对于emp表中工资高于本部门平均水平且人数多于1人的，列出员工数、部门号、平均工资，按部门号排序。 　　（13）对于emp表中低于自己工资至少5人的员工，列出其部门号、姓名、工资，以及工资少于自己的人数。 实验12 查询综合练习（3） 　　知识点回顾 　　在MySQL中查询使用select语句，语法格式见实验5。 　　命令操作 　　完成如下操作。 create table s（ sid varchar（10） primary key, sn varchar（10）, sd varchar（10）, sa varchar（10））;#drop table s;create table c（ cid varchar（10） primary key, cn varchar（10））;#drop table c;create table sc（ sid varchar（10） references s（sid）, cid varchar（10） references c（cid）, g int）;#drop table sc;insert into s values（'001','汪洋','计算机学院','25'）;insert into s values（'002','王世俊','计算机学院','23'）;insert into s values（'003','王鑫','软件学院','22'）;insert into s values（'004','陈桂超','化学学院','23'）;insert into s values（'005','通信生','软件学院','25'）;select * from s;insert into c values（'01','化学'）;insert into c values（'02','语文'）;insert into c values（'03','数学'）;insert into c values（'04','英语'）;select * from c;insert into sc values（'001','01',70）;insert into sc values（'001','02',80）;insert into sc values（'001','03',90）;insert into sc values（'001','04',100）;insert into sc values（'002','01',80）;insert into sc values（'002','02',90）;insert into sc values（'003','01',60）;insert into sc values（'004','02',70）;insert into sc values（'003','03',50）;insert into sc values（'004','04',60）;select * from sc; 　　（1）使用标准SQL嵌套语句查询选修课程名称为“语文”的学员学号、单位和姓名。 　　（2）使用标准SQL嵌套语句查询选修课程编号为02的学员姓名和所属单位。 　　（3）使用标准SQL嵌套语句查询不选修课程编号为02的学员姓名和所属单位。 　　（4）使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位。 　　（5）查询选修了课程的学员人数。 　　（6）查询选修课程超过2门的学员学号、姓名、单位。 实验13 查询综合练习（4） 　　知识点回顾 　　在MySQL中查询使用select语句，语法格式见实验5。 　　命令操作 　　完成如下操作。 　　（1）创建一个名称为mydb1的数据库。 　　（2）查看所有数据库。 　　（3）创建一个使用UTF-8字符集的mydb2数据库。 　　（4）创建一个使用UTF-8字符集并带校对规则的mydb3数据库。 　　（5）显示库的创建信息。 　　（6）删除前面创建的mydb1数据库。 　　（7）查看服务器中的数据库，并把其中某一个库的字符集修改为GB 2312。 　　（8）使用mydb1数据库。 　　（9）查看库中所有表。 　　（10）查看表的创建细节。 　　（11）查看表的结构。 　　（12）创建一个员工表。 　　（13）在员工表中增加一个image列。 　　（14）修改job列，使其长度为60。 　　（15）删除sex列。 　　（16）表名改为user。 　　（17）修改表的字符集为UTF-8。 　　（18）列名name修改为username。 　　（19）将表名改回employee。 　　（20）添加一名员工的信息： （1,'aaa','1980-09-09','1980-09-09','bbb',1000,'bbbbbbbb'） 　　（21）查看插入的数据。 　　（22）使用insert语句向表中插入一个员工的信息。 　　（23）插入失败后的解决方案。 　　（24）显示失败后的解决方案。 　　（25）将所有员工工资修改为5000元。 　　（26）将姓名为aaa的员工工资修改为3000元。 　　（27）将姓名为aaa的员工工资修改为4000元，job改为ccc。 　　（28）将员工aaa的工资在原有基础上增加1000元。 　　（29）删除表中名称为zs的记录。 　　（30）删除表中所有记录。 　　（31）使用truncate删除表中记录。 实验14 函数 　　知识点回顾 　　MySQL提供了众多功能强大、方便易用的函数。使用这些函数，可以极大地提高用户对数据库的管理效率。 　　字符串函数是最常用的一种函数，主要用来处理数据库中的字符串数据。字符串函数包括求字符串长度、合并字符串、替换字符串、获取指定长度的字符串等函数。 　　MySQL中的数学函数也是比较常用的一类函数，可以利用数学函数对数值进行数学运算，例如获取数值的绝对值、获取随机数等。 　　MySQL中的日期和时间函数可以用来返回系统日期和时间等，也可以计算两个日期间相差了多少天。 　　命令操作 　　利用MySQL的函数进行如下运算。 　　（1）计算3的3次方。 　　（2）计算16的平方根。 　　（3）返回不大于-3.5的最大整数。 　　（4）计算PI（圆周率）对应的角度。 　　（5）将字符串'BEIJING'变成小写。 　　（6）截取字符串'sichuanchengdu'的后7位字符。 　　（7）截取字符串'sichuanchengdu'的前7位字符。 　　（8）从字符串'chengdu is a beautiful city'的第8个字符位置开始，截取一个长度为8的子串。 　　（9）将字符串'mybook'逆序输出。 　　（10）计算当前日期的月份。 　　（11）计算当前日期是本年的第几天。 　　（12）通过函数获取当前日期的月份。 　　（13）计算今天到春节间隔的天数。 　　（14）计算学生的年龄并显示出来。 实验15 运算符 　　知识点回顾 　　运算符是用来连接表达式中各个操作数的符号，其作用是用来指明对操作数所进行的运算。MySQL运算符包括4类，分别是算术运算符、比较运算符、逻辑运算符和位运算符。分别如表15-1~表15-4所示。 表15-1 算术运算符 运 算 符 作 用 + 加法 - 减法 * 乘法 / , div 除法，返回商 % , mod 除法，返回余数 表15-2 比较运算符 符 号 表达式的形式 作 用 = x1=x2 判断x1是否等于x2 <>或!= x1<>x2或x1!=x2 判断x1是否不等于x2 <=> x1<=>x2 判断x1是否等于x2 > x1>x2 判断x1是否大于x2 >= x1>=x2 判断x1是否大于等于x2 < x1> 按位右移。m>>n表示m的二进制数向右移n位，左边补上n个0。例如，二进制数011右移1位后变成001，最后一个1直接被移出 　　命令操作 　　完成如下操作。 　　（1）在MySQL中执行如下算术表达式。 　　① 4*2-3 　　②（3+6）/3 　　③（3+6）/2 　　④ 9 div 2 　　⑤ MOD（9,2） 　　（2）在MySQL中执行下面的比较运算符表达式。 　　① 5>6 　　② 25>=31 　　③ 20<=30 　　④ 5<=>5 　　（3）在MySQL中执行下面的逻辑运算符表达式。 　　① -2&&3 　　② -3||null 　　③ 1 XOR 0 　　④ !1 　　（4）在MySQL中执行下面的位运算。 　　① 5&7 　　② 11|15 　　③ 12^13 　　④ ~12 实验16 存储过程和触发器 　　知识点回顾 　　存储过程（stored procedure）是把经常使用的一组SQL语句组合在一起，经过编译后以一个名称存储在数据库中。 　　在MySQL中，创建存储过程的基本语法格式如下： create procedure sp_name（[[in|out|inout] param_name type[,...]]）body 　　其中， 　　（1）create procedure：用来创建存储过程的关键词。 　　（2）sp_name：存储过程的名称。 　　（3）in | out | inout：参数的类型。in为输入参数；out为输出参数；inout既可以表示输入参数，也可以表示输出参数。 　　（4）param_name：参数的名称。 　　（5）type：参数类型，该类型可以是MySQL数据库中的任意类型。 　　（6）body：存储过程体，可以用begin...end来表示SQL代码的开始和结束。 　　触发器这种特殊的存储过程，主要是通过当某个事件发生时自动被触发执行。当在数据表中插入记录、修改记录或者删除记录时，MySQL就会自动执行触发器所定义的SQL语句，从而确保对数据的处理必须符合由这些SQL语句所定义的规则。 　　创建触发器的基本语法格式如下： create trigger trigger_namebefore|after trigger_even on table_name for each rowbeginroutine_bodyend 　　其中， 　　（1）trigger_name：触发器的名称（命名最好见名知意）。 　　（2）before | after：触发器执行的时间，before指在触发事件之前执行触发语句，after表示触发事件之后执行触发语句。 　　（3）trigger_even：触发的条件，当有insert、update、delete操作时，触发器被触发。 　　（4）table_name：触发事件操作的表的名称。 　　（5）for each row：任何一条记录上的操作满足触发事件时都会触发该触发器。 　　（6）routine_body：触发器被触发时要执行的触发语句。 　　命令操作 　　完成如下操作。 　　（1）编写存储过程stu_gra，查询计科系学生的信息。 　　（2）编写存储过程stu_dep，根据给定的系别号，查询该系学生的姓名、成绩。 　　（3）编写存储过程cou_stu，根据给定的学号，查询该学生所选课程的学分。 　　（4）创建触发器del_inf，当有学生退学时，删除student表中该学生的信息，触动触发器，同时删除该学生对应的选课和成绩信息。 　　（5）删除触发器del_inf。 　　（6）删除存储过程stu_gra。 实验17 索引 　　知识点回顾 　　索引是创建在表中通过对指定的字段进行排序的一种结构，其作用与目录在书籍中的作用类似，主要是提高查找信息的速度。索引的建立是不可见的，但是其作用可以从数据的检索效率中体现出来。 　　MySQL中创建普通索引的语法格式如下： create table 表名（字段名 数据类型， … 字段名 数据类型, index 索引名（索引字段））; 　　在已经存在的表中添加索引，语法格式如下： alter table 表名 add index 索引名（索引字段）; 　　命令操作 　　请回答以下问题并进行操作。 　　（1）表中建立了索引后，导入大量数据为什么会很慢？ 　　（2）MySQL中索引、主键和唯一性的区别是什么？ 　　（3）创建winf表。 　　创建表的同时在id字段上创建名为index_id的唯一性索引，而且以降序的格式排列。winf表的结构表17-1所示。 表17-1 winf表 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 id 编号 int（10） 是 否 是 是 是 name 姓名 varchar（20） 否 否 是 否 否 type 职位类别 varchar（10） 否 否 否 否 否 address 工作地址 varchar（50） 否 否 否 否 否 wages 备注信息 text 否 否 否 否 否 contents 工作内容 int 否 否 否 否 否 extra 附加内容 text 否 否 否 否 否 　　① 为name字段创建索引index_name。 　　② 使用alter table语句在type和address上创建名为index_ta的索引。 　　③ 使用alter table语句在extra字段上创建名为index_ext的全文索引。 　　④ 删除winf表的唯一性索引index_id。 　　提示：创建索引时，请注意区分不同的类别。 实验18 视图 　　知识点回顾 　　视图是虚表，是从一个或几个基本表（或视图）中导出的表，视图本身不占用存储空间。视图只展示用户感兴趣的字段，而屏蔽其他字段，这样既可以方便用户的操作，又可以保障数据库系统的安全性。 　　在MySQL中创建视图的基本语法格式如下： create [algorithm = temptable/merge/undefined] view 视图名 as select语句; 　　命令操作 　　创建animal表，表结构如表18-1所示。按照要求完成下列操作。 表18-1 animal表 字段名 字段描述 数据类型 主键 外键 非空 唯一 自增 id 编号 int（4） 是 否 是 是 是 name 名称 varchar（20） 否 否 是 否 否 kinds 种类 varchar（8） 否 否 是 否 否 legs 腿的数量 int（4） 否 否 否 否 否 behavior 习性 varchar（50） 否 否 否 否 否 　　（1）在animal表的基础上创建视图ani_view，该视图包含字段id、name和legs。 　　（2）向ani_view视图中插入几条记录，并查看giant视图。 　　（3）查询该视图中动物的名称。 　　（4）查询该视图中腿有4条的动物有哪些。 　　（5）删除某一种动物的信息，并查看视图。 　　（6）删除ani_view视图。 实验19 用Java访问MySQL数据库 　　Java是现在最流行的程序语言之一，是由Sun公司开发的程序设计语言。Java语言是一种面向对象的编程语言，而且具有跨平台性和高效的网络编程特性。Java语言可以通过MySQL数据库的接口访问MySQL数据库。下面我们要练习的内容包括：Java如何连接MySQL数据库；Java如何操纵MySOL数据库；Java如何备份MySQL数据库；Java如何还原MySQL数据库。通过练习编写访问MySQL数据库的Java代码，希望读者不仅可以了解Java语言访问MySQL数据库的原理和方法，提高对MySQL数据库的理解，而且还可以提高Java编程的水平。 　　1. Java连接MySQL数据库 　　Java 语言可以通过JDBC（Java Database Connectivity，Java 数据库连接）来访问MySQL数据库。JDBC的编程接口提供的接口和类与MySQL数据库建立连接，然后将SQL语句的执行结果进行处理。但这需要一个MySQL数据库的JDBC驱动程序。下面介绍Java连接MySQL数据库的方法。 　　（1）下载JDBC驱动MySQL Connector/J 　　读者可以在MySQL的官方网站下载JDBC驱动。我们以MySQL Connector/J 5.1的JDBC驱动程序为例来进行讲解。MySQL Connector/J 5.1的下载网址为http:/dev.mysql.com/downloads connector/j/5.1.html。在下载页面有Source and Binaries（tar.gz）和 Source and Binaries（zip）两个下载选项。前者主要用于Linux 操作系统，后者主要用于Windows 操作系统。下载后的文件分别是mysql-connector-java-5.1.10.tar.gz和 mysql-connector-java-5.1.10.zip。这里面都包含驱动的源代码和二进制包。源代码可以自行进行编译。二进制包是编译好的驱动，名称为mysql-connector-java-5.1.10-bin jar。 　　（2）安装MySQL Connector/J驱动 　　Shell或DOS窗口和Eclipse等编程工具中使用JDBC的方式是不一样的，前者需要将JDBC驱动的路径添加到环境变量中，后者可以直接将JDBC驱动添加到Eclipse等工具中。 　　在Windows操作系统中右击“我的电脑”图标，在弹出的菜单中选择“属性”命令，然后单击“高级”选项卡中的“环境变量”按钮，在弹出的窗口中可以看到用户环境变量。在classpath变量中添加mysql-connector-java-5.1.10-bin.jar的路径。在DOS窗口中执行的Java语句中需要调用JDBC驱动时，系统会自动到classpath变量中设置的路径中去查找。 　　解压完成后，可以看到源代码和jar包文件。需要在/etc/profile文件中添加JDBC驱动的jar包的路径。假设jar包mysql-connector-java-5.1.10-bin.jar存储在/home/mine目录下。用vi工具打开/etc/profile文件，并按照下面的方式添加环境变量。 CLASSPATH=.;/home/mine/mysql-connector-java-5.1.10-bin.jarexport CLASSPATH 　　如果读者使用的是Eclipse或者MyEclipse，可以直接将JDBC驱动添加到编程工具中。选择Window→Preferences命令，进入Preferences对话框，在该对话框中可以选择Java→Build Path→User Libraris选项。在出现的窗格中可以添加新的jar包，如图19-1所示。 图19-1　Preferences对话框 　　在对话框右边单击New...按钮，可以建立库文件并为其命名，可以命名为MySQL-JDBC。单击Add JARs按钮，可以添加新的jar包，在此处将mysql-connector-java-5.1.10-bin.jar添加到Eclipse中。 　　右击工程名并选择Build Path命令，在弹出的下拉菜单中选择Add Library命令。再在弹出的Add Library对话框中选择User Library选项，然后单击Next按钮。在新对话框中选择MySQL-JDBC选项，然后单击Finish按钮，这样JDBC驱动就添加到Eclipse中。 　　注意：在Windows操作系统中，在classpath变量中添加JDBC驱动的路径后可能需要重新启动计算机。在MyEclipse中可以使用上述方法添加JDBC驱动。如果在MyEclipse中创建Web工程，可以直接将JDBC驱动的jar包复制到指定工程的WebRoot→WEB-INF→lib目录下。 　　（3）连接MySQL数据库 　　在java.sql包中存在DriverManager类、Connnection接口、Statement接口和ResultSet接口。这些类和接口的作用如下。 　　? DriverManager类：主要用于管理驱动程序和连接数据库。 　　? Connection接口：主要用于管理建立好的数据库连接。 　　? Statement接口：主要用于执行SQL语句。 　　? ResultSet接口：主要用于存储数据库返回的记录。 　　通过DriverManager类和Connection 接口实现连接数据库的功能。首先使用java.lang.Class类中的forName（）方法来指定JDBC驱动的类型。forName（）方法指定MySQL驱动的语句如下： Class.forName（"com.mysql.jdbc.Driver"）; 　　其中，com.mysql.jdbc.Driver为MySQL的JDBC驱动中的Driver类。除了这个Driver类以外，还有另外一个Driver类，它存储在org.git.mm.mysql包下，因此也可以使用forName（）方法指定MySQL驱动，语句如下： Class.forName（"org.git.mm.mysql.Driver"）; 　　org.git.mm.mysql.Driver与com.mysql.jdbc.Driver的作用完全相同。org.git.mm.mysql.Driver类的代码如下： Public class Driver extends com.myql.jdbc.Driver{ Public Driver（） throws SQLException{ Super（）; }} 　　从上面的代码可以看出，org.git.mm.mysql.Driver继承了com.mysql.jdbc.Driver。需要注意的是，使用forName（）方法时需要抛出ClassNotFoundException异常。 　　说明：org.git.mm.mysql.Driver 是早期的JDBC驱动程序。随着MySQL官方推出com.mysql.jdbc.Driver以后，org.git.mm.mysql.Driver慢慢地退出了历史舞台。但是为了保证早期使用org.git.mm.mysql.Driver的Java代码的可用性，在新的JDBC驱动程序中添加了名为org.git.mm.mysql.Driver的类。 　　指定了MySQL驱动程序之后，就可以使用DriverManager类和Connection接口来连接数据库。在DriverManager类中提供了getConnection（）方法。getConnection（）方法有以下3个输入参数。 　　? url：指定JDBC的数据源，其基本形式是“jdbc:mysql:数据源”。数据源中包括了IP或主机名、端口号和数据库名。 例如，jdbc:mysql://localhost:3306/hjh中，localhost表示本地计算机，3306是MySQL的端口号，hjh是数据库名。 　　? user：MySQL数据库的用户名。 　　? password：指定用户名的密码。 　　getConnection（）方法的返回值是Connection对象。下面是使用getConnection（）方法的语句： Connection connection=DriverManager.getConnection（url,user,password）;//创建Connection对象 　　【示例1】 本示例连接本地计算机的MySQL数据库。MySQL使用默认端口3306，连接的数据库是hjh。使用用户root来连接，其密码为huang。连接MySQL的语句如下： Connection connection=DriverManager.getConnection（"jdbc:mysql://localhost:3306/hjh","root","huang"）; 　　通过这个语句就可以连接到MySQL的hjh数据库了。该语句也可以写成如下形式： String url="jdbc:mysql://localhost:3306/hjh";String user="root";String password="huang";Connection connection=DriverManager.getConnection（url,user,password）; 　　这个语句要调用java.sql包下面的DriverManager类和Connection接口。Connection接口是在JDBC驱动中实现的。JDBC驱动的com.mysql.jdbc包下有Connection类。使用getConnection（）方法时需要抛出SQLException异常。 　　2. 用Java操作MySQL 　　连接MySQL数据库之后，可以对MySQL数据库中的数据进行查询、插入、更新和删除等操作。Statement接口主要用来执行SQL语句，其中定义一些执行SQL语句的方法。SQL语句执行后返回的结果由ResultSet接口管理。通过这两个接口，Java可以方便地操作MySQL数据库。下面详细介绍用Java操作MySQL数据库的方法。 　　（1）创建Statement对象 　　Connection对象调用createStatement（）方法来创建Statement对象，其代码如下： Statement statement=connection.createStatement（）; 　　其中，statement是Statement对象，connection是Connection对象。createStatement（）方法返回Statement对象。通过这个Java语句就可以创建Statement对象，Statement对象创建成功后，可以调用其中的方法来执行SQL语句。 　　（2）使用select语句查询数据 　　Statement对象可以调用executeQuery（）方法执行select语句，select语句的查询结果返回给ResultSet对象。调用executeQuery（）方法的代码如下: ResultSet result=statement.executeQuery（"select语句"）; 　　通过该语句可以将查询结果存储到result中。查询结果可能有多条记录，这就需要使用循环语句来读取所有记录。其代码如下： while（result.next（）） { String s=result.getString（"字段名"）; System.out.print（s）;} 　　其中，“字段名”参数表示查询出来记录的字段名称。使用getString（）函数可以将指定字段的值取出来，不管字段的数据类型是什么，都是当作字符串取出来的。只要result.next（）的值不为空，则可以执行循环。 While（result.next（））{ //判断是否还有记录 String id= result.getString（"stu_id"）; //获取stu_id字段的值 String course= result.getString（"c_name"）; //获取c_name字段的值 String grade= result.getString（"grade"）; //获取grade字段的值 System.out.println（id+" "+course+" "+grade）; //输出字段的值} 　　【示例2】 从score表中查询学生的学号、考试科目和成绩。部分代码如下： Statement statement=connection.createStatement（）; //创建 Statement对象//执行select语句，并且将查询结果传递到ResultSet对象中 ResultSet result=statement.executeQuery（"select stu_id,c_name,grade from score"）; 　　这段代码执行select语句，从score表中查询出stu_id、c_name 和grade这3个字段的值。因为查询出来的记录可能有很多条，所以需要用while语句来进行循环读取。读取到最后一条记录以后，result.next（）的值变为空，此时循环结束。 　　说明：ResultSet接口中还定义了其他的方法。例如，getRow（）方法可以获取当前行在结果集中的位置；first（）方法可以从当前行回到第一行；last（）方法可以从当前行跳到最后一行；isFirst（）方法判断当前行是否是第一行；isLast（）方法判断当前行是否是最后一行。 　　（3）插入、更新或者删除数据 　　executeQuery（）方法只能执行select语句。如果需要进行插入、更新或者删除操作，则需要Statement对象调用executeupdate（）方法来实现。executeupdate（）方法执行完后，返回影响表的行数。下面是调用executeQuery（）方法的代码： int result=statement.executeupdate（sql）; 　　其中，sql参数必须是insert语句、update语句或者DELETE语句。该方法返回的结果为数字。 　　【示例3】 向score表插入一条新记录。部分代码如下： Statement statement=connection.createStatement（）; //创建Statement对象String sql="insert into score values（21,902,"英语"，85）"； //获取insert语句int result=statement.executeupdate（sql）; //执行insert语句，返回插入的记录数System.out.println（result）; //输出插入的记录数 　　代码执行成功后，可以将新记录插入到score表中。同时，返回数字1，这表示对表中的一条记录进行了操作。 　　【示例4】 更新score表中id为16的记录，将该记录的grade字段的值改为100。部分代码如下： Statement statement=connection.createStatement（）; //创建Statement对象String sql="update score set grade=100 where id=16"； //获取update语句int result=statement.executeupdate（sql）; //执行update语句，返回更新的记录数System.out.println（result）; //输出更新的记录数 　　代码执行成功后，可以更新id为16的记录，同时返回数字1，这表示更新了表中的一条记录。 　　【示例5】 从score表中删除id为16的记录。部分代码如下： Statement statement=connection.createStatement（）; //创建Statement对象String sql="delete from score where id=16"; //获取delete语句int result=statement.executeupdate（sql）; //执行delete语句，返回更新的记录数System.out.println（result）; //输出更新的记录数 　　代码执行成功后，可以删除id为16的记录，同时返回数字1，这表示删除了表中的一条记录。 　　（4）执行任意SQL语句 　　execute（）方法可以执行select语句、insert语句、update语句和delete语句等。无法确定要执行的SQL语句是查询还是更新时，可以使用execute（）函数。该函数的返回结果是boolean类型的值，返回值为true表示执行查询语句，false表示执行更新语句。 　　下面是调用execute（）方法的代码： boolean result=statement.execute（sql）; 　　提示：因为executeQuery（）方法只能执行select语句，而executeupdate（）方法能够执行update语句、insert语句、delete语句等改变数据的SQL语句，所以，在不知道SQL语句的类型或者要执行不同类型的SQL语句时，可以选择execute（）方法。 　　如果要获取select 语句的查询结果，需要调用getResultSet（）方法。如果要获取insert 语句、update语句或者delete语句影响表的行数，需要调用getupdateCount（）方法。这两个方法的调用语句如下： ResultSet result1=statement.getResultSet（）;int reuslt2=statement.getupdate（）; 　　【示例6】 下面调用execute（）函数执行SQL语句。部分代码如下： Statement statement=connection.createStatement（）; //创建statement对象String sql="select stu_id,c_name,grade from score"; //定义变量sql，获取select语句boolean st=statement.execute（sql）; //执行select语句 //如果执行select语句，execute（）返回trueif（st==true）{ Result result=statement.getResultSet（）; //将查询结果传递给Result对象 While（result.next（））{ //判断是否还有记录 String id= result.getString（"stu_id"）; //获取stu_id字段的值 String course= result.getString（"c_name"）; //获取c_name字段的值 String grade= result.getString（"grade"）; //获取grade字段的值 System.out.println（id+" "+course+" "+grade）; //输出字段的值 }}else{ int i=statement.getupdateCount（）; //通过获取发生变化的记录数 system.out.println（“the number is”+i）; //输出记录数} 　　如果执行的是select语句，那么st的值就是true，将执行if语句中的代码；如果执行的是insert语句、update语句或者DELETE语句，将执行else语句中的代码。 　　（5）关闭创建的对象 　　当所有SQL语句都执行完毕后，需要关闭所创建的Connection对象、Statement对象和ResultSet对象。关闭对象的顺序与创建对象的顺序相反，关闭的顺序为ResultSet对象、Statement对象、Connection对象。对象调用close（）方法来关闭对象，然后将对象的值设为空。关闭对象的部分代码如下： if（result!=null）{ //判断Result对象是否为空 result.close（）; //调用close（）方法关闭ResultSet对象 result=null;}if（statement!=null）{ //判断Statement对象是否为空 statement.close（）; //调用close（）方法关闭Statement对象 statement=null;}if（connection!=null）{ //判断Connection对象是否为空 connection.close（）; //调用close（）方法关闭Connection对象 connection=null;} 　　3. 用Java备份与还原MySQL数据库 　　在Java语言中可以使用mysqldump命令来备份MySQL数据库，也可以使用mysql命令来还原MySQL数据库。下面介绍用Java备份与还原MySQL数据库的方法。 　　（1）用Java备份MySQL数据库 　　通常使用mysqldump命令来备份MySQL数据库。其语句格式如下： mysqldump -u username -pPassword dbname table1 table2...>BackupName.sql 　　其中，username参数表示登录数据库的用户名；Password参数表示用户的密码，其与-p之间不能用空格隔开；dbname参数表示数据库的名称；table1和table2参数表示表的名称，没有该参数时将备份整个数据库；BackupName.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为sql的文件。 　　Java语言的Runtime类中的exec（）方法可以运行外部命令。调用exec（）方法的代码如下： Runtime rt=Runtime.getRuntime（）;Rt.exec（“命令语句”）； 　　用Java备份MySQL数据库时，使用exec（）方法来执行mysqldump命令。使用exec（）方法时要进行异常处理。 　　【示例7】 下面是在Windows操作系统下用Java备份MySQL数据库。部分代码如下： String str="mysqldump -u root -phuang -opt test>c:/test.sql"; //将mysqldump命令的语句赋值给strRuntime rt=Runtime.getRuntime（）; //创建Runtime对象rt.exec（"cmd/c"+str）; //调用exec（）函数 　　上面的代码可以将数据库test备份到C:＼目录下的test.sql文件中。-opt选项可以提高备份的速度，cmd表示要使用cmd命令来打开DOS窗口，/c表示执行完命令后关闭命令窗口。 　　说明：Windows操作系统下一定要加上cmd/c，因为Windows操作系统中的mysqldump命令是在DOS窗口中运行的。在Linux操作系统下，只有拥有root权限或者mysq1权限的用户才可以执行这段代码，否则Java代码执行时会出现异常。 　　（2）用Java还原MySQL数据库 　　通常使用mysql命令来还原MySQL数据库。其语句格式如下: mysql -u root -p[dbname]80）; 　　（11）查询来自四川达州和四川巴中的学生的信息。 select * from studentwhere （st_from='四川达州' or st_from='四川巴中'）; 　　（12）查询来自四川达州和四川巴中的女生的信息。 select * from studentwhere （st_from='四川达州' or st_from='四川巴中'）and st_sex='女'; 　　（13）使用like关键字查询。 　　① 查询“计算机”开头的课程的信息。 select * from course where cs_nm like '计算机%'; 　　② 查询所有姓“张”的学生的信息。 select * from student where st_nm like '张%'; 　　③ 查询所有名字为三个字且姓“张”的学生的信息。 select * from student where st_nm like '张__'; 　　④ 查询所有课程中包含“数据”的课程的信息。 select * from course where cs_nm like '%数据%'; 　　（14）使用in关键字查询 　　① 查询11系、12系、13系学生的信息。 select * from studentwhere st_dpid in （'11', '12','13'）; 　　② 查询所有姓“张”、姓“王”、姓“李”、姓“赵”的学生的信息。（答案不唯一） select * from student where left（st_nm,1）in （'张','王','李',' 赵'）; 　　（15）使用between...and...查询。 　　① 查询成绩为60~80的学生的学号。 select st_id from slt_coursewhere （score between 60 and 80）; 　　② 查询选修了101课程，成绩为60~80的学生的学号。 select st_id from slt_course where cs_id='101'and （score between 60 and 80）; 　　③ 查询在1999.7.1至2000.7.1出生的学生的信息。 select * from studentwhere st_birthbetween '1999.7.1' and '2000.7.1'; 　　（16）使用[not] null关键字查询。 　　① 查询所有生源地为空的学生的信息。 select * from student where st_from is null; 　　② 查询所有生源地非空的学生的信息。 select * from studentwhere st_from is not null; 　　③ 查询成绩为空的学生的学号。 select st_id from slt_coursewhere score is null; 　　④ 查询选修了102号课程且成绩为空的学生的信息。 select * from slt_coursewhere cs_id='102'and score is null; 　　实验6 查询排序 　　（1）按照年龄由小到大的顺序显示学生的信息。 select * from student order by st_dpid; 　　（2）查询课程信息，按照课程名称降序显示。 select * from course order by cs_nm desc; 　　（3）查询选修了101课程的学生的学号，按成绩降序显示。 select * from slt_course where cs_id='101'order by st_id desc; 　　（4）查询11系学生的学号、姓名、年龄，按照年龄升序显示。 select st_id,st_nm,st_birth from studentwhere st_dpid='11' order by st_birth desc; 　　（5）查询课程信息，按照课程的学分降序排列，再按照课程名称升序排列。 select * from course order by cs_sc desc; 　　实验7 查询统计与汇总 　　（1）查询学生总人数。 select count（*） from student; 　　（2）查询来自四川达州的学生的总数。 select count（*） from studentwhere st_from='四川达州'; 　　（3）查询选修101号课程的人数。 select count（*） from slt_coursewhere cs_id= '101'; 　　（4）查询被选修课程的数量。 select count（distinct cs_id）from slt_course; 　　（5）查询11班学生的平均入学成绩。 select avg（st_score） from studentwhere left（st_dpid,2）='11'; 　　（6）查询学号为1901001的学生选修课程的数量、总分及平均分（为选课数量、总分、平均分重命名）。 select count（cs_id） as 选课数量,sum（score） as 总分,avg（score） as 平均分 from slt_coursewhere st_id='1901001'; 　　（7）求各个课程号和相应的选课人数。 select cs_id,count（st_id）from slt_course group by cs_id; 　　（8）统计各班人数。 select left（st_dpid,2） as 班级,count（st_dpid） as 人数from student group by left（st_dpid,2）; 　　（9）依次按班级、系号分类统计学生的人数、入学平均分。 select st_id as 系号,st_id as 系号,left（st_dpid,2） as 班级,count（st_dpid）as 人数,avg（st_score） as 均分from student group by left（st_dpid,2）; 　　（10）查询被选修的课程中平均分在75分以上的课程号和平均分。 select cs_id as 课程编号,avg（score） as 均分from slt_coursegroup by cs_id having avg（score）>75; 　　（11）查询选修了两门以上（含两门）课程的学生的学号。 select st_id from slt_coursegroup by st_id having count（*）>1; 　　实验8 数据查询——多表连接查询 　　（1）查询学生的学号、姓名、系别及所选课程编号。 select s.st_id,s.st_nm,s.st_dpid,sc.cs_idfrom student s join slt_course scon s.st_id = sc.st_id; 　　（2）查询学生的学号、姓名、所选课程编号及名称。 select s.st_id,s.st_nm,sc.cs_id,c.cs_nmfrom student s,slt_course sc,course cwhere s.st_id = sc.st_idand sc.cs_id = c.cs_id; 　　（3）查询学生的学号、姓名、所选课程编号及名称、成绩。 select s.st_id,s.st_nm,sc.cs_id,c.cs_nm,sc.scorefrom student s,slt_course sc,course cwhere s.st_id = sc.st_idand sc.cs_id = c.cs_id; 　　（4）查询学生的学号、姓名、所在系别的名称。 select s.st_id,s.st_nm,d.dp_nmfrom student s,dept dwhere s.st_dpid = d.dp_id; 　　（5）查询学生的学号、姓名、所在系别的名称及联系电话。 select s.st_id,s.st_nm,d.dp_nm,d.dp_telfrom student s,dept dwhere s.st_dpid = d.dp_id; 　　（6）查询选修了101号课程的学生的姓名、学号、成绩。 select s.st_id,s.st_nm,sc.scorefrom student s,slt_course scwhere sc.cs_id='101'and s.st_id = sc.st_id; 　　（7）查询选修了“数据结构”课程的学生的学号、姓名、成绩。 select s.st_id,s.st_nm,sc.scorefrom student s,slt_course sc,course cwhere c.cs_nm='数据结构' and s.st_id = sc.st_idand c.cs_id = sc.cs_id; 　　（8）查询没有选修任何课程的学生的学号、姓名。 select s.st_id,s.st_nmfrom student s,slt_course scwhere sc.cs_id is null; 　　提示：因为学生都选了课，所以为空。 　　（9）查询不及格的学生的姓名、学号。 select s.st_id,s.st_nmfrom student s,slt_course scwhere sc.score < 60; 　　提示：学生都及格了。 　　（10）查询入学成绩在420分以上的学生的学号、姓名、所在系的名称及联系电话。 select s.st_id,s.st_nm,d.dp_nm,d.dp_telfrom student s,dept dwhere s.st_score > 420and s.st_dpid = d.dp_id; 　　（11）查询计科系学生的信息。 select * from student s where s.st_dpid = 11; 　　（12）查询计科系学生的选修课成绩。 select s.st_id,s.st_nm,sc.cs_id,sc.scorefrom student s,slt_course scwhere s.st_id = sc.st_id and s.st_dpid= 11; 　　实验9 数据查询——子查询 　　（1）查询计科系学生的信息。 select * from studentwhere st_dpid=（select dp_id from deptwhere dp_nm="计科系"）; 　　（2）查询不及格的学生的姓名、学号。 select st_nm,st_idfrom studentwhere st_id=（ select st_id from slt_course where score<60）; 　　（3）查询选修了课程的学生的姓名、学号、性别。 select st_nm,st_id,st_sexfrom studentwhere st_id in（select st_id from slt_course）; 　　（4）查询“张飞”同学所在班级的学生的姓名、学号。 select st_nm,st_idfrom studentwhere left（st_id,2）=（select left（st_id,2） from student where st_nm="张飞"）; 　　（5）查询101号课程选课成绩比所有科目的平均成绩高的学生的学号、姓名。 select sc.st_id,s.st_nm from slt_course sc,student swhere sc.st_id=s.st_id and sc.score>（ select avg（score） from slt_course where cs_id="101"）; 　　（6）查询姓“张”的学生所在的系的名称和联系电话。 select dp_nm,dp_telfrom deptwhere dp_id in（select st_dpid from student where st_nm like "张%"）; 　　（7）查询女生的选修课的平均成绩。 select avg（score） from slt_coursewhere st_id in （ select st_id from student where st_sex="女"）; 　　（8）查询班长的选修课成绩。 select cs_id,score from slt_course wherest_id=（select st_id from student where st_mnt="班长"）; 　　（9）查询班长的选修课名称、成绩和学分。 select c.cs_nm,s.score,c.cs_scfrom course c,slt_course s,student twhere t.st_id=s.st_id and c.cs_id=s.cs_idand t.st_mnt="班长"; 　　（10）查询入学成绩在420分以上的学生所在系的名称及联系电话。 select dp_nm,dp_tel from dept where dp_idin （select st_dpid from student where st_score>420）; 　　（11）查询入学成绩最高的学生所在系的名称及联系电话。 select dp_nm,dp_tel from dept where dp_id=（select st_dpid from student where st_score=（select max（st_score） from student））; 　　（12）查询计科系学生选修课的平均成绩。 select avg（score） from slt_course where st_idin（select st_id from student where st_dpid=（select dp_id from dept where dp_nm="计科系"））; 　　实验10 查询综合练习（1） 　　新建数据库，执行如下操作： create database train;use train; 　　1. 根据数据表写出查询语句 　　（1）查询student表中的所有记录的sname、ssex和class列。 select s.sname,s.ssex,s.classfrom student s; 　　（2）查询教师所在的单位，即查询不重复的depart列。 select distinct departfrom teacher; 　　（3）查询student表的所有记录。 select * from student; 　　（4）查询score表中成绩为60~80的所有记录。 select sc.*from score scwhere sc.degree >= 60 and sc.degree <=80order by sc.sno desc; 　　（5）查询score表中成绩为85、86或88的记录。 select sc.*from score scwhere sc.degree in （85,86,88）order by sc.sno desc; 　　（6）查询student表中95031班或性别为“女”的学生的记录。 select st.*from student stwhere st.class = '95031'or st.ssex ='女'order by st.sno; 　　（7）以class降序查询student表的所有记录。 select *from studentorder by class desc; 　　（8）以cno升序、degree降序查询score表的所有记录。 select sc.*from score scorder by sc.cno ,sc.degree desc; 　　（9）查询95031班的学生人数。 select count（*）from student stwhere st.class='95031'; 　　（10）查询score表中最高分的学生学号和课程号。 select sc.sno,sc.cnofrom score scwhere sc.degree =（ select max（sc.degree）from score sc ）order by sc.sno; 　　（11）查询3_105号课程的平均分。 select avg（sc.degree）from score scwhere sc.cno='3_105'; 　　（12）查询score表中至少有5名学生选修并以3开头的课程的平均分数。 select avg（sc.degree）from score scwhere sc.cno like '3%'group by sc.cnohaving count（sc.sno） >=5; 　　（13）查询最低分大于70且最高分小于90的sno列。 select sc.sno,sc.degreefrom score scwhere sc.degree >70and sc.degree < 90; 　　（14）查询所有学生的sname、cno和degree列。 select st.sname,sc.cno,sc.degreefrom student st,score scwhere st.sno=sc.sno; 　　（15）查询所有学生的sno、cname和degree列。 select distinct st.sno,co.cname,sc.degreefrom student st,score sc,course cowhere st.sno=sc.sno and sc.cno= co.cnoorder by st.sno; 　　（16）查询95033班所选课程的平均分。 select avg（sc.degree）from student st,score scwhere st.sno=sc.sno and st.class='95033'; 　　2. 使用如下命令建立一个grade表并完成查询 create table grade（low numeric（10,0）,upp numeric（10,0） ,rank varchar（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'）;commit; 　　（1）查询选修3_105课程且成绩高于109号学生成绩的所有学生的记录。 select distinct st.*from student st,score scwhere sc.cno='3_105' and sc.degree > （ select sc.degree from score sc where sc.sno='109' and sc.cno='3_105'）; 　　（2）查询score中选学一门以上课程的学生中分数为非最高分成绩的记录。 select sc.*from score scwhere sc.degree < （ select max（sc.degree） from score sc）group by sc.cnohaving count（sc.sno） >1; 　　（3）查询成绩高于学号为109、课程号为3_105的课程成绩的所有记录。 select sc.*from score scwhere sc.cno='3_105' and sc.degree >（ select sc.degree from score sc where sc.cno='3_105' and sc.sno='109'）; 　　（4）查询和学号为108的学生同年出生的所有学生的sno、sname和sbirthday列。 set @birthday=（select st.sbirthday from student st where st.sno='108'）;select st.sno,st.sname,st.sbirthdayfrom student stwhere year（st.sbirthday） = year（@birthday）; 　　（5）查询“张旭”教师任课的学生成绩。 select sc.*from teacher te,score sc,course cowhere te.tname='张旭'and te.tno=co.tno and co.cno=sc.cno; 　　（6）查询选修某课程的学生人数多于5人的教师姓名。 select distinct te.tnamefrom teacher te,score sc,course cowhere te.tno=co.tnoand co.cno=sc.cno and sc.cno in （select t.cnofrom （select count（*） bb, sc.cnofrom score scgroup by sc.cno） twhere t.bb >5）; 　　（7）查询95033班和95031班全体学生的记录。 select distinct st.*from student stwhere st.class='95033'or st.class='95031'order by st.sno; 　　（8）查询存在有85分以上成绩的课程cno。 select distinct sc.cnofrom score scwhere sc.degree >85order by sc.cno; 　　（9）查询出“计算机系”教师所教课程的成绩表。 select sc.*from score sc,course co,teacher tewhere te.depart='计算机系'and te.tno=co.tnoand sc.cno=co.cnoorder by sc.sno; 　　（10）查询“计算机系”与“电子工程系”不同职称的教师的tname和prof。 select te.tname,te.proffrom teacher tewhere te.depart='计算机系'and te.prof not in（select t.prof from teacher twhere t.depart='电子工程系'）group by te.prof; 　　（11）查询选修编号为3_105课程且成绩高于选修编号为3_245的学生的cno、sno和degree，并按degree列从高到低次序排序。 select sc.cno,sc.sno,sc.degreefrom score scwhere sc.cno='3_105'and sc.degree > （select max（sc.degree）from score sc where sc.cno='3_245'）order by sc.degree desc; 　　（12）查询选修编号为3_105且成绩高于选修编号为3_245课程的学生的cno、sno和degree。 select sc.cno,sc.sno,sc.degreefrom score scwhere sc.cno='3_105'and sc.degree > （select max（sc.degree）from score sc where sc.cno='3_245'）; 　　（13）查询所有教师和学生的name、sex和birthday。 select st.sname name,st.ssex,st.sbirthdayfrom student st union allselect te.tname,te.tsex,te.tbirthdayfrom teacher te; 　　（14）查询所有“女”教师和“女”学生的name、sex和birthday。 select st.sname name,st.ssex,st.sbirthdayfrom student stwhere st.ssex='女' union allselect te.tname,te.tsex,te.tbirthdayfrom teacher tewhere te.tsex='女'; 　　（15）查询成绩比该课程平均成绩低的学生的成绩表。 select distinct sc.*from score scwhere sc.degree <（ select avg（ss.degree）from score ss where ss.cno=sc.cno ）order by sc.cno; 　　（16）查询所有任课教师的tname和depart。 select te.tname,te.departfrom teacher te join course using（tno）; 　　（17）查询所有未讲课的教师的tname和depart。 select te.tname,te.departfrom teacher tewhere not exists （select sc.tnofrom course sc where sc.tno=te.tno）; 　　（18）查询至少有2名男生的班号。 select st.classfrom student stwhere st.ssex='男'group by st.classhaving count（st.ssex） >1; 　　（19）查询student表中不姓“王”的学生记录。 select st.*from student stwhere st.sname not like '王%'; 　　（20）查询student表中每个学生的姓名和年龄。 select st.sname, st.sbirthday,round（datediff（now（）,st.sbirthday）/365） agefrom student st; 　　（21）查询student表中最大和最小的sbirthday日期值。 select min（st.sbirthday） a, max（st.sbirthday） bfrom student st; 　　（22）以班号和年龄从大到小的顺序查询student表中的全部记录。 select st.*from student storder by st.class desc,st.sbirthday asc; 　　（23）查询“男”教师及其所上的课程。 select a.tname,b.cnamefrom teacher a join course busing（tno） where a.tsex='男'; 　　（24）查询最高分学生的sno、cno和degree列。 select sc.sno,sc.cno,sc.degreefrom score scwhere sc.degree = （ select max（sc.degree） from score sc）; 　　（25）查询和“李军”同性别的所有学生的sname。 select st.snamefrom student stwhere st.ssex =（select st.ssex from student st where st.sname='李军'）; 　　（26）查询所有选修“计算机导论”课程的“男”学生的成绩表。 select a.* from score ajoin （student b,course c）using（sno,cno） where b.ssex='男'and c.cname='计算机导论'; 　　实验11 查询综合练习（2） create table dept（ deptno varchar（10） primary key, dname varchar（10））;create table emp（ empno varchar（10） primary key, ename varchar（10）, job varchar（10）, mgr varchar（10）, sal varchar（10）, deptno varchar（10） references dept（deptno））;#drop table dept;#drop table emp;insert into dept values （'1','事业部'）;insert into dept values （'2','销售部'）;insert into dept values （'3','技术部'）;insert into emp values （'01','jacky','clerk','tom','1000','1'）;insert into emp values （'02','tom','clerk','','2000','1'）;insert into emp values （'07','biddy','clerk','','2000','1'）;insert into emp values （'03','jenny','sales','pretty','600','2'）;insert into emp values （'04','pretty','sales','','800','2'）;insert into emp values （'05','buddy','jishu','canndy','1000','3'）;insert into emp values （'06','canndy','jishu','','1500','3'）;select * from dept;select * from emp; 　　（1）列出emp表中各部门的部门号、最高工资、最低工资。 select deptno as 部门号,max（sal） as 最高工资,min（sal） as 最低工资from emp group by deptno; 　　（2）列出emp表中各部门job为clerk的员工的最高工资、最低工资和部门号。 select max（sal） as 最高工资,min（sal） as 最低工资,deptno as 部门号 from emp wherejob='clerk' group by deptno; 　　（3）对于emp表中最低工资小于2000的部门，列出job为clerk的员工的部门号、最高工资、最低工资。 select b.deptno as 部门号,max（sal） as 最高工资,min（sal） as 最低工资 from emp as bwhere job='clerk' and （select min（sal）from emp as awhere a.deptno=b.deptno）<2000group by b.deptno; 　　（4）根据部门号由高到低、工资由低到高列出每个员工的姓名、部门号、工资。 select ename as 姓名,deptno as 部门号,sal as 工资 from emporder by deptno desc,sal asc; 　　（5）列出buddy所在部门中每个员工的姓名与部门号。 select b.ename as 姓名,b.deptno as 部门号 from emp as bwhere b.deptno=（select a.deptno from emp as a where a.ename='buddy'）; 　　（6）列出每个员工的姓名、工作、部门号、部门名。 select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,deptwhere emp.deptno=dept.deptno; 　　（7）列出emp表中工作为clerk的员工的姓名、工作、部门号、部门名。 select ename as 姓名,job as 工作,dept.deptno as 部门号,dept.dname as 部门名 from emp,deptwhere emp.deptno=dept.deptno and job='clerk'; 　　（8）如果emp表中有管理者，列出管理者姓名（管理者外键为mgr）。 select a.deptno as 部门号,a.ename as 员工,b.ename as 管理者from emp as a,emp as bwhere a.mgr is not null and a.mgr=b.ename; 　　（9）对于dept表，列出所有部门号、部门名，同时列出各部门工作为clerk的员工名与工作。 select a.deptno as 部门号,a.dname as 部门名,b.ename as 员工名,b.job as 工作 from dept as a,emp as b where a.deptno=b.deptnoand b.job='clerk'; 　　（10）对于工资高于本部门平均水平的员工，列出部门号、姓名、工资，按部门号排序。 select b.deptno as 部门号,b.ename as 姓名,b.sal as 工资 from emp as bwhere b.sal>（select avg（a.sal） from emp as awhere a.deptno=b.deptno） order by b.deptno; 　　（11）对于emp表，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序。 select a.deptno as 部门号,count（a.sal） as 员工数 from emp as awhere a.sal>（select avg（b.sal）from emp as b where a.deptno=b.deptno）group by a.deptno orderby a.deptno; 　　（12）对于emp表中工资高于本部门平均水平且人数多于1人的，列出员工数、部门号、平均工资，按部门号排序。 select count（a.empno） as 员工数,a.deptno as 部门号,avg（sal） as 平均工资from emp as a where （select count（c.empno）from emp as c where c.deptno=a.deptno andc.sal>（select avg（sal） from emp as bwhere c.deptno=b.deptno））>1group by a.deptno order by a.deptno; 　　（13）对于emp表中低于自己工资至少5人的员工，列出其部门号、姓名、工资，以及工资少于自己的人数。 select a.deptno as 部门号,a.ename as 姓名,a.sal as 工资,（select count（b.ename） from emp as bwhere b.sal=5; 　　或者 select a.deptno,a.ename,a.sal,（select count（b.ename）from emp as bwhere b.sal=5; 　　实验12 查询综合练习（3） 　　（1）使用标准SQL嵌套语句查询选修课程名称为“语文”的学员学号、单位和姓名。 select sid as 学号,sd AS 单位,sn AS 姓名 from swhere sid in（select sid from c,scwhere c.cid=sc.cid and c.cn='语文'）; 　　（2）使用标准SQL嵌套语句查询选修课程编号为02的学员姓名和所属单位。 select sn as 姓名,sd as 单位from s,scwhere s.sid=sc.sid and sc.cid='02'; 　　（3）使用标准SQL嵌套语句查询不选修课程编号为02的学员姓名和所属单位。 select sn as 姓名,sd as 单位from swhere sid not in（select sid from scwhere sc.cid='02'）; 　　（4）使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位。 select sn,sd from s where sidin（select sid from sc group by sidhaving count（*）=（select count（*） from c））; 　　（5）查询选修了课程的学员人数。 select count（distinct sid） as 人数 from sc; 　　（6）查询选修课程超过2门的学员学号、姓名、单位。 select sid as 学号,sn as 姓名,sd as 单位 from s where sid in（select sid from sc group by sidhaving count（distinct cid）>2）; 　　实验13 查询综合练习（4） 　　（1）创建一个名称为mydb1的数据库。 create database mydb1; 　　（2）查看所有数据库。 show databases; 　　（3）创建一个使用UTF-8字符集的mydb2数据库。 create database mydb2 character set utf8; 　　（4）创建一个使用UTF-8字符集并带校对规则的mydb3数据库。 create database mydb3 characterset utf8 collate utf8_general_ci; 　　（5）显示库的创建信息。 show create database mydb3; 　　（6）删除前面创建的mydb1数据库。 drop database mydb1; 　　（7）查看服务器中的数据库，并把其中某一个库的字符集修改为GB 2312。 alter database mydb2 character set gb2312; show create database mydb2; 　　（8）使用mydb1数据库。 use mydb1; 　　（9）查看库中所有表。 show tables; 　　（10）查看表的创建细节。 show create table employee; 　　（11）查看表的结构。 desc employee; 　　（12）创建一个员工表。 create table employee （ id int,name varchar（20）, gender varchar（4）,birthday date, entry_date date,job varchar（40）, salary double,resume text）; 　　（13）在员工表中增加一个image列。 alter table employee add image blob; 　　（14）修改job列，使其长度为60。 alter table employee modify job varchar（60）; 　　（15）删除sex列。 alter table employee drop gender; 　　（16）表名改为user。 alter table employee rename user; 　　（17）修改表的字符集为UTF-8。 alter table user character set gb2312;show create table user; 　　（18）列名name修改为username。 alter table user change column name username varchar（20）; 　　（19）将表名改回employee。 alter table user rename employee; 　　（20）添加一名员工的信息： （1,'aaa','1980-09-09','1980-09-09','bbb',1000,'bbbbbbbb'）insert into employee（id,username,birthday,entry_date,job,salary,resume）Values（1,'aaa','1980-09-09','1980-09-09','bbb',1000,'bbbbbbbb'）; 　　（21）查看插入的数据。 select * from employee; 　　（22）使用insert语句向表中插入一个员工的信息。 insert into employee（id,username,birthday,entry_date,job,salary,resume）Values（2,'小李子','1980-09-09','1980-09-09','bbb',1000,'bbbbbbbb'）; 　　（23）插入失败后的解决方案。 show variables like 'chara%';set character_set_client=gb2312; 　　（24）显示失败后的解决方案。 set character_set_results=gb2312; 　　（25）将所有员工工资修改为5000元。 update employee set salary=5000; 　　（26）将姓名为aaa的员工工资修改为3000元。 update employee set salary=3000where username='aaa'; 　　（27）将姓名为aaa的员工工资修改为4000元，job改为ccc。 update employee set salary=4000,job='ccc'where username='aaa'; 　　（28）将员工aaa的工资在原有基础上增加1000元。 update employee set salary=salary+1000where username='aaa'; 　　（29）删除表中名称为zs的记录。 delete from employee where username='小李子'; 　　（30）删除表中所有记录。 delete from employee; 　　（31）使用truncate删除表中记录。 truncate table employee; 　　实验14 函数 　　利用MySQL的函数进行如下运算。 　　（1）计算3的3次方。 select pow（3,3）; 　　（2）计算16的平方根。 select sqrt（16）; 　　（3）返回不大于-3.5的最大整数。 select floor（-3.5）; 　　（4）计算PI（圆周率）对应的角度。 select degrees（PI（））; 　　（5）将字符串'BEIJING'变成小写。 select lcase（'beijing'）; 　　（6）截取字符串'sichuanchengdu'的后7位字符。 select right（'sichuanchengdu',7）; 　　（7）截取字符串'sichuanchengdu'的前7位字符。 select left（'sichuanchengdu',7）; 　　（8）从字符串'chengdu is a beautiful city'的第8个字符位置开始，截取一个长度为8的子串。 select substr（'chengdu is a beautiful city',8,8）; 　　（9）将字符串'mybook'逆序输出。 select reverse（'mybook'）; 　　（10）计算当前日期的月份。 select monthname（now（））; 　　（11）计算当前日期是本年的第几天。 select dayofyear（now（））; 　　（12）通过函数获取当前日期的月份。 select month（now（））; 　　（13）计算今天到春节间隔的天数。 select datediff（now（）,'2021-02-03'）; 　　（14）计算学生的年龄并显示出来。 select timestampdiff（year,'1999-02-16',now（））; 　　实验15 运算符 　　（1）在MySQL中执行如下算术表达式。 　　① 4*2-3 select 4*2-3; 　　②（3+6）/3 select （3+6）/3; 　　③（3+6）/2 select （3+6）/2; 　　④ 9 div 2 select 9 div 2; 　　⑤ MOD（9,2） select MOD（9,2）; 　　（2）在MySQL中执行下面的比较运算符表达式。 　　① 5>6 select 5>6; 　　② 25>=31 select 25>=31; 　　③ 20<=30 select 20<=30; 　　④ 5<=>5 select 5<=>5; 　　（3）在MySQL中执行下面的逻辑运算符表达式。 　　① -2&&3 select -2&&3; 　　② -3||null select -3||null; 　　③ 1 XOR 0 select 1 XOR 0; 　　④ !1 select !1; 　　（4）在MySQL中执行下面的位运算。 　　① 5&7 select 5&7; 　　② 11│15 select 11︳15; 　　③ 12^13 select 12^13; 　　④ ~12 select ~12; 　　实验16 存储过程和触发器 　　（1）编写存储过程stu_gra，查询计科系学生的信息。 delimiter &&create procedure stu_gra（）beginselect * from student,deptwhere student.st_dpid = dept.dp_idand dept.dp_nm="计科系";end &&delimiter ; 　　（2）编写存储过程stu_dep，根据给定的系别号，查询该系学生的姓名、成绩。 delimiter &&create procedure stu_dep（in dep varcha（10））beginselect st_nm,score from student,slt_coursewhere student.st_id=slt_course.st_id and student.st_dpid=dep;end &&delimiter; call stu_dep（11）; 　　（3）编写存储过程cou_stu，根据给定的学号，查询该学生所选课程的学分。 delimiter &&create procedure cou_stu（in sid varchar（10））beginselect cs_sc from course,slt_coursewhere slt_course.st_id=sid and course.cs_id=slt_course.cs_id;end &&delimiter; 　　（4）创建触发器del_inf，当有学生退学时，删除student表中该学生的信息，触动触发器，同时删除该学生对应的选课和成绩信息。 delimiter &&create trigger del_inf after delete on student for each row begin delete from slt_course where st_id=old.st_id; end &&delimiter; 　　（5）删除触发器del_inf。 drop trigger del_inf; 　　（6）删除存储过程stu_gra。 drop procedure stu_gra; 　　实验17 索引 　　（1）表中建立了索引后，导入大量数据为什么会很慢？ 　　对已经建立了索引的表中插入数据时，插入一条数据就要对该记录按索引排序，因此导入大量数据的时候速度会很慢。解决这个问题的办法是在没有任何索引的情况插入数据，然后建立索引。 　　（2）MySQL中索引、主键和唯一性的区别是什么？ 　　索引建立在一个或者几个字段上。建立了索引后，表中的数据就按照索引的一定规则排序，这样可以提高查询速度。 　　主键是表中数据的唯一标识，不同记录的主键值不同。在建立主键时，系统会自动建立一个唯一性索引。 　　唯一性也是建立在表中一个或者几个字段上，其目的是为了对应不同的记录，具有唯一性的字段的值是不同的。 　　（3）创建winf表。 　　创建表的同时在id字段上创建名为index_id的唯一性索引，而且以降序的格式排列。 create table winf（ id int（10） primary key auto_increment, name varchar（20） not null, type varchar（10）, address varchar（50）, wages int, contents tinytext, extra text, unique index index_id（id desc））; 　　① 为name字段创建索引index_name。 create index index_name on winf（name）; 　　② 使用alter table语句在type和address上创建名为index_ta的索引。 alter table winf add index index_ta（type,address）; 　　③ 使用alter table语句在extra字段上创建名为index_ext的全文索引。 alter table winf add fulltext index index_ext（extra）; 　　④ 删除winf表的唯一性索引index_id。 drop index index_id on winf; 　　实验18 视图 　　创建animal表。 create table animal（ id int（4） primary key auto_increment, name varchar（20） not null, kinds varchar（8） not null, legs int（4）,behavior varchar（50））; 　　按照要求完成下列操作。 　　（1）在animal表的基础上创建视图ani_view，该视图包含字段id、name和legs。 create view ani_view （id,name,legs）as select id,name,legs from animal; 　　（2）向ani_view视图中插入几条记录，并查看giant视图。 insert into ani_view values（1001,"青蛙",4）;insert into ani_view values（1002,"螃蟹",8）; select * from ani_view; 　　（3）查询该视图中动物的名称。 select name from ani_view; 　　（4）查询该视图中腿有4条的动物有哪些。 select name form ani_view where legs=4; 　　（5）删除某一种动物的信息，并查看视图。 delete from ani_view where id=1001;select * from ani_view; 　　（6）删除ani_view视图。 drop view ani_view; 　　实验20 绘制E-R图 　　E-R图如下图所示。