基础SQL语句大全.doc

第一章:
--创建数据库
create database mydb
on
(
name=mydb_Data,
filename='D:\DBFile\',
size=2MB,
maxsize=10MB,
filegrowth=1MB
)
log on
(
name=mydb_Log,
filename='D:\DBFile\',
size=1MB,
maxsize=10MB,
filegrowth=10%
)
--查看数据库信息
exec sp_helpdb mydb
use mydb
--修改信息
alter database mydb
modify file
(
name=mydb_Data,
size=3MB
)
--修改数据库名称
alter database firstdb
modify name=seconddb
exec sp_renamedb 'seconddb','firstdb'
--删除数据库
drop database firstdb
create table ttt
(id int)
第二章:
--打开自己的数据库
use mydb
--创建简单表
create table tb1
(
sid int,
sname varchar(20),
sex char(2)
)
select * from tb1
--自动获取列值的表
--default约束
create table tb2
(
sid int ,
sname varchar(20),
sex char(2) default '男'
)
select * from tb2
insert into tb2 values(1,'张三','男')
insert into tb2(sid,sname) values(2,'李四')
insert into tb2 values(3,'王五',null)
--自动编号
create table tb3
(
sid int identity(1000,10),
sname varchar(20)
)
select * from tb3
insert into tb3 values('张三')
insert into tb3 values('李四')
insert into tb3 values('王五')
delete from tb3 where sid=1020
insert into tb3 values('赵六')
--主键约束
create table students
(
sno char(4) primary key,
sname varchar(20) not null,
age int
)
select * from students
insert into students values('s001','张三',21)
insert into students values('s001','张三',21)--error
insert into students(sname,age) values('张三',21)--error
insert into students(sno,age) values('s002',22)--error
insert into students values('s002','李四',21)
insert into students(sno,sname) values('s003','王五')
--课程表
create table course
(
cno int ,
cname varchar(30),
constraint pk_course primary key (cno)
)
select * from course
insert into course values(1,'C#基础')
insert into course values(1,'SQLServer')
insert into course values(2,'SQLServer')
insert into course (cname) values('WindowsForm')
--成绩表(学号,课程号,成绩)
create table score
(
sno char(4) references students(sno),
cno int,
grade int ,
Constraint fk_course foreign