sql基础知识

连接数据库

mysql -u root -p123456--连接数据库

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';   --修改密码

fiush privileges;刷新权限
---所有语句都用;结尾




use  school-- use + 数据库名


show tabels;--查看数据库所有的表
describe  student;--显示数据库中所有表的信息

create database;

exit;--退出

--  sql单行注释 
/*
*/(sql多行注释)

数据库XXX语言（CRUD增删改查）

DDL 定义

DML 操作

DQL 查询

DCL 控制

2.操作数据库

操作数据库>操作数据库中的表>操作数据库中表的数据

mysql不区分大小写

2.1操作数据库

1.创建数据库

create database + 数据库名字

CREATE DATABASE IF NOT EXISTS+ 名字 -- 防止已经有这个数据库了

2.删除数据库

DROP database if exist + 名字

3.使用数据库

use + `数据库名字` --如果表名或者字段名是特殊字符 +``

4.查看数据库

show databases --查看所有的数据库

2.2数据库的数据类型

数值

·tinyint 十分小的数据 1个字节

·smallint 较小的数据 2个字节

mediumintint 中等大小的数据 3字节

·int 标准的整数 4个字节

·big 较大的数据 8字节

float 浮点数 4字节

·double 浮点数 8字节

·decimall 字符串形式的浮点数金融计算的时候使用

字符串

·char类型

·varchar 可变字符串 对应String

tinytext 微型文本 2^8-1

text 文本串 2^16-1 报存大文本

时间日期

java.util.date

date YYYY-MM-DD 日期格式

time HH:mm:ss 时间格式

datetime YYYY-MM-DD HH:mm:ss

timestamp 时间戳 1970到现在的毫秒

year 年份表示

null

没有值为止

==不要用null计算

2.3数据库的字段属性（重点)

Unsigned：无符号的整数不能声明为负数

声明了该列不能为负数

zerofill

0填充的不足的位数使用0填充 int(3) 5---> 005

自增

自动在上一条基础上加一

通常用来设计唯一的主键 -index 必须是整数类型

可以自定义设主键的起始值和步长

-非空 NUll not null

假设设置了not null 如果不赋值则会报错

设置了NUll 不赋值默认是null

默认：

设置默认的值 sex:默认值为：男如果不指定则就是男

每一个表必须存在以下五个字段未来做项目的应用表示每一条记录存在意义

id 主键

'VERSON' 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间

2.4创建数据库表

-- 目标：创建一个school数据库

CREATE TABLE IF NOT EXISTS `student2` (
     `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
        `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
        `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
        `sex` VARCHAR(2) NOT NULL DEFAULT '女`' COMMENT '性别',
        `birthday` DATETIME DEFAULT NULL COMMENT '生日',
         `addrsess` VARCHAR(10) DEFAULT NULL COMMENT '家庭住址',

        PRIMARY KEY (`id`)
)ENGINE =INNODB DEFAULT CHARSET= utf8

格式

CREATE TABLE [IF NOT EXIST] `表名`(
    `字段名` 列类型 [属性] [索引] [注释] ,
    `字段名` 列类型 [属性] [索引] [注释] ,
    `字段名` 列类型 [属性] [索引] [注释] ,
    ······
    `字段名` 列类型 [属性] [索引] [注释] 
)[表类型][字符集设计] [注释]

常用命令

SHOW CREATE DATABASE school --  查看创建数据库的语句
SHOW CREATE TABLE student2 -- 查看student2数据库表的语句
DESC student2 -- 查看表的具体结构

2.5 数据表的类型

在物理空间存在的位置

-- 数据库引擎
/*
INNODB  默认使用
MYISAM  早些年使用的
*/

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大约为MYISAM*2

常规使用操作：

MYISAM 节约空间，速度快
INNODB 安全性高支持事务处理多表多操作用户类型

设置数据库表的字符集编码

CHARSET=utf8

