简介

MySql是比较流行的关系型数据库，比较适用于中小型系统的数据库使用，所以了解其基本的操作是很重要的。

一、MySql常用语句

表(数据库)的CURD

查询表（数据库）

1. 1. 1. 获取所有可用的数据库：SHOW DATABASES;
      2. 选择数据库：USE  db_name；
      3. 显示数据库服务器的状态：SHOW STATUS;
      4. 显示数据库服务器错误或警告信息：SHOW ERRORS或者SHOW WARNINGS;
      5. 显示创建数据库时的常见语句：SHOW CREATE DATABASES db_name
      6. 显示创建表时的创建语句：SHOW CREATE TABLE db_name
      7. 获取当前所选数据库中所有的可用表：SHOW TABLES
      8. 获取表中所有列的信息：SHOW COLUMNS FROM table_name或DISCRIBE table_name

创建表（数据库）

1. 1. 1. 创建数据库：CREATE  DATEBASE  db_name
      2. 创建表:：CREATE TABLE table_name,例如：

         CREATE TABLE customers(
         
         cust_id INT NOT NULL AUTO_INCREMENT, cust_name CHAR(50) NOT NULL, cust_age INT NULL DEFAULT 18, PRIMARY KEY(cust_id)
         
          )ENGINE=INNODB;

细节：

1）允许NULL值，则在插入行数据的时候允许不给出该列的值，NOT NULL则是在插入或者更新该列的时候，必须明确给出该列的值。

2）AUTO_INCREMENT表示整型数据自动增长+1

3）DEFAULT表示该列的默认值，如果在插入行数据的时候，若没有给出该列的值就会使用指定的默认值

4）PRIMARY KEY用于指定主键，主键可以指定一列数据，也可以由多列数据组合构成，如PRIMARY KEY(cust_id,cust_name)

5)ENGINE用于指定引擎类型，常见的引擎类型有：

1. InnoDB，是一个支持可靠事务处理的引擎，但是不支持全文本搜索
2. MyLSAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
3. MEMORY在功能上等同于MyLSAM,但由于数据存储在内存中，速度很快（特别适合临时表） 

6）在创建表的时候可以使用FOREIGN KEY来来创建外键，即一个表的FOREIGN KEY指向另一个表中的PRIMARY KEY。外键FOREIGN KEY用于约束破坏表的联结动作，保证两个表的数据完整性，同时也能防止非法数据插入外键列，因为该列值必须指向另一个表的主键，例如：

CREATE TABLE Orders(

Id_O int NOT NULL,

OrderNo int NOT NULL,

Id_P int,

PRIMARY KEY (Id_O),

FOREIGN KEY(Id_P) REFERENCES Persons(Id_P)

)

删除表（数据库）

1. 1. 删除数据库：DROP DATABASE db_name
   2. 删除表：：DROP TABLE db_name

更新表

更新表结构信息可以使用ALTER TABLE子句，如为表增加一列：

ALTER TABLE table_name ADD name CHAR(20)

添加约束，定义外键：

 ALTER TABLE customers
 ADD CONSTRAINT fk_custormer_orders
 FOREIGN KEY(cust_id) REFERENCES orders (order_cust)

重命名表，使用RENAME子句，RENAME backup_name  TO new_name,backup_name1 TO new_name1;更改多个表名可以使用逗号分隔

表数据的CURD

查询表数据

基本查询语句

根据过滤条件查询表中的单列或者多列或者全部列的信息：SELECT FORM table_name WHERE [过滤条件]，其中过滤条件操作符有：=,<>,!=,<.<=,>,>=,BETWEEN AND,IS NULL。为查询某一列信息去重：DISTINCT，例如：

SELECT DISTINCT cust_name FROM customers

限制单列查询结果的行数：LIMIT，SELECT cust_name FROM customers LIMIT 6;LIMIT后面跟一个数值表示从0开始取到5行数据，如果LIMIT 2,6表示从3开始取，总取6行数据。取一个或者多个列对输出进行排序：ORDER BY，例如：

SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name;

指定条件范围，在范围中的每个条件都可以进行匹配：IN，例如：

SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000)。

另外，NOT操作符可以和IN操作符配合使用，用于表示检索出不符合条件的所有数据；

用于模糊查询：LIKE，与之配合使用通配符有%和_，%表示任意字符出现的次数，_表示只能匹配一个字符，例如：

SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%'；

使用分组查询并可以满足一定的分组过滤条件：GROUP BY HAVING，

