MySql基础

1、数据库基础

常见英文缩写
数据库： DB
数据库管理系统： DBMS，有Oracle、 MySQL、 SQL Server和DB2等
数据库系统： DBS 由计算机
硬件、软件、数据库管理系统和数据管理员组成

数据抽象：物理抽象、概念抽象、视图级抽象,内模式、模式、外模式

完整性约束：实体完整性、参照完整性、用户定义完整性

2、 MySQL常用命令：

2.1 数据库操作

CREATE DATABASE [IF NOT EXISTS] db_name;//语法形式//创建数据库CREATE DATABASE IF NOT EXISTSMYSQL_TRAIN_DBDEFAULT CHARSET utf8DEFAULT COLLATE utf8_general_ci;//删除数据库DROP DATABASE [IF EXISTS] db_name

5.7版本MySQL默认存储引擎是InnoDB，可以在创建表的时候指定存储引擎。存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同事存储引擎也决定了表在计算机中的存储方式。InnoDB支持行锁，事务安全，集群索引，外键等

2.2 表的操作

建表

CREATE TABLE EMPLOYEE (    EMPLOYEE_ID INT NOT NULL AUTO_INCREMENT,    EMPLOYEE_CODE VARCHAR (20),    EMPLOYEE_NAME VARCHAR(45),    DEPT_ID INT,    PRIMARY KEY (EMPLOYEE_ID),    CONSTRAINT EMPLOYEE_FK    FOREIGN KEY (DEPT_ID)    REFERENCES DEPT(DEPTNO))ENGINE = InnoDBDEFAULT CHARACTER SET = utf8;

完整性约束关键字 含义 NOT NUL 约束字段的值不能为空 DEFAULT 设置字段的默认值 UNIQUE KEY(UK) 约束字段的值唯一 PRIMARY KEY(PK) 约束字段为表的主键，表记录的唯一标识 AUTO_INCREMENT 约束字段的值为自动增加 FOREIGN KEY(FK) 外键

主键可以是一个字段或者多个字段

查看表
SHOW CREATE TABLE tbl_name

删除表
DROP TABLE EMPLOYEE2;

修改表
ALTER TABLE DEPT ADD DESCRI VARCHAR(60);

插入记录
INSERT INTO tablename {column1,column2,…} values(exp1,exp2,…);

2.3视图操作

本质上是一种虚拟表，视图并不在数据库中以存储的数据值形式存在，提高复杂SQL的复用性和表操作的安全性。

视图的列可以来自不同的表，视图的建立和删除不影响实表，对视图内容的更新（添加、删除、修改）直接影响实表

CREATE VIEW view_name ASSelect * FROM Table_name;//建视图SHOW CREATE VIEW view_name;DROP VIEW VIEW_DEPT_DNO;删视图ALTER VIEW view_name [(column_list)]AS select_statement;修改视图

2.4索引的操作，索引加快查询。

索引组织方式：BTREE、 HASH
索引分为：普通索引（单列），复合索引（多列），主键索引，唯一索引

Create UNIQUE INDEX index_name ON TableName(col_name);//建索引DROP INDEX index_name ON tbl_name;删除索引 

2.5触发器操作

触发事件：insert update delete

CREATETRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROWtrigger_body //创建SHOW TRIGGERS  //查看DROP TRIGGER//删除

2.6数据库管理操作

GRANT (Select,delete,…) ON (对象) TO USER_NAME [WITH GRANT OPTION];//授权REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION] //撤权

3、MySQl日志管理

二进制日志：
二进制日志主要用来进行数据库恢复和数据库同步复制
操作错误日志：
详细记录了MySQL服务器的开启、关闭和错误信息
通用查询日志：
慢查询日志：
主要记录执行时间超过指定时间的查询语句

4、数据类型

TINYINT 1字节
SMALLINT 2字节
INT 4字节
FLOAT 4字节
DOUBLE 8字节
DECIMAL （M,D） M+2字节 精确小数类型
CHAR 256字符 定长字符串
VARCHAR 65537字符 变长字符
BLOB 二进制形式的长文本数据
TEXT 长文本数据
DATE 3字节
TIME 3字节
YEAR 3字节

