create table class_info
(
 class_id varchar(20),
 class_name varchar(30),
)
insert into class_info values ( 'g9901','网页一班')
insert into class_info values ( 'g9902','网页二班')
insert into class_info values ( 'g9903','网页二班')

 

 

 

select *from class_info
create table course
(
 course_id char(2),
 student_id varchar(20),
 grade char(3)
)
insert into course values (1,'g940210',60)
insert into course values (2,'g940211',90)
insert into course values (3,'g940212',70)
insert into course values (4,'g940213',88)
insert into course values (5,'g940214',98)
select *from course
create table course_info
(
 courseid char(2),
 coursename varchar(20)
)
insert into course_info values (1,'flash')
insert into course_info values (2,'flsh')
insert into course_info values (3,'ps')
select * from course_info

insert into student values ('g940220','赵凯',1,20,'1985-1-9',
'g9903','1999-9-1','江苏市仪征青年路16号' )  1、插入学生记录

 

 

 

 

select *from student   3、显示所有学生的信息

)
insert into student values('g940210','张虹',1,15,'1984-9-1','g9902','1999-9-1','南京市鼓楼区平岗3号')
insert into student values('g940212','林红',0,22,'1984-6-1','g9901','1999-9-1','南京市鼓楼区平岗3号')
insert into student values('g940213','曹雨',1,21,'1983-12-1','g9902','1999-9-1','南京市鼓楼区平岗3号')
insert into student values('g940214','苪华',1,18,'1984-9-1','g9901','1999-9-1','南京市鼓楼区平岗3号')
insert into student values('g940215','李红【大】',0,17,'1983-9-9','g9902','1999-9-1','南京市鼓楼区平岗3号')
insert into student values('g940216','李红【小】',0,22,'1984-9-8','g9901','1999-9-1','南京市鼓楼区平岗3号')
insert into student values('g940217','赵青',1,19,'1985-9-9','g9902','1999-9-1','南京市鼓楼区平岗3号')
insert into student(student_id,student_name,sex,age,birth,class_id,enteance_date) values('g940218','林海',1,16,'1985-10-9','g9901','1999-9-1')
   

 
对student表做如下操作
插入一条学生记录，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','江苏市仪征青年路16号')
 
1把赵青学生的年龄改为20
代码：update student set age=20 where student_id='g940217'
 
2显示所有学生的信息
代码：select * from student
 
3显示所有女同学的信息
代码：select * from student where sex=0
 
4显示所有大于18岁的女生信息
代码：select * from student where sex=0 and age>18
 
5显示所有在84年到85年之间出生的学生信息
select * from student where birth between '1984' and '1986'
 
elect * from student where birth between '1984' and '1985-12-31'
 
6通过以上两种方法可以比较出来结果，我们的方法二是不可以采取的。
从student表中检索出家庭地址列为空值的同学信息。
代码：select * from student where home_addr is null
 
7从student表中分别检索出姓张的所有同学的资料；
代码：select * from student where student_name like '张%'
 
8名字的第二个字是“红”或“虹”的所有同学的资料；
代码：select * from student where student_name like '_[红,虹]%'
 
9名字的第二个字不是“红”或“虹”的同学的资料。
select * from student where student_name like '_[^红,虹]%'
 
select * from student where student_name not like '_[红,虹]%'
 
10从student表中分别检索出学生的编号、姓名信息并分别以“学生编号”、“学生姓名”标题显示。
select student_id as '学生编号','学生姓名'=student_name from student
 
11从student表中检索编号为‘g940210’,‘g940215’,‘g940218’的学生信息。
select * from student where student_id in ('g940210','g940215','g940218')
 
12统计学生的最大年龄，最小年龄，平均年龄、年龄总和及学生总数。
统计最大年龄代码1：select max(age) from student
 
统计最大年龄代码2：select max(age) as '学生的最大年龄' from student
 
统计最小年龄代码：select min(age) as '学生的最小年龄' from student
 
统计学生的平均年龄代码：select avg(age) as '学生的平均年龄' from student
 
统计学生年龄总和代码：select sum(age) as '学生的年龄总和' from student
 
统计学生总数代码：select count(student_id) as '学生的总数' from student
 
将以上汇总代码：select max(age) as '学生的最大年龄',min(age) as '学生的最小年龄',avg(age) as '学生的平均年龄',count(student_id) as '学生的总数' from student