MySQL进阶

目录

• 基本操作
  • help 帮助
  • 登录 退出
  • 简单查询，不区分大小写
  • 数据库的创建及使用
  • 表的创建和使用
  • 表中导入数据
  • 数据检索部分
  • 日期计算
  • 数据库的备份与恢复
  • case when 语句
  • group by 语句
  • MySQL 元数据
• mysql 函数
  • MySQL 字符串函数
  • MySQL 数字函数
  • MySQL 日期函数
  • MySQL 高级函数
• MySQL 索引
  • 普通索引
  • 唯一索引
  • 显示索引
• mysql 事务
  • 含义
  • 特点
  • 相关步骤：
  • MySQL 执行引擎
• MySQL 语句优化

基本操作

help 帮助

help timpstampdiff;

登录 退出

- 登录
service mysqld start
mysql -uroot -p123456
- 退出
quit

简单查询，不区分大小写

# 查看版本号及当前时间
select version(),user(),current_date;
# 行末 c 表示放弃执行

数据库的创建及使用

create database menagerie;
use menagerie

表的创建和使用

# 创建
 create table pet(
    -> name varchar(20),
    -> owner varchar(20),
    -> species varchar(20),
    -> sex char(1),
    -> birth date,
    -> death date);
# 查看创表语句
show create table  pet;
# 查看表结构
 desc pet;

表中导入数据

# 方法1 使用insert into 
insert into pet values('Fluffy','Harold','cat','f','1993-02-04');
...
或者 pet.txt 上传至Linux本地,注意都是制表符
Fluffy  Harold  cat     f       1993-02-04
Claws   Gwen    cat     m       1994-03-17
Buffy   Harold  dog     f       1989-05-13
Fang    Benny   dog     m       1990-08-27
Bowser  Diane   dog     m       1979-08-31      1995-07-29
Chirpy  Gwen    bird    f       1998-09-11
Whistler        Gwen    bird    N      1997-12-09      N
Slim    Benny   snake   m       1996-04-29

# 执行 
load data local infile '/opt/data/sql/pet.txt'into table pet;
# 删除所有表数据
delete from pet;

数据检索部分

# 数据更新
update pet set death=null where death='0000-00-00'；
# 查找生日在98以后
select * from pet where birth>'1998-01-01';
# 多条件查询，优先执行括号中的语句
SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
# 去除重复 distinct 
# 排序 order by
select * from pet
order by birth,species desc;
按照 birth 升序，species 降序

日期计算

# 查看宠物年龄
SELECT TIMESTAMPDIFF(YEAR,birth,curdate()) as age from pet;
# 查看没死的宠物年龄（null和not null 要用 is 去比较）
 SELECT TIMESTAMPDIFF(YEAR,birth,curdate()) as age from pet
    -> where death is null;

数据库的备份与恢复

• 备份命令

语法：mysqldump -u 用户名 -p 数据库名> 磁盘SQL文件路径
由于mysqldump命令不是sql命令，需要在dos窗口下使用(即退出mysql后执行)。
mysqldump -uroot -p123456 menagerie >/opt/data/sql/menagerie.sql

• 恢复命令

语法：mysql -u 用户名-p 导入库名< 硬盘SQL文件绝对路径
需求：
​1、恢复数据库，需要手动的先创建数据库：
create database heima;
2、重新开启一个新的dos窗口。
//恢复命令
mysql -uroot -p123456 heima </opt/data/sql/menagerie.sql

case when 语句

# 创建表
create table employee(
    -> empid                   int  ,                                       
    -> deptid                  int   ,                                      
    -> sex                     varchar(20)  ,                                    
    -> salary                  double 
    -> );
# 表数据上传至 /opt/data/sql/ 下
1	10	female	5500.0
2	10	male	4500.0
3	20	female	1900.0
4	20	male	4800.0
5	40	female	6500.0
6	40	female	14500.0
7	40	male	44500.0
8	50	male	6500.0
9	50	male	7500.0
# 添加数据
load data local infile '/opt/data/sql/employee.txt' into table employee;
# 按照salary分级，5000,10000,15000之间分别表示为 低等收入，中等收入，高等收入


