MySQL（mariadb）

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系列

• 官方网址：
  • https://www.mysql.com/
  • http://mariadb.org/
  • https://www.percona.com
• 官方文档 • https://dev.mysql.com/doc/
  • https://mariadb.com/kb/en/
  • https://www.percona.com/software/mysql-database/percona-server
• 版本演变：
  • MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 -->8.0
  • MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3

MYSQL的特性

• 插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特 性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
  • MyISAM ==> Aria
  • InnoDB ==> XtraDB
• 单进程，多线程
• 诸多扩展和新特性
• 提供了较多测试组件
• 开源

安装MYSQL

• Mariadb安装方式：
  • 1、源代码：编译安装
  • 2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
  • 3、程序包管理器管理的程序包
  CentOS 安装光盘
  项目官方：https://downloads.mariadb.org/mariadb/repositories/
  国内镜像：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadbx.y.z/yum/centos/7/x86_64/

RPM包安装MySQL

• RPM包安装
  CentOS 7：安装光盘直接提供
  • mariadb-server 服务器包
  • mariadb 客户端工具包
  CentOS 6
  • mysql-server 服务器包

• 提高安全性
  • mysql_secure_installation
  • 设置数据库管理员root口令
  • 禁止root远程登录
  • 删除anonymous用户帐号
  • 删除test数据库

MySQL程序

• 客户端程序：
  • mysql: 交互式的CLI工具
  • mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所 有数据转换成insert等写操作语句保存文本文件中
  • mysqladmin：基于mysql协议管理mysqld
  • mysqlimport：数据导入工具
• MyISAM存储引擎的管理工具：
  • myisamchk：检查MyISAM库
  • myisampack：打包MyISAM表，只读
• 服务器端程序
  • mysqld_safe
  • mysqld
  • mysqld_multi 多实例 ，示例：mysqld_multi --example
• 脚本模式：
  • mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
  • mysql> source /path/from/somefile.sql

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 获取程序默认使用的配置

socket地址

• 服务器监听的两种socket地址：
  • ip socket: 监听在tcp的3306端口，支持远程通信
  • unix sock: 监听在sock文件上，仅支持本机通信
  • 如：/var/lib/mysql/mysql.sock) 说明：host为localhost,127.0.0.1时自动使用unix sock

执行命令

• 运行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
  说明：_和- 相同
  1，ON，TRUE意义相同， 0，OFF，FALSE意义相同
• 配置文件：
  后面覆盖前面的配置文件，顺序如下：
  • 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 选项

通用二进制格式安装过程

