首页 > 代码库 > MySQL基础实战
MySQL基础实战
一．数据库基础：
DBMS：数据库管理系统(Database Management System)
RDBMS：关系数据库管理系统(Relational Database Management System)
C/S:通过专有协议
关系模型：表（行，列），二维关系
范式：第一范式，第二范式，第三范式；
关系运算：
选择
投影
数据库：表，索引，视图（虚表）
SQL：结构化查询语言
DDL:数据定义语言
DML:数据操作语言
编程接口：
自行定义存储过程
自行定义存储函数
触发器：
时间调度器：类似Linux中的定时任务
过程式编程：选择、循环
三层模型：
物理层：表空间（管理）
逻辑层：表，索引，视图（DBA）
视图层：用户角度视图（用户）
解决方案：
Oracle,Sytbase,Informix,DB2(IBM)
Mysql,MariaDB,Postgresql,SQLite,
二．MySQL基础
版本：---5.1---5.5---5.6---5.7
MariaDB:
插件式存储引擎：
单进程多线程：
连接线程：维护用户连接
守护线程：对应的数据写入到磁盘上
Mysql配置文件：集中式配置，能够为mysql的多个应用程序提供配置信息
[mysqld]：mysql的服务器
[mysqld_safe]：mysql的的启动服务器
[mysqld_multi]：单机多实例配置，适合集中管理
[server]：mysql服务端
[mysql]：mysql数据存放
[mysqldump]：mysql数据导出
[client]：mysql客户端
安装方式：
os vendor：rpm
MySQL:
rpm
展开可用
源码，编译
安装后的设定：
（1）为所有root用户设定密码：
mysql>set passwrod
mysql>update mysql.user SET password=PASSWORD(‘you_passwd‘) WHERE cluase;
#mysqladmin：命令行设置
(2)删除所有匿名用户
mysql>DROP USER ‘‘@‘localhost‘;
上述两步骤可运行命令：mysql_secure_installation即可设置
(3)建议关闭主机名反解功能：
[mysqld]
skip-name-resolve
元数据数据库：mysql，user，host等
获取元数据有三种思路，第一种，各种show，第二种，各种select，第三种，是mysql的命令行下的命令，不是sql语句
1：show
[sql]
show databases; --列出所有数据库
show create databasedb_name; --查看数据库的DDL
show tables; --列出默认数据库的所有表
show tables from db_name; --列出指定数据库的所有表
show table status; --查看表的描述性信息
show table status fromdb_name; --查看具体表的信息
show create table tbl_name; --查看表的DDL
show columns from tbl_name; --查看列信息
show index from tbl_name; --查看索引信息
有几种show语句还可以带有一条like ‘pattern‘字句，用来限制语句的输出范围，其中‘pattern‘允许包含‘%‘和‘_‘通配符，比如下面这条语句返回domaininfo表中以s开头的所有列：
showcolumns from domaininfo like ‘s%‘;
2：select
像上面这张支持like字句的所有show都可以改写成一条where字句，如：
showcolumns from domaininfo where field=‘sysdomain‘;
注：desc tbl_name和explain tbl_name的效果和show columns from tbl_name一致。
从INFORMATION_SCHEMA数据库里查询相关表
INFORMATION_SCHEMA是MySQL自带的一个系统数据库，它里面存储了所有的元数据，通过select里面的相关表就可以获取你想要的元数据。和show语句相比，它比较麻烦，但它的好处是标准的SQL语句，更具有可移植性，且更灵活，可以通过各种表达式获取你真正需要的信息。
3：从命令行获取元数据
前面两种方法都必须得在MySQL命令行里执行，而mysqlshow和mysqldump提供了从OS命令行获取元数据库的方法，如：
mysqlshow --列出所有数据库 例：mysqlshow-uroot -p123456
mysqlshow db_name --列出给定数据库的所有表
mysqlshow db_name tbl_name --列出给定数据库表的所有列
mysqlshow --keys db_name tbl_name --列出索引信息
mysqlshow --status db_name --列出数据库的描述性信息
mysqldump可以让你看到create table语句（就想show create table语句一样），如：
[sql]
mysqldump --no-data db_name [tbl_name] ...
注意：在用mysqldump查看表结构时，一定要加上--no-data，否则你看到的将是数据库表里的数据
mysql--->mysqld C/S架构
客户端程序：
mysql：交互式的client工具
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保 存文本文件中；
mysqladmin：基于mysql协议管理mysqld；
mysqlimport：数据导入工具（但并不是mysqldump导出的数据）
非客户端类的管理工具：
myisamchk，myisampack
如何获取程序默认的使用配置：
mysql --print-defaults：获取mysql客户端程序默认的配置
mysqld --print-defaults：获取mysql服务端程序的默认配置
客户端类应用程序的可用选项：
-u --user= ：用户
-h --host= ：主机IP
-p --password= ：密码
-P --port= ：端口
--portcol=(tcp|sock) ：协议
-s --socket= ：Linux默认连接范式，mysql还可以使用TCP进行连接
-D --database= ：数据库名
-c --compress ：mysql压缩
mysql -e"SQL" 命令行执行命令，不进入交互式运行
例：mysql -uroot -p123456 -e "show databases;"
mysql的使用模式：
交互式模式：
可运行命令有两类：
Mysql>\h
Mysql>help
服务器端命令：
SQL，需要语句结束符；
脚本模式：
#mysql -uusername -hHOST -pPASSWORD</path/from/somefile.sql
mysql>source/path/from/somefile.sql
服务器端（ mysqld）：工作特性有多种定义方式
命令行选项
配置文件参数
获取可用参数列表：mysqld --help --werbose
获取运行中的mysql进程各参数及其值：
mysql>SHOWGLOBAL VARIABLES;
mysql>SHOWSESSION VARIBLES;
注意：其中有些参数支持运行时修改，有些参数不支持，且只能通过修改配置文件，并重启服务器程序 生效。
有些参数作用域是全局的，不可改变；有些可以为每个用户单独提供的设置。
修改服务器变量的值：
mysql>help SET 获取语法
全局：
mysql>SET GLOBALsystem_var_name=value;
mysql>SET@@global.system_var_name=value;
会话：
mysql>SET [SESSION]system_var_name=value;
mysql>SET@@[session.]system_var_name=value;
状态变量：用于保存mysql运行中的统计数据的变量;
mysql>SHOW GLOBAL STATUS;
mysql>SHOW [SESSION]STATUS;
三．SQL语句基础
SQL标准：ANSI SQL标准
SQL-86，SQL-89，SQL-92，SQL-99，SQL-03
mysql 数据类型：
字符型
数值型
日期时间型
内建类型：
字符型：
char，binary:定长数据类型
VARCHAR，VARBINWRY：变长数据类型;需要结束符
TEXT:TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT
BLOB:TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB
MEUM,SET
数值型:
精确数值型:
整型：微整形,小整型，中等整型，大整型
十进制型:十进制
近似数值型：
浮点型：单精度，双精度，
日期时间型：日期(三字节)，时间(三字节)，日期时间(八字节)，时间戳，年份；
内建型：
字符类型的修饰符：
NOT NULL:非空约束;
NULL:空
DEFAULT ‘STRING‘：指定默认值；
CHARACTER SET ‘‘：使用字符集
COLLATION:使用的排序规则
mysql>SHOW CHARACTER SET;
mysql>SHOW COLLATION;
整型修饰符：
NOT NULL
NULL
DEDAULT NUMBER
日期修饰符：
内建类型SET和EMUM的修饰符
SQL 模型：定义mysqld对约束等的响应行为：
修改方式:
mysql>SET GLOBALsql_mode=‘MODE‘;
mysql>SET@@GLOBAL.sql_mode=‘MODE‘；
需要修改权限：仅对修改后新创建的回话有效，对已创建的回话无效
mysql>SET SESSIONsql_mode=‘MODE‘;
mysql>SER@@session.sql_mode=‘MODE‘;
常用模型：TRADITIONAL，STRICT_TRANS_TABLES，or STRICT_ALL_TABLES
修改模型之后可以对sqlserver或者oracle兼容
SQL语句：DDL，DML
DDL：数据定义语言：主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。
CREATE,ALTER,DROP ：创建，添加、删除
DB组件：数据库、表、索引、视图、用户、存储过程、存储函数、触发器、时间调度器
CREATE相关的常用命令：（CREATE可以用的,相应的ALTER也可以用）
CREATE DATABASE ：创建库
CREATE EVENT :创建事件
CREATE FUNCTION ：创建函数
CREATE FUNCTION UDF ：
CREATE INDEX ：创建索引
CREATE LOGFILE GROUP ：创建测试日志
CREATE PROCEDURE ：创建程序
CREATE SERVER ：创建服务
CREATE TABLE ：创建表
CREATE TABLESPACE ：创建表空间
CREATE TRIGGER ：创建触发器
CREATE USER ：创建用户
CREATE VIEW ：创建视图
DML：数据操作语言：一般是对数据库里的数据进行操作的语言
INSERT,DELETE,UPDATE,SELECT ：插入、修改、更新、查询
数据库级别:
CREATE,ALTER,DROP
{DATABASE|SCHEMA}
{IF EXISTS}
{IF NOT EXISTS}
表级别：二位关系
设计表：遵循规范;表使用规范，统一规范
定义：字段，索引
字段：字段名，字段数据类型，修改符
约束：索引：应该创建在经常用作查询条件的字段上；
索引：实现级别在存储引擎；
索引分类：
稠密索引、稀疏索引
B+树索引、hash索引、R树索引、FULLTEXT索引
聚集索引、非聚集索引
简单索引、组合索引
创建表：CREATE TABLE
（1）直接创建
(2) 通过查询先存的表创建:新表会被直接复制插入查询而来的数据
（3）通过复制现存的表的表结构创建：不复制数据
注意：Storage Engine是指表的类型，也即在表创建指明其使用的存储引擎
同一个库中表要使用同一种存储引擎类型
查看表结构：DESCRIBE TABLE_NAME;
修改表:ALERT TABLE
删除表:DROP TABLE
四．MYSQL基础架构及SQL语句
Mysql架构：
1.Connectors
指的是不同语言中与SQL的交互：
Native C API ：调用接口
JDBC：JDBC（Java Data Base Connectivity,java数据库连接）是一种用于执行SQL语句的Java API
ODBC：开放数据库连接（Open Database Connectivity）
2 Management Serveices & Utilities：
系统管理和控制工具：集群、复制、备份、还原、实例管理控制等信息
3 Connection Pool: 连接池
连接池，管理缓冲用户连接，线程处理等需要缓存的需求（用户与mysql进行通讯需要走的一座桥）
管理缓冲用户连接，线程处理等需要缓存的需求。
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。
4 SQL Interface: SQL接口。
接受用户的SQL命令，并且返回用户需要查询的结果。比如selectfrom就是调用SQLInterface
5 Parser: 解析器。
SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
6 Optimizer: 查询优化器。
查询优化器（sql的顺序是：from --> where --> 列名）
SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query语句的结果
他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender =1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
7 Cache和Buffer： 查询缓存。(也就是查询结果缓存在里面)
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集cache 到内存中，与该 query 的一个 hash 值 做
一个对应。该 Query 所取数据的基表发生任何数据的变化之后，MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中，Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
8 、存储引擎接口
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,NDB
默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术，但是它不支持事务。
InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。
Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。
Mysql逻辑架构图
简略描述：
每个虚线框为一层，总共三层。
第一层，服务层(为客户端服务):为请求做连接处理，授权认证，安全等。
第二层，核心层:查询解析，分析，优化，缓存，提供内建函数;存储过程，触发器，视图。
第三层，存储引擎层，不光做存储和提取数据，而且针对特殊数据引擎还要做事务处理
> 在解析查询之前，服务器会“询问”是否进行了查询缓存(只能缓存SELECT语句和相应结果)。缓存过的直接返回结果，未缓存的就需要进行解析查询，优化，重新执行返回结果。
> 解析查询时会创建一个内部数据结构(树)，然后对其进行各种优化。
> 优化:重写查询，决定查询的读表顺序，选择需使用的索引。
Mysql属于单进程多线程
用户连接：连接线程
mysql数据文件类型：
数据文件、索引文件
重做日志、撤销日志、二进制日志、磁错误日志、查询日志、慢查询日志、（中继日志）
五．Msyql的DDL& DML 语句
索引：INDEX
索引管理：
按特定数据结构存储数据：
索引类型：
聚集索引、非聚集索引：数据是否与索引存储在一起；
主键索引、辅助索引：一一对应的
稠密索引、稀疏索引：是否索引了每一个数据项
B+树索引、hash索引、R树索引（空间索引）、全文索引
简单索引、组合索引
左前缀索引
覆盖索引
管理索引的途径：
创建索引：创建表时指定：CREATE INDEX
创建或删除索引：修改表的命令
删除索引：DROP INDEX
查看表上的索引:
SHOW{INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
EXPLAIN:查看执行索引语句的情况
视图：VIEW
虚表：
创建方法：
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE |TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
删除视图：
DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]
视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对于基表实现；其修改操作受基表限制
DML：
INSERT、DELETE、UPDATE、SELECT
INSERT:
一次插入一行或多行数据：如果一次插入一百行，最好触发一次索引更新
Syntax:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(col_name,...)]
{VALUES | VALUE} ({expr |DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERTtal_name [(col1,...)] VALLUES (vall,...),(val21,...)
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name,...)]
SET col_name={expr | DEFAULT}, ...
[ ONDUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name,...)]
[(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
DELETE:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*]] ...
USING table_references
[WHERE where_condition]
注意：一定要有限制条件，否则将清空表中的所有数据
限制条件：
WHERE
LIMIT
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
SELECT:
重要组件：Query Cache
查询执行路劲中的组件：查询缓存、解析器、预处理器、优化器、查询执行引擎、存储引擎
SELECT语句的执行流程：
FROMClause---WHERE Clause---GROUP BY---HAVING Clause--ORDER BY---SELECT--LIMIT
单表查询：
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT][SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE][SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[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_countOFFSET offset}]
[FOR UPDATE | LOCK IN SHARE MODE]
DISTINCT:数据去重
SQL_CACHE：显式指定存储查询结果于缓存中
SQL_NO_CACHE：显式查询结果不予缓存
query_cache_type的值为“ON”是，查询缓存功能打开
SELECT的结果符合缓存条件及会缓存，否则，不予缓存
显式执行SQL_NO_CACHE，不予缓存；
query_cache_type的值为“DEMANG”时，查询缓存功能按需进行
显式指定SQL_CACHE的SELECT语句才会缓存，其它均不予缓存
SHOW GLOBAL VARIABLES LIKE ‘query%‘; 查询缓存是否开启，和缓存空间大小
SHOW GLOABL STATUS LIKE ‘Qcache%‘; 查询缓存命中次数
Qcache_hits缓存命中次数值
SHOE GLOABL STATUS LIKE ‘Com_se%‘; 查询次数
Com_select查询次数值
使用查询命中次数除以查询次数就是命中率
字段显式可以使用别名：
col1 AS alias1，col2 AS alias2，...
WHERE字句：指明过滤条件以实现"选择"的功能；
过滤条件：布尔型表达式
算术操作符：+，-，*，/
比较操作符：=，！=，<>,<=>,>,<，>=,<=
BETWEEN min_num AND max_num
IN (element1,elemnt2,...)
IS NULL
IS NOT NULL
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]
对查询结果中的数据请求施加“锁”；
FOR UPDATE:写锁，拍他锁；
LOCK IN SHARE MODE：读锁，共享锁
练习：导入helldb.sql生成数据库
（1）在Students表中，查询年龄大于25岁的，且为男性的同学的名字和年龄；
（2）以ClassID为分组依据，显示每组的平均年龄；
（3）显示第2题中的平均年龄大于30的分组及平均年龄
（4）显示以L开头的名字的同学的信息
（5）显示TeacherID非空的同学的相关信息；
（6）以年龄排序后，显示年龄最大的前10位同同学的信息
（7）查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法
多表查询：
交叉连接：笛卡尔乘积
内连接：
等值连接：让表之间的字段以“等值”建立连接关系
不等值连接
自然连接
自连接
外连接：
左外连接：
FORM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：
FORM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
子查询：在查询语句嵌套这查询语句
基于某语句的查询结果再次进行的查询
用WHERE字句中的子查询：
（1）用于比较表达式中的子查询，子查询仅能返回单个值
SELECT Name,Age FROM studentsWHERE Age>(SELECT avg(Age) FORM students);
（2）用于IN中的子查询：子查询应该单键查询并返回一个或多个值从而构成列表；
（3）用于EXISTS;
用于FORM字句中的子查询；
使用格式：SELECT tb_alais.col... FROM (SELECT clause) AS tb_alias WHERE Clause;
联合查询：UNION
MySQL基础实战