MySQL数据库for CentOS7 yum安装、5.7.30编译安装和基础操作

原创

superman超哥 2020-06-06 21:21:12 博主文章分类：mysql ©著作权

文章标签 MySQL数据库for CentOS7 文章分类 运维

©著作权归作者所有：来自51CTO博客作者superman超哥的原创作品，请联系作者获取转载授权，否则将追究法律责任

1. MySQL数据库简介

MySQL 是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。

关系数据库管理系统(Relational Database Management System， RDBMS)，是将数据组织为相关的行和列的系统，而管理关系数据库的计算机软件就是关系数据库管理系统。

数据库一般分为以下两种：

关系型数据库；
非关系型数据库。

常用的关系型数据库软件有: MySQL、Mariadb、Oracle、SQL Server、 PostgreSQL、DB2等。

常用的非关系型数据库软件有: Redis、memcached、MongoDB。

MySQL现阶段有免费版本和付费版本，公司里面使用MySQL多数都是使用免费版本。

Mariadb数据库是MySQL数据库原来的团队，后续独立出来进行开发的完全开源版本。

1.1 MySQL引擎

MySQL引擎有很多，企业里面主流的myisam、innodb两种。

1.1.1 MyISAM

主要强调的是性能，其执行速度比InnoDB类型更快，但不提供事务支持，不支持外键，如果执行大量的select(查询)操作，MyISAM是更好的选择，支持表锁。MyISAM引擎查询性能高。

1.1.2 InnoDB

提供事务，支持事务、外键、行级锁等高级数据库功能，可执行大量的insert或update，InnoDB引擎写性能高。

1.2 MySQL数据库安装

MySQL安装方式有两种，一种是yum/rpm安装，另外一种是tar源码安装。

1.2.1 MySQL yum安装

yum安装方法很简单，执行命令如下即可:

1.2.1.1 CentOS 6 yum安装

[root@node01 ~]#yum install –y mysql-server mysql-devel mysql

1.2.1.2 CentOS 7 yum安装

[root@node01 ~]# yum install -y mariadb mariadb-devel mariadb-server

1.2.1.3 查询yum安装的内容

