MYSQL数据库详解

数据库相关概念

1、数据的时代

• 涉及的数据量大
• 数据不随程序的结束而消失
• 数据被多个应用程序共享
• 大数据

2、数据库的发展史

• 萌芽阶段：文件系统，使用磁盘文件来存储数据
• 初级阶段：第一代数据库，出现了网状模型、层次模型的数据库
• 中级阶段：第二代数据库，关系型数据库和结构化查询语言
• 高级阶段：新一代数据库，“关系-对象”型数据库

3、文件管理系统的缺点

• 编写应用程序不方便
• 数据冗余不可避免
• 应用程序依赖性
• 不支持对文件的并发访问
• 数据间联系弱
• 难以按用户视图表示数据
• 无安全控制功能

4、数据库管理系统

• 数据库是数据的汇集，它以一定的组织形式存于存储介质上
• DBMS：是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系统的核心
• DBA：负责数据库的规划、设计、协调、维护和管理等工作
• 应用程序指以数据库为基础的应用程序

5、数据库管理系统的基本功能

• 数据定义
• 数据处理
• 数据安全
• 数据备份

6、网状数据库

最早出现的是网状DBMS，1964年通用电气公司的Charles Bachman成功地开发出世界上第一个网状IDS，也是第一个数据库管理系统，IDS 具有数据模式和日志的特征，只能在GE主机运行。

7、层次数据库

8、数据库系统的架构

• 单机架构
• 大型主机/终端架构
• 主从式架构（C/S）
• 分布式架构

9、关系型数据库

• 关系 ：关系就是二维表，其中：表中的行、列次序并不重要

• 行row：表中的每一行，又称为一条记录

• 列column：表中的每一列，称为属性，字段

• 主键Primary key：用于惟一确定一个记录的字段

• 域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值

• RDBMS：

  MySQL: MySQL, MariaDB, Percona Server
  PostgreSQL: 简称为pgsql，EnterpriseDB
  Oracle
  MSSQL
  DB2
  

• 数据库排名：https://db-engines.com/en/ranking

10、实体-联系模型E-R

• 实体Entity：客观存在并可以相互区分的客观事物或抽象事件称为实体
  • 在E-R图中用矩形框表示实体，把实体名写在框内
• 属性：实体所具有的特征或性质
• 联系：联系是数据之间的关联集合，是客观存在的应用语义链
  • 实体内部的联系：指组成实体的各属性之间的联系。如职工实体中，职工号和部门经理号之间有一种关联关系
  • 实体之间的联系：指不同实体之间联系。例：学生选课实体和学生基本信息实体之间
  • 实体之间的联系用菱形框表示

联系类型

• 一对一联系(1:1)
• 一对多联系(1:n)
• 多对多联系(m:n)

数据的操作

• 数据提取：在数据集合中提取感兴趣的内容。SELECT
• 数据更新：变更数据库中的数据。INSERT、DELETE、UPDATE

数据的约束条件：：是一组完整性规则的集合

• 实体（行）完整性 Entity integrity
• 域（列）完整性 Domain Integrity
• 参考完整性 Referential Integrity

11、简易数据规划流程

第一阶段：收集数据，得到字段

• 收集必要且完整的数据项
• 转换成数据表的字段

第二阶段：把字段分类，归入表，建立表的关联

• 关联：表和表间的关系
• 分割数据表并建立关联的优点
• 节省空间
• 减少输入错误
• 方便数据修改

第三阶段：

• 规范化数据库

12、数据库的正规化分析

