mysql基础

1. 关系型数据库介绍

1.1 数据结构模型

数据结构模型主要有：

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

关系模型：
二维关系：row，column

数据库管理系统：DBMS
关系：Relational，RDBMS

1.2 RDBMS专业名词

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

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

事务：多个操作被当作一个整体对待就称为一个事务
要看一个关系型数据库是否支持事务，需要看其是否支持并满足ACID测试
ACID：ACID是事务的一个基本标准

• A：Automicity，原子性
• C：Consistency，一致性
• I：Isolation，隔离性
• D：Durability，持久性

SQL：Structure Query Language，结构化查询语言
约束：constraint，向数据表提供的数据要遵守的限制

• 主键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。且必须提供数据，不能为空（NOT NULL）。
  • 一个表只能存在一个
• 惟一键约束：一个或多个字段的组合，填入的数据必须能在本表中唯一标识本行。允许为空（NULL）
  • 一个表可以存在多个
• 外键约束：一个表中的某字段可填入数据取决于另一个表的主键已有的数据
  检查性约束

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

关系运算：

• 选择：挑选出符合条件的行（部分行）
• 投影：挑选出需要的字段
• 连接

数据抽象方式：

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

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

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

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

1.4 SQL语句

SQL语句有三种类型：

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

2.mysql安装与配置

2.1 mysql安装

[root@lwf ~]# cd /usr/src/
[root@lwf src]# ls
debug  kernels
[root@lwf src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
--2019-02-19 09:38:00--  http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
正在解析主机 dev.mysql.com (dev.mysql.com)... 137.254.60.11
正在连接 dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... 已连接。
已发出 HTTP 请求，正在等待回应... 301 Moved Permanently
位置：https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm [跟随至新的 URL]
--2019-02-19 09:38:00--  https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
正在连接 dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm [跟随至新的 URL]
--2019-02-19 09:38:05--  https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 184.26.113.98
正在连接 repo.mysql.com (repo.mysql.com)|184.26.113.98|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：25548 (25K) [application/x-redhat-package-manager]
正在保存至: “mysql57-community-release-el7-10.noarch.rpm”

100%[===========================================>] 25,548      --.-K/s 用时 0.1s    

2019-02-19 09:38:05 (236 KB/s) - 已保存 “mysql57-community-release-el7-10.noarch.rpm” [25548/25548])

[root@lwf src]# ls
debug  kernels  mysql57-community-release-el7-10.noarch.rpm
[root@lwf src]# yum -y install mysql57-community-release-el7-10.noarch.rpm 
已加载插件：product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
正在检查 mysql57-community-release-el7-10.noarch.rpm: mysql57-community-release-el7-10.noarch
mysql57-community-release-el7-10.noarch.rpm 将被安装
正在解决依赖关系
--> 正在检查事务
---> 软件包 mysql57-community-release.noarch.0.el7-10 将被 安装
--> 解决依赖关系完成

依赖关系解决

=====================================================================================
 Package                架构   版本   源                                        大小
=====================================================================================
正在安装:
 mysql57-community-release
                        noarch el7-10 /mysql57-community-release-el7-10.noarch  30 k

事务概要
=====================================================================================
安装  1 软件包

总计：30 k
安装大小：30 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  正在安装    : mysql57-community-release-el7-10.noarch                          1/1 
  验证中      : mysql57-community-release-el7-10.noarch                          1/1 

已安装:
  mysql57-community-release.noarch 0:el7-10                                          