5、三范式：

第一范式（1NF）:
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
简而言之，第一范式就是无重复的列，列不可分割。

第二范式（2NF）：
是在第一范式（1NF）的基础上建立起来的。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。每一行用主键标识。

第三范式（3NF）
满足第三范式（3NF）必须先满足第二范式（2NF）。
简而言之，第三范式就是属性不依赖于其它非主属性。任意两个非主键字段的数据值之间不存在函数依赖关系。

6、事务：

是一系列的数据库操作，是数据库应用的基本逻辑单位。可以保证单用户的数据一致性，事务性质：ACID

原子性。即不可分割性，事务要么全部被执行，要么就全部不被执行。

一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

关闭MySQL自动提交
使用MySQL命令“set autocommit=0;” ，可以显示地
关闭MySQL自动提交；
使用MySQL命令“start transaction;” 可以隐式地关闭
自动提交;

关闭MySQL自动提交后
使用commit命令可以提交事务中的更新语句
使用rollback命令可以回滚事务中的更新语句

锁可以保证多用户的数据一致性，锁的类型包括读锁和写锁。

读锁允许其它MySQL客户机对数据同时“读”，但
不允许其它MySQL客户机对数据任何“写”。
写锁不允许其它MySQL客户机对数据同时读，
也不允许其它MySQL客户机对数据同时写 。

MySQL数据库为我们提供的四种隔离级别：

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ Read committed (读已提交)：可避免脏读的发生。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
　　关于事务隔离级别具体请查看本链接

7、表的操作

INSERT INTO TABLE_NAME(COL1,COL2,...)VALUES(‘字符值或日期值‘,数值,….);

UPDATE TABLE_NAMESET COL1=‘VALUE1’ , COL2=VALUE2, …WHERE 条件

DELETE：DELETE FROM TABLE_NAMEWHERE 条件…

条件中 x=y 表示 x等于y
x<>y x不等于y
IS NULL/IS NOT NULL

通配符:
_ 任意一个字符
%：任意0~n个字符

聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

having与where的区别:
1.having是在groupby分组后对数据进行过滤.
where是在分组前对数据进行过滤

2.having后面可以使用聚合函数(统计函数)
where后面不可以使用聚合函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

LIMIT用来限定查询结果的起始行，以及总行数。

查询语句书写顺序：select – from- where- group by- having- order by-limit
查询语句执行顺序：from - where -group by - having - select - order by-limit

8、函数

concat(x1,x2,….)函数用于将x1、 x2等若
干个字符串连接成一个新字符串；
concat_ws(x,x1,x2,….)函数使用x将x1、
x2等若干个字符串连接成一个新字符串。

ltrim(x)函数用于去掉字符串x开头的所有
空格字符；
rtrim(x)函数用于去掉字符串x结尾的所有
空格字符；
trim(x)函数用于去掉字符串x开头以及结
尾的所有空格字符。

left(x,n)函数返回字符串x的前n个字符；
right(x,n)函数返回字符串x的后n个字符;
substring(x,start,length)函数从字符串x
的第n个位置开始获取length长度的字符串；

version()函数获取当前MySQL版本号；
curdate()函数获取MySQL服务器当前日期；
curtime()函数获取MySQL服务器当前时间；

datediff(x1,x2)函数用于计算日期x1与x2之间的相
隔天数；
adddate(d,n)函数返回起始日期d加上n天的日期；
subdate(d,n)函数返回起始日期d减去n天的日期；

9、优化

1、避免使用 left join
对于大量数据，left join效率低，可以换用 inner join或者将条件分解成多个部分查询

on与 where的执行顺序
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。ON将从匹配阶段产生的数据中检索过滤。
2、应尽量避免在 where 子句中使用 or 来连接条件
如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，可以换union

3、尽量避免子查询，而用join

Explain可以用来查看SQL语句的执行效果