1. MySQL-第一章-简介及安装

1、MySQL-DBA工作职责

1.1 开发DBA

1.2 运维DBA

1.2.1 初级运维

	安装  搭建

1.2.2 中级运维

	mysql数据库管理员 ：用户权限、索引管理 、储存管理、备份恢复、故障处理
1.2.3 高级运维
性能优化 ，集群高可用

2 Linux云计算运维-MySQL运维DBA

2.1 mysql简介安装 ***

2.2 MySQL体系结构和管理 **

2.3 MySQL SQL语句 **

2.4 MySQL基础优化-索引及执行计划 ***

2.5 MySQL储存引擎

2.6 MySQL日志管理 ***

2.7 MySQL备份复制 ***

2.8 MySQL主从复制 ***

2.9 MySQL主从复制架构演变-高可用及读写分离

2.10 MySQL主从复制架构演变-分布式架构

2.11 MySQL整体优化

2.12 NoSQL-Redis缓存技术

2.13 NoSQL-MongoDB分布式数据库技术

3、DBA 职业素养

3.1 人品

3.2 严谨

    规范操作，要有理论支撑或者实践检验

3.3 细心

    变更类的操作，多思考

3.4 心态

    能顶住压力。

==============================

3.5 熟悉操作系统

3.6 熟悉业务(开发)

3.7 熟悉行业

4、MySQL版本企业选择

4.1 规范 GA版本 ：稳定的发布版本
4.2 版本类型 E：企业版，收费版，提供SR（服务），偏传统行业 C：社区版，开源，互联网企业的选择

4.3 企业版本选择 一般新上的环境，会选择GA大约发布时间为6-12个月版本

4.4 企业主流版本 5.6版本：5.6.34 5.6.36 5.6.38 5.7版本：5.7.20 5.7.22 5.7.24

4.5 MySQL分支 Oracle MySQL MariaDB PerconaDB RDS 与 Oracle MySQL 最相似

4.6 软件下载 地址： https://downloads.mysql.com/archives/community/ RPM包：

二进制包：（上课使用的版本）解压就可以用

源码包：C++的源码，需要二次开发的时候，需要做统一配置管理的时候 研究源码

5 安装和基本配置

5.0 上传下载软件包

	#创建存放软件包的目录
[root@db-01 ~]# mkdir -p /data 

	#上传软件包
	#进行解压
[root@db-01 data]# tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

5.1 进行修改名称

root@db-01 data]# mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql 

5.2修改环境变量

[root@db-01 data]# vim /etc/profile

export PATH=/data/mysql/bin:$PATH 	#添加的内容

[root@db-01 data]# source /etc/profile		#生效环境变量

[root@db-01 data]# mysql -V 		#进行检测
	mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper 

5.3清理历史软件包，没有的不用清理

 #查看有没有历史软件包；会影响数据的初始化
[root@db-01 data]# rpm -qa |grep mariadb-libs   
	mariadb-libs-5.5.56-2.el7.x86_64 

#进行卸载
[root@db-01 data]# yum remove mariadb-libs  -y
Loaded plugins: fastestmirror 
Resolving Dependencies 
--> Running transaction check 

5.4 建用户，设置虚拟用户

#mysql管理用户的功能需要非root去进行管理，可以是虚拟用户，或者设置普通户不设置密码
[root@db-01 data]# useradd mysql 
[root@db-01 data]# id mysql uid=1000(mysql) gid=1000(mysql) groups=1000(mysql) 

5.5 初始化数据

5.5.0 版本初始化方式介绍
	5.6 版本的初始化程序：
		初始化完成后是无密码的 和无安全策略的
    	/data/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data

	5.7 版本的初始化程序：
		提示：版本5.7 以后初始化命令变为bin/mysqld命令，提供了两种初始化方式，

		第一种初始化方式：开启临时密码的安全策略
	    /data/mysql/bin/mysqld--initialize --user=mysql--basedir=/data/mysql --datadir=/data/mysql/data
	演示：
    	2019-04-03T03:40:15.595883Z 1 [Note] A temporary password is generated for root@localhost: **MWht)!4%sa,3**

		密码的复杂度进行了要求，对于密码的过期时间设置了限制（默认180天）

	第二种初始化方式：关闭临时密码的安全策略
	    /data/mysql/bin/mysqld--initialize-insecure --user=mysql--basedir=/data/mysql --datadir=/data/mysql/data

5.5.0 创建数据目录

mkdir -p /data/mysql/data

5.5.1 下载依赖包

[root@db-01 data]#yum install -y libaio-devel

5.5.2 初始化（系统）数据

	#5.7.20版本
[root@db01 data]# /data/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data
初始化结果

5.5.3 初始化参数介绍

--initialize-insecure： 是用无安全限制的方式进行数据初始化

--user=mysql          ：设置数据的管理用户

--basedir=/data/mysql ：软件的安装路径

--datadir=/data/mysql/data ：数据的存储位置

5.5.4 修改权限

[root@db01 mysql]# chown -R mysql.mysql /data

5.5.5 准备启动脚本和基础配置文件

	#5.5.5.1 准备sys-v启动脚本
[root@db01 support-files]# pwd
/data/mysql/support-files
[root@db01 support-files]# cp mysql.server /etc/init.d/mysqld

	#5.5.5.2 准备配置文件
vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=6
[mysql]
socket=/tmp/mysql.sock

#5.5.5.3 SYS-V启动数据库
	#启动
