目录

 

Mysql安装安装及配置

登录/退出

修改Mysql命令提示符

Mysql语句的规范

Mysql常用命令

常用数据类型

数据库操作

数据表相关操作

存储引擎

运算符

函数

查询数据操作

子查询

连接查询

索引

视图

 

mysql优化思维导图 mysql总结思维导图_mysql

Mysql安装及配置

安装目录介绍

bin目录

存储可执行文件

data目录

存储数据文件

include目录

存储包含的头文件

docs目录

存储文档

share目录

错误消息和字符串文件

my.ini

mysql的配置文件

字符集

客户端字符集（mysql）

服务器端字符集(mysqld)

登录/退出

登录常用参数

 

-u

用户名

-p

密码

-h:

服务器名称

-P：

端口号

-D：

打开指定数据库

--prompt=name：

指定命令提示符

--delimiter=name：

指定分隔符

-V

输出版本信息且退出

退出

exit、quit、\q、Ctrl+c

修改Mysql命令提示符

连接客户端时通过参数指定

mysql -uroot -proot --prompt 提示符

连接上客户端后，通过propmpt命令修改

prompt提示符

命令提示符常用参数

  • \D:完整的日期
  • \d:当前数据库
  • \h:服务器名称
  • \u:当前用户名

Mysql语句的规范

  1. 关键字与函数名称全部使用大写
  2. 数据库名称、表名称、字段名称等全部小写
  3. SQL语句必须以分隔符结尾
  4. SQL语句支持折行操作，只要不把单词、标记或者引号字符串分割为两部分，可以在下一行继续写，效果一样
  5. 数据库名称、表名称、字段名称等尽可能不要使用MYSQL的保留字，如果需要使用的时候使用反引号（` `）将名称括起来，以示区分,否则会报错

Mysql常用命令

  • SELECT VERSION():显示当前版本
  • SELECT NOW():显示当前日期时间
  • SELECT USER():显示当前用户

常用数据类型

数值类型---整数类型

类型

存储范围

大小（字节）

TINYINT

有符号值：-127至127

无符号值：0至255

1

SMALLINT

有字符值：-32768至32767

无字符值：0至65535

2

MEDIUMINT

有符号值：-8388608至8388607

无符号值：0至16777215

3

INT

有符号值：-2147683648至2147683647

无符号值：0至4294967295

4

BIGINT

有符号值：-9223372036854775808至9223372036854775807

无符号值：0至18446744073709551615

8

BOOL,BOOLEAN

等价于TINYINT(1),0为false，其余都为true

1

浮点类型

         类型

                存储范围

   大小（字节）

                  备注

FLOAT[(M,D)],单精度

(-3.402823466E+38,-1.175494351

E-38),0,(1.175494351E-38,3402

823466351E+38)

4

如果M（数值总长度）和D（保留小数点后位数）被省略，根据硬件允许的限制来保存值，单精度浮点数精确到大约7位小数

DOUBLE[(M,D)]，双精度

(-1.7976931348623157

E+308,-2.2250738585072014E

-308),0,(2.2250738585072014E

-308,1.7976931348623157E+308)

8

 

DECIMAL[(M,D)]，小数值

依赖M和D的值

如果M>D大小为M+2,否则大小为D+2

与DOUBLE一样，内部以字符串形式存储数值

 

字符串类型

类型

存储需求

CHAR（M）

M个字节，0<=M<=255

CARCHAR(M)

L+1个字节，其中L<-M且0<=M<65535

TINYTEXT

L+1个字节，其中L<256

TEXT或BLOB

L+2个字节，其中L<65536

MEDIUMTEXT

L+3个字节，其中L<16777216

LONGTEXT

L+4个字节，其中L<4294967296

ENUM('value1'，‘value2’.....)

1或2个字节，取决于枚举值的个数（最多65535个值）

SET('value1','value2'...)

1、2、3、4或8个字节，取决于set中成员的数目（最多64个成员）

 

日期时间类型

类型

格式