不设置的话,会是默认的latin1编码会不支持中文

2.6修改和删除表

修改

-- 修改表名  ALTER TABLE 旧的表名 RENAME AS 新表名
ALTER TABLE student2 RENAME AS teacher1
-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段 （重命名，修改约束！）
-- ALTER TABLE 表名 modify 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束
--ALTER TABLE 表名  change 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE  age age1 INT(2) -- 字段重命名

-- 删除表的字段
--ALTER　TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

删除

--删除表(如果表存在删除)
DROP TABLE if exists teacher1

所有的创建和删除尽量加上判断以面判断

注意点:

多有的字段名使用TABLE 上面的包裹
注释: --
sql 关键字不敏感建议写小写

3.MYSQL数据管理

3.1外键（了解即可）

方式一：在创建表的过程中添加外键

-- 目标：创建一个school数据库
-- 
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid引用年纪表的gradeid
-- 定义外键key
-- 给外添加引用
CREATE TABLE IF NOT EXISTS `student2` (`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女`' COMMENT '性别',
 `gradeid` INT(10) NOT NULL  COMMENT '学生的年级',
 `addrsess` VARCHAR(10) DEFAULT NULL COMMENT '家庭住址',
 
 PRIMARY KEY (`id`),
 KEY `FK_gradeid`(`gradeid`),
 CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE =INNODB DEFAULT CHARSET= utf8

删除有外键表关系的时候必须要删除引用别人的表在删除需要删除的表

方式二：创建表成功后添加外键约束

-- 目标：创建一个school数据库
-- 
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
`gradename` VARCHAR(20) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE= INNODB DEFAULT CHARSET=utf8

-- 学生表的gradeid引用年纪表的gradeid
-- 定义外键key
-- 给外添加引用
CREATE TABLE IF NOT EXISTS `student2` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女`' COMMENT '性别',
 `gradeid` INT(10) NOT NULL  COMMENT '学生的年级',
 `addrsess` VARCHAR(10) DEFAULT NULL COMMENT '家庭住址',
 
 PRIMARY KEY (`id`)
 
)ENGINE =INNODB DEFAULT CHARSET= utf8

-- 创建表的时候没有外键关系 
ALTER TABLE `student2`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- alter table 表名 add constraint 约束名 foreign key (作为外键的列)  REFERENCES 那个表（）哪个字段

以上的操作都是物理外键数据库级别的外键不建议使用！（避免出现数据库过多）

==最佳实践==

数据库就是单纯表
我们想使用多张表的数据想用外键时（程序实现）

3.2DML语言

数据库存在的意义：数据存储。数据管理

DML语言：数据操作语言

insert
update
delete

3.3添加 insert

insert

语法：nsert into 表名（[字段名1，字段2，字段3]）valuses（(值1），（值2），（值3)）

-- 插入语句（添加）
 -- insert into 表名 （[字段名1，字段2，字段3]）valuses(`值1`），（`值2`），（`值3`)
 
 INSERT INTO `grade` (`gradename`) VALUES ('大四')

-- 由于主键自增我们可以省略 （如果不写表的字段 他就会一一匹配）
-- 一般写插入语句 我们一定要数据和字段一一对应
-- 插入多个字段

INSERT INTO `grade`(`gradename`) VALUES('大一'),('大二')

INSERT INTO `student2`(`name`)VALUES ('张三') 
INSERT INTO `student2`(`name`,`password`,`sex`)
    VALUES('张三','aaaaa','男'),('老卢','123456','男')

注意事项：

1.字段和字段之间用英文，隔开

2.字段可以省略但是后面的值要一一对应

3.可以同时插入多条语句用('张三','aaaaa','男'),('老卢','123456','男')中间逗号隔开

3.4修改 update

update 修改谁（条件）set 原来的值 =新值

-- 修改学员的名字
UPDATE `student2` SET `name`='路演' WHERE id=1
-- 不指定条件的话 会改所有的表
update `student` set `name`= '阳仔'

-- 修改多个属性
UPDATE `student2` SET `name`='张二',`password` ='232323' WHERE id =2

--根据多个条件判断
update `student2` set `name`='张二',`password` ='232323' where id < 4 and id> 2


-- 语法;
-- update 表明 set colnum_name = value where id = 1 条件

条件：where 字句运算符 id 等于某个值或者大于小于某个区间

操作符返回boo值

操作符	意义	范围	结果
=	等于	5=6	false
<>或者！=	不等于
>
<
<=
>=
BEtWEEN...and...	在某个范围内		[2,5]
AND	我和你
OR	我或你\|\|

注意:

colnum_name 是数据库的列
value 可以是值也可以是变量

多个设置属性之间用英文逗号隔开

update `student2`set`birthday`=current_time where   `name`='长江七号'

3.5删除delete

delete

语法：delete from 表名 where id= 1;

-- 删除指定的数据
DELETE FROM `student2` WHERE id = 10;

TRUNCATE 完全清空一个数据库表，表的结构和索引约束不会变

truncate `student`

turncate 和delete区别

相同点：
不同
- ：truncate会重新设置自增列计数器归零不会影响事务
- delete 删除表不会清零自增

4.DQL查询数据（重点）

4.1DQL


-- 查询全部学生
SELECT * FROM student

-- 查询指定字段
SELECT `studentno` ,`sex`FROM student

-- 别名 给结果取个名字
SELECT `studentno` AS 学号 ,`sex` AS 性别 FROM  student

-- 函数 Concat(a,b)\
SELECT CONCAT('姓名:',studentname) AS 姓名 FROM student

语法： select 字段 from 表明 from 表名

有的时候列名字不是很清楚用as起别名

去重复 distinct

-- 去重复 distinct
SELECT DISTINCT `studentno` FROM result

数据库的列(表达式)

- 查询系统版本
  SELECT VERSION()
  -- 计算
  SELECT 100*4-1

-- 学员成绩 + 1分
SELECT `studentno` ,`studentresult`+1 AS 加分后 FROM result

数据库的表达式：文本的值列 null 函数 j计算表达式系统变量

selsct 表达式 from 表

4.3 where 条件语句

逻辑运算符

运算符	语法
and &&	a and b a&&b
or \|\|	a or b a\|\|b
not !	not a !a

尽量使用英文字母

--   ===========where===========
-- 查询考试成绩在（88-99]之间
SELECT studentno , studentresult FROM result
WHERE studentresult > 88 AND studentresult <=  99

-- 模糊查询(区间)
SELECT studentno , studentresult FROM result
WHERE studentresult BETWEEN 88 AND 99

模糊查询: 比较运算符

运算符	语法	描述
is null	a is null	如果操作符为null则结果为真
is not null	a is not null	如果操作符为not null 则结果为真
between	a between c and d	若a 在b和c之间为真
like	a like b	SQL匹配如果a匹配b,则结果为真
in	a in(a1,a2,a3)	假设a在（a1,a2,a3...）某一个中真

-- =========模糊查询=====
-- 查询型姓刘的同学
-- 结合like  % (代表0到任意一个字符) _(一个字符)

-- 查询姓张的同学
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '张%'

-- 查询姓张且后面一个字名字的同学
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '张_'

-- 查询姓张且后面两个字名字的同学
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '张__'

--  查询名字中间有嘉字的同学
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '%伟%'

-- ================in=========
-- 查询1000 1001 号学员
SELECT `studentno`,`studentname` FROM student
WHERE `studentno`IN (1000,1001,1002)

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM student
-- WHERE `address`like '北京%'

-- 查询在北京朝阳或者广东的学生的学生
WHERE `address` IN ('北京朝阳','广东')

-- ==========null not null

-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address`='' OR `address`IS NULL

-- 查询有出生日期的同学
SELECT `studentno`,`studentname` FROM student
WHERE`borndate` IS  NOT NULL

4.4 联表查询

join对比

f93ef5db533995aa3d877494973c0a4b

-- innerjoin
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno

-- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno  -- 找交叉点

 -- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno  -- 找交叉点

操作	描述
inner join	如果表中至少有一个匹配就返回行
left join	从左表中返回所有的值及时右表没有匹配
right join	从右表中返回所有的值及时左表没有匹配

/*
思路：
查询参加考试的学生的（学号、姓名、科目编号、分数）
分析查询的字段在哪个表
确定使用哪种连接查询
确当交叉点
判断条件：学生表中的studentno=result 表中的studentno

*/

-- join（连接的表） on（判断条件） 连接查询
-- where 等值查询

-- innerjoin
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentno = r.studentno

-- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno  -- 找交叉点

 -- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno  -- 找交叉点

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno 
WHERE studentresult IS NULL

-- 思考题（查询了参加考试的学生的信息，学号，学生，姓名，科目名称 分数）
/*
思路：
查询参加考试的学生的（学号、姓名、科目名、分数）
分析查询的字段在哪个表 student result subject
确定使用哪种连接查询
确当交叉点
判断条件：学生表中的studentno=result 表中的studentno

*/

SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno 
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno

-- 我要查询哪写数据 selsct... 
-- 从 哪几个表中查 from表 xxx join连接的表
-- 假设存在一种多张表查询  先查两张 再慢慢增加

自连接（了解）

父类

categoryid	categoryidname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryidname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps

操作：查询父类对应的子类关系

父类
信息技术	办公信息
软件开发	web开发
软件开发	数据库
美术设计	ps设计

-- 自连接查询
-- 父类子类查询 ： 把一张表看成两张表

SELECT a.`categoryName` AS 父类 ,b.`categoryName` AS 子类
FROM `category` AS a , `category` AS b
WHERE a.`categoryid`=b.`pid`

join

-- 查询学员所属的年级   ( 学号 姓名 年级名称)
SELECT `studentno`,`studentname`,`gradename`
FROM student s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`

-- 查询科目所属的年级 (科目名称  年级名称)
SELECT `subjectname`,`gradename`
FROM `subject` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`

-- 查询了参加 高等数学考试的学生信息 ： 学号 ，学生姓名 科目名 分数

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student AS a 
INNER JOIN result s
ON a.`studentno`=s.`studentno`
INNER JOIN `subject` sub
ON s.`subjectno`=sub.`subjectno`
WHERE subjectname = '高等数学-1'

4.5 分页和排序

排序

SELECT studentresult FROM  `result`
ORDER BY `studentresult` ASC

分页

-- 分页 每页只显示五条数据
-- 语法： limit 当前页 ，页面大小
-- limit 0,5  1~5 第一页
-- limit 1,5  2~6  
-- limit 5,5  第二页
-- limit  (n-1)*5,a   第n页
SELECT studentresult FROM  `result`
ORDER BY `studentresult` ASC
LIMIT 0,5  -- 五条数据为一页 显示第一页五条数据

4.6子查询

where(特定的值)

本质：在where语句中嵌套一个子查询语句

-- 查询高等数学-1的所有考试结果（学号，科目编号，成绩）
-- 方式一：连接查询

SELECT studentno ,r. subjectno,`studentresult`
FROM result r 
INNER JOIN `subject` sub
ON r. `subjectno`= sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC\

-- 方式二： 子查询

SELECT studentno , subjectno,`studentresult`
FROM result  r
WHERE r.subjectno =(SELECT `subjectno` FROM `subject` WHERE `subjectname`= '高等数学-1')

-- 查询分数不小于80分的学生 学号 和姓名

SELECT  DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`

-- 在上面的基础上加上一个科目  高等数学2

SELECT  DISTINCT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult`>=80 AND `subjectno`=(SELECT `subjectno` FROM `subject` WHERE`subjectname`='高等数学0-')

-- 再改造
SELECT   `studentno`,`studentname` FROM student WHERE studentno IN
(SELECT`subjectno` FROM `result` WHERE `studentresult`>=80 
AND `subjectno`=(SELECT `subjectno` FROM `subject` WHERE`subjectname`='高等数学0-') )

4.7 分组和过滤

  -- 查询不同课程的平均分大于八十分 最高分  最低分
  -- 核心：分组  不同课程分组
  SELECT subjectname ,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
  FROM result r
  INNER JOIN `subject` sub
  ON r.`subjectno` = sub.`subjectno`
  GROUP BY r.subjectno  -- 通过什么字段来分组
  HAVING AVG(`studentresult`)>=80 -- having 在分组之后用来加条件

5.mysql函数

官网地址：

https://dev.mysql.com/

5.1常用函数

-- =================常用函数===============
-- 数学运算 
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4)  -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()  -- 返回0-1之间的随机数
SELECT SIGN(7)  -- 判断一数的符号  0 是0  负数返回-1 正数返回1