• 二进制格式安装过程
  (1) 准备用户
  • groupadd -r -g 306 mysql
  • useradd -r -g 306 -u 306 –d /data/mysql mysql
  (2) 准备数据目录，建议使用逻辑卷
  • mkdir /data/mysql
  • chown mysql:mysql /data/mysql
  (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 = /data/mysql
  • innodb_file_per_table = on
  • skip_name_resolve = on 禁止主机名解析，建议使用 (5)创建数据库文件
  • cd /usr/local/mysql/
  • ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql (6)准备服务脚本，并启动服务
  • cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld
  • chkconfig --add mysqld
  • service mysqld start
  (7)PATH路径
  • echo ‘PATH=/user/local/mysql/bin:$PATH’ > /etc/profile.d/mysql (8)安全初始化
  • /user/local/mysql/bin/mysql_secure_installation

源码编译安装mariadb

• 安装包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boostdevel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssldevel libevent-devel libaio-devel

• 做准备用户和数据目录 • useradd –r –s /sbin/nologin –d /data/mysql/ mysql • mkdir /data/mysql • chown mysql.mysql /data/mysql • tar xvf mariadb-10.2.18.tar.gz
• cmake 编译安装 cmake的重要特性之一是其独立于源码(out-of-source)的编译功能，即编译工作可以在 另一个指定的目录中而非源码目录中进行，这可以保证源码目录不受任何一次编译的影 响，因此在同一个源码树上可以进行多次不同的编译，如针对于不同平台编译 编译选项:https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html
  cd mariadb-10.2.18/

cmake . \
-DCMAKE_INSTALL_PREFIX=/app/mysql \
-DMYSQL_DATADIR=/data/mysql/ \
-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=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci
make && make install

• 提示：如果出错，执行rm -f CMakeCache.txt

• 准备环境变量
  • 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/mysql/ --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

关系型数据库的常见组件

• 组件
  • 数据库：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-1986, SQL-1989, SQL-1992, SQL-1999, SQL-2003 SQL-2008, SQL-2011

SQL语言规范

• 在数据库系统中，SQL语句不区分大小写(建议用大写)
• SQL语句可单行或多行书写，以“;”结尾
• 关键词不能跨多行或简写
• 用空格和缩进来提高语句的可读性
• 子句通常位于独立行，便于编辑，提高可读性
• 注释： • SQL标准：
  /*注释内容*/ 多行注释
  -- 注释内容 单行注释，注意有空格
  • MySQL注释：
  #
• 数据库的组件(对象)： 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
• 命名规则：
  • 必须以字母开头
  • 可包括数字和三个特殊字符（# _ $）
  • 不要使用MySQL的保留字
  • 同一database(Schema)下的对象不能同名

SQL语句分类

• SQL语句分类：
  • DDL: Data Defination Language 数据定义语言
  CREATE，DROP，ALTER
  • DML: Data Manipulation Language 数据操纵语言
  INSERT，DELETE，UPDATE
  • DCL：Data Control Language 数据控制语言
  GRANT，REVOKE，COMMIT，ROLLBACK
  • DQL：Data Query Language 数据查询语言
  SELECT

数据库操作

• 创建数据库：
  • 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;

表

• 表：二维关系
• 设计表：遵循规范
• 定义：字段，索引
  • 字段：字段名，字段数据类型，修饰符
  • 约束，索引：应该创建在经常用作查询条件的字段上
• 创建表：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是指表类型，也即在表创建时指明其使用的存储引擎，同一 库中不同表可以使用不同的存储引擎
  • 同一个库中表建议要使用同一种存储引擎类型
• 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
  SHOW COLUMNS FROM [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

数据类型

• 数据类型：
  • 数据长什么样
  • 数据需要多少空间来存放
• 系统内置数据类型和用户定义数据类型
• MySql支持多种列类型：
  • 数值类型
  • 日期/时间类型
  • 字符串(字符)类型 • https://dev.mysql.com/doc/refman/5.5/en/data-types.html
• 选择正确的数据类型对于获得高性能至关重要，三大原则：
  • 更小的通常更好，尽量使用可正确存储数据的最小数据类型
  • 简单就好，简单数据类型的操作通常需要更少的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 自动存储记录修改时间
  • YEAR(2), YEAR(4)：年份
  timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

修饰符

• 所有类型：
  • NULL 数据列可包含NULL值
  • NOT NULL 数据列不允许包含NULL值
  • DEFAULT 默认值
  • PRIMARY KEY 主键
  • UNIQUE KEY 唯一键
  • CHARACTER SET name 指定一个字符集
• 数值型
  • AUTO_INCREMENT 自动递增，适用于整数类型
  • UNSIGNED 无符号

表操作

• DROP TABLE [IF EXISTS] 'tbl_name';
• ALTER TABLE 'tbl_name'
  1.字段：
  • 添加字段：add ADD col1 data_type [FIRST|AFTER col_name]
  • 删除字段：drop
  • 修改字段： alter（默认值）,change（字段名）,modify（字段属性）
  2.索引:
  • 添加索引：add index
  • 删除索引：drop index
• 查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name;
• 查看帮助：Help ALTER TABLE

DML语句

• DML: INSERT, DELETE, UPDATE
• 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] ... ]

• 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 选项：-U|--safe-updates| --i-am-a-dummy
• DELETE:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
可先排序再指定删除的行数

• 注意：一定要有限制条件，否则将清空表中的所有数据 限制条件：
  • WHERE
  • LIMIT
• TRUNCATE TABLE tbl_name; 清空表

DQL语句

• SELECT

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]

• 字段显示可以使用别名：
  col1 AS alias1, col2 AS alias2, ...
• WHERE子句：指明过滤条件以实现“选择”的功能：
  • 过滤条件：布尔型表达式
  • 算术操作符：+, -, *, /, %
  • 比较操作符：=,<=>（相等或都为空）, <>, !=(非标准SQL), >, >=, <, <=
  • BETWEEN min_num AND max_num
  • IN (element1, element2, ...)
  • IS NULL
  • IS NOT NULL
• DISTINCT 去除重复列
  • SELECT DISTINCT gender FROM students;
• LIKE:
  • % 任意长度的任意字符
  • _ 任意单个字符
