第11周

本章内容

关系型数据库基础

MySQL架构

存储引擎

安装MySQL

服务器选项，系统和状态变量

管理数据库和表

用户和权限管理

函数和存储过程

优化查询和索引管理

锁和事务管理

日志管理

备份还原

MySQL集群•

数据的时代

涉及的数据量大

数据不随程序的结束而消失

数据被多个应用程序共享

大数据

数据库的发展史

萌芽阶段—–文件系统

使用磁盘文件来存储数据

初级阶段—–第一代数据库

出现了网状模型、层次模型的数据库

中级阶段—–第二代数据库

关系型数据库和结构化查询语言（类似于横行纵列的表格）

高级阶段——新一代数据库

“关系-对象”型数据库

文件管理系统的缺点

编写应用程序不方便

数据冗余不可避免

应用程序依赖性

不支持对文件的并发访问

数据间联系弱

难以按用户视图表示数据

无安全控制功能

数据库管理系统（DBMS）的优点

相互关联的数据的集合

较少的数据冗余

程序与数据相互独立存储引擎就是数据存贮的格式

保证数据的安全、可靠（可精确到某个列可以访问）

最大限度地保证数据的正确性（比如年龄可以限制范围1-100等）

数据可以并发使用并能同时保证一致性

其实就是数据按照数据管理的格式存放在硬盘上，访问时需要通过数据库管理软件来访问

数据库管理系统

数据库是数据的汇集，它以一定的组织形式存于存储介质上（磁盘或者内存中（memcache））

DBMS是管理数据库的系统软件，它实现数据库系统的各种功能。是数据库系

统的核心

DBA：负责数据库的规划、设计、协调、维护和管理等工作

应用程序指以数据库为基础的应用程序

raw文件系统，就是没有文件系统的磁盘空间，就是裸磁盘。可以存放数据但是存放的是0101

数据库管理系统也可以访问这种系统

数据库管理系统的基本功能

数据定义（定义列是什么类型比如是整数或者字符等）

数据处理（增删改查等）

数据安全（加权限控制）

数据备份（必须会）

数据库系统的架构

单机架构（单个人使用如财务软件）

大型主机/终端架构（一个主机多个终端）

主从式架构（C/S）（MYSQL等数据库都是这种架构）性能上有瓶颈

分布式架构将数据分散到多个服务器上提高性能

关系型数据库（性能不好）

不是所有的数据库都是关系型的

NO SQL

not only sql

memcached redis （k/v）mongodb(文档)

关系：关系就是二维表。并满足如下性质：

表中的行、列次序并不重要

行row：表中的每一行，又称为一条记录

列column：表中的每一列，称为属性，字段

主键（Primary key）：用于惟一确定一个记录（record）的字段（PK）这个字段所有记录的内容不能重复

域domain：属性的取值范围，如，性别只能是‘男’和‘女’两个值

主键（班级编号）学员课程课程编号课程

关系数据库

RDBMS：

MySQL: MySQL, MariaDB, Percona Server

PostgreSQL: 简称为pgsql，EnterpriseDB

Oracle：

MSSQL：

DB2:

事务transaction：多个操作被当作一个整体对待（5个操作如果在一个事务里要么都做要么都不做）

ACID:

A: 原子性比如取钱（如果事务做到一半断电了，那么系统恢复后不会继续任务而是依据log回滚到没做事务之前的状态）

C：一致性（列如俩个银行卡之间汇钱，一个减少一个增多但是总的不变）

I: 隔离性（B的事务没有结束之前A只能看到110）有一定得隔离级别

D：持久性事务结束，数据永久生效保存在磁盘中

隔离性如图

关系型数据库相关概念介绍

（1）概念：

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。

（2）关系：

关系就是指存储数据的二维表，表种的行和列的存贮不注重次序，因为展示可以通过相关操作来实现次序的需求。

（3）行row：

表中的每一行，又称为一条记录record

（4）列column：

表中的每一列，称为属性，字段，域（field）；这个域和domain这个域的含义不同。前者指列的一种称呼，后者指列的取值范围。

（5）事务（transaction）：

事务(Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)，通常就是指数据库事务。

事务的特性：ACID

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。

隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。

持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中。

实体-联系模型E-R

实体Entity

• 客观存在并可以相互区分的客观事物或抽象事件称为实体。

• 在E-R图中用矩形框表示实体，把实体名写在框内

属性

• 实体所具有的特征或性质

联系

联系是数据之间的关联集合，是客观存在的应用语义链

• 实体内部的联系：指组成实体的各属性之间的联系。如职工实体中，职工号和

部门经理号之间有一种关联关系

• 实体之间的联系：指不同实体之间联系。例学生选课实体和学生基本信息实体

之间

• 实体之间的联系用菱形框表示

联系类型

联系的类型

一对一联系(1:1)

一对多联系(1:n)

多对多联系(m:n)

一对多如图

员工部门

foreign key 外键

多对多如图

学员编号课程

5.数据三要素

（1）数据结构：

包括两类，一类是与数据类型、内容、性质有关的对象，比如关系模型中的域、属性和关系等；另一类是与数据之间联系有关的对象，它从数据组织层表达数据记录与字段的结构。

（2）数据的操作：

数据提取（SELECT）在数据集合中提取感兴趣的内容。

数据更新（INSERT、DELETE、 UPDATE ）变更数据库中的数据。

（3）数据的约束条件：

是一组完整性规则的集合。

实体（行）完整性 Entity integrity

值行的唯一性，列和列总是不同的，利用主键实现（唯一键UK（作用在字段上表示这个字段不能有重复的但是可以是空值（null），主键只有一个且不能是空，唯一键可以有多个））

域（列）完整性 Domain Integrity

指有一个合理的取值范围

参考完整性 Referential Integrity（就是外键两个表之间的。）

简易数据规划流程

第一阶段：收集数据，得到字段

• 收集必要且完整的数据项

• 转换成数据表的字段

第二阶段：把字段分类，归入表，建立表的关联

• 关联：表和表间的关系

• 分割数据表并建立关联的优点

• 节省空间

• 减少输入错误

• 方便数据修改

第三阶段：

• 规范化数据库

数据库的正规化分析

RDMBS设计范式基础概念

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不

同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越

小

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式

（3NF）、巴德斯科范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称

完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上

进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般