-- 字符串函数
SELECT  CHAR_LENGTH('sadasd')-- 返回字符串长度
SELECT CONCAT('我爱','nime') -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'草鸡') -- 插入  替换 从某个位置替换某个长度
SELECT LOWER('KuangShen') -- 群不字母小写
SELECT UPPER('DaXieZiMu')-- 全部字母大写
SELECT INSTR('luyang','y') -- y第一次出现的位置
SELECT REPLACE('坚持就能成功','坚持','努力')  -- 替换字符
SELECT SUBSTR('涯哥哥永远得神',4,6)   -- 返回指定字符串  （源字符串  开始位置 截取长度）
SELECT REVERSE ('qsdads') -- 反转

-- 查询姓周的同学
SELECT REPLACE(studentname,'张','鲁') FROM `student`
WHERE 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聚合函数（常用）

-- ==============聚合函数=============
  -- 想查询一个表中有多少个记录
  SELECT COUNT(studentname) FROM student; -- 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 平均分
  SELECT MAX(`studentresult`)
  SELECT MIN(`studentresult`)
  
  -- 查询不同课程的平均分大于八十分 最高分  最低分
  -- 核心：分组  不同课程分组
  SELECT subjectname ,AVG(`studentresult`) AS 平均分,MAX(`studentresult`),MIN(`studentresult`)
  FROM result r
  INNER JOIN `subject` sub
  ON r.`subjectno` = sub.`subjectno`
  GROUP BY r.subjectno  -- 通过什么字段来分组
  HAVING AVG(`studentresult`)>=80 -- having 在分组之后用来加条件

