5.7 特性

  1. 初始化完成后，随机 Root 密码

  2. 默认安装完成没有 test 数据库，用户自定义创建及权限控制

  3. 默认 SSL 加密

  4. 密码过期策略

  5. 用户锁

  6. 支持 JSON

    服务端提供操作 JSON 的函数，存储方式：将 JSON 编码成 BLOB

  7. 支持两类生成列

    虚拟生成列、存储生成列

  8. 引入系统库

安装配置

配置

默认端口：3306
默认用户：root

免安装配置 Windows

  1. 配置环境变量

  2. 创建配置文件，命名为 my.ini

    basedir = D:\mysql-5.7.20-winx64
    datadir = D:\mysql-5.7.20-winx64\data
    port =3306

  3. 安装 MySQL 服务，执行命令为 mysqld–install

  4. 执行 MySQL 初始化命令

    mysqld –initialize-insecure –user=mysql

  5. 启动 MySQL 服务，并使用用户 root 登录，同时设置登录密码

    net start mysql
    mysqladmin -u -root -p password

  6. 测试是否部署成功

    mysql -u root -p

服务

DOS

net start\stop mysql(mysql 服务器名称)

登录mysql 数据库

mysql -h localhost -u -root -p[密码]

Linux

RPM包、二进制包、源码包（`需要编译二进制后使用`）

卸载

查看当前系统中是否安装 MySQL，`CentOS 7 下，默认安装的数据库为 MariaDB`
rpm  -qa | grep -i mysql
停止服务、卸载
service mysql stop
rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
查找老版本 MySQL 的目录
find / -name mysql
删除目录
rm -rf 目录名

安装、启动

rpm -ivh mysql-community-common-5.7.29-1.el6.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.29-1.el6.x86_64.rpm
rpm -ivh mysql-community-client-5.7.29-1.el6.x86_64.rpm
rpm -ivh mysql-community-server-5.7.29-1.el6.x86_64.rpm
service mysql start | stop | restart | status
mysql -uroot -p
修改密码
set password='testroot'
  • 扩展

    蓝色：目录文件

    绿色：可执行文件

    红色：压缩文件

    浅蓝色：链接文件

    白色：普通文件

    黄色：设备文件

my.cnf 配置文件详解

[client]
port=3306
socket=/var/run/mysql/mysql.sock
[mysqldump]
quick
max_allowed_packet = 16M

socket：套接字文件所在目录
quick：支持较大的数据库转储，导出非常巨大的表时需要此项 。
max_allowed_packet：服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小（当与大的BLOB字段一起工作时相当必要），每个连接独立的大小，大小动态增加。

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /mydata/mysql/data
port=3306
server-id = 1
socket=/var/run/mysql/mysql.sock

user：mysqld 程序在启动后将在给定 UNIX/Linux 账户下执行。mysqld 必须从 root 账户启动才能在启动后切换到另一个账户下执行。mysqld_safe 脚本将默认使用 user=mysql 选项来启动 mysqld 程序。
basedir：指定 MySQL 安装的绝对路径；
datadir：指定 MySQL 数据存放的绝对路径；
port：服务端口号，默认为 3306
server-id：MySQL 服务的唯一编号，每个 MySQL 服务的 id 需唯一。
socket：socket 文件所在目录

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
lower_case_table_names = 1
key_buffer_size=16M
max_allowed_packet=8M
no-auto-rehash
sql_mode=TRADITIONAL

character-set-server：数据库默认字符集，主流字符集支持一些特殊表情符号（特殊表情符占用 4 个字节）
collation-server：数据库字符集对应一些排序等规则，注意要和 character-set-server 对应
init_connect：设置 client 连接 mysql 时的字符集，防止乱码
lower_case_table_names：是否对 sql 语句大小写敏感，1 表示不敏感
key_buffer_size：用于指定索引缓冲区的大小
max_allowed_packet：设置一次消息传输的最大值
no-auto-rehash：仅仅允许使用键值的 UPDATES 和 DELETES
sql_mode：表示 SQL 模式的参数，通过这个参数可以设置检验 SQL 语句的严格程度

Mysql SQL

show

show create table t_user \G;

use

注释

#、--、/* ...... */

SQL 语句的大小写规则

windows\Mac 系统文件名不区分大小写
Linux 系统文件名区分大小写
MySql 用服务器主机的底层文件系统所包含的目录和文件表示数据库和表。数据库名和表名的默认大小写取决于操作系统的命名规则。视图 用文件夹表示
存储函数、存储过程、事件名称不区分大小写，列名、索引不区分大小写
触发器名称区分大小写
表的别名区分大小写

MySql 系统帮助

\h contents

· help_category：关于帮助主题类别的信息
· help_keyword：与帮助主题相关的关键字信息
· help_relation：帮助关键字信息和主题信息之间的映射
· help_topic：帮助主题的详细内容

数据类型

  1. 数值类型

    整型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
    浮点型：FLOAT、DOUBLE
    定点数类型：DECIMAL

