Mysql必知必会笔记

2018-02-26 本文已影响0人 at小宇

网络图片

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;

打开和关闭游标

OPEN ordernumbers; #打开游标

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.改善性能

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

Mysql必知必会笔记

1.简介

2.连接mysql

3.查询

检索数据

过滤数据

分组数据

子查询

组合查询

4.联结表

内部联结

外部联结

5.mysql聚合函数

拼接字段（Concat）

文本处理函数

数字处理函数

时间和日期处理函数

聚合函数

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

插入数据

更新数据

删除数据

7.视图

8.存储过程

创建存储过程

执行存储过程

删除存储过程

9.游标

创建游标

打开和关闭游标

使用游标数据

10.触发器

11.事物处理

使用commit

使用rollback

使用保留点

12.数据库维护

备份数据

查看日志文件

13.改善性能

ENDING!

猜你喜欢

热点阅读