Tools

Mysql Notes

基本点

database
table
column
datatype
row
primary key(unique and non-null)
(不更新主键列中的值；不重用主键列的值；不在主键列中使用可能会更改的值；)

SQL(Structured Query Language)

MySQL Administrator
MySQL Query Browser

初见

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql -h 110.110.110.110 -uroot -p abcd123

mysqladmin -uroot -password ab12

grant select,insert,update,
delete on *.* to [email=test2@localhost][color=#355e9e]test2@localhost[/color][/email] identified by \"abc\";

create/drop database database-name;
show databases;
use database-name;
show tables;

show columns from table-name; <=> describe table-name;

show grants;    show status;    show errors;

SQL 语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT * FROM products;
SELECT prod_name FROM products;
SELECT prod_name,prod_id,prod_price FROM products;

SELECT DISTINCT vend_id FROM products; # 返回不同的值

SELECT prod_name FROM products LIMIT 5; # 前5行
SELECT prod_name FROM products LIMIT 5,10; # 从第5行开始的10行；

SELECT products.prod_name FROM products; # 限定表名的列
SELECT products.prod_name FROM crashcourse.products; # 限定数据库的表


SELECT prod_name FROM products ORDER BY prod_name;
SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_name,prod_id;
SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_name DESC;
SELECT prod_name,prod_id,prod_price FROM products ORDER BY prod_name DESC,prod_id;

过滤条件

1
2

SELECT prod_name,prod_id FROM products WHERE  prod_price = 2.50; 
<;  <>; !=; <=; >; >=; BETWEEN AND;

组合过滤

1
2
3
4
5
6
7
8

SELECT prod_name,prod_id FROM products WHERE  prod_price = 2.50 AND prod_id < 1000;
AND; OR;  (AND 优先级高于 OR); IN (IN 比 OR 快); NOT； 

SELECT prod_name,prod_price FROM products WHERE (vend_id = 10002 OR vend_id = 10003 ) AND prod_price <= 1000;

SELECT prod_name,prod_price FROM products WHERE vend_id IN (10002,10003) ORDER BY prod_name;
 
SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (10002,10003) ORDER BY prod_name;

空值

1

SELECT prod_name,prod_id FROM products WHERE  prod_price is NULL;

通配符 LIKE (列匹配)

1
2
3
4
5
6
7
8

%(任何字符出现任何次数)；_(与%一样，但只匹配单个字符)

SELECT prod_name,prod_id FROM products WHERE  prod_name LIKE 'jet%';
SELECT prod_name,prod_id FROM products WHERE  prod_name LIKE '%jet%';
SELECT prod_name,prod_id FROM products WHERE  prod_name LIKE '%jet';
SELECT prod_name,prod_id FROM products WHERE  prod_name LIKE 's%e';

SELECT prod_name,prod_id FROM products WHERE  prod_name LIKE '_ ton anvil';

正则表达式 REGEXP (行匹配)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

.(任意一个字符); 
|;  1|2|3
[]; [a-z]
[:alnum:]; [a-zA-Z0-9]
\\.; .
\\-; -
\\f; 换页
\\n; 换行
\\r; 回车
\\t; 制表
\\v; 纵向制表

*; >=0 个
+; >=1 个;  {1,}
?; 0个或1个; {0;1}
{n};    n次
{n,};   >=n; 
{n,m}   (m<=255);

^; 句首
$; 句尾
[[:<:]]; 词首
[[:>:]]; 词尾

SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;

REGEXP '^[0-9\\.]'
REGEXP '[[:digit:]]{4}'
REGEXP '[0-9]{4}'
REGEXP '[0-9][0-9][0-9][0-9]'

创建计算字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

Concat； (concatenate拼接)

SELECT Concat(RTrim(vend_name), ' (', vend_country, ')') FROM vendors WHERE vend_price < 1000 ORDER BY vend_name;

#### 计算
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM order items WHERE order_num = 20005;

#### 使用函数
RTrim();
LTrim();
Upper();
Lower();
Left();
Length();
Soundex(); //将任何文本串转化为其语音表示的字母数字模式的算法
SubString();


SELECT cust_id, order_num FROM orders WHERE Year(order_data) = 2005 AND Month(order_date) = 9;

分组数据

1
2
3
4
5
6

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

#### 过滤分组
//Where 过滤指定的是行而不是分组，也没有分组的概念；having过滤分组；
//Where 在数据分组前过滤，having在数据分组后过滤；
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING num_prods >=2 ;

子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT order_num FROM orderitems WHERE prod_id = "TNT2";  // 订单信息
SELECT cust_id FROM orders WHERE order_num IN (20005,20007); // 客户id
SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004); // 客户信息

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"));

