oracle基础语句

电玩女神 2022-06-13 08:24 168阅读 0赞

USE lin;\#数据库  
\#建表  
CREATE TABLE acct (  
acct\_no VARCHAR(32) NOT NULL,  
acct\_name VARCHAR(60) NOT NULL,  
acct\_status VARCHAR(2),  
reg\_date DATE,  
balance DECIMAL(16,2),  
PRIMARY KEY (acct\_no)  
);  
DESC acct;\#查看表结构  
\#账户交易明细表  
CREATE TABLE acct\_detail (  
 acct\_no     VARCHAR(32) NOT NULL,  
 trans\_date  DATE        NOT NULL,  
 trans\_sn    VARCHAR(10) NOT NULL,  
 trans\_type  INTEGER     NOT NULL,  
 amt         DECIMAL(16, 2),  
 balance     DECIMAL(16, 2),  
 PRIMARY KEY (trans\_date, trans\_sn)  
);  
\#客户关系表  
CREATE TABLE cust(  
 cust\_no VARCHAR(32) NOT NULL,  
 cust\_name VARCHAR(60) NOT NULL,  
 tel\_no VARCHAR(32),  
 PRIMARY KEY(cust\_no)  
 );  
\#增加一列  
alter table acct add t\_name varchar(64);  
\#删除一列  
ALTER TABLE acct DROP COLUMN t\_name;  
\#重命名  
RENAME TABLE acct TO linxing;  
RENAME TABLE linxing TO acct;

\#给acct插入数据  
INSERT INTO acct(acct\_no,acct\_name,acct\_status,reg\_date,balance)  
     VALUES('0007','tom',1,NOW(),4000.00),('0008','Mavelin',1,NOW(),6000.00);\#now()取日期函数  
INSERT INTO acct(acct\_no,acct\_name,acct\_status,reg\_date,balance)  
     VALUES('0001','to',2,NOW(),4000.00);  
SELECT \*FROM acct;\#从acct表查询所有行、所有列  
INSERT INTO acct\_detail VALUES('0001',NOW(),'sn0001','1',2000.00,2000.00),  
         ('0001',NOW(),'sn0002','1',500.00,2500.00),  
         ('0002',NOW(),'sn0003','1',2500.00,2500.00);  
INSERT INTO acct\_detail VALUES('0007',NOW(),'sn0004','1',2000.00,2000.00);  
SELECT \*FROM acct\_detail;  
\#查询  
SELECT \*FROM acct;  
SELECT \*FROM acct WHERE acct\_no='0007';\#带一个条件查询  
SELECT \*FROM acct WHERE acct\_no='0007'OR balance=6000.00;  
\#统计  表里有几条数据  
SELECT COUNT(\*) FROM acct;  
\#统计  balance金额>=3000的  
SELECT COUNT(\*) FROM acct WHERE balance >=3000;  
SELECT COUNT(\*) FROM acct WHERE balance >=3000 AND acct\_status='0';\#两个条件  
SELECT COUNT(\*) FROM acct WHERE balance  NOT IN  
( SELECT balance FROM acct WHERE balance >1500);\#带条件查数目条件为子查询  
SELECT COUNT(\*) '笔数',acct\_status '状态' FROM acct  
GROUP BY acct\_status  
HAVING COUNT(\*) >=2;\#组合查询having过滤  having笔数大于等于1笔就显示  
\#模糊查询  
SELECT \*FROM acct WHERE acct\_name LIKE'z%';\#z开头的  
\#最大 最小 平均 求和  
SELECT MAX(balance),MIN(balance),AVG(balance),SUM(balance) FROM  acct;  
\#联合union all 不去重复  
SELECT '最大'AS '统计项',MAX(balance) AS '金额' FROM acct  
UNION ALL  
SELECT '最小'AS '统计项',MIN(balance) AS '金额' FROM acct  
UNION ALL  
SELECT '平均'AS '统计项',AVG(balance) AS '金额' FROM acct;

\#union  去重复 union all 不去重复  
SELECT 'aaa' FROM DUAL  
UNION  
SELECT 'aaa' FROM DUAL;  
SELECT 'aaa' FROM DUAL  
UNION ALL  
SELECT 'aaa' FROM DUAL;  
\#修改数据  
UPDATE acct SET balance =balance+1000  
WHERE acct\_no='0007'  
\#  
UPDATE acct SET reg\_date=(SELECT NOW() FROM DUAL)  
WHERE acct\_no='0008';  
\#排序  
SELECT \*FROM acct ORDER BY balance ASC;\#升序  
SELECT \*FROM acct ORDER BY balance DESC;\#降序  
SELECT \*FROM acct ORDER BY acct\_name ASC, balance DESC;\#两个条件name升balance降  
\#delete  
SELECT \*FROM acct;  
DELETE FROM acct WHERE acct\_no='0007';

SELECT \*FROM acct ORDER BY balance DESC  
LIMIT 3 \#limit 函数将前面3行取出

SELECT \*FROM acct WHERE acct\_no IN('0001','0008');\#in 后跟一个集合  
SELECT \*FROM acct WHERE acct\_no NOT IN('0001','0002');\#not in  不在集合里的  
SELECT \*FROM acct WHERE acct\_no  IN  
(SELECT acct\_no FROM acct WHERE balance>3000);\#in后可跟一个查询的结果  
SELECT \*FROM acct WHERE acct\_no NOT IN(SELECT acct\_no FROM acct WHERE acct\_no='0005');  
       \#显示查询到的所有信息                  \#显示查acct\_no满足条件的信息  
DESC acct;  
INSERT INTO acct(acct\_no,acct\_name,acct\_status,balance)VALUES(  
'0005','wangwu','1',2000);  
SELECT \*FROM acct WHERE reg\_date IS NULL;

SELECT \*FROM acct WHERE acct\_no NOT IN(  
SELECT DISTINCT acct\_no FROM acct\_detail  
       \#不同的有区别的  
)

\#联合查询     先看表SELECT \*FROM acct\_detail;  
SELECT a.acct\_no, a.acct\_name, b.trans\_date, b.amt  
FROM acct a, acct\_detail b  
WHERE a.acct\_no = b.acct\_no;  
\#外联接  左联接  
SELECT a.acct\_no,a.acct\_name,b.trans\_date,b.amt  
FROM acct a  
LEFT JOIN acct\_detail b  
ON a.acct\_no =b.acct\_no;

\#case  
SELECT \*FROM acct;  
SELECT acct\_no,  
 CASE acct\_status  
 WHEN '0'THEN'正常'  
 WHEN '1'THEN'冻结'  
 WHEN '2'THEN'销户'  
 WHEN '3'THEN'挂失'  
 WHEN '9'THEN'待激活'  
 ELSE'未知'  
 END'status'  
      FROM acct;