sql数据操作0509

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)
 );
#给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(*) '笔数',acct_status '状态' from acct
group by acct_status
having count(*) >=1;#组合查询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;
#修改数据
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后可跟一个查询的结果
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'