数据库笔记


MySQL

关系型数据库:建立在关系模型基础上,借助数学中的集合代数等概念,把数据进行规范化的管理。(数据库是一系列的二维数组的集合)

常用的数据类型:

SQL语言:

1.数据定义语言 DDL (DATA DEFINTION LANGUAGE):DROP、CREATE、ALTER等语句

2.数据操作语言 DML (DATA MANIPULATION LANGUAGE):INSERT、 UPDATE、DALETE语句

3.数据查询语言 DQL (DATA QUERT  LANGUAGE): SELECT

4.数据控制语言 DCL (DATA CONTROL LANGUAGE): GRANT、REVOKE、COMMIT、ROLLBACK等语句

 

使用数据库

USE 库名

创建数据库

CREATE DATABASE 库名

创建表的语法

CREATE TABLE 表名 字段名 数据类型

插入数据的语法

INSERT INTO 表名[ (字段名) ] VALUES ('对应的数据')

更新数据的语法

UPDATE 表名 SET 修改的条件 WHERE 条件判断

例:update course  set t_id=804 where c_no='3-245'

查询表结构

DESC 表名

删除数据语法:使用DROP

DROP DATABASE 库名;

MySQL使用的SQL语言是用于访问数据库最常用的标准化语言。

MySQL分为社区版和商业版,体积小,速度快,成本低,成为中小型企业选择的主流数据库。

MySQL搭配PHP和apache组成良好的开发环境。

MySQL的优势:

  1. 速度:速度快
  2. 价格:MySQL对于多数人来说是免费的,对于企业来说,只需要很少的投入就能获得较多较完整的功能
  3. 容易使用:Oracle等大型的数据库相比,语法更加简单,容易上手
  4. 可移植性强:MySQL属于跨平台的数据库管理系统,可以用在多个平台之上,例如Windows、Linux、unix等
  5. 有着丰富的接口:Java、PHP、Perl、python、ruby、C...都有访问的API接口
  6. 支持查询语句:MySQL可以用标准的SQL语法,支持开放式数据库连接
  7. 安全性和联系性:有着十分灵活地权限和密码系统,可以在任何有网络的情况下连接数据库,提升共享效率

MySQL服务器工具
mysqld:启动
mysqld_multi :可以启动或者是关闭在服务器里面多台MySQL的工具。
myisamchk:用来描述、检查、优化和维护myISAM表的工具。
mysqlbug:通过这个来发布MySQL存在的bug。
mysql.server:服务器启动脚本,用于启动特定级别的服务器,调用mysqld-safe来启动mysql服务器
mysql_install_db:用于数据库的初始化,一般在数据库第一次安装时使用。

MySQL客户端工具
myisampack:压缩MyISAM表产生更小只读表的工具
mysql:交互式输入SQL语句或者从文件以批处理形式执行命令的工具
mysqlaccess:检测主机名,用户名,用户权限的作用
mysqladmin:用来修改密码、重载授权表,将表刷新到硬盘上,重新刷新日志,检索版本号,检索进程,检索服务器的状态信息。
mysqlbinlog:通过二进制日志的工具,来帮助恢复数据
mysqlcheck:检查、修复、分析以及优化表的表维护客户端程序
mysqldump:客户端备份工具
mysqlhotcopy:用于在服务器运行状态下,备份数据(myISAM表)
mysqlshow:显示数据库、表、列以及索引相关信息的程序
perror显示系统或者MySQL错误带含义的工具

库的含义
mysql:负责存储数据库用户的权限,用户信息,关键字等mysql管理信息。
information_schcma:保存关于mysql服务器所维护的所有其他数据库的信息,数据库名,表,表栏的数据类型和访问权限。
performance_schcma:MySQL5.5用于收集服务器性能参数

 

数据库存储引擎

数据库存储引擎基于数据底层,数据使用数据存储引擎来进行对数据的增、删、改、查

不同的存储引擎提供不同的存储机制,支持不同索引、锁定水平。使用特定的存储引擎可以获取特定的功能。

MyISAM   InnoDB   Mcmory

mysql> SHOW ENGINES\G                      #查询引擎
Engine: InnoDB                             #引擎
Support: DEFAULT                           #是否支持
Comment: Supports transactions, row-level locking, and foreign keys            #
Transactions: YES                              #是否支持事务
XA: YES                                              #是否支持分布式
Savepoints: YES                                #是否支持保存点

 

MyISAM特点:

  1. mysql5.5版本之前数据表的默认存储引擎
  2. MyISAM读取速度快,占用资源少,不支持事务,不支持外键,支持全文索引,空间索引
  3. 读写相互阻塞
  4. MyISAM只能缓存索引,不能缓存数据

MyISAM使用场景:

  1. 再不大量写入数据的情况,只读取数据的情况,使用此种存储引擎
  2. 不需要事务的情况下,可以使用   例如:银行转账
  3. 并发较低,数据修改较少的情况
  4. 硬件配置较差可以考虑使用MyISAM存储引擎

 

InnoDB特点:

  1.  5.5版本之后是MySQL默认存储引擎,支持行级别锁定,支持外键,支持事务
  2. 对于事物来说,具有提交,回滚和崩溃恢复能力的事务安全存储引擎,可处理大型数据量,性能高,对硬件要求较高
  3. 具有较高的缓存性能,能缓存索引,也能缓存数据
  4. 使用InnoDB时,会在mysql初始化存放配置文的目录生成一个名为ibdata1的10MB大小的自动扩展的数据文件,以及两个名字叫ib_logfile0和ib_logfile1的5MB大小的日志文件。

InnoDB使用场景

  1. 支持外键,需要关联其他表时需要使用InnoDB
  2. 需要支持事务的业务,以及高并发业务
  3. 数据更新较为频繁业务,   例如:微博,微信
  4. 数据一致性较高的业务,   例如:转账,充值

memory特点

  1. Memory存储引擎将数据存储到内存中,可以提供快速的查询和访问
  2. Memory支持hash和btree索引,不支持BLOB和TEXT数据类型,支持AUTO_INCREMENT个对可包含控制的列的索引
  3. 当不需要memory表的内容时,释放memory使用的内存,执行delete from或者truncate table删除数据,或者删除表

 

功能              MyISAM           InnoDB                Memory

存储限制          256TB             64TB                 RAM

支持事务           NO               YES                  NO

支持全文索引       YES               NO                   NO

支持数索引         YES               YES                  YES

支持哈希缓存        NO               NO                   YES

支持数据缓存        NO               YES                   NO

支持外键            NO               YES                   NO

表是数据库存储数据的基本单位

一个表可以包含若干个字段,但是只能有一个存储引擎

存储引擎语法:

CREATE TABLE <表名>

字段1,数据类型[完整性约束条件],
字段2,数据类型[完整性约束条件],
......
);
完整性约束条件是对字段进行限制,要求对该属性进行操作的时候符合约束条件的内容,如果不满足约束条件将不能执行该操作。


约束条件             作用

PRIMARY KEY          标识该属性为该表的主键,可以唯一地标识对应的数据

FOREIGN KEY          标识该属性为表的外键,是与之联系的某表的主键

