易截截图软件、单文件、免安装、纯绿色、仅160KB

SQL语句PART7

Merge statement
function benefits: 1) provides the ability to conditionally update, insert or delete data into a database table. 2) performs an update if the row exists, and an insert if it is a new row. --> 1) avoids seperate updates, 2) increase performance and ease of use. 3) is useful in data warehousing applications.
syntax:
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col1_val,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);
e.g.: table: temp_t1:
results:
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     REPORT
5    3    督察督办     SUPERVISAL
6    2    值班快报     DAILYREPORT
7    2    我的事情     NOTIFY
8    4    提案            RESOLUTION
9    4    建议            SUGGESTION
e.g.: table: temp_t2:
results:
1    1    收文            SHOUWEN
2    1    发文            FAWEN
3    1    交办            JIAOBAN
4    2    值班报告     REPORT
5    3    督察督办    


相关文档:

(转)SQL经典面试题集(三)


第二十题:
怎么样抽取重复记录
表:
id name
--------
1 test1
2 test2
3 test3
4 test4
5 test5
6 test6
2 test2
3 test3
2 test2
6 test6
查出所有有重复记录的数据,用一句sql 来实现
create table D(
id varchar (20),
name varchar (20)
)
insert into D values('1','test1')
insert into D v ......

sql server 内存表

--1加内存表
EXEC sp_tableoption '表名','pintable', 'true' 
--2卸载内存表
EXEC sp_tableoption '表名','pintable', 'false' 
--2查询是否有内存表驻留
SELECT * from INFORMATION_SCHEMA.Tables 
WHERE TABLE_TYPE = 'BASE TABLE' 
          AND OBJECTPROP ......

SQL语句PART4

GRANT
Name
GRANT -- 定义访问权限
Synopsis GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
    [,...] | ALL [ PRIVILEGES ] }
    ON [ TABLE ] tablename [, ...]
    TO { username | GROUP groupname | PUBLIC } [, ...] [ WI ......

SQL语句PART5

Confirming granted privileges
Data Dictionary View                Description
ROLE_SYS_PRIVS                System privileges granted to roles
ROLE_TAB_PRIVS    & ......

SQL语句PART6

Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE,  INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation. ......
© 2009 ej38.com All Rights Reserved. 关于E健网联系我们 | 站点地图 | 赣ICP备09004571号