​select *,
case 
when salary < 5000 then "低等收入" 
when salary>= 5000 and salary < 10000 then "中等收入"
when salary > 10000 then "高等收入"  
end  as level,
case sex
when "female" then 1 
when "male" then 0
end as flag 
from employee;

# case when then else end
select * , case when deptid<40 then "部门1" else "部门2" end as "部门" from employee; 

group by 语句

# 准备表

CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------

INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'),
 ('2', '小王', '2016-04-20 15:25:47', '3'),
 ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), 
('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

# 使用 group by 实现简单的去重查询
select name from employee_tbl group by name;
# 在其后使用having 限定

MySQL 元数据

- 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
- 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
- MySQL服务器信息： 包含了数据库服务器的当前状态，版本号等。

命令	描述
SELECT VERSION( )	服务器版本信息
SELECT DATABASE( )	当前数据库名 (或者返回空)
SELECT USER( )	当前用户名
SHOW STATUS	服务器状态
SHOW VARIABLES	服务器配置变量

mysql 函数

MySQL 字符串函数

函数	描述	实例
ASCII(s)	返回字符串 s 的第一个字符的 ASCII 码。	返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstCharFROM Customers;
CHAR_LENGTH(s)	返回字符串 s 的字符数	返回字符串 itcast 的字符数SELECT CHAR_LENGTH("itcast") AS LengthOfString;
CHARACTER_LENGTH(s)	返回字符串 s 的字符数	返回字符串 itcast 的字符数SELECT CHARACTER_LENGTH("itcast") AS LengthOfString;
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串SELECT CONCAT("SQL ", "itcast ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn)	同 CONCAT(s1,s2,...) 函数，但是每个字符串直接要加上 x，x 可以是分隔符	合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...)	返回第一个字符串 s 在字符串列表(s1,s2...)中的位置	返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2)	返回在字符串s2中与s1匹配的字符串的位置	返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e");
**FORMAT(x,n) **	函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。	格式化数字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2)	字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串	从字符串第一个位置开始的 6 个字符替换为 itcast：SELECT INSERT("google.com", 1, 6, "runnob"); -- 输出：runnob.com
LOCATE(s1,s)	从字符串 s 中获取 s1 的开始位置	获取 b 在字符串 abc 中的位置：SELECT INSTR('abc','b') -- 2
**LCASE(s) **	将字符串 s 的所有字母变成小写字母	字符串 itcast 转换为小写：SELECT LOWER('itcast') -- itcast
LEFT(s,n)	返回字符串 s 的前 n 个字符	返回字符串 itcast 中的前两个字符：SELECT LEFT('itcast',2) -- it
LEFT(s,n)	返回字符串 s 的前 n 个字符	返回字符串 abcde 的前两个字符：SELECT LEFT('abcde',2) -- ab
LOCATE(s1,s)	从字符串 s 中获取 s1 的开始位置	返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2
**LOWER(s) **	将字符串 s 的所有字母变成小写字母	字符串 itcast 转换为小写：SELECT LOWER('itcast') -- itcast
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 itcast开始处的空格：SELECT LTRIM(" itcast") AS LeftTrimmedString;-- itcast
MID(s,n,len)	从字符串 s 的 start 位置截取长度为 length 的子字符串，同 SUBSTRING(s,n,len)	从字符串 itcast 中的第 2 个位置截取 3个 字符：SELECT MID("itcast", 2, 3) AS ExtractString; -- tca
POSITION(s1 IN s)	从字符串 s 中获取 s1 的开始位置	返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2
** REPEAT(s,n) **	将字符串 s 重复 n 次	将字符串 itcast 重复三次：SELECT REPEAT('itcast',3) -- itcastitcastitcast
**REPLACE(s,s1,s2) **	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba
RIGHT(s,n)	返回字符串 s 的后 n 个字符	返回字符串 itcast 的后两个字符：SELECT RIGHT('itcast',2) -- st
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s1，使字符串的长度达到 len	将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 itcast 的末尾空格：SELECT RTRIM("itcast ") AS RightTrimmedString; -- itcast
SPACE(n)	返回 n 个空格	返回 10 个空格：SELECT SPACE(10);
**STRCMP(s1,s2) **	比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1	比较字符串：SELECT STRCMP("itcast", "itcast"); -- 0
**SUBSTR(s, start, length) **	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 itcast 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("itcast", 2, 3) AS ExtractString; -- tca
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 itcast 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("itcast", 2, 3) AS ExtractString; -- tca
SUBSTRING_INDEX(s, delimiter, number)	返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数，返回第 number 个字符左边的字符串。如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。	SELECT SUBSTRING_INDEX('a*b','*',1) -- aSELECT SUBSTRING_INDEX('a*b','*',-1)    -- bSELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 itcast 的首尾空格：SELECT TRIM(' itcast ') AS TrimmedString;
UCASE(s)	将字符串转换为大写	将字符串 itcast 转换为大写：SELECT UCASE("itcast"); -- itcast
UPPER(s)	将字符串转换为大写	将字符串 itcast 转换为大写：SELECT UPPER("itcast"); -- itcast

