SQL:查询学习

 if db_id(N'student') is not null
drop DATABASE student;
GO
CREATE DATABASE student
ON
(
    NAME='t',
    FILENAME='F:/sqlserver2005.ISO/MicrosoftSQLServer2005简体中文开发版/cs_sql_2005_dev_all_dvd/SQL Server x86/student.mdf',
    SIZE=5MB,
    MAXSIZE=100MB,
    FILEGROWTH=5MB
   
)
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='s_info')
drop TABLE s_info;
GO
CREATE TABLE s_info
(
   s_id int primary key,
   s_name nvarchar(20) not null,
   s_sex nvarchar(2) not null
)
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name='course')
drop TABLE course;
GO
CREATE TABLE course
(
   s_id int not null,
   c_id int not null,
   score float  null
)
Go
ALTER TABLE s_info
ADD CONSTRAINT ck_sex check (s_sex='男' or s_sex='女')
GO
ALTER TABLE course
ADD CONSTRAINT pk_cid primary key(s_id,c_id)
GO
ALTER TABLE course
ADD CONSTRAINT fk_course_student foreign key(s_id) references s_info(s_id)
Go
insert into s_info values('疾风','男')
insert into s_info values('小敏','女')
GO
UPDATE s_info set s_name='xiaoshuai' where s_name=''
GO
/*修改列名*/
SELECT '学号'=s_id,'姓名'=s_name,'性别'=s_sex from s_info
/*得到学号最大的学生信息*/
SELECT top 1 '学号'=s_id,'姓名'=s_name,'性别'=s_sex from s_info ORDER by s_id DESC
/*得到姓张的和姓李的人的信息*/
SELECT '学号'=s_id,'姓名'=s_name,'性别'=s_sex from s_info where s_name like '[张李]%'
go
/*内连接查询*/
select * from s_info as S INNER JOIN course C
on S.s_id=C.s_id
Go
/*外部左插叙即以s_info为主的查询*/
SELECT * FROM s_info S LEFT OUTER JOIN course C
on S.s_id=C.s_id
Go
SELECT * from s_info,course
where s_info.s_id=course.s_id
Go
/*全局变量*/
SELECT @@VERSION
GO
/*局部变量，必须先定义才能使用*/
DECLARE @A FLOAT
SET @A=100
SELECT @A
Go
DECLARE @A FLOAT
SELECT @A =AVG(score) from course
SELECT @A
GO
SELECT * from s_info where s_id in
(SELECT s_id from course)