数据库命令
mysql -u root -p密码 --连接数据库 update mysql.user set authentication_string=password('密码') where user='root' and Host = 'localhost'; --修改用户密码 flush privilenges; --刷新权限 ------------------------------------------ --所有语句都使用;结尾 show databases; --查看所有数据库 mysql> use 数据库名 --切换数据库 Database changed show bable; --查看数据库中所有的表 describe 数据库名; --显示数据库中所有的表的信息 create database 数据库名; --创建一个数据库 exit; --退出连接 -- 单行注释(SQL本来的注释),也可以用#; /* SQL的多行注释 */
数据库xxx语言 CRUD 增删改查 CV程序猿 API程序猿 CRUD程序猿(业务程序猿)
DDL 定义
DML 操作
DQL 查询
DCL 控制
操作数据库
-
创建数据库
CREAT DATABASE[IF NOT EXISTS] westos
-
删除数据库 DROP DATABASE [IF EXISTS] WESTOS
-
使用数据库 如果表名或者字段名是一个特殊字符,就需要``
USE
school
-
查看数据库
SHOW DATABASES 查看所有的数据库
数据库的列类型
数值:
tinyint:十分小的数据,1个字节
smallint:较小的数据,2个字节
mediumint:中等大小的数据,3个字节
int:标准的整数,4个字节 常用的
bigint:较大的数据,8个字节
float:浮点数,4个字节
double:浮点数,8个字节(精度问题)
dcimal:字符串类型的浮点数(金融计算的时候,一般使用decimal)
字符串
char:字符串固定大小:0~255
varchar:可变字符串:0~65535 常用的
tinytext:微型文本:2^8-1
text:文本串:2^16-1
时间日期
java.util,Date
date YYYY-MM-DD,日期
time:HH:MM:SS,时间
datetime:YYYY-MM-DD HH:MM:SS ,最常用的时间格式
timestamp:时间戳 1970.1.1 到现在的毫秒数 也较为常用
year:年份表示
null
没有值,未知
注意,不要使用null值进行运算,结果为null
数据库的字段属性
unsigned:
无符号的整数
声明了该列不能声明为负数
zerofill:
0填充的
不足的位数,使用0来填充,int(3),5 ---005
自增:
自动在上一条的基础上+1(默认)
通常用来设置唯一的主键,index,必须是整数类型
可以自定义设计主键自增的起始值和步长
非空:null和not null
假设设置为not null,如果不给它赋值,就会报错!
null:如果不填写值,默认就是null;
默认:
设置默认的值
例如gender,默认值为男,如果不指定该列的值,则会有默认的值!
格式
CREATE TABLE [IF NOT EXISTS] `表名`( `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], `字段名` 列类型 [属性] [索引] [注释], ...... `字段名` 列类型 [属性] [索引] [注释], )[表类型] [字符集设置] [注释]
常用命令
SHOW CREATE DATABASE school --查看创建数据库的语句 SHOW CREATE TABLE student --查看student数据表的定义语句 DESC student --显示表的结构
数据表的类型
INNODB:默认使用
MYISAM:早些年使用的
修改
--修改表名: ALTER TABLE 旧表名 RENAME AS 新表名 ALTER TABLE teacher RENAME AS teacher1 --增加表的字段: ALTER TABLE 表名 ADD 字段名 列属性 ALTER TABLE teacher1 ADD age INT(11) --修改表的字段:(重命名,修改约束!) --ALTER TABLE 表名 MODIFY 字段名 列属性[] ALTER TABLE teacher1 MODIFY age VARCHAR(11) --修改约束 --ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[] ALTER TABLE teacher1 CHANGE age age1 INT(1) --字段重命名 --删除表的字段: ALTER TABLE 表名 DROP 字段名 ALTER TABLE teacher1 DROP age1
删除
--删除表(如果表存在再删除) DROP TABLE IF EXISTS teacher1
所有的创建和删除操作尽量加上判断,以免报错
注意点:
`` 字段名,使用这个包裹!
注释 -- /**/
sql关键字大小写不敏感,建议写小写
所有的符号全部用英文!
MYSQL数据管理
1.外键(了解)
方式1.在创建表的时候增加约束(麻烦,比较复杂)
--ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 哪个表哪个字段
删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)
方式2.创建表成功后,添加外键约束
以上的操作都是物理外键(数据库级别的外键),不建议使用 ,避免数据库过多造成困扰
数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
我们通过程序来实现多张表的数据,来使用外键
2.DML语言(全部记住,背下来)
数据库的意义:数据存储,数据管理
DML语言:数据库操作语言
insert
update
delete
3.添加
--插入语句(添加) --INSERT INTO 表名([字段名1,字段名2,......])values('值1','值2','值3',....)
注意事项:
-
字段和字段之间使用 英文逗号 隔开
-
字段是可以省略的,但是后面的值必须要一一对应,不能少
-
可以同时插入多条数据,VALUES后面的值,需要使用,隔开即可
VALUES(),()....
4.修改
--修改学员名字,带了简介 UPDATE `student` SET `name` ='龙涛' WHERE id = 1; --不修改条件的情况下,会改动所有表! UPDATE `student` SET `name` = '长江7号' --语法: -- UPDATE 表名 set colnum_name = value,[colnum_name = value,...] whele [条件]
条件:where语句 运算符 id等于某个值,大于某个值,在某个区间
操作符会返回布尔值
操作符有:
=
<>或!=
'>' (无单引号)
<
'>=' (无单引号)
<=
BETWEEN ... and ...
AND
OR
注意:colnum_name 是数据库的列,尽量带上``
条件,筛选的条件,如果没有指定,则会修改所有的列
value,是一个具体的值,也可以是一个变量
5.删除
语法:delete from 表名 [where 条件]
DELETE FROM `student` WHERE id = 1;
TRUNCATE命令
作用:完全清空一个数据库表,表的结构和索引约束不会改变
delete 和TRUNCATE 的区别
相同点:都能删除数据,都不会删除表结构
不同点:
TRUNCATE 重新设置 自增列 计数器会归零
TRUNCATE 不会影响事务
delete删除的问题,重启数据库,现象
INNODB:自增列会从1开始(存在内存中的,断电即失)
MYISAM:继续从上一个自增开始(存在文件中的,不会丢失)
DQL.查询数据
(Data Query LANGUAGE:数据查询语言)
所有的查询操作都用它 select
简单查询和复杂查询都能做
数据库中最核心的语言,最重要的语句
使用频率最高的语言
指定查询字段
--查询全部的数据 SELECT * FROM 表 SELECT * FROM `student` --查询指定字段 SELECT 字段 FROM 表 SELECT `id`,`name` FROM `student` --别名,给结果起一个名字 AS SELECT `id` AS 学号,`name` AS 学生姓名 FROM `student`
语法:SELECT 字段,... FROM 表
有的时候,列名字不是那么的见名知意,起别名 AS
字段名 as 别名 表名 as 别名
去重:distinct
作用:去除SELECT查询出来的结果中重复的数据,重复的数据只显示一条
--查询一下有哪些同学参加了考试 SELECT * FROM result --查询全部的考试成绩 --查询有哪些同学参加了考试 SELECT `id` FROM result --发现重复数据,去重 SELECT DISTINCT `id` FROM result
数据库的列(表达式)
SELECT VERSION() --查询系统版本 SELECT 100*3-1 AS 计算结果 --用来计算(表达式) SELECT @@auto_increment_increment --查询自增步长(变量) SELECT `id` `age`+1 AS '修改年龄后' FROM `student`
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量....
select 表达式
from 表
where 条件子句
作用:检索数据中符合条件的值
搜索的条件由一个或多个表达式组成,结果布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与 |
or || | a or b a||b | 逻辑或 |
Not ! | not a !a | 逻辑非 |
尽量使用英文字母
SELECT `id`,`age` FROM `student` --查询考试成绩在 95~100分之间 SELECT `id`,`age` FROM `student` WHERE `id`>=2 AND `age`<=19 --模糊查询(区间) SELECT `id`,`age` FROM `student` WHERE `age` BETWEEN 19 AND 20
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
IS NULL | a is null | 如果操作符为null,则结果为真 |
IS NOT NULL | a is not null | 如果操作符巍峨not null,结果为真 |
BETWEEN | a between b and c | 若a在b和c之间,则结果为真 |
like | a like b | SQL匹配,如果a匹配b,则结果为真 |
in | a in(a1,a2,a3)... | 假设a在a1,a2,..其中的某一个值中,结果为真 |
like: -- 查询姓龙的同学 SELECT`id`,`age`FROM `student` WHERE `name`LIKE '龙%' -- 查询姓龙的同学,名字后面只有一个字的 SELECT`id`,`age`FROM `student` WHERE `name`LIKE '龙_' -- 查询姓龙的同学,名字后面有两个字的 SELECT`id`,`age`FROM `student` WHERE `name`LIKE '龙__' -- 查询名字中有涛字的同学, SELECT`id`,`age`FROM `student` WHERE `name`LIKE '%涛%' ====================================== in:(具体的一个或多个值) --查询1,2,3号学员信息 SELECT`id`,`name`,`age`FROM `student` WHERE `id` IN (1,2); ====================================== null not null --查询地址为空的学生 null '' SELECT `id`,`name`FROM `student` WHERE `age`='' OR `age` is null
联表查询
join 对比
join (连接的表) on 连接查询
where 等值查询
在使用left jion时,on和where条件的区别如下: 1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。 2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
查询参加了考试的同学(学号,姓名,科目编号,分数) SELECT * FROM student SELECT * FROM result /*思路 1.分析需求,分析查询的字段来自哪些表,(连接查询) 2.确定使用哪种查询?7种 确定交叉点(这两个表中哪些数据是相同的) 判断的条件:学生表中 id = 成绩表中 id */ SELECT `s.id`,`name`,`subject`,`result` FROM `student` AS s INNER JOIN result AS r ON s.id = r.id --Right Join SELECT s.id,s.name,subject,result FROM student s RIGHT JOIN result r ON s.id = r.id; --Left Join SELECT s.id,s.name,subject,result FROM student s LEFT JOIN result r ON s.id = r.id;
操作 | 描述 |
---|---|
Inner Join | 如果表中至少有一个匹配,就返回行 |
Left join | 即使右表中没有匹配,也会从左表中返回所有的值 |
Right join | 即使左表中没有匹配,也会从右表中返回所有的值 |
--查询缺考的同学 SELECT s.id,s.name,subject,result FROM student s LEFT JOIN result r ON s.id = r.id; WHERE result IS NULL --思考(查询参加了考试的同学信息:学号,姓名,科目,分数) /*思路 1.分析需求,分析查询的字段来自哪些表,student、result、subject(连接查询) 2.确定使用哪种查询?7种 确定交叉点(这两个表中哪些数据是相同的) 判断的条件:学生表中 id = 成绩表中 id */ SELECT s.id,name,subject,result FROM student s RIGHT JOIN result r on r.id = s.student --我要查询哪些数据 select ... --从那几个表中查 FROM 表 XXX join 连接的表 on交叉条件 --假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加。 --FROM a LEFT JOIN b --FROM a RIGHT JOIN b
自连接(了解)
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
父类
categoryid | categoryName |
---|---|
2 | 信息技术 |
2 | 软件开发 |
5 | 美术设计 |
子类
pid | categoryid | categoryName |
---|---|---|
3 | 4 | 数据库 |
2 | 8 | 办公信息 |
3 | 6 | web开发 |
5 | 7 | ps技术 |
操作:查询父类对应的子类关系
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | 数据库 |
软件开发 | web开发 |
美术设计 | ps技术 |
分页和排序
语法:limit(查询起始下标,pageSize)
子查询
where(这个值是计算出来的)
本质在where语句中嵌套一个子查询语句
where(select*from )
分组和过滤
SELECT AVG(`studentresult`) 平均分,MAX(`studentresult`) 最高分,MIN(`studentresult`) 最低分 FROM `result` r INNER JOIN `subject` sub ON r.`subjectNo` = sub.`subjectNo` GROUP BY r.`subjectNo` HAVING 平均分>85
SELECT小结
MySQL函数
常用函数
-- 数学运算 SELECT ABS(-8) -- 绝对值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 返回一个0~1之间的随机数 SELECT SIGN() -- 判断一个数的符号 0-0 负数返回-1,整数返回1 -- 字符串函数 SELECT CHAR_LENGTH('abc') -- 字符串长度 SELECT CONCAT('我','爱','我自己') -- 拼接字符串 SELECT INSERT('我爱编程',1,2,'超级热爱')-- 查询,从某个位置还是替换某个长度 SELECT LOWER('LongTao')-- 小写字母 SELECT UPPER('LongTao')-- 大写字母 SELECT INSTR('LongTao','g')-- 返回第一次出现的子串的索引 SELECT REPLACE('龙涛说坚持就能成功','坚持','努力')-- 替换出现的指定字符串 SELECT SUBSTR('龙涛说坚持就能成功',4,6)返回指定的字符串,(原字符串,截取的位置,截取的长度) SELECT REVERSE('龙涛说坚持就能成功')--反转 -- 时间和日期函数 SELECT CURRENT_DATE()-- 获取当前日期 SELECT CURDATE() -- 同上 SELECT NOW() -- 获取当前的时间 SELECT LOCALTIME()-- 本地时间 SELECT SYSDATE() -- 系统时间 -- 系统 SELECT SYSTEN_USER() SELECT USER() SELECT VERSION()
聚合函数(常用)
函数名称 | 描述 |
---|---|
COUNT() | 计数 |
SUM() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
.... | .... |
GROUP BY 字段名 -- 通过什么来分组
数据库级别的MD5加密(扩展)
什么是MD5?
MD5不可逆具体的值的MD5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前值
事务管理
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consystency)
事务的前后的数据完整性要保证一致
隔离性(Isolation)
多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,各事务之间要相互隔离
隔离所导致的一些问题:脏读、不可重复读、虚读(幻读)
持久性(Durability)
事务一旦提交则不可逆,被持久化到数据库中!
-- mysql是默认开启事务自动提交的 SET autocommit = 0 --关闭 SET autocommit = 1 --开启 -- 手动处理事务 SET autocommit = 0 -- 关闭自动条件 -- 事务开启 START TRANSACTION -- 标记一个事务的开始,从这个之后的 sql 都在同一个事务内 INSERT XX INSERT XX -- 提交:持久化(成功!) COMMIT -- 回滚:回到原来的样子(失败!) ROLLBACK -- 事务结束 SET autocommit = 1 -- 开启自动提交 SAVEPOINT 保存点名 -- 设置 ROLLBACK TO SAVE POINT 保存点名 -- 回滚到保存点 RELEASE SAVEPOINT 保存点名 -- 删除保存点
索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构
索引的分类
主键索引 (PRIMARY KEY)
唯一的标识,不可重复,只能有一个列作为主键
唯一索引 (UNIQUE KEY)
避免重复的列出现,唯一索引可以重复,多个列都可以 标识为唯一索引
常规索引 (KEY/INDEX)
默认的,index、key关键字来设定
全文索引 (FULLTEXT)
在特定的数据库引擎下才有
快速定位数据
-- 索引的使用 -- 1.在创建表的时候使用 -- 2.创建完毕后,增加索引 -- 显示所有的索引信息 SHOW INDEX FROM student -- 增加一个全文索引(索引名) 列名 ALTER TABLE school.student ADD FULLTEST INDEX `studentname`(`studentName`); -- EXPLAIN 分析sql执行的状况 EXPLAIN SELECT * FROM student; -- 非全文索引 EXPLAIN SELECT *FROM student WHERE MATCH(studentName) AGAINST('刘'); -- id_表名_字段名 -- CREATE INDEX 索引名 on 表(字段)
索引原则
索引不是越多越好
不要对进程变动的数据加索引
小数据量的表不需要加索引
索引一般加载常用来查询的字段上
索引的数据结构
Hash 类型的索引
Btree:innoDB 的默认数据结构
权限管理
用户管理
SQL yog 可视化管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码' CREATE USER longtao IDENTIFIED BY'123456' -- 修改密码(修改当前用户密码) SET PASSWORD = PASSWORD('密码') -- 修改密码(修改指定用户密码) SET PASSWORD FOR longtao = PASSWORD('111111') -- 重命名 RENAME USER 原来的名字 TO 新的名字 RENAME USER longtao TO longtao2 -- 用户授权 ALL PRIVILEGES 全部的权限,库.表 -- ALL PRIVILEGES 除了给别人授权,其他都能干 GRANT ALL PRIVILEGES ON *.* TO longtao -- 查询权限 SHOW GRANTS FOR longtao SHOW GRANTS FOR root@localhost -- root 用户的权限 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION -- 删除用户 DROP USER longtao
MySQL
为什么要备份:
保证重要的数据不丢失
数据转移A-->B
MySQL 数据库备份的方式
直接拷贝物理文件
在Sqlyog这种可视化工具中手动导出
在想要导出的表或者库中右键,选择备份或导出
使用命令行导出 mysqldump 命令行使用
# mysqldump -h 主机 - u用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h 主机 - u用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql # mysqldump -h 主机 - u用户名 -p 密码 数据库 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql #导入 #登录的情况下,切换到指定的数据库 #source d:/a.sql mysql -u用户名 -p密码 库名<备份文件
假设要备份数据库,防止数据丢失
把数据库给别人,sql文件给别人即可
规范数据库设计
为什么需要设计
当数据库比较复杂的时候,就需要设计了
糟糕的数据库设计:
数据冗余,浪费空间
数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
程序的性能差
良好的数据库设计:
节省空间
保证数据的完成性
方便我们开发系统
软件开发中,关于数据库的设计
分析需求:分析业务和需要处理的数据库的需求
概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
收集信息,分析需求
用户表(用户登录注销,用户的个人信息,写博客,创建分类)
分类表(文章分类,谁创建的)
文章表(文章的信息)
友链表(友链信息)
自定义表(系统信息,某个关键的字,或者一些主字段) key:value
标识实体(把需求落地到每个字段)
标识实体 之间的关系
写博客:user --> blog
创建分类: user --> category
关注: user --> user
友链: links
评论:user -->user-blog
三大范式
为什么需要信息规范化
信息重复
更新异常
插入异常
无法正常显示信息
删除异常
丢失一些有效的信息
三大范式(了解)
第一范式
原子性:保证每一列不可再分
第二范式
前提:满足第一范式
每张表只描述一件事情
第三范式
前提:满足第一范式和第二范式
确保数据表中的每一列数据都和主键直接相关,而不能间接相关
(规范数据库的设计)
规范性 和 性能的问题
关联查询的表不得超过3张表
考虑商业化的需求和目标(成本,用户体验!)数据库的性能 更加重要
在规范性能的问题的时候,需要适当的考虑一下 规范性
故意给某些表增加一些冗余字段(从多表查询中变为单表询)
故意增加一些计算列(从大数据量降低为小数据量的查询:索 引)
JDBC(重点)
java操作数据库
数据库驱动
驱动:声卡、显卡、数据库
我们的程序会通过数据库驱动,和数据库打交道!
JDBC
SUN 公司为了简化开发人员的操作(对数据库的统一操作),提供了一个(java操作数据库的)规范,俗称JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说,我们只需要掌握 JDBC 的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包
第一个JDBC程序
创建测试数据库
-
创建一个普通项目
-
导入数据库驱动
-
编写测试代码
public static void main(String[] args) throws ClassNotFoundException, SQLException { //1.加载驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.用户信息和url String url = "jdbc:mysql://localhost:3306/myschool?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true"; String username = "root"; String password = "1156466.abc"; //3.连接成功 Connection connection = DriverManager.getConnection(url,username,password); //4.执行SQL的对象 Statement statement = connection.createStatement(); //5.执行SQL的对象 去 执行SQL,可能存在结果,查看返回值 String sql = "SELECT * FROM student"; ResultSet resultSet = statement.executeQuery(sql);//返回的结果集 while(resultSet.next()) { System.out.println("id="+resultSet.getObject("studentId")+","); System.out.println("name="+resultSet.getObject("studentName")+","); System.out.println("age="+resultSet.getObject("age")+","); System.out.println("grade="+resultSet.getObject("grade")); } //6.释放连接 resultSet.close(); statement.close(); connection.close(); }
步骤总结:
-
加载驱动
-
连接数据库DriverManager
-
获得执行sql的对象 Statement
-
获得返回的结果集
-
释放连接
DriverManager
Class.forName("com.mysql.cj.jdbc.Driver");//固定写法,加载驱动 Connection connection = DriverManager.getConnection(url,username,password); // connection 代表数据库 //事务提交 //事务回滚 //数据库设置自动提交 connection.commit(); connection.rollback(); connection.setAutoCommit(true or false);
URL
String url = "jdbc:mysql://localhost:3306/myschool?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=true"; //mysql默认端口号是3306 //协议://主机地址 :端口号/数据库名?参数1&参数2&参数3 //oracle默认端口号是1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的对象 prepareStatement 执行SQL的对象
String sql = "SELECT * FROM student";//编写SQL statement.executeQuery();//查询操作 返回ResultSet statement.execute();//执行任何SQL statement.executeUpdate();//更新、插入、删除,都是用这个,返回一个受影响的行数
ResultSet 查询的结果集,封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道列类型的情况下使用 //如果知道列的类型就使用指定的类型 resultSet.getInt(); resultSet.getString(); ...
遍历,指针
resultSet.beforeFirst();//移动到最前面 resultSet.afterLast();//移动到最后面 resultSet.next();//移动到洗衣歌数据 resultSet.previous();//移动到前一行 resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close(); statement.close(); connection.close();//耗资源,用完关掉
statement对象
JDBC中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增删改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改查语句造成了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuety方法返回代表查询结果的ResultSet对象
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = conn.createStatement(); String sql = "insert into user(...) values(...)"; int num = st.executeUpdate(sql); if(num>0) { System.out.println("插入成功"); }
CRUD操作-delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement st = conn.createStatement(); String sql = "delete from user where id = 1"; int num = st.exectueUpdate(sql); if(num>0) { System.out.println("删除成功"); }
CRUD操作-update
使用executeUpdate(String sql) 方法完成数据修改操作,示例操作:
Statement st = conn.createStatement(); String sql = "update user set name='' where name=''"; int num = st.executeUpdate(sql); if(num>0){ System.out.println("修改成功"); }
CRUD操作-read
使用executeQuery(String sql) 方法完成数据修改操作,示例操作:
Statement st = conn.createStatement(); String sql = "select * from user where id=1"; ResultSet rs = st.executeQuery(sql); while(rs.next()) { //根据获取列的数据类型,分别调用rs的相应方法映射到java对象中 }
代码实现
-
提取工具类
package com.longtao.study01.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String driver = null; private static String url = null; private static String username = null; private static String password = null; static { try{ InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties properties = new Properties(); properties.load(in); driver = properties.getProperty("driver"); url = properties.getProperty("url"); username = properties.getProperty("username"); password = properties.getProperty("password"); //1.驱动只加载一次 Class.forName(driver); } catch (Exception e){ e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,username,password); } //释放连接资源 public static void release(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(); } } } }
-
编写增删改的方法
excuteUpdate
package com.longtao.study01; import com.longtao.study01.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestInsert { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection();//获取数据库连接 st = conn.createStatement();//获得SQL的执行对象 String sql = "INSERT INTO student(`studentId`,`studentName`,`age`,`grade`) \n VALUES(6,'longtao',19,1)"; //String sql = "DELETE FROM student WHERE studentId = 5"; //String sql = "UPDATE student SET `studentName`='嘿嘿' WHERE studentid = 4"; int i = st.executeUpdate(sql); if(i>0) { System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
-
查询
excuteQuery
package com.longtao.study01; import com.longtao.study01.utils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestSelect { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); //SQL String sql = "select * from student where studentId = 1"; rs = st.executeQuery(sql);//查询完毕会返回一个结果集 if(rs.next()) { System.out.println(rs.getString("studentName")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露
PreparedStatement对象
preparedStatement 可以防止SQL注入,效率更高!
-
新增
-
删除
-
更新
-
查询
-
防止SQL注入
public static void main(String[] args) { //正常的SQL登录 // login("龙涛","1"); //非正常的登录 login(" 'or '1=1"," ' or '1=1 "); } //登录业务 public static void login(String studentName,String studentId){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //PreparedStatement 防止SQL注入的本质,把传递进啦id参数当做字符 //假设其中存在转义字符,就直接忽略,比如 ‘ 会被直接转义 //SQL String sql = "select * from student where studentName = ? and studentId = ?";//Mybatis st = conn.prepareStatement(sql); st.setString(1,studentName); st.setInt(2,1); //查询完毕会返回一个结果集 rs = st.executeQuery(); while(rs.next()) { System.out.println(rs.getString("studentName")); System.out.println(rs.getString("studentId")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }
使用IDEA连接数据库
连接成功后可以选择数据库
事务
要么都成功,要么都失败
ACID
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库了
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来结果不一致
代码实现
-
开启事务
conn.setAutoCommit(false);
-
一组事务执行完毕,提交事务
-
可以在catch语句中显示的定义回滚语句,但默认失败就会回滚
public static void main(String[] args) { Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //关闭数据库的自动提交功能,自动会开启事务 conn.setAutoCommit(false);//开启事务 String sql1 = "update account set money = money-100 where name ='A'"; st = conn.prepareStatement(sql1); st.executeUpdate(); int x = 1/0;//报错 String sql2 = "update account set money = money+100 where name ='B'"; st = conn.prepareStatement(sql2); st.executeUpdate(); //业务完毕,提交事务 conn.commit(); System.out.println("成功"); } catch (Exception e) { try { conn.rollback();//如果失败,则回滚事务 } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }
数据库连接池
数据库连接---执行完毕---释放
连接--释放 十分浪费系统资源
池化技术:准备好一些预先的资源,过来就连接预先准备好的
最小连接数...
最大连接数... 业务最高承载上限
等待超时:...ms
编写连接池,实现一个接口 DataSource
开源放数据源实现(拿来即用)
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据连接池之后,我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的 jar 包
结论
无论使用什么数据源,本质是不变的,DataSource接口不会变,方法就不会变
Druid
Apache