SQl基础命令语句笔录
1. 创建
数据库teaching（包括course,score,teacher,class,teach_class表）。
use master
go
if exists(select * from sysdatabases where name='teaching')
drop database teaching
create database teaching
on (name =teaching,filename ='g:/SQL/teaching.mdf')
log on(name=teaching_log,filename='g:/SQL/teaching_log.ldf')
2.利用T -SQL 语句在teaching数据库中创建数据表。
a. 创建course表的结构：
create table course(
courseno nchar(6) not null,
cname nchar(20) not null,
type nchar(8) not null,
period tinyint not null,
credit numeric(4,1) not null,
constraint PK_course primary key clustered
(courseno ASC)
)
b.插入数据
insert course values('c1111','电子技术','必修',74,4)
c.查询语句例子
1. 查询student表中所有年龄大于20岁的男生的姓名和年龄。
select sname as 姓名,year(getdate())-year(birthday) as 年龄
from student
2. 查询选修课程且期末成绩不为空的学生人数。
select count(*) as 人数
from score
where final is not null and courseno in(select courseno fromcourse where type='选修')
3.查询每名学生的学号、选修课程数目、总成绩，并将查询结果存放到生成的”学生选课统计表”中。
select studentno as 学号,count(*) as 选修课程数目,sum(final) as 总成绩
into 学生选课统计表
from score
group by studentno
4.查询选修’c05109’课程，并且期末成绩在前5名的学生学号、课程号和期末成绩。
select top 5 studentno as 学生学号,courseno as 课程号,final as 期末成绩
from score
order by final DESC