NOT NULL             标识该属性不能为空

UNIQUE               标识该属性值唯一

AUTO_INCREMENT       标识该属性自动增加

DEFAULT              为该属性设置默认值

主键 (PRIMARY KEY)

  1. 要求主键列的数据是唯一的,不允许为空值
  2. 主键可以是单一字段,也可以是多个字段的组合。

单字段主键

1.直接添加到数据类型的后面

字段名 数据类型 PRIMARY KEY

  1. 在定义完所有列之后指定主键

[CONSTRAINT 约束名] PRIMARY KEY (字段名)

 

联合主键只有在最后添加才能成功,如果单独添加会报错

 

外键约束

用来建立两个表数据之间的的联系,外键可以是一列或者多列,谁创建外键,谁是子表

  1. 数据类型需要一致
  2. 子表关联的父表上的字段必须是父表的主键

创建外键的语法

语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)

[CONSTRAINT 外键名] FOREIGN KEY [字段名1,字段名2...] REFERENCES <主表名> 主键列1 [主键2...]

CONSTRAINT FK_ID FOREIGN KEY (user_id) REFERENCES tb_user (id)

:关联复合主键的时候,一定要先关联第一个字段,否则不能成功

数据库中,一个表字段可以是本表的主键,同时,也可以是其他表的外键,如果某字段是本表的主键,那么该字段引用的父表中的字段一定是父表的主键

保证数据的一致性

 

非空约束 NOT NILL

指字段的值不能为空值。

字段名  数据类型  NOT NULL

 

唯一性约束 UNIQUE

设置该字段的值不能重复

语法

[CONSTRAINT 约束名] UNIQUE (字段名)

字段名 数据类型 UNIQUE

 

默认约束条件

在创建的时候如果没有给设置默认值的字段赋值,那么数据库会自动给这个字段插入默认值

语法:

字段名 数据类型 DEFAULT (设置的默认值)

 

设置标的属性自动增加(AUTO_INCREMENT)

特殊约束条件,用于为表中插入新的记录生成唯一ID,一个表只允许有一个自增约束

语法:

字段名 数据类型 AUTO_INCREMENT

 

清除表数据

DELETE FROM 表名                                 不释放表空间

TRUNCATE TABLE 表名                            释放表空间

truncate 在功能上和不带WHERE判断的DELETE语句相同

truncate速度比delete快,占用资源少

 

MySQL数据库开启远程连接

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123.com' WITH GRANT OPTION;

然后记得一定要刷新!!!

flush privileges;

 

修改表

修改表名

ALTER TABLE <旧表名> RENAME  [TO] <新表名>

 

修改字段的数据类型

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

 

修改字段名

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>

 

添加字段

ALTER TABLE <表名> ADD <新字段名> <数据类型>[约束条件][FIRST] AFTER 已经存在的字段名

 

修改已经存在字段的排列位置

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2

 

修改表的存储引擎

ALTER TABLE <表名> ENGINE=要更改的存储引擎

 

创建复合主键

ALTER TABLE <表名> ADD PRIMARY KEY(字段1,字段2)

 

创建外键

语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)

 

删除外键约束

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>

 

删除主键

ALTER TABLE <表名> DROP PRIMARY KEY

 

删除唯一性约束

ALTER TABLE <表名> DROP KEY <约束名>

 

关闭外键约束检查

SET FOREIGN_KEY_CHECKS=0


数据类型

数值的数据类型                存储需求     有符号的取值范围                             无符号的取值范围

TINNYINT       很小的整数      1字节      -127~128                                     0~255

SMALLINT       小的整数        2字节      -32768~32767                                 0~65535

MEDIUMINT      中等大小的整数   3字节      -8388608~8388607                             0~16777215

INT            普通大小的整数   4字节      -2147483648~2147483647                       0~4294967295

BIGINT         大的整数         8字节      -9223372036854775808~9223372036854775807     0~18446744073709551615

字段名 数据类型 unsigned

浮点数数据类型

FLOAT  (M,N)               单精度浮点数

DOUBLE  (M,N)            双精度浮点数

定点数数据类型

DECIMAL  (M,N)

M代表精度(显示的位数),N是标度(保留几位小数)

如果decimal不指定精度,默认(10,0)

 

浮点数优势:取值范围更大

缺点:精度不准确

精度要求较高时,使用decimal

 

日期时间数据类型

类型名称            日期格式              日期范围                                           存储需求

YEAR                  YYYY               1901 ~ 2155                                       1字节

TIME                  HH:MM:SS           -838:59:59 ~ 839:59:59                            3字节

DATE                 YYYY-MM-DD          1000-01-01 ~ 9999-12-31                           3字节

DATETIME      YYYY-MM-DD HH:MM:SS        1000-01-01 00:00:00 ~ 9999-12-31 23:59:59          8字节

TIMESTAMP   YYYY-MM-DD HH:MM:SS          1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07UTC   4字节
YEAR:
以2位字符串格式表示的 YEAR ,范围为 '00' ~ '99' ,其中,'00' ~ '69' 被转换为 2000 ~ 2069 ,'70' ~ '99' 被转换为 1970 ~ 1999
以2位数字格式表示的 YEAR ,范围为 1 ~ 99 ,其中,1 ~ 69 被转换为 2001 ~ 2069 ,70 ~ 99 被转换为 1970 ~ 1999
mysql> INSERT INTO test3 VALUES('0'),('00'),('77'),('10');

 

字符串数据类型

  1. 字符串除了可以用来储存字符串数据,还可以存储图片视频音频等文件(以二进制形式存储)
  2. mysql支持两种字符串类型,文本字符串,二进制字符串
文本字符串
文本字符串    类型说明               存储需求

CHAR(M)      固定长度的文本字符串     M 字节,1 <= M <= 255

VARCHAR(M)   可变长度的文本字符串     L+1 字节在此 L <= M 和 1 <= M <= 255

TINYTEXT     非常小的文本字符串       L+1 字节,在此 L < 2^8

TEXT         小的文本字符串           L+2 字节,在此 L < 2^16

MEDIUMTEXT   中等大小的文本字符串      L+3 字节,在此L < 2^24

LONGTEXT     大的文本字符串            L+4 字节,在此 L < 2^32

 

CHAR,当插入的字符没有达到规定的字符要求将会在字符串右侧填充空格来达到指定长度

VARCHAR,  M显示宽度,最大长度实际由最长的行的大小和使用的字符集确定,实际占用空间是插入的实际长度+1


文本数据类型

  1. TINYTEXT                      最大长度为 255 个字符                          占用空间+1
  2. TEXT                                最大长度为 65536 个字符                     占用空间+2
  3. MEDIUMTEXT              最大长度为 16777215 个字符                占用空间+3
  4. LONGTEXT                    最大长度为 4294967295 个字符          占用空间+4

TEXT数据类型用来以二进制的形式存放文本文件

 

ENUM

枚举:在给定的范围内选择范围中的一个值,ENUM是一个字符串对象,

字段名 ENUM (值1,值2,...值n)

最大允许65535个元素

每个枚举值都有一个索引值,列表值所允许的成员值从1开始