[root@db-01 ~]# /etc/init.d/mysqld start            
Starting MySQL.Logging to '/data/mysql/data/db-01.err'.
SUCCESS!

	#关闭
[root@db-01 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!

	#重启
[root@db-01 ~]# /etc/init.d/mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL. SUCCESS!

#5.5.5.4 准备sys-d（systemd）管理的脚本

vim /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
	#注意：将原来模式启动mysqld先关闭，然后再用systemd管理。
    systemctl  start/stop/restart/status   mysqld

#5.5.5.5 SYS-d启动数据库
#查看状态 
[root@db-01 ~]# #systemctl status mysqld.service

	#关闭服务
[root@db-01 ~]# #systemctl stop mysqld.service         

	#开启服务
[root@db-01 ~]# #systemctl start mysqld.service        

	#重启服务
[root@db-01 ~]# #systemctl restart mysqld.service    

5.6 连接数据库

mysql

5.7 设置root的初始密码

[root@db01 ~]# mysqladmin -uroot -p password 123

MySQL语句

1. 查看MySQL所有用户
select * from mysql.user

2. 添加用户
grant select on mindoc_db.* to 'li'@'%' identified by '12345';

2. 删除用户
delete from mysql.user where user='li' and host='%' ;

注释：
	delete：删除
	mysql.user：数据库的用户表
	where：判断
	user：用户名
	and：和
	host：主机，（%，表示所有）

SQL语句

1.数据操作类SQL语句

2.数据定义类SQL语句

3.数据控制类SQL语句

4.事物控制类SQL语句

5.程序化SQL语句

SELECT语句查询基本结构

介绍

SELECT语句是在SQL中常用的到语句，使用SELECT语句可以从数据表中或视图中进行查询，并将查询结果以表格的形式返回，以表格返回的结果称为结果集。

SELECT语句的主要结构如下

SELECT select_list
[ INTO new_talble ]
FROM table_name
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_by_ [ASC|DESC] ]

参数说明

select_list：指定需要查询返回的列。多个列之间使用逗号分隔。在选择列时也可以使用“*”符号来表示返回表中的所有列。
	
INTO new_table:创建新表并将查询行插入新表中。new_table表示指定新表的名称。

FROM table_name：指定需要查询的表，也称为来源表。这些来源表可能包括基表、视图、和连接表。
	FROM字句还可以包含连接说明，该说明定义了数据库用来在表之间进行导航的特定路径。

GROUP BY

GROUP BY group_by_expression
根据group_by_expression列中的值将结果分成组。例如。

示例

HAVING

HAVING :指定组或者聚合的搜索条件。逻辑上讲，HAVING字句从中间结果集对行进行筛选，这些中间结果集是用SELECT语句中的FROM WHERE或GROUP BY 字句创建的。
HAVING子句通常与GROUP BY字句一起使用，尽管HAVING字句前面不必有GROUP BY子句。

ORDER BY

ORDER By
	语句用于根据指定的列对结果集进行排序。 
	ORDER BY 语句默认按照升序对记录进行排序。
	ASC和DESC关键字用于指定行是按升序还是按降序排序。
	ORDER BY之所以重要，是因为关系理论规定除非已经指定ORDER BY，否则不能假设结果集中的行带有任何序列。
	如果结果集的顺序对于SELECT语句来说很重要，在该语句中就必须使用ORDER BY 子句。

示例

升序
	select * from kq order by rq

降序
	select * from kq order by rq DESC

单列查询

单列查询可以对表中某一列的数据进行查看。在SELECT语句中只要给出一个查看的列明就能实现单列查询。

SELECT goods_name FROM goods;

多列查询

使用SELECT语句进行多列查询的时候，只需要列出多个列的列名，多个列名之间需要使用逗号分隔。在查询结果中列的顺序，将以SELECT语句中指定列名的先后顺序显示。

SELECT goods_name,weight,goods_id FROM goods;

查询所有的列

在对数据表进行查询时，有时会对表中所有的列进行查询。如果表中列过多的话，在SELECT语句中指定所有的列会很麻烦，这时可以用“*”符号来代替所有的列。

SELECT * FROM goods;

别名的应用

创建数据表时字段一般都是使用英文单词来表示字段名。可以使用别名来增加阅读行。

四种方式

1.使用双引号创建别名
SELECT goods_name "商品名称",weight "价钱" FROM goods;

2.使用单引号创建别名
SELECT goods_name '商品名称',weight '价钱' FROM goods;

3.不使用引号创建别名
SELECT goods_name 商品名称,weight 价钱 FROM goods;

4.使用AS关键字创建别名
SELECT goods_name AS "商品名称",
weight AS "价钱"
 FROM goods;

连表查询，多表查询时出现相同的列名

多个表查询时可能会出现相同的列名，使用别名来进行区分列名是属于那个表中的。

SELECT user_address.mobile AS "订单表电话号码",
users.mobile AS "用户表中电话号码",
user_address.address,
users.user_id
FROM user_address,users
WHERE user_address.user_id = users.user_id

为计算结果设置别名列

在表中可以对多个列进行计算，计算后会产生一个新列，这时可以使用别名给该列指定列名，如果不指定该列列名，则默认该列为无列名

SELECT goods_name,
market_price AS "市场价",
cost_price AS "成本价",
(market_price - cost_price) AS "商品盈利"
FROM goods;

为使用聚合函数的列设置别名

在查询表中的数据时，有时会使用聚合函数对数据进行查询，查询后会产生一个新的列，可以使用别名来指定该列的列名。

