package com.DBExercise;
import com.UtilClass.DBUtilImp;
import com.UtilClass.DBUtil;
import com.UtilClass.DealSQL;
import java.util.List;
public class Practice1 {
private static String[] getsqls(){
// 2024/03/26 sql练习
// 1. 创建一个员工表(employee),包含以下字段:
// 员工编号(employee_id)| 整数类型,自增,主键
// 员工姓名(eployee_name)| 字符类型,必输
// 年龄(age)|数字类型,需要接受小数;
// 入职日期(entry_date)|日期类型
// 入职部门(department)|字符类型
// 员工状态(status)|状态;在职、已离职、停薪留职、退休;
// 员工编号(employee_id)|整数类型,自增,主键
// 员工姓名(eployee_name)|字符类型,必输
// 年龄(age)|数字类型,需要接受小数;
// 入职日期(entry_date)|日期类型
// 入职部门(department)|字符类型
// 员工状态(status)|状态;在职、已离职、停薪留职、退休;
// 2. 在员工表中插入10条以上的数据,包含不同员工状态、不同部门的员工数据;
// 3. 编写sql语句,把年龄(age)大于等于60的员工状态(status)变更为“退休”;
// 4. 编写查询语句,分别查询出在职、已离职、停薪留职、退休人员数量;
// 5. 用分组查询语句实现第4题;
// 6. 编写查询语句,查询入职日期早于某一日期的员工姓名;
// 7. 查询出重名的员工有哪些,给出重名员工编号;
// 8. 用你所知的任何方法,对员工表填充1万条数据;
String[] sqlStatements = new String[8];
//1.创建一个员工表(employee)
sqlStatements[0] = """
drop table if exists zj_employee ;
create table zj_employee
(
employee_id int AUTO_INCREMENT primary key not null comment '员工编号',
employee_name varchar(20) not null comment '员工姓名',
age float comment '年龄',
entry_date date comment '入职日期',
department varchar(20) comment '入职部门',
status varchar(20) check (status in ('在职', '已离职', '停薪留职', '退休')) comment '员工状态:在职、已离职、停薪留职、退休'
);
""";
//2.在员工表中插入10条以上的数据,包含不同员工状态、不同部门的员工数据:
sqlStatements[1] = """
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('香馨', 56.1, '2022-01-01', 'Dept_A', '在职');
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('向卉', 72.7, '2021-07-15', 'Dept_B', '已离职');
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('向彤', 24.5, '2020-12-25', 'Dept_C', '已离职');
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('向雪', 35.6, '2020-12-25', 'Dept_C', '停薪留职');
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('晓燕', 39.5, '2020-12-25', 'Dept_C', '在职');
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('晓莉', 75.4, '2020-12-25', 'Dept_C', '已离职');
INSERT INTO zj_employee (employee_name, age, entry_date, department, status)
VALUES ('晓凡', 48.2, '2020-12-25', 'Dept_C', '在职');
""";
//3.编写sql语句,把年龄(age)大于等于60的员工状态(status)变更为“退休”;
sqlStatements[2] = """
update zj_employee
set status ="退休"
where age >= 60;
""";
//4.编写查询语句,分别查询出在职、已离职、停薪留职、退休人员数量;
sqlStatements[3] = """
select count(*) as "在职人数"
from zj_employee
where status = "在职";
select count(*) as "已离职人数"
from zj_employee
where status = "已离职";
select count(*) as "停薪留职人数"
from zj_employee
where status = "停薪留职";
select count(*) as "退休人数"
from zj_employee
where status = "退休";
""";
//5.用分组查询语句实现第4题;
sqlStatements[4] = """
select status, COUNT(*) as cnt
from zj_employee
Group by status;
""";
//6.编写查询语句,查询入职日期早于某一日期的员工姓名;
sqlStatements[5] = """
select employee_name
from zj_employee
where entry_date > "2020-12-25";
""";
//7.查询出重名的员工有哪些,给出重名员工编号;
sqlStatements[6] = """
select employee_id, employee_name from zj_employee
where employee_name in\s
(select employee_name from zj_employee\s
group by employee_name having count(employee_name) > 1);
""";
//8.用你所知的任何方法,对员工表填充1万条数据;
sqlStatements[7] = """
-- 清空员工表
truncate table zj_employee;
-- 如果已存在,删除populateEmployees存储过程
DROP PROCEDURE IF EXISTS populateEmployees;
-- 定义存储过程,用于批量插入员工数据
DELIMITER $$
CREATE PROCEDURE populateEmployees(IN total INT)
BEGIN
-- 定义循环变量
DECLARE i INT DEFAULT 1;
-- 使用循环批量插入数据
WHILE i <= total DO
INSERT INTO zj_employee (
employee_id, employee_name, age,entry_date, Department,status)
-- 生成随机员工信息
VALUES (i+60,
concat(substring('赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵堪汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯咎管卢莫经房裘干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚'
,floor(1+190*rand()),1),
substring('明国华建文平志伟东海强晓生光林小民永杰 军金健一忠洪江福祥中正振勇耀春大宁亮宇兴宝少剑云学仁涛瑞飞鹏安亚泽世汉达卫利胜敏群波成荣新峰刚家龙德庆斌辉良玉俊立浩天宏子松克清长嘉红山贤阳乐锋智青跃元武广思雄锦威启昌铭维义宗英凯鸿
没有合适的资源?快使用搜索试试~ 我知道了~
最近练习的源码包,更新ODBC数据库操作
共36个文件
java:34个
properties:1个
md:1个
需积分: 0 0 下载量 85 浏览量
2024-04-06
20:55:18
上传
评论
收藏 18KB 7Z 举报
温馨提示
最近练习的源码包,更新ODBC数据库操作
资源推荐
资源详情
资源评论
收起资源包目录
src.7z (36个子文件)
src
jdbc.properties 416B
Main.java 778B
快捷键.md 682B
com
UtilClass
FileUtileWr.java 2KB
FileUtilRe.java 4KB
DBUtil.java 2KB
DBUtilImp.java 4KB
DealSQL.java 2KB
CsInputInt.java 1KB
BasicKnowlage
Summation1.java 426B
Summation2.java 473B
Summation.java 940B
HelloWord.java 757B
BubbleSort.java 454B
NumSum
NumSum.java 744B
NumInput.java 1KB
FileOption
FileStr.java 2KB
FileOper.java 1KB
JudgeFile.java 438B
LessonDemo.java 3KB
FileByte.java 1KB
FileOptionTest.java 1KB
Constellation
Constellation.java 2KB
ConstMain.java 932B
CheckDate.java 4KB
model
UserInfo.java 1KB
Department.java 1KB
Employee.java 1KB
TrianglePrint
TrianglePrint.java 1KB
DBExercise
Practice2Function.java 5KB
JDBCDemo.java 2KB
JDBCDemo1.java 2KB
Practice1.java 12KB
Practice3.java 4KB
Practice2.java 5KB
Recursion
Cecursion.java 611B
共 36 条
- 1
资源评论
qq_1694565166
- 粉丝: 111
- 资源: 2
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功