[SQL]新刪修查Create Select Insert Update Delete
-- **SQL Server**
--Create
CREATE TABLE [dbo].[TEST](
[ISSUEID] [char](16) NOT NULL,
[DETAIL] [char](16) NOT NULL,
[STR1] [char](10) NULL,
[STR2] [char](1) NOT NULL DEFAULT ('G'));
--Select
select * from TEST ;
--Drop
DROP TABLE [dbo].[TEST];
--Insert
INSERT INTO [dbo].[TEST] ([ISSUEID],[DETAIL],[STR1],[STR2]) VALUES ('202212','','','') ;
--Update
UPDATE [dbo].[TEST] SET [ISSUEID] = '20221201' ,[DETAIL] = 'YAO' ,[STR1] = '1' ,[STR2] = '2' WHERE ISSUEID='202212' ;
--Delete
DELETE FROM [dbo].[TEST] WHERE ISSUEID='202212' ;
--Truncate
TRUNCATE TABLE [dbo].[TEST];
-- **Oracle SQL**
--Create table
create table O_TEST
(
CHANNEL VARCHAR2(20) not null,
MOBILE VARCHAR2(20),
EMAIL VARCHAR2(30),
MEMO VARCHAR2(30)
);
--SIUD
select * from onces.O_TEST where channel='crud';
insert into onces.O_TEST (CHANNEL, MOBILE, EMAIL, MEMO) values ('crud', '0000000000', '', '');
update onces.O_TEST set mobile='mob' where channel='crud';
delete from onces.O_TEST where channel='crud';
--truncate
truncate table onces.O_TEST;
--drop
drop table onces.O_TEST;
ps.Delete會鎖定每筆資料，再執行刪除、Truncate Table只鎖定資料表和頁面，不會鎖定每個資料列。