linzy

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::

DDL alter create drop

DML select       update delete insert

DCL  grant revoke deny

===================================================================================

创建表:

create table 表名(

         col_1 数据类型 约束,

         col_2 数据类型 约束,

         ...

         id int identity(1,1) primary key

)

数据类型:

varchar char nvarchar int tinyint smallint bitint float numeric(p,n) datetime...

 

insert into 表名 (字段1,字段2) values (context1,context2)

 

 

alter table 表名 add col_1 数据类型 约束

alter table emp add address varchar(25) default "上海"

 

alter table emp drop column address

 

alter table emp drop constraint 约束名

 

drop table 表名

===================================================================================

1.主键

create table A_1(

         aId int primary key, ---1

         aName varchar(20),

         constraint PK_A_1 primary key (aId)       ---2

)

*alter table A_1 alter column aId int not null

alter table A_1 add constraint PK_A_1 primary key (aId) ---3

 

2.外键      1(父表):n(子表)关系,子表里的外键是父表里的主键

create table emp(

         empno int primary key,

         ename varchar(10),

         depno int foreign key references dep (depno),

         constraint FK_emp foreign key (..) references dep (..)

)

create table dep(

         depno int primary key,

         dname varchar(10)

)

alter table emp add constraint foreign key (..) references dep (..)

 

3.检查约束 check约束

create table people(

         pName varchar(20),

         pSex varchar(2) constraint ck_sex check (pSex = '男' or pSex = '女'),

         pId smallint not null constraint ck_pId check (pId between 1 and 100000)

)

4.default

create table people(

         pSex varchar(2) default '男',

         constraint ck_Sex check (pId between 1 and 100000)

)

primary key

foreign key references

default check

===================================================================================

1.insert update delete

 

insert into 表(col_1,col_2,...) values (value1,value2,...)

update 表 set col_1=val_2,col_2,val_2,... [where 条件]

delete from 表 where 条件

 

2.select

select [col_1,col_2]|* from tab1,tab2,...

[where 条件]

[group by 分组的列名]

[having 聚合函数比较操作]

[order by 排序的列名]

 

别名:select id,name as 姓名, '年龄'=age from student

select distinct id from tab1      distinct:过滤重复值

 

*where 条件1[or|and][条件2]......

1.比较操作 > < >= <= <> =

2.逻辑操作 and or not

select * from sc where score not between 70 and 80

--and

--or

--not

--between .. and

3.like         % _ select * from tab where sname like '%$_%' escape '$' 张_中

4.in          ... where sid in (2,3,4)

5.聚合函数 max min count avg sum

6.group by        select sex,count(sex) from student group by sex

select col1[,col2...] from 表 group by col1[,col2...]

7.having ... having count(*) > 2               对聚合函数修饰

 

8.等值连接操作(内连接)

select tname,cname from teacher t,course c where t.tno = c.tno

select tname,cname from teacher t inner join course c on t.tno = c.tno

9.外连接

left  outer join ... on ...

right outer join ... on ...

full  outer join ... on ...

===================================================================================

1.where子查询

2.from子查询

3.in子查询

4.any        >any 大于最小的 <any小于最大的 =any 相当于in

select * from 表 where col >any (select * from ...)

5.all   >all 小于最大的 <all小于最小的 =all没有数据

===================================================================================

一。函数操作:

1.getdate():获取当前日期

在当前系统增加2天:getdate()+2

2.dateadd(datepart, numer, date)  dateadd(dd,2,getdate())

3.datediff(datepart,start,end)         datediff(dd, getdate(), getdate()+3)        ==3

4.datepart(datepart,date)      返回日期某一部分

 

5.convert(datatype, data, style) 返日期转换成新数据类型

select convert(varchar(10), getdate(), 103) dd/mm/yyyy

yyyy-mm-dd hh:mi:ss(24h):120

参考网站:w3school.com.cn

 

二。字符函数的操作

1.len:字符串长度

select len(sname) from tab    ...... where len(sname) =3

2.replace:字符串替换函数    replate(expression,str2,str3)

select replace('abcdefg', 'abc', '$$$') -> $$$defg

3.substring(expression, start, length)

4.upper(s)

5.lower(s)

6.rtrim(s)

7.ltrim(s)

8.charindex(expression1, expresseion2 [,start_location])

charindex('a', 'abc') char('a','abcabc',2)

 

三。数字函数

abs:绝对值      select abs(-2.5)

sqrt:平方根     select sqrt(4)

rand:随机数    select rand()*10 从0-9之间的数

select convert(int, rand()*10)

...

 

四。其他重要

select scope_identity():插入成功的最后一个

 

===================================================================================

视图         create view t_view as select * from 表

 

1.创建视图    create view <视图名> [with encryption] as <子查询> [with check option]

2.修改数据    一张表 Or with check option 不行:聚合函数 group by 多张表

3.修改视图结构  alter view <视图名> as <子查询>

4.查看视图      sp_helptxt <视图名>

 

===================================================================================

存储过程:(主要是变量)

一、定义变量,更新数据

declare @num int;

set @num = 10;

update product set price = price + @num

-------->>>

create procedure ModifyPrice

(

  @num money;

)

as

update produce set price = price + @num [where ...]

 

执行:exec ModifyPrice @num=10

      Or execute ModifyPrice 10

 

-------->>>

参数:传入参数(普通参数),付出参数(output),return参数(返加状态,默认0,成功。)

create procedure GetCustomerCount

(

         @count int output;

)

as

declare @num int;

select @num = count(*) from customers

set @count = @num;

使用-->

declare @mycount int;

exec GetCustomerCount @count = @mycount output;

  Or execute GetCustomerCount @mycount output

print @mycount;

-------->>>

create procedure CreateUser

(

         @username nvarchar(100);

)

as

         declare @namelen int;

         set @namelen = len(username);

         if @namelen >=2

                   return 0;

         else

                   return 1;

 

使用-->

declare @returnValue int;

exec @returnValue = CreateUser @username = '林鑫';

print @returnValue;

 

===================================================================================

用户自定义函数

create function GetMinute

(

         @datevalue datetime;

)

return int

as

begin

         declare @min int;

         set @min = datepart(minute, @datevalue);

         return @min;

end;

 

:-->> select GetMinute(getdate())

===================================================================================

触发器 after触发器 instead of触发器 DDL触发器 DML触发器

 

create trigger tg_ship

on Shippers after insert,delete,update

as

         insert into shipper_log (onuser, op, shipname, shipphone)

         select user_name(), 'Insert', companyname, phone

         form inserted;

go

 

inserted insert update受影响的新数据镜像

updated  delete update受影响的旧数据镜像

===================================================================================

事务和并发     Transaction and      rollback/commit

 

隐式事务:每执行一条DML操作,就直接提交到数据库保存

delete from table where id = ?

 

显式事务,明确指出事务的边界

begin transaction

delete from table where id = ?1

delete from table where id = ?2

delete from table where id = ?3

 

--结束事务(回滚\提交)

rollback;

commit;

 

原子性(atomicity)、一致性(consistency)、隔离性(Isolation)、持久性(Durability)

 

posted on 2013-07-29 12:02  linzy  阅读(295)  评论(2编辑  收藏  举报