赞
踩
#cmd中打开mysql
mysql -u root -p
1.创建数据库,名称为cdadb;(如果已有,则省略)
create database cdadb;
2.创建数据表customer(客户)、desposite(存款)、bank(银行),表结构如下:
customer的表结构:
属性名称 | 类型与长度 | 中文含义 | 备注 |
---|---|---|---|
c_id | char(6) | 客户标识 | 主键,非空 |
name | varchar(30) | 客户姓名 | 非空 |
location | Varchar(30) | 工作地点 | |
salary | decimal(8,2) | 工资 |
#创建表 mysql> create table customer( -> c_id char(6) primary key not null, -> name varchar(30) not null, -> location varchar(30), -> salary decimal(8,2)); #显示表结构 mysql> desc customer; +----------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+-------+ | c_id | char(6) | NO | PRI | NULL | | | name | varchar(30) | NO | | NULL | | | location | varchar(30) | YES | | NULL | | | salary | decimal(8,2) | YES | | NULL | | +----------+--------------+------+-----+---------+-------+
bank的表结构:
属性名称 | 类型与长度 | 中文含义 | 备注 |
---|---|---|---|
b_id | char(5) | 银行标识 | 主键,非空 |
bank_name | char(30) | 银行名次 | 非空 |
#创建表
mysql> create table bank(
-> b_id char(5) primary key not null,
-> bank_name char(30) not null
-> );
#显示表结构
mysql> desc bank;
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| b_id | char(5) | NO | PRI | NULL | |
| bank_name | char(30) | NO | | NULL | |
+-----------+----------+------+-----+---------+-------+
desposite的表结构:
属性名称 | 类型与长度 | 中文含义 | 备注 |
---|---|---|---|
d_id | int | 存款流水号 | 主键,非空,自增 |
c_id | char(6) | 客户标识 | 外键,关联customer表的c_id |
b_id | char(5) | 银行标识 | 外键,关联bank表的b_id |
dep _date | date | 存入日期 | |
dep_type | char(1) | 存款期限 | 1,3,5分别代表1年期、3年期和5年期 |
amount | decimal(8,2) | 存款金额 |
mysql> create table desposite( -> d_id int primary key not null auto_increment, -> c_id char(6), -> b_id char(5), -> dep_date date, -> dep_type char(1), -> amount decimal(8,2), -> constraint de_c_id foreign key(c_id) references customer(c_id), -> constraint de_b_id foreign key(b_id) references bank(b_id)); mysql> desc desposite; +----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+--------------+------+-----+---------+----------------+ | d_id | int(11) | NO | PRI | NULL | auto_increment | | c_id | char(6) | YES | MUL | NULL | | | b_id | char(5) | YES | MUL | NULL | | | dep_date | date | YES | | NULL | | | dep_type | char(1) | YES | | NULL | | | amount | decimal(8,2) | YES | | NULL | | +----------+--------------+------+-----+---------+----------------+
总结:
1、创建表
create table 表名(
字段名1 数据类型 约束,
字段名2 数据类型 约束,
...
字段名n 数据类型 约束
);
2、展示表结构
desc 表名;
3、外键约束
(constraint 外键约束名) foreign key(本表中的某个字段) references 与本表约束表的表名(与本表约束表的字段)
3.录入数据如下:customer的数据如下,注意最后一条记录用你的学号和你的姓名代替。
c_id | name | location | salary |
---|---|---|---|
101001 | 孙杨 | 广州 | 1234 |
101002 | 郭海 | 南京 | 3526 |
101003 | 卢江 | 苏州 | 6892 |
101004 | 郭惠 | 济南 | 3492 |
你的学号 | 你的姓名 | 北京 | 6324 |
mysql> insert into customer(c_id,name,location,salary)
-> values('101001','孙杨','广州',1234),
-> ('101002','郭海','南京',3526),
-> ('101004','郭慧','济南',3492),
-> ('你的学号','你的名字','北京',6324);
mysql> select * from customer;
+----------+----------+----------+---------+
| c_id | name | location | salary |
+----------+----------+----------+---------+
| 101001 | 孙杨 | 广州 | 1234.00 |
| 101002 | 郭海 | 南京 | 3526.00 |
| 101004 | 郭慧 | 济南 | 3492.00 |
| 你的学号 | 你的名字 | 北京 | 6324.00 |
+----------+----------+----------+---------+
bank的数据如下:
b_id | bank_name |
---|---|
B0001 | 工商银行 |
B0002 | 建设银行 |
B0003 | 中国银行 |
B0004 | 农业银行 |
mysql> insert into bank(b_id,bank_name) -> values ('B0001','工商银行'), -> ('B0002','建设银行'), -> ('B0003','中国银行'), -> ('B0004','建设银行'); mysql> select * from bank; +-------+-----------+ | b_id | bank_name | +-------+-----------+ | B0001 | 工商银行 | | B0002 | 建设银行 | | B0003 | 中国银行 | | B0004 | 建设银行 | +-------+-----------+
desposite的数据如下:
d_id | c_id | b_id | dep_date | dep_type | amount |
---|---|---|---|---|---|
1 | 101001 | B0001 | 2011-04-05 | 3 | 42526 |
2 | 101002 | B0003 | 2012-07-15 | 5 | 66500 |
3 | 101003 | B0002 | 2010-11-24 | 1 | 42366 |
4 | 101004 | B0004 | 2008-03-31 | 1 | 62362 |
5 | 101001 | B0003 | 2002-02-07 | 3 | 56346 |
6 | 101002 | B0001 | 2004-09-23 | 3 | 353626 |
7 | 101003 | B0004 | 2003-12-14 | 5 | 36236 |
8 | 101004 | B0002 | 2007-04-21 | 5 | 26267 |
9 | 101001 | B0002 | 2011-02-11 | 1 | 435456 |
10 | 101002 | B0004 | 2012-05-13 | 1 | 234626 |
11 | 101003 | B0003 | 2001-01-24 | 5 | 26243 |
12 | 101004 | B0001 | 2009-08-23 | 3 | 45671 |
insert into desposite(d_id,c_id,b_id,dep_date,dep_type,amount) -> values (1,'101001','B0001','2011-04-05','3',42526), -> (2,'101002','B0003','2012-07-15','5',66500), -> (3,'101003','B0002','2010-11-24','1',42366), -> (4,'101004','B0004','2008-03-31','1',62362), -> (5,'101001','B0003','2002-02-07','3',56346), -> (6,'101002','B0001','2004-09-23','3',353626), -> (7,'101003','B0004','2003-12-14','5',36236), -> (8,'101004','B0002','2007-04-21','5',26267), -> (9,'101001','B0002','2011-02-11','1',435456), -> (10,'101002','B0004','2012-05-13','1',234626), -> (11,'101003','B0003','2001-01-24','5',26243), -> (12,'101004','B0001','2009-08-23','3',45671); mysql> select * from desposite; +------+--------+-------+------------+----------+-----------+ | d_id | c_id | b_id | dep_date | dep_type | amount | +------+--------+-------+------------+----------+-----------+ | 1 | 101001 | B0001 | 2011-04-05 | 3 | 42526.00 | | 2 | 101002 | B0003 | 2012-07-15 | 5 | 66500.00 | | 3 | 101003 | B0002 | 2010-11-24 | 1 | 42366.00 | | 4 | 101004 | B0004 | 2008-03-31 | 1 | 62362.00 | | 5 | 101001 | B0003 | 2002-02-07 | 3 | 56346.00 | | 6 | 101002 | B0001 | 2004-09-23 | 3 | 353626.00 | | 7 | 101003 | B0004 | 2003-12-14 | 5 | 36236.00 | | 8 | 101004 | B0002 | 2007-04-21 | 5 | 26267.00 | | 9 | 101001 | B0002 | 2011-02-11 | 1 | 435456.00 | | 10 | 101002 | B0004 | 2012-05-13 | 1 | 234626.00 | | 11 | 101003 | B0003 | 2001-01-24 | 5 | 26243.00 | | 12 | 101004 | B0001 | 2009-08-23 | 3 | 45671.00 | +------+--------+-------+------------+----------+-----------+
总结:
1、插入多条记录
insert into person(字段名)
values (数据1),
(数据2),
...,
(数据n);
注:如果不指定字段名,那么数据需要和表结构里面字段名顺序一一对应
2、查询表中所有数据
select * from 表名;
4.更新customer表的salary属性,将salary低于5000的客户的salary变为原来的2倍.
分析:
本题是一道比较简单的更新数据的题目,故知道更新数据的语句语法即可完成该题。
更新数据的语句如下:
update 表名 set 列名 1 = 值 1(,列 2 = 值 2) (where 筛选列 = 筛选值)
题目要求salary变为原来的两倍,故set salary=salary*2;题目要求变成两倍的条件是salary低于5000 ,故where salary<5000
(1)更新数据的语句如下:
update 表名
set 列名 1 = 值 1(,列 2 = 值 2)
(where 筛选列 = 筛选值)
(2)由条件将语句替换
表明:customer
需更新的值的情况:salary变为原来的2倍 -> set salary=salary*2
筛选条件:salary低于5000 -> where salary<5000
MySQL语句如下:
mysql> select * from customer; +----------+----------+----------+---------+ | c_id | name | location | salary | +----------+----------+----------+---------+ | 101001 | 孙杨 | 广州 | 1234.00 | | 101002 | 郭海 | 南京 | 3526.00 | | 101004 | 郭慧 | 济南 | 3492.00 | | 你的学号 | 你的名字 | 北京 | 6324.00 | +----------+----------+----------+---------+ mysql>update customer set salary=salary*2 where salary<5000; mysql> select * from customer; +----------+----------+----------+---------+ | c_id | name | location | salary | +----------+----------+----------+---------+ | 101001 | 孙杨 | 广州 | 2468.00 | | 101002 | 郭海 | 南京 | 7052.00 | | 101004 | 郭慧 | 济南 | 6984.00 | | 你的学号 | 你的名字 | 北京 | 6324.00 | +----------+----------+----------+---------+
5.对desposite表进行统计,按银行统计存款总数,显示为b_id,total。
分析:
本题要求我们按银行统计存款总数,故需要对银行(b_id)进行分组;题目还要统计存款总数,故需要对每个银行的存款(amount)进行汇总(sum);且注意题目要求显示b_id,total。
MySQL语句如下:
mysql> select b_id,sum(amount) as total from desposite group by b_id;
+-------+-----------+
| b_id | total |
+-------+-----------+
| B0001 | 441823.00 |
| B0002 | 504089.00 |
| B0003 | 149089.00 |
| B0004 | 333224.00 |
+-------+-----------+
6.对desposite、customer、bank进行查询,查询条件为location在广州、苏州、济南的客户,存款在300000至500000之间的存款记录,显示客户姓名name、银行名称bank_name、存款金额amount.
本题主要考核的是多表联合,在此处我使用的是内连接。
题目中要求客户的location在广州、苏州、济南,可采用in来进行查询;
且题目中要求该客户的存款在300000至500000之间,可采用between and来进行查询。
由于这两个题目是要求同时满足,使用and连接
mysql> select c.name,b.bank_name,d.amount
-> from desposite d inner join customer c inner join bank b
-> on d.c_id=c.c_id and d.b_id=b.b_id
-> where c.location in ('广州','苏州','济南') and d.amount between 300000 and 500000;
+------+-----------+-----------+
| name | bank_name | amount |
+------+-----------+-----------+
| 孙杨 | 建设银行 | 435456.00 |
+------+-----------+-----------+
注:
在给desposite表插入数据时出现了一项错误:
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (d
.desposite
, CONSTRAINT de_c_id
FOREIGN KEY (c_id
) REFERENCES customer
(c_id
))
解决方法:
SET FOREIGN_KEY_CHECKS = 0;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。