《MySQL必知必会》

Posted on

MySQL入门

基本操作

MySQL的配置文件在/etc/mysql/mysql.conf/文件夹下面，数据存放在var/lib/mysql下面

DDL

  • USE database_name：选择数据库
  • SHOW DATABASES：显示所有数据库
  • SHOW TABLES：显示某一数据库中所有表，使用之前要先选择特定数据库
  • CREATE DATABASE database_name：创建数据库
  • CREATE TABLE table_name(col_name_1 type,col_name_2 type,...)：创建表create table `stu_info` (`id` int, `name` varchar(40));
  • CREATE UNIQUE INDEX index_name ON table_name(col_name)：创建唯一索引（索引只是为了加速查询）
  • CREATE INDEX index_name ON table_name(col_name)：创建简单索引
  • DROP TABLE：删除表
  • DROP INDEX：删除索引

数据类型

数值

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)

日期/时间

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值

字符串

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

通配符

LIKE操作符指示MySQL后跟的搜索模式利用通配符而不是直接相等匹配

最常见的通配符是%，表示任何字符出现任意次数

1
2
3
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'h%eda';

通配符可以位于搜索模式的任意位置，且搜索可以是区分大小写

另一个通配符是_，与%用处一样，但是只匹配单个字符，不能多也不能少

通配符虽然好用，但是不能过度使用，因为相比其他搜索花费时间更长

插入数据

1
2
3
4
mysql> insert into stu_info
    -> values(17052231,
    -> 'robert');
Query OK, 1 row affected (0.16 sec)

存储过程

存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合

使用存储过程的好处就是简单、安全、高性能；但是编写存储过程比写SQL复杂，不一定含有创建存储过程的权限

CALL processname(@parm1,@parm2);：执行存储过程
CREATE PROCEDURE pro_name();：创建存储过程
DROP PROCEDURE pro_name;：删除存储过程
例子

1
2
3
4
5
6
7
8
9
10
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num=onumber
    INTO ototal;
END;

MySQL支持IN（传递给存储过程）、OUT（从存储过程中传出）和INOUT（对存储过程传入传出）类型的参数

调用该存储过程需要输入变量，MySQL中变量都是以@开始，CALL ordertotal(20005,@total);，改调用语句不产生任何数据，要显示输出变量可以SELECT @total;

只有包含业务规则和智能处理时，才能发挥存储过程的真正威力

SHOW CREATE PROCEDURE pro_name;：显示创建存储过程的语句
SHOW PROCEDURE STATUS pro_name：显示何时、由谁创建等详细信息

视图

视图是虚拟的表，只包含使用时动态检索数据的查询，不包含实际数据。就像正视图是物体从正面看的样子，视图就是原始表从某个角度看到的样子，而定义这个角度就是查询

有了视图，客户就不用再操心访问的数据是基本数据还是派生数据，这就体现了这种封装方式的方便

使用视图可以

  • 重用SQL语句
  • 在编写查询后，可以方便重用而不必知道他的基础查询细节
  • 使用表的组成部分而不是整个表
  • 保护数据，可以授予用户表的特定部分的访问权限而不是整个表的访问权限
  • 更改数据格式或表示，视图可以返回与底层表不同的的格式

在创建和使用视图时也有一些常见规则和限制

  • 视图唯一命名
  • 视图数目没有限制
  • 创建视图需要访问权限
  • 视图可以嵌套
  • 视图不能索引，没有关联的触发器或默认值

CREATE VIEW：创建视图
SHOW CREATE VIEW viewname：查看创建视图的语句
DROP VIEW viewname：删除视图
CREATE OR REPLACE VIEW：更新视图

下面的例子就创建了vendorlocations视图，通过该视图可以重新格式化检索出的数据

1
2
3
4
5
6
7
8
CREATE VIEW vendorlocations AS
SELECT Concat(PTrim(vend_name),'(',PTrim(vend_country),')')
        AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT * 
