Question

如何使用Microsoft SQL Server Management Studio来写SQL？

本人完全菜鸟，对SQL略知一二，对Management Studio则是一窍不通，哪位朋友可以解释一下如何用它进行SQL的对应操作？最基本的就行，我想用它来学习SQL。

Answer 1

首先你需要打开它吧？它是SQL的DBMS图形化操作系统。
如果是想用SQL语言来操作的话呢，那就在建立好连接的基础上，然后点击窗体上面的“新建查询”按钮，然后在窗体右边会出现一块编辑区，你在编辑区域里面书写相应的SQL语句就可以。

下面是我写的一个例子，你可以参考下：
现在是建立数据库的SQL语句：

use master
create database stuDB
on
(
name='stuDB', size=5mb,filegrowth=15%, filename='E:\B\stuDB.mdf'
)
log on
(
name='stuDB_log',size=1mb,maxsize=6mb,filename='E:\B\stuDB_log.ldf'
)

use stuDB
go

下面呢，是在新建好的数据库中新建数据表：

create table stuInfo
(
stuName varchar(32) not null,
stuNo varchar(16) not null primary key ,
stuSex varchar(8) not null default('男'),
stuAge int not null,
stuSeat int not null identity(1,1),
stuAddress ntext default('地址不详')
)

以下是为新建好的数据表添加约束：

alter table stuInfo
add constraint ck_stuNo check (stuNo like 's253xx')

alter table stuInfo
add constraint ck_stuAge check (stuAge>=15 and stuAge<=50)

下面是给数据表添加数据：

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('张秋丽','s25301',default,18,'北京海淀')

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('李斯文','s25303','女',22,'河南洛阳')

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('李文才','s25302',default,31,'')

insert into stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('欧阳骏雄','s25304',default,28,'新疆克拉玛依')

下面是查询表中的所有数据
select *from stuInfo

select stuName as 姓名,stuNo as 学号,stuSex as 性别,stuAge as 年龄,stuSeat as 座号,stuAddress as 家庭住址
from stuInfo

同样，也是新建数据表的语句块：

create table stuMarks
(
examNo varchar(32) not null primary key,
stuNo varchar(16) not null,
writtenExam int default(0),
labExam int default(0)
)

添加约束：

alter table stuMarks
add constraint ck_examNo check (examNo like 'E200507xxxx')

alter table stuMarks
add constraint fk_stuNo foreign key (stuNo) references stuInfo(stuNo)

alter table stuMarks
add constraint ck_writtenExam check (writtenExam>0 and writtenExam<=100)

alter table stuMarks
add constraint ck_labExam check (labExam>0 and labExam<=100)

添加数据到数据表：

insert into stuMarks(examNo,stuNo,writtenExam,labExam) values('E2005070001',
's25301',80,58)

insert into stuMarks(examNo,stuNo,writtenExam,labExam) values('E2005070002',
's25302',50,default)

insert into stuMarks(examNo,stuNo,writtenExam,labExam) values('E2005070003',
's25303',97,82)

下面就是一些简单的查询了：

select examNo as 考号,stuNo as 学号,writtenExam as 笔试成绩,labExam as 试验成绩
from stuMarks

select stuName as 姓名,stuNo as 学号,stuSex as 性别,stuAge as 年龄,stuSeat as 座号,stuAddress as 家庭住址
from stuInfo

select *from stuInfo
where stuSex='男'
order by stuSeat desc

select * from stuMarks
where writtenExam>75 and writtenExam<=100
order by stuNo

select* from stuMarks
where writtenExam <>0 and labExam <>0

select stuName,writtenExam,labExam
into lingshi
from stuInfo,stuMarks

以下就是SQL的聚合函数部分了：

select avg(writtenExam) as 笔试平均成绩 from stuMarks

select avg(labExam) as 机试平均成绩 from stuMarks

select count(*) as 考试人数 from stuMarks where writtenExam>60

select count(*) as 没有通过考试的人数 from stuMarks where writtenExam<60

Answer 2

