-- 创建公交管理系统数据库db_bus
-- 包含t_driver司机表、t_bus公交表、t_attendance发车表
数据准备:
t_driver司机表:
-- t_driver包含字段did(司机编号),dname(司机姓名),sex(性别),age(年龄),salary(薪资)
create database db_bus;
use db_bus;
CREATE TABLE `t_driver` (
`did` int(10) NOT NULL,
`dname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`sex` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(3) NULL DEFAULT NULL,
`salary` decimal(10, 2) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `t_driver` VALUES (10001, '赵芳', '女', 37, 8000.00);
INSERT INTO `t_driver` VALUES (10002, '刘龙', '男', 45, 8300.00);
INSERT INTO `t_driver` VALUES (10003, '刘凤', '女', 45, 8300.00);
INSERT INTO `t_driver` VALUES (10004, '王树', '男', 52, 7900.00);
INSERT INTO `t_driver` VALUES (10005, '陆山', '男', 39, 7500.00);
INSERT INTO `t_driver` VALUES (10006, '吴莉', '女', 35, 7000.00);
INSERT INTO `t_driver` VALUES (10007, '张云', '女', 54, 8000.00);
INSERT INTO `t_driver` VALUES (10008, '方星', '男', 49, 8600.00);
INSERT INTO `t_driver` VALUES (10009, '何国', '男', 32, 6700.00);
INSERT INTO `t_driver` VALUES (10010, '吴天', '男', 33, 6500.00);
t_bus公交表:
-- t_bus包含字段 bid(公交编号),platenum(车牌号),linenum(线路号码)
CREATE TABLE `t_bus` (
`bid` int(10) NOT NULL AUTO_INCREMENT,
`platenum` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`linenum` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`bid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `t_bus` VALUES (10001, '汽A00001', '518');
INSERT INTO `t_bus` VALUES (10002, '汽A00002', '659');
INSERT INTO `t_bus` VALUES (10003, '汽A00003', '485');
INSERT INTO `t_bus` VALUES (10004, '汽A00004', '518');
INSERT INTO `t_bus` VALUES (10005, '汽A00005', '485');
INSERT INTO `t_bus` VALUES (10006, '汽A00006', '945');
INSERT INTO `t_bus` VALUES (10007, '汽A00007', '568');
INSERT INTO `t_bus` VALUES (10008, '汽A00008', '912');
INSERT INTO `t_bus` VALUES (10009, '汽A00009', '912');
INSERT INTO `t_bus` VALUES (10010, '汽A00010', '776');
t_attendance发车表:
-- t_attendance包含字段aid(发车编号),bid(公交编号),did(司机编号),starttime(发车时间),endtime(收班时间)
CREATE TABLE `t_attendance` (
`aid` int(10) NOT NULL AUTO_INCREMENT,
`bid` int(10) NOT NULL,
`did` int(10) NOT NULL,
`starttime` datetime NULL DEFAULT NULL,
`endtime` datetime NULL DEFAULT NULL,
PRIMARY KEY (`aid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10011 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = DYNAMIC;
INSERT INTO `t_attendance` VALUES (10001, 10001, 10002, '2023-11-08 08:00:33', '2023-11-08 10:23:48');
INSERT INTO `t_attendance` VALUES (10002, 10003, 10005, '2023-11-08 13:00:33', '2023-11-08 15:23:48');
INSERT INTO `t_attendance` VALUES (10003, 10004, 10001, '2023-11-08 09:00:33', '2023-11-08 11:23:48');
INSERT INTO `t_attendance` VALUES (10004, 10006, 10004, '2023-11-08 07:00:33', '2023-11-08 09:23:48');
INSERT INTO `t_attendance` VALUES (10005, 10001, 10009, '2023-11-08 15:00:33', '2023-11-08 17:23:48');
INSERT INTO `t_attendance` VALUES (10006, 10003, 10009, '2023-11-09 08:00:33', '2023-11-09 10:23:48');
INSERT INTO `t_attendance` VALUES (10007, 10008, 10009, '2023-11-10 21:00:33', '2023-11-10 22:23:48');
INSERT INTO `t_attendance` VALUES (10008, 10002, 10005, '2023-11-08 10:00:33', '2023-11-08 12:23:48');
INSERT INTO `t_attendance` VALUES (10009, 10007, 10007, '2023-11-08 17:00:33', '2023-11-08 19:23:48');
INSERT INTO `t_attendance` VALUES (10010, 10009, 10002, '2023-11-08 18:00:33', '2023-11-08 20:00:33');
题目:
1.修改t_driver表中did字段设置为主键且自动增长。
ALTER TABLE t_driver MODIFY did INT PRIMARY KEY AUTO_INCREMENT;
2.添加一个新公交信息,公交车牌号为'汽A56875',线路号码为'506'。
INSERT INTO t_bus (platenum, linenum) VALUES ('汽A56875', '506');
3.用sql语句查询年龄在40岁以下的女性司机。
SELECT * FROM t_driver WHERE sex = '女' AND age < 40;
4.用sql语句查询编号在10001到10006之间的司机,工资按高到低显示。
SELECT * FROM t_driver WHERE did between 10001 AND 10006 order by salary desc;
5.用sql语句查询薪资在前3名的司机信息。
SELECT * FROM t_driver order by salary desc limit 3;
6.用sql语句查询男、女司机的平均工资,并且按平均工资的降序排列。
SELECT sex,avg(salary)as 平均工资 FROM t_driver group by sex order by 平均工资 desc;
7.用sql语句查询男、女司机的平均年龄,并且按平均年龄的降序排列。
SELECT sex,avg(age)as 平均年龄 FROM t_driver group by sex order by 平均年龄 desc;
8.用sql语句查询吴姓司机的信息。
SELECT * FROM t_driver where dname like '吴_';
9.用sql语句查询男、女司机的工资收入,对其收入按如下规则进行替换,若工资收入小于等于7000元,则替换为’较低工资‘;若工资收入大于7000元且小于8000元,则替换为’中等工资‘;
若工资收入大于8000元,则替换为’较高工资‘,并将工资收入字段更名为’工资等级‘。
select dname,age,
case
when salary<=7000 then '较低工资'
when salary>7000 and salary>8000 then '中等工资'
else '较高工资'
end as ’工资等级‘
from t_driver;
10.修改t_driver表中司机薪资增加200。
UPDATE t_driver SET salary = salary + 200;
11.用sql语句查询t_bus表中所有的线路编号,要求去除重复。
SELECT DISTINCT linenum FROM t_bus;
12.用sql语句查询每个司机的发车次数,要求显示司机姓名,发车次数。
SELECT dname '司机姓名', COUNT(*) as '发车次数'
FROM t_driver,t_attendance
WHERE t_driver.did=t_attendance.did GROUP BY dname;
——————有时间我会增加一些题目,可能有多表的查询