SET

SET是一个字符串对象,可以在给定的范围内选取零个或者多个值。

字段名 SET (值1,值2,...值n)

SET最多允许有64个元素

如果插入SET字段中的值有重复,SET会自动删除重复的值,插入SET字段的值顺序不重要,SET会在储存数据库的时候,按照定义的顺序显示

如果插入了SET给定范围内不存在的数据,SET将会把不存在的数据进行删除,保留范围内存在的数据

数据类型              说明                        存储需求               存储范围(最大长度)
BIT(M)                位字段类型                  约(M+7)/8个字节
BINARY(M)             固定长度的二进制字符         M个字节
VARBINARY(M)          可变长度的二进制字符         L+1个字节
TINYBLOB              非常小的BLOB                L+1字节,L<2^8          255
BLOB                  小的BLOB                    L+2字节,L<2^16         65535
MEDIUMBLOB            中等大小的BLOB              L+3字节,L<2^24         16777215
LONGBLOB              非常大的BLOB                L+4字节,L<2^32         4294967295

BLOB没有字符集,排序和比较基于列值字节的数值

BLOB是数据库中用来存储二进制文件的数据类型,是二进制的对象,用来存储图片,视频,音频等文件。

和CHAR,WARCHAR类似,区别是BINARY和VARBINARY包含二进制字节字符串

BINARY当插入的字符串不满足设定的长度时,BINARY会在数据右侧填充\0以补齐指定长度

VARBINARY是可变的二进制字符串,指定长度之后,长度可以再0到最大值之间

运算符

算术运算符               +    -   *   /    %

比较运算符

=

<=>

<> <=
=
> < ``` BETWEEN AND     当AND前后的数为前大后小的时,判断的值需要大于等于第一个值,小于等于后一个值,当AND前后的数为前大后小时,判断的值需要大于前者,小于后值 IS NULL            判断一个值是否为空值 IS NOT NULL        判断一个值是否不为空值 IN                 判断一个值是否在范围内 NOT IN             判断一个值是否不在范围内 LIKE               通配符匹配 REGEXP             正则表达式匹配 LEAST              在给定的范围内选定最小值 GREATEST           在给定的范围内选取最大值 ISNULL ``` 匹配字符串,如果匹配返回1,不匹配返回0 like 可以使用两种通配符
  1. % 用于匹配任意字符
  2. _用于匹配单一字符
REGEXP用来匹配字符串,如果匹配返回1,如果不匹配返回0 ^用于匹配以什么开头的字符串 $用于匹配以什么结尾的字符串 . 用来匹配任何一个单一字符 [...]用于匹配在方括号内的任何字符 用于匹配零个或多个在*前面的字符   逻辑运算符 ``` 运算符 NOT 或 !        逻辑非 AND 或 &&       逻辑与 OR  或 ||        逻辑或 XOR                逻辑异或 ```   位操作运算符 运算符        作用 |                   位或 &                  位与 ^                  位异或 <<               位左移 >>              位右移 ~                 位取反 位或( | ):对应的二进制位有一个或两个为1,则该位的运算结果为1,否则为0 位与(&): 位异或( ^ ):对应的二进制位不同时,返回1,否则返回0 位左移( << ):将指定的二进制位全部左移指定的位数,左移指定位之后,左边高位将被移除并丢弃,右边低位空出的位置用0补齐 位右移( >> ):将指定的二进制位全部右移指定的位数,右移指定位之后,右边高位将被移除并丢弃,左边低位空出的位置用0补齐 位取反( ~ ):将对应的二进制数逐位反转 优先级 低 = || OR XOR && AND NOT BENTWEEN =,<=>,>=,<> ,<= ,<,>,!= ,IS ,LIKE ,IN,REGEXP | & <<    >> - ,+ *,/, % -,~ !  

函数

ABS:绝对值 PI:圆周率 SQRT:平方根 MOD:取余函数   MOD(x,y) 获取整数的函数 CEIL (x):不小于获取的这个数,且最接近的这个整数的值 CEILING   (x): FLOOR    (x):不大于获取的这个数,且最接近的这个整数的值 取随机值的函数 RAND( ),获取随机数的函数(取值范围在0-1之间) RAND(x),返回一个确定的随机值,x用来作为种子值,用来产生随机序列 四舍五入的函数 ROUND(x) ROUND(x,y) 截取数值的函数 TRUNCATE(x,y) 符号函数 SING(x),整数返回1,负数返回-1,0返回0 幂运算函数 POW(x,y) POWER(x,y) EXP(x) 对数运算函数 LOG LOG10 角度和弧度互相转换的函数 RADIANS:用于将x角度转换为弧度 DEGREES:用于将x弧度转换为角度 正弦函数和反正弦函数 SIN(x) ASIN(x) x是弧度值 余弦函数和反余弦函数 COS(x) ACOS(x) x均为弧度值 正切函数,反正切函数,余切函数 TAN() ATAN(x) COT(x) x依然是弧度值

函数

字符串函数 1、计算字符串长度的函数 CHAR_LENGTH(str)   统计字符串的字符个数 LENGTH(str)    用于统计字符串的字节长度 2、合并字符串长度 CONCAT(str1,str2,str3,…)用于合并字符串 CONCAT_WS(x,str1,str2,...)使用x作为分隔符,合并字符串。如果分隔符是NULL,返回NULL,如果NULL为值,函数自动忽略NULL 3、替换字符串的函数 INSERT(s1,x,len,s2) 用于返回字符串s1,字符串起始于x的位置被字符串s2替换,替换len字符 当len超过了s2替换长度时,有多少位替换多少位 如果任何一个参数为NULL,返回NULL 4、转换大小写的函数 LOWER(strr)、LCASE(str)  将大写全部转换为小写 UPPER(str)、UCASE(str)   将小写全部转换为大写 5、获取指定长度的字符串的函数 LEFT(s,n) RIGHT(s,n) s:要获取的字符串                  n:长度 ``` mysql> select left('football',4); +--------------------+ | left('football',4) | +--------------------+ | foot | +--------------------+ 1 row in set (0.00 sec) mysql> select right('football',4); +---------------------+ | right('football',4) | +---------------------+ | ball | +---------------------+ 1 row in set (0.00 sec) ``` 6、填充字符串函数 LPAD(s1,len,s2)  返回字符串s1,左边由s2填充到len长度,如果s1大于len长度,将被缩短至len长度,始终从右边开始缩减 RPAD(s1,len,s2) 7、删除空格的函数 LTRIM(s)       删除左边的空格 RTRIM(s)       删除右边的空格 TRIM(s)          删除两侧的空格 8、删除指定字符串的函数 TRIM(s1  FROM  s)删除字符串s两端的含有s1子字符串的内容 ``` mysql> select TRIM('xy' FROM 'xyzxyabcxyxyx'); +---------------------------------+ | TRIM('xy' FROM 'xyzxyabcxyxyx') | +---------------------------------+ | zxyabcxyxyx | +---------------------------------+ 1 row in set (0.00 sec) ``` 9、重复生成字符串的函数 REPEAT(s,n)        代表将s重复n遍 10、空格函数 SPACE(n)             生成n个函数 11、替换函数 REPLACE(s,s1,s2) 使用s2替换s中的所有s1 12、比较字符串大小的函数 STRCMP(s1,s2)用于比较字符串s1和s2的大小,如果前后相同返回0,前大后小返回1,前小后大返回-1 13、获取子字符串的函数 SUBSTRING(s,n,len) MID(s,n,len)

