Mysql必知必会笔记

网络图片

1.简介

mysql是一个快速的、多线程的、多用户的、支持健壮SQL的数据库管理系统（DBMS）。mysql可以应用在关键任务、高并发的系统上，也可以用来部署大规模软件。mysql最新版本5.7用户手册官网： mysql5.7用户手册

2.连接mysql

要使用mysql数据库，需要用mysql客户端连接到mysql服务器。服务器可以是你自己创建的，也可以是他人提供的主机，客户端有三种：mysql命令行工具、mysql administrator和mysql query browser。

连接mysql可以在shell中输入如下命令： mysq连接
连接上mysql服务器主机后，可以查看服务器上有哪些数据库、数据库有哪些表、表的构成以及你想操纵的数据库等：

1.SHOW DATABASES; #查看服务器上所有的数据库
2.SHOW TABLES; #查看当前数据库上所有的表
3.USE DATABASE; #选择所要操纵的书籍
4.DESCRIBE TABLE; #描述表结构

3.查询

• 检索数据

1.SELECT * FROM table; #检测全部列
2.SELECT column1,column2 ... FROM table; #检索部分列
3.SELECT DISTINCT * FROM table; #检索不同列，DISTINCT后面所有列
4.SELECT * FROM table LIMIT 3 OFFSET 0; #限制输出结果
5.SELECT * FROM table ORDER BY column DESC/ASC；#输出结果按序排列

• 过滤数据

检索的数据可以通过where子句过滤。

比如：SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;语句检索的是prod_price值为2.50的行记录。SELECT prod_name, prod_price FROM products WHERE prod_price < 10;语句检索的是prod_price小于10的行记录。

where子句支持如下操作符： where子句操作符

此外，where子句也支持 空值检查，SELECT cust_id FROM customers WHERE cust_email IS NULL;语句检索的email值为空的行记录。

where子句 IN 操作符用来指定条件范围，SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002, 1003) ORDER BY prod_name;语句检索的是供应商1002和1003制造的所有产品。

where子句 NOT 操作符否定它之后所跟的任何条件，SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002, 1003) ORDER BY prod_name;语句比上述多了一个NOT操作符，检索的是除1002和1003之外的所有供应商制造的产品。

为了进行更强的过滤控制，MySQL允许给出多个 WHERE 子句。这些子句可以同过 AND 或 OR 来组合使用。SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price < 10;语句检索供应商1003制造的价格小于10的所有产品。当AND 和OR操作符同时出现在一个查询子句中，AND比OR的优先级更高，可以用括号来显示的指定执行的次序。

where子句也支持通配符 LIKE 过滤，通配符包括%（匹配任意字符任意次数）和（匹配任意单个字符）。SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';将检索产品名以jet起头的产品。*SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ' ton anvil';*将检索产品名称1 ton anvil和2 ton anvil。注意LIKE '%'不能匹配NULL值。

where子句支持正则表达式 REGEXP 过滤。基本的语法格式类似为SELECT prod_name FROM products WHERE prod_name REGEXP '1000 | 2000';将匹配产品名字中含有1000或2000的记录。正则表达式的语法不在本文中详述。

• 分组数据

分组允许把数据分为多个逻辑组，再对每个组进行聚集运算、检索等操作，这主要使用 GROUP BY 子句和 HAVING 子句。
SELECT vend_id, COUNT( * ) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT( * ) >= 2;语句检索出了具有2个以上、价格为10以上的产品的供应商。

• 子查询

sql允许创建子查询，即嵌套在其它查询中的查询。SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

• 组合查询

sql允许通过 UNION 操作符将多条SELECT语句组合成一个结果集。
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 (1001, 1002);
UNION默认清除了重复的行，使用UNION ALL可以保留重复的行。

4.联结表

联结的创建很简单，规定要联结的所有表以及他们如何关联（通过外键）即可。SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;语句中WHERE子句的联结条件非常重要，如果没有联结条件，将返回笛卡尔积，检索出的行数为第一个表中的行数乘以第二个表中的行数。

• 内部联结

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

• 外部联结

SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

内部联结又称等值联结，和上面所用的where子句效果相同。而外部联结分为左联结和右联结，它还包括没有关联的行。

5.mysql聚合函数

• 拼接字段（Concat）

SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

• 文本处理函数

SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

常用文本处理函数 常用文本处理函数续

• 数字处理函数

常用数值处理函数

• 时间和日期处理函数

SELECT cust_id, order_num FROM orders WHERE Data(order_date) BETWEEN '2005-09-01' AND '2005-09-30';

常用日期和时间处理函数

• 聚合函数

我们经常需要汇总分析数据而不用实际把它们检索出来，比如找出表列的最大值、最小值、平均值等。

SELECT AVG(prod_price) AS avg_price FROM products;

sql聚合函数
聚合函数可以指定DISTINCT来只包含不同的值，默认对所有的行执行计算。

