MySQL下载与安装
下载
去MySQL官网社区下载压缩版的最新版就好,这里贴上地址:MySQL :: Download MySQL Community Server
页面中选择自己的系统版本,我这里是windows,如何点击Looking for previous GA versions才可以选择以前的版本,在Other Downloads中下载第一个就好。我这里下载的是5.7.36
为什么要下载压缩版而不是安装版:方便卸载。安装版不便于卸载,以至于我们想重装mysql的时候会卸载不干净,就会导致无法安装上新的MySQL。
注:MySQL8以上的没有my.ini配置文件,因此本博客的安装教程不适用于MySQL8以上的版本
安装
-
下载会得到一个压缩包,里面就是MySQL的全部东西,直接解压到我们想安装MySQL的目录
-
添加环境变量,在 我的电脑–环境–属性–高级中选择PATH编辑。在弹出的表格或者提示框中输入你mysql安装目录下bin的路径,保存。(注意这里路径不要手动去输入,手动输入极容易出错。打开你的mysql下的bin目录,在文件的上方有个地址栏,点一下复制它粘贴过去就好了)
-
在你的MySQL安装根目录下也就是我这里的D:\Environment\mysql-5.7.36这个路径下新建文件
my.ini
(这里要注意的是必须打开文件扩展名才能创建ini文件)
-
使用记事本或者notepad++等编辑工具打开它,添加这几行(这里注意填你自己的MySQL安装路径)
[mysqld] basedir=D:\Environment\mysql-5.7.36\ datadir=D:\Environment\mysql-5.7.36\data\ port=3306 skip-grant-tables
-
启动管理员模式下的CMD(这里必须要是管理员模式,否则的话会导致安装出问题),然后把路径切换到mysql目录下的bin目录(直接输入
cd /d D:\Environment\mysql-5.7.36\bin
),然后输入mysqld --install
,等待执行完毕,提示中有successfully即可 -
再输入
mysqld --initialize-insecure --user=mysql
初始化数据文件,耐心等待执行完毕即可,这一步没有任何提示。 -
开启mysql服务,输入
net start mysql
,等待提示mysql服务启动成功即可 -
启动成功之后,要登陆mysql去修改我们的登陆密码,输入
mysql -u root -p
即可,如果提示enter password:,那么直接回车即可,登陆成功之后会看到命令行前面变成了mysql> -
修改MySQL密码,直接输入:
update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';
回车执行即可,然后要刷新权限输入flush privileges;
(至此,你的mysql账号和登陆密码就修改好了,密码尽量不要动保持123456就好,避免后面忘记密码要重装)提示信息包含OK即可 -
最后打开刚刚我们创建的my.ini文件(cmd不要关)把最后一行skip-grant-tables在前面加上#注释掉或者直接删除它,它的作用是方便刚刚我们登陆mysql的时候不需要输入密码就能登陆,然后回到我们的cmd重启一下mysql服务,先退出
exit
,然后net stop mysql
和net start mysql
-
在cmd窗口中测试登陆
mysql -u root -p123456
,登陆成功就意味着mysql安装完成了
图形化界面SQLyog的安装
到网上下载一个SQLyog安装,再百度一下注册码输入就好。后面mysql的学习基本上都是使用图形化软件的。所以这个必须要安装。我安装的是SQLyog Ultimate v12.08
MySQL命令行连接数据库
-- 命令行连接数据库
mysql -uroot -p123456
-- 修改用户密码,所有的sql语句都要使用;结尾
updata mysql.user set authentication_String=password('123456') where user='root' and Host = 'localHost';
-- 刷新权限
flush privileges;
-- 查看所有的数据库
show databases;
-- 切换数据库显示databases时表示切换成功
use 数据库名
-- 在某个数据库中使用该命令查看所有的表
show tables;
-- 显示表中的所有信息
describe 表名;
-- 创建一个新数据库
create database 数据库名;
-- 退出连接
exit;
-- 单行注释,--符号后面必须要跟空格才能开始写注释
/*
这是多行注释
*/
数据库分为4种语言
数据库细分可以分为4中语言
DDL 数据库定义语言
DML 数据库操作语言
DQL 数据库查询语言
DCL 数据库控制语言
我们常说的CRUD就是增删改查的意思
操作数据库
sql关键字不区分大小写
CREATE DATABASE IF NOT EXISTS 数据库名 /*生成数据库*/
DROP DATABASE IF EXISTS 数据库名 /*删除数据库*/
USE `school` /*使用(切换)数据库,当名字是某个关键字时使用反单引号`` */
SHOW DATABASES/*查看所有的数据库*/
数据库列类型
数值型
数据类型 | 内存大小 |
---|---|
tinyint | 1个字节 |
smallint | 2字节 |
mediumint | 3个字节 |
int(常用) | 4个字节 |
bigint | 8个字节 |
float | 4个字节 |
double | 8个字节 |
字符串
数据类型 | 内存大小 |
---|---|
char(字符串类型,固定大小) | 0~255 |
varchar(可变字符串)(常用变量相当于String) | 0~65535 |
tinytext(微型文本) | 2^8-1 |
text(文本类型)(一般用于保存大文本) | 2^16-1 |
时间日期
java.util.Date
date:YYYY-MM-DD,(日期格式)
time:HH:mm:ss,(时间格式)
datetime:(上两种的总和),(最常用)
timestamp:(时间戳),1970.1.1至今
year:年份表示
null
表示没有值或者未知
注:不要使用null进行运算
数据库字段属性
unsigned:
- 无符号数
- 该列不能申明为负数
zerofill:
- 0填充
- 使用0来填充不足的位数
自增:
- 在上一条的记录上+1
- 通常用来设置唯一的主键,必须是整数类型
- 可以自定义设置主键的起始值和自增的步长
非空:
- 设置not null,不赋值就会报错,必须赋值
- 设置null,一般情况,不填写值就为null
默认:
- 设置一个默认的值
- 如果不填写,自动将设置好的默认值填入
阿里巴巴规范手册规定
每一张表都必须存在以下五个字段:
- id 主键
version
版本- is_delete 伪删除
- gmt_create 创建时间
- gmt_update 修改时间
MySQL中数据类型长度问题(重要)
在修改表数据时,我们明明指定了id长度为3,但是却可以显示3333这样的四位数是为什么?
其实int(3)这个3指的是最大显示宽度跟我们的内存无关。也就是当我们选择0填充,插入一个1时,这个int(3)最大显示宽度会给我们限制到001这样,但如果你插入的是像3333这样int能够保存的数,但是超出了最大显示宽度,显示的是这个数的本身。(最大显示宽度为255)
使用sql语句建表
-- 一般表的名称 和字段尽量使用``括起来,避免和关键字冲突
-- AUTO_INCREMENT 表示自增
-- 在创建表的语句块内,所有的语句后面加上,最后一条可以不加
-- 在创建表的最后设置主键PRIMARY KEY(`id`)
-- 在括号后加上表的属性设置(引擎,默认编码,校对可选)
CREATE TABLE IF NOT EXISTS `studentdd` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学员id',
`name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`age` INT(3) NOT NULL COMMENT '年龄',
`password` VARCHAR(20) NOT NULL DEFAULT '123' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL 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
常用命令
SHOW CREATE DATABASE `school` -- 查看创建数据库的语句
SHOW CREATE TABLE studentdd -- 查看表的定义语句
DESC studentdd -- 显示表的结构
数据表的类型
MyISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 约为MyISAM的两倍 |
MyISAM:占用空间小,执行速度较快
INNODB:安全性高,支持事务,支持外键,多表多用户操作
引擎和表以文件的形式存储在硬盘里
InnoDB:*.frm文件+上级目录下的ibdata1文件
MyISAM:
- *.frm 表结构的定义文件
- *.MYD 存放表数据的文件
- *.MYI 存放表索引的文件
修改删除表
-- 修改表名
ALTER TABLE `studentdd` RENAME AS `student1`
-- 增加表的字段,后面可跟列属性
ALTER TABLE `student1` ADD ages INT(22) COMMENT'修改后的年龄'
-- 修改表的字段(change更名,更名的同时可以修改约束)
ALTER TABLE `student1` CHANGE ages age1 VARCHAR(3)
-- 修改表的字段(modify 修改约束)
ALTER TABLE `student1` MODIFY age1 INT(1)
-- 删除表的字段
ALTER TABLE `student1` DROP age1
-- 删除表
DROP TABLE IF EXISTS `student1`
所有的删除和创建操作都尽量加上判断操作IF EXISTS
,以免删除错误
字段名尽量都用反单引号``括起来,避免和关键字重复
注释 --、 #、 /**/
sql关键字大小写不敏感,建议都用小写
MySQL数据管理
外键
一般外键的约束名带前缀FK_
第一种创建外键的方法,(但一般不这么创建外键)
- 首先定义一个键key,key 键名 字段名
- 然后给这个键增加约束constraint(执行引用) 键名
KEY `FK_grandid` (`grandid`), -- 实际上这一行可以不需要
constraint `FK_gramdid` foreign key (`grandid`) references `grade`(`gradeid`)
删除带有外键引用关系的表的时候,必须先删除设立了外键的那个表,被引用的表才能够被删除
第二种方式(推荐使用这一种,在创建表之后对表的外键进行约束会更合理一些):
在创建表之后,对表添加外键约束
ALTER TABLE `student`
ADD CONSTRAINT `FK_grandid` FOREIGN KEY (`grandid`) REFERENCES `grand`(`grandid`);
以上两种方式都是数据库级别的外键,是物理外键,在前面提到,删除一张表的时候,要先去删除引用它的表,这会造成一些困扰比如当数据库表很多很多的时候,我们难以找到引用它的表。(所以一般不会这么去使用外键)
所以,推荐使用程序级别层面的外键:
- 数据库就是单纯用来存数据的,只有行和列
- 程序层面去实现外键的关联
在阿里巴巴开放规范中写道:
强制 不得使用数据库级别的外键与级联,所有外键必须在应用层面实现
记一点:只有主键才可以设置为自增
DML(重要)
数据库管理语言
增加
insert语句
-- 对表进行添加
-- insert into `表名` (字段1,字段2.....) values(值1,值2.....),(值1,值2....)。。。。。;
-- 如果不写字段名只写值的话,插入语句就会对表的所有字段一一对应,当出现类型不匹配的时候就会报错
INSERT INTO `grand` (`grandname`) VALUES('大四');
-- 插入多行数据
INSERT INTO `grand` (`grandname`) VALUES
('大一'),('大二');
记一个小点:如果主键设置为自增非空的话,在插入数据的时候可以不查入主键,它会自己填充一个数字
修改
update语句
-- 基本的修改语句 update `表名` set `需要修改的字段名`=新值 where 条件
-- 如果修改语句不加上条件,会把表中所有记录的相应字段都修改。如果没有开日志,数据库将不可恢复
UPDATE `student` SET `name`='333' WHERE id = 1;
-- 修改多个属性需要使用逗号隔开
UPDATE `student` SET `name`='222',`email`='22222@qq.com' WHERE id = 1;
UPDATE `student` SET `name`='222',`email`='22222@qq.com' WHERE id BETWEEN 2 AND 5;
-- 多条件定位
UPDATE `student` SET `name`='222',`email`='22222@qq.com' WHERE id = 1 AND `name` = '222';
where字句中的操作符:
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | 5=6 | false |
<>或 != | 不等于 | 5<>6或者5!=6 | true |
> | 大于 | ||
< | 小于 | ||
>= | 大于等于 | ||
<= | 小于等于 | ||
between … and … | 闭合区间 | [2,5] | |
and | 相当于&& | 5>1 and 1>2 | false |
or | 相当于 || | 5>1 or 1>2 | true |
在修改语句中,values既可以是一个值也可以是一个变量,例如current_time
删除
delete语句
-- 避免这样去删除数据库(以后有专门清除数据库的方法,不用delete)
-- delete from `表名` where 条件
DELETE FROM `student` WHERE `id` = 1;
truncate语句
truncate table `student`; -- 清空表数据
相同点和不同点:
- 相同点:都能删除数据,都不会删除表结构
- 不同:
- truncate会重新设置自增列,计数器归零
- truncate不会影响事务
DQL查询数据(重要)
所有的查询都用它 select语句
-- 查询表的所有数据
SELECT * FROM `student`;
-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`;
-- 为查询出来的数据表头起一个别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student`;
-- 函数concat() 拼接字符串
SELECT CONCAT('姓名',`studentname`) AS 拼接之后 FROM `student`;
列名不见名知意,可以使用as来为查出的数据表头起一个别名
去重
去重:distinct
-- 查询有哪些人参加了考试
-- distinct 去重 相同数据只展示一条
SELECT DISTINCT `studentno` FROM `result`;
-- 查询系统版本(函数)
SELECT VERSION();
SELECT 100*3-1 AS 计算结果; -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增步长(变量)
-- 如果将所有的成绩加1分之后显示
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM `result`;
数据库中的表达式:文本值、列,null,函数,计算表达式,系统变量
where条件子句
作用:检索数据中符合条件的值
搜索条件由一个或者多个表达式组成,结果是一个布尔值
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b a&&b | 逻辑与,两个都为真,结果才为真 |
or || | a or b a || b | 逻辑或,有一个为真,结果就为真 |
not ! | not a !a | 逻辑非,对结果取反 |
尽量使用英文而不使用符号
例子:
-- 模糊查询(区间)
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 95 AND 100;
-- 非逻辑测试
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000;
-- 非逻辑测试
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno` = 1000;
模糊查询
本质上就是对运算符的使用
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null,则结果为真 |
is not null | a is not null | 如果操作符不为null,则结果为真 |
between | c between a and b | 如果c在a和b之间,则结果为真 |
like | a like b | SQL匹配,如果a匹配b,则结果为真 |
in | a in(a1,a2,a3…) | 假设a在a1或者a2其中的某一个值中,结果为真 |
-- 模糊查询测试
-- 模糊查询中的一个重要的关键字like,
-- like可以结合%(代表0到任意个字符)和_(代表一个字符)来使用
-- like后边的字符必须带单引号
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘%';
-- in(这匹配的是多个具体的值,不可以和 % 或者_ 一起使用)
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IN('北京朝阳','广东');
-- is null is not null
SELECT `studentno`,`studentname` FROM `student`
WHERE `address` IS NULL OR address='';
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL;
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL;
联表查询
有三种连接:leftjoin(左连接),innerjoin(内连接),rightjoin(右链接)
网上有七种sql连接理论
-- 联表查询
/*
1. 分析需求:分析查询的字段来自哪些表(连接查询)
2. 确定使用那种连接?一共有7中,三种核心,其余扩展
确定交叉点(两个表中哪个数据是相同的)
判断条件
*/
-- 在某些情况下as为表起别名是可以省略as的用空格替代它
SELECT `student`.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student`
INNER JOIN `result`
WHERE `student`.`studentno` = `result`.`studentno`;
-- 按照交叉点在哪个区域来确定使用哪种连接
-- 在SQL92规范中使用where来连接表在SQL99规范中可以使用on来连接表
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno` = r.`studentno`;
-- 左连接
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`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`
-- 具体思路要查询哪些数据 select XXX....
-- 从哪些表中去查询数据 form 表1 XXX join 表2 on 交叉条件
-- 假设存在多表查询,首先从两张表开始,逐表增加
操作 | 描述 |
---|---|
inner join | 在两个表中至少有一个匹配就会返回行 |
left join | 会从左表中返回所有的值,即使右表中没有匹配 |
right join | 会从右表中返回所有的值,即使左表中没有匹配 |
这里主要是根据交叉点来进行查找的,如果选定的交叉点,在左表中使用到了,右表中使用到了,inner join就会返回,但如果这个交叉点只有右表使用到了,而左表未使用,则查不到该条数据,如果是right join则会查到该条数据但是相应的左表中的某些字段就会为null
自链接
自连接就是,自己的表和自己的表进行连接,核心就是把一张表拆成两张一样的表
-- 查询父子信息
SELECT a.`categoryname` AS 父栏目,b.`categoryname` AS 子栏目
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pid`
分页和排序:
排序:
-- 排序: 升序ASC 降序DESC
-- 基本排序语法 order by 需要排序的字段 顺序(ASC/DESC)
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC
分页:
分页的目的是缓解数据库压力,给用户更好的体验
-- 分页,每页只显示5条数据
-- 语法:limit 起始数据,页面大小
-- 一般可以得出公式:第n页 (n-1)pageSize,pageSize
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE `subjectname` = '数据库结构-1'
ORDER BY `studentresult` DESC
子查询:
在where中嵌套一个select查询语句
执行顺序是由里及外,可以嵌套
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `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`
WHERE `studentresult`>=80;
-- 高等数学分数不少于80分的学生姓名
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`='高等数学-2'
);
分组和过滤
-- 查询不同课程的平均分、最高分、最低分
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 平均分 >=80 -- 筛选平均分超过80的组
MySQL函数:
常用函数:
-- ======================== MySQL常用函数 =====================================
-- 数学运算
SELECT ABS(); -- 求参数的绝对值
SELECT CEILING(); -- 向上取整
SELECT FLOOR(); -- 向下取整
SELECT RAND(); -- 返回一个0-1的随机数
SELECT SIGN(); -- 负数返回-1,正数返回1,0返回0
-- 字符串函数
SELECT CHAR_LENGTH(); -- 查询字符串长度
SELECT CONCAT(); -- 拼接字符串
SELECT INSERT('我热爱编程',1,1,'超级'); -- 可以实现替换和插入,替换长度设置为0即为插入
SELECT LOWER(); -- 字符串转小写
SELECT UPPER(); -- 字符串转大写
SELECT INSTR(); -- 返回第一次出现子串的索引
SELECT REPLACE(); -- 替换出现的指定字符串
SELECT SUBSTR(); -- 返回指定的子字符串(源字符串,截取位置,截取长度)
SELECT REVERSE(); -- 反转字符串,回文
-- 时间和日期函数
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()
聚合函数:
函数名称 | 描述 |
---|---|
count() | 统计个数 |
sum() | 求和 |
avg() | 求平均 |
max | 最大值 |
min | 最小值 |
… | … |
-- ======================= 聚合函数 =============================
SELECT COUNT(`borndate`) FROM `student`; -- 按字段名统计,会忽略null值
SELECT COUNT(*) FROM `student`; -- 统计行数,不会忽略null值
SELECT COUNT(1) FROM `student`; -- 统计行数,不会忽略null值
数据库级别的md5加密:
调用MD5()函数即可
事务:
事务就是一组sql放在一起,要么都成功,要么都失败
事务原则: ACID原则 原子性,一致性,隔离性,持久性 在事务中可能产生一些脏读、幻读等等
原子性(Atomicity):
要么都成功,要么都失败
一致性(Consistency):
事务前后的数据完整性保证一致
隔离性(Isolation):
多个用户并发访问数据库的时候,数据库为每一个用户开启事务,不被其他事务操作数据干扰
持久性(Durability):
事务一旦提交则不可逆,被持久化到数据库中
隔离导致的一些问题:
脏读:
一个事务读取了另一个事务未提交的数据
不可重复读取:
读取表某一行数据,多次读取结果不同
幻读:
在一个事务内读取到了别的事物插入的数据,前后读取不一致
-- ============================== 事物 ===========================================
-- MySQL是默认开启事务自动提交的。
SET autocommit = 0; /*设置事务自动提交关闭*/
SET autocommit = 1; /*设置事务自动提交开启*/
-- 手动处理事务
SET autocommit = 0;
-- 开启事务
START TRANSACTION
INSERT xxx
INSERT xxx
-- 提交事务(成功则持久化)
COMMIT
-- 失败则回滚(回滚是将数据恢复成提交之前的样子)
ROLLBACK
-- 事务结束要重新开启自动提交,否则后面不能正常执行sql语句
SET autocommit = 1;
-- 事务可以回滚到一个设置好的保存点
SAVEPOINT 保存点名 -- 设置一个事务的保存点,事务可以回滚到此刻的数据
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 删除某个保存点
-- 开始模拟转账
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;
索引
索引用来帮助mysql高效获取数据的数据结果,索引本身就是数据结构的一种
- 主键索引(primary key)
- 唯一标识主键,主键不可重复,只能有一个列作为主键
- 唯一索引(unique key)
- 避免该字段中出现重复数据,可以创建多个唯一索引
- 常规索引(key / index)
- 普通的索引,就是用来查找数据的。可以用index或者key来设置
- 全文索引(FullText)
- 在早期版本innodb引擎是不支持全文索引的,但是在现在主流版本中innodb引擎支持了全文索引
- 快速定位数据
可以在创建表的时候创建索引,也可以在已经创建好的表中添加索引
mysql中写函数
-- 写一个插入100万条数据的函数
DELIMITER $$ -- 修改sql语句结束执行的标志
-- 创建函数
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),'123456@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 * FROM `app_user` WHERE `name`='用户99999'; -- 0.410 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户99999';
-- 为表创建索引
-- craete index(普通索引) id_表名_字段名 on 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM `app_user` WHERE `name`='用户99999'; -- 0.002 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户99999';
索引在大数据量的时候明显加快查询速度
索引原则:
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据的表不需要索引
- 索引一般加在经常查询的字段
权限管理和备份
用户管理
-- 创建用户
CREATE USER zhong IDENTIFIED BY '123456';
-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');
-- 修改指定用户密码
SET PASSWORD FOR zhong = PASSWORD('123456');
-- 重命名
RENAME USER zhong TO zhong2
-- 用户授权 ALL PRIVILEGES代表所有权限 on在哪些库哪些表上 to给哪个用户
-- ALL PRIVILEGES不能给别的用户授权
GRANT ALL PRIVILEGES ON *.* TO zhong2;
-- 查看授权
SHOW GRANTS FOR zhong2;
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM zhong2;
-- 删除用户
DROP USER zhong2;
MySQL备份
保证重要的数据不丢失
数据转移
备份的方式:
- 直接拷贝物理文件
- 在可视化工具中手动导出
- 使用命令行导出 mysqldump
# mysqldump -h 主机 -u 用户 -p 密码 数据库名 表名 >盘符:/文件名.sql
# mysqldump -h 主机 -u 用户 -p 密码 数据库名 表名1,表名2,表名3 >盘符:/文件名.sql
# mysqldump -h 主机 -u 用户 -p 密码 数据库名>盘符:/文件名.sql
C:\windows\system32>mysqldump -hlocalhost -uroot -p123456 shop account > D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 数据库导入
# 在登陆的情况下,切换到指定数据库使用source 盘符:/文件名.sql 即可
规范数据库设计
当数据库比较复杂的时候,良好的数据库设计可以避免很多问题的发生
一个糟糕的数据库设计:
- 数据冗余,浪费空间
- 插入删除会比较麻烦
- 程序性能差
一个良好的数据库设计:
- 节省空间
- 保证数据库的完整性
- 性能好,方便系统的开发
设计数据库:
- 需求分析
- 总体设计
- 详细设计
数据库三大范式
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
满足第一范式的前提下,消除对主键的部分函数依赖
第三范式(3NF):
满足第二范式的前提下,消除函数传递依赖
但是,规范化程度越高,性能损耗越大,所以有的时候不得不考虑一些逆规范化操作
比如:阿里巴巴开发规划中强制要求:关联查询的表不得超过三张
- 在商业化的需求和目标中,需要考虑成本以及用户体验,数据库的性能很重要
- 可以故意给一些表增加一些冗余字段(为了使表从多表查询变为单表查询)
- 故意增加一些计算列(代替索引的使用,节省存储空间,也提高了性能)
JDBC:
下载jdbc驱动包——mysql-connector-java-5.1.47.jar
在IDE中新建一个目录lib
在lib下放我们的jar包
将lib添加为库
连接数据库固定写法(通过映射):
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//加载驱动(固定写法)
Class.forName("com.mysql.jdbc.Driver");
//构建用户信息和url
//这里要换成你自己的数据库名
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String username = "root";
String password = "123456";
//连接数据库对象(拿到数据库))
Connection connection = DriverManager.getConnection(url, username, password);
//通过数据库创建一个可以执行sql语句的对象(但是statement是不安全的)主要是sql注入的问题
Statement statement = connection.createStatement();
//通过上面创建的对象来执行sql
String sql = "SELECT * FROM users";
//调用执行sql的语句成功会返回一个结果集
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"));
System.out.println("========================================");
}
//释放连接
resultset.close();
statement.close();
connection.close();
}
}
连接数据库基本上是固定写法,可以封装成工具类来执行sql语句。
封装工具类
package com.zhongzheng.jdbcUtils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class Utils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
InputStream in = Utils.class.getClassLoader().getSystemResourceAsStream("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 (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
使用properties文件来读取数据
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
在别的方法中直接调用:
package com.zhongzheng.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.zhongzheng.jdbcUtils.Utils;
public class Insert {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet re = null;
//连接数据库对象
try {
conn = Utils.getConnection();
//执行sql对象
st = conn.createStatement();
//执行sql
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)"
+ "VALUES(4,'wuming','123456','wuming@qq.com','2021-01-01');";
int hangShu = st.executeUpdate(sql);
if(hangShu>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
Utils.release(conn, st, re);
}
}
}
SQL注入问题:
SQL语句本质是拼接的字符串,不知道用户会传一个什么值过来。当用户传过来的值是一个SQL语句的时候,就可能存在数据泄露的风险
package com.zhongzheng.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.zhongzheng.jdbcUtils.Utils;
public class SQL注入 {
public static void main(String[] args) {
try {
//login(" ' or '1=1", "123456"); //SQL注入,可查询所有用户信息
login(" ' or '1=1' #", ""); //SQL注入,可查询所有用户信息
//login("zhangshan", "123456");// 正常登陆
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void login(String username, String password) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet re = null;
conn = Utils.getConnection();
st = conn.createStatement();
String sql = "select * from `users` where `name` = '" + username + "' and `password` = '" + password + "'";
re = st.executeQuery(sql);
while(re.next()) {
System.out.println(re.getString("name"));
System.out.println(re.getString("password"));
}
Utils.release(conn, st, re);
}
}
PreparedStatement:
插入:
package com.zhongzheng.jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.zhongzheng.jdbcUtils.Utils;
public class TestInserte {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
try {
conn = Utils.getConnection();
//预编译的Statemen对象就是使用?占位符来代替变量的
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?);";
st = conn.prepareStatement(sql);//需要先写sql,才能被执行
st.setInt(1, 4);
st.setString(2, "zhong");
st.setString(3, "123456");
st.setString(4, "992683167@qq.com");
// new java.util.Date().getTime() 获得时间戳
//new Date(XXX) 把Java时间包装成一个sql能使用的时间
st.setDate(5, new Date(new java.util.Date().getTime()));
//和statement对象的区别就是当statement对象执行的时候需要传入sql语句作为参数
//预编译的statement对象可以直接调用执行,不需要传入参数,因为之前生成预编译的statement对象的时候已经传递过sql参数了
int hangShu = st.executeUpdate();
if(hangShu>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
Utils.release(conn, st, null);
}
}
}
防止SQL注入:
package com.zhongzheng.jdbc;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.zhongzheng.jdbcUtils.Utils;
public class Test防止SQL注入 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet re = null;
try {
conn = Utils.getConnection();
String sql = "select * from `users` where `name` = ? and `password` = ?";
st = conn.prepareStatement(sql);
//prepareStatement的本质就是将用户传递进来的参数当作字符处理。整体外面包裹引号,并且将里面的类似单引号'之类的字符转义
st.setString(1, " ' or '1=1");
st.setString(2, "123456");
re = st.executeQuery();
while(re.next()) {
System.out.println(re.getString("name"));
System.out.println(re.getString("password"));
System.out.println("===================");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
Utils.release(conn, st, re);
}
}
}
使用IDEA连接数据库
打开IDEA之后找到右边的侧边栏
依次填入
最后点击一下测试,如果Successful则能成功连接到数据库,点下方apply即可
必须导入jdbc驱动并且添加进lib目录才可以成功
点击选项卡Schemas,进入如下页面,选中你需要导入的数据库点击apply即可
双击右侧显示的数据库或者表,即可查看数据库或者表内容
在显示出来的页面修改表内容之后需要点击上方栏目内的提交按钮才能提交到数据库进行修改
点击侧边栏中的sql编辑器即可打开一个sql编辑器用来直接写sql代码,可以新建一个,也可以使用默认
事务:
- 开启事务
conn.setAutoCommit(false);
,关闭自动提交即开启事务 - 一组业务执行完毕之后提交事务,
commite
- 可以在catch中显示的定义rollback语句,但程序默认开启回滚
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.jdbcUtils.Utils;
public class PrintHello {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet re = null;
try {
conn = Utils.getConnection();
// 在数据库中关闭自动提交之后需要手动开启事务,但是在Java程序中关闭自动提交之后,事务自动开启,不需要手动去开启
conn.setAutoCommit(false);
String sql1 = "update `account` set `money` = `money` - 100 where `name` = 'A'";
pst = conn.prepareStatement(sql1);
pst.executeUpdate();
// int x = 1/0;//让事务失败测试回滚
String sql2 = "update `account` set `money` = `money` + 100 where `name` = 'B'";
pst = conn.prepareStatement(sql2);
pst.executeUpdate();
conn.commit();//提交事务
System.out.println("事务成功");
} catch (SQLException throwables) {
throwables.printStackTrace();
try {
conn.rollback();
System.out.println("事务失败,回滚成功");
} catch (SQLException e) {
e.printStackTrace();
}
}finally {
Utils.release(conn, pst, re);
}
}
}
数据库连接池:
在我们上面使用的获得连接,执行sql,释放资源。在这个过程中,获得连接和释放资源非常消耗计算机的资源。并且获得连接和释放资源都需要一定的执行速度。使用提出了一个池化技术,即预先开启几个连线,但不释放资源,有sql语句来就执行,否则等待。
常用连接数:一般指连接池里面,活跃的连接。
最小连接数:同上
最大连接数:当连接数大于常用连接数时,连接池可能会开启一些备用的连接来满足程序需求。一共使用的连接数就是最大连接数。超过最大连接数的执行请求排队等待
等待超时:当队列中的请求超过我们设定的某个时间的时候,我们会让超时的请求断开连接即从等待队列中移除
超时:空闲的连接,超过设定好的时间没有执行请求就把它释放掉
编写连接池,需要实现一个接口——DataSource
我们可以使用一些开源数据源当作连接池
DBCP:
需要的jar包:Commons-dbcp-1.4、commons-pool-1.6
需要设置properties文件
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
封装utils类
package com.zhongzheng.jdbcUtils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
static {
try {
InputStream in = Utils.class.getClassLoader().getSystemResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 (工厂模式)
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试连接
package com.zhongzheng.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.zhongzheng.jdbcUtils.JdbcUtils_DBCP;
import com.zhongzheng.jdbcUtils.Utils;
public class TestDBCP {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet re = null;
//连接数据库对象
try {
conn = JdbcUtils_DBCP.getConnection();
//执行sql对象
st = conn.createStatement();
//执行sql
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)"
+ "VALUES(4,'wuming','123456','wuming@qq.com','2021-01-01');";
int hangShu = st.executeUpdate(sql);
if(hangShu>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
JdbcUtils_DBCP.release(conn, st, re);
}
}
}
C3P0
需要的jar包:c3p0-0.9.5.5、mchange-commons-java-0.2.19
需要设置xml配置文件,而且和DBCP相比,C3P0使用日志
<?xml version="1.0" encoding="GBK"?>
<c3p0-config>
<!--
c3p0的缺省(默认)配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource();"这样写就表示使用的是c3p0的缺省(默认)
-->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false</property>
<property name="user">root</property>
<property name="password">root</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!--
c3p0的命名配置
如果在代码中"ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");"这样写就表示使用的是mysql的缺省(默认)
-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="acquiredIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">20</property>
</named-config>
</c3p0-config>
封装utils类
package com.zhongzheng.jdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
try {
//C3P0使用xml配置文件,所以不需要我们手动读取文件
//创建数据源 (工厂模式)
dataSource = new ComboPooledDataSource("MySQL");
//也可以不使用xml文件里的配置,在代码中直接配置(一般不这么使用)
// dataSource = new ComboPooledDataSource();
// //配置数据库连接
// dataSource.setDriverClass(null);
// dataSource.setUser(null);
// dataSource.setPassword(null);
// dataSource.setJdbcUrl(null);
//
// dataSource.setMaxPoolSize(0);
// dataSource.setMinPoolSize(0);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放连接
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试连接
package com.zhongzheng.jdbc;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.zhongzheng.jdbcUtils.JdbcUtils_C3P0;
public class TestC3P0 {
public static void main(String[] args) {
Connection conn = null;
Statement st = null;
ResultSet re = null;
//连接数据库对象
try {
conn = JdbcUtils_C3P0.getConnection();
//执行sql对象
st = conn.createStatement();
//执行sql
String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)"
+ "VALUES(5,'wuming','123456','wuming@qq.com','2021-01-01');";
int hangShu = st.executeUpdate(sql);
if(hangShu>0) {
System.out.println("插入成功");
}else {
System.out.println("插入失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//释放连接
JdbcUtils_C3P0.release(conn, st, re);
}
}
}