5.3 数据库级别的MD5加密（扩展）

  
  -- ============测试MD5 加密==========
  
  CREATE TABLE`restmd5`(
  `id` INT(4) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `pwd` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
  )ENGINE=INNODB DEFAULT CHARSET=utf8
  
  -- 明文加密
  INSERT INTO `restmd5` VALUES(1,'张三','123456'),(2,'李三','123456'),(3,'王五','123456')
  
  -- 加密
  UPDATE `restmd5` SET pwd=MD5(pwd) WHERE id!=2 -- 
  UPDATE `restmd5` SET pwd=MD5(pwd)  -- 加密全部密码
  
  -- 插入时加密
    INSERT INTO `restmd5` VALUES(4,'张三',MD5('123456'))
  INSERT INTO restmd5 VALUES()
  
  -- 如何校验：
  SELECT *  FROM testmd5 WHERE `name`='张三'AND pwd=MD5('123456')

6.事务

6.1 什么是事务

要么都成功要么都失败

1.sql执行 A给B转账 a1000 b 800 a给b转200

2.sql执行 b收到200 a800 b400

将一组sql放在一个批次中去执行

事务原则： ACID原则：原子性，一致性、隔离性，持久性（脏读，幻读）

原子性：

一致性：

事务前后的数据完整性不能改变