SELECT MAX(market_price) AS "市场价",
MIN(cost_price) AS "成本价"
FROM goods;

删除重复数据

在某些情况下，数据表中会有可能出现重复数据。为了解决数据重复的问题就需要使用DISTINCT关键字

DISTINCT关键字可以从SELECT语句的结果中删除重复的行。如果没有指定DISTINCT关键字，那么将返回所有行，包括重复的行。在使用DISTINCT关键字删除重复记录时，须将DISTINCT关键字放在第一个字段的前面。

在SELECT来表中只能使用一次DISTINCT关键字，而且DISTINCT关键字必须放在第一位，不要在其后面添加逗号。
DISTINCT关键字不是指某一行，而是指不重复SELECT输出的所有列。

正常查询的出的行

SELECT consignee,address,mobile FROM orderform;

DISTINCT关键字后

SELECT DISTINCT consignee,address,mobile FROM orderform;

限制查询结果

在SELECT字句中使用“”符号，可以查询到数据表中所有的数据信息。“”在有时候也会给我们带来不便。比如数据量过大。

限制查询结果，但在不同的数据库中SQL命令也不尽相同。

在SQLserver数据库中限制查询结果

在SQL server数据库中使用SELECT语句时，可以使用TOP关键字来限制最多返回多少行。
TOP关键字可以指定返回表中的前n行数据。

SELECT TOP n FROM table;

参数说明：
TOP：指定从查询结果集中输出前n行，n是介于 0 ~ 4294967295 之间的整数。

在MySQL数据库中限制查询结果

在mysql数据库中需要使用LIMIT字句来限制查询结果。
LIMIT字句可以对查询结果的记录条数进行限定，控制他输出的行数。

使用LIMIT还可以从查询结果的中间取值。首先要定义两个参数，参数1是开始读取的第一条记录的编号，参数2是要查询记录的个数。
在查询结果中，第一个结果是从0开始的，而不是1.

输出查询的前5行数据

SELECT goods_name,brand_id 
FROM goods
LIMIT 5;

查询信息表从第3条数据开始的5条数据的信息

SELECT goods_name,brand_id 
FROM goods
LIMIT 2,5;

除此之外，还可以使用LIMIT n OFFSET m语句来限制查询结果，n是查询的行数，m是查询的起始位置。

查询商品表从4条数据开始的2条数据的信息。

SELECT goods_name,brand_id 
FROM goods
LIMIT 2 OFFSET 3;

在Oracle数据库中限制查询结果

在orale数据库中需要使用ROWNUM来计算行。

在Oracle数据库中，查看前5条数据

SELECT goods_name,brand_id 
FROM goods
WHERE ROWNUM <=5;

连接列值

连接列值将多个列中的数据合并到一列中。合并多个列后需要给该列设置一个别名，以方便用户查看，默认列名为“无列名”

GROUP_CONCAT

查看品牌表中，将品牌名称列和品牌分类列连接合并成一个心裂，列名为“品牌信息”

合并列前数据
	SELECT name,cat_nameFROM brand

合并列后
SELECT GROUP_CONCAT(name,cat_name) AS '商品信息' FROM brand GROUP BY name;

合并列时添加字符串

SELECT GROUP_CONCAT(name,'and',cat_name) AS '商品信息' FROM brand GROUP BY name;

查询中使用计算列

在使用SELECT语句查询数据时，可以使用算术运算符来辅助完成一些查询功能。

注意：上面运算符号的优先级是：先乘除（* / %），后加减（+ -）。相同优先级时，表达式采用从左到右计算顺序，可以采用括号明确优先级，以便减少失误。

数据原表

SELECT * FROM goods;

减法运算符“-”的应用。

计算每件商品的销售利润。

SELECT goods_id AS '商品ID',
goods_name AS '商品名称',
(shop_price - cost_price) AS '销售利润'
FROM goods;

乘法运算符“*”的应用

计算每件商品的销售额，每件商品的销售额可以通过销售数据量乘以单价来实现。

SELECT goods_id AS '商品ID',
goods_name AS '商品名称',
(shop_price * sales_sum)  AS '销售额'
FROM goods;

算术运算符的综合运用。

计算每件商品的销售利润

SELECT goods_id AS '商品ID',
goods_name AS '商品名称',
((sales_sum * shop_price) - (sales_sum * cost_price) ) / sales_sum  AS '销售利润'
FROM goods
where sales_sum != 0;

查询中使用表达式

在SELECT语句中也可以使用表达式。列入，给某列数据增加一个在字符串或使用表达式单独生成一个新列。

原表数据

SELECT goods_id AS '商品ID',
goods_name AS '商品名称',
cost_price AS '加价前'
FROM goods

数值表达式

使用数值表达式将进价增加50元，并使用别名将该列重新命名

SELECT goods_id AS '商品ID',
goods_name AS '商品名称',
(cost_price + 50) AS '加价后'
FROM goods

字符表达式

将商品信息表中销售数量和商场价格列添加单位“个”和“元”，并重新命名。
MySQL使用CONCAT()

SELECT goods_id AS '商品ID',
goods_name AS '商品名称',
CONCAT(sales_sum,'个') AS '销售数量',
shop_price AS '商场价格'
FROM goods;

sqlserver使用convert()

使用表达式创建新列

使用数值表达式在表中创建两个新的列。

MySQL

SELECT goods_id AS '商品ID',goods_name AS '商品名称',1+1 AS '结果',concat('字符','串') AS '拼接'
FROM goods

