MySQL必知必会 学习笔记

mysql必知必会

了解mysql

什么是数据库

数据库：保存有组织的数据的容器（通常是一个文件或一组文件）

表

表是一种结构化的文件，可用来存储某种特定类型的数据

主键

一列（或一组列），其值能够唯一区分表中每个行

表中的任何列都可以作为主键，但它需要满足以下条件：

任意两行都不具有相同的主键值

每个行都必须具有一个主键值，主键列不允许NULL值

SQL

SQL是结构化查询语言（Structured Query Language）的缩写，SQL是一种专门用来与数据库通信的语言

SQL的优点：

SQL不是某个特定数据库供应商专有的语言，几乎所有重要的DBMS（数据库管理系统）都支持SQL

客户机-服务器软件

MySQL是一种DBMS

DBMS可分为两类：一类为基于共享文件系统的DBMS，另一类为基于客户机-服务器的DBMS

MySQL数据库是基于客户机-服务器的数据库，服务器部分是负责所有数据访问和处理的一个软件、客户机是与用户打交道的软件

使用mysql

SQL语句是由简单的英语单词构成的，这些单词称为关键字，每个SQL语句都是由一个或多个关键字构成的

SQL语句不区分大小写，为了代码易于阅读和调试，一般将SQL关键字使用大写，对所有列和表名使用小写

读取数据库

USE world;
1

SHOW 语句

查看可用数据库

SHOW DATABASES;
1

获得一个数据库内的表的列表

SHOW TABLES;
1

显示广泛的服务器状态信息

SHOW STATUS;
1

显示详细信息

show columns from TABLEX;
1

SELECT语句检索

MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：

• 数据库名与表名是严格区分大小写的；
• 表的别名是严格区分大小写的；
• 列名与列的别名在所有的情况下均是忽略大小写的
• 变量名也是严格区分大小写的；

MySQL在Windows下都不区分大小写。

检索单列

利用select语句从city表中检索一个名为ID的列

SELECT ID FROM CITY;
1

检索多列

检索多列与检索单列唯一的不同是必须在SELECT关键字后给出多个列名，列名之间必须以逗号分隔

SELECT ID,Name FROM city;
1

检索所有列

SELECT * FROM city;
1

检索不同行

即unique()

SELECT DISTINCT id FROM city;
1

限制结果

检索列，返回不多于5行

SELECT DISTINCT id FROM city LIMIT 5;
1

从行5开始的5行，第一个数字、为开始位置，第二个数为要检索的行数

SELECT DISTINCT id FROM city LIMIT 5,5;
1

LIMIT 5 OFFSET 5如同LIMIT 5，5

使用完全限定的表名

SELECT city.id from city;
1

排序

排序检索数据

从小到大排序

SELECT id FROM city ORDER BY id;
1

按多个列排序

多个列排序时，排序完全按所规定的顺序进行

以如下句子为例

SELECT id,name FROM city ORDER BY id,name
1

先按id进行排序，若相同id下还有相同值，再按照name进行排序

指定排序方向

默认从小到大，若需要从大到小排序，必须指定DESC关键字

SELECT id FROM city DESC;
1

DESC关键字只应用到直接位于其前面的列名

SELECT id,name FROM city id DESC,name;
1

过滤

WHERE过滤

where指定过滤条件

SELECT id FROM city WHERE id = 5;
1

WHERE操作符

空值检查

SELECT id FROM city WHERE id IS NULL;
1

非空值

SELECT id FROM city WHERE id IS NOT NULL;
1

组合WHERE过滤

对于多重组合，SQL在处理OR操作符前，优先处理AND操作符，可以通过加圆括号的方法来明确处理顺序

例如如下语句：

SELECT id FROM city WHERE id = 5 OR id = 6 AND id = 7;
1

返回结果如下：

证明其判断顺序为，（id=5）or （id=6 and id=7）

若需要明确其处理顺序为（id=5 or id=6）and id = 7

语句如下：

SELECT id FROM city WHERE (id = 5 or id = 6) AND id = 7;
1

返回结果如下

IN操作符

IN操作符完成与or相同的功能，IN操作符一般比OR操作符清单执行更快

SELECT id FROM city WHERE id IN (5,6,7);
1

NOT 操作符

否定NOT之后所有的任何条件

SELECT id FROM city WHERE ID NOT IN (5,6,7);
1

MYSQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别

通配符

通配符：用来匹配值的一部分的特殊字符。

搜索模式：由字面值、通配符或两者组合构成的搜索条件

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较

%通配符（三种位置）

举例：

SELECT id FROM city WHERE ID LIKE '%5';
1

使用一个通配符且通配符在前代表检索任意与5结尾的id

SELECT id FROM city WHERE ID LIKE '5%';
1

