Mysql必知必会
作者：
吴庭炎 来源：
本站原创 发布时间：2011年12月01日 点击数：
218
STRUCTURED QUERY LANGUAGE
DBMS，数据库管理系统，分为两类，一类基于共享文件系统的DBMS（包括access和filemaker，不用于高端应用），另外一类基于客户机-服务器的DBMS。服务器部分负责数据访问和处理。
使用mysql需要客户机。
工具：Mysql命令行实用程序（快速测试和执行脚本）、mysql administrator（图形交互客户机）、mysql query browser（编写和执行命令）
1. 了解数据库和表show/use
Use + 数据库名；
Show databases；
Show tables；
Show + 表名；
Show columns from customers；
Show status；显示服务器状态
Show create database和create ；table；显示创建数据库的语句
Show grants ；显示授权用户的权限
Show errors/warnings；
2. 检索数据select
检索单行
Select host from field名
检索多行
Select A,B,C from field名
Select * from field名
检索不同的行
Select distinct host from field名
限制结果
Select host from field名 limit5；显示前五个
Select host from field名 limit3,4；从第三个开始，显示4个
完全限定的表名
Select field名.host from field名
*添加table
mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1),birth DATE, birthaddr VARCHAR(20));?
Query OK, 0 rows affected (0.00 sec)
由于name、birthadd的列值是变化的，因此选择VARCHAR，其长度不一定是20??。可以选择从?
1到255的任何长度，如果以后需要改变它的字长，可以使用ALTER?TABLE语句??。）;?
性别只需一个字符就可以表示："m"或"f"，因此选用CHAR(1);?
birth列则使用DATE数据类型??。
创建了一个表后，我们可以看看刚才做的结果，用SHOW TABLES显示数据库中有哪些表：?
mysql> SHOW TABLES;?
+---------------------+?
|?Tables in menagerie |?
+---------------------+?
| mytables|?
+---------------------+
显示表的结构：?
mysql> DESCRIBE mytable;?
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default |Extra |
+-------------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | |?NULL | |
| sex | char(1) | YES | | NULL | |
| birth |date | YES | | NULL | |
| deathaddr | varchar(20) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
3. 排序检索数据order by
Select host from field名 order by host；按字母排列
多列排列
Select A，B，C from field名 order by A,B,C；
降序排序（多列查询，desc跟在个字的id后）
Select A from field名 order by A desc；
与limit结合查询最大最小值
Select A from field名 order by A desc limit 1；
4. 过滤数据where
Select A,b,c from field名 where A=xx;
=等于
<>不等于
!=不等于
<小于
<=小于等于
>大于
>=大于等于
between之间
检查单个值
Select A,b（*） from field名 where A=xx;
不匹配查询
Select A,b,c from field名 where A<>1000;
范围查询 between
Select A,b,c from field名 where Abetween 5 and 10;
空值查询
Select A,b,c from field名 where A is null;
5. 数据过滤and or
Select A,b from field名 where A=xx and b!= 5;
计算次序问题
Select A,b from field名 where A=xx or A=xy and b!= 5;
因为and的优先级高于or，会先算黄色部分，要用括号括住
Select A,b from field名 where （A=xx or A=xy） and b!= 5;
其实有时候可以用between
in操作符
Select A,b from field名 where A in （101,103）;
括号内为合法值，与or功能相当
not操作符
Select A,b from field名 where A not in （101,103）;
6. 通配符过滤
like
Select A,b from field名 where like 'jet%';
%表示任何字符出现任何次数
Select A,b from field名 where like 'jet_';
_表示单个字符
使用通配符技巧
·不要过度使用
·不要放在语句前面，会影响搜索速度
·注意通配符的位置，位置错了可能返回不了
7. 使用Mysql正则表达式
Select A,b from field名 where regexp '100';
regexp虽然像like，但正则表达式匹配整个列，可以与信息相同，且不区分大小写
进行or匹配
Select A from field名 where regexp '1000|2000';
Select A from field名 where regexp '[123] tom';匹配前面有1或2或3的字符
Select A from field名 where regexp '1|2|3 ton';系统理解成1或2或3 ton
匹配范围
Select A from field名 where regexp '[1-10] ton'; [a-z]也可以
要搜索匹配符号，在前面加\\
Select A from field名 where regexp '\\-';匹配\，用\\\
匹配字符类
[:alnum:] 任意字母和数字，同[a-zA-Z0-9]
[:alpha:] 任意字符,同[a-zA-Z]
[:blank:]空格和制表
[:cntrl:]ACII控制字符
[:digit:]任意数字[0-9]
[:graph:]同print，不包括空格
[:lower:]任意小写字母
[:print:]任意打印字符
[:punct:]不在alnum也不在cntrl的字符
[:space:]包括空格在内的任意字符
[:upper:]大写
[:xdigit:]任意十六进制数字
匹配多个实例
Select A from field名 where regexp '\\([0-9] stick?\\)';?表示任意字符出现0或1次
Select A from field名 where regexp '[[[:digit:]]{4}';任意连续出现的4个数字，也可以'[0-9][0-9][0-9][0-9]'
^文本开始，$文本结束
搜索一个以数字开始的数，'^[0-9\\.]'
8. 创建计算字符conat
字段field=列column，
拼接concatenate
Select concat（name1,'('country,'）') fromXX 将name和country拼接
使用别名as
Select concat（name1,'('country,'）') as name2 from XX,拼接后给他一个名字
换行算术计算
Select A,B,C,A*B as D FROM fielname where XXX
+-*/
9. 使用处理函数
Select A,upper(A) as name from order by A
upper大写 left（）返回左边字符 right（） lower（）小写 LTrim（）去掉左边空格 RTrim（）去掉右边空格
soundex查找发音相似的数据
Where soundex（A）=soundex（'y LIE'）
日期和时间处理函数
10. 汇总数据
聚集函数aggregate function
·确定表中行数
·获取表中行组的和
·找到表列的最大值、最小值、平均值等
Select avg（A） as B FROM field名
Select count（*） as B from field名 计算行数
max
min
sum计算和
聚集不同的值 distinct(支持5以后的版本)
Select avg（distinct A） as B FROM field名，如果指定列明，distinct只用于count()，不用于count(*)
组合聚集函数
Select count(*) as A,
Min(b) as B,
Max(c) as C,
Avg(d) as D from field名
11. 分组数据
返回每个对象的产品数目
Select a，count(*) as B from field名 group by field名；
过滤分组having
上面加一句，having count(*)>=2，得出分组大于等于2的对象
where过滤行，having过滤分组，
分组和排序
Group by分组行，但输出地可能不是分组的顺序，解决方法：order by
Select order_num,sum(A*B) AS Cf from field name group by order_num having sum(A*B)>50 order by C
字句顺序
Select
From
Where
Group by
Having
Order by
limit
12. 使用子查询
任何SQL语句都是查询，譬如检索所有订单——检索具有前一步的客户ID——检索前一步客户ID的客户信息
Select cust_id from orders where order_num in(select order_num from orderitems where prod_id="TNT2");
先查物品为TNT2的订单，然后返回客户ID
现在再查询客户信息
Select cust_name,cust_contact from customers
Where cust_id in (Select cust_id from orders where order_num in(select order_num from orderitems where prod_id="TNT2"));
查询比较需要限定域名
13. 联结表join 最重要的操作
主键primary key：标示一个字段的唯一的值；外键foreign key：定义两个表之间的关系，联系起来
数据在多个表中，需要用连接来查询完整的数据
维护引用完整性——不允许随意插入数值，只允许特定的表添加数值
创建联结
Select vend_name,prod_name,prod_price
from vendors,products
Where vendors.vend_id=products.vend_id
Order by vend_name,prod_name;(需要限定列名)
笛卡尔积
没有联结关系的表返回的结果。检出的行数是第一个表的行数乘以第二个表的行数
内部联结（效果一样，ANSI SQL规范首选inner join）
Select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id=products.vend_id
联结多个表
Select vend_name,prod_name,prod_price,quantity
from vendors,products,orderitems
Where vendors.vend_id=products.vend_id
And orderitems.pro_id=vendors.vend_id
And order_num = 20005;
操作比较耗费资源，考虑清楚再做
联结比嵌套简单
14. 创建高级联结
使用表别名
之前别名检索
Select concat(vend_name,vend_country as vend_title
From vendors
Order by vend_name;
且看select cust_name,cust_contact
From customers as c,orders s o,orderitems as oi
Where c.cust_id=o.cust_id
And oi.order_num=o.order_num
And prod_id = 'TNT2';只在查询中使用，别名不返回客户机
使用不同类型的联结
自联结
Select p1.pro_id,p1.prod_name
From products as p1,products as p2
Where p1.vend_id = p2.vend_id
And p2.prod_id = 'DTNTR';
自然联结
标准的联结返回相同的数据，自然联结排错重复出现，每列返回一次
Select c.*,o.order_num,o.order_date,
Oi.prod_id,oi.quantity,OI.item_price
From customers as corders as o,orderitems as oi
Where c.cust_id=o.cust_id
And oi.order_num=o.order_num
And prod_id='FB';
外部联结（无关联的联结）
Select cust_id,prod_name,prod_price
from customers left outer join products
on customers.cust_id=products.cust_id;
使用带聚集函数的联结（函数可以和联结一起使用）
15. 组合查询union
需要用到的情况
·单个查询中从不同的表返回类似结构的数据；
·单个表执行多次查询，按单次查询返回数据。
创建组合查询
语句之间放上union
Select vend_id,prod_id,prod_price
From products
Where pro_price=5
Union
Select vend_id,pro_id,prod_price
From products
Where vend_id in (1001,1002);
需要注意，
·必须由两条以上select语句组成
·每个查询必须包含相同的列、表达式或聚集函数
·列数据类型必须兼容
包含和取消重复的行
union自动去除重复的行，union all可以显示重复的。
排序时，只允许使用一个order by
16. 全文本搜索
LIKE和正则表达式存在局限
性能。搜索行数增加时，搜索耗时
明确控制。很难明确控制匹配什么和不匹配什么
智能化的结果。比较机械
·启用全文搜索支持
不要在导入数据时使用fulltext
·进行全文本搜索
使用match（）和aginst（）执行
match指定搜索的列，against指定要搜索的内容
Select note_text
From productnotes
Where match(note_text) against('rabbit');
可以用like子句
Select note_text
From productnotes
Where note-text like '%rabbit%';
使用查询扩展（用于查询与搜索相关但不包含词的内容）
流程：进行全文搜索——检查匹配行和有用的词——再次用有用的词搜索
只用于4.1.1版本以上
Select note_text
From productnotes
Where match(note_text) against('rabbit' with query expansion);
布尔文本搜索boolean mode，即使创建table时没有定义full text也可以用。另一种方式，提供要匹配的词，要排斥的词，排列提示。表达式分组和另外一些内容
Select note_text
From productnotes
Where match(note_text) against('rabbit' in boolean mode);
包含heavy，不包含以rope开始的词
Select note_text
From productnotes
Where match(note_text) against('heavy -rope*' in boolean mode);
布尔操作符
+必须包含
-必须排除
>包含，增加等级值
<包含，减少等级值
()把词组作为表达式
~取消一个词的排序
*词尾通配符
""定义短语
·使用说明
短词被忽略且排除（3个字符以下，但可以改）
mysql自带非用词被忽略
出现频率高于50%行的词不出现
表中行数小于3，不返回结果
忽略单引号
不具有分隔符语言不能反悔全文本搜索结果
17. 插入数据insert
Insert into customers
values（null，
'pep',
'100 main street',
'new york',
'CA',
）;
更好更安全的方法
Insert into customers （cust_names,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
）
Values('a',
'b',
'c',
'd',
null
);
可以不按字段顺序，但values要对应
插入多行
上面方法重复输入或者
Insert into customers （cust_names,
Cust_address,
Cust_city,
Cust_state,
Cust_zip,
）
Values('a',
'b',
'c',
'd',
null
)，
Values('e',
'f',
'g',
'h',
null
）;
插入检索出的数据
Insert into customers （cust_names,
Cust_address,
Cust_city,
Cust_state,
Cust_zip
）
Select cust_id,cust_names,
Cust_address,
Cust_city,
Cust_state,
Cust_zip
From custnew;
将custnew的数据导入customers
18. 更新和删除数据
更新数据update
方法·更新特定行；更新所有行
Update customers
Set cust_email = 'elmer@fudd.com',
cust_name = 'fudd'
Where cust_id=10005;
删除数据
Delete from customers
Where cust_id = 10006;
指导原则
·更新和删除前先select一下，一定要带where
·保证每个表都有主键
·使用强制引用完整性的数据库，这样不允许删除与其他表相关联的数据的行
19. 创建和操纵表
创建表
Create table customers
（cust_id int not null auto_increment,
Cust_name char(50) not null,
Cust_zip char(10) null,
...，
Primary key(cust_id)
）engine=innoDB;
使用null值
Not null不允许为空
null，允许为空
主键不唯一，创建时用逗号隔开即可
Auto_increment，自动增量，给该列赋予下一个可用的值
指定默认值
Create table customers
（cust_id int not null auto_increment,
Cust_name char(50) not null,
Cust_zip char(10) null default1,
...，
Primary key(cust_id)
）engine=innoDB;
把zip 这个值在未指定的情况下默认为1
引擎类型
innoDB，不支持全文搜索，支持事务处理
memory速度很快，适用于临时表
My ISAM不支持事务处理，支持全文搜索
引擎可以混用
更新表
增加列
Alter table vendors
Add vend_phone char(20);
删除列
Alter table vendors
Drop column vend_phone;
删除表
Drop table customers2；
重命名表
Rename table customers2 to customers；
20. 使用视图，5以上版本
只包含动态检索数据的查询
创建视图
Create view productcustomers as
Select cust_name,cust_contact,prod_id
From customers,orders,orderitems
Where customers.cust_id = orders.cust_id
And orderitems.order_num = orders.order_num;
然后可以直接对新的表进行查询
·用视图重新格式化检索出来的数据
Select
As
From
Order XXX
Create view 视图名 as
Select concat()
From
Order
然后即可以查询了
·用视图过滤不想要的数据
Create view 视图名 as
Select cust_id,cust_name,cust_email
From customers
Where cust_email is not null;
·用视图计算字符
Create view orderXXX as
Select prod_id,quantity,item_price,quantity*item_price as expanded_price
From orderitems
Where order_num =2005;
更新视图
如果含有以下操作，则不能更新
分组，链接，子查询，并，聚集函数，distinct，导出
21. 使用存储过程（5以后）
经常一个完整的操作需要多条语句完成。存储过程，位以后使用而保存起来的语句集合，可视为批文件
为什么使用？
简化复杂操作；保证数据完整性，防止执行错误；简化改动管理；提高性能；即简单、安全、高性能
·使用存储过程
Call productpricing(
@pricelow.
@pricehigh,
@priceaverage);
创建存储过程
Create procedure productpricing()
Begin
Select avg(pro_price) as priceaverage
From products;
End;
使用这个存储过程
Call productpricing();
删除存储过程
Drop procedure productpricing;
使用参数
一般存储过程不显示结果，而把结果返回给指定的变量。
Create procedure productpricing(
Out pl decimal(8,2),
Out ph decimal(8,2),
Out pa decimal(8,2)
)
Begin
Select min(prod_price)
Into pl
From products;
Select max(prod_price)
Into ph
From products;
Select avg(prod_price)
Into pa
From products;
End;
指定变量，正好3个
Call productpricing(
@pricelow.
@pricehigh,
@priceaverage);
调用的时候，select @pricingaverage;
·建立智能存储过程（比较复杂，暂时不学）
·检查存储过程
Show create procedure；
22. 使用游标
有时需要在结果中前进或后退几行，需要使用游标cursor
步骤：声明游标——打开游标——根据需要取出各行——关闭游标
创建游标
Create procedure processorders()
Begin
Declare ordernumbers cursor
For
Select order_num from orders;
End;
打开游标open ordernumbers;close ordernumbers;
使用游标数据
Create procedure processorders()
Begin
Declare o int;
Declare ordernumbers cursor
For
Select order_num from orders;
Open ordernumbers;
Fetch ordernumbers into o'
Close ordernumbers;
End;
fetch用于检索当前行到一个名为o的局部变量
可用于循环检索数据，有点像javascript了
23. 使用触发器
表发生变更的时候触发其他处理，只支持delete，insert和update
创建触发器
Create trigger newproduct after insert on products
For each row select 'product added';
删除触发器
Drop trigger newproduct；
使用触发器
·insert触发器
Create trigger neworder after insert on orders
For each row select new.order_num;
测试
Insert into orders(order_date,cust_id)
Values(now(),10001);
delete触发器，update触发器
24. 管理实务处理
维护完整性，保证完全执行或完全不执行
使用rollback
Select * from ordertotals;
Start transaction;
Delete from ordertotals;
Select * from ordertotals;rollback;select*from ordertotals;
使用commit隐含提交
Start transaction;
Delete from ordertotals;
Delete orders where xxx;
Commit;------写出更改
使用保留点
复杂事务需要部分提交和回退
Save point delete1;
Rollback to delete1;
关闭默认提交行为
Set autocommit=0;
25. 全球化和本地化
校对，规定字符集如何比较
使用字符集和校对顺序
show character set;
Show collation;
指定字符集和校对
Create table mytable
(
Columnn1 int,
Columnn2 varchar(10)
)
Default character set hebrew
Collate hebrew_general_ci;
可以每一列设定
还可以按照校对顺序排序
Select * from customers
Order by lastname,firstname collate latin1_general_cs;
26. 安全管理
访问控制
管理用户
USE MYSQL;
SELECT user from user;
创建账号
Create user 账号 identified by '密码'；
删除账号
Drop user XX;
设置访问权限
查看 show grants for XX;
分配权限
Grant select on XXXX.* to ben;
给select权限
撤销
Revoke select on XXXX.* FROM ben;
GRANT ALL整个服务器
ON DATABASE.*整个 塑胶扣
On database.table特定的表
修改口令
Set password for ben =password（'xxxxx'）;
27. 数据库维护
备份
Check analyze
查看日志
28. 改善性能