FROM vendorlocations;

联结表

联结是SQL中最强大的特性

关系表的设计就是要保证把信息分解成多个表，一类数据一个表，各表通过某些常用的值（即关系）相关联

外键为某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系

通过联结，单条SELECT语句可以检索出存储在多个表中的数据。联结不是物理实体，而是MySQL根据需要建立，它存在于查询的执行当中

创建联结：vendors,products两个表通过WHERE子句正确联结，应该保证所有联结都有WHERE子句，下面距离的联结也成为等值联结或者内部联结，可以联结的表的数量没有限制，但是联结的表越多，查询性能下降越厉害

1
2
3
4
SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name,prod_name;

上面的例子也可以这么写

1
2
3
SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id

SQL JOIN

游标

游标是一个存储在MySQL服务器上的数据库查询，是该语句检索出来的结果集，只能用于存储过程

使用游标需要经过几个步骤：创建游标，打开游标，检索、关闭游标

创建游标：DECLARE，如下

1
2
3
4
5
6
CREATE PROCEDURE processorders()
BEGIN
	DELCARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
END;

打开游标：OPEN CURSOR，在处理OPEN语句时执行查询，检索出来的结果集用于浏览与滚动

关闭游标：CLOSE CURSOR，释放游标使用的所有内存与资源

使用游标数据：FETCH，访问结果集的每一行，指定检索什么数据，检索出来的数据存储在什么地方，还将自动移动到下一行

在下面的例子中，使用FETCH不断检索order_num并写入到新表中，反复执行直到done为真，DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;这条语句即是设置done为真，它指出状态码02000出现时（即没有找到）设置done为真

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
CREATE PROCEDURE processorders()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
	
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    CREATE TABLE IF NOT EXISTS ordertotals
        (order_num INT,total DECIMAL(8,2));

    OPEN ordernumbers;

    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;

触发器

  • 在学生表中拥有字段学生姓名，字段学生总数，每当添加一条学生信息时，学生的总数就必须同时更改
  • 在学生表中还会有学生姓名的缩写，学生住址等字段，添加学生信息时，往往需要检查电话、邮箱等格式是否正确

上面的例子使用触发器完成时具有这样的特点，需要在表发生改变时，自动进行一些处理。MySQL在触发DELETE INSERT UPDATE语句时就会自动执行所设置的操作，其他SQL语句则不会激活触发器，是一种特殊的存储过程

创建触发器需要4条信息

  • 惟一的触发器名
  • 触发器关联的表
  • 触发器应该相应的活动（DELETE INSERT UPDATE）
  • 触发器何时执行（处理之前还是之后）
1
2
3
4
5
6
7
8
9
10
11
12
CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_name：触发器的名称，不能与已经存在的触发器重复；
trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；
trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；
tbl_name：该触发器作用在tbl_name上；
trigger_body：Boby中使用BEGIN、END可以执行多条SQL语句

例子

1
2
3
4
5
CREATE 
    TRIGGER newproduct 
    AFTER INSERT 
    ON products FOR EACH ROW 
    SELECT 'Product added';

每个表每个事件只允许一个触发器，每个表最多支持6个触发器

删除触发器：DROP TRIGGER newproduct

INSERT触发器：可以引用一个NEW虚拟表，访问被插入的行；而且在BEFORE INSERT触发器中，NEW表中的值也可以更新；对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在执行之后包含新的自动生成值，例子

1
2
3
4
5
6
7
CREATE 
    TRIGGER neworder
    AFTER INSERT 
    ON orders FOR EACH ROW 
    SELECT NEW.order_num;

order_num由MySQL自动生成，当插入一个新订单时，触发器从NEW.order_num取得这个值并返回它

DELETE触发器：可以引用OLD虚拟表，访问被删除的行；OLD的值全部都是只读的，不能更新；例子

