mysql基础（6）查询练习

 mysql语句练习（基本）（这些已经就够用了，想多接触的就看看复杂的）

create database jiangsu;   #新建一个名为jiangsu的数据库
use jiangsu;   #使用该数据库
create table location(Region char(20),Store_name char(20));   #创建location表，字段1为Region，数据类型为char，数据长度为20；字段2为Store_name，数据类型为char，长度为20.
insert into location values('North','Xuzhou');   #插入4条数据
insert into location values('North','Suqian'); 
insert into location values('South','Nanjing'); 
insert into location values('South','Suzhou'); 
create table store_info(Store_name char(20),Sales int(10),Date char(10));   #再创建一张store_info表
insert into store_info values('Xuzhou',300,'2020-12-08');   #插入4条数据
insert into store_info values('Suqian',249,'2020-12-07');
insert into store_info values('Nanjing',1500,'2020-12-05');
insert into store_info values('Suzhou',700,'2020-12-08');

1、基本查询

----------------select-------------------
select用于查询表格中的一个或多个字段的数据记录
语法格式：select 字段1，字段2，... from 表名;
select * from location;   #查询location表中的所有字段，*表示所有，如果不嫌麻烦，当然也可以将所有字段都输进去
select Region from location;   #查询location表中的Region的数据
select sales,Date from store_info;   #查询store_info表中的sales,Date字段的数据  

----------------DISTINCT-------------------
DISTINCT用于将重复的数据压缩为一个
语法格式：select distinct 字段名 from 表名；
select distinct Store_name from store_info;   #查询dtore_info表中的Store_name字段的数据，如有重复，则只显示一个

----------------where-------------------
where用于带条件查询
语法格式：select 字段名 from 表名 where 条件语句;
select Store_name from store_info where sales=300;   #查询store_info表中sales字段的值等于300的Store_name的数据
select Store_name from store_info where sales>500;   #查询store_info表中sales字段的值大于500的Store_name的数据

----------------and   or-------------------
and，且，用于查询一个数据范围；or，或，用于查询包含条件语句的所有数据
语法格式：select 字段名 from 表名 where 条件1 and/or 条件2;
select Store_name from store_info where sales>250 and sales<1000;   #查询store_info表中sales字段的值大于250，且小于1000的Store_name的数据
select Store_name from store_info where sales<250 or sales>1000;   #查询store_info表中sales字段的值小于250，或者 大于1000的Store_name的数据
select Store_name from store_info where sales>1000 or (sales >200 and sales < 500);   #括号的优先级高，所以先根据括号里的and条件语句进行筛选，然后再根据or进行筛选，最后查询最终筛选出来的数据；该语句先筛选出sales大于200且小于500的值，再使用or进行或的删选，最终筛选出来的结果应该是在200到500之间的值或者大于1000的值。
select Store_name from store_info where sales>1000 or sales >200 and sales < 500;   #如果不加括号，and的优先级是比or要高的，也就是说，当一条条件语句中同时存在and和or（没有括号），会先执行and条件。

----------------in-------------------
in用来显示已知值的数据，简单来说，in后面跟的是一个数据集合，查询语句会根据数据集合中的值进行筛选查询。not in 就是取数据集合中的反，不在数据集合中的数据。
语法格式：select 字段名1 from 表名 where 字段名2 in ('字段名2的值1','字段名2的值2，......');
select * from store_info where Store_name in ('Nanjing','Xuzhou');   #将Nanjing和Xuzhou的所有信息都查询出来。
注：in可以用or代替
上述语句等于：select * from store_info where Store_name='Nanjing' or Store_name='Xuzhou';

----------------between...and-------------------
between 值1 and 值2 ,在值1与值2之间(值2 > 值1)，该语句查询的是一个范围，包含值1和值2。其作用相在某一方面当于大于等于 ... and 小于等于 ... 。
语法格式：select 字段名 from 表名 where 字段名 between 值1 and 值2;
select * from store_info where Date between '2020-12-07' and '2020-12-10';   #查询store_info表中的Data的值在12-06与12-10之间的所有数据

----------------通配符-------------------
通配符一般情况下和like一起使用进行模糊查询，模糊查询的概念就是将所有符合条件的数据全部查询出来，而等于号是精确查询，会直接将具体的某一数据查询出来
模糊查询的字符如下：
%：百分号表示0个，1个或多个字符
_:下划线表示单个字符
语法格式：select 字段 from 表名 where 字段 like '通配符';
select * from store_info where Date like '2020%';   #将Date的值为2020，后面随便（2020后有没有都行）的值全部查询出来
select * from store_info where Date like '2020-12-0_';   #将2020-12-0，后面只能匹配一个字符（必须存在且只能有一个）的所有数据查询出来

----------------like-------------------
like，模糊查询，用于查询符合条件的所有数据，通常和通配符一起使用，语法和通配符一样的，因为是结合使用。
create database name;
use name;
create table stu_name(sname char(10));
insert into stu_name values('张');
insert into stu_name values('张三');
insert into stu_name values('张四');
insert into stu_name values('张无忌');
insert into stu_name values('一张纸');
insert into stu_name values('弓长张');
select * from stu_name where sname like '张%';   #查询所有张姓的名字，只要姓张就行
select * from stu_name where sname like '%张';   #查询所有最后一个字是张的姓名，前面无所谓
select * from stu_name where sname like '%张%';   #查询所有包含张的姓名，张字在姓在名都行
select * from stu_name where sname like '张_';   #查询所有张姓且只有两个字的名字
select * from stu_name where sname like '张__';（两条下划线）  #查询所有张姓，且必须为三个字的名字
select * from stu_name where sname like '_张%';  #查询所有第二个字为张的名字
select * from stu_name where sname like '张_%';   #查询所有张姓，名字至少包含两个字的名字
select * from stu_name where sname like '张%_';   #查询所有张姓，名字至少包含两个字的名字，该语句和上面的查询结果一样，但理解是不同的。

----------------order by-------------------
order by 用于关键字的排序
语法格式：select 字段 from 表名 [where 条件语句] order by 字段 asc/desc;
asc:按字段升序，默认为asc
desc：按字段降序
select Store_name,Date,sales from store_info order by sales;   #按照sales升序排列后，查询name、date和sales
select Store_name,Date,sales from store_info order by sales desc;   #按照sales降序排列后，查询name、date和sales

2、函数查询

-------------数学函数-------------------
abs(x)         #返回x的绝对值
rand()         #返回0到1之间的随机数
mod(x,y)       #返回x除以y的余数
power(x,y)     #返回x的y次方
round(x)       #返回离x最近的整数
round(x,y)     #保留x的y位小数四舍五入之后的值
sqrt(x)        #返回x的平方根
truncate(x,y)  #返回x截断为y位小数的值
ceil(x)        #返回大于或等于x的最小整数
floor(x)       #返回小于或等于x的最大整数
greatest(x,y,z,...)   #返回集合中最大的值
least(x,y,z,...)      #返回集合中最小的值
---------------------------------------------
select abs(-1),rand(),mod(5,2),power(2,3),round(1.75);
select round(3.1415926,5),sqrt(2),truncate(3.141592653,4),ceil(5.2),floor(3.2),greatest(1.61,2.54,0.87),least(5.23,8.71,4.13);







--------------聚合函数--------------------
avg()       #返回指定列的平均值
count()     #返回指定列中非空值的个数
min()       #返回指定列的最小值
max()       #返回指定列的最大值
sum(x)      #返回指定列的所有值的和  
------------------------------------------
select avg(sales) from store_info;   #查询sales的平均值
平均值的另一种方法：
select sum(sales)/(select count(sales) from store_info) from store_info;

select count(Date) from store_info;   #统计Date的数据个数，包括重复的值，但不包括空值

select count(distinct Date) from store_info;   #统计Date的数据个数,重复的数据只统计一次，不包括空值

select count(*) from store_info;   #全部统计，包括空值，count(*)扫描全表

select min(sales) from store_info;   #查询sales的最小值
最小值的另一种方法：
select sales from store_info order by sales limit 1;

select max(sales) from store_info;   #查询sales的最大值
最大值的另一种方法：
select sales from store_info order by sales desc limit 1;

select sum(sales) from store_info;   #查询sales的和








-----------------字符串函数--------------------
trim()             #返回去除指定格式的值
concat(x,y)        #将提供的参数x和y拼接成一个字符串
substr(x,y)        #获取字符串x中第y个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z)      #获取字符串x中第y个位置开始，长度为z的字符串
length(x)          #返回字符串x的长度
replace(x,y,z)     #将字符串z替代字符串x中的字符串y
upper(x)           #将字符串x中的所有字母变成大写
lower(x)           #将字符串x中的所有字母变成小写
left(x,y)          #返回字符串x中的前y个字符
right(x,y)         #返回字符串x中的后y个字符
repeat(x,y)        #将字符串x重复y次
space(x)           #返回x个空格
strcmp(x,y)        #比较x和y，返回的值可以为-1，0，1
reverse(x)         #将字符串x反转

select concat(Region,Store_name) from location where Store_name='Xuzhou';   #将location表中Store_name='Xuzhou'的Region,Store_name的值拼接在一起

select Region || ' ' || Store_name from location where Store_name='Xuzhou';   #在my.cnf中开启了PIPES_AS_CONCAT模式后，可以使用 || 代替concat函数，将多个字符串拼接在一起

select substr(Store_name,3) from store_info where Store_name='Suqian';   #将Suqian的第3个字符往后的所有字符截取出来

select substr((select Region || ' ' || Store_name from location where Store_name='Xuzhou'),1,5);   #将上一条拼接的字段的第1到5个字符截取出来

select trim([[位置] [要除移的字符串] from] 字符串);
#位置：leading（开头），tariling（结尾），both（开头及结尾）
#要除移的字符串：从字符串的开头、结尾，或开头及结尾除移的字符串，缺省时为空格
select trim(leading 'Xu' from 'Xuzhou');   #将Xuzhou开头的Xu去掉

select Region,length(Store_name) from location;   #查询location表中的Region字段的值和Store_name的值的长度

select replace(Region,'th','thern') from location;   #将location表中的Region字段的值包含th的替换为thern，然后返回。

------------------group by-------------------
group by用于对查询结果进行汇总分组，通常是结合聚合函数
一起使用，group by有一个原则，凡是在group by后面出现的
字段，必须在select后面出现。凡是在select后面出现、且未在
group by后面出现的字段，必须出现在group by后面。
语法格式：select 字段1，sum(字段2) from 表名 group by 字段1;

select Store_name,sum(sales) from store_info group by Store_name order by sum(sales);

------------------having----------------------
having用来过滤由group by语句返回的记录值，通常与group by语句联合使用。having语句的存在弥补了where关键字不能与聚合函数联合使用的不足。
语法格式：select 字段1,sum(字段2) from 表名 group by 字段1 having (函数条件)；
select Store_name,sum(sales) from  store_info group by Store_name having sum(sales) >1000;

-------------------别名----------------------
别名包括字段别名和表的别名，当一张表的名字或者表中的某一个字段名过于冗长时，可以使用别名将之代替，从而降低查询的失误率。
语法格式：select 字段 [AS] 字段别名 from 表名 [AS] 表格别名；
select Store_name NAME,sales SALE from store_info;

------------------子查询---------------------
子查询通常用于连接表格，在where子句或者having子句中插入另一个SQL语句。
语法格式：select 字段1 from 表格1 where 字段2 [比较运算符] (select 字段2 from 表格2 where 条件语句);括号里的select语句是内查询，括号外的select语句是外查询
select Region from location where Store_name=(select Store_name from store_info where sales=300);   #比较运算符，可以是=、>、<或者>=、<=

select sales from store_info where Store_name in (select Store_name from  location);   #也可以是in、between...and、like等

3、exists

 ------------------exists---------------------
 exists用来测试内查询有没有产生任何结果，类似布尔值
 是否为真。如果内查询产生了结果，则将结果作为外查询
 的条件继续执行，如果没有结果，那么整条语句都不会产
 生结果。
 语法格式：select 字段1 from 表1 where exists (select 字段2 from 表2 where 条件语句);
 select sum(sales) from store_info where exists (select * from location where Region='North');   #
 #先执行内查询语句，即在location表中查询Region为North的所有数据，如果存在，则执行外查询语句；如果不存在，就不执行外查询
------------------连接查询-----------------------
inner join(内连接)：内连接只返回两个表中字段的值相等的数据，即两表的交集
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录
select * from location left join store_info on location.Store_name=store_info.Store_name;  #左连接

select * from location right join store_info on location.Store_name=store_info.Store_name;   #右连接

select * from location inner join store_info on location.Store_name=store_info.Store_name;   #内连接法1

SELECT * FROM location A, store_info B WHERE A.Store_Name = B.Store_Name;   #内连接法2

SELECT Region REGION, SUM(B.Sales) SALES FROM location A, store_info B WHERE A.Store_Name = B.Store_Name GROUP BY Region;   #查询两表中name值相等的Region的值和sales的和，并按照Region字段进行分组，REGION是字段Region的别名，SALES是sum(sales)的别名，A是表location的别名，B是info表的别名

4、视图

---------------------view-----------------------
view，视图，视图是一张虚拟的表，通常用于保存多表联合查询出来数据，这样可以极大的减轻SQL语句的复杂度，在执行n张表的联合查询时，视图可以起到很大的便利作用。视图跟表格的不同是，表格中有实际储存数据记录，而视图是建立在表格之上的一个架构，它本身并不实际储存数据记录。而视图不会因为退出数据库而消失。
语法格式1：create view 视图名 as 查询语句;   #创建视图
语法格式2：drop view 视图名;   #删除视图
show tables from 库名;   #该命令不仅可以查看库所包含的表，也可以查看有哪些视图

------------------------union-------------------------
union，联集，其作用是将两个SQL语句的结果合并起来，两个SQL语句所产生的字段需要是同样的数据记录种类
union ：生成结果的数据记录值将没有重复，且按照字段的顺序进行排序
union all ：将生成结果的数据记录值都列出来，无论有无重复
语法格式：[select 语句 1] UNION [all] [SELECT 语句 2];
select Store_name from location union select Store_name from store_info;

select Store_name from location union all select Store_name from store_info;

-------------------求交集的几种方法------------------
存在重复数据：
1.select A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name;  
2.select A.Store_name from location A,store_info B where A.Store_name=B.Store_name; 
3.select A.Store_name from location A inner join store_info B using (Store_name);

无重复数据：
1.想要得到无重复数据其实很简单，在重复数据的查询语句select后加上distinct去重即可。
select distinct A.Store_Name FROM location A inner join store_info B on A.Store_Name = B.Store_Name;

2.使用子查询，在内查询中查询info中的name字段，并将之作为外查询的条件，这样，外查询的查询语句的范围就只能在内查询查出的数据中进行。（子查询实际上就是变相的内查询）
select distinct Store_name from location where Store_name in (select Store_name from store_info);

3.使用左查询，将location和info表进行左查询，会查询出location表中的所有name字段的值以及info表中与location表中name的值相等的数据，再使用distinct进行去重
select distinct A.Store_name from location A left join store_info B using(Store_name) where B.Store_name is not null;

4.使用级联查询，union all会将两张表的所有数据都连接到一起，这时只需要通过count()函数将大于1的数值统计出来，即可实现查询两表的共同数值。
 select Store_name,count(A.Store_name) from (select Store_name from location union all select Store_name from store_info) A group by A.Store_name having count(A.Store_name)>1 ;

---------------------无交集---------------------
既然我们可以查询出有交集的数据，那么取反就可以实现无交集的查询了
select distinct Store_name from location  where Store_name not in (select Store_name from store_info);

