mssql之从头再来

去了baidu,每周都得到数据库以及C#这一块的专家,便自以为.....才来到csdn两天,才明白自己是多么的.....,好在我觉悟了,从头再来!

if exists(select * from master..sysdatabases where name='mydb')
-- if (select db_id('mydb')  )is null
print '数据库已经存在'
else
begin
create database mydb
end


use mydb

--if exists(select 1 from sysobjects where name='test')
create table test
(
id int primary key identity(1,1),
name varchar(30) not null unique,
age tinyint,
notetime smalldatetime
)

--为了多运用，比如说notetime smalldatetime default getdate(),但是为了累积自己的知识，因此创建约束以及默认值

--约束阿,默认值等都是对象,检验他是否存在和表一样
create default GetCurrentTime as getdate()
--如果要修改,那么直接alter default getcurrenttime as '2000-01-11'
sp_bindefault getcurrenttime,'test.notetime'

create rule checkage as  @age<=120

sp_bindrule checkage,'test.age'



insert into test values ('张三',15,getdate())
insert into test (name,age)values ('李四',15)


insert into test (name,age)values ('王五',19)
insert into test values ('赵六',40,'2001-4-6')


insert into test (name,age)values ('小二',50)
insert into test values ('老三',70,getdate())


select * from test

1    张三    15    2008-02-05 14:39:00
2    李四    15    2008-02-05 14:39:00
3    王五    19    2008-02-05 14:39:00
4    赵六    40    2001-04-06 00:00:00
5    小二    50    2008-02-05 14:39:00
6    老三    70    2008-02-05 14:39:00


select * from test


select * from test where id=1

select * from test where id between 3 and 5

select top 5 * from test except select top  2* from test

select * from test where id=1 or id=2

select * from test where in in (1,2,3)

select name,age from test where name like '%二%'

select * from test where age !=10 order by  name,age desc


select * from test where notetime between '2001-04-06 00:00:00' and '2008-01-05 14:39:00'

select month(notetime) from test
--day,year都是一样的,如果要获取月日的话,那么要先转换成文本类型,否则会被识别为数字



alter table test add sex bit default 0

select * from test

update test set sex=0


update test set age=age+3 where age=15

select * from test


select name,min(id)as 'id' from test group by name

delete test where id in(
select min(id) from test group by age having count(age)>1)

--drop table test