MySQL
MySQL初识
1.1连接数据库
命令行连接
mysql -uroot -p123456 --连接数据库
update mysql.user set authentication_string=password('132456')where user='root' and Host = 'localhost'; --修改用户密码
flush privileges; --刷新权限
----------------------------------
--所有语句都是用;结尾
show databases; --查看所有数据库
mysql> use school --切换数据库use数据库名
Database changed
show tables; --查看数据库中所有表
describe student; --显示数据库中所有表的信息
create database heihiehie;--创建一个数据库
exit;--退出连接
-- 单行注释(SQL的本来的注释)
/* (SQL的多行注释)
hello
ds
*/
1.2.操作数据库
1.创建数据库
CREATE DATABASE [IF NOT EXISTS] hello;
2.删除数据库
DROP DATABASE [IF EXISTS] hello
3.使用数据库
-- 如果使用的时特殊字符,则需要加上``
USE 'scool'
4.查看数据库
SHOW DATABASES --查看所有的数据库
1.3.数据库的列类型
数值
数值
-
tinyint 十分小的数据 1个字节
-
smallint 较小的数据 2个字节
-
mediumint 中等大小的数据 3个字节
-
int 标准的整数 4个字节
-
bigint 较大的数据 8个字节
-
float 浮点数 4个字节
-
double 浮点数 8个字节
-
decimal 字符串形式的浮点数
字符串
-
char 字符串固定大小的 0~255
-
varchar 可变字符串 0~ 65535 常用变量 String
-
tinytext 微型文本 2^16-1 保存大文本
时间日期
Java.util.Data
- data YYYY-MM-DD 日期格式
- time HH:mm:ss 时间格式
- datatime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- year 年份表示
null
- 没有值,未知
- 注意,不要使用NULL进行运算,结果为NULL
1.4.数据库的字段属性(重点)
Unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill
- 0填充的
- 不足的位数,使用0来填充,int(3),5……005
自增
- 通常理解为自增,自动在上一条记录的基础上+1(默认)
- 通常用来设计唯一的主题~index,必须是整数类型
- 可以自定义设计主键自增的起始值和步长
非空 NULL not null
- 假设设置为not null ,如果不给他赋值,就会报错
- NUll,如果不填写值,
默认:
- 设置默认的值
- sex,默认值为男,如果不指定该列的值,则有默认的值
1.5.创建数据库表
-- 目标:创建一个school数据可以
-- 创建学生表(列,字段)使用SQL创建
-- 学号int,登陆密码varchar(20)姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 使用英文(),表的名称和字段尽量使用``括起来
--AUTO_INCREMENT自增
--字符串使用单引号括起来
--所有的语句后面加,最后一个不用加
-- PRIMARY KEY 主键,一般一个表只有唯一一个主键
CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR (20) NOT NULL DEFFAULT '123456' COMMENT '密码',
PRIMARY KRY (`id`)
)ENGING=INNNODB DEFAULT CHARSET=UTF8
格式
CREATE TABLE [IF NOT EXISTS] `表名`(
'字段名' 列类型[属性][索引][注释],
'字段名' 列类型[属性][索引][注释],
……
)[表类型][字符串设置][注释]
常用命令
SHOW CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看student数据表的定义语句
DESC student --显示表的结构
1.6.数据表的类型
--关于数据库引擎
/*
INNODB默认使用
MYISAM 早些年使用
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
常规使用操作:
- MYISAM 节约空间,速度较快
- INNODB 安全性高,事务的处理,多表多用户操作
在物理空间存在的位置
所有的数据库文件都在Data目录下,一个文件夹对应一个数据库
本质还是文件的存储
MySQL引擎在物理文件上的区别
- INNODB在数据库表中只有一个*.frm文件,以及上级目录下的ibadta1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
CHARSET=utf8
不设置的话,会是MySQL默认的字符集编码~(不支持中文)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码
character-set-server=utf8
1.7.修改删除表
修改
-- 修改表名 : ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher 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(1) --字段重命名
--删除表的字段:ALTER TABLE 表名 DROP 字段名
ALTER TABLE teavher1 DROP age1
删除
--删除表(如果存在再删除)
DROP TABLE IF EXISTS teacher1
2.MySQl数据管理
2.1外键(了解即可)
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMRNT '年纪id',
`gradname` VARCHAR(50) 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 DEFFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR (20) NOT NULL DEFFAULT '123456' COMMENT '密码',
PRIMARY KRY (`id`)
)ENGING=INNNODB DEFAULT CHARSET=UTF8
--创建表得时候没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES GRADE (`gradeid`);
--ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列)REFERENCES 哪个表(哪个字段)
以上操作都是物理外键,数据库级别的外键,不建议使用(避免数据库过多造成困扰)
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)
- 我们想使用多张表的数据,想使用外键(程序去实现)
2.2 DML语言 (全部记住)
- insert
- update
- delete
2.3 添加
insert
--插入语句(添加)
--insert into 表名(【字段名1,字段名2,字段名3……】)values ('值1'),('值2'),('值3')……
INSERT INTO `grade`(`gradename`)VALUES('大四')
--由于主键自增我们可以省略(如果不写表的字段,他就会一一匹配)
INSERT INTO `grade` VALUES('大三')
-- 一般写插入语句,我们一定要数据和字段一一对应
--插入多个字段
INSEERT INTO `grade`(`gradename`)
VALUES('大一'),('大二')
INSERT INTO `student`(`name`)VALUES('张三')
INSERT INTO `student`(`name`,`psw`)VALUES('张三','1111')
语法:insert into 表名(【字段名1,字段名2,字段名3……】)values (‘值1’),(‘值2’),(‘值3’)……
注意事项:
1.字段和字段之间用英文的逗号隔开
2.字段是可以省略的,但是后面的值必须要一一对应
3.可以同时插入多条数据,VALUES后面的值,需要使用,隔开,即VALUES(),(),……
2.4 修改
update
--修改学院名字,带了简介
UPDATE `student` SET `name`='孙维' WHERE ID = 1;
--不指定条件的情况下,会改动所有的表
UODATE `student` SET `name`='传擦'
--修改多个属性,都好隔开
UPDATE `student` SET `name` ='孙维' ,`emial`='132'WHERE ID =1;
--语法:
--UPDATE set 表名 colnum_name = value,[colnum_name = value....]where [条件]
条件:where子句运算符id等于某个值,大于某个值,在某个区间内修改
操作符会返回布尔值
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | fasle |
<>或!= | 不等于 | 5<>6 | true |
> | |||
< | |||
>= | |||
<= | |||
between…and… | 在某范围内 | [2,3] | |
and | 和&& | 5>1and1>2 | |
or | 或|| | 5>1or 2>1 |
--通过多个条件定位数据
UPDATE `student` SET `name` = '老我' WHERE `name` = 'dsa' AND sex='男'
语法:UPDATE 表名 set colnum_name = value,[colnum_name = value,…] where [条件]
注意:
-
colnum_name是数据库的列,尽量带上``
-
条件,筛选的条件,如果没有指定,则会修改所有的列
-
value,是一个具体的值,也可以是一个变量
-
多个设置的属性之间,使用英文逗号隔开
update `student` set `birthday`=current_time where `name` = 'sda'and sex='ds'
2.5 删除
delete 命令
语法:delete from 表名 [where 条件]
-- 删除数据(全部删除)
delete from `student`
--删除指定数据
delete from `student` where id = 1;
truncate 命令
作用:用于清空数据,数据库、表的结构和索引约束不发生改变
--清空student表
truncate `student`
delete 和truncat 区别
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- truncat 重新设置自增列计数器会归零
- truncat不会影响事务
3.DQL查询数据(最重点)
3.1 DQL
(Data Query LANGUAGE:数据查询语言)
- 所有的查询操作都用它Select
- 简单的查询,复杂的查询都能完成
- 数据库中最核心的语言,最重要的语句
- 使用频率最高的语句
3.2 指定查询字段
--查询全部学生 Select 字段 From 表
select * from student
--查询指定字段
select `studentno`,`studentname` frome stduent
--别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
select `studentno` as 学号,`studentname` as 学生姓名 from student as s
--函数concat(a,b)
select concat('姓名',studentname) as 新名字 from student
语法:select 字段……from表
有的时候,列名字不是那么的见名知意,我们就会起别名 AS 字段名 as 别名 表名 as 别名
去重
作用:去除select查询出来的结果中重复的数据,重复的数据只显示一条
--查询一下有哪些同学参加了考试,成绩
select * from result --查询全部的考试成绩
select `studentno` from resilt --查询有哪些同学参加了考试
select distinct `studentno` from result -- 发现重复数据,进行去重
数据库的列(表达式)
selcet version() --查询系统版本(函数)
select 100*3-1 as 计算结果 --用来计算(表达式)
select @@auto_increment --查询自增的步长(变量)
--学员的考试成绩 +1分查看
select `studentno`,`studentresult`+1 as `提分后` from result
数据库中的表达式:文本值,列,null,函数,计算表达式,系统变量……
select 表达式 from 表
3.3 where条件子句
作用:检索数据中符合条件
的值
搜索的条件有一个或者多个表达式组成!结果为布尔值
逻辑运算符
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&& b | 逻辑与,两个都为真,结果为真 |
or || | a or b a || b | 逻辑或,一个为真,则为真 |
Not ! | not a !a | 逻辑非,真为假,假为真 |
select studentno,studentresule from result
--查询考试成绩在95-100之间
select studentno,studentresult from result
where studentresult>=95 and studentresult <=100
--and &&
select studentno,studentresult from result
where studentresult>=95 && studentresult <=100
--模糊查询
select studentno,studentresult from result
where studentresult between 95 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 | 如果操作符不为null,则结果为真 |
between | a between b and c | 若a在b和c之间,则结果为真 |
like | a like b | sql匹配,如果a匹配b,则结果为真 |
in | a in (a1,a2,a3…) | 假设a在a1,或者a2之间的某一个值中,则为真 |
--模糊查询
--查询姓刘的同学
--like结合 %(代表0到任何一个字符) _(一个字符)
select `studnetno`,`studnetname` from `student`
where stduentname like ’刘%‘
--查询姓刘的同学名字后面只有一个字符
select `studnetno`,`studnetname` from `student`
where stduentname like ’刘_‘
--查询姓刘的同学,名字后面只有两个字的
select `studnetno`,`studnetname` from `student`
where stduentname like ’刘__‘
--查询名字里有加的同学 %嘉%
select `studnetno`,`studnetname` from `student`
where stduentname like ’%嘉%‘
--in(具体的一个或者多个值)
--查询1001,1002,1003号学生
select `studnetno`,`studnetname` from `student`
where stduentno in (1001,1002,1003)
--查询在北京的学生
select `studnetno`,`studnetname` from `student`
where addressin ('北京')
3.4 连表查询
select s.studetno ,studentname,subjectno,studetntresult
form student as s
inner join result as r
on s.stduentno = 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 s.studentno = r.studentno
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
--=====================连表查询 join===================
--查询参加了考试的同学(学号,姓名,科目编号,分数)
select * from studnet
select * from result
--join (连接的表) on (判断的条件) 连接查询
--where 等值查询
select s.studentno,studentname,subjectno,studentresult
from student as s
inner join result as r
where s.studentno = r.studentno
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
--思考题 (查询参加了考试的学生的信息:学号,姓名,科目,分数)
/*1.分析需求,分析查询的字段来自那些表,student,result,subject(连接查询)
2.确定使用哪些连接查询
确定交叉点(这两个表中哪些数据是相同的)
判断条件:学生表中studentno = 成绩表 studentno
*/
select s.studentno,studentname,subjectname,studentresult
from student as s
right join result as r
on s.studentno = r.studentno
inner join `subject` sub
on r.subjectno = sub.subjectno
--我要查询哪些数据 select……
--从哪几个表中查询from 表 xxx join 链接的表 on 交叉条件
--假设存在一种多张表查询,先查询两张表然后在慢慢增加
--from a left join b
--from a right join b
自连接
3.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 sutudentresult asc
分页
--分页,每页只显示五条数据
--语法:limit起始值,页面的大小
--网页应用:当前,总的页数,页面的大小
--linit 0,5 1~5
--limit 1,5 2~6
--limit 6,5
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` sub
on r.`subjectno` = sub.`subjectno`
where subjectname = '数据库结构-1'
order by studentresult asc
linit 5,5
--第一页 limit 0,5 (1-1)*5
--第二页 limit 5,5 (2-1)*5
--第三页 limit 10,5 (3-1)*5
--第n页 limit 0,5 (n-1 )*5\
--[pagesize:页面大小]
--【(n-1)*pagesize:起始值】
--【n:当前页】
--[数据总值/页面大小=总页数]
语法:limit(查询起始下标,pagesize)
3.6 子查询
--1、查询数据库结构-1的所有考试结果(学号,科目编号,成绩),降序排序
-- 方式一:使用连接查询
select `studentno`,r.`subjectbi`,`studentresult`
from `result` r
inner join `subject` sub
on 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 s.studentno,studentname
from student s
inner join result r
on s.studentno = r.studentno
inner join `subject` sub
on r.`subjectno` = sub.`subjectno`
where `subjectname`='高等数学-2' and studentresult >=80
--分数不小于80分的学生的学号和姓名
select distinct s.`studentno`,`studentname`
from student s
inner join result r
on r.studentno =s.studentno
where `studentresult`>=80
--在这个基础上增加一个科目。高等数学-2
--查询高等数学-2的编号
select distinct s.`studentno`,`studentname`
from studetn s
inner join result r
on r.studentno = s.studentno
where `studentresult` >=80 and `subjectno` =(
select subjectno from `subject`
where `subjectname` ='高等数学-2'
)
--再改造
select studentno,studentname from student where studentno in (
swlect studentno from result where studentresult >80 and subjectno=(
select subjectno from `subject` where `subjectname` = '高等数学-2'
)
)
3.7 分组和过滤
--查询不同课程的平均分,最高分,最低分,平均分大于80
--核心:根据不同的课程分组
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result r
inner join `subject` sub
on r.`subjectno` = sub.`subjectno`
group by r.subjectno --通过字段来分组
having 平均分>80
4.MySQL函数
4.1 常用函数
--数学运算
select abs (-8) --绝对值 8
select ceiling(9.4) --向上取整 10
select floor (9.4) --向下取整 9
select rand () --返回一个0-1之间的随机数
select sign (10) --判断一个数的符号 0返回0 负数返回-1 正数返回 1
--字符串函数
select char_length('和嘿嘿') --字符串长度 3
select concat ('我','你')--拼接字符串 我你
select insert ('你好你你你',1,2,'我') --查询 替换 从某个位置开始替换某个长度 我你你你
select lower ('KAUD')--转小写 kaud
select upper('kkl')--转大写KKL
selecy instr ('qwe','e')--返回第一次出现的子串的索引 3
select replace ('坚持坚持','坚持','努力') -- 替换 努力努力
select substr ('你好呀大撒大撒',2,3) --返回指定的子字符串 (源字符串,截取的位置,截取的长度)好呀大
select reverse('你好')--反转 好你
--查询姓周的同学。将他的名字换成邹
select resplace(studentname,'周','邹') from student
where sutdentname like '周%'
--时间和日期函数(记住)
select current_date ()-- 获取当前日期
select curdate()-- 获取当前日期
select now()--获取当前时间
select localtime()--获取本地时间
select sysdate()--系统时间
select year(now())--获取年份
--系统
select system_user()
4.2 聚合函数(常用)
函数名称 | 描述 |
---|---|
count | 计数 |
sum | 求和 |
avg | 平均值 |
max | 最大值 |
min | 最小值 |
select count(studentname) from student; --count(指定列),忽略null值
select count(*) from student;--count(*) 不忽略null值 本质 计算行数
select count(1) from studetn;--count(1) 不忽略null值 本质 计算行数
select sum(`studentresult`) as 总和 form result --总和
select avg(`studentresult`) as 平均分 form result--平均分
select max(`studentresult`) as 最高分 form result--最高分
select min(`studentresult`) as 最低分 form result--最低分
5.事务
5.1.什么是事务
要么都成功,要么都失败
——————————
1、SQL执行 a给b 转账 a1000 ---->200 b 200
2、SQL执行 b收到a的钱 a 800 -----> b 400
——————————
将一组SQL放在一个批次中去执行
事务原则:ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读……)
原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保持一直
持久性(Durablilty)
事务一旦提交则不可逆,被持久化到数据库中
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,事务之间要相互隔离
隔离所导致的一些问题
脏读:
指一个事务读取了另外一个事务未提交的数据
不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同(不一定是错误,只是场合不对)
虚读(幻读):
指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
mysql是默认开启事务自动提交的
事务执行流程
执行事务
模拟场景
6、索引
MYSQL官方怼索引的定义为:索引(index)是帮助MYSQL搞笑获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。
6.1索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
-
主键索引(PRIMARY KEY)
- 唯一的标识,主键不可重复,只能有一个列作为主键
-
唯一索引(UNIQUE KEY)
- 避免出现重复的列出现,唯一索引可以重复,多个列都可以标识位 唯一索引
-
常规索引(KEY/INDEX)
- 默认的,index,key关键字来设置
-
全文索引(Fulltext)
- 在特定的数据库引擎下才有,MyILSAM
- 快速定位数据
基础语法
--索引的使用
--1.在创建表的时候给字段增加索引
--2.创建完毕后,增加索引
--显示所有的索引信息
SHOW INDEX FROM student
--增加一个全文索引(索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX 'studentName'('studnetName');
--EXPLATN 分析sql执行的情况
EXPLAIN SELECT * FROM student;--非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');
6.2测试索引
6.3 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据类型
Hash类型的索引
Btree:InnDB的默认数据结构
阅读:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
7.权限管理和备份
7.1用户管理
SQL命令操作
用户表:mysql.user
本质:对这张表进行增删改查
--创建用户CREATE USER 用户名 IDENTIFIED BY '密码'
CREATR USER kuangshen IDENTIFIED BY '132465'
--修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('132456')
--修改密码(修改指定用户密码)
SET PASSWORD FOR sun = PASSWORD('123456')
--重命名 RENAME USER 原来名字 TO 新的名字
RENAME USER sun TO sun1
--用户授权 ALL PRIVILEGES 全部的权限,库,表
--ALL PRIVIKEGES除了给别人授权,其他都能够干
GRANT ALL PRIVILEGES ON *.* TO sun1
--查询权限
SHOW GRANTS FOR sun1--查看指定用户的权限
SHOW GRANTS FOR root@localhost
--root用户权限:GRANT ALL PRIVIEGES ON *.* TO 'ROOT'@'localhost' WITH GRANT OPTION
--撤销权限 REVOE 哪些权限,仔哪个库撤销,给谁撤销
REBOKE ALL PRIVILEGES ON *.* FROM sun1
--删除用户
DROP USER sun1
7.2MySQL备份
为什么要备份
- 保证重要的数据不丢失
- 数据转移A–>B
MySQL数据库备份的方式
-
直接拷贝物理文件
-
在Sqlyog这种可视化工具中手动导出
- 在想要导出的表或者库中,右键,选择备份或导出
-
使用命令行导出 mysqldump 命令行使用
# mysql -h 主机 -u 用户名 -p 密码 数据库 表名 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p1236456 scool student>D:/a.sql # mysql -h 主机 -u 用户名 -p 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p1236456 scool student result >D:/b.sql # mysql -h 主机 -u 用户名 -p 密码 数据库 > 物理磁盘位置/文件名 mysqldump -hlocalhost -uroot -p1236456 scool >D:/c.sql # 导入 # 登录的情况下,切换到指定的数据库 # sourse 备份文件 sourse d:/a.sql mysql -u用户名 -p密码 库名 < 备份文件
8、规范数据库设计
8.1 为什么需要设计
当数据库比较复杂的时候,我们就需要设计了
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦,异常【屏蔽使用物理外键】
- 程序的性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤:(个人博客)
- 收集信息,分析需求
- 用户表(用户登录注销,用户的个人信息,写博客,创建分类)
- 分类表(文章分类,谁创建的)
- 文章表(文章的信息)
- 评论表(评论信息)
- 友链表(友链信息)
- 自定义表(系统信息,某个关键的字,或者一些主字段) key:value
- 标识实体(把需求落实到每个字段)
- 标识实体之间的关系
- 写博客:user–>blog
- 创建分类:user–>category
- 关注:user–>user
- 友链:links
- 评论:user-user-blog
9.2 三大范式
为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效的信息
三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,不能间接相关。
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标,(成本,用户体验)数据库的性能更加重要
- 再规范性能得问题得时候,需要适当得考虑一下规范性
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)
10、JDBC(重点)
10.1、数据库驱动
驱动:声卡,显卡,数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qDLd8cb4-1610528096474)(C:\Users\孙大维\AppData\Roaming\Typora\typora-user-images\image-20201214172537159.png)]
我们的程序会通过数据库驱动和数据库打交道
10.2、JDBC
SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC
对于开发人员来说,我们只需要掌握JDBC接口的操作即可
java.sql
javax.sql
还需要导入一个数据库驱动包
10.3 第一个JDBC程序
创建测试数据库
CREATE DATABASE jdbcstudy CHARACTER set utf8 COLLATE ust8_general_ci;
USE jdbcStudy;
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(1,'zhansan','123456','zs@sin.com','1998-12-12'),
(2,'lisi','123456','lisi@.com','1984-03-03'),
(3,'wangwu','123456','wangwu@.com','1998-05-05');
1、创建一个普通项目
2、导入数据库驱动
3、编写测试代码
步骤总结:
- 加载驱动
- 连接数据库DriverManager
- 获得执行sql的对象Statement
- 获得返回的结果集
- 释放连接
DriverManager
Class.forName("com.mysql.jdbc.Driver");//固定写法,加载驱动
Connection connection = DriverManager.getConnection(url,username,password);
// connection 代表数据库
//事务提交
//事务回滚
//数据库设置自动提交
connection.commit();
connection.rollback()
connection.setAutoCommit();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudent?useUnicode=true&characterEncoding=utf8&useSSL=ture";
//mysql--3306
//jdbc:mysql://主机号:端口号/数据库名?参数1&参数2&参数3
//oralce--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.getObject();
遍历,指针
resultSet.beforeFirst(); //移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.nect();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statment.close();
connection.close();//耗资源,用完就关掉
10.4 statement对象
Jdbc中的starement对象用于对数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增,删,改,查的sql语句,executeUpdate执行完后,将会放回一个整数(即增删改语句导致了数据库几行数据发生了变化)
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象
CRUD操作——add
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement st = conn,createStatement();
String sql = "insert into user(...)values(...)";
int num = str.execyteUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
CRUD操作——delete
使用executeUpdate(String sql)方法完成数据删除操作,示例操作:
Statement st = conn,createStatement();
String sql = "delete into user(...)values(...)";
int num = str.execyteUpdate(sql);
if(num>0){
System.out.println("删除成功!!!");
}
CRUD操作——update
使用executeUpdate(String sql)方法完成数据修改操作,示例操作:
Statement st = conn,createStatement();
String sql = "update into user(...)values(...)";
int num = str.execyteUpdate(sql);
if(num>0){
System.out.println("修改成功!!!");
}
CRUD操作——read
使用executeUpdate(String sql)方法完成数据查询操作,示例操作:
Statement st = conn,createStatement();
String sql = "select * from user where id=1";
ResultSet rs = str.execyteQuer(sql);
while(rs.next()){
//根据获取列的数据类型,分别调用rs的相应方法映射倒java对象中;
}
代码实现
1.提取工具类
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.printStcakTrace();
}
}
if(conn!=null){
try{
conn,close();
}carch(SQLException e){
e.printStackTrace();
}
}
}
2.编写增删改的方法,executeUpdate
增
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.getConnectin();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "INSERT INTO users(id,'NAME','PASSWORD','mail','brithday')"+"VALUES(4,'kuangshen','123456','432423@qq.com','2020-01-01')";
int i = st.executeUpdate(sql);
if(i>0){
System.out.prnitln("插入成功!");
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
删
public class TestInsert{
public static void main(String[] args){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnectin();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "DELETE FROM users WHERE id =4";
int i = st.executeUpdate(sql);
if(i>0){
System.out.prnitln("删除成功!");
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
改
public class TestInsert{
public static void main(String[] args){
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnectin();//获取数据库连接
st = conn.createStatement();//获得SQL的执行对象
String sql = "UPDATE users SET 'NAME'='kuangshen','email'='1321312@qq.com',WHERE id=1";
int i = st.executeUpdate(sql);
if(i>0){
System.out.prnitln("插入成功!");
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
3.查询的方法
查
public class TestSelect{
public static void main (String[] args){
Connection conn=null;
Statement st =null;
ResultSet rs =null;
try{
conn =JdbcUtils.getConnetion();
st = conn.createStatemnt();
//SQL
String sql ="sekect * from users where id = 1";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while(rs.next()){
System.out.println(rs.getString(columnLabel:"NAME"));
}
}catch(SQLEception e){
e.printStackTeace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
SQL注入的问题
sql存在漏洞,会被攻击导致数据泄露,SQL会被拼接or
public class SQL注入{
public static void main (String[] args){
//Login("kuangshen","132456");
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.cteateStatement();
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.5 PrepareStatement对象
prepareStatement可以防止SQL注入,效率更高
1.新增
public class TestInsert{
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
try{
conn = JdbcUtils.getConnectin();//区别,使用?占位符代替参数
String sql = "INSERT INTO users(id,'NAME','PASSWORD','mail','brithday') values(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setInt(1,4);//id
st.setString(2,"sunwei");
st.setString(3,"123456");
st.setString(4,"123456@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(sql);
if(i>0){
System.out.prnitln("插入成功!");
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
2.删除
public class TestDelete{
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
try{
conn = JdbcUtils.getConnectin();//区别,使用?占位符代替参数
String sql = "delete from users where id =?";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setString(1,4);
st.setDate(5,new java.sql.Date(new Date().getTime()));
//执行
int i = st.executeUpdate(sql);
if(i>0){
System.out.prnitln("插入成功!");
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
3.修改
public class TestUpdate{
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
try{
conn = JdbcUtils.getConnectin();//区别,使用?占位符代替参数
String sql = "update usres set 'name' =? where id =?;";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setString(1,"sunwei");
st.setId(2,"2");
//执行
int i = st.executeUpdate(sql);
if(i>0){
System.out.prnitln("更新成功!");
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
4.查询
public class TestSelect{
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs =null;
try{
conn = JdbcUtils.getConnectin();//区别,使用?占位符代替参数
String sql = "Select * from useres where if =?";
st = conn.prepareStatement(sql);//预编译SQL,先写sql,然后不执行
//手动给参数赋值
st.setId(1,4);
re = st.executeQuery();//执行
if(rs.next){
System.out.prnitln(rs.getString("NAME"));
}
}catch(SQLException e){
e.printStackTRace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
}
10.6、使用IDEA连接数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kPvoOcNj-1610528096481)(C:\Users\孙大维\AppData\Roaming\Typora\typora-user-images\image-20210113154531666.png)]
连接成功后可以选择数据库
双击数据库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YnEovacL-1610528096483)(C:\Users\孙大维\AppData\Roaming\Typora\typora-user-images\image-20210113154836015.png)]
更新数据
连接失败,查看原因
10.7、事务
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
1、开启事务conn.setAutoCommit(false);
2、一组业务执行完毕,提交事务
3、可以在catch语句中显示的定义回滚语句,但默认失败就会回滚
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
Result rs = null;
try{
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交,会自动开启事务
conn.setAutoCommit(false);//开启事务
String sql1 = "update account set money = money-100 where name ='A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sq2 = "update account set money = money-100 where name ='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.comit();
System.out.println("成功!");
}catch(SQLExection e){
try{
conn.rollback();//如果失败则回滚事务
}catch(SQLExection e1){
e1.printStackTrace();
}
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
10.8、数据库连接池
数据库连接——执行完毕——释放
连接——释放 十分昂菲系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:10
最大连接数:15
等待超时:100ms
编写连接池,实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包
commons-dbcp-1.4 , commons-pool-1.6
C3P0
需要用到的jar包
c3p0-0.9.5.5 . ,mchange-commins-java-0.2.19
结论
610528096484)]
[外链图片转存中…(img-B2RiIRmY-1610528096485)]
连接失败,查看原因
[外链图片转存中…(img-7LzTQxtt-1610528096486)]
10.7、事务
要么都成功,要么都失败
ACID原则
原子性:要么全部完成,要么都不完成
一致性:总数不变
隔离性:多个进程互不干扰
持久性:一旦提交不可逆,持久化到数据库
隔离性的问题:
脏读:一个事务读取了另一个没有提交的事务
不可重复读:在同一个事务内,重复读取表中的数据,表数据发生了改变
虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
1、开启事务conn.setAutoCommit(false);
2、一组业务执行完毕,提交事务
3、可以在catch语句中显示的定义回滚语句,但默认失败就会回滚
public static void main(String[] args){
Connection conn = null;
PreparedStatement st = null;
Result rs = null;
try{
conn = JdbcUtils.getConnection();
//关闭数据库的自动提交,会自动开启事务
conn.setAutoCommit(false);//开启事务
String sql1 = "update account set money = money-100 where name ='A'";
st = conn.prepareStatement(sql1);
st.executeUpdate();
String sq2 = "update account set money = money-100 where name ='B'";
st = conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕,提交事务
conn.comit();
System.out.println("成功!");
}catch(SQLExection e){
try{
conn.rollback();//如果失败则回滚事务
}catch(SQLExection e1){
e1.printStackTrace();
}
e.printStackTrace();
}finally{
JdbcUtils.release(conn,st,rs);
}
}
10.8、数据库连接池
数据库连接——执行完毕——释放
连接——释放 十分昂菲系统资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:10
最大连接数:15
等待超时:100ms
编写连接池,实现一个接口 DataSource
开源数据源实现
DBCP
C3P0
Druid:阿里巴巴
使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包
commons-dbcp-1.4 , commons-pool-1.6
C3P0
需要用到的jar包
c3p0-0.9.5.5 . ,mchange-commins-java-0.2.19
结论
无论使用什么数据源,本质还是一样得,Data Source接口不会变,方法就不会变