如检索总计订单金额大于等于50的订单号和订单总金额，并按总金额进行排序：SELECT order_num,SUM(quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY order_total

WHERE和HAVING的比较，WHERE是行级过滤，而HAVING是组级过滤。被HAVING过滤掉的数据不会出现在分组中。WHERE中通配符以及多个WHERE子句的连接同样适用于HAVING子句；

GROUP BY的使用注意事项：

（1）GROUP BY子句中可以嵌套分组（即通过多个列进行分组GROUP BY cust_id, cust_name），但是进行数据汇总时，是在最后规定的分组上进行；

（2）GROUP BY子句中列出的每个列都必须是检索列或者是有效的表达式。

（3）如果有NULL值，将值NULL作为一个分组进行返回，如果有多行NULL值，它们将分为一组

1. 嵌套其他查询中的查询，称之为子查询。执行过程由里向外，里层查询结果作为外层查询的条件：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')。当然，多表的查询可以是用联结查询。

联结查询

1. 1. 内部联结，是基于两个表之间的相等测试，如果不加过滤条件的话，会造成“笛卡尔积”：SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id；同样可以使用WHERE进行多表联结查询，但是更推荐使用INNER JOIN等联结方式。
   2. 外部联结包括左外联结LEFT JOIN和右外联结RIGHT JOIN和全连接FULL JOIN。LEFT JOIN会全部返回左表数据，RIGHT JOIN会全部返回右表的数据，FULL JOIN会将左右两个表的数据全部返回，例如：

SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id；

联结函数与聚集函数一起使用：

SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id；

组合查询

多个查询（SELECT）可以使用UNION将多个查询结果进行合并成一个结果集返回，UNION必须包含两个及以上的SELECT查询，并且每个查询必须包含相同的列、表达式或聚集函数，数据类型不必相同，MySql会进行隐式的类型转换，如：

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

1. UNION返回的是去重后的结果，如果不需要去重则可以使用UNION ALL
2. 可以多组合查询使用ORDER BY进行查询，但是是针对最终的结果进行排序，而不是针对单个SELECT查询进行排序，因此对于组合查询来说ORDER BY子句只有一个。

使用函数对数据进行处理

1. 1. 拼接列名（将多个字符串连接在一起）：concat(str1,str2,str3,…)
   2. 执行算术表达式计算（加减乘除等）。
   3. 文本处理函数如UPPER(),LTRIM(),RTRIM()等函数，upper(str)函数为将字符转换成大写，LTRIM（str）将字符串左边的空格除去，RTRIM（str）将字符串右边的空格去除。
   4. 时间和日期处理函数，DATE(date)返回日期或日期/时间表达式的日期部分，DAY(date)返回日期中的天,YEAR(date)返回日期中的年等等
   5. 数值处理函数，ABS(data)返回data的绝对值，COS(data)返回data的余弦值等等
   6. 常用的聚集函数，AVG（）求平均值，CONNT()计算行数，MAX（）返回列的最大值，MIN（）返回列的最小值，SUM（）计算列的总数

插入表数据

1. 1. 向表中插入行数据，使用INSERT INTO  tableName ()VALUES（），更安全的方式是指定列名，在该子句中能够省略列名的前提条件是：该列可以允许定义为NULL值和在定义该列的时候已经有默认值了，否则需要在INSERT INTO的时候指定列名。
   2. 如果插入多行数据可以将多组值使用逗号分隔，INSERT INTO tableName VALUES(),(),()等
   3. 将查询出来的数据插入到表中，可以使用INSERT INTO tableName () SELECT..语句，其中SELECT中可以带WHERE语句，该语句通常被用于复制表数据，例如：

INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5；

更新表数据

1. 1. 更新表数据使用UPDATE tableName SET… WHERE；更新的是某列数据，建议加上WHERE子句指定某一行，否则会更新某一列的全部数据。

删除表数据

1. 1. 1. 删除表数据使用DELETE FROM tableName WHERE …;删除的是某行数据，建议需要加WHERE条件指定某行，否则会删除整个表的数据，这是非常不安全的，另外DELETE只是删除表中的数据，不会删除表结构信息
      2. 如果想删除表中的全部数据可以使用TRUNCATE将DELETE代替，它比DELETE删除效率更高。

索引

mysql索引的建立对于mysql的高效运行是很重要的，索引可以大大提高MySQL的检索速度，索引分单列索引和组合索引；单列索引即一个索引只包含一个列；组合索引即一个索引包含多个列。

创建索引

创建索引的方式有两种，一种就是直接使用CREATE INDEX 进行创建，另外一种就是通过修改表结构来进行添加，就是利用ALTER TABLE语句

使用CREATE INDEX创建索引

语法为：

  CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  [USING index_type]
  ON table_name (index_col_name,...)

其中[UNIQUE|FULLTEXT|SPATIAL]这三个关键字表示创建索引的类型，它们分别表示唯一索引，全文索引，空间索引，如果没有指定任何关键字，则默认为普通索引，所以总共有四种索引。Index_name为索引的名称,用户自定义，[USING index_type]其中index_type表示索引的实现方式，在mysql中有两种不同形式的索引，分别为BTREE索引和HASH索引；如果存储引擎为MyISAM和InnoDB的表中只能使用BTREE,默认是BTREE；如果存储引擎为MEMORY的表中可以使用HASH和BTREE两种类型的索引，其默认值是HASH。table_name表示指定的表名；index_col_name表示需要创建索引的字段名称，我们还可以针对多个字段创建复合索引，只需要在多个字段名称之间用逗号隔开就好，此外，对于CHAR和VARCHAR类型的字段，我们还可以使用只使用字段内容的前面一部分来创建索引，只需要在对应的字段名称后面加上(length)的指令即可，表示使用该字段的前面length个字符来创建索引，例如：

我们以customers表的cust_name字段(类型为VARCHAR(50))为例，使用cust_name字段的6个字符前缀来创建索引。

 CREATE INDEX idx_cust_name ON user (cust_name(6))

使用ALTER TABLE创建索引

语法为：

 ALTER TABLE table_name
 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
 (index_col_name,...) [USING index_type]

 

删除索引

删除指定表中指定名称的索引，语法为：

ALTER TABLE table_name
DROP INDEX index_name;

例如：

删除名称为idx_cust_name的索引，其SQL语句为：

ALTER TABLE customers
DROP INDEX idx_cust_name;

 

查看索引

在mysql中查看某个数据库表中的索引也是非常简单，只需要使用以下两个命令中的任意一种：

如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name ：

SHOW INDEX FROM table_name [FROM db_name]

如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀 ：

SHOW INDEX FROM [db_name.]table_name

修改索引

在mysql中没有提供修改索引的直接指令，一般情况下我们先删除原来的索引，再根据需要创建一个同名的索引，从而达到修改索引的目的。

1. 先删除原来的索引

ALTER TABLE user
DROP INDEX idx_user_username；

1. 再创建同名的索引

CREATE INDEX idx_cust_name ON customers (cust_name(8));

二、MySql关键字执行顺序

在SQL语句中每个关键字都会按照顺序往下执行，而每一步操作都会生成一个虚拟表，最后产生的虚拟表会作为执行的最终结果返回，下面是常用的关键字的执行顺序：

sql语句：

(8)SELECT (9)DISTINCT<select_list> (1)FROM <left_table> (3)<join_type> JOIN <right_table> (2) ON <join_condition> (4)WHERE <where_condition> (5)GROUP BY<group_by_list> (6)WITH{CUBE|ROLLUP} (7)HAVING<having_condition> (10)ORDER BY<order_by_list> (11)LIMIT<limit_number>

实际的执行顺序：

• FROM：对FROM左边的表left_table和右边的表right_table计算笛卡尔积，产生虚表VT1；

• ON：对虚拟表VT1进行ON筛选，只有那些符合<join_condition>条件的行才会被记录在虚拟表VT2中；

• JOIN：如果是OUT JOIN，那么将保留表中（如左表或者右表）未匹配的行作为外部行添加到虚拟表VT2中，从而产生虚拟表VT3；

• WHERE:对虚拟表VT3进行WHERE条件过滤，只有符合<where_condition>的记录才会被放入到虚拟表VT4；

• GROUP BY：根据GROUP BY子句中的列，对虚拟表VT4进行分组操作，产生虚拟表VT5；

• CUBE|ROLLUP：对虚拟表VT5进行CUBE或者ROLLUP操作，产生虚拟表VT6；

• HAVING：对虚拟表VT6进行HAVING条件过滤，只有符合<having_condition>的记录才会被插入到虚拟表VT7中；

• SELECT：执行SELECT操作，选择指定的列，插入到虚拟表VT8中；

• DISTINCT：对虚拟表VT8中的记录进行去重，产生虚拟表VT9；

• ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10；
• LIMIT：取出指定行的记录，产生虚拟表VT11，并将结果返回。

三、MySql高级功能

事务处理

什么是事务

事务处理是用来维护数据库的完整性，它保证成批的sql操作要么完全执行成功，要么完全执行失败。

事务的关键概念

1. 1. 事务：指的是一组sql语句
   2. 回退：指的是撤销指定sql语句的执行
   3. 提交：指的是将为存储的执行sql语句结果执行到数据库表中
   4. 保留点：指的是事务处理中设置的临时占位符，可以对它发布回退

如何创建事务并执行

START TRANSACTION;

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);