SqlServer

SELECT goods_id AS 商品ID,goods_name AS 商品名称,1+1,'字符'+'串列'
FROM goods;
或
SELECT goods_id AS 商品ID,goods_name AS 商品名称,1+1,'字符串列'
FROM goods;

Oracle

SELECT goods_id AS 商品ID,goods_name AS 商品名称,1+1,'字符'||'串列'
FROM goods;

掌握在SELECT查询语句中如何使用连接列值、计算列值以及使用表达式。

条件查询

在查询表中的数据时，往往不需要所有的数据，只需要查询出满足自己需要的数据，可以通过WHERE字句指定查询条件，来对数据进行查询。

WHERE字句

WHERE字句可以实现按一定条件查询的功能。

格式

SELECT <字段列表>
FROM <表名>
WHERE <条件表达式>

为了满足多种不同的查询，HWERE字句提供了丰富的搜索条件，WHERE字句的运算符

原表数据

使用比较运算符限制查询结果

使用“=”查询数据

等值条件用来判断用户指定的条件是否与表中的某条数据相同，如果相同则条件满足，将该条记录出来。等值条件使用“=”来判断。

在商品信息表中，找出ID为106的商品

SELECT * FROM goods WHERE goods_id = '106';

使用“>”查询数据

大院条件用来判断表中所指定条件的数据。

在商品信息表中，使用WHERE字句查询商品被点击数大于50的商品信息

SELECT * FROM goods
WHERE click_count > '50';

范围查询

范围查询时用来查询给定的两个值之间的数据，通常使用BETWEEN...AND和NOT...BETWEEN...AND来指定条件范围。

使用BETWEEN...AND查询条件时，指定的第一个值必须要小于第二个值。因为BETWEENT...AND实质是查询条件“大于或等于第一个值，并且小于或等于第二个值”的简写形式，即BETWEEN...AND要包括两端的值，等于比较运算符“>=...<=”.

1.查询两个值之间的数据，可以使用BETWEEN来实现，使用BETWEEN可以方便地编写查询条件。
2.在BETWEEN中也可以使用日期类型的数据作为查询条件。

本章使用BETWEEN...AND语句进行查询范围查询，可以查询数字、日期之间的范围，还可以用NOT..BETWEEN...AND语句来实现在不同两个数之间的数据查询。
还介绍了日期时间的查询语句以及用到的时间日期函数。

查询两个值之间的数据

在商品信息表中，查询时长价在1000元到3000元之间的商品信息。

SELECT goods_id AS '商品ID',goods_name AS '商品名称',market_price AS '市场价'
FROM goods
WHERE market_price BETWEEN 1000 AND 3000 ;

查询两个日期之间的数据

在图书信息表中，查询录入时间在2017-12-1和2018-12-1之间的图书信息

SELECT ISBN,BookName,INTime AS '数据录入时间'
FROM bookinfo_zerobasis
WHERE INTime BETWEEN '2017-12-1' AND '2018-12-1'

在BETWEEN中使用日期函数

SQLserver数据库：通过GETDATE()函数和DATEADD()函数，获取当天的日期和前一天的日期，在通过使用BETWEEN...AND来查询出这两个日期之间的数据。

MySQL数据库：通过NOW()和DATE_ADD()函数，获取当前日期和前一天数据,在通过使用BETWEEN...AND来查询出这两个日期之间的数据。

查询数据录入时间在昨天和今天之间的图书信息。
MySQL

SELECT ISBN,BookName,INTime AS '数据录入时间' 
FROM bookinfo_zerobasis
WHERE INTime
BETWEEN 
DATE_ADD(date_format(NOW(),'%Y-%m-%d'),INTERVAL -1 DAY)
AND
NOW();

SQLserver

SELECT ISBN,BookName,INTime AS '数据录入时间' 
FROM bookinfo_zerobasis
WHERE INTime
BETWEEN 
DATEADD(DAY,-1,GETDATE())
AND
GETDATE();

查询不在两个数之间的数据

在商品信息中市场价不在2000元到3000元之间的商品信息。

SELECT ISBN,BookName,INTime AS '数据录入时间' 
FROM bookinfo_zerobasis
WHERE INTime
BETWEEN 
DATEADD(DAY,-1,GETDATE())
AND
GETDATE();

日期时间查询

在不同的DBMS中，系统都提供了许多用于处理时间和日期的函数，通过这些函数可以实现计算特定日期和时间。

转换日期格式

有时候数据库中存储的日期可能回事不规范的日期形式。为了方便查看需要将日期转换为四位数的年份。

把长日期格式数据转换为短日期格式数据

CONVERT函数可以将日期转换为yyyy-mm-dd格式时间。
SQLserver数据库并不支持分开的TIME、DATE和TIMESTAMP数据类型，而是支持单一的DATETIME数据类型，用于定义保存符合的日期和时间值。

在SQLserver中convert函数转换格式：

CONVERT(date_type[(length)] , expre , style)

参数说明：
	date_type：要转换的数据类型
	expre：DATETIME类型数据
	style：指定转换形式。取值不同则对应的日期，时间格式将不同。

在MySQL中convert函数转换格式

CONVERT(x,type)

原表

提取MySQL日期类型格式

SELECT ISBN,bookname,
CONVERT(INTime,date) AS '日期' 
FROM bookinfo;

将日期格式中“-”转换为“/”

