MySQL
2021/7/17 2:05:54
本文主要是介绍MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
1.初始MySQL
1.1新建表
1.2连接数据库
命令行连接
数据库xxx语言
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言
2.操作数据库(了解)
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字不区分大小写
2.1操作数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
2.删除数据库
DROP DATABASE [IF EXISTS] westos;
3.使用数据库
-- Tab键的上面,如果你的表名或者字段名是一个特殊字符,则需要带`` USE `school`;
4.查看数据库
SHOW DATABASES; -- 查看所有的数据库
学习思路
对照sqlyog可视化历史记录查看sql
固定的语法或关键字必须要强行记住!
2.2数据库的列类型
数值
字符串
时间日期
null
2.3数据库的字段属性(重点)
2.4创建数据库表(重点)
-- 注意点,使用英文(),表的名称和字段尽量使用``括起来 -- AUTO_INCREMENT 自增 -- COMMENT 备注 -- 字符串使用''括起来 -- 所有的语句后面加,(英文的),最后一个不用加 -- ENGINE=INNODB 设置引擎 -- DEFAULT CHARSET=utf8 设置编码 CREATE TABLE IF NOT EXISTS `student` ( `id` INT (4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT'出生日期', `address` VARCHAR(100) DEFAULT NULL COMMENT'家庭住址', `email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱', PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
常用命令
SHOW CREATE DATABASE school -- 查看创建数据库的语句 SHOW CREATE TABLE student -- 查看student数据表的定义语句 DESC student -- 显示数据表的结构
2.5数据表的类型
-- 关于数据库引擎 /* INNODB 默认使用~ MYISAM 早些年使用 */
| MYISAM | INNODB | |
|---|---|---|
| 事物支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大,约为两倍 |
常规使用操作
MYSIAM 节约空间,速度较快
INNODB 安全性高,事物的处理,多表多用户操作
在物理空间存在的位置
设置数据库表的字符集编码
DEFAULT CHARSET=utf8
2.6修改删除表
修改
-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名ALTER TABLE teacher RENAME AS teacher1-- 添加表的字段 ALTER TABLE 表名 ADD 字段名 字段列属性ALTER TABLE teacher1 ADD age INT(4)-- 修改表的字段(重命名,修改约束!)-- ALTER TABLE 表名 MODIFY 列名 列属性[]ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]ALTER TABLE teacher1 CHANGE age age1 INT(4) -- 重命名-- 删除表的字段 ALTER TABLE 表名 DROP 字段名ALTER TABLE teacher1 DROP age1
删除
-- 删除表(如果存在在删除)DROP TABLE IF EXISTS teacher1
所有的创建和删除操作,尽量加上判断,以免报错
注意点:
-
``字段名,使用这个包裹
-
注释 -- /**/
-
sql关键字大小写不敏感,建议大家写小写
3.MySQL数据管理
3.1外键(了解即可)
方式一,在创建表的时候增加约束(麻烦,比较复杂)
CREATE TABLE IF NOT EXISTS `grade`(`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT'年级id',`gradename` VARCHAR(20) NOT NULL COMMENT'年级名称',PRIMARY KEY(`gradeid`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 学生表的gradeid字段 要去引用年级表的gradeid-- 定义外键key-- 给这个外键添加约束(执行引用) references 引用CREATE TABLE IF NOT EXISTS `student` ( `id` INT (4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR (2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `gradeid` INT(10) NOT NULL COMMENT'学生的年级', `address` VARCHAR (100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR (50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`), KEY `FK_gradeid`(`gradeid`), CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`) ) ENGINE = INNODB DEFAULT CHARSET=utf8
删除有外键关系的表的时候,必须要先删除引用别人的表(从表),再删除被引用的表(主表)
方式二
CREATE TABLE IF NOT EXISTS `student` ( `id` INT (4) NOT NULL AUTO_INCREMENT COMMENT '学号', `name` VARCHAR (30) NOT NULL DEFAULT '匿名' COMMENT '姓名', `pwd` VARCHAR (20) NOT NULL DEFAULT '123456' COMMENT '密码', `sex` VARCHAR (2) NOT NULL DEFAULT '女' COMMENT '性别', `birthday` DATETIME DEFAULT NULL COMMENT '出生日期', `gradeid` INT(10) NOT NULL COMMENT'学生的年级', `address` VARCHAR (100) DEFAULT NULL COMMENT '家庭住址', `email` VARCHAR (50) DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`id`) ) ENGINE = INNODB DEFAULT CHARSET=utf8-- 创建表的时候没有外键约束ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
以上操作都是物理外键,数据库级别的外键,我们不建议使用!(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
3.2DML语言(全部记住)
3.3添加
insert
-- 插入语句(添加)-- insert into 表名([字段1,字段2],字段3]) values('值1'),('值2'),('值3'),······INSERT INTO grade(`gradename`) VALUES(('大三'))-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）INSERT INTO grade VALUES(('大三'))-- 一般写插入语句，我们一定要数据和字段一一对应！-- 插入多个字段INSERT INTO `grade`(`gradename`)VALUES('大二'),('大一')INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张收纳','123456','男'),('李四','456123','女'),('小芳','159874','女')INSERT INTO `student`(`birthday`,`address`,`email`)VALUES('2018-12-01','afdsasdf','asdffsd'),('2019-4-12','adsf','adfasdf')
语法:insert into 表名([字段1,字段2],字段3]) values('值1'),('值2'),('值3'),······
注意事项:
-
字段和字段之间使用英文逗号 隔开
-
字段是可以省略的,但是后面的值必须要一一对应,不能少
-
可以同时插入多条数据,VALUES后面的值,需要使用,隔开即可
values(),()......
3.4修改
update 修改谁 (条件) set原来的值=新值
-- 修改学员名字,带了条件UPDATE`student` SET NAME='小黎明' WHERE id = 1-- 不指定条件的情况下,会改动所有表!UPDATE `student` SET sex='男'-- 修改多个属性,隔开UPDATE `student` SET NAME='下山',email='14asdf' WHERE id = 2-- 语法:-- update 表名 set column_name = value,[column_name = value,''''] where[条件]UPDATE`student` SET NAME='黎明' WHERE id BETWEEN 2 AND 6-- 通过多个条件定位数据UPDATE `student` SET NAME = '张三' WHERE `name`='小黎明' AND sex='男'
条件:where字句 运算符 id等于某个值,大于某个值,在某个区间内修改.....
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或!= | 不等于 | 5!=6 | true |
| > | |||
| < | |||
| <= | |||
| >= | |||
| BETWEEN...AND... | 在某个范围内 | [2,5] | |
| AND | 我和你&& | 5>1 and 1>2 | false |
| OR | 我或你!! | 5>1 or 1>2 | true |
注意:
-
column_name是数据库的列,尽量带上``
-
条件,筛选的条件,如果没有指定,则会修改所有的列
-
value是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE NAME='黎明' AND sex='男'
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 `test`(`id` INT(4) NOT NULL AUTO_INCREMENT,`coll` VARCHAR(20) NOT NULL,PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')DELETE FROM `test` -- 不会影响自增TRUNCATE TABLE `test` -- 自增会归零
4.DQL查询数据(最重点)
4.1DQL
select完整语法
注:顺序不能改变
4.2查询指定字段
语法:select 字段 ...... from 表
有时候，列名字不是那么的见名知意，我们起别名 AS 字段名 as 别名 表名 as 别名
去重,distinct
作用，去除select查询出来的结果中,重复的数据,只显示一条
-- 查询全部学生 select 字段 from 表SELECT *FROM student-- 查询指定字段SELECT `student_id`,`student_name` FROM studnet-- 别名,给结果起一个名字 AS 可以给字段起别名也可以给表起别名SELECT `student_id` AS 学号,`student_name` AS 学生姓名 FROM student-- 函数Concat(a,b)SELECT CONCAT('姓名:',`student_name`) AS 学生名字 FROM student-- 查询有哪些班级SELECT DISTINCT `student_class` FROM student -- 发现重复数据,去重
数据库的列(表达式)
SELECT VERSION() -- 查询系统版本(函数)SELECT 1256*3/2-1 AS 计算结果 -- 用来计算(表达式)SELECT @@auto_increment_increment -- 查询自增的步长(变量)-- 学生班级号+1 看看SELECT `student_id`,`student_class`+1 AS '改变班级后' FROM student
数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量,系统变量.....
select 表达式 from 表
4.3where 条件子句
作用:检索数据中符合条件的值
搜素的条件由一个或者多个表达式组成!
逻辑运算符
尽量使用英文字母
-- =================where=============================-- 查询宿舍号在101-109之间的 ANDSELECT `dorm_id`,`dorm_name` AS 宿舍号 FROM dormWHERE `dorm_name` >= 101 AND `dorm_name` <= 109SELECT `dorm_id`,`dorm_name` AS 宿舍号 FROM dormWHERE `dorm_name` >= 101 && `dorm_name` <= 109-- 模糊查询(区间)SELECT `dorm_id`,`dorm_name` AS 宿舍号 FROM dormWHERE `dorm_name` BETWEEN 101 AND 109-- 除宿舍号为101以外的宿舍SELECT `dorm_id`,`dorm_name` FROM dormWHERE `dorm_name`!= 101SELECT `dorm_id`,`dorm_name` FROM dormWHERE NOT `dorm_name` = 101
模糊查询:比较运算符
-- ===================模糊查询====================-- 查询姓张的同学-- like结合 %(代表0到任意个字符) _(代表一个字符)SELECT `student_id`,`student_name` FROM studentWHERE `student_name` LIKE('张%')-- 查询姓张的同学,后面只有一个字的SELECT `student_id`,`student_name` FROM studentWHERE `student_name` LIKE('张_')-- 查询姓张的同学,后面只有两个字的SELECT `student_id`,`student_name` FROM studentWHERE `student_name` LIKE('张__')-- 查询名字中带三的同学SELECT `student_id`,`student_name` FROM studentWHERE `student_name` LIKE('%三%')-- =========in(具体的一个或者多个值)==========-- 查询学号为30,31,32的学生SELECT `student_id`,`student_name` FROM studentWHERE `student_id` IN(30,31,32)-- 查询专业为信息与计算科学或软件工程的学生SELECT `student_id`,`student_name`,`student_major` FROM studentWHERE `student_major` IN('信息与计算科学','软件工程')-- =========NULL NOT NULL===========-- 查询楼宇为空的学生SELECT `student_id`,`student_name`,`student_building` FROM studentWHERE `student_building`='' OR `student_building` IS NULL-- 查询有住宿时间的同学 不为空SELECT `student_id`,`student_name`,`out_date` FROM studentWHERE `out_date` IS NOT NULL-- 查询毕业的同学SELECT `student_id`,`student_name`,`student_class`,`student_remark` FROM studentWHERE `student_remark` LIKE('毕业%')SELECT `student_id`,`student_name`,`student_class`,`student_remark` FROM studentWHERE `student_class` IN(1104,1103)SELECT `student_id`,`student_name`,`student_class`,`student_remark` FROM studentWHERE `student_class` IS NOT NULL
4.4联表查询
Join对比
-- ===============联表查询join============/*思路1.分析需求,分析查询的字段来自哪些表.(连接查询)2.确定使用哪种连接查询?7种确定交叉点(这两个表中哪个数据是相同的)判断的条件楼宇表中的building_id = 宿舍楼宇中间表 building_id*/SELECT b.building_id,building_name,building_remarkFROM building AS bINNER JOIN dorm_building AS dWHERE b.building_id = d.building_id-- right joinSELECT b.building_id,building_name,building_remarkFROM building AS bRIGHT JOIN dorm_building AS dON b.building_id = d.building_id-- left joinSELECT b.building_id,building_name,building_remarkFROM building AS bLEFT JOIN dorm_building AS dON b.building_id = d.building_id
-- ===============联表查询============/*思路1.分析需求,分析查询的字段来自哪些表.(连接查询)2.确定使用哪种连接查询?7种确定交叉点(这两个表中哪个数据是相同的)判断的条件楼宇表中的building_id = 宿舍楼宇中间表 building_id*/-- join(连接的表) on(判断的条件) 连接查询-- where 等值查询SELECT b.building_id,building_name,building_remark,dorm_idFROM building AS bINNER JOIN dorm_building AS dWHERE b.building_id = d.building_id-- right joinSELECT b.building_id,building_name,building_remark,dorm_idFROM building AS bRIGHT JOIN dorm_building AS dON b.building_id = d.building_id-- left joinSELECT b.building_id,building_name,building_remark,dorm_idFROM building AS bLEFT JOIN dorm_building AS dON b.building_id = d.building_id-- 查询未选择宿舍的人SELECT b.`building_id`,`building_name`,`building_remark`,`dorm_id`FROM building AS bLEFT JOIN dorm_building AS dON b.building_id = d.building_idWHERE `dorm_id`IS NULL-- 查询楼宇名`building_name`,宿舍类型`dorm_type`,楼宇号`building_id`,宿舍号`dorm_id`/*思路1.分析需求,分析查询的字段来自哪些表.(building,dorm,dorm_building)(连接查询)2.确定使用哪种连接查询?7种确定交叉点(这两个表中哪个数据是相同的)判断的条件楼宇表中的building_id = 宿舍楼宇中间表 building_id*/SELECT `building_name`,`dorm_type`,b.`building_id`,r.`dorm_id`FROM building AS bLEFT JOIN `dorm_building` AS dON b.building_id = d.building_idINNER JOIN dorm AS rON d.dorm_id = r.dorm_id-- 我要查询哪些数据select...-- 从哪几个表中查 from 表 xxx Join 连接的表 on 交叉条件-- 假设存在一种多张表查询,慢慢来,先查询两张表然后再慢慢增加-- from a left join b-- from a right join b
自连接
自己的表和自己的表连接,核心:一张表拆为两张一样的表即可
操作:查询父类对应的子类关系
注:父类的categoryid等于子类的pid
-- ===========自连接==================CREATE TABLE IF NOT EXISTS `category` (`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',`pid` INT(10) NOT NULL COMMENT '父id',`categoryname` VARCHAR(50) NOT NULL COMMENT '主题名字',PRIMARY KEY(`categoryid`))ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8INSERT INTO `category`(`categoryid`,`pid`,`categoryname`)VALUES('2','1','信息技术'),('3','1','软件开发'),('4','3','数据库'),('5','1','美术设计'),('6','3','web开发'),`category`('7','5','ps技术'),('8','2','办公信息');SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'FROM `category` AS a,`category` AS bWHERE a.categoryid = b.pid
4.5分页和排序
排序
-- 排序,升序asc,降序desc-- ORDER BY通过哪个字段排,怎么排-- 查询的结果根据宿舍号降序排序SELECT `building_name`,`dorm_type`,b.`building_id`,r.`dorm_id`FROM building AS bLEFT JOIN `dorm_building` AS dON b.building_id = d.building_idINNER JOIN dorm AS rON d.dorm_id = r.dorm_idORDER BY `dorm_id` DESC
分页
-- 100万-- 问什么要分页?-- 缓解数据库压力,给人的体验更好,瀑布流-- 分页,每页只显示五条数据-- 语法:limit起始值,页面的大小-- 网页应用:当前,总的页数,页面的大小SELECT `building_name`,`dorm_type`,b.`building_id`,r.`dorm_id`FROM building AS bLEFT JOIN `dorm_building` AS dON b.building_id = d.building_idINNER JOIN dorm AS rON d.dorm_id = r.dorm_idWHERE dorm_type = '四人间'ORDER BY `dorm_id` ASCLIMIT 5,5 -- 第一页 limit 0,5-- 第二页 limit 5,5-- 第三页 limit 10,5-- 第n页 limit (n-1)*5,5 (n-1)*pagesize,pagesize-- [pagesize:页面大小]-- [(n-1)*pagesize:起始值]-- [n:当前页]-- [数据总数/页面大小=总页数]
-- 思考:-- 查询java第一学年 课程成绩排名前十的学生,并且分数要大于80 的学生信息(学号,姓名,课程名称,分数)SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`FROM `student` AS sINNER JOIN `result` AS rON s.`StudentNo` = r.`StudentNo`INNER JOIN `subject` AS subON r.`SubjectNo` = sub.`SubjectNo`WHERE `SubjectName`='JAVA第一学年' AND `StudentResult`>80ORDER BY `StudentResult` DESCLIMIT 0,10
语法:limit(查询起始下标,pagesize)
4.6子查询
-- =========where=============-- 查询数据库结构-1 的所有考试结果(学号,科目名字,成绩),降序排列-- 方式一:使用连接查询SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`FROM result AS rINNER JOIN SUBJECT AS subON r.`SubjectNo` = sub.`SubjectNo`WHERE `SubjectName` = '数据库结构-1'ORDER BY `StudentResult` DESC-- 方式二: 使用子查询(由里及外)-- 查询数据库结构-1 的所有考试结果SELECT `StudentNo`,`SubjectNo`,`StudentResult`FROM result WHERE `SubjectNo` = (SELECT `SubjectNo` FROM `subject`WHERE `SubjectName` = '数据库结构-1' )ORDER BY `StudentResult` DESC-- 查询课程为高等数学-2，且分数不小于80的同学的学号和姓名SELECT DISTINCT s.`StudentNo`,`StudentName` FROM student AS sINNER JOIN result AS rON s.`StudentNo` = r.`StudentNo` INNER JOIN `subject` AS subON r.`SubjectNo` = sub.`SubjectNo`WHERE `StudentResult` >= 80 AND sub.`SubjectName` = '高等数学-2'-- 分数不小于80分的学生的学号和姓名SELECT DISTINCT s.`StudentNo`,`StudentName` FROM student AS sINNER JOIN result AS rON s.`StudentNo` = r.`StudentNo` WHERE `StudentResult` >= 80-- 在这个基础上增加一个科目，高等数学-2-- 查询高等数学-2 的编号SELECT DISTINCT s.`StudentNo`,`StudentName` FROM student AS sINNER JOIN result AS rON s.`StudentNo` = r.`StudentNo` WHERE `StudentResult` >= 80 AND `SubjectNo` = (SELECT `SubjectNo` FROM `subject`WHERE `SubjectName` = '高等数学-2')-- 再改造(由里及外)SELECT DISTINCT `StudentNo`,`StudentName` FROM student WHERE `StudentNo` IN(SELECT `StudentNo` FROM `result` WHERE `StudentResult` >= 80 AND `SubjectNo` = (SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = '高等数学-2'))-- 练习,查询c语言-1前五名同学的成绩的信息（学号,姓名,分数）SELECT r.`StudentNo`,`StudentName`,`StudentResult` FROM result AS rINNER JOIN `student` AS sON r.`StudentNo` = s.`StudentNo`INNER JOIN `subject` AS subON sub.`SubjectNo` = r.`SubjectNo`WHERE `SubjectName`='C语言-1' ORDER BY `StudentResult` DESCLIMIT 0,5
4.7分组和过滤
-- 查询不同课程的平均分,最高分,最低分,平均分>=80-- 核心(根据不同的课程分组)SELECT `SubjectName`,AVG(`StudentResult`) AS '平均分',MAX(`StudentResult`) AS '最高分',MIN(`StudentResult`) AS '最低分' FROM result AS rINNER JOIN `subject` AS subWHERE r.`SubjectNo` = sub.`SubjectNo`GROUP BY r.`SubjectNo` -- 通过什么字段来分组HAVING '平均分'>=80
4.8Select小结
5.MySQL函数
官网:https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
5.1常用函数
-- =================常用函数===========SELECT ABS(-8) -- 绝对值SELECT CEILING(9.4) -- 向上取整SELECT FLOOR(9.4) -- 向下取整SELECT RAND() -- 返回一个0~1之间的随机数SELECT SIGN(9) -- 判断一个数的符号 0-0 负数返回-1,正数返回 1-- 字符串函数SELECT CHAR_LENGTH('即使再小的帆也能远航') -- 字符串长度SELECT CONCAT('我','爱','你们') -- 拼接字符串SELECT INSERT('我喜欢编程helloworld',1,2,'超级热爱') -- 查询,从某个位置开始替换某个长度SELECT LOWER('SCxlm')-- 小写字母SELECT UPPER('SCxlm')-- 大写字母SELECT INSTR('huangsheng','h')-- 返回第一次出现的子串的索引SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 替换出现的指定字符串SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 返回指定的子字符串(源字符串,截取的位置,截取的长度)SELECT REVERSE('我把名字倒过来') -- 反转-- 查询姓周的同学,名字 邹SELECT REPLACE(studentname,'周','邹') FROM studentWHERE `StudentName` LIKE('周%')-- 时间和日期函数(记住)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 SYSTEM_USER() -- 获取系统用户SELECT USER()SELECT VERSION()-- 获取版本号
5.2聚合函数(常用)
| 函数名称 | 描述 |
|---|---|
| COUNT()**** | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| ...... | ...... |
-- ==========集合函数================-- 都能够统计 表中的数据(想查询一个表中有多少个记录,就使用这个count())SELECT COUNT(`ExamDate`) FROM result -- Count(字段),会忽略所有的null值SELECT COUNT(*) FROM student -- Count(*),不会忽略null值,本质计算行数SELECT COUNT(1) FROM result -- Count(1),不会忽略所有null值,本质计算行数SELECT SUM(`StudentResult`) AS 总分 FROM `result`SELECT AVG(`StudentResult`) AS 平均分 FROM `result`SELECT MAX(`StudentResult`) AS 最高分 FROM `result`SELECT MIN(`StudentResult`) AS 最低分 FROM `result`
5.3数据库级别的MD5加密
什么是MD5?
主要增强算法复杂度和不可逆性.
MD5不可逆,具体的值,MD5是一样的
MD5破解网站的原理,背后有一个字典,MD5加密后的值,加密的前提
-- ===========测试MD5加密==========CREATE TABLE `testmd5`(`id` INT(4) NOT NULL COMMENT '用户id',`name` VARCHAR(20) NOT NULL COMMENT '用户名',`pwd` VARCHAR(50) NOT NULL COMMENT '用户密码',PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 明文密码INSERT INTO testmd5 VALUES(1,'张三','1234546'),(2,'李四','3454678'),(3,'王五','654321')-- 加密UPDATE testmd5 SET pwd=MD5(`pwd`) WHERE id =1UPDATE testmd5 SET pwd=MD5(`pwd`) -- 加密全部的数据-- 插入的时候加密INSERT INTO testmd5 VALUES(4,'天津',MD5('456789'))-- 如何校验:将用户传递来的密码,进行md5加密,然后比对加密后的值SELECT * FROM testmd5 WHERE NAME='张三' AND pwd=(MD5('123456'))
6.事物
6.1什么是事物?
要么都成功,要么都失败
1、SQL执行，A给B转账 A 1000 -----> B 200
2、SQL执行，B收到A转账 A 800 -----> B 400
将一组SQL放在一个批次中执行~
事务原则：ACID原则 原子性，一致性，隔离性，持久性 （脏读，幻读......）
参考博客链接:https://blog.csdn.net/dengjili/article/details/82468576
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性要保持一致,1000
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）--事务提交
事务一旦提交则不可逆，被持久化到数据库中！
隔离所导致的问题
脏读：
指一个事务读取了另外一个事务未提交的数据。
不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
执行事务
-- =============事务================-- mysql 是默认开启事务自动提交的SET autocommit = 0/*关闭*/SET autocommit = 1/*开启(默认的)*/-- 手动处理事务SET autocommit = 0-- 事务开启START TRANSACTION -- 标记一个事务的开始,从这个之后的sql都在同一个事务内INSERT xxINSERT xx-- 提交:持久化(成功！)COMMIT-- 回滚:回到原来的样子（失败!）ROLLBACK-- 事务结束SET autocommit = 1 -- 开启自动提交-- 了解SAVEPOINT 保存点名 -- 设置一个事务的保存点ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点RELEASE SAVEPOINT 保存点名 -- 撤销保存点
模拟场景
-- 转账CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ciUSE shopCREATE TABLE `account`(`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT'用户id',`name`VARCHAR(20) NOT NULL COMMENT'用户名',`money` DECIMAL(9,2) NOT NULL COMMENT'转账金额',PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO account(`name`,`money`)VALUES('A',2000),('B',1000)-- 模拟转账，事务SET autocommit = 0 -- 关闭自动提交START TRANSACTION -- 开启一个事物（一组事务）UPDATE account SET money = money - 500 WHERE `name`='A' -- A减500UPDATE account SET money = money + 500 WHERE `name`='B' -- B加500COMMIT -- 提交事务,事务一旦提交，就被持久化了ROLLBACK -- 回滚SET autocommit = 1 -- 恢复默认值
7.索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。0.5s 0.00001s
提取句子主干，就可以得到索引的本质:索引是数据结构。
7.1索引的分类
在一个表中，之间索引只能有一个，唯一索引可以有多个
- 主键索引(PRIMARY KEY)
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复。多个列都可以标识唯一索引
- 常规索引（KEY/INDEX）
- 默认的,index.key关键字来设置
- 全文索引（FullText）
- 在特定的数据引擎下才有，MyISAM
- 快速定位数据
基础语法
-- 索引的使用-- 1.在创建表的时候给字段增加索引-- 2.创建完毕后，增加索引-- 显示所有的索引信息SHOW INDEX FROM `student`-- 增加一个全文索引 (索引名) 列名ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`)-- EXPLAIN 分析sql执行的状况EXPLAIN SELECT * FROM student -- 非全文索引EXPLAIN SELECT * FROM student WHERE MATCH(`StudentName`) AGAINST('刘')
7.2测试索引
CREATE TABLE `app_user1`(`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 NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'-- 插入100万数据DELIMITER $$ -- 写函数之前必须要写,标志CREATE FUNCTION mock_data2()RETURNS INTBEGIN DECLARE num INT DEFAULT 1000000;-- 注:电脑配置不够好，不要插这么多数据 DECLARE i INT DEFAULT 0; WHILE i < num DO INSERT INTO `app_user1`(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'123456789@qq.com' ,CONCAT('158',FLOOR(RAND()*((999999999-100000000)+100000000))) ,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100)); END WHILE; RETURN i;END;SELECT mock_data2();SELECT * FROM app_user1 WHERE `name`='用户9999';-- 1.093 secDELETE FROM app_user1-- id_表名_字段名-- create index 索引名 on 表(字段)CREATE INDEX id_app_user_name ON app_user1(`name`)SELECT * FROM app_user1 WHERE `name`='用户9999';-- 0.001 secEXPLAIN SELECT * FROM app_user1 WHERE `name` = '用户9999'
索引在小数据量的时候,用处不大,但是在大数据的时候,区别十分明显~
7.3索引原则
- 索引不是越多越好
- 不要对经常变动数据加索引
- 小数据量的表必须要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree:innoDB的默认数据结构
阅读:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
8.权限管理和备份
8.1用户管理
SQL yog可视化管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户 CREATE USER 用户名 IDENTIFIED BY 密码
CREATE USER kuangsheng IDENTIFIED BY '123456'
-- 修改密码(修改当前一用户密码)
SET PASSWORD = PASSWORD('123456')
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR kuangshen = PASSWORD('123456')
-- 重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER kuangsheng TO scxlm
-- 用户授权 ALL PRIVILEGES 全部的权限,库.表
-- ALL PRIVILEGES 除了给别人授权,其他都能够干
GRANT ALL PRIVILEGES ON *.* TO scxlm
-- 查询权限
SHOW GRANTS FOR scxlm -- 查看指定用户权限 GRANT ALL PRIVILEGES ON *.* TO 'scxlm'@'%'
SHOW GRANTS FOR root@localhost
-- root用户权限: GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
-- 撤销权限 revoke 哪些权限,在哪个库撤销,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM scxlm
-- 删除用户
DROP USER kuangsheng
8.2MySQL备份
为什么要备份:
- 保证重要的数据不丢失
- 数据转移
MySQL数据库备份的方式:
-
直接拷贝物理文件
-
在Sqlyog这种可视化工具中手动导出
- 在想要导出的表或者数据库中,右键,选择备份或者导出
-
使用命令行导出 mysqldump 命令行使用
-- 导出一张表 # mysqldump -h 主机 -u 用户名 -p 密码 数据库 表名 >物理磁盘位置/表名 C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. -- 导出多张表 # mysqldump -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 >物理磁盘位置/表名 C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. -- 导出数据库 C:\Users\Administrator>mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. #导入 #登录的情况下,切换到指定的数据库 #source 备份文件 mysql> use school mysql> source D:/a.sql
假设你要备份数据库,防止数据丢失!
把数据库给朋友!sql文件给别人即可!
9.规范数据库设计
9.1为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计：
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计：设计关系图E-R图
设计数据库的步骤：（个人博客）
-
收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章的信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段）key：value
- 说说表（发表心情 id......content......create_time）
-
标识实体（把需求落地到每个字段）
-
标识实体之间的关系
- 写博客:user-->blog
- 创建分类:user-->category
- 关注:user-->user
- 友链:links
- 评论:user-user-blog
9.2.三大范式
-
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
- 无法正常显示信息
-
删除异常
- 丢失有效的信息
三大范式
第一范式（1NF）
原子性:保证每一列不可再分
第二范式（2NF）
前提：满足第一范式
每张表只描述一件事情
第三范式（3NF）
满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
（规范数据库的设计）
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标，（成本，用户体验！）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性！
- 故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
- 故意增加一些计算列（从大数据量降为小数据量的查询:索引）
10.JDBC（重点）
10.1数据库驱动
驱动:声卡，显卡，数据库
我们的程序会通过数据库驱动和数据库打交道！
10.2JDBC
SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java）操作数据库规范，俗称JDBC，这些规范的实现由具体的厂商去做~
对于开发人员来说，我们只需要掌握JDBC接口的操作即可！
java.sql
javax.sql
还需要导入一个数据库驱动包 mysql-connector-java-5.1.41.jar
10.3第一个JDBC程序
1.创建一个普通项目
2.导入数据库驱动
package com.kuang.lesson01;
import java.sql.*;
//我的第一个JDBC程序
public class JdbcFirstDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2.用户信息和url
//useUnicode = true 支持中文编码
//characterEncoding=utf8 设置字符集为utf8
//useSSL=true 使用安全连接
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode = true&characterEncoding=utf8&&useSSL=true";
String username="root";
String password = "123456";
//3.连接成功，数据库对象 Connection代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象
Statement statement = connection.createStatement();
//5.执行SQL的对象去执行SQL，可能存在结果，查询返回结果
String sql= "select * from users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
System.out.println("id="+ resultSet.getObject("id"));
System.out.println("name="+ resultSet.getObject("name"));
System.out.println("password="+ resultSet.getObject("password"));
System.out.println("email="+ resultSet.getObject("email"));
System.out.println("birth="+ resultSet.getObject("birthday"));
System.out.println("===================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
1、加载驱动
2、连接数据库DriverManager
3、获取执行sql的对象 Statement
4、获得返回的结果集 注：只有查询有结果集
5、释放连接
DriverManager
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
//connection 代表数据库
//事务提交
//事务回滚
//数据库设置自动提交
connection.commit();
connection.rollback();
connection.setAutoCommit(true);
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncording=utf8&useSSL=true"; //mysql -- 3306 //协议://主机地址:端口号/数据库名?参数1&参数2&参数3 //oracle --1521 //jdbc:oracle:thin:@localhost:1521:sid
Statement执行SQL的对象 prepareStatement执行SQL的对象
String sql = "select * from users"; //编写sql
statement.executeQuery();//查询操作 返回ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//更新、插入、删除。都是用这个，返回一个受影响的行数
ResultSet查询的结果集:封装了所有的查询结果
获得指定的数据类型
resultSet.getObject();//在不知道类型的情况下使用
//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
......
遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到下一行
resultSet.absolute(row);//移动到指定行
释放资源
//6.释放连接 resultSet.close(); statement.close(); connection.close();//耗资源,用完关掉！
10.4statement对象
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库友送增、删、改的SqlI语句，exeluleupuate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
代码实现
1、提取工具类
package com.kuang.lesson2.utils;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"); 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(); } } }}
2、编写增删改的方法，executeUpdate()
package com.kuang.lesson2;import com.kuang.lesson2.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 users(`id`,`name`,`password`,`email`,`birthday`)" + "VALUES(4,'kuangshen','123456','kuangsehn@qq.com','2021-07-15')" ; int i = st.executeUpdate(sql); if (i>0){//受影响行数>0 System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
package com.kuang.lesson2;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestDelete {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 = "DELETE FROM users WHERE id=1"; int i=st.executeUpdate(sql); if (i>0){ System.out.println("删除成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
package com.kuang.lesson2;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class TestUpdate { public static void main(String[] args) { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); String sql = "UPDATE users SET `name`='啦啦啦',`password`='789456' WHERE id=2"; int i = st.executeUpdate(sql); if (i>0){ System.out.println("更新成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
3.查询
package com.kuang.lesson2;import com.kuang.lesson2.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 users"; rs = st.executeQuery(sql); while (rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getDate("birthday")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
4.SQL注入问题
sql存在漏洞,会被攻击导致数据泄露，SQL会被拼接or
package com.kuang.lesson2;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class SQL注入 { public static void main(String[] args) { //login("kuangshen","123456"); login("'or'1=1","'or'1=1"); } //登录业务 public static void login(String username,String password){ Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); //SELECT * FROM users WHERE `name`='wangwu' AND `password`='123456' String sql = "select * from users where `name`='"+username+"' AND `password`='"+password+"'"; rs = st.executeQuery(sql); while(rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
10.5PreparedStatement对象
PreparedStatement可以防止SQL注入，效率更好！
1、新增
package com.kuang.lesson3;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.util.Date;import java.sql.PreparedStatement;import java.sql.SQLException;import static com.kuang.lesson2.utils.JdbcUtils.*;public class TestInsert { public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; try { conn = getConnection(); //区别 //使用？占位符代替参数 String sql =" INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)"; ps = conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行 //手动给参数赋值 ps.setInt(1,5); ps.setString(2,"小白龙"); ps.setString(3,"456789"); ps.setString(4,"lala@qq.com"); //注意点：sql.Date 数据库 java.sql.Date() // util.Date Java new Date().getTime()获得时间戳 ps.setDate(5,new java.sql.Date(new Date().getTime())); //int i int i = ps.executeUpdate(); if(i>0){ System.out.println("插入成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,ps,null); } }}
2、删除
package com.kuang.lesson3;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import static com.kuang.lesson2.utils.JdbcUtils.*;public class TestDelete { public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs =null; try { conn = getConnection(); String sql ="DELETE FROM users WHERE id=?"; ps = conn.prepareStatement(sql); ps.setInt(1,1); int i = ps.executeUpdate(); if(i>0){ System.out.println("删除成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,ps,rs); } }}
3、更新
package com.kuang.lesson3;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestUpdate { public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "UPDATE users SET `name`='啦啦啦',`password`='789456' WHERE id=?"; ps = conn.prepareStatement(sql); ps.setInt(1,3); int i =ps.executeUpdate(); if (i>0){ System.out.println("修改成功"); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,ps,rs); } }}
4、查询
package com.kuang.lesson3;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestSelect { public static void main(String[] args) { Connection conn = null; PreparedStatement ps =null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); String sql = "select * from users where id=?";//编写SQL ps = conn.prepareStatement(sql);//预编译 ps.setInt(1,3);//传递参数 rs = ps.executeQuery();//执行 while (rs.next()){ System.out.println(rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } }}
5.防止SQL注入
package com.kuang.lesson3;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.*;public class SQL注入 { public static void main(String[] args) { //login("kuangshen","123456"); login("'or'1=1","'or'1=1");//Process finished with exit code 0 } //登录业务 public static void login(String username,String password){ Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符 //假如其中存在转义字符,比如说'会被直接转义 String sql = "select * from users where `name`=? and `password`=?"; st = conn.prepareStatement(sql); st.setString(1,username); st.setString(2,password); rs = st.executeQuery();//查询完毕会返回一个结果集 while(rs.next()){ System.out.println(rs.getString("name")); System.out.println(rs.getString("password")); } } catch (SQLException e) { e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } }}
10.7使用IDEA连接数据库
连接成功后,可以选择数据库
更新数据
10.8事物
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来结果不一致
代码实现
1、开启事务conn.setAutoCommit(false);
2、一组业务执行完毕，提交事务
3、可以在catch语句中显示的定义回滚语句，但默认失败就会回滚
package com.kuang.lesson4;import com.kuang.lesson2.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestTransaction2 { public static void main(String[] args) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); //关闭数据库的自动提交功能,自动开启事务 conn.setAutoCommit(false);//开启事务 String sql1 = "update account set money = money - 100 where name = 'A'"; ps = conn.prepareStatement(sql1); ps.executeUpdate(); int x = 1/0;//报错 String sql2 ="update account set money = money + 100 where name = 'B'"; ps = conn.prepareStatement(sql2); ps.executeUpdate(); //业务完毕,提交事务 conn.commit(); System.out.println("成功!"); } catch (SQLException e) { //如果失败,则默认回滚// /* try {// conn.rollback();//如果事务失败则回滚事务// } catch (SQLException ex) {// ex.printStackTrace();// }*/ e.printStackTrace(); }finally { JdbcUtils.release(conn,ps,rs); } }}
这篇关于MySQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017，VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