//表别名及联结
SELECT cust_name,cust_contact 
FROM customers AS c, orders AS o, order items AS oi
WHERE c.cust_id = o.cust_id
AND oi.oder_num = o.order_num
AND prod_id = 'TNT2'

//注意限制有歧义性的子查询
SELECT cust_name,cusr_stat,(SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers;

联结表 join table

相同数据出现多次并不好，关系表设计就是保证把信息分成多个表，一类数据一个表，通过某些常用值关联，便于管理、修改和维护。

比如供应商表vendors和产品表products。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

//联结表的创建非常简单，规定要联结的所有表以及它们如何关联即可
//注意确保WHERE语句的正确性
SELECT vend_name,prod_name,prod_price 
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY  vend_name,prod_name

//内部联结 inner join
SELECT vend_name,prod_name,prod_price 
FROM vendors
INNER JOIN products 
ON vendors.vend_id = products.vend_id

SELECT customers.cust_id,orders.order_num
FROM customers
INNER JOIN orders 
ON customers.cust_id = orders.cust_id

//外部链接，包含那些相关表中没有关联行的行  LEFT OUTER JOIN ON
SELECT customers.cust_id,orders.order_num
FROM customers
LEFT OUTER JOIN orders //从左边表customers选择行行为
ON customers.cust_id = orders.cust_id

//聚集函数，数据汇总
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS ord_num
FROM customers 
INNER JOIN orders // LEFT OUTER JOIN 亦可，匹配未下单客户
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

组合查询 union

在单个查询中从不同表中返回类似结构的数据
对单个表执行多个查询，按当个查询返回数据

1
2
3
4
5
6
7
8
9
10
11
12

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (10001,10003)

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5 
OR vend_id IN (10001,10003)

UNION 必须有两条或两条以上的SELECT语句
每个查询必须有相同的列、表达式或者聚集函数
列数据必须兼容，类型不必完全相同
重复结果自动删除(UNION ALL)
最后一条语句使用OERDER BY

全文本搜索

一般创建表时使用

1
2
3
4
5
6
7
8
9

CREATE TABLE productnotes        
(                
note_id    int    NOT NULL    AUTO_INCREMENT        
prod_id    char(10)    NOT NULL,        
note_date    date time    NULL,        
note_text    text    NULL,        
PRIMARY KEY(note_id),        
FULLTEXT(note_text)        
) ENGIN=MyISAM

创建表时指定FULLTEXT，不要在导入数据时使用 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

SELECT note_text
FORM productnotes 
WHERE Match(note_text) Against('rabbit');
// WHERE Match(note_text) Against('rabbit' WITH QUERY EXPANSION); 查询扩展
// WHERE note_text LIKE '%rabbit%';

SELECT note_text,
    Match(note_text) Against('rabbit') AS rank
FORM productnotes 

// 布尔文本搜索，即使没有FULLTEXT索引也可以用，但是慢
SELECT note_text
FORM productnotes 
WHERE Match(note_text) Against('rabbit' IN BOOLEAN MODE);
// WHERE Match(note_text) Against('rabbit -rope*' IN BOOLEAN MODE);

+ 包含一个词
- 排除
> 包含，增加等级
< 包含，减少等级
() 把词组成表达式
~ 取消一个词的排序值
* 词尾通配符
"" 定义一个短语

//WHERE Match(note_text) Against('+rabbit +rope' IN BOOLEAN MODE);
//WHERE Match(note_text) Against('rabbit rope' IN BOOLEAN MODE);
//WHERE Match(note_text) Against('"rabbit rope"' IN BOOLEAN MODE);
//WHERE Match(note_text) Against('>rabbit <rope' IN BOOLEAN MODE);

插入数据 INSERT

插入完整的行
插入行的一部分
插入多行
插入某些查询结果 

1
2
3
4
5
6
7
8
9
10
11
12
13
14

INSERT INTO customers
VALUES(NULL,'Pop E. LA', '100 Main Street', 'Los Angeles', 'CA', '900046', 'USA', NULL, NULL);

// 安全做法，指名列
INSERT INTO customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)
VALUES('Pop E. LA', '100 Main Street', 'Los Angeles', 'CA', '900046', 'USA', NULL, NULL);

