实用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)
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
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
;