mysql 必知必会 笔记

一、正则表达式

 匹配文本中特殊的串

select prod_name
from products 
where prod_name REGEXP '.000'
ORDER BY prod_name;
# 与文字正文1000匹配的一个正则表达式
# REGEXP 后面跟的东西作为正则表达式

 

匹配出2000，和1000 匹配任意一个字符

REGEXP 前面加上 BINARY  可以区分大小写

select prod_name
from products
where prod_name REGEXP '[[:digit:]]{4}' # 从来匹配任意四个数字
order by prod_name;

 

|	或
[]	匹配中括号中的某个元素 [0-9][a-z]
\\	为了匹配特殊字符 前面需要加上\\ 进行转译 也可以用来引用元字符 　　两个是因为mysql 自己解释一个， 正则表达式解释另一个
*	0 个或多个匹配
+	1个或多个匹配（等于{1,}
?	0 个或1个匹配 （等于{0,1})
{n}	指定书目的匹配
{n,}	不少于指定书目的匹配
{n,m}	匹配书目的范围（m不超过255）
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

字符类
[:alnum:]	任意字母和数字 同[a-zA-Z0-9]
[:alpha:]	任意字符 同[a-zA-Z]
[:blank:]	空格和制表 同[\\t]
[:cntrl:]	ASCII 控制字符 （ASCII 0到31和127）
[:digit:]	任意数字 同[0-9]
[:graph:]	与[:print:]相同，但不包括空格
[:lower:]	任意小写字母 同[a-z]
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符 同[\\f\\n\\r\\t\\v]
[:upper:]	任意大写字母 同[A-Z]
[:xdigit:]	任意十六进制数字 同[a-fA-F0-9]

 

 

二、函数

1. 文本处理函数

left()	返回串左边的字符
length()	返回串的长度
locate()	返回串的一个子串
lower()	将串转换为小写
ltrim()	去掉串左边的空格
right()	返回串右边的字符
rtrim()	去掉串右边的空格
soundex()	返回串的soundex值
substring()	返回子串的字符
upper()	将串转换为大写

 

2. 日期函数

AddDate()	增加一个日期(天、周等)
AddTime()	增加一个时间(时、分等)
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

3. 数据处理函数

Abs()	返回一个数的绝对值
cos()	返回一个角度的余弦
exp()	返回一个数的指数值
mod()	返回除操作的余数
pi()	返回圆周率
rand()	返回一个随机数
sin()	返回一个角度的正弦
sqrt()	返回一个数的平方根
tan()	返回一个角度的正切

4.聚集函数 aggregation function

AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

三、子句顺序

select - from - where - group by- having- order by- limit

四、全文本搜索

 

在创建表时启用全文本搜索，在添加表约束的位置加一条，fulltext(列)

最好先创建表，导入数据后，再修改表，定义fulltext

传递给Match()的值必须与 FULLTEXT()定义中的相同。如果指定多个列，则必须列出它 们(而且次序正确)。 

除非使用BINARY方式， 否则全文本搜索不区分大小写。 

(使用全文本搜索)返回以文本匹配的良好程度排序的数据 

 

select note_text
FROM productnotes
WHERE match(note_text) against ('anvils')

 

查询扩展

用来设法放宽所返回的全文本搜索结果的范围 

select note_text
FROM productnotes
WHERE match(note_text) against ('anvils' with query expansion)

表中的行越多(这些行中的文本就越多)，使用 查询扩展返回的结果越好。 

 

布尔文本搜索  boolean mode

即使没有fulltext 索引也可以使用

 

select note_text
FROM productnotes
WHERE match(note_text) against ('heavy' in boolean mode);

select note_text
FROM productnotes
WHERE match(note_text) against ('heavy -rope*' in boolean mode);

# 指包含heavy 但不包含任意以rope开始的词的行

 布尔操作符 ： P129 (书上页码）

+	包含，词必须存在
-	排除，词必须不出现
<	包含，而且增加等级值
>	包含， 且减少等级值
()	把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

 

五、创建游标

创建一个新的表并填入数据（基于现存表数据的修改）

delimiter $
create procedure processorders()
begin
    -- declare local variables
    DECLARE done BOOLEAN DEFAULT 0; # 做为循环的条件
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);

    -- declare the cursor
    DECLARE ordernumbers CURSOR 
    FOR
    SELECT order_num FROM orders;

    -- declare continue handler
    DECLARE CONTINUE HANDLER 
    FOR
    SQLSTATE '02000'
    SET done = 1;  -- 与前面的相呼应
    
    -- create a table to store the results
    CREATE TABLE if not EXISTS ordertotals( order_num INT, total DECIMAL(8,2));

    -- open the cursor
    open ordernumbers;
    
    -- loop through all rows
    REPEAT
        FETCH ordernumbers INTO o;  -- 存入数据
        CALL ordertotal(o,1,t); -- 调用过程
        INSERT INTO ordertotals(order_num,total) values(o,t);  -- 插入上面建的表
    UNTIL done END repeat;  -- 结束循环
    CLOSE ordernumbers; -- 关闭游标
END $

 

六、触发器

触发器只支持 delete insert update

只有表才支持触发器， 视图不支持

触发器不能更新或覆盖，为了修改一个触发器，必须先删除他，然后再重新创建

create trigger newproduct after insert on products for each row select 'product added';

删除触发器

drop trigger newproduct;

书上错: insert  虚拟NEW 表

create TRIGGER neworder after insert on orders 
for each row select NEW.order_num into @sad;

Delete 虚拟OLD 表

delimiter $
create trigger deleteorder 
before delete on orders
for EACH ROW
BEGIN
    insert into archive_orders(order_num,order_date,cust_id)
    values(OLD.order_num,OLD.order_date,OLD.cust_id);
end $

UPDATE 虚拟OLD 表 访问以前的值，引用NEW表 访问新的值

# 保证州名缩写总是大写(不管UPDATE语句中给出的是大 写还是小写):
create trigger updatevendor before update on vendors 
FOR EACH ROW set new.vend_state = UPPER(new.vend_state);