初学者sql server 语句怎么写

--有点乱。你自己粘贴好捋一捋。

use master

go

--判断数据库是否存在，存在则删除

if exists(select * from sysdatabases where name='MySchool') --判断MySchool是否存在

 drop DATABASE MySchool --如果数据库存在则删掉数据库

GO

--创建数据库

create DATABASE MySchool

on

PRIMARY --创建主数据库文件

(

 name='MyScool',

 filename='E:\DataBase\Test\MySchool\MySchool.mdf',

 size=5MB,

 Maxsize=20MB,

 FileGrowth=1MB

)

log on --创建日志文件

(

 name='MySchoolLog',

 filename='E:\DataBase\Test\MySchool\MySchool.ldf',

 size=5MB,

 maxsize=20MB,

 FileGrowth=1MB

)

GO

use MySchool

go

--------------------------创建表----------------------------------

--创建表Student

IF EXISTS (SELECT * FROM  sysobjects  WHERE  name='Student' )

DROP TABLE Student

GO

create table Student

(

 StudentNo nvarchar(50) not null,

 LoginPwd nvarchar(20) not null,

 StudentName nvarchar(50) not null,

 Sex char(2) not null,

 GradeId int not null,

 Phone nvarchar(20) not null,

 Address nvarchar(255) null,

 BornDate datetime null,

 Email nvarchar(50) null

)

GO

--创建表Subject

IF exists (select * from sysobjects where name='Subject')

Drop table Subject

Go

create table Subject

(

 SubjectId int identity(1,1) not null,

 SubjectName nvarchar(20) not null,

 ClassHour int not null,

 GradeId int not null

)

go

--创建表Result

If exists(select * from sysobjects where name='Result')

drop table Result

GO

create table Result

(

 Id int identity(1,1) not null,

 StudentNo nvarchar(50) not null,

 SubjectId int not null,

 StudentResult int not null,

 ExamDate smalldatetime not null

)

go

--创建年级表Grade

if exists(select * from sysobjects where name='Grade')

drop table Grade

go

create table Grade

(

 GradeId int identity(1,1) not null,

 GradeName nvarchar(50) not null

)

------------------------------------------------------------------

-------------------------为表添加约束-----------------------------

--为年级表Grade表添加约束

alter table Grade --主键约束

add constraint PK_Grade_GradeId Primary key(GradeId)

--为Student表添加约束

alter table Student --主键约束

add constraint PK_Student_StudentNo Primary key(StudentNo)

alter table Student --长度约束

add constraint CK_Student_LoginPwd check( len (LoginPwd)>=6)

alter table Student --密码默认约束

add constraint DF_Student_LoginPwd default ('123456') for LoginPwd

alter table Student --地址默认约束

add constraint DF_Student_Address default ('学生宿舍') for Address

alter table Student --检查约束

add constraint CK_Student_sex check (sex = ('男') or sex = ('女'))

alter table Student --外键约束

add constraint FK_Student_GradeId

 foreign key (GradeId) references Grade(GradeId)

go

alter table Student --Email约束

add constraint CK_Student_Email check (email like ('%@%'))

--为Subject表添加约束

alter table Subject --主键约束

add constraint PK_Student_SubjectId Primary key(SubjectId)

alter table Subject --大于0

add constraint CK_Subject_ClassHour check (ClassHour >0)

--为Result表添加约束

alter table Result --主键约束

add constraint PK_Result_Id Primary key(Id)

alter table [dbo].[Result] --必须在0~100之间

add constraint CK_Result_StudentResult check(StudentResult >0 and StudentResult <100)

exec sp_helpconstraint 'Student'  --查询所有约束

----------------------添加数据------------------------------------

--为Grade表添加数据

insert Grade(GradeName)  --使用insert...select语句一次插入多行数据

select 'S1' union

select 'S2' union

select 'Y2'

--为Student表添加数据

insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email)

 values('S1201302001','zhangsan','张三','男',1,'01062768866','解放路','1991-1-1','zhangsan@126.com')

insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate)

 values('S1201302002','lisi89','李四','男',1,'13812345678','长江路路','1996-2-1')

 

insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate)

 values('S1201302003',default,'王五','男',1,'13912345678',default,'1995-5-3')

 

insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email)

 values('S1201302004','wanglili','王丽丽','女',1,'1311245678',default,'1994-6-2','wangll@sohu.com')

 