//多条 INSERT 语句；相隔
//多条语句如果插入列相同，Valuse可用(),() 逗号相隔

// INSERT SELECT
INSERT INTO  customer_new(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email)        
SELECT cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email         
FROM customer

更新或删除数据 UPDATE / DELETE

更新表中特定行
更新表中所有行(要小心) 

1
2
3
4
5
6
7
8
9
10

UPDATE customers        
SET cust_email = 'test@info.me' 
//删除某个列的值 SET cust_email = NULL         
WHERE cust_id = 10005     
   
DELETE FROM customers        
WHERE  cust_id = 10005 
// DELETE 删除整行，UPDATE可删除整列
// DELETE 删除表的内容而不是表
// 更快的删除 TRUNCATE TABLE， 删除原来的表并重新创建一个表

创建和操纵表

使用交互式方式创建和管理表
mysql命令行 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

CREATE TABLE customers        
(                
cust_id        int    NOT NULL    AUTO_INCREMENT,        
cust_name      char(50)    NOT NULL,        
cust_city      char(50)    NULL,   
cust_state     char(50)    NULL,
cust_zip       char(50)    NULL,
cust_country   char(50)    NULL,     
cust_email     char(50)    NULL,       
PRIMARY KEY(cust_id),        
) ENGIN=InnoDB;

// 主键使用单个列，其值必须唯一；多个列，列组合值必须唯一
// 主键可以在创建表时定义，也可以在之后定义
// 每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

CREATE TABLE orderitems        
(                
order_num        int        NOT NULL,        
order_item       int        NOT NULL,          
prod_id          char(10)   NOT NULL,  
quantity         int        NOT NULL    DEFAULT 0,
item_price       decimal(8,2)    NOT NULL,      
PRIMARY KEY(order_num,order_item),        
) ENGIN=InnoDB;

// InnoDB 是一个可靠的事务处理引擎，不支持全文搜索；
// MEMORY 功能上等同与MyISAM，数据存储在内存中，速度很快，适用于临时表
// MyISAM 是一个性能极高的引擎，支持全文搜索，不支持事务处理

// 在表的设计中需要花费大量时间来考虑，以便后期不对该表进行大的改动
// 更新表 ALTER TABLE
// ALTER TABLE要极小心，改动前先备份，更改不能撤销，增加了不能删除，删除了不能恢复
ALTER TABLE vendors
ADD vend_phone CHAR(20)