---

MySQL 数字函数

函数名	描述	实例
**ABS(x) **	返回 x 的绝对值	返回 -1 的绝对值：SELECT ABS(-1) -- 返回1
ACOS(x)	求 x 的反余弦值(参数是弧度)	SELECT ACOS(0.25);
ASIN(x)	求反正弦值(参数是弧度)	SELECT ASIN(0.25);
ATAN(x)	求反正切值(参数是弧度)	SELECT ATAN(2.5);
ATAN2(n, m)	求反正切值(参数是弧度)	SELECT ATAN2(-0.8, 2);
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)	向上取整	SELECT CEIL(1.5) -- 返回2
CEILING(x)	向上取整	SELECT CEIL(1.5) -- 返回2
COS(x)	求余弦值(参数是弧度)	SELECT COS(2);
COT(x)	求余切值(参数是弧度)	SELECT COT(6);
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号	返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)	将弧度转换为角度	SELECT DEGREES(3.1415926535898) -- 180
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5：SELECT 10 DIV 5; -- 2
EXP(x)	返回 e 的 x 次方	计算 e 的三次方：SELECT EXP(3) -- 20.085536923188
**FLOOR(x) **	向下取整	小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)	返回列表中的最大值	返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "itcast", "Apple"); -- itcast
LEAST(expr1, expr2, expr3, ...)	返回列表中的最小值	返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "itcast", "Apple"); -- Apple
LN	**返回数字的自然对数 **	返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453
LOG(x)	返回自然对数(以 e 为底的对数)	SELECT LOG(20.085536923188) -- 3
LOG10(x)	返回以 10 为底的对数	SELECT LOG10(100) -- 2
**LOG2(x) **	**返回以 2 为底的对数 **	返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156
**MAX(expression) **	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS LargestPrice FROM Products;
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数：SELECT MOD(5,2) -- 1
PI()	返回圆周率(3.141593）	SELECT PI() --3.141593
POW(x,y)	返回 x 的 y 次方	2 的 3 次方：SELECT POW(2,3) -- 8
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方：SELECT POWER(2,3) -- 8
RADIANS(x)	将角度转换为弧度	180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898
RAND()	返回 0 到 1 的随机数	SELECT RAND() --0.93099315644334
**ROUND(x) **	**返回离 x 最近的整数 **	SELECT ROUND(1.23456) --1
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	SELECT SIGN(-10) -- (-1)
SIN(x)	求正弦值(参数是弧度)	SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)	返回x的平方根	25 的平方根：SELECT SQRT(25) -- 5
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)	求正切值(参数是弧度)	SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

---

MySQL 日期函数

