mysql

• 1. mysql数据库基础
  • 1.1数据库
    • 1.1.1常见的数据库
    • 1.1.2数据库结构模型
    • 1.1.3 数据库管理系统
    • 1.1.4常见的关系型数据库管理系统
    • 1.1.5 关系型数据库的常见组件
• 2. SQL语句
  • 2.1 mysql安装
    • 2.1.1 mysql方式
    • 2.2.2mysql配置
    • 2.2.3 mysql的程序组成
    • 2.2.4 mysql工具使用
    • 2.2.5 服务器监听的两种socket地址
• 3. mysql数据库操作
  • 3.1 DDL操作
    • 3.1.1 数据库操作
    • 3.1.2 表操作
    • 3.1.3 用户操作
    • 3.1.4 查看命令show
• 4. MySQL数据类型

1. mysql数据库基础

1.1数据库

1.1.1常见的数据库

• 关系型数据库：数据存放在硬盘当中
  字段：每一列的第一行就是字段
  记录：每一行代表一条记录

  • mysql（5.7之前都是开源，8.0开始商业化）
  • mariadb -->10.0
  • MSsql MicreSoft Sql
  • Oracle 商业软件
  • sqlserver

• 非关系型数据库：数据存放在内存当中，变量，key=value

  • mongodb
  • redis
  • memcache

• sqlite：数据存放在文件当中 sql

1.1.2数据库结构模型

• 层次模型
• 网状结构
• 关系模型

1.1.3 数据库管理系统

数据库管理系统：DBMS DateBase Managment System mysql

关系：Relational，RDBMS
Relationship 关系
RDBMS 关系数据库系统

1.1.4常见的关系型数据库管理系统

• MySQL：MySQL，MariaDB，Percona-Server
• PostgreSQL：简称为pgsql
• Oracle
• MSSQL

SQL：Structure Query Language，结构化查询语言

约束：constraint，向数据表提供的数据要遵守的限制

• 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。

  • 一个表只能存在一个

• 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）

  • 一个表可以存在多个

• 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据

• 检查性约束

索引：将表中的一个或多个字段中的数据复制一份另存，并且这些数据需要按特定次序排序存储

1.1.5 关系型数据库的常见组件

关系型数据库的常见组件有：

• 数据库：database
• 表：table，由行（row）和列（column）组成
• 索引：index
• 视图：view
• 用户：user
• 权限：privilege
• 存储过程：procedure
• 存储函数：function
• 触发器：trigger
• 事件调度器：event scheduler

2. SQL语句

SQL语句有三种类型：

• DDL：Data Defination Language，数据定义语言
• DML：Data Manipulation Language，数据操纵语言
• DCL：Data Control Language，数据控制语言

2.1 mysql安装

2.1.1 mysql方式

mysql安装方式有三种：

• 源代码：编译安装
• 二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
• 程序包管理器管理的程序包：
  • rpm：有两种
    • OS Vendor：操作系统发行商
    • 提供的项目官方提供的
• deb

