sql数据操作0509

本是古典何须时尚 2022-06-16 07:24 93阅读 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)  
 );  
\#给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'