s:要获取的字符串,

n从第几位获取,

len:获取的位数

14、匹配子字符串开始位置的函数 LOCATE(str1,str) POSITION(str1  IN  str) INSTR(str,str1) 15、反转字符串函数 REVERSE(s) 16、返回指定位置的字符串函数 ELT(n,s1,s2,s3,……)

n:返回第n个字符串,如果n超过范围返回NULL

``` mysql> select elt(3,'a','b','c','d'),elt(5,'a','b','c','d'); +------------------------+------------------------+ | elt(3,'a','b','c','d') | elt(5,'a','b','c','d') | +------------------------+------------------------+ | c | NULL | +------------------------+------------------------+ 1 row in set (0.00 sec) ``` 17、返回指定字符串位置的函数 FOELD(s,s1,s2,……) 18、返回子字符串位置的函数 FIND_IN_SET(s1,s2)            用于返回子字符串s1在字符串列表s2中的位置 19、日期和时间的函数 (1)获取当前日期的函数 CURDATE( ) CURRENT_DATE( ) CURDATE( )+0 ``` mysql> select CURDATE()+0; +-------------+ | CURDATE()+0 | +-------------+ | 20181022 | +-------------+ 1 row in set (0.00 sec) ``` (2)获取当前时间的函数 CURTIME( ) CURRENT_TIME( ) (3)获取当前日期和时间的函数 CURRENT_TIMESTAMP( ) NOW( ) SYSDATE( ) (4)获取时间戳的函数 UNIX_TIMESTAMP( ) 1970-01-01 00:00:01 UTC 2038-01-19 03:14:07 (5)转换时间戳的函数 FROM_UNIXTIME( )   将时间戳转换为普通格式的时间 (6)获取UTC日期的函数 UTC_DATE( ) (7)获取UTC时间的函数 UTC_TIME( ) (8)获取月份的函数 MONTH(DATE)                               月份的数字 MONTHNAME(DATE)                  月份的英文 (9)获取星期的函数 DAYNAME(DATE)                       返回date对应的英文名 DAYOFWEEK(DATE)                  用于返回date对应的一周中的索引(位置),1代表周日,以此类推 WEEKDAY(DATE)                        用于返回日期对应的工作日索引(位置),0代表周一,以此类推 WEEKOFYEAR(DATE)                用于计算date是一年中的第几周 (10)获取天数的函数 DAYOFYEAR(date)                    返回现在的日期是一年中的第几天 DAYOFMONTH(date)                计算date是一个月中的第几天 (11)获取年份的函数 YEAR(date) 获取季度的函数 QUARTER(date) (12)获取分钟的函数 NINUTE(date) (13)获取秒钟的函数 SECOND(date) (14)获取日期的的指定值的函数 EXTRACT(TYPE FROM DATE) (15)时间和秒钟转换的函数 TIME_TO_SEC(TIME) SEC_TO_TIME(TIME) (16)计算日期和时间的函数 DATE_ADD(date,INTERVAL  expr  type)                  对日期进行加运算 ADDDATE(date,INTERVAL  expr  type) ``` mysql> SELECT DATE_ADD('2018-10-23 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS col1; +---------------------+ | col1 | +---------------------+ | 2018-10-24 00:01:00 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDDATE('2018-10-23 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS col1; +---------------------+ | col1 | +---------------------+ | 2018-10-24 00:01:00 | +---------------------+ 1 row in set (0.00 sec) ``` DATE_SUB(date,INTERVAL  expr  type)                对日期进行减运算 SUBDATE (date,INTERVAL  expr  type) ``` mysql> SELECT DATE_SUB('2018-10-23 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS col2; +---------------------+ | col2 | +---------------------+ | 2018-10-23 23:58:58 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBDATE('2018-10-23 23:59:59',INTERVAL '1:1' MINUTE_SECOND) AS col2; +---------------------+ | col2 | +---------------------+ | 2018-10-23 23:58:58 | +---------------------+ 1 row in set (0.00 sec) ``` ADDTIME(date,expr )                   对时间进行加操作 ``` mysql> select ADDTIME ('23:59:59','1:1:1'); +------------------------------+ | ADDTIME ('23:59:59','1:1:1') | +------------------------------+ | 25:01:00 | +------------------------------+ 1 row in set (0.00 sec) ``` SUBTIME(date,expr )                  对时间进行减操作 ``` mysql> select SUBTIME ('23:59:59','1:1:1'); +------------------------------+ | SUBTIME ('23:59:59','1:1:1') | +------------------------------+ | 22:58:58 | +------------------------------+ 1 row in set (0.00 sec) ``` DATEDIFF( )                   用于计算两个日期之间间隔的天数 ``` mysql> select datediff('2018-10-23','2018-01-01'); +-------------------------------------+ | datediff('2018-10-23','2018-01-01') | +-------------------------------------+ | 295 | +-------------------------------------+ 1 row in set (0.00 sec) ``` (17)将日期和时间格式化的函数 DATE_FORMAT(DATE,FORMAT)              用于格式化日期 TIME_FORMAT(TIME,FORMAT) GET_FORMAT(VAL_TYPE,FORMAT_TYPE)       指定值的类型和格式化类型,然后显示成格式字符串   d:显示该月日期,格式(00..31) e:显示该月日期,格式() %f:微秒() %h:以两位数表示12小时制,格式() %j:一年中的天数() %l:显示12小时制,格式() %p:上午(AM)或者下午(PM) %r:时间,12小时制(小时hh:分钟mm:秒钟ss 后面带AM或者PM) %S:%s以两位数表示秒(0..59) %T:显示24小时制,(小时hh:分钟mm:秒ss) %U:周,(00..53),其中周日为每周第一天 %u:周,(00..53),其中周一为每周第一天 %V:周,(01..53),其中周日为每周的第一天 %v:周,(01..53),其中周一为每周的第一天 %X:该周的年份,其中周日为每周第一天,格式(YYYY)和%V同时使用 %x:该周的年份,其中周一为每周第一天,格式(YYYY)和%v同时使用 %W:表示工作日的名称 %M:表示月份的名称 %Y:表示以四位数显示年份 %H:用两位数表示24小时制 %k:当不满10的时候使用一位数,表示24小时制 %i:当不满10的时候使用一位数,表示24小时制 %I:使用两位数表示12小时制 20、条件判断函数 (1)IF(expr,v1,v2)        如果表达式expr的结果为true,返回v1,否则返回v2 ``` mysql> select if(1>2,'NO','YES'); +--------------------+ | if(1>2,'NO','YES') | +--------------------+ | YES | +--------------------+ 1 row in set (0.00 sec) ``` (2)IFNULL(v1,v2)    如果v1不为NULL,返回v1,如果v1为NULL,返回v2 ``` mysql> select IFNULL(1,2),IFNULL(NULL,2); +-------------+----------------+ | IFNULL(1,2) | IFNULL(NULL,2) | +-------------+----------------+ | 1 | 2 | +-------------+----------------+ 1 row in set (0.00 sec) ``` (3)CASE expr WHERE v1 THEN r1 [WHEN v2 THEN r2]  [ELSE rn] END 如果expr结果等于某个vn,返回对应的位置THEN后面的结果,如果所有值都不符合,返回ELSE后面的rn,如果没有ELSE返回NULL ``` mysql> select CASE 14*15 WHEN 1 THEN 'zz' WHEN 100 THEN 'zz' ELSE 'more' END; +----------------------------------------------------------------+ | CASE 14*15 WHEN 1 THEN 'zz' WHEN 100 THEN 'zz' ELSE 'more' END | +----------------------------------------------------------------+ | more | +----------------------------------------------------------------+ 1 row in set (0.00 sec) ``` 21、系统信息函数 (1)获取MySQL版本号的函数 VERSION() (2)查看当前用户连接数id的函数 CONNECTTION_ID( ) (3)查看当前用户的连接信息 SHOW PROCESSLIST Command:显示当前连接执行的命令,一般取值为sleep(休眠)、查询(query)、连接(connect) (4)查看当前登录的用户名的函数 USER( ) CURRENT_USER( ) SYSTEM_USER( ) (5)查看指定字符串字符集的函数 CHARSET(str) (6)获取最后一个自动生成的ID值的函数 LAST_INSERT_ID( ) 22、加密函数 (1)PASSWORD(str)             从明文密码str计算并返回加密后的密码字符串。单向形式,不可逆!(加密后,无法解密) ``` mysql> select PASSWORD('123.com'); +-------------------------------------------+ | PASSWORD('123.com') | +-------------------------------------------+ | *AC241830FFDDC8943AB31CBD47D758E79F7953EA | +-------------------------------------------+ 1 row in set (0.00 sec) ``` (2)MD5(str)            为字符串str算出一个MD5  128比特效验和,把字符串加密成32位16进制的字符串 ``` mysql> select MD5('123.com'); +----------------------------------+ | MD5('123.com') | +----------------------------------+ | cbff36039c3d0212b3e34c23dcde1456 | +----------------------------------+ 1 row in set (0.00 sec) ``` (3)ENCODE(str,pswd_str)             使用pswd_str作为密码,加密str ``` mysql> create table jiami -> ( -> -> id INT(11), -> pswd BLOB -> ); Query OK, 0 rows affected (0.00 sec) mysql> insert into jiami(id,pswd) values (1,ENCODE('sjk','yjs')); Query OK, 1 row affected (0.00 sec) ``` 23、解密函数 DECODE(crypt_str,pswd_str) ``` mysql> select DECODE(ENCODE('sjk','yjs'),'yjs'); +-----------------------------------+ | DECODE(ENCODE('sjk','yjs'),'yjs') | +-----------------------------------+ | sjk | +-----------------------------------+ 1 row in set (0.00 sec) ``` 24、格式化函数 FORMAT(x,n)            将x格式化,并且以四舍五入的方式保留小数后n位,结果以字符串的形式返回 25、不同进制的数字进行转换的函数 CONV( ) 例:将16进制的A,转换为2进制 ``` mysql> select CONV('a',16,2); +----------------+ | CONV('A',16,2) | +----------------+ | 1010 | +----------------+ 1 row in set (0.00 sec) ``` 26、IP地址与数字互相转换的函数 INET_ATON(expr)             将网络地址转换为数值 ``` mysql> select INET_ATON('192.168.1.1'); +--------------------------+ | INET_ATON('192.168.1.1') | +--------------------------+ | 3232235777 | +--------------------------+ 1 row in set (0.00 sec) ``` INET_NTOA(expr)             将数值转换为网络地址 ``` mysql> select INET_NTOA('3232235777'); +-------------------------+ | INET_NTOA('3232235777') | +-------------------------+ | 192.168.1.1 | +-------------------------+ 1 row in set (0.00 sec) ``` 27、加锁函数解锁函数 (1)GET_LOCK(str,timeout)                 使用字符串str得到一个锁,阻塞超时时间timeout秒。            timeout如果等于负数,等同于永久加锁
  • 如果成功得到锁,返回1
  • 如果操作超时,返回0
  • 如果发生错误,返回NULL