6.插入、更新、删除数据

• 插入数据

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES ('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), ('M.Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

INSERT除了可以直接插入一个指定的行，还可以通过所谓的INSERT SELECT将一条SELECT语句的结果插入表中。

INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_emial, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country FROM custnew;

• 更新数据

UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

• 删除数据

DELETTE FROM customers WHERE cust_id = 10006;
若没有WHERE子句，UPDATE和DELETE语句都将更新或删除整个表记录。
DELETE不会删除表，只会删除表记录，可以使用TRUNCATE TABLE语句完成相同的工作，且速度更快。

7.视图

视图不包含表中任何列和数据，它包含的是一个SQL查询，用于重用SQL语句，简化复杂的SQL操作，保护数据。

1.CREATE VIEW #创建视图
2.SHOW CREATE VIEW viewname #查看创建视图的语句
3.DROP VIEW viewname #删除视图

CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FORM customers, orders, orderitems WHERE custmoer.cust_id = order.cust_id AND orderitems.order_num = orders.order_num; ##创建productcustomers视图

SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2'; ##使用productcustomers视图

8.存储过程

简单来说，存储过程是一条或多条mysql语句的集合。通过封装在容易使用的单元中，简化复杂的操作，同时保证了数据的完整性，而且提高了性能（使用存储过程比使用单独的sql语句要快）。

• 创建存储过程

CREATE PROCEDURE productpricing(
  OUT p1 DECIMAL(8,2),
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN
  SELECT Min(prod_price)
  INTO p1
  FROM products;
  SELECT Max(prod_price)
  INTO ph
  FROM products;
  SELECT Avg(prod_price)
  INTO pa
  FROM products;
END;

• 执行存储过程

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

• 删除存储过程

DROP PROCEDURE productpricing;

9.游标

游标可以对检索出来的行进行批处理，主要用于交互式应用，比如用户需要滚动屏幕上的数据，对数据进行浏览或更改。游标在Mysql中只能用于存储过程。

• 创建游标

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

• 打开和关闭游标

1. OPEN ordernumbers; #打开游标
2. CLOSE ordernumbers; #关闭游标

• 使用游标数据

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 the cursor
    CLOSE ordernumbers;

END;

10.触发器

当想要某条语句在事件发生时自动执行，可以使用触发器。
创建触发器时，需要给出4条信息：

1.唯一的触发器名;
2.触发器关联的表;
3.触发器应该响应的活动（DELETE、INSERT或UPDATE）;
4.触发器何时执行（处理之前或之后）；

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

使用语句 DROP TRIGGER newproduct; 删除触发器。

11.事物处理

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

• 使用commit

在事物块处理中，提交不会隐含的进行，为了明确的提交，使用COMMIT语句：

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

• 使用rollback

MySQL的ROLLBACK命令用来回退MySQL语句：

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

• 使用保留点

ROLLBACK回退整个事物，对复杂的事物可能需要部分回退，这个时候需要在事物块中放置占位符，以便回退到这个占位符，也称为保留点。

SAVEPOINT delete1;    #创建保留点
ROLLBACK TO delete1;    #回退到保留点

12.数据库维护

• 备份数据

1.使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件；
2.使用命令行实用程序mysqlhotcopy从一个数据库赋值所有数据（并非所有数据库引擎都支持这个使用程序）；
3.使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。

• 查看日志文件

MySQL维护管理员依赖的一系列日志文件主要有以下几种：

日志类型	备注
错误日志	包含启动和关闭问题以及任意关键错误的细节，通常名为hostname.err
查询日志	记录所有的MySQL活动，通常名为hostname.log
二进制日志	记录更新过数据的所有语句，通常名为hostname-bin
缓慢查询日志	记录执行缓慢的任何查询，通常名为hostname-slow.log

13.改善性能

1. MySQL具有特定的硬件建议，关键的生产DBMS应该运行在自己的专用服务器上。
2. MySQL使用一系列的默认设置预先配置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。
3. MySQL是一个多用户多线程的DBMS，它经常执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果遇到显著的性能不良，可使用 SHOW PROCESSLIST 显示所有活动进程，用KILL命令终止某个特定的进程。
4. 总有不止一种方法编写同一条SELECT语句，应该试验联结、并、子查询等，找出最佳的方法。
5. 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
6. 一般来说，存储过程执行的比一条一条的执行其中的各条MySQL语句块。
7. 绝不要检索比需求还要多的数据。
8. 必须索引数据库表以改善数据检索的性能。
9. 你的SELECT语句有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接他们的UNION语句，你能看到极大的性能改善。
10. 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。
11. LIKE很慢，一遍来说最好是使用FULLTEXT而不是LIKE。
12. 位于http://dev.mysql.com/doc/的MySQL文档有许多提示和技巧（甚至有用户提供的评论和反馈），一定要查看这些有价值的资料。

ENDING!