SELECT ISBN,bookname,
REPLACE(CONVERT(INTime,date),'-','/') AS '日期' 
FROM bookinfo;

计算两个日期相差的天数

计算两个日期的间隔天数，可以使用DATEDIFF()函数实现。

DATEDIFF()函数格式语法

SQLserver中DATEDIFF()函数语法：
语法：	
	DATEDIFF(datepart,stratdate,enddate)
注释：
	datepart：规定了应在那一日期部分计算间隔差额的参数
	stratdate：计算的开始日。其返回值为datetime或smalldatetime值或日期格式字符串的表达式。
	enddate：计算的终止日志。其返回值为datetime或smalldatetime值或日期格式字符串的表达式。

MySQL中DATEDIFF()函数的语法：
语法：
	DATEDIFF(expr1,exper2)
注释：
	expr1：起始时间
	expr2：结束时间
	expr1和expr2为日期或date-and-time表达式。计算中只用到这些值的日期部分。

库源数据

计算图书出版社表中图书出版的日期与该书下一次出版日期之间相差的天数。
MySQL

SELECT 书号,书名,
CONVERT(出版日期,DATE) AS '出版日期' ,
CONVERT(下一次出版日期,DATE) AS '下一次出版日期',
DATEDIFF(下一次出版日期,出版日期) AS '相差天数'
FROM bookpub

SQLserver

SELECT 书号,书名,
CONVERT(char(10),出版日期,120) AS '出版日期' ,
CONVERT(char(10),下一次出版日期,120) AS '下一次出版日期',
DATEDIFF(day,下一次出版日期,出版日期) AS '相差天数'
FROM bookpub

按指定日期查询数据

1.DAY()函数

DAY()函数返回代表指定日期的天的日期部分的整数。

语法：
	DAY(DATE)
注释：
	date：为时间格式的表达式
	DAY()：函数

用法

SQLserver用法：
	SELECT DAY(0) AS 1 ,DAY('02/0/2019') AS 2;
注释：
	SQLserver将date为0解释为01/01/1900。

MySQL用法：
	SELECT DAY('2020-2-2') AS '取值',DAY(now()) AS '现在'

SQLserver和MySQL中的DAY()的排序方式不一样，需要注意。

2.MONTH()函数

MONTH()：函数实现按月查询数据。取出月份。

月份

语法：
	MONTH(date)
注释：
	

SQLserver用法：
	SELECT "month number" = MONTH('02.03.2018')

MySQL用法：
	SELECT MONTH('1432-12-02')

3.YEAR()函数

YEAR()函数用于返回表示指定日期中的年份的整数。

语法：
	YEAR(date)

MySQL用法：
	
SELECT YEAR('2012-12-22')

查询图书信息表中“零基础系列”图书的出版日期在2017年10分的图书信息

SELECT bookname,type,pdate 
FROM bookinfo
WHERE
YEAR(pdate) = '2017' AND MONTH(pdate) = '10' 
AND type = '零基础系列';

使用逻辑运算符过滤数据

如果想把几个单一条件组合成一个符合条件，这就需要使用逻辑运算符NOT、AND和OR，才能完成符合条件查询。

1.使用AND运算符

在查询表是，如果要想满足两个给定的条件，可以在WHERE字句中使用AND运算符来实现。AND运算符表示“与，并且，和”的关系，再进行查询时，既要满足给定的第一个条件，也要满足给定的第二个条件，如果不满足两个查询条件的其中一个，这条记录就会被排除掉。

在定义查询语句时，在WHERE字句中可以设置多个筛选条件，可以同时使用多个AND运算符连接多个条件表达式，只有同时满足所有查询条件的记录才会被查询出来。

and运算符的真值表

在商品信息表中，查找商品的本店价格大于300元并且小于6000元的所有商品的ID、名称和本店价格。

SELECT goods_id AS '商品ID',
goods_name AS '名称',
shop_price AS '本店价格'
FROM goods
WHERE shop_price > '3000' AND shop_price < '6000';

在商品信息表中，查询商品的点击数量大于20次，库存数据为1000、本店价格大于2000元的所以商品信息。

SELECT goods_name AS '商品名称',
click_count AS '点击率',
store_count AS '库存数量',
shop_price AS '本店价格'
FROM goods 
WHERE click_count > '20' AND store_count = '1000' AND shop_price > '2000';

使用OR运算符

在查询表时，如果想要满足两个给定条件的其中一个时，可以在WHERE子句中使用OR运算符来实现。OR运算符表示“或”的关系，即满足第一个给定的条件或者满足第二个给定的条件，如果不满足两个查询条件中的任何一个，这样的记录就会被排除掉。

OR运算符的真值表

在定义查询语句时，在WHERE子句中可以同时使用多个OR运算符连接多个条件表达式，只要满足任何一个查询条件的记录就会被查询出来。

在图书信息表红，查询出图书名称为“零基础学java”或“零基础学PHP”的图书书号、图书名称、作者和价格

SELECT ISBN AS '书号',
BookName AS '名称',
Writer AS '作者',
Price '价格'
FROM bookinfo_zerobasis
WHERE BookName = '零基础学Java' OR BookName = '零基础学PHP'

在图书信息中，查询出图书名包含“PHP”“Oracle”“Android”的图书名称、价格、出版时间

SELECT BookName AS '名称',
Price AS '价格',
pDate as '出版时间'
FROM bookinfo
WHERE BookName LIKE '%PHP%' OR BookName LIKE '%oracle%' OR  BookName LIKE '%android%' 