``` mysql> select GET_LOCK('sjk','10'); +----------------------+ | GET_LOCK('sjk','10') | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) ``` (2)RELEASE_LOCK(str)                用于解开被GET_LOCK获取的锁
  • 如果被解开,返回1
  • 如果该线程尚未创建锁,返回0
  • 如果该锁不存在,返回NULL
  • 如果锁从未被GET_LOCK( )调用获取,或者已经被提前解开,表示锁不存在
``` mysql> select RELEASE_LOCK('sjk'); +---------------------+ | RELEASE_LOCK('sjk') | +---------------------+ | 1 | +---------------------+ 1 row in set (0.00 sec) ``` (3)IS_FREE_LOCK(str)       检查名字叫做str的锁是否可用
  • 如果可以使用,返回1
  • 如果正在被使用,返回0
  • 如果出现错误,返回NULL
(4)IS_USED_LOCK(str)

查询语句

SELECT  {*|<字段>} FROM 表1 [表2]  [WHERE <表达式>] [group by 字段] [having ] [order by 字段1][字段2] [limit…]  

WHERE

=                                              等于 <>,    !=                              不等于 <                                             小于 >                                             大于 <=                                          小于等于 >=                                          大于等于 BETWEEN                           再两者之间 IN(s1,s2,...sn)           在in给定的范围内 LIKE                                      通配符查询 AND                                       满足AND前面的条件同时满足AND后面的条件 OR                                         直满足OR前后的一个条件就可以输入符合条件的内容 ORDER BY                           把规定的字段进行排序,默认升序排序(ASC),降序:在后面加(DESC) LIKE                                      模糊查询,使用通配符进行查询 %                                            匹配任意字符 _                                            匹配任意单个字

分组查询

GROUP BY 字段 HAVING 条件表达式 分组一般和聚合函数一起使用 MAX( ),MIN( ),COUNT( ),SUM( ),AVG( ) GROUP_CONCAT(字段) limit                 制查询结果的数量 ``` mysql> select * from fruits limit 5; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | a1 | 101 | apple | 5.20 | | b1 | 101 | blackberry | 10.20 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | t1 | 102 | balance | 10.30 | +------+------+------------+---------+ 5 rows in set (0.00 sec) ``` limit [位置偏移量] 行数             偏移量从0开始 ```  mysql> select * from fruits limit 1,6; +------+------+------------+---------+ | f_id | s_id | f_name | f_price | +------+------+------------+---------+ | b1 | 101 | blackberry | 10.20 | | bs1 | 102 | orange | 11.20 | | bs2 | 105 | melon | 8.20 | | t1 | 102 | balance | 10.30 | | t2 | 102 | grape | 5.30 | | o2 | 103 | coconut | 9.20 | +------+------+------------+---------+ 6 rows in set (0.01 sec) ```

