企业官网建设流程全解析

一、MYSQL

1、什么是SQL

2、DDL数据定义语言-表结构-创建

3、DML-数据类型：数值类型、字符串类型、日期时间类型

数值类型

字符串类型

日期时间类型

4、DQL 数据查询语言

DQL-条件查询

DQL-分组查询

DQL-排序查询

DQL-分页查询

5、DDL、DML、DQL

-- =================== DDL数据定义语言，用来定义数据库对象（数据库、表、字段）=================== -- DDL表结构创建（如下语法中的database，也可以替换成schema，如create schema webproject; MYSQL8版本中，默认字符集为utf8mb4） -- 查询所有数据库 show databases; -- 查询当前数据库 select database(); -- 使用/切换数据库 -- use webproject; -- 创建数据库 -- create database if not exists webproject default charset utf8mb4; -- 删除数据库 -- drop database if exists webproject; -- DDL 表结构：查询、修改、删除 -- 查询当前数据库所有的表 show tables; -- 查询表结构 desc emp; -- 查询建表语句 show create table emp; -- 添加字段（比如添加员工等级）：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]; -- alter table emp add emp_level tinyint unsigned comment '员工等级'; -- 修改字段类型：alter table 表名 modify 字段名 新数据类型(长度); -- alter table emp modify emp_level int; -- 修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]; -- alter table emp change emp_level emp_grade tinyint unsigned comment '员工等级'; -- 删除字段：alter table 表名 drop 字段名; -- alter table emp drop column emp_grade; -- 修改表名：rename table 表名 to 新表名; -- alter table emp rename to employee; -- 删除表（在删除表时，表中的全部数据也会删除） -- drop table if exists tb_emp; -- 设计员工表 tb_emp create table tb_emp ( id int unsigned primary key auto_increment comment '主键', emp_name varchar(20) not null unique comment '用户名', emp_password varchar(32) default '123456' comment '密码', emp_english_name varchar(30) not null comment '英文名', emp_gender tinyint unsigned not null comment '性别，1男；2女', emp_phone char(11) not null unique comment '手机号', emp_job tinyint unsigned comment '职位，1班主任；2讲师；3学工主管；4教研主任；5咨询师', emp_salary int unsigned comment '薪资', emp_entry_date date comment '入职日期', emp_image varchar(255) comment '头像', create_time datetime comment '创建时间', update_time datetime comment '修改时间' ) comment '员工表'; -- =================== DML数据操作语言，用来对数据库表中的数据进行增删改 =================== -- DML-insert -- 指定字段添加数据：insert into emp(字段名1,字段名2) values(值1,值2); -- insert into tb_emp(emp_name,emp_english_name,emp_gender,emp_phone) values('宋江','songjiang',1,'13104482101'); -- 全部字段添加数据：insert into emp values(值1,值2,...); -- 方式一 -- insert into tb_emp values (null,'林冲', '123456', 'linchong', 1, '13104482111', 1, 15000, '2026-08-11', '1.jpg', now(), now()); -- 方式二 -- INSERT INTO `webproject`.`tb_emp` (`id`, `emp_name`, `emp_password`, `emp_english_name`, `emp_gender`, `emp_phone`, `emp_job`, `emp_salary`, `emp_entry_date`, `emp_image`, `create_time`, `update_time`) values (null,'鲁智深', '123456', 'luzhishen', 1, '13104482121', 1, 20000, '2026-08-11', '2.jpg', now(), now()); -- 批量添加数据（指定字段）：insert into emp(字段名1,字段名2) values(值1,值2),(值1,值2); -- 批量添加数据（全部字段）：insert into emp values(值1,值2,...)，(值1,值2,...); INSERT INTO `webproject`.`tb_emp` (`id`, `emp_name`, `emp_password`, `emp_english_name`, `emp_gender`, `emp_phone`, `emp_job`, `emp_salary`, `emp_entry_date`, `emp_image`, `create_time`, `update_time`) values (null, '宋江', '123456', 'songjiang', 1, '13100000001', 1, 50000, '2024-01-01', '1.jpg', now(), now()), (null, '卢俊义', '123456', 'lujunyi', 1, '13100000002', 1, 48000, '2024-01-02', '2.jpg', now(), now()), (null, '吴用', '123456', 'wuyong', 1, '13100000003', 5, 35000, '2024-01-03', '3.jpg', now(), now()), (null, '公孙胜', '123456', 'gongsunsheng', 1, '13100000004', 5, 35000, '2024-01-04', '4.jpg', now(), now()), (null, '关胜', '123456', 'guansheng', 1, '13100000005', 2, 32000, '2024-01-05', '5.jpg', now(), now()), (null, '林冲', '123456', 'linchong', 1, '13100000006', 2, 32000, '2024-01-06', '6.jpg', now(), now()), (null, '秦明', '123456', 'qinming', 1, '13100000007', 2, 30000, '2024-01-07', '7.jpg', now(), now()), (null, '呼延灼', '123456', 'huyanzhuo', 1, '13100000008', 2, 30000, '2024-01-08', '8.jpg', now(), now()), (null, '花荣', '123456', 'huarong', 1, '13100000009', 2, 28000, '2024-01-09', '9.jpg', now(), now()), (null, '柴进', '123456', 'chaijin', 1, '13100000010', 2, 28000, '2024-01-10', '10.jpg', now(), now()), (null, '李应', '123456', 'liying', 1, '13100000011', 2, 25000, '2024-01-11', '11.jpg', now(), now()), (null, '朱仝', '123456', 'zhutong', 1, '13100000012', 2, 25000, '2024-01-12', '12.jpg', now(), now()), (null, '鲁智深', '123456', 'luzhishen', 1, '13100000013', 3, 26000, '2024-01-13', '13.jpg', now(), now()), (null, '武松', '123456', 'wusong', 1, '13100000014', 3, 26000, '2024-01-14', '14.jpg', now(), now()), (null, '董平', '123456', 'dongping', 1, '13100000015', 2, 24000, '2024-01-15', '15.jpg', now(), now()), (null, '张清', '123456', 'zhangqing', 1, '13100000016', 2, 24000, '2024-01-16', '16.jpg', now(), now()), (null, '杨志', '123456', 'yangzhi', 1, '13100000017', 2, 24000, '2024-01-17', '17.jpg', now(), now()), (null, '徐宁', '123456', 'xuning', 1, '13100000018', 2, 23000, '2024-01-18', '18.jpg', now(), now()), (null, '索超', '123456', 'suochao', 1, '13100000019', 2, 23000, '2024-01-19', '19.jpg', now(), now()), (null, '戴宗', '123456', 'daizong', 1, '13100000020', 5, 20000, '2024-01-20', '20.jpg', now(), now()), (null, '刘唐', '123456', 'liutang', 1, '13100000021', 3, 20000, '2024-01-21', '21.jpg', now(), now()), (null, '李逵', '123456', 'likui', 1, '13100000022', 3, 20000, '2024-01-22', '22.jpg', now(), now()), (null, '史进', '123456', 'shijin', 1, '13100000023', 3, 20000, '2024-01-23', '23.jpg', now(), now()), (null, '穆弘', '123456', 'muhong', 1, '13100000024', 3, 19000, '2024-01-24', '24.jpg', now(), now()), (null, '雷横', '123456', 'leiheng', 1, '13100000025', 3, 19000, '2024-01-25', '25.jpg', now(), now()), (null, '李俊', '123456', 'lijun', 1, '13100000026', 4, 22000, '2024-01-26', '26.jpg', now(), now()), (null, '阮小二', '123456', 'ruanxiaoer', 1, '13100000027', 4, 18000, '2024-01-27', '27.jpg', now(), now()), (null, '张横', '123456', 'zhangheng', 1, '13100000028', 4, 18000, '2024-01-28', '28.jpg', now(), now()), (null, '阮小五', '123456', 'ruanxiaowu', 1, '13100000029', 4, 18000, '2024-01-29', '29.jpg', now(), now()), (null, '张顺', '123456', 'zhangshun', 1, '13100000030', 4, 18000, '2024-01-30', '30.jpg', now(), now()), (null, '阮小七', '123456', 'ruanxiaoqi', 1, '13100000031', 4, 18000, '2024-02-01', '31.jpg', now(), now()), (null, '杨雄', '123456', 'yangxiong', 1, '13100000032', 3, 17000, '2024-02-02', '32.jpg', now(), now()), (null, '石秀', '123456', 'shixiu', 1, '13100000033', 3, 17000, '2024-02-03', '33.jpg', now(), now()), (null, '解珍', '123456', 'xiezhen', 1, '13100000034', 3, 16000, '2024-02-04', '34.jpg', now(), now()), (null, '解宝', '123456', 'xiebao', 1, '13100000035', 3, 16000, '2024-02-05', '35.jpg', now(), now()), (null, '燕青', '123456', 'yanqing', 1, '13100000036', 3, 18000, '2024-02-06', '36.jpg', now(), now()), (null, '朱武', '123456', 'zhuwu', 1, '13100000037', 5, 18000, '2024-02-07', '37.jpg', now(), now()), (null, '黄信', '123456', 'huangxin', 1, '13100000038', 2, 16000, '2024-02-08', '38.jpg', now(), now()), (null, '孙立', '123456', 'sunli', 1, '13100000039', 2, 16000, '2024-02-09', '39.jpg', now(), now()), (null, '宣赞', '123456', 'xuanzan', 1, '13100000040', 2, 15000, '2024-02-10', '40.jpg', now(), now()), (null, '郝思文', '123456', 'haosiwen', 1, '13100000041', 2, 15000, '2024-02-11', '41.jpg', now(), now()), (null, '韩滔', '123456', 'hantao', 1, '13100000042', 2, 15000, '2024-02-12', '42.jpg', now(), now()), (null, '彭玘', '123456', 'pengqi', 1, '13100000043', 2, 15000, '2024-02-13', '43.jpg', now(), now()), (null, '单廷珪', '123456', 'shantinggui', 1, '13100000044', 2, 15000, '2024-02-14', '44.jpg', now(), now()), (null, '魏定国', '123456', 'weidingguo', 1, '13100000045', 2, 15000, '2024-02-15', '45.jpg', now(), now()), (null, '萧让', '123456', 'xiaorang', 1, '13100000046', 5, 12000, '2024-02-16', '46.jpg', now(), now()), (null, '裴宣', '123456', 'peixuan', 1, '13100000047', 5, 12000, '2024-02-17', '47.jpg', now(), now()), (null, '欧鹏', '123456', 'oupeng', 1, '13100000048', 2, 14000, '2024-02-18', '48.jpg', now(), now()), (null, '邓飞', '123456', 'dengfei', 1, '13100000049', 2, 14000, '2024-02-19', '49.jpg', now(), now()), (null, '燕顺', '123456', 'yanshun', 1, '13100000050', 2, 14000, '2024-02-20', '50.jpg', now(), now()), (null, '杨林', '123456', 'yanglin', 1, '13100000051', 3, 13000, '2024-02-21', '51.jpg', now(), now()), (null, '凌振', '123456', 'lingzhen', 1, '13100000052', 5, 14000, '2024-02-22', '52.jpg', now(), now()), (null, '蒋敬', '123456', 'jiangjing', 1, '13100000053', 5, 13000, '2024-02-23', '53.jpg', now(), now()), (null, '吕方', '123456', 'lvfang', 1, '13100000054', 2, 14000, '2024-02-24', '54.jpg', now(), now()), (null, '郭盛', '123456', 'guosheng', 1, '13100000055', 2, 14000, '2024-02-25', '55.jpg', now(), now()), (null, '安道全', '123456', 'andaoquan', 1, '13100000056', 5, 15000, '2024-02-26', '56.jpg', now(), now()), (null, '皇甫端', '123456', 'huangfuduan', 1, '13100000057', 5, 13000, '2024-02-27', '57.jpg', now(), now()), (null, '王英', '123456', 'wangying', 1, '13100000058', 3, 12000, '2024-02-28', '58.jpg', now(), now()), (null, '扈三娘', '123456', 'husanniang', 2, '13100000059', 3, 14000, '2024-03-01', '59.jpg', now(), now()), (null, '鲍旭', '123456', 'baoxu', 1, '13100000060', 3, 12000, '2024-03-02', '60.jpg', now(), now()), (null, '樊瑞', '123456', 'fanrui', 1, '13100000061', 3, 13000, '2024-03-03', '61.jpg', now(), now()), (null, '孔明', '123456', 'kongming', 1, '13100000062', 3, 11000, '2024-03-04', '62.jpg', now(), now()), (null, '孔亮', '123456', 'kongliang', 1, '13100000063', 3, 11000, '2024-03-05', '63.jpg', now(), now()), (null, '项充', '123456', 'xiangchong', 1, '13100000064', 3, 12000, '2024-03-06', '64.jpg', now(), now()), (null, '李衮', '123456', 'ligun', 1, '13100000065', 3, 12000, '2024-03-07', '65.jpg', now(), now()), (null, '金大坚', '123456', 'jindajian', 1, '13100000066', 5, 11000, '2024-03-08', '66.jpg', now(), now()), (null, '马麟', '123456', 'malin', 1, '13100000067', 2, 12000, '2024-03-09', '67.jpg', now(), now()), (null, '童威', '123456', 'tongwei', 1, '13100000068', 4, 12000, '2024-03-10', '68.jpg', now(), now()), (null, '童猛', '123456', 'tongmeng', 1, '13100000069', 4, 12000, '2024-03-11', '69.jpg', now(), now()), (null, '孟康', '123456', 'mengkang', 1, '13100000070', 5, 11000, '2024-03-12', '70.jpg', now(), now()), (null, '侯健', '123456', 'houjian', 1, '13100000071', 5, 10000, '2024-03-13', '71.jpg', now(), now()), (null, '陈达', '123456', 'chenda', 1, '13100000072', 3, 11000, '2024-03-14', '72.jpg', now(), now()), (null, '杨春', '123456', 'yangchun', 1, '13100000073', 3, 11000, '2024-03-15', '73.jpg', now(), now()), (null, '郑天寿', '123456', 'zhengtianshou', 1, '13100000074', 3, 10000, '2024-03-16', '74.jpg', now(), now()), (null, '陶宗旺', '123456', 'taozongwang', 1, '13100000075', 5, 10000, '2024-03-17', '75.jpg', now(), now()), (null, '宋清', '123456', 'songqing', 1, '13100000076', 5, 10000, '2024-03-18', '76.jpg', now(), now()), (null, '乐和', '123456', 'yuehe', 1, '13100000077', 5, 10000, '2024-03-19', '77.jpg', now(), now()), (null, '龚旺', '123456', 'gongwang', 1, '13100000078', 2, 11000, '2024-03-20', '78.jpg', now(), now()), (null, '丁得孙', '123456', 'dingdesun', 1, '13100000079', 2, 11000, '2024-03-21', '79.jpg', now(), now()), (null, '穆春', '123456', 'muchun', 1, '13100000080', 3, 10000, '2024-03-22', '80.jpg', now(), now()), (null, '曹正', '123456', 'caozheng', 1, '13100000081', 3, 10000, '2024-03-23', '81.jpg', now(), now()), (null, '宋万', '123456', 'songwan', 1, '13100000082', 3, 10000, '2024-03-24', '82.jpg', now(), now()), (null, '杜迁', '123456', 'duqian', 1, '13100000083', 3, 10000, '2024-03-25', '83.jpg', now(), now()), (null, '薛永', '123456', 'xueyong', 1, '13100000084', 3, 10000, '2024-03-26', '84.jpg', now(), now()), (null, '施恩', '123456', 'shien', 1, '13100000085', 3, 10000, '2024-03-27', '85.jpg', now(), now()), (null, '李忠', '123456', 'lizhong', 1, '13100000086', 3, 10000, '2024-03-28', '86.jpg', now(), now()), (null, '周通', '123456', 'zhoutong', 1, '13100000087', 2, 10000, '2024-03-29', '87.jpg', now(), now()), (null, '汤隆', '123456', 'tanglong', 1, '13100000088', 5, 10000, '2024-03-30', '88.jpg', now(), now()), (null, '杜兴', '123456', 'duxing', 1, '13100000089', 5, 10000, '2024-04-01', '89.jpg', now(), now()), (null, '邹渊', '123456', 'zouyuan', 1, '13100000090', 3, 10000, '2024-04-02', '90.jpg', now(), now()), (null, '邹润', '123456', 'zourun', 1, '13100000091', 3, 10000, '2024-04-03', '91.jpg', now(), now()), (null, '朱贵', '123456', 'zhugui', 1, '13100000092', 5, 10000, '2024-04-04', '92.jpg', now(), now()), (null, '朱富', '123456', 'zhufu', 1, '13100000093', 5, 10000, '2024-04-05', '93.jpg', now(), now()), (null, '蔡福', '123456', 'caifu', 1, '13100000094', 5, 10000, '2024-04-06', '94.jpg', now(), now()), (null, '蔡庆', '123456', 'caiqing', 1, '13100000095', 5, 10000, '2024-04-07', '95.jpg', now(), now()), (null, '李立', '123456', 'lili', 1, '13100000096', 5, 10000, '2024-04-08', '96.jpg', now(), now()), (null, '李云', '123456', 'liyun', 1, '13100000097', 5, 10000, '2024-04-09', '97.jpg', now(), now()), (null, '焦挺', '123456', 'jiaoting', 1, '13100000098', 3, 10000, '2024-04-10', '98.jpg', now(), now()), (null, '石勇', '123456', 'shiyong', 1, '13100000099', 3, 10000, '2024-04-11', '99.jpg', now(), now()), (null, '孙新', '123456', 'sunxin', 1, '13100000100', 5, 10000, '2024-04-12', '100.jpg', now(), now()), (null, '顾大嫂', '123456', 'gudasao', 2, '13100000101', 5, 10000, '2024-04-13', '101.jpg', now(), now()), (null, '张青', '123456', 'zhangqing2', 1, '13100000102', 5, 10000, '2024-04-14', '102.jpg', now(), now()), (null, '孙二娘', '123456', 'sunerniang', 2, '13100000103', 5, 10000, '2024-04-15', '103.jpg', now(), now()), (null, '王定六', '123456', 'wangdingliu', 1, '13100000104', 5, 10000, '2024-04-16', '104.jpg', now(), now()), (null, '郁保四', '123456', 'yubaosi', 1, '13100000105', 5, 10000, '2024-04-17', '105.jpg', now(), now()), (null, '白胜', '123456', 'baisheng', 1, '13100000106', 5, 10000, '2024-04-18', '106.jpg', now(), now()), (null, '时迁', '123456', 'shiqian', 1, '13100000107', 5, 12000, '2024-04-19', '107.jpg', now(), now()), (null, '段景住', '123456', 'duanjingzhu', 1, '13100000108', 5, 10000, '2024-04-20', '108.jpg', now(), now()); -- DML-update：update tb_emp set 字段名1=值1,字段名2=值2,... [where 条件]; -- DML-delete：delete from tb_emp [where 条件]; -- =================== DQL数据查询语言，用来查询数据库中表的记录 =================== -- select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排列字段列表 limit 分页参数 -- where条件查询之like模糊查询时，_表示匹配单个字符，%表示匹配多个字符 -- DQL 分页查询，起始索引从0开始。注意：页码索引=（页码-1）* 每页记录数 -- 查询 第1页 的员工数据，每页记录5条记录 select * from tb_emp limit 0,5; -- 查询 第2页 的员工数据，每页记录5条记录 select * from tb_emp limit 5,5; -- 查询 第3页 的员工数据，每页记录5条记录 select * from tb_emp limit 10,5;