• RLIKE：正则表达式，索引失效，不建议使用
• REGEXP：匹配字符串可用正则表达式书写模式，同上
• 逻辑操作符：
  • NOT
  • AND
  • OR
  • XOR
• GROUP：根据指定的条件把查询结果进行“分组”以用于做“聚合”运算 avg(), max(), min(), count(), sum()
  • HAVING: 对分组聚合运算后的结果指定过滤条件
• ORDER BY: 根据指定的字段对查询结果进行排序
  • 升序：ASC
  • 降序：DESC
• LIMIT [[offset,]row_count]：对查询的结果进行输出行数数量限制
• 对查询结果中的数据请求施加“锁”
  • FOR UPDATE: 写锁，独占或排它锁，只有一个读和写
  • LOCK IN SHARE MODE: 读锁，共享锁，同时多个读

示例

• 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

SQL JOINS

多表查询

• 交叉连接：笛卡尔乘积
• 内连接：
  • 等值连接：让表之间的字段以“等值”建立连接关系；
  • 不等值连接
  • 自然连接:去掉重复列的等值连接
  • 自连接
• 外连接：
  • 左外连接：
  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 AgeFROM 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语句执行过程

视图

• 视图：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]

• 视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制

函数

• 函数：系统函数和自定义函数 系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summaryref.html
• 自定义函数 (user-defined function UDF)
  • 保存在mysql.proc表中
  • 创建UDF

CREATE [AGGREGATE] FUNCTIONfunction_name(parameter_name
type,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
runtime_body

• 说明： 参数可以有多个,也可以没有参数 必须有且只有一个返回值

自定义函数

• 创建函数 示例：无参UDF
  • CREATE FUNCTION simpleFun() RETURNS VARCHAR(20) RETURN "Hello World!”;
• 查看函数列表：
  • SHOW FUNCTION STATUS;
• 查看函数定义
  • SHOW CREATE FUNCTION function_name
• 删除UDF:
  • DROP FUNCTION function_name
• 调用自定义函数语法:
  • SELECT function_name(parameter_value,...)
• 示例：有参数UDF
  DELIMITER // CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) BEGIN DELETE FROM students WHERE stuid = uid; RETURN (SELECT COUNT(stuid) 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; SET a = x, b = y; RETURN a+b; END// DELIMITER ;
• 为变量赋值语法
  • 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//

存储过程

• 存储过程优势
  • 存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
  • 提高了运行速度
  • 同时降低网络数据传输量
• 存储过程与自定义函数的区别
  • 存储过程实现的过程要复杂一些,而函数的针对性较强
  • 存储过程可以有多个返回值,而自定义函数只有一个返回值
  • 存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用
• 存储过程：存储过程保存在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;
• 查看存储过程定义
  • 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;
• 创建含参存储过程：只有一个IN参数
  delimiter // CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED) BEGIN SELECT * FROM students WHERE stuid = uid; END// delimiter ;
  • 运行:call selectById(2);
• 示例
  delimiter // CREATE PROCEDURE dorepeat(n INT) BEGIN SET @i = 0; SET @sum = 0; REPEAT SET @sum = @sum+@i; SET @i = @i + 1; UNTIL @i > n END REPEAT; END// delimiter ;
  • 运行CALL dorepeat(100);
  • SELECT @sum;
• 创建含参存储过程:包含IN参数和OUT参数
  delimiter // CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED) BEGIN DELETE FROM students WHERE stuid = uid; SELECT row_count() into num; END// delimiter ;
  • 运行call deleteById(2,@Line);
  • SELECT @Line;
• 说明:创建存储过程deleteById,包含一个IN参数和一个OUT参数.调用时,传入删 除的ID和保存被修改的行数值的用户变量@Line,select @Line;输出被影响行数

流程控制

• 存储过程和函数中可以使用流程控制来控制语句的执行
• 流程控制：
  • 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_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (stu_id) ); 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';