SELECT * FROM customers;

 SAVEPOINT insertinto;

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);

 ROLLBACK TO insertinto;

执行结果为：插入数据('1',5,18)有效，因为，只会从保留点SAFEPOINT之后开始回退，也就是说保留点SAFEPOINT之前的SQL语句执行的结果仍然有效。

注意的一些细节：

• STAET TRANSACTION用来表示下面的SQL语句集为一段事务；

• SAFEPOINT 用于指定保留点insertinto；

• ROLLBACK TO表示从指定保留点开始回退，也就是说保留点之前的SQL语句执行结果依然有效。如果仅仅使用ROLLBACK进行回退的话就表示从STAET TRANSACTION之后所有的SQL语句执行效果都会撤销；

        mysql提交（写或保存）操作都是自动进行的，是隐含提交，但是在事务处 理块中是不会隐含提交的，要使用COMMIT子句进行提交，如：

START TRANSACTION;

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);

COMMIT;

采用COMMIT提交事务，如果里面的sql语句都执行成功了，才会将数据写到表中，否则全部sql都会失败。

1. 触发器
1. 什么是触发器

当某条sql语句发生时，需要自动执行某些其他的sql语句等操作的时候就应该使用触发器，触发器只能响应DELETE,INSERT,UPDATE这三个操作。

