sqlserver基础操作

启动服务：

1.在系统服务启动

2.在sql配置管理器服务选项中启动

3.在管理员cmd：net start mssqlserver;net stop mssqlserver

use master
go
if(not exists(select* from sysdatabases where name='school'))
create database school
on primary
(
	name='School_data',filename='E:School_data.mdf',size=10MB,filegrowth=1MB
)

log on
(
	name='School_log',filename='E:School_log.ldf',size=2MB,filegrowth=1MB
)
go

use school
create table class(clId int not null identity(1,1) constraint PK_cid primary key,clName varchar(40) not null);//定义自增长主键
create table student(stuId int not null identity(1,1)constraint PK_sid primary key,stuName varchar(20) not null,sex varchar check (sex in('F','M')) not null,clID int constraint FK_StuClass foreign key(clID) references class(clID)) //定义外键约束,检查约束

insert into class(clName) values('软件1班')
insert into class(clName) values('软件2班')
insert into class(clName) values('软件2班')

update class set clName='软件3班' where clId=3
insert into class(clName) values('软件4班')

select *from class

insert into student(stuName,sex,clID) values('用户1','F',1)
insert into student(stuName,clID) values('用户2','M',2)
insert into student(stuName,clID) values('用户3',3)
insert into student(stuName,clID) values('用户4',sex) values('用户5','M')

select *from student


//内连接
select student.clID,stuName,clName from student
inner join class on student.clId=class.clId order by clId
//左连接
select student.clID,clName from student
left join class on student.clId=class.clId
where student.clId is null order by clId
//右连接
select count(student.clID)as count from student
right join class on student.clId=class.clId
group by class.clId having count(student.clID)>1
//嵌套查询
select *from student where clId=(select clId from class where clId=2)


go

use master
go
if exists(select *from sysdatabases where name='school')
	drop database school


use master
go
exec sp_detach_db school
go

exec sp_attach_db school,'E:School_data.mdf','E:School_log.ldf'
go

use master;

select distinct type from spt_values;

create database hh; use hh; drop table ggz; create table ggz(id int IDENTITY(1,1),num int not null,adds varchar(10)) //添加主键约束 alter table ggz alter column id int not null; alter table ggz add constraint pk_id primary key(id); //删除主键约束 alter table ggz drop constraint pk_id; //添加唯一约束 alter table ggz add constraint UQ_adds unique(adds); //添加默认约束 alter table ggz add constraint DF_adds default('ca') for adds; //修改属性 alter table ggz alter column adds varchar(10) null; //插入 insert into ggz(id,num) values(3,3); //添加检查约束 alter table ggz add constraint CK_num check(num in(1,0)); //更新 update ggz set num=2 where id=3; insert into ggz(num) values(0); //删除 delete from ggz where num=0;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!