SQLServer

1.创建表

create table T_name

(dept_name varchar(20) not null,

primary key dept_name,

foreign key d_name) redfurences department

 

2.插入记录

insert into T_name values(A1,A2...)

 

3.删除表，删除表数据

drop table T_name;

delete from T_name;

 

4.增加列、删除列

alter table T_name add A,D;

alter table T_name drop A;

 

5.distinict,all 去除、不去除重复

 

6.相似查询

select * from T_name where A like '%xx%' group by xx;

 

7.and or not

 

8.upper(x),lower(x),trim(x),||串联,like '%xx%'

百分号：(%) 匹配任意字符串

下划线：(_)匹配任意一个字符

‘%like%’：包含

‘___%’：包含至少三个字符的字符串

escape：定义转义字符 like‘ab\%cd%’ escape '\'

 

9.select * from T_name order by xx desc/asc;

between xx and xx;

 

10.并运算：union->自动去除重复

(select xx from xx where xx) union (select xx from xx where xx)

交运算：intersect (all:保留所有重复)

差运算：except (all)

is null, is unknow, is not null, is not unknow

 

11.聚集函数：

平均值：avg              最小值：min                 最大值：max                

总和：sum       计数：count         select count(x) as x from xx

 

12.自然连接：natural join-> 两个关系模式中都出现的属性上取值想通的元组对

 

13.嵌套子查询

in，not in

select A1 from t_name where xx and A1 in (select A1 from T_name where xx)

select B1 from T_name where B1 not in('x1', 'x2')

集合比较：some,all, >, =, <

select A1 from T_name where B1 > some(selcet B1 from T_name where x)

空关系： exists 存在 unique 重复元组存在

select A1 from T_name as T where x and exists (select * from T_name as T where x)

with:定义临时关系

withmax_budget (value) as (select max(budget) from T_name)

select budget from T1,T2 where T1.budget = T2.value

 

14.delete from T_name where x

insert into T_naem values (x,y...)

update T_name set xx=xx where xx

update t_nameset xx = case

When xx then xx

else xx

End

 

15.外连接：natural outer join

select * from T_name natural left/right/full out join xx where xx;

inner join：内连接，不保留未匹配元组的连接运算

select * from T_name left outer join takes on xx

 

16.视图

create view v_name as select xx from T_name

 

17.约束 not null, unique(A1...), check

create table T_naem (a1,b1,c1,primary key (a1,b1), check (a1 in ('a', 'b', 'c')));

参照完整性：referential integrity

foreign key (xx) reference T_name

 

18.date,time(p),time stmp(p) p:秒的小数点后的数字位数

default：默认值

 

19.索引

create index index_name on T_name (列名)

 

20.大对象类型

字符数据的大对象数据类型 clob

二进制数据得大对象数据类型 blob

 

21.触发器

create trigger t_name after insert on T_name referencing new row as nrow

for each row when (nrow.id not in (select time_slot_id from time_slot))

begin 

xx

end;

 

22.复制表

select * into T_b from T_a where 1<>1

 

23.insert into T_b(1,2,3) select d,e,f from T_b;