函数名	描述	实例
ADDDATE(d,n)	**计算其实日期 d 加上 n 天的日期 **	SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);->2017-06-25
ADDTIME(t,n)	时间 t 加上 n 秒的时间	SELECT ADDTIME('2011-11-11 11:11:11', 5)->2011-11-11 11:11:16 (秒)
CURDATE()	返回当前日期	SELECT CURDATE();-> 2018-09-19
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE();-> 2018-09-19
CURRENT_TIME	返回当前时间	SELECT CURRENT_TIME();-> 19:59:02
CURRENT_TIMESTAMP()	返回当前日期和时间	SELECT CURRENT_TIMESTAMP()-> 2018-09-19 20:57:43
CURTIME()	返回当前时间	SELECT CURTIME();-> 19:59:02
DATE()	**从日期或日期时间表达式中提取日期值 **	SELECT DATE("2017-06-15"); -> 2017-06-15
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF('2001-01-01','2001-02-02')-> -32
DATE_ADD(d，INTERVAL expr type)	计算起始日期 d 加上一个时间段后的日期	SELECT ADDDATE('2011-11-11 11:11:11',1)-> 2011-11-12 11:11:11    (默认是天)SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE)-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
DATE_FORMAT(d,f)	按表达式 f的要求显示日期 d	SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。	Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDateFROM Orders
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
DAYNAME(d)	返回日期 d 是星期几，如 Monday,Tuesday	SELECT DAYNAME('2011-11-11 11:11:11')->Friday
DAYOFMONTH(d)	计算日期 d 是本月的第几天	SELECT DAYOFMONTH('2011-11-11 11:11:11')->11
DAYOFWEEK(d)	日期 d 今天是星期几，1 星期日，2 星期一，以此类推	SELECT DAYOFWEEK('2011-11-11 11:11:11')->6
DAYOFYEAR(d)	计算日期 d 是本年的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11')->315
EXTRACT(type FROM d)	从日期 d 中获取指定的值，type 指定返回的值。 type可取值为：	SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11
ROM_DAYS(n)	计算从 0000 年 1 月 1 日开始 n 天后的日期	SELECT FROM_DAYS(1111)-> 0003-01-16
HOUR(t)	返回 t 中的小时值	SELECT HOUR('1:2:3')-> 1
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY("2017-06-20");-> 2017-06-30
LOCALTIME()	返回当前日期和时间	SELECT LOCALTIME()-> 2018-09-19 20:57:43
LOCALTIMESTAMP()	返回当前日期和时间	SELECT LOCALTIMESTAMP()-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year)	基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期	SELECT MAKEDATE(2017, 3);-> 2017-01-03
MAKETIME(hour, minute, second)	组合时间，参数分别为小时、分钟、秒	SELECT MAKETIME(11, 35, 4);-> 11:35:04
MICROSECOND(date)	返回日期参数所对应的毫秒数	SELECT MICROSECOND("2017-06-20 09:34:00.000023");-> 23
MINUTE(t)	返回 t 中的分钟值	SELECT MINUTE('1:2:3')-> 2
MONTHNAME(d)	返回日期当中的月份名称，如 Janyary	SELECT MONTHNAME('2011-11-11 11:11:11')-> November
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11')->11
NOW()	返回当前日期和时间	SELECT NOW()-> 2018-09-19 20:57:43
PERIOD_ADD(period, number)	为 年-月 组合日期添加一个时段	SELECT PERIOD_ADD(201703, 5); -> 201708
PERIOD_DIFF(period1, period2)	返回两个时段之间的月份差值	SELECT PERIOD_DIFF(201710, 201703);-> 7
QUARTER(d)	返回日期d是第几季节，返回 1 到 4	SELECT QUARTER('2011-11-11 11:11:11')-> 4
SECOND(t)	返回 t 中的秒钟值	SELECT SECOND('1:2:3')-> 3
SEC_TO_TIME(s)	将以秒为单位的时间 s 转换为时分秒的格式	SELECT SEC_TO_TIME(4320)-> 01:12:00
STR_TO_DATE(string, format_mask)	将字符串转变为日期	SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");-> 2017-08-10
SUBDATE(d,n)	日期 d 减去 n 天后的日期	SELECT SUBDATE('2011-11-11 11:11:11', 1)->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n)	时间 t 减去 n 秒的时间	SELECT SUBTIME('2011-11-11 11:11:11', 5)->2011-11-11 11:11:06 (秒)
SYSDATE()	返回当前日期和时间	SELECT SYSDATE()-> 2018-09-19 20:57:43
TIME(expression)	提取传入表达式的时间部分	SELECT TIME("19:30:10");-> 19:30:10
TIME_FORMAT(t,f)	按表达式 f 的要求显示时间 t	SELECT TIME_FORMAT('11:11:11','%r')11:11:11 AM
TIME_TO_SEC(t)	将时间 t 转换为秒	SELECT TIME_TO_SEC('1:12:00')-> 4320
TIMEDIFF(time1, time2)	计算时间差值	SELECT TIMEDIFF("13:10:11", "13:10:10");-> 00:00:01
TIMESTAMP(expression, interval)	单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和	SELECT TIMESTAMP("2017-07-23", "13:10:11");-> 2017-07-23 13:10:11
TO_DAYS(d)	计算日期 d 距离 0000 年 1 月 1 日的天数	SELECT TO_DAYS('0001-01-01 01:01:01')-> 366
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK('2011-11-11 11:11:11')-> 45
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星期二	SELECT WEEKDAY("2017-06-15");-> 3
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR('2011-11-11 11:11:11')-> 45
YEAR(d)	返回年份	SELECT YEAR("2017-06-15");-> 2017
YEARWEEK(date, mode)	返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推	SELECT YEARWEEK("2017-06-15");-> 201724