1
2
3
4
5
6
7
8
CREATE 
    TRIGGER deleteorder
    BEFORE DELETE
    ON orders FOR EACH ROW 
    BEGIN
        INSERT INTO archive_orders(order_num,order_data,order_id)
        VALUES(OLD.order_num,OLD.order_data,OLD.order_id);
    END;

UPDATE触发器：既可以引用OLD，也可以引用NEW虚拟表；在BEFORE UPDATE触发器中，NEW的值可以被更新；OLD的值不能更新

触发器能保证数据的一致性（大小写、格式等）；可以创建审计追踪，通过触发器把更改记录到另一个表

事务

在单一操作中更新跨越多张表的数个行，该操作要么成功，要么失败，是数据库运行中的逻辑工作单位，其本质就是一系列操作，但操作的总和要满足ACID属性

  • 事务：一组SQL语句
  • 回退：撤销指定SQL语句的过程
  • 提交：将为存储的SQL语句结果写入数据库表

  • 保留点：事务处理设置的临时占位符，对其发布回退

  • START TRANSACTION：标识事务的开始，事务处理只管理INSERT、UPDATE和DELETE命令，对于DREATE、DROP操作，即使执行回退，也不会撤销

  • ROLLBACK：用于回退，只能在一个事务处理内使用，即在START TRANSACTION命令之后，执行ROLLBACK之后，事务自动关闭，更改隐含提交
  • COMMIT：一般的MySQL语句是隐含提交的，当在事务处理模块时，提交必须显式提交，使用COMMIT，且只在不出错的时候写出更改

  • SAVEPOINT：为了回退部分事务处理，可以在事务处理合适的位置放置占位符，回退时只需回退到某个占位符，这些占位符就称为保留点，保留点在事务处理完成后会自动释放，也可以显式释放RELEASE SAVEPOINT，如

    1
    2
    3
    4
    5
    6
    7
    START TRANSACTION
    ...
    SAVEPOINT delete1;
    ...
    ROLLBACK TO delete1;
    ...
    COMMIT

  • SET autocommit=0;：默认是自动提交所有更改，设置该标志可以取消自动提交更改

如下例子

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
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stu_info values ( 17052230, 'sam');
Query OK, 1 row affected (0.09 sec)

mysql> select * from stu_info;
+----------+--------+
| id       | name   |
+----------+--------+
| 17052231 | robert |
| 17052230 | sam    |
+----------+--------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from stu_info;
+----------+--------+
| id       | name   |
+----------+--------+
| 17052231 | robert |
+----------+--------+
1 row in set (0.00 sec)

维护

有多种方式可以实现数据备份

  • 使用命令行工具mysqldump转储所有内容到某个外部文件
  • 使用命令行工具mysqlhotcopy从一个数据库复制所有数据
  • BACKUP TABLE file_name或者SELECT INTO OUTFILE file_nameSQL语句来转储所有数据到某个外部文件

MySQL提供一些语句来保证数据库正确运行

  • ANALYZE TABLE：检查表键是否正确
  • CHECK TABLE：针对许多问题对表进行检查

多数MySQL服务器是作为系统进程或服务自动启动的，在派出系统启动问题时，要学会使用命令行工具，mysqld用于启动执行

查看日志也很重要

  • 错误日志：包含启动和关闭问题以及关键错误的细节，位于data目录下，名称为hostname.err，日志名可以通过--log-error命令行选项修改
  • 查询日志：记录所有MySQL活动，名称为hostname.log，可通过--log修改
  • 二进制日志：记录更新过数据或者可能更新过数据的所有语句
  • 缓慢查询日志：记录执行缓慢的任何查询，在确定优化时很有用

外键

定义语法

1
2
3
4
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

被引用的表称为父表，引用的表称为子表。ON DELETE和ON UPDATA表示对父表进行DELETE和UPDATE操作时，对子表的操作选择

常用命令

SHOW PROCESSLIST;：展示MySQL线程列表和状态