实用SQL語句

实用SQL語句

1、将一个表中的内容拷贝到另外一个表中

insert into testT1(a1,b1,c1) select a,b,c from test;

insert into testT select * from test; (前提是兩個表的結構完全相同)

insert into notebook(id,title,content)

select notebook_sequence.NEXTVAL,first_name,last_name from students;

注:a1,b1,c1是現在表中的字段名。A,b,c是原表中的字段名

拷贝单条数据
insert into a (select '123' as id, temp,temp2 from b where id=10)

2、查找重复记录

SELECT DRAWING,DSNO FROM EM5_PIPE_PREFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5

_PIPE_PREFAB D

WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

3、删除重复记录

DELETE FROM EM5_PIPE_PREFAB

WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5

_PIPE_PREFAB D

WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND

EM5_PIPE_PREFAB.DSNO=D.DSNO);

4、復制表結構

在ORACLE中: create table new_table as select * from old_table where rownum < 1;

在SQL SERVER中:说明：复制表(只复制结构,源表名：a 新表名：b)

　　SQL: select * into b from a where 1<>1 　

语句

5、一次刪除多個字段

alter table TEST_A drop (TEST_num,test_name);

6、實現一個表的備份

create table tableName_bak as select * from tableName;

7、創建兩個表的主外鍵關聯

--先創建主表

create table t1(id int,stu_name varchar(50));

--為主表增加持久的主鍵約束

alter table t1 add(constraint t1_id primary key(id) deferrable);

--創建從表，并將從表的id設為主鍵

create table t2(id int primary key,sex varchar(10),address varchar(50));

--為從表增加外鍵約束，該約束來自于主表所創建的約束字段

--當從主表中刪除記錄時，自動刪除從表中與之相對的具有相同id的記錄

--當在從表中插入記錄時，不能夠插入在主表中沒有id的記錄

alter table t2 add(constraint t2_id_fk foreign key(id) references t1(id) on delete cascade deferrable);

--測試數據

insert into t1 values(1,'FLB');

insert into t1 values(2,'FLB1');

insert into t1 values(3,'FLB2');

insert into t1 values(4,'FLB3');

insert into t2 values(1,'boy','leijiang');

insert into t2 values(2,'boy','leijiang');

insert into t2 values(3,'boy','leijiang');

insert into t2 values(4,'boy','leijiang');

--下面這條記錄插入錯誤

insert into t2 values(5,'boy','leijiang');

--刪除主表中的記錄

delete t1;

--查看從表中記錄，已經沒有記錄，說明約束成功

select * from t2;

8、關于系統時間

--取得當前系統時間、當前月的最后一天，離當前月的結束還有几天

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",

LAST_DAY(SYSDATE) - SYSDATE "Days Left"

FROM DUAL;

--取得當前星期几

select to_char(sysdate,'Day') from dual;

--取得當前月

select to_char(sysdate,'Month') from dual;

--取得指定日期的星期

select to_char(to_date('20020126','yyyymmdd'),'Day') from dual;