oracle基础语句

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;