insert into Student(StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate)

 values('S1201302005',default,'张峰','男',1,'1321234678',default,'1991-8-8')

------------------------------------------------------------------

use MySchool

go

-------------------------修改-------------------------------------

--根据学号修改地址

update Student

set Address = '山东省济南市文化路1号院'

where StudentNo = 'S1201302004'

--根据学号修改年级

update Student

set GradeId=2

where StudentNo='S1201302005'

--根据课程修改学时

update Subject

set ClassHour='55'

where SubjectName='走进Java编程世界'

--根据考试时间加分

update Result

set StudentResult=StudentResult+5

where StudentResult<60

--根据学号修改学生在特定时间考试的某一科分数

update Result

set StudentResult='55'

where StudentNo='S1201302002' and ExamDate='2013-10-18' and SubjectId=2

--将电子邮件为空的学生统一修改为"未知@"

update Student

set Email='未知@'

where Email is null

------------------------------------------------------------------

use MySchool

go

------------将表数据导向另一个不存在的表--------------------------

--通讯录表Address_IS(使用insret select语句,该表必须事先存在)

create table Address_IS

(

 Id int Primary key identity(1,1) not null,

 SName nvarchar(50) not null,

 Phone nvarchar(255) not null,

 Address nvarchar(255) null,

 Email nvarchar(50) null

)

insert into Address_IS(SName,Phone,Address,Email)

select Student.StudentName,Student.Phone,Student.Address,Student.Email

from Student

--通讯录表Address_SI(使用select into语句,该表不能预先存在)

Select Student.StudentName,Student.Phone,Student.Address,Student.Email

into Address_SI

from Student

------------------------------------------------------------------

use MySchool

go

--------------------删除记录--------------------------------------

--删除1997年7月1日之后入学的学生

delete from Student

where BornDate>'1997-7-1'

------------------------------------------------------------------

use MySchool

go

------------向Subject表插入数据-----------------------------------

insert Subject(SubjectName, ClassHour, GradeId)

select '走进Java编程世界',40,1 union

select 'HTML和CSS网页技术',60,1 union

select 'C#语言和数据库技术',70,1

------------------------------------------------------------------

use MySchool

go

--------------------增加成绩纪录----------------------------------

insert Result(StudentNo, SubjectId, StudentResult, ExamDate)

select 'S1201302001',1,80,'2013-9-13' union

select 'S1201302002',1,45,'2013-9-13' union

select 'S1201302001',2,90,'2013-10-18' union

select 'S1201302002',2,60,'2013-10-18'

------------------------------------------------------------------

use MySchool

go

if exists(select * from sysobjects where name='Grade')

drop table Grade

if exists(select * from sysobjects where name='Result')

drop table Result

if exists(select * from sysobjects where name='Student')

drop table Student

if exists(select * from sysobjects where name='Subject')

drop table Subject

go

if exists(select * from databases where name='MySchool')

drop database MySchool

go

建模以后按以下步骤进行：

1、进入结构分析模块（UG4.0是设计仿真，需要自己安装FEA软件）

2、选择分析解算器（3.0自带structure P.E.可以用）

3、加载荷

4、加约束（固定约束最好有一个）

5、赋材料

6、网格化

7、模型检查

8、解算

解算以后进入导航器看result，可以看到位移(displacement),应力（strss)、应变（strain)等的云图，其它后处理不做介绍

use SM

go

1.

//创建SC表

if exists(select * from sysobjects where name='sc') //判断数据中是否有该表，若有先删除再重建

drop table sc

create table sc

(

StudentNo varchar(20) not null,

CourseNo varchar(20) not null,

StudentResult int not null

)

go

//添加约束

2.

alter table Student add constraint PK_stuno primary key(StudentNo)

3.

alter table Student add constraint CK_sex check (sex='男' or sex='女')

4.

alter table Course add constraint PK_courseno primary key(CourseNo)

5.

alter table Course add constraint UQ_coursename unique (CourseName)

6.

alter table sc add constraint FK_studentno foreign key(StudentNo) references Student(StudentNo)

7.

alter table sc add constraint FK_courseno foreign key(CourseNo) references Course(CourseNo)

8.

alter table sc add constraint PK_stu primary key(StudentNo,CourseNo)

---