说来，数据库只需满足第三范式(3NF）即可

范式是可以违反的。

范式

1NF：无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个

值，即实体中的某个属性不能有多个值或者不能有重复的属性。除去同类型的

字段，就是无重复的列

说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的

数据库就不是关系数据库

2NF：属性完全依赖于主键，第二范式必须先满足第一范式，要求表中的每个

行必须可以被唯一地区分。通常为表加上一个列，以存储各个实例的唯一标识

PK，非PK的字段需要与整个PK有直接相关性

3NF：属性不依赖于其它非主属性，满足第三范式必须先满足第二范式。第三

范式要求一个数据库表中不包含已在其它表中已包含的非主关键字信息，非PK

的字段间不能有从属关系

第一范式

第二范式：

城市编号和人名没有直接的依赖性其他都符合第二范式。

单独设置一张表

城市编号城市名称城市电话区号

复合主键

约束

7.数据库约束

（1）主键

关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键。

要求：必须提供数据，即字段不为空（null）；字段不重复，即唯一标识本行；一张表只能有一个主键。

目的：确定一条唯一的记录。

（2）复合主键

表中一个属性无法唯一标识一条记录，用两条属性组才可以唯一标识一条记录，那么两条属性主就组成了符合主键。单独的每列的字段可重复，但是两个列组成主键后总字段是不重复的。

（3）唯一键

一个或多个字段的组合，填入的数据必须能在本表唯一标识本行。和主键不同之处：在表中可以有多个；允许为空值

（4）外键

一个表中的某字段可填入的数据取决于另一个表的主键或唯一键已有的数据，键表示了两个表之间的相关联系。一个表的主键是另外一张表的外键（唯一键也可以做外键）

检查：字段值在一定范围内

SQL概念

SQL: Structure Query Language

结构化查询语言（只关注数据是什么而不关注在磁盘上的格式）

SQL解释器：

数据存储协议：应用层协议，C/S

S：server, 监听于套接字，接收并处理客户端的应用请求标准3306端口号

C：Client

程序接口

CLI

GUI

应用编程接口程序要和数据库连接要符合数据库接口

ODBC：Open Database Connectivity

JDBC：Java Data Base Connectivity

基本概念

索引：将表中的一个或多个字段中的数据复制一份另存，并且此些需要按特定（例如书的目录）

次序排序存储（如果添加新的章节那么需要更新整个目录和页数）如果用于查看则使用索引如果改的多读的少则看情况添加索引

关系运算：

选择：挑选出符合条件的行挑行（什么手机）

投影：挑选出需要的字段挑列（什么cpu）

连接：表间字段的关联（外键）

数据模型

数据抽象：

物理层：数据存储格式，即RDBMS在磁盘上如何组织文件（存放文件的格式不同的引擎不同的格式）为了优化性能，将文件（数据和日志）放在不同的位置

逻辑层：DBA角度，描述存储什么数据，以及数据间存在什么样的关系（约束，第一范式等）

视图层：用户角度，描述DB中的部分数据（列如商品中的一些属性价格等）（进货价格不让查看）

关系模型的分类：

关系模型

基于对象的关系模型

半结构化的关系模型：XML数据（扩展的标记语言）

在centos6中/etc/gconf/gconf.xml.defaults/%gconf-tree.xml就是XML格式的

MySQL历史

1979年：TcX公司 Monty Widenius，Unireg数据库引擎

1996年：发布MySQL1.0，Solaris版本，Linux版本

1999年：MySQL AB公司，瑞典

2003年：MySQL 5.0版本，提供视图、存储过程等功能

2008年：sun 收购

2009年：oracle收购sun

2009年：Monty成立MariaDB

MySQL和MariaDB

官方网址：

https://www.mysql.com/

http://mariadb.org/

官方文档

https://dev.mysql.com/doc/

https://mariadb.com/kb/en/

版本演变：

MySQL：5.1 –> 5.5 –> 5.6 –> 5.7

MariaDB：5.5 –>10.0–> 10.1 –> 10.2 –> 10.3

MariaDB的特性

插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特

性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是

MYSQL默认引擎

MyISAM ==> Aria

InnoDB ==> XtraDB

存储引擎比较：

https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-

engines.html

单进程，多线程

诸多扩展和新特性

提供了较多测试组件

开源

安装 Mariadb

Mariadb安装方式：

1、源代码：编译安装

2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用

3、程序包管理器管理的程序包

CentOS安装光盘

项目官方：

https://downloads.mariadb.org/mariadb/repositories/

[root@centos ~]# yum install mysql-server

/etc/rc.d/init.d/mysqld 服务程序

/usr/libexec/mysqld 二进制主程序

/var/lib/mysql 用户数据存放位置

[root@centos ~]# service mysqld start 监听端口是3306

[root@centos ~]# ll !*

ll /var/lib/mysql

total 20488

-rw-rw—- 1 mysql mysql 10485760 Jun 5 11:37 ibdata1

-rw-rw—- 1 mysql mysql 5242880 Jun 5 11:37 ib_logfile0

-rw-rw—- 1 mysql mysql 5242880 Jun 5 11:37 ib_logfile1

drwx—— 2 mysql mysql 4096 Jun 5 11:37 mysql

srwxrwxrwx 1 mysql mysql 0 Jun 5 11:37 mysql.sock 本地连接

drwx—— 2 mysql mysql 4096 Jun 5 11:37 test

[root@centos ~]# which mysql

/usr/bin/mysql

[root@centos ~]# rpm -qf /usr/bin/mysql

mysql-5.1.73-8.el6_8.x86_64

[root@centos ~]# mysql 可以直接连接数据库

mysql> \h

? (\?) Synonym for `help’.

clear (\c) Clear the current input statement.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter.

edit (\e) Edit command with $EDITOR.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

help (\h) Display this help.

nopager (\n) Disable pager, print to stdout.

notee (\t) Don’t write into outfile.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print (\p) Print current command.

prompt (\R) Change your mysql prompt.

quit (\q) Quit mysql.

rehash (\#) Rebuild completion hash.

source (\.) Execute an SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

system (\!) Execute a system shell command.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don’t show warnings after every statement.

mysql> show databases; 必须加；

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| test |

+——————–+

3 rows in set (0.00 sec)

mysql> \! hostname 执行linux命令

centos.localdomain

mysql> status

————–

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 5

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ”

Using delimiter: ;

Server version: 5.1.73 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: latin1

Conn. characterset: latin1

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 10 min 18 sec

Threads: 1 Questions: 12 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.19

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

————–

mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Connection id: 5

Current database: mysql

Current user: root@localhost mysql自身的用户账号

SSL: Not in use

Current pager: stdout

Using outfile: ”

Using delimiter: ;

Server version: 5.1.73 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: latin1

Conn. characterset: latin1

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 11 min 59 sec

Threads: 1 Questions: 41 Slow queries: 0 Opens: 30 Flush tables: 1 Open tables: 23 Queries per second avg: 0.57

————–

[root@CENTOS7 ~]# yum install mariadb-server

[root@CENTOS7 ~]# systemctl start mariadb

[root@CENTOS7 ~]# ss -ntl

LISTEN 0 50 *:3306 *:*

[root@CENTOS7 ~]# pstree -p 多线程

├─mysqld_safe(2885)───mysqld(3047)─┬─{mysqld}(3051)

│ ├─{mysqld}(3052)

│ ├─{mysqld}(3053)

│ ├─{mysqld}(3054)

│ ├─{mysqld}(3055)

│ ├─{mysqld}(3056)

│ ├─{mysqld}(3057)

│ ├─{mysqld}(3058)

│ ├─{mysqld}(3059)

│ ├─{mysqld}(3060)

│ ├─{mysqld}(3061)

│ ├─{mysqld}(3062)

│ ├─{mysqld}(3063)

│ ├─{mysqld}(3064)

│ ├─{mysqld}(3065)

│ ├─{mysqld}(3066)

│ ├─{mysqld}(3074)

│ └─{mysqld}(3075)

[root@CENTOS7 ~]# ll /var/lib/mysql 启动mariadb后自动生成的文件，所有的文件及文件夹都是masql所拥有

total 28700

-rw-rw—- 1 mysql mysql 16384 Jun 5 11:59 aria_log.00000001

-rw-rw—- 1 mysql mysql 52 Jun 5 11:59 aria_log_control

-rw-rw—- 1 mysql mysql 18874368 Jun 5 11:59 ibdata1

-rw-rw—- 1 mysql mysql 5242880 Jun 5 11:59 ib_logfile0

-rw-rw—- 1 mysql mysql 5242880 Jun 5 11:59 ib_logfile1

drwx—— 2 mysql mysql 4096 Jun 5 11:59 mysql

srwxrwxrwx 1 mysql mysql 0 Jun 5 11:59 mysql.sock

drwx—— 2 mysql mysql 4096 Jun 5 11:59 performance_schema

drwx—— 2 mysql mysql 6 Jun 5 11:59 test

mysql performance_schema test 这3个文件夹对应的就是3个mysql数据库

[root@CENTOS7 ~]# mysql

MariaDB [(none)]> show databases;

+——————–+

| Database |

+——————–+

| information_schema | 内存中虚拟的数据库

| mysql |

| performance_schema |

| test |

+——————–+

4 rows in set (0.00 sec)

[root@CENTOS7 mysql]# cp -r mysql mysqlcron

[root@CENTOS7 mysql]# ls

aria_log.00000001 ibdata1 ib_logfile1 mysqlcron performance_schema

aria_log_control ib_logfile0 mysql mysql.sock test

[root@CENTOS7 mysql]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 4

Server version: 5.5.56-MariaDB MariaDB Server

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

MariaDB [(none)]> show databases

-> ;

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| mysqlcron |

| performance_schema |

| test |

+——————–+

5 rows in set (0.00 sec)

如果想备份数据库也可以备份文件夹，但是文件夹过大可能导致在备份的过程中使其中的文件发生改变，（备份的文件时间必须一致），在备份是可以停止数据库服务后再备份。（不建议使用此方法）

[wang@CENTOS7 ~]$ mysql

MariaDB [mysqlcron]> 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]> show tables 查看数据库mysql内容

-> ;

+—————————+

| Tables_in_mysql |

+—————————+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+—————————+

24 rows in set (0.00 sec)

MariaDB [mysql]> select * from user 查看user表的内容

-> ;

MariaDB [mysql]> select * from user\G 竖行显示

*************************** 1. row ***************************

Host: localhost

User: root

Password:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

*************************** 2. row ***************************

Host: centos7.localdomain

User: root

Password:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

*************************** 3. row ***************************

Host: 127.0.0.1

User: root

Password:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

*************************** 4. row ***************************

Host: ::1

User: root

Password:

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

*************************** 5. row ***************************

Host: localhost

User:

Password:

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

*************************** 6. row ***************************

Host: centos7.localdomain

User:

Password:

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

plugin:

authentication_string:

6 rows in set (0.00 sec)

由于任何人都可以随意的访问数据库所以不安全

[root@CENTOS7 mysql]# chown mysql.mysql mysqlcron 修改文件或文件夹的所属组和所属者

MariaDB [(none)]> select user();

+—————-+

| user() |

+—————-+

| root@localhost |

+—————-+

1 row in set (0.00 sec)

MariaDB [mysql]> select user,password,host from user;

+——+———-+———————+

| user | password | host |

+——+———-+———————+

| root | | localhost |

| root | | centos7.localdomain |

| root | | 127.0.0.1 |

| root | | ::1 |

| | | localhost |

| | | centos7.localdomain |

+——+———-+———————+

6 rows in set (0.00 sec)

Rpm安装mariadb

安装和使用MariaDB

Rpm包安装

CentOS 7：安装光盘直接提供

mariadb-server 服务器包

mariadb 客户端工具包

CentOS 6

提高安全性

mysql_secure_installation

设置数据库管理员root口令

禁止root远程登录

删除anonymous用户帐号

删除test数据库

设置口令

[wang@CENTOS7 ~]$ mysql_secure_installation

[wang@CENTOS7 ~]$ mysql

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)

[wang@CENTOS7 ~]$ mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 17

Server version: 5.5.56-MariaDB MariaDB Server

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

MariaDB [(none)]>

MariaDB [(none)]> select user();

+—————-+

| user() |

+—————-+

| root@localhost |

+—————-+

1 row in set (0.00 sec)

MariaDB [mysql]> select user,password,host from user;

+——+——————————————-+———————+

| user | password | host |

+——+——————————————-+———————+

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | localhost |

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | centos7.localdomain |

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | 127.0.0.1 |

| root | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 | ::1 |

+——+——————————————-+———————+

4 rows in set (0.00 sec)

这是4个不同的账号账号分别是root localhost

[root@centos ~]# mysql -uroot -pmagedu -h192.168.30.100

ERROR 1130 (HY000): Host ‘192.168.30.99’ is not allowed to connect to this MariaDB server

MariaDB程序

 客户端程序：

mysql: 交互式的CLI工具

mysqldump: 备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所

有数据转换成insert等写操作语句保存文本文件中

mysqladmin：基于mysql协议管理mysqld

mysqlimport: 数据导入工具

 MyISAM存储引擎的管理工具：

myisamchk：检查MyISAM库

myisampack：打包MyISAM表，只读

服务器端程序

mysqld_safe

mysqld 获取默认设置： mysqld –print-defaults

mysqld_multi：多实例，示例：mysqld_multi –example 就是一个服务器上跑多份，一个mysql就是一个实例

[root@CENTOS7 ~]# getent passwd mysql 账号中设置的家目录就是数据库存放的路径

mysql:x:27:27:MariaDB Server:/var/lib/mysql:/sbin/nologin

安装mariadb-server时自动跑的安装前脚本

[root@CENTOS7 ~]# rpm -q –scripts mariadb-server

preinstall scriptlet (using /bin/sh):

/usr/sbin/groupadd -g 27 -o -r mysql >/dev/null 2>&1 || :

/usr/sbin/useradd -M -N -g mysql -o -r -d /var/lib/mysql -s /sbin/nologin \

-c “MariaDB Server” -u 27 mysql >/dev/null 2>&1 || :

useradd -o 可以忽略id号唯一性

多实例如图

用户账号

mysql用户账号由两部分组成：

‘USERNAME’@’HOST‘

说明：

HOST限制此用户可通过哪些远程主机连接mysql服务器

支持使用通配符：

% 匹配任意长度的任意字符

172.16.0.0/255.255.0.0 或 172.16.%.%

_ 匹配任意单个字符

Mysql 客户端

 mysql使用模式：

 交互式模式：

可运行命令有两类：

客户端命令：

\h, help

\u，use

\s，status

\!，system

服务器端命令：

SQL, 需要语句结束符；

 脚本模式：

mysql –uUSERNAME -pPASSWORD < /path/somefile.sql

mysql> source /path/from/somefile.sql

MariaDB [(none)]> create database testds 创建一个数据库

-> ;

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| mysqlcron |

| performance_schema |

| test |

| testds |

+——————–+

6 rows in set (0.00 sec)

[root@CENTOS7 ~]# cat test.sql

create database testdb2;

show databases;

[root@CENTOS7 ~]# mysql -uroot -pmagedu < test.sql

Database

information_schema

mysql

mysqlcron

performance_schema

test

testdb2

testds

MariaDB [(none)]> drop database testdb2; 删除

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> sorce test.sql

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘sorce test.sql’ at line 1

MariaDB [(none)]> source test.sql

Query OK, 1 row affected (0.00 sec)

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| mysqlcron |

| performance_schema |

| testdb2 |

+——————–+

5 rows in set (0.00 sec)

服务器端命令需要加；客户端命令不需要加；

prompt (\R) Change your mysql prompt.修改mysql的提示符

MariaDB [(none)]> prompt \u@[\D] \r:\m:\s->

PROMPT set to ‘\u@[\D] \r:\m:\s->’

root@[Tue Jun 5 14:39:13 2018] 02:39:13->

[root@CENTOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf

# These groups are read by MariaDB command-line tools

# Use it for options that affect only one utility

[mysql]

# 在此可以添加修改提示符的信息

3中修改提示符的方式

Use an environment variable. You can set the MYSQL_PS1 environment variable to a prompt string. For example:

shell> export MYSQL_PS1=”(\u@\h) [\d]> “

• Use a command-line option. You can set the –prompt option on the command line to mysql. For example:

shell> mysql –prompt=”(\u@\h) [\d]> “

(user@host) [database]>

• Use an option file. You can set the prompt option in the [mysql] group of any MySQL option file, such as /etc/my.cnf or the .my.cnf

file in your home directory. For example:

[mysql]

prompt=(\\u@\\h) [\\d]>\\_

Mysql客户端

mysql客户端可用选项：

-A, –no-auto-rehash 禁止补全

-u, –user= 用户名,默认为root

-h, –host= 服务器主机,默认为localhost

-p, –passowrd= 用户密码,建议使用-p,默认为空密码

-P, –port= 服务器端口

-S, –socket= 指定连接socket文件路径

-D, –database= 指定默认数据库

-C, –compress 启用压缩

-e “SQL“ 执行SQL命令

-V, –version 显示版本

-v –verbose 显示详细信息

–print-defaults 获取程序默认使用的配置

[root@CENTOS7 ~]# mysql -uroot -pmagedu -e “show databases;”

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| mysqlcron |

| performance_schema |

| testdb2 |

+——————–+

[root@CENTOS7 ~]# mysql -uroot -pmagedu -e “drop database testdb2;show databases;”

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| mysqlcron |

| performance_schema |

+——————–+

socket地址

服务器监听的两种socket地址：

ip socket: 监听在tcp的3306端口，支持远程通信

unix sock: 监听在sock文件上，仅支持本机通信

如：/var/lib/mysql/mysql.sock)

说明：host为localhost,127.0.0.1时自动使用unix sock

本机是 socket通讯远程tcp通讯

执行命令

 运行mysql命令：默认空密码登录

mysql>use mysql

mysql>select user();查看当前用户

mysql>SELECT User,Host,Password FROM user;

 登录系统：mysql –uroot –p

 客户端命令：本地执行

mysql> help

每个命令都完整形式和简写格式

mysql> status 或 \s

 服务端命令：通过mysql协议发往服务器执行并取回结果

每个命令都必须命令结束符号；默认为分号

SELECT VERSION();

服务器端配置

 服务器端(mysqld)：工作特性有多种配置方式

1、命令行选项：

2、配置文件：类ini格式

集中式的配置，能够为mysql的各应用程序提供配置信息

[mysqld]

[mysqld_safe]

[mysqld_multi]

[mysql]

[mysqldump]

[server]

[client]

格式：parameter = value

说明：_和- 相同

0，OFF，FALSE意义相同，1，ON，TRUE意义相同

服务器端配置

这些都是mysql的配置文件

[root@CENTOS7 ~]# cd /etc/my.cnf.d/

[root@CENTOS7 my.cnf.d]# ls

client.cnf mysql-clients.cnf server.cnf

[root@CENTOS7 ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

!includedir /etc/my.cnf.d

配置文件

配置文件：

后面覆盖前面的配置文件，顺序如下：

 /etc/my.cnf Global选项

 /etc/mysql/my.cnf Global全局选项

 SYSCONFDIR/my.cnf Global选项

 $MYSQL_HOME/my.cnf Server-specific 选项

 –defaults-extra-file=path

 ~/.my.cnf User-specific 选项

 获取可用参数列表：

mysqld –help –verbose

查看服务器默认配置

[root@CENTOS7 ~]# /usr/libexec/mysqld –print-defaults

/usr/libexec/mysqld would have been started with the following arguments:

–datadir=/var/lib/mysql –socket=/var/lib/mysql/mysql.sock –symbolic-links=0

MairaDB配置

侦听3306/tcp端口可以在绑定有一个或全部接口IP上

vim /etc/my.cnf

[mysqld]

skip-networking=1 关闭网络连接，只侦听本地客户端，所有和服务器的交互

都通过一个socket实现，socket的配置存放在/var/lib/mysql/mysql.sock）可在

/etc/my.cnf修改

可以在维护时使用，临时禁用远程连接

在网站https://downloads.mariadb.org/mariadb/repositories/#mirror=tuna&distro=CentOS&distro_release=centos7-amd64–centos7&version=10.2中复制yum配置路径

[root@CENTOS7 ~]# vim /etc/yum.repos.d/base.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.2/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=1

通用二进制格式安装过程

二进制格式安装过程

(1) 准备用户

groupadd -r -g 306 mysql

useradd -r -g 306 -u 306 –m –d /app/data mysql

(2) 准备数据目录

以/app/data为例,建议使用逻辑卷

chown mysql:mysql /app/data

(3) 准备二进制程序

tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local

cd /usr/local;ln -sv mariadb-VERSION mysql

chown -R root:mysql /usr/local/mysql/

通用二进制格式安装过程

 (4) 准备配置文件

mkdir /etc/mysql/

cp support-files/my-large.cnf /etc/mysql/my.cnf

[mysqld]中添加三个选项：

datadir = /app/data

innodb_file_per_table = on

skip_name_resolve = on 禁止主机名解析，建议使用

通用二进制格式安装过程

 (5)创建数据库文件

cd /usr/local/mysql/

./scripts/mysql_install_db –datadir=/app/data –user=mysql

 (6)准备日志文件

touch /var/log/mysqld.log

chown mysqld /var/log/mysqld.log

 (7)准备服务脚本，并启动服务

cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

chkconfig –add mysqld

service mysqld start

 (8)安全初始化

/user/local/mysql/bin/mysql_secure_installation

在centos7上用二进制安装10.2.15版本的mariadb

1.检查环境

iptables

selinux

有没有安装相关的mariadb的别的版本

用户是否创建

下载二进制包

rz 将二进制包导入/root/下

创建mysql账号。

[root@CENTOS7 ~]# useradd -r -d /data/mysqldb -s /sbin/nologin mysql

[root@CENTOS7 ~]# getent passwd mysql

mysql:x:991:986::/data/mysqldb:/sbin/nologin

[root@CENTOS7 ~]# id mysql

uid=991(mysql) gid=986(mysql) groups=986(mysql)

4.创建安装路径

tar -xvf mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local/

[root@CENTOS7 local]# ls

bin etc games include lib lib64 libexec mariadb-10.2.15-linux-x86_64 sbin share src

[root@CENTOS7 local]# ln -s mariadb-10.2.15-linux-x86_64/ mysql

[root@CENTOS7 local]# ll

total 0

drwxr-xr-x. 2 root root 6 Nov 5 2016 bin

drwxr-xr-x. 2 root root 6 Nov 5 2016 etc

drwxr-xr-x. 2 root root 6 Nov 5 2016 games

drwxr-xr-x. 2 root root 6 Nov 5 2016 include

drwxr-xr-x. 2 root root 6 Nov 5 2016 lib

drwxr-xr-x. 2 root root 6 Nov 5 2016 lib64

drwxr-xr-x. 2 root root 6 Nov 5 2016 libexec

drwxr-xr-x 12 root root 290 Jun 5 16:13 mariadb-10.2.15-linux-x86_64

lrwxrwxrwx 1 root root 29 Jun 5 16:15 mysql -> mariadb-10.2.15-linux-x86_64/

[root@CENTOS7 local]# ll mysql/ 其内的文件所属组所有者有问题

total 176

drwxr-xr-x 2 root root 4096 Jun 5 16:13 bin

-rw-r–r– 1 500 500 17987 May 17 05:31 COPYING

-rw-r–r– 1 500 500 86263 May 17 05:31 COPYING.thirdparty

-rw-r–r– 1 500 500 2268 May 17 05:31 CREDITS

drwxr-xr-x 3 root root 18 Jun 5 16:13 data

-rw-r–r– 1 500 500 8245 May 17 05:31 EXCEPTIONS-CLIENT

drwxr-xr-x 3 root root 19 Jun 5 16:12 include

-rw-r–r– 1 500 500 8694 May 17 05:31 INSTALL-BINARY

drwxr-xr-x 3 root root 280 Jun 5 16:12 lib

drwxr-xr-x 4 root root 30 Jun 5 16:13 man

drwxr-xr-x 11 root root 4096 Jun 5 16:13 mysql-test

-rw-r–r– 1 500 500 2374 May 17 05:31 README.md

-rw-r–r– 1 500 500 19510 May 17 05:31 README-wsrep

drwxr-xr-x 2 root root 30 Jun 5 16:13 scripts

drwxr-xr-x 32 root root 4096 Jun 5 16:13 share

drwxr-xr-x 4 root root 4096 Jun 5 16:13 sql-bench

drwxr-xr-x 3 root root 275 Jun 5 16:13 support-files

[root@CENTOS7 local]# chown -R root: mysql/

[root@CENTOS7 local]# ll

total 0

drwxr-xr-x. 2 root root 6 Nov 5 2016 bin

drwxr-xr-x. 2 root root 6 Nov 5 2016 etc

drwxr-xr-x. 2 root root 6 Nov 5 2016 games

drwxr-xr-x. 2 root root 6 Nov 5 2016 include

drwxr-xr-x. 2 root root 6 Nov 5 2016 lib

drwxr-xr-x. 2 root root 6 Nov 5 2016 lib64

drwxr-xr-x. 2 root root 6 Nov 5 2016 libexec

drwxr-xr-x 12 root root 290 Jun 5 16:13 mariadb-10.2.15-linux-x86_64

lrwxrwxrwx 1 root root 29 Jun 5 16:15 mysql -> mariadb-10.2.15-linux-x86_64/

drwxr-xr-x. 2 root root 6 Nov 5 2016 sbin

drwxr-xr-x. 5 root root 49 Mar 27 17:54 share

drwxr-xr-x. 2 root root 6 Nov 5 2016 src

[root@CENTOS7 bin]# ls /usr/local/mysql/bin

aria_chk myisamchk mysql_client_test mysql_embedded mysqltest wsrep_sst_common

aria_dump_log myisam_ftdump mysql_client_test_embedded mysql_find_rows mysqltest_embedded wsrep_sst_mariabackup

aria_ftdump myisamlog mysql_config mysql_fix_extensions mysql_tzinfo_to_sql wsrep_sst_mysqldump

aria_pack myisampack mysql_convert_table_format mysqlhotcopy mysql_upgrade wsrep_sst_rsync

aria_read_log my_print_defaults mysqld mysqlimport mysql_waitpid wsrep_sst_xtrabackup

innochecksum mysql mysqld_multi mysql_plugin mytop wsrep_sst_xtrabackup-v2

mariabackup mysqlaccess mysqld_safe mysql_secure_installation perror

mariadb_config mysqladmin mysqld_safe_helper mysql_setpermission replace

mbstream mysqlbinlog mysqldump mysqlshow resolveip

msql2mysql mysqlcheck mysqldumpslow mysqlslap resolve_stack_dump

[root@CENTOS7 bin]# echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh

[root@CENTOS7 bin]# . /etc/profile.d/mysql.sh

[root@CENTOS7 bin]# echo $PATH

/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

添加一个新的硬盘划分成逻辑卷用于存放用户数据

[root@CENTOS7 bin]# echo ” – – – ” > /sys/class/scsi_host/host2/scan

[root@CENTOS7 bin]# pvcreate /dev/sdb

Physical volume “/dev/sdb” successfully created.

[root@CENTOS7 bin]# vgcreate vg0 /dev/sdb

Volume group “vg0” successfully created

[root@CENTOS7 bin]# lvcreate -n lv_mysql -l 100%FREE vg0

Logical volume “lv_mysql” created.

[root@CENTOS7 bin]# lvs

LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert

lv_mysql vg0 -wi-a—– <200.00g

[root@CENTOS7 bin]# mkfs.xfs /dev/vg0/lv_mysql

meta-data=/dev/vg0/lv_mysql isize=512 agcount=4, agsize=13106944 blks

= sectsz=512 attr=2, projid32bit=1

= crc=1 finobt=0, sparse=0

data = bsize=4096 blocks=52427776, imaxpct=25

= sunit=0 swidth=0 blks

naming =version 2 bsize=4096 ascii-ci=0 ftype=1

log =internal log bsize=4096 blocks=25599, version=2

= sectsz=512 sunit=0 blks, lazy-count=1

realtime =none extsz=4096 blocks=0, rtextents=0

[root@CENTOS7 bin]# blkid

/dev/sdb: UUID=”mEFJk7-R0yE-lVHd-tlcc-uMGX-f0K6-Nh4BNj” TYPE=”LVM2_member”

[root@CENTOS7 ~]# vim /etc/fstab

UUID=758a4a10-ea77-4b17-8126-14914075c86c /app xfs defaults 0 0

[root@CENTOS7 ~]# mount -a

[root@CENTOS7 ~]# df -h

/dev/mapper/vg0-lv_mysql 200G 33M 200G 1% /app

[root@CENTOS7 ~]# mkdir /app/mysqldb

[root@CENTOS7 ~]# ll -d /app/mysqldb

drwxr-xr-x 2 root root 6 Jun 5 16:40 /app/mysqldb

[root@CENTOS7 ~]# chown mysql.mysql /app/mysqldb

[root@CENTOS7 ~]# ll -d /app/mysqldb

drwxr-xr-x 2 mysql mysql 6 Jun 5 16:40 /app/mysqldb

[root@CENTOS7 ~]# chmod 770 /app/mysqldb

[root@CENTOS7 ~]# ll -d /app/mysqldb

drwxrwx— 2 mysql mysql 6 Jun 5 16:40 /app/mysqldb

[root@CENTOS7 ~]# cd /usr/local/mysql

[root@CENTOS7 mysql]# ls

bin COPYING.thirdparty data include lib mysql-test README-wsrep share support-files

COPYING CREDITS EXCEPTIONS-CLIENT INSTALL-BINARY man README.md scripts sql-bench

[root@CENTOS7 scripts]# ls

mysql_install_db

这个程序必须在这个目录下执行

[root@CENTOS7 mysql]# pwd

/usr/local/mysql

执行这个脚本生成用户数据库基本文件

[root@CENTOS7 mysql]# scripts/mysql_install_db –datadir=/app/mysqldb –user=mysql

[root@CENTOS7 mysql]# ll /app/mysqldb

total 110620

-rw-rw—- 1 mysql mysql 16384 Jun 5 16:49 aria_log.00000001

-rw-rw—- 1 mysql mysql 52 Jun 5 16:49 aria_log_control

-rw-rw—- 1 mysql mysql 938 Jun 5 16:49 ib_buffer_pool

-rw-rw—- 1 mysql mysql 12582912 Jun 5 16:49 ibdata1

-rw-rw—- 1 mysql mysql 50331648 Jun 5 16:49 ib_logfile0

-rw-rw—- 1 mysql mysql 50331648 Jun 5 16:49 ib_logfile1

drwx—— 2 mysql root 4096 Jun 5 16:49 mysql

drwx—— 2 mysql mysql 20 Jun 5 16:49 performance_schema

drwx—— 2 mysql root 6 Jun 5 16:49 test

配置配置文件

[root@CENTOS7 mysql]# cd support-files/

[root@CENTOS7 support-files]# ls

binary-configure my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate policy wsrep_notify

magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server wsrep.cnf

[root@CENTOS7 support-files]# cp /etc/my.cnf{,.bak}

[root@CENTOS7 support-files]# cp my-huge.cnf /etc/my.cnf

[root@CENTOS7 support-files]# vim /etc/my.cnf

datadir =/app/mysqldb

[root@CENTOS7 support-files]# cp mysql.server /etc/init.d/mysqld

[root@CENTOS7 support-files]# chkconfig –list

[root@CENTOS7 support-files]# chkconfig –add mysqld

[root@CENTOS7 support-files]# chkconfig –list

[root@CENTOS7 support-files]# service mysqld start

Starting mysqld (via systemctl): [ OK ]

[root@CENTOS7 ~]# /usr/local/mysql/bin/mysqld –print-defaults

/usr/local/mysql/bin/mysqld would have been started with the following arguments:

–port=3306 –datadir=/app/mysqldb –socket=/tmp/mysql.sock –skip-external-locking –key_buffer_size=384M –max_allowed_packet=1M –table_open_cache=512 –sort_buffer_size=2M –read_buffer_size=2M –read_rnd_buffer_size=8M –myisam_sort_buffer_size=64M –thread_cache_size=8 –query_cache_size=32M –thread_concurrency=8 –log-bin=mysql-bin –server-id=1

实验：centos7.4二进制安装mariadb-10.2.15-linux-x86_64.tar.gz

1 检查环境

iptables

selinux

mariadb-server

2 下载二进制包

3 useradd -r -d /data/mysqldb -s /sbin/nologin mysql

4 tar xvf mariadb-10.2.15-linux-x86_64.tar.gz -C /usr/local/

cd /usr/local

ln -s mariadb-10.2.15-linux-x86_64/ mysql

chown -R root:root mysql/

5 echo PATH=/usr/local/mysql/bin:$PATH > /etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

6 mkdir -pv /data/mysqldb

chown mysql.mysql /data/mysqldb

chmod 770 /data/mysqldb

cd /usr/local/mysql

scripts/mysql_install_db –datadir=/data/mysqldb –user=mysql

cd /usr/local/mysql

cp support-files/my-huge.cnf /etc/my.cnf

vim /etc/my.cnf

[mysqld]

datadir=/data/mysqldb 加此行

cd /usr/local/mysql

cp support-files/mysql.server /etc/init.d/mysqld

chkconfig –add mysqld

service mysqld start

10 安全加固

mysql_secure_installation

源码编译安装mariadb

 安装包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-

devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-

devel ncurses-devel libxml2-devel

 做准备用户和数据目录

mkdir /data

useradd –r –s /bin/false –m –d /data/mysqldb/ mysql

tar xvf mariadb-10.2.12.tar.gz

 cmake 编译安装

cd mariadb-10.2.12/

编译选项:

https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html

源码编译安装mariadb

cmake . \

-DCMAKE_INSTALL_PREFIX=/app/mysql \

-DMYSQL_DATADIR=/data/mysqldb/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

-DWITH_DEBUG=0 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

make && make install

源码编译安装mariadb

 准备环境变量

echo ‘PATH=/app/mysql/bin:$PATH’ > /etc/profile.d/mysql.sh

. /etc/profile.d/mysql.sh

 生成数据库文件

cd /app/mysql/

scripts/mysql_install_db –datadir=/data/mysqldb/ –user=mysql

 准备配置文件

cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf

 准备启动脚本

cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld

 启动服务

chkconfig –add mysqld ;service mysqld start

安装完成后也可以修改数据存放路径

vim /etc/my.cnf.d/server.cnf

[mysqld]

datadir=/data/mysql

创建文件夹

mkdir /data/mysql -p

chown mysql.mysql /data/mysql

mysql_install_db –datadir=/data/mysql –user=mysql

sysctmctl restart mariadb

源码安装mariadb-server；

 安装包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-

devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-

devel ncurses-devel libxml2-devel

tar xvf mariadb-10.2.12.tar.gz

[root@CENTOS7 mariadb-10.3.7]# mkdir -pv /data/mysqldb

mkdir: created directory ‘/data/mysqldb’

[root@CENTOS7 mariadb-10.3.7]# chown mysql.mysql /data/mysqldb/

[root@CENTOS7 mariadb-10.3.7]# ll /data/mysqldb/

total 0

[root@CENTOS7 mariadb-10.3.7]# ll -d /data/mysqldb/

drwxr-xr-x. 2 mysql mysql 6 Jun 5 19:05 /data/mysqldb/

源码编译安装mariadb

cmake . \

-DCMAKE_INSTALL_PREFIX=/app/mysql \

-DMYSQL_DATADIR=/data/mysqldb/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_ARCHIVE_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITHOUT_MROONGA_STORAGE_ENGINE=1 \

-DWITH_DEBUG=0 \

-DWITH_READLINE=1 \

-DWITH_SSL=system \

-DWITH_ZLIB=system \

-DWITH_LIBWRAP=0 \

-DENABLED_LOCAL_INFILE=1 \

-DMYSQL_UNIX_ADDR=/app/mysql/mysql.sock \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

make && make install

[root@CENTOS7 mariadb-10.3.7]# ls /app/mysql/

bin COPYING.thirdparty data EXCEPTIONS-CLIENT INSTALL-BINARY man README.md scripts sql-bench

COPYING CREDITS docs include lib mysql-test README-wsrep share support-files

[root@CENTOS7 mariadb-10.3.7]# ls /app/mysql//bin

aria_chk myisamchk mysqlcheck mysql_find_rows mysqltest tokuftdump

aria_dump_log myisam_ftdump mysql_client_test mysql_fix_extensions mysql_tzinfo_to_sql tokuft_logprint

aria_ftdump myisamlog mysql_config mysqlhotcopy mysql_upgrade wsrep_sst_common

aria_pack myisampack mysql_convert_table_format mysqlimport mysql_waitpid wsrep_sst_mariabackup

aria_read_log my_print_defaults mysqld mysql_ldb mytop wsrep_sst_mysqldump

innochecksum myrocks_hotbackup mysqld_multi mysql_plugin perror wsrep_sst_rsync

mariabackup mysql mysqld_safe mysql_secure_installation replace wsrep_sst_xtrabackup

mariadb_config mysqlaccess mysqld_safe_helper mysql_setpermission resolveip wsrep_sst_xtrabackup-v2

mbstream mysqladmin mysqldump mysqlshow resolve_stack_dump

msql2mysql mysqlbinlog mysqldumpslow mysqlslap sst_dump

[root@CENTOS7 mariadb-10.3.7]# echo PATH=/app/mysql/bin/:$PATH > /etc/profile.d/mysql.sh

[root@CENTOS7 mariadb-10.3.7]# . /etc/profile.d/mysql.sh

[root@CENTOS7 mariadb-10.3.7]# cd /app/mysql/

[root@CENTOS7 mysql]# scripts/mysql_install_db –datadir=/data/mysqldb/ –user=mysql –basedir=/app/mysql

[root@CENTOS7 mysql]# ls /data/mysqldb/ -l

total 110620

-rw-rw—-. 1 mysql mysql 16384 Jun 5 21:07 aria_log.00000001

-rw-rw—-. 1 mysql mysql 52 Jun 5 21:07 aria_log_control

-rw-rw—-. 1 mysql mysql 972 Jun 5 21:07 ib_buffer_pool

-rw-rw—-. 1 mysql mysql 12582912 Jun 5 21:07 ibdata1

-rw-rw—-. 1 mysql mysql 50331648 Jun 5 21:07 ib_logfile0

-rw-rw—-. 1 mysql mysql 50331648 Jun 5 21:07 ib_logfile1

drwx——. 2 mysql root 4096 Jun 5 21:07 mysql

drwx——. 2 mysql mysql 20 Jun 5 21:07 performance_schema

drwx——. 2 mysql mysql 20 Jun 5 21:07 test

[root@CENTOS7 support-files]# cp my-huge.cnf /etc/my.cnf

vim /etc/my.cnf

[root@CENTOS7 support-files]# cp mysql.server /etc/init.d/mysqld

setfacl -R -m -u:mysql:rwx /app/mysql

service mysqld start

实现多实例；

/usr/bin/mysqld_multi这个就可以实现多实例，但是不能应用在不同的版本上。

mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv

[root@CENTOS7 ~]# tree /mysqldb/

/mysqldb/

├── 3306

│ ├── data

│ ├── etc

│ ├── log

│ ├── pid

│ └── socket

├── 3307

│ ├── data

│ ├── etc

│ ├── log

│ ├── pid

│ └── socket

└── 3308

├── data

├── etc

├── log

├── pid

└── socket

[root@CENTOS7 ~]# chown -R mysql.mysql /mysqldb/

mysql> show variables like ‘%basedir%’; 查找basedir的路径如果是二进制安装就不许指定basedur

+—————+——-+

| Variable_name | Value |

+—————+——-+

| basedir | /usr/ |

+—————+——-+

1 row in set (0.00 sec)

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3306/data –user=mysql –basedir=/uer/

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3306/data –user=mysql

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3307/data –user=mysql

[root@CENTOS7 ~]# mysql_install_db –datadir=/mysqldb/3308/data –user=mysql

[root@CENTOS7 ~]# cp /etc/my.cnf /mysqldb/3306//etc/

[root@CENTOS7 ~]# vim /mysqldb/3306/etc/my.cnf

[root@CENTOS7 ~]# cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnf

[root@CENTOS7 ~]# cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/my.cnf

[root@CENTOS7 ~]# cat /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnf /mysqldb/3308/etc/my.cnf

[mysqld]

port=3306

datadir=/mysqldb/3306/data

socket=/mysqldb/3306/socket/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/mysqldb/3306/log/mariadb.log

pid-file=/mysqldb/3306/pid/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

[mysqld]

port=3307

datadir=/mysqldb/3307/data

socket=/mysqldb/3307/socket/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/mysqldb/3307/log/mariadb.log

pid-file=/mysqldb/3307/pid/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

[mysqld]

port=3308

datadir=/mysqldb/3308/data

socket=/mysqldb/3308/socket/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/mysqldb/3308/log/mariadb.log

pid-file=/mysqldb/3308/pid/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

需要启动脚本

导入预先准备好的启动脚本

[root@CENTOS7 ~]# vim mysqld

#!/bin/bash

port=3306

mysql_user=”root”

mysql_pwd=””

cmd_path=”/usr/bin”

mysql_basedir=”/mysqldb”

mysql_sock=”${mysql_basedir}/${port}/socket/mysql.sock”

function_start_mysql()

{

if [ ! -e “$mysql_sock” ];then

printf “Starting MySQL…\n”

${cmd_path}/mysqld_safe –defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &

else

printf “MySQL is running…\n”

exit

}

function_stop_mysql()

{

if [ ! -e “$mysql_sock” ];then

printf “MySQL is stopped…\n”

exit

else

printf “Stoping MySQL…\n”

${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown

}

function_restart_mysql()

{

printf “Restarting MySQL…\n”

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

printf “Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n”

esac

[root@CENTOS7 ~]# cp mysqld /mysqldb/3306

[root@CENTOS7 ~]# cp mysqld /mysqldb/3307

[root@CENTOS7 ~]# cp mysqld /mysqldb/3308

[root@CENTOS7 ~]# vim /mysqldb/3308/mysqld

#!/bin/bash

port=3308

[root@CENTOS7 ~]# vim /mysqldb/3307/mysqld

#!/bin/bash

port=3307

[root@CENTOS7 ~]# chmod /mysqldb/3308/

data/ etc/ log/ mysqld pid/ socket/

[root@CENTOS7 ~]# chmod +x /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod +x /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3308/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3307/mysqld

[root@CENTOS7 ~]# chmod 700 /mysqldb/3306/mysqld

[root@CENTOS7 ~]# chmod +x /mysqldb/3307/mysqld

[root@CENTOS7 ~]# chmod +x /mysqldb/3306/mysqld

[root@CENTOS7 ~]# /mysqldb//3308/mysqld start

Starting MySQL…

[root@CENTOS7 ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 50 *:3308

[root@CENTOS7 ~]# /mysqldb//3307/mysqld start

Starting MySQL…

[root@CENTOS7 ~]# /mysqldb//3306/mysqld start

Starting MySQL…

[root@CENTOS7 ~]# ss -ntl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 50 *:3307 *:*

LISTEN 0 50 *:3308 *:*

LISTEN 0 50 *:3306 *:*

[root@CENTOS7 ~]# mysql -S /mysqldb/3308/socket/mysql.sock

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 1

Server version: 5.5.56-MariaDB MariaDB Server

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

MariaDB [(none)]>

MariaDB [(none)]> show variables like ‘%potr%’;

Empty set (0.00 sec)

MariaDB [(none)]> show variables like ‘%port%’;

+————————————-+——-+

| Variable_name | Value |

+————————————-+——-+

| extra_port | 0 |

| innodb_import_table_from_xtrabackup | 0 |

| innodb_support_xa | ON |

| large_files_support | ON |

| port | 3308 |

| progress_report_time | 5 |

| report_host | |

| report_password | |

| report_port | 3308 |

| report_user | |

+————————————-+——-+

10 rows in set (0.00 sec)

[root@CENTOS7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

MariaDB [(none)]> show variables like ‘%port%’;

+————————————-+——-+

| Variable_name | Value |

+————————————-+——-+

| extra_port | 0 |

| innodb_import_table_from_xtrabackup | 0 |

| innodb_support_xa | ON |

| large_files_support | ON |

| port | 3307 |

| progress_report_time | 5 |

| report_host | |

| report_password | |

| report_port | 3307 |

| report_user | |

+————————————-+——-+

MariaDB [(none)]> create database db3307;

Query OK, 1 row affected (0.00 sec)

[root@CENTOS7 ~]# ls /mysqldb//3307/data/

aria_log.00000001 aria_log_control db3307 ibdata1 ib_logfile0 ib_logfile1 mysql performance_schema test

实验：centos7.4 yum 安装包的实现多实例

1 yum install mariadb-server

2 mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv

chown -R mysql.mysql /mysqldb/

3 mysql_install_db –datadir=/mysqldb/3306/data –user=mysql

mysql_install_db –datadir=/mysqldb/3307/data –user=mysql

mysql_install_db –datadir=/mysqldb/3308/data –user=mysql

cp /etc/my.cnf /mysqldb/3306/etc/

vim /mysqldb/3306/etc/my.cnf

[mysqld]

port=3306

datadir=/mysqldb/3306/data

socket=/mysqldb/3306/socket/mysql.sock

[mysqld_safe]

log-error=/mysqldb/3306/log/mariadb.log

pid-file=/mysqldb/3306/pid/mariadb.pid

#!includedir /etc/my.cnf.d

cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnf

cp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/my.cnf

vim /mysqldb/3308/etc/my.cnf

vim /mysqldb/3307/etc/my.cnf

cp mysqld /mysqldb/3306/

cp mysqld /mysqldb/3307/

cp mysqld /mysqldb/3308/

chmod 700 /mysqldb/3308/mysqld

chmod 700 /mysqldb/3307/mysqld

chmod 700 /mysqldb/3306/mysqld

vim /mysqldb/3306/mysqld

vim /mysqldb/3307/mysqld

vim /mysqldb/3308/mysqld

6 /mysqldb/3308/mysqld start

/mysqldb/3307/mysqld start

/mysqldb/3306/mysqld start

mysql -S /mysqldb/3308/socket/mysql.sock

mysql -S /mysqldb/3307/socket/mysql.sock

mysql -S /mysqldb/3306/socket/mysql.sock

[root@CENTOS7 ~]# /mysqldb/3308/mysqld stop 停止程序

Stoping MySQL…

Enter password:

[root@CENTOS7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

MariaDB [(none)]> select user,host,password from mysql.user;

+——+———————+———-+

| user | host | password |

+——+———————+———-+

| root | localhost | |

| root | centos7.localdomain | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | centos7.localdomain | |

+——+———————+———-+

6 rows in set (0.00 sec)

MariaDB [(none)]> update mysql.user set password=password(“centos”) where user=’root’;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

MariaDB [(none)]> select user,host,password from mysql.user;

+——+———————+——————————————-+

| user | host | password |

+——+———————+——————————————-+

| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | centos7.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED |

因为脚本文件没有修改修改完成后配置文件没有生效

[root@CENTOS7 ~]# /mysqldb/3307/mysqld stop

Stoping MySQL…

Enter password:

/usr/bin/mysqladmin: connect to server at ‘localhost’ failed

error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’

使权限生效

[root@CENTOS7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@CENTOS7 ~]# /mysqldb/3307/mysqld stop

Stoping MySQL…

[root@CENTOS7 ~]# ss -ntl

关系型数据库的常见组件

数据库：database

表：table

行：row

列：column

索引：index

视图：view

用户：user

权限：privilege

存储过程：procedure，无返回值

存储函数：function，有返回值

触发器：trigger

事件调度器：event scheduler，任务计划

SQL语言的兴起与语法标准

20世纪70年代，IBM开发出SQL，用于DB2

1981年，IBM推出SQL/DS数据库

业内标准微软和Sybase的T-SQL，Oracle的PL/SQL

SQL作为关系型数据库所使用的标准语言，最初是基于IBM的实现在1986年被

批准的。1987年，“国际标准化组织(ISO)”把ANSI(美国国家标准化组织)

SQL作为国际标准。

SQL：ANSI SQL

SQL-86, SQL-89, SQL-92, SQL-99, SQL-03

SQL语言规范

在数据库系统中，SQL语句不区分大小写(建议用大写)

但字符串常量区分大小写

SQL语句可单行或多行书写，以“;”结尾

关键词不能跨多行或简写

用空格和缩进来提高语句的可读性

子句通常位于独立行，便于编辑，提高可读性

注释：

SQL标准：

/*注释内容*/ 多行注释

aaa

bbb

cccc

select user，host，/*注释*/password，from user；

— 注释内容单行注释，注意有空格单行注释要么自己一行要么写在命令的后面。

MySQL注释：

数据库对象

数据库的组件(对象)：

数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等

命名规则：

必须以字母开头

可包括数字和三个特殊字符（# _ $）

不要使用MySQL的保留字列如select

同一database(Schema)下的对象不能同名（即使是不同类型的也不能同名）

在同一个实例（instance）

SQL语句分类

SQL语句分类：

DDL: Data Defination Language 数据定义语言

CREATE, DROP, ALTER （增删改）表的结构，数据库的名称等，修改数据库中的对象

DML: Data Manipulation Language数据的操作语言

INSERT, DELETE, UPDATE（增删改）对数据表增删改，修改数据的

DCL：Data Control Language数据的控制语言，主要针对权限

GRANT, REVOKE（授权，撤销）

DQL：Data Query Language数据的查询语言

SELECT

SQL语句构成

SQL语句构成：

Keyword组成clause，关键字组成子句

多条clause组成语句

示例：

SELECT * SELECT子句

FROM products FROM子句

WHERE price>400 WHERE子句

说明：此为一组SQL语句，由三个子句构成，SELECT,FROM和WHERE是关键字

数据库操作

 创建数据库：

CREATE DATABASE|SCHEMA [IF NOT EXISTS] ‘DB_NAME’;

CHARACTER SET ‘character set name’ 字符集

COLLATE ‘collate name’ 排序规则

 删除数据库

DROP DATABASE|SCHEMA [IF EXISTS] ‘DB_NAME’;

 查看支持所有字符集：SHOW CHARACTER SET;

 查看支持所有排序规则：SHOW COLLATION;

 获取命令使用帮助：

mysql> HELP KEYWORD;

 查看数据库列表：

mysql> SHOW DATABASES;

[root@CENTOS7 ~]# systemctl start mariadb

[root@CENTOS7 ~]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.56-MariaDB MariaDB Server

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

MariaDB [(none)]>

MariaDB [(none)]> show databases; 查看数据库列表

+——————–+

| Database |

+——————–+

| information_schema |

| mysql |

| performance_schema |

| test |

+——————–+

4 rows in set (0.00 sec)

MariaDB [(none)]> \! ls /var/lib/mysql

aria_log.00000001 aria_log_control ibdata1 ib_logfile0 ib_logfile1 mysql mysql.sock performance_schema test

在硬盘上只能看到performance_schema test mysql 因为 information_schema 是一个虚拟的放在内存中。

MariaDB [(none)]> create database db1; 创建一个数据库（名字最好是代表这个数据库是存什么的）

MariaDB [(none)]> \! cat /var/lib/mysql/db1/db.opt 自己创建的数据库中存放的文件

default-character-set=latin1 默认的字符集是拉丁这个支持ABC如果想支持别的语言最好用utf-8

default-collation=latin1_swedish_ci 默认的排序方式是拉丁

MariaDB [(none)]> drop database db1; 删除数据库

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show character set; 查看系统支持的所有字符集

utf8 | UTF-8 Unicode | utf8_general_ci | 3 支持全世界的语言

MariaDB [(none)]> show collation;查看所有的排序规则

MariaDB [(none)]> help create; 查看create命令如何使用

MariaDB [(none)]> help create database;

http://dev.mysql.com/doc/refman/5.5/en/create-database.html 详细查询

MariaDB [(none)]> use db1 指定当前数据库是谁

Database changed

MariaDB [db1]>

MariaDB [db1]> show tables; 查看数据库中的表

Empty set (0.00 sec)

表

表：二维关系

设计表：遵循规范

定义：字段，索引

字段：字段名，字段数据类型，修饰符

约束，索引：应该创建在经常用作查询条件的字段上

创建表

创建表：CREATE TABLE

(1) 直接创建

(2) 通过查询现存表创建；新表会被直接插入查询而来的数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,…)] [table_options]

[partition_options] select_statement

(3) 通过复制现存的表的表结构创建，但不复制数据

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE

old_tbl_name | (LIKE old_tbl_name) }

注意：

 Storage Engine是指表类型，也即在表创建时指明其使用的存储引擎，同一库中不同

表可以使用不同的存储引擎

 同一个库中表建议要使用同一种存储引擎类型

MariaDB [db1]> help create table; 查询create table用法

共3中创建方法

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name当在脚本中运行时需要加IF NOT EXISTS

(create_definition,…)

[table_options]

[partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,…)]

[table_options]

[partition_options]

select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

{ LIKE old_tbl_name | (LIKE old_tbl_name) }

数据类型

数据类型：

 数据长什么样？

 数据需要多少空间来存放？

系统内置数据类型和用户定义数据类型

MySql支持多种列类型：

 数值类型

 日期/时间类型

 字符串(字符)类型

选择正确的数据类型对于获得高性能至关重要，三大原则：

 更小的通常更好，尽量使用可正确存储数据的最小数据类型

 简单就好，简单数据类型的操作通常需要更少的CPU周期

 尽量避免NULL，包含为NULL的列，对MySQL更难优化

数据类型

 1、整型

 tinyint(m) 1个字节范围(-128~127)

 smallint(m) 2个字节范围(-32768~32767)

 mediumint(m) 3个字节范围(-8388608~8388607)

 int(m) 4个字节范围(-2147483648~2147483647)

 bigint(m) 8个字节范围(+-9.22*10的18次方)

取值范围如果加了unsigned，则最大值翻倍，如tinyint unsigned的取值范围为(0~255)

int(m)里的m是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定

了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。对于存储

和计算来说，Int(1)和Int(20)是相同的

BOOL，BOOLEAN：布尔型，是TINYINT(1)的同义词。zero值被视为假。非zero值视

为真

数据类型

2、浮点型(float和double)，近似值

 float(m,d) 单精度浮点型 8位精度(4字节) m总个数，d小数位

 double(m,d) 双精度浮点型16位精度(8字节) m总个数，d小数位

 设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是

123.457，但总个数还以实际为准，即6位

数据类型

3、定点数

 在数据库中存放的是精确值,存为十进制

 decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位

 MySQL5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数

字）。例如，decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：小数点

前的数字用4个字节，小数点后的数字用4个字节，小数点本身占1个字节

 浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节

存储。double占用8个字节

 因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用

decimal——例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替

decimal

数据类型

4、字符串(char,varchar,_text)

 char(n) 固定长度，最多255个字符

 varchar(n)可变长度，最多65535个字符

 tinytext 可变长度，最多255个字符

 text 可变长度，最多65535个字符

 mediumtext 可变长度，最多2的24次方-1个字符

 longtext 可变长度，最多2的32次方-1个字符

 BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

 VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

 内建类型：ENUM枚举, SET集合

数据类型

 char和varchar：

• 1.char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以

char类型存储的字符串末尾不能有空格，varchar不限于此。

• 2.char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入

的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节。

• 3.char类型的字符串检索速度要比varchar类型的快

 varchar和text：

• 1.varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节

（n< n>255)，text是实际字符数+2个字节。

• 2.text类型不能有默认值

• 3.varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于

text

数据类型

 5.二进制数据：BLOB

• BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，

而Blob是以二进制方式存储，不分大小写

• BLOB存储的数据只能整体读出

• TEXT可以指定字符集，BLOB不用指定字符集

 6.日期时间类型

• date 日期 ‘2008-12-2’

• time 时间 ’12:25:36′

• datetime 日期时间 ‘2008-12-2 22:06:44’

• timestamp 自动存储记录修改时间，好处假设银行账户中存放1亿，如果有时间戳那么一但挪用这笔钱，就会导致时间戳不一样，自动修改

• YEAR(2), YEAR(4)：年份

timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字

段可以存放这条记录最后被修改的时间

修饰符

所有类型：

• NULL 数据列可包含NULL值

• NOT NULL 数据列不允许包含NULL值

• DEFAULT 默认值

• PRIMARY KEY 主键

• UNIQUE KEY 唯一键

• CHARACTER SET name 指定一个字符集

数值型

• AUTO_INCREMENT 自动递增，适用于整数类型

• UNSIGNED 无符号

salary decimal(5,2)表示共5位精确到小数点后两位

char是比较占空间的，但是在内存中存放是固定长度的，查询更快

创建一个students表

MariaDB [db1]> create table students ( id tinyint unsigned not null primary key,name char(10) not null,phone char(11),sex char(1) );

MariaDB [db1]> show tables;

+—————+

| Tables_in_db1 |

+—————+

| students |

+—————+

1 row in set (0.00 sec)

MariaDB [db1]> desc students; 查看这个表的定义

+——-+———————+——+—–+———+——-+

+——-+———————+——+—–+———+——-+

| id | tinyint(3) unsigned | NO | PRI | NULL | |

| sex | char(1) | YES | | NULL | |

+——-+———————+——+—–+———+——-+

MariaDB [db1]> show table status like ‘students’\G

*************************** 1. row ***************************

Name: students 表明

Engine: InnoDB 存储引擎在centos6上适应的是myisam

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: NULL

Create_time: 2018-06-06 20:41:40

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

创建表

 CREATE TABLE [IF NOT EXISTS] ‘tbl_name’ (col1 type1 修饰符, col2 type2 修饰

符, …)

 字段信息

• col type1

• PRIMARY KEY(col1,…)

• INDEX(col1, …)

• UNIQUE KEY(col1, …)

 表选项：

• ENGINE [=] engine_name

SHOW ENGINES;查看支持的engine类型

• ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

 获取帮助：mysql> HELP CREATE TABLE;

表操作

查看所有的引擎：SHOW ENGINES

查看表：SHOW TABLES [FROM db_name]

查看表结构：DESC [db_name.]tb_name

删除表：DROP TABLE [IF EXISTS] tb_name

查看表创建命令：SHOW CREATE TABLE tbl_name

查看表状态：SHOW TABLE STATUS LIKE ‘tbl_name’

查看库中所有表状态： SHOW TABLE STATUS FROM db_name

MariaDB [db1]> show table status from db1\G 查看数据库中有几个表

*************************** 1. row ***************************

Name: students

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: NULL

Create_time: 2018-06-06 20:41:40

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

创建emp表格，定义其相关属性

MariaDB [db1]> create table emp ( id int unsigned primary key auto_increment, name varchar(30) not null, sex char(1) default ‘m’, address varchar(100) ) engine=innodb charset=utf8;

Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> desc emp;

+———+——————+——+—–+———+—————-+

+———+——————+——+—–+———+—————-+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| sex | char(1) | YES | | m | |

+———+——————+——+—–+———+—————-+

4 rows in set (0.01 sec)

MariaDB [db1]> show table status like ’emp’\G

*************************** 1. row ***************************

Name: emp

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: 1

Create_time: 2018-06-06 21:06:40

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

MariaDB [db1]> show table status from db1\G

*************************** 1. row ***************************

Name: emp

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: 1

Create_time: 2018-06-06 21:06:40

Update_time: NULL

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

*************************** 2. row ***************************

Name: students

Engine: InnoDB

Version: 10

Row_format: Compact

Rows: 0

Avg_row_length: 0

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 10485760

Auto_increment: NULL

Create_time: 2018-06-06 20:41:40

Update_time: NULL

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

2 rows in set (0.00 sec)

MariaDB [db1]> show create table emp\G 查看一张旧的表示如何定义的

*************************** 1. row ***************************

Table: emp

Create Table: CREATE TABLE `emp` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`sex` char(1) DEFAULT ‘m’,

`address` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

第二种方法创建表

利用以前的表创建新的表

MariaDB [db1]> create table user select user,host,password from mysql.user;

MariaDB [db1]> desc user;

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

不光数据表的结构复制了，数据也复制了

MariaDB [db1]> select * from user

-> ;

+——+———————+———-+

| user | host | password |

+——+———————+———-+

| root | localhost | |

| root | centos7.localdomain | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | centos7.localdomain | |

+——+———————+———-+

只要表的数据结构，不要表的数据内容

MariaDB [db1]> create table user2 select user,host,password from mysql.user where 1 = 0 ;

MariaDB [db1]> desc user2;

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

MariaDB [db1]> select * from user2;

Empty set (0.00 sec)

第三种方法创建表

MariaDB [db1]> create table user3 like mysql.user;

MariaDB [db1]> desc user3; 查看表的数据结构，这种方法创建的表是把旧的表的数据结构完全复制，但是不复制表的数据内容

MariaDB [db1]> select * from user3;

Empty set (0.00 sec)

MariaDB [db1]> show columns from user2; 等价于desc user2；

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

表操作，修改表的数据结构，但是不建议修改

 DROP TABLE [IF EXISTS] ‘tbl_name’;

 ALTER TABLE ‘tbl_name’

字段：

添加字段：add

ADD col1 data_type [FIRST|AFTER col_name]

删除字段：drop

修改字段：

alter（默认值）, change（字段名）, modify（字段属性）

索引:

添加索引：add index

删除索引: drop index

表选项

修改:

 查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;

 查看帮助：Help ALTER TABLE

数据存放在表中，表中的字段的顺序不是特别重要，因为在查询的时候是可以自己指定的，记录的顺序也不是特别重要

当想修改name的类型时，将varchar（30）增大问题不大，但是一旦变小就有可能使以前存的数据遭到破坏

MariaDB [db1]> desc emp;

MariaDB [db1]> help drop table

Name: ‘DROP TABLE’

Description:

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] …

[RESTRICT | CASCADE] 级联删除，如果俩个表之前存在1依赖2，那么删除表2也会删除表1

student teacher

id name teacherid id name

修改表示例

ALTER TABLE students RENAME s1;

ALTER TABLE s1 ADD phone varchar(11) AFTER name;

ALTER TABLE s1 MODIFY phone int;

ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);

ALTER TABLE s1 DROP COLUMN mobile;

Help ALTER TABLE 查看帮助

修改表示例

ALTER TABLE students ADD gender ENUM(‘m’,’f’)

ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL

PRIMARY KEY;

ALTER TABLE students ADD UNIQUE KEY(name); 唯一键

ALTER TABLE students ADD INDEX(age);

DESC students;

SHOW INDEXES FROM students;

ALTER TABLE students DROP age;

set 多选，其中的字符可以选一个两个及多个

enmu 多选一最多65535个字符中选取一个

创建复合主键

MariaDB [db1]> create table t1 ( name char(30),city char(30),sex char(1),primary key(name,city) );

MariaDB [db1]> desc t1;

+——-+———-+——+—–+———+——-+

+——-+———-+——+—–+———+——-+

| name | char(30) | NO | PRI | | |

| city | char(30) | NO | PRI | | |

| sex | char(1) | YES | | NULL | |

+——-+———-+——+—–+———+——-+

DML语句，表数据的操作

 DML:

 INSERT, DELETE, UPDATE, SELECT

 INSERT：

一次插入一行或多行数据

语法

 INSERT [L OW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

{VALUES | VALUE} ({expr | DEFAULT},…),(…),…

[ ON DUPLICATE KEY UPDATE 如果重复更新之

col_name=expr

[, col_name=expr] … ]

简化写法：

INSERT tbl_name [(col1,…)] VALUES (val1,…), (val21,…)

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

SELECT …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

MariaDB [db1]> help insert

Name: ‘INSERT’

Description:

Syntax:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)] 如果这里不写字段的名字，那么久必须按照表的数据结构按顺序每个字段都赋值。当写字段名时后面的value就必须与字段对应，没有写的字段则是空或默认值

{VALUES | VALUE} ({expr | DEFAULT},…),(…),…

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

Or:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

SET col_name={expr | DEFAULT}, …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

Or:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name [(col_name,…)]

SELECT …

[ ON DUPLICATE KEY UPDATE

col_name=expr

[, col_name=expr] … ]

在学生表中加入一行数据

MariaDB [db1]> insert students values(1,’bai’,’10086′,’m’);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

+—-+——+——-+——+

带字段名后，values值必须与字段名一一对应

MariaDB [db1]> insert students (name,id) values (‘wang’,70);

MariaDB [db1]> select * from students;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

+—-+——+——-+——+

一次增加两条记录

MariaDB [db1]> insert students (id,name,sex) values (2,’liu’,’m’),(3,’lin’,’f’);

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [db1]> select * from students

-> ;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

MariaDB [db1]> insert students set id=4,name=’zhao’ ;

MariaDB [db1]> select * from students

-> ;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

MariaDB [db1]> insert emp (name,address) select user,host from user;

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

MariaDB [db1]> select * from emp;

+—-+——+——+———————+

| id | name | sex | address |

+—-+——+——+———————+

| 1 | root | m | localhost |

| 2 | root | m | centos7.localdomain |

| 3 | root | m | 127.0.0.1 |

| 4 | root | m | ::1 |

| 5 | | m | localhost |

| 6 | | m | centos7.localdomain |

+—-+——+——+———————+

MariaDB [db1]> show create table emp

-> ;

+——-+—————————————————————————————————————————————————————————————————————————————————–+

| Table | Create Table |

| emp | CREATE TABLE `emp` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`sex` char(1) DEFAULT ‘m’,

`address` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |

MariaDB [db1]> desc emp;

+———+——————+——+—–+———+—————-+

+———+——————+——+—–+———+—————-+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| sex | char(1) | YES | | m | |

+———+——————+——+—–+———+—————-+

4 rows in set (0.00 sec)

MariaDB [db1]> desc user;

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

+———-+———-+——+—–+———+——-+

将旧表的信息直接加入新表，新旧表的字段要相互匹配，且新表要包容旧表（例如新表的char（）要大于旧表的char（））

DML语句

UPDATE：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference

SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] …

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段

限制条件：

WHERE

LIMIT

Mysql 选项：–safe-updates| –i-am-a-dummy|-U

MariaDB [db1]> select * from emp

-> ;

+—-+——+——+———————+

| id | name | sex | address |

+—-+——+——+———————+

| 1 | root | m | localhost |

| 2 | root | m | centos7.localdomain |

| 3 | root | m | 127.0.0.1 |

| 4 | root | m | ::1 |

| 5 | | m | localhost |

| 6 | | m | centos7.localdomain |

+—-+——+——+———————+

6 rows in set (0.00 sec)

修改第一行中的name和address

MariaDB [db1]> update emp set name=’admin’,address=’beijing’ where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> select * from emp;

+—-+——-+——+———————+

| id | name | sex | address |

+—-+——-+——+———————+

| 1 | admin | m | beijing |

| 2 | root | m | centos7.localdomain |

| 3 | root | m | 127.0.0.1 |

| 4 | root | m | ::1 |

| 5 | | m | localhost |

| 6 | | m | centos7.localdomain |

+—-+——-+——+———————+

6 rows in set (0.00 sec)

limit限定修改的行数

MariaDB [db1]> update emp set name=’adnim’,address=’beijing’ where name=’root’ limit 2;

MariaDB [db1]> select * from emp;

+—-+——-+——+———————+

| id | name | sex | address |

+—-+——-+——+———————+

| 1 | admin | m | beijing |

| 2 | adnim | m | beijing |

| 3 | adnim | m | beijing |

| 4 | root | m | ::1 |

| 5 | | m | localhost |

| 6 | | m | centos7.localdomain

DML语句

DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name

[WHERE where_condition]

[ORDER BY …]

[LIMIT row_count]

可先排序再指定删除的行数

注意：一定要有限制条件，否则将清空表中的所有数据

限制条件：

WHERE

LIMIT

TRUNCATE TABLE tbl_name; 清空表

生产中不会真的删除记录，都是在表的后面加上一个delete的标记位，标记位为1就相当于是删除了，这样就算误删除记录还在。

students

id name phone teacherid delete

1 wang 10086 1 1 认为删除

2 zhang 10010 1 0 没有删除

删除emp表中的第4行

MariaDB [db1]> delete from emp where id=4; 可以还原

MariaDB [db1]> select * from emp;

+—-+——-+——+———————+

| id | name | sex | address |

+—-+——-+——+———————+

| 1 | admin | m | beijing |

| 2 | adnim | m | beijing |

| 3 | adnim | m | beijing |

| 5 | | m | localhost |

| 6 | | m | centos7.localdomain |

直接删除表中的所有数据

MariaDB [db1]> truncate table emp; 无法恢复

Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> select * from emp;

Empty set (0.00 sec)

由于删除和更新表都是很危险的操作因此可以用下面选项来避免

Mysql 选项：–safe-updates| –i-am-a-dummy|-U

[root@CENTOS7 ~]# mysql –safe-updates 等价于mysql -U

MariaDB [db1]> update user set user=’admin’;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

MariaDB [db1]> delete from user ;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

可以写到配置文件中

[root@CENTOS7 ~]# vim /etc/my.cnf.d/mysql-clients.cnf

[mysql]

safe-updates

MariaDB [db1]> update user set user=’admin’ ;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

DQL语句

 SELECT

[ALL | DISTINCT | DISTINCTROW ]

[SQL_CACHE | SQL_NO_CACHE]

select_expr [, select_expr …]

[FROM table_references

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], … [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], …]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[FOR UPDATE | LOCK IN SHARE MODE]

SELECT

字段显示可以使用别名：

col1 AS alias1, col2 AS alias2, …

WHERE子句：指明过滤条件以实现“选择”的功能：

过滤条件：布尔型表达式

算术操作符：+, -, *, /, %

比较操作符：=, !=, <>（不等于）, <=, >, >=, <, <=

BETWEEN min_num AND max_num

IN (element1, element2, …)

IS NULL

IS NOT NULL

SELECT

LIKE:

%: 任意长度的任意字符

_：任意单个字符

RLIKE：正则表达式，索引失效，不建议使用

REGEXP：匹配字符串可用正则表达式书写模式，同上

逻辑操作符：

NOT

AND

XOR 异或

SELECT

GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算

avg(), max(), min(), count(), sum()

HAVING: 对分组聚合运算后的结果指定过滤条件

ORDER BY: 根据指定的字段对查询结果进行排序

升序：ASC

降序：DESC

LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制

对查询结果中的数据请求施加“锁”

FOR UPDATE: 写锁，独占或排它锁，只有一个读和写只能自己读写

LOCK IN SHARE MODE: 读锁，共享锁，同时多个读，只能自己写，可以多个读

避免多个用户同时修改一个数据库而带来问题

最简单的查询

MariaDB [db1]> select * from user;

+——+———————+———-+

| user | host | password |

+——+———————+———-+

| root | localhost | |

| root | centos7.localdomain | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

| | centos7.localdomain | |

MariaDB [db1]> select ‘1+2=’,1+2;

+——+—–+

| 1+2= | 1+2 |

+——+—–+

| 1+2= | 3 |

+——+—–+

1 row in set (0.00 sec)

MariaDB [db1]> select ‘1+2=’,1+2 from user; 显示的行数和user的行一样类似于awk

+——+—–+

| 1+2= | 1+2 |

+——+—–+

| 1+2= | 3 |

+——+—–+

MariaDB [db1]> select ‘number’,user,password from user;

+——–+——+———-+

| number | user | password |

+——–+——+———-+

| number | root | |

| number | | |

+——–+——+———-+

select语句查看的顺序是数据在磁盘上的存放位置，后插入的记录的磁盘存放位置不一定就在之前的记录的后面

精确查找

MariaDB [db1]> select * from user where host=’localhost’;

+——+———–+———-+

| user | host | password |

+——+———–+———-+

| root | localhost | |

| | localhost | |

+——+———–+———-+

where后面可以跟多个条件

MariaDB [db1]> select * from user where host=’localhost’and user=’root’;

+——+———–+———-+

| user | host | password |

+——+———–+———-+

| root | localhost | |

+——+———–+———-+

1 row in set (0.01 sec)

MariaDB [db1]> select * from user where host=’localhost’ or user=’root’;

+——+———————+———-+

| user | host | password | password不是空

+——+———————+———-+

| root | localhost | |

| root | centos7.localdomain | |

| root | 127.0.0.1 | |

| root | ::1 | |

| | localhost | |

+——+———————+———-+

5 rows in set (0.00 sec)

MariaDB [db1]> select * from students where sex is null

-> ;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

+—-+——+——-+——+

2 rows in set (0.00 sec)

MariaDB [db1]> select * from students where sex is not null;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

+—-+——+——-+——+

3 rows in set (0.00 sec)

MariaDB [db1]> insert students values(5,’wang’,’10000′,’m’);

MariaDB [db1]> select * from students where id>=2 and id<=5;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

| 5 | wang | 10000 | m |

+—-+——+——-+——+

MariaDB [db1]> select * from students where id between 2 and 5;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

| 5 | wang | 10000 | m |

in（’f’,’m’）包含f或者m的

MariaDB [db1]> select * from students where sex in (‘m’,’f’);

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

| 5 | wang | 10000 | m |

+—-+——+——-+——+

4 rows in set (0.00 sec)

MariaDB [db1]> select * from students where sex in (‘m’,’f’) or sex is null;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

| 5 | wang | 10000 | m |

字段名称加别名

MariaDB [db1]> select id as 编号,name as 姓名 from students where sex in (‘m’,’f’) or sex is null;

+——–+——–+

| 编号 | 姓名 |

+——–+——–+

| 1 | bai |

| 2 | liu |

| 3 | lin |

| 4 | zhao |

| 5 | wang |

| 70 | wang |

+——–+——–+

也可以给表起别名，students表别名是st，在多表中使用

MariaDB [db1]> select id as 编号,name as 姓名 from students as st where sex in (‘m’,’f’) or sex is null;

MariaDB [db1]> select st.id as 编号,st.name as 姓名 from students as st where sex in (‘m’,’f’) or sex is null;

+——–+——–+

| 编号 | 姓名 |

+——–+——–+

| 1 | bai |

| 2 | liu |

| 3 | lin |

| 4 | zhao |

| 5 | wang |

| 70 | wang |

+——–+——–+

6 rows in set (0.00 sec)

select 模糊查询

以w开头的

MariaDB [db1]> select * from students where name like ‘w%’ ; %相当于*

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 5 | wang | 10000 | m |

+—-+——+——-+——+

包含i的

MariaDB [db1]> select * from students where name like ‘%i%’ ;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

+—-+——+——-+——+

MariaDB [db1]> select * from students where name like ‘___’ ; _ 代表任意一个字符

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

正则表达式查询

MariaDB [db1]> select * from students where name rlike ‘^l’;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 2 | liu | NULL | m |

| 3 | lin | NULL | f |

+—-+——+——-+——+

MariaDB [db1]> select * from students where sex != ‘m’ ;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 3 | lin | NULL | f |

+—-+——+——-+——+

1 row in set (0.00 sec)

MariaDB [db1]> select * from students where not sex != ‘m’ ;

+—-+——+——-+——+

| id | name | phone | sex |

+—-+——+——-+——+

| 1 | bai | 10086 | m |

| 2 | liu | NULL | m |

| 5 | wang | 10000 | m |

+—-+——+——-+——+

在students表中添加一个score字段

MariaDB [db1]> alter table students add score tinyint after phone;

MariaDB [db1]> select * from students

-> ;

+—-+——+——-+——-+——+

+—-+——+——-+——-+——+

| 1 | bai | 10086 | NULL | m |

| 2 | liu | NULL | NULL | m |

| 3 | lin | NULL | NULL | f |

| 5 | wang | 10000 | NULL | m |

添加成绩

MariaDB [db1]> update students set score=90 where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> update students set score=80 where id=2;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> update students set score=80 where id=3;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> update students set score=88 where id=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> update students set score=77 where id=5;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [db1]> delete from students where sex is null ;

Query OK, 2 rows affected (0.00 sec)

MariaDB [db1]> insert students values(4,’zhang’,’110′,60,’f’);

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> select * from students;

+—-+——-+——-+——-+——+

+—-+——-+——-+——-+——+

| 1 | bai | 10086 | 90 | m |

| 2 | liu | NULL | 80 | m |

| 3 | lin | NULL | 80 | f |

| 4 | zhang | 110 | 60 | f |

| 5 | wang | 10000 | 77 | m |

+—-+——-+——-+——-+——+

5 rows in set (0.00 sec)

MariaDB [db1]> insert students values(6,’huang’,’110′,99,’m’);

按性别分组，统计性别有几组

MariaDB [db1]> select sex from students group by sex

-> ;

+——+

| sex |

+——+

| f |

| m |

分组后select后跟的值一般是统计汇总的平均值，总和，最大值，最小值等，或者是统计的字段（sex）

MariaDB [db1]> select sex,max(score) from students group by sex;

+——+————+

| sex | max(score) |

+——+————+

| f | 80 |

| m | 99 |

+——+————+

MariaDB [db1]> select sex,max(score) as 最好成绩 from students group by sex;

+——+————–+

| sex | 最好成绩 |

+——+————–+

| f | 80 |

| m | 99 |

+——+————–+

2 rows in set (0.00 sec)

MariaDB [db1]> select sex,avg(score) as 平均成绩 from students group by sex;

+——+————–+

| sex | 平均成绩 |

+——+————–+

| f | 70.0000 |

| m | 86.5000 |

+——+————–+

各个班的男生女生的平均成绩

MariaDB [db1]> select sex,avg(score) as 平均成绩 from students group by sex,class;

添加新的字段class

MariaDB [db1]> alter table students add class tinyint default 1 ;

id大于等于3的class修改成2

MariaDB [db1]> update students set class=2 where id>=3 ;

Query OK, 4 rows affected (0.00 sec)

Rows matched: 4 Changed: 4 Warnings: 0

MariaDB [db1]> select * from students;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 1 | bai | 10086 | 90 | m | 1 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 6 | huang | 110 | 99 | m | 2 |

MariaDB [db1]> insert students values(7,’aaa’,’100′,86,’f’,1);

MariaDB [db1]> select * from students;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 1 | bai | 10086 | 90 | m | 1 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 6 | huang | 110 | 99 | m | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

MariaDB [db1]> select class,sex,avg(score) as 平均成绩 from students group by class,sex ;

+——-+——+————–+

| class | sex | 平均成绩 |

+——-+——+————–+

| 1 | f | 86.0000 |

| 1 | m | 85.0000 |

| 2 | f | 70.0000 |

| 2 | m | 88.0000 |

+——-+——+————–+

MariaDB [db1]> select class,sex,count(id) as 各班人数 from students group by class,sex ;（id）也可以用（*）

+——-+——+————–+

| class | sex | 各班人数 |

+——-+——+————–+

| 1 | f | 1 |

| 1 | m | 2 |

| 2 | f | 2 |

| 2 | m | 2 |

MariaDB [db1]> select class,sex,avg(score) as 平均成绩 from students group by class,sex having 平均成绩>80;

+——-+——+————–+

| class | sex | 平均成绩 |

+——-+——+————–+

| 1 | f | 86.0000 |

| 1 | m | 85.0000 |

| 2 | m | 88.0000 |

where在分组前做，having在分组后做

MariaDB [db1]> select class,sex,avg(score) as 平均成绩 from students where class=1 group by class,sex having 平均成绩>80;

+——-+——+————–+

| class | sex | 平均成绩 |

+——-+——+————–+

| 1 | f | 86.0000 |

| 1 | m | 85.0000 |

+——-+——+————–+

排序

MariaDB [db1]> select * from students order by score;

正序排序

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

| 1 | bai | 10086 | 90 | m | 1 |

| 6 | huang | 110 | 99 | m | 2 |

倒序排序

MariaDB [db1]> select * from students order by score desc;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 6 | huang | 110 | 99 | m | 2 |

| 1 | bai | 10086 | 90 | m | 1 |

| 7 | aaa | 100 | 86 | f | 1 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 4 | zhang | 110 | 60 | f | 2 |

+—-+——-+——-+——-+——+——-+

按电话号码排序

MariaDB [db1]> select * from students order by phone;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

| 5 | wang | 10000 | 77 | m | 2 |

| 1 | bai | 10086 | 90 | m | 1 |

| 4 | zhang | 110 | 60 | f | 2 |

| 6 | huang | 110 | 99 | m | 2 |

MariaDB [db1]> select * from students order by phone desc;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 4 | zhang | 110 | 60 | f | 2 |

| 6 | huang | 110 | 99 | m | 2 |

| 1 | bai | 10086 | 90 | m | 1 |

| 5 | wang | 10000 | 77 | m | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

MariaDB [db1]> insert students values (8,’ma’,’123′,null,’f’,2);

Query OK, 1 row affected (0.00 sec)

MariaDB [db1]> select * from students

-> ;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 1 | bai | 10086 | 90 | m | 1 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 6 | huang | 110 | 99 | m | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

| 8 | ma | 123 | NULL | f | 2 |

MariaDB [db1]> select * from students order by score asc;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 8 | ma | 123 | NULL | f | 2 |

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

| 1 | bai | 10086 | 90 | m | 1 |

| 6 | huang | 110 | 99 | m | 2 |

MariaDB [db1]> select * from students order by -score desc; 这里的-只对数字有效

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

| 2 | liu | NULL | 80 | m | 1 |

| 3 | lin | NULL | 80 | f | 2 |

| 7 | aaa | 100 | 86 | f | 1 |

| 1 | bai | 10086 | 90 | m | 1 |

| 6 | huang | 110 | 99 | m | 2 |

| 8 | ma | 123 | NULL | f | 2 |

limit 只取前几个

MariaDB [db1]> select * from students order by score asc limit 3;

+—-+——-+——-+——-+——+——-+

+—-+——-+——-+——-+——+——-+

| 8 | ma | 123 | NULL | f | 2 |

| 4 | zhang | 110 | 60 | f | 2 |

| 5 | wang | 10000 | 77 | m | 2 |

mysql中引入脚本

第一种方法

[root@CENTOS7 ~]# mysql < hellodb_innodb.sql

第二种方法

MariaDB [(none)]> source hellodb_innodb.sql

MariaDB [hellodb]> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| db1 |

| hellodb |

| mysql |

| performance_schema |

| test |

+——————–+

6 rows in set (0.00 sec)

MariaDB [hellodb]> use hellodb

Database changed

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| toc |

(6) 以年龄排序后，显示年龄最大的前10位同学的信息

MariaDB [hellodb]> select * from students order by age desc limit 10 ;

+——-+————–+—–+——–+———+———–+

+——-+————–+—–+——–+———+———–+

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

示例

 DESC students;

 INSERT INTO students VALUES(1,’tom’，’m’),(2,’alice’,’f’);

 INSERT INTO students(id,name) VALUES(3,’jack’),(4,’allen’);

 SELECT * FROM students WHERE id < 3;

 SELECT * FROM students WHERE gender=’m’;

 SELECT * FROM students WHERE gender IS NULL;

 SELECT * FROM students WHERE gender IS NOT NULL;

 SELECT * FROM students ORDER BY name DESC LIMIT 2;

 SELECT * FROM students ORDER BY name DESC LIMIT 1,2;

 SELECT * FROM students WHERE id >=2 and id <=4

 SELECT * FROM students WHERE BETWEEN 2 AND 4

 SELECT * FROM students WHERE name LIKE ‘t%’

 SELECT * FROM students WHERE name RLIKE ‘.*[lo].*’;

 SELECT id stuid,name as stuname FROM students

练习

导入hellodb.sql生成数据库

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

(2) 以ClassID为分组依据，显示每组的平均年龄

(3) 显示第2题中平均年龄大于30的分组及平均年龄

(4) 显示以L开头的名字的同学的信息

(5) 显示TeacherID非空的同学的相关信息

(6) 以年龄排序后，显示年龄最大的前10位同学的信息

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息

多表访问

多表查询：学生对应的老师是谁两张表取交集。

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 老师姓名 from students as s,teachers as t where s.teacherid=t.tid;

+————–+—————+

| 学生姓名 | 老师姓名 |

+————–+—————+

| Yu Yutong | Song Jiang |

| Shi Zhongyu | Miejue Shitai |

| Ding Dian | Lin Chaoying |

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 老师姓名 from students as s inner join teachers as t on s.teacherid=t.tid;

| 学生姓名 | 老师姓名 |

+————–+—————+

| Yu Yutong | Song Jiang |

| Shi Zhongyu | Miejue Shitai |

| Ding Dian | Lin Chaoying |

两张表取交集用inner join ，可以更多张表取交集。

MariaDB [hellodb]> select * from students cross join teachers; 交叉连接，笛卡尔乘积

+——-+—————+—–+——–+———+———–+—–+—————+—–+——–+

+——-+—————+—–+——–+———+———–+—–+—————+—–+——–+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 94 | M |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |

| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 4 | Lin Chaoying | 93 | F |

| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Song Jiang | 45 | M |

| 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 94 | M |

| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | Miejue Shitai | 77 | F |

| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | Lin Chaoying | 93 | F |

什么情况下使用交叉连接；

列如

职员表职位表

id name id name

1 a 1 ceo

2 b

2 coo

左外连接

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 老师姓名 from students as s left outer join teachers as t on s.teacherid=t.tid;

+—————+—————+

| 学生姓名 | 老师姓名 |

+—————+—————+

| Shi Zhongyu | Miejue Shitai |

| Shi Potian | NULL |

| Xie Yanke | NULL |

| Ding Dian | Lin Chaoying |

| Yu Yutong | Song Jiang |

| Shi Qing | NULL |

右外连接

ariaDB [hellodb]> select s.name as 学生姓名, t.name as 老师姓名 from students as s right outer join teachers as t on s.teacherid=t.tid;

+————–+—————+

| 学生姓名 | 老师姓名 |

+————–+—————+

| Shi Zhongyu | Miejue Shitai |

| Ding Dian | Lin Chaoying |

| Yu Yutong | Song Jiang |

| NULL | Zhang Sanfeng |

+————–+—————+

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 老师姓名 from students as s left outer join teachers as t on s.teacherid=t.tid where t.name is null;

+—————+————–+

| 学生姓名 | 老师姓名 |

+—————+————–+

| Shi Potian | NULL |

| Xie Yanke | NULL |

| Shi Qing | NULL |

MariaDB [hellodb]> select s.name as 学生姓名, t.name as 老师姓名 from students as s right outer join teachers as t on s.teacherid=t.tid where s.name is null;

+————–+—————+

| 学生姓名 | 老师姓名 |

+————–+—————+

| NULL | Zhang Sanfeng |

+————–+—————+

1 row in set (0.00 sec)

需要union 来实现 union就是将两张表竖着连接起来。

select union select 前后两个select的数据格式应该一致

MariaDB [hellodb]> select stuid , name from students

-> union

-> select tid,name from teachers;

+——-+—————+

| stuid | name |

+——-+—————+

| 1 | Shi Zhongyu |

| 2 | Shi Potian |

| 3 | Xie Yanke |

| 4 | Ding Dian |

| 5 | Yu Yutong |

| 6 | Shi Qing |

| 7 | Xi Ren |

| 8 | Lin Daiyu |

| 9 | Ren Yingying |

| 10 | Yue Lingshan |

| 11 | Yuan Chengzhi |

| 12 | Wen Qingqing |

| 13 | Tian Boguang |

| 14 | Lu Wushuang |

| 15 | Duan Yu |

| 16 | Xu Zhu |

| 17 | Lin Chong |

| 18 | Hua Rong |

| 19 | Xue Baochai |

| 20 | Diao Chan |

| 21 | Huang Yueying |

| 22 | Xiao Qiao |

| 23 | Ma Chao |

| 24 | Xu Xian |

| 25 | Sun Dasheng |

| 1 | Song Jiang |

| 2 | Zhang Sanfeng |

| 3 | Miejue Shitai |

| 4 | Lin Chaoying |

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 老师姓名 from students as s left outer join teachers as t on s.teacherid=t.tid

-> union

-> select s.name as 学生成绩,t.name as 老师姓名 from students as s right outer join teachers as t on s.teacherid=t.tid;

+—————+—————+

| 学生姓名 | 老师姓名 |

+—————+—————+

| Shi Zhongyu | Miejue Shitai |

| Shi Potian | NULL |

| Xie Yanke | NULL |

| Ding Dian | Lin Chaoying |

| Yu Yutong | Song Jiang |

| Shi Qing | NULL |

| Xi Ren | NULL |

| Lin Daiyu | NULL |

| Ren Yingying | NULL |

| Yue Lingshan | NULL |

| Yuan Chengzhi | NULL |

| Wen Qingqing | NULL |

| Tian Boguang | NULL |

| Lu Wushuang | NULL |

| Duan Yu | NULL |

| Xu Zhu | NULL |

| Lin Chong | NULL |

| Hua Rong | NULL |

| Xue Baochai | NULL |

| Diao Chan | NULL |

| Huang Yueying | NULL |

| Xiao Qiao | NULL |

| Ma Chao | NULL |

| Xu Xian | NULL |

| Sun Dasheng | NULL |

| NULL | Zhang Sanfeng |

+—————+—————+

MariaDB [hellodb]> select s.name as 学生姓名,t.name as 老师姓名 from students as s left outer join teachers as t on s.teacherid=t.tid where t.name is null

-> union

-> select s.name as 学生姓名, t.name as 老师姓名 from students as s right outer join teachers as t on s.teacherid=t.tid where s.name is null;

还可以写成

MariaDB [hellodb]> select * from (select s.stuid,s.name as studentname,t.tid,t.name as teachername from students as s left outer join teachers as t on s.teacherid=t.tid union select s.stuid,s.name,t.tid,t.name from students as s right outer join teachers as t on s.teacherid=t.tid) as a where a.stuid is null or a.tid is null;

+—————+—————+

| 学生姓名 | 老师姓名 |

+—————+—————+

| Shi Potian | NULL |

| Xie Yanke | NULL |

| Shi Qing | NULL |

| Xi Ren | NULL |

| Lin Daiyu | NULL |

| Ren Yingying | NULL |

| Yue Lingshan | NULL |

| Yuan Chengzhi | NULL |

| Wen Qingqing | NULL |

| Tian Boguang | NULL |

| Lu Wushuang | NULL |

| Duan Yu | NULL |

| Xu Zhu | NULL |

| Lin Chong | NULL |

| Hua Rong | NULL |

| Xue Baochai | NULL |

| Diao Chan | NULL |

| Huang Yueying | NULL |

| Xiao Qiao | NULL |

| Ma Chao | NULL |

| Xu Xian | NULL |

| Sun Dasheng | NULL |

| NULL | Zhang Sanfeng |

+—————+—————+

自内连接

MariaDB [hellodb]> select * from students

-> ;

+——-+—————+—–+——–+———+———–+

+——-+—————+—–+——–+———+———–+

| 1 | Shi Zhongyu | 22 | M | 2 | 3 |

| 2 | Shi Potian | 22 | M | 1 | 7 |

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 4 | Ding Dian | 32 | M | 4 | 4 |

| 5 | Yu Yutong | 26 | M | 3 | 1 |

| 6 | Shi Qing | 46 | M | 5 | NULL |

| 7 | Xi Ren | 19 | F | 3 | NULL |

| 8 | Lin Daiyu | 17 | F | 7 | NULL |

| 9 | Ren Yingying | 20 | F | 6 | NULL |

| 10 | Yue Lingshan | 19 | F | 3 | NULL |

| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |

| 12 | Wen Qingqing | 19 | F | 1 | NULL |

| 13 | Tian Boguang | 33 | M | 2 | NULL |

| 14 | Lu Wushuang | 17 | F | 3 | NULL |

| 15 | Duan Yu | 19 | M | 4 | NULL |

| 16 | Xu Zhu | 21 | M | 1 | NULL |

| 17 | Lin Chong | 25 | M | 4 | NULL |

| 18 | Hua Rong | 23 | M | 7 | NULL |

| 19 | Xue Baochai | 18 | F | 6 | NULL |

| 20 | Diao Chan | 19 | F | 7 | NULL |

| 21 | Huang Yueying | 22 | F | 6 | NULL |

| 22 | Xiao Qiao | 20 | F | 1 | NULL |

| 23 | Ma Chao | 23 | M | 4 | NULL |

| 24 | Xu Xian | 27 | M | NULL | NULL |

| 25 | Sun Dasheng | 100 | M | NULL | NULL |

+——-+—————+—–+——–+———+———–+

25 rows in set (0.00 sec)

MariaDB [hellodb]> select s1.name as 雇员,s2.name as 领导 from students as s1 inner join students as s2 on s1.stuid=s2.teacherid;

+————-+————-+

| 雇员 | 领导 |

+————-+————-+

| Xie Yanke | Shi Zhongyu |

| Xi Ren | Shi Potian |

| Xu Zhu | Xie Yanke |

| Ding Dian | Ding Dian |

| Shi Zhongyu | Yu Yutong |

+————-+————-+

5 rows in set (0.00 sec)

MariaDB [hellodb]> select s1.name as 雇员,s2.name as 领导 from students as s1 inner join students as s2 on s2.stuid=s1.teacherid;

+————-+————-+

| 雇员 | 领导 |

+————-+————-+

| Shi Zhongyu | Xie Yanke |

| Shi Potian | Xi Ren |

| Xie Yanke | Xu Zhu |

| Ding Dian | Ding Dian |

| Yu Yutong | Shi Zhongyu |

+————-+————-+

5 rows in set (0.00 sec)

MariaDB [hellodb]> select s1.name as 雇员,s2.name as 领导 from students as s1 left outer join students as s2 on s2.stuid=s1.teacherid;

+—————+————-+

| 雇员 | 领导 |

+—————+————-+

| Shi Zhongyu | Xie Yanke |

| Shi Potian | Xi Ren |

| Xie Yanke | Xu Zhu |

| Ding Dian | Ding Dian |

| Yu Yutong | Shi Zhongyu |

| Shi Qing | NULL |

| Xi Ren | NULL |

| Lin Daiyu | NULL |

| Ren Yingying | NULL |

| Yue Lingshan | NULL |

| Yuan Chengzhi | NULL |

| Wen Qingqing | NULL |

| Tian Boguang | NULL |

| Lu Wushuang | NULL |

| Duan Yu | NULL |

| Xu Zhu | NULL |

| Lin Chong | NULL |

| Hua Rong | NULL |

| Xue Baochai | NULL |

| Diao Chan | NULL |

| Huang Yueying | NULL |

| Xiao Qiao | NULL |

| Ma Chao | NULL |

| Xu Xian | NULL |

| Sun Dasheng | NULL |

+—————+————-+

子查询：查询语句嵌套查询语句

MariaDB [hellodb]> select * from scores;

+—-+——-+———-+——-+

+—-+——-+———-+——-+

| 1 | 1 | 2 | 77 |

| 2 | 1 | 6 | 93 |

| 3 | 2 | 2 | 47 |

| 4 | 2 | 5 | 97 |

| 5 | 3 | 2 | 88 |

| 6 | 3 | 6 | 75 |

| 7 | 4 | 5 | 71 |

| 8 | 4 | 2 | 89 |

| 9 | 5 | 1 | 39 |

| 10 | 5 | 7 | 63 |

| 11 | 6 | 1 | 96 |

| 12 | 7 | 1 | 86 |

| 13 | 7 | 7 | 83 |

| 14 | 8 | 4 | 57 |

| 15 | 8 | 3 | 93 |

+—-+——-+———-+——-+

MariaDB [hellodb]> select avg(score) from scores;

+————+

| avg(score) |

+————+

| 76.9333 |

+————+

1 row in set (0.00 sec)

MariaDB [hellodb]> select stuid,score from scores where score > (select avg(score) from scores);

+——-+——-+

| stuid | score |

+——-+——-+

| 1 | 77 |

| 1 | 93 |

| 2 | 97 |

| 3 | 88 |

| 4 | 89 |

| 6 | 96 |

| 7 | 86 |

| 7 | 83 |

| 8 | 93 |

MariaDB [hellodb]> select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid;

+————-+——-+

| name | score |

+————-+——-+

| Shi Zhongyu | 77 |

| Shi Zhongyu | 93 |

| Shi Potian | 47 |

| Shi Potian | 97 |

| Xie Yanke | 88 |

| Xie Yanke | 75 |

| Ding Dian | 71 |

| Ding Dian | 89 |

| Yu Yutong | 39 |

| Yu Yutong | 63 |

| Shi Qing | 96 |

| Xi Ren | 86 |

| Xi Ren | 83 |

| Lin Daiyu | 57 |

| Lin Daiyu | 93 |

MariaDB [hellodb]> select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid where score > (select avg(score) from scores);

+————-+——-+

| name | score |

+————-+——-+

| Shi Zhongyu | 77 |

| Shi Zhongyu | 93 |

| Shi Potian | 97 |

| Xie Yanke | 88 |

| Ding Dian | 89 |

| Shi Qing | 96 |

| Xi Ren | 86 |

| Xi Ren | 83 |

| Lin Daiyu | 93 |

+————-+——-+

多表查询

交叉连接：笛卡尔乘积

内连接：

等值连接：让表之间的字段以“等值”建立连接关系；

不等值连接

自然连接:去掉重复列的等值连接

自连接

外连接：

左外连接：

FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col

右外连接

FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

多表查询

子查询：在查询语句嵌套着查询语句，性能较差

基于某语句的查询结果再次进行的查询

用在WHERE子句中的子查询：

 用于比较表达式中的子查询；子查询仅能返回单个值

SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);

 用于IN中的子查询：子查询应该单键查询并返回一个或多个值从构成列表

SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);

 用于EXISTS

多表查询

用于FROM子句中的子查询

使用格式：SELECT tb_alias.col1,… FROM (SELECT clause) AS tb_alias

WHERE Clause;

示例：

SELECT s.aage,s.ClassID FROM (SELECT avg(Age) AS aage,ClassID

FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID) AS s

WHERE s.aage>30;

联合查询：UNION

SELECT Name,Age FROM students UNION SELECT Name,Age FROM

teachers;

select 语句的执行顺序

练习

 导入hellodb.sql，以下操作在students表上执行

 1、以ClassID分组，显示每班的同学的人数

 2、以Gender分组，显示其年龄之和

 3、以ClassID分组，显示其平均年龄大于25的班级

 4、以Gender分组，显示各组中年龄大于25的学员的年龄之和MariaDB [hellodb]> select gender, MariaDB [hellodb]> select gender, sum(age) from students where age > 25 group by gender ;

+——–+———-+

| gender | sum(age) |

+——–+———-+

| M | 317 |

+——–+———-+

MariaDB [hellodb]> select gender, sum(age) from students where age > 18 group by gender ;

+——–+———-+

| gender | sum(age) |

+——–+———-+

| F | 138 |

| M | 495 |

+——–+———-+

这个题无法使用select gender, sum(age) from students group by gender having age > 18 ;因为having后面只能跟 gender 和汇总的数据，别的不行。

 5、显示前5位同学的姓名、课程及成绩

 6、显示其成绩高于80的同学的名称及课程；

 7、求前8位同学每位同学自己两门课的平均成绩，并按降序排列

 8、显示每门课程课程名称及学习了这门课的同学的个数

 9、如何显示其年龄大于平均年龄的同学的名字

 10、如何显示其学习的课程为第1、2，4或第7门课的同学的名字

 11、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学

 12、统计各班级中年龄大于全校同学平均年龄的同学

MariaDB [hellodb]> select avg(age) from students;

+———-+

| avg(age) |

+———-+

| 27.4000 |

+———-+

MariaDB [hellodb]> select name,classid,age from students where age > (select avg(age) from students);

+————–+———+—–+

| name | classid | age |

+————–+———+—–+

| Xie Yanke | 2 | 53 |

| Ding Dian | 4 | 32 |

| Shi Qing | 5 | 46 |

| Tian Boguang | 2 | 33 |

| Sun Dasheng | NULL | 100 |

+————–+———+—–+

视图是一种虚拟的表，只是一种查询结果，并不存放在磁盘上，类似于shall中的别名。

视图：VIEW,虚表，保存有实表的查询结果

创建方法：

CREATE VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

查看视图定义：SHOW CREATE VIEW view_name

删除视图：

DROP VIEW [IF EXISTS]

view_name [, view_name] …

[RESTRICT | CASCADE] 慎用

视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；

其修改操作受基表限制

定义一个视图

MariaDB [hellodb]> create view wiew_students as select stuid,name from students;

Query OK, 0 rows affected (0.00 sec)

使用视图

MariaDB [hellodb]> select * from wiew_students;

MariaDB [hellodb]> select name from wiew_students;

+—————+

| name |

+—————+

| Shi Zhongyu |

| Shi Potian |

| Xie Yanke |

| Ding Dian |

MariaDB [hellodb]> desc wiew_students;

+——-+——————+——+—–+———+——-+

+——-+——————+——+—–+———+——-+

| stuid | int(10) unsigned | NO | | 0 | |

MariaDB [hellodb]> show table status like ‘wiew_students’\G

*************************** 1. row ***************************

Name: wiew_students

Comment: VIEW

定义时的命令

MariaDB [hellodb]> show create view wiew_students\G

*************************** 1. row ***************************

View: wiew_students

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `wiew_students` AS select `students`.`StuID` AS `stuid`,`students`.`Name` AS `name` from `students`

character_set_client: utf8

collation_connection: utf8_general_ci

用视图可以将保存在物理磁盘的数据隐藏起来，只给用户看想给用户看的信息，还能将很长的命令定义成视图（类似别名）来减少工作量。

修改视图的数据内容，实际上就是修改了students的数据内容

MariaDB [hellodb]> update wiew_students set name=’qtds’ where stuid=25;

Query OK, 1 row affected (0.04 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [hellodb]> select * from wiew_students;

25 | qtds

MariaDB [hellodb]> select * from students;

25 | qtds | 100 | M | NULL | NULL |

MariaDB [hellodb]> create view view_oldstudents as select * from students where age > ’50’ ;

MariaDB [hellodb]> select * from view_oldstudents;

+——-+———–+—–+——–+———+———–+

+——-+———–+—–+——–+———+———–+

| 3 | Xie Yanke | 53 | M | 2 | 16 |

| 25 | qtds | 100 | M | NULL | NULL |

+——-+———–+—–+——–+———+———–+

MariaDB [hellodb]> update view_oldstudents set age=48 ;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

MariaDB [hellodb]> select * from view_oldstudents;

Empty set (0.00 sec)

MariaDB [hellodb]> select * from students;

3 | Xie Yanke | 48 | M | 2 | 16

25 | qtds | 48 | M | NULL | NULL

物化视图：就是将视图物理化，这时的视图就是存放在磁盘中的

删除视图，不会影响原来的数据。

MariaDB [hellodb]> drop view view_oldstudents;

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| toc |

| wiew_students |

函数

函数：系统函数和自定义函数

系统函数 :https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

自定义函数 (user-defined function UDF)

 保存在mysql.proc表中

 创建UDF:

CREATE [AGGREGATE] FUNCTION function_name(parameter_name

type,[parameter_name type,…])

RETURNS {STRING|INTEGER|REAL}

runtime_body

 说明：

参数可以有多个,也可以没有参数

必须有且只有一个返回值

系统自带的函数

MariaDB [hellodb]> select count(*) from students;

+———-+

| count(*) |

+———-+

| 25 |

mysql中的函数是不能单独使用的，必须嵌入到sql语句中使用。

定义一个函数

MariaDB [hellodb]> create function si() returns varchar(20) return “hello world”;

Query OK, 0 rows affected (0.03 sec)

使用这个函数

MariaDB [hellodb]> select si();

+————-+

| si() |

+————-+

| hello world |

查看现有的自定义函数

MariaDB [hellodb]> show function status\G

*************************** 1. row ***************************

Db: hellodb

Name: si

Type: FUNCTION

Definer: root@localhost

Modified: 2018-06-07 18:53:13

Created: 2018-06-07 18:53:13

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

自定义函数

示例:无参UDF

CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN “Hello

World!“;

示例：有参数UDF

DELIMITER //

CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)

BEGIN

DELETE FROM students WHERE stuid = uid;

RETURN (SELECT COUNT(uid) FROM students);

END//

DELIMITER ;

自定义函数

 自定义函数中定义局部变量语法:

 DECLARE 变量1[,变量2,… ]变量类型 [DEFAULT 默认值]

 说明：局部变量的作用范围是在BEGIN…END程序中,而且定义局部变量语句必须在

BEGIN…END的第一行定义

 示例:

DELIMITER //

CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)

RETURNS SMALLINT

BEGIN

DECLARE a, b SMALLINT UNSIGNED DEFAULT 10;

SET a = x, b = y;

RETURN a+b;

END//

自定义函数

为变量赋值语法

 SET parameter_name = value[,parameter_name = value…]

 SELECT INTO parameter_name

示例:

…

DECLARE x int;

SELECT COUNT(id) FROM tdb_name INTO x;

RETURN x;

END//

MariaDB [hellodb]> delimiter // 这时表示命令结束就变成了//，而不再是；

MariaDB [hellodb]> create function asum(x smallint unsigned,y smallint unsigned) x，y是形参

-> returns smallint

-> begin

-> declare a,b smallint unsigned default 10; a，b是实参。

-> set a = x,b = y;

-> return a+b;

-> end //

MariaDB [hellodb]> delimiter ;

MariaDB [hellodb]> select asum(10,20);

+————-+

| asum(10,20) |

+————-+

| 30 |

+————-+

1 row in set (0.00 sec)

MariaDB [hellodb]> show function status\G

*************************** 1. row ***************************

Db: hellodb

Name: asum

Type: FUNCTION

Definer: root@localhost

Modified: 2018-06-07 19:11:12

Created: 2018-06-07 19:11:12

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

*************************** 2. row ***************************

Db: hellodb

Name: si

Type: FUNCTION

Definer: root@localhost

Modified: 2018-06-07 18:53:13

Created: 2018-06-07 18:53:13

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

删除函数

MariaDB [hellodb]> drop function si;

这时只剩一个自定义函数了

MariaDB [hellodb]> show function status\G

*************************** 1. row ***************************

Db: hellodb

Name: asum

Type: FUNCTION

Definer: root@localhost

Modified: 2018-06-07 19:11:12

Created: 2018-06-07 19:11:12

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: utf8_general_ci

存储过程

存储过程：存储过程保存在mysql.proc表中

创建存储过程

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]])

routime_body

其中:proc_parameter : [IN|OUT|INOUT] parameter_name type

其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；

param_name表示参数名称；type表示参数的类型

查看存储过程列表

SHOW PROCEDURE STATUS

自定义的函数也保存在mysql.proc数据库中。

MariaDB [hellodb]> select * from mysql.proc\G

*************************** 1. row ***************************

db: hellodb

name: asum

type: FUNCTION

specific_name: asum

language: SQL

sql_data_access: CONTAINS_SQL

is_deterministic: NO

security_type: DEFINER

param_list: x smallint unsigned,y smallint unsigned

returns: smallint(6)

body: begin

declare a,b smallint unsigned default 10;

set a = x,b = y;

return a+b;

end

definer: root@localhost

created: 2018-06-07 19:11:12

modified: 2018-06-07 19:11:12

sql_mode:

comment:

character_set_client: utf8

collation_connection: utf8_general_ci

db_collation: utf8_general_ci

body_utf8: begin

declare a,b smallint unsigned default 10;

set a = x,b = y;

return a+b;

end

MariaDB [hellodb]> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| db1 |

| hellodb |

| mysql | 系统数据库，将来备份数据时不光备份用户数据库也应备份系统数据库。

| performance_schema |

| test |

MariaDB [hellodb]> select now();

+———————+

| now() |

+———————+

| 2018-06-07 19:23:03 |

存储过程

查看存储过程定义

SHOW CREATE PROCEDURE sp_name

调用存储过程:

CALL sp_name ([ proc_parameter [,proc_parameter …]])

CALL sp_name

说明:当无参时,可以省略”()”,当有参数时,不可省略”()”

存储过程修改:

ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西,不能修改

存储过程体,所以要修改存储过程,方法就是删除重建

删除存储过程:

DROP PROCEDURE [IF EXISTS] sp_name

存储过程示例

创建无参存储过程:

delimiter //

CREATE PROCEDURE showTime()

BEGIN

SELECT now();

END//

delimiter ;

CALL showTime;

MariaDB [hellodb]> delimiter //

MariaDB [hellodb]> create procedure showtime()

-> begin

-> select now();

-> end //

Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> delimiter ;

MariaDB [hellodb]> call showtime;

+———————+

| now() |

+———————+

| 2018-06-07 19:25:30 |

存储过程示例

创建含参存储过程:只有一个IN参数

delimiter //

CREATE PROCEDURE seleById(IN id SMALLINT UNSIGNED)

BEGIN

SELECT * FROM students WHERE stuid = uid;

END//

delimiter ;

call seleById(2);

存储过程示例

示例

delimiter //

CREATE PROCEDURE dorepeat(p1 INT)

BEGIN

SET @x = 0;

REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; repeat相当于for循环

END

delimiter ;

CALL dorepeat(1000);

SELECT @x;

存储过程示例

创建含参存储过程:包含IN参数和OUT参数

delimiter //

CREATE PROCEDURE deleteById(IN id SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)

BEGIN

DELETE FROM students WHERE stuid = id;

SELETE row_count() into num; row_count()系统自带函数，显示删除的信息。

END//

delimiter ;

call seleById(2,@Line);

SELETE @Line;

说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删

除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

存储过程

存储过程优势:

存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,

当需要时从数据库中直接调用,省去了编译的过程

提高了运行速度

同时降低网络数据传输量

存储过程与自定义函数的区别:

存储过程实现的过程要复杂一些,而函数的针对性较强

存储过程可以有多个返回值,而自定义函数只有一个返回值

存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用

流程控制

存储过程和函数中可以使用流程控制来控制语句的执行

流程控制：

 IF：用来进行条件判断。根据是否满足条件，执行不同语句

 CASE：用来进行条件判断，可实现比IF语句更复杂的条件判断

 LOOP：重复执行特定的语句，实现一个简单的循环

 LEAVE：用于跳出循环控制

 ITERATE：跳出本次循环，然后直接进入下一次循环

 REPEAT：有条件控制的循环语句。当满足特定条件时，就会跳出循环语句

 WHILE：有条件控制的循环语句

触发器

 触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行

 创建触发器

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

 说明：

trigger_name：触发器的名称

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发

trigger_event:：{ INSERT |UPDATE | DELETE }，触发的具体事件

tbl_name：该触发器作用在表名

查询表不需要触发器，修改才需要触发器。

触发器示例

CREATE TABLE student_info (

stu_no INT(11) NOT NULL AUTO_INCREMENT,

stu_name VARCHAR(255) DEFAULT NULL,

PRIMARY KEY (stu_no)

);

CREATE TABLE student_count (

student_count INT(11) DEFAULT 0

);

INSERT INTO student_count VALUES(0);

示例：创建触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，

学生数减少

CREATE TRIGGER trigger_student_count_insert

AFTER INSERT

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count+1;

CREATE TRIGGER trigger_student_count_delete

AFTER DELETE

ON student_info FOR EACH ROW

UPDATE student_count SET student_count=student_count-1;

触发器

查看触发器

SHOW TRIGGERS

查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发

器信息。

mysql> USE information_schema;

Database changed

mysql> SELECT * FROM triggers WHERE

trigger_name=’trigger_student_count_insert’;

删除触发器

DROP TRIGGER trigger_name;

MySQL用户和权限管理

元数据数据库：mysql

系统授权表：

db, host, user

columns_priv, tables_priv, procs_priv, proxies_priv

用户账号：

‘USERNAME’@’HOST’：

@’HOST’:

主机名；

IP地址或Network;

通配符：

%, _: 172.16.%.%

用户管理

创建用户：CREATE USER

CREATE USER ‘USERNAME’@’HOST’ [IDENTIFIED BY ‘password’]；

默认权限：USAGE 只能连接数据库，没有别的权限

用户重命名：RENAME USER

RENAME USER old_user_name TO new_user_name

删除用户：

DROP USER ‘USERNAME’@’HOST‘

示例：删除默认的空用户

DROP USER ”@’localhost’;

创建一个wang用户，并制定口令是centos

MariaDB [mysql]> create user ‘wang’@’192.168.30.100’ identified by ‘centos’;

Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select user,host,password from user;

wang | 192.168.30.100 | *128977E278358FF80A246B5046F51043A2B1FCED |

刷新权限

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

[root@CENTOS7 ~]# mysql -uwang -pcentos -h 192.168.30.101

MariaDB [(none)]> status;

Connection: 192.168.30.101 via TCP/IP

查看当前用户名

MariaDB [(none)]> select user();

+———————+

| user() |

+———————+

| wang@192.168.30.100 |

MariaDB [mysql]> show processlist;

+—-+——+———————-+——-+———+——+——-+——————+———-+

+—-+——+———————-+——-+———+——+——-+——————+———-+

| 9 | wang | 192.168.30.100:57698 | NULL | Sleep | 56 | | NULL | 0.000 |

MariaDB [(none)]> use test;

Database changed

MariaDB [test]>

MariaDB [mysql]> show processlist;

+—-+——+———————-+——-+———+——+——-+——————+———-+

+—-+——+———————-+——-+———+——+——-+——————+———-+

| 9 | wang | 192.168.30.100:57698 | test | Sleep | 4 | | NULL | 0.000 |

限制用户只能在192.168.30.100的机器上登录wang账号

[root@CENTOS7 ~]# mysql -uwang -pcentos -h192.168.30.101

ERROR 1130 (HY000): Host ‘192.168.30.101’ is not allowed to connect to this MariaDB server

MariaDB [(none)]> use mysql

删除用户信息。

MariaDB [mysql]> drop user ”@’localhost’;

MariaDB [mysql]> select user,host,password from mysql.user;

可以一次删除多行

MariaDB [mysql]> delete from user where user=”;

匿名用户已经不存在。

MariaDB [mysql]> select user,host,password from mysql.user;

+——+———————+——————————————-+

| user | host | password |

+——+———————+——————————————-+

| root | localhost | |

| root | centos7.localdomain | |

| root | 127.0.0.1 | |

| root | ::1 | |

| wang | 192.168.30.100 | *128977E278358FF80A246B5046F51043A2B1FCED |

刷新一下权限，迫使其立即生效。

MariaDB [mysql]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

用户管理

修改密码：

mysql>SET PASSWORD FOR ‘user’@’host’ = PASSWORD(‘password’);

 mysql>UPDATE mysql.user SET password=PASSWORD(‘your_password’) WHERE clause;

此方法需要执行下面指令才能生效：mysql> FLUSH PRIVILEGES;

 #mysqladmin -u root –poldpass password ‘newpass‘

忘记管理员密码的解决办法：

启动mysqld进程时，为其使用如下选项：

–skip-grant-tables –skip-networking

使用UPDATE命令修改管理员密码

关闭mysqld进程，移除上述两个选项，重启mysqld

为用户设置口令。

MariaDB [mysql]> set password for root@’::1’=password(‘centos’);

MariaDB [mysql]> select user,host,password from user;

root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED

MariaDB [mysql]> update user set password=password(‘centos’) where password=”;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3 Changed: 3 Warnings: 0

MariaDB [mysql]> select user,host,password from user;

+——+———————+——————————————-+

| user | host | password |

+——+———————+——————————————-+

| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | centos7.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.30.100 | *128977E278358FF80A246B5046F51043A2B1FCED

刷新策略

MariaDB [(none)]> flush privileges;

shell命令，查看mysqladmin用法。

[root@CENTOS7 ~]# mysqladmin –help

测试数据库是什么状态。

[root@CENTOS7 ~]# mysqladmin -uroot -pcentos ping

mysqld is alive

删除数据库db1.

[root@CENTOS7 ~]# mysqladmin -uroot -pcentos drop db1

Dropping the database is potentially a very bad thing to do.

Any data stored in the database will be destroyed.

Do you really want to drop the ‘db1’ database [y/N] y

Database “db1” dropped

db1数据库已经被删除。

[root@CENTOS7 ~]# mysql -uroot -pcentos -e ‘show databases’

+——————–+

| Database |

+——————–+

| information_schema |

| hellodb |

| mysql |

| performance_schema |

| test |

一但忘记数据库账号的密码如何破解：

如果是一个刚刚创建装好mariadb，那么可以停掉数据库，将/var/lib/mysql/中的文件全部删除就可以了。

cd /var/lib/mysql

rm -rf mysql/*

所有的用户账号就丢失了，如果无法启动mariadb，那么可以rm -rf * 启动mariadb就行。

修改口令

[root@CENTOS7 ~]# mysqladmin -uroot -pcentos password ‘magedu’

对没有口令的账号设置口令

[root@CENTOS7 ~]# mysqladmin password ‘centos’

[root@CENTOS7 ~]# mysql -uroot -pcentos

如果忘记口令

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

[mysqld]

skip-grant-tables 忽略授权表的意思

[root@CENTOS7 ~]# systemctl restart mariadb 重启服务后可以直接登陆

MariaDB [(none)]> use mysql

MariaDB [mysql]> update user set password=password(‘centos’) where user=’root’ and host=’localhost’;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [mysql]> select user,host,password from user;

+——+———————+——————————————-+

| user | host | password |

+——+———————+——————————————-+

| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |

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

[mysqld]

#skip-grant-tables

[root@CENTOS7 ~]# systemctl restart mariadb

到此已经破解mysql口令。

[root@CENTOS7 ~]# mysql -uroot -pcentos

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.56-MariaDB MariaDB Server

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

[root@CENTOS7 ~]# scp -r /var/lib/mysql/hellodb/ 192.168.30.100 /var/lib/mysql

[root@CENTOS7 ~]# ll /var/lib/mysql

drwx——. 2 root root 171 Jun 7 21:04 hellodb

[root@CENTOS7 ~]# chown -R mysql.mysql /var/lib/mysql/hellodb/

[root@CENTOS7 ~]# ll /var/lib/mysql

drwx——. 2 mysql mysql 171 Jun 7 21:04 hellodb

传数据的时候不光要把文件夹传过去mariadb10版本之前的版本数据库的数据放在下面文件中

[root@CENTOS7 ~]# ll /var/lib/mysql/

-rw-rw—- 1 mysql mysql 18874368 Jun 7 20:59 ibdata1

[root@CENTOS7 ~]# scp /var/lib/mysql/ibdata1 192.168.30.100:/var/lib/mysql/

[root@CENTOS7 ~]# systemctl restart mariadb

MariaDB [(none)]> use hellodb

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| classes |

| coc |

| courses |

| scores |

| students |

| teachers |

| toc |

| wiew_students |

已经可以使用拷过来的数据。

MariaDB [hellodb]> select * from coc;

+—-+———+———-+

| ID | ClassID | CourseID |

+—-+———+———-+

| 1 | 1 | 2 |

| 2 | 1 | 5 |

| 3 | 2 | 2 |

| 4 | 2 | 6 |

| 5 | 3 | 1 |

| 6 | 3 | 7 |

| 7 | 4 | 5 |

| 8 | 4 | 2 |

| 9 | 5 | 1 |

| 10 | 5 | 9 |

| 11 | 6 | 3 |

| 12 | 6 | 4 |

| 13 | 7 | 4 |

| 14 | 7 | 3 |

MariaDB [hellodb]> select st.stuid,st.name,sc.courseid from students as st inner join scores as sc on st.stuid=sc.stuid;

+——-+————-+———-+

| stuid | name | courseid |

+——-+————-+———-+

| 1 | Shi Zhongyu | 2 |

| 1 | Shi Zhongyu | 6 |

| 2 | Shi Potian | 2 |

| 2 | Shi Potian | 5 |

| 3 | Xie Yanke | 2 |

| 3 | Xie Yanke | 6 |

| 4 | Ding Dian | 5 |

| 4 | Ding Dian | 2 |

| 5 | Yu Yutong | 1 |

| 5 | Yu Yutong | 7 |

| 6 | Shi Qing | 1 |

| 7 | Xi Ren | 1 |

| 7 | Xi Ren | 7 |

| 8 | Lin Daiyu | 4 |

| 8 | Lin Daiyu | 3 |

3张表相互连接

MariaDB [hellodb]> select st.stuid,st.name,sc.courseid,co.course from students as st inner join scores as sc on st.stuid=sc.stuid inner join courses as co on sc.courseid=co.courseid;

+——-+————-+———-+—————-+

+——-+————-+———-+—————-+

| 1 | Shi Zhongyu | 2 | Kuihua Baodian |

| 1 | Shi Zhongyu | 6 | Weituo Zhang |

| 2 | Shi Potian | 2 | Kuihua Baodian |

| 2 | Shi Potian | 5 | Daiyu Zanghua |

| 3 | Xie Yanke | 2 | Kuihua Baodian |

| 3 | Xie Yanke | 6 | Weituo Zhang |

| 4 | Ding Dian | 5 | Daiyu Zanghua |

| 4 | Ding Dian | 2 | Kuihua Baodian |

| 5 | Yu Yutong | 1 | Hamo Gong |

| 5 | Yu Yutong | 7 | Dagou Bangfa |

| 6 | Shi Qing | 1 | Hamo Gong |

| 7 | Xi Ren | 1 | Hamo Gong |

| 7 | Xi Ren | 7 | Dagou Bangfa |

| 8 | Lin Daiyu | 4 | Taiji Quan |

| 8 | Lin Daiyu | 3 | Jinshe Jianfa |

MySQL权限管理

权限类别：

数据库级别

表级别

字段级别

管理类

程序类函数，存储过程，触发器都是程序累得。

MySQL用户和权限管理

管理类：

CREATE TEMPORARY TABLES 创建临时表

CREATE USER 创建用户

FILE

SUPER

SHOW DATABASES 查看数据库

RELOAD

SHUTDOWN 停止mariadb

REPLICATION SLAVE 集群复制

REPLICATION CLIENT

LOCK TABLES

PROCESS

MySQL用户和权限管理

 程序类： FUNCTION、PROCEDURE、TRIGGER

CREATE

ALTER

DROP

EXCUTE

 库和表级别：DATABASE、TABLE

ALTER

CREATE

CREATE VIEW

DROP

INDEX

SHOW VIEW

GRANT OPTION：能将自己获得的权限转赠给其他用户

MySQL用户和权限管理

数据操作：

SELECT

INSERT

DELETE

UPDATE

字段级别：

SELECT(col1,col2,…)

UPDATE(col1,col2,…)

INSERT(col1,col2,…)

所有权限：ALL PRIVILEGES 或 ALL

授权

 参考：https://dev.mysql.com/doc/refman/5.7/en/grant.html

 GRANT priv_type [(column_list)],… ON [object_type] priv_level TO ‘user’@’host’

[IDENTIFIED BY ‘password’] [WITH GRANT OPTION];

 priv_type: ALL [PRIVILEGES]

 object_type:TABLE | FUNCTION | PROCEDURE

db_name.routine_name(指定库的函数,存储过程,触发器)

 with_option: GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

示例：GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO ‘someuser’@’somehost’;

创建用户

MariaDB [hellodb]> create user wang@’192.168.30.%’ identified by ‘centos’;

创建用户并直接赋予权限，

MariaDB [hellodb]> grant all on hellodb.* to wang@’192.168.%.%’ identified by ‘magedu’;

MariaDB [hellodb]> select user,host,password from mysql.user;

wang | 192.168.30.100 | *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.30.% | *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.%.% | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664

删除用户wang@’192.168.30.100‘

MariaDB [hellodb]> drop user wang@192.168.30.100;

删除用户wang@‘192.168.30.%’

MariaDB [hellodb]> drop user wang@’192.168.30.%’;

MariaDB [hellodb]> select user,host,password from mysql.user;

+——+———————+——————————————-+

| user | host | password |

+——+———————+——————————————-+

| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | centos7.localdomain | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | 127.0.0.1 | *128977E278358FF80A246B5046F51043A2B1FCED |

| root | ::1 | *128977E278358FF80A246B5046F51043A2B1FCED |

| wang | 192.168.%.% | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |

MariaDB [hellodb]> grant select,insert on hellodb.* to mage@’%’ identified by ‘centos’;

MariaDB [hellodb]> select user,host,password from mysql.user;

mage | % | *128977E278358FF80A246B5046F51043A2B1FCED

[root@CENTOS7 ~]# mysql -umage -pcentos -h192.168.30.101

因为没有权限所以不能删除。

MariaDB [hellodb]> delete from students;

ERROR 1142 (42000): DELETE command denied to user ‘mage’@’192.168.30.100’ for table ‘students’

新建laowang用户并授权只能查看students表中的age和name。

MariaDB [hellodb]> grant select(age,name) on hellodb.students to laowang@’%’ identified by ‘centos’;

[root@CENTOS7 ~]# mysql -ulaowang -pcentos -h192.168.30.101

MariaDB [(none)]> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| hellodb |

| test |

MariaDB [hellodb]> show tables;

+——————-+

| Tables_in_hellodb |

+——————-+

| students |

MariaDB [hellodb]> select * from students;

ERROR 1143 (42000): SELECT command denied to user ‘laowang’@’192.168.30.100’ for column ‘StuID’ in table ‘students’

MariaDB [hellodb]> select name,age from students;

+—————+—–+

| name | age |

+—————+—–+

| Shi Zhongyu | 22 |

| Shi Potian | 22 |

MariaDB [(none)]> show grants for wang@’192.168.%.%’;

+—————————————————————————————————————+

| Grants for wang@192.168.%.% |

+—————————————————————————————————————+

| GRANT USAGE ON *.* TO ‘wang’@’192.168.%.%’ IDENTIFIED BY PASSWORD ‘*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664’ |

| GRANT ALL PRIVILEGES ON `hellodb`.* TO ‘wang’@’192.168.%.%’

取消wang@192.168.%.% 的delete权限

MariaDB [(none)]> revoke delete on hellodb.* from wang@’192.168.%.%’;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for wang@’192.168.%.%’;

+———————————————————————————————————————————————————————————————————————————–+

| Grants for wang@192.168.%.% |

| GRANT USAGE ON *.* TO ‘wang’@’192.168.%.%’ IDENTIFIED BY PASSWORD ‘*6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664’ |加上delete就是all的权限。

| GRANT SELECT, INSERT, UPDATE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `hellodb`.* TO ‘wang’@’192.168.%.%’

查看当前登录的账号的权限。

MariaDB [(none)]> show grants for current_user();

+—————————————————————————————————————————————-+

| Grants for root@localhost |

+—————————————————————————————————————————————-+

| GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ IDENTIFIED BY PASSWORD ‘*128977E278358FF80A246B5046F51043A2B1FCED’ WITH GRANT OPTION |

| GRANT PROXY ON ”@” TO ‘root’@’localhost’ WITH GRANT OPTION

这些授权放在什么地方呢？

其中列的授权放在columns_priv ，表权限存放在tables_priv中。

MariaDB [(none)]> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| hellodb |

| mysql |

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]> show tables;

+—————————+

| Tables_in_mysql |

+—————————+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+—————————+

24 rows in set (0.00 sec)

MariaDB [mysql]> select * from columns_priv ;

+——+———+———+————+————-+———————+————-+

+——+———+———+————+————-+———————+————-+

| % | hellodb | laowang | students | age | 2018-06-08 20:48:20 | Select |

+——+———+———+————+————-+———————+————-+

2 rows in set (0.01 sec)

MariaDB [mysql]> select * from columns_priv\G

*************************** 1. row ***************************

Host: %

Db: hellodb

User: laowang

Table_name: students

Column_name: age

Timestamp: 2018-06-08 20:48:20

Column_priv: Select

*************************** 2. row ***************************

Host: %

Db: hellodb

User: laowang

Table_name: students

Column_name: name

Timestamp: 2018-06-08 20:48:20

Column_priv: Select

2 rows in set (0.00 sec)

表权限存放的地方

MariaDB [mysql]> select * from tables_priv\G

*************************** 1. row ***************************

Host: %

Db: hellodb

User: laowang

Table_name: students

Grantor: root@localhost

Timestamp: 2018-06-08 20:48:20

Table_priv:

Column_priv: Select

这些都存放在musql数据库中将来备份数据的时候应该将mysql数据库也同时备份。也可以直接通过修改表的方法添加权限用insert语句

授权

 回收授权：REVOKE priv_type [(column_list)] [, priv_type

[(column_list)]] … ON [object_type] priv_level FROM user [, user] …

示例：REVOKE DELETE ON testdb.* FROM ‘testuser’@’%‘

 查看指定用户获得的授权：

Help SHOW GRANTS

SHOW GRANTS FOR ‘user’@’host’;

SHOW GRANTS FOR CURRENT_USER[()];

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

(1) GRANT或REVOKE等执行权限操作会保存于系统表中，MariaDB的服务进程通常会自

动重读授权表，使之生效

(2) 对于不能够或不能及时重读授权表的命令，可手动让MariaDB的服务进程重读授权

表：

mysql> FLUSH PRIVILEGES;

用户通过连接模块连接mysql，每连接一个用户就要占用一个线程，mysql有一个线程池。

transactions （事务）

locking granulanty 锁级别，如果俩个人同时修改一张表在myisam中实现不了。在innodb可以。表级锁，影响迸发访问量

MVCC：多版本迸发控制机制，多个用户同时修改数据而不产生影响

students表后两个列是系统自动添加的创建版本，删除版本

id name createverion deleteversion

1 a 事务的编号1 5

2 b 2 事务的编号2

3 c 3 3

4 d 6 4

事务4只能看到1,3，因为4还没有发生2已经被删除

full-text search indexes 全文索引

clustered indexes 聚簇索引。

data caches 数据缓存

froreign key support 外键

查看系统支持的存储引擎

MariaDB [(none)]> show engines\G

MariaDB的特性

插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特

性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是

MYSQL默认引擎

MyISAM ==> Aria

InnoDB ==> XtraDB

存储引擎比较：

https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-

engines.html

单进程，多线程

诸多扩展和新特性

提供了较多测试组件

开源

存储引擎

MyISAM引擎特点：

不支持事务

表级锁定

读写相互阻塞，写入不能读，读时不能写

只缓存索引

不支持外键约束

不支持聚簇索引

读取数据较快，占用资源较少

不支持MVCC（多版本并发控制机制）高并发

崩溃恢复性较差

MySQL5.5.5前默认的数据库引擎

存储引擎

适用场景：只读（或者写较少）、表较小（可以接受长时间进行修复操作）

MyISAM引擎文件：

tbl_name.frm: 表格式定义

tbl_name.MYD: 数据文件

tbl_name.MYI: 索引文件

存储引擎是和表相关的而不是和库相关的，创建表的时候需要写存储引擎

存储引擎

InnoDB引擎特点：

支持事务，适合处理大量短期事务

行级锁

读写阻塞与事务隔离级别相关

可缓存数据和索引

支持聚簇索引

崩溃恢复性更好

支持MVCC高并发

从MySQL5.5后支持全文索引

从MySQL5.5.5开始为默认的数据库引擎

存储引擎

InnoDB数据库文件

所有InnoDB表的数据和索引放置于同一个表空间中

表空间文件：datadir定义的目录下

数据文件：ibddata1, ibddata2, …

每个表单独使用一个表空间存储表的数据和索引

启用：innodb_file_per_table=ON

两类文件放在数据库独立目录中

数据文件(存储数据和索引)：tb_name.ibd

表格式定义：tb_name.frm

每个表单独使用一个表空间存储表的数据和索引

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

[mysqld]

innodb_file_per_table=ON

[root@centos7 ~]# vim hellodb_innodb.sql

[root@centos7 ~]# mysql -uroot -pcentos < hellodb_innodb.sql

[root@centos7 ~]# ll /var/lib/mysql/hellodb2

total 1432

-rw-rw—- 1 mysql mysql 8636 Jun 9 14:10 classes.frm

-rw-rw—- 1 mysql mysql 98304 Jun 9 14:10 classes.ibd

-rw-rw—- 1 mysql mysql 8630 Jun 9 14:10 coc.frm

-rw-rw—- 1 mysql mysql 98304 Jun 9 14:10 coc.ibd

查看innodb_file_per_table的状态

MariaDB [(none)]> show variables like ‘innodb_file%’;

+————————–+———-+

| Variable_name | Value |

+————————–+———-+

| innodb_file_format | Antelope |

| innodb_file_format_check | ON |

| innodb_file_format_max | Antelope |

| innodb_file_per_table | ON |

其它存储引擎

Performance_Schema:Performance_Schema数据库

Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似

数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎

MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表

进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data

Base)环境，如数据仓库

Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持

SELECT和INSERT操作；支持行级锁和专用缓存区

Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一

个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而

后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务

器创建一个逻辑数据库。非常适合分布式或数据集市环境

其它数据库引擎

BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性

Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的

应用程序，这类查找需求还要求具有最高的正常工作时间和可用性

CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用

CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换

BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功

能可用于分布式数据库设计，数据自动复制，但不是本地存储

example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数

据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

数据自动复制，从服务器应该使用BLACKHOLE ：黑洞存储引擎

其它存储引擎

MariaDB支持的其它存储引擎：

OQGraph

SphinxSE

TokuDB

Cassandra

CONNECT

SQUENCE

管理存储引擎

查看mysql支持的存储引擎:

show engines;

查看当前默认的存储引擎:

show variables like ‘%storage_engine%’;

设置默认的存储引擎：

vim /etc/my.conf

[mysqld]

default_storage_engine= InnoDB;

管理存储引擎

查看库中所有表使用的存储引擎

Show table status from db_name;

查看库中指定表的存储引擎

show table status like ‘tb_name‘;

show create table tb_name;

设置表的存储引擎：

CREATE TABLE tb_name(… ) ENGINE=InnoDB;

ALTER TABLE tb_name ENGINE=InnoDB;

MySQL中的系统数据库

mysql数据库：是mysql的核心数据库，类似于sql server中的master库，主要

负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理

信息

PERFORMANCE_SCHEMA:MySQL 5.5开始新增的数据库，主要用于收集数据

库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不

能创建存储引擎为PERFORMANCE_SCHEMA的表

information_schema数据库:MySQL 5.0之后产生的，一个虚拟数据库，物理

上并不存在。information_schema数据库类似与“数据字典”，提供了访问数

据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限

（更加细化的访问方式）

服务器配置

mysqld选项，服务器系统变量和服务器状态变量

https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-

tables.html

https://mariadb.com/kb/en/library/full-list-of-mariadb-options-

system-and-status-variables/

获取运行中的mysql进程使用各服务器参数及其值

mysql> SHOW GLOBAL VARIABLES;

mysql> SHOW [SESSION] VARIABLES;

注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且只能通

过修改配置文件，并重启服务器程序生效；有些参数作用域是全局的，且不可改变；

有些可以为每个用户提供单独（会话）的设置

查看mysql的多有选项

[root@centos7 ~]# /usr/libexec/mysqld –help –verbose

查看系统变量

MariaDB [(none)]> show variables;

服务器系统变量：分全局和会话两种

服务器状态变量：分全局和会话两种状态变量就是统计系统状态的。只读的。如cmd_delete 是查看删除次数的。

获取运行中的mysql进程使用各种服务器参数及其值

show global variables；

show [session]variables;

设置服务器系统变量三种方法：

在命令行中设置:

shell> ./mysqld_safe –aria_group_commit=”hard“

在配置文件my.cnf中设置：

aria_group_commit = “hard”

在mysql客户端使用SET命令：

SET GLOBAL aria_group_commit=”hard”;

–skip-name-resolve 反向地址解析可以加到文件中。

SET GLOBAL –skip-name-resolve不可以直接设置，只能在文件中更改，然后从新启动服务生效。

服务器端设置

修改服务器变量的值：

mysql> help SET

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

mysql> SET GLOBAL system_var_name=value;

mysql> SET @@global.system_var_name=value;

修改会话变量：

mysql> SET [SESSION] system_var_name=value;

mysql> SET @@[session.]system_var_name=value;

状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

mysql> SHOW GLOBAL STATUS;

mysql> SHOW [SESSION] STATUS;

MariaDB [(none)]> show global variables like ‘skip_name_resolve’;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| skip_name_resolve | OFF |

MariaDB [(none)]> show variables like ‘sql_log_bin’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| sql_log_bin | ON |

MariaDB [(none)]> show variables like ‘datadir’;

+—————+—————–+

| Variable_name | Value |

+—————+—————–+

| datadir | /var/lib/mysql/ | 数据的路径

MariaDB [(none)]> show variables like ‘basedir’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| basedir | /usr | 软件安装的路径

MariaDB [(none)]> set datadir=’/data’;

ERROR 1238 (HY000): Variable ‘datadir’ is a read only variable

SQL_MODE

 服务器变量SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前

会话的设置

 常见MODE:

 NO_AUTO_CREATE_USER

禁止GRANT创建密码为空的用户

 NO_AUTO_VALUE_ON_ZERO

在自增长的列中插入0或NULL将不会是下一个自增长值

 NO_BACKSLASH_ESCAPES

反斜杠“\”作为普通字符而非转义字符

 PAD_CHAR_TO_FULL_LENGTH

启用后，对于CHAR类型将不会截断空洞数据

 PAD_CHAR_TO_FULL_LENGTH

启用后，对于CHAR类型将不会截断空洞数据

 PIPES_AS_CONCAT

将”||”视为连接操作符而非“或运算符 ”

MariaDB [(none)]> show variables like ‘sql_mode’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| sql_mode | |

MariaDB [hellodb]> create table test (id int,name char(5));

Query OK, 0 rows affected (0.01 sec)

MariaDB [hellodb]> select * from test;

Empty set (0.00 sec)

MariaDB [hellodb]> insert test values(1,’abc’),(2,’wangxiaocun’);

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 1

MariaDB [hellodb]> select * from test;

+——+——-+

| id | name |

+——+——-+

| 1 | abc |

| 2 | wangx |

在报警时赶紧查看，可以看到报警。

MariaDB [hellodb]> show warnings;

MariaDB [hellodb]> set sql_mode=’traditional’;

MariaDB [hellodb]> insert test values(3,’lalaxiaocun’);

ERROR 1406 (22001): Data too long for column ‘name’ at row 1

MariaDB [hellodb]> show variables like ‘sql_mode’;

+—————+——————————————————————————————————————————————————+

| Variable_name | Value |

+—————+——————————————————————————————————————————————————+

| sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

mysql的通讯流程

对于不同的语言有不同的接口用来连接mysql

不是任何人都能连接的，有connection pool 连接池找一个空闲的线程为用户提供服务，如果结束，则会把之前的信息清空，让后给下一个用户使用。连接限制：迸发连接数也就在几百。（瓶颈所在）

在sql interface 用户执行增删改查命令时检查语法正确性。parser 解析器：能不能执行之前的语句要看用户有没有权限，如果能执行，就将语言翻译成sql的二进制语言进行执行，在执行的时候在optimizer 来优化命令的执行，（类似于路由选择最佳路径）在执行计划可以看到。读取和写入都有相应的cache和buffers （在不同的引擎中可能不同）通过引擎，引擎发指令给内核，内核将数据存放到磁盘上。

mysql的块是磁盘块的整数倍默认是16k

查询缓存

查询缓存（ Query Cache ）原理：

缓存SELECT操作或预处理查询的结果集和SQL语句，当有新的SELECT语句或预

处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓

存的SQL语句，是否完全一样，区分大小写

优缺点

不需要对SQL语句做任何解析和执行，当然语法解析必须通过在先，直接从

Query Cache中获得查询结果，提高查询性能

查询缓存的判断规则，不够智能，也即提高了查询缓存的使用门槛，降低其效率；

查询缓存的使用，会增加检查和清理Query Cache中记录集的开销

查询缓存

 哪些查询可能不会被缓存

 查询语句中加了SQL_NO_CACHE参数如果查询的表太大适合使用SQL_NO_CACHE可以提高性能

 查询语句中含有获得值的函数，包含自定义函数，如：NOW()

CURDATE()、GET_LOCK()、RAND()、CONVERT_TZ()等

 对系统数据库的查询：mysql、information_schema 查询语句中使用SESSION级别变量

或存储过程中的局部变量

 查询语句中使用了LOCK IN SHARE MODE、FOR UPDATE的语句查询语句中类似

SELECT …INTO 导出数据的语句

 对临时表的查询操作CREATE [TEMPORARY] ；存在警告信息的查询语句；不涉及任何表或视图的查询语句；

某用户只有列级别权限的查询语句

 事务隔离级别为Serializable时，所有查询语句都不能缓存

MariaDB [hellodb]> select now(); 不需要缓存，因为时间在一直改变

+———————+

| now() |

+———————+

| 2018-06-09 17:10:39 |

查询缓存

查询缓存相关的服务器变量

 query_cache_min_res_unit: 查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，

但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足

 query_cache_limit：单个查询结果能缓存的最大值，默认为1M，

对于查询结果过大而无法缓存的语句，建议使用 SQL_NO_CACHE

 query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值

40KB，低于此值有警报

 query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返

回结果，默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则

表示不允许

 query_cache_type: 取值为ON, OFF, DEMAND

参看： https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html

MariaDB [(none)]> show variables like ‘query_cache_min_res_unit’;

+————————–+——-+

| Variable_name | Value |

+————————–+——-+

| query_cache_min_res_unit | 4096 |

缓存总的可用空间

MariaDB [(none)]> show variables like ‘query_cache_size’;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| query_cache_size | 0 |

缓存功能是否开启

MariaDB [(none)]> show variables like ‘query_cache_type’;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| query_cache_type | ON |

查询缓存

SELECT语句的缓存控制

SQL_CACHE: 显式指定存储查询结果于缓存之中

SQL_NO_CACHE: 显式查询结果不予缓存

query_cache_type参数变量：

query_cache_type的值为OFF或0时，查询缓存功能关闭

query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓

存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，

此为默认值

 query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定

SQL_CACHE的SELECT语句才会缓存；其它均不予缓存

查看官方文档： https://mariadb.com/kb/en/library/server-system-variables/

#query_cache_type

查询缓存

 查询缓存相关的状态变量

SHOW GLOBAL STATUS LIKE ‘Qcache%’;

 Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数

 Qcache_free_memory：处于空闲状态的 Query Cache 内存总量

 Qcache_hits：Query Cache 命中次数

 Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数

 Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要删除老的 Query Cache 以给新

的 Cache 对象使用的次数

 Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于

query_cache_type 设置的不会被 Cache 的 SQL语句

 Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量

 Qcache_total_blocks：Query Cache 中总的 Block

查询和缓存相关的数据

MariaDB [(none)]> show status like ‘Qcache%’\G

*************************** 1. row ***************************

Variable_name: Qcache_free_blocks 可用的块

Value: 0

*************************** 2. row ***************************

Variable_name: Qcache_free_memory 剩余空间

Value: 0

*************************** 3. row ***************************

Variable_name: Qcache_hits 命中率

Value: 0

*************************** 4. row ***************************

Variable_name: Qcache_inserts 增加了几次记录

Value: 0

*************************** 5. row ***************************

Variable_name: Qcache_lowmem_prunes

Value: 0

*************************** 6. row ***************************

Variable_name: Qcache_not_cached

Value: 0

*************************** 7. row ***************************

Variable_name: Qcache_queries_in_cache

Value: 0

*************************** 8. row ***************************

Variable_name: Qcache_total_blocks

Value: 0

命中率

命中率和内存使用率估算

query_cache_min_res_unit ≈(query_cache_size – Qcache_free_memory) /

Qcache_queries_in_cache

查询缓存命中率 ≈ (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

查询缓存内存使用率 ≈ (query_cache_size – qcache_free_memory) /

query_cache_size * 100%

查询缓存中内存块的最小分配单位query-cache_min_res_unit :

(query_cache_size – Qcache_free_memery)/Qcache_queries_in_chche

查询执行了几次select命令

MariaDB [hellodb]> show status like ‘com_select%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Com_select | 3 |

MariaDB [hellodb]> select * from students;

MariaDB [hellodb]> show status like ‘com_select%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Com_select | 4 |

InnoDB存储引擎

InnoDB存储引擎的缓冲池:

通常InnoDB存储引擎缓冲池的命中不应该小于99%

查看相关状态变量：

show global status like ‘innodb%read%’\G

 Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数

 Innodb_buffer_pool_read_ahead: 预读的次数

 Innodb_buffer_pool_read_ahead_evicted: 预读页，但是没有读取就从缓冲池中被替换

的页数量，一般用来判断预读的效率

 Innodb_buffer_pool_read_requests: 从缓冲池中读取页次数

 Innodb_data_read: 总共读入的字节数

 Innodb_data_reads: 发起读取请求的次数，每次读取可能需要读取多个页

innodb 存储引擎的缓存

MariaDB [hellodb]> show global status like ‘innodb%read%’\G

*************************** 1. row ***************************

Variable_name: Innodb_buffer_pool_read_ahead

Value: 0

*************************** 2. row ***************************

Variable_name: Innodb_buffer_pool_read_ahead_evicted

Value: 0

*************************** 3. row ***************************

Variable_name: Innodb_buffer_pool_read_ahead_rnd

Value: 0

*************************** 4. row ***************************

Variable_name: Innodb_buffer_pool_read_requests

Value: 2090

*************************** 5. row ***************************

Variable_name: Innodb_buffer_pool_reads

Value: 213

*************************** 6. row ***************************

Variable_name: Innodb_data_pending_reads

Value: 0

*************************** 7. row ***************************

Variable_name: Innodb_data_read

Value: 5673472

*************************** 8. row ***************************

Variable_name: Innodb_data_reads

Value: 224

*************************** 9. row ***************************

Variable_name: Innodb_master_thread_1_second_loops

Value: 18

*************************** 10. row ***************************

Variable_name: Innodb_master_thread_10_second_loops

Value: 1

*************************** 11. row ***************************

Variable_name: Innodb_master_thread_background_loops

Value: 10

*************************** 12. row ***************************

Variable_name: Innodb_master_thread_main_flush_loops

Value: 10

*************************** 13. row ***************************

Variable_name: Innodb_master_thread_sleeps

Value: 18

*************************** 14. row ***************************

Variable_name: Innodb_pages_read

Value: 212

*************************** 15. row ***************************

Variable_name: Innodb_read_views_memory

Value: 184

*************************** 16. row ***************************

Variable_name: Innodb_rows_read

Value: 52

索引

索引是特殊数据结构：定义在查找时作为查找条件的字段，索引是现在存储引擎。

优点：提高查询速度，索引可以减低服务需要扫描的数据量，减少io的次数，索引可以帮助服务器避免排序和使用临时表，索引可以帮助将随机i/o（全表扫描）转为顺序i/o 。缺点：占用额外空间，影响插入速度

索引实现在存储引擎

索引类型：

聚簇（集）索引、非聚簇索引：数据是否与索引存储在一起，排序的顺序是否一致。

主键索引、辅助索引

稠密索引、稀疏索引：是否索引了每一个数据项

B+ TREE、HASH、R TREE

简单索引（一个字段上建立所以）、组合索引（多个字段上建立索引）

左前缀索引：取前面的字符做索引

覆盖索引：从索引中即可取出要查询的数据，性能高

二级索引是非聚集索引，其表中不存放数据，而是存放着指向的主键

B-Tree索引

B+ Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左

前缀索引，适合查询范围类的数据

可以使用B-Tree索引的查询类型：

全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30

匹配最左前缀：即只使用索引的第一列，如：姓wang

匹配列前缀：只匹配一列值开头部分，如：姓以w开头的

匹配范围值：如：姓ma和姓wang之间

精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的

只访问索引的查询

B-Tree索引

B-Tree索引的限制：

如果不从最左列开始，则无法使用索引：如：查找名为xiaochun，或姓为g结

尾

不能跳过索引中的列：如：查找姓wang,年龄30的，只能使用索引第一列

如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如：

姓wang,名x%,年龄30，只能利用姓和名上面的索引

特别提示：

索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引

为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同

类型的查询需求

Hash索引

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索

引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好

只有Memory存储引擎支持显式hash索引

适用场景：

只支持等值比较查询，包括=, IN(), <=>

不适合使用hash索引的场景：

不适用于顺序查询：索引存储顺序的不是值的顺序

不支持模糊匹配

不支持范围查询

不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

索引

空间索引（R-Tree）：

MyISAM支持空间索引，可以使用任意维度组合查询，使用特有的函数访问，

常用于做地理数据存储，使用不多

全文索引(FULLTEXT)：

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎

索引优点：

索引可以降低服务需要扫描的数据量，减少了IO次数

索引可以帮助服务器避免排序和使用临时表

索引可以帮助将随机I/O转为顺序I/O

MariaDB [(none)]> show global variables like ‘query_cache_%’;

+——————————+———+

| Variable_name | Value |

+——————————+———+

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 0 |

| query_cache_strip_comments | OFF |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

| query_cache_size | 0 | 因为缓存空间是0所以无法缓存。

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

query_cache_size=10M 给予10M缓存

[root@CENTOS7 ~]# systemctl restart mariadb

查看缓存的性能指标

MariaDB [(none)]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 10468296 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 1 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |

MaMariaDB [(none)]> use hellodb

riaDB [(none)]> use hellodb

第一次搜索

MariaDB [hellodb]> select * from students;

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 10466192 |

| Qcache_hits | 0 |

| Qcache_inserts | 1 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 2 |

| Qcache_queries_in_cache | 1 |

| Qcache_total_blocks | 4 |

第二次搜索

MariaDB [hellodb]> select * from students;

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 10466192 |

| Qcache_hits | 1 |

| Qcache_inserts | 1 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 2 |

| Qcache_queries_in_cache | 1 |

| Qcache_total_blocks | 4 |

搜索8次

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 10466192 |

| Qcache_hits | 7 |

| Qcache_inserts | 1 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 2 |

| Qcache_queries_in_cache | 1 |

| Qcache_total_blocks | 4 |

虽然是一样的命令，但是一旦用大写，那么就是新的缓存。

MariaDB [hellodb]> SELECT * from students;

MariaDB [hellodb]> show global status like ‘Qcache%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 10464600 |

| Qcache_hits | 7 |

| Qcache_inserts | 2 |

| Qcache_lowmem_prunes | 0 |

| Qcache_not_cached | 2 |

| Qcache_queries_in_cache | 2 |

| Qcache_total_blocks | 6 |

索引

高性能索引策略：

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一

部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较

符号的一侧

左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值

多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

冗余和重复索引：（A），（A，B）即为冗余索引

不好的索引使用策略，建议扩展索引，而非冗余

myisan的数据和索引是分开存放的

innodb的数据和索引是存放在一起的都放在ibdata1这个文件中

-rw-rw—- 1 mysql mysql 18874368 Jun 10 12:10 ibdata1

主键索引

如果经常用名字进行搜索，那么应该用名字建立索引。名字索引是指向主键索引的，并不存放数据

复合索引有先后顺序，按左侧的排序只有当左侧相同时在按右侧排序指针指向主键搜索的时候要使用索引需要搜索左侧的。不能搜索后续的。

索引

高性能索引策略：

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一

部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较

符号的一侧

左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值

多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

冗余和重复索引：（A），（A，B）即为冗余索引

不好的索引使用策略，建议扩展索引，而非冗余

索引优化建议

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，

此列在使用时也不会使用索引

尽量使用短索引，如果可以，应该制定一个前缀长度

对于经常在where子句使用的列，最好设置索引

对于有多个列where或者order by子句，应该建立复合索引

对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

尽量不要在列上进行运算（函数操作和表达式操作）

尽量不要使用not in和<>操作

多表连接时应该小表join大表，a10条记录b1000条记录，那么在查询的时候会循环10次反之1000次

在千万级分页时使用limit

对于经常使用的查询，可以开启缓存

大部分情况连接效率远大于子查询

管理索引

创建索引：

CREATE INDEX index_name ON tbl_name (index_col_name,…字段);

help CREATE INDEX

删除索引：

DROP INDEX index_name ON tbl_name;

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name

查看students表是否有索引。

MariaDB [hellodb]> show indexes from students\G

*************************** 1. row ***************************

Table: students

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: StuID

Collation: A

Cardinality: 25

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

explain 查看select语句的执行情况rows25表示查询了25行就是全表扫描

MariaDB [hellodb]> explain select * from students where age = 20\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 25

Extra: Using where

对students表的age列建立索引

MariaDB [hellodb]> create index index_age on students(age);

这时students表有俩个索引

MariaDB [hellodb]> show indexes from students\G

*************************** 1. row ***************************

Table: students

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: StuID

Collation: A

Cardinality: 25

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: students

Non_unique: 1

Key_name: index_age

Seq_in_index: 1

Column_name: Age

Collation: A

Cardinality: 25

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

这时在搜索只搜索了2行

MariaDB [hellodb]> explain select * from students where age = 20\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: ref

possible_keys: index_age

key: index_age

key_len: 1

ref: const

rows: 2

Extra:

对姓名再次建立索引

MariaDB [hellodb]> create index index_name on students(name);

MariaDB [hellodb]> explain select * from students where name like ‘b%’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: range

possible_keys: index_name

key: index_name

key_len: 152

ref: NULL

rows: 1

Extra: Using index condition

1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from students where name like ‘%b’\G %b这种写法无法使用索引

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 25

Extra: Using where

查看索引的使用

set global userstat =1；

show index_statistics

MariaDB [hellodb]> set global userstat=1

MariaDB [hellodb]> show variables like ‘userstat’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| userstat | ON |

MariaDB [hellodb]> select * from students where age=20;

age这个索引被使用了。

MariaDB [hellodb]> show index_statistics;

+————–+————+————+———–+

+————–+————+————+———–+

当删除表中的一些行时，索引就会变得不紧凑，产生很多磁盘碎片，这时可以使用OPTIMIZE TABLE tb_name 来清理磁盘碎片使索引变得紧凑。

EXPLAIN

通过EXPLAIN来分析索引的有效性：

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

输出信息说明：参考 https://dev.mysql.com/doc/refman/5.7/en/explain-

output.html

id: 当前查询语句中，每个SELECT语句的编号id表示有几个select语句，当有子查询时有俩条记录

复杂类型的查询有三种：

简单子查询

用于FROM中的子查询

联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

EXPLAIN

select_type：

简单查询为SIMPLE

复杂查询：

SUBQUERY: 简单子查询

PRIMARY:最外面的SELECT

DERIVED: 用于FROM中的子查询

UNION：UNION语句的第一个之后的SELECT语句

UNION RESULT: 匿名临时表

table：SELECT语句关联到的表

EXPLAIN

type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以

下顺序，性能从低到高

 ALL: 全表扫描

 index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用

覆盖索引，而非全表扫描

 range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束

于另一点

 ref: 根据索引返回表中匹配某单个值的所有行

 eq_ref：仅返回一个行，但与需要额外与某个参考值做比较

 const, system: 直接返回单个行

possible_keys：查询可能会用到的索引

key: 查询中使用到的索引

key_len: 在索引使用的字节数

EXPLAIN

ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值

rows：MySQL估计为找所有的目标行而需要读取的行数

Extra：额外信息

Using index：MySQL将会使用覆盖索引，以避免访问表

Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤

Using temporary：MySQL对结果排序时会使用临时表

Using filesort：对结果使用一个外部索引排序

查看students表中有几个索引

MariaDB [hellodb]> show indexes from students\G

删除索引

MariaDB [hellodb]> drop index index_name on students;

MariaDB [hellodb]> drop index index_age on students;

创建复合索引

MariaDB [hellodb]> create index index_name_age on students(name,age);

MariaDB [hellodb]> show index from students\G

*************************** 2. row ***************************

Table: students

Non_unique: 1

Key_name: index_name_age

Seq_in_index: 1

Column_name: Name

Collation: A

Cardinality: 25

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: students

Non_unique: 1

Key_name: index_name_age

Seq_in_index: 2

Column_name: Age

Collation: A

Cardinality: 25

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

MariaDB [hellodb]> explain select * from students where name like ‘b%’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: range

possible_keys: index_name_age

key: index_name_age

key_len: 152

ref: NULL

rows: 1

Extra: Using index condition

MariaDB [hellodb]> explain select * from students where name like ‘x%’\G ？？？？？

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: ALL

possible_keys: index_name_age

key: NULL

key_len: NULL

ref: NULL

rows: 25

Extra: Using where

1 row in set (0.00 sec)

MariaDB [hellodb]> explain select * from students where name like ‘s%’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: students

type: range

possible_keys: index_name_age

key: index_name_age

key_len: 152

ref: NULL

rows: 3

Extra: Using index condition

建立唯一键索引。

MariaDB [hellodb]> create unique index uni_index_name on students(name);

MariaDB [hellodb]> show index from students\G

Table: students

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: StuID

Collation: A

Cardinality: 25

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

限定索引的字符数支取name的俩个字符

MariaDB [hellodb]> create index index_name on students (name(2));

MariaDB [hellodb]> show index from students\G

Table: students

Non_unique: 1

Key_name: index_name

Seq_in_index: 1

Column_name: Name

Collation: A

Cardinality: 25

Sub_part: 2 两个字符

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

utf8的类型会一个占用4个字节

而我们定义的是name（2）两个字符，所以是8个字节。

QL语句性能优化

查询时，能不要*就不用*，尽量写全字段名

大部分情况连接效率远大于子查询

多表连接时，尽量小表驱动大表，即小表 join 大表

在千万级分页时使用limit

对于经常使用的查询，可以开启缓存

多使用explain和profile分析查询语句

查看慢查询日志，找出执行时间长的sql语句优化

并发控制

锁粒度：

表级锁

行级锁

锁：

读锁：共享锁，只读不可写，多个读互不阻塞，

写锁：独占锁,排它锁，一个写锁会阻塞其它读和它锁

实现

存储引擎：自行实现其锁策略和锁粒度

服务器级：实现了锁，表级锁；用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁

显式锁：用户手动请求

并发控制

锁策略：在锁粒度及数据安全性寻求的平衡机制

显示使用锁

 LOCK TABLES

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] …

lock_type: READ ， WRITE

UNLOCK TABLES 解锁

 FLUSH TABLES tb_name[,…] [WITH READ LOCK]

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

 SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

查询时加写或读锁

对students表添加读锁，自己能读不能写，别的用户也只能读不能写。

MariaDB [hellodb]> lock tables students read;

MariaDB [hellodb]> update students set classid=2 where stuid=24;

ERROR 1099 (HY000): Table ‘students’ was locked with a READ lock and can’t be updated

别的用户会一直卡在这个界面无法修改，当解除锁后会立即修改

MariaDB [hellodb]> update students set classid=2 where stuid=24;

解除锁

MariaDB [hellodb]> unlock tables;

别的用户的修改已经执行

MariaDB [hellodb]> update students set classid=2 where stuid=24;

Query OK, 1 row affected (1 min 50.52 sec)

Rows matched: 1 Changed: 1 Warnings: 0

添加写锁，自己可以读不可以写，别的用户不能读也不能写，如果别的用户可以查看是因为有缓存。

MariaDB [hellodb]> lock tables students write;

解锁

MariaDB [hellodb]> unlock tables;

清除缓存

MariaDB [hellodb]> flush tables students;

事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元

事务日志：记录事务信息，实现undo,redo等故障恢复功能

ACID特性：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失

败后回滚

C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状

态

I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务

所见；隔离有多种隔离级别，实现并发

D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

对数据增删改才是事务，查询不算事务

本文来自投稿，不代表Linux运维部落立场，如若转载，请注明出处：http://www.178linux.com/100803

相关推荐

MySQL

正则表达式简单明了的学法

第5周

正则表达式浅解

第七周作业(占位)

马哥教育 第一周培训4.1

马哥教育第一周培训4.1