MySQL学习笔记

命令篇

操作数据库

SHOW DATABASES;
CREATE DATABASE database_name;
SHOW CREATE DATABASE database_name; 查看数据库定义
DROP DATABSE database_name; 删除数据库（其中的表和数据也被删除，不可恢复）
DESCRIBE table_name; （DESC table_name简写）查看表的基本结构


SHOW CREATE TABLE table_name 查看表的详细信息

修改表名 ALTER TABLE old_name RENAME [TO] new_name;
修改字段类型 ALTER TABLE table_name MODIFY column_name data_type;
改字段名 ALTER TABLE table_name CHANGE column_old_name column_new_name new_data_type;
添加字段 ALTER TABLE table_name ADD column_name datatype [NOT NULL ] [FIRST | AFTER column_name];
删除字段 ALTER TABLE table_name DROP column_name;
修改字段的排列位置 ALTER TABLE table_name MODIFY c1 type [FRIST | AFTER c2];
更改表的存储引擎 ALTER TABLE table_name ENGINE=InnoDB;
删除表的外键约束 ALTER TABLE table_name DROP FOREIGN KEY 外键约束名
删除表 DROP TABLE [IF EXISTS] table_name1,table_name2,…;

查询操作

SELECT [DISTINCT]…c1[AS nc1],c2,c3… FROM table_name WHERE column_name [BETWEEN … AND… | IN (a,b,c) | LIKE ‘pattern’ | IS NULL] AND|OR … [ GROUP BY column_name] [HAVING cause1] [ORDER BY column1 DESC,column2] LIMIT [2] 3;

百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符
下划线通配符‘_’，一次只能匹配任意一个字符
排序默认为升序，也可使用DESC来指定为降序。先按照column1排序(降序)，相同的再按照column2来排序（升序）。
使用GROUP BY可以对多个字段进行分组，GROUP BY关键字后面跟需要分组的字段，MySQL 根据多字段的值来进行层次分组，分组层次从左到右，即先按第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组…依次类推。
LIMIT 2,4 从第三条记录开始，打印4条（index start from 0）

连接查询

当两个表或者多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行查询。
内连接查询

外连接查询

子查询

ANY和SOME关键字的子查询：

SELECT num1 PROM tbl1 WHERE numl > ANY (SELECT num2 PROM tb12);  ANY和SOME相同，大于其中某一个就可满足条件

ALL关键字的子查询，需要满足所有：

SELECT numl FROM tbl1 WHERE numl > ALL (SELECT num2 FROM tbl2);

EXISTS关键字的子查询：

SELECT * PROM fruits WHERE EXISTS (SELECT s name FROM suppliers WHERE s_ id -107);

IN关键字的子查询

SELECTc id FROM orders WHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

比较运算符的子查询： > >= , <= !=

UNION合并查询结果

表、列别名

正则表达式查询

增删改

将查询结果插入到列表中：

INSERT INTO table_name1 (column list1) SELECT (column list2) FROM table_name2 WHERE (condition);

UPDATE person SET age = 15,name=' LiMing' WHERE id= 11;

DELETE PROM person WHERE age BETWEEN 19 AND 22;

DELETE FROM person;
TRUNCATE TABLE table name。

基本概念

客户端／服务器架构

服务器程序直接和存储的数据打交道，可以有好多客户端程序连接到这个服务器程序，发送增删改查的请求，然后服务器就响应这些请求，从而操作它维护的数据。 MySQL 服务器进程的默认名称为 mysqld ， 而我们常用的 MySQL 客户端进程的默认名称为 mysql 。

bin目录下的可执行文件

UNIX里启动服务器程序：
mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。
mysqld_safe 是一个启动脚本，它会间接的调用 mysqld ，而且还顺便启动了另外一个监控进程，这个监控进程，在服务器进程挂了的时候，可以帮助重启它。
mysql.server 也是一个启动脚本，它会间接的调用 mysqld_safe ，在调用 mysql.server 时在后边指定 start参数。

客户端与服务器的连接

本质上是进程之间的通信：

1. TCP/IP, 数据库服务器进程和客户端进程可能运行在不同的主机中，它们之间必须通过网络来进行通讯。MySQL 采用 TCP 作为服务器和客户端之间的网络通信协议。通过 IP地址 + 端口号 的方式来与这个进程连接。MySQL 服务器启动的时候会默认申请 3306 端口号。登录时也可直接指定端口号：mysql -h127.0.0.1 -uroot -P3307 -p （大P为端口号，小p为密码）
2. 命名管道和共享内存，适用于Windows操作系统，启用这些通信方式的时候需要在启动服务器程序和客户端程序时添加一些参数，使用共享内存的方式进行通信的服务器进程和客户端进程必须在同一台 Windows 主机中。
3. Unix域套接字文件，服务器进程和客户端进程都运行在同一台操作系统为类 Unix 的机器上的话，我们可以使用 Unix域套接字文件来进行进程间通信。

服务器处理客户端请求

连接管理 、解析与优化（查询缓存，语法解析，查询优化）、存储引擎（SHOW ENGINES;
查看当前服务器程序支持的存储引擎）

系统变量与状态变量

SHOW VARIABLES [LIKE 匹配的模式]; 查看 MySQL 服务器程序支持的系统变量以及它们的当前值。
服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为 MySQL 系统变量，比如允许同时连入的客户端数量用系统变量 max_connections 表示，表的默认存储引擎用系统变量default_storage_ engine 表示，查询缓存的大小用系统变量 query_cache_size 表示。