TIME

HH:MM:SS

DATE

YYYY-MM-DD

DATETIME

YYYY-MM-DD HH:MM:SS

TIMESTAMP

YYYYMMDD HHMMMSS

YEAR

YYYY

二进制类型

主要保存二进制数据的，例如图片、视频的格式

如何选择合适的数据类型

整数

根据要显示的最大值

浮点型

根据要显示的小数位数，如果要保存的数据的小数点位数不超过十可以用FLOAT，若超过10则用DOUBLE，DECIMAL的精度更高

字符串型

主要根据定长与变长的区别进行选择，CHAR类型占用空间比较大，但是处理速度快，而VARCHAR占用空间小，处理速度相对慢，如果数据长度变化不大，则建议使用CHAR，变化大，例如评论数据建议使用VARCHAR

时间

根据需要现实的时间类型进行选择

ENUM与SET类型

长度不同，ENUM类型最多可以由65535个成员，而SET类型最多只能包含64个成员。且ENUM只能单选，而SET类型可以多选；

TEXT与BLOB类型

TEXT只能存储字符数据，而BLOB可以存储二进制数据。如果是纯文本，适合TEXT。如果是图片等适合存二进制；BLOB大小写敏感，而TEXT字段不区分大小写。

数据库操作

创建数据库

CREATE {DATABAASE | SCHEMAL} [IF NOT EXISTS]db_name {[DEFAULT] CHARACTER SET = charset_name}

查看当前服务器下的数据库列表

SHOW {DATABASES|SCHEMAS}

查看指定数据库的定义

SHOW CREATE {DATABASES|SCHEMA} db_name

修改指定数据库的编码方式

ALTER {DARABSE|SCHEMA} db_name [DEFAULT] CHARACTER SET [=] charset_name

打开指定数据库

USE db_name

删除指定数据库

DROP {DATABASE|SCHEMA} [IF EXISTS] db_name

查看上一步产生的警告信息

SHOW WARMING

 

数据表相关操作

什么是数据表

数据表是数据库最重要的组成部分之一，它是其他对象的基础。数据表是存储数据的数据结构，数据表包含了特定实体类型的数据，数据由行（row）和列（column）构成的二维网格，数据表一定先有表结构，再有数据，数据表至少有一列，可以没有行或者多行，数据表名称要求唯一，而且不要包含特殊字符

如何创建

CREATE TABLE [IF NOT EXISTS] tbl_name (字段名称 字段类型[完整性约束条件])ENFINE=引擎名称 CHARSET='编码方式'

完整性约束条件

PRIAMRY KEY/KEY

NOT NULL

UNIQUE KEY /UNIQUE

FOREIGN KEY

AUTO_INCREMENT

DEFAULT（默认值）

查看表数据及表结构

查看数据库下的数据表

SHOW TABLES

查看指定表的表结构

DESC tbl_name

DESCRIBE tlb_name

SHOW COLUMNS FROM tbl_name

修改表结构

修改表名

ALTER TABLE tbl_name RENAME [TO/AS] new_tbl_name

RENAME TABLE tbl_name TO new_tbl_name

添加字段

ALTER TABLE tbl_name ADD 字段名称 字段类型 【完整性约束条件】 [FIRST AFTER 字段名称]

删除字段

ALTER TABLE tbl_name DROP 字段名

修改字段

ALTER TABLE tbl_name MODIFY 字段名称 字段类型【完整性约束条件】 【FIRST | AFTER 字段名】

修改字段名

ALTER TABLE tbl_name CHAGE 旧字段名称 新字段名称 字段类型 【完整性约束条件】 【FIRST | AFTER 字段名】

添加默认值

ALTER TABLE tbl_name 字段名 SET DEFAULT 默认值

删除默认值

ALTER TABLE tbl_name ALTER 字段名 DROP DEFAULT

修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

设置自增长的值

ALTER TABLE tlb_name AUTO_INCREMENT=值

删除数据表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name....]

存储引擎

