mysql

关系型数据库管理系统 、开源（MariaDB）、隶属Oracle数据库
官网地址：http://www.mysql.com/
关系型数据库
—— MySql Mssql Oracle等都属于关系型数据库
用关系模型来组织管理数据。关系模型可以理解为二维表的模型，由二维表及表之间的联系所组成的数据组织。
非关系型数据库
—— NoSQL：Mongodb Redis……
是一种新的数据结构存储方式的集合。存储方式有文档、键值对、图片形式等，结构不固定，每一个元祖可以有不一样的字段或键值对，约束少等特点。
对比
1、对并发量较大的系统，读写较高的情况下，关系型数据库将数据存储在硬盘中，非关系数据库将数据存储在缓存中，执行效率的差别
2、存储类型：NOSQL存储格式有文档、键值对、图片等形式，可存储除了基础类型外还有以及对象等格式类型，Mysql只能存储基础类型数据
3、可扩展性
My.ini配置文件：
端口号 3306
数据存放位置：mysql安装目录下的data文件夹下
修改XAMPP集成环境 root用户的密码：
//方式1：
UPDATE user SET password=PASSWORD(‘123456’) WHERE user=’root’;
flush privileges;

//方式2：
mysqladmin -uroot -p123456　password　000000
Mysql安装后自带的数据库有：
information_schema：当前mysql实例中所有数据库的信息,主要存储了系统中的一些数据库对象信息：如用户表信息、列信息、权限信息、字符集信息、分区信息等。
mysql：是mysql的核心数据库，存储了系统的用户权限信息及帮助信息。
performance_schema：主要用于收集数据库服务器性能参数。
test：系统自动创建的测试数据库，任何用户都可以使用。
常用命令：
1、连接数据库：
mysql –h主机地址 -u用户名 -p密码
mysql –u root –p 123456;

2、显示数据库：
show databases;

3、操作数据库：
use 数据库名称;

4、显示数据表：
show tables;

5、创建数据库：
create database 数据库名称;
或：
create database if not exists 数据库名;

设置数据库编码UTF-8：
CREATE DATABASE IF NOT EXISTS 数据库名 default character set utf8 COLLATE utf8_general_ci;

5、修改数据库编码：
alter database 数据库名 character set latin1;

6、建表：
create table 表名 (字段名 varchar(20), 字段名 int(1));
create table Tables(
id int(4) not null primary key auto_increment,
name char(20) not null,
sex int(4) not null default ‘0’,
age double(16,2));

7、修改表：
删除列:ALTER TABLE tablename DROP COLUMN names;
添加列:ALTER TABLE Tablename ADD COLUMN names char(10);
修改字段：ALTER TABLE tablename CHANGE names idd BIGINT;
修改字段类型：ALTER TABLE tablename MODIFY idd BIGINT NOT NULL;
修改字段编码：ALTER TABLE tablename CHANGE name name varchar(255) CHARACTER SET utf8;

8、删除表：
drop table 表名;

9、删库：
drop database 库名;
用图形化管理工具备份数据库、还原数据库操作;
导出：mysqldump -u root -p dbname > dbname.sql
导入：use dbname; source /var/db/news.sql;
Mysql存储引擎
Mysql5.5后默认采用InnoDB存储引擎。
1、MyISAM引擎：
不支持事务、外键，对访问速度快要求的（SELECT）使用。每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为： .frm(存储表定义) MYD(MYData，存储数据) MYI(MYIndex，存储索引)
2、InnoDB（默认引擎）
提供了具有提交、回滚和崩溃恢复能力的事务安全。MySQL支持外键的存储引擎只有InnoDB
3、MEMORY引擎：memory使用存在内存中的内容来创建表。
4、CSV：存储引擎把数据以逗号分隔的格式存储在文本文件中。
Mysql常用数据类型：
Int 整形 (-2 147 483 648，2 147 483 647)
Bigint 极大整数值 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807)
FLOAT 单精度浮点数值
DOUBLE 双精度浮点数值
DATE 日期值 YYYY-MM-DD
DATETIME 日期和时间值 YYYY-MM-DD HH:MM:SS
CHAR 定长字符串 0-255字节
VARCHAR 变长字符串 0-65535 字节
TEXT 长文本数据
char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）
在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）
decimal类型常用于存储金额数据
DECIMAL(M,D) M是数字的最大数（精度）,其范围为1～65（在较旧的MySQL版本中，允许的范围是1～254），M 的默认值是10。D是小数点右侧数字的数目（标度）。其范围是0～30，但不得超过M。
FLOAT 可以表示绝对值非常小、小到约 1.17E-38 (0.000…0117，小数点后面有 37 个零) 的小数，而 DOUBLE 更是可以表示绝对值小到约 2.22E-308 (0.000…0222，小数点后面有 307 个零) 的小数。FLOAT 类型和 DOUBLE 类型占用存储空间分别是 4 字节和 8 字节。如果需要用到小数的字段，精度要求不高的，当然用 FLOAT 了。

