1.数据库基础
数据库:DB
概念:数据仓库,软件,安装在操作系统之上,可以存大量的数据
作用:存数据,管理数据
DBMS:数据库管理系统
- 数据库管理软件,科学有效的管理我们的数据,维护和获取数据
- mysql
数据库分类:
- 关系型数据库(SQL):mysql oracle sqlserver(表和表之间,行和列之间的关系进行数据存储)
- 非关系型数据库(NOT ONLY SQL):redis(对象存储,通过对象的属性决定 )
mysql:sql语言
安装mysql
安装步骤
1、下载后得到zip压缩包.
2、解压到自己想要安装到的目录
3、添加环境变量:我的电脑->属性->高级->环境变量
选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹
4、编辑 my.ini 文件 ,注意替换路径位置
[mysqld]
basedir=D:\Program Files\mysql-5.7
datadir=D:\Program Files\mysql-5.7\data
port=3306
skip-grant-tables
5、启动管理员模式下的CMD,并将路径切换至mysql下的bin目录,然后输入mysqld –install (安装mysql)
6、再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
7、然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面(密码可为空)
8、进入界面后更改root密码
update mysql.user set authentication_string=password(‘123456’) where user=‘root’ and Host = ‘localhost’;
9、刷新权限
flush privileges;
10、修改 my.ini文件删除最后一句skip-grant-tables
11、重启mysql即可正常使用
net stop mysql
net start mysql
安装sqlyog
连接数据库
命令行连接
cmd输入:mysql -uroot -p123456(连接数据库)
showdatabases;(显示所有数据库)
use school;(切换数据库)
show tables;(查看数据库中所有的表)
describe student;(显示数据库中表的所有信息)
creat database westos;(创建数据库)
exit;(退出连接)
–:(单行注释)
/**/(多行注释)
数据库语言(CRUD增删改查)
DDL:定义语言
DML:操作语言
DQL:查询语言
DCL:控制语言
2.操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
mysql关键字不区分大小写
2.1操作数据库
- 创建数据库
CREATE DATABASE [IF NOT EXISTS] WESTOS;
- 删除数据库
DROP DATABASE [IF EXISTS] westos;
- 使用数据库
--TAB键上面,如果表名或字段名是特殊字符,需要带``
USE `school`
SELECT `user` FROM student
- 查看数据库
SHOW DATABASES
2.2数据库的数据类型(列类型)
数值
- tinyint:十分小的数据 : 一个字节
- smallint:较小的数据 : 两个字节
- mediumint :中等大小的数据 :三个字节
- int :标准的整数: 四个字节(常用)
- bigint :较大的数据: 八个字节
- float: 浮点数 : 四个字节
- double: 浮点数: 八个字节
- decimal: 字符串形式的浮点数 :金融计算时一般用decimal
字符串
- char :固定大小的字符串 :0-255
- varchar : 可变字符串 :0-65535 (常用的变量)(对应String)
- tinytext: 微型文本 :2^8-1
- text :文本串 :2^16-1(保存大文本)
时间日期
- date :YYYY-MM-DD,日期格式
- time : HH:mm:ss,时间格式
- datetime:YYYY-MM-DD HH:mm:ss (最常用的时间格式)
- timestamp:时间戳 1970.1.1到现在的毫秒数
- year 年份表示
null
- 没有值,未知
- 不要使用null进行运算,结果为null
2.3数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 声明该列不能声明为负数
zerofill
- 零填充的
- 不足的位数以0填充
自增
- 通常理解为自增,在上一条记录的基础上+1
- 通常用来设计唯一的主键,必须为整数类型
- 可以自定义主键自增的起始值和步长
非空 null not null
- 假设设置为 not null ,如果不赋值,就会报错
- null,如果不填写值,默认为null
默认
- 设置默认值
- sex,默认值为男,如果不指定该列的值,则会有默认的值男
2.4创建数据库表
/*
注意点:使用英文(),表的名称和字段用``括起来
auto_increment自增
字符串使用单引号括起来
所有的语句后面加英文逗号
最后一个字段不用加
primary key 主键,一般一个表只有一个
*/
CREATE TABLE `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 `表名`(
`字段名` 列类型[属性][索引][注释],
`字段名` 列类型[属性][索引][注释],
......
`字段名` 列类型[属性][索引][注释]
)[表类型][字符集设置][注释]
SHOW CREATE DATABASE school(查看创建数据库的语句)
SHOW CREEATE TABLE student(查看student数据表的定义语句)
DESC student(显示表的结构 )
2.5数据表的类型
INNODB(默认使用)
MYSIAM(早些年使用)
MYISAM | INNODB |
---|---|
不支持事务 | 支持 |
不支持数据行锁定(表锁) | 支持数据行锁定 |
不支持外键约束 | 支持外键约束 |
支持全文索引 | 不支持全文索引 |
表空间较小 | 表空间较大,约为MHISAM的两倍 |
常规操作:
- MYISAM节约空间,速度较快
- INNODB安全性高,支持事务的处理,多表多用户操作
在物理空间的存在位置
- 所有的数据库文件存在data文件下
- 本质还是文件的存储
mysql引擎在物理文件的区别
- INNODB:在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM:对应的文件:x.frm表结构定义文件,*x.MYD数据文件,x.MYI索引文件
设置数据库表的字符集编码
CHARSET =utf8;
在my.ini中配置默认的编码
character-set-server=utf8
``
#### 2.6修改删除表
> 修改
```sql
ALTER TABLE student RENAME AS student1 /*重命名表*/
ALTER TABLE student1 ADD age INT(11) /*增加表的字段*/
ALTER TABLE student1 MODIFY age VARCHAR(11)/*修改约束*/
ALTER TABLE student1 CHANGE age age1 INT(1)/*字段重命名*/
ALTER TABLE student1 DROP age1/*删除表的字段*/
删除
DROP TABLE student1/*删除表*/
所有的创建和删除尽量加上IF EXISTS 避免报错
注意点
- 字段名使用``包裹
- 注释:-- 或/**/
- sql 关键字大小写不敏感,建议小写
- 所有的符号用英文符号
3.mysql数据管理
3.1外键(了解)
方式一
CREATE TABLE `grade`(
`gradeid` INT (10) NOT NULL AUTO_INCREMENT COMMENT'年级id',
`gradename`VARCHAR(50) NOT NULL COMMENT'年级名称',
PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
CREATE TABLE `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
方式二:创建表成功后添加外键约束
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
删除有外键关系的表,需要先删除引用别人的表(从表),再删除被引用的表(主表 )
以上操作都是物理外键,数据库级别的外键,不建议使用(避免数据库太多造成困扰)
最佳实现
数据库就是单纯的表,只用来存数据,只有行和列,使用多张表的数据,用程序实现外键
3.2DML语言(重点)
数据库的意义:数据存储和管理
DML语言:数据操作语言
- insert
- update
- delete
3.3添加
insert
-- 插入语句
-- insert into 表名([字段名1 字段2,字段3])value ('值1'),('值2'),('值3')
INSERT INTO `grade`(`gradename`) VALUES ('大四')
-- 由于主键自增,我们可以省略主键(如果不写表的字段,就会一一匹配)
INSERT INTO `grade`(`gradename`) VALUES ('大四')
-- 插入时数据和字段一一对应
-- 插入多个字段
INSERT INTO `grade`(`gradename`)VALUES('大三'),('大二'),('大一')
INSERT INTO `student`(`name`) VALUES('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES('张三','aaaaa','男')
语法:insert into 表名([字段名1 字段2,字段3])value (‘值1’),(‘值2’),(‘值3’)
==
注意事项:字段之间用英文逗号隔开
字段可以省略,但后面的值必须一一对应
可以同时插入多条数据,values后面的值需要逗号隔开
3.4修改
update
-- 修改学员的名字
UPDATE `student` SET `name`='小雪' WHERE id=1;
-- 不指定条件的情况下,会改动所有的表
UPDATE `student` SET `name`='小雪'
-- 修改多个属性,逗号隔开
UPDATE `student` SET `name`='小雪',`email`='12313123@qq.com' WHERE id=1;
语法:UPDATE 表名 SET[ colnum_name = value ],[colnum_name],[]…where 条件
条件:where子句 运算符 id等于某个值,大于某个值,在某个区间内修改
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<> 或!= | 不等于 | 5!=6 | ture |
> | 大于 | 5>4 | true |
< | 小于 | 5<4 | false |
<= | 小于等于 | 5<=4 | false |
>= | 大于等于 | 5>=4 | ture |
BETWEEN… AND… | 某个范围内 | [2,5] | |
AND | 和&& | 5>1and1>2 | false |
OR | 或|| | 5>1or1>2 | true |
多个条件定位数据,无上限
-- 多个条件定位数据,无上限
UPDATE `student` SET `name`='小明' WHERE `name` = '张三'AND `sex` = '女'
UPDATE `student` SET `name`='小明' WHERE `name` = '张三'OR `sex` = '女'
语法:UPDATE 表名 SET[ colnum_name = value ],[colnum_name],[]…
注意事项:
- colnum_name是数据库的列,尽量带``
- 条件,是筛选的条件,如果没有指定会修改所有的列
- value,是具体的值,也可以是一个变量
- 多个属性之间用英文逗号隔开
3.5删除
delete
语法delete from 表名[where条件]
-- 删除数据
DELETE FROM `student` WHERE id=1;
truncate命令:完全清空数据库表,表的结构和索引约束不会变
TRUNCATE `student`
tranucate和delete区别
- 相同点:都能删除数据,不会删除表结构
- 不同点:
- tranucate重新设置自增列,计数器会归零
- tranucate不会影响事务
4.DQL查询数据(最重点)
4.1DQL
数据查询语言
- 所有的查询操作都用它 select
- 简单的查询,复杂的查询都能做
- 数据库中最核心的语言,最重要的语句
4.2指定查询字段
-- 查询全部的学生 select 字段 from 表
SELECT * FROM student
SELECT * FROM result
-- 查询指定字段
SELECT `studentno` ,`studentname` FROM student
-- 别名,给结果起名字 as ,可以给字段起别名,也可以给表起别名
SELECT `studentno` AS 学号 ,`studentname` AS 学生姓名 FROM student
-- 函数 concat(a,b)拼接字符串
SELECT CONCAT ('姓名:',studentname) AS 新名字 FROM student
语法:
select 字段,...from 表
有时候列名字不是那么见名知意,我们起名 as 字段名 as 表名 as
去重distinct
作用:去除select查询出来结果的重复数据,只显示一条
-- 查询有哪些同学参加了考试
SELECT *FROM result -- 查询全部的成绩
-- 查询有哪些同学参加了考试
SELECT `studentno` FROM result
-- 发现重复数据,去除
SELECT DISTINCT `studentno` FROM result
数据库的列(表达式)
SELECT VERSION()-- 查看系统版本(函数)
SELECT 100*3-1 AS 计算结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)
-- 学员考试成绩加一分查看
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM result
数据库中的表达式:
文本值,列,null,函数,计算表达式,系统变量
select 表达式
from 表
4.3where条件字句
作用:检索数据中符号条件的值
搜索的条件由一个或多个表达式组成,结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果为真 |
or || | a or b a || b | 逻辑或,其中一个为真,结果为真 |
not ! | not a ! a | 逻辑非,真为假,假为真 |
SELECT `studentno`,`studentresult` FROM result
-- 查询考试成绩在95-100之间的
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult>=95 AND studentresult<=100
-- and &&
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM result
WHERE studentresult BETWEEN 50 AND 100
-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE studentno != 1000
-- != not
SELECT `studentno`,`studentresult` FROM result
WHERE NOT studentno=1000
模糊查询:比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,则结果为真 |
is not null | a is not null | 如果操作符为not null,则结果为真 |
between | a between b and c | 如果a在b和c之间,则结果为真 |
like | a like b | 如果a能匹配到b,则结果为真 |
in | a in a1,a2,a3… | 假设a在a1,或a2…其中的某个值中,则结果为真 |
-- ==============模糊查询=========================
-- 查询姓张的同学
-- 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)
-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM student
WHERE address IN ('河北','北京朝阳')
-- ==============null not null ====================
-- 查询地址为空的学生
SELECT `studentno`,`studentname` FROM student
WHERE address IS NULL OR address=''
-- 查询有出生日期的学生
SELECT `studentno`,`studentname` FROM student
WHERE borndate IS NOT NULL
4.4联表查询
join 对比
-- ==========联表查询join================
-- 查询参加了考试的同学(学号,姓名,科目编号,分数)
SELECT * FROM student
SELECT * FROM result
/*
思路:
1.分析需求,分析查询的字段来自哪些表
2.确定使用哪种连接查询 7种、
3.确定交叉点(这两个表中哪个数据是相同的)
判断的条件:学生表的studentno =成绩表的 studentno
*/
-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno
-- right join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
-- left join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student s
LEFT JOIN result r
ON savepoint.studentno = r.studentno
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行(并集) |
left join | 即使右表中没有匹配,也会从左表中返回所有的值 |
right join | 即使左表中没有匹配,也会从右表中返回所有的值 |
多张表查询
先查两张表,再增加多个表
-- 查询参加考试的学员的信息(三表查询)
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` k
ON r.subjectno=k.subjectno
自连接:自己的表和自己的表连接。核心:一张表拆为两张一样的表
父类表:
categoryid | categoryname |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类表:
pid | categoryid | categoryname |
---|---|---|
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`
练习:
-- 查询参加高等数学-1考试同学的信息
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname ='高等数学-1'
4.5分页和排序
排序:
-- 排序:升序asc 降序desc
-- order by
-- 查询结果根据成绩排序:降序 升序
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname ='高等数学-1'
ORDER BY studentresult ASC
分页:
-- 100万条数据
-- 为什么分页:缓解数据库压力,给人更好的体验
-- 分页,每页显示五条数据
-- 语法:limit 起始值,页面的大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname ='高等数学-1'
ORDER BY studentresult ASC
LIMIT 0,1
-- 第一页:limit 0,5
-- 第二页:limit 5,5
-- 第三页:limit 10,5
-- 第n页: limit (n-1)*pagesize,pagesize(页面大小)
-- n:当前页
-- (n-1)*pagesize:初始值
-- 总页数:数据总数/页面大小
语法:limit (起始值下标,页面大小)
-- 查询 高等数学课程成绩排名前十的学生,且分数大于80的学生信息
SELECT s.studentno,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
WHERE subjectname = '高等数学-1'AND studentresult>80
ORDER BY studentresult DESC
LIMIT 0,10
4.6子查询
where(这个值是计算出来的)
本质:在where语句中嵌套一个子查询语句
where(select* from)
-- 查询数据库结构一所有的考试结果
-- 子查询
SELECT `studentno`,`subjectno`,`studentresult`
FROM result
WHERE subjectno=(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
ORDER BY studentresult DESC
4.7分组及过滤
-- 查询不同课程的平均分,最高分,最低分
-- 核心:根据不同的课程,有分组的概念
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM `result` r
INNER JOIN `subject` s
ON r.subjectno=s.subjectno
GROUP BY r.subjectno -- 通过什么字段分组
HAVING AVG(studentresult)>=80
4.8select小结
select 去重 要查询的字段 from 表(表和字段可以取别名)
xxxx join 要连接的表 on 等值判断
where (具体的值/子查询语句)
group by(通过哪个字段来分组)
having(过滤分组后的信息,条件和where一样)
order by(通过哪个字段排序 :升序asc 降序desc)
limit start pagesize
5.mysql函数
5.1常用函数
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4)-- 向上取整
SELECT FLOOR(9.4)-- 向下取整
SELECT RAND()-- 0-1的随机数
SELECT SIGN(-8)-- 判断一个数的符号
-- 字符串函数
SELECT CHAR_LENGTH('即使再小的帆也能远航')-- 字符串长度
SELECT CONCAT ('我','爱','你们')-- 拼接字符串
SELECT INSERT('我爱编程',1,4,'超级热爱')-- 插入,替换,从某个位置开始替换某个长度
SELECT LOWER('DSsad')-- 小写字母
SELECT UPPER('dssadad')-- 大写字母
SELECT INSTR ('helloworld','o')-- 返回第一次出现的字串的索引
SELECT REPLACE('helloworld','hello','你好')-- 替换出现的指定字符串
SELECT SUBSTR('helloworld',1,4)-- 返回指定的子字符串(原字符串,开始的位置,截取的长度)
SELECT REVERSE('helloworld') -- 反转字符串
-- 查询姓张的同学,名字 改为周
SELECT REPLACE(studentname,'张','周') FROM student
WHERE studentname LIKE '张%'
-- 时间和日期函数(记住)
SELECT CURRENT_DATE() -- 获取当前日期
SELECT NOW()-- 获取当前时间
SELECT LOCALTIME()-- 本地时间
SELECT SYSDATE()-- 系统时间
-- 系统
SELECT USER()
SELECT VERSION()
5.2聚合函数(常用)
函数名称 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 求平均 |
max() | 最大值 |
min() | 最小值 |
… | … |
-- 都能统计表中的数据
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 平均分 FROM result
SELECT MAX(studentresult) AS 最高分 FROM result
SELECT MIN(studentresult) AS 最低分 FROM result
5.3数据库级别的md5加密
什么是md5:不可逆,具体值的md5值是一样的
CREATE TABLE `testMD5`(
`id` INT(4)NOT NULL,
`name`VARCHAR(20)NOT NULL,
`pwd`VARCHAR(20)NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 明文密码
INSERT INTO testMD5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')
-- 加密
UPDATE testMD5 SET pwd =MD5(pwd)
-- 插入的时候加密
INSERT INTO testMD5 VALUES(4,'小明',MD5('123456'))
-- 如何校验:将用户传进来的密码进行加密,比对加密后的值
SELECT * FROM testMD5 WHERE `name`='小明'AND pwd=MD5('123456')
6.事务
6.1什么是事务
要么都成功,要么都失败
1.sql执行 a给b转账 a1000 b200 a转200给b
2.sql执行 b收到a的钱 a800 b400
将一组sql放在一个批次中执行
INNODB支持事务
事务原则:ACID原则
原子性,一致性,隔离性,持久性(脏读,幻读)
原子性:两个步骤一起成或者一起失败
一致性:事务操作前后的状态一致,符合最终一致性
隔离性:多个用户同时操作,不会对单一事务进行影响
持久性:事务结束后的数据不会随外界原因导致数据丢失
隔离级别:
脏读:一个事务读取了另外事务未提交的数据
不可重复读:读取表中某一行的数据,多次读取结果不同
幻读:读取到了别的事务插入的数据
执行事务
-- mysql默认开始事务自动提交
SET autocommit =0 -- 关闭
SET autocommit =1 -- 开启(默认)
-- 手动处理事务
SET autocommit =0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始
INSERT xx
INSERT xx
-- 提交 :持久化
COMMIT
-- 回滚:回到原来的样子
ROLLBACK
-- 事务结束
SET autocommit =1 -- 开启自动提交
模拟场景
-- 模拟场景(转账)
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
CREATE TABLE `account`(
`id` INT(4)NOT NULL AUTO_INCREMENT,
`name`VARCHAR(20) NOT NULL,
`money`DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`money`) VALUES
('a',2000.00),('b',10000.00)
-- 模拟转账
SET autocommit = 0 -- 关闭自动提交
START TRANSACTION -- 开启事务
UPDATE account SET money =money-500 WHERE `name`='a'
UPDATE account SET money = money+500 WHERE `name`='b'
COMMIT -- 提交事务
ROLLBACK -- 回滚
SET autocommit =1 -- 开始自动提交
7.索引
索引是帮助mysql高效获取数据的数据结构
7.1索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引 primary key
-唯一标识:主键不可重复 ,只能有一个列作为主键 - 唯一索引 unique key
-避免重复的列出现,可重复 ,多个列可标识为唯一索引 - 常规索引 key
-默认的,key关键字来设置 - 全文索引 fulltext
-在特定的数据库引擎下,快速定位数据
基础语法
-- ========索引的使用================
-- 在创建表的时候给字段增加索引
-- 创建完毕后增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个索引(索引名)列名
ALTER TABLE student ADD FULLTEXT INDEX `studentname`(studentname)
-- 分析sql执行的状况
EXPLAIN SELECT * FROM student -- 常规,非全文索引
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('张')
7.2测试索引
CREATE TABLE `app_user` (
`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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 插入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`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
SET i=i+1;
END WHILE;
RETURN i;
END;
DROP FUNCTION mock_data
SELECT mock_data() -- 执行此函数 生成一百万条数据
SELECT * FROM app_user
WHERE `name`='用户999999'
EXPLAIN SELECT * FROM app_user WHERE `name`='用户999999'
-- id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`)
索引在小数据量时用处不大,大数据时区别明显
7.3索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
8.权限管理和备份
8.1用户管理
sqlyog可视化管理
sql命令操作
用户表:mysql.user
本质:对这张表进行增删改查
-- 创建用户
CREATE USER xuebo IDENTIFIED BY '123456'
-- 修改密码(当前用户)
SET PASSWORD =PASSWORD('111111')
-- 修改密码(指定用户)
SET PASSWORD FOR xuebo =PASSWORD('123456')
-- 重命名
RENAME USER xueboo TO xuebo
-- 用户授予全部权限
-- 除了给别人授权其他都可以做
GRANT ALL PRIVILEGES ON *.* TO xuebo
-- 查看权限
SHOW GRANTS FOR xuebo
-- root用户的权限
SHOW GRANTS FOR root@localhost
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM xuebo
-- 删除用户
DROP USER xuebo
8.2MYSQL备份
为什么要备份:
- 保证重要数据不丢失
- 数据转移
mysql数据库备份的方式 - 直接拷贝物理文件
- 在sqlyog这种可视化工具中手动导出
- 使用命令行导出:mysqldump 命令行使用
命令行导出:
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
命令行导入:
use school source d:/a.sql
9.规范数据库设计
9.1为什么需要设计
当数据库比较复杂时,需要设计
糟糕的数据库设计
- 数据冗余,浪费空间
- 数据插入删除比较麻烦/异常
- 程序性能差
良好的数据库设计 - 节省内存空间
- 保证数据的完整性
- 方便系统的开发
软件开发中数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图 E-R图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 评论表
- 友链表(友链信息)
- 自定义表(系统信息,某个关键字,或者一些主字段)
- 标识实体(把需求落地到每个字段)
- 标识实体之间的关系
- 写博客:user–>博客
- 创建分类:user–>category
- 关注:user–>user
- 友链:links
- 评论:user-user-blog
9.2三大范式
数据规范化:
- 信息重复
- 更新异常
- 插入异常:无法正常显示信息
- 删除异常:丢失有效信息
三大范式
第一范式:
原子性:保证每一列不可再分
第二范式:
前提:满足第一范式
每张表只描述一件事情
第三范式
前提:满足第一范式和第二范式
确保数据表中的每一列数据都和主键直接相关,不能间接相关
规范和性能的问题
关联查询的表不能超过三张
- 考虑成本,用户体验时,性能更加重要
- 适当考虑规范性
- 故意增加冗余的字段(从多表查询变为单表查询)
故意增加计算列(从大数据量减为小数据量的查询)
10.JDBC
10.1数据库驱动
程序会通过数据库驱动和数据库打交道
10.2jdbc
为了简化开发人员对数据库的统一操作,提供了一个规范java操作数据库的规范,JDBC,规范的具体实现由具体厂商去做
对于开发人员,只需要掌握jdbc接口的操作即可
java.sql
javax.sql
还需要数据库驱动包 mysql-connector-java
10.3第一个jdbc程序
创建测试数据库
1.创建一个普通项目
2.导入数据库驱动
3.编写测试代码
package xuebo.lesson1;
import com.mysql.jdbc.Driver;
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
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";
String username="root";
String password="123456";
//3.连接成功,返回数据库对象
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行sql的对象
Statement statement = connection.createStatement();
//5.执行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("birthday="+ resultSet.getObject("birthday"));
System.out.println("=========================================");
}
//6.释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结:
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集()
- 释放连接
== 分析代码==
DriverManager与connection
Class.forname("com.mysql.jdbc.Driver");// 固定写法,加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8";
// mysql–3306
// jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
statement 执行sql的对象
String sql ="SELECT * FROM users";//编写ssql
statement.executeQuary();//查询操作返回ResultSet
statement.execute();//执行任何sql
stetement.executeUpdate();//更新,插入,删除都用这个
ResultSet查询的结果集,封装所有的查询结果
获得指定的数据类型
resultSet.getObject();//不知道类型的情况下使用
resultSet.getString();
resultSet.getInt();
遍历:指针
resultSet.next(); //移动到下一个数据
释放资源
//6.释放连接
resultSet.close();
statement.close();
connection.close();
10.4statement对象
statement对象用于向数据库发送sql语句。增删改查语句
statement.executeQuery:查询语句
statement.executeUpdate:修改数据库,返回受影响的行数
CRUD操作-create
使用executeUpdate完成添加数据操作
Stetement st=conn.createStatement();
String sql ="insert into user(...)values(...)";
int num=st.executeUpdate(sql);
if(num>0){
system.out.println("插入成功!");
}
CRUD操作-delete
Stetement st=conn.createStatement();
String sql ="delete from user where id=1";
int num=st.executeUpdate(sql);
if(num>0){
system.out.println("删除成功!");
}
CRUD操作-update
Stetement st=conn.createStatement();
String sql ="update user set name=' ' where name=' '";
int num=st.executeUpdate(sql);
if(num>0){
system.out.println("修改成功!");
CRUD操作-read
Stetement st=conn.createStatement();
String sql ="select * from user where id=1";
ResultSet rs= st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型,映射到java对象中
}
代码实现
- 提取工具类
package xuebo.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.properties");
Properties properties = new Properties();
properties.load(in);
driver= properties.getProperty("driver");
url= properties.getProperty("url");
username= properties.getProperty("username");
password = properties.getProperty("password");
//1.驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
- 编写增删改的方法
package xuebo.lesson2;
import xuebo.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,'薛博','123456','12313@qq.com','2021-01-23')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
- 编写查询的方法
package xuebo.lesson2;
import xuebo.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestQuery {
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 WHERE id=1";
rs = st.executeQuery(sql);
while(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
sql注入went
sql存在漏洞,会被攻击导致数据泄露sql会被拼接 or
10.5preparestatement对象
可以防止sql注入,且效率更高
1.新增
package xuebo.lesson3;
import xuebo.lesson2.utils.JdbcUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestInsert {
public static void main(String[] args) {
Connection conn =null;
PreparedStatement st = null;
try {
conn = JdbcUtils.getConnection();
//区别:
//使用问好占位符代替参数
String sql ="INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)values(?,?,?,?,?)";
st = conn.prepareStatement(sql);// 预编译sql
//手动给参数赋值
st.setInt(1,4);//id赋值
st.setString(2,"xuebo");
st.setString(3,"122313");
st.setString(4,"131@qq.com");
//注意点:sql.Date 数据库 java.sql.Date()
// util.Date Java new Date().getTime()获得时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
// 执行
int i = st.executeUpdate();
if(i>0){
System.out.println("添加成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2.删除
只需要修改sql语句和参数
3.更新
只需要修改sql语句和参数
4.查询
package xuebo.lesson3;
import xuebo.lesson2.utils.JdbcUtils;
import java.sql.*;
public class TestQuery {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st =null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
String sql="select * from users where id=?";
st = conn.prepareStatement(sql);
st.setInt(1,1);
rs = st.executeQuery();
if(rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.7使用IDEA连接数据库
可以网上查看
10.8事务
要么都成功,要么都失败
ACID原则
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内重复读取表中的数据,数据发生了改变
幻读:在一个事务内读取到了别人插入的数据导致数据前后不一致
代码实现
package xuebo.lesson4;
import xuebo.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) throws SQLException {
Connection conn =null;
PreparedStatement st =null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
//1.关闭数据库的自动提交,自动开启事务
conn.setAutoCommit(false);
//2.执行sql
String sql1 ="update account set money = money-500 where name ='A'";
st=conn.prepareStatement(sql1);
st.executeUpdate();
int x =1/0;//报错
String sql2 ="update account set money = money+500 where name ='B'";
st=conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.commit();
System.out.println("成功");
} catch (SQLException throwables) {
conn.rollback();
System.out.println("有错");
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.9数据库连接池
数据库连接–执行–释放
连接–释放十分浪费资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
编写连接池,实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池后,我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包:
commons-dbcp-1.4
commons-pool-1.6
C3P0
无论使用什么数据源,本质是一样的,DataSource接口不变,方法就不会变