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;
#建表
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 '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行取出
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('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 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;
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'
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'