持久性

事务没有提交就恢复到原状如果提交就持久化到数据库

事务提交后不可逆

隔离性

事务之间相互隔离进行

事务隔离级别

脏读

不可重复读

幻读

  -- ========事务===========
  -- mysql 默认开启事务自动提交的
  SET autocommit = 0 -- 关闭
  SET autocommit = 1 -- 开启 （默认的）
  
  -- 手动处理事务
   SET autocommit = 0 -- 关闭自动提交
  -- 事务开启
  START TRANSACTION  -- 标记一个事物的开始 总这之后  sql都在同一个事务
  
  INSERT 
  INSERT
  
  -- 提交：持久化 成功！
  COMMIT
  
  -- 回滚 回到原来的样子 失败
  ROLLBACK
  
   --  事务结束
  SET autocommit = 1;
  
  -- 了解即可
  SAVEPOINT 存点名 -- 设置一个事务的保存点
  ROLLBACK SAVEPOINT 存点名 -- 回滚到保存点
  RELEASE SAVEPOINT 报存点名  -- 删除svepoint 保存点
  
  
  
  -- 转账
  CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
  
  
  USE shop
  
  CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
  )ENGINE=INNODB DEFAULT CHARSET=utf8

  
  -- 模拟转账 ： 事务
  SET autocommit = 0; -- 关闭自动提交
  START TRANSACTION  -- 开启事务
  
  UPDATE `account` SET money= money -500 WHERE `name`='A'  -- A-500
  UPDATE `account` SET money=money+500 WHERE `name`='B' -- A-500
  
  COMMIT -- 提交事务
  ROLLBACK -- 回滚
  
  SET autocommit =1;

