MySQL基础知识学习（一）

一、MySQL的SQL语言包含四个部分

1、数据定义语言（Data Definition Language，DDL）

用来创建、修改或是删除数据库以及表等对象，主要包含以下几种指令：

• DROP：删除数据库和表等对象
• CREATE：创建数据库和表等对象
• ALTER：修改数据库和表等对象的结构

2、数据操作语言（Data Manipulation Language，DML）

用来变更表中的记录，主要包含以下几种命令：

• SELECT：查询表中的数据
• INSERT：向表中插入新数据
• UPDATE：更新表中的数据
• DELETE：删除表中的数据

3、数据查询语言（Data Query Language，DQL）

用来查询表中的记录，来查询表中的数据，主要包含命令：

• SELECT：查询表中的数据

4、数据控制语言（Data Control Language，DCL）

用来确认或者取消对数据库中的数据进行的变更；除此之外，还可以对数据库中的用户设定权限。
主要包含以下几种命令：

• GRANT：赋予用户操作权限
• REVOKE：取消用户的操作权限
• COMMIT：确认对数据库中的数据进行的变更
• ROLLBACK：取消对数据库中的数据进行的变更

二、MySQL的SQL语句大小写规则

1、MySQL的关键字和函数名不区分大小写

下面这些语句都是等价的：

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2020-11-25 10:57:19 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2020-11-25 10:57:31 |
+---------------------+
1 row in set (0.00 sec)

mysql> sElect nOw();
+---------------------+
| nOw()               |
+---------------------+
| 2020-11-25 10:57:43 |
+---------------------+
1 row in set (0.00 sec)

2、MySQL数据库名、表名和视图名在Windows不区分大小写，在Linux系统区分大小写

MySQL 用服务器主机的底层文件系统所包含的目录和文件来表示数据库和表。因此，数据库名和表名的默认大小写取决于服务器主机的操作系统在命名方面的规定。

比如Windows系统的文件名不区分大小写，所以运行在 Windows 系统上面的MySQL服务器也不用区分数据库名和表名的大小写。Linux系统的文件名区分大小写，所以运行在Linux系统上的MySQL服务器需要区分数据库名和表名的大小写。对于MacOS X平台，其文件系统中的名字是个例外，它们不区分大小写。

MySQL 会使用一个文件来表示一个视图，所以以上与表有关的也同样适用于视图。

3、MySQL的存储程序名字不区分大小写，触发器的名字要区分大小写

存储函数、存储过程和事件的名字都不区分大小写；触发器的名字要区分大小写，这一点与标准 SQL 的行为有所不同。

4、MySQL的列名和索引名不区分大小写

下面这些语句都是等价的：

mysql> select Name from temptable;
+------------+
| Name       |
+------------+
| DONGSANJUN |
+------------+
1 row in set (0.00 sec)

mysql> select NaMe from temptable;
+------------+
| NaMe       |
+------------+
| DONGSANJUN |
+------------+
1 row in set (0.00 sec)

5、默认情况下，表的别名要区分大小写

SQL 语句中可以使用任意的大小写（大写、小写或大小写混用）来指定一个别名。如果需要在同一条语句里多次用到同一个别名，则必须让它们的大小写保持一致。

注意：

