mysql学习笔记

1、数据库名称不能修改

2、查看表结构：description/desc 表名

3、修改字符集
修改配置文件：character_set_server = utf8
修改数据库字符集：alter database 数据库名 character set utf8;
修改表字符集：ALTER TABLE  表名 DEFAULT CHARACTER SET utf8（字符集） COLLATE utf8_general_ci（排序规则）;

查看字符集编码：show VARIABLES like 'character%';

4、SET NAMES 'utf8';  
相当于下面的三句指令：
SET character_set_client = utf8;  
SET character_set_results = utf8;   
SET character_set_connection = utf8;

数据乱码：
（1）client声明和实际编码不符
（2）results声明和客户端编码不符
数据丢失：
（1）connection和服务器的字符集比client小

字符集：具有继承关系
服务器：数据库：表：列：

5、values后面可以跟多条记录值

6、tinyint：默认是有符号的，范围为（-128,127）
tinyint（M）unsigned zerofill
M：在zerofill时才有意义，固定宽度，显示效果
unsigned：无符号（0,255）
zerofill：零填充（默认无符号）

7、浮点型float（M，D）只有mysql可以指定D
M：总位数
D：小数位数（超出部分四舍五入（银行规则））

8、定点型decimal（M，D）——更精确

9、char（M）/varchar（M）
M：表示字符数
char（M）：尾部空格补齐，范围（0,255），若尾部实际有空格则丢失
varchar（M）：
text：不能全文索引，没有默认值

10、日期时间
year：1个字节，范围（1901,2155），建议输入4位完整年份
插入2位字符串，如果插入‘00’~‘69’，则相当于插入2000~2069；
如果插入‘70’~‘99’，则相当于插入1970~1999

date：取值范围为“1000-01-01——9999-12-31”

time：取值范围为“-838:59:59——838:59:59”

datetime：取值范围为“1000-01-01 00:00:00——9999-12-31 23:59:59”

注意：实际开发中，由于时间可能需要计算的原因，一般使用时间戳（int）
时间戳：1970-01-01 00:00:00 到当前时间的秒数

11、count（*）：无论*是什么，计算的都是符合where条件的记录数

12、子查询类型
where：把内层查询的结果作为外层查询的条件
from：把内层查询的结果当成临时表供外层查询
exists：把外层的查询结果拿到内层，看内层的查询是否成立

13、union：两次查询的列数一致，去重复
当列名不一致时，以第一个sql的列名为准
查询中有order by或limit时，需要加（）
使用order by排序时，需要配合limit，否则排序不起作用

14、左连接：以左表为准，根据条件去右表匹配，找不到用null补齐
右连接：原理同上，推荐改为左连接
内连接：为左/右连接的交集，查询左右都存在的数据，去除为null的数据
外连接：mysql不支持，可以用union实现，为左/右连接的并集

15、增加列：默认添加到表的最后列
放到任意列后：alter table 表名 add 列声明  after 已存在的列名
放到第一列：alter table 表名 add 列声明 first

修改列：alter table 表名 change 被修改的列名 新的列声明

删除列：alter table 表名 drop 列名

16、视图：由查询结果构成的一个虚拟表
create view 视图名 as select语句
视图的好处：
（1）简化查询：查询结果多次使用
（2）权限控制：可以只开放表的部分列数据
（3）大数据分表：把分表合成一个视图
视图和表的关系：
（1）表数据改变，视图数据跟着变
（2）视图并不是都可以增删改的，可以增删改时也会影响表
（3）视图和表中的数据一一对应时，视图可以增删改
（4）当视图insert时，视图必须包含表中所有没有默认值的列
视图的algorithm：默认merge
（1）merge：当引用视图时，引用视图的语句和创建视图的语句合并成一个查询语句，去表中查询
（2）temptable：当引用视图时，根据视图的创建语句建立一个临时表，引用视图的语句查询临时表
（3）undefined：系统自动选择
改变视图的algorithm：
create algorithm=temptable view 视图名 as select语句

17、触发器trigger
四要素：
监视地点：table
监视事件：insert/update/delete
触发时间：before/after
触发事件：insert/update/delete

create trigger 触发器名称 
after/before insert/update/delete on 表名 [for each row（行级触发器）]
begin 
  sql语句
end

触发器中引用行的值：
insert：新增的行用new表示，行中的列值用new.列名表示
delete：删除的行用old表示，行中的列值用old.列名表示
update：修改前的行用old表示，行中的列值用old.列名表示,
修改后的行用new表示，行中的列值用new.列名表示

18、delimiter 结束符：自定义语句结束符

19、存储引擎（engine=innodb）：数据存储方式
Myisam：速度快，不支持事务
InnoDB：安全性高，支持事务

20、事务：一组操作的集合，要么都执行，要么都不执行（原子性）
特性：原子性、一致性、隔离性、持久性（ACID）

start transaction;
操作 //事务日志文件
commit/rollback;//事务的隐式提交

21、数据库备份与恢复
系统自带的备份工具：mysqldump，可以导出库、表
（1）导出库中的表
mysqldump -u用户名 -p密码 库名 表1 表2 ... >备份文件地址
导出的是建表语句和insert语句
（2）导出库中的所有表（即导出整个库）
mysqldump -u用户名 -p密码 库名 >备份文件地址
导出的是所有表的建表语句和insert语句，没有建库语句
（3）以库为单位导出库
mysqldump -u用户名 -p密码 -B 库1 库2 ... >备份文件地址
导出的是库中所有表的建表语句和insert语句，有建库语句
（4）导出所有库
mysqldump -u用户名 -p密码 -A >备份文件地址

恢复：
（1）登录到mysql命令行
1、恢复库级的备份文件
source 库级备份文件地址;
2、恢复表级的备份文件
use 库名;
source 表级备份文件地址
（2）不登录到mysql命令行
1、恢复库级的备份文件
mysql -u用户名 -p密码 < 库级备份文件地址
2、恢复表级的备份文件
mysql -u用户名 -p密码 库名 < 表级备份文件地址

22、索引：数据的目录
不用索引，平均查找N/2次
二叉树索引，平均查找log2N次
hash索引，平均查找1次（散列值不连续、可能重复）

好处：加快查询速度
坏处：降低增删改速度，索引文件需要占用空间

注意：当表的索引过多，需要换服务器导数据时
应先删除索引，再导数据，最后再统一加索引

类型：
（1）普通索引：index
仅仅是加快查询速度
（2）唯一索引：unique index
行上的值不能重复
（3）主键索引：primary key
不能重复
主键必唯一，但是唯一索引不一定是主键，一个表上只能有一个主键，但可以有一个或多个唯一索引
（4）全文索引：fulltext index
match (全文索引名) against (查询关键字)-->匹配度
全文索引的停止词：
全文索引不对频繁的词做索引
mysql默认情况下，全文索引对中文意义不大：中文分词问题

查看表的索引：show index from 表名

建立索引：
alter table 表名 add index/unique/fulltext [索引名](列名)
alter table 表名 add primary key (列名)

删除索引：
alter table 表名 drop index 索引名
alter table 表名 drop primary key

23、存储过程
查看存储过程：
show procedure status \G

删除存储过程：
drop procedure 存储过程名称

调用存储过程：
call 存储过程名称

让我们一起遨游在代码的海洋里！