Mysql基础

1. 1. MySQL

1.1 概述

MySQL是数据库管理系统（DBMS），关系型数据库，类型：按行储存。

1.2 SQL语言

数据类型：

数值类型：int，double…

日期类型：date YYYY-MM-DD，time HH：MM：SS

Timestamp默认初始值是当前日期时间

字符串类型：varchar（M），text长文本数据（纯文本） blob二进制形式的长文本数据（文本、图片）

       ENUM枚举、Set集合、JSON类型：JavaScript格式数据

Sql分类：

DDL  库、表、列   create、show、alter…set…、drop、alter table…change/modify/add/drop

DML 表数据  insert into、update…set…、delete from…where…、truncate （DDL）

DQL 表数据   书写顺序：select…from…where…group by…having…order by…limit…

                     执行顺序：from…where…group by…having…select…order by…limit…

DCL 访问权限、事务  grant、commit、rollback

常用：  <=> 安全等于可以判断是否为null

<>相当于!= 可以判断null

&& 相当于AND  || 相当于OR   ! 相当于NOT

Like “%”多个字符,”_”一个字符 用于字符串或日期

Between x and y 闭区间

Distinct 字段   去重

排序相关:      order by 字段1,字段2  先按字段1排序，如果相同按字段2排序

1.3 数据完整性

·实体完整性：一行是一个实体，保证唯一，域完整性：保证单元格数据正确，引用完整性：实体（多表）之间关系的约束，也称外键约束

·约束（CONSTRAINTS）

主键约束（primary key）、 唯一约束(unique) 、 自动增长列(auto_increment)

联合主键语法：PRIMARY KEY(字段1，字段2)

自增列必须是键列（主键列，唯一键列，外键列），并且要求非空，通常是主键

数据类型约束、非空约束、默认值约束、check约束

外键约束（foreign key） 语法：CONSTRAINT 约束名 foreign key(字段) references 主表名(主表字段)

·索引（INDEX）

Mysql会在主键、唯一键、外键列上自动创建索引，其他列需要建立索引的话，需要手动创建。

1.4 多表查询

UNION 合并的俩表，列数、类型必须相同，union all 不去重

inner join…on… 内连接，两表的重复部分

left join…on… 俩表的重复部分和左表的独有部分，独有部分右表字段值都为null

right join…on… 俩表的重复部分和右表的独有部分，独有部分左表字段值都为null

left join…on…where 关联字段=null 只要左表的独有部分

1.5 函数

·单行函数

数值和字符串

CEIL(x)	返回大于x的最小整数值
FLOOR(x)	返回小于x的最大整数值
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机值
ROUND(x,y)	返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)	返回数字x截断为y位小数的结果
SQRT(x)	返回x的平方根
POW(x,y)	返回x的y次方
CONCAT(S1,S2,......,Sn)	连接S1,S2,......,Sn为一个字符串
CONCAT_WS(s, S1,S2,......,Sn)	同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上s
CHAR_LENGTH(s)	返回字符串s的字符数
LENGTH(s)	返回字符串s的字节数，和字符集有关
INSERT(str, index , len, instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)	将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)	将字符串s的所有字母转成小写字母
LEFT(s,n)	返回字符串s最左边的n个字符
RIGHT(s,n)	返回字符串s最右边的n个字符
LPAD(str, len, pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
RPAD(str ,len, pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
LTRIM(s)	去掉字符串s左侧的空格
RTRIM(s)	去掉字符串s右侧的空格
TRIM(s)	去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)	去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)	去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)	去掉字符串s结尾处的s1
REPEAT(str, n)	返回str重复n次的结果
REPLACE（str, a, b）	用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)	比较字符串s1,s2
SUBSTRING(s,index,len)	返回从字符串s的index位置其len个字符

日期函数

