共计 8597 个字符,预计需要花费 22 分钟才能阅读完成。
1、MySQL 基础概念
mysql 逻辑架构如下:
每个客户端连接都会在服务器中拥有一个线程,这个连接的查询只会在这个单独的线程中执行。
MySQL 是分层的架构。上层是服务器层的服务和查询执行引擎,下层是存储引擎。虽然有很多不同作用的插件 API,但存储引擎 API 还是最重要的。如果能理解 MySQL 在存储引擎和服务层之间处理查询时如何通过 API 来回交互,就能抓住 MySQL 的核心基础架构的精髓。
数据库系统实现了各种死锁检测和死锁超时机制,InnoDB 目前处理死锁的机制是,将持有最少行级排它锁的事务进行回滚。MySQL 服务层不管理事务,事务是由下层的存储引擎实现的。
注意,SQL 语句一般以 ’;’ 或者 ’\g’ 结束。
- 数据库(database): 保存有组织的数据的容器。
- 表(table): 某种特定类型数据的结构化清单。
- 列(column): 表中的一个字段,所有表都是由一个或多个列组成的。
- 数据类型(datatype): 所容许的数据的类型,每个表列都有相应的数据类型,它限制该类所存储的类型。
- 行(row): 是表中的一个记录。
- 主键(primary key): 是一列(或者一组列),其值能够唯一区分表中每个行。
2、使用 MySQL
- mysql -h host -u username -p 与数据库建立连接
- use database_name 选择数据库
- show databases 显示所有的数据库
- show tables 显示当前数据库下所有的表
- show columns from table_name 显示表的列信息,作用和 desc table_name 是一样的
MySQL 支持的其他 show 语句还有:
- show status 显示 MySQL 服务器状态信息
- show create database_name 和 show create table_name 分别显示创建特定数据库和数据表的 MySQL 语句
- show grants 显示授予用户的安全权限
- show errors 和 show warnings 显示服务器错误或警告信息
3、检索数据
SQL 语句中最常用的就是 select 语句了,它用来在一个或多个表中检索数据,select 使用示例如下:
- select column_name from table_name 从 table_name 数据表中检索 column_name 列,检索单个列
- select column1_name, column2_name from table_name 检索多个列
- select * from table_name 检索所有列
注意,select 返回所有匹配的行,但是,如果我们不想每个值都每次出现,怎么办呢?例如,想让上图中输出的 id 唯一,这样可以再 select 语句中添加 distinct 关键字,select distinct id from goods,这样显示结果如下:
有时我们想限制输出的结果,比如返回第一行或前几行,可使用 limit 子句,如下所示:
- select * from goods limit 2 返回前 2 行结果,注意,当行数不够时,只能返回数据表中有限的行数
- select * from goods limit 2, 2 返回从行 2 开始的 2 行
当然,我们也可以使用完全限制来进行数据检索:
select goods.id, goods.name from goods 输出 goods 表中所有记录
4、排序检索数据
排序检索数据主要使用 select 语句的 order by 子句,根据需要排序检索出的数据,select 语句默认返回结果是没有特定顺序的,在排序检索数据时也可以指定排序的方向,比如升序或者降序等,order by 子句默认为升序排列。
(这个是降序配列)
有时,我们需要对多个列排序怎么办呢?这时可以使用如下 sql 语句来执行,select * from goods order by id desc, name,注意,这里是对 id 进行降序排列,如果 id 相同时,对 name 进行升序排列。如果想对多个列进行降序排列,需要对每个列指定 desc 关键字。
使用 order by 和 limit 的组合,我们能够找到一个列中最高或者最低的值,比如这里还用 goods 表做测试,先给 goods 表增加一个 num 字段(alter table goods add num int),并添加上对应的值,最后 goods 表内容为:
找出剩余数量最多的的水果是:
注意:select 语句的 order by 子句对检索出的数据进行排序,这个字句必须出现在 select 语句中的最后一条子句。至于为什么,这个我暂时还不知道哈。。。
5、过滤数据
select 语句中,数据可以根据 where 子句指定的过滤条件进行过滤,where 子句在表名 (from 子句) 之后给出,比如,select id, name from goods where id = 2,该语句只显示 id 为 2 记录的 id 和 name。注意:如果同时使用 where 和 order by 子句,应该让 order by 子句在 where 之后,否则会产生错误。
where 子句操作符如下:
- = 等于
- <> 不等于
- != 不等于
- < 小于
- > 大于
- >= 大于等于
- between 在指定的两个值之间(比如 select * from goods where id between 1 and 2)
我们在创建表时,可以指定其中的列是否可以不包含值,在一个列不包含值时,其值为空值 null,select 语句有一个特殊的 where 子句,用来检测具有 null 值的列,比如:select * from goods where num is null 就把 num 是空值得记录给打印出来。
6、数据过滤
常用的 select 子句在过滤数据时使用的是单一的条件,为了进行更强的过滤控制,可以下多个 where 子句,这些子句有两种方式:以 and 子句和 or 子句的方式使用。
- select * from goods where id = 2 and num > 10 检索 id 为 2 并且 num 大于 10 的记录
- select * from goods where id = 3 or num > 15 检索 id 为 3 并且 num 大于 15 的记录
假如多个 and 和 or 语句放在一起,则优先处理 and 操作符,此时可以使用圆括号来改变其优先顺序。圆括号还可以指定 in 操作符的条件范围,范围中的每个条件都可以进行匹配。
where 子句中的 not 操作符只有否定它之后的任何条件这一作用。
7、用通配符进行过滤
使用 like 操作符进行通配搜索,以便对数据进行复杂过滤。
百分号 (%) 操作符 搜索中,% 表示任何字符出现任意次数。
下划线 (_) 通配符,它用来匹配单个字符而不是多个字符。
通配符很有用,但这是有代价的,通配符的搜索处理一般比其他搜索花费时间长,这里有一些技巧:
- 不要过度使用通配符,如果其他操作符能达到同样的目的,就应该使用其他操作符。
- 在确实需要使用通配符时,除非绝对必要,否则不要把它们用在搜索模式的开始处,把通配符放在开始处,搜索起来是最慢的。
- 注意通配符位置,位置不对可能不会返回想要的结果。
8、正则表达式
正则表达式的作用是匹配文本,将一个模式 (正则表达式) 与一个文本串进行比较。mysql 的 where 子句对正则表达式提供了初步支持,允许你指定正则表达式,过滤 select 检索出的数据。先看一下表记录:
- select * from goods where name regexp ‘ 香 ’ order by num desc 检索出 name 中有 ’ 香 ’ 的所有记录
- select * from goods where name regexp ‘ 香.’ 检索出 name 中有 ’ 香 ’ 的所有记录,’.’ 表示匹配任意一个字符
- select * from goods where name regexp ‘ 香 | 瓜 ’ 检索处 name 中有 ’ 香 ’ 或者 ’ 瓜 ’ 的所有记录
如果记录匹配正则表达式,则就会被检索出来,使用下面正则表达式重复元字符可以进行更强的控制。
- * 匹配 0 个或多个
- + 匹配 1 个或多个(等于{1,})
- ? 匹配 0 个或 1 个(等于{0,1})
- {n} 指定书目的匹配
- {n,} 不少于指定数据的匹配
- {n,m} 匹配指定数据的范围(m 不超过 255)
- ^ 文本的开始
- $ 文本的结束
- [[:<:]] 词的开始
- [[:>:]] 词的结束
注意:regexp 和 like 作用类似,regexp 和 like 不同之处在于,like 匹配整个串而 regexp 匹配子串,利用定位符,通过 ’^’ 开始每个表达式,用 ’$’ 结束每个表达式,可以使 regexp 的作用和 like 一样。
9、创建计算字段
拼接将值连接到一起构成单个值,在 mysql 的 select 语句中,可使用 concat()函数来拼接两个列。
从上图中我们可以看到新的计算列的名字只是一个值,我们可以使用别名来使得它更有意义。同时也可以对检索出的数据进行算术运算,加减乘除都是支持的。
10、使用数据处理函数
常用的文本处理函数
函数 | 说明 |
left() | 返回串左边的字 |
length() | 返回串的长度 |
locate() | 找出串的一个子串 |
lower() | 将串转换为小写 |
ltrim() | 去掉串左边的空格 |
right() | 返回串右边的字符 |
rtrim() | 返回串右边的字符 |
soundex() | 返回串的 soundex 值 |
substring() | 返回子串的字符 |
upper() | 将串转换为大写 |
11、汇总数据
经常需要汇总数据而不是把它们检索出来,为此 mysql 提供了专门的函数,以便分析和报表生成。常用的例子有:确定表中行数、获取表中行组的和、找出表列的最大值(最小值或平均值)。聚集函数运行在行组上,计算和放回单个值得函数,mysql 提供了 5 个聚集函数,这些函数一般比自己的客户端应用程序中计算要快得多。
- AVG() 返回某列的平均值
- COUNT() 返回某列的行数
- MAX() 返回某列的最大值
- MIN() 返回某列的最小值
- SUM() 返回某列之和
使用关键字 distinct 使得只选择不同 num 的记录来参与计算。
12、分组数据
sql 聚集函数可以用来汇总数据,这使得我们能够对行计数、计算平均值、获取最大最小值不用检索所有数据。而创建分组允许把数据分为多个逻辑组,以便对每个组进行聚集计算。其涉及到 group by 子句和 having 子句。
在使用 group by 子句前,需要知道一些重要的规定:
- group by 子句可以包含任意数目的列,这使得能对分组进行嵌套,为数据分组提供更细致的控制。
- 如果在 group by 子句中嵌套了分组,数据将在最后规定的分组上汇总。换句话说,在建立分组时,指定的所有列都一起计算。
- group by 子句中列出的每个列都必须是检索列或有效的表达式,但不能是聚集函数。
- 如果分组列中具有 null 值,则将 null 作为一个分组返回,如果有多个 null 值,它们将分为一组。
- group by 子句必须出现在 where 子句后,order by 子句前。
除了能用 group by 分组数据外,mysql 还允许过滤分组,规定包括哪些分组,排除哪些分组。过滤分组需使用 having 子句,因为 where 过滤的是行而不是分组。注意:where 是在数据分组前进行过滤,having 是在数据分组后进行过滤。
group by 和 order by 经常完成相同的工作,但是二者是非常不同的:
order by | group by |
排序产生的输出 | 分组行,但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列) | 只可能使用选择列或表达式列,而且必须使用每个选择列表达式 |
不一定需要 | 如果与聚集函数一起使用列(或表达式),则必须使用 |
下面是 select 语句中子句的顺序,以在 select 中使用时必须遵循的次序为顺序。
子句 | 说明 | 是否必须使用 |
select | 要返回的列或表达式 | 是 |
from | 从中检索数据的表 | 仅再从表选择数据时使用 |
where | 行级过滤 | 否 |
group by | 分组说明 | 仅在按组计算聚集时使用 |
having | 组级过滤 | 否 |
order by | 输出排序顺序 | 否 |
limit | 要检索的行数 | 否 |
13、使用子查询
什么是子查询呢?子查询就是嵌套在其他查询中的查询,在 where 子句中使用子查询,应该保证 select 语句具有和 where 子句中相同数目的列,通常,子查询将返回单个列并且与单个列匹配。
14、联结表
外键为某一个表中的一列,它包含另一个表的主键值,定义了两个表的关系。如果数据存储在多个表中,使用连接可用单条 select 语句检索出需要的数据。应该保证所有的连联结都有 where 子句,否则 mysql 将返回比想要的多的多的数据,因为此时检索出的行数目是第一个表行数乘以第二个表行数。
联结是 SQL 中最重要最强大的特性。关系表的设计就是要保证把信息分解成多个表,一类数据一个表。各表通过某些常用的值 (即关系设计中的关系 relational) 互相关联。
SELECT vend_name, prod_name, prod_price FROM vecdors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name; 创建连接表
使用 WHERE 子句作为过滤条件,它只包含匹配给定给定条件的行。没有 WHERE 子句,第一个表中的每个行将于第二个表中的每个行配对,而不管它们逻辑上是否可以配对在一起。SQL 对一条 SELECT 语句中可以联结的表的数目没有限制。
基于两个表之间相等测试的联接称为 内部联接。其实,对于这种联结可以使用稍微不同的语法来明确指定联结的类型,下面的 SELECT 语句返回与前面例子完全相同的数据。
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
15、创建高级联结
除了可以给列名和计算字段其别名外,还可以给表起别名。
除了使用内部联接 (或称为等值联接) 的简单链接,还可以使用自联结、自然联结、外部链接。
- 自联结:可以在单条 SELECT 语句中不止一次引用相同的表。
- 自然联结 :无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列),标准的联结(内部联接) 返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次。
- 外部联结:许多联结将一个表中的行与另一个表中的行相关联,但有时候会需要包含没有关联行的那些行。
- select * from persion as haha; 使用表别名
- select proid_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id =‘DTNTR’); 找到生产 ID 为 DTNTR 的物品的供应商,然后找到这个供应商生产的其他物品。这种方式使用了子查询,下面是使用 自联结 的操作:
- SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id =‘DTNTR’;
- SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id; 这是内部联接方式,下面是外部联结方式
- SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
16、组合查询
利用 union 操作符和组合多条 SQL 查询,让给出的多条 select 语句结果组合成单个结果集。注意,union 必须有 2 条或者 2 条以上的 select 语句组成,union 中每个查询必须包含相同的列、表达式或聚集函数(不过每个列不需要以相同的次序列出)。union 会自动去除重复的行,这个是默认的行为,如果不想这样,使用 union all 而不是使用 union。
如何对 union 语句的输出进行排序呢?使用 order by 子句时,必须放在 union 最后一条 select 语句之后,对于结果集,只能有一种排序规则,所以不允许使用功能多条 order by 子句。
17、插入数据
插入语句 insert 一般会有产生输出,一般只会打印影响的行数。insert 时如果不提供列名,则必须给每个表列提供一个值,如果提供列名,则必须对每个列出的列给出一个值,否则报错。
插入一个完整的行:
插入多个行:
18、更新和删除数据
分别使用 update 语句和 delete 语句来进行更新和删除数据操作。先把 goods 表中数据清除掉一部分,goods 表数据如下:
- update goods set num = 1 where name = ‘ 苹果 ’; 此时对应苹果的 num 变为 1
- delete from goods where num = 7; 删除 num 为 7 的记录,也就是 name 为橘子的记录。
注意,这里有一些使用 update 和 delete 语句的一些注意事项:
- 除非确实打算更新和删除每一行,否则绝对不要使用不带 where 子句的 update 或 delete 语句。
- 保证每个表都有主键,尽可能像 where 子句那样使用它。
- 在对 update 或 delete 语句使用 where 子句前,应该先用 select 进行测试,保证过滤的是正确的记录,以防止编写的 where 子句不正确。
- mysql 没有撤销功能,应小心使用 update 或 delete 功能。
19、创建和操纵表
利用 create table 创建表,必须给出表的名字,在关键字 create table 只有给出;表列的名字和定义,用逗号隔开。
create table vendors (vend_id int not null auto_increment,
vend_name char(50) not null,
vend_address char(50) null default '*',
vend_city char(40) null,
primary key (vend_id)
) engine=InnoDB;
这条语句创建一个 vendors 表,vend_id 和 vend_name 是必须的,指定为 not null,其他的为非必须的,指定为 null,null 为默认设置。auto_increment 关键字告诉 mysql,本列每当增加一行时自动增量,可以保证该列值唯一。每个表只允许有一个 auto_increment 列,而且它必须被索引(如,通过使它为主键)。default 为默认值。
注意,主键必须唯一,表中的每个行必须具有唯一的主键,如果主键使用单个列,则它的值必须唯一;如果使用多个列,则这些列的组合必须唯一。外键不能垮跨越引擎。mysql 内部打包了多种引擎,以下几个是需要知道的引擎:
- InnoDB 是一个可靠的事务处理引擎,它不支持全文本搜索。
- MEMORY 在功能等同于 MyISAM,但由于数据存储在内存,所有速度很快(特别适合用临时表)。
- MyISAMysql 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
使用 alter table 语句更新表结构。
删除表(不是其内容,而是整个表)使用 drop 语句即可。drop table table_name; 这条语句删除 table_name 表,只要它存在,注意,删除表时没有确认,也不能撤销。
使用 rename table 语句重命名表。
20、视图
视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询。视图仅仅是用来查看存储在别处数据的一种设施,视图本身不包含数据,因此它们返回的数据是从其他表中检索出来的。视图提供了一种 MySQL 的 SELECT 语句层次的封装,可用来简化数据处理以及重新格式化基础数据或保护基础数据。
使用视图的常见应用:
- 重用 SQL 语句
- 简化复杂的 SQL 操作,在编写查询后,可以方便地重用它而不必知道具体的查询细节
- 使用表的组成部分而不是整个表
- 保护数据,授予客户表的特定部分访问权限而不是全部
- 更改数据格式和表示,视图可返回与底层表的表示和格式不同的数据
使用视图简化复杂的联结,视图使用 create view 语句来创建,使用 show create view viewname 来查看创建视图的语句;用 drop 删除视图,语法为 drop view viewname。
通常,视图是可更新的(可以对它们使用 insert、update 和 delete),更新一个视图将更新其基表,因为视图本身是没有任何数据的。但是,并非所有的视图是可更新的,如果 mysql 不能正确的确定被更新的基数据,则不能被更新,即如果有一下操作,视图不允许更新:分组、联结、子查询、并、聚集函数、distinct 等。
参考
1、MySQL 必知必会 http://www.linuxidc.com/Linux/2016-04/129853.htm
2、高性能 MySQL http://www.linuxidc.com/Linux/2014-10/108464.htm
本文永久更新链接地址:http://www.linuxidc.com/Linux/2016-05/131389.htm