什么是存储引擎

存储引擎是指表的类型，数据库的存储类型。其实就是对于数据库文件的一种存储机制，如何实现存储数据，如何为存储的数据建立索引以及如何更新，查询数据等技术实现的方法。数据库的存储类决定了表在计算机的存储方式、用户可以根据不同的存储方式。是否进行事务处理等选择合适的存储引擎。

如何查看Mysql的存储引擎

查看Mysql支持的存储引擎：SHOW ENGINES

查看显示支持的存储引擎信息:SHOW VARIABLES LIKE 'have%'

查看默认的存储引擎：SHOW VARIBALES LIKE 'storage_engine'

Mysql常用存储引擎及特点

InnoDB存储引擎

支持事物处理、支持外键、支持崩溃修复与并发控制。其适用于对事物完整性要求比较高，实现并发控制的场景，当然由于其支持事物的提交和回滚，所以它也可用于需要频繁更新和删除的数据库

MyISAM存储引擎

插入数据快，对空间和内存的占用较低，适用于主要对表进行插入和读取操作。如果应用的完整性、并发性要求比较低，可以使用。

MEMORY存储引擎

所有的数据都在内存中，数据的处理速度快，但是安全性不高。其不能创建太大的表，可以将对安全性要求不高且数据量不大的临时数据进行存放。MySQL 中使用该引擎作为临时表，存放查询的中间结果。

同一个数据库可以有多种存储引擎的表

 

运算符

算数运算符

                 符号

               作用

                     表达式

                  +

            加法运算

                    x1+x2+...

                  -

            减法运算

                    x1-x2-...

                  *

            乘法运算

                     x1*x2*...

                  /

            除法运算

                     x1/x2/...

                DIV

            除法运算

                     x1 DIV x2

                  %

               取余

                     x1%x2

               MOD

               取余

                     MOD(x1，x2)

 

比较运算符

                     =  

判断是否相等

                  <=> 

作用判断是否相等，可以判断是否等于NULL

                 >、>=

 判断是否大于等于

                 <、<=

 判断是否小于等于

   IS NULL或IS NOT NULL   

判断是否为NULL

      BETWEEN AND或

  NOT  BETEWEEN AND   

判断是否在范围内

            IN 或者 NOT IN   

判断是否在某一个固定的范围内

         LIKE 或 NOT LIKE

判断是否匹配

           REGEXP    

判断是否正则匹配

逻辑运算符

            &&或者AND

表示并且

              ||或者OR

表示或者

              !或者NOT

取反

                 XOR

异或表达

函数

数学函数

CEIL（）   

 进一取整

FLOOR（） 

 舍一取整

MOD

取余（取模）

POWER（）  

幂运算

ROUND（）   

四舍五入

TRUNCATE（） 

数字截取

ABS（）

   取绝对值

PI（）

圆周率

RAND（）和RAND（X） 

返回0~1之间的随机数，RAND（X）当X相同时返回的随机数相同

SIGN（X）   

返回X的符号，X为负数、0、正数分别返回-1、0、1

EXP（X）

 计算e的几次方

字符串函数

CHAR_LENGTH（S）   

返回字符串的字符数

LENGTH

    返回字符串的长度

CONCAT（S1,S2...）   

将字符串合并为一个字符串

CONCAT_WS（X,S1,S2...）  

以指定分隔符连接字符串

UPPER（S）/UCASE（S）    

将字符串转换为大写

LOWER（S）/LACSE（S）    

将字符串转换为小写

LEFT(S,N)/RIGHT(S,N)  

返回字符串的前/后n个字符

LPAD(S1,LEN,S2)/RPAD(S1,LEN,S2) 

将字符串S1用S2填充到指定的LEN

 

日期时间函数

CURDATE(),CURRENT_DATE()   

 返回当前日期

CURTIME(),CURRENT_TIME()   

 返回当前时间

NOW()   

返回当前日期和时间

MONTH(日期)    

返回日期中月份的值

MONTHNAME(日期)   