使用NOT运算符

在查询表时，如果需要查询不满足给定条件的数量，可以在WHERE字句中使用NOT运算符来实现。NOT运算符表示“非”的关系，即不满足所给定的条件。

在商品信息表中，查询商品的库存量不是1000的所有商品ID、商品名称、商品库存数量。

SELECT goods_name AS '名称',
store_count AS '存货数量',
goods_id AS '商品ID'
FROM goods
WHERE NOT store_count = '1000';
或
SELECT goods_name AS '名称',
store_count AS '存货数量',
goods_id AS '商品ID'
FROM goods
WHERE store_count != '1000';
或
SELECT goods_name AS '名称',
store_count AS '存货数量',
goods_id AS '商品ID'
FROM goods
WHERE store_count <> '1000';

逻辑运算符的优先级

在查询中也可以将AND运算符、OR运算符和AND运算符综合起来使用，但这3个运算符是有不同的运算顺序的。
对运算符优先级别的了解，在使用时非常重要。如果运算符使用不当，在使用时就可能达不到预期的效果。

SQL在处理OR运算符之前会优先处理AND运算，可以使用小括号对运算符和查询条件进行分组。
因为小括号具有比AND、OR和NOT运算符更高的优先级，所以在使用不同的运算逻辑符时，都应该使用小括号对运算符和查询条件进行明确分组。

优先级按照由高到低的排列顺序

在商品信息表中，查询出商品分类id为191，或商品分类ID为123，并且商品价格大于2000元的所有商品

SELECT cat_id,goods_name,shop_price
FROM goods
WHERE (cat_id = '191' OR cat_id = '123') AND shop_price > '2000';

在图书信息表中，查询出图书名称包含PHP和JSP，并且不是“机械工业出版社”出版的所有图书名称，出版社和作者信息

SELECT BookName,publisher,Writer
FROM bookinfo
WHERE (BookName LIKE '%PHP%' OR BookName LIKE '%JSP%')
AND publisher != '机械工业出版社';
或者
SELECT BookName,publisher,Writer
FROM bookinfo
WHERE (BookName LIKE '%PHP%' OR BookName LIKE '%JSP%')
AND not publisher = '机械工业出版社';

在商品信息表中，查询出所有商品分类不是191和123的所有商品信息。

SELECT cat_id,goods_name,shop_price
FROM goods
WHERE (NOT cat_id = '191') AND (NOT cat_id = '123')
或者
SELECT cat_id,goods_name,shop_price
FROM goods
WHERE ( cat_id != '191') AND ( cat_id != '123')

使用IN操作类型过滤数据

在对表中的数据进行查询时，有时需要查询出多个条件中满足一个条件的数据，这种情况使用OR运算符，但是对于比较多的条件来说使用OR运算符并不方便。此时可以使用IN操作符代替OR运算符来完成查询任务。

使用IN查询数据

使用IN操作符可以判断某个字段的信息是否在指定的集合中。如果字段的值在集合中，则满足查询条件，该记录将被查询出来，如果不在集合中，则不满足查询条件。

在IN操作符后的值列表中不但可以使用数值类型数据，还可以使用字符类型数据。

在IN操作符后的值列表中还可以使用算术表达式，

在使用IN操作符对数据进行查询时，不但可以使用数值类型和字符类型的数据作为值列表。还可以使用列名作为值列表。

使用IN操作符查询数据的基本语法格式:

SELECT name
FORM table
WHERE column_name IN (value1,value2,...)

注释：
	name：表示需要查询的列名
	table：表示需要查询的表名
	column_name：表示需要指定查询条件的列
	value：表示值列表

在商品信息表中，查询出分类为191,123和131的商品分类

SELECT cat_id,goods_name,shop_price
FROM goods
WHERE cat_id  in (191,123,131);

在商品品牌表中，查询出商品品牌为“OPPO,维维,湾仔码头,华硕/ASUS”的商品品牌名称和分类名称

SELECT name,cat_name
FROM brand
WHERE name IN ('OPPO','维维','湾仔码头','华硕/ASUS')

在IN中使用算术表达式

在商品信息表中中，查询出商品本店价格为3799-100,3799,3799+100三种价格的商品名称和本店价格。

SELECT goods_name,shop_price 
FROM goods
WHERE shop_price IN (3799-100,3799,3799+100);

在IN中使用列进行查询。

在商品信息表中，查询出商品的时长价格或本店价格为3899的商品名称、商品市场价格和本店价格。

SELECT goods_name,market_price,shop_price 
FROM goods
WHERE 3899 IN (market_price,shop_price);

使用NOT IN查询数据

NOT IN操作符可以查询出给定条件以外的数据。

利用NOT IN操作符查询条件的基本语法

SELECT NAME
FROM table
WHERE column_name NOT IN (value1,value2,...)

注释：
	NAME：表示需要查询列的列名
	table：表示需要查询的表名
	column_name：表示需要指定查询条件的列
	value：表示值列表

在图书信息表中，查询出版日期不是017年8月和2017年9月的所有图书名称、作者信息、图书出版日期

SELECT pdate,bookname,writer
FROM bookinfo_zerobasis
WHERE pDate NOT IN ('2017年8月','2017年9月');

使用NOT IN查询后两行数据

使用NOT IN 查询后两行的数据，前提条件时需要知道该标准中有多少行。

在订单表中红共有10条订单记录，查询后两行数据的订单、订单号和总订单价

