mysql
数据库引擎
-
innodb(默认使用)
-
myisam(早些年使用的)
| innodb | myisam | |
|---|---|---|
| 事务支持（跟spring遇到的是一个意思） | 支持 | 不支持 |
| 数据行锁定 | 支持 | 不支持 |
| 外键约束 | 支持 | 不支持 |
| 全文索引 | 不支持 | 支持 |
| 表空间大小 | 较大，约为myisam的两倍 | 较小 |
-
myisam 节约空间，速度较快
-
innodb 安全，事务的处理，多表多用户操作
-
共同点 存储位置，数据库文件都在data目录下
在文件上面的区别
-
innodb 在数据库对应文件夹中只有一个.frm文件，以及上级目录下的ibdata1文件
-
myisam 对应位置有.frm文件（表结构），.myd文件（数据文件），.myi文件（索引文件）
ps：跟在设置数据库引擎后的DEFAULT charset=8是设置表的默认字符集编码，不设置的话是mysql的默认字符集编码（Latin1，不支持中文），my.ini可以设置默认的编码：character-set-server=utf8（不建议）
-
新学的命令
show create database xxx
查看创建数据库的语句
show create table xxx
查看某个数据表的语句
desc xxx
显示表的结构
复习
查询(important)
select [all | distinct]
{* | table.* | [table.field1[as 别名1],table.field2[as 别名2],...],...}
from table [as 表别名]
[left | right | inner join table2 on 条件] --联合查询
[where ...] --结果需要满足的条件
[group by ...] --结果按照哪些字段分组
[having ...] --group by需要满足的次要条件
[order by ...] --结果按照条件(一般是一些字段)排序
[limit {[offset,]row_count | row_count_offset offset}]; --指定结果从哪条到哪条
ps：[]代表可选，{}代表必选，where和on的结果差不多，可以写一个也可以两个都写
- 指定了INNER关键字的连接是内连接，内连接是系统默认的，可以省略INNER关键字。使用内连接后仍可使用WHERE子句指定条件。
- left和right都是外连接，决定了查询结果中哪个表里的值为null
- 干货
- inner join使用on或where结果是一样的，区别就是join on是连接查询（利于阅读），where是等值查询。where与join两种方式
- left/right join用on或where的区别：区别
- on 条件是在生成临时表时使用的条件，where 条件是在临时表生成好后，再对临时表进行过滤的条件（使用where之后就没有 left join 的含义了，即必须返回左边表的记录），条件不为真的就全部过滤掉。
- 自连接（了解即可），一张表拆为两张一样的表即可
记一个解题思路：如果查询多张表，先查询两张表，然后增加（增加join on语句）
数据库
unsigned（非负的意思）：
- 无符号的整数
- 声明了该列不能为负数
zerofill：
- 0填充
- 不足的位数使用0填充。int(3) 5 -> 005
null和not null
- not null 不赋值就会报错
- null 不填写值，默认为null
default
- 默认值 sex default ‘男’
create database xxx character set utf8 collate utf _8_general_ci --创建数据库，很多时候都是用的navicat创建的，这个是设置字符集的语句
- collate 表示核对
create table `xxx` (
…
)engine = innodb default charset = utf8
- 设置字符集时与创建数据库时的语句稍有不一样
聚合函数(important)
-
COUNT()
计数
- COUNT(指定列字段名) 根据列名计数,会忽略所有的null值
- COUNT(*) 包含所有列，不会忽略列值为null的
- COUNT(1) 忽略所有的列，用1代表代码行，不会忽略null值
-
SUM(‘列名’)
对某个列的值总和
其他，略
数据表
-
修改表名
alter table 原名 rename as 新名 -
增加字段
alter table teacher add age int(11) -
修改表的字段(重命名，修改约束)
修改约束，原来是int，现在改成varchar
alter table teacher modify age varchar(11)字段重命名
alter table teacher change age age2 int(1) -
删除表的字段
alter table teacher drop age2 -
删除表
drop table teacherif exists ：如果表存在再删除，避免报错（若没有该表上面的语句会报错，下面的就不会）
drop table if exists teacher
ps：
- 表名称和字段名尽量用``包裹，为了安全，防止出错（防止被误认为是关键字）
表数据
-
删除表数据(delete)
delete from 表名 where条件
delete from `student` where id = 1 -
专业清空表数据(truncate)
完全清空表，表结构和索引不会变,不建议使用delete清空
truncate `student`与delete相比：
- 相同点 完全清空表，表结构不会变
- 区别 truncate会重新设置自增列（从零开始），不会影响事务；delete删除后接着自增（不归零），但是如果数据库引擎是innodb，net start mysql之后，自增列会归零（因为是存在内存当中的，断电即失）
jdbc
一段感兴趣的代码
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
--
Properties properties = new Properties();
properties.load(in);
String driver = properties.getProperty("driver");
ps：若此处配置数据库连接池的是xml文件，则不需要读取代码
感兴趣的一个区别：
java.sql.Date 数据库的；使用 ：java.sql.Date(new Date().getTime())；
util.Date Java的 new Date().getTime() 获得时间戳
statement对象，两个方法
- executeQuery()执行查询语句，返回ResultSet对象
- executeUpdate()执行增删改语句，返回整数
sql注入问题（拼接字符串，针对statement对象；而PreparedStatement对象就可以防止sql注入）
意思是web应用程序对用户输入数据的合法性没有判断或者过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾添加额外的sqsl语句，以此来实现欺骗数据库服务器执行非授权的任意查询(把所有的用户名密码都查出来)，从而进一步得到相应的信息，上文参考百度百科，即sql存在漏洞，会被攻击，导致数据泄露。输入的时候sql被拼接 or
select * from users where `name` = '' or '1=1' and `password` = '' or '1=1'
--' or '1=1是拼接上去的;' or '1=1也是拼接上去的
PreparedStatement对象（防止sql注入）
原理：预编译会在参数外面加引号并且过滤特殊字符(转义字符)，原来的注入语句就会变成下面这样：
where name = '\'test\' or 1=1' --mysql的SQL文日志
自然不会被注入
略（与statement对象类似，executeQuery()执行查询语句，executeUpdate()执行增删改语句）
数据库连接池
数据库连接的连接和释放是十分浪费系统资源的
池化技术：预先准备一些资源，使用预先准备好的
-
最小连接数：10 根据常用的来
-
最大连接数：15 最高业务承载上限 超过15就排队等待
-
等待超时：100ms 直接断掉
-
…
编写连接池需要实现一个接口：DataSource
开源的实现类
- DBCP
- C3P0
- Druid
使用不同的数据源导入不同的包就行
常用函数（不包括聚合函数）
数学函数
-
ABS()
绝对值
-
CEILING()
向上取整
-
FLOOR()
向下取整
-
RAND()
随机数，0~1之间的随机数
-
SIGN
判断一个数的符号，0返回0，负数返回-1，正数返回1
字符串函数
-
CHAR_LENGTH()
字符串长度
-
CONCAT(‘啦’,‘啦’,‘啦’)
合并字符串
-
INSERT()
在指定位置插入字符串，也可以有替换的功能
-
LOWER()、UPPER()
转换为小、大写
-
INSTR(‘yyh’,‘h’)
返回第一次出现的子串索引
-
REPLACE(‘hhhlll’,‘hl’,‘yyh’)
替换出现的指定字符串,hl -> yyh
-
SUBSTR(‘hhhlll’,4,6)
返回指定的子字符串，4开始，截取6个
-
REVERSE(‘hhhlll’)
字符串反转
时间和日期函数 这个个人用得还比较多
-
CURRENT_DATE()/CURDATE()
当前日期
-
NOW()
当前时间，包含时分秒
-
LOCALTIME()
本地时间
-
SYSDATE()
返回函数执行时间，系统时间
-
YEAR/MONTH/DAY/HOUR/MINUTE/SECOND()
不用多说
系统
-
SYSTEM_USER()/USER()
当前用户
-
VERSION()
版本
数据库的MD5加密（即MD5()，一个函数）
MD5：MD5叫信息摘要算法，MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于**普通数据的加密保护领域 ** ，1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。
应用：
- 密码管理 可以将任意长度的输入串经过计算得到固定长度的输出，而且只有在明文相同的情况下，才能等到相同的密文，这样就可以把用户的密码以MD5值的方式保存起来，用户注册的时候，系统是把用户输入的密码计算成 MD5 值，然后再去和系统中保存的 MD5 值进行比较，如果密文相同，就可以认定密码是正确的，否则密码错误。
- 电子签名 MD5 算法还可以作为一种电子签名的方法来使用，使用 MD5算法就可以为任何文件（不管其大小、格式、数量）产生一个独一无二的“数字指纹”，借助这个“数字指纹”，通过检查文件前后 MD5 值是否发生了改变，就可以知道源文件是否被改动。
- 垃圾邮件筛选 在电子邮件使用越来越普遍的情况下，可以利用 MD5 算法在邮件接收服务器上进行垃圾邮件的筛选，以减少此类邮件的干扰。
总结：MD5不可逆，只有在明文相同的情况下，才能等到相同的密文
事务(只看加粗的就行)
ACID原则：原子性，一致性，隔离性，持久性
事务就是多条sql，要么都ok，要么都回滚
产生一些问题：脏读，幻读
-
原子性
a：800元，给b：200元，转账200两个步骤
800-200
200+200
这两个步骤(sql)要么都ok，要么都回滚
-
一致性
最终一致性：a和b的总价值一定是1000元（事务前后的数据要一致）
-
持久性
事务一旦提交就不可逆
事务结束后数据不随着外界原因导致数据丢失
操作前：a 800，b 200
操作后：a 600，b 400
若在操作前（事务还没有提交），服务器宕机或断电，那么重启数据库后，应该是：a 800，b 200（恢复到原状）
若在操作后（事务已经提交），服务器宕机或断电，那么重启数据库后，应该是：a 600，b 400（事务一旦提交就不可逆）
-
隔离性
排除其他事务对本次事务的影响
如：a(800)给b(200)转200，c(1000)给b(200)转100,多个用户操作有个隔离的关系
隔离失败就会有问题：
- 脏读 一个事务读取了另一个事务未提交的数据
- 不可重复读(不一定是错误，可能是场合不对) 在一个事务内读取表中的某一行数据，多次读取结果不同。比如在第二次读取的时候b有人转账进来300
- 虚读(幻读) 在一个事务内读取到了别的事务插入的数据（一般是行影响，多了一行）
mysql是默认开启事务自动提交的。
关闭：
set autocommit = 0
开启(默认)：
set autocommit = 1
手动处理事务
先关闭前面的自动提交，然后下面
-
事务开启
start transaction从这之后的sql都在同一个事务内
sql语句; sql语句; sql语句; -
提交（ok，不可逆，也就是持久化）
commit; -
回滚（fail，回到原来的样子）
rollback; -
事务结束（开启自动提交）
set autocommit = 1;
ps：整个过程中可以设置保存点（了解）：
savepoint 保存点名 --设置保存点
rollback to savepoint 保存点名 --回滚到保存点
release savepoint 保存点名 --撤销保存点
索引
官方原话：
索引（index）是帮助mysql高效获取数据的数据结构。 0.5s -> 0.000001s（大大的节省了时间）
索引是数据结构，对于大量的数据正常查询可能要查询好多条记录，索引只需要一条，非常快
-
分类
-
主键索引 primary key navicat中索引选项中不显示主键索引
唯一的标识，主键不可重复
-
唯一索引 unique key
避免重复的列出现（两列叫同一个名字肯定是不行的）
-
常规索引 key/index
默认的（不写就有）
-
全文索引 fulltext
在特定的数据库引擎才有（以前是myisam才有），快速定位数据。
现在应该都有了，不只是myisam
ps：主键索引只能有一个，唯一索引可以有多个（多个列可以标识为唯一索引，列名可以不同）
-
-
命令
- 显示表的索引信息
show index from xxx-
增加一个全文索引(改表的语句)
alter table `表名` add fulltext index `索引名`(`字段名`);索引名：给索引起名字
ps：还有两种创建索引的方式，一个是建表的时候，另一个如下(常规索引)
create index 索引名 on `表名`('字段名'); --索引名：id_表名_字段名 -
分析sql执行的状况 explain
explain select * from `表名`; --非全文索引，会查很多行explain select * from `表名` where match(`字段名`) against('字符串'); --全文索引的使用，只查了一行，数据量比较少会没有用 -
写函数之前必须的标志(mysql的sql编程，了解)
delimiter $$ --写函数之前必须的标志 create function mock_data() returns int begin --开始写函数体 end;
-
索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
权限管理
sql命令操作（navicat，略）
系统用户表：mysql下的user表(在此表增删改)
有三个默认用户：root、mysql.session、mysql.sys
-
创建用户
create user yyh identified by '密码' -
修改密码(当前用户)
set password = password('新密码') -
修改密码(指定用户)
set password for yyh = password('新密码') -
给用户重命名
rename user yyh to yyh2 -
用户授权(all privileges，全部权限，除了给别人授权的权限；库.表)
grant all privileges on *.* to yyh2 --全部的库全部的表，对所有表都有最高的权限 -
查看权限
show grants for yyh2 --查看指定用户的show grants for root@localhost --查看root用户的 -
撤销权限(跟授权一样)
revoke all privileges on *.* from yyh2 -
删除用户
drop user yyh2
数据库备份
原因：
- 数据不丢失
- 数据转移
方式：
-
直接拷贝文件（data下的数据库文件）
-
navicat可以导出（这个俺以前经常干，略）
-
命令
-
导出一张表(mysqldump -h主机 -u用户名 -p密码 数据库 数据表 >导出位置)
mysqldump -hlocalhost -uroot -p666 ssm users >D:/1.sql ---h表示host的意思 -
导出多张表(mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >导出位置)
mysqldump -hlocalhost -uroot -p666 ssm users traveller role >D:/2.sql ---h表示host的意思 -
导出数据库(mysqldump -h主机 -u用户名 -p密码 数据库 >导出位置)
mysqldump -hlocalhost -uroot -p666 ssm >D:/3.sql ---h表示host的意思 -
导入数据表(use database之后)
source d:/1.sql若没有登录，则
``mysql -u用户名 -p密码 库名< 备份文件`
-
294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
