mssql之从头再来
来源:互联网 发布:怎么寻找淘宝达人 编辑:程序博客网 时间:2024/06/10 19:20
去了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
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
- mssql之从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来!!!!
- 从头再来
- 从头再来
- 从头再来!!
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 从头再来
- 大型3d游戏资源加载三步曲(硬盘-&gt;内存-&gt;显存)
- grep的一些使用方法总结
- 使用fckeditor2.4.3作为文件浏览器
- 处理交叉透明物体的渲染
- 三十三十三十
- mssql之从头再来
- C++内存管理之内存分配详解
- 2007年个人回忆与总结
- Mssql函数转载
- mssql表联级
- C++辨析
- Anole3——实现用户满意度接近100%的方案
- 1988年的全新苹果II拆包记
- 完美绿色dezend本地版下载