连接查询

外连接查询:(左连接查询,右连接查询) 内连接查询:使用比较运算符进行表之间的列数据的比较操作,并且列出这些表中和连接条件匹配的数据行 select  字段  from  表1  inner  join  表2  ON  条件 自连接是特殊内连接方式,指在物理层面是一张表,而逻辑层面是两张表 外连接 左连接   LEFT OUTER JOIN (LEFT JOIN)返回包括左表中所有记录和右表中连接字段相等的记录 右连接   RIGHT OUTER  JOIN (RIGHT JOIN)返回包括右表中的所有记录和左表中连字段相等的记录

子查询

一个查询语句嵌套在另一个查询语句内部 4.1版本之后开始使用 含有ANY关键字的子查询 含有ALL关键字的子查询 含有exists关键字的子查询 如果内测查询不存在,不会提供外侧查询

联合查询

SELECT COLUMN FROM 表名 UNION [ALL] SELECT COLUMN FROM 表名 查询水果的对应的最高价格 ``` mysql> select s_id,GROUP_CONCAT(f_name),f_price from fruits where (f_price,s_id) IN (select max(f_price),s_id from fruits GROUP BY s_id) GROUP BY s_id; +------+----------------------+---------+ | s_id | GROUP_CONCAT(f_name) | f_price | +------+----------------------+---------+ | 101 | blackberry | 10.20 | | 102 | orange | 11.20 | | 103 | coconut | 9.20 | | 104 | berry | 7.60 | | 105 | xxtt | 11.60 | | 106 | mango | 15.70 | | 107 | xbababa,xxxx | 3.60 | +------+----------------------+---------+ 7 rows in set (0.00 sec) ```      

索引

使用索引可以提高数据库中对特定数据的查询速度 索引是单独的,存储在磁盘上的数据结构 有效的索引建立可以提高数据库的查询速度 索引通过存储引擎实现 MyISAM          btree索引 InnoDB            btree索引和hash索引 Memory           btree索引和hash索引 每一种存储引擎,每个表最少支持创建16个索引,总索引长度最少支持256字节 优势:
  1. 加快查询速度
  2. 创建唯一索引来保证数据表中数据的唯一性
  3. 实现数据完整性,加速表和表之间的连接
  4. 减少分组和排序时间
缺点:
  1. 创建索引需要消耗磁盘空间,索引越多占用空间越大
  2. 创建索引呵呵维护索引需要耗费时间,随着数据量的增加耗费的时间越多
  3. 当对表中的数据进行增删改的时候,索引动态维护,降低数据维护速度

索引的分类

唯一索引和普通索引

唯一索引允许在定义唯一列上插入空值,但不允许重复

单列索引和组合索引

组合索引满足最左侧前缀原则。利用索引当中位于最左侧的列进行索引

全文索引

在定义的索引列上支持值的全文查找,允许你在索引定义的列上插入重复值和空值 CHAR,VARCHAR,TEXT

空间索引

 

创建索引的规则

  1. 索引并不是创建的越多越好
  2. 数据量小的表最好不要创建索引
  3. 对于经常更新的数据不要创建索引
  4. 对于重复值较多的字段不要创建索引
  5. 对于唯一性属于某种数据本身特征时,可以创建唯一索引
  6. 在频繁的进行排序或者分组的创建索引,如果排序的列有多个,可以创建全文索引

创建索引的语法

CREATE INDEX 创建索引 ALTER TABLE 添加索引 ``` CREATE TABLE 表名 字段名 数据类型 [unique唯一索引 | fulltext 全文索引 | spatial 空间索引] index|key [索引名] (col_name [length]) [asc|desc] ```   explain:判断 select_type:查询类型 simple:表示不包含union查询或者子查询 primary:表示此查询是外层查询 union:表示此查询时候union的第二次或者随后的查询 dependent union:union中额第二个或之后的查询语句,取决于外面的查询 subquery:子查询中的第一个select key_len:判断索引是否被使用到 ref:那个字段或者常数是否被使用 rows:在搜素到正确结果之前,需要扫描的行数 extra:额外信息 Non_unique:0代表唯一索引 Null:是否允许为空 在已经创建的表上添加索引 ``` ALTER table 表名 ADD [unique唯一索引|fulltext全文索引|spatial空间索引|index | key [索引名] ] ```  

视图

视图是一张虚表,不保存任何数据,所有数据都来源于真实表 最大的特点:保证数据的安全性 视图的优点
  1. 简单化:看到的就是需要的
  2. 安全性:通过视图用户只能查询和修改所能见到的数据,其他没在视图中体现的数据用户是看不到也获取不到的
  3. 逻辑数据独立性:视图可以帮助用屏蔽真是表结构变化带来的影响
创建视图的语法 ``` create [or replace] [algroithm视图选择的算法={undefined|merge|temptable}] view 视图名 [(column_list)] AS select_statement [with[cascaded|local] check option] ``` undefined:视图在使用时自动选择算法(默认nerge) merge:表示将使用的视图语句和视图定义合并起来,是视图定义的某一部分取代语句的部分 temptable:将视图结果存入临时表,然后使用临时表来执行语句 with check option:更新视图时要满足所有相关视图和表的条件 cascaded:默认选项,表示更新视图时满足相关视图和表的条件 local:表示更新视图时,只需要满足自身定义的条件即可 视图是不含有任何约束条件的 视图在存在某些情况将不能执行更新操作 视图中不包含原表中被定义为非空的列 在定义视图的select语句后字段列表中使用了数学表达式 在定义视图的select语句后字段中使用了聚合函数 select中使用union、group by或having无法执行更新

删除视图

``` DROP VIEW [IF EXISTS] 视图名 [RESTRICT|CASCADE] ``` IF EXISTS:先判断视图是否存在,如果不存在出现一个warning restrict:确保只有不存在相关视图和完整性约束的表才能被删除 cascade:任何相关视图和完整性约束一并删除 视图和表的区别 视图是一张虚表,而原表属于真实存在的表,视图基于SQL语句的结果集成的可视化的表 视图不存放数据,而表是存放数据的 视图可以把多个表的内容集成到一起,而真实表不可以视图 表可以修改约束条件,而视图没有任何约束条件 表和视图虽然都占用物理空间,但是视图只是逻辑概念存在 表属于全局模式的表,是实表,而视图数据局部模式的表,是虚表 视图的建立和删除只影响视图本身,不影响对应的实表的数据 联系 视图是基于表上建立的,他的结构和内容都来自于真实表,一个视图的内容可以来自于一个表,或者多个表 mysqldump :备份 SOURCE 指定路径:恢复

事务

多条SQL语句,要么全成功,要么全部失败 ACID特性 A:原子性 一个事物必须被视为一个不可分割的单元 C:一致性 数据库有一种状态切换到另一种状态 I: 隔离性 事务在提交之前,对其他事务不可见 D:持久性 一旦事务提交之后,所修改的内容将永久保存到数据库 开启事务 BEGIN START TRANSACTTION 开启事务 ROLLBACK 回滚。回到当前事务最开始时候的位置 提交事务 COMMIT SHOW VARIABLES LIKE 'AUTOCOMMIT' 查看是否为自动提交 默认情况下MySQL启动自动提交模式只要使用DML语句,MySQL会立即隐式提交事务