SELECT order_id,order_sn,total_amount
FROM orderform
WHERE order_id NOT IN 
(select order_id from  (select order_id from orderform LIMIT 8) AS sc);

格式化结果集

格式化结果集通过使用一些函数，将结果集中的某列数据进行格式化。以方便用户查看。例如，将结果集中的日期进行格式化，对小数的位数进行格式化等。

1.格式化日期

在SQL server数据库中格式化日期

在SQL server中，日期类型通常是以 “yyyy-mm-dd hh:mm:ss.mmm ”的形式存在的，有时候为了使用用户查看方便，需要对日期进行格式化。对日期进行格式化可以使用convert()函数来实现。

CONVERT()函数将某种数据类型的表达式转换为指定样式的另一种数据类型。在SQL server数据库中，应用CONVERT()函数语法格式如下

CONVERT (data_type [(length)], expression [, style])

参数说明：
data_type：规定目标数据类型
length：nchar、nvarchar、char、varchar、binary或是arbinary数据类型的可选参数。用于规定输出的长度。
expression：需要转换的值
style：可选参数，规定日期/时间的输出格式。

使用CONVERT()函数以“hh:mm:ss” 格式显示当前时间。

SELECT CONVERT(VARCHAR,GETDATE(),108) AS nowtime;

通过使用CONVERT()函数来对用户注册时间列中的日期进行格式化
在users用户信息表中，查询前6条记录的用户id，注册邮箱和注册时间，并对用户的注册时间中的日期进行格式化

SELECT TOP 6 user_id,emall,CONVERT(VARCHAR(10),reg_time,120) AS reg_time FROM users;

在mysql数据库中格式化日期

在mysql中，对日期进行格式化使用的是DATE_FROMAT()函数，
该函数用于以不同的格式显示该日期/时间数据。

DATE_FORMAT(date,fromat)

参数说明：
date：一个合法的日期
format：规定日期/时间的输出格式

在users用户信息表中，查询前6条记录的用户id、注册邮箱、注册时间，并将注册时间列中的时间输出为“年/月/日”的格式

SELECT user_id,email,DATE_FORMAT(reg_time,'%y/%m/%d') AS reg_time 
FROM users LIMIT 6;

在Oracle数据库中格式化日期

在Oracle中，日期的默认格式为“26-3月 -17”（2017年3月26日），这种格式让人看得不习惯，所以此时就需要通过相应的函数对这个日期进行格式化。

对日期格式化使用的是TO_CHAR()函数。该函数用于指定的数据按照指定的格式转换为字符串型数据

TO_CHAR(expression,fromat)

参数说明：
expression：需要转换的日期或数字
format：转换后的格式

2.8 DD 指定日期在当月中第几天(范围：1-31)
SQL> select to_char(sysdate,'DD YYYY-MM-DD PM hh24:mi:ss ') from dual;
TO_CHAR(SYSDATE,'DDYYYY-MM-DDP')
------------------------------
27 2015-12-27 下午 18:12:08

2.9 DDD 指定日期在当年中的第几天(范围：1-366)
SQL> select to_char(sysdate,'DDD YYYY-MM-DD PM hh24:mi:ss ') from dual;
TO_CHAR(SYSDATE,'DDDYYYY-MM-DD')
------------------------------
361 2015-12-27 下午 18:13:15

2.10 DAY 指定日期在周中的名称
select to_char(sysdate,'Day YYYY-MM-DD PM hh24:mi:ss ') from dual;
TO_CHAR(SYSDATE,'DAYYYYY-MM-DD')
-----------------------------------
星期日 2015-12-27 下午 18:46:23

2.11 MON 返回指定日期的月份简写
SQL> select to_char(sysdate,'MON YYYY-MM-DD PM hh24:mi:ss ') from dual;
TO_CHAR(SYSDATE,'MONYYYY-MM-DD')
----------------------------------
12月 2015-12-27 下午 18:47:33

2.12 MONTH 返回指定日期的月份全称
SQL> select to_char(sysdate,'MONTH YYYY-MM-DD PM hh24:mi:ss ') from dual;
TO_CHAR(SYSDATE,'MONTHYYYY-MM-')
--------------------------------
12月 2015-12-27 下午 18:48:44

2.13 MM 返回指定日期的月份(范围：1-12)
SQL>  select to_char(sysdate,'MM') from dual;
TO_CHAR(SYSDATE,'MM')
---------------------
12

2.14 HH 小时(范围：1-12)
SQL>  select to_char(sysdate,'HH') from dual;
TO_CHAR(SYSDATE,'HH')
---------------------
06

2.15 HH12 小时(范围：1-12)
select to_char(sysdate,'HH12') from dual;
TO_CHAR(SYSDATE,'HH12')
-----------------------
06

2.16 HH24 小时(范围：0-23)
SQL> select to_char(sysdate,'HH24') from dual;
TO_CHAR(SYSDATE,'HH24')
-----------------------
18

2.17 MI 返回指定时间的分钟(范围0-59)(注意java表示分钟是mm，月份是MM，而Oracle表示分钟是mi，月份是MM)
SQL> select to_char(sysdate,'MI') from dual;
TO_CHAR(SYSDATE,'MI')
---------------------
59

2.18 SS 返回指定时间的秒数(范围：0-59)
SQL> select to_char(sysdate,'SS') from dual;
TO_CHAR(SYSDATE,'SS')
---------------------
13