使用一个通配符且通配符在后代表检索任意与5开头的id

通配符出现在搜索模式的中间，找出以5开头要以6街为的id

SELECT id FROM city WHERE ID LIKE '5%6';
1

使用两个通配符表示匹配任何位置包含文本5的id

SELECT id FROM city WHERE ID LIKE '%5%';
1

通配符%不可以匹配到NULL

_通配符

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符

例如

SELECT id FROM city WHERE id LIKE '_5'
1

返回结果如下

SELECT id FROM city WHERE id LIKE '5_'
1

返回结果如下

通配符不建议过度使用

正则表达式

MySQL中的正则表达式匹配不区分大小写，及大写和小写都匹配。为区分大小写，可使用BINARY关键字

基本字符匹配

检索列id包含文本1000的所有行

SELECT id FROM city WHERE id REGEXP '1000'
1

REGEXP后所跟的东西作为正则表达式处理

SELECT id FROM city WHERE id REGEXP '.100'
1

表示匹配任意一个字符

OR匹配

SELECT id FROM city WHERE id REGEXP '100|200';
1

匹配几个字符之一

SELECT id FROM city WHERE id REGEXP '[123]100';
1

表示匹配1100、2100、3100

与

SELECT id FROM city WHERE id REGEXP '[1|2|3]100'
1

等效

与

SELECT id FROM city WHERE id REGEXP '[1-3]100'
1

等效

匹配特殊字符

为了匹配特殊字符，必须用 \ \为前导，\ \ -表示查找-，\ \ .表示查找.

多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但MySQL要求两个反斜杠，MySQL自己解释一个，正则表达式库解释另一个

匹配字符类

匹配多个实例

定位符

创建计算字段

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而术语字段通常用在计算字段的连接上

拼接字段

拼接：将值联结到一起构成单个值

可使用Concat()函数来拼接两个列

SELECT CONCAT(id,'(',id,')') FROM city;
1

RTrim()去掉串右边的空格

LTrim()去掉串左边的空格

使用别名

AS

SELECT CONCAT(id,'(',id,')') AS t FROM city LIMIT 5;
1

它指示SQL创建一个包含指定计算的名为t的计算字段。任何客户机应用都可以按名引用这个列，就像它是一个实际的表列一样

执行计算字段

SELECT id, id*id as new_id FROM city LIMIT 5;
1

使用数据处理函数

文本处理函数

日期和时间处理函数

数值处理函数

汇总数据

聚集函数

SELECT MAX(id) as msx FROM city;
1

组合聚集函数

SELECT MAX(ID),MIN(ID),AVG(ID) FROM city;
1

分组数据

创建分组

GROUP BY子句建立

SELECT MAX(ID) ,countrycode FROM city GROUP BY ID LIMIT 5;
1

效果如下

不使用group by的效果如下：

GROUP BY子句指示MySQL按ID排序并分组数据，这导致ID会被分组，MAX函数是应用与分组后的数据

过滤分组

WHERE过滤行，HAVING过滤分组

SELECT MAX(ID) ,countrycode FROM city GROUP BY ID HAVING ID >=2 LIMIT 5;
1

分组和排序

GROUP BY和ORDER BY的区别

SELECT子句顺序

联结表

关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

可伸缩性：能够适应不断增加的工作量而不失败

联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结

创建联结

SELECT ID,code FROM city,country WHERE ID='5'
1

SELECT语句联结了city和country两个表，这两个表用WHERE子句正确联结

在引用的列可能出现二义性时，必须使用完全限定列名，即用一个点分割的表名和列名

在联结两个表时，实际上时将第一个表中的每一行与第二个表中的每一行配对，WHERE子句作为过滤条件，它只包含那些匹配给定条件的行，没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起

笛卡尔积：由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

内部联结

INNER JOIN ON

SELECT id,code FROM city INNER JOIN country ON city.id='5';
1

mysql的特点

联结多个表

AND

SELECT ID,code FROM city,country WHERE ID='5' AND code = 'ZWE';
1

组合查询

用UNION操作符来组合数条SQL查询

SELECT id,name FROM city WHERE name = 'kabul' UNION SELECT id,name FROM city WHERE id<='5';
1

UNION必须由两条或以上的SELECT语句组成，语句之间用关键字UNION分割，UNION中的每个查询必须包含相同的列、表达式或聚集函数；列数据类型必须兼容

UNION从查询结果集中自动去除了重复的行，这是UNION的默认行为，如果需要，可以改变它。事实上想返回所有匹配行，可使用UNION ALL而不是UNION

SELECT id,name FROM city WHERE name = 'kabul' UNION ALL SELECT id,name FROM city WHERE id<='5';
1

全文本搜索

MyISAM支持全文本搜索，InnoDB不支持全文本搜索

