SQL数据库中表里的数据建立、修改和查找
制作人:丁琪 QQ:854804038
【试验名称】:SQL数据库中表里的数据建立、修改和查找
【试验过程】:
一、创建数据库:jwgl
代码:create database jwgl
1、创建表:student
相应的代码:
create database jwgl
go
use jwgl
go
create table student
(
student_id varchar(7) primary key not null,
student_name varchar(8) not null,
sex bit not null,
age int not null,
birth smalldatetime not null,
class_id varchar(5) not null,
entrance_date smalldatetime not null,
home_addr varchar(40) default '地址不详'
)
给表(student)中添加数据:
相应的代码:
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940210','张虹',1,15,'1984-9-1','g9902','1999-9-1','南京市鼓楼区平岗号')insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940212','林红',1,22,'1984-6-1','g9901','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940213','曹雨',1,21,'1984-12-1','g9902','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940214','莴华',1,18,'1983-9-1','g9901','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940215','李红[大]',1,17,'1984-9-1','g9902','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940216','李红[小]',1,22,'1984-9-8','g9901','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940217','赵清',1,19,'1985-9-9','g9902','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date,home_addr) values ('g940218','林海',1,16,'1985-9-1','g9901','1999-9-1','南京市鼓楼区平岗号')
insert into student (student_id,student_name,sex,age,birth,class_id,entrance_date) values ('g940219','海青',1,16,'1985-9-1','g9901','1999-9-1')
插入的结果:
相应代码:select * from student
2、创建表:class_info
相应的代码:
create table class_info
(
class_id varchar(7) primary key not null,
class_name varchar(8) not null,
)
给表(class_info)中添加数据并查询:
相应的代码:
insert into class_info values ('g9901','网页一班')
insert into class_info values ('g9902','网页二班')
insert into class_info values ('g9903','网页三班')
select * from class_info
3、创建表:course
相应的代码:
create table course
(
course_id tinyint not null,
student_id varchar(7) not null,
grade tinyint not null,
)
给表(course)中添加数据并查询:
相应的代码:
insert into course values (1,'g40210',60)
insert into course values (2,'g40212',90)
insert into course values (1,'g40213',87)
insert into course values (2,'g40210',88)
insert into course values (3,'g40210',98)
select * from course
4、创建表:course_info
给表(course_info)中添加数据并查询:
相应的代码:
insert into course_info values (1,'flash')
insert into course_info values (2,'fireworks')
insert into course_info values (3,'ps')
select * from course_info
二、对数据库中的数据进行查询、修改和建立
(1)插入一条学生记录,student_id:g940220,student_name:赵凯,sex:1,age:20,birth:1985-1-9,class_id:g9903,entrance_date:1999-9-1,home_addr:江苏市仪征青年路16号
相应的代码:
insert into student values ('g940220','赵凯',1,20,'1985-1-9','g9903','1999-9-1','江苏市仪征青年路号')
(2)把赵青学生的年龄改为20
相应的代码:
select * from student
update student set age=20 where student_id='g940217'
(3)显示所有学生的信息
相应的代码:
select * from student
(4)显示所有女同学的信息
相应的代码:
select * from student where sex=0
(5)显示所有大于18岁的女生信息
相应的代码:
select * from student where sex=1 and age>18
(6)显示所有在1984年到1985年之间出生的学生信息
select * from student where birth between '1984' and '1986'
(7)从student表中检索出家庭地址列为空值的同学信息
相应的代码:
select * from student where home_addr is null
(8)从student表中分别检索出姓张的所有同学的资料
相应的代码:
select * from student where student_name like '张%'
(9)名字的第二个字是“红”或“虹”的所有同学的资料
相应的代码:
select * from student where student_name like '_[红,虹]%'
(10)名字的第二个字不是“红”或“虹”的同学的资料
相应的代码:
第一种方法:select * from student where student_name like '_[^红,虹]%'
第二种方法:select * from student where student_name not like '_[红,虹]%'
(11)从student表中分别检索出学生的编号、姓名信息并分别以“学生编号”、“学生姓名”标题显示
相应的代码:
第一种方法:select student_id as '学生编号',student_name as '学生姓名' from student
第二种方法:select student_id as '学生编号','学生姓名'=student_name from student
(12)从student表中检索编号为‘g940210’,‘g940215’,‘g940218’的学生信息
相应的代码;
select * from student where student_id in ('g40210','g940215','g940218')
(13)统计学生的最大年龄,最小年龄,平均年龄、年龄总和及学生总数
1、统计学生的最大年龄
相应的代码:
第一种显示方式:select max(age) from student
第二种显示方式:select max(age) as '学生的最大年龄' from student
2、统计学生的最小年龄
相应的代码:
select min(age) as '学生的最小年龄' from student
3、统计学生的平均年龄
相应的代码:
select avg(age) as '学生的平均年龄' from student
4、统计学生年龄的总和
select sum(age) as '学生的年龄总和' from student
5、统计学生的总数
select count(student_id) as '学生的总数' from student
6、综合显示学生最大年龄、最小年龄、平均年龄、年龄总和
相应的代码:
select max(age) as '学生的最大年龄',min(age) as '学生的最小年龄',avg(age) as '学生的平均年龄',count(student_id) as '学生的总数' from student