基础知识资料 sql 存在检测、建库、 建表、约束、外键、级联删除 --打开库master use mastergo --判断删除已存在的数据库 stuDBif exists(select * from sysdatabases where name = 'stuDB')drop database stuDB --新建数据库 stuDBcreate database stuDBon primary(name='stuDBdata',filename='d:\sql\stuDBdata.mdf',size=5mb,maxsize=unlimited,filegrowth=10%)log on(name='stuDBlog',filename='d:\sql\stuDBdata.ldf',size=1mb,maxsize=unlimited,filegrowth=1mb)go --打开use stuDBgo --判断是否已存在要创建的表if exists(select * from sysobjects where name = 'stuinfo')drop table stuInfo --新建表create table stuInfo(stuName varchar(20) not null,stuNo varchar(20) not null,stuSex varchar(20) not null,stuAge int not null,stuSeat int identity(1,1) not null,stuAddress text not null)go exec sp_help stuInfo --查看表属性 --为表添加约束alter table stuInfo-- with nocheck --在创建约束时忽略已存在的坏数据addconstraint pk_stuNo primary key (stuNo),constraint ck_stuNo check(stuNo like 's253__'),// constraint ck_stuNo check(stuNo like 's253[0-9][0-9]'),这样会全面些。constraint ck_stuSex check(stuSex in ('男','女')),constraint ck_stuAge check(stuAge between 15 and 40),constraint ck_stuSeat check(stuSeat between 1 and 30),constraint df_stuAddress default '地址不祥' for stuAddressgo exec sp_helpconstraint stuInfo --查看约束 -- nocheck -临时禁用现有约束alter table stuInfonocheckconstraint pk_stuNogo -- check -恢复现有约束alter table stuInfocheckconstraint pk_stuNogo --添加数据insert into stuInfo values('小强','s25301','男',18,'北京海淀')insert into stuInfo values('旺财','s25303','女',22,'河南洛阳')insert into stuInfo values('梅超风','s25302','男',31,default)insert into stuInfo values('欧阳俊雄','s25304','男',28,'新疆威武哈') --查看表中数据select * from stuinfo --打开stuDB数据库use stuDBgo --检查stuMarks表是否存在if exists(select * from sysobjects where name='stuMarks')drop table stuMarksgo --新建stuMarks表create table stuMarks(sId int identity(101,1) not null,stuNo varchar(20) not null,writtenExam float not null,labExam float not null)go --查看表属性exec sp_help stuMarks --为stuMarks建立约束alter table stuMarksaddconstraint pk_sId primary key(sId),constraint fk_stuNo foreign key(stuNo)references stuInfo(stuNo)on update no action --级联更新(无操作)on delete cascade,--级联删除constraint ck_writtenExam check(writtenExam between 0 and 100),constraint ck_labExam check(labExam between 0 and 100)go --查看表约束exec sp_helpconstraint stuMarks insert into stuMarksvalues('s25301',80,65)insert into stuMarksvalues('s25302',87,95)insert into stuMarksvalues('s25303',83,69)insert into stuMarksvalues('s25304',70,74)
--查看表中是否存在约束名为 ck% select * from sysobjects where name like 'ck%'--存在检测if exists(select * from sysobjects where name = 'ck_stuAge')alter table stuInfodrop constraint ck_stuAgego --创建stuAge约束alter table stuinfoaddconstraint ck_stuAge check (stuAge between 15 and 40)go
insert into stuInfo values('小强','s25301','男',18,'北京海淀') insert into stuInfo values('旺财','s25303','女',22,'河南洛阳') insert into stuInfo values('梅超风','s25302','男',31,default) insert into stuInfo values('欧阳俊雄','s25304','男',28,'新疆威武哈')
1.求向sql 2005 表中输入数据的具体方法和具体操作。 ---<1>通过sql语句 insert into 表名 (列名,列名) value (值,值) ---<2>就是下面你说的 2.我在sql2005的表上点鼠标右键“打开表”输入数据时为什么会出现一个红色的小圆圈里带一个感叹号? --你只要输入数据,在把手表移开,就好了!
sql 存在检测、建库、 建表、约束、外键、级联删除
--打开库master
use mastergo
--判断删除已存在的数据库 stuDBif exists(select * from sysdatabases where name = 'stuDB')drop database stuDB
--新建数据库 stuDBcreate database stuDBon primary(name='stuDBdata',filename='d:\sql\stuDBdata.mdf',size=5mb,maxsize=unlimited,filegrowth=10%)log on(name='stuDBlog',filename='d:\sql\stuDBdata.ldf',size=1mb,maxsize=unlimited,filegrowth=1mb)go
--打开use stuDBgo
--判断是否已存在要创建的表if exists(select * from sysobjects where name = 'stuinfo')drop table stuInfo
--新建表create table stuInfo(stuName varchar(20) not null,stuNo varchar(20) not null,stuSex varchar(20) not null,stuAge int not null,stuSeat int identity(1,1) not null,stuAddress text not null)go
exec sp_help stuInfo --查看表属性
--为表添加约束alter table stuInfo-- with nocheck --在创建约束时忽略已存在的坏数据addconstraint pk_stuNo primary key (stuNo),constraint ck_stuNo check(stuNo like 's253__'),// constraint ck_stuNo check(stuNo like 's253[0-9][0-9]'),这样会全面些。constraint ck_stuSex check(stuSex in ('男','女')),constraint ck_stuAge check(stuAge between 15 and 40),constraint ck_stuSeat check(stuSeat between 1 and 30),constraint df_stuAddress default '地址不祥' for stuAddressgo
exec sp_helpconstraint stuInfo --查看约束
-- nocheck -临时禁用现有约束alter table stuInfonocheckconstraint pk_stuNogo
-- check -恢复现有约束alter table stuInfocheckconstraint pk_stuNogo
--添加数据insert into stuInfo values('小强','s25301','男',18,'北京海淀')insert into stuInfo values('旺财','s25303','女',22,'河南洛阳')insert into stuInfo values('梅超风','s25302','男',31,default)insert into stuInfo values('欧阳俊雄','s25304','男',28,'新疆威武哈')
--查看表中数据select * from stuinfo
--打开stuDB数据库use stuDBgo
--检查stuMarks表是否存在if exists(select * from sysobjects where name='stuMarks')drop table stuMarksgo
--新建stuMarks表create table stuMarks(sId int identity(101,1) not null,stuNo varchar(20) not null,writtenExam float not null,labExam float not null)go
--查看表属性exec sp_help stuMarks
--为stuMarks建立约束alter table stuMarksaddconstraint pk_sId primary key(sId),constraint fk_stuNo foreign key(stuNo)references stuInfo(stuNo)on update no action --级联更新(无操作)on delete cascade,--级联删除constraint ck_writtenExam check(writtenExam between 0 and 100),constraint ck_labExam check(labExam between 0 and 100)go
--查看表约束exec sp_helpconstraint stuMarks
insert into stuMarksvalues('s25301',80,65)insert into stuMarksvalues('s25302',87,95)insert into stuMarksvalues('s25303',83,69)insert into stuMarksvalues('s25304',70,74)
--查看表中是否存在约束名为 ck%
select * from sysobjects where name like 'ck%'--存在检测if exists(select * from sysobjects where name = 'ck_stuAge')alter table stuInfodrop constraint ck_stuAgego
--创建stuAge约束alter table stuinfoaddconstraint ck_stuAge check (stuAge between 15 and 40)go
insert into stuInfo values('旺财','s25303','女',22,'河南洛阳')
insert into stuInfo values('梅超风','s25302','男',31,default)
insert into stuInfo values('欧阳俊雄','s25304','男',28,'新疆威武哈')
1.求向sql 2005 表中输入数据的具体方法和具体操作。
---<1>通过sql语句
insert into 表名 (列名,列名) value (值,值)
---<2>就是下面你说的 2.我在sql2005的表上点鼠标右键“打开表”输入数据时为什么会出现一个红色的小圆圈里带一个感叹号?
--你只要输入数据,在把手表移开,就好了!