性能：通配符和正则表达式 匹配通常要求MySQL尝试匹配表中所有行，因此，由于被搜索行数不断增加，这些搜索可能非常耗时。

全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配，哪些词不匹配，它们的匹配频率等等

随着数据的改变，MySQL会自动进行所有的索引和重新索引

启用全文本搜索支持

一般在创建表时启用全文本搜索。

定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新

进行全文本搜索

使用两个函数Match()和Against()执行全文本搜索，Match()指定被搜索的列，Against()指定要使用的搜索表达式

查询扩展 Against(x,WITH QUERY EXPANSION)

布尔文本搜索 Against(x, IN BOOLEAN MODE)

全文本搜索的使用说明

在索引全文本数据时，短词被忽略且从索引中排除，短词定义为那些具有3个或3个以下字符的词，可以进行更改

MySQL自带停用词列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表

MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略

如果表中的行数少于3行，则全文本搜索不返回结果

忽略词中的单引号，don’t索引为dont

不具有词分隔符（汉语）的语言不能恰当地返回全文本搜索结果

仅在MyISAM数据库引擎中支持全文本搜索

数据插入

INSERT语句

INSERT INTO

INSERT INTO city VALUES(4080,'Rafah','ZWE','789',5);
1

如果插入地列没有值，应该使用NULL值

更安全但更繁琐地方法如下：

INSERT INTO city(ID,Name,CountryCode,District,Population) VALUES(4081,'Rafah','ZWE','789',5);
1

列名与值为一一对应，列的顺序可以改变

插入多个行

INSERT INTO city(ID,Name,CountryCode,District,Population) VALUES(4082,'Rafah','ZWE','789',5),(4083,'Rafah','ZWE','789',5);
1

插入检索出的数据

使用INSERT SELECT从custnew中将所有数据导入customers，这条语句插入多少行依赖于custnew表中有多少行。

更新数据

UPDATE语句

UPDATE语句由三部分组成，分别是：要更新的表，列名和它们的新值，确定要更新行的过滤条件。

UPDATE city SET Name = '...' WHERE id=4080;
1

删除数据

DELETE FROM city WHERE id = 4080;
1

MySQL没有撤销按钮，应该非常小新地使用UPDATE和DELETE

创建和操作表

创建表

CREATE TABLE

CREATE TABLE test1(cust_id int NULL);
1

不允许NULL值 NOT NULL

主键用以下地类似地语句定义：

PRIMARY KEY(ID);
1

AUTO_INCREMENT

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

id int NOT NULL AUTO_INCREMENT,
1

指定默认值

quantity int NOT NULL DEFAULT 1,
1

在未给出数量地情况下使用数量1

引擎类型

• InnoDB是一个可靠地事务处理引擎，它不支持全文本搜索
• MEMORY在功能等同于MyISAM，但由于数据存储在内存而不是磁盘中，速度很快，特别适合与临时表
• MyISAM是一个性能极高地引擎，它支持全文本搜索，但不支持事务处理

引擎类型可以混用

MyISAM由于其性能和特性可能是最受欢迎地引擎

更新表

ALTER TABLE语句

给表添加一个列

ALTER TABLE city ADD int;
1

给表删除一个列

ALTER TABLE city DROP COLUMN IDD; 
1

删除表

DROP TABLE city;
1

重命名表

RENAME TABLE city TO city2;
1

可以使用逗号对多个表重命名

使用视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询

视图的常见应用

• 重用SQL语句
• 简化复杂的SQL操作
• 使用表的组成部分而不是整个表
• 保护数据
• 更改数据格式和表示

视图仅仅是用来查看存储在别处的数据的一种设施。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

视图用CREATE VIEW语句来创建

CREATE VIEW shitu AS SELECT ID FROM city LIMIT 5;
1

使用SHOW CREATE VIEW viewname来查看创建视图的语句

SHOW CREATE VIEW shitu
1

检索视图

SELECT ID FROM shitu WHERE ID=10;
1

用DROP删除视图，其语法为DROP VIEW viewname

存储过程

存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数

创建存储过程

DELIMITER // 告诉命令行使用程序使用//作为新的语句结束分割符号

除 \ 符号外，任何字符都可以用作语句分隔符

DELIMITER // CREATE PROCEDURE productpricing() BEGIN SELECT Avg(ID) AS priceaverage FROM city; END // DELIMITER;
1

CREATE PROCEDURE productpricing() BEGIN SELECT Avg(ID) AS priceaverage FROM city; END;
1

调用

CALL productpricing();
1

删除存储过程

DROP PROCEDURE productpricing;
1

游标

游标是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

事务处理

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

改善性能

MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;

MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程，还可以用KILL命令终结某个特定的进程。