完毕！
[root@lwf src]# ls /etc/yum.repos.d/
CentOS7-Base-163.repo  epel-testing.repo     mysql-community-source.repo
epel.repo              mysql-community.repo  redhat.repo
[root@lwf src]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel
已加载插件：product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
mysql-connectors-community                                    | 2.5 kB  00:00:00     
mysql-tools-community                                         | 2.5 kB  00:00:00     
mysql57-community                                             | 2.5 kB  00:00:00     
(1/3): mysql-connectors-community/x86_64/primary_db           |  37 kB  00:00:01     
(2/3): mysql-tools-community/x86_64/primary_db                |  54 kB  00:00:01     
(3/3): mysql57-community/x86_64/primary_db                    | 170 kB  00:00:02     
正在解决依赖关系
--> 正在检查事务
---> 软件包 mysql-community-client.x86_64.0.5.7.25-1.el7 将被 安装
--> 正在处理依赖关系 mysql-community-libs(x86-64) >= 5.7.9，它被软件包 mysql-community-client-5.7.25-1.el7.x86_64 需要
---> 软件包 mysql-community-common.x86_64.0.5.7.25-1.el7 将被 安装
---> 软件包 mysql-community-devel.x86_64.0.5.7.25-1.el7 将被 安装
---> 软件包 mysql-community-server.x86_64.0.5.7.25-1.el7 将被 安装
--> 正在检查事务
---> 软件包 mariadb-libs.x86_64.1.5.5.56-2.el7 将被 取代
--> 正在处理依赖关系 libmysqlclient.so.18()(64bit)，它被软件包 2:postfix-2.10.1-6.el7.x86_64 需要
--> 正在处理依赖关系 libmysqlclient.so.18(libmysqlclient_18)(64bit)，它被软件包 2:postfix-2.10.1-6.el7.x86_64 需要
---> 软件包 mysql-community-libs.x86_64.0.5.7.25-1.el7 将被 舍弃
--> 正在检查事务
---> 软件包 mysql-community-libs-compat.x86_64.0.5.7.25-1.el7 将被 舍弃
---> 软件包 postfix.x86_64.2.2.10.1-6.el7 将被 升级
---> 软件包 postfix.x86_64.2.2.10.1-7.0.1.el7.centos 将被 更新
--> 正在处理依赖关系 libpq.so.5()(64bit)，它被软件包 2:postfix-2.10.1-7.0.1.el7.centos.x86_64 需要
--> 正在检查事务
---> 软件包 postgresql-libs.x86_64.0.9.2.24-1.el7_5 将被 安装
--> 解决依赖关系完成

依赖关系解决

=====================================================================================
 Package                    架构   版本                      源                 大小
=====================================================================================
正在安装:
 mysql-community-client     x86_64 5.7.25-1.el7              mysql57-community  24 M
 mysql-community-common     x86_64 5.7.25-1.el7              mysql57-community 274 k
 mysql-community-devel      x86_64 5.7.25-1.el7              mysql57-community 3.7 M
 mysql-community-libs       x86_64 5.7.25-1.el7              mysql57-community 2.2 M
      替换  mariadb-libs.x86_64 1:5.5.56-2.el7
 mysql-community-libs-compat
                            x86_64 5.7.25-1.el7              mysql57-community 2.0 M
      替换  mariadb-libs.x86_64 1:5.5.56-2.el7
 mysql-community-server     x86_64 5.7.25-1.el7              mysql57-community 165 M
为依赖而安装:
 postgresql-libs            x86_64 9.2.24-1.el7_5            base              234 k
为依赖而更新:
 postfix                    x86_64 2:2.10.1-7.0.1.el7.centos centosplus        2.5 M

事务概要

安装  6 软件包 (+1 依赖软件包)
升级           ( 1 依赖软件包)

总下载量：200 M
Downloading packages:
已安装:
  mysql-community-client.x86_64 0:5.7.25-1.el7                                       
  mysql-community-common.x86_64 0:5.7.25-1.el7                                       
  mysql-community-devel.x86_64 0:5.7.25-1.el7                                        
  mysql-community-libs.x86_64 0:5.7.25-1.el7                                         
  mysql-community-libs-compat.x86_64 0:5.7.25-1.el7                                  
  mysql-community-server.x86_64 0:5.7.25-1.el7                                       

作为依赖被安装:
  postgresql-libs.x86_64 0:9.2.24-1.el7_5                                            