返回日其中月份名称

DAYNAME(日期)    

返回星期几

DAYOFWEEK(日期)   

 返回一周内的第几天，6代表星期日

WEEKDAY(日期)    

返回日期是星期几，0表示星期一

WEEK(日期)   

 返回今年第几个星期

YREA(日期)   

返回年份值

HOUR(时间)  

返回小时值

MINUTE(时间)

返回分钟值

SECOND(时间)   

返回秒数

DATEDIFF(日期1，日期2)   

计算两个日期之间的差值

条件判断函数

IF(EXPR,V1,V2)

 如果表达式EXPR成立，返回结果V1,否则返回V2

IFNULL(V1,V2)    

如果V1的值不为空，就显示V1的值，否则返回V2的值

CASE WHEN exp1 THEN V1 [WHEN exp2 THEN V2][ELSE Vn] END    

CASE 表示函数开始，END表示函数结束。如果表达式exp1成立时，返回V1如果表达式exp2成立时，返回V2的值。依次类推，最后遇到ELSE时，返回Vn的值。

系统函数

VERSION 

返回数据库的版本号

CONNECTION_ID()   

 返回服务器的连接数

DATABASE(),SCHEMA()  

返回当前数据库名

USER(),SYSTEM_USER()   

返回当前用户

CURRENT_USER(),CURRENT_USER 

返回当前用户

CHARSET(STR) 

返回字符串STR的字符集

COLLATION(STR)   

返回字符串STR的校验字符集

LAST_INSERT_ID()    

返回最近生成的AUTO_INCREMENT值

其他函数

MD5(STR)    

信息摘要算法

PASSWORD(STR)

 密码算法

ENCODE(str,pwd,str) 

加密结果是二进制数，必须使用BLOB类型字段保存

DECODE(crypt_str,pwd_str)  

对通过EDCODE加密之后的内容解密

FORMAT(x,n)   

将数字x进行格式化，将x保留到小数点后n位

ASCII(s)   

返回字符串s的第一个字符的ASCII码

BIN(x)    

返回x的二进制编码

OCT(x)    

返回x的八进制编码

HEX(x)  

 返回x的十六进制编码

CONV(x,f1,f2)    

将x从f1禁止数变为f2进制数

INET_ATON(IP)   

 将IP地址转换为数字

INET_NTOA(n)    

将数字转换为IP地址

GET_LOCT(name,time)   

定义锁

RELEASE_LOCK(name)    

解锁

查询数据操作

查询记录

SELECT select_test[,select_test2...]
[
 FROM table
 [WHERE 条件]
 [GROUP BY {col_name | position} [ASC | DESC],...分组]
 [HAVING 条件 对分组结果进行二次筛选]
 [ORDER BY{col_name | position}    [ASC | DESC],...排序]
 [LIMIT 限制显示行数]
]

查询表达式

  1. 每一个表达式表示想要的一列，必须至少有一列，多列之间以逗号分割
  2. *表示所有列，tbl_name.*可以表示命名表的所有列
  3. 查询表达式可以使用[AS]alias_name为其赋予别名

WHERE条件

比较

=、<、<=、>=、!=、<>、!>、!<、<=>

指定范围

BETWEEN AND 、NOT BETWEEN AND

指定集合

IN、NOT IN

匹配字符

LIKE、NOT LIKE

是否为空值

IS NULL、IS NOT NULL

多个查询条件

AND、OR

GROUP BY查询结果分组

配合GROUP_CONCAT( )得到分组详解

配合聚合函数

COUNT()、MAX()、MIN()、AVG()、SUM()

配合WITH ROLLUP记录上面所有记录的总和

HAVING子句

通过HAVAING语句对分组结果进行二次筛选

ORDER BY 排序

通过ORDER BY对查询结果进行排序（ASC|DESC）

LIMIT限制查询结果显示条数

LIMIT 显示条数

LIMIT 偏移量，显示条数

子查询

定义

