mysql的题目_mysql经典例题

MYSQL上机题

1． 使用sql语句创建数据库，名称为CustomDB

2． 创建数据表customer(客户)、deposite(存款)、bank(银行)，表结构如下：(10分)

customer的表结构

属性名称

类型与长度

中文含义

备注

c_id

char(6)

客户标识

主键，非空

name

varchar(30)

客户姓名

非空

location

Varchar(30)

工作地点

salary

decimal(8,2)

工资

bank的表结构

属性名称

类型与长度

中文含义

备注

b_id

char(5)

银行标识

主键，非空

bank_name

char(30)

银行名次

非空

deposite的表结构

属性名称

类型与长度

中文含义

备注

d_id

int

存款流水号

主键，非空，自增

c_id

char(6)

客户标识

外键，关联customer表的c_id

b_id

char(5)

银行标识

外键，关联bank表的b_id

dep _date

date

存入日期

amount

decimal(8,2)

存款金额

3、录入数据：

customer的数据如下

c_id

name

location

salary

101001

孙杨

广州

1234

101002

郭海

南京

3526

101003

卢江

苏州

6892

101004

郭惠

济南

3492

bank的数据如下：

b_id

bank_name

B0001

工商银行

B0002

建设银行

B0003

中国银行

B0004

农业银行

deposite的数据如下：

d_id

c_id

b_id

dep_date

amount

1

101001

B0001

2011-04-05

42526

2

101002

B0003

2012-07-15

66500

3

101003

B0002

2010-11-24

42366

4

101004

B0004

2008-03-31

62362

5

101001

B0003

2002-02-07

56346

6

101002

B0001

2004-09-23

353626

7

101003

B0004

2003-12-14

36236

8

101004

B0002

2007-04-21

26267

9

101001

B0002

2011-02-11

435456

10

101002

B0004

2012-05-13

234626

11

101003

B0003

2001-01-24

26243

12

101004

B0001

2009-08-23

45671

4、修改数据(sql语句实现)：

1.将数据表deposite中孙杨的存款金额加10000

2.将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加1000

3.将数据表deposite中郭海的银行标识改为建设银行

5、查询数据(sql语句实现)：

1.查询孙杨的存款信息(显示信息：客户ID，客户姓名，银行名称，存款金额)

2.查询日期为2011-04-05这一天进行过存款的客户ID，客户姓名，银行名称，存款金额

3.查询郭海在工商银行的存款信息(显示信息：客户ID，客户姓名，银行标识，银行名称，存款日期，存款金额)

4.查询工商银行存款大于等于一万的客户姓名(使用表链接和子查询两种方式实现)

5.查询在农业银行存款前五名的客户存款信息(显示信息：客户姓名，银行名称，存款金额)

6.查询姓“卢”的客户的存款信息(显示信息：客户姓名，银行名称，存款金额)

详细答案如下：

/*创建顾客表*/

create table if not exists customer(

c_id char(6) primary key,

name varchar(30)not null,

location varchar(30),

salary decimal(8,2)

);

/*客户表插入数据*/

insert into customer

values(‘101001‘,‘孙杨‘,‘广州‘,1234),

(‘101002‘,‘郭海‘,‘南京‘,3526),

(‘101003‘,‘卢江‘,‘苏州‘,6892),

(‘101004‘,‘郭惠‘,‘济南‘,3492);

/*创建银行表*/

create table if not exists bank(

b_id char(5) primary key,

bank_name char(30) not null

);

/*bank表插入数据*/

insert into bank

values(‘B0001‘,‘工商银行‘),

(‘B0002‘,‘建设银行‘),

(‘B0003‘,‘中国银行‘);

insert into bank

values(‘B0004‘,‘农业银行‘);

/*创建存款表(注意外键的代码使用)*/

create table if not exists deposite(

d_id int(10) auto_increment primary key,

c_id char(6),

b_id char(5),

dep_date date,

amount decimal(8,2),

constraint FK_c_id foreign key(c_id) references customer(c_id)

);

/*deposite表插入数据*/

insert into deposite

values(1,‘101001‘,‘B0001‘,‘2011-04-05‘,42526),

(2,‘101002‘,‘B0003‘,‘2012-07-15‘,66500),

(3,‘101003‘,‘B0002‘,‘2010-11-24‘,42366),

(4,‘101004‘,‘B0004‘,‘2008-03-31‘,62362),

(5,‘101001‘,‘B0003‘,‘2002-02-07‘,56346),

(6,‘101002‘,‘B0001‘,‘2004-09-23‘,353626),

(7,‘101003‘,‘B0004‘,‘2003-12-14‘,36236),

(8,‘101004‘,‘B0002‘,‘2007-04-21‘,26267),

(9,‘101001‘,‘B0002‘,‘2011-02-11‘,435456),

(10,‘101002‘,‘B0004‘,‘2012-05-13‘,234626),

(11,‘101003‘,‘B0003‘,‘2001-01-24‘,26243),

(12,‘101004‘,‘B0001‘,‘2009-08-23‘,45671);