---

MySQL 高级函数

函数名	描述	实例
BIN(x)	返回 x 的二进制编码	15 的 2 进制编码:SELECT BIN(15); -- 1111
BINARY(s)	将字符串 s 转换为二进制字符串	SELECT BINARY "itcast";-> itcast
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE resultEND	CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。	SELECT CASE 　　WHEN 1 > 0　　THEN '1 > 0'　　WHEN 2 > 0　　THEN '2 > 0'　　ELSE '3 > 0'　　END->1 > 0
CAST(x AS type)	转换数据类型	字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE);-> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n)	返回参数中的第一个非空表达式（从左向右）	SELECT COALESCE(NULL, NULL, NULL, 'itcast.com', NULL, 'google.com');-> itcast.com
CONNECTION_ID()	返回服务器的连接数	SELECT CONNECTION_ID();-> 4292835
CONV(x,f1,f2)	返回 f1 进制数变成 f2 进制数	SELECT CONV(15, 10, 2);-> 1111
CONVERT(s USING cs)	函数将字符串 s 的字符集变成 cs	SELECT CHARSET('ABC')->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk))->gbk
CURRENT_USER()	返回当前用户	SELECT CURRENT_USER();-> guest@%
DATABASE()	返回当前数据库名	SELECT DATABASE(); -> itcast
IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。	SELECT IFNULL(null,'Hello Word')->Hello Word
ISNULL(expression)	判断表达式是否为空	SELECT ISNULL(NULL);->1
LAST_INSERT_ID()	返回最近生成的 AUTO_INCREMENT 值	SELECT LAST_INSERT_ID();->6
NULLIF(expr1, expr2)	比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1	SELECT NULLIF(25, 25);->
SESSION_USER()	返回当前用户	SELECT SESSION_USER();-> guest@%
SYSTEM_USER()	返回当前用户	SELECT SYSTEM_USER();-> guest@%
USER()	返回当前用户	SELECT USER();-> guest@%
VERSION()	返回数据库的版本号	SELECT VERSION()-> 5.6.34

MySQL 索引

# 索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在	SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

# 索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

普通索引

# 创建索引
CREATE INDEX indexName ON mytable(username(length)); // length 表示 索引字段的前length个字段建索引
//创建索引
create index id on B(A_ID);

# 修改表结构时添加
ALTER table tableName ADD INDEX indexName(columnName)

# 创建表时直接指定
CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

# 删除索引
drop index [indexname] on mytable

唯一索引

在普通索引基础上，所有建索引的索引名前 加上 unique
MySQL事务

显示索引

SHOW INDEX FROM table_name;

mysql 事务

含义

通过一组逻辑操作单元（一组DML——sql语句），将数据从一种状态切换到另外一种状态
MySQL 事务主要用于处理操作量大，复杂度高的数据。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

特点

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，在中间某个环节不会结束。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