5、sql判断

---------------------case---------------------
case是SQL语句用来做为when-then-else（当...就...否则...）之类逻辑的关键字
语法格式：
select case 字段名
when 条件1 then 结果1
when 条件2 then 条件2
.....
[else 结果n]
end
from 表名;
 
 --------空值(NULL) 和 无值('') 的区别----------
1．无值的长度为 0，不占用空间的；而 NULL 值的长度是 NULL，是占用空间的。
2．IS NULL 或者 IS NOT NULL，是用来判断字段是不是为 NULL 或者不是 NULL，不能查出是不是无值的。
3．无值的判断使用=''或者<>''来处理。<> 代表不等于。 
4．在通过 count()指定字段统计有多少行数时，如果遇到 NULL 值会自动忽略掉，遇到无值会加入到记录中进行计算。
create table city(name char(10));   #新建city表
insert into city values ('beijing');   #插入三个值
insert into city values ('nanjing');
insert into city values ('xuzhou');
insert into city values ('');   #插入两个无值
insert into city values ('');
insert into city values (null);   #插入两个空值
insert into city values (null);
select * from city;   #查询city表的所有值
select length(name) from city;   #查询name字段值的长度
select count(name) from city;   #统计name字段的值，空值会被忽略
select length('111'),length(null),length('');   #比较有值、空值、无值的长度
select * from city where name is null;   #查询name为空的值
select * from city where name is not null;   #查询name字段不为空的值
select * from city where name = '';   #查询name值为无值的数据
select * from city where name <> '';   #查询name字段不为无值的数据，空值会被忽略

6、正则表达式

--------------------正则表达式-------------------------
^:匹配文本的开始字符
$:匹配文本的结束字符
.:匹配任何一个字符
*:匹配零个或多个在它前面的字符
+:匹配前面的字符1次或多次
字符串:匹配包含指定的字符串 					
p1|p2:匹配p1或p2 							
[...]:匹配字符集合中的任意一个字符 			
[^...]:匹配不在括号中的任何字符 				
{n}:匹配前面的字符串 n 次 
{n,m}:匹配前面的字符串至少n次，至多m次
语法格式：select 字段 from 表名 where 字段 REGEXP {模式};
select * from city where name regexp 'zhou';   #匹配数据中带zhou的
select * from city where name regexp 'nan|bei';   #匹配数据中有nan或bei的
select * from city where name regexp '^[xnb]';   #匹配以xnb任一字符开头的数据

 mysql语句练习（复杂）

建表语句：

