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;
--插入新入数据
insert into
managerinfo (managerid,loginname,password,name,tel) values
('2','zhangsan','1234','zhangsan','18554525695');
--创建账号信息表
create table
logininfo (
LoginNamevarchar2(10) NOT NULL,
LogionPaswordvarchar2(10) NOT Null,
CONSTRAINTS
PK_Loginname PRIMARY KEY(LoginName)
);
--从一个表的特定列导入到另一个表内
INSERT INTO
logininfo (loginname,logionpasword) SELECT loginname,password FROM managerinfo;
--查询语句查找logininfo的全部字段
select * from
logininfo;
DROP table
logininfo;
--创建表时插入数据
CREATE TABLE
logininfo AS SELECT loginname,password FROM managerinfo;
--修改表中数据UPDATE
UPDATE logininfo SET
password = '3456' WHERE loginname = 'xiaoming';
--修改表中全部数据
UPDATE logininfo SET
loginname = 'test';
--删除数据
DELETE FROM
logininfo where loginname = 'zhangsan';
--删除表中全部数据
DELETE FROM
logininfo;
--TRUNCATE关键字与DELETE相似，区别在于TRUNCATE是无条件删除
TRUNCATE TABLE
logininfo;
--MERGE关键字与update功能相似，区别在于MERGE可以对数据表同时进行添加和修改
/*
MERGE [INTO]
table_name1
USING table_name2
ON (condition)
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THENmerge_insert_clause;
*/
--其中merge_update_clause为条件正确是执行的sql语句；merge_insert_clause:为条件不正确是执行的sql
--MERGE更新数据表
MERGE INTO logininfo
using managerinfo
ON(logininfo.loginid =managerinfo.managerid)
WHEN MATCHED THEN UPDATE SETlogininfo.loginname = managerinfo.loginname;
--MERGE插入数据
MERGE INTO logininfo
using managerinfo
ON(logininfo.loginid =managerinfo.managerid)
WHEN NOT MATCHED THEN INSERT VALUES(managerinfo.name,managerinfo.password,managerinfo.managerid);
--MERGE插入与更新数据
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);
网友评论