select * from
managerinfo;
--添加語句
INSERT INTO
managerinfo (managerid,loginname,password,name,tel)
values('1','xiaoming','1234','xiaoming','13853635223');
select table_name
from user_tables;
select * from
managerinfo;
--插入新入數(shù)據(jù)
insert into
managerinfo (managerid,loginname,password,name,tel) values
('2','zhangsan','1234','zhangsan','18554525695');
--創(chuàng)建賬號(hào)信息表
create table
logininfo (
LoginNamevarchar2(10) NOT NULL,
LogionPaswordvarchar2(10) NOT Null,
CONSTRAINTS
PK_Loginname PRIMARY KEY(LoginName)
);
--從一個(gè)表的特定列導(dǎo)入到另一個(gè)表內(nèi)
INSERT INTO
logininfo (loginname,logionpasword) SELECT loginname,password FROM managerinfo;
--查詢語句查找logininfo的全部字段
select * from
logininfo;
DROP table
logininfo;
--創(chuàng)建表時(shí)插入數(shù)據(jù)
CREATE TABLE
logininfo AS SELECT loginname,password FROM managerinfo;
--修改表中數(shù)據(jù)UPDATE
UPDATE logininfo SET
password = '3456' WHERE loginname = 'xiaoming';
--修改表中全部數(shù)據(jù)
UPDATE logininfo SET
loginname = 'test';
--刪除數(shù)據(jù)
DELETE FROM
logininfo where loginname = 'zhangsan';
--刪除表中全部數(shù)據(jù)
DELETE FROM
logininfo;
--TRUNCATE關(guān)鍵字與DELETE相似乓梨，區(qū)別在于TRUNCATE是無條件刪除
TRUNCATE TABLE
logininfo;
--MERGE關(guān)鍵字與update功能相似痕寓，區(qū)別在于MERGE可以對(duì)數(shù)據(jù)表同時(shí)進(jìn)行添加和修改
/*
MERGE [INTO]
table_name1
USING table_name2
ON (condition)
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THENmerge_insert_clause;
*/
--其中merge_update_clause為條件正確是執(zhí)行的sql語句；merge_insert_clause:為條件不正確是執(zhí)行的sql
--MERGE更新數(shù)據(jù)表
MERGE INTO logininfo
using managerinfo
ON(logininfo.loginid =managerinfo.managerid)
WHEN MATCHED THEN UPDATE SETlogininfo.loginname = managerinfo.loginname;
--MERGE插入數(shù)據(jù)
MERGE INTO logininfo
using managerinfo
ON(logininfo.loginid =managerinfo.managerid)
WHEN NOT MATCHED THEN INSERT VALUES(managerinfo.name,managerinfo.password,managerinfo.managerid);
--MERGE插入與更新數(shù)據(jù)
MERGE INTO logininfo
USING managerinfo
ON
(logininfo.loginid = managerinfo.managerid)
WHEN MATCHED THEN UPDATE SETlogininfo.loginname = managerinfo.loginname
WHEN NOT MATCHED THEN INSERTvalues(managerinfo.loginname,managerinfo.password,managerinfo.managerid);