首先你需要打开它吧？它是SQL的DBMS图形化操作系统。
如果是想用SQL语言来操作的话呢，那就在建立好连接的基础上，然后点击窗体上面的“新建查询”按钮，然后在窗体右边会出现一块编辑区，你在编辑区域里面书写相应的SQL语句就可以。
下面是我写的一个例子，你可以参考下：
现在是建立数据库的SQL语句：
use
master
create
database
stuDB
on
(
name='stuDB',
size=5mb,filegrowth=15%,
filename='E:\B\stuDB.mdf'
)
log
on
(
name='stuDB_log',size=1mb,maxsize=6mb,filename='E:\B\stuDB_log.ldf'
)
use
stuDB
go
下面呢，是在新建好的数据库中新建数据表：
create
table
stuInfo
(
stuName
varchar(32)
not
null,
stuNo
varchar(16)
not
null
primary
key
,
stuSex
varchar(8)
not
null
default('男'),
stuAge
int
not
null,
stuSeat
int
not
null
identity(1,1),
stuAddress
ntext
default('地址不详')
)
以下是为新建好的数据表添加约束：
alter
table
stuInfo
add
constraint
ck_stuNo
check
(stuNo
like
's253xx')
alter
table
stuInfo
add
constraint
ck_stuAge
check
(stuAge>=15
and
stuAge<=50)
下面是给数据表添加数据：
insert
into
stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('张秋丽','s25301',default,18,'北京海淀')
insert
into
stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('李斯文','s25303','女',22,'河南洛阳')
insert
into
stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('李文才','s25302',default,31,'')
insert
into
stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)
values('欧阳骏雄','s25304',default,28,'新疆克拉玛依')
下面是查询表中的所有数据
select
*from
stuInfo
select
stuName
as
姓名,stuNo
as
学号,stuSex
as
性别,stuAge
as
年龄,stuSeat
as
座号,stuAddress
as
家庭住址
from
stuInfo
同样，也是新建数据表的语句块：
create
table
stuMarks
(
examNo
varchar(32)
not
null
primary
key,
stuNo
varchar(16)
not
null,
writtenExam
int
default(0),
labExam
int
default(0)
)
添加约束：
alter
table
stuMarks
add
constraint
ck_examNo
check
(examNo
like
'E200507xxxx')
alter
table
stuMarks
add
constraint
fk_stuNo
foreign
key
(stuNo)
references
stuInfo(stuNo)
alter
table
stuMarks
add
constraint
ck_writtenExam
check
(writtenExam>0
and
writtenExam<=100)
alter
table
stuMarks
add
constraint
ck_labExam
check
(labExam>0
and
labExam<=100)
添加数据到数据表：
insert
into
stuMarks(examNo,stuNo,writtenExam,labExam)
values('E2005070001',
's25301',80,58)
insert
into
stuMarks(examNo,stuNo,writtenExam,labExam)
values('E2005070002',
's25302',50,default)
insert
into
stuMarks(examNo,stuNo,writtenExam,labExam)
values('E2005070003',
's25303',97,82)
下面就是一些简单的查询了：
select
examNo
as
考号,stuNo
as
学号,writtenExam
as
笔试成绩,labExam
as
试验成绩
from
stuMarks
select
stuName
as
姓名,stuNo
as
学号,stuSex
as
性别,stuAge
as
年龄,stuSeat
as
座号,stuAddress
as
家庭住址
from
stuInfo
select
*from
stuInfo
where
stuSex='男'
order
by
stuSeat
desc
select
*
from
stuMarks
where
writtenExam>75
and
writtenExam<=100
order
by
stuNo
select*
from
stuMarks
where
writtenExam
<>0
and
labExam
<>0
select
stuName,writtenExam,labExam
into
lingshi
from
stuInfo,stuMarks
以下就是SQL的聚合函数部分了：
select
avg(writtenExam)
as
笔试平均成绩
from
stuMarks
select
avg(labExam)
as
机试平均成绩
from
stuMarks
select
count(*)
as
考试人数
from
stuMarks
where
writtenExam>60
select
count(*)
as
没有通过考试的人数
from
stuMarks
where
writtenExam<60

Answer 3

上面的同志说的很好，给他加点分吧，呵呵