MySQL简单了解
数据库
1. 连接数据库
-- -p后面不能有空格，也可以直接输入-p然后回车再输入密码
mysql -uroot -p123456
2. 操作数据库
数据库对大小写不敏感
所有得符号必须是英文状态下的
所有得命令要以英文的分号;结尾
-
创建数据库
-- create database [if not exists] 数据库名; -- [ ]里面的是判断条件，可加可不加 create database if not exists mydata; -
使用数据库
-- use 数据库名; use mydata; -
删除数据库
-- drop database [if exists] 数据库名; drop database if exists mydata; -
查看所有得数据库
show databases;
3. 操作数据表
3.1 数据的列类型
-
数值
类型名 类型描述 所占大小 tinyint 很小的数据 1个字节 smallint 较小的数据 2个字节 mediumblob 中等的数据 3个字节 int（常用） 标准的整数 4个字节 bigint 较大的数据 8个字节 float 浮点数 4个字节 double 浮点数 8个字节 decimal 字符串形式的浮点数 常用于金融计算 -
字符串
类型名 类型描述 所占大小 char 字符串固定大小 0~255 varchar（常用） 可变字符串 0~65535 tinytext 微型文本 2^8-1 text 文本串 2^16-1 -
时间日期
类型名 描述 备注 date YYYY-MM-dd 日期格式 time HH:mm:ss 时间格式 datatime（常用） YYYY-MM-dd HH:mm:ss timestamp 1970.1.1到现在的毫秒数 时间戳 year 年份 -
null
- 空值
- 使用空值运算结果一定是null
3.2 数据库的字段属性
- unsigned：
- 无符号的整数
- 声明了该列不能为负数
- zerofill：
- 0填充
- 不足的位数用0进行填充
- eg：int(3) 1 --> 001
- 自增 auto_increment：
- 自动增量，默认在上一条基础上 +1
- 通常用于设计唯一的主键，必须是整数类型
- 可以自定义自增的起始值以及步长
- 非空
- not null说明此列不能为空
- 默认 default：
- 设置默认值
- 如果不指定该列的值，则为默认值
3.3 创建数据表
/*
格式
create table [if not exists] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
...
`字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]
*/
create table if not exists `student1`(
`id` int(10) not null auto_increment comment '学号',
`name` varchar(20) not null default '匿名' comment '姓名',
`sex` char(2) not null default '男' comment '性别',
`birthday` datetime default null comment '出生日期',
`address` varchar(100) default null comment '家庭地址',
`email` varchar(20) default null comment '邮箱',
primary key (`id`)
)engine=innodb default charset=utf8;
3.4 数据表的操作
-- 查看表结构
-- decs 表名;
decs student;
-- 修改表的名字
-- alter table 旧表名 rename as 新表名;
alter table student rename as student1;
-- 增加表的字段
-- alter table 表名 add 字段名 属性 [注释等];
alter table student1 add age int(3) comment '年龄';
-- 修改表的字段名
-- alter table 表名 change 旧字段名 新字段名 新类型;
alter table student1 change age age1 varchar(10);
-- alter table 表名 change 旧字段名 新字段名; -----这样会报错！
-- 修改表的约束
-- alter table 表名 modify 字段名 新类型 [not null];
alter table student1 modify age1 varchar(10) not null;
-- 删除表的字段
-- alter table student1 drop age1;
alter table student1 drop age1;
-- 删除表
-- drop table [if exists] 表名;
drop table if exists student1;
4. MySQL的数据管理（重要）
4.1 外键约束(了解)
/*
假设一个学生表，里面有一个年级字段
又有一张年级表
如果用外键进行关联
*/
-- 第一种方法，创建表的时候直接添加外建约束
-- 创建一张年级表
create table if not exists `gread`(
`greadid` int(10) not null auto_increment comment '年级id',
`greadname` varchar(10) not null comment '年级名称',
primary key (`greadid`)
)engine=innodb default charset=utf8;
-- 创建一张学生表
create table if not exists `student`(
`id` int(10) not null auto_increment comment '学生id',
`name` varchar(20) not null comment '学生姓名',
`sex` char(2) not null comment '学生性别',
`gread` int(10) not null comment '学生年级',
primary key (`id`),
key `FK_gread` (`gread`),
constraint `FK_gread` foreign key (`gread`) references `gread` (`greadid`)
)engine=innodb default charset=utf8;
-- 第二种方法，创建表之后添加外建约束
-- 创建一张年级表
create table if not exists `gread`(
`greadid` int(10) not null auto_increment comment '年级id',
`greadname` varchar(10) not null comment '年级名称',
primary key (`greadid`)
)engine=innodb default charset=utf8;
-- 创建一张学生表
create table if not exists `student`(
`id` int(10) not null auto_increment comment '学生id',
`name` varchar(20) not null comment '学生姓名',
`sex` char(2) not null comment '学生性别',
`gread` int(10) not null comment '学生年级',
primary key (`id`)
-- key `FK_gread` (`gread`),
-- constraint `FK_gread` foreign key (`gread`) references `gread` (`greadid`)
)engine=innodb default charset=utf8;
-- 添加外建约束
/*
alter table `表名`
add constraint `约束名` foreign key (`添加外建的字段`) references `哪个表` (`哪个字段`);
*/
alter table `student`
add constraint `FK_gread` foreign key (`gread`) references `gread` (`greadid`);
以上为物理外键，如果要删除表的话很麻烦。
数据库就是单纯的表，只用来存数据
如果想用来实现多表数据，使用外键（使用程序来实现）
4.2 DML语言（数据库操作语言）
-
insert（添加）
-- 添加语句 -- 字段名与value必须以一对应 -- insert into `表名` (`字段名1`,`字段名2`,`字段名3`) values ('值','值','值'); /* 也可以插入多行语句 insert into `表名` (`字段名1`,`字段名2`,`字段名3`) values ('值','值','值'),('值','值','值'),('值','值','值'); */ insert into `student` (`name`,`sex`,`gread`) values ('张三','男','1'); -- 这种方式也可以添加数据(不常用) insert into `student` set `name` = '王五',`sex` = '男',`gread` = 0; -- 添加所有的字段 -- 如果所有的字段都要添加可以不用写字段名 -- insert into `表名` values ('值','值','值','值'); insert into `student` values (1,'李四','女',0); -
update（更新）
-- 更新语句 -- update `表名` set `字段名`='修改值' where 条件; update `student` set `name`='张三1' where `id`=1; -- 也可以修改多条数据 -- update `表名` set `字段名`='修改值',`字段名`='修改值' where 条件; update `student` set `name`='张三2',`gread`=3 where `id`=2; -- 条件也可以是多个 update `student` set `gread`=3 where `id`>=2; update `student` set `sex`='男' where `id`>=1 and `gread`=3; -
delete（删除）
-- 删除某一条数据 -- delete from `表名` where 条件; delete from `student` where `id`=1; -- 删除所有表数据 delete from `student`; -- 这样写并不好，不建议用 truncate `student`; -- 完整删除表数据 /* delete与truncate的区别 首先delete支持条件，能够删除指定数据，truncate不支持 truncate删除数据表的时候会重新设置自增列 delete只有在innodb引擎下删除数据表并且重启数据库，自增才会从一开始 在myisam引擎下使用delete删除数据表，自增列会从上一个自增量开始 */
4.3 DQL语言（数据库查询语言，*）
-
select（查询）
-- 查询表中所有的信息 -- select * from `表名`; select * from `student`; -- 查询指定字段,并给字段设置别名 -- 别名是为了让查询的结果更易懂，也可以不写，还是加上比较好 -- select `字段名` as 别名,`字段名` as 别名 from `表名`; select `studentno` as 学号,`studentname` as 姓名 from `student`; -- select还可以使用函数 -- 比如下面这个，查询所有男学生的个数 select count(`sex`) as 男学生个数 from `student` where `sex`= 0; -- 如果查询结果大多重复，可以使用distinct select distinct `studentno` as 学号 from `result`; -- 查询90到100之间成绩的学生 select `studentno` as 学号,`studentresult` as 成绩 from `result` where `studentresult`>=90 and `studentresult`<=100; -- and 可以使用 && 代替,效果等同 -- 或者这样，查询1003这个学生之外90到100之间成绩的学生 select `studentno` as 学号, `studentresult` as 成绩 from `result` where `studentresult` between 90 and 100 and `studentno`!=1003; -
模糊查询
运算符 语法 描述 is null xx is null 为null，返回真 is not null xx is not null 为not null，返回真 between a between b and c a在b和c之间返回真 like a like b 若匹配成功则返回真 in a in (a1,a2....) a在a1或者a2或者。。某一个值中，返回真 /* like与通配符的联合使用 %: 用来表示任意多个字符，包含0个字符 _: 用来表示任意单个字符 */ -- 查询所有姓张的同学 select `studentno`,`studentname` from `student` where `studentname` like '张%'; -- 查询所有姓张且名字是两个字的同学 select `studentno`,`studentname` from `student` where `studentname` like '张_'; -- 查询学号是1000,1001,1002的学生信息 select `studentno`,`studentname` from `student` where `studentno` in (1000,1001,1002); -- 查询地址在河南的学生信息 select * from `student` where `address` like '河南%'; -- 查询地址为空的同学 select * from `student` where `address`='' or `address` is null; -
联表查询
操作 描述 inner join 如果表中至少有一个匹配，就返回行 left join 以左表为主表进行关联查询 right join 以右表为主表进行关联查询 /* 假设现在有四张表 grade(年级表): gradeid int(11) NOT NULL 年级编号 gradename varchar(50) NOT NULL 年级名称 result(成绩表): studentno int(4) NOT NULL 学号 subjectno int(4) NOT NULL 课程编号 examdat edatetime NOT NULL 考试日期 studentresult int(4) NOT NULL 考试成绩 student(学生表): studentno int(4) NOT NULL 学号 loginpwd varchar(20) NULL studentname varchar(20) NULL 学生姓名 sextiny int(1) NULL 性别，0或1 gradeid int(11) NULL 年级编号 phone varchar(50) NOT NULL 联系电话，允许为空 address varchar(255) NULL 地址，允许为空 born datedatetime NULL 出生时间 email varchar(50) NOT NULL 邮箱账号允许为空 identitycard varchar(18) NULL 身份证号 subject(课程表): subjectno int(11) NOT NULL 课程编号 subjectname varchar(50) NULL 课程名称 classhour int(4) NULL 学时 gradeid int(4) NULL 年级编号 */ -- =================联表查询================= -- 假设需要查学生的成绩与学生的信息，这时就需要两张表进行关联查询 -- inner join select s.`studentno` as '学号',`studentname` as '姓名', `subjectno` as '科目编号',`studentresult` as '成绩' from student as s inner join result as r on s.studentno = r.studentno; -- right join select s.`studentno` as '学号',`studentname` as '姓名', `subjectno` as '科目编号',`studentresult` as '成绩' from student as s right join result as r on s.studentno = r.studentno; -- left join -- 查询未参加考试的学生，需要以student表为主表找到成绩为null的学生即为未参加考试的学生 select s.`studentno` as '学号',`studentname` as '姓名', `subjectno` as '科目编号',`studentresult` as '成绩' from student as s left join result as r on s.studentno = r.studentno where studentresult is NULL; -- 再添加一个条件，假设还需要考试类型 select s.`studentno` as '学号',`studentname` as '姓名', `subjectname` as '考试科目',`studentresult` as '成绩' from `student` as s right join `result` as r on s.`studentno` = r.`studentno` inner join `subject` as sub on r.`subjectno` = sub.`subjectno`; -- 再添加一个条件,还需要学生的年级信息 -- 多表查询，先查询两张表，在慢慢添加（化繁为简） select s.`studentno` as '学号',`studentname` as '姓名', `gradename` as '年级',`subjectname` as '考试科目',`studentresult` as '成绩' from `student` as s inner join `grade` as g on s.`gradeid` = g.`gradeid` right join `result` as r on s.`studentno` = r.`studentno` inner join `subject` as sub on r.`subjectno` = sub.`subjectno`; -- 自联表- 关于分页和排序的问题
-- 查到学生的成绩后我们想把成绩按照降序进行排序可以吗 -- 当然可以 /* 排序的使用格式 order by `字段名` 排序的方式 排序的方式只有两种 desc:降序 asc:升序 */ -- 查到所有参加高等数学-3这个科目考试同学的成绩并对成绩进行降序排序 select s.`studentno` as '学号',`studentname` as '姓名', `subjectname` as '考试科目',`studentresult` as '成绩' from `student` as s inner join `result` as r on s.`studentno` = r.`studentno` inner join `subject` as sub on r.`subjectno` = sub.`subjectno` where sub.`subjectname`='高等数学-3' order by `studentresult` desc; /* 分页使用格式 limit 起始值,显示的数据量 eg:limit 0,10 从第一条数据开始且最多只显示10条 */ -- 查到所有参加高等数学-3这个科目考试同学的成绩并对成绩进行降序排序 -- 追加条件，显示前十名的数据，且必须大于80分 select s.`studentno` as '学号',`studentname` as '姓名', `subjectname` as '考试科目',`studentresult` as '成绩' from `student` as s inner join `result` as r on s.`studentno` = r.`studentno` inner join `subject` as sub on r.`subjectno` = sub.`subjectno` where sub.`subjectname`='高等数学-3' where `studentresult`>=80 order by `studentresult` desc limit 0,10; -
子查询和嵌套查询
-- C语言-1 前五名成绩的同学信息 -- 联表查询 select s.`studentno` as 学号,`studentname` as 姓名,`subjectname` as 科目,`studentresult` as 成绩 from `student` as s inner join `result` as r on s.`studentno` = r.`studentno` inner join `subject` as sub on sub.`subjectno` = r.`subjectno` where sub.`subjectname` = 'C语言-1' and r.`studentresult` >= 60 order by r.`studentresult` desc limit 0,5; -- 子查询 select s.`studentno` as 学号,`studentname` as 姓名,`studentresult` as 成绩 from `student` as s inner join `result` as r on s.`studentno` = r.`studentno` where r.`studentresult` >= 60 and r.`subjectno` = ( select `subjectno` from `subject` where `subjectname` = 'C语言-1' ) order by r.`studentresult` desc limit 0,5;
5. MySQL常用的函数
-- 数学函数
select abs(-10); -- 取绝对值
select ceiling(2.1); -- 向上取整
select floor(2.9); -- 向下取整
select rand(); -- 返回0到1的随机数
select sign(-3); -- 返回参数的符号，负数返回-1，正数返回1,0返回0
-- 字符串函数
select char_length("我是中国人"); -- 返回字符串的长度
select concat('h','e','l','l','o'); -- 拼接字符串
select insert('hello!世界',7,2,'word'); -- 插入字符串，也可以替换
select lower('hello!WORld'); -- 转小写
select upper('hello!WORld'); -- 转大写
select instr('hello!word','w'); -- 返回指定字符串第一次出现的索引
select replace('www.baidu.cn','cn','com'); -- 替换指定字符串
select substr('www.baidu.cn',5,5); -- 截取字符串
select reverse('www.baidu.cn'); -- 反转字符串
-- 时间函数
select current_date(); -- 获取当前日期
select curdate(); -- 获取当前日期
select now(); -- 获取当前完整日期及时间
select sysdate() -- 获取系统时间
select year(now()); -- 获取当前年
-- 系统函数
select user(); -- 返回当前用户
select version(); -- 返回当前版本
-- 聚合函数(重点，真正常用的点)
count(); -- 计数
max(); -- 最大值
min(); -- 最小值
avg(); -- 平均值
sum(); -- 求和
select `subjectname`,avg(studentresult)as 平均值,
max(studentresult)as 最大值,min(studentresult)as 最小值
from result r
inner join subject sub
on sub.subjectno = r.subjectno -- 如过写到此处结束，最会返回一条数据，这一条是所有科目的平均分，所有科目里面的做大最小值
group by r.subjectno
having 平均值 >= 80
order by 平均值 desc;
md5加密
-- ===============密码实现MD5加密=================
CREATE TABLE `md5text`(
`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` VARCHAR(10) NOT NULL COMMENT '用户名',
`password` INT(10) NOT NULL COMMENT '密码',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
ALTER TABLE `md5text` MODIFY `password` VARCHAR(50);
INSERT INTO `md5text` VALUES(1,'张三',MD5(123456)), (2,'李四',MD5(123456)), (3,'王五',MD5(123456));;
-- 验证密码
-- 验证MD5加密后的数据（md5加密是不可逆的）
SELECT * FROM `md5text` WHERE `password` = MD5(123456);
6. 事务
事务的ACID原则
- 原子性（Atomicity）：
- 要么都成功要么都失败
- 一致性（Consistency）：
- 事务前后的数据保持一致
- 隔离性（Isolation）:
- 多个并发事务之间要相互隔离
- 持久性（Durability）：
- 事物一旦提交便不可逆，被持久化到数据库中
事务的大致流程
-- mysql是默认开启事务的
-- 当手动开始一个事务时需要先关闭它，事务结束后再把它开启
set autocommit = 0; -- 关闭自动提交
-- 开始事务需要有一个标记
start transaction; -- 标记事务的开启
-- 处理的事务
commit; -- 提交，（持久化到数据库中）
rollback; -- 回滚，（回到原来的数据）
set autocommit = 1; -- 开启自动提交
模拟事务（转账）
create table `shop`(
`id` int(10) auto_increment not null comment '用户id',
`username` varchar(20) not null comment '用户名',
`balance` decimal(10,2) not null comment '用户余额',
primary key (`id`)
)engine=innodb default charset=utf8;
-- 添加进行操作的账户
insert into `shop` values(1,'张三',3000.00),(2,'李四',5000.00);
set autocommit = 0; -- 关闭自动提交
start transaction; -- 开启事务
-- 实现李四给张三转500元
update `shop` set `balance` = `balance`+500 where `id` = 1;
update `shop` set `balance` = `balance`-500 where `id` = 2;
commit; -- 提交
rollback; -- 回滚
set autocommit = 1; -- 开启自动提交
7. 索引
索引是帮助mysql高效获取数据的数据结构
索引的分类
- 主键索引（primary key）：唯一的标识，主键不可重复，只能有一个
- 唯一索引（unique key）：控制该列不能有相同的值
- 常规索引（index/key）：默认的
- 全文索引（fulltext）
/*
添加索引的是方式
1. 创建表的时候直接添加，比如主键
2. 使用alter进行添加
主键索引
alter table 表名 add primary key(字段名);
唯一索引
alter table 表名 add unique 索引名(字段名);
常规索引
alter table 表名 add index 索引名(字段名);
全文索引
alter table 表名 add fulltext(字段名);
3. 使用create进行添加
create index 索引名 on 表名(字段名);
create unique index 索引名 on 表名(字段名);
不能使用create创建主键索引
*/
SHOW INDEX FROM student; -- 显示所有的索引
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',
`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',
`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男;1:女）',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT'0' COMMENT '年龄',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';
-- 添加一百万条数据
DELIMITER $$
CREATE FUNCTION mock_data1()
RETURNS INT DETERMINISTIC
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
DECLARE i INT DEFAULT 0;
WHILE i<num DO
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data1(); -- 执行此函数 生成一百万条数据
-- 未添加索引前
SELECT * FROM `app_user` WHERE `name` = '用户15314'; -- 0.424sec
SELECT * FROM `app_user` WHERE `name` = '用户153414'; -- 0.494sec
SELECT * FROM `app_user` WHERE `name` = '用户15314'; -- 0.467sec
-- 分析sql执行的状况
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户15414'; -- 0.466sec
-- 添加索引
CREATE INDEX index_name ON `app_user`(`name`);
-- 添加索引后
SELECT * FROM `app_user` WHERE `name` = '用户15314'; -- 0.001sec
EXPLAIN SELECT * FROM `app_user` WHERE `name` = '用户15314';
8. 用户管理
-- 创建用户
-- create user 用户名 identified by 密码;
create user user1 identified by '123456';
-- 修改当前用户密码
set password = password('123456');
-- 修改指定用户密码
set password for user1 = password('111111');
-- 重命名
rename user user1 to user2;
-- 授予所有的权限
grant all privilenges on *.* to user2;
-- 查看权限
show grants for user2;
-- 撤销所有的权限
revoke all privilenges on *.* from user2;
-- 删除指定用户
drop user user2;
9. 数据备份
# 导出文件
# mysqldump -h主机 -用户名 -p密码 数据库 表1 表2 > 导出的路径寄文件名
# 也可直接导出数据库，不加表即可
mysqldump -hlocalhost -uroot -p123456 school student > G:/student.sql
# 导入文件，需要先进去mysql并进入到指定数据库中
source G:/student.sql;
10. 三大范式
-
第一范式（1NF）
-
确保每一列都不能再分
假设有一列里面有家庭人数和家庭住址，这就不满足第一范式，因为家庭人数和家庭住址可以在分为两不同的个列
-
-
第二范式（2NF）
-
第二范式的前提是满足第一范式，确保了每张表只描述一件事情
假如有一张表，学生信息表，表里有（学生id，姓名，性别，住址，年级，所学科目，）在此时往表中加入了新的字段（考试科目，考试成绩）这样一张新的表就不是单纯的描述学生的信息了，（考试科目与考试成绩）应该在另一张成绩表上面
-
-
第三范式（3NF）
-
前提是满足第二范式，确保数据表中的每一列都与主键直接相关
还是一张学生表，其中有（学生id，姓名，性别，住址，所属系，系主任）其中系主任与学生id并没有直接关系，不满足第三范式
-
-
三大范式是一定要遵守的吗？
答案肯定不是。为什么呢？
联表查询会时效率变慢，一般联表查询不会超过三张，如果严格使用三大范式，联表查询一定会变得很多，查询的速度就会变慢，
效率就会的降低，性能就会下降，用户体验就会变差。不符合商业化的需求和目标，从商业话的角度出发，性能肯定要比规范重要，在规范性能的时候，可以适当考虑规范性，
比如说，会故意给某些表添加一个冗余字段（可以从多表查询降为单表查询），会增加查询的效率
也可能会增加一些计算列（从大数据量降为小数据量），也会增加查询的效率
11. JDBC
11.1 什么是JDBC
-
我们的一应用程序是不能够直接访问数据库的，必须通过相关的数据库驱动来进行管理，每一种数据库都有相应的驱动，开发任用对数据库操作是就很麻烦，SUN公司就写了一种用于执行SQL语句的Java API，就是JDBC（Java Data Base Connectivity），可以为多种关系数据库提供统一访问。
-
JDBC的步骤
-- 首先创建一张表 CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci; USE jdbcStudy; CREATE TABLE users( `id` INT PRIMARY KEY, `name` VARCHAR(40), `password` VARCHAR(40), `email` VARCHAR(40), `birthday` DATE )ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO users VALUES(1,'张三','123456','zssan@qq.com','2000-01-01'), (2,'李四','123456','lisi@qq.com','2000-01-01'), (3,'王五','123456','wangwu@qq.com','2000-01-01');//使用jdbc之前先把jar包引入（本次使用mysql-connector-java-5.1.47.jar） //https://mvnrepository.com/artifact/mysql/mysql-connector-java可以下载 //下载好之后把jar包引入到模块下的lib目录下（创建一个lib目录） //右击添加好的jar包选择Add as Library…点击ok即可 import java.sql.*; public class MyFirstJdbc { public static void main(String[] args) throws ClassNotFoundException, SQLException { //1. 加载驱动(固定写法)8.0版本以上有变(com.mysql.cj.jdbc.Driver) Class.forName("com.mysql.jdbc.Driver"); //2. 用户信息和url String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; String name = "root"; String password = "123456"; //3. 连接成功，获取的数据库对象 Connection conn = DriverManager.getConnection(url,name,password); //4. 执行sql的对象 Statement statement = conn.createStatement(); //5. 执行sql语句 String sql = "select * from users"; //返回一个结果集，接收这个结果集并遍历输出 ResultSet resultSet = statement.executeQuery(sql); while (resultSet.next()){ //如果不知道具体的类型可以使用 resultSet.getObject() System.out.println("id = " + resultSet.getInt("id")); System.out.println("name = " + resultSet.getString("name")); System.out.println("password = " + resultSet.getString("password")); System.out.println("email = " + resultSet.getString("email")); System.out.println("birthday = " + resultSet.getDate("birthday")); System.out.println("=================="); } //6. 关闭对象（一定要关闭对象） conn.close(); statement.close(); resultSet.close(); } } /* //执行sql的对象 Statement statement = conn.createStatement(); st.executeUpdate(); //执行插入，更新，删除的sql语句，返回受影响的行数 st.executeQuery(); //执行查询sql语句，返回结果集 st.execute(); //执行任何sql语句 */
11.2 Statement对象
java中的Statement的对象用于给数据库发从sql语句，实现数据的增删改查操作
executeUpdate();这个方法可以完成增删改的操作
//添加
Statement st = conn.createStatement();
String sql = "insert into users (字段1,字段2,...) values (值1,值2...)";
int num = st.executeUpdate(sql);
if (num > 0){
System.out.println("添加成功！");
}
//更新
Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if (num > 0){
System.out.println("修改成功！");
}
//删除
Statement st = conn.createStatement();
String sql = "delete from users where id=1";
int num = st.executeUpdate(sql);
if (num > 0){
System.out.println("删除成功！");
}
executeQuery();这个方法可以完成查的操作
Statement st = conn.createStatement();
String sql = "select * from users";
ResultSet rs = st.executeUpdate(sql);
while (rs.next()){
//....
}
- JDBC的步骤是固定不变，变化的只有sql语句，为了方便，一般会写一个连接数据库的工具类与数据库的配置文件信息
# 数据库配置信息db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false
name=root
password=123456
/*
* 数据库连接工具类
*/
import java.sql.*;
import java.util.ResourceBundle;
public class SqlUtil {
private static String url = null;
private static String name = null;
private static String password = null;
static {
try {
//使用资源绑定器,获得数据库配置文件信息
ResourceBundle bundle = ResourceBundle.getBundle("com/utils/db");
//获得驱动
String driver = bundle.getString("driver");
//获得数据库信息
url = bundle.getString("url");
//用户
name = bundle.getString("name");
//密码
password = bundle.getString("password");
//加载驱动
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/*
获得数据库对象
*/
public static Connection getConn() throws SQLException {
return DriverManager.getConnection(url,name,password);
}
/*
释放资源
*/
public static void closeAll(Connection conn, Statement st,ResultSet rs){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- Statement的简单实现
public class StatementText {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//获得数据库对象
conn = SqlUtil.getConn();
//获得执行sql的对象
st = conn.createStatement();
//sql语句
//添加数据
String sql1 = "INSERT INTO users VALUES(4,'张三','123456','zssan@qq.com','2000-01-01')";
//修改数据
String sql2 = "update users set password='111111' where id = 4";
//删除数据
String sql3 = "delete from users where id = 1";
//查询数据
String sql4 = "select * from users where id = 4";
//返回的结果
int i1 = st.executeUpdate(sql1);
int i2 = st.executeUpdate(sql2);
int i3 = st.executeUpdate(sql3);
rs = st.executeQuery(sql4);
//如果受影响行数大于0，说明添加成功
if(i1 > 0){
System.out.println("添加成功！");
}
if(i2 > 0){
System.out.println("修改成功！");
}
if(i3 > 0){
System.out.println("删除成功！");
}
//如果能查询到数据，就输出这个人的名字
if (rs.next()){
System.out.println("name=" + rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
SqlUtil.closeAll(conn,st,rs);
}
}
}
11.3 SQL注入
sql注入是在输入的字符串之中注入SQL指令，在设计不良的程序当中忽略了字符检查，那么这些注入进去的恶意指令就会被数据库服务器误认为是正常的SQL指令而运行，因此遭到破坏或是入侵，容易发生数据的泄露
import com.utils.SqlUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
Sql注入的问题
*/
public class SqlText {
public static void main(String[] args) {
//使用正常的方式登录，没有问题
//login("李四","123456");
//使用不正常的方式登录，这样的话会拿到数据库的所有数据
//select * from users where `name` = ' ' or '1=1' and `password` = ' ' or '1=1';
login(" 'or '1=1"," 'or '1=1");
}
public static void login(String name,String password){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//获取数据库对象
conn = SqlUtil.getConn();
//执行sql的对象
st = conn.createStatement();
//sql语句
String sql = "select * from users where `name` = '"+name+"' and `password` = '"+ password+"'";
//执行sql并返回结果集
rs = st.executeQuery(sql);
//获取得到的值
while (rs.next()){
System.out.println("id = " + rs.getInt("id"));
System.out.println("name = " + rs.getString("name"));
System.out.println("password = " + rs.getString("password"));
System.out.println("email = " + rs.getString("email"));
System.out.println("birthday = " + rs.getDate("birthday"));
System.out.println("===========");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
SqlUtil.closeAll(conn,st,rs);
}
}
}
11.4 PrepareStatement对象
sql注入是一个很可怕的问题，如何防止sql注入，PrepareStatement对象就可以有效防止sql注入
import com.utils.SqlUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
/*
PrePareStatementText对象可以有效防止sql注入问题
大致的流程与Statement相同在sql语句与执行的那一部分有些稍微不同
*/
public class PrePareStatementText {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = SqlUtil.getConn();
//sql语句
String sql = "insert into users values (?,?,?,?,?)";
//预编译sql语句
st = conn.prepareStatement(sql);
//填充？的内容，一个参数是第几个问号，第二个参数是填充的内容
st.setInt(1,5);
st.setString(2,"k_423");
st.setString(3,"k_423");
st.setString(4,"k_423@163.con");
//回获取当前系统时间
Date date = new Date();
//初始化时间格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//把Date类型转为String类型
String s =sdf.format(date);
st.setString(5,s);
//执行sql,这里与Statement有不同，不需要传入sql语句
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放资源
SqlUtil.closeAll(conn,st,null);
}
}
}
浙公网安备 33010602011771号