mysql基本表概念_MySQL最基本的概念梳理

[导读]本文根据《MySQL必知必会》(Ben Forta著，2009)整理，基于MySQL4.1-5，可作为深入研究MySQL之前的漱口篇。(基本语句、正则表达式、联结、全文本搜索、增删改查、存储过程、游标、触发器、事务、索引)

本文根据《MySQL必知必会》(Ben Forta著，2009)整理，基于MySQL4.1-5，可作为深入研究MySQL之前的漱口篇。(基本语句、正则表达式、联结、全文本搜索、增删改查、存储过程、游标、触发器、事务、索引)。

基本语句

limit：使用limit(x,y)进行分页等；

NULL判断：Select xxx from yyy where xxx IS NULL;

优先级：Select prod_name,prod_price from products where vend_id=1002 or vend_id=1003 and prod_price>=10;And 优先于 or；

匹配：%与_，后者匹配单个字符；

去空：RTrim(xxx),去掉值右边所有空格；LTrim(xxx),去掉值左边空格；Trim(),去掉两边空格；

拼接，重命名：Select concat(vend_name,’(’,vend_country,’)’)as vend_file from vendors;

字符串处理：Left()、Length()、Locate()找一个串的子串、Lower()大转小、Right()、Soundex()、SubString()、Upper()小转大；

日期时间：AddDate()、AddTime()、CurDate()、CurTime()、Date()、DateDiff()、Date_Add()、Date_Format()、Day()、DayOfWeek()、Hour()、Minute()、Month()、Now()、Second()、Time()、Year()。MySQL的日期格式：yyyy-mm-dd;

数据处理：Abs()绝对值、Cos()、Exp()指数、Mod()除余、Pi()、Rand()随机、Sin()、Sqrt()平方根、Tan()；

聚集：AVG()平均值、COUNT()行数、MAX()最大值、MIN()最小值、SUM()求和；

Select子句顺序：select,from,where,group by,having,order by,limit

正则表达式

REGEXP：Prod_name包含文本1000的：select prod_name from products where prod_name REGEXP’1000’ order by prod_name;

匹配任意一个字符：… where prod_name REGEXP’.000’;

BINARY：正则匹配不区分大小写，匹配要加BINARY,如：where prod_name REGEXP BINARY ‘JetPack.000’;

OR匹配：where prod_name REGEXP ‘1000|2000’;

匹配几个字符之一：where prod_name REGEXP ‘[123]Ton’;

匹配范围：where prod_name REGEXP’[1-5]Ton’;

匹配特殊字符需要转义：where vend_name REGEXP’.’;

也用于元字符：f换页,

换行,制表,回车,v纵向制表;

联结与组合

等值联结：

笛卡尔积：

Select vend_name,prod_name,prod_price from vendors,products order by vend_name,prod_name;

内部联结：

自联结：

等价于：

自然联结(排除多次出现，每个列只返回一次)：

外部联结(包含了在相关表中没有关联行的行):

Left(right) outer join：左(右)边表中选中所有行；

全文本搜索

MySQL常用的两种引擎：MyISAM(支持全文搜索)InnoDB(不支持全文搜索)；

启动全文本搜索：

方法：Macth()+Against()

等价于：

前者是按等级降序，后者不会；前者有索引，更快。

可以在select中加一个计算列：

查询扩展：MySQL对数据和索引进行两遍扫描，第一遍进行基本的全文搜索，找出与条件匹配的所有行àMySQL检查这些匹配行，选择所有有用的词àMySQL再进行全文搜索，使用原来条件+所有有用的词。

增删改查

一般删除用delete，更快删除用truncate table(删除原来的表，新建一个表)。

Mysql没有撤销按钮，需要小心使用update和delete。

创建表：

创建多个主键：

主键是单个，则单个列值唯一，主键时多个，则多个组合起来唯一。

Select last_insert_id()返回最后一个AUTO_INCREMENT值。

引擎类型：MySQL有一个具体管理和处理数据的内部引擎，通过引擎创建表，内部处理你的查询等请求。

三种引擎：InnoDB,可靠的事务处理引擎，不支持全文本搜索；MEMORY功能等同于MyISAM，但数据存储在内存(不是磁盘中)，速度快(适合临时表)；MyISAM,性能极高，支持全文本搜索，不支持事务处理；

增加列：alter table vendors add vend_phone char(20);

删除列：alter table vendors drop column vend_phone;

存储过程

创建存储过程：

调用存储过程：

删除：

使用参数：

调用带参存储过程：

获取结果：

同时使用IN和OUT：

调用IN和OUT存储过程：

智能存储过程：

显示用来创建一个存储过程的create语句：

SHOW CREATE PROCEDURE ordertotal;

获得何时、由谁创建等详细信息：

SHOW PROCEDURE STATUS LIKE ‘ordertotal’；

游标

游标是一个存储在MySQL服务器上的数据库查询，是被查询语句检索出来的结果集。存储游标后，应用程序根据需要滚动或浏览数据。MySQL游标只能用于存储过程(和函数)。

创建游标：

存储过程处理完成后，游标即消失(仅用于存储过程)。

打开游标：

OPEN ordernumbers;

关闭游标：

CLOSE ordernumbers;

使用FETCH访问游标的每一行，指定检索什么数据(列)，检索的数据存在何处，向前移动游标的内部行指针以检索下一行：

使用游标循环检索数据(你可以在循环内放入任何需要的处理)：

对取出的数据做一个实际的处理：

触发器

在某个表发生更改时(DELETE、INSERT、UPDATE)自动处理某些事情。

创建触发器：需要给出四条信息：唯一的触发器名、关联的表、应该响应的活动(删除、插入、更新)、何时执行(处理前、后)。

触发器仅支持表(视图、临时表均不支持)。

删除触发器：

INSERT触发器：使用NEW虚拟表访问被插入的行。

DELETE触发器：引用OLD虚拟表访问被删除的行：

上例使用BEGIN…END为非必须，BEGIN…END的好处：可容纳多条SQL语句。

UPDATE触发器：引用OLD访问更新前的值，引用NEW访问更新后的值。

事务

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

关于事务的名词：回退、提交、保留点(事务处理中设定的临时占位符，可对它发布回退)。

事务的开始：

使用ROLLBACK:

使用COMMIT:一般的MySQL默认自动提交。但在事务处理块儿中，提交不会默认进行：

使用保留点：简单的ROLLBACK和COMMIT会写入或撤销整个事务处理。复杂的事务处理需要部分提交或回退。这时需要在事务处理块儿的合适位置放置占位符。

更改默认的提交行为：

索引

创建索引(INDEX:普通；PRIMARY KEY:唯一且不能为空；UNIQUE:唯一且不允许重复)：

删除索引：