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,sex,clID) values('用户2','M',2)

insert into student(stuName,sex,clID) values('用户3','F',3)

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

insert into student(stuName,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,stuName,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 *from spt_values order by number;//默认递增





select type,count(type) from spt_values group by type having count(type)>19;

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;