SQL语句
Structured Query Language 结构化查询语言
DML — Data Manipulation Language数据操作语言 如：select update insert delete语句
DCL — Data Control Language数据控制语言 如：GRANT、REVOKE 等（权限）
DDL — Data Definition Language数据定义语言 如：CREATE TABLE 、DROP TABLE、ALTER TABLE 等

通配符：
% ： a%b 以a开头 以b结尾的内容
_ ： a_b 以a开头 以b结尾的三个字符
逻辑运算符：
AND &&
OR ||
NOT !
INSERT 语句
Insert into 表名 (字段1，字段2…) values(内容1，内容2，内容3)

Delete 语句
Delete from 表名 where 条件

Update语句
Update 表名 set 字段1=值1, 字段2=值2 where 条件

Select语句
Select 字段，，，* from 表名 where 条件
Order by * Desc 或 * Asc 可多条排序方法用逗号分割
Limit limit 0,10 从0开始取10条记录出来
Group by 、having: 分组查询 having分组查询条件

SELECT A ,COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2
Mysql内置函数
Count(字段名) 总计
Sum(字段名) 求和
Avg(字段名) 平均
Max(字段名) 最大值
Min(字段名) 最小值
MySql谓词
Between and 范围
Not between and
In 特定的集合中
Like 匹配一个模式
Is null 、 is not null

主键与外键
外键仅适用InnoDB引擎
主键：帮助MySQL以最快的速度把一条特定的数据记录的位置确定下来。主键必须是唯一的。主键应该是紧凑的，因此整数类型比较适合。
外键：引用另外一个数据表的某条记录。外键列类型尽可能与主键列类型保持一致。外键列应该加上NOT NULL
注意：
1、当主表中没有对应的记录时，不能将记录添加到子表
2、不能更改主表中的值而导致子表中的记录孤立
3、子表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删子表
外键类型：

1、Cascade 在父表上update/delete记录时，同步update/delete掉子表的匹配记录

2、No Action 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

3、Restrict 不允许对父表对应候选键进行update/delete操作，同上

4、Set null 在父表上update/delete记录时，将子表上匹配记录的列设为null，注意子表的外键列不能为not null

连接查询
同时涉及多个表的查询
普通连接
内连接( INNER JOIN )
外连接（ 左外联结—LEFT JOIN 右外联结—RIGHT JOIN 全外连接—FULL JOIN）
注：普通连接操作只输出满足连接条件的元组，外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出
普通连接：

SELECT Students.SName, Sc.CourseID, Sc.Grade
FROM Students,Sc
WHERE Students.SNo = Sc.StudentID

内连接：Inner Join 表名 on (条件)
SELECT
S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩
FROM Students AS S
INNER JOIN Sc AS C ON (S.SCode = C.StudentID)

外连接：LEFT/RIGHT Join 表名 on (条件)
SELECT
S.SName AS 姓名, CS.CName AS 课程, C.Grade AS 成绩
FROM Students AS S
LEFT/RIGHT/FULL JOIN Sc AS C ON (S.SCode = C.StudentID)

联合查询：union/union all 合并多查询结果（union会过滤重复记录、union all不会过滤）

SELECT prodinfo.pname, ptype.type_name
FROM prodinfo
RIGHT JOIN ptype
ON prodinfo.ptype = ptype.tid

UNION

SELECT prodinfo.pname, ptype.type_name
FROM prodinfo
LEFT JOIN ptype
ON prodinfo.ptype = ptype.tid
视图
虚拟表，内容由查询结果决定（筛选）。视图是不以数据值的形式存在数据库中，只有视图被引用的时候才会动态生成。
创建和使用视图：
CREATE
VIEW testdb.testView
AS
(SELECT id,val FROM tset_tab1 WHERE id IS NOT NULL);
使用视图的好处：
1、简化操作，虚拟表
2、更方便重构表数据结构
3、加强数据结构的安全