四、修改数据(sql语句实现)：

1.将数据表deposite中孙杨的存款金额加10000

update  deposite set amount =amount+10000

where c_id in(select c_id from customer where name=‘孙杨‘);

2.将数据表deposite中所属账户为工商银行并且存入日期为2011-04-05的人员的存款金额加1000

update deposite set amount=amount+1000

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=‘郭海‘);

五、查询数据(sql语句实现)：

1.查询孙杨的存款信息(显示信息：客户ID，客户姓名，银行名称，存款金额)

方法(1):内连接查询

SELECT customer.c_id,customer.name,bank.bank_name,deposite.amount

FROM deposite

INNER JOIN  customer on customer.c_id= deposite.c_id

INNER JOIN  bank on bank.b_id=deposite.b_id

and customer.name=‘孙杨‘

方法(2):表连接查询：

SELECT customer.name,customer.c_id,bank.bank_name,deposite.amount

from customer,bank,deposite

WHERE  customer.c_id= deposite.c_id

and bank.b_id= deposite.b_id and customer.name=‘孙杨‘;

2.查询日期为2011-04-05这一天进行过存款的客户ID，客户姓名，银行名称，存款金额

方法(1):内连接查询：

SELECT customer.c_id,customer.name,bank.bank_name,deposite.amount

FROM deposite

INNER JOIN  customer on customer.c_id= deposite.c_id

INNER JOIN  bank on bank.b_id=deposite.b_id

and deposite.dep_date=‘2011-04-05‘;

方法(2):表连接查询：

SELECT customer.name,customer.c_id,bank.bank_name,deposite.amount

from deposite,customer,bank,

WHERE deposite.dep_date=‘2011-04-05‘

and customer.c_id= deposite.c_id

and bank.b_id= deposite.b_id ;

3.查询郭海在建设银行的存款信息(显示信息：客户ID，客户姓名，银行标识，银行名称，存款日期，存款金额)

方法(1)：内连接查询：

SELECT customer.c_id,customer.name,bank.b_id ,bank.bank_name,

deposite.amount,deposite.dep_date

FROM deposite

INNER JOIN  customer on customer.c_id= deposite.c_id

INNER JOIN  bank on bank.b_id=deposite.b_id

and customer.name=‘郭海‘ and bank.bank_name=‘建设银行‘;

方法(2):表连接查询：

SELECT customer.c_id,customer.name,bank.b_id ,bank.bank_name,

deposite.amount,deposite.dep_date

FROM deposite,bank,customer

WHERE  customer.c_id= deposite.c_id

and bank.b_id=deposite.b_id

and customer.name=‘郭海‘ and bank.bank_name=‘建设银行‘;

4.查询工商银行存款大于等于一万的客户姓名(使用表链接和子查询两种方式实现)

方法(1)：子查询：

SELECT customer.name FROM customer WHERE  c_id in (

SELECT c_id from deposite WHERE amount in

(SELECT amount  from deposite WHERE  amount>1000 and b_id IN

(SELECT b_id from bank where bank_name=‘工商银行‘)));

方法(2)：表连接查询：

SELECT customer.name

FROM deposite,bank,customer

WHERE  customer.c_id= deposite.c_id

AND bank.b_id=deposite.b_id

WHERE amount>10000 AND bank_name=‘工商银行‘;

方法(3)：内连接查询：

SELECT customer.name

FROM deposite

INNER JOIN  customer on customer.c_id= deposite.c_id

INNER JOIN  bank on bank.b_id=deposite.b_id

WHERE amount>10000 AND bank_name=‘工商银行‘;

5.查询在农业银行存款前五名的客户存款信息(显示信息：客户姓名，银行名称，存款金额)a

方法(1)：内连接查询：

SELECT customer.name,bank.bank_name,deposite.amount

FROM deposite

INNER JOIN  customer on customer.c_id= deposite.c_id

INNER JOIN  bank on bank.b_id=deposite.b_id

WHERE bank_name=‘农业银行‘ ORDER BY amount DESC LIMIT 5

方法(2)：表连接查询：

SELECT customer.name,bank.bank_name,deposite.amount

FROM deposite,bank,customer

WHERE  customer.c_id= deposite.c_id

AND bank.b_id=deposite.b_id

and bank_name=‘农业银行‘ ORDER BY amount DESC LIMIT 5;

6.查询姓“卢”的客户的存款信息(显示信息：客户姓名，银行名称，存款金额)

方法(1)：内连接查询：

SELECT customer.name,bank.bank_name,deposite.amount

FROM deposite

INNER JOIN  customer on customer.c_id= deposite.c_id

INNER JOIN  bank on bank.b_id=deposite.b_id  where name LIKE ‘卢%‘;

方法(2)：表连接查询：

SELECT customer.name,bank.bank_name,deposite.amount

FROM deposite,bank,customer

WHERE customer.c_id= deposite.c_id

AND bank.b_id=deposite.b_id  AND name LIKE ‘卢%‘;