作为依赖被升级:
  postfix.x86_64 2:2.10.1-7.0.1.el7.centos                                           

替代:
  mariadb-libs.x86_64 1:5.5.56-2.el7                                                 

完毕！

2.2 mysql配置

[root@lwf ~]# systemctl start mysqld
[root@lwf ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 二 2019-02-19 15:17:59 CST; 10s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3889 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3815 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3892 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3892 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.p...

2月 19 15:17:42 lwf.localdomain systemd[1]: Starting MySQL Server...
2月 19 15:17:59 lwf.localdomain systemd[1]: Started MySQL Server.
[root@lwf ~]# ss -anlt
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128            *:22                         *:*                  
LISTEN      0      100    127.0.0.1:25                         *:*                  
LISTEN      0      128           :::80                        :::*                  
LISTEN      0      32            :::21                        :::*                  
LISTEN      0      128           :::22                        :::*                  
LISTEN      0      100          ::1:25                        :::*                  
LISTEN      0      80            :::3306                      :::*        
[root@lwf ~]# grep "password" /var/log/mysqld.log 
2019-02-19T07:17:46.542353Z 1 [Note] A temporary password is generated for root@lwf: RBzuhdfLo3%J
[root@lwf ~]# 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.25

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

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

mysql> ALTER USER 'root'@'lwf' IDENTIFIED BY 'lwf1234';
Query OK, 0 rows affected (0.00 sec)
[root@lwf ~]# yum -y remove mysql57-community-release-el7-10.noarch
已加载插件：product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
正在解决依赖关系
--> 正在检查事务
---> 软件包 mysql57-community-release.noarch.0.el7-10 将被 删除
--> 解决依赖关系完成

依赖关系解决

=====================================================================================
 Package                          架构          版本          源                大小
=====================================================================================
正在删除:
 mysql57-community-release        noarch        el7-10        installed         30 k

事务概要
=====================================================================================
移除  1 软件包

安装大小：30 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  正在删除    : mysql57-community-release-el7-10.noarch                          1/1 
centosplus/x86_64                                             | 3.4 kB  00:00:00     
epel/x86_64/metalink                                          | 9.0 kB  00:00:00     
extras/x86_64                                                 | 3.4 kB  00:00:00     
updates/x86_64                                                | 3.4 kB  00:00:00     
  验证中      : mysql57-community-release-el7-10.noarch                          1/1 

删除:
  mysql57-community-release.noarch 0:el7-10                                          

完毕！

3. mysql的程序组成

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

3.1 mysql工具使用

语法：mysql [OPTIONS] [database]

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

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

Copyright (c) 2000, 2019, 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> 
[root@lwf ~]# mysql -uroot -p -h 127.0.0.1 -e 'SHOW DATABASES;'

Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

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

socket	类型 说明
ip	socket 默认监听在tcp的3306端口，支持远程通信
unix sock	监听在sock文件上（/tmp/mysql.sock，/var/lib/mysql/mysql.sock 仅支持本地通信 server地址只能是：localhost，127.0.0.1

4. mysql数据库操作

4.1 DDL操作

4.1.1 数据库操作

• 创建数据库
  语法：CREATE DATABASE [IF NOT EXISTS] 'DB_NAME;

mysql>  create database if not exists liwenfeng;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liwenfeng             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

• 删除数据库
  语法：DROP DATABASE [IF EXISTS] ‘DB_NAME’;
  mysql> drop database if exists liwenfeng;
  Query OK, 0 rows affected (0.02 sec)

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

4.1.2 表操作

• 创建表
  语法：CREATE TABLE table_name (col1 datatype 修饰符,col2 datatype 修饰符) ENGINE=‘存储引擎类型’;

mysql> use liwenfeng;
Database changed
mysql> create table liwenfeng (id int NOT NULL,name varchar(100) not null,age tinyint);
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+------------------+
| Tables_in_liwenfeng |
+------------------+
| liwenfeng           |
+------------------+
1 row in set (0.01 sec)

4.1.3 用户操作

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

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

数据库用户创建
语法：CREATE USER ‘username’@‘host’ [IDENTIFIED BY ‘password’];

mysql> create user 'lwf'@'127.0.0.1' identified by 'lwf1234';
Query OK, 0 rows affected (0.03 sec)

mysql> quit
Bye
[root@lwf ~]# mysql -ulwf -plwf1234 -h127.0.0.1
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> 

删除数据库用户
语法：DROP USER ‘username’@‘host’;

mysql> drop user 'lwf'@'127.0.0.1';
Query OK, 0 rows affected (0.01 sec)

4.1.4 查看命令SHOW

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

mysql> show engines;   //查看当前数据库支持的所有存储引擎
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show databases;    //查看数据库信息
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liwenfeng             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> show tables from liwenfeng;   //不进入某数据库而列出其包含的所有表
+------------------+
| Tables_in_liwenfeng |
+------------------+
| liwenfeng           |
+------------------+
1 row in set (0.00 sec)

查看表结构
语法：DESC [db_name.]table_name;

mysql> desc liwenfeng.liwenfeng;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.17 sec)