/*
 Navicat Premium Dump SQL

 Source Server         : localhost_3306
 Source Server Type    : MySQL
 Source Server Version : 80033 (8.0.33)
 Source Host           : localhost:3306
 Source Schema         : companymanagement

 Target Server Type    : MySQL
 Target Server Version : 80033 (8.0.33)
 File Encoding         : 65001

 Date: 21/02/2025 11:28:32
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments`  (
  `dept_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门ID，主键，自动递增',
  `dept_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门名称，非空',
  `city` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '部门所在城市',
  `budget` decimal(15, 2) NULL DEFAULT NULL COMMENT '部门预算（必须大于0）',
  PRIMARY KEY (`dept_id`) USING BTREE,
  CONSTRAINT `departments_chk_1` CHECK (`budget` > 0)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '部门信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES ('CS000001', '客户服务', '重庆', 16000000.00);
INSERT INTO `departments` VALUES ('DEV00001', '研发中心', '北京', 25000000.00);
INSERT INTO `departments` VALUES ('FIN00001', '财务中心', '深圳', 22000000.00);
INSERT INTO `departments` VALUES ('HRD00001', '人力资源', '广州', 12000000.00);
INSERT INTO `departments` VALUES ('IT000001', '信息技术', '武汉', 19000000.00);
INSERT INTO `departments` VALUES ('MKT00001', '市场运营', '上海', 18000000.00);
INSERT INTO `departments` VALUES ('OPS00001', '运营支持', '西安', 13000000.00);
INSERT INTO `departments` VALUES ('PROD0001', '产品管理', '成都', 17000000.00);
INSERT INTO `departments` VALUES ('SALES001', '销售中心', '南京', 20000000.00);
INSERT INTO `departments` VALUES ('TECH0001', '技术保障', '杭州', 15000000.00);

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees`  (
  `emp_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工ID，主键，自动递增',
  `emp_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '员工姓名，非空',
  `salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '员工薪资（必须大于等于0）',
  `dept_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所属部门ID，外键关联departments表',
  `manager_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '直属上级ID，外键关联本表emp_id，可为空表示无上级',
  `hire_date` date NOT NULL COMMENT '入职日期',
  PRIMARY KEY (`emp_id`) USING BTREE,
  INDEX `dept_id`(`dept_id` ASC) USING BTREE,
  INDEX `manager_id`(`manager_id` ASC) USING BTREE,
  CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`emp_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `employees_chk_1` CHECK (`salary` >= 0)
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '员工信息表（包含自引用管理结构）' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('EMP00001', '王总', 59961.38, 'OPS00001', NULL, '2013-07-12');
INSERT INTO `employees` VALUES ('EMP00002', '李总', 86532.36, 'FIN00001', NULL, '2015-03-16');
INSERT INTO `employees` VALUES ('EMP00003', '赵总', 53631.41, 'TECH0001', NULL, '2010-10-09');
INSERT INTO `employees` VALUES ('EMP00004', '刘总', 54139.33, 'MKT00001', NULL, '2013-05-19');
INSERT INTO `employees` VALUES ('EMP00005', '陈总', 83098.70, 'DEV00001', NULL, '2013-03-14');
INSERT INTO `employees` VALUES ('EMP00006', '杨总', 86134.30, 'TECH0001', NULL, '2010-06-15');
INSERT INTO `employees` VALUES ('EMP00007', '黄总', 97078.83, 'SALES001', NULL, '2017-04-18');
INSERT INTO `employees` VALUES ('EMP00008', '周总', 90680.49, 'TECH0001', NULL, '2014-09-22');
INSERT INTO `employees` VALUES ('EMP00009', '吴总', 83311.10, 'PROD0001', NULL, '2010-03-30');
INSERT INTO `employees` VALUES ('EMP00010', '徐总', 66514.87, 'SALES001', NULL, '2016-10-11');
INSERT INTO `employees` VALUES ('EMP00011', '周静', 14343.63, 'CS000001', 'EMP00010', '2015-11-15');
INSERT INTO `employees` VALUES ('EMP00012', '郭洋', 15866.89, 'FIN00001', 'EMP00001', '2015-10-26');
INSERT INTO `employees` VALUES ('EMP00013', '孙鹏', 30380.32, 'HRD00001', 'EMP00008', '2015-04-24');
INSERT INTO `employees` VALUES ('EMP00014', '赵娜', 18222.52, 'FIN00001', 'EMP00008', '2020-02-28');
INSERT INTO `employees` VALUES ('EMP00015', '徐秀', 27954.64, 'IT000001', 'EMP00008', '2017-06-08');
INSERT INTO `employees` VALUES ('EMP00016', '刘英', 19294.92, 'TECH0001', 'EMP00004', '2020-05-16');
INSERT INTO `employees` VALUES ('EMP00017', '刘敏', 34572.34, 'MKT00001', 'EMP00003', '2016-11-10');
INSERT INTO `employees` VALUES ('EMP00018', '孙英', 20984.32, 'PROD0001', 'EMP00001', '2018-02-04');
INSERT INTO `employees` VALUES ('EMP00019', '黄婷', 33244.38, 'PROD0001', 'EMP00012', '2020-01-24');
INSERT INTO `employees` VALUES ('EMP00020', '陈丽', 25560.42, 'OPS00001', 'EMP00019', '2018-10-24');
INSERT INTO `employees` VALUES ('EMP00021', '杨鹏', 26499.65, 'SALES001', 'EMP00018', '2016-03-24');
INSERT INTO `employees` VALUES ('EMP00022', '赵洋', 37340.83, 'CS000001', 'EMP00004', '2018-12-27');
INSERT INTO `employees` VALUES ('EMP00023', '高伟', 32168.21, 'OPS00001', 'EMP00016', '2019-08-13');
INSERT INTO `employees` VALUES ('EMP00024', '赵宁', 15269.17, 'MKT00001', 'EMP00001', '2016-05-04');
INSERT INTO `employees` VALUES ('EMP00025', '王鹏', 21785.48, 'IT000001', 'EMP00015', '2015-06-07');
INSERT INTO `employees` VALUES ('EMP00026', '赵鹏', 14972.74, 'SALES001', 'EMP00009', '2018-05-03');
INSERT INTO `employees` VALUES ('EMP00027', '高宇', 30378.13, 'FIN00001', 'EMP00021', '2016-01-15');
INSERT INTO `employees` VALUES ('EMP00028', '刘宇', 27480.20, 'OPS00001', 'EMP00009', '2018-01-12');
INSERT INTO `employees` VALUES ('EMP00029', '赵敏', 12255.79, 'FIN00001', 'EMP00014', '2016-07-22');
INSERT INTO `employees` VALUES ('EMP00030', '林英', 16255.32, 'CS000001', 'EMP00024', '2016-06-25');
INSERT INTO `employees` VALUES ('EMP00031', '王明', 32087.18, 'MKT00001', 'EMP00022', '2017-09-23');
INSERT INTO `employees` VALUES ('EMP00032', '郭宇', 36181.60, 'FIN00001', 'EMP00007', '2015-09-08');
INSERT INTO `employees` VALUES ('EMP00033', '吴艳', 17633.89, 'IT000001', 'EMP00024', '2017-03-21');
INSERT INTO `employees` VALUES ('EMP00034', '吴敏', 14630.62, 'IT000001', 'EMP00027', '2015-10-05');
INSERT INTO `employees` VALUES ('EMP00035', '徐洋', 37294.10, 'CS000001', 'EMP00003', '2015-08-09');
INSERT INTO `employees` VALUES ('EMP00036', '胡浩', 31258.22, 'PROD0001', 'EMP00001', '2018-01-08');
INSERT INTO `employees` VALUES ('EMP00037', '黄洋', 20851.38, 'MKT00001', 'EMP00005', '2018-10-06');
INSERT INTO `employees` VALUES ('EMP00038', '林鹏', 35858.55, 'DEV00001', 'EMP00034', '2019-05-21');
INSERT INTO `employees` VALUES ('EMP00039', '吴艳', 14864.66, 'FIN00001', 'EMP00007', '2020-06-21');
INSERT INTO `employees` VALUES ('EMP00040', '何婷', 16330.34, 'CS000001', 'EMP00024', '2016-01-20');
INSERT INTO `employees` VALUES ('EMP00041', '赵洋', 10786.64, 'TECH0001', 'EMP00029', '2018-11-08');
INSERT INTO `employees` VALUES ('EMP00042', '徐宁', 21994.12, 'MKT00001', 'EMP00035', '2016-05-13');
INSERT INTO `employees` VALUES ('EMP00043', '赵杰', 23928.72, 'CS000001', 'EMP00036', '2015-01-10');
INSERT INTO `employees` VALUES ('EMP00044', '朱磊', 22550.76, 'SALES001', 'EMP00004', '2020-04-21');
INSERT INTO `employees` VALUES ('EMP00045', '陈鹏', 28941.04, 'CS000001', 'EMP00043', '2016-11-04');
INSERT INTO `employees` VALUES ('EMP00046', '吴杰', 33442.23, 'SALES001', 'EMP00038', '2016-05-01');
INSERT INTO `employees` VALUES ('EMP00047', '李芳', 21054.41, 'HRD00001', 'EMP00041', '2016-06-14');
INSERT INTO `employees` VALUES ('EMP00048', '刘浩', 22933.10, 'MKT00001', 'EMP00038', '2019-04-28');
INSERT INTO `employees` VALUES ('EMP00049', '徐磊', 28788.28, 'IT000001', 'EMP00004', '2019-12-04');
INSERT INTO `employees` VALUES ('EMP00050', '高静', 33843.00, 'HRD00001', 'EMP00036', '2017-07-01');
INSERT INTO `employees` VALUES ('EMP00051', '周丽', 15197.84, 'MKT00001', 'EMP00018', '2018-12-20');
INSERT INTO `employees` VALUES ('EMP00052', '马强', 16758.25, 'FIN00001', 'EMP00006', '2019-10-31');
INSERT INTO `employees` VALUES ('EMP00053', '郑杰', 16868.68, 'IT000001', 'EMP00028', '2020-06-20');
INSERT INTO `employees` VALUES ('EMP00054', '朱磊', 21840.45, 'TECH0001', 'EMP00019', '2015-06-04');
INSERT INTO `employees` VALUES ('EMP00055', '徐宇', 27397.75, 'OPS00001', 'EMP00015', '2016-11-18');
INSERT INTO `employees` VALUES ('EMP00056', '高宁', 17065.78, 'FIN00001', 'EMP00017', '2017-06-24');
INSERT INTO `employees` VALUES ('EMP00057', '张秀', 15057.10, 'SALES001', 'EMP00056', '2015-04-19');
INSERT INTO `employees` VALUES ('EMP00058', '刘艳', 10143.55, 'SALES001', 'EMP00012', '2016-05-30');
INSERT INTO `employees` VALUES ('EMP00059', '朱鑫', 31224.63, 'HRD00001', 'EMP00019', '2015-05-25');
INSERT INTO `employees` VALUES ('EMP00060', '孙静', 30658.76, 'PROD0001', 'EMP00037', '2017-02-23');
INSERT INTO `employees` VALUES ('EMP00061', '郑英', 9523.04, 'OPS00001', 'EMP00029', '2017-08-03');
INSERT INTO `employees` VALUES ('EMP00062', '李婷', 21556.99, 'IT000001', 'EMP00008', '2019-04-07');
INSERT INTO `employees` VALUES ('EMP00063', '郭浩', 36061.60, 'FIN00001', 'EMP00032', '2018-02-13');
INSERT INTO `employees` VALUES ('EMP00064', '郭婷', 21271.15, 'IT000001', 'EMP00002', '2018-08-31');
INSERT INTO `employees` VALUES ('EMP00065', '孙芳', 11176.66, 'SALES001', 'EMP00008', '2018-04-06');
INSERT INTO `employees` VALUES ('EMP00066', '郑婷', 37435.01, 'FIN00001', 'EMP00045', '2015-10-23');
INSERT INTO `employees` VALUES ('EMP00067', '胡杰', 34240.79, 'IT000001', 'EMP00001', '2015-07-11');
INSERT INTO `employees` VALUES ('EMP00068', '马娜', 10819.58, 'MKT00001', 'EMP00035', '2017-07-31');
INSERT INTO `employees` VALUES ('EMP00069', '陈强', 12927.20, 'OPS00001', 'EMP00014', '2017-11-09');
INSERT INTO `employees` VALUES ('EMP00070', '张宇', 18007.07, 'HRD00001', 'EMP00040', '2015-10-09');
INSERT INTO `employees` VALUES ('EMP00071', '朱秀', 17707.45, 'DEV00001', 'EMP00022', '2016-05-22');
INSERT INTO `employees` VALUES ('EMP00072', '胡秀', 16447.62, 'IT000001', 'EMP00036', '2018-01-11');
INSERT INTO `employees` VALUES ('EMP00073', '刘鑫', 11182.38, 'OPS00001', 'EMP00034', '2015-07-18');
INSERT INTO `employees` VALUES ('EMP00074', '陈婷', 36844.29, 'FIN00001', 'EMP00032', '2016-06-16');
INSERT INTO `employees` VALUES ('EMP00075', '张浩', 35251.54, 'FIN00001', 'EMP00017', '2019-11-18');
INSERT INTO `employees` VALUES ('EMP00076', '郑磊', 19096.02, 'DEV00001', 'EMP00026', '2015-10-07');
INSERT INTO `employees` VALUES ('EMP00077', '马丽', 23844.00, 'PROD0001', 'EMP00075', '2018-06-18');
INSERT INTO `employees` VALUES ('EMP00078', '杨明', 16585.30, 'SALES001', 'EMP00044', '2018-06-01');
INSERT INTO `employees` VALUES ('EMP00079', '周婷', 28785.58, 'CS000001', 'EMP00018', '2020-02-13');
INSERT INTO `employees` VALUES ('EMP00080', '徐静', 12470.06, 'SALES001', 'EMP00077', '2015-08-30');
INSERT INTO `employees` VALUES ('EMP00081', '孙宇', 26355.25, 'PROD0001', 'EMP00062', '2016-06-17');
INSERT INTO `employees` VALUES ('EMP00082', '刘杰', 23693.22, 'CS000001', 'EMP00046', '2017-03-04');
INSERT INTO `employees` VALUES ('EMP00083', '王婷', 26131.85, 'SALES001', 'EMP00010', '2016-10-21');
INSERT INTO `employees` VALUES ('EMP00084', '郭强', 27251.36, 'MKT00001', 'EMP00051', '2017-07-15');
INSERT INTO `employees` VALUES ('EMP00085', '朱秀', 22261.10, 'IT000001', 'EMP00040', '2015-04-17');
INSERT INTO `employees` VALUES ('EMP00086', '高磊', 29829.73, 'OPS00001', 'EMP00032', '2016-04-13');
INSERT INTO `employees` VALUES ('EMP00087', '孙敏', 9122.17, 'IT000001', 'EMP00014', '2019-05-18');
INSERT INTO `employees` VALUES ('EMP00088', '郭鹏', 15798.67, 'IT000001', 'EMP00014', '2020-04-22');
INSERT INTO `employees` VALUES ('EMP00089', '吴鹏', 12829.38, 'HRD00001', 'EMP00007', '2015-08-04');
INSERT INTO `employees` VALUES ('EMP00090', '李杰', 32189.34, 'FIN00001', 'EMP00015', '2020-03-20');
INSERT INTO `employees` VALUES ('EMP00091', '周杰', 34259.10, 'IT000001', 'EMP00044', '2017-05-16');
INSERT INTO `employees` VALUES ('EMP00092', '马英', 12543.92, 'FIN00001', 'EMP00064', '2015-10-01');
INSERT INTO `employees` VALUES ('EMP00093', '赵浩', 24437.78, 'SALES001', 'EMP00065', '2016-02-19');
INSERT INTO `employees` VALUES ('EMP00094', '徐芳', 20461.21, 'DEV00001', 'EMP00042', '2016-06-16');
INSERT INTO `employees` VALUES ('EMP00095', '李娜', 21770.83, 'DEV00001', 'EMP00018', '2017-05-30');
INSERT INTO `employees` VALUES ('EMP00096', '张艳', 19294.46, 'MKT00001', 'EMP00021', '2019-06-12');
INSERT INTO `employees` VALUES ('EMP00097', '张静', 24776.22, 'PROD0001', 'EMP00038', '2017-04-29');
INSERT INTO `employees` VALUES ('EMP00098', '李宇', 33944.28, 'CS000001', 'EMP00087', '2019-06-02');
INSERT INTO `employees` VALUES ('EMP00099', '赵洋', 14516.89, 'DEV00001', 'EMP00014', '2016-10-19');
INSERT INTO `employees` VALUES ('EMP00100', '徐鹏', 12547.19, 'MKT00001', 'EMP00016', '2016-03-27');

-- ----------------------------
-- Table structure for projects
-- ----------------------------
DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects`  (
  `project_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目ID，主键，自动递增',
  `project_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目名称，非空',
  `dept_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '负责部门ID，外键关联departments表',
  `lead_emp_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目负责人ID，外键关联employees表',
  `start_date` date NOT NULL COMMENT '项目开始日期',
  `end_date` date NULL DEFAULT NULL COMMENT '项目结束日期（必须晚于开始日期）',
  PRIMARY KEY (`project_id`) USING BTREE,
  INDEX `dept_id`(`dept_id` ASC) USING BTREE,
  INDEX `lead_emp_id`(`lead_emp_id` ASC) USING BTREE,
  CONSTRAINT `projects_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `projects_ibfk_2` FOREIGN KEY (`lead_emp_id`) REFERENCES `employees` (`emp_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `projects_chk_1` CHECK ((`end_date` is null) or (`end_date` > `start_date`))
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '项目信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of projects
-- ----------------------------
INSERT INTO `projects` VALUES ('PROJ23010074', '产品管理年度计划730号', 'PROD0001', 'EMP00077', '2023-01-21', NULL);
INSERT INTO `projects` VALUES ('PROJ23010076', '财务中心年度计划721号', 'FIN00001', 'EMP00090', '2023-01-22', '2023-09-01');
INSERT INTO `projects` VALUES ('PROJ23010091', '销售中心年度计划826号', 'SALES001', 'EMP00007', '2023-01-09', NULL);
INSERT INTO `projects` VALUES ('PROJ23010099', '产品管理年度计划340号', 'PROD0001', 'EMP00018', '2023-01-09', '2023-06-25');
INSERT INTO `projects` VALUES ('PROJ23020013', '运营支持年度计划246号', 'OPS00001', 'EMP00001', '2023-02-21', '2023-05-17');
INSERT INTO `projects` VALUES ('PROJ23020032', '产品管理年度计划768号', 'PROD0001', 'EMP00077', '2023-02-04', '2023-11-13');
INSERT INTO `projects` VALUES ('PROJ23020049', '产品管理季度冲刺573号', 'PROD0001', 'EMP00018', '2023-02-10', NULL);
INSERT INTO `projects` VALUES ('PROJ23020057', '客户服务季度冲刺358号', 'CS000001', 'EMP00030', '2023-02-03', '2023-11-27');
INSERT INTO `projects` VALUES ('PROJ23020062', '技术保障年度计划420号', 'TECH0001', 'EMP00054', '2023-02-09', NULL);
INSERT INTO `projects` VALUES ('PROJ23020073', '客户服务年度计划666号', 'CS000001', 'EMP00030', '2023-02-25', '2023-09-21');
INSERT INTO `projects` VALUES ('PROJ23020098', '研发中心年度计划196号', 'DEV00001', 'EMP00099', '2023-02-19', NULL);
INSERT INTO `projects` VALUES ('PROJ23030010', '人力资源年度计划909号', 'HRD00001', 'EMP00070', '2023-03-10', '2023-09-11');
INSERT INTO `projects` VALUES ('PROJ23030024', '研发中心年度计划256号', 'DEV00001', 'EMP00076', '2023-03-07', NULL);
INSERT INTO `projects` VALUES ('PROJ23030056', '市场运营年度计划841号', 'MKT00001', 'EMP00084', '2023-03-20', '2023-08-27');
INSERT INTO `projects` VALUES ('PROJ23040007', '产品管理季度冲刺714号', 'PROD0001', 'EMP00036', '2023-04-09', NULL);
INSERT INTO `projects` VALUES ('PROJ23040033', '技术保障季度冲刺342号', 'TECH0001', 'EMP00003', '2023-04-21', '2023-09-07');
INSERT INTO `projects` VALUES ('PROJ23040047', '研发中心季度冲刺559号', 'DEV00001', 'EMP00099', '2023-04-20', '2024-02-28');
INSERT INTO `projects` VALUES ('PROJ23040060', '技术保障季度冲刺718号', 'TECH0001', 'EMP00003', '2023-04-13', '2023-12-31');
INSERT INTO `projects` VALUES ('PROJ23040061', '技术保障年度计划395号', 'TECH0001', 'EMP00041', '2023-04-09', '2023-10-21');
INSERT INTO `projects` VALUES ('PROJ23040069', '客户服务季度冲刺360号', 'CS000001', 'EMP00022', '2023-04-09', NULL);
INSERT INTO `projects` VALUES ('PROJ23040072', '信息技术季度冲刺857号', 'IT000001', 'EMP00015', '2023-04-30', '2023-07-01');
INSERT INTO `projects` VALUES ('PROJ23040078', '研发中心年度计划53号', 'DEV00001', 'EMP00095', '2023-04-01', '2023-08-15');
INSERT INTO `projects` VALUES ('PROJ23040082', '销售中心季度冲刺905号', 'SALES001', 'EMP00021', '2023-04-26', '2023-12-26');
INSERT INTO `projects` VALUES ('PROJ23040084', '研发中心年度计划384号', 'DEV00001', 'EMP00071', '2023-04-27', NULL);
INSERT INTO `projects` VALUES ('PROJ23040094', '研发中心年度计划296号', 'DEV00001', 'EMP00076', '2023-04-28', '2024-01-22');
INSERT INTO `projects` VALUES ('PROJ23050001', '市场运营年度计划596号', 'MKT00001', 'EMP00017', '2023-05-14', '2023-11-21');
INSERT INTO `projects` VALUES ('PROJ23050002', '财务中心季度冲刺527号', 'FIN00001', 'EMP00092', '2023-05-15', '2024-04-02');
INSERT INTO `projects` VALUES ('PROJ23050040', '研发中心季度冲刺606号', 'DEV00001', 'EMP00094', '2023-05-21', NULL);
INSERT INTO `projects` VALUES ('PROJ23050042', '信息技术年度计划65号', 'IT000001', 'EMP00064', '2023-05-26', '2024-01-19');
INSERT INTO `projects` VALUES ('PROJ23060009', '销售中心季度冲刺235号', 'SALES001', 'EMP00083', '2023-06-21', '2024-02-17');
INSERT INTO `projects` VALUES ('PROJ23060012', '信息技术季度冲刺874号', 'IT000001', 'EMP00025', '2023-06-28', NULL);
INSERT INTO `projects` VALUES ('PROJ23060028', '市场运营年度计划910号', 'MKT00001', 'EMP00017', '2023-06-02', NULL);
INSERT INTO `projects` VALUES ('PROJ23060034', '客户服务年度计划138号', 'CS000001', 'EMP00082', '2023-06-16', '2023-09-16');
INSERT INTO `projects` VALUES ('PROJ23060045', '运营支持年度计划350号', 'OPS00001', 'EMP00055', '2023-06-25', '2023-08-26');
INSERT INTO `projects` VALUES ('PROJ23060075', '研发中心年度计划87号', 'DEV00001', 'EMP00076', '2023-06-08', NULL);
INSERT INTO `projects` VALUES ('PROJ23060083', '销售中心季度冲刺711号', 'SALES001', 'EMP00058', '2023-06-14', '2024-01-27');
INSERT INTO `projects` VALUES ('PROJ23060085', '运营支持年度计划844号', 'OPS00001', 'EMP00020', '2023-06-07', '2023-08-25');
INSERT INTO `projects` VALUES ('PROJ23060095', '产品管理季度冲刺581号', 'PROD0001', 'EMP00060', '2023-06-28', '2023-12-04');
INSERT INTO `projects` VALUES ('PROJ23070008', '人力资源年度计划658号', 'HRD00001', 'EMP00050', '2023-07-22', '2023-10-24');
INSERT INTO `projects` VALUES ('PROJ23070011', '信息技术季度冲刺510号', 'IT000001', 'EMP00085', '2023-07-10', NULL);
INSERT INTO `projects` VALUES ('PROJ23070035', '技术保障年度计划641号', 'TECH0001', 'EMP00003', '2023-07-11', '2023-12-02');
INSERT INTO `projects` VALUES ('PROJ23070050', '销售中心季度冲刺356号', 'SALES001', 'EMP00007', '2023-07-09', NULL);
INSERT INTO `projects` VALUES ('PROJ23070065', '技术保障年度计划828号', 'TECH0001', 'EMP00016', '2023-07-15', '2023-12-03');
INSERT INTO `projects` VALUES ('PROJ23070093', '信息技术季度冲刺425号', 'IT000001', 'EMP00049', '2023-07-12', '2023-11-19');
INSERT INTO `projects` VALUES ('PROJ23080014', '信息技术年度计划700号', 'IT000001', 'EMP00034', '2023-08-18', '2024-01-13');
INSERT INTO `projects` VALUES ('PROJ23080030', '技术保障年度计划653号', 'TECH0001', 'EMP00054', '2023-08-31', NULL);
INSERT INTO `projects` VALUES ('PROJ23080064', '研发中心季度冲刺482号', 'DEV00001', 'EMP00099', '2023-08-04', '2023-09-17');
INSERT INTO `projects` VALUES ('PROJ23080088', '市场运营年度计划713号', 'MKT00001', 'EMP00084', '2023-08-01', NULL);
INSERT INTO `projects` VALUES ('PROJ23080097', '市场运营季度冲刺490号', 'MKT00001', 'EMP00100', '2023-08-11', '2023-11-15');
INSERT INTO `projects` VALUES ('PROJ23090017', '客户服务年度计划692号', 'CS000001', 'EMP00079', '2023-09-22', NULL);
INSERT INTO `projects` VALUES ('PROJ23090021', '信息技术季度冲刺221号', 'IT000001', 'EMP00091', '2023-09-13', '2024-01-23');
INSERT INTO `projects` VALUES ('PROJ23090026', '客户服务季度冲刺450号', 'CS000001', 'EMP00098', '2023-09-10', NULL);
INSERT INTO `projects` VALUES ('PROJ23090038', '运营支持季度冲刺165号', 'OPS00001', 'EMP00073', '2023-09-25', '2024-07-31');
INSERT INTO `projects` VALUES ('PROJ23090090', '运营支持年度计划952号', 'OPS00001', 'EMP00028', '2023-09-18', '2024-04-13');
INSERT INTO `projects` VALUES ('PROJ23100004', '研发中心季度冲刺564号', 'DEV00001', 'EMP00005', '2023-10-24', NULL);
INSERT INTO `projects` VALUES ('PROJ23100005', '技术保障季度冲刺197号', 'TECH0001', 'EMP00041', '2023-10-06', NULL);
INSERT INTO `projects` VALUES ('PROJ23100018', '财务中心年度计划725号', 'FIN00001', 'EMP00075', '2023-10-04', '2024-07-03');
INSERT INTO `projects` VALUES ('PROJ23100022', '财务中心季度冲刺112号', 'FIN00001', 'EMP00032', '2023-10-28', NULL);
INSERT INTO `projects` VALUES ('PROJ23100029', '产品管理季度冲刺723号', 'PROD0001', 'EMP00009', '2023-10-31', NULL);
INSERT INTO `projects` VALUES ('PROJ23100063', '人力资源季度冲刺897号', 'HRD00001', 'EMP00050', '2023-10-11', '2023-12-31');
INSERT INTO `projects` VALUES ('PROJ23100086', '市场运营季度冲刺964号', 'MKT00001', 'EMP00051', '2023-10-30', '2024-06-13');
INSERT INTO `projects` VALUES ('PROJ23100092', '市场运营季度冲刺343号', 'MKT00001', 'EMP00004', '2023-10-04', NULL);
INSERT INTO `projects` VALUES ('PROJ23110003', '信息技术年度计划263号', 'IT000001', 'EMP00064', '2023-11-25', '2024-06-05');
INSERT INTO `projects` VALUES ('PROJ23110036', '销售中心年度计划708号', 'SALES001', 'EMP00065', '2023-11-27', NULL);
INSERT INTO `projects` VALUES ('PROJ23110053', '财务中心年度计划194号', 'FIN00001', 'EMP00074', '2023-11-29', NULL);
INSERT INTO `projects` VALUES ('PROJ23110066', '研发中心季度冲刺305号', 'DEV00001', 'EMP00076', '2023-11-24', '2023-12-26');
INSERT INTO `projects` VALUES ('PROJ23110079', '人力资源年度计划515号', 'HRD00001', 'EMP00059', '2023-11-25', NULL);
INSERT INTO `projects` VALUES ('PROJ23120044', '销售中心季度冲刺710号', 'SALES001', 'EMP00046', '2023-12-25', NULL);
INSERT INTO `projects` VALUES ('PROJ23120052', '财务中心年度计划52号', 'FIN00001', 'EMP00063', '2023-12-17', NULL);
INSERT INTO `projects` VALUES ('PROJ23120058', '市场运营季度冲刺790号', 'MKT00001', 'EMP00068', '2023-12-18', '2024-02-18');
INSERT INTO `projects` VALUES ('PROJ23120081', '客户服务季度冲刺158号', 'CS000001', 'EMP00043', '2023-12-06', '2024-04-17');
INSERT INTO `projects` VALUES ('PROJ24010006', '财务中心季度冲刺937号', 'FIN00001', 'EMP00014', '2024-01-16', NULL);
INSERT INTO `projects` VALUES ('PROJ24010020', '运营支持年度计划259号', 'OPS00001', 'EMP00061', '2024-01-14', NULL);
INSERT INTO `projects` VALUES ('PROJ24010023', '技术保障季度冲刺776号', 'TECH0001', 'EMP00006', '2024-01-26', '2024-07-28');
INSERT INTO `projects` VALUES ('PROJ24010043', '财务中心季度冲刺822号', 'FIN00001', 'EMP00029', '2024-01-31', '2024-07-09');
INSERT INTO `projects` VALUES ('PROJ24010048', '技术保障年度计划793号', 'TECH0001', 'EMP00041', '2024-01-03', NULL);
INSERT INTO `projects` VALUES ('PROJ24010051', '销售中心季度冲刺816号', 'SALES001', 'EMP00046', '2024-01-21', '2024-08-22');
INSERT INTO `projects` VALUES ('PROJ24010068', '产品管理年度计划616号', 'PROD0001', 'EMP00097', '2024-01-23', '2024-09-19');
INSERT INTO `projects` VALUES ('PROJ24010070', '运营支持季度冲刺991号', 'OPS00001', 'EMP00086', '2024-01-27', '2024-10-27');
INSERT INTO `projects` VALUES ('PROJ24020015', '产品管理季度冲刺33号', 'PROD0001', 'EMP00019', '2024-02-18', NULL);
INSERT INTO `projects` VALUES ('PROJ24020031', '客户服务年度计划644号', 'CS000001', 'EMP00030', '2024-02-10', NULL);
INSERT INTO `projects` VALUES ('PROJ24020054', '运营支持季度冲刺66号', 'OPS00001', 'EMP00001', '2024-02-23', '2024-08-27');
INSERT INTO `projects` VALUES ('PROJ24020059', '技术保障年度计划784号', 'TECH0001', 'EMP00003', '2024-02-02', '2024-09-13');
INSERT INTO `projects` VALUES ('PROJ24020077', '客户服务季度冲刺310号', 'CS000001', 'EMP00030', '2024-02-18', '2024-07-16');
INSERT INTO `projects` VALUES ('PROJ24020100', '市场运营年度计划127号', 'MKT00001', 'EMP00100', '2024-02-25', NULL);
INSERT INTO `projects` VALUES ('PROJ24030019', '人力资源季度冲刺829号', 'HRD00001', 'EMP00089', '2024-03-16', NULL);
INSERT INTO `projects` VALUES ('PROJ24030025', '财务中心年度计划403号', 'FIN00001', 'EMP00014', '2024-03-06', '2024-05-05');
INSERT INTO `projects` VALUES ('PROJ24030087', '研发中心季度冲刺637号', 'DEV00001', 'EMP00095', '2024-03-25', '2024-07-29');
INSERT INTO `projects` VALUES ('PROJ24030096', '客户服务季度冲刺239号', 'CS000001', 'EMP00030', '2024-03-04', '2024-05-14');
INSERT INTO `projects` VALUES ('PROJ24040016', '信息技术季度冲刺768号', 'IT000001', 'EMP00087', '2024-04-16', '2024-08-03');
INSERT INTO `projects` VALUES ('PROJ24040037', '技术保障年度计划923号', 'TECH0001', 'EMP00041', '2024-04-28', '2024-12-08');
INSERT INTO `projects` VALUES ('PROJ24040039', '技术保障年度计划434号', 'TECH0001', 'EMP00006', '2024-04-18', NULL);
INSERT INTO `projects` VALUES ('PROJ24040041', '市场运营季度冲刺980号', 'MKT00001', 'EMP00042', '2024-04-03', '2024-05-29');
INSERT INTO `projects` VALUES ('PROJ24040067', '运营支持年度计划757号', 'OPS00001', 'EMP00023', '2024-04-27', '2024-12-10');
INSERT INTO `projects` VALUES ('PROJ24040071', '人力资源季度冲刺545号', 'HRD00001', 'EMP00013', '2024-04-13', '2024-12-29');
INSERT INTO `projects` VALUES ('PROJ24040080', '财务中心季度冲刺841号', 'FIN00001', 'EMP00075', '2024-04-10', NULL);
INSERT INTO `projects` VALUES ('PROJ24040089', '研发中心年度计划258号', 'DEV00001', 'EMP00076', '2024-04-11', '2025-01-12');
INSERT INTO `projects` VALUES ('PROJ24050027', '产品管理季度冲刺476号', 'PROD0001', 'EMP00081', '2024-05-13', '2024-08-04');
INSERT INTO `projects` VALUES ('PROJ24050046', '财务中心年度计划725号', 'FIN00001', 'EMP00029', '2024-05-04', '2024-08-29');
INSERT INTO `projects` VALUES ('PROJ24050055', '财务中心年度计划668号', 'FIN00001', 'EMP00014', '2024-05-07', '2024-12-24');

-- ----------------------------
-- Table structure for tasks
-- ----------------------------
DROP TABLE IF EXISTS `tasks`;
CREATE TABLE `tasks`  (
  `task_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务ID，主键，自动递增',
  `project_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '所属项目ID，外键关联projects表',
  `parent_task_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '父任务ID，外键关联本表task_id，可为空表示顶级任务',
  `assigned_emp_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '分配员工ID，外键关联employees表',
  `task_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '任务名称，非空',
  `status` enum('Pending','In Progress','Completed') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'Pending' COMMENT '任务状态（枚举值：未开始/进行中/已完成）',
  `deadline` date NOT NULL COMMENT '任务截止日期',
  PRIMARY KEY (`task_id`) USING BTREE,
  INDEX `project_id`(`project_id` ASC) USING BTREE,
  INDEX `parent_task_id`(`parent_task_id` ASC) USING BTREE,
  INDEX `assigned_emp_id`(`assigned_emp_id` ASC) USING BTREE,
  CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`project_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`parent_task_id`) REFERENCES `tasks` (`task_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`assigned_emp_id`) REFERENCES `employees` (`emp_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '任务明细表（支持多级子任务）' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tasks
-- ----------------------------
INSERT INTO `tasks` VALUES ('TSK000001', 'PROJ23110066', NULL, 'EMP00094', '测试验收-v4', 'Pending', '2023-12-30');
INSERT INTO `tasks` VALUES ('TSK000002', 'PROJ23060083', NULL, 'EMP00046', '需求分析-v7', 'Pending', '2023-06-15');
INSERT INTO `tasks` VALUES ('TSK000003', 'PROJ23100004', NULL, 'EMP00071', '原型设计-v3', 'In Progress', '2024-01-01');
INSERT INTO `tasks` VALUES ('TSK000004', 'PROJ23100063', NULL, 'EMP00070', '需求分析-v5', 'Pending', '2023-10-17');
INSERT INTO `tasks` VALUES ('TSK000005', 'PROJ24050046', NULL, 'EMP00002', '需求分析-v6', 'Completed', '2024-06-06');
INSERT INTO `tasks` VALUES ('TSK000006', 'PROJ23070093', NULL, 'EMP00049', '测试验收-v1', 'Pending', '2023-09-21');
INSERT INTO `tasks` VALUES ('TSK000007', 'PROJ24010051', NULL, 'EMP00083', '测试验收-v3', 'Pending', '2024-02-25');
INSERT INTO `tasks` VALUES ('TSK000008', 'PROJ23060045', NULL, 'EMP00061', '上线部署-v6', 'In Progress', '2023-08-31');
INSERT INTO `tasks` VALUES ('TSK000009', 'PROJ23100086', NULL, 'EMP00051', '需求分析-v6', 'In Progress', '2024-01-19');
INSERT INTO `tasks` VALUES ('TSK000010', 'PROJ24010043', NULL, 'EMP00029', '原型设计-v4', 'Completed', '2024-04-22');
INSERT INTO `tasks` VALUES ('TSK000011', 'PROJ23020032', NULL, 'EMP00077', '需求分析-v5', 'Completed', '2023-03-23');
INSERT INTO `tasks` VALUES ('TSK000012', 'PROJ23060075', NULL, 'EMP00094', '上线部署-v10', 'Pending', '2023-07-25');
INSERT INTO `tasks` VALUES ('TSK000013', 'PROJ23100005', NULL, 'EMP00016', '需求分析-v9', 'Completed', '2023-12-15');
INSERT INTO `tasks` VALUES ('TSK000014', 'PROJ23060085', NULL, 'EMP00069', '代码开发-v2', 'Pending', '2023-06-30');
INSERT INTO `tasks` VALUES ('TSK000015', 'PROJ23080088', NULL, 'EMP00084', '上线部署-v4', 'Pending', '2023-10-08');
INSERT INTO `tasks` VALUES ('TSK000016', 'PROJ23020062', NULL, 'EMP00006', '代码开发-v6', 'In Progress', '2023-03-05');
INSERT INTO `tasks` VALUES ('TSK000017', 'PROJ23060085', 'TSK000014', 'EMP00020', '需求分析-v9', 'In Progress', '2023-08-17');
INSERT INTO `tasks` VALUES ('TSK000018', 'PROJ23080030', NULL, 'EMP00041', '测试验收-v7', 'Pending', '2023-10-23');
INSERT INTO `tasks` VALUES ('TSK000019', 'PROJ23060095', NULL, 'EMP00018', '代码开发-v2', 'In Progress', '2023-08-11');
INSERT INTO `tasks` VALUES ('TSK000020', 'PROJ23120058', NULL, 'EMP00100', '上线部署-v4', 'Pending', '2024-01-05');
INSERT INTO `tasks` VALUES ('TSK000021', 'PROJ24040071', NULL, 'EMP00070', '原型设计-v10', 'In Progress', '2024-05-30');
INSERT INTO `tasks` VALUES ('TSK000022', 'PROJ23110036', NULL, 'EMP00010', '原型设计-v4', 'Completed', '2024-01-17');
INSERT INTO `tasks` VALUES ('TSK000023', 'PROJ23040060', NULL, 'EMP00016', '上线部署-v6', 'Pending', '2023-05-14');
INSERT INTO `tasks` VALUES ('TSK000024', 'PROJ23110053', NULL, 'EMP00014', '原型设计-v6', 'Pending', '2024-02-19');
INSERT INTO `tasks` VALUES ('TSK000025', 'PROJ24030087', NULL, 'EMP00005', '需求分析-v1', 'Pending', '2024-03-30');
INSERT INTO `tasks` VALUES ('TSK000026', 'PROJ24050055', NULL, 'EMP00039', '测试验收-v7', 'Pending', '2024-05-21');
INSERT INTO `tasks` VALUES ('TSK000027', 'PROJ23030010', NULL, 'EMP00059', '代码开发-v3', 'Pending', '2023-06-04');
INSERT INTO `tasks` VALUES ('TSK000028', 'PROJ23060028', NULL, 'EMP00096', '代码开发-v4', 'In Progress', '2023-08-21');
INSERT INTO `tasks` VALUES ('TSK000029', 'PROJ24040037', NULL, 'EMP00054', '测试验收-v4', 'Completed', '2024-06-13');
INSERT INTO `tasks` VALUES ('TSK000030', 'PROJ23060075', 'TSK000012', 'EMP00038', '上线部署-v6', 'Completed', '2023-07-26');
INSERT INTO `tasks` VALUES ('TSK000031', 'PROJ23060085', 'TSK000017', 'EMP00028', '原型设计-v8', 'Completed', '2023-07-03');
INSERT INTO `tasks` VALUES ('TSK000032', 'PROJ23110079', NULL, 'EMP00059', '测试验收-v7', 'Pending', '2024-01-24');
INSERT INTO `tasks` VALUES ('TSK000033', 'PROJ24050055', 'TSK000026', 'EMP00039', '需求分析-v1', 'Completed', '2024-07-01');
INSERT INTO `tasks` VALUES ('TSK000034', 'PROJ24030025', NULL, 'EMP00063', '原型设计-v2', 'Completed', '2024-06-03');
INSERT INTO `tasks` VALUES ('TSK000035', 'PROJ24020031', NULL, 'EMP00035', '原型设计-v3', 'In Progress', '2024-03-11');
INSERT INTO `tasks` VALUES ('TSK000036', 'PROJ23110053', NULL, 'EMP00012', '原型设计-v3', 'Pending', '2024-02-22');
INSERT INTO `tasks` VALUES ('TSK000037', 'PROJ24020077', NULL, 'EMP00035', '测试验收-v7', 'Completed', '2024-04-04');
INSERT INTO `tasks` VALUES ('TSK000038', 'PROJ24010006', NULL, 'EMP00032', '上线部署-v7', 'Completed', '2024-02-24');
INSERT INTO `tasks` VALUES ('TSK000039', 'PROJ24020031', 'TSK000035', 'EMP00030', '代码开发-v1', 'In Progress', '2024-03-16');
INSERT INTO `tasks` VALUES ('TSK000040', 'PROJ23040082', NULL, 'EMP00093', '代码开发-v2', 'Pending', '2023-05-04');
INSERT INTO `tasks` VALUES ('TSK000041', 'PROJ24010051', 'TSK000007', 'EMP00058', '上线部署-v1', 'In Progress', '2024-02-02');
INSERT INTO `tasks` VALUES ('TSK000042', 'PROJ23040072', NULL, 'EMP00049', '测试验收-v7', 'Pending', '2023-05-23');
INSERT INTO `tasks` VALUES ('TSK000043', 'PROJ24010023', NULL, 'EMP00003', '测试验收-v9', 'Pending', '2024-04-12');
INSERT INTO `tasks` VALUES ('TSK000044', 'PROJ23040078', NULL, 'EMP00094', '需求分析-v2', 'Pending', '2023-04-20');
INSERT INTO `tasks` VALUES ('TSK000045', 'PROJ24020015', NULL, 'EMP00018', '测试验收-v7', 'In Progress', '2024-02-21');
INSERT INTO `tasks` VALUES ('TSK000046', 'PROJ23110036', 'TSK000022', 'EMP00046', '原型设计-v2', 'In Progress', '2024-02-14');
INSERT INTO `tasks` VALUES ('TSK000047', 'PROJ24040067', NULL, 'EMP00061', '测试验收-v2', 'In Progress', '2024-05-30');
INSERT INTO `tasks` VALUES ('TSK000048', 'PROJ23100063', NULL, 'EMP00070', '代码开发-v4', 'In Progress', '2023-12-03');
INSERT INTO `tasks` VALUES ('TSK000049', 'PROJ23100005', 'TSK000013', 'EMP00006', '代码开发-v3', 'Pending', '2023-12-08');
INSERT INTO `tasks` VALUES ('TSK000050', 'PROJ23090090', NULL, 'EMP00069', '测试验收-v6', 'In Progress', '2023-10-14');
INSERT INTO `tasks` VALUES ('TSK000051', 'PROJ24010070', NULL, 'EMP00023', '需求分析-v2', 'Pending', '2024-04-24');
INSERT INTO `tasks` VALUES ('TSK000052', 'PROJ23100018', NULL, 'EMP00056', '原型设计-v10', 'In Progress', '2023-12-21');
INSERT INTO `tasks` VALUES ('TSK000053', 'PROJ23090090', 'TSK000050', 'EMP00073', '需求分析-v5', 'Pending', '2023-10-19');
INSERT INTO `tasks` VALUES ('TSK000054', 'PROJ24030025', 'TSK000034', 'EMP00066', '测试验收-v9', 'Pending', '2024-03-26');
INSERT INTO `tasks` VALUES ('TSK000055', 'PROJ24030025', 'TSK000034', 'EMP00075', '原型设计-v9', 'Pending', '2024-05-22');
INSERT INTO `tasks` VALUES ('TSK000056', 'PROJ23080064', NULL, 'EMP00005', '上线部署-v6', 'Pending', '2023-10-10');
INSERT INTO `tasks` VALUES ('TSK000057', 'PROJ23060045', 'TSK000008', 'EMP00055', '上线部署-v7', 'In Progress', '2023-07-01');
INSERT INTO `tasks` VALUES ('TSK000058', 'PROJ24020059', NULL, 'EMP00008', '代码开发-v4', 'Pending', '2024-03-18');
INSERT INTO `tasks` VALUES ('TSK000059', 'PROJ23120058', 'TSK000020', 'EMP00048', '代码开发-v3', 'Completed', '2024-02-22');
INSERT INTO `tasks` VALUES ('TSK000060', 'PROJ23020032', NULL, 'EMP00019', '原型设计-v3', 'Completed', '2023-02-04');
INSERT INTO `tasks` VALUES ('TSK000061', 'PROJ23040072', 'TSK000042', 'EMP00072', '原型设计-v5', 'Completed', '2023-05-17');
INSERT INTO `tasks` VALUES ('TSK000062', 'PROJ23060085', 'TSK000014', 'EMP00001', '需求分析-v2', 'In Progress', '2023-06-12');
INSERT INTO `tasks` VALUES ('TSK000063', 'PROJ23110036', 'TSK000022', 'EMP00080', '原型设计-v2', 'Completed', '2023-12-21');
INSERT INTO `tasks` VALUES ('TSK000064', 'PROJ23040007', NULL, 'EMP00009', '测试验收-v7', 'In Progress', '2023-06-30');
INSERT INTO `tasks` VALUES ('TSK000065', 'PROJ23110036', 'TSK000046', 'EMP00078', '上线部署-v9', 'In Progress', '2023-11-29');
INSERT INTO `tasks` VALUES ('TSK000066', 'PROJ23100004', 'TSK000003', 'EMP00076', '原型设计-v2', 'Completed', '2023-12-04');
INSERT INTO `tasks` VALUES ('TSK000067', 'PROJ23110036', 'TSK000065', 'EMP00078', '上线部署-v2', 'Completed', '2024-02-24');
INSERT INTO `tasks` VALUES ('TSK000068', 'PROJ23090090', NULL, 'EMP00069', '测试验收-v8', 'Completed', '2023-10-05');
INSERT INTO `tasks` VALUES ('TSK000069', 'PROJ24040016', NULL, 'EMP00049', '上线部署-v8', 'Pending', '2024-06-18');
INSERT INTO `tasks` VALUES ('TSK000070', 'PROJ23060034', NULL, 'EMP00040', '上线部署-v1', 'In Progress', '2023-07-13');
INSERT INTO `tasks` VALUES ('TSK000071', 'PROJ24020077', 'TSK000037', 'EMP00079', '代码开发-v9', 'Completed', '2024-05-07');
INSERT INTO `tasks` VALUES ('TSK000072', 'PROJ23060045', NULL, 'EMP00055', '代码开发-v10', 'In Progress', '2023-09-06');
INSERT INTO `tasks` VALUES ('TSK000073', 'PROJ23080088', 'TSK000015', 'EMP00031', '需求分析-v2', 'Completed', '2023-10-25');
INSERT INTO `tasks` VALUES ('TSK000074', 'PROJ23030056', NULL, 'EMP00017', '需求分析-v8', 'Pending', '2023-05-20');
INSERT INTO `tasks` VALUES ('TSK000075', 'PROJ23110053', 'TSK000024', 'EMP00032', '代码开发-v9', 'In Progress', '2023-12-18');
INSERT INTO `tasks` VALUES ('TSK000076', 'PROJ23060009', NULL, 'EMP00007', '原型设计-v4', 'In Progress', '2023-08-19');
INSERT INTO `tasks` VALUES ('TSK000077', 'PROJ23100029', NULL, 'EMP00077', '代码开发-v9', 'Completed', '2023-11-14');
INSERT INTO `tasks` VALUES ('TSK000078', 'PROJ23060012', NULL, 'EMP00049', '原型设计-v5', 'In Progress', '2023-08-31');
INSERT INTO `tasks` VALUES ('TSK000079', 'PROJ23040084', NULL, 'EMP00038', '测试验收-v4', 'Completed', '2023-05-03');
INSERT INTO `tasks` VALUES ('TSK000080', 'PROJ23100022', NULL, 'EMP00074', '代码开发-v10', 'In Progress', '2023-12-27');
INSERT INTO `tasks` VALUES ('TSK000081', 'PROJ23100018', 'TSK000052', 'EMP00014', '上线部署-v8', 'Pending', '2023-12-25');
INSERT INTO `tasks` VALUES ('TSK000082', 'PROJ24010068', NULL, 'EMP00009', '原型设计-v8', 'Completed', '2024-03-02');
INSERT INTO `tasks` VALUES ('TSK000083', 'PROJ23080030', 'TSK000018', 'EMP00003', '测试验收-v7', 'Pending', '2023-11-05');
INSERT INTO `tasks` VALUES ('TSK000084', 'PROJ23020049', NULL, 'EMP00060', '测试验收-v10', 'In Progress', '2023-04-06');
INSERT INTO `tasks` VALUES ('TSK000085', 'PROJ24020054', NULL, 'EMP00023', '需求分析-v9', 'Pending', '2024-04-22');
INSERT INTO `tasks` VALUES ('TSK000086', 'PROJ23070050', NULL, 'EMP00007', '原型设计-v2', 'Completed', '2023-08-11');
INSERT INTO `tasks` VALUES ('TSK000087', 'PROJ24040041', NULL, 'EMP00051', '原型设计-v4', 'In Progress', '2024-04-22');
INSERT INTO `tasks` VALUES ('TSK000088', 'PROJ24020100', NULL, 'EMP00068', '上线部署-v5', 'Completed', '2024-03-20');
INSERT INTO `tasks` VALUES ('TSK000089', 'PROJ23100063', 'TSK000004', 'EMP00047', '测试验收-v1', 'In Progress', '2023-10-12');
INSERT INTO `tasks` VALUES ('TSK000090', 'PROJ23080097', NULL, 'EMP00037', '测试验收-v7', 'Pending', '2023-10-06');
INSERT INTO `tasks` VALUES ('TSK000091', 'PROJ23060085', 'TSK000017', 'EMP00073', '上线部署-v4', 'Completed', '2023-08-11');
INSERT INTO `tasks` VALUES ('TSK000092', 'PROJ23090090', NULL, 'EMP00086', '上线部署-v8', 'Completed', '2023-11-06');
INSERT INTO `tasks` VALUES ('TSK000093', 'PROJ24030087', 'TSK000025', 'EMP00038', '原型设计-v8', 'Pending', '2024-04-03');
INSERT INTO `tasks` VALUES ('TSK000094', 'PROJ23060083', 'TSK000002', 'EMP00026', '原型设计-v1', 'In Progress', '2023-06-23');
INSERT INTO `tasks` VALUES ('TSK000095', 'PROJ23060045', 'TSK000008', 'EMP00055', '测试验收-v6', 'Completed', '2023-09-02');
INSERT INTO `tasks` VALUES ('TSK000096', 'PROJ23040007', NULL, 'EMP00060', '测试验收-v3', 'Completed', '2023-06-19');
INSERT INTO `tasks` VALUES ('TSK000097', 'PROJ23120044', NULL, 'EMP00021', '需求分析-v10', 'In Progress', '2024-01-23');
INSERT INTO `tasks` VALUES ('TSK000098', 'PROJ23040078', 'TSK000044', 'EMP00099', '原型设计-v7', 'Completed', '2023-06-22');
INSERT INTO `tasks` VALUES ('TSK000099', 'PROJ23060085', 'TSK000014', 'EMP00020', '上线部署-v8', 'Pending', '2023-08-20');
INSERT INTO `tasks` VALUES ('TSK000100', 'PROJ23080014', NULL, 'EMP00025', '代码开发-v7', 'Completed', '2023-11-15');
INSERT INTO `tasks` VALUES ('TSK000101', 'PROJ23070050', 'TSK000086', 'EMP00078', '上线部署-v8', 'Pending', '2023-07-28');
INSERT INTO `tasks` VALUES ('TSK000102', 'PROJ23120058', 'TSK000059', 'EMP00096', '代码开发-v5', 'In Progress', '2024-02-12');
INSERT INTO `tasks` VALUES ('TSK000103', 'PROJ24030019', NULL, 'EMP00013', '需求分析-v8', 'Completed', '2024-04-11');
INSERT INTO `tasks` VALUES ('TSK000104', 'PROJ23100029', NULL, 'EMP00018', '上线部署-v7', 'Completed', '2024-01-17');
INSERT INTO `tasks` VALUES ('TSK000105', 'PROJ24040037', 'TSK000029', 'EMP00003', '测试验收-v9', 'Completed', '2024-06-22');
INSERT INTO `tasks` VALUES ('TSK000106', 'PROJ24010020', NULL, 'EMP00055', '代码开发-v6', 'Pending', '2024-02-24');
INSERT INTO `tasks` VALUES ('TSK000107', 'PROJ23060034', 'TSK000070', 'EMP00011', '代码开发-v7', 'Pending', '2023-08-02');
INSERT INTO `tasks` VALUES ('TSK000108', 'PROJ24020100', NULL, 'EMP00096', '代码开发-v2', 'Pending', '2024-04-18');
INSERT INTO `tasks` VALUES ('TSK000109', 'PROJ23020098', NULL, 'EMP00005', '代码开发-v4', 'Pending', '2023-04-06');
INSERT INTO `tasks` VALUES ('TSK000110', 'PROJ23080064', 'TSK000056', 'EMP00005', '上线部署-v7', 'Completed', '2023-09-13');
INSERT INTO `tasks` VALUES ('TSK000111', 'PROJ24050055', 'TSK000033', 'EMP00029', '代码开发-v6', 'In Progress', '2024-06-25');
INSERT INTO `tasks` VALUES ('TSK000112', 'PROJ23120058', NULL, 'EMP00051', '代码开发-v8', 'Pending', '2024-01-12');
INSERT INTO `tasks` VALUES ('TSK000113', 'PROJ23050040', NULL, 'EMP00076', '原型设计-v10', 'In Progress', '2023-07-30');
INSERT INTO `tasks` VALUES ('TSK000114', 'PROJ24040080', NULL, 'EMP00039', '原型设计-v3', 'In Progress', '2024-06-17');
INSERT INTO `tasks` VALUES ('TSK000115', 'PROJ23070035', NULL, 'EMP00003', '测试验收-v1', 'In Progress', '2023-09-05');
INSERT INTO `tasks` VALUES ('TSK000116', 'PROJ23070065', NULL, 'EMP00008', '原型设计-v4', 'Completed', '2023-09-08');
INSERT INTO `tasks` VALUES ('TSK000117', 'PROJ23020049', 'TSK000084', 'EMP00060', '代码开发-v10', 'In Progress', '2023-02-25');
INSERT INTO `tasks` VALUES ('TSK000118', 'PROJ23100086', 'TSK000009', 'EMP00084', '测试验收-v10', 'Completed', '2023-12-07');
INSERT INTO `tasks` VALUES ('TSK000119', 'PROJ23110053', 'TSK000024', 'EMP00056', '代码开发-v9', 'Completed', '2024-02-02');
INSERT INTO `tasks` VALUES ('TSK000120', 'PROJ23110066', NULL, 'EMP00099', '原型设计-v7', 'Completed', '2024-01-28');
INSERT INTO `tasks` VALUES ('TSK000121', 'PROJ23020073', NULL, 'EMP00040', '原型设计-v8', 'Completed', '2023-03-29');
INSERT INTO `tasks` VALUES ('TSK000122', 'PROJ24030087', 'TSK000093', 'EMP00005', '需求分析-v5', 'Completed', '2024-05-30');
INSERT INTO `tasks` VALUES ('TSK000123', 'PROJ23010074', NULL, 'EMP00019', '需求分析-v1', 'Completed', '2023-03-18');
INSERT INTO `tasks` VALUES ('TSK000124', 'PROJ23100018', NULL, 'EMP00014', '上线部署-v10', 'Pending', '2023-11-26');
INSERT INTO `tasks` VALUES ('TSK000125', 'PROJ24010043', 'TSK000010', 'EMP00039', '上线部署-v9', 'In Progress', '2024-03-24');
INSERT INTO `tasks` VALUES ('TSK000126', 'PROJ23060009', 'TSK000076', 'EMP00007', '测试验收-v6', 'In Progress', '2023-07-28');
INSERT INTO `tasks` VALUES ('TSK000127', 'PROJ23070065', 'TSK000116', 'EMP00003', '上线部署-v4', 'Pending', '2023-10-08');
INSERT INTO `tasks` VALUES ('TSK000128', 'PROJ23030010', NULL, 'EMP00059', '测试验收-v10', 'Completed', '2023-03-27');
INSERT INTO `tasks` VALUES ('TSK000129', 'PROJ23060083', 'TSK000002', 'EMP00078', '需求分析-v7', 'Completed', '2023-08-18');
INSERT INTO `tasks` VALUES ('TSK000130', 'PROJ23050040', 'TSK000113', 'EMP00095', '原型设计-v10', 'Completed', '2023-06-18');
INSERT INTO `tasks` VALUES ('TSK000131', 'PROJ24010006', 'TSK000038', 'EMP00074', '原型设计-v3', 'In Progress', '2024-02-04');
INSERT INTO `tasks` VALUES ('TSK000132', 'PROJ24050046', NULL, 'EMP00092', '上线部署-v9', 'Completed', '2024-05-30');
INSERT INTO `tasks` VALUES ('TSK000133', 'PROJ24040080', 'TSK000114', 'EMP00063', '原型设计-v7', 'Pending', '2024-06-12');
INSERT INTO `tasks` VALUES ('TSK000134', 'PROJ23050042', NULL, 'EMP00062', '测试验收-v6', 'Completed', '2023-07-01');
INSERT INTO `tasks` VALUES ('TSK000135', 'PROJ23010099', NULL, 'EMP00060', '原型设计-v6', 'Completed', '2023-01-09');
INSERT INTO `tasks` VALUES ('TSK000136', 'PROJ23050002', NULL, 'EMP00092', '原型设计-v10', 'Completed', '2023-08-03');
INSERT INTO `tasks` VALUES ('TSK000137', 'PROJ24040041', 'TSK000087', 'EMP00042', '测试验收-v10', 'In Progress', '2024-04-12');
INSERT INTO `tasks` VALUES ('TSK000138', 'PROJ24020100', 'TSK000088', 'EMP00024', '代码开发-v7', 'Pending', '2024-03-13');
INSERT INTO `tasks` VALUES ('TSK000139', 'PROJ24040071', 'TSK000021', 'EMP00089', '代码开发-v3', 'Completed', '2024-06-17');
INSERT INTO `tasks` VALUES ('TSK000140', 'PROJ23100018', NULL, 'EMP00012', '代码开发-v5', 'Completed', '2023-12-18');
INSERT INTO `tasks` VALUES ('TSK000141', 'PROJ23100092', NULL, 'EMP00024', '原型设计-v1', 'In Progress', '2023-10-22');
INSERT INTO `tasks` VALUES ('TSK000142', 'PROJ23090021', NULL, 'EMP00033', '需求分析-v9', 'Pending', '2023-09-27');
INSERT INTO `tasks` VALUES ('TSK000143', 'PROJ23060085', 'TSK000099', 'EMP00073', '原型设计-v5', 'Pending', '2023-06-27');
INSERT INTO `tasks` VALUES ('TSK000144', 'PROJ24030025', NULL, 'EMP00066', '需求分析-v1', 'Completed', '2024-04-16');
INSERT INTO `tasks` VALUES ('TSK000145', 'PROJ24040041', 'TSK000137', 'EMP00096', '需求分析-v7', 'Completed', '2024-06-06');
INSERT INTO `tasks` VALUES ('TSK000146', 'PROJ23090026', NULL, 'EMP00043', '测试验收-v2', 'In Progress', '2023-10-14');
INSERT INTO `tasks` VALUES ('TSK000147', 'PROJ23060085', 'TSK000017', 'EMP00086', '测试验收-v5', 'Pending', '2023-06-07');
INSERT INTO `tasks` VALUES ('TSK000148', 'PROJ24010043', NULL, 'EMP00002', '需求分析-v8', 'Pending', '2024-02-08');
INSERT INTO `tasks` VALUES ('TSK000149', 'PROJ23070008', NULL, 'EMP00089', '需求分析-v2', 'Completed', '2023-07-25');
INSERT INTO `tasks` VALUES ('TSK000150', 'PROJ24020077', 'TSK000037', 'EMP00082', '需求分析-v10', 'In Progress', '2024-05-06');
INSERT INTO `tasks` VALUES ('TSK000151', 'PROJ23100018', 'TSK000140', 'EMP00014', '原型设计-v9', 'In Progress', '2023-10-10');
INSERT INTO `tasks` VALUES ('TSK000152', 'PROJ23110036', NULL, 'EMP00093', '原型设计-v9', 'Pending', '2024-02-09');
INSERT INTO `tasks` VALUES ('TSK000153', 'PROJ23070093', 'TSK000006', 'EMP00064', '测试验收-v4', 'In Progress', '2023-09-06');
INSERT INTO `tasks` VALUES ('TSK000154', 'PROJ23060045', 'TSK000057', 'EMP00069', '上线部署-v3', 'Completed', '2023-09-10');
INSERT INTO `tasks` VALUES ('TSK000155', 'PROJ23050042', 'TSK000134', 'EMP00025', '需求分析-v3', 'Pending', '2023-06-22');
INSERT INTO `tasks` VALUES ('TSK000156', 'PROJ24010048', NULL, 'EMP00006', '代码开发-v8', 'Pending', '2024-02-04');
INSERT INTO `tasks` VALUES ('TSK000157', 'PROJ23050040', 'TSK000130', 'EMP00038', '测试验收-v2', 'In Progress', '2023-08-17');
INSERT INTO `tasks` VALUES ('TSK000158', 'PROJ24040041', 'TSK000137', 'EMP00048', '测试验收-v10', 'Completed', '2024-06-28');
INSERT INTO `tasks` VALUES ('TSK000159', 'PROJ23050002', 'TSK000136', 'EMP00014', '代码开发-v4', 'In Progress', '2023-08-03');
INSERT INTO `tasks` VALUES ('TSK000160', 'PROJ24040067', NULL, 'EMP00061', '上线部署-v4', 'Completed', '2024-06-27');
INSERT INTO `tasks` VALUES ('TSK000161', 'PROJ24050027', NULL, 'EMP00009', '上线部署-v3', 'In Progress', '2024-05-13');
INSERT INTO `tasks` VALUES ('TSK000162', 'PROJ23040069', NULL, 'EMP00098', '原型设计-v3', 'Pending', '2023-05-31');
INSERT INTO `tasks` VALUES ('TSK000163', 'PROJ23010076', NULL, 'EMP00027', '需求分析-v7', 'Pending', '2023-04-13');
INSERT INTO `tasks` VALUES ('TSK000164', 'PROJ23030010', NULL, 'EMP00059', '上线部署-v9', 'In Progress', '2023-03-27');
INSERT INTO `tasks` VALUES ('TSK000165', 'PROJ24040080', 'TSK000114', 'EMP00090', '原型设计-v6', 'Pending', '2024-05-01');
INSERT INTO `tasks` VALUES ('TSK000166', 'PROJ23060095', 'TSK000019', 'EMP00097', '需求分析-v2', 'Pending', '2023-07-09');
INSERT INTO `tasks` VALUES ('TSK000167', 'PROJ23040084', 'TSK000079', 'EMP00076', '测试验收-v7', 'Pending', '2023-07-07');
INSERT INTO `tasks` VALUES ('TSK000168', 'PROJ23080097', NULL, 'EMP00084', '上线部署-v1', 'Completed', '2023-08-23');
INSERT INTO `tasks` VALUES ('TSK000169', 'PROJ23060095', 'TSK000019', 'EMP00081', '代码开发-v8', 'Completed', '2023-08-18');
INSERT INTO `tasks` VALUES ('TSK000170', 'PROJ23070008', 'TSK000149', 'EMP00089', '需求分析-v7', 'In Progress', '2023-07-31');
INSERT INTO `tasks` VALUES ('TSK000171', 'PROJ23040078', 'TSK000044', 'EMP00095', '原型设计-v10', 'Pending', '2023-05-09');
INSERT INTO `tasks` VALUES ('TSK000172', 'PROJ23100022', NULL, 'EMP00075', '需求分析-v10', 'Completed', '2023-12-05');
INSERT INTO `tasks` VALUES ('TSK000173', 'PROJ23040061', NULL, 'EMP00054', '测试验收-v3', 'Pending', '2023-05-04');
INSERT INTO `tasks` VALUES ('TSK000174', 'PROJ23010091', NULL, 'EMP00046', '测试验收-v5', 'In Progress', '2023-02-08');
INSERT INTO `tasks` VALUES ('TSK000175', 'PROJ24020015', 'TSK000045', 'EMP00009', '代码开发-v2', 'Pending', '2024-04-05');
INSERT INTO `tasks` VALUES ('TSK000176', 'PROJ23110053', NULL, 'EMP00063', '测试验收-v10', 'In Progress', '2024-01-08');
INSERT INTO `tasks` VALUES ('TSK000177', 'PROJ23020032', 'TSK000060', 'EMP00019', '原型设计-v10', 'Completed', '2023-04-25');
INSERT INTO `tasks` VALUES ('TSK000178', 'PROJ24010043', 'TSK000125', 'EMP00066', '原型设计-v5', 'In Progress', '2024-04-04');
INSERT INTO `tasks` VALUES ('TSK000179', 'PROJ23100029', 'TSK000077', 'EMP00060', '测试验收-v1', 'Pending', '2023-12-30');
INSERT INTO `tasks` VALUES ('TSK000180', 'PROJ23100029', NULL, 'EMP00097', '测试验收-v6', 'In Progress', '2023-12-13');
INSERT INTO `tasks` VALUES ('TSK000181', 'PROJ23100022', 'TSK000080', 'EMP00002', '测试验收-v1', 'Pending', '2023-12-13');
INSERT INTO `tasks` VALUES ('TSK000182', 'PROJ23070093', 'TSK000006', 'EMP00072', '原型设计-v6', 'Pending', '2023-09-03');
INSERT INTO `tasks` VALUES ('TSK000183', 'PROJ23040061', 'TSK000173', 'EMP00016', '代码开发-v10', 'In Progress', '2023-06-23');
INSERT INTO `tasks` VALUES ('TSK000184', 'PROJ23080088', NULL, 'EMP00100', '上线部署-v6', 'Pending', '2023-09-01');
INSERT INTO `tasks` VALUES ('TSK000185', 'PROJ23040007', 'TSK000096', 'EMP00097', '原型设计-v10', 'Pending', '2023-05-04');
INSERT INTO `tasks` VALUES ('TSK000186', 'PROJ24030087', 'TSK000025', 'EMP00071', '需求分析-v6', 'Pending', '2024-05-22');
INSERT INTO `tasks` VALUES ('TSK000187', 'PROJ23120044', 'TSK000097', 'EMP00093', '代码开发-v6', 'Completed', '2024-01-02');
INSERT INTO `tasks` VALUES ('TSK000188', 'PROJ23040078', NULL, 'EMP00099', '测试验收-v9', 'Pending', '2023-06-26');
INSERT INTO `tasks` VALUES ('TSK000189', 'PROJ23090021', 'TSK000142', 'EMP00062', '上线部署-v6', 'In Progress', '2023-11-21');
INSERT INTO `tasks` VALUES ('TSK000190', 'PROJ24020100', 'TSK000108', 'EMP00037', '代码开发-v1', 'Completed', '2024-03-30');
INSERT INTO `tasks` VALUES ('TSK000191', 'PROJ23070011', NULL, 'EMP00088', '测试验收-v4', 'In Progress', '2023-07-25');
INSERT INTO `tasks` VALUES ('TSK000192', 'PROJ23040084', NULL, 'EMP00076', '需求分析-v6', 'In Progress', '2023-05-17');
INSERT INTO `tasks` VALUES ('TSK000193', 'PROJ23090026', 'TSK000146', 'EMP00082', '测试验收-v3', 'In Progress', '2023-10-01');
INSERT INTO `tasks` VALUES ('TSK000194', 'PROJ23070093', 'TSK000153', 'EMP00049', '需求分析-v6', 'Pending', '2023-07-24');
INSERT INTO `tasks` VALUES ('TSK000195', 'PROJ23110053', 'TSK000075', 'EMP00052', '代码开发-v6', 'Completed', '2024-02-24');
INSERT INTO `tasks` VALUES ('TSK000196', 'PROJ23100086', NULL, 'EMP00100', '需求分析-v8', 'In Progress', '2024-01-23');
INSERT INTO `tasks` VALUES ('TSK000197', 'PROJ24010051', 'TSK000007', 'EMP00010', '测试验收-v7', 'Pending', '2024-02-29');
INSERT INTO `tasks` VALUES ('TSK000198', 'PROJ23020049', 'TSK000117', 'EMP00097', '原型设计-v5', 'Pending', '2023-03-06');
INSERT INTO `tasks` VALUES ('TSK000199', 'PROJ24010068', 'TSK000082', 'EMP00081', '需求分析-v3', 'In Progress', '2024-02-18');
INSERT INTO `tasks` VALUES ('TSK000200', 'PROJ23010076', NULL, 'EMP00012', '测试验收-v6', 'In Progress', '2023-02-21');
INSERT INTO `tasks` VALUES ('TSK000201', 'PROJ24030019', 'TSK000103', 'EMP00070', '原型设计-v3', 'Completed', '2024-04-26');
INSERT INTO `tasks` VALUES ('TSK000202', 'PROJ23010074', 'TSK000123', 'EMP00060', '测试验收-v5', 'Completed', '2023-02-19');
INSERT INTO `tasks` VALUES ('TSK000203', 'PROJ23080014', 'TSK000100', 'EMP00091', '原型设计-v4', 'In Progress', '2023-08-20');
INSERT INTO `tasks` VALUES ('TSK000204', 'PROJ23110066', NULL, 'EMP00094', '上线部署-v8', 'Pending', '2023-12-08');
INSERT INTO `tasks` VALUES ('TSK000205', 'PROJ24040080', 'TSK000133', 'EMP00052', '原型设计-v6', 'In Progress', '2024-04-27');
INSERT INTO `tasks` VALUES ('TSK000206', 'PROJ23040072', 'TSK000042', 'EMP00087', '测试验收-v2', 'In Progress', '2023-05-13');
INSERT INTO `tasks` VALUES ('TSK000207', 'PROJ23120058', 'TSK000059', 'EMP00024', '需求分析-v10', 'In Progress', '2024-01-10');
INSERT INTO `tasks` VALUES ('TSK000208', 'PROJ23110053', NULL, 'EMP00039', '测试验收-v8', 'Completed', '2023-12-14');
INSERT INTO `tasks` VALUES ('TSK000209', 'PROJ23020073', 'TSK000121', 'EMP00082', '代码开发-v9', 'In Progress', '2023-04-13');
INSERT INTO `tasks` VALUES ('TSK000210', 'PROJ23100018', 'TSK000151', 'EMP00039', '测试验收-v2', 'In Progress', '2023-12-03');
INSERT INTO `tasks` VALUES ('TSK000211', 'PROJ24010020', 'TSK000106', 'EMP00028', '代码开发-v6', 'Pending', '2024-03-11');
INSERT INTO `tasks` VALUES ('TSK000212', 'PROJ23010076', NULL, 'EMP00027', '上线部署-v7', 'In Progress', '2023-02-26');
INSERT INTO `tasks` VALUES ('TSK000213', 'PROJ23030010', 'TSK000164', 'EMP00047', '代码开发-v3', 'In Progress', '2023-05-07');
INSERT INTO `tasks` VALUES ('TSK000214', 'PROJ23100086', 'TSK000009', 'EMP00068', '原型设计-v7', 'In Progress', '2024-01-22');
INSERT INTO `tasks` VALUES ('TSK000215', 'PROJ23060083', 'TSK000094', 'EMP00078', '需求分析-v6', 'In Progress', '2023-09-08');
INSERT INTO `tasks` VALUES ('TSK000216', 'PROJ23060083', NULL, 'EMP00093', '需求分析-v3', 'Pending', '2023-07-17');
INSERT INTO `tasks` VALUES ('TSK000217', 'PROJ23040047', NULL, 'EMP00095', '代码开发-v1', 'In Progress', '2023-06-07');
INSERT INTO `tasks` VALUES ('TSK000218', 'PROJ23110003', NULL, 'EMP00085', '代码开发-v6', 'In Progress', '2023-12-19');
INSERT INTO `tasks` VALUES ('TSK000219', 'PROJ23040072', 'TSK000061', 'EMP00091', '代码开发-v5', 'In Progress', '2023-07-02');
INSERT INTO `tasks` VALUES ('TSK000220', 'PROJ23080030', NULL, 'EMP00008', '上线部署-v7', 'Pending', '2023-10-18');
INSERT INTO `tasks` VALUES ('TSK000221', 'PROJ23020073', 'TSK000209', 'EMP00082', '代码开发-v6', 'Pending', '2023-05-02');
INSERT INTO `tasks` VALUES ('TSK000222', 'PROJ23120058', 'TSK000102', 'EMP00031', '代码开发-v9', 'Completed', '2024-03-11');
INSERT INTO `tasks` VALUES ('TSK000223', 'PROJ23050040', 'TSK000157', 'EMP00038', '代码开发-v2', 'Pending', '2023-07-13');
INSERT INTO `tasks` VALUES ('TSK000224', 'PROJ23100022', NULL, 'EMP00002', '上线部署-v4', 'Pending', '2024-01-01');
INSERT INTO `tasks` VALUES ('TSK000225', 'PROJ24030019', 'TSK000103', 'EMP00050', '需求分析-v1', 'Pending', '2024-05-13');
INSERT INTO `tasks` VALUES ('TSK000226', 'PROJ24040067', 'TSK000160', 'EMP00023', '代码开发-v4', 'In Progress', '2024-07-07');
INSERT INTO `tasks` VALUES ('TSK000227', 'PROJ23110079', 'TSK000032', 'EMP00089', '原型设计-v9', 'Pending', '2023-11-26');
INSERT INTO `tasks` VALUES ('TSK000228', 'PROJ24010006', NULL, 'EMP00014', '代码开发-v9', 'In Progress', '2024-03-24');
INSERT INTO `tasks` VALUES ('TSK000229', 'PROJ24010020', 'TSK000106', 'EMP00061', '原型设计-v9', 'In Progress', '2024-04-11');
INSERT INTO `tasks` VALUES ('TSK000230', 'PROJ24050027', 'TSK000161', 'EMP00077', '需求分析-v4', 'In Progress', '2024-07-04');
INSERT INTO `tasks` VALUES ('TSK000231', 'PROJ23050001', NULL, 'EMP00096', '需求分析-v4', 'In Progress', '2023-06-06');
INSERT INTO `tasks` VALUES ('TSK000232', 'PROJ23070008', NULL, 'EMP00013', '需求分析-v3', 'Completed', '2023-10-03');
INSERT INTO `tasks` VALUES ('TSK000233', 'PROJ23020098', 'TSK000109', 'EMP00038', '上线部署-v6', 'Completed', '2023-03-27');
INSERT INTO `tasks` VALUES ('TSK000234', 'PROJ24050055', 'TSK000033', 'EMP00039', '需求分析-v6', 'Pending', '2024-07-03');
INSERT INTO `tasks` VALUES ('TSK000235', 'PROJ24010051', 'TSK000197', 'EMP00026', '测试验收-v10', 'Completed', '2024-03-31');
INSERT INTO `tasks` VALUES ('TSK000236', 'PROJ24010070', NULL, 'EMP00086', '代码开发-v9', 'Completed', '2024-01-27');
INSERT INTO `tasks` VALUES ('TSK000237', 'PROJ23120052', NULL, 'EMP00039', '上线部署-v9', 'Pending', '2023-12-28');
INSERT INTO `tasks` VALUES ('TSK000238', 'PROJ23050001', 'TSK000231', 'EMP00084', '上线部署-v1', 'Completed', '2023-05-24');
INSERT INTO `tasks` VALUES ('TSK000239', 'PROJ24010043', 'TSK000125', 'EMP00014', '上线部署-v1', 'Pending', '2024-03-16');
INSERT INTO `tasks` VALUES ('TSK000240', 'PROJ23060045', NULL, 'EMP00020', '测试验收-v5', 'In Progress', '2023-08-10');
INSERT INTO `tasks` VALUES ('TSK000241', 'PROJ23100022', 'TSK000224', 'EMP00039', '原型设计-v4', 'In Progress', '2024-01-06');
INSERT INTO `tasks` VALUES ('TSK000242', 'PROJ23100029', 'TSK000077', 'EMP00036', '原型设计-v9', 'Pending', '2024-01-24');
INSERT INTO `tasks` VALUES ('TSK000243', 'PROJ24050046', 'TSK000132', 'EMP00039', '上线部署-v3', 'In Progress', '2024-05-10');
INSERT INTO `tasks` VALUES ('TSK000244', 'PROJ24040041', NULL, 'EMP00024', '测试验收-v10', 'Completed', '2024-05-07');
INSERT INTO `tasks` VALUES ('TSK000245', 'PROJ24040039', NULL, 'EMP00006', '上线部署-v6', 'Completed', '2024-05-21');
INSERT INTO `tasks` VALUES ('TSK000246', 'PROJ23040094', NULL, 'EMP00099', '需求分析-v9', 'In Progress', '2023-06-11');
INSERT INTO `tasks` VALUES ('TSK000247', 'PROJ24010043', 'TSK000178', 'EMP00092', '上线部署-v9', 'Completed', '2024-02-12');
INSERT INTO `tasks` VALUES ('TSK000248', 'PROJ23040084', NULL, 'EMP00038', '需求分析-v9', 'Completed', '2023-05-29');
INSERT INTO `tasks` VALUES ('TSK000249', 'PROJ24010020', 'TSK000229', 'EMP00023', '代码开发-v8', 'Pending', '2024-04-09');
INSERT INTO `tasks` VALUES ('TSK000250', 'PROJ23100004', 'TSK000003', 'EMP00005', '代码开发-v9', 'Completed', '2024-01-21');
INSERT INTO `tasks` VALUES ('TSK000251', 'PROJ24040016', 'TSK000069', 'EMP00015', '需求分析-v3', 'In Progress', '2024-06-28');
INSERT INTO `tasks` VALUES ('TSK000252', 'PROJ23010099', NULL, 'EMP00018', '代码开发-v1', 'Completed', '2023-02-20');
INSERT INTO `tasks` VALUES ('TSK000253', 'PROJ23050042', 'TSK000134', 'EMP00072', '代码开发-v6', 'Completed', '2023-06-15');
INSERT INTO `tasks` VALUES ('TSK000254', 'PROJ23040060', 'TSK000023', 'EMP00003', '上线部署-v5', 'In Progress', '2023-07-06');
INSERT INTO `tasks` VALUES ('TSK000255', 'PROJ24040039', 'TSK000245', 'EMP00003', '原型设计-v4', 'In Progress', '2024-05-17');
INSERT INTO `tasks` VALUES ('TSK000256', 'PROJ23020057', NULL, 'EMP00035', '上线部署-v1', 'Pending', '2023-04-28');
INSERT INTO `tasks` VALUES ('TSK000257', 'PROJ23080064', 'TSK000056', 'EMP00071', '代码开发-v4', 'Completed', '2023-10-08');
INSERT INTO `tasks` VALUES ('TSK000258', 'PROJ23030010', 'TSK000027', 'EMP00070', '需求分析-v2', 'In Progress', '2023-05-14');
INSERT INTO `tasks` VALUES ('TSK000259', 'PROJ23090090', 'TSK000050', 'EMP00055', '上线部署-v10', 'Pending', '2023-11-16');
INSERT INTO `tasks` VALUES ('TSK000260', 'PROJ23120052', NULL, 'EMP00012', '原型设计-v3', 'Pending', '2024-01-30');
INSERT INTO `tasks` VALUES ('TSK000261', 'PROJ23060028', 'TSK000028', 'EMP00004', '测试验收-v9', 'Completed', '2023-06-20');
INSERT INTO `tasks` VALUES ('TSK000262', 'PROJ24040067', 'TSK000160', 'EMP00028', '上线部署-v8', 'Pending', '2024-05-15');
INSERT INTO `tasks` VALUES ('TSK000263', 'PROJ23020073', 'TSK000121', 'EMP00079', '需求分析-v2', 'In Progress', '2023-04-28');
INSERT INTO `tasks` VALUES ('TSK000264', 'PROJ24040041', NULL, 'EMP00096', '原型设计-v9', 'In Progress', '2024-06-16');
INSERT INTO `tasks` VALUES ('TSK000265', 'PROJ24010006', 'TSK000131', 'EMP00092', '代码开发-v1', 'Pending', '2024-03-03');
INSERT INTO `tasks` VALUES ('TSK000266', 'PROJ24040089', NULL, 'EMP00076', '测试验收-v3', 'Pending', '2024-05-10');
INSERT INTO `tasks` VALUES ('TSK000267', 'PROJ23060075', 'TSK000030', 'EMP00095', '需求分析-v3', 'Completed', '2023-06-21');
INSERT INTO `tasks` VALUES ('TSK000268', 'PROJ23030056', NULL, 'EMP00048', '上线部署-v8', 'Completed', '2023-06-03');
INSERT INTO `tasks` VALUES ('TSK000269', 'PROJ24020015', 'TSK000045', 'EMP00081', '代码开发-v4', 'Pending', '2024-03-08');
INSERT INTO `tasks` VALUES ('TSK000270', 'PROJ23020049', 'TSK000117', 'EMP00097', '代码开发-v1', 'Pending', '2023-04-14');
INSERT INTO `tasks` VALUES ('TSK000271', 'PROJ24020031', 'TSK000039', 'EMP00082', '需求分析-v9', 'Completed', '2024-02-29');
INSERT INTO `tasks` VALUES ('TSK000272', 'PROJ24040039', NULL, 'EMP00008', '需求分析-v9', 'Pending', '2024-05-16');
INSERT INTO `tasks` VALUES ('TSK000273', 'PROJ24030019', 'TSK000201', 'EMP00070', '上线部署-v1', 'Completed', '2024-04-10');
INSERT INTO `tasks` VALUES ('TSK000274', 'PROJ23070008', 'TSK000149', 'EMP00047', '测试验收-v3', 'Pending', '2023-08-14');
INSERT INTO `tasks` VALUES ('TSK000275', 'PROJ24020015', 'TSK000045', 'EMP00018', '代码开发-v2', 'Completed', '2024-05-02');
INSERT INTO `tasks` VALUES ('TSK000276', 'PROJ23090017', NULL, 'EMP00040', '测试验收-v6', 'Completed', '2023-11-27');
INSERT INTO `tasks` VALUES ('TSK000277', 'PROJ23060034', 'TSK000107', 'EMP00045', '需求分析-v5', 'Completed', '2023-09-04');
INSERT INTO `tasks` VALUES ('TSK000278', 'PROJ23060083', 'TSK000215', 'EMP00057', '测试验收-v4', 'In Progress', '2023-08-30');
INSERT INTO `tasks` VALUES ('TSK000279', 'PROJ23080064', 'TSK000257', 'EMP00094', '上线部署-v5', 'In Progress', '2023-09-27');
INSERT INTO `tasks` VALUES ('TSK000280', 'PROJ23080014', NULL, 'EMP00034', '原型设计-v2', 'In Progress', '2023-08-29');
INSERT INTO `tasks` VALUES ('TSK000281', 'PROJ23110066', 'TSK000204', 'EMP00038', '代码开发-v7', 'Completed', '2023-12-08');
INSERT INTO `tasks` VALUES ('TSK000282', 'PROJ24020100', 'TSK000190', 'EMP00042', '代码开发-v8', 'Pending', '2024-05-15');
INSERT INTO `tasks` VALUES ('TSK000283', 'PROJ24020031', 'TSK000035', 'EMP00079', '代码开发-v7', 'In Progress', '2024-04-16');
INSERT INTO `tasks` VALUES ('TSK000284', 'PROJ24010043', NULL, 'EMP00039', '测试验收-v3', 'Completed', '2024-04-08');
INSERT INTO `tasks` VALUES ('TSK000285', 'PROJ23080088', 'TSK000184', 'EMP00068', '代码开发-v7', 'Completed', '2023-09-28');
INSERT INTO `tasks` VALUES ('TSK000286', 'PROJ23040078', 'TSK000171', 'EMP00076', '代码开发-v2', 'Pending', '2023-05-06');
INSERT INTO `tasks` VALUES ('TSK000287', 'PROJ23070035', 'TSK000115', 'EMP00054', '测试验收-v9', 'In Progress', '2023-10-03');
INSERT INTO `tasks` VALUES ('TSK000288', 'PROJ23070050', NULL, 'EMP00007', '代码开发-v3', 'Completed', '2023-09-22');
INSERT INTO `tasks` VALUES ('TSK000289', 'PROJ23020013', NULL, 'EMP00001', '原型设计-v3', 'In Progress', '2023-05-09');
INSERT INTO `tasks` VALUES ('TSK000290', 'PROJ23020098', 'TSK000233', 'EMP00071', '需求分析-v2', 'Pending', '2023-03-18');
INSERT INTO `tasks` VALUES ('TSK000291', 'PROJ23030010', 'TSK000213', 'EMP00089', '代码开发-v4', 'Completed', '2023-03-13');
INSERT INTO `tasks` VALUES ('TSK000292', 'PROJ24050027', NULL, 'EMP00097', '需求分析-v6', 'Pending', '2024-07-07');
INSERT INTO `tasks` VALUES ('TSK000293', 'PROJ23090026', 'TSK000146', 'EMP00098', '测试验收-v7', 'Completed', '2023-09-19');
INSERT INTO `tasks` VALUES ('TSK000294', 'PROJ24040039', 'TSK000272', 'EMP00003', '代码开发-v4', 'Pending', '2024-05-19');
INSERT INTO `tasks` VALUES ('TSK000295', 'PROJ23090021', 'TSK000189', 'EMP00049', '测试验收-v5', 'Pending', '2023-09-30');
INSERT INTO `tasks` VALUES ('TSK000296', 'PROJ24010070', NULL, 'EMP00061', '上线部署-v10', 'Pending', '2024-02-12');
INSERT INTO `tasks` VALUES ('TSK000297', 'PROJ23100029', 'TSK000104', 'EMP00009', '原型设计-v9', 'Pending', '2024-01-23');
INSERT INTO `tasks` VALUES ('TSK000298', 'PROJ23120044', 'TSK000097', 'EMP00057', '代码开发-v8', 'Pending', '2024-03-05');
INSERT INTO `tasks` VALUES ('TSK000299', 'PROJ24020100', 'TSK000088', 'EMP00084', '代码开发-v6', 'Pending', '2024-03-09');
INSERT INTO `tasks` VALUES ('TSK000300', 'PROJ23040007', NULL, 'EMP00036', '上线部署-v1', 'In Progress', '2023-04-21');

-- ----------------------------
-- Procedure structure for GenerateProjectData
-- ----------------------------
DROP PROCEDURE IF EXISTS `GenerateProjectData`;
delimiter ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `GenerateProjectData`()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE v_start_date DATE;
  DECLARE v_end_date DATE;
  DECLARE v_emp_id VARCHAR(10);

  WHILE i <= 100 DO
    -- 生成时间范围（确保end_date > start_date）
    SET v_start_date = DATE('2023-01-01') + INTERVAL FLOOR(RAND()*500) DAY;
    SET v_end_date = CASE
      WHEN RAND() > 0.4 THEN v_start_date + INTERVAL FLOOR(30 + RAND()*300) DAY
      ELSE NULL
    END;

    -- 获取部门信息
    SELECT dept_id, dept_name
    INTO @v_dept_id, @v_dept_name
    FROM departments
    ORDER BY RAND()
    LIMIT 1;

    -- 获取负责人（部门无员工时随机选择）
    SET v_emp_id = IFNULL(
      (SELECT emp_id FROM employees WHERE dept_id = @v_dept_id ORDER BY RAND() LIMIT 1),
      (SELECT emp_id FROM employees ORDER BY RAND() LIMIT 1)
    );

    INSERT INTO projects (
      project_id,
      project_name,
      dept_id,
      lead_emp_id,
      start_date,
      end_date
    ) VALUES (
      CONCAT('PROJ', DATE_FORMAT(v_start_date, '%y%m'), LPAD(i, 4, '0')),
      CONCAT(
        @v_dept_name,
        CASE WHEN RAND() > 0.5 THEN '年度计划' ELSE '季度冲刺' END,
        FLOOR(RAND()*1000)+1, '号'
      ),
      @v_dept_id,
      v_emp_id,
      v_start_date,
      v_end_date
    );

    SET i = i + 1;
  END WHILE;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

---------创建数据库、表、插入数据----------------------

-- 建表
-- 学生表
-- 创建学生表
CREATE TABLE Student(
    s_id VARCHAR(20) PRIMARY KEY COMMENT '学生编号',
    s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
    s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
    s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

-- 创建课程表
CREATE TABLE Course(
    c_id VARCHAR(20) PRIMARY KEY COMMENT '课程编号',
    c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
    t_id VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师编号', -- 添加 NOT NULL 约束和默认值
    FOREIGN KEY (t_id) REFERENCES Teacher(t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

-- 创建教师表
CREATE TABLE Teacher(
    t_id VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
    t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';

-- 创建成绩表
CREATE TABLE Score(
    s_id VARCHAR(20) COMMENT '学生编号',
    c_id  VARCHAR(20) COMMENT '课程编号',
    s_score INT(3) COMMENT '分数',
    PRIMARY KEY(s_id,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';

-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 插入课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 插入教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 插入成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
--------------------------------------------------

---------创建数据库、表、插入数据----------------------

-- 建表
-- 学生表
-- 创建学生表
CREATE TABLE Student(
    s_id VARCHAR(20) PRIMARY KEY COMMENT '学生编号',
    s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
    s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
    s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';

-- 创建课程表
CREATE TABLE Course(
    c_id VARCHAR(20) PRIMARY KEY COMMENT '课程编号',
    c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
    t_id VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师编号', -- 添加 NOT NULL 约束和默认值
    FOREIGN KEY (t_id) REFERENCES Teacher(t_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';

-- 创建教师表
CREATE TABLE Teacher(
    t_id VARCHAR(20) PRIMARY KEY COMMENT '教师编号',
    t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';

-- 创建成绩表
CREATE TABLE Score(
    s_id VARCHAR(20) COMMENT '学生编号',
    c_id  VARCHAR(20) COMMENT '课程编号',
    s_score INT(3) COMMENT '分数',
    PRIMARY KEY(s_id,c_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';

-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 插入课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 插入教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 插入成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
--------------------------------------------------

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
```sql
SELECT 
  Student.s_id, 
  Student.s_name, 
  Student.s_birth, 
  Student.s_sex, 
  Score01.s_score AS score_01, 
  Score02.s_score AS score_02
FROM 
  Student
INNER JOIN Score AS Score01 ON Student.s_id = Score01.s_id AND Score01.c_id = '01'  
INNER JOIN Score AS Score02 ON Student.s_id = Score02.s_id AND Score02.c_id = '02' 
WHERE 
  Score01.s_score > Score02.s_score;
```

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
```sql
SELECT 
  Student.s_id, 
  Student.s_name, 
  Student.s_birth, 
  Student.s_sex, 
  Score01.s_score AS score_01, 
  Score02.s_score AS score_02
FROM 
  Student
INNER JOIN Score AS Score01 ON Student.s_id = Score01.s_id AND Score01.c_id = '01'  
INNER JOIN Score AS Score02 ON Student.s_id = Score02.s_id AND Score02.c_id = '02' 
WHERE 
  Score01.s_score < Score02.s_score;
```

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
```sql
SELECT 
  Student.s_id, 
  Student.s_name, 
  AVG(Score.s_score) AS avg_score
FROM 
  Student
INNER JOIN Score ON Student.s_id = Score.s_id
GROUP BY 
  Student.s_id
HAVING 
  AVG(Score.s_score) >= 60;
```

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的）
```sql
SELECT 
  Student.s_id, 
  Student.s_name, 
  IFNULL(AVG(Score.s_score), 0) AS avg_score
FROM 
  Student
LEFT JOIN Score ON Student.s_id = Score.s_id
GROUP BY 
  Student.s_id
HAVING 
  IFNULL(AVG(Score.s_score), 0) < 60;
```

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，并从高到低排序
```sql
SELECT 
  Student.s_id,
  Student.s_name, 
  COUNT(Score.c_id) AS course_count, 
  SUM(Score.s_score) AS total_score
FROM 
  Student
LEFT JOIN Score ON Student.s_id = Score.s_id
GROUP BY 
  Student.s_id
ORDER BY 
  total_score DESC;
```

6、查询"李"姓老师的数量
```sql
SELECT 
  COUNT(*) AS li_teacher_count
FROM 
  Teacher
WHERE 
  t_name LIKE '李%';
```

7、查询学过"张三"老师授课的同学的信息
```sql
SELECT 
  DISTINCT Student.s_id, 
  Student.s_name, 
  Student.s_birth, 
  Student.s_sex
FROM 
  Teacher
INNER JOIN Course ON Teacher.t_id = Course.t_id AND Teacher.t_name = '张三'
INNER JOIN Score ON Course.c_id = Score.c_id
INNER JOIN Student ON Score.s_id = Student.s_id;
```

8. 查询没学过"张三"老师授课的同学的信息。
```sql
SELECT *
FROM Student
WHERE s_id NOT IN (
    SELECT Score.s_id
    FROM Score
    JOIN Course ON Score.c_id = Course.c_id
    JOIN Teacher ON Course.t_id = Teacher.t_id
    WHERE Teacher.t_name = '张三'
);
```

9. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息，及两门课程成绩。
```sql
SELECT Student.*, Score01.s_score AS score01, Score02.s_score AS score02
FROM Student
JOIN Score AS Score01 ON Student.s_id = Score01.s_id AND Score01.c_id = '01'
JOIN Score AS Score02 ON Student.s_id = Score02.s_id AND Score02.c_id = '02';
```

10. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
```sql
SELECT *
FROM Student
WHERE s_id IN (
    SELECT s_id
    FROM Score
    WHERE c_id = '01'
)
AND s_id NOT IN (
    SELECT s_id
    FROM Score
    WHERE c_id = '02'
);
```

11. 查询没有学全所有课程的同学的信息。
```sql
SELECT *
FROM Student
WHERE s_id NOT IN (
    SELECT s_id
    FROM Score
    GROUP BY s_id
    HAVING COUNT(DISTINCT c_id) = (SELECT COUNT(*) FROM Course)
);
```

12. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
```sql
SELECT DISTINCT Student.*
FROM Student
JOIN Score ON Student.s_id = Score.s_id
WHERE Score.c_id IN (
    SELECT c_id
    FROM Score
    WHERE s_id = '01'
)
AND Student.s_id != '01';
```

13. 查询和"01"号的同学学习的课程完全相同的其他同学的信息。
```sql
SELECT DISTINCT s1.*
FROM Student s1
JOIN Score sc1 ON s1.s_id = sc1.s_id
WHERE sc1.c_id IN (
    SELECT sc2.c_id
    FROM Score sc2
    WHERE sc2.s_id = '01'
)
AND s1.s_id NOT IN (
    SELECT s_id
    FROM Score
    WHERE c_id NOT IN (
        SELECT c_id
        FROM Score
        WHERE s_id = '01'
    )
)
AND s1.s_id != '01'
```

15. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
```sql
SELECT Student.s_id, Student.s_name, AVG(Score.s_score) AS avg_score
FROM Student
JOIN Score ON Student.s_id = Score.s_id
GROUP BY Student.s_id, Student.s_name
HAVING SUM(Score.s_score < 60) >= 2;
```

16. 查询"01"课程分数小于60，按分数降序排列的学生信息及01分数
```sql
SELECT s.s_id, s.s_name, s.s_birth, s.s_sex, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
WHERE sc.c_id = '01' AND sc.s_score < 60
ORDER BY sc.s_score DESC;
```

17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
```sql
SELECT s.s_id, s.s_name, AVG(sc.s_score) AS avg_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
GROUP BY s.s_id, s.s_name
ORDER BY avg_score DESC;
```

18. 查询各科成绩最高分、最低分和平均分及及格率、中等率、优良率、优秀率
```sql
SELECT
    c.c_id,
    c.c_name,
    MAX(sc.s_score) AS max_score,
    MIN(sc.s_score) AS min_score,
    AVG(sc.s_score) AS avg_score,
    SUM(sc.s_score >= 60) / COUNT(sc.s_id) AS pass_rate,
    SUM(sc.s_score BETWEEN 70 AND 80) / COUNT(sc.s_id) AS middle_rate,
    SUM(sc.s_score BETWEEN 80 AND 90) / COUNT(sc.s_id) AS good_rate,
    SUM(sc.s_score >= 90) / COUNT(sc.s_id) AS excellent_rate
FROM Course AS c
LEFT JOIN Score AS sc ON c.c_id = sc.c_id
GROUP BY c.c_id, c.c_name;
```

20、查询学生的总成绩并进行排名
```sql
SELECT s.s_id, s.s_name, SUM(sc.s_score) AS total_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY total_score DESC;
```

21、查询不同老师所教不同课程平均分从高到低显示
```sql
SELECT t.t_id, t.t_name, c.c_name, AVG(sc.s_score) AS avg_score
FROM Teacher t
JOIN Course c ON t.t_id = c.t_id
JOIN Score sc ON c.c_id = sc.c_id
GROUP BY c.c_id
ORDER BY avg_score DESC;
```


23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
```sql
SELECT 
    c.c_id,
    c.c_name,
    SUM(CASE WHEN sc.s_score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS `[100-85]`,
    SUM(CASE WHEN sc.s_score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS `[85-70]`,
    SUM(CASE WHEN sc.s_score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS `[70-60]`,
    SUM(CASE WHEN sc.s_score < 60 THEN 1 ELSE 0 END) AS `[0-60]`
FROM Course c
JOIN Score sc ON c.c_id = sc.c_id
GROUP BY c.c_id;
```
备注: 如果你需要百分比，你可能需要对每个分数段的人数除以总人数进行计算。

24、查询学生平均成绩及其名次
```sql
SELECT s.s_id, s.s_name, AVG(sc.s_score) AS avg_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
ORDER BY avg_score DESC;
```

19. 按各科成绩进行排序，并显示排名
首先，需要注意在MySQL中使用变量来计算排名是一个常见的做法。对于每个课程，我们可以这样进行排序并给出排名：

```sql
SELECT 
    s_id,
    c_id,
    s_score,
    RANK() OVER (
        PARTITION BY c_id
        ORDER BY s_score DESC
    ) as score_rank
FROM 
    Score
ORDER BY
    c_id,
    s_score DESC;
```

22. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
MySQL中没有内置的方法来直接选择一个范围内的排名，但我们可以通过上一步的查询结果再进行筛选，比如：

```sql
SELECT *
FROM (
    SELECT s_id,
           c_id,
           s_score,
           @rank := IF(@prev_c_id = c_id, @rank + 1, 1) AS `rank`,
           @prev_c_id := c_id
    FROM Score,
         (SELECT @rank := 0, @prev_c_id := '') AS vars
    ORDER BY c_id, s_score DESC
) ranked_scores
WHERE `rank` BETWEEN 2 AND 3;
```

24.1 添加名次rank，（相同分数的相同名次，并列排名）

```sql
SELECT s_id,
       c_id,
       s_score,
       DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS `rank`
FROM Score;
```

25. 查询各科成绩前三名的记录
利用窗口函数的能力，我们可以很方便地实现这一点：

```sql
SELECT *
FROM (
    SELECT s_id,
           c_id,
           s_score,
           DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS `rank`
    FROM Score
) AS ranked_scores
WHERE `rank` <= 3;
```


26、查询每门课程被选修的学生数：
```sql
SELECT c_id, COUNT(DISTINCT s_id) as student_count
FROM Score
GROUP BY c_id;
```
解释：这条SQL语句统计每门课程(c_id)的选修学生数。使用`DISTINCT`确保每名学生只被计数一次。

27、查询出只有两门课程的全部学生的学号和姓名：
```sql
SELECT s.s_id, s.s_name
FROM Student AS s
JOIN (SELECT s_id FROM Score GROUP BY s_id HAVING COUNT(c_id) = 2) AS sc
ON s.s_id = sc.s_id;
```
解释：子查询首先从Score表中找出所有只选修了两门课程的学生，然后主查询通过连接操作与学生表Student进行合并，获取这些学生的学号(s_id)和姓名(s_name)。

28、查询男生、女生人数：
```sql
SELECT s_sex, COUNT(*) as count
FROM Student
GROUP BY s_sex;
```
解释：根据学生性别(s_sex)分组，并计算每组的学生数。

30、查询同名同性学生名单，并统计同名人数：
```sql
SELECT s_name, s_sex, COUNT(*) as count
FROM Student
GROUP BY s_name, s_sex
HAVING COUNT(*) > 1;
```
解释：根据姓名(s_name)和性别(s_sex)分组，只选取那些在分组后数量大于1的记录，即有相同姓名和性别的学生。

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩：
```sql
SELECT s.s_id, s.s_name, AVG(sc.s_score) as avg_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score) >= 85;
```
解释：连接Student表和Score表，计算每个学生的平均成绩，然后选择平均成绩大于等于85的记录。

35、查询所有学生的课程及分数情况：
```sql
SELECT s.s_id, s.s_name, c.c_name, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id;
```
解释：连接Student、Score和Course表，获取所有学生的课程名称和成绩。

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：
```sql
SELECT s.s_name, c.c_name, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id
WHERE sc.s_score > 70;
```
解释：连接Student、Score和Course表，并筛选出分数大于70分的记录。

37、查询不及格的学生id,姓名，及其课程名称，分数：
```sql
SELECT s.s_id, s.s_name, c.c_name, sc.s_score
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
JOIN Course AS c ON sc.c_id = c.c_id
WHERE sc.s_score < 60;
```
解释：连接Student、Score和Course表，筛选出成绩不及格（假设不及格分数线为60）的学生信息。

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名：
```sql
SELECT s.s_id, s.s_name
FROM Student AS s
JOIN Score AS sc ON s.s_id = sc.s_id
WHERE sc.c_id = '01' AND sc.s_score >= 80;
```
解释：连接Student和Score表，筛选出选修了编号为'01'的课程且成绩不低于80分的学生信息。

39、求每门课程的学生人数
```sql
SELECT c_id, COUNT(DISTINCT s_id) AS student_count
FROM Score
GROUP BY c_id;
```

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
```sql
SELECT s.s_id, s.s_name, sc.s_score
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
JOIN Course c ON sc.c_id = c.c_id
JOIN Teacher t ON c.t_id = t.t_id
WHERE t.t_name = '张三'
ORDER BY sc.s_score DESC
LIMIT 1;
```

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
```sql
SELECT s1.s_id, s1.c_id, s1.s_score
FROM Score s1
JOIN Score s2 ON s1.s_score = s2.s_score AND s1.c_id <> s2.c_id AND s1.s_id = s2.s_id;
```

42、查询每门功课成绩最好的前两名
```sql
SELECT *
FROM (
    SELECT s_id,
           c_id,
           s_score,
           DENSE_RANK() OVER (PARTITION BY c_id ORDER BY s_score DESC) AS `rank`
    FROM Score
) AS ranked_scores
WHERE `rank` <= 2;
```

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
```sql
SELECT c_id, COUNT(DISTINCT s_id) AS student_count
FROM Score
GROUP BY c_id
HAVING student_count > 5
ORDER BY student_count DESC, c_id ASC;
```

44、检索至少选修两门课程的学生学号
```sql
SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(c_id) >= 2;
```

45、查询选修了全部课程的学生信息
```sql
SELECT s.s_id, s.s_name
FROM Student s
JOIN Score sc ON s.s_id = sc.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT sc.c_id) = (SELECT COUNT(*) FROM Course);
```

46、查询各学生的年龄，按照(这个语句不完整，但假设是按照年龄升序排列)
```sql
SELECT s_id, s_name, TIMESTAMPDIFF(YEAR, STR_TO_DATE(s_birth, '%Y-%m-%d'), CURDATE()) AS age
FROM Student
ORDER BY age;
```