CURDATE() 或 CURRENT_DATE()	返回当前日期
CURTIME() 或 CURRENT_TIME()	返回当前时间
NOW() / SYSDATE() / CURRENT_TIMESTAMP() / LOCALTIME() / LOCALTIMESTAMP()	返回当前系统日期时间
YEAR(date) / MONTH(date) / DAY(date) / HOUR(time) / MINUTE(time) / SECOND(time)	返回具体的时间值
WEEK(date) / WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意：周日是1，周一是2，。。。周六是7
WEEKDAY(date)	返回周几，注意，周1是0，周2是1，。。。周日是6
DAYNAME(date)	返回星期：MONDAY,TUESDAY.....SUNDAY
MONTHNAME(date)	返回月份：January，。。。。。
DATEDIFF(date1,date2) / TIMEDIFF(time1, time2)	返回date1 - date2的日期间隔 / 返回time1 - time2的时间间隔
DATE_ADD(datetime, INTERVAL expr type)	返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_FORMAT(datetime ,fmt)	按照字符串fmt格式化日期datetime值
STR_TO_DATE(str, fmt)	按照字符串fmt对str进行解析，解析为一个日期

SELECT          DATE_ADD(NOW(),      INTERVAL       1      YEAR);
SELECT                   DATE_ADD(NOW(),      INTERVAL       -1     YEAR);  
SELECT           DATE_ADD(NOW(),      INTERVAL       '1_1'     YEAR_MONTH);   单引号

（2）DATE_FORMAT(datetime,fmt) 和 STR_TO_DATE(str, fmt)

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	表示两位数字表示年份
%M	月名表示月份（January,....）	%m	两位数字表示月份（01,02,03。。。）
%b	缩写的月名（Jan.，Feb.，....）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数(01,02...)
%e	数字形式表示月中的天数（1,2,3,4,5.....）
%H	两位数字表示小数，24小时制（01,02..）	%h和%I	两位数字表示小时，12小时制（01,02..）
%k	数字形式的小时，24小时制(1,2,3)	%l	数字形式表示小时，12小时制（1,2,3,4....）
%i	两位数字表示分钟（00,01,02）	%S和%s	两位数字表示秒(00,01,02...)
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun.，Mon.,Tues.，..）
%w	以数字表示周中的天数(0=Sunday,1=Monday....)
%j	以3位数字表示年中的天数(001,002...)	%U	以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天
%u	以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

流程函数

函数	用法
IF(value,t ,f)	如果value是真，返回t，否则返回f
IFNULL(value1, value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 .... [ELSE resultn] END	相当于Java的if...else if...else...
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END	相当于Java的switch...case...

1.6 事务

·概述

事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。事务特性：原子性Atomicity：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生；一致性Consistency：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。例如：转账前和转账后的总金额不变；隔离性Isolation：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务执行时，不能被其他事务的执行所干扰，多个并发执行的事务之间要相互隔离。（通过事务的隔离级别保证隔离性）；持久性Durability：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

·隔离级别

事务并发问题：赃读：指一个事务读取了另一个事务未提交的数据。不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务修改并提交后的数据。虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

事务隔离级别：Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

Repeatable read (可重复读)：可避免脏读、不可重复读的发生。（MySQL默认）

Read committed (读已提交)：可避免脏读的发生。（Oracle默认）

Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

·事务操作

MySQL两种引擎：InnoDB是支持事务的，MyISAM是不支持事物的（存取效率高）

默认是自动提交事务，每条语句都处在单独的事务中。

1.7 用户与权限

·用户：针对不同的用户进行合理的用户角色权限分配，即用户管理。数据库管理员DBA一般使用root账户，拥有全部数据库的权限。

• root@localhost：只允许在本机使用root用户登录
• root@%：运行在任意机器上使用root用户登录
• root@192.168.11.56：只运行在192.168.11.56主机上使用root用户登录
• tom@%：运行在任意机器上使用tom用户登录

·权限：全局 > 数据库 > 表 > 字段

·sql乱码问题

修改my.ini配置 用utf8编码

1.8 索引（重要）

定义： 排好序的快速查找数据结构

数据结构：Btree 树状结构

优势： 降低IO，提高查询效率；降低了数据的排序成本

劣势： 索引也是表，占磁盘空间。查询快，但修改慢，数据改动时索引也要修改。索引需要优化。

建立索引的情况：

·主键自动建立索引

·频繁查询的字段建立索引

·外键建立索引

·排序字段建立索引

·高并发下倾向建立组合索引

·group by条件建立索引

·频繁修改的字段不要建立索引

·where条件里用不到的字段不建立索引

·表记录太少不要建立索引（300万）

·经常增删改的表不要建立索引

·重复记录太多建立索引没有太大作用