查看某表的创建命令
语法：SHOW CREATE TABLE table_name;

mysql> show create table liwenfeng.liwenfeng;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                            |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| liwenfeng | CREATE TABLE `liwenfeng` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `age` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

查看某表的状态
语法：SHOW TABLE STATUS LIKE ‘table_name’\G

mysql> show table status like 'liwenfeng'\G   //查看liwenfeng表的状态
*************************** 1. row ***************************
           Name: liwenfeng
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2019-02-19 15:50:31
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

4.1.5 获取帮助

获取命令使用帮助
语法：HELP keyword;

mysql> help create table;  //获取创建表的帮助

Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

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

4.2 DML操作

DML操作包括增(INSERT)、删(DELETE)、改(UPDATE)、查(SELECT)，均属针对表的操作。

4.2.1 INSERT语句

DML操作之增操作insert
语法：INSERT [INTO] table_name [(column_name,…)] {VALUES | VALUE} (value1,…),(…),…

mysql> use liwenfeng;
Database changed
mysql> INSERT INTO liwenfeng (id,name,age) VALUE (1,'tom',20);
Query OK, 1 row affected (0.12 sec)

4.2.2 SELECT语句

DML操作之查操作select
语法：SELECT column1,column2,… FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];

mysql> use liwenfeng;
Database changed
mysql> select * from liwenfeng;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select name from liwenfeng;
+------+
| name |
+------+
| tom  |
+------+
1 row in set (0.01 sec)

mysql> select * from liwenfeng order by age;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT * FROM liwenfengORDER BY age DESC;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)

4.2.3 update语句

DML操作之改操作update
语法：UPDATE table_name SET column1 = new_value1[,column2 = new_value2,…] [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];

mysql> select * from liwenfeng ;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.00 sec)
mysql> update liwenfeng set age = 20 where name = 'tom';
Query OK, 0 rows affected (1.84 sec)
Rows matched: 1  Changed: 0  Warnings: 0
mysql> select * from liwenfeng where name = 'tom';
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   20 |
+----+------+------+
1 row in set (0.01 sec)

4.2.4 delete语句

DML操作之删操作delete
语法：DELETE FROM table_name [WHERE clause] [ORDER BY ‘column_name’ [DESC]] [LIMIT [m,]n];

mysql> select * from liwenfeng;                                                        
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
|  7 | lisi      | NULL |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql> delete from liwenfeng where id = 7
    -> ;
Query OK, 1 row affected (0.02 sec)

mysql> select * from liwenfeng;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
6 rows in set (0.00 sec)

mysql> delete from liwenfeng;        //删除整张表的内容
Query OK, 6 rows affected (0.00 sec)

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

mysql> desc liwenfeng;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.20 sec)

4.2.5 truncate语句

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间
truncate	删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据不能用于加入了索引视图的表

语法：TRUNCATE table_name;

