MySQL

本文主要是介绍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的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！