四种隔离级别

未提交读 read uncommitted 允许别的事务,去读取这个事务未提交之前的数据 缺点造成脏读、幻读 一个事物在为提交之前,对另一个事务可读,这种称为脏读 已提交读 read committted 已提交读为一个事物只能在提交时候才能查看到事务内所做的修改 可重复读 repeatable read 数据可以进行重复读取,解决了已提交读,不可重复读的问题 可串行读 seaializable 属于最高隔离级别,强制事务串行执行,强制给事务进行排序,适用于对一致性要求较高且bing

设置隔离级别

SET SESSIO N TX_ISOLATION='READ UNCOMMITTED'; 查询隔离级别 SELECT @@TX_ISOLATIOON 隔离级别 脏读 不可重复 幻读 加锁读 未提交读 有 是 有 没有 已提交读 没有 是 有 没有 可重复读 没有 不是 有 没有 可串行读 没有 不是 没有 是

触发器

触发器是一个特殊存储过程,属于嵌入到MySQL的一段程序 触发器是由事件来触发的,事件包括insert 、update、delete 如果定义了触发程序,当执行上述三个语句时,触发器就会执行已经写好的触发程序,当触发触发器时,触发器自动执行,不需要人工干预。 创建触发器语法 ``` CREATE TRIGGER 触发器名 触发器时机(before或after) 触发的事件(insert、update或者delete) ON 表名 for EACH ROW END ``` after:是先完成数据的增删改,然后再触发触发器,触发器的语句晚于增删改操作,无法影响前面增删改 before:先触发触发器,然后再增删改,触发的语句优于增删改 DELIMITER //         切换结束符 查询触发器 show TRIGGERS/G 查看所有的触发器(where加触发器的名字可以查看指定的触发器) select * from information_schema.triggers/G 删除触发器 DROP TRIGGER 触发器名称

MySQL用户和权限

1.用户列 包含:user,host,password。表示用户,主机和密码 2.权限列 只要授权过的用户,都将拥有一个usage权限,代表允许用户登陆数据,usage权限不能通过revoke收回 权限列的字段决定了用户权限,表示在全局范围内允许用户对数据库进行给定权限的操作,包含查询权限,修改权限等普通权限,还包括关闭服务器,加载用户等高级权限 ``` 查询权限 mysql> select * from mysql.user\G *************************** 1. row *************************** Host: localhost User: root Password: *AC241830FFDDC8943AB31CBD47D758E79F7953EA Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y 是否有权限加载文件 Grant_priv: Y 是否有权限给其他用户授权 References_priv: Y 是否有权限创建约束关系 Index_priv: Y 是否有权限创建索引 Alter_priv: Y 是否有用于修改的权限 Show_db_priv: Y 是否有用于查看数据库的权限 Super_priv: Y 是否有超级用户的权限,是否有杀死进程、的权限 Create_tmp_table_priv: Y 是否有创建临时表的权限 Lock_tables_priv: Y 是否有锁表的权限 Execute_priv: Y Repl_slave_priv: Y 从服务器复制的权限 Repl_client_priv: Y 是否有从服务器查询信息的权限 Create_view_priv: Y 是否用于创建视图的权限 Show_view_priv: Y 是否有查看视图的权限 Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y 是否有创建事件的权限 Trigger_priv: Y 是否对触发器有各种操作权限 Create_tablespace_priv: Y 是否有权限创建表空间 ``` 3.安全列 ``` ssl_type: 安全套接字层 ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: 插件 authentication_string: 5.7版本之后用来显示password ``` 4.资源控制列 max_questions:用户每小时允许执行查询操作的次数 max_updates:用户每小时允许更新操作的次数 max_connections:用户每小时允许连接操作额次数 max_user_connections:用户允许同时建立连接的次数   新建用户 使用create user 创建用户 ``` CREATE USER user_specification user@host [identidied by [PASSWORD]'password' identified with auto_plugin [as'auto_string'] ``` 使用GRANT创建新用户(可以修改密码) ``` GRANT PRIVILEGES ON db.table TO user@host [identified by 'password']['user[identifie by 'password']][with grant option]; ``` with grant option:代表有权限给其他用户授权 flush privileges:刷新授权 直接操作MySQL用户表 ``` insert into mysql.user ``` 删除用户 DROP USER delete 语句删除用户 修改密码 1、mysqladmin -u 用户名 -h 指定主机 -p password 新密码 2、直接修改user表 ``` mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('123.com') where user='root' AND host='localhost'; Query OK, 0 rows affected (0.28 sec) Rows matched: 1 Changed: 0 Warnings: 0 ``` 3、使用set来更改密码,加上for修改指定用户密码 SET PASSWORD=PASSWORD('新密码') 4、使用grand来修改用户密码   当root密码丢失的时候,使用 --skip-grant-tables ``` [root@shuai 桌面]# service mysqld stop Shutting down MySQL.... [确定] [root@shuai 桌面]# mysqld_safe --skip-grant-tables user=mysql 181030 12:39:53 mysqld_safe Logging to '/var/lib/mysql/shuai.err'. 181030 12:39:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 181030 12:40:00 mysqld_safe mysqld from pid file /var/lib/mysql/shuai.pid ended [root@shuai 桌面]# service mysqld start --skip-grant-tables Starting MySQL.. [确定] [root@shuai 桌面]# mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.22-log Source distribution Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> update mysql.user set password=password('123.com') where user='root'; Query OK, 3 rows affected (0.00 sec) Rows matched: 4 Changed: 3 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) [root@shuai 桌面]# service mysqld restart Shutting down MySQL. [确定] Starting MySQL.. [确定] ```

收回权限

收回所有权限 REVOKE ALL PRIVILEGES GRANT  OPTION FROM 'user'@'host','user'@'host'... 收回某条权限 ROVOKE priv_type [(columns)],priv_type... ON table1,table2... FROM 'user'@'host'  

MySQL日志记录数据库日常操作和错误信息

1、错误日志 记录MySQL服务启动,运行或者停止的时候出现的问题 2、查询日志 记录建立的客户端的连接和执行的语句 3、二进制日志 记录所有更改数据的语句,用于数据的恢复和复制 4、慢查询日志 通过设置阈值,当有查询语句超过预设阈值将会被记录到慢查询日志当中 二进制以一种有效的格式,并且是事务安全的方式包含更新日志中的可用信息。尽可能的恢复数据

二进制日志

开启二进制日志

