--添加列
ALTER TABLE 表名 ADD 字段名 数据库类型 null DEFAULT('')
--删除列
ALTER TABLE 表名 DROP COLUMN 列名
--删除默认值
ALTER TABLE 表名
DROP CONSTRAINT 默认值名
--添加默认值
ALTER TABLE 表名 ADD CONSTRAINT
默认值名 DEFAULT 默认值 FOR 字段名
--设置主键
create table xing
(
chenid int identity(1,1) primary key, --单个主键
chenname varchar(50) not null
)
create table qiang
(
pingid int,
pingname int references xing(chenid), --外键
primary key(pingid,pingname) --多个主键
)
--
create table qiang
(
pingid int,
pingname int references xing(chenid) ON UPDATE CASCADE ON DELETE CASCADE, --外键级联更新级联删除
primary key(pingid,pingname)
)
SELECT IDENT_CURRENT('jobs') --整个数据库最后返的自动增长值
SELECT @@IDENTITY --当前表返的自动增长值
--行上锁
BEGIN TRANSACTION
INSERT INTO jobs (job_desc,min_lvl,max_lvl) VALUES ('Accountant',12,125)
SELECT IDENT_CURRENT('jobs')
COMMIT TRANSACTION
--触发器
CREATE TRIGGER TR_CHEN
ON k_teacher_Info
FOR DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON
IF EXISTS(SELECT 1 FROM DELETED WHERE f_createdate<'2008-11-1') OR EXISTS(SELECT 1 FROM INSERTED WHERE f_createdate<'2008-11-1')
BEGIN
RAISERROR('不能删除10月份的老师信息。',16,1)
ROLLBACK TRANSACTION
END
END
--时间操作
select
case when date > '2008-11-8' AND date < '2008-11-8 23:59:59' then
case when datediff(minute,date,getdate()) < 60
then convert(varchar(3),datediff(minute,date,getdate()))+'分钟前'
else
convert(varchar(3),datediff(hour,date,getdate()))+'小时 / ' + convert(varchar(2),datediff(minute,date,getdate())%60) + '分钟前'
end
else Convert(varchar(2),datepart(hour,date))+':' + convert(varchar(2),datepart(minute,date))+' ' + convert(varchar(40),datename(weekday,date)) end as diffdate
from tchen
SELECT id,date,
CASE
When DATEPART(WEEKDAY,date)=DATEPART(WEEKDAY,GETDATE()) Then '今天'
When DATEPART(WEEKDAY,GETDATE())-DATEPART(WEEKDAY,DATE)=1 Then '昨天'
ELSE DATENAME(WEEKDAY,date)
END AS weekname,
CONVERT(VARCHAR(2),DATEPART(MONTH,DATE))+'-'+CONVERT(VARCHAR(2),DATENAME(DAY,DATE))
FROM tchen ORDER BY date DESC
SELECT @@DATEFIRST --设置一星期的第一天
select datepart(weekday,getdate()) --返回整数 1不星期日
select datename(weekday,getdate()) --返回具体内容
RAISERROR('删除错误!',16,1) --抛出错误信息方法
SET QUOTED_IDENTIFIER ON --设置单引号 OFF时可以使用双引号
SET ANSI_NULLS ON --设置使用NULL