[root@mr ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
--2022-07-25 17:42:52--  http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Resolving dev.mysql.com (dev.mysql.com)... 23.2.135.207, 2600:1402:3800:2ab::2e31, 2600:1402:3800:28b::2e31
Connecting to dev.mysql.com (dev.mysql.com)|23.2.135.207|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-25 17:42:52--  https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
Connecting to dev.mysql.com (dev.mysql.com)|23.2.135.207|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm [following]
--2022-07-25 17:42:53--  https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
Resolving repo.mysql.com (repo.mysql.com)... 184.27.29.58
Connecting to repo.mysql.com (repo.mysql.com)|184.27.29.58|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25680 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql57-community-release-el7-11.noarch.rpm’

mysql57-community-releas 100%[================================>]  25.08K   167KB/s    in 0.2s    

2022-07-25 17:42:53 (167 KB/s) - ‘mysql57-community-release-el7-11.noarch.rpm’ saved [25680/25680]
[root@mr ~]# ls
123   htop-1.0.2                                   sleep
2     htop-1.0.2.tar.gz                            wget-1.14-18.el7_6.1.x86_64.rpm
a     mysql57-community-release-el7-11.noarch.rpm  wget-1.19.5-10.el8.x86_64.rpm
abc   nohup.out                                    xixi
hehe  outfile
[root@mr ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm 
warning: mysql57-community-release-el7-11.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
  1:mysql57-community-release-el7-11 ################################# [100%]
[root@mr ~]# 
[root@mr ~]# ls /etc/yum.repos.d/
CentOS-Stream-AppStream.repo  CentOS-Stream-HighAvailability.repo  mysql-community.repo
CentOS-Stream-BaseOS.repo     CentOS-Stream-Media.repo             mysql-community-source.repo
CentOS-Stream-Debuginfo.repo  CentOS-Stream-PowerTools.repo
CentOS-Stream-Extras.repo     CentOS-Stream-RealTime.repo
[root@mr ~]# 
[root@mr ~]# yum module disable mysql
Last metadata expiration check: 0:03:45 ago on Mon 25 Jul 2022 05:46:28 PM CST.
Dependencies resolved.
==================================================================================================
Package                Architecture          Version                Repository              Size
==================================================================================================
Disabling modules:
mysql                                                                                           

Transaction Summary
==================================================================================================

Is this ok [y/N]: y
Complete!
[root@mr ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
Last metadata expiration check: 0:04:07 ago on Mon 25 Jul 2022 05:46:28 PM CST.
Dependencies resolved.
==================================================================================================
Package                    Arch       Version                        Repository             Size
==================================================================================================
Installing:
mysql-community-client     x86_64     5.7.38-1.el7                   mysql57-community      28 M
mysql-community-common     x86_64     5.7.38-1.el7                   mysql57-community     311 k
mysql-community-devel      x86_64     5.7.38-1.el7                   mysql57-community     4.2 M
mysql-community-server     x86_64     5.7.38-1.el7                   mysql57-community     178 M
Installing dependencies:
mysql-community-libs       x86_64     5.7.38-1.el7                   mysql57-community     2.6 M
ncurses-compat-libs        x86_64     6.1-9.20180224.el8             baseos                328 k
net-tools                  x86_64     2.0-0.52.20160912git.el8       baseos                322 k

Transaction Summary
==================================================================================================
Install  7 Packages

Total download size: 213 M
Installed size: 916 M
Downloading Packages:
(1/7): net-tools-2.0-0.52.20160912git.el8.x86_64.rpm              787 kB/s | 322 kB     00:00    
(2/7): ncurses-compat-libs-6.1-9.20180224.el8.x86_64.rpm          506 kB/s | 328 kB     00:00    
(3/7): mysql-community-common-5.7.38-1.el7.x86_64.rpm             143 kB/s | 311 kB     00:02    
(4/7): mysql-community-devel-5.7.38-1.el7.x86_64.rpm              2.1 MB/s | 4.2 MB     00:02    
(5/7): mysql-community-libs-5.7.38-1.el7.x86_64.rpm               832 kB/s | 2.6 MB     00:03    
(6/7): mysql-community-client-5.7.38-1.el7.x86_64.rpm             4.2 MB/s |  28 MB     00:06    
(7/7): mysql-community-server-5.7.38-1.el7.x86_64.rpm             7.9 MB/s | 178 MB     00:22    
--------------------------------------------------------------------------------------------------
Total                                                             8.3 MB/s | 213 MB     00:25     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
 Preparing        :                                                                          1/1 
 Installing       : mysql-community-common-5.7.38-1.el7.x86_64                               1/7 
 Installing       : mysql-community-libs-5.7.38-1.el7.x86_64                                 2/7 
 Running scriptlet: mysql-community-libs-5.7.38-1.el7.x86_64                                 2/7 
 Installing       : net-tools-2.0-0.52.20160912git.el8.x86_64                                3/7 
 Running scriptlet: net-tools-2.0-0.52.20160912git.el8.x86_64                                3/7 
 Installing       : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                            4/7 
 Installing       : mysql-community-client-5.7.38-1.el7.x86_64                               5/7 
 Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                               6/7 
 Installing       : mysql-community-server-5.7.38-1.el7.x86_64                               6/7 
 Running scriptlet: mysql-community-server-5.7.38-1.el7.x86_64                               6/7 
 Installing       : mysql-community-devel-5.7.38-1.el7.x86_64                                7/7 
 Running scriptlet: mysql-community-devel-5.7.38-1.el7.x86_64                                7/7 
[/usr/lib/tmpfiles.d/mysql.conf:23] Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.

 Verifying        : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                            1/7 
 Verifying        : net-tools-2.0-0.52.20160912git.el8.x86_64                                2/7 
 Verifying        : mysql-community-client-5.7.38-1.el7.x86_64                               3/7 
 Verifying        : mysql-community-common-5.7.38-1.el7.x86_64                               4/7 
 Verifying        : mysql-community-devel-5.7.38-1.el7.x86_64                                5/7 
 Verifying        : mysql-community-libs-5.7.38-1.el7.x86_64                                 6/7 
 Verifying        : mysql-community-server-5.7.38-1.el7.x86_64                               7/7 
Installed products updated.

Installed:
 mysql-community-client-5.7.38-1.el7.x86_64     mysql-community-common-5.7.38-1.el7.x86_64       
 mysql-community-devel-5.7.38-1.el7.x86_64      mysql-community-libs-5.7.38-1.el7.x86_64         
 mysql-community-server-5.7.38-1.el7.x86_64     ncurses-compat-libs-6.1-9.20180224.el8.x86_64    
 net-tools-2.0-0.52.20160912git.el8.x86_64     

Complete!
[root@mr ~]# 

2.2.2mysql配置

yum安装

[root@mr ~]# systemctl enable --now mysqld（设置开机自启）
[root@mr ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2022-07-25 17:56:50 CST; 8s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7107 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYS>
  Process: 7057 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7109 (mysqld)
    Tasks: 27 (limit: 12221)
   Memory: 311.3M
   CGroup: /system.slice/mysqld.service
           └─7109 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jul 25 17:56:48 mr systemd[1]: Starting MySQL Server...
Jul 25 17:56:50 mr systemd[1]: Started MySQL Server.
[root@mr ~]# ss -antl
State      Recv-Q     Send-Q         Local Address:Port         Peer Address:Port     Process     
LISTEN     0          128                  0.0.0.0:111               0.0.0.0:*                    
LISTEN     0          128                  0.0.0.0:22                0.0.0.0:*                    
LISTEN     0          25                   0.0.0.0:514               0.0.0.0:*                    
LISTEN     0          80                         *:3306                    *:*                    
LISTEN     0          128                     [::]:111                  [::]:*                    
LISTEN     0          128                     [::]:22                   [::]:*                    
LISTEN     0          25                      [::]:514                  [::]:*                    
[root@mr ~]# grep "password" /var/log/mysqld.log
2022-07-25T09:56:48.633177Z 1 [Note] A temporary password is generated for root@localhost: 9MS,WsyWhp># 
[root@mr ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>  set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mr ~]# mysql -uroot -p'123456'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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>  exit
Bye

[root@mr ~]# rpm -qa | grep mysql
mysql57-community-release-el7-11.noarch
[root@mr ~]# rpm -e mysql57-community-release-el7-11.noarch
[root@mr ~]# 

yum安装mariadb:

• 1.yum install -y mariadb*
• 2. systemctl enable --now  mariadb
• 3. mysql
• 4. set password = password('xxxx');
• 5. exit
• 6. mysql -uroot -pxxxx

2.2.3 mysql的程序组成

• 客户端
  • mysql：CLI交互式客户端程
  • mysql_secure_installation：安全初始化，强烈建议安装完以后执行此命令
  • mysqldump：mysql备份工具
  • mysqladmin
• 服务器端
  • mysqld

[root@mr ~]# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 0 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password: 

Re-enter new password: 

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL 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? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

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

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 
[root@mr ~]# 
[root@mr ~]# mysql -uroot -pmarui
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> 


[root@mr ~]# ps aux | grep mysqld
mysql       7109  0.0  9.5 1383676 189808 ?      Sl   17:56   0:04 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root        7373  0.0  0.0  12144  1168 pts/0    S+   21:08   0:00 grep --color=auto mysqld
[root@mr ~]# 

2.2.4 mysql工具使用

//语法：mysql [OPTIONS] [database]
//常用的OPTIONS：
-uUSERNAME //指定用户名，默认为root
-hHOST //指定服务器主机，默认为localhost，推荐使用ip地址
-pPASSWORD //指定用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
-V //查看当前使用的mysql版本
-e //不登录mysql执行sql语句后退出，常用于脚本

[root@mr ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.38, for Linux (x86_64) using  EditLine wrapper
[root@mr ~]# mysql -uroot -pmarui -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> exit
Bye
[root@mr ~]# mysql -uroot -pmarui -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@mr ~]# 

2.2.5 服务器监听的两种socket地址

3. mysql数据库操作

3.1 DDL操作

3.1.1 数据库操作

[root@mr ~]# mysql -uroot -pmarui
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database runtime;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database runtime;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> 

3.1.2 表操作

mysql> create table Linux(id int not null,name varchar(10),age tinyint (2));
Query OK, 0 rows affected (0.01 sec)

mysql> desc Linux;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | tinyint(2)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from Linux;
Empty set (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| Linux             |
+-------------------+
1 row in set (0.00 sec)

mysql>  drop table Linux;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> 

3.1.3 用户操作

mysql用户帐号由两部分组成，如'USERNAME'@'HOST'，表示此USERNAME只能从此HOST上远程登录这里（'USERNAME'@'HOST'）的HOST用于限制此用户可通过哪些主机远程连接mysql程序，其值可为：

• IP地址，如：172.16.12.129
• 通配符
  • %：匹配任意长度的任意字符，常用于设置允许从任何主机登录
  • _：匹配任意单个字符

mysql> create user 'tom'@'mr' identified by 'marui';
Query OK, 0 rows affected (0.00 sec)

mysql> drop user 'tom'@'mr';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.user;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| localhost | root          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *9370E4584BA357054A156F3EF8CBCE33FBAB4BF3 | N                | 2022-07-25 21:03:40   |              NULL | N              |
| localhost | mysql.session | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-07-25 17:56:48   |              NULL | Y              |
| localhost | mysql.sys     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-07-25 17:56:48   |              NULL | Y              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
3 rows in set (0.00 sec)

mysql> 

3.1.4 查看命令show

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

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)

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from runtime;
Empty set (0.00 sec)

mysql> 

4. MySQL数据类型

MySQL中定义数据字段的类型对数据库的优化是非常重要的MySQL支持 多种类型，大致可以分为三类:数值、日期/时间和字符串(字符)类型。

数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、 SMALLINT、 DECIMAL和NUMERIC)， 以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值，并且支持MyISAM，MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT，MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE，TIMESTAMP、 TIME和YEAR。 每个时间类型有一个有效值范围和一个"零"值，当 指定不合法的MySQL不能表示的值时使用"零"值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。

字符串类型
字符串类型指CHAR、VARCHAR、BINARY、 VARBINARY、BLOB、TEXT、 ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

注意: char(n) 和varchar(n)中括号中n代表字符的个数，并不代表宇节个数，比如CHAR(30)就可以存储30个字符。

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是它们包含二进制宇符串而不要非二进制宇符串。也就
是说，它们包含字节字符串而不是字符宇符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型: TINYBLOB、 BLOB、 MEDIUMBLOB 和
LONGBLOB。它们区别在于可容纳存储范围不同。

有4种TEXT类型: TINYTEXT、 TEXT、MEDIUMTEXT和LONGTEXT。对应的这4种BLOB类型，可存储的最大长度
不同，可根据实际情 况选择。