log-bin=mysql-bin                                  日志的存放位置 expire_logs_day=10                               清楚日志的天数 max_binlog_size=100M                        超过最大大小,将开启一个新的日志来记录数据(默认1G) ``` 查看日志信息 mysql> show variables like 'log_%'; +---------------------------------+---------------------------+ | Variable_name | Value | +---------------------------------+---------------------------+ | log_bin | ON | //是否开启日志 | log_bin_trust_function_creators | OFF | //是否允许创建函数 | log_error | /var/mysql/data/shuai.err | //错误日志存放位置 | log_output | FILE | // | log_queries_not_using_indexes | OFF | //记录没有使用索引的查询语句 | log_slave_updates | OFF | //是否开启从库更新 | log_slow_queries | OFF | //是否开启慢查询日志 | log_warnings | 1 | //是否将警告信息存放到记录日志当中(1代表启用,0代表禁用) +---------------------------------+---------------------------+ 8 rows in set (0.00 sec) ``` log_warnings:如果设置的数字超过1,表示将会把连接产生的“失败的连接”和拒绝访问 ``` 查询当前有哪些日志 mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000018 | 292 | | mysql-bin.000019 | 662 | | mysql-bin.000020 | 1046 | | mysql-bin.000021 | 1771 | | mysql-bin.000022 | 5708 | | mysql-bin.000023 | 4875 | | mysql-bin.000024 | 1092 | | mysql-bin.000025 | 2425 | | mysql-bin.000026 | 1266 | | mysql-bin.000027 | 527 | | mysql-bin.000028 | 3151 | | mysql-bin.000029 | 457 | | mysql-bin.000030 | 107 | | mysql-bin.000031 | 126 | | mysql-bin.000032 | 126 | | mysql-bin.000033 | 150 | | mysql-bin.000034 | 150 | | mysql-bin.000035 | 107 | +------------------+-----------+ 18 rows in set (0.00 sec) ```

刷新所有日志

``` mysql> flush logs; Query OK, 0 rows affected (0.02 sec) ``` ``` [root@shuai data]# mysqladmin -uroot -p123.com flush-logs ```

查看二进制日志

``` [root@shuai data]# mysqlbinlog mysql-bin.000034 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #181031 9:57:05 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.22-log created 181031 9:57:05 BINLOG ' sbTZWw8BAAAAZwAAAGsAAAAAAAQANS41LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #181031 9:57:42 server id 1 end_log_pos 150 Rotate to mysql-bin.000035 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; ```

删除二进制日志

  • rf -rf 删除指定二进制日志
  • reset master;删除所有二进制文件
  • purge master logs to '二进制名'
  • purge binary logs before  'date'

还原

``` mysqlbinlog [option] 日志文件名 | mysql -u user -p password ```
  • start-date                  开始时间
  • start-date                  结束时间
  • start-position           开始位置
  • start-position            结束位置
关闭二进制日志(临时) mysql> set sql_log_bin=0;  

错误日志

开启错误日志 [root@shuai data]# vim /etc/my.cnf 在配置文件中加入:log-error 重启数据库

查询日志

记录所有用户的操作,包括启动和关闭服务,执行查询和更新语句 开启查询日志 [root@shuai data]# vim /etc/my.cnf 再配置文件中加入:log 重启数据库

慢查询日志

记录查询时长超过指定时间的日志 开启慢查询日志 [root@shuai data]# vim /etc/my.cnf 在配置文件中加入: log-slow-queries long_query_time=5               //阈值等于5,超过5秒就被记录到慢查询日志(单位:秒)。如果不写入这条,默认为10秒 重启数据库 ``` 查询慢查询阈值 mysql> show variables like 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+ 1 row in set (0.00 sec) ```   PRIMARY KEY 主键查询,外层查询 UNION RESULT UNION 查询的结果 type:提供了判断查询是否高效的依据。通过type字段,可以判断此次查询是全表扫描还是索引扫描 system:表中只有一条数据,是特殊const类型 const:对于主键唯一索引的查询扫描,最多返回一行数据,const查询速度快 eq_ref:一般用于连接查询,表示对于前表的每个结果,都能匹配到后表的一行结果,一般连接的运算符都是=,查询效率较高 ref:出现在多表连接查询,针对于非主键索引或者非唯一索引,或者使用了最左前缀索引原则索引查询 range:表示使用索引范围查询,通过索引字段范围获取表中数据记录,通常出现在使用比较运算符的情况下 index:表示全索引扫描。在所有的索引中进行查询,不扫描数据,查询的数据直接通过扫描索引的方式获得。using index ALL:全表扫描,性能最差的查询 速度: ALL  优化数据库表结构 1、把字段较多的表分成多个表 2、增加中间表来提高效率 3、合理增加冗余字段 4、优化插入数据的速度
  • 禁用索引 ``` ALTER TABLE 表名 DISABLE KEYS; ```
  • 禁用唯一性检查 ``` SET UNIQUE_CHECKS=0; 关闭 =1 开启 ```
5、把多条插入语句合并成一条提高插入数据的速度
  • 禁用外键检查 SET FOREIGN_KEY_CHECKS=0 ;    关闭          =1   开启
  •  禁用自动提交 SET AUTOCOMMIT=0   关闭     =1   开启
分析表、检查表、优化表 分析表:分析关键字的分布 检查表:检查是否存在错误 优化表:消除更新或者删除造成的空间浪费  

分析表

analyze [local | no_write_to_binlog] TABLE 表名1,[表名2...]          不写入二进制日志 no_write_to_binlog           不写入二进制日志 msg_type   信息级别(status 正常、info 有信息、note 注意、warning 警告、error 错误) msg_text   显示信息

检查表

检查表中是否存在错误关键字统计,检查视图是否有错误 CHECK TABLE 表名 option={quick|fast|medium|exended|changed} iption:只针对MyISAM表 quick:不扫描检查,不检查错误连接 fast:只检查没有被正确关闭的表 meidium:扫描行验证被删除的连接是否有效,同时可以计算各行的关键字效验和 extended:对每行所有的关键字进行全面的关键字查找 changed:只检查上次检查后被更改的表,和没有被正确关闭的表

优化表

``` optimize [local | no_write_to_binlog]  TABLE 表1 [表2...] ```
  • .frm:表结构文件
  • .MYD:MyISAM的数据
  • .MYI:表里面的索引信息
共享表空间
  • 所有的InnoDB的数据和索引全部存放在ibdata1
独享表空间
  • 如果开启独享表空间代表每个表都有一个ibdata1.
  • 如果开启独享表空间,如果删除表中大量的行,索引会重新组合并且会自动优释放相应空间,不必优化
开启索引缓冲区 ``` [root@shuai 桌面]# vim /etc/my.cnf query_cache_size=512M query_cache_type=1 [root@shuai 桌面]# service mysqld restart Shutting down MySQL.... [确定] Starting MySQL.. [确定] 刷新缓冲区 mysql> flush query cache; ```  

备份

语法: 备份单个库 ``` mysqldump -u 用户 -p 密码 要备份的库 > /路径 ``` 备份多个库 mysqldump -u user -p 密码 -B (--databases) 库名1 [库名2...] >/路径 恢复数据 ``` mysql -u user -p 密码 库名 < /备份文件 ```
  • -u:指定用户名
  • -p:指定密码
  • -d:只备份表结构,不备份表数据
  • -t:只备份表数据,不备份表结构
  • -A:备份所有库
  • -B(--databases):备份多个库
         
Last modification:March 27th, 2020 at 03:36 pm
如果觉得我的文章对你有用,请随意赞赏