7.索引

7.1 索引的分类

在一张表中主键索引只能有一个唯一索引可能有多个

主键索引 primary key
- 唯一的标识主键不能重复只能有一个列作为索引
唯一索引 unique key
- 避免重复的列出现，可以重复多个列都可以标为唯一索引
常规索引 key/index
- 默认的，index或者key来设置
全文索引 FullTxet
- 在特定的数据库引擎下才有 myisam
- 快速定位数据

基础语法

7.2测试索引

-- 插入100万数据

DELIMITER $$ -- 写函数之前必须要写
CREATE FUNCTION mock_data() 
RETURNS INT 
BEGIN 
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
    -- 插入数据 
    INSERT  INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES(CONCAT('用户',i),'232323@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
    SET i = i+1;
   END WHILE;
   RETURN i;
  
END;
SELECT mock_data();

SELECT * FROM app_user WHERE `name` = '用户9999'; -- 耗时一秒

SELECT * FROM student\
-- id _ 表名——字段名
CREATE INDEX id_app_user_name  ON app_user(`name`)
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 耗时0.01秒

索引在小时据量作用不大大数据量时作用较大

7.3 索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

8.数据库备份

9.权限管理

10.数据库的规约、三大范式

8.权限管理和备份

8.1 用户管理

sql可视化管理

-- 创建用户 create user 用户名 identified by '密码'
CREATE USER luyang IDENTIFIED BY '123456'

-- 修改密码  （修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码 （修改指定用户密码）
SET PASSWORD FOR luyang = PASSWORD('123456')

-- 重命名 rename user 原来的名字 to 新的名字
RENAME USER luyang TO luyang1

-- 用户授权  all privileges 全部权限，库，表
-- all priviliges
GRANT ALL  PRIVILEGES ON *.* TO luyang

-- 查询权限 
SHOW GRANTS FOR luyang
SHOW GRANTS FOR root@localhost

-- root用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 revokes哪些权限，在哪个库撤销 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM luyang

-- 撒谎用户
DROP USER kuangshen

8.2 数据库备份

为什么备份：

保证重要的数据不丢失
数据转移

mysql数据库备份方式

直接拷贝物理文件
可视化工具中手动导出
使用命令行导出 mysqldump命令行使用

# mysqldump -h 主机 -u用户 -p密码 数据库 表名 > 报存文件位置

mysqldump -hlocalhost -uroot -p123456 school student >  D:a.sql


-- 导入
mysql -uroot -p123456
use school
登录的情况下  切换到指定的数据库 
sourcr 备份文件的位置

假设你要备份数据库防止数据丢失

9.规范数据库设计

9.1 为什么要设计

当数据比较复杂的时候需要设计

糟糕的数据库设计：

数据冗余
数据库插入和删除比较麻烦、异常【屏蔽使用物理外键】
程序性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性

软件开发中，关于数据库的设计

分析需求
概要设计：设计关系图E-R图

设计数据库的步骤 （个人博客）

9.2数据库三大范式

为什么需要三大范式

数据重复
更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失有效信息

三大范式

第一范式（1NF）

原子性：保证每一列不能再分（地址详细地址）

第二范式（2NF）

前提条件：满足第一范式

每张表只描述一件事情（订单编号商品编号数量价格商品名称）

第三范式（3NF）

前提：满足第一第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关而不能间接相关（学生信息班主任加了班主任信息）

（规范数据库设计）

规范性和性能问题

关联查询的表不得超过三张表

10.JDBC

10.1数据库驱动

我们的程序会通过数据库驱动和数据库打交道

10.2 JDBC

SUN公司为了简化开发人员对数据库的统一操作提供的java操作的数据库的规范 JDBC

掌握JDBC操作即可

java.sql

javax.sql

还需要导入数据库驱动包

10.3第一个JDBC程序

创建测试数据库

CREATE TABLE `users`(
    id INT PRIMARY KEY,
    NAME VARCHAR(40),
    PASSWORD VARCHAR(40),
    email VARCHAR(60),
    birthday DATE
);

INSERT INTO `users`(id,NAME,PASSWORD,`email`,`birthday`)
VALUES
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

1.创建一个项目

2.导入数据库驱动

3.编写测试代码

package SqlJdbc;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;
//  w我的第一个jdbc程序
public class demo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.用户信息和url
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncodingUtf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3。连接成功 数据库对象
        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("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
     }
}