// 外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers 
FOREIGN KEY (prod_id)
REFERENCES customers (cuts_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors 
FOREIGN KEY (vend_id)
REFERENCES vendors (vend_id);

// 删除表 DROP
DROP TABLE customers；

// 重命名 RENAME
RENAME TABLE test TO customers;

RENAME TABLE back_customers TO customers,
    back_vendors TO vendors,
    back_products TO products;

使用视图

视图是虚拟的表，动态查询的封装；视图主要用于检索数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

CREATE VIEW
SHOW CREATE VIEW viewname
DROP VIEW viewname
CREATE OR REPLACE VIEW 

CREATE VIEW product_customers 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 cust_name, cust_contact 
FROM product_customers
WHERE prod_id = 'TNT2';

// 格式化
CREATE VIEW vendor_locations AS
SELECT Concat(RTrim(vend_name), ' (', vend_country, ')') AS vend_title
FROM vendors
ORDER BY vend_name;

// 视图过滤
CREATE VIEW customer_email_list AS
SELECT cust_id,cust_name,cust_eamil
FROM customers
WHERE cust_email IS NOT NULL;

// 视图与计算字段
CREATE VIEW orderitems_expanded  AS
SELECT order_num, prod_id, quantity, item_price,
    quantity * item_price AS expanded_price
FROM orderitems;

// 更新视图
// 可以对其使用INSERT、UPDATE、DELETE操作，更新一个视图将更新其基表

// 以下视图不能操作
// 分组 GROUP BY 和 HAVING
// 联结
// 子查询
// 并
// 聚集函数，Min，Count，Sum
// DISTINCT
// 导出计算列

存储过程

为以后的使用而保存的一条或多条mysql语句的集合，可以视为批文件；
存储过程的3个主要好处：简单、安全、高性能；
存储过程的编写比SQL语句复杂；
多数人可能只有使用权限而没有创建权限； 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

// 执行,类似函数
CALL product_pricing(@price_low,@price_high,@price_avergae);

// 编写
CREATE PROCEDURE product_pricing()
BEGIN 
    SELECT Avg(prod_price) AS price_avergae
    FROM products;
END;

// 删除
DROP product_pricing;

// 一般，存贮过程并不显示结果，而是返回给指定变量
CREATE PROCEDURE product_pricing(
    OUT pl DECIMAL(8,2), // IN 传入
    OUT ph DECIMAL(8,2), // OUT 传出
    OUT pa DECIMAL(8,2)  // INOUT
)
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;

CALL product_pricing(@price_low,@price_high,@price_avergae);// 不输出
SELECT @price_low,@price_high,@price_avergae;

// ordertotal 接受订单号并返回该订单的合计
CREATE PROCEDURE order_total(
    IN  onumber INT, // IN 传入
    OUT ototal DECIMAL(8,2), // OUT 传出
)
BEGIN 
    SELECT Sum(itme_price * quantity) 
    FROM orderitems
    WHERE order_num = onumber INTO ototal;
END;

CALL order_total(20005,@total);
SELECT  @total;

只有在存储过程中包含业务规则和智能处理时，其威力才会显示出来

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

-- Name: order_total
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE order_total(
    IN  onumber INT, // IN 传入
    IN  taxable INT,
    OUT ototal DECIMAL(8,2), // OUT 传出
) COMMENT 'Obtain order total, optionally adding tax'

BEGIN 
    
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentge
    DECLARE texrate INT DEFAULT 6;
    
    -- Get the order total
    SELECT Sum(itme_price * quantity) 
    FROM orderitems
    WHERE order_num = onumber INTO total;
    
    -- Is this taxable?
    IF taxable THEN
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    
    SELECT total INTO ototal; 
END;

CALL order_total(20005,0,@total);
SELECT  @total;
SHOW CREATE PROCEDURE  order_total;

使用游标

需要在检索出来的行中 前进或后退一行或多行。它不是一条语句，而是该语句检索出来的结果。
主要用于交互式应用，可以滚动屏幕上的数据，并对数据进行浏览或修改。

先声明，SELECT 语句检索，再关闭。

1
2
3
4
5
6
7
8
9
10

CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR 
    SELECT order_num FROM orders;
END;

//打开 
OPEN ordernumbers;
CLOSE ordernumbers;

数据库管理

1

mysql -u root -p

数据库维护

1
2
3
4

ANALYZE TABLE orders;
CHECK TABLE orders,order_items;

source creat.sql