• 修改密码：
  • mysql>SET PASSWORD FOR 'user'@'host' = PASSWORD(‘password');
  • mysql>UPDATE mysql.user SET password=PASSWORD('password') WHERE clause;
  • 此方法需要执行下面指令才能生效： mysql> FLUSH PRIVILEGES;
  #mysqladmin -u root -poldpass password ‘newpass’
• 忘记管理员密码的解决办法：
  • 启动mysqld进程时，为其使用如下选项： --skip-grant-tables --skip-networking
  • 使用UPDATE命令修改管理员密码
  • 关闭mysqld进程，移除上述两个选项，重启mysqld

权限管理

• 管理类：
  • CREATE TEMPORARY TABLES
  • CREATE USER
  • FILE
  • SUPER
  • SHOW DATABASES
  • RELOAD
  • SHUTDOWN • REPLICATION SLAVE
  • REPLICATION CLIENT
  • LOCK TABLES
  • PROCESS
• 程序类： FUNCTION、PROCEDURE、TRIGGER
  • CREATE
  • ALTER
  • DROP
  • EXCUTE
• 库和表级别：DATABASE、TABLE
  • ALTER
  • CREATE
  • CREATE VIEW
  • DROP
  • INDEX
  • SHOW VIEW
  • GRANT OPTION：能将自己获得的权限转赠给其他用户
• 数据操作：
  • 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
  • priv_level: *(所有库) | *.* | db_name.* |db_name.tbl_name | tbl_name(当前库 的表) | 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‘;
• 回收授权：REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user [, user] ...
  示例：
  • REVOKE DELETE ON testdb.* FROM 'testuser'@‘172.16.0.%’;
• 查看指定用户获得的授权：
  • 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体系结构

存储引擎

Notes:

1. Implemented in the server, rather than in the storage engine.

2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

3. Implemented in the server via encryption functions. Data-at-rest tablespace encryption is available in MySQL 5.7 and later.

4. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

5. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.

6. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.

7. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

8. See the discussion later in this section.

存储引擎

• MyISAM引擎特点：
  • 不支持事务
  • 表级锁定
  • 读写相互阻塞，写入不能读，读时不能写
  • 只缓存索引
  • 不支持外键约束
  • 不支持聚簇索引
  • 读取数据较快，占用资源较少
  • 不支持MVCC（多版本并发控制机制）高并发
  • 崩溃恢复性较差
  • MySQL5.5.5前默认的数据库引擎
• MyISAM存储引擎适用场景
  只读（或者写较少）、表较小（可以接受长时间进行修复操作）
• 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
  参看：https://mariadb.com/kb/en/library/xtradbinnodb-serversystem-variables/#innodb_file_per_table
  ON (>= MariaDB 5.5)
  两类文件放在数据库独立目录中
  数据文件(存储数据和索引)：tb_name.ibd
  表格式定义：tb_name.frm

其它存储引擎

• Performance_Schema：Performance_Schema数据库使用
• Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
• MRG_MyISAM：使MySQLDBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large DataBase)环境，如数据仓库
• Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持 SELECT和INSERT操作；支持行级锁和专用缓存区
• Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务 器创建一个逻辑数据库。非常适合分布式或数据集市环境
• BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
• Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的 应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
• CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用 CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
• BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功 能可用于分布式数据库设计，数据自动复制，但不是本地存储
• example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数 据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
• 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的核心数据库，类似于SqlServer中的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-optiontables.html  
https://mariadb.com/kb/en/library/full-list-of-mariadb-optionssystem-and-status-variables/

• 注意：其中有些参数支持运行时修改，会立即生效；有些参数不支持，且 只能通过修改配置文件，并重启服务器程序生效；有些参数作用域是全局 的，且不可改变；有些可以为每个用户提供单独（会话）的设置
• 获取mysqld的可用选项列表：

mysqld --help --verbose  
mysqld --print-defaults 获取默认设置

• 设置服务器选项方法：
  在命令行中设置
  • shell> ./mysqld_safe --skip-name-resolve=1
  在配置文件my.cnf中设置
  • skip_name_resolve=1
• 服务器系统变量：分全局和会话两种
• 获取系统变量

mysql> SHOW GLOBAL VARIABLES;
mysql> SHOW [SESSION] VARIABLES;
mysql> SELECT @@VARIABLES;

• 修改服务器变量的值：
  • 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;

服务器变量SQL_MODE

• SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会 话的设置，参看：https://mariadb.com/kb/en/library/sql-mode/
• 常见MODE:
  • NO_AUTO_CREATE_USER
  禁止GRANT创建密码为空的用户
  • NO_ZERO_DATE
  在严格模式，不允许使用‘0000-00-00’的时间
  • ONLY_FULL_GROUP_BY
  对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
  • NO_BACKSLASH_ESCAPES
  反斜杠“\”作为普通字符而非转义字符
  • PIPES_AS_CONCAT
  将"||"视为连接操作符而非“或运算符”