• 数据库规范化，又称数据库或资料库的正规化、标准化，是数据库设计中的一系列原理和技术，以减少数据库中数据冗余，增进数据的一致性。关系模型的发明者埃德加·科德最早提出这一概念，并于1970年代初定义了第一范式、第二范式和第三范式的概念
• RDMBS设计范式基础概念：设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，不同的规范要求被称为不同范式，各种范式呈递次规范，越高的范式数据库冗余越小
• 目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般数据库只需满足第三范式(3NF）即可

范式

第一范式（1NF）

所谓第一范式（1NF）是指在关系模型中，对域添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

第二范式（2NF）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

第三范式（3NF）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不能包含已在其它关系已包含的非主关键字信息。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

SQL 概念

1、SQL: Structure Query Language

• 结构化查询语言
• SQL解释器：
• 数据存储协议：应用层协议，C/S

2、S：server

监听于套接字，接收并处理客户端的应用请求。

3、C：Client

客户端程序接口：CLI 和 GUI
应用编程接口：ODBC：Open Database Connectivity 和 JDBC：Java Data Base Connectivity

数据库：数据集合

1、表

表：为了满足范式设计要求，将一个数据集分拆为多个。

2、约束

约束，constraint，表中的数据要遵守的限制。

• 主键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；必须提供数据，不能为空，即NOT NULL，一个表只能有一个
• 惟一键：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行；允许为NULL，一个表可以存在多个
• 外键：一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据
• 检查：字段值在一定范围内

3、索引

将表中的一个或多个字段中的数据复制一份另存，并且按特定次序排序存储。

常用的索引类型：B+ Tree：Balance Tree；hash

注意：有助于读请求，但不利于写请求。

4、关系运算

• 选择：挑选出符合条件的行
• 投影：挑选出需要的字段
• 连接：表间字段的关联

5、数据抽象

• 物理层：数据存储格式，即RDBMS在磁盘上如何组织文件
• 逻辑层：DBA角度，描述存储什么数据，以及数据间存在什么样的关系
• 视图层：用户角度，描述DB中的部分数据

6、关系模型的分类

• 关系模型
• 实体-关系模型
• 基于对象的关系模型
• 半结构化的关系模型：XML数据

MySQL

1、MySQL 历史

• 1979年：TcX公司 Monty Widenius，Unireg
• 1996年：发布MySQL1.0，Solaris版本，Linux版本
• 1999年：MySQL AB公司，瑞典
• 2003年：MySQL 5.0版本，提供视图、存储过程等功能
• 2008年：Sun 收购
• 2009年：Oracle收购sun
• 2009年：Monty成立MariaDB

2、MySQL 系列

• 官方地址

  https://www.mysql.com/
  http://mariadb.org/
  https://www.percona.com
  

• 官方文档

  https://dev.mysql.com/doc/
  https://mariadb.com/kb/en/
  https://www.percona.com/software/mysql-database/percona-server
  

• 版本演变

  MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 -->8.0
  MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3
  

3、MySQL 特性

插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎。

• (1) 更多的存储引擎

  MyISAM：不支持事务，表级锁，崩溃后不保证安全恢复；
  MyISAM --> Aria
  InnoDB --> XtraDB ：支持事务，行级锁，外键，热备；
  

• (2) 诸多扩展和新特性

• (3) 提供了较多的测试组件

• (4) truly open source

4、MySQL 安装（Mariadb）

• 1、源代码：编译安装
• 2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
• 3、程序包管理器管理的程序包

5、SQL 语言规范

• 在数据库系统中，SQL语句不区分大小写(建议用大写)

• SQL语句可单行或多行书写，以“;”结尾

• 关键词不能跨多行或简写

• 用空格和缩进来提高语句的可读性

• 子句通常位于独立行，便于编辑，提高可读性、

• 注释：

  SQL标准：
  	/*注释内容*/ 多行注释
  	-- 注释内容 单行注释，注意有空格
  MySQL注释：
  		#
  

6、数据库对象

• 数据库的组件(对象)：

  数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
  

• 命名规则：

  必须以字母开头
  可包括数字和三个特殊字符（# _ $）
  不要使用MySQL的保留字
  同一database(Schema)下的对象不能同名
  

7、SQL 语句分类

• DDL：Data Defination Language 数据定义语言

  CREATE，DROP，ALTER
  

• DML：Data Manipulation Language 数据操纵语言

  INSERT，DELETE，UPDATE
  

• DCL：Data Control Language 数据控制语言

  GRANT，REVOKE，COMMIT，ROLLBACK
  

• DQL：Data Query Language 数据查询语言

  SELECT
  

8、SQL 语句构成

Keyword组成clause
多条clause组成语句

示例：

SELECT *          # SELECT子句
FROM products     # FROM子句
WHERE price>400   # WHERE子句

说明：一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

MariaDB程序的组成：C/S

1、C：Client --> mysql protocol --> Server

• mysql: 交互式的CLI工具
• mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中
• mysqladmin：基于mysql协议管理mysqld
• mysqlimport：数据导入工具

2、S：Server

mysqld
mysqld_safe：建议运行服务端程序
mysqld_multi：多实例

三类套接字：

• IPv4|IPv6：3306/tcp
• Unix Sock：/var/lib/mysql/mysql.sock, /tmp/mysql.sock 文件类型是 s 实现套接字通信的

特殊说明;
C <--> S: localhost, 127.0.0.1 (只有使用 localhost 或者 127.0.0.1 才能使用 Unix Sock 通信，其它地址就是使用 3306套接字 进行的)

3、配置文件

ini风格，用一个文件为多个程序提供配置。

集中式的配置，能够为mysql的各应用程序提供配置信息。

[mysql]        客户端
[mysqld]       服务器端
[mysqld_safe]
[server]
[client]
[mysqldump]

格式：parameter = value
说明：_ 和 - 相同（1，ON，TRUE意义相同， 0，OFF，FALSE意义相同）

mysql的各类程序启动都读取不止一个配置文件，按顺序读取，且最后读取的为最终生效。

# my_print_defaults
Default options are read from the following files in the given order:
/etc/mysql/my.cnf 
/etc/my.cnf
 ~/.my.cnf 
				
片段式配置：
/etc/my.cnf + /etc/my.cnf.d/*.cnf

命令行交互式客户端程序：mysql

命令行交互式客户端程序：mysql （命令其实不分大小写，但是为了约定俗成，客户端命令使用小写，服务端命令使用大写）。

1、语法格式

mysql [OPTIONS] [database]

2、常用选项

-u, --user=username：用户名，默认为root
-h, --host=hostname：远程主机（即mysql服务器）地址，默认为localhost;
	# 客户端连接服务端，服务器会反解客户的IP为主机名，关闭此功能(skip_name_resolve=ON)
-p, --password[=PASSWORD]：USERNAME所表示的用户的密码； 默认为空

-P, --port=#：mysql服务器监听的端口；默认为3306/tcp
-S, --socket=/PATH/TO/mysql.sock：套按字文件路径				
						
-D, --database=DB_name：连接到服务器端之后，设定其处指明的数据库为默认数据库
-e, --execute='SQL STATEMENT'：连接至服务器并让其执行此命令后直接返回

加固 mysql

加固mysql服务器，在安装完成后，运行mysql_secure_installation命令。

[root@Neo ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):  # 默认空密码，直接回车
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y                         # 是否设定 root 用户的密码，Y 代表设定
New password:                                      # root
Re-enter new password:                             # root
Password updated successfully!
Reloading privilege tables..
 ... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y                     # 是否移除默认用户，为了安全，肯定需要移除
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y                # 是否禁止 root 用户远程登陆，肯定禁止
 ... Success!                                        # 为了安全，以后只能使用单独授权的用户进行远程登陆

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n       # 是都需要删除 test 的数据库
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y                     # 是否重载授权表，需要重载
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

mysql 的用户账号组成

mysql 的用户账号由两部分组成：'USERNAME'@'HOST'; 其中HOST用于限制此用户可通过哪些远程主机连接当前的 mysql 服务。

HOST的表示方式，支持使用通配符：

%：匹配任意长度的任意字符
	172.16.%.%,  172.16.0.0/16
_：匹配任意单个字符

3、命令

客户端命令：本地执行

mysql> help
	\u db_name：设定哪个库为默认数据库
	\q：退出
	\d CHAR：设定新的语句结束符
	\g：语句结束标记
	\G：语句结束标记，结果竖排方式显式
	\s：显示服务器状态
	\!: 执行shell命令
	\.: 装载并运行sql脚本

服务端命令：通过mysql连接发往服务器执行并取回结果（SQL语句）

注意：mysql 进程都是以 mysql 用户去运行的，所以需要 mysql 程序执行的文件，一定要能被 mysql 用户可读。

DDL， DML， DCL

注意：每个语句必须有语句结束符，默认为分号 (;)

获取帮助：
mysql> help contents
	Administration
	Account Management
	Data Definition
	Data Manipulation
	Data Types

myslq> help '命令类别'

4、示例

MariaDB [(none)]> SHOW DATABASES;      # 当前存在的数据库名称
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> help                  # 显示帮助

General information about MariaDB can be found at
http://mariadb.org

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

MariaDB [(none)]> help contents             # 显示命令类别
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   ... ...
   User-Defined Functions
   Utility

MariaDB [(none)]> help 'DATA Types'         # 查看具体命令类别的帮助
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   ... ...
   VARBINARY
   VARCHAR
   YEAR DATA TYPE
MariaDB [(none)]> CREATE TABLE\c           # \c 取消命令执行
MariaDB [none]> \u mysql                   # 使用 mysql 数据库
MariaDB [mysql]> SELECT * FROM user;       # 默认横向显示
MariaDB [mysql]> SELECT * FROM user\G      # 纵向显示
MariaDB [mysql]> \! ls /var
adm  cache  crash  db  empty  games  gopher  kerberos  lib  local  lock  log  mail  nis  opt  preserve	run  spool  tmp  www  yp
MariaDB [mysql]> 

[root@neo ~]# ps -aux | grep mysql
mysql    16335  0.0  0.0 113308  1616 ?        Ss   02:13   0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql    16497  0.1  1.1 969248 89448 ?        Sl   02:13   0:07 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysq/mysql.sock
root     26676  0.0  0.0 112708   976 pts/0    S+   04:03   0:00 grep --color=auto mysql

mysql 进程都是以 mysql 用户去运行的，所以需要 mysql 程序执行的文件，一定要能被 mysql 用户可读


MariaDB [(none)]> SHOW CHARACTER SET;        # 支持的字符集
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+
39 rows in set (0.00 sec)
MariaDB [(none)]> SHOW COLLATION;         # 查看字符集的排序规则
+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
+----------+-----------------------------+---------------------+--------+

mysql 数据类型

1、表：行和列

创建表：定义表中的字段。

定义字段时，关键的一步即为确定其数据类型。用于确定：数据存储格式、能参与运算种类、可表示的有效的数据范围。

2、字符型：字符集

码表：在字符和二进制数字之间建立映射关系。

mysql> SHOW CHARACTER SET; 
mysql> SHOW COLLATION;

3、数据种类

字符型

char(n)      # 固定长度，最多255个字符
varchar(n)   # 可变长度，最多65535个字符
tinytext     # 可变长度，最多255个字符
text         # 可变长度，最多65535个字符
mediumtext   # 可变长度，最多2的24次方-1个字符
longtext     # 可变长度，最多2的32次方-1个字符
BINARY(M)    # 固定长度，可存二进制或字符，长度为0-M字节
VARBINARY(M) # 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：
	ENUM     # 枚举
	SET      # 集合

• 定长字符型

  CHAR(#)      # 不区分字符大小写，排序时会有影响
  BINARY(#)    # 区分字符大小写，排序时会有影响
  

• 变长字符型

  VARCHAR(#)       # 不区分字符大小写，多占一个或两个字符空间（要有结束符，因为变长，不知道如何结束）
  VARBINARY(#)     # 区分字符大小写 
  

• 对象存储

  TEXT                          # 不区分字符大小写
  	TINYTEXT、SMALLTEXT、MEDIUMTEXT、TEXT、LONGTEXT
  BLOB：Binary Large OBject     # 区分字符大小写
  	TINYBLOB，SMALLBLOB，MEDIUMBLOB，BLOB，LONGBLOB；
  

• 内置类型

  SET     # 集合
  ENUM    # 只能选择固定的数据	
  

char 和 varchar

• 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
• 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
• 3.char类型的字符串检索速度要比varchar类型的快

varchar 和 text

• 1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节
• 2.text类型不能有默认值
• 3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text

数值型

• 精确数值型

  INT（TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT） 整数
  	UNSIGNED # 是无符号
  		TINYINT          # 范围是指 -128~127
  		TINYINT UNSIGNED # 范围是指 0~255
  DECIMAL
  

• 近似数值型

  FLOAT
  DOBULE
  

整型

tinyint(m)        # 1个字节 范围(-128~127)
smallint(m)       # 2个字节 范围(-32768~32767)
mediumint(m)      # 3个字节 范围(-8388608~8388607)
int(m)            # 4个字节 范围(-2147483648~2147483647)
bigint(m)         # 8个字节 范围(+-9.22*10的18次方)
BOOL，BOOLEAN    # 布尔型，是TINYINT(1)的同义词。zero值被视为假，非zero值视为真

注意：

• 加了unsigned，则最大值翻倍，如：tinyint unsigned的取值范围为(0~255)
• int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和Int(20)是相同的

浮点型(float和double)，近似值

• float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位
• double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。

定点数

• 在数据库中存放的是精确值,存为十进制
• decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
• MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节
• 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占用8个字节
• 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

日期时间型

• 日期型：DATE
• 时间型：TIME
• 日期时间型：DATETIME
• 时间戳：TIMESTAMP UNIX元年至今的秒数
• 年份：YEAR(2), YEAR(4)

字段数据修饰符

• 所有类型：

  NULL                # 数据列可包含NULL值
  NOT NULL            # 数据列不允许包含NULL值
  DEFAULT             # 默认值
  PRIMARY KEY         # 主键，唯一，非空
  UNIQUE KEY          # 唯一键，唯一，可空
  CHARACTER SET name  # 指定一个字符集
  

• 数值型

  AUTO_INCREMENT      # 自动递增，适用于整数类型
  UNSIGNED            # 无符号
  

服务器端命令

1、类别

• DDL：数据定义语言，主要用于管理数据库组件，例如数据库、表、索引、视图、用户、存储过程（CREATE、ALTER、DROP）
• DML：数据操纵语言，主要用管理表中的数据，实现数据的增、删、改、查（INSERT， DELETE， UPDATE， SELECT）

2、获取命令帮助

mysql> help  KEYWORD

3、数据库管理

创建

CREATE  {DATABASE | SCHEMA}  [IF NOT EXISTS]  db_name;
	# 一般在创建数据库或者表时，需要带 IF NOT EXISTS ，这样的就不会报错，只会显示告警信息
	# 常用于脚本中，避免在创建数据库时，因数据库存在报错而导致退出脚本执行
	# 脚本一报错，就就会终止执行，立即退出
	
[DEFAULT]  CHARACTER SET [=] charset_name
[DEFAULT]  COLLATE [=] collation_name

查看支持的所有字符集      # SHOW CHARACTER SET 
查看支持的所有排序规则    # SHOW  COLLATION

注意：创建数据库后，会在此目录生成一个子目录；在此目录下创建一个目录，属主属组改为 mysql ，也相当于在 mysql 里添加了一个数据库。

[root@neo ~]# ll /var/lib/mysql/   
total 28700
-rw-rw----. 1 mysql mysql    16384 Aug  7 02:13 aria_log.00000001
-rw-rw----. 1 mysql mysql       52 Aug  7 02:13 aria_log_control
-rw-rw----. 1 mysql mysql 18874368 Aug  7 02:13 ibdata1
-rw-rw----. 1 mysql mysql  5242880 Aug  7 02:13 ib_logfile0
-rw-rw----. 1 mysql mysql  5242880 Aug  7 02:13 ib_logfile1
drwx------. 2 mysql mysql     4096 Aug  7 02:13 mysql
srwxrwxrwx. 1 mysql mysql        0 Aug  7 02:13 mysql.sock
drwx------. 2 mysql mysql     4096 Aug  7 02:13 performance_schema
drwx------. 2 mysql mysql        6 Aug  7 02:13 test

告警信息查看

MySQL的告警信息SHOW WARNING;和 shell 命令里的 echo $?一样。只能查看上条命令的告警信息（或执行状态返回值）。大家在实际使用时要注意。

MariaDB [hidb]> CREATE DATABASE hidb;       # 会报错，中断脚本执行
ERROR 1007 (HY000): Can't create database 'hidb'; database exists

MariaDB [hidb]> SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Error | 1007 | Can't create database 'hidb'; database exists |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [hidb]> CREATE DATABASE IF NOT EXISTS hidb;
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [hidb]> SHOW WARNINGS;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1007 | Can't create database 'hidb'; database exists |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [hidb]> SHOW WARNING;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WARNING' at line 1
MariaDB [hidb]> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                     |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WARNING' at line 1 |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [hidb]> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-08-11 10:47:47
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

MariaDB [hidb]> SHOW TABLE STATUS\G;       # 注意有分号和没有分号的区别
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1
    Create_time: 2019-08-11 10:47:47
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: No query specified

修改

ALTER {DATABASE | SCHEMA}  [db_name]
	[DEFAULT]  CHARACTER SET [=] charset_name
	[DEFAULT]  COLLATE [=] collation_name

删除

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

删除注意事项

MySQL 没有回收站，注意没有回收站。进行删除后无法恢复。所以大家想对某些数据库进行删除操作时，可以把相应的数据库目录进行转移。再确定不使用时，再进行删除。

数据库目录默认路径：# /var/lib/mysql/

查看

help SHOW DATABASES 
SHOW DATABASES LIKE  ’‘;
	# LIKE 后可跟 通配符

SHOW DATABASES LIKE '%db';    # 显示以 db 为结尾的数据库					

4、表管理

• 表：二维关系

• 设计表：遵循规范

• 定义：字段，索引

  字段：字段名，字段数据类型，修饰符
  约束，索引：应该创建在经常用作查询条件的字段上
  

表创建

CREATE TABLE  [IF NOT EXISTS]  [db_name.]tbl_name  (create_defination)  [table_options]

create_defination:
	字段：col_name  data_type
	键：
		PRIMARY KEY (col1, col2, ...)
		UNIQUE KEY  (col1, col2,...)
		FOREIGN KEY (column)
	索引：
		KEY|INDEX  [index_name]  (col1, col2,...)
		
	table_options：
		ENGINE [=] engine_name
		CHARACTER SET [=] charset_name
		COLLATE [=] collation_name

	查看数据库支持的所有存储引擎类型：
		mysql> SHOW  ENGINES;
						
	查看某表的状态信息：
		mysql> SHOW  TABLES  STATUS  [LIKE  'tbl_name'][WHERE clause]

创建表的示例：
CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED);
DESC students;
CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表修改

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name  [alter_specification [, alter_specification] ...]
					
	alter_specification:
		字段：
			添加：ADD  [COLUMN]  col_name  data_type  [FIRST | AFTER col_name ]
			删除：DROP  [COLUMN] col_name 
			修改：
				CHANGE [COLUMN] old_col_name new_col_name column_definition  [FIRST|AFTER col_name]	
				MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]
		键：
			添加：ADD  {PRIMARY|UNIQUE|FOREIGN}  KEY (col1, col2,...)
			删除：
				主键：DROP PRIMARY KEY
				外键：DROP FOREIGN KEY fk_symbol
		索引：
			添加：ADD {INDEX|KEY} [index_name]  (col1, col2,...)
			删除：DROP {INDEX|KEY}  index_name
		表选项：
			ENGINE [=] engine_name
						
查看表上的索引的信息：
	mysql> SHOW INDEXES FROM tbl_name;

修改表的示例：
ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f')
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students drop primary key ;
ALTER TABLE students ADD UNIQUE KEY(name);
ALTER TABLE students ADD INDEX(age);
ALTER TABLE students drop primary key ;
DESC students;
SHOW INDEXES FROM students;
ALTER TABLE students DROP age;
Help ALTER TABLE                     # 查看帮助

表删除

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

表查看

# SHOW ENGINES                          # 查看所有的引擎
# SHOW TABLES [FROM db_name]            # 查看表 
# DESC [db_name.]tb_name                # 查看表结构
# SHOW COLUMNS FROM [db_name.]tb_name   # 查看表结构

# SHOW CREATE TABLE tbl_name            # 查看表创建命令
# SHOW TABLE STATUS LIKE 'tbl_name’     # 查看表状态
# SHOW TABLE STATUS FROM db_name        # 查看库中所有表状态

MariaDB [hidb]> SHOW TABLES;
+----------------+
| Tables_in_hidb |
+----------------+
| students       |
+----------------+
1 row in set (0.00 sec)

MariaDB [hidb]> DESC students;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| stuid     | int(11)             | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)         | NO   | MUL | NULL    |                |
| gender    | enum('M','F')       | YES  |     | NULL    |                |
| birthdata | date                | YES  |     | NULL    |                |
| classid   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [hidb]> SHOW COLUMNS FROM students;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| stuid     | int(11)             | NO   | PRI | NULL    | auto_increment |
| name      | varchar(20)         | NO   | MUL | NULL    |                |
| gender    | enum('M','F')       | YES  |     | NULL    |                |
| birthdata | date                | YES  |     | NULL    |                |
| classid   | tinyint(3) unsigned | YES  |     | NULL    |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

MariaDB [hidb]> SHOW TABLE STATUS;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| students | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |        16384 |         0 |              1 | 2019-08-11 10:47:47 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

表的引用方式

tbl_name
db_name.tbl_name

第二种创建方式

复制表结构：
	CREATE TABLE tbl_name LIKE other_table_name

第三种创建方式

复制表数据：
	CREATE TABLE tbl_name () SELECT clause

创建表的三种方式（汇总）：CREATE TABLE

• 直接创建

• 通过查询现存表创建，新表会被直接插入查询而来的数据

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement
  

• 通过复制现存的表的表结构创建，但不复制数据

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
  

注意：Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎：

• 同一库中不同表可以使用不同的存储引擎
• 同一个库中表建议要使用同一种存储引擎类型

5、索引管理

• 索引是特殊的数据结构

• 索引要有索引名称

• 创建

  CREATE  [UNIQUE|FULLTEXT|SPATIAL] INDEX  index_name  [BTREE|HASH]  ON tbl_name (col1, col2,,...)
  

• 查看

  SHOW INDEXES FROM [db_name.]tbl_name;
  

• 删除

  DROP  INDEX index_name ON tbl_name
  

DML

DML：INSERT， DELETE， UPDATE， SELECT

1、INSERT INTO

INSERT  [INTO]  tbl_name  [(col1,...)]  {VALUES|VALUE}  (val1, ...),(...),...

注意：
	字符型：引号
	数值型：不能用引号

2、SELECT

(1) SELECT  *  FROM  tbl_name[, tbl_name_2];
	返回指定表的所有数据；慎用（一般都需要带 WHERE 条件）

(2) SELECT  col1, col2, ...  FROM  tbl_name;
	显示时，字段可以显示为别名
		col_name  AS  col_alias

(3) SELECT  col1, ...  FROM tbl_name  WHERE clause; 
	WHERE clause：用于指明挑选条件
		col_name 操作符 value：
			age > 30; 
								
		操作符(1) ：
			>, <, >=, <=, ==, !=
								
			组合条件：
				and 
				or
				not
								
		操作符(2) ：
			BETWEEN ...  AND ...
			
			LIKE 'PATTERN'    # 通配符
						 %    # 任意长度的任意字符
						 _    # 任意单个字符
					
			RLIKE  'PATTERN'  # 正则表达式对字符串做模式匹配，索引失效，不建议使用
				
			IS NULL
			
			IS NOT NULL

(4) SELECT col1, ... FROM tbl_name  [WHERE clause]  ORDER BY  col_name, col_name2, ...  [ASC|DESC];
	ASC     # 升序
	DESC    # 降序

(5) 分组：
	GROUP BY：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算
		count(), sum(), avg(), max(), min()					
	HAVING：对聚合的结果做条件过滤

(6) 其它：
	LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
	
	对查询结果中的数据请求施加“锁”
		# FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
		# LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

3、DELETE

DELETE   FROM  tbl_name  [WHERE where_condition]  [ORDER BY ...]  [LIMIT row_count]
					
(1) DELETE  FROM  tbl_name  WHERE where_condition 
(2) DELETE  FROM  tbl_name  [ORDER BY ...]  [LIMIT row_count]

4、UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference  SET col_name1=value1 [, col_name2=value2] ... [WHERE where_condition]  [ORDER BY ...] [LIMIT row_count]

5、示例

DESC students;
INSERT INTO students VALUES(1,'tom'，'m'),(2,'alice','f');
INSERT INTO students(id,name) VALUES(3,'jack'),(4,'allen');
SELECT * FROM students WHERE id < 3;
SELECT * FROM students WHERE gender='m';
SELECT * FROM students WHERE gender IS NULL;
SELECT * FROM students WHERE gender IS NOT NULL;
SELECT * FROM students ORDER BY name DESC LIMIT 2;
SELECT * FROM students ORDER BY name DESC LIMIT 1,2;
SELECT * FROM students WHERE id >=2 and id <=4
SELECT * FROM students WHERE BETWEEN 2 AND 4
SELECT * FROM students WHERE name LIKE ‘t%’
SELECT * FROM students WHERE name RLIKE '.*[lo].*';
SELECT id stuid,name as stuname FROM students

用户账号及权限管理

1、用户账号

'username'@'host'
	host：此用户访问当前mysql服务器时，允许其通过哪些主机远程创建连接；
		表示方式：IP，网络地址、主机名、通配符(%和_)；
						
	禁止检查主机名：my.cnf
		[mysqld]
		skip_name_resolve = ON

2、创建用户账号

CREATE  USER   'username'@'host'  [IDENTIFIED BY  'password'];

3、删除用户账号

DROP USER  ’user‘@’host' [, user@host] ...

4、授权

权限级别：管理权限、数据库、表、字段、存储例程。

GRANT priv_type,...  ON  [object_type] db_name.tbl_name TO 'user'@'host' [IDENTIFIED BY 'password'];

	priv_type： ALL  [PRIVILEGES]
	db_name.tbl_name：
		*.*：所有库的所有表；
		db_name.*：指定库的所有表；
		db_name.tbl_name：指定库的特定表；
		db_name.routine_name：指定库上的存储过程或存储函数；
						
	[object_type]
		TABLE
		FUNCTION
		PROCEDURE

查看指定用户所获得的授权：
	SHOW GRANTS FOR  'user'@'host'				
	SHOW GRANTS FOR CURRENT_USER;
						
回收权限：
	REVOKE  priv_type, ...  ON  db_name.tbl_name  FROM  'user'@'host';

注意：MariaDB服务进程启动时，会读取mysql库的所有授权表至内存中

• (1) GRANT或REVOKE命令等执行的权限操作会保存于表中，MariaDB此时一般会自动重读授权表，权限修改会立即生效
• (2) 其它方式实现的权限修改，要想生效，必须手动运行FLUSH PRIVILEGES命令方可

图形管理组件

phpMyAdmin     # 运行于 lamp
Navicat
Mysql-Front
ToadForMySQL
SQLyog