2.19 FF[1-9] 返回毫秒数，可指定长度1-9，默认6位 （Oracle中date类型没有毫秒，用这个格式会报错，timestamp才有毫秒）
复制代码
SQL> select to_char(SYSTIMESTAMP,'FF9') from dual;
TO_CHAR(SYSTIMESTAMP,'FF9')
---------------------------
487000000

SQL> select to_char(SYSTIMESTAMP,'FF4') from dual;
TO_CHAR(SYSTIMESTAMP,'FF4')
---------------------------
4740

SQL> select to_char(SYSTIMESTAMP,'FF6') from dual;
TO_CHAR(SYSTIMESTAMP,'FF6')
---------------------------
032000

在users用户信息表中，查询前6条记录用户id和出生日期并将该日期转换为“年-月-日”的格式

SELECT users_id,birthday
TO_CHAR(birthday,'YYYY-MM-DD')
FROM users
WHERE FOWNUM <= 6;

数据表的数值类型转换

在数据表中，某些数值数据会以字符串的类型保存，而使用时，可能需要数值类型，这时就可以使用CAST()函数进行转换。

SQL server：CAST()函数

在SQLserver数据库中，CAST()函数语法：

CAST(expression AS data_type)

参数说明：
	expression：任何有效的SQL server表达式
	AS：用于分隔两个参数，前一个参数是要处理的数据，后一个参数是要转换的数据库类型
	data_type：目标系统所提供的数据类型，包括bigint和sql_variant。不能使用用户定义的数据类型。
	例如，将字符串“236”转换为整数

如果试图进行不可能的转换，如将含有字母的varchar类型转换为int类型，SQL server会显示一条错误信息。

应用CAST()函数将浮点型数据转换为整数，小数部分会舍去而不会进行四舍五入。

如果通过CAST()函数将类似“11.2”的字符串转换为decimal类型，首先需要定义decimal的精度和小数位数。精度是数字的总和，包括小数点左边和右边数字位数的总和。而小数位数是小数点右边的位数。

在users表中，查询前6条记录的用户ID，注册邮箱和用户消费总额，并将用户消费总额列中的数据格式转换为整数。

SELECT TOP 6 user_id,email,CAST(total_amount as int) as total_amount 
FROM users;

2.MySQL：CAST()函数

就是CAST(xxx AS 类型), CONVERT(xxx,类型)。

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

格式：

CAST(expression AS data_type)

在users表中，查询前6条记录的用户ID，注册邮箱和用户消费总额，并将用户消费总额列中的数据格式转换为整数。

SELECT user_id,email,CAST(total_amount AS SIGNED)
FROM users
LIMIT 6;

或

SELECT user_id,email,CAST(total_amount AS DECIMAL(20,0))
FROM users
LIMIT 6;

Oracle：CAST()函数

在Oracle数据库中，CAST()函数的语法：

CAST(expression AS data_type)

参数说明：
	expression：任何有效的表达式
	data_type：转换后的数据类型
	

MySQL备份

分为两种备份方式逻辑备份mysqldump和物理备份xbk

mysqldump逻辑备份

基于sql语句进行备份，备份出来的是sql语句，可读性高，文本格式，便于备份处理。
压缩比例高，占用磁盘空间少

mysqlbinlog
是导出MySQL的binlog日志，可进行恢复

mysql备份命令

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
备份压缩并添加时间戳
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

参数介绍

u 用户
p 密码
A 全备参数
P 端口
h IP地址
--set-gtid-purged=OFF 解决备份时的警告， 构建主从复制时，不需要加这个参数
-R 备份存储过程及函数
--triggers 备份触发器
-E 备份事件
-F 在备份开始时,刷新一个新binlog日志
--master-data=2 以注释的形式,保存备份开始时间点的binlog的状态信息
--single-transaction
（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定
（1）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能

查看binlog在开启状态

mysqlbinlog配置文件的存放位置
	/etc/my.cnf
配置文件内容介绍：

server_id=3306
	主要是在主从复制过程中必须要加的,但是在5.7版本中,要用以下参数(log_bin),开启binlog日志,即使是单机也是必加的
	
log_bin=/data/binlog/mysql-bin
(1)开启二进制日志功能
(2)设置二进制日志目录路径及名称前缀
	
binlog_format=row
行模式，DML记录的方式，

重启生效

MySQL定时备份shell脚本

#!/bin/bash
# 备份保留时间 14天
BTIME=14
# 文件名
DATENAME=`date -d '-1 days' '+%Y-%m-%d_%H:%M:%S'`
# 备份目录
DIR="/backup/"
LSML="/tmp/SQL"
# 数据库信息
USER="root"
PWD="meg@314159"
DATABASE="test"
#判断数据备份路径是否存在
if [ ! -d $DIR ];then
mkdir -p $DIR
fi

if [ ! -d $LSML ];then
mkdir -p $LSML
fi

# 备份并压缩
echo "${DATENAME}开启逻辑备份"
mysqldump -u$USER -p$PWD -A -R --triggers --single-transaction|gzip > /backup/mysql_${DATENAME}.gz

# 查找超过14天的 进行移动到临时目录中
RMLIST=`find $DIR -name 'mysql_20*.sql.gz' -mtime +$BTIME`
for RMI in $RMLIST
do
mv $RMI $LSML
echo "mv $RMI 到 $LSML"
done;

# 查找超过21天的 进行删除
RMLIST2=`find $LSML -name 'mysql_20*.sql.gz' -mtime +21`
for RMI2 in $RMLIST2
do
rm -rf $RMI2
echo "rm $RMI2"
done