对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。
不过系统变量有作用范围之分，GLOBAL ：全局变量，影响服务器的整体操作。SESSION ：会话变量，影响某个客户端连接的操作。在服务器启动时，会将每个全局变量初始化为其默认值（可以通过命令行或选项文件中指定的选项更改这些默认值）。然后服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。

SHOW SESSION VARIABLES LIKE 'default_storage_engine';
SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';

服务器程序中维护了好多关于程序运行状态的变量，称为状态变量 。如Threads_connected 表示当前有多少客户端与服务器建立了连接， Handler_update表示已经更新了多少行记录。

SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

状态变量用来显示服务器程序运行状况，所以值只能由服务器来设置，程序员不能设置的。与系统变量 类似，状态变量也有 GLOBAL 和 SESSION 两个作用范围。不写明作用范围，默认的作用范围是 SESSION。

字符集和比较规则

SHOW CHARSET;

SHOW COLLATION [LIKE 匹配的模式];
每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则， SHOW COLLATION 的返回结果中的Default 列的值为 YES 的就是该字符集的默认比较规则，比方说 utf8 字符集默认的比较规则就是
utf8_general_ci 。
MySQL 有4个级别的字符集和比较规则，分别是：
服务器级别 数据库级别 表级别 列级别

1. 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
2. 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

不论哪个级别的字符集和比较规则，这两条规则都适用。
4个级别字符集和比较规则的联系如下：
如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
客户端和服务器通信中的字符集
character_set_client | 服务器解码请求时使用的字符集
character_set_connection | 处理请求时把请求字符串从 character_set_client 转为 character_set_connection
character_set_results | 服务器向客户端返回数据时使用的字符集
character_set_connection 只是服务器在将请求的字节串从 character_set_client 转换为
注意： character_set_connection 时使用，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用 character_set_connection 代表的字符集进行编码。

约束

主键约束（主码），表中一列或者多列的组合。


外键约束（对于参照完整性，一个列或者多个列，一个表可以有一个或者多个外键，外键可以为空值）

非空约束 字段值不可为空，如果在添加数据时，没有指定值，数据库系统会报错。

column_name VARCHAR(20) NOT NULL,

唯一性约束 要求该列唯一，可以为空（即只能出现一个空值）

column_name VARCHAR(20) UNIQUE,

默认约束 为某列指定默认值，

deptId INT(10) DEFAULT 1111,

属性值自动增加
一个表只可以有一个字段使用AUTO_INCREMENT,且该字段必须是主键的一部分，初始值为1，字段类型可以是任意的整数类型（TINYINT,SMALLINT,INT,BIGINT）

数据类型

整数类型可以添加自增约束条件，


INT(11) 后括号中的数字11，代表该字段类型指定的显示宽度，即能够显示的数值中的数字的位数。

M为精度（总共的位数） N为标度（小数的位数）

MySQL函数

mysql中的函数包括：数学函数、字符串函数、日期、时间函数、条件判断函数、系统信息函数、加密函数等
数学函数： ABS(X)绝对值函数、PI()返回圆周率函数、SQRT(X)平方根、MOD(x,y)求余函数。。。。
字符串函数：CHAR_LENGTH(str)返回值为字符串 str 所包含的字符个数。一个多字节字符算作一个单字符。合并字符串函数CONCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…….) 替换字符串的函数INSERT(s1,x,len,s2) LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。
获取指定长度的字符串的函数LEFT(s,n)和 RIGHT(s,n)
时间日期函数：CURDATE()和CURRENT DATE()函数作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。
CURTIME()和 CURRENT_TIME()函数作用相同,将当前时间以‘HH:MM:SS’或HHMMSS的格式返回，具体格式根据函数在字符串或是数字语境中而定。

索引

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。本节将介绍索引的含义、分类和设计原则。

普通索引：
最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。


唯一索引：
创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的就是:索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

组合索引（最左前缀）：


全文索引：

空间索引：

在已经存在的表上创建索引： 略

查看表中的索引：

SHOW INDEX FROM table_name;

删除索引：

存储过程与函数

略

视图

视图是从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。在视图中用户可以使用SELECT 语句查询数据，以及使用INSERT、UPDATE 和 DELETE修改记录。从MySQL 5.0开始可以使用视图，视图可以使用户操作方便，而且可以保障数据库系统的安全。
视图的作用：




对视图结构的修改：


更新视图：

通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对其基本表增加或者删除记录。

删除视图：

触发器

待补充

日志

存储引擎

存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引
擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。
创建表时指定存储引擎：

CREATE TABLE 表名(
 建表语句;
) ENGINE = 存储引擎名称;

如果表已经建好了，我们也可以使用下边这个语句来修改表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称;

innoDB引擎早期随着mysql的更新而更新，从版本5.1开始，mysql数据库允许存储引擎开发商以动态的方式加载引擎，这样存储引擎的更新就不再受数据库版本的限制。即可以理解为，MySQL5.1支持两个版本的InnoDB,一个是静态编译，一个是动态加载。

InnoDB体系架构：
该存储引擎有多个内存块，组成一个大的内存池，维护内部数据结构、缓存磁盘数据（提升访问速度），以及数据的写回，重做日志等。

待补充