mysql实训2
银行存款记录数据库模拟实训
1.创建数据库和表
创建customer表
create table customer( c_id varchar(10) primary key not null comment '客户标识', name varchar(30) not null comment '客户姓名', location varchar(30) comment '工作地点', salary decimal(8,2) comment '工资' );
创建bank表
create table bank( b_id char(5) primary key not null comment '银行标识', bank_name char(30) comment '非空' );
创建deposit表
create table deposti( d_id int primary key not null auto_increment comment '存款流水号', c_id varchar(10) comment '客户标识', foreign key(c_id) references customer(c_id), b_id char(5) comment '银行标识', foreign key(b_id) references bank(b_id), dep_date date comment '存入日期', amount decimal(8,2) comment '存款金额' );
2.插入数据
向customer表中插入数据:
insert into customer(c_id,name,location,salary) values('101001','孙杨','广州',1234); insert into customer(c_id,name,location,salary) values('101002','郭靖','南京',3526); insert into customer(c_id,name,location,salary) values('101003','卢江','苏州',6892); insert into customer(c_id,name,location,salary) values('101004','郭惠','济南',3492);
向bank表中插入数据：
insert into bank(b_id,bank_name) values('B0001','工商银行'); insert into bank(b_id,bank_name) values('B0002','建设银行'); insert into bank(b_id,bank_name) values('B0003','中国银行'); insert into bank(b_id,bank_name) values('B0004','农业银行');
向deposit表插入数据:
insert into deposit(d_id,c_id,b_id,dep_date,amount) values(1,'101001','B0001','2011-04-05',42546); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(2,'101002','B0003','2012-07-15',66500); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(3,'101003','B0002','2010-11-24',42366); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(4,'101004','B0004','2008-03-31',62362); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(5,'101001','B0003','2012-02-07',56346); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(6,'101002','B0001','2004-09-23',353626); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(7,'101003','B0004','2003-12-14',36236); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(8,'101004','B0002','2007-04-21',26267); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(9,'101001','B0002','2011-04-05',435456); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(10,'101002','B0004','2012-05-13',234626); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(11,'101003','B0003','2001-01-24',26243); insert into deposit(d_id,c_id,b_id,dep_date,amount) values(12,'101004','B0001','2009-08-23',45671);
3.查询数据
查询孙杨的存款信息
select c.c_id,name,bank_name,amount from customer as c,bank,deposit where c.c_id = deposit.c_id and bank.b_id = deposit.b_id and name = '孙杨'
查询日期为2011-04-05这一天进行过存款的客户ID、客户姓名、银行名称、存款金额。
select c.c_id,name,bank_name,amount from customer as c, bank as b, deposit as d where c.c_id = d.c_id and b.b_id = d.b_id and d.dep_date = '2011-04-05';
查询农业银行存款前五名的客户存款信息
select c.c_id,name,bank_name,dep_date,amount from customer as c, bank as b,deposit as d where c.c_id = d.c_id and b.b_id = d.b_id and b.bank_name = '农业银行' order by amount desc limit 5
商品进销系统模拟实训
1.创建数据库和表
创建数据库命令: create database sellandstore 创建以中文命名的表和字段，SQL语句如下：
CREATE TABLE `产品类别` (
`类别编号` varchar(255) NOT NULL,
`类别名称` varchar(255) DEFAULT NULL,
`说明` varchar(255) DEFAULT NULL,
PRIMARY KEY (`类别编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `产品资料` (
`产品编号` varchar(255) NOT NULL,
`产品` varchar(255) DEFAULT NULL,
`供应商编号` varchar(255) DEFAULT NULL,
`类别编号` varchar(255) DEFAULT NULL,
`单位数量` varchar(255) DEFAULT NULL,
`单价` varchar(255) DEFAULT NULL,
`库存量` varchar(255) DEFAULT NULL,
`已订购量` varchar(255) DEFAULT NULL,
`安全存量` varchar(255) DEFAULT NULL,
`不再销售` varchar(255) DEFAULT NULL,
PRIMARY KEY (`产品编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `订货明细` (
`订单号码` varchar(255) NOT NULL,
`产品编号` varchar(255) NOT NULL,
`单价` varchar(255) DEFAULT NULL,
`数量` varchar(255) DEFAULT NULL,
`折扣` varchar(255) DEFAULT NULL,
PRIMARY KEY (`订单号码`,`产品编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `订货主档` (
`订单号码` varchar(255) NOT NULL,
`客户编号` varchar(255) DEFAULT NULL,
`员工编号` varchar(255) DEFAULT NULL,
`订单日期` varchar(255) DEFAULT NULL,
`要货日期` varchar(255) DEFAULT NULL,
`送货日期` varchar(255) DEFAULT NULL,
`送货方式` varchar(255) DEFAULT NULL,
`运费` varchar(255) DEFAULT NULL,
`收货人` varchar(255) DEFAULT NULL,
`送货地址` varchar(255) DEFAULT NULL,
`送货城市` varchar(255) DEFAULT NULL,
`送货行政区` varchar(255) DEFAULT NULL,
`送货邮政编码` varchar(255) DEFAULT NULL,
`送货国家地区` varchar(255) DEFAULT NULL,
PRIMARY KEY (`订单号码`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `货运公司` (
`货运公司编号` varchar(255) NOT NULL,
`货运公司名称` varchar(255) DEFAULT NULL,
`电话` varchar(255) DEFAULT NULL,
PRIMARY KEY (`货运公司编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `客户` (
`客户编号` varchar(255) NOT NULL,
`公司名称` varchar(255) DEFAULT NULL,
`联系人` varchar(255) DEFAULT NULL,
`联系人职称` varchar(255) DEFAULT NULL,
`地址` varchar(255) DEFAULT NULL,
`城市` varchar(255) DEFAULT NULL,
`行政区` varchar(255) DEFAULT NULL,
`邮政编码` varchar(255) DEFAULT NULL,
`国家地区` varchar(255) DEFAULT NULL,
`电话` varchar(255) DEFAULT NULL,
`传真电话` varchar(255) DEFAULT NULL,
PRIMARY KEY (`客户编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `商品供应` (
`供应商编号` varchar(255) NOT NULL,
`供应商` varchar(255) DEFAULT NULL,
`联系人` varchar(255) DEFAULT NULL,
`联系人职务` varchar(255) DEFAULT NULL,
`地址` varchar(255) DEFAULT NULL,
`城市` varchar(255) DEFAULT NULL,
`行政区` varchar(255) DEFAULT NULL,
`邮政编码` varchar(255) DEFAULT NULL,
`国家地区` varchar(255) DEFAULT NULL,
`电话` varchar(255) DEFAULT NULL,
`传真电话` varchar(255) DEFAULT NULL,
`公司网站` varchar(255) DEFAULT NULL,
PRIMARY KEY (`供应商编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `员工` (
`员工编号` varchar(255) NOT NULL,
`身份证号码` varchar(255) DEFAULT NULL,
`姓名` varchar(255) DEFAULT NULL,
`性别` varchar(255) DEFAULT NULL,
`家庭住址` varchar(255) DEFAULT NULL,
`邮政编码` varchar(255) DEFAULT NULL,
`电话号码` varchar(255) DEFAULT NULL,
`出生日期` datetime DEFAULT NULL,
`婚姻状况` varchar(255) DEFAULT NULL,
`雇佣日期` varchar(255) DEFAULT NULL,
`起薪` varchar(255) DEFAULT NULL,
`目前薪资` varchar(255) DEFAULT NULL,
`加薪日期` varchar(255) DEFAULT NULL,
`工作代码` varchar(255) DEFAULT NULL,
`部门` varchar(255) DEFAULT NULL,
`直属主管` varchar(255) DEFAULT NULL,
PRIMARY KEY (`员工编号`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;2.插入数据
INSERT INTO `产品类别` VALUES ('1', '饮料', '软性饮料,咖啡,啤酒,及麦酒');
INSERT INTO `产品类别` VALUES ('2', '调味品', '甜酸酱,配料,涂料,及香料');
INSERT INTO `产品类别` VALUES ('3', '点心', '甜点心,糖果,甜面包');
INSERT INTO `产品类别` VALUES ('4', '日用品', '寿司');
INSERT INTO `产品类别` VALUES ('5', '谷类/麦片', '面包,饼干,面团,麦片');
INSERT INTO `产品类别` VALUES ('6', '肉/家禽', '肉品');
INSERT INTO `产品资料` VALUES ('28', '烤肉酱', '12', '7', '每箱12瓶', '45.6', '26', '0', '0', '-1');
INSERT INTO `产品资料` VALUES ('29', '鸭肉', '12', '6', '每袋3公斤', '123.79', '0', '0', '0', '-1');
INSERT INTO `产品资料` VALUES ('3', '蕃茄酱', '1', '2', '每箱12瓶', '10', '13', '70', '25', '0');
INSERT INTO `产品资料` VALUES ('30', '黄鱼', '13', '8', '每袋3公斤', '25.89', '10', '0', '15', '0');
INSERT INTO `产品资料` VALUES ('31', '温馨寿司', '14', '4', '每箱12瓶', '14.9386', '0', '70', '4', '0');
INSERT INTO `产品资料` VALUES ('32', '白寿司', '14', '4', '每箱12瓶', '32', '9', '40', '25', '0');
INSERT INTO `订货明细` VALUES ('10255', '2', '15.2', '20', '0');
INSERT INTO `订货明细` VALUES ('10255', '36', '15.2', '25', '0');
INSERT INTO `订货明细` VALUES ('10255', '59', '44', '30', '0');
INSERT INTO `订货明细` VALUES ('10256', '53', '26.2', '15', '0');
INSERT INTO `订货明细` VALUES ('10256', '77', '10.4', '12', '0');
INSERT INTO `订货明细` VALUES ('10257', '27', '35.1', '25', '0');
INSERT INTO `订货主档` VALUES ('10268', 'GROSR', '8', '1996-07-30 00:00:00', '1996-08-27 00:00:00', '1996-08-02 00:00:00', '3', '66.29', '刘先生', '沈阳市河东路二段120号', '沈阳市', '河东', '1', '');
INSERT INTO `订货主档` VALUES ('10269', 'WHITC', '5', '1996-07-31 00:00:00', '1996-08-14 00:00:00', '1996-08-09 00:00:00', '1', '4.56', '黎先生', '青岛永大路4号', '青岛', '永大', '2', '');
INSERT INTO `订货主档` VALUES ('10270', 'WARTH', '1', '1996-08-01 00:00:00', '1996-08-29 00:00:00', '1996-08-02 00:00:00', '1', '136.54', '成先生', '沈阳市中港路一段78号', '沈阳市', '中港', '2', '');
INSERT INTO `订货主档` VALUES ('10271', 'SPLIR', '6', '1996-08-01 00:00:00', '1996-08-29 00:00:00', '1996-08-30 00:00:00', '2', '4.54', '唐小姐', '济南市中山路7号', '济南市', '中山', '2', '');
INSERT INTO `订货主档` VALUES ('10272', 'RATTC', '6', '1996-08-02 00:00:00', '1996-08-30 00:00:00', '1996-08-06 00:00:00', '2', '98.03', '王先生', '上海市中新路11号', '上海市', '中新', '2', '');
INSERT INTO `订货主档` VALUES ('10273', 'QUICK', '3', '1996-08-05 00:00:00', '1996-09-02 00:00:00', '1996-08-12 00:00:00', '3', '76.07', '刘先生', '沈阳市河东路二段120号', '沈阳市', '河东', '5', '');
INSERT INTO `客户` VALUES ('FAMIA', '红阳事业', '徐先生', '业务', '哈尔滨市花中路15号', '哈尔滨市', '花中区', '328306', '', '(0252) 246-6665', '(0252) 246-6665');
INSERT INTO `客户` VALUES ('FISSA', '嘉元实业', '周先生', '营销专员', '合肥市大洋路38号', '合肥市', '大洋区', '327000', '', '(0452) 889-6638', '(0452) 889-6638');
INSERT INTO `客户` VALUES ('FOLIG', '路福村', '方先生', '董事长', '武汉市永平路7号', '武汉市', '永平区', '218023', '', '(010) 255-2555', '(010) 255-2555');
INSERT INTO `客户` VALUES ('FOLKO', '雅洲信托', '陈先生', '董事长', '北京市海淀东路64号', '北京市', '海淀区', '113008', '', '(010) 277-9682', '(010) 277-9682');
INSERT INTO `客户` VALUES ('FRANK', '棕国信托', '余小姐', '会计人员', '北京市海淀东路42号3楼之一', '北京市', '海淀区', '110058', '', '(021) 391-6932', '(021) 391-6932');
INSERT INTO `客户` VALUES ('FRANR', '信华银行', '苏先生', '业务', '武汉市竹北路8号', '武汉市', '竹北区', '114754', '', '(020) 937-7588', '(020) 937-7588');
INSERT INTO `商品供应` VALUES ('2', '光权', '黄小姐', '董事长', '沈阳市中港路一段9号', '沈阳市', '沈阳', '420117', '', '(024) 555-4822', '(024) 555-4822', '#CAJUN.HTM#');
INSERT INTO `商品供应` VALUES ('20', '一心', '刘先生', '业务', '沈阳市中港路一段28号', '沈阳市', '沈阳', '535128', '', '(0287) 568-5735', '(0287) 568-5735', '');
INSERT INTO `商品供应` VALUES ('21', '日日通', '方先生', '业务助理', '长春南投路599号', '长春', '长春', '218000', '', '(0234) 355-5991', '(0234) 355-5991', '');
INSERT INTO `商品供应` VALUES ('22', '顺成', '刘先生', '研发人员', '济南市永大路477号', '济南市', '青岛', '249999', '', '(0834) 559-7654', '(0834) 559-7654', '');
INSERT INTO `商品供应` VALUES ('23', '利利', '谢小姐', '董事长', '青岛市中山路57号', '青岛市', '青岛', '513120', '', '(0346) 481-7877', '(0346) 481-7877', '');
INSERT INTO `商品供应` VALUES ('9', '掬花', '谢小姐', '董事长', '沈阳市仁爱路四段59号', '沈阳市', '沈阳', '515555', '', '(025) 889-5522', '(025) 889-5522', '');
INSERT INTO `员工` VALUES ('1016', '362409156370981', '王惠康', '-1', '天津县凤山市滨山街4号7楼之1', '115497', '022 7401837', '1978-05-11 00:00:00', '-1', '1993-12-09 00:00:00', '4864', '4208', '1994-02-08 00:00:00', '', '信息部', '李华');
INSERT INTO `员工` VALUES ('1017', '362409156371113', '白明玉', '-1', '沈阳市文心路三段296号7楼A室', '538607', '025 3242377', '1971-03-29 00:00:00', '-1', '1993-12-14 00:00:00', '1494', '1570', '1994-03-28 00:00:00', '', '信息部', '李华');
INSERT INTO `员工` VALUES ('1018', '362409156371253', '吴秋明', '-1', '上海市永和市竹林路91巷55弄5号之1,5楼', '532720', '010 63640231', '1980-01-01 00:00:00', '-1', '1994-01-27 00:00:00', '2524', '2800', '1994-03-20 00:00:00', '', '信息部', '李华');
INSERT INTO `员工` VALUES ('1019', '362409156371383', '郑惠琪', '-1', '上海市泰山乡贵子路36巷5弄3号', '532829', '010 69053296', '1980-01-01 00:00:00', '-1', '1993-12-07 00:00:00', '1468', '1022', '1994-02-15 00:00:00', '', '信息部', '李华');
INSERT INTO `员工` VALUES ('102', '107854156382220', '游仲崇', '-1', '武汉市科学工业园区展业二路10号1楼', '535594', '0571 730333', '1967-09-06 00:00:00', '-1', '1994-01-30 00:00:00', '1350', '2381', '1994-03-09 00:00:00', '', '财务部', '王耀民');
INSERT INTO `员工` VALUES ('1020', '362409156371613', '杨伟仁(PC)', '-1', '青岛潮州镇中正路59号', '117942', '08 7824357', '1980-01-01 00:00:00', '-1', '1994-01-24 00:00:00', '1826', '1254', '1994-02-17 00:00:00', '', '信息部', '李华');3.查询数据
统计各个部门的最高工资、最低工资、平均工资、部门人数。
select 部门,max(目前薪资) as 最高工资,min(目前薪资) as 最低工资, avg(目前薪资) as 平均工资,count(*) as 部门人数 from 员工 group by 部门
查询员工表中各部门年龄最大的员工
select a.* from 员工 as a, (select 部门,min(出生日期) as 部门最小出生日期 from 员工 group by 部门) as b where a.部门 = b.部门 and a.
出生日期= b.部门最小出生日期;
查询有哪些员工姓名是重复的并显示重复姓名的人数
select 姓名,count(*)
from 员工
group by 姓名
having count(*) >= 2;查询比平均单价高的产品的名称和单价
select 产品,单价 from 产品资料 where 单价 > ( select avg(单价) from 产品资料 );
查询哪些公司没有采购（客户，订货主档）
select * from 客户 where 客户编号 not in (select 客户编号 from 订货主档)
查询销售数量最多的产品信息（产品资料，订货明细）
select * from 产品资料 where 产品编号= ( select 产品编号 from 订货明细 group by 产品编号 order by count(*) desc limit 1 );
查询销售金额最多的产品信息
select * from 产品资料 where 产品编号 = ( select 产品编号 from 订货明细 group by 产品编号 order by sum(单价数量(1-折扣)) desc limit 1 );
查询1996年7月份客户采购的信息
select o.订单号码,o.客户编号,o.订单日期,s.单价,s.数量,s.折扣,c.公司名称,c.联系人 from 订货主档 as o,订货明细 as s, 客户 as c where o.客户编号 = c.客户编号 and o.订单号码 = s.订单号码 and o.订单日期 between '1996-07-01 00:00:00' and '1996-07-31 23:59:59';
查询采购金额最多的前三位公司详细信息
select o.订单号码,o.订单日期,s.单价,s.数量,s.折扣,c.公司名称,c.联系人 from 订货主档 as o,订货明细 as s,客户 as c where o.客户编号 = c.客户编号 and o.订单号码 = s.订单号码 and o.订单号码 in ( select 订单号码 from 订货明细 group by 订单号码 order by sum(单价数量(1-折扣)) desc limit 3 ) );
根据客户的采购数量多少从高到低排序对客户排序
select o.客户编号,count(o.订单号码) from 订货主档 as o,订货明细 as s,客户 as c where o.客户编号=c.客户编号 and o.订单号码=s.订单号码 group by o.客户编号 order by count(o.订单号码) desc;
统计各种产品类别的数量，按照产品类别数量从低到高显示
select t.类别编号,count(*)
from 产品类别 as t,产品资料 as c
where t.类别编号=c.类别编号
group by t.类别编号 order by count(*);