mysql> select * from liwenfeng;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  1 | tom       |   20 |
|  2 | jerry     |   23 |
|  3 | wangqing  |   25 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  6 | zhangshan |   20 |
+----+-----------+------+
6 rows in set (0.00 sec)

mysql> truncate liwenfeng;
Query OK, 0 rows affected (0.66 sec)

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

mysql> desc liwenfeng;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

4.3 DCL操作

4.3.1 创建授权grant

权限类型(priv_type)

权限类型	代表什么？
ALL	所有权限
SELECT	读取内容的权限
INSERT	插入内容的权限
UPDATE	更新内容的权限
DELETE	删除内容的权限

指定要操作的对象db_name.table_name

表示方式	表示方式
.	所有库的所有表
db_name	指定库的所有表
db_name.table_name	指定库的指定表

WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户，说白点就是将自己的权限完全复制给另一个用户。不建议使用。

GRANT priv_type,… ON [object_type] db_name.table_name TO ‘username’@‘host’ [IDENTIFIED BY ‘password’] [WITH GRANT OPTION];

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

//授权wangqing用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'liwenfeng'@'lwf' IDENTIFIED BY 'lwf1234';
Query OK, 0 rows affected, 1 warning (0.54 sec)

mysql> GRANT ALL ON *.* TO 'liwenfeng'@'127.0.0.1' IDENTIFIED BY 'lwf1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//授权wangqing用户在172.16.12.129上远程登录访问wangqingge数据库
mysql> GRANT ALL ON liwenfeng.* TO 'liwenfeng'@'192.168.110.21' IDENTIFIED BY 'lwf1234'; 
Query OK, 0 rows affected, 1 warning (0.04 sec)

//授权wangqing用户在所有位置上远程登录访问wangqingge数据库
mysql> GRANT ALL ON *.* TO 'liwenfeng'@'%' IDENTIFIED BY 'lwf1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.3.2 查看授权

查看当前登录用户的授权信息

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@lwf                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'lwf' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'lwf' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户liwenfeng的授权信息

mysql> show grants for liwenfeng;
+---------------------------------------------+
| Grants for liwenfeng@%                         |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'liwenfeng'@'%' |
+---------------------------------------------+
1 row in set (0.02 sec)

mysql> SHOW GRANTS FOR 'liwenfeng'@'lwf';
+-----------------------------------------------------+
| Grants for liwenfeng@lwf                         |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'liwenfeng'@'lwf' |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for 'liwenfeng'@'127.0.0.1';
+-----------------------------------------------------+
| Grants for liwenfeng@127.0.0.1                         |
+-----------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'liwenfeng'@'127.0.0.1' |
+-----------------------------------------------------+
1 row in set (0.00 sec)

4.3.3 取消授权REVOKE

语法：REVOKE priv_type,… ON db_name.table_name FROM ‘username’@‘host’;

mysql> revoke all on *.* from 'liwenfeng'@'192.168.110.21';
Query OK, 0 rows affected (0.00 sec)
 
mysql> drop user 'liwenfeng'@'192.168.110.21';
 
mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)

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

• GRANT或REVOKE等执行权限操作会保存于表中，mysql的服务进程会自动重读授权表，并更新至内存中
• 对于不能够或不能及时重读授权表的命令，可手动让mysql的服务进程重读授权

实例

1.搭建mysql服务

[root@lwf src]# wget http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
[root@lwf src]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel
[root@lwf ~]# systemctl start mysqld
[root@lwf ~]# ss -anlt
State       Recv-Q Send-Q Local Address:Port               Peer Address:Port              
LISTEN      0      128            *:22                         *:*                  
LISTEN      0      100    127.0.0.1:25                         *:*                  
LISTEN      0      128           :::80                        :::*                  
LISTEN      0      32            :::21                        :::*                  
LISTEN      0      128           :::22                        :::*                  
LISTEN      0      100          ::1:25                        :::*                  
LISTEN      0      80            :::3306                      :::*        

