数据库操作（一）

drop table zmt_s_c;

drop table zmt_student;

drop table zmt_course;

create table zmt_student(

 sno char(4)  primary key not null,

 sname nchar(10) not null,

 sex char(1) check (sex= ‘M‘ or sex=‘F‘),//条件约束

 age smallint check (age<=25 and age >=18)

);

insert into zmt_student values(‘1‘,‘张梦婷‘,‘F‘,22);

insert into zmt_student values(‘2‘,‘杨明珠‘,‘F‘,22);

insert into zmt_student values(‘3‘,‘崔雄‘,‘M‘,22);

insert into zmt_student values(‘4‘,‘马森‘,‘M‘,22);

insert into zmt_student values(‘5‘,‘曹真‘,‘M‘,22);

insert into zmt_student values(‘6‘,‘慕安峰‘,‘M‘,22);

create table zmt_course(

 cno char(4) primary key not null,

 cname char(10),

 credit smallint not null check (credit>=0 and credit<=100)

);

insert into zmt_course values(‘1‘,‘数学‘,22);

insert into zmt_course values(‘2‘,‘英语‘,22);

insert into zmt_course values(‘3‘,‘语文‘,22);

create table zmt_student(

   sno char(4) identity(1,1)  primary key not null,//identity(1,1)：标识列， identity(a,b)，ab均为正整数，a表示开始数，b表示增幅，就像identity(1,1)意思就是该列自动增长，由1开始每次增加是1，

   sname nchar(10) not null,

　sex char(1) check (sex= ‘M‘ or sex=‘F‘),

   age smallint check (age<=25 and age >=18)

);

create table zmt_course( 

 cno char(4) identity(1,1) primary key not null,

cname char(10),

 credit smallint not null check (credit>=0 and credit<=100)

 );

create table zmt_s_c(

 sno char(4),

 cno char(4),

 grade smallint check(grade>=0 and grade <=100),

 constraint zmt_s_c_sno_fk foreign key(sno)  references zmt_student(sno), //外键约束

 constraint zmt_s_c_cno_fk foreign key(cno)  references zmt_course(cno)

);

insert into zmt_s_c values(‘1‘,‘2‘,80);

insert into zmt_s_c values(‘1‘,‘1‘,77);

insert into zmt_s_c values(‘1‘,‘3‘,90);

  --1.查询性别不是男的学生的姓名

　　select sname from zmt_student where sex!=‘M‘;

  --2.查询性别是男的 年龄大于20的学生姓名

　　select sname from zmt_student where sex=‘M‘ and age>20;

  --3.按照课程号降序排列 课程名升序排序 查询出结果集

　　select * from zmt_course order by cno desc,cname asc;

  --4.在S_C表中，查询学号是1的学生 一共修了几门课程

 select count(cno) from zmt_s_c where sno=‘1‘;