类型名称 说明 存储需求
TINYINT -128〜127 1个字节
SMALLINT -32768〜32767 2个宇节
MEDIUMINT -8388608〜8388607 3个字节
INT (INTEGHR) -2147483648〜2147483647 4个字节
BIGINT -9223372036854775808〜9223372036854775807 8个字节

FLOAT [4 个字节] 类型的取值范围如下：
有符号的取值范围：-3.402823466E+38～-1.175494351E-38
无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38

DOUBLE [8 个字节] 类型的取值范围如下：
有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308
无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308

  1. 日期和时间类型

    YEAR、TIME、DATE、DATETIME 和 TIMESTAMP

类型名称 日期范围 存储需求
YEAR
YYYY		 1901 ~ 2155 1 个字节
TIME
HH:MM:SS		 -838:59:59 ~ 838:59:59 3 个字节
DATE
YYYY-MM-DD		 1000-01-01 ~ 9999-12-3 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		 1980-01-01 00:00:01 UTC
2040-01-19 03:14:07 UTC		 4 个字节

  1. 字符串类型

    CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM、SET

  2. 二进制类型

    BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

存储引擎

InnoDB、MyISAM、Memory、Merge、Archive、Federated、CSV、Blackhole

存储引擎 描述
ARCHIVE 用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV 在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE 会丢弃写操作，该操作会返回空内容。
FEDERATED 将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB 具备外键支持功能的事务处理引擎
MEMORY 置于内存的表
MERGE 用来管理由多个 MyISAM 表构成的表集合
MyISAM 主要的非事务处理存储引擎
NDB MySQL 集群专用存储引擎

查看默认引擎

show variables like 'default_storage_engine%'
特性 MyISAM InnoDB MEMORY
存储限制 有 支持 有
事务安全 不支持 支持 不支持
锁机制 表锁 行锁 表锁
B树索引 支持 支持 支持
哈希索引 不支持 不支持 支持
全文索引 支持 不支持 不支持
集群索引 不支持 支持 不支持
数据缓存 - 支持 支持
索引缓存 支持 支持 支持
数据可压缩 支持 不支持 不支持
空间使用 低 高 N/A
内存使用 低 高 中等
批量插入速度 高 低 高
支持外键 不支持 支持 不支持

数据操作

修改表

ALTER TABLE <表名>
{ ADD COLUMN <列名> <类型>[约束条件] （FIRST | AFTER <已存在字段名>）
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

删除关联表

ALTER TABLE 子表 DROP FOREIGN KEY 外键约束;
DROP TABLE 主表;

查看表结构

DESC 表名;
SHOW CREATE TABLE <表名>;

约束

1）主键约束

<字段名> <数据类型> PRIMARY KEY [默认值] (AUTO_INCREMENT)
PRIMARY KEY(字段，字段...)
ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>)
ALTER TABLE <数据表名> DROP PRIMARY KEY

2）外键约束

CONSTRAINT <外键名>] FOREIGN KEY 字段名
REFERENCES <主表名> 主键列

3）唯一约束

<字段名> <数据类型> UNIQU
可以有多个，并且设置唯一约束的列是允许有空值的

4）检查约束

CHECK(表达式)

5）非空约束
6）默认值约束

函数

常用函数汇总

去重

DISTINCT

设置别名

AS

分页查询

LIMIT 初始位置，记录数
LIMIT 记录数 OFFSET 初始位置

WHERE with HAVING

WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名

JOIN

笛卡尔积：两个集合 X 和 Y 的乘积

交叉连接 – 笛卡尔积

内连接

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

外连接

LEFT OUTER JOIN、RIGHT OUTER JOIN

正则表达式

REGEXP

清空表

TRUNCATE TABLE 表名

TRUNCATE 和 DELETE 的区别

DELETE 是 DML 类型的语句；TRUNCATE 是 DDL 类型的语句。它们都用来清空表中的数据。
DELETE 是逐行一条一条删除记录的；TRUNCATE 则是直接删除原来的表，再重新创建一个一模一样的新表，而不是逐行删除表中的数据，执行数据比 DELETE 快。因此需要删除表中全部的数据行时，尽量使用 TRUNCATE 语句， 可以缩短执行时间。
DELETE 删除数据后，配合事件回滚可以找回数据；TRUNCATE 不支持事务的回滚，数据删除后无法找回。
DELETE 删除数据后，系统不会重新设置自增字段的计数器；TRUNCATE 清空表记录后，系统会重新设置自增字段的计数器。
DELETE 的使用范围更广，因为它可以通过 WHERE 子句指定条件来删除部分数据；而 TRUNCATE 不支持 WHERE 子句，只能删除整体。
DELETE 会返回删除数据的行数，但是 TRUNCATE 只会返回 0，没有任何意义。

查看模式

select @@sql_mode

视图

使用视图重新格式化检索出的数据。
使用视图简化复杂的表连接。
使用视图过滤数据。

存储过程

DELIMITER //
CREATE PROCEDURE ShowStuScore()
-> BEGIN
-> SELECT * FROM tb_students_score;
-> END //