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.
COLOR: black">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
style="FONT-SIZE: 10pt; COLOR: red">.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、一次刪除多個字段
trong>
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(
yle="FONT-SIZE: 10pt; COLOR: black">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)
R: blue">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
tyle="FONT-SIZE: 10pt; COLOR: blue">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');
--下面這條記錄插入錯誤
ONT-SIZE: 10pt; COLOR: blue">insert into t2 values(5,'boy','leijiang');
--刪除主表中的記錄
delete t1;
--查看從表中記錄，已經沒有記錄，說明約束成功
select * from t2;
8、關於系統時間
--取得當前系統時間、當前月的最後一天，離當前月的結束還有幾天
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",
LAST_DAY(SYSDATE
t; COLOR: red">) -
SYSDATE "Days Left"
FROM DUAL;
--取得當前星期幾select to_char(
sysdate,'Day')
from dual;
--取得當前月select to_char(
sysdate,'Month')
from dual;
--取得指定日期的星期select to_char(
to_date(<
/span>'20020126','yyyymmdd'),'Day') from dual;