存储过程
SQL语句预编译的集合，存是数据库中，可以由应用程序调用执行，可以带相关参数，可以理解为是将SQL语句进行了封装并放在数据库中，并且是已经编译好了，调用即可立即执行，所以执行效率要比直接执行sql语句的效果要高。
优点或特性：
1、执行sql效率更高;(第一次效率相同，后面直接从内存中调用)
2、减少流量
3、安全性更高（都是预先检查语法问题的）
DELIMITER

CREATE/∗[DEFINER=user|CURRENTUSER]∗/PROCEDURE‘testdb‘.‘存数过程名称‘(参数)/∗LANGUAGESQL|[NOT]DETERMINISTIC|CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA|SQLSECURITYDEFINER|INVOKER|COMMENT′string′∗/BEGIN过程体（SQL语句CURD）END

DELIMITER ;
参数: [in | out | inout ] 参数名 类型
In :该参数调用存储过程时指定的， 进
Out：该参数在是返回用的， 出
Inout：调用时指定，并可改变并返回 进出
调用存储过程：
Call 存储过程名(参数)
Call test();
Call test(2);
Call test(27, @num); //@num为变量的意思
CALL test2(1,@counts);
SELECT @counts;

索引
InnoDB引擎和MyISAM引擎。
数据表中建立索引可以很大程度上提高查询的速度（大数据量），也是优化mysql的其中一种办法。在查表（多表联合）查询中建立索引比较常用到。
索引分为：单列索引和组合索引
单列索引：只有一个字段的索引。
组合索引：可以包含多个字段。
Mysql索引类型：
1、PRIMARY 主键索引 建立在主键上。表的主键会自动创建主键索引
2、FULLTEXT全文索引 WHERE name LIKE “%keyword%”，限制在CHAR、VARCHAR ，TEXT 类型的列（文本）上可以创建全文索引
3、UNIQUE 唯一索引 该字段的值在表中是有唯一性的
如：身份证号、注册邮箱、注册手机等
4、INDEX 默认索引（普通索引）
例外：索引虽然可以提高查询速度，但会降低更新表的速度，因为在更新表时，不仅要更新表数据，还要更新索引文件。 索引文件会占用硬盘空间,如果一个表的索引建的过多，会导致表文件更大。
使用索引需注意：
1、 索引不包括NULL值
2、 where条件中如果已经使用索引，order by的字段不会使用索引。尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
触发器
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
INSERT、UPDATE、DELETE触发器
DELIMITER

CREATE/∗[DEFINER=user|CURRENTUSER]∗/TRIGGER‘testdb‘.‘test‘AFTERINSERTON‘testdb‘.‘testtab1‘FOREACHROWBEGINUPDATEtesttab3SETprices=prices+NEW.tabpriceWHEREidISNOTNULL;END

DELIMITER ;
AFTER触发就是在执行命令操作之后执行触发操作； BEFORE触发就是在执行操作命令之前执行触发操作。
事件调度器
MySQL中通过定时操作，完成某些对数据的修改删除等操作，可以定时精准至秒来执行操作任务。
在使用这个功能之前必须确保event_scheduler已开启:
set GLOBAL event_scheduler = 1; 或et GLOBAL event_scheduler = ON;
查看当前是否已开启事件调度器：
SHOW VARIABLES LIKE ‘event_scheduler’
创建定时任务：
DELIMITER $$

CREATE EVENT testdb.tests_event

ON SCHEDULE EVERY 1 SECOND STARTS TIMESTAMP ‘2017-07-23 15:58:55’

DO
BEGIN
CALL testss(); //或自定义SQL语句
END$$

DELIMITER ;
MySql数据库设计 — 三大范式
第一范式：确保每列的原子性.
如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
例如：产品信息表（产品名、产品价格、库存量、产品类别），其中产品类别还可以细分为（产品大类、产品子类）
第二范式:在第一范式的基础上,目标是确保表中的每列都和主键相关.
如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
例如：订单表（订单编号[主键]、产品ID、订购时间、订购数量、总价），其中产品ID和订单编号没有关系，不依赖于主键，则不满足第二范式。
第三范式：在第二范式的基础上，确保每列都和主键列直接相关,而不是间接相关.
如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.
假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，依赖A-〉C是传递依赖，而不是直接依赖。
例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列”订单编号”相关，再细看你会发现”顾客姓名”和”顾客编号”相关，”顾客编号”和”订单编号”又相关，最后经过传递依赖，”顾客姓名”也和”订单编号”相关。为了满足第三范式，应去掉”顾客姓名”列，放入客户表中。
MYSQL事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务；
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行；
事务用来管理insert,update,delete语句。
事务是必须满足4个条件（ACID）Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）
1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性 ： 有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项 决定什么时候吧事务保存到日志里。