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

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 wrapperConnection 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 secThreads: 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 wrapperConnection 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 secThreads: 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 wrapperConnection 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 secThreads: 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: userEngine: MyISAMVersion: 10Row_format: DynamicRows: 3Avg_row_length: 113Data_length: 340
Max_data_length: 281474976710655Index_length: 4096Data_free: 0Auto_increment: NULLCreate_time: 2020-06-01 14:58:34Update_time: 2020-06-01 14:58:35Check_time: NULLCollation: utf8_binChecksum: NULLCreate_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: userEngine: MyISAMVersion: 10Row_format: DynamicRows: 3Avg_row_length: 113Data_length: 340
Max_data_length: 281474976710655Index_length: 4096Data_free: 0Auto_increment: NULLCreate_time: 2020-06-01 14:58:34Update_time: 2020-06-01 14:58:35Check_time: NULLCollation: utf8_binChecksum: NULLCreate_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: 0mysql>

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

#加在第一列

mysql> alter table t1 add job2 varchar(20) first;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql>
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: 0mysql> 
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: 0mysql> 
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: 0mysql> 
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: 0mysql> 
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: 0mysql>

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: 0mysql> 
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: 0mysql> 
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: 0mysql> 
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: defTABLE_SCHEMA: mysqlTABLE_NAME: v1VIEW_DEFINITION: select `mysql`.`t1`.`name` AS `name`,`mysql`.`t1`.`job` AS `job` from `mysql`.`t1`CHECK_OPTION: NONEIS_UPDATABLE: YESDEFINER: root@localhostSECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
*************************** 2. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: mysqlTABLE_NAME: v2VIEW_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: NONEIS_UPDATABLE: YESDEFINER: root@localhostSECURITY_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: 1mysql> 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: 1mysql>

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 ServerCopyright (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 -ADatabase 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: 0MariaDB [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]