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)