子查询是将一个查询语句嵌套在另一个查询语句中，将内层语句的查询结果作为外层查询的条件

引发子查询的情况

  1. 使用[NOT]IN的子查询
  2. 使用比较运算符的子查询（=、>、<、>=、<=、<>、!=、<=>）
  3. 使用[NOT] EXISTS的子查询
  4. 使用ANY|SOME、ALL的子查询

将查询结果写入数据表

INSERT [INTO] tbl_name [(col_name,....)] SELECT...

创建数据表的同时将查询结果写入到数据表中

CREAETE TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] select_statement

连接查询

什么是连接查询

连接查询是将两个或者喝两个以上的表按照某个条件连接起来，从中选取需要的数据，连接查询是同时查询多个表时使用的，当不同的表存在相同意义的字段时，可以通过该字段连接这几个表

内连接查询

JOIN | CROSS JOIN INNER JOIN

内连接是等值连接，它使用比较运算符(=、>、<、<>...)根据每个表共有的列的值匹配两个表中的行

外连接查询

左外连接

LEFT [OUTER] JOIN

显示左表的全部记录及右表符合条件的记录

右外连接

RIGHT [OUTER]    JOIN

显示右表的全部记录以及左表符合条件的记录

联合查询

UNION

UNION ALL

UNION和UNION ALL区别是UNION去掉相同记录，UNION是简单的合并到一起

外键

外键是表的一个特殊字段，被参照的表是主键，外键所在字段的表为子表，设置外间的原则：依赖于已存在的表的主键，外间的作用是建立该表与其他表的关联关系，父表中对记录做操作时，子表中与之对应的信息也会有相应的变化

外键的作用是保持数据的一致性和完整性

可以实现一对一或者一对多的关系

注意

父表和子表必须使用相同的存储引擎，而且禁止使用临时表

数据表的存储引擎只能为InnoDB

外键列和参照列必须具有相似的数据类型，其中数字的长度与是否有符号位都必须相同，而字符的长度可以不同

外键和参照列必须创建索引，如果外键不存在索引，Mysql会自动创建索引

外键约束的参照操作

CASCADE:从父表删除或者更新且自动删除或更新子表中匹配的行

SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL

RESTRICT：拒绝对父表的删除或更新操作

NO ACTION:标准SQL的关键字，在Mysql中与RESESTRICT相同

索引

什么索引

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度

优缺点

可以提高检索数据的速度

创建和维护索引需要消耗时间

其可以提高查询速度，但是会减慢写入速度

分类

普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引

如何创建索引

创建表时创建索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
...,
[UNIQUE|FULL TEXT |SPATIAL] INDENX | KEY [索引名称]（字段名称[(长度)]
[ASC | DESC])
);

在已经存在的表上创建索引

CREATE [UNIQUE | FULL TEXT | SPATIAL ]INDEX |索引名称 ON 表名 {字段名[（长度）] [ASC | DESC]}

ALTER TABLE tbl_name ADD [UNIQUE | FULL TEXT | SPATIAL] INDEX 索引名 (字段名称[(长度)] [ASC| DESC]);

如何删除索引

DROP INDEX 索引名 ON tbl_name;

ALTER TABLE 表名 DROP INDEX 索引名;

视图

什么视图？

视图是一个“虚表”，是一个逻辑表，本身并不包含数据，不在数据库中以存储的数据值集形式存在其内容由查询定义。作为一个select语句保存在数据字典中的同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ] ; 创建视图需要create view 权限，并且对于查询涉及的列有select权限；使用create or replace 或者 alter修改视图，那么还需要改视图的drop权限。

为什么要创建视图？

  1. 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
  2. 安全：用户只能查询或修改他们所能见到得到的数据
  3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

视图的创建

create view  视图名[(col_list1,col_list2,...)]  as  select 字段名 from 表名;

查看视图

show full tables;

删除视图

drop view 视图名[,视图名1，...];

重命名视图

rename table 视图名 to 新视图名;

笔者也在学习中，如有错误，还请各位多多指正。