[root@node01 ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.65-1.el7.x86_64
mariadb-devel-5.5.65-1.el7.x86_64
mariadb-server-5.5.65-1.el7.x86_64
mariadb-5.5.65-1.el7.x86_64
[root@node01 ~]#

1.2.1.4 yum安装mariadb程序

mariadb主配置目录: /var/lib/mysql

mariadb数据目录：/var/lib/mysql

mariadb命令目录：/usr/bin

mariadb默认配置文件：/etc/my.cnf

mariadb启动文件：/usr/bin

mariadb日志文件：/var/log/mariadb

说明：Mariadb和 mysql数据库软件命令和配置都是类似的。

1.2.2 MySQL源码安装5.7版本

1.2.2.1 源码编译安装前准备

因为CentOS系统自带了mariadb，我们需要把它先卸载。

[root@node02 ~]# rpm -qa|grep mariadb
mariadb-server-5.5.60-1.el7_5.x86_64
mariadb-libs-5.5.60-1.el7_5.x86_64
mariadb-5.5.60-1.el7_5.x86_64
[root@node02 ~]# 
[root@node02 ~]# yum -y remove mariadb mariadb-server mariadb-libs

创建用户

[root@node02 ~]# useradd -s /sbin/nologin -M mysql      #提示mysql已存在可以忽略此步骤

[root@node02 ~]# mkdir -p /data/mysql
[root@node02 ~]# chown -R mysql.mysql /data

安装MySQL相关依赖包

[root@node02 ~]# yum install -y gcc gcc-devel gcc-c++ gcc-c++-devel libaio* autoconf* automake* zlib* libxml* ncurses-devel ncurses libgcrypt* libtool* cmake openssl openssl-devel bison bison-devel perl-Data_Dumper boost boost-doc boost-devel

下载源码并解压

[root@node02 ~]# cd /usr/src/
[root@node02 src]# wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.30.tar.gz
[root@node02 src]# ls
debug  kernels  mysql-5.7.30.tar.gz
[root@node02 src]# tar xf mysql-5.7.30.tar.gz
[root@node02 src]# cd mysql-5.7.30/
[root@node02 mysql-5.7.30]#

下载boost(5.7版本更新之后有很多变化，比如现在安装必须要安装这个boost库了)

[root@node02 mysql-5.7.30]# mkdir ./boost
[root@node02 mysql-5.7.30]# cd ./boost
[root@node02 boost]# wget http://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

说明：解压等操作编译的时候程序会做，这里只需把包下载或者拷贝到这里即可

1.2.2.2 源码编译安装

编译

[root@node02 boost]# cd ../
[root@node02 mysql-5.7.30]# cmake \
-DBUILD_CONFIG=mysql_release \
-DCMAKE_BUILD_TYPE=RelWithDebInfo \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql \
-DSYSCONFDIR=/etc \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DSYSTEMD_PID_DIR=/data/mysql \
-DMYSQL_USER=mysql \
-DWITH_SYSTEMD=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_DEBUG=0 \
-DMYSQL_MAINTAINER_MODE=0 \
-DWITH_SSL:STRING=system \
-DWITH_ZLIB:STRING=bundled \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=./boost

编译参数介绍

cmake \
-DBUILD_CONFIG=mysql_release \                  #此选项使用Oracle使用的相同构建选项配置源分发，以生成官方MySQL版本的二进制分发。
-DCMAKE_BUILD_TYPE=RelWithDebInfo \             #要生成的构建类型 = 启用优化并生成调试信息。这是默认的MySQL构建类型。
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \       #选项表示基本安装目录。
-DMYSQL_DATADIR=/data/mysql \                   #MySQL数据目录的位置。
-DSYSCONFDIR=/etc \                             #默认my.cnf选项文件目录。
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \             #服务器侦听套接字连接的Unix套接字文件路径。这必须是绝对路径名。默认是/tmp/mysql.sock。
-DSYSTEMD_PID_DIR=/usr/local/mysql \            #当MySQL由systemd管理时，在其中创建PID文件的目录的名称。默认是 /var/run/mysqld; 这可能会根据INSTALL_LAYOUT值隐式更改 。
-DMYSQL_USER=mysql \                            #指定MySQL的启动用户
-DWITH_SYSTEMD=1 \                              #安装systemd支持文件
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_TCP_PORT=3306 \                          #配置 MySQL 监听的端口号
-DENABLED_LOCAL_INFILE=1 \                       #使mysql客户端具有load data infile的功能，该功能具有安全隐患  load data infile语句从一个文本文件中以很高的速度读入一个表                
-DENABLE_DOWNLOADS=1 \                           #googlemock发行版的路径，用于基于Google Test的单元测试。=1，CMake将从GitHub下载发行版。
-DWITH_PARTITION_STORAGE_ENGINE=1 \ 
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \                         #服务器字符集
-DDEFAULT_COLLATION=utf8_general_ci \            #服务器排序规则
-DWITH_DEBUG=0 \                                 #不包含调试支持。
-DMYSQL_MAINTAINER_MODE=0 \                      #是否启用MySQL维护者特定的开发环境。如果启用，此选项会导致编译器警告变为错误。
-DWITH_SSL:STRING=system \
-DWITH_ZLIB:STRING=bundled \
-DDOWNLOAD_BOOST=1 \                             #是否下载Boost库。默认是OFF。
-DWITH_BOOST=./boost                             #指定Boost库目录位置。

查看编译是否有异常

[root@node02 mysql-5.7.30]# echo $?              #返回结果为0说明编译无异常

源码安装

[root@node02 mysql-5.7.30]# make && make install

这个过程比较漫长，耐心等待一下

查看安装是否有异常

[root@node02 mysql-5.7.30]# echo $?              #返回结果为0说明编译无异常

1.2.2.4 源码安装后配置

添加 systemd 服务控制

[root@node02 mysql-5.7.30]# cp ./scripts/mysqld.service /usr/lib/systemd/system

添加环境变量

[root@node02 mysql-5.7.30]# cat > /etc/profile.d/mysql.sh << EOF 
PATH=/usr/local/mysql/bin:$PATH
export PATH
EOF
[root@node02 mysql-5.7.30]# source /etc/profile

空密码初始化数据库

[root@node02 mysql-5.7.30]# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

配置启动MySQL

[root@node02 mysql-5.7.30]# systemctl enable mysqld.service
[root@node02 mysql-5.7.30]# systemctl daemon-reload
[root@node02 mysql-5.7.30]# systemctl start mysqld.service
[root@node02 mysql-5.7.30]# systemctl status mysqld.service

1.3 登录数据库服务器

1.3.1 通过unix套接字连接

1.3.1.1 直接通过mysql登录，查看连接状态

[root@node02 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:          5
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.30 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 33 min 25 sec

Threads: 1  Questions: 17  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.008
--------------

mysql>

1.3.1.2 mysql -uroot -p登录，查看连接状态

[root@node02 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> 
mysql> status
--------------

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:          3
Current database:                                 #当前使用的那个数据库，没有选择为空
Current user:           root@localhost
SSL:                    Not in use                #是否使用加密
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;                         #结束符为分号
Server version:         5.7.30 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket #连接方式，本地套接字
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 17 min 33 sec

Threads: 1  Questions: 7  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.006
--------------

mysql>

1.3.2 通过tcp套接字连接

1.3.2.1 通过mysql -h127.0.0.1登录服务器，查看状态

[root@node02 ~]# mysql -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> status
--------------

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.30 MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 31 min 40 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 105  Flush tables: 1  Open tables: 98  Queries per second avg: 0.006
--------------

mysql>

可以看到连接id不同，套接字也不同，使用的是tcp/ip的套接字通信。如果有时候遇到无法通过本地套接字连接，可以使用指定服务器ip连接。

1.4 常用命令操作

1.4.1 数据库的操作命令

#查询数据库：

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

mysql>

初始化后，默认会有四个数据库：

information_schema：信息数据库。主要保存着关于MySQL服务器所维护的所有其他数据库的信息，如数据库名，数据库的表，表栏的数据类型与访问权限等。通过show databases;查看到数据库信息，也是出自该数据库中得SCHEMATA表。
mysql： mysql的核心数据库。主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。
performance_schema ：用于性能优化的数据库。

#查看数据库的创建语句：

mysql> show create database mysql;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| mysql    | CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

mysql为数据库名。

#查看字符集命令：

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>

#创建数据库zabbix，字符集位gbk；

mysql> create database zabbix default character set gbk;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> show create database zabbix;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

#修改数据库的字符集：

mysql> alter database zabbix default character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> show create database zabbix;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| zabbix   | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

#创建数据库：

mysql> create database zabbix charset=utf8; 
ERROR 1007 (HY000): Can't create database 'zabbix'; database exists
mysql> 

mysql> create database if not exists zabbix charset=gbk;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>

用上面的第二条命令创建数据库，如果数据库已经存在就不会报错了。

#查看警告：

mysql> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Error | 1007 | Can't create database 'zabbix'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql>

#删除数据库：

mysql> drop database zabbix;
mysql>

或者：

mysql> drop database zabbix;
ERROR 1008 (HY000): Can't drop database 'zabbix'; database doesn't exist
mysql> 
mysql> drop database if exists zabbix;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

用上面第二种方式删除数据库，如果数据库不存在就不会报错了。

1.4.2 创建表命令

mysql> create table t1(id int(10) auto_increment primary key,name varchar(20),job varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql>

1.4.3 查看表结构相关命令

查看所有表：

mysql> use mysql;
Database changed
mysql> 
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| t1                        |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
32 rows in set (0.00 sec)

mysql>

或者

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| t1                        |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
32 rows in set (0.00 sec)

mysql>

查看所有表的详细信息：

use mysql;

show table status\G

或者

show table status from mysql\G

查看某张表的详细信息：

mysql> use mysql;
Database changed
mysql> 
mysql> show table status like "user"\G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 113
    Data_length: 340
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-06-01 14:58:34
    Update_time: 2020-06-01 14:58:35
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

mysql>

或者

mysql> show table status from mysql like "user"\G
*************************** 1. row ***************************
           Name: user
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 3
 Avg_row_length: 113
    Data_length: 340
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2020-06-01 14:58:34
    Update_time: 2020-06-01 14:58:35
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: 
        Comment: Users and global privileges
1 row in set (0.00 sec)

mysql>

查看表结构：

mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

mysql>

查看创建表的sql语句：

mysql> show create table mysql.user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `password_last_changed` timestamp NULL DEFAULT NULL,
  `password_lifetime` smallint(5) unsigned DEFAULT NULL,
  `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)

mysql>

1.4.4 修改表结构相关命令

添加表字段：

mysql> alter table t1 add job1 varchar(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

默认是加在后面，如果想加在第一列，或者某个字段后可以进行指定：

#加在第一列

mysql> alter table t1 add job2 varchar(20) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| job2  | varchar(20) | YES  |     | NULL    |                |
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| job   | varchar(10) | YES  |     | NULL    |                |
| job1  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql>

#加在name字段后：

mysql> alter table t1 add job3 varchar(20) after name;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| job2  | varchar(20) | YES  |     | NULL    |                |
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| job3  | varchar(20) | YES  |     | NULL    |                |
| job   | varchar(10) | YES  |     | NULL    |                |
| job1  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

修改表字段名，需要将字段属性写全：

mysql> alter table t1 change id age int(5);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| job2  | varchar(20) | YES  |     | NULL    |       |
| age   | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| job3  | varchar(20) | YES  |     | NULL    |       |
| job   | varchar(10) | YES  |     | NULL    |       |
| job1  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

id: 原字段

age: 新字段

修改表字段的属性或者位置：

#修改字段的顺序，把job3放在第一列：

mysql> alter table t1 modify job3 varchar(20) first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| job3  | varchar(20) | YES  |     | NULL    |       |
| job2  | varchar(20) | YES  |     | NULL    |       |
| age   | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| job   | varchar(10) | YES  |     | NULL    |       |
| job1  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

删除表字段：

mysql> alter table t1 drop job3;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| job2  | varchar(20) | YES  |     | NULL    |       |
| age   | int(5)      | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| job   | varchar(10) | YES  |     | NULL    |       |
| job1  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

1.4.5 增、删、改、查

1.4.5.1 增

#全字段增加数据：

mysql> insert into t1 values(1,"xiaoming","it");
Query OK, 1 row affected (0.00 sec)

mysql>

或者

mysql> insert t1 values(2,"xiaohua","student");
Query OK, 1 row affected (0.00 sec)

mysql>

或者

mysql> insert t1 set name="xiaoming",job="teacher";
Query OK, 1 row affected (0.00 sec)

mysql>

#指定字段增加数据：

mysql> insert into t1(name) value("xiaoqiang"),("xiaowang");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>

1.4.5.2 删

#物理删除，数据就真没有了

mysql> delete from t1 where id=4;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> select * from t1;
+----+----------+---------+
| id | name     | job     |
+----+----------+---------+
|  2 | xiaohua  | student |
|  3 | xiaoming | teacher |
|  5 | xiaowang | NULL    |
+----+----------+---------+
3 rows in set (0.00 sec)

mysql>

#逻辑删除，需要添加一个字段，默认设置为0：

mysql> alter table t1 add isdelete bit default 0;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from t1;
+----+----------+---------+----------+
| id | name     | job     | isdelete |
+----+----------+---------+----------+
|  2 | xiaohua  | student |          |
|  3 | xiaoming | teacher |          |
|  5 | xiaowang | NULL    |          |
+----+----------+---------+----------+
3 rows in set (0.00 sec)

mysql>

#将isdelete字段设置为1：

mysql> update t1 set isdelete=1 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> select * from t1;
+----+----------+---------+----------+
| id | name     | job     | isdelete |
+----+----------+---------+----------+
|  2 | xiaohua  | student |          |
|  3 | xiaoming | teacher |         |
|  5 | xiaowang | NULL    |          |
+----+----------+---------+----------+
3 rows in set (0.00 sec)

mysql>

#然后查找isdelete字段为0得数据即可过滤了：

mysql> select * from t1 where isdelete=0;
+----+----------+---------+----------+
| id | name     | job     | isdelete |
+----+----------+---------+----------+
|  2 | xiaohua  | student |          |
|  5 | xiaowang | NULL    |          |
+----+----------+---------+----------+
2 rows in set (0.00 sec)

mysql>

1.4.5.3 改

#修改表中得数据，不增加行,insert into会增加行： update t1 set name="xiaoxiao" where id=6; t1是表名，

name是字段名，

where后面是条件语句，如果没有，就是对整个表得修改了，要慎重！

#修改多个字段用逗号分隔：

mysql> update t1 set name="xiaoqiang",job="engineer" where id=3 ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> 
mysql> select * from t1;
+----+-----------+----------+----------+
| id | name      | job      | isdelete |
+----+-----------+----------+----------+
|  2 | xiaohua   | student  |          |
|  3 | xiaoqiang | engineer |         |
|  5 | xiaowang  | NULL     |          |
+----+-----------+----------+----------+
3 rows in set (0.00 sec)

mysql>

1.4.5.4 查

全字段查找，不建议：

mysql> select * from t1;
+----+-----------+----------+----------+
| id | name      | job      | isdelete |
+----+-----------+----------+----------+
|  2 | xiaohua   | student  |          |
|  3 | xiaoqiang | engineer |         |
|  5 | xiaowang  | NULL     |          |
+----+-----------+----------+----------+
3 rows in set (0.00 sec)

mysql>

查找指定字段：

mysql> select name,job from t1;
+-----------+----------+
| name      | job      |
+-----------+----------+
| xiaohua   | student  |
| xiaoqiang | engineer |
| xiaowang  | NULL     |
+-----------+----------+
3 rows in set (0.00 sec)

mysql>

1.4.9 根据运算符查找

>= 大于等于

= 等于

> 大于

< 小于

<= 小于等于

!= 不等于

查找id大于等于4的用户id,用户名及工作：

mysql> select id,name,job from t1 where id >=4;
+----+----------+------+
| id | name     | job  |
+----+----------+------+
|  5 | xiaowang | NULL |
+----+----------+------+
1 row in set (0.00 sec)

mysql>

and 多个条件同时满足

or 几个条件，满足其一即可

查找id大于等于3，并且没有标记删除的数据

mysql> select id,name,job,isdelete from t1 where id>=3 and isdelete=0;
+----+----------+------+----------+
| id | name     | job  | isdelete |
+----+----------+------+----------+
|  5 | xiaowang | NULL |          |
+----+----------+------+----------+
1 row in set (0.00 sec)

mysql>

1.4.10 模糊查找

like 模糊查找

% 匹配任意多个字符

_ 匹配单个字符

查找名字中含有xiao字符的数据：

mysql> select id,name,job from t1 where name like "xiao%";
+----+-----------+----------+
| id | name      | job      |
+----+-----------+----------+
|  2 | xiaohua   | student  |
|  3 | xiaoqiang | engineer |
|  5 | xiaowang  | NULL     |
+----+-----------+----------+
3 rows in set (0.00 sec)

mysql>

插入一条数据，查找名字中含有xiao并且后面跟一个单字符的数据

mysql> insert t1(name) value("xiaom");
Query OK, 1 row affected (0.01 sec)

mysql> 

mysql> select id,name,job from t1 where name like "xiao_";
+----+-------+------+
| id | name  | job  |
+----+-------+------+
|  6 | xiaom | NULL |
+----+-------+------+
1 row in set (0.00 sec)

mysql>

1.4.11 范围查找

in 表示非连续的范围

between A and B 表示一个连续的范围内

not 不在某个条件内

查找id等于2，3或者6的数据

mysql> select id,name,job from t1 where id in(2,3,6);
+----+-----------+----------+
| id | name      | job      |
+----+-----------+----------+
|  2 | xiaohua   | student  |
|  3 | xiaoqiang | engineer |
|  6 | xiaom     | NULL     |
+----+-----------+----------+
3 rows in set (0.00 sec)

mysql>

查找id在3到6之间数据（包含3和6）：

mysql> select id,name,job from t1 where id between 3 and 6;
+----+-----------+----------+
| id | name      | job      |
+----+-----------+----------+
|  3 | xiaoqiang | engineer |
|  5 | xiaowang  | NULL     |
|  6 | xiaom     | NULL     |
+----+-----------+----------+
3 rows in set (0.00 sec)

mysql>

查找id不等于3，6的数据：

mysql> select id,name,job from t1 where id not in (3,6);
+----+----------+---------+
| id | name     | job     |
+----+----------+---------+
|  2 | xiaohua  | student |
|  5 | xiaowang | NULL    |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql>

1.4.12 空值查找

is null查找空值

mysql> select id,name,job from t1 where job is null;
+----+----------+------+
| id | name     | job  |
+----+----------+------+
|  5 | xiaowang | NULL |
|  6 | xiaom    | NULL |
+----+----------+------+
2 rows in set (0.00 sec)

mysql>

1.4.13 聚合

mysql> select max(age) from t1;
+----------+
| max(age) |
+----------+
|       15 |
+----------+
1 row in set (0.01 sec)

mysql> 
mysql> select min(age) from t1;
+----------+
| min(age) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> 
mysql> select count(*) from t1 where isdelete=0;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql>

1.4.14 排序

order by 字段 asc 根据“列”从小到大排序

根据id，从小到大进行排序：

mysql> select * from t1 order by id asc;
+----+-----------+------+----------+----------+
| id | name      | age  | job      | isdelete |
+----+-----------+------+----------+----------+
|  2 | xiaohua   |   10 | student  |          |
|  3 | xiaoqiang |    6 | engineer |         |
|  5 | xiaowang  |   15 | NULL     |          |
|  6 | xiaom     |   11 | NULL     |          |
+----+-----------+------+----------+----------+
4 rows in set (0.00 sec)

mysql>

order by字段 desc 根据“列”从大到小排序

根据id，从大到小进行排序：

mysql> select * from t1 order by id desc;
+----+-----------+------+----------+----------+
| id | name      | age  | job      | isdelete |
+----+-----------+------+----------+----------+
|  6 | xiaom     |   11 | NULL     |          |
|  5 | xiaowang  |   15 | NULL     |          |
|  3 | xiaoqiang |    6 | engineer |         |
|  2 | xiaohua   |   10 | student  |          |
+----+-----------+------+----------+----------+
4 rows in set (0.00 sec)

mysql>

1.4.15 分组

group by 字段

#根据名字分组，统计同名的个数：

mysql> select count(*),name from t1 group by name;
+----------+-----------+
| count(*) | name      |
+----------+-----------+
|        1 | xiaohua   |
|        1 | xiaom     |
|        1 | xiaoqiang |
|        2 | xiaowang  |
+----------+-----------+
4 rows in set (0.00 sec)

mysql>

1.4.16 限制

limit n; 显示前n行

#显示前三行

mysql> select * from t1 limit 3;
+----+-----------+------+----------+----------+
| id | name      | age  | job      | isdelete |
+----+-----------+------+----------+----------+
|  2 | xiaohua   |   10 | student  |          |
|  3 | xiaoqiang |    6 | engineer |         |
|  5 | xiaowang  |   15 | NULL     |          |
+----+-----------+------+----------+----------+
3 rows in set (0.00 sec)

mysql>

#显示从第3行开始后的3行；

mysql> select * from t1 limit 3,3;
+----+----------+------+-------+----------+
| id | name     | age  | job   | isdelete |
+----+----------+------+-------+----------+
|  6 | xiaom    |   11 | NULL  |          |
|  9 | xiaowang |   18 | sales |         |
+----+----------+------+-------+----------+
2 rows in set (0.00 sec)

mysql>

#显示从第3行开始后的4行

mysql> select * from t1 limit 4 offset 3;
+----+----------+------+-------+----------+
| id | name     | age  | job   | isdelete |
+----+----------+------+-------+----------+
|  6 | xiaom    |   11 | NULL  |          |
|  9 | xiaowang |   18 | sales |         |
+----+----------+------+-------+----------+
2 rows in set (0.00 sec)

mysql>

1.5 视图的相关操作

视图并不是真实存在的表，主要是将常用的到字段或者数据整合成一个“表”。

1.5.1 创建视图

#先创建表：

mysql> create table t2(id int(10) not null auto_increment primary key,name varchar(20),job varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql>

#插入数据：

mysql> insert t2 set name="xiaoming",job="it";
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert t2 set name="xiaowang",job="student";
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> insert t2 set name="xiaohong",job="teacher";
Query OK, 1 row affected (0.00 sec)

mysql>

#创建视图：

mysql> create view v1 as select name,job from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create view v2 as select * from t2 where id >= 2;
Query OK, 0 rows affected (0.00 sec)

mysql>

1.5.2 查看视图

#查看所有视图：

mysql> select * from information_schema.views where table_schema="mysql"\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: mysql
          TABLE_NAME: v1
     VIEW_DEFINITION: select `mysql`.`t1`.`name` AS `name`,`mysql`.`t1`.`job` AS `job` from `mysql`.`t1`
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 2. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: mysql
          TABLE_NAME: v2
     VIEW_DEFINITION: select `mysql`.`t2`.`id` AS `id`,`mysql`.`t2`.`name` AS `name`,`mysql`.`t2`.`job` AS `job` from `mysql`.`t2` where (`mysql`.`t2`.`id` >= 2)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: root@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
2 rows in set (0.00 sec)

mysql>

#查看某个视图结构：

mysql> desc v2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| job   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql>

#查看视图内容：

mysql> select * from v2;
+----+----------+---------+
| id | name     | job     |
+----+----------+---------+
|  2 | xiaowang | student |
|  3 | xiaohong | teacher |
+----+----------+---------+
2 rows in set (0.00 sec)

mysql>

1.5.3 删除视图

mysql> drop view v2;
Query OK, 0 rows affected (0.00 sec)

mysql>

或者

mysql> drop view if exists v2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

1.5.4 修改视图

alter view v1 as select id,job from t1;

mysql> alter view v1 as select id,job from t1;
Query OK, 0 rows affected (0.00 sec)

mysql>

1.6 修改密码

1.6.1 密码为空或者已知当前密码

方法一：用SET PASSWORD命令

首先登录MySQL。

格式：mysql> set password for用户名@localhost = password('新密码');

范例：

mysql> set password for root@localhost=password('123');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

方法二：在shell终端执行：

[root@node02 ~]# mysql -uroot -p -e "set password for root@localhost=password('123456');"
Enter password:        ------此处输入原密码
[root@node02 ~]#

ps:注意双引号和单引号！！！

方法三：在shell终端用mysqladmin

格式：mysqladmin -u用户名 -p旧密码 password新密码

范例：

[root@node02 ~]# mysqladmin -uroot -p123 password 123456
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@node02 ~]#

方法四：用UPDATE直接编辑user表首先登录MySQL。

mysql> use mysql; 

mysql 5.7以前版本
mysql> update user set password=password('123') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> 

mysql 5.7以后版本
mysql> update user set authentication_string=password('123') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql>

1.6.2 忘记密码

1.6.2.1 mysql 5.5

可执行使用/usr/bin/mysqld_safe --user=mysql --skip-grant-tables &

[root@node01 ~]# /usr/bin/mysqld_safe --user=mysql --skip-grant-tables &
[1] 16638
[root@node01 ~]# 200603 06:27:49 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
200603 06:27:49 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@node01 ~]#

然后用mysql命令进入修改密码：

[root@node01 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.65-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> update mysql.user set passwrod=password('') where user="root" and host="localhost";
ERROR 1054 (42S22): Unknown column 'passwrod' in 'field list'
MariaDB [(none)]> 
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> update user set password=password('123') where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mysql]> 

MariaDB [mysql]> flush  privileges; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]>

1.6.2.2 mysql 5.7

编辑修改/etc/my.cnf

[root@node02 ~]# vim /etc/my.cnf

在[mysqld]中添加

skip-grant-tables

例如：

[mysqld]

skip-grant-tables

datadir=/data/mysql

socket=/temp/mysql.sock

重启mysql

 [root@node02 ~]# systemctl restart mysqld.service

用户无密码登录

[root@node02 ~]# mysql -uroot -p (直接点击回车，密码为空)

选择数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

修改root密码

mysql> update mysql.user set authentication_string=password('1qaz.#EDC') where user='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql>

执行

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

退出 mysql

mysql> quit
Bye
[root@node02 ~]#

编辑 /etc/my.cnf

删除 skip-grant-tables 保存退出

重启mysql

[root@node02 ~]# systemctl restart mysqld.service
[root@node02 ~]#

1.7 安全设置

安装完mysql，如果用于生产环境，最好执行mysql_secure_installation来做一些常规化安全设置。

1.7.1 设置密码复杂度策略及密码；

[root@node02 ~]# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.

New password: 

Re-enter new password:

1.7.2 删除匿名用户；

Estimated strength of the password: 100 
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.

1.7.3 不允许远程登录；

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) : Y
Success.

1.7.4 删除测试数据库；

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.

1.7.5 刷新MySQL的系统权限相关表。

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!