实例1
customer表
deposite 表
bank表
1.将数据表deposite中孙杨的存款金额加10000
UPDATE deposite SET amount=amount+10000
where (SELECT c_id FROM customer WHERE name
=‘孙杨’)
【update 表 set 字段=新字段】
2.将数据表deposit 中所属为工商银行并且存入日期为2011-04-05的人员的存款加1000
UPDATE deposite set amount=amount+10000
WHERE dep_date =‘2011-04-05’ AND b_id in(SELECT b_id FROM bank WHERE bank_name=‘工商银行’)
3.将数据表deposite中郭海的银行标识改为建设银行
UPDATE deposite SET b_id =(SELECT b_id FROM bank WHERE bank_name=‘建设银行’)
where c_id in(select c_id FROM customer WHERE NAME=‘郭海’)
【三个表同时使用到】
4.查询孙杨的存款信息(显示信息:客户ID,客户姓名,银行名称,存款金额)
SELECT customer.c_id,name,bank_name,amount
FROM INNER JOIN customer ON customer.c_id=deposite.c_id
INNER join bank on bank.b_id = deposite.b_id
WHERE name='郭海’AND bank_name=‘工商银行’;
5.查询日期为2011-04-05这一天进行过付款的客户id,客户姓名,银行名称,存款金额,按存款金额降序排列
SELECT d.c_id,c.name,b.bank_name,d.amount FROM bank b,customer c,deposite d
WHERE c.c_id=d.c_id AND b.b_id=d.d_id AND dep_date>=‘2011-04-05’ ORDER BY d.amount;
6.按银行统计存款总数,显示为b.id,bank_name,total;
SELECT b.b_id ,b.bank_name ,SUM(d.amount) total
FROM deposite d,bank b
WHERE d.b_id=b.b_id GROUP BY b.bank_name;
【进行分组查询时,需要多表查,需要用x.字段名的形式进行连接且使用 group by+分组的规则(按)】
7.对deposited,custmoer,bank进行查询,查询条件为location在广州,苏州,济南的客户,存款在300000至50000之间的存款记录,显示客户姓名name,银行名称bank,name,存款金额amount,将结果保存在视图中
SELECT c.NAME,b.bank_name,d.amount FROM deposite d,bank b ,customer c
WHERE c.c_id=d.c_id AND b.b_id= d.b_id
AND location not in (‘南京’) AND (amount between 30000 AND 50000);
8.查询在农业银行存款前2名的客户存款信息(显示:客户姓名,银行名称,存款金额)
SELECT name 客户姓名,bank_name 银行名称,amount 存款金额
FROM deposite d,customer c,bank b
WHERE b.b_id=d.b_id AND bank_name=‘农业银行’ AND d.c_id=c.c_id LIMIT 2;
9.更新customer表的salary属性,将salary低于5000的客户salary变为原来的2倍
update customer set salary =salary*2 WHERE (salary < ‘5000’)
10.创建存储过程,使用int类型传入参数10000,并查询工商银行大于等于一万的客户姓名,金额,存款期限(查询使用表链接和子查询两种方式实现)
//使用表连接查询方式创建存储过程
delimiter
C
R
E
A
T
E
P
R
O
C
E
D
U
R
E
A
A
(
i
n
N
U
M
E
R
I
C
I
N
T
)
B
E
G
I
N
S
E
L
E
C
T
N
A
M
E
,
a
m
o
u
n
t
,
d
e
p
t
y
p
e
F
R
O
M
b
a
n
k
b
,
d
e
p
o
s
i
t
e
d
W
H
E
R
E
b
.
b
a
n
k
n
a
m
e
=
′
工
商
银
行
′
A
N
D
d
.
a
m
o
u
n
t
>
=
n
u
m
A
N
D
d
.
d
i
d
=
b
.
b
i
d
E
N
D
CREATE PROCEDURE AA( in NUMERIC INT ) BEGIN SELECT NAME ,amount,dep_type FROM bank b,deposite d WHERE b.bank_name='工商银行' AND d.amount>=num AND d.d_id=b.b_id END
CREATEPROCEDUREAA(inNUMERICINT)BEGINSELECTNAME,amount,deptypeFROMbankb,depositedWHEREb.bankname=′工商银行′ANDd.amount>=numANDd.did=b.bidEND
delimiter
–实现存储过程
set @num=10000;
call anser07(@num);