MySQL练习

---

-- 创建公交管理系统数据库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; 

——————有时间我会增加一些题目，可能有多表的查询