MySQL学习笔记3

5、SQL函数

5.1常用函数

数学运算符
select abs(-8) 绝对值
select ceiling(9.4) 向上取整
select floor(9.4)向下取整
select rand() 返回一个0~1之间的随机数
select sign(10) 判断一个数的符号，0返回0，负数返回-1，正数返回1

字符串函数
select char_length(‘你好’) 字符串长度
select concat(‘大家’,‘好’)拼接字符串
select insert(‘我爱编程’,1,2,‘超级热爱’)从第几个位置开始替换某个长度
select lower(‘Test’)全小写
select upper(‘Test’)全大写
select instr(‘Test’,‘e’)返回第一次出现的子串索引
select replace(‘happyEveryDay’,‘Day’,‘Month’)替换出现的指定字符串
select substr(‘test’,2,2)返回指定的子字符串，源字符串，截取位置，截取长度
select reverse(‘test’)反转

时间和日期函数
select current_date()获取当前日期
select curdate()获取当前日期
select now()获取当前时间
select localtime()获取本地时间
select sysdate()获取系统时间
select year(now())
select month(now())
select hour(now())

系统
select system_user()
select user()
select version()

5.2聚合函数

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

统计表中的数据
select count(id) from student count(字段)会忽略所有的null值
select count(*) from student count(×)不会忽略null值，本质是计算行
select count(1) from student 不会忽略null值，本质是计算行
select sum(scoreNum) from score
select max(scoreNum) from score
select min(scoreNum) from score

5.2.1 count(1)和count(*)的区别

若数据表没有主键，count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(✖)要快

5.3数据库级别的MD5加密

insert into user(1,‘happy’,‘12345’)
加密
update user set pass=md5(pass) where id=1
插入时加密
insert into user(1,‘happy’,md5(‘12345’))
校验方式，将用户传递的密码，进行md5加密，然后比对加密后的值
select * from user where name=‘happy’ and pass=md5(‘12345’)

6.事务

6.1什么是事务

要么都成功，要么都失败
1 a给b转账 a 1000 -->200 b 200
2 b收到a的转账 a 800–> b 400
将一组sql放到一个批次去执行

事务的原则：ACID原则 原子性，一致性，隔离性，持久性(脏读，幻读，……)
原子性(atomicity)：要么都成功 要么都失败；一个事务的所有系列操作步骤被视为一个动作，所有的步骤要么全部都完成，要么一个都不完成，如果事务的过程中发生错误，就会回滚到事务发生前的状态，将要被改变的数据库记录不会改变
一致性(consistency)：数据前后的完整性要保持一致
隔离性(isolation)：事务的隔离性是指多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离，保证并发的事务按顺序，一个一个的完成
持久性(durability)事务提交
事务一旦被提交则不可逆，被持久化到数据库中
隔离导致的一些问题
脏读：一个事务读取了另一个事务未提交的数据
不可重复读：在一个事务内读取了表中的某一行数据，多次读取结果不同(这个不一定是错误，只是某些场合不对)
虚读(幻读)：
是指在一个事务中读取到了别的事务插入的数据，导致前后读取不一致
参考博客：https://www.cnblogs.com/shan-kylin/p/9543294.html
mysql默认开启事务自动提交
set autocommit=0 关闭
set autocommit=1 开启

手动处理事务例子
set autocommit=0关闭自动提交事务
开启事务
start transaction 标记一个事务的开启，才这个之后的sql都在同一个事务中
insert xx
insert xx
commit 提交，持久化
rollback 回滚(提交成功后，则不能回滚)
set autocommit=1 开启自动提交
转账例子
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;恢复默认值

savepoint 保存点名 设置一个事务保存点
rollback savepoint 保存点名 回滚到保存点
release savepoint 保存点名 撤销保存点

7.索引

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。

7.1索引的分类