6、分页查询：不引入pageHelper（传统）和引入pageHelper

7、多表关系保证数据一致性的方案（一对多、一对一、多对多）

一对多

-- 设计员工表 emp create table emp ( id int unsigned primary key auto_increment comment '主键', emp_name varchar(20) not null unique comment '用户名', emp_password varchar(32) default '123456' comment '密码', emp_english_name varchar(30) not null comment '英文名', emp_gender tinyint unsigned not null comment '性别，1男；2女', emp_phone char(11) not null unique comment '手机号', emp_job tinyint unsigned comment '职位，1班主任；2讲师；3学工主管；4教研主任；5咨询师', emp_salary int unsigned comment '薪资', emp_entry_date date comment '入职日期', emp_image varchar(255) comment '头像', dept_id int unsigned comment '部门ID', create_time datetime default current_timestamp comment '创建时间', update_time datetime default current_timestamp on update current_timestamp comment '修改时间' ) comment '员工表'; CREATE TABLE dept( id int UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'ID，主键', dept_name VARCHAR(10) NOT NULL UNIQUE COMMENT '部门名称', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间' ) comment '部门表'; -- 一对多（或多对一）增加外键约束（emp的dept_id-->dept的主键id） ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_id FOREIGN KEY(dept_id) REFERENCES dept(id);

一对一

-- ==========================一对一的唯一约束 ========================== create table tb_user( id int unsigned primary key auto_increment comment 'id，主键', userName varchar(10) not null comment '姓名', userGender tinyint unsigned not null comment '性别，1男；2女', userPhone char(11) comment '手机号', userDegree varchar(10) comment '学历' )comment '用户基础信息表' create table tb_user_card( id int unsigned primary key auto_increment comment 'id，主键', nationality varchar(10) not null comment '民族', birthday date not null comment '生日', idcard char(18) not null comment '身份证号', issued varchar(30) not null comment '签发机关', expire_begin date not null comment '有效期-开始', expire_end date not null comment '有效期-结束', user_id int unsigned not null unique comment '用户id', constraint fk_user_id foreign key(user_id) references tb_user(id) )comment '用户身份证信息表'

多对多

-- ==========================多对多的中间表 ========================== create table tb_student( id int unsigned primary key auto_increment comment 'id，主键', studentName varchar(10) not null comment '姓名', studentNo varchar(10) not null comment '学号' )comment '学生表' create table tb_course( id int unsigned primary key auto_increment comment 'id，主键', courseName varchar(10) not null comment '课程名称' )comment '课程表' create table tb_student_course( id int unsigned primary key auto_increment comment 'id，主键', student_id int not null comment '学生ID', course_id int not null comment '课程ID', constraint fk_courseid foreign key(student_id) references tb_course(id), constraint fk_studentid foreign key(course_id) references tb_student(id) )comment '学生课程中间表'

8、多表查询-连接查询、子查询

二、jdbc（DML和DQL）

package com; import com.pojo.tbEmp; import org.junit.Test; import java.sql.*; import java.util.ArrayList; import java.util.List; /** * 一、【AI辅助】你是一名Java开发工程师，帮我基于JDBC程序来操作数据库，执行如下SQL语句： * select * from tb_emp where emp_name = '宋江' and emp_phone ='13100000001'; * 并将查询到的每一行记录都封装到实体类tbEmp中，然后将tbEmp对象的数据输出到控制台中。 * tbEmp实体类属性如下： * @Data * @AllArgsConstructor * @NoArgsConstructor * public class tbEmp { * private Integer id; * private String empName; * private String empPassword; * private String empEnglishName; * private Integer empGender; * private String empPhone; * private Integer empJob; * private Integer empSalary; * @JsonFormat(pattern = "yyyy-MM-dd") * private LocalDate empEntryDate; * private String empImage; * @JsonFormat(pattern = "yyyy-MM-dd") * private LocalDate createTime; * @JsonFormat(pattern = "yyyy-MM-dd") * private LocalDate updateTime; * } * * * 二、什么是依赖注入 * 注意这条语句：账号：hhhh，密码'or'1'='1 * select * from tb_emp where emp_english_name = 'hhhh' and emp_password =''or'1'='1'; * 在 SQL 标准中，‌AND 的优先级高于 OR‌。因此，数据库解析器不会从左到右依次执行，而是先计算 AND 部分，再计算 OR 部分。 * 际的执行逻辑等价于加了括号后的样子： * select * from tb_emp where (emp_english_name = 'hhhh' AND emp_password = '') OR ('1'='1'); * 根据布尔逻辑：任何值 OR True 的结果都是 True，类似于 select * from tb_emp;查询出所有数据，导致数据泄露。 */ public class jdbcTest { // 提取数据库连接配置为常量 private static final String URL = "jdbc:mysql://localhost:3306/webproject?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8"; private static final String USERNAME = "root"; private static final String PASSWORD = "root"; @Test public void testUpdate() throws ClassNotFoundException, SQLException { // 使用占位符 ? 防止SQL注入 String sql = "UPDATE tb_emp SET emp_password = ? WHERE id = ?"; // try-with-resources 自动关闭资源（即使发生异常也能保证关闭） try ( Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); PreparedStatement pstmt = connection.prepareStatement(sql); ) { // 设置参数（索引从1开始） pstmt.setString(1, "123456"); pstmt.setInt(2, 1); // 执行DML语句 int rows = pstmt.executeUpdate(); System.out.println("更新的行数为：" + rows); } catch (SQLException e) { System.out.println("数据库操作异常！"); e.printStackTrace(); } } @Test public void testSelect() { // 使用占位符 ? 防止SQL注入 String sql = "SELECT * FROM tb_emp WHERE emp_name = ? AND emp_phone = ?"; List<tbEmp> empList = new ArrayList<>(); // try-with-resources 自动关闭资源（即使发生异常也能保证关闭） try ( Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); PreparedStatement pstmt = conn.prepareStatement(sql); ) { // 加载驱动（JDBC 4.0+ 可省略，但建议保留） Class.forName("com.mysql.cj.jdbc.Driver"); // 设置参数 pstmt.setString(1, "宋江"); pstmt.setString(2, "13100000001"); // 执行DQL查询 try (ResultSet rs = pstmt.executeQuery()) { while (rs.next()) { tbEmp emp = new tbEmp(); // 数据库下划线命名 emp_name → Java驼峰 empName，需手动对应 emp.setId(rs.getInt("id")); emp.setEmpName(rs.getString("emp_name")); emp.setEmpPassword(rs.getString("emp_password")); emp.setEmpEnglishName(rs.getString("emp_english_name")); emp.setEmpGender(rs.getInt("emp_gender")); emp.setEmpPhone(rs.getString("emp_phone")); emp.setEmpJob(rs.getInt("emp_job")); emp.setEmpSalary(rs.getInt("emp_salary")); //rs.getDate() 返回 java.sql.Date，通过 .toLocalDate() 转换为 LocalDate.time.LocalDate Date entryDate = rs.getDate("emp_entry_date"); emp.setEmpEntryDate(entryDate != null ? entryDate.toLocalDate() : null); emp.setEmpImage(rs.getString("emp_image")); Date createDate = rs.getDate("create_time"); emp.setCreateTime(createDate != null ? createDate.toLocalDate() : null); Date updateDate = rs.getDate("update_time"); emp.setUpdateTime(updateDate != null ? updateDate.toLocalDate() : null); empList.add(emp); } } // 输出结果 System.out.println("========== 查询结果 =========="); System.out.println("共查询到 " + empList.size() + " 条记录"); System.out.println("=============================="); for (tbEmp emp : empList) { System.out.println(emp); } } catch (ClassNotFoundException e) { System.out.println("数据库驱动加载失败！"); e.printStackTrace(); } catch (SQLException e) { System.out.println("数据库操作异常！"); e.printStackTrace(); } } }

三、mybatis

1、什么是mybatis及使用方式

2、mybatis的配置及辅助配置

application.properties

spring.application.name=springboot-mybatis # 配置mybatis日志输出 mybatis.configuration.log-impl=org.apache.ibatis.logging.commons.JakartaCommonsLoggingImpl # mybatis 配置映射下划线命名法驼峰命名法，即将数据库字段名中的下划线转换为驼峰命名法 mybatis.configuration.map-underscore-to-camel-case=true # 配置mybatis mapper xml文件路径 mybatis.mapper-locations=classpath:mapper/*.xml spring.datasource.url=jdbc:mysql://localhost:3306/webproject?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf-8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

3、mybatis的动态sql

四、Restful