1.数据库doc命令
1.1命令行连接
1.mysql -uroot -p12456 连数据库
2.update mysql.user set authentication_string=password('123456')where user='root' and Host='localhoset';--修改用户名密码
3.flush privileges;-- 刷新权限
4.-- 所有的语句都使用；结尾
5.show databases;-查看所有的数据库
6.user 数据库名 -- 切换到指定的数据库
7.show tables-- 查看数据库中的所有的表
8.describe student -- 显示表述中的所有的信息
9.exit -- 退出连接
1.2数据库语言
DDL 定义
DML 操作
DQL 查询
DCL 控制
2.操作数据库
操作数据库>操作数据库中的表>操作数据库中的数据
mysql 的关键字不区分大小写
2.1操作数据库（了解）
1.创建数据库
create database if not exists +数据库的名字
-- if not 不存在就创建一个数据库
2.删除数据库
drop database if exists +数据库的名字
-- if exists 如果存在就删除
3.使用数据库
use 数据库
--使用数据库 切换到指定的数据库 如果数据库的名字为关键字时要加一个`数据库名`；
4.查看所有的数据库
show databases -- 查看显示所有的数据库
方法：对比sqlyog的可视化操作
固定的语法和关键字必须记住。
2.2数据库的数值类型
数值
int 标准的整数 4个字节常用的
bigint 较大的数据 8 个字节
-- 还有一些的不常用的csdn查找即可
float 浮点数 4个字节
double 浮点数 8个字节
decimal 字符串的浮点数 金融计算的时候一般使用decimal
字符串
char 字符串的长度固定为0-255
varchar 可变字符串0-65535的字符串长度
tinytext 微型文本 2^8-1
text 文本串 2^16-1 保存大文本
日期
dada YYYY-MM-DD,日期的格式
time HH:mm:ss 时间的格式
datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳 197.1.1到现在的毫秒 也较为常用
year 年份表示
null
没有值
注意 不要使用null 进行运算 结果为null
2.3数据库的字段属性（重点）
Unsignes
-
无符号的整数
-
声明了该列不能声明负数
zerofill
-
0填充
-
不足的位数用0来填充
自增AUTO_INCREMENT
-
通常理解为自增，自动在一条的基础上+1（默认）
-
通常用来设计唯一的主键 index，必须为整数的类型
-
可以通过自定义设计主键自增的起始值和步长
非空
NULL not null
-
假设设置为非空 如果不给他赋值，就会报错
-
null ，如果不填写值默认就是null！
默认DEFAUNT
- 设置默认的值
- sex默认值为男，如果不设置就会有默认的值
注释 COMMENT
拓展：
-- 每一个表都必须有以下的五个字段！未来做项目用，表示记录存在的意义！
id 主键
·version· 乐观锁
is delete 伪删除
gmt_create 创建时间
gmt_updata 修改时间
2.4创数据库表（重点）
-- 目标:创建school数据库
-- 创建学生表（列 字段）使用sql创建
-- 学号int 登陆密码varchar(20)
-- 姓名 varchar(20)
-- 性别varchar(2)
-- 出生的日期datetime
-- 家庭地址
-- email
-- 每一个语句的末尾加上一个英文的逗号，最后一个不用加
-- primary key() 一般一个表中只有一个主键
CREATE TABLE `tempdata`.`student4`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`name` VARCHAR(60) NOT NULL DEFAULT 'null' COMMENT '学生的名字',
`age` INT(3) NOT NULL DEFAULT 0 COMMENT '学生的年龄',
`sex` CHAR(1) NOT NULL DEFAULT '男' COMMENT '学生的性别',
PRIMARY KEY (`id`)) ENGINE=INNODB CHARSET=utf8
COLLATE=utf8_general_ci;
– 格式
create table if exists +`数据库的名`.`表名(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
.....
)`[表的类型][字符集设置][注释]
常用命令
show create +数据库的名 -- 查看数据库的创建语句
show create `库`.`表` -- 查看数据库的的表的创建的语句
desc +表名 --查看表的结构
2.5数据库的类型
-- 2.5.1关于数据库的引擎
-- 关于数据库的引擎
INNODB --默认使用
MYISAM --早些年使用
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大约为2倍 |
常规使用
- MYISAM 节约空间，速度较快
- INNODB 安全性高 事务的处理 多表多用户操作
–2.5.2在物理空间中存在的位置 （了解）
- 所有的数据库的文件都存在data的目录下 一个文件夹对应一个数据库存储本质上就是文件的存储
–MySQL引擎在物理文件上的区别
- INNODB 在数据库中只有一个*.frm文件 以及上一级目录下的ibdata文件
- MYISAM 对应的文件
- *frm 表结构的定义文件
- *.MYD 数据文件 （data） 存放数据文件
- *.MYI 索引文件 （index）
-2.5.3设置数数据库的字符集编码
charset=utf8 --设置字符集编码
2.6修改删除表
注意点
- 在所有的创建和删除操作上尽量加上判断以免报错
- 字段名``使用这个包裹
- 注释 – /**/
- sql语句的大小写不敏感
- 所有的符号都要用英文
2.6.1 修改表(sql语句的关键字不区分大小写)
--修改表的名字：alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1;
--增加表的字段 alter table 表名 add 字段名 列的属性
alter table teacher add name varchar(30)
-- 修改表的字段(修改约束就是修改属性)
--alter table 表名 modify 字段名 列属性
alter table student modify age varchar(11)--修改约束
--修改表的字段(重命名)
--alter table 表名 change 字段的旧名字 字段的新名字 列的属性(字段的属性)
alter table student change name name1 varchar(60)
--删除表字段的 alter table 表名 drop 字段名
alter table student drop name --删除了name的字段
2.6.2 删除表(sql语句的关键字不区分大小写)
--删除表 (如果表存在的话就删除)
drop table if exists student
3.MySQL的数据管理
3.1外键（了解即可）
以下的操作都是物理的外键 ， 数据库级别的外键 我们不建议使用！ 避免数据库过多造成麻烦
方式一 在创建表的时候增加约束 （比较麻烦 复杂）
-- 学生表的gradeid字段 要去引用年级的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）references 引用
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`))
ENGINE=INNODB CHARSET=utf8
-- 学生表的gradeid字段 要去引用年级的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）references 引用
CREATE TABLE `student1`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生的id',
`gradeid` INT(10) NOT NULL COMMENT '年级id',
PRIMARY KEY(`id`),
---- 学生表的gradeid字段 要去引用年级的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）references 引用
KEY `FK_gradeid` (gradeid),
CONSTRAINT `FK_grade` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
)
ENGINE=INNODB CHARSET=utf8;
**方式二 在创建表之后添加外键约束 **
-- 创建年级表
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY(`gradeid`))
ENGINE=INNODB CHARSET=utf8;
--创建学生表
CREATE TABLE `student1`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生的id',
`gradeid` INT(10) NOT NULL COMMENT '年级id',
PRIMARY KEY(`id`))
ENGINE=INNODB CHARSET=utf8;
-- 创建表之后添加外键
---- 学生表的gradeid字段 要去引用年级的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）references 引用
-- later table 表名 add constraint `约束名` foreign key(`作为外键的列`) references 哪个表 那个字段
alter table `student1` add constraint `Fk_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
最佳实践
- 数据库就是单纯的表，只用来存数据 ，只有行（数据）列（字段）
- 我们想要使用多张表的数据，想使用外键就用程序去实现
3.2 DML 语言（全部记住）重点
3.3添加
insert
--插入语句添加
insert into `student`(`name`,`age`,`sex`)values('张三',15,'男');-- 数据和字段一一对应
insert into `表名`(`字段1`,`字段2`,`字段3`) values('值1'),('值2')....;--插入多个值
insert into `表名` values ('值1','值2','值3')--可以省略字段名 但是值必须要一一对应(包括id主键)
语法：insert into表名(字段1,字段2,字段3) values('值1'),('值2')
注意事项>
- 字段之间要用逗号隔开
- 可以同时插入多条数据 values后面的值，需要使用，隔开values(),(),…
- 可以省略字段，但是后面的值就必须一一对应
3.4修改
update
-- 如果不指定条件的话就会改动所有的表中所有的数据
update `student2` set name1='狗蛋'; -- `student2`表中的所有的name1字段的值都会被改变为狗蛋
--update `表名` set `字段` ='值1' where 条件
update `student2` set name='狗剩' where id=5:
--修改多个字段的时候的需要用逗号隔开
update `student2` set name1='狗蛋', age=15 where id=1;
-- 多个条件定位一条要修改的数据 where 条件一and 条件二
-- where 条件一 or 条二
update `student2` set name1='狗子' where id=2 and age=15；--意义上上id即等于3有等于5 所以改变的行数为0行
update `student2` set name1='狗蛋' where id>3 or id<2; --意义上是id>3或者是id<2 满足>3 和<2的数据都会被改变
--改变日期属性
update `student2` set birthday=current_time where name='张三' and sex='女';
条件： where 语句运算符 id等于某个值 大于某个值 在区间内修改
操作符返回boolen值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | ||
| < > 或!= | 不等于 | ||
| > | 大于 | ||
| < | 小于 | ||
| >= | 大等于 | ||
| <= | 小等于 | ||
| betwwen and | 某个范围内 | [2,5]闭区间 | |
| and | && 与 | id>5 and id<3 | false |
| or | || 或 | id>5 or id<3 | true |
语法：update 表名 set 字段=‘value, [字段=value ] where [条件]
注意：
-
字段尽量带上`
-
条件 筛选的条件如果没有指定 那么旧修改所有的列
-
value 是一个具体的值，，也可以是一个变量
-
多个设置属性之间要用英文的逗号隔开
3.5删除
delete 命令
语法：delete from 表名 [where 条件]
--删除数据 避免这样写 会全部删除
delete from `student`
--删除指定的数据
delete from `student` where id=1;
truncate命令
作用是完全清空一个数据库，表的结构和索引约束都不会变
--清空 student 表
truncate `student`
delete 和truncate的区别
- 相同点：都有删除数据 都不会删除表结构
- 不同点
- truncate重新设置自增列 计数会归零
- truncate不会影响事务
--测试delete 和truncate的区别
create table `student1`(
`id`int(5) not null auto_increment comment '学号',
`name` varchar(20) not null comment '姓名',
primary key(`id`)
) engine=innodb charset=utf8;
delete from `student1`;-- 不会影响自增
truncate from `student1`--自增会归零
了解即可：delete 删除的问题 重启数据库的现象
- innodb 引擎 自增的列会重1开始 （存在内存当中 ，断电即失）
- MyISAM 引擎 继续冲上一个自增的量开始 （存在文件中 不会丢失）
4.DQL查询数据（最重点）
4.1DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询都用它
- <font color=red>数据库中最核心的语言</font>
- 使用频率最高的语言
SELECT 语法
select [all | distinct]
{* | table.* | [table.field[as alias1] [,table.field2[as alias2]] [,....]}
from table_name [as table_alias]
[left | fight | inner join table_name2]-- 连表查询
[where ...]-- 指定结果需要满足的条件
[group BY ...] -- 指定结果按照那几个字段来分组
[having] -- 过滤分组后的记录需要满足的次要条件
[order by ...]-- 指定查询记录按照一个或多个的条件排序
[limit {offset,} row_count |row_countOFFSET offset}]; -- 指定查询记录从哪条查到哪条
注意：[]括号代表可选 ，{} 括号代表必选
4.2指定查询语句
--查询全部的学生
select *from student --学生表中的所有的信息
--查询指定的字段
select `id`,`name` from student;
--别名 给查询的结果起一个名字 as 可以给字段起名 也可以给表起名
select `id` as 序号,`name` as 学生名字, from student as 学生表
--函数 concat(a,b) 拼接字段
select concat ('姓名',name) as from student
语法：select 字段 ,from 表 – 从表中查询相应的字段的数据
去重 distinct
作用是去除 select 查询语句中的重复的数据 重复数据只显示一条
--查询有哪一些同学参加了开始 在成绩表中查询
select *from result --考试成绩表
select `studentid` from result --查询有哪些同学参加了考试
select distinct `studentid` from result --发现重复数据去重
数据库的列（表达式）
select version() --查询系统版本(函数)
select 100*3-2 as 计算结果 --用来计算(表达式)
select @@auto_increment_increment --计算增长的步长 (变量)
--学生的考试成绩加一分
select `studentResult`+1 as '提分后' from student4;
数据库的的表达式 :文本值 列 null 函数 计算表达式 系统标量
selcet 表达式 from 表
4.3where 条件字句
作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成 结果为boolean值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与 一假则假 |
| or || | a or b a||b | 逻辑或 一真则真 |
| not ! | not a !a | 逻辑非 真为假 假为真 |
尽量使用英文
-- 年龄除了18的所有人的 非 not !
SELECT CONCAT('名字:',`name`) AS 名字,age AS 年龄 FROM student4 AS 年龄不是18的人
WHERE NOT age=18;
-- 年龄小于18岁的未成年的人
SELECT `name` AS 姓名,`age` AS 年龄 FROM student4 AS 未成年人
WHERE age<18;
-- 年龄大于40而小于80的中年人 and &&
SELECT `name` AS 姓名 ,`age` AS 年龄 FROM student4 AS 老年人
WHERE age>40 AND age<80;
-- 年龄大于80或小于40的中年人 or ||
SELECT `name` AS 姓名 ,`age` AS 年龄 FROM student4 AS 老年人
WHERE age>80 or age<40;
-- 模糊查询 between and
SELECT `name` AS 姓名 ,`age` AS 年龄 FROM student4
WHERE age BETWEEN 15 AND 60;
模糊查询 ：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is NULL | a is null | 如果操作符为null 结果为true |
| is Not NULL | is not null | 如果操作符不为null 结果为true |
| BETWEEN AND | a between b and c | 如果a在b和c之间 结果为true |
| Like | . | SQL匹配，如果a匹配成功，那结果为true |
| In | a in (a1,a2,a3) | 加入a是a1或者是a2 其中的一个值则返回true |
--===========================模糊查询==============================
-- 查询生日为空的数据人的名字和年龄
SELECT `name` AS 名字, `age` AS 年龄 FROM student4
WHERE `birthday` IS NULL;
-- 查询生日不为空的人的名字和年龄
SELECT `id` ,`name`,`age` FROM `student4`
WHERE birthday IS NOT NULL;
-- 查询姓张的人的名字 like 结合 % (代表0到任意个字符) 和_(代表1个字符)
SELECT `name` AS 名字 ,`age` AS 年龄 FROM `student4`
WHERE `name` LIKE '张%'
-- 查询名字中间有八的数据
SELECT `nam1
SELECT `id`,`name` FROM student5
WHERE `address` IN ('内蒙','辽宁');
4.4连表查询
核心思路：找到要查找的字段所在的表
- 总结 = 时可以用where IN 不可以用where 应该用having
select + 字段 ，字段 ，字段
from + 所在的表
left /right join +所在的表
on +判断的表达式
补充： https://blog.csdn.net/weixin_39411321/article/details/90602030?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control
-- ===========================连表查询=========================
/*-- 思路
1.分析需求 分析查询的表来自哪个表 (连接查询)
2.确定使用哪种连接查询
确定交叉点 (这两个表的哪两个数据是相同的)
判断的条件：学生表中
*/
-- 连表查询
# 3) 查询大于5000的员工，来至于哪些部门，输出部门的名字 员工表e 部门表d
SELECT e.`name` '员工姓名',e.`gender` '性别',e.`salary` '薪资',e.`join_date` '入职日期',
d.`name` AS '所属部门'
FROM employee e, dept d
WHERE e.dept_id IN(
SELECT dept_id
FROM employee -- 总结 = 时可以用where IN 不可以用where 应该用having
GROUP BY dept_id
HAVING e.salary>5000
)
AND e.dept_id=d.id;
-- 查询参加考试的学生的名字和年级(就是有成绩的人)
-- 所有查找的是result的studentName和student4中的studentName相等
-- innner jion
SELECT s4.`studentName` ,`age`,`testscore`
FROM student4 AS s4
INNER JOIN `result` AS r
ON s4.studentName=r.studentName
-- right jion
SELECT s4.`studentName` ,`testscore`
FROM `student4` AS s4
RIGHT JOIN result r
ON s4.studentName=r.studentName
-- left jion
SELECT `studentName` ,`testscore`
FROM `student4` AS s4
LEFT JOIN result r
ON s4.studentName=r.studentName
--right jion
SELECT s4.`studentName`,s4.`studentId`,`testscore`
FROM `student4` AS s4
RIGHT JOIN result AS r
ON s4.studentName=r.studentName
/*三表查询
-- 思考(查询了考试的同学的信息 姓名studentName 年级gradename 分数testscore
分析 这三个字段分别是哪几个表的字段 student4 result grade 这三个表
*/
SELECT s4.`studentName`,`testscore`,s4.`gradeid`
FROM `student4` AS s4
RIGHT JOIN `result` AS r
ON s4.studentName=r.studentName
INNER JOIN `grade` g
ON r.studentName=g.studentName
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配就返回a 返回相交的字段 |
| left join | 会从左表中返回所有的值 即使右表中没有匹配 a left join b 就是以a为基准 左边为基准 |
| right join | 会从右表中返回所有的值，即使左表中没有匹配 a right join b 就是以b为基准 右边为基准 |
自连接 （了解）
自己的表和自己的表连接，核心是 一张表拆分为两张一样的表即可
父类
| 父类fpid | 父类的名字 |
|---|---|
| 1 | 猫科动物 |
| 2 | 犬科动物 |
子类
| 子类的pid | 名称 |
|---|---|
| 1 | 小狗 |
| 1 | 大狗 |
| 2 | 小猫 |
| 2 | 大猫 |
SELECT a.`item` AS '父栏目', b.`item` AS '子栏目'
FROM `type` AS a ,`type` AS b
WHERE a.pid=b.fpid
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VX1ZnHGF-1608535568638)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201119175414496.png)]
4.5分页和排序
排序
-- ==============================分页limit和排序order by=================================
-- 排序:升序 ASC 降序 DESC
SELECT s4.`studentName` ,`testscore`
FROM `student4` AS s4
RIGHT JOIN result r
ON r.studentName=s4.studentName
ORDER BY testscore DESC
单列排序
SELECT s4.`studentName` ,`testscore`FROM `student4
order by `testscore` DESC
组合排序
SELECT s4.`studentName` ,`testscore`FROM `student4
order by `testscore` DESC ,`id` desc
分页
-- ===============分页===============
-- 为什么要分页
-- 缓解数据库的压力
-- 语法 ：limit 起始值 页面的大小
SELECT s4.`studentName` ,`testscore`
FROM `student4` AS s4
RIGHT JOIN result r
ON r.studentName=s4.studentName
ORDER BY testscore DESC
LIMIT 2,6 --
-- 第n页 limit 0,5 (n-1)*pageSize,pageSize
-- packSize :页面的大小
-- (n-1)*packageSize 起始值
-- n为当前的页数
-- 数据总数/页面的大小=总页数
例题：
-- 排名前3 大一 分数大于60的学生的信息 (姓名 年龄 分数 年级)
SELECT s4.`studentName`,`age`,`gradename` ,`testscore`
FROM `student4` AS s4
RIGHT JOIN `result` AS r
ON s4.studentName=r.studentName
RIGHT JOIN `grade` g
ON r.studentName=g.studentName AND `testscore`>=60
ORDER BY testscore DESC
LIMIT 0,3
4.6子查询
where （这个值是计算出来的）<-------表达式
-- =================子查询===================
-- 查询年级为大一 考试分时>60的同学的姓名和年龄
-- 方式一 连表查询
SELECT s4.`studentName` ,`age`,`gradename`
FROM `student4` AS s4
INNER JOIN `result` AS r
ON s4.`studentName`=r.`studentName` AND testscore>80
INNER JOIN `grade` AS g
ON g.`studentName`=s4.`studentName`
WHERE g.`gradename`='大三'
-- 方式二 子查询
-- 查询年级为大三 考试分时>60的同学的姓名和年龄
-- 分为了两个表第一个为分数大于60的同学
SELECT s4.`studentName`,`age`
FROM `student4` AS s4
INNER JOIN `result` r
ON s4.`studentName`=r.`studentName` AND testscore>60
-- 在这个基础上再增加一个科目为年级为大三
SELECT s4.`studentName`,`age`
FROM `student4` AS s4
INNER JOIN result AS r
ON s4.`studentName`=r.`studentName`
WHERE testscore>60 AND s4.`studentName`=(
SELECT g.`studentName` FROM `grade` AS g
WHERE gradename='大三'
)
ALTER TABLE `result` ADD `gradeid` INT(10)
-- 方式二再改造
-- 查询年级为大一 考试分时>60的同学的姓名和年龄
SELECT `studentName`,`age` FROM `student4` WHERE studentName IN(
SELECT `studentName` FROM `result` WHERE testscore>60 AND `studentName` IN (
SELECT `studentName` FROM `grade` WHERE gradename='大三')
)
ALTER TABLE `grade` ADD studentName VARCHAR(20)
-- 子查询 from （select） temp
SELECT job,avgsal
FROM (
SELECT job,AVG(sal) AS avgsal
FROM emp
GROUP BY job) temp
WHERE temp.avgsal>2000;
4.7 分组和过滤
-- 查询不同年级的同学的最高分 最低分 平均分 根据不同的年级来分
SELECT r.`gradeid` AS 年级 ,AVG(testscore) AS 平均分,MAX(testscore) AS 最高分,MIN(testscore) AS 最低分
FROM `result` AS r
INNER JOIN grade AS g
ON g.`gradeid`=r.`gradeid`
GROUP BY r.`gradeid`
5.MySQL函数
5.1常用函数
-- ===============常用函数====================
-- 数学运算
SELECT ABS(-1) -- 绝对值
SELECT CEILING (3.2) -- 向上取整
SELECT FLOOR (3.2) -- 向下取整
SELECT RAND() -- 返回一个0-1的随机数
SELECT SIGN(10) -- 判断一个数的符号 正数返回1 负数返回一个-1 0返回0
-- 字符串的函数
SELECT CHAR_LENGTH('我爱你中国') -- 返回字符串的长度
SELECT CONCAT ('我','爱','你','中','国')-- 拼接字符串
SELECT INSERT('我爱你中国',1,2,'非常爱')-- 从某个位置开始 替换到某个长度
SELECT LOWER('woAiNi') -- 将字符串转化为小写的字母
SELECT UPPER('woaoin') -- 将字符串转换为大写的字母
SELECT INSTR('我爱你中国','爱你') -- 返回第一次子串出现的索引
SELECT REPLACE('我爱你中国','爱你','非常的爱你') -- 替换指定过的字符串为指定的内容
SELECT SUBSTR('我爱你中国',2,4) -- 返回从指定位置开始 长度为指定长度的字符串 （源字符串，截取的位置，j截取的长度）
SELECT REVERSE('清晨我上马') -- 反转字符串 返回
-- 时间和日期的函数（记住）
SELECT CURRENT_DATE() -- 获取当前的时间
SELECT CURDATE(); -- 返回当前的日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 获取系统时间
-- 获取年 月 日 时 分 秒
SELECT YEAR(NOW()) -- 获取到指定时间的年份
SELECT MONTH(NOW()) -- 获取到指定时间的月份
SELECT DAY(NOW()) -- 获取到指定时间的日子
SELECT HOUR(NOW()) -- 获取到指定时间的时
SELECT MINUTE(NOW()) -- 获取到指定时间的分
SELECT SECOND(NOW()) -- 获取到指定时间的秒
-- 系统
SELECT USER(); -- 返回系统的用户
SELECT VERSION(); -- 返回系统的版本
5.2聚合函数（最常用的函数）
| 函数名称 | 描述 |
|---|---|
| count（） | 计数 |
| SUM | 求和 |
| AVG | 平均值 |
| MAX | 最大值 |
| MIN | 最小值 |
| … | … |
-- =======================聚合函数====================
-- 能够统计表中的数据 （想查询一个表中有多少个数据，就会使用这个 count() ）
SELECT COUNT(`字段`) FROM student; -- Count(字段 ) 会忽略所有的null
SELECT COUNT (*)FROM student ; -- 不会忽略所有的null值 本质上是计算行数
SELECT COUNT (1) FROM student ; -- 不会忽略所有的null 值 本质上是计算行数
SELECT SUM(`testscore`) AS 总和 from table
SELECT AVG(`testscore`) AS 平均值 from table
SELECT MAX (`testscore`) AS 最高分 from table
SELECT MIN (`testscore`) AS 最低分 from table
补充： 分组查询
#1 统计各个分类商品的个数
-- 关键字 select `字段`,count(`字段`) from `product` group by `分类字段名`
SELECT category_name, COUNT(category_name) FROM `product` GROUP BY `category_name`
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT `category_name`, COUNT(category_name) FROM `product`
WHERE `category_name` IS NOT NULL GROUP BY `category_name`
--补充条件写在 where Having
HAVING COUNT(category_name)>1 ;
5.3 数据库级别的MD5加密（扩展）（面试）
什么是MD5? 百度百科
功能 主要是增强算法复杂度和不可逆性
MD5不可逆， 具体的值的md5是一样的
MD5 破解网站的原理是 背后有一个字典 MD5加密的值 加密的前值
-- ==============测试md5加密=================
-- 明文密码
INSERT INTO `student3` VALUES (2,'王五','123456','男','ddd','ddd','3');
-- 在插入的时候就进行加密
INSERT INTO `student3` VALUES (3,'王五',MD5('123456'),'男','ddd','ddd','3');
-- 校验 将用户插入的密码进行md5加密比对加密前的值
SELECT * FROM `student3`
WHERE `name`='王五' AND `password`=MD5('123456')
6.事务
6.1什么是事务(概念需要背记)
要么都成功 要么都失败
sql 执行 A给B转钱 A1000 ------>200 B200
sql 执行 B收到A的钱 A变成800 B变成400
为一组事务必须满足同时成功 或失败
以上的的两个sql语句放在一个批次中执行
事务的原则 ACID 原则 原子性 一致性 隔离性 持久性 （脏读 ，幻读。。。）
参考的博客地址：https://blog.csdn.net/dengjili/article/details/82468576
原子性（Atomicity）
要么都成功
一致性（Consistency）
事务前后的数据完整性保证一致
持久性（Durability）
事务一旦提交则不可逆
隔离性（Isolation）
事务的隔离性是对各用户并发访问数据库时 ，数据库为每一个用户开启的事务，不能被其他的事务的操作数据所干扰
事务之间要相互隔离
隔离原则再看 面试要聊
不隔离所导致的一些问题
脏读：
指一个事务读取了另一个事务未提交的数据
不可重复性：
在事务内读取表中的某一行的数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）
虚度（幻读）
是指一个事务内读取到了别的事务插入的数据，导致前后的读取不一致
事务案例
-- ================事务的实例=========================
-- 转账的事务 完整的案例
-- 创建一张表
CREATE TABLE `bank` (
`userid` INT(5) NOT NULL AUTO_INCREMENT COMMENT '客户的id',
`username` VARCHAR(50) NOT NULL COMMENT '客户的名字',
`money` DECIMAL(9,2) NOT NULL COMMENT '客户的余额',
PRIMARY KEY(`userid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 给表插入数据
INSERT INTO `bank` VALUES(001,'张三',1500),(002,'李四',2000);
-- 模拟转账的事务
SET autocommit=0; -- 关闭自动提交
START TRANSACTION -- 开启一组事务
UPDATE `bank` SET money=money-500 WHERE `username`='张三';
UPDATE `bank` SET money=money+500 WHERE `username`='李四';
COMMIT;-- 提交事务
ROLLBACK; -- 回滚
SET autocommit=1;-- 恢复默认值
7.索引
MySQL 官方对索引的定义为： 索引（index） 是帮助MySQL是帮助MySQL高效获取数据的数据结构
索引就是一种数据结构
7.1 索引的分类
-
主键的索引 (PRIMARY KEY）
- 唯一的标识 主键不可重复 只能有一个列作为主键
-
唯一索引 (UNIQUE KEY)
- 避免重读列的出现 唯一索引可以重复 ，多个列都有可以标识为唯一索引
-
常规的索引 (KEY /INDEX)
默认的 index key关键字来设置
-
全文索引 （FullText）
-
在特点的数据库的引擎下才有 MyISAM
-
快速的定位数据
-
-- ======索引的使用================
-- 1.在创建表的时候增加索引
-- 2.创建完毕后增加索引
-- 显示索引的信息
-- 显示所有的所有的索引信息
SHOW INDEX FROM `student4`
-- 增加一个索引的信息
ALTER TABLE `tempdata`.`student4` MODIFY UNIQUE INDEX `studentName`(`studentName`);
-- 添加索引
id_表名_字段名_ -- 索引名
-- create (哪种类型的索引)index 索引名 on 表(字段)
create index id_app_testIndex_username on app_testIndex(`username`)
-- explain 分析sql执行的情况
EXPLAIN SELECT *FROM student4 WHERE MATCH studentName AGAINST('张三');
EXPLAIN SELECT *FROM student4; -- 常规索引
-- 测试了studentName在全文索引下查询了一行就找到了 rows=1 where match 字段 against '名字' 全文索引的特殊的格式
7.2 测试索引
存储函数与存储过程的区别
-
1.存储函数有且只有一个返回值，而存储过程不能有返回值。
-
2.函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数
-
3.存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。
-
4.存储过程可以调用存储函数。但函数不能调用存储过程。
-
5.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.
-- ===========测试索引=============
-- 1.创建一张app用户表
CREATE TABLE `tempdata`.`app_testIndex`(
`userid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`username` VARCHAR(50) NOT NULL COMMENT '用户名',
PRIMARY KEY(`userid`) -- 设置主键索引
)ENGINE=INNODB DEFAULT CHARSET=utf8 -- 设置默认的编码
-- 给表插入数据1000条数据
-- delimiter就是告诉mysql解释器，该段命令是否已经结束了，是否可以执行了。
-- 默认情况下，delimiter是分号;，遇到分号就执行。
-- 后面的双美元符号 就是告诉mysql，遇到双美元符号再执行
-- 方式一 创建存储的过程
DELIMITER $$ -- 必须要写
CREATE PROCEDURE add_data1() -- 创建存储的过程
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE num INT DEFAULT 1000;
WHILE i<num DO
INSERT INTO `app_testIndex` (`username`)VALUES(CONCAT('用户',i));
SET i=i+1;
END WHILE;
END $$
DELIMITER;
-- 调用存储过程将数据存到表中
CALL add_data1(); -- 调用存储过程将数据存到表中
drop add_data1(); -- 删除定义的存储过程
-- 方式二
-- 创建函数之前必须改变系统设置
SET GLOBAL log_bin_trust_function_creators=TRUE;
-- 创建函数
DELIMITER $$
CREATE FUNCTION my_test_function(i INT(10)) RETURNS INT(10) # 创建函数 学习while循环的用法
BEGIN
WHILE i <1000 DO
INSERT INTO `tempdata`.`app_testIndex` (`username`)VALUES(CONCAT('用户',i));
SET i = i + 1;
END WILE;
RETURN i;
END $$
DELIMITER ;
select function my_test_function();-- 调用函数
drop function my_test_function();-- 删除函数
测试索引的效果
-- 未添加索引时的查询速度为
EXPLAIN SELECT *FROM app_testindex WHERE `username`='用户99999';-- 总耗时 : 0.84 sec rows:999486
-- 添加索引
id_表名_字段名_ -- 索引名
-- create (哪种类型的索引)index 索引名 on 表(字段)
create index id_app_testIndex_username on app_testIndex(`username`)
-- 测试添加索引后的查询速度
EXPLAIN SELECT *FROM app_testindex WHERE `username`='用户99999';-- 总耗时 : 0.0002 sec rows:1
-- 可以得出结论：索引在数据小的时候用处不大但是在大大数据的时候区别是十分明显的
7.3索引的原则
-
不要对经常变动的数据增加索引
-
索引不是越多越好
-
小数据量的表不需要添加索引
-
索引一般写在常用的查询字段上
索引的数据结构
hash 数据结构
Btree ：innodb 的默认的索引的数据结构是Btree
阅读索引的数据结构与底层的实现：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8.用户管理与备份
8.1用户管理
sql命令操作 本质上是对 mysql.user 这张用户表表进行增删改查
-- ===============用户管理======================
-- 创建用户 CREATE USER 用户名 IDENTIFIED by '密码'
CREATE USER syt IDENTIFIED BY'123456'
-- 修改密码（修改当前用户的密码）
SET PASSWORD =PASSWORD('123456')
-- 修改指定(用户的密码)
SET PASSWORD FOR syt=PASSWORD('123456')
-- 重命名 RENAME USER 原来的名字 TO 新名字
RENAME USER syt TO syt2
-- 用户授权 ALL PRIVILEGES 包括除了创建用户以外所有的权限
-- *.* （所有的库所有的表 通配符）指定库.表
GRANT ALL PRIVILEGES ON *.* TO syt
-- 查询权限
SHOW GRANTS FOR syt -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost -- 查看root权限
-- root用户的权限 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限 在那个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM syt
-- 删除用户
drop user syt
8.2MySQL的备份
- 为了保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式
-
直接拷贝物理文件
-
在可视化工具中手动导出
-
命令行备份
# mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysql -hlocalhost -uroot -p123456 tempdata student4 > e:/student4.sql
# mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 表名3 >物理磁盘位置/文件名
mysql -hlocalhost -uroot -p123456 tempdata student4 student3 student5 > e:/student4.sql
#移库
# mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysql -hlocalhost -uroot -p123456 tempdata > e:/tempdata.sql
#导入
#登录的情况下， 切换到指定到数据库
source e:/student4.sql
9.规范数据库的设计
当数据库比较复杂的时候我们就要设计数据库
糟糕的数据库的设计
- 数据冗余
- 数据插入和删除比较麻烦，异常
- 程序的性能差
良好的数据库的设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求，分析业务和需要处理的数据库设计
设计数据库的步骤（个人博客）
-
收集信息
-
用户表（用户登录注销，用户的个人信息，写博客,创建分类）
-
分类表
-
文章表
-
讨论表
-
友链表
-
自定义表
-
说说表
-
-
标识实体（把需求落到每个字段）
-
标识实体的关系
- 写博客 user> blog
- 创建分类 user> category
- 关注 user>user
- 友链 links
- 评论 user-user blog
9.2三大范式
为什么数据规范化
- 信息重复
- 更新异常
- 插入异常
- 删除异常
三大范式
第一范式（1NF）
原子性：保证每一列不能再分
第二范式(2NF)
前提：满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一第二范式
确保数据表中的每一列的数据都与主键直接相关，而不能间接相关
规范和性能的问题
阿里明确规定关联的数据表不得超过三张表
- 考虑商业化的目标和需求（成本，用户体验） 数据库的性能更加重要
- 在规范性能问题的时候要适当考虑下规范性
- 有时故意增加一写冗余的字段（从多表查询变成单表查询）提高了查询的性能
- 故意增加一些计算列（从大数据量降低到小数据量的查询:索引）
10.JDBC(重点)
10.1数据库驱动
驱动：声卡 显卡 数据库
程序通过驱动才能与之进行交互[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Z7LirUwz-1608535568646)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201123092752536.png)]
10.2 JDBC
sun公司为了简化开发人员的（对于数据库的统一）操作 ，提供了一个（java操作数据路的规范）速成 JDBC
对于开发人员来说，我们只需要掌握JDBCj接口的操作即可
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G56FXcpy-1608535568650)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201123093411432.png)]
10.3第一个JDBC的程序
测试代码
package org.example;
import java.sql.*;
/**
*第一个jJDBC的程序
*
*/
public class App
{
public static void main( String[] args ) throws ClassNotFoundException, SQLException {
//查询语句
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
//2.用户信息和url
/*
* jdbc:mysql://localhost:3306/tempdata? 库的地址 tempdata为数据库的有一个库
* useUnicode=true 使用Unicode 编码支持中文
*character Encoding=utf8 编码方式utf8
* useSSL=true 使用安全的连接
* */
String url="jdbc:mysql://localhost:3306/tempdatauseUnicode=true&characterEncoding=utf8&useSSL=true";
String usename="root";
String password="123456";
//3.连接数据库
Connection connection= DriverManager.getConnection(url,usename,password);
//4.获取执行sql语句的对象
Statement statement = connection.createStatement();
//5.执行sql语句的对象去执
String sql="SELECT *from bank";
//用执行的sql语句的对象取执行sql命令
ResultSet resultSet = statement.executeQuery(sql);//返回了一个结果集
while(resultSet.next()){
System.out.println("id"+resultSet.getObject("userid"));
System.out.println("username"+resultSet.getObject("username"));
System.out.println("money"+resultSet.getObject("money"));
}
//6.释放资源
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
1.加载驱动
2.连接数据库DriverManager
3.获取执行sql语句的对象 statement Statement ；
4.获得返回的结果集
5.释放资源
DiverManager 获取驱动类
// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
//连接数据库
Connection connection= DriverManager.getConnection(url,usename,password);
//connection 代表返回的数据库可以操作所有数据库的指令
//事务提交
connection.rollback; //事务回滚
connection.commit; //事务的提交
connection.setAutoCommit();//设置自动提交
URL 获取到数据库的地址类
String url="jdbc:mysql://localhost:3306/tempdata?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql --默认的端口号 3306
//协议://主机地址:端口号/数据库名称？参数1参数2参数3
//oralce-- 默认的端口号 1521
//jdbc:oracle:thin:@localhost:1521:sid
Statement 执行SQL的类
String sql="select *from studnet4"
statement.executeQuery();//查询操作返回Resultset
statement.execute();//执行任何的SQL
statement.executeUpdate();//更新插入删除 用这个返回一个受影响的行数
statement.executeBatch();//执行多个sql语句
ResultSet 查询的结果集：封装了所有的查询结果
获取到指定的数据类型
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDate();
resultSet.getDouble();
resultSet.getObject();
resultSet.getFloat();
遍历指针
resultSet.previous();//移动到前一行
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.absolute();//移动到指定行
释放资源
//6.释放资源
resultSet.close();
statement.close();
connection.close();
10.4 statement对象
jdbc中statement对象用于向数据库发送SQL语句想完成对数据库的增删改查，只要通过这个对象向数据库发送增删改查的语句即可
Statement的对象的executeUpdata方法，用于向数据库发送增删改的sql语句，executeUpdate执行完后，将会返回一个整数
（即增删改的语句导致几行的数据发生了变化）
Statement.executeQuery方法用于向数据库发送查询的语句，executeQuery返回代表查询结果的ResultSet对象
CRUD操作- create
使用executeUpdata(String sql) 完成数据库的插入操作
Statement statement1 = connection.createStatement();
String sql="insert into bank('username',`money`) values ('陈起',1200)";
int i = statement1.executeUpdate(sql);
if (i>0){
System.out.println("插入成功'");
}
CRUD操作- delete
使用executeUpdata(String sql) 完成数据库的删除操作
Statement statement1 = connection.createStatement();
String sql="delete from bank where userid=2";
int i = statement1.executeUpdate(sql);
if (i>0){
System.out.println("删除成功'");
}
CRUD操作- updata
使用executeUpdata(String sql) 完成数据库的修改操作
Statement statement1 = connection.createStatement();
String sql="updata bank set username='狗蛋' where userid=2 ";
int i = statement1.executeUpdate(sql);
if (i>0){
System.out.println("更新成功");
}
sql存在注入的漏洞 会被攻击导致数据泄露
SQL会被拼接 ‘or’ 1=1 返回真
package org.example.lession01;
import org.example.utils.JdbcUtils;
import org.example.utils.JdbcUtils2;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
* 登录的业务 用Statementa执行sql语句存在注入的漏洞 可能会被攻击导致数据库中的信息的泄露
* 在开发中我们用PrepardStatement执行sql
* */
public class sql注入 {
public static void main(String[] args) {
login(" 'or' 1=1","'or' 1=1");
}
public static void login(String username, String password) {
Connection connection = null;
Statement statement = null;
ResultSet set = null;
try {
//获取连接
connection = JdbcUtils2.getConnection();
//创建statement对象
statement = connection.createStatement();
//执行sql语句 从表中查询传入的数据
String sql = "select *from `User` where `username`='" + username + "' and `password`='" + password + "'";
set = statement.executeQuery(sql);
while (set.next()) {
System.out.println("用户名" + set.getString("username"));
System.out.println("密码" + set.getString("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils2.release(connection,statement,set);
}
}
}
10.5PreparedStatement 对象
1.增加
package org.example.lession02;
import org.example.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*
* PrepardStatement 执行查询的的sql语句
* */
public class TestSelect {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet set = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//sql语句
String sql = "select *from `User` where userid=?";
//执行预编译
statement = connection.prepareStatement(sql);
//给参数赋值
statement.setInt(1, 3);
//执行
ResultSet set1 = statement.executeQuery();
while (set1.next()) {
System.out.println("id为" + set1.getInt("userid"));
System.out.println("用户名为" + set1.getString("username"));
System.out.println("密码为" + set1.getString("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//释放资源
JdbcUtils.release(connection,statement,set);
}
}
}
2.删除
package org.example.lession02;
import org.example.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* PrepardStatement执行sql删除的语句
*
* */
public class TestDelete {
public static void main(String[] args) {
Connection connection=null;
PreparedStatement statement=null;
try {
//调用工具类中获取连接的方法
connection= JdbcUtils.getConnection();
//sql语句 用问号代替字段的值
String sql="delete from `User` where userid=? ";
//预编译
statement=connection.prepareStatement(sql);
//给参数赋值
statement.setInt(1,2);
//执行
int i = statement.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,null);
}
}
}
3.修改
package org.example.lession02;
import org.example.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 执行修改的语句
* */
public class TestUpdata {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//Sql语句 使用?占位符 代替字段的属性
String sql = "Update `User` set `username`='王八蛋' where userid=?";
//预编译
statement = connection.prepareStatement(sql);
//给参数赋值
statement.setInt(1, 3);
//返回执行的结果 返回的是发生改变的行数 i
int i = statement.executeUpdate();
if (i > 0) {
System.out.println("修改成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//释放资源
JdbcUtils.release(connection, statement, null);
}
}
}
4.查询
package org.example.lession02;
import org.example.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*
* PrepardStatement 执行查询的的sql语句
* */
public class TestSelect {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet set = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//sql语句
String sql = "select *from `User` where userid=?";
//执行预编译
statement = connection.prepareStatement(sql);
//给参数赋值
statement.setInt(1, 3);
//执行
ResultSet set1 = statement.executeQuery();
while (set1.next()) {
System.out.println("id为" + set1.getInt("userid"));
System.out.println("用户名为" + set1.getString("username"));
System.out.println("密码为" + set1.getString("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//释放资源
JdbcUtils.release(connection,statement,set);
}
}
}
防止sql注入 的登录的业务
package org.example.lession02;
import org.example.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*
* 防止sql注入的漏洞 我们采用 PrepardStatement的对象执行sql
*
* */
public class PreparedSta {
public static void main(String[] args) {
login("syt","123456");
}
//模拟登录的业务
public static void login(String username, String psssword) {
Connection connection = null;
PreparedStatement statement = null;
ResultSet set = null;
try {
//获取连接
connection=JdbcUtils.getConnection();
//sql的查询语句
String sql="select *from `User` where `username`=? and `password`=?";
//预编译
statement=connection.prepareStatement(sql);
//给参数赋值
statement.setString(1,username);
statement.setString(2,psssword);
//执行查询
set = statement.executeQuery();
while (set.next()){
System.out.println("id为"+set.getInt("userid"));
System.out.println("用户名为"+set.getString("username"));
System.out.println("密码为"+set.getString("password"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//释放资源
JdbcUtils.release(connection,statement,set);
}
}
}
10.6使用IDEA连接数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AYLwZoz8-1608535568655)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201127102836590.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ao5hYZvB-1608535568658)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201127103123391.png)]
前提是要将jar包导入
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wZfpxQtz-1608535568666)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201127103513856.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SUODqhX7-1608535568668)(C:\Users\syt\AppData\Roaming\Typora\typora-user-images\image-20201127103836527.png)]
10.7事务
要么都成功要么都失败
ADID原则
原子性：要么都完成要么都完不成
一致性：总数不变
隔离性：多个进程互不干扰
持久性;一旦提交不可逆 吃计划数据库
隔离性的问题:
脏读;一个事务读取了另一个没有提交的事务
不可重复性:在同一个事务中。重复读取表中的数据，数据发生了改变
虚度：在同一个事务中，读取到了别人插入的数据。导致前后读取的结果不一致
代码实现
1.开启事务
2.一组业务的执行完毕，提交事务
3.可以在catch语句中显示定义的回滚语句，但是默认失败就会自动的回滚
实现
/*创建表的语句
create table `acount` (
`userid` int(5) not null auto_increment comment '用户的id',
`username` varchar(20) not null comment '用户的名字',
`usermoney` decimal(10) not null comment '用户的存款',
primary key (`userid`)
)engine=innodb charset =utf8;
插入数据
insert into `acount` (`username`,`usermoney`) values('网三',1500),('赵六',2100);
*/
package org.example.lession3;
import org.example.lession02.PreparedSta;
import org.example.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 转账的事务的实现
* */
public class TestTransaction1 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement=null;
//获取连接
try {
connection= JdbcUtils.getConnection();
//关闭自动提交 默认开启事务
connection.setAutoCommit(false);
String sql1="Update `acount` set `usermoney`=`usermoney`-200 where `username`='赵六'";
statement=connection.prepareStatement(sql1);
statement.executeUpdate();
//提交事务
connection.commit();
System.out.println("执行成功");
} catch (SQLException throwables) {
//如果失败的话就回滚 程序默认是回滚的
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
} finally {
JdbcUtils.release(connection,statement,null);
}
}
}
10.8数据库的连接池
数据库的连接 --执行完毕 —释放
连接释放的操作是十分浪费资源的
所以就有了池化的技术
池化技术：准备一些预先的资源，过来就连接准备好的资源
最小连接数
最大连接数
等待超时编写连接池：实现一个接口：DataSource
开源数据源
-
DBCP ：
-
C3P0：
-
Druid：阿里巴巴
使用了数据连接池以后我们在项目的开发中就不需要编写连接数据库的代码了
DBCP
导入的jar包
//maven的pom.xml文件种添加依赖
<dependency>
<groupId>org.apache.directory.studio</groupId>
<artifactId>org.apache.commons.pool</artifactId>
<version>1.6</version>
</dependency>
配置文件：
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/tempdata?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
#
initialSize=10
#最大连接数量
maxActive=50
#
maxIdle=20
#
minIdle=5
#
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意：“user” 与 “password” 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=utf8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
工具类的实现
package org.example.utils;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/*自定义jdbc工具包
* */
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
//读取配置文件
InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.propertise");
Properties properties = new Properties();
//载入配置文件流通道
properties.load(in);
//创建数据源 工厂模式 创建对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
} //获取连接的方法
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放资源的方法
public static void release(Connection con, Statement sta, ResultSet set) {
try {
if (con != null) {
con.close();
}
if (sta != null) {
sta.close();
}
if (set != null) {
set.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
测试代码 使用DBCP连接池添加数据到数据库
package org.example.lession04;
import org.example.utils.JdbcUtils;
import org.example.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
* 测试dbcp数据库连接池
* */
public class TestDBCP {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
try {
//获取连接
connection = JdbcUtils_DBCP.getConnection();
//使用? 占位符代替参数
String sql = "insert into `User`(`userid`,`username`,`password`)values (?,?,?)";
//预编译sql 先写sql然后不执行 返回一个PrepareStatment的对象
PreparedStatement st = connection.prepareStatement(sql);
//手动给参数赋值
st.setInt(1, 2);
st.setString(2, "张三");
st.setString(3, "123456");
//执行操作
int i = st.executeUpdate();
if (i > 0) {
System.out.println("插入成功 ");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtils_DBCP.release(connection, statement, null);
}
}
}
C3P0
C3P0数据源连接池
jar包 c3p0-0.9.5.2 mchange-commons-java-0.2.12
xml配置
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<default-config>
<!-- C3P0的默认的配置 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/day25</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
</default-config>
<named-config name="MySQL">
<!-- C3P0的默认的配置
//在工具类的导入配置文件时传入xml配置文件的相应的的name
dataSource=new ComboPooledDataSource("MySQL");
-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/tempdata</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">10</property>
<property name="checkoutTimeout">3000</property>
</named-config>
</c3p0-config>
– C3P0的工具类
package org.example.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
* C3P0d的工具包
* */
public class JdbcUtilis_C3P0 {
private static ComboPooledDataSource dataSource=null;
static {
//代码版的配置
/*dataSource=new ComboPooledDataSource();
dataSource.setDriverClass();
dataSource.setUser();
dataSource.setPassword();
dataSource.setJdbcUrl();
dataSource.setMaxPoolSize();
dataSource.setMinPoolSize();*/
//导入配置文件
dataSource=new ComboPooledDataSource("MySQL");
}
//获取连接的方法
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放资源的方法
public static void release(Connection con, Statement sta, ResultSet set) {
try {
if (con != null) {
con.close();
}
if (sta != null) {
sta.close();
}
if (set != null) {
set.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
代码实现类 * 测试使用C3P0连接池的插入数据到数据库中
/*
* 测试使用C3P0连接池的插入数据到数据库中
* */
package org.example.lession04;
import org.example.utils.JdbcUtilis_C3P0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestC3P0 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement statement = null;
try {
//获取连接
connection = JdbcUtilis_C3P0.getConnection();
String sql = "insert into `User`(`userid`,`username`,`password`)values(?,?,?)";
//预编译
statement = connection.prepareStatement(sql);
//给参数赋值
statement.setInt(1, 5);
statement.setString(2, "王万");
statement.setString(3, "123456");
//执行
int i = statement.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
JdbcUtilis_C3P0.release(connection,statement,null);
}
}
}
MySQL完结
即使在小的帆也能远航
扫一扫
99
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
