mysql 你必须了解的知识

数据库的简单概念

• DBS（database system） ：数据库与数据库管理系统的总称
• DB（database） ：数据存放的位置
• DBMS（database manage system）：管理数据库的应用程序

1.你并不直接访问数据库；你使用的是DBMS，它替你访问数据库。

2.MySQL是一种DBMS，即它是一种数据库软件。

3.SQL是一种专门用来与数据库通信的语言。

4.只有数据库知道SELECT语句中哪些列是实际的 表列，哪些列是计算字段。从客户机（如应用程序）的角度来看，计算 字段的数据是以与其他列的数据相同的方式返回的

5.聚集函数（count、avg、max、min等）操作针对的想要信息的是汇总信息

简单的操作注意点

1.在处理SQL语句时，其中所有空格都被忽略。SQL 语句可以在一行上给出，也可以分成许多行。同时也s忽略大小写

2.检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1 将检索出第二行而不是第一行

3.where 过滤查询信息时，优先进行 AND 逻辑， 后进行 OR逻辑

4.IN操作符一般比OR操作符清单执行更快。 IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建 立WHERE子句。

5.% 代表搜索模式中给定位置的0个、1个或多个字符。

6.在确实需要使用通配符时，除非绝对有必要，否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起 来是最慢的。

7.regexp 'str' 默认相当于 like '%str%'

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

9.GROUP BY子句和HAVING子句是一对

10.varchar虽然是可变长的但仍需要增加最大长度

子查询

相关子查询：涉及外部查询的子查询

select customers.cust_name, customers.cust_state,
		(
            select count(*)
            from orders
            where orders.cust_id = customers.cust_id
        )
from customers

多表联结

联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结 及其语法是学习SQL的一个极为重要的组成部分。

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

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

重要的是，要理解联结不是物理实体。换句 话说，它在实际的数据库表中不存在。联结由MySQL根据需 要建立，它存在于查询的执行当中。

在联结两个表时，你实际上做 的是将第一个表中的每一行与第二个表中的每一行配对。

内部联结

select vendors.vend_name, products.prod_name,products.prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id

与下方表示相同

select vendors.vend_name, products.prod_name,products.prod_price
from vendors, products
where vendors.vend_id = products.vend_id

MySQL在运行时关联指定的每个表以处理联结。 这种处理可能是非常耗费资源的，因此应该仔细，不要联结 不必要的表。联结的表越多，性能下降越厉害。

高级联结

**自联结 **

select p1.prod_name
from products as p1 inner join products as p
on p.prod_id = 'DTNTR'
and p1.vend_id = p.vend_id

外联结

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字 指定包括其所有行的表

分组结果无法展示一对多关系，只能展示对汇总的关系

Union（组合查询）

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。

全文本搜索

并非所有引擎都支持全文本搜索

两个最常使用的引擎为MyISAM和InnoDB， 前者支持全文本搜索，而后者不支持。

启用全文本搜索

create table productnotes(
	note_id int not null auto_increment,
    note_text text null,
    ....,
    primary key(note_id),
    fulltext(note_text)
) engine=MyISAM;

正如所见，全文本搜索提供了简单LIKE搜索不能提供的功能。而且， 由于数据是索引的，全文本搜索还相当快。

如果数据检索(query)是最重要的（通常是这样），则你可以通过在 INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL 降低INSERT语句的优先级，如下所示：

insert into low_priority into

insert into 性能提升

其中单条INSERT语句有多组值，每组值用一对圆括号括起来， 用逗号分隔。

提高INSERT的性能 此技术可以提高数据库处理的性能，因 为MySQL用单条INSERT语句处理多个插入比使用多条INSERT 语句快。

insert into table_name (name) values ('zzz'), ('ttt')

insert into与select

将新表的字段插入已有的表

insert into table_name(name) select name from new_table

事实上，MySQL甚至不关心SELECT返回的列名。它使用的是 列的位置，因此SELECT中的第一列（不管其列名）将用来填充 表列中指定的第一个列，第二列将用来填充表列中指定的第二 个列，如此等等。这对于从使用不同列名的表中导入数据是非 常有用的。

update语句

update由三部分构成：

• 表
• 列名+新值
• 过滤条件

update table_nameset name = 'new name'where cust_id = 'zzz'

IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些 行中的一行或多行时出一个现错误，则整个UPDATE操作被取消 （错误发生前更新的所有行被恢复到它们原来的值）。为即使是发 生错误，也继续进行更新，可使用IGNORE关键字，如下所示：

 UPDATE IGNORE customers

引擎简介

1. InnoDB是一个可靠的事务处理引擎，它不支持全文 本搜索;

2. MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘） 中，速度很快（特别适合于临时表）；

3. MyISAM是一个性能极高的引擎，它支持全文本搜索， 但不支持事务处理。

外键（用于 强制实施引用完整性）不能跨引擎，即使用一 个引擎的表不能引用具有使用不同引擎的表的外键。

视图与sql复用

创建更新一个视图

创建更新productcustomers视图

create or replace view productcustomers as select cust_name, cust_contactfrom customers, orders,orderitemswhere customers.cust_id = orders.cust_idand orderitems.order_num = orders.order_num

查询一个视图

select * from productcustomers

等价于===>