1. 如何创建触发器并执行

创建触发器时需要给出最重要的四条信息：

1. 全局唯一的触发器名
2. 触发器关联的表
3. 触发器在何时执行（操作执行之前或之后）
4. 触发器响应的动作（DELETE,INSERT ,UPDATE）

由于触发器只能响应三种操作，所以可创建的触发器也就只有三种类型：DELETE触发器，INSERT触发器，UPDATE触发器。

1. INSERT触发器

在执行INSERT触发器时，也这样几点需要注意：

1. 在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，可以用NEW来访问刚插入的行数据；
2. 在BEFORE INSERT触发器中，NEW中的值可以被更新；
3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自定生成值。

创建一个INSERT触发器，每次插入一行数据，每次会返回当前插入的行数据的id。

 /*创建触发器*/
 CREATE TRIGGER insertcustomers AFTER INSERT ON customers
 FOR EACH ROW SELECT NEW.cust_id INTO @newinsertid;
 
 /*执行触发器*/
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 SELECT @newinsertid;

注意细节：

1. 使用CREATE TRIGGER来创建触发器；
2. AFTER INSERT表明在插入行数据之后，触发器才会执行特征操作；
3. FOR EACH ROW 表示对插入的每一行数据，触发器都起作用；
4. 针对INSERT触发器，可以使用虚拟表NEW，来使用刚插入的行数据。比如例子中，SELECT NEW.cust_id INTO @newinsertid表示将新插入的行数据的id赋值给变量@newinsertid；

 

1. DELETE触发器

DELETE触发器在DELETE语句执行之前或者之后，需要知道以下两点：

1. 在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，来访问被删除的行；
2. OLD表中的数据只能读，不能被更新，而在INSERT触发器中，就可以通过NEW来更新被插入的行数据；

例如，针对customers表，当删除一行数据时，返回被删除数据的cust_id以及cust_name:

 /*创建DELETE触发器*/
 
 DELIMITER //
 CREATE TRIGGER insertcustomers AFTER DELETE ON customers
 FOR EACH ROW
 BEGIN
   SELECT OLD.cust_name INTO @deletecustname;
   SELECT OLD.cust_id INTO @deletecustid;
 END //
 
 /*调用DELETE触发器*/
 DELETE FROM customers WHERE cust_id = 3;
 SELECT @deletecustname;
 SELECT @deletecustid;

基本上与创建INSERT触发器一样，只不过在DELETE触发器中只能使用OLD来访问被删除的行数据。

 

1. UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或者之后执行，需要知道一下几点：

1. 在BEFORE UPDATE触发器中可以使用NEW和OLD来访问数据，而在AFTER UPDATE触发器中使用NEW来访问数据会报错，只能使用OLD来访问数据；
2. 在BEFORE UPDATE触发器中，NEW中的值可以被改变，即允许更改将用于UPDATE的数据；
3. OLD中的行数据只能读，不能被更新；

一个UPDATE触发器示例如下：

 /*创建UPDATE触发器*/
 
 DELIMITER //
 CREATE TRIGGER insertcustomers BEFORE UPDATE ON customers
 FOR EACH ROW
 BEGIN
 
 SELECT NEW.cust_name INTO @beforeupdate;
 SET NEW.cust_name = 'reset_name';
 SELECT OLD.cust_name INTO @afterupdate;
 END //
 
 /*调用UPDATE触发器*/
 UPDATE customers SET cust_name = 'happy' WHERE cust_id = 5;
 SELECT @beforeupdate;
 SELECT @afterupdate;