在一个表中主键索引只能有一个，唯一索引可以有多个
主键索引(primary key)
唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引(unique key)
避免重复的列值出现，多个列都可以标识为唯一索引
常规索引(key/index)
默认的，index,key关键字来设置
全文索引(fulltext)
在特定的数据库引擎下才有，myisam
快速定位数据
基础语法
索引的使用
1、创建表的时候给字段加索引
2、创建完毕后，添加索引
显示所有的索引信息
show index from student
增加一个全文索引 索引名(列名)
alter table student add fulltext index in_name(name)

explain分析sql执行情况
explain select * from student
create index 索引名 on 表(字段名)
索引在小数据量的时候用处不大，但在大数据量的时候区别十分明显

7.2索引原则

索引不是越多越好
不要对经常变动数据加索引
小数据量的表不需要加索引
索引的数据结构
Hash类型的索引
btree：innodb的默认数据结构
参考博客：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

8.权限管理和备份

8.1用户管理

本质是对表进行增删改查
创建用户 create user 用户名 identified by ‘密码’
create user test identified by ‘111111’
修改密码，修改当前用户密码
set password=password(‘111111’)
修改密码，修改指定用户
set password for test =password(‘111111’)
重命名 rename user 旧名字 to 新名字
rename user test to test1
用户授权 all privileges 全部的权限
grant all privileges on *.星号 to test1 库.表
查询权限
show grants for test1 查看指定用户的权限
show grants for root@localhost
撤销权限
revoke all privileges on 星号.星号 from test1
删除用户
drop user test1

8.2MySQL备份

为什么要进行备份
保证重要的数据不丢失
数据转移
MySQL数据库备份方式

• 直接拷贝物理文件
• 在sqlyog这样的可视化工具中导出
• 使用命令行导出mysqldump 命令行使用
  mysqldump命令行使用
  mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 磁盘物理位置/文件名
  mysqldump -hlocalhost -uroot -p111111 school student >E:/a.sql
  导入，登录的情况下
  source 备份文件
  source e:/a.sql
  未登录情况下
  mysql -u用户名 -p密码 数据库名<备份文件

9.规范数据库设计

9.1为什么要设计

当数据库比较复杂的时候，我们就需要设计
糟糕的数据库设计：
数据冗余，浪费空间
数据插入和删除都会比较麻烦，容易出现异常(屏蔽使用物理外键)
程序的性能差
良好的数据库设计：
节省内存空间
保证数据库的完整性
方便我们开发系统
软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库需求
• 概要设计：设计关系图，E-R图
  为什么需要数据规范化
  避免信息重复、更新异常、插入异常、删除异常等
  三大范式
  第一方式(1NF)：
  原子性，保证每一列不可再分
  第二范式(2NF)：
  如果关系模式R满足第一范式，并且所有的非主属性完全依赖于关系模式R的每一个候选键，称R满足第二范式；如，关系模式中的主键是联合主键(AB)，通过联合主键可以定位到唯一一条记录，通过联合主键的子集(A或B)却无法得到
  第三范式(3NF)：
  关系模式R满足第二范式，X是R的子集，如果X非传递依赖于任意一个R的候选关键字，则称R满足第三范式
  传递依赖：A–>B，B–>C C传递依赖于A

规范性和性能问题

• 关联查询的表不得超过3张
• 考虑商业化的需求和目标，（成本体验，用户体验）数据库的性能更加重要
• 在考虑性能问题的同时，适当考虑一下规范性
• 故意给一些表增加一些冗余字段(多表查询变未单表查询)
• 故意增加一些计算列(从大数据量变为小数据量的查询：索引)

10.JDBC

10.1JDBC

我们的程序主要通过数据库驱动来和数据库进行连接
sun公司为了简化开发人员的(对数据库的统一)操作，提供了一个java操作数据库的规范，俗称JDBC，这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC接口的操作即可
需要导入数据库驱动包 mysql-connector-java-具体版本.jar