select cust_name, cust_contactfrom customers, orders,orderitemswhere customers.cust_id = orders.cust_idand orderitems.order_num = orders.order_num

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相 同的名字）。

对于可以创建的视图数目没有限制。

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造 一个视图。

视图不能索引，也不能有关联的触发器或默认值。

视图可以和表一起使用。例如，编写一条联结表和视图的SELECT 语句。

对视图进行update类操作会更新视图的基表，但是视图主要用于数据的检索而不是更新

存储过程

有点像批处理

使用存储过程有3个主要的好处，即简单、安全、高性能。

存储过程的调用叫做CALL

call store_process_name()

创建存储过程

用CREATE PROCEDURE productpricing()语 句定义。如果存储过程接受参数，它们将在()中列举出来。

create procedure store_process_name()begin	select Avg(prod_price) as avg_price	from productsend;

在MySQL处理这段代码时，它创建一个新的存储过程productpricing。没有返回数据，因为这段代码并未调用存储过程，这里只是为 以后使用而创建它。

delimiter更换分隔符

end后分割符为;后面不能接sql语句，使用delimiter更换成其他分隔符如//可以进行sql

**“@“**一个变量的标识符

MySQL支持IN（传递给存储过程）、OUT（从存 储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参 数。

单独OUT案例

drop procedure if exists productprincing;delimiter //create procedure productprincing(out min_price decimal(8,2), out max_price decimal(8,2), out avg_price decimal(8,2))begin	select min(prod_price)	into min_price	from products;	select max(prod_price)	into max_price	from products;	select avg(prod_price)	into avg_price	from products;end //delimiter ;call productprincing(@p1, @p2, @p3);select @p1;

OUT与IN集合

drop procedure if exists ordertotal;delimiter //create procedure ordertotal(	in ordernum int,	out ototal decimal(8,2))begin select sum(quantity * item_price) into ototal from orderitems where order_num = ordernum;end //delimiter ;call ordertotal(20005, @total);select @total;

只有在存储过程内包含业务规则和智能处理时， 它们的威力才真正显现出来。

drop procedure if EXISTS ordertotal;delimiter //create procedure ordertotal(	in onumber int,	in taxable boolean,	out ototal decimal(8,2))begin	declare total decimal(8,2);	declare taxrate int default 6;	select sum(item_price*quantity)	from orderitems	where order_num = onumber	into total;	if taxable then	select total + total / 100 * taxrate into total;	end if;	select total into ototal;end //delimiter ;call ordertotal(20005, 1, @ototal);select @ototal;call ordertotal(20005, 0, @ototal);select @ototal;show procedure status

游标

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

MySQL游标只能用于 存储过程（和函数）。

游标的创建与使用

• 创建语句：declare cursor_name cursor for
• 使用游标：open cursor_name
• 获取下一行数据：fetch cursor_name
• 关闭游标：close cursor(默认在存储过程的end时关闭游标)

drop procedure if exists processorders;delimiter //create procedure processorders(out output int)begin	declare ordernumbers cursor	for	select order_num from orders;	open ordernumbers;	fetch ordernumbers into output;end //delimiter ;call processorders(@output);select @output;

触发器

响应update、insert、delete语句并触发一个语句执行

mysql中触发器名必 须在每个表中唯一，其他dbms不一定统一

只有表才支持触发器，视图不支持（临时表也不 支持）。

事务

MyISAM和InnoDB是两种最常使用 的引擎。前者不支持明确的事务处理管理，而后者支持。

事务处理是一种 机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完 整的操作结果。

利用事务处理，可以保证一组操作不会中途停止，它们 或者作为整体执行，或者完全不执行（除非明确指示）。

• 事务（transaction）：一组sql
• 回退（rollback）
• 提交（commit）
• 保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

你不能回退CREATE或DROP操作。事务处理块中可以使用 这两条语句，但如果你执行回退，它们不会被撤销。

**隐含事务关闭 **：当COMMIT或ROLLBACK语句执行后，事务会自 动关闭

字符集问题

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同 的方式存储和检索。

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示
• 校对为规定字符如何比较的指令

不同的表，甚至不同的列都可能需要不同的字符集，而且两者都 可以在创建表时指定。

访问权限

应该严肃对待root登录的使用。仅在绝对需 要时使用它（或许在你不能登录其他管理账号时使用）。不应 该在日常的MySQL操作中使用root。

MySQL用户账号和信息存储在名为mysql的MySQL数据库中

mysql数据库管理系统采用一个数据库记录所有的用户信息

创建用户

create user user_name identified by 'password';

查看用户权限

show grants for user_name;

赋予权限

grant select on database1.* to zgz;

此语句为名为zgz的用户在database1数据库中赋予select的权利

更换口令

set password for zgz = Password('new_password');

数据备份

改善性能

• 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、 子查询等，找出最佳的方法。
• 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL 语句快。
• 应该总是使用正确的数据类型。
• 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除 非你真正需要每个列）。
• 必须索引数据库表以改善数据检索的性能。
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
• 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条 SELECT语句和连接它们的UNION语句，你能看到极大的性能改 进。

其他

distinct：用于过滤出不一样的select结果

MySQL 5支持LIMIT的另一种替代语法。LIMIT 4 OFFSET 3意为从行3开始取4行，就像LIMIT 3, 4一样。