复制代码

输出为@beforeupdate为‘happay’，而@afterupdate为'reset_name'。有这样一些细节：

1. NEW虚拟表中的数据可以更改，如这里采用 SET NEW.cust_name = 'reset_name';，将待更新的cust_name由“happy”变成了“reset_name”；
2. 在BEFORE UPDATE触发器中可以使用NEW和OLD来访问数据，而在AFTER UPDATE触发器中使用NEW来访问数据会报错；

1. 删除触发器

删除触发器，可以使用 DROP TRIGGER语句，比如DROP TRIGGER insertcustomers;。触发器不能更新或者覆盖，如果要修改触发器，必须删除这个触发器。

 

存储过程

什么是存储过程

就是为了复用性或实现复杂的业务功能，而保存的一条或者多条SQL语句的集合，可以认为是一个sql脚本。

为什么需要使用存储过程

1. 1. 通过把处理业务功能封装在容易使用的单元中，简化复杂的操作
   2. 不要求反复建立一系列处理步骤，这保证了数据的完整性，在各个应用程序中使用同一个存储过程，则所使用的代码都是相同的
   3. 简化对变动的管理，如果表名、列名或业务逻辑有变化，只需要改变存储过程的代码就可以了，其他人在使用这个存储过程就不需要变动什么也不需要直到修改哪些代码；具备了安全性。
   4. 使用存储过程比单独使用SQL语句要快，提高了性能。
   5. 存储过程可以用来编写业务功能更灵活的代码。

总结：存储过程具有三个特性：简单可复用、安全性和高性能

存储过程的缺点

存储过程的有点上面已经说了，其缺点还是有的：

1. 存储过程的编写比基本的sql语句更复杂，需要更高的技能。
2. 使用存储过程需要数据管理员设置权限，比较麻烦。

1. 存储过程的具体怎么使用
1. 创建存储过程

使用CREATER PROCEDURE语句创建：

DELIMITER//

 CREATE PROCEDURE procedure_name(

 IN custid INT,

IN taxable BOOLEAN,

OUT ototal DECIMAL(8,2)

)COMMENT 'obtain total order price'

 BEGIN

 /*declare variable for total*/

DECLARE total DECIMAL(8,2);

DECLARE taxrate INT DEFAULT 6;

 /*get the order total*/

SELECT SUM(item_price*item_quantity) INTO total FROM customers WHERE cust_id = custid;

 /*is this taxable?*/

 IF taxable THEN

 SELECT total+(total/100*taxrate) INTO total;

END IF;

SELECT total INTO ototal;

END //

注意一些细节：

1.  使用CREATE PROCEDURE语句进行创建，（）圆括号中为存储过程的参数，其中参数类型有：

1. IN类型，表示传递给存储过程；

2. OUT 类型，表示存储过程返回的结果，在调用存储过程时需要传入@开始的变量；

3. INOUT类型，表示在存储过程中可以传入和传出；

1.  DECLARE用来声明一个变量，如这里的total，taxrate。注意MySQL中定义变量时都是变量名在前，数据类型在后。
2. 存储过程具体逻辑写在BEGIN END之间；
3. 将值赋给变量使用INTO关键字；
4. 由于存储过程中每个SQL语句中用；作为分隔符，会和单个SQL造成冲突，因此可使用DELIMITER重新定义分类符，如该例子中定义//为分隔符，自然存储过程结尾就用END //结尾，而不再是END。同时，分隔符//成对出现后，恢复到默认的";"作为分隔符；

1. 执行存储过程

使用CALL子句进行执行存储过程，CALL子句接受存储过程名称以及需要传递的参数，例如：

 CALL ordertotal(1,TRUE,@total);

如果存储过程中定义了OUT类型的输入参数，那么在执行存储过程时需要传入变量，如这里@total，并且变量都是用@开始的。如果存储过程中没有参数的话，就用空圆括号表示即可，CALL ordertotal()；

1. 删除存储过程

删除存储过程可以使用DROP PROCEDURE procedure_name来删除。

1. 查询存储过程
1. 显示创建一个存储过程的语句：SHOW CREATE PROCEDURE procedure_name
2. 查询所有存储过程的状态，如果在定义存储过程的时候使用了COMMENT添加了注释，那么可以通过注释进行筛选：SHOW PROCEDURE STATUS LIKE '%comment_name%';