相关步骤：

事务的分类：

# 隐式事务，没有明显的开启和结束事务的标志
      比如 insert、update、delete语句本身就是一个事务
# 显式事务，具有明显的开启和结束事务的标志
	1、开启事务
	取消自动提交事务的功能
	2、编写事务的一组逻辑操作单元（多条sql语句）
	insert,update,delete
	3、提交事务或回滚事务
例如
#开启事务
SET autocommit=0;
START TRANSACTION;
#编写一组事务的语句
UPDATE account SET balance = 1000 WHERE username='张无忌';
UPDATE account SET balance = 1000 WHERE username='赵敏';

#结束事务
ROLLBACK;
#commit;

SELECT * FROM account;

MySQL 执行引擎

首先简单了解下MySQL执行引擎，首先MySQL执行引擎分类比较多，InnoDB（重要）、MyIsam（重要）、Memory、Mrg_Myisam、Blackhole等，虽然看着多，不过在日常学习和使用过程中只需要掌握InnoDB和MyIsam即可。下面对比InnoDB和MyISAM的区别：

1.InnoDB（事务型数据库引擎）

• 优点：InnoDB是一个事务型的存储引擎，有行级锁定和外键约束，提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，设计目标是处理大容量数据库系统；
• 缺点：不支持全文索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表；
• 适用场景：经常更新的表，适合处理多重并发的更新请求，需要事务、外键；

2.MyISAM(分析型数据库引擎）

• 优点：支持全文类型索引，索引和记录分开存储，并存储了表的行数，所以select count(*)效率很高（不加where）；
• 缺点：不支持数据库事务，更新操作需要锁定整个表，不支持行级锁和外键；
• 适用场景：经常读取数据的场合，更新操作少；

MySQL 语句优化

• 那么如何提高数据库SQL语句执行速度呢？有人会说性能调优是数据库管理员（DBA）的事，然而性能调优跟程序员们也有莫大的关系。

• 技巧1 比较运算符能用 “=”就不用“<>”
  “=”增加了索引的使用几率。

• 技巧2 明知只有一条查询结果，那请使用 “LIMIT 1”
  “LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。

• 技巧3 为列选择合适的数据类型

能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盘和内存消耗越小越好嘛。

• 技巧4 将大的DELETE，UPDATE or INSERT 查询变成多个小查询
  能写一个几十行、几百行的SQL语句是不是显得逼格很高？然而，为了达到更好的性能以及更好的数据控制，你可以将他们变成多个小查询。

• 技巧5 使用UNION ALL 代替 UNION，如果结果集允许重复的话
  因为 UNION ALL 不去重，效率高于 UNION。

• 技巧6 为获得相同结果集的多次执行，请保持SQL语句前后一致
  这样做的目的是为了充分利用查询缓冲。

比如根据地域和产品id查询产品价格，第一次使用了：

那么第二次同样的查询，请保持以上语句的一致性，比如不要将where语句里面的id和region位置调换顺序。

• 技巧7 尽量避免使用 “SELECT *”
  如果不查询表中所有的列，尽量避免使用 SELECT *，它将以磁盘扫描方式取出单条数据的末尾，而字段方式则会直接取到数据项。

（1）SELECT *，需要数据库先 Query Table Metadata For Columns，一定程度上为数据库增加了负担。
但是实际上，两者效率差别不大。
（2）考虑到今后的扩展性。
因为程序里面你需要使用到的列毕竟是确定的， SELECT * 只是减少了一句 SQL String 的长度，并不能减少其他地方的代码。

• 技巧8 WHERE 子句里面的列尽量被索引

只是“尽量”哦，并不是说所有的列。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

• 技巧9 JOIN 子句里面的列尽量被索引

同样只是“尽量”哦，并不是说所有的列。

• 技巧10 ORDER BY 的列尽量被索引

ORDER BY的列如果被索引，性能也会更好。

• 技巧11 使用 LIMIT 实现分页逻辑

不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输。

• 技巧12 使用 EXPLAIN 关键字去查看执行计划

EXPLAIN 可以检查索引使用情况以及扫描的行。