步骤总结：

1.加载驱动

2.连接数据库DriverManager

3.获得执行sql的对象 Statement

4.获得返回的结果集

5.释放连接

10.4 statement对象

代码实现

1.提取工具类

package SqlJdbc.lesson2.Utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;
    static {
        try{
             InputStream in =JdbcUtils.class.getClassLoader().getResourceAsStream("db.protites");
            Properties properties = new Properties();
            properties.load(in);
           driver = properties.getProperty("driver");
           url  = properties.getProperty("url");
           username = properties.getProperty("username");
           password = properties.getProperty("password");

            //1.驱动只用加载一次
            Class.forName(driver);

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }



    //释放连接
    public static void release(Connection connection,Statement st, ResultSet rs){
        if(rs!=null){
            try{
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

2.编写增删改方法 excuteUdate

添加

package SqlJdbc.lesson2;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.*;

public class insert {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库连接
            statement =connection.createStatement();//获取sql的执行对象
             String sql ="INSERT INTO users (`id`,`name`,`password`,`email`,birthday)VALUES(4,'asd','123456','lu.com','1078-12-08')";

             int i =statement.executeUpdate(sql);
             if(i>0) System.out.println("插入成功");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

删除

package SqlJdbc.lesson2;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class delete {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库连接
            statement =connection.createStatement();//获取sql的执行对象
            String sql ="DELETE FROM users WHERE id =4";

            int i =statement.executeUpdate(sql);
            if(i>0) System.out.println("删除成功");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

修改

package SqlJdbc.lesson2;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class update {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库连接
            statement =connection.createStatement();//获取sql的执行对象
            String sql ="UPDATE  users SET `name` ='yang2' WHERE id=4";

            int i =statement.executeUpdate(sql);
            if(i>0) System.out.println("修改成功");

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

3.编写查询 executeQuery

package SqlJdbc.lesson2;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class select {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库连接
            statement =connection.createStatement();//获取sql的执行对象
            String sql ="select * from users where id=1";

            resultSet=statement.executeQuery(sql);
            if(resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

4.数据注入

package SqlJdbc.lesson2;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;
public class sql注入 {
    public static void main(String[] args) {
        login(" 'or '1=1"," 'or '1=1");
    }


    public static  void login(String name , String password){
        Connection connection = null;
        Statement statement =null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库连接
            statement =connection.createStatement();//获取sql的执行对象
            //SELECT * FROM users WHERE `name`='zhangsan'AND PASSWORD = '123456'
            String sql ="select * from users where `name`='"+name + "'AND `password` = '"+password+"'";

            resultSet=statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
                System.out.println("+++++++++++++++++++++++");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }

    }

10.5PreparedStatement

ke以防止sql注入并且考虑更高

1.新增

package SqlJdbc.lesson3;

import SqlJdbc.lesson2.Utils.JdbcUtils;
import java.util.Date;
import java.sql.Connection;
import java.sql.*;

public class insert {
    public static void main(String[] args) {
        Connection connection =null;
        PreparedStatement st =null;

        try {
            connection = JdbcUtils.getConnection();

            String sql ="insert into users (`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
            st = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行

            //手动给参数复制
            st.setInt(1,5);
            st.setString(2,"luyng");
            st.setString(3,"123456");
            st.setString(4,"231.com");
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            int i =st.executeUpdate();
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,st,null);
        }
    }
}

2.删除

package SqlJdbc.lesson3;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class delete {
    public static void main(String[] args) {
        Connection connection =null;
        PreparedStatement st =null;

        try {
            connection = JdbcUtils.getConnection();

            String sql ="delete  from users where id =?";
            st = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行

            //手动给参数复制
            st.setInt(1,5);


            int i =st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,st,null);
        }
    }
}

3.更新

package SqlJdbc.lesson3;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class update {
    public static void main(String[] args) {
        Connection connection =null;
        PreparedStatement st =null;

        try {
            connection = JdbcUtils.getConnection();

            String sql ="update  users set name = ? where id =? ";
            st = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行

            //手动给参数复制
            st.setInt(2,5);
            st.setString(1,"luyngggg");


            int i =st.executeUpdate();
            if(i>0){
                System.out.println("修改成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,st,null);
        }
    }
}

4.查找

package SqlJdbc.lesson3;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class select {
    public static void main(String[] args) {
        Connection connection =null;
        PreparedStatement st =null;
        ResultSet resultSet =null;

        try {
            connection = JdbcUtils.getConnection();

            String sql ="select * from users where id =?";
            st = connection.prepareStatement(sql);//预编译sql,先写sql,然后不执行

            //手动给参数复制
            st.setInt(1,5);

            resultSet =st.executeQuery();
            if(resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,st,resultSet);
        }
    }
}

5.防止sql注入

package SqlJdbc.lesson3;

import SqlJdbc.lesson2.Utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;

public class sqllogin {
    public static void main(String[] args) {
        login("zhangsan","123456");
    }


    public static  void login(String name , String password){
        Connection connection = null;
        PreparedStatement st =null;
        ResultSet resultSet =null;
        try {
            connection = JdbcUtils.getConnection();//获取数据库连接


            String sql ="select * from users where `name`=? AND `password` = ?";
            st = connection.prepareStatement(sql);
            st.setString(1,name);
            st.setString(2,password);

            resultSet=st.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
                System.out.println("===================");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            JdbcUtils.release(connection,st,resultSet);
        }
    }
}

10.6使用idea连接数据库

10.7事务