• SQL开发人员习惯对SQL本身的关键字进行大写，而对表或者列的名称使用小写，这样可以提高代码的可阅读性和可维护性。
• 在SQL语句中直接书写的字符串、日期或者数字等称为常数，需要使用英文单引号’'括起来。
• 表名、列名不是字符串，不能使用单引号。在MySQL中可以用倒引号`(在键盘Tab键上面)把表名和列名括起来。
• SQL语句的单词之间必须使用半角空格（英文空格）或换行符来进行分隔。

三、MySQL系统帮助

mysql> HELP

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

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.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

列举一些其他帮助指令：

mysql> HELP 'CREATE TABLE'
mysql> HELP 'INT';
mysql> HELP 'Data Types';
mysql> HELP'contents';

拓展

MySQL 提供了 4 张数据表来保存服务端的帮助信息，即使用HELP语法查看的帮助信息。执行语句就是从这些表中获取数据并返回给客户端的，MySQL提供的 4 张数据表如下：

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

四、MySQL数据类型简介

1、使用帮助指令 help ‘Data Types’ 查看MySQL支持的数据类型

mysql> 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
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL的数据类型有大概可以分为5种：整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

• 数值类型
  整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。

• 日期/时间类型
  包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

• 字符串类型
  包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。

• 二进制类型
  包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

注意：整数类型和浮点数类型可以统称为数值数据类型。定义字段的数据类型对数据库的优化是十分重要的。如果让 MySQL 把一个字符串列当作一个数字列来对待，会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。而且在计算过程中使用这样的列，会导致 MySQL 不会使用这些列上的任何索引，从而进一步降低查询的速度。

五、MySQL系统变量

1、可以使用以下命令查看与当前会话相关的所有会话变量以及全局变量：

• SHOW GLOBAL VARIABLES [LIKE <需要匹配的字符>];
• SHOW SESSION VARIABLES [LIKE <需要匹配的字符>];

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)


mysql> SHOW SESSION VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec)

2、可以通过以下方法设置系统变量：

• 修改 MySQL 源代码，然后对 MySQL 源代码重新编译（该方法适用于 MySQL 高级用户，这里不做阐述）。
• 在 MySQL 配置文件（mysql.ini 或 mysql.cnf）中修改 MySQL 系统变量的值（需要重启 MySQL 服务才会生效）。
• 在 MySQL 服务运行期间，使用 SET 命令重新设置系统变量的值。

3、SET命令修改系统变量

需要注意的是，更改全局变量只影响更改后连接客户端的相应会话变量，而不会影响目前已经连接的客户端的会话变量（即使客户端执行 SET GLOBAL 语句也不影响）。也就是说，对于修改全局变量之前连接的客户端只有在客户端重新连接后，才会影响到客户端。

设置全局变量的值的方法如下：
SET @@global.innodb_file_per_table=default;
SET @@global.innodb_file_per_table=ON;
SET global innodb_file_per_table=ON;

客户端连接时，当前全局变量的值会对客户端的会话变量进行相应初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。

设置会话变量的值的方法如下：
SET @@session.pseudo_thread_id=5;
SET session pseudo_thread_id=5;
SET @@pseudo_thread_id=5;
SET pseudo_thread_id = 5;

如果没有指定修改全局变量还是会话变量，服务器会当作会话变量来处理。比如：
SET @@sort_buffer_size = 50000;

注意：
使用 SET 设置全局变量或会话变量成功后，如果 MySQL 服务重启，数据库的配置就又会重新初始化。一切按照配置文件进行初始化，全局变量和会话变量的配置都会失效。

MySQL 中还有一些特殊的全局变量，如 log_bin、tmpdir、version、datadir，在 MySQL 服务实例运行期间它们的值不能动态修改，也就是不能使用 SET 命令进行重新设置，这种变量称为静态变量。数据库管理员可以使用前面提到的修改源代码或更改配置文件来重新设置静态变量的值。

六、MySQL存储引擎

1、支持的存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 提供了多个不同的存储引擎：

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

2、修改存储引擎

查看当前默认存储引擎：

mysql> SHOW VARIABLES LIKE 'default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

设置默认存储引擎：

SET default_storage_engine=< 存储引擎名 >

但是当再次重启客户端时，默认存储引擎仍然是 InnoDB。如果希望永久修改默认的存储引擎，就需要修改 my.cnf 配置文件。在 my.cnf 配置文件的 [mysqld] 后面加入以下语句：

default-storage-engine=存储引擎名称

MySQL中修改数据表的存储引擎的语法格式如下：

ALTER TABLE <表名> ENGINE=<存储引擎名>;

感谢分享，支持原创，引用连接地址