2.创建一个以你名字为名的数据库，并创建一张表student，该表包含三个字段（id，name，age），表结构如下：

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

mysql> create database if not exists lwf;
Query OK, 1 row affected (0.10 sec)

mysql> use lwf;
Database changed
mysql> create table student (id int(10),name varchar(50),age int(10));
Query OK, 0 rows affected (0.25 sec)

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(10)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(10)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

3.查看下该新建的表有无内容（用select语句）

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

4.往新建的student表中插入数据（用insert语句），结果应如下所示：

±—±------------±-----+
| id | name | age |
±—±------------±-----+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 6 | zhangshan | 20 |
| 7 | lisi | NULL |

mysql>  INSERT INTO student (id,name,age) VALUES (2,'jerry',23),(3,'wangqing',25),(4,'sean',,28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> INSERT INTO student (id,name,age) VALUES (2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL);
Query OK, 6 rows affected (0.09 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from student;                                                       
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    2 | jerry     |   23 |
|    3 | wangqing  |   25 |
|    4 | sean      |   28 |
|    5 | zhangshan |   26 |
|    6 | zhangshan |   20 |
|    7 | lisi      | NULL |
+------+-----------+------+
6 rows in set (0.00 sec)

5.修改lisi的年龄为50

mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student where name = 'lisi';
+------+------+------+
| id   | name | age  |
+------+------+------+
|    7 | lisi |   50 |
+------+------+------+
1 row in set (0.00 sec)

6.以age字段降序排序

mysql> select * from student order by age desc;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    7 | lisi      |   50 |
|    4 | sean      |   28 |
|    5 | zhangshan |   26 |
|    3 | wangqing  |   25 |
|    2 | jerry     |   23 |
|    6 | zhangshan |   20 |
+------+-----------+------+
6 rows in set (0.00 sec)

7.查询student表中年龄最小的3位同学

mysql> select * from student order by age limit 3;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    6 | zhangshan |   20 |
|    2 | jerry     |   23 |
|    3 | wangqing  |   25 |
+------+-----------+------+
3 rows in set (0.00 sec)

8.查询student表中年龄最大的4位同学

mysql>  select * from student order by age desc limit 4;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    7 | lisi      |   50 |
|    4 | sean      |   28 |
|    5 | zhangshan |   26 |
|    3 | wangqing  |   25 |
+------+-----------+------+
4 rows in set (0.04 sec)

9.查询student表中名字叫zhangshan的记录

mysql> select * from student where name = 'zhangshan';
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    5 | zhangshan |   26 |
|    6 | zhangshan |   20 |
+------+-----------+------+
2 rows in set (0.00 sec)

10.查询student表中名字叫zhangshan且年龄大于20岁的记录

mysql> select * from student where name = 'zhangshan' and age >=20;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    5 | zhangshan |   26 |
|    6 | zhangshan |   20 |
+------+-----------+------+
2 rows in set (0.04 sec)

11.查询student表中年龄在23到30之间的记录

mysql> select * from student where age between 23 and 30;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    2 | jerry     |   23 |
|    3 | wangqing  |   25 |
|    4 | sean      |   28 |
|    5 | zhangshan |   26 |
+------+-----------+------+
4 rows in set (0.00 sec)

12.修改zhangshan的年龄为100

mysql> update student set age = 100 where name = 'zhangshan';
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from student where name = 'zhangshan';
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    5 | zhangshan |  100 |
|    6 | zhangshan |  100 |
+------+-----------+------+
2 rows in set (0.00 sec)

13.删除student中名字叫zhangshan且年龄小于等于20的记录

mysql> delete from student where name = 'zhangshan' and age<=20;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    2 | jerry     |   23 |
|    3 | wangqing  |   25 |
|    4 | sean      |   28 |
|    5 | zhangshan |  100 |
|    6 | zhangshan |  100 |
|    7 | lisi      |   50 |
+------+-----------+------+
6 rows in set (0.00 sec)