0.MySQL的常用操作

SHOW DATABASES;显示所有的数据库

USE database;切换到某一个数据库

SHOW TABLES;显示当前数据库中的所有的表

SHOW COLUMNS FROM table;详细显示某一个表的信息

SHOW STATUS;显示服务器状态

SHOW CREATE DATABASE database/SHOW CREATE TABLE table;显示创建某一个数据库/表的语句

SHOW GRANTS;显示用户的权限

SHOW ERRORS/SHOW WARNINGS

1.检索

SELECTG DISTINCT xxx; 表示只检索返回不同的值

SELECT xxx FROM xxx LIMIT 5; 表示返回不多于 5 行

SELECT xxx FROM xxx LIMIT 5, 5; 第一个数表示开始的位置，第二个数表示要检索的行数

~ LIMIT 4 OFFSET 3; 从第 3 开始取 4 行

~ ORDER BY A, B; 先按照 A 排序，如果 A 一样，按照 B 排序

~ ORDER BY A DESC; 降序排序 （默认升序 ASC）

~ WHERE A IS NULL; 当取不到值的时候符合要求

LIKE %：任意多个字符 _：一个字符

REGEXP ‘xxx’ 正则表达式匹配

Concat：字符拼接
SELECT Concat(name, “=”, name) FROM ~

SELECT AVG() FROM ~ 所检索出来的值的平均值

SELECT COUNT(*) FROM ~ 把所有取到的值计算进来，然后得出检索出的总数据条数

SELECT COUNT(column) FROM ~ 写指定的列的名字，只对该列为非空的情况下计算为一条数据

~ GROUP BY a HAVING b > 10; 分组查询

~ GROUP BY a HAVING b > 10 ORDER BY a LIMIT 5 OFFSET 2; 一条包含常见检索条件的SQL语句

UNION 指示MySQL执行多条SELECT语句，并把输出组合成单个查询结果
UNION 必须由两条或者两条以上的SELECT语句组成，检索的数据应该相同
UNION ALL 取出所有的行（包括重复的行），UNION 在检索时默认会去除重复的行

2.插入

INSERT INTO table (aa, bb) VALUES (cc, dd); 插入数据

INSERT LOW_PRIORITY INTO ~ 降低插入相对于检索来说的优先级

3.更新

UPDATE table SET aa = bb WHERE cc = dd; 数据更新操作

UPDATE IGNORE table ~ 如果更新时遇到错误，也要继续更新

4.删除

DELETE FROM table WHERE aa = bb; 删除一整条数据

DELETE从表中删除行，甚至是删除所有的行，但是DELETE不删除表本身

表的清空：TRUNCATE table; 此条语句实际上是删除原来的表并重新创建一个表，而不是逐行删除表中的数据，所以速度很快

5.创建表

PRIMARY KEY (a, b) 创建主键

AUTO_INCREMENT 自增
SELECT last_insert_id() 获得最后一个AUTO_INCREMENT 的值

DEFAULT a 制定默认值

InnoDB 可靠的事务处理引擎，不支持全文本搜索
MEMORY 功能上等同于 MyISAM，数据存储在内存，速度很快
MyISAM 性能极高，支持全文本搜索，不支持事务处理

6.重命名表

RENAME TABLE a TO b, c TO d;

7.更新表

ALTER TABLE table ~

~ ADD a CHAR(20) 增加一个列a

~ DROP COLUMN a 删除列a

8.删除表

DROP TABLE a;

9.视图

视图是虚拟的表。
可以把某整个查询包装成一个视图

优点：

  1. 重用SQL语句
  2. 简化复杂的SQL操作
  3. 使用表的的组成部分而不是整个表
  4. 保护数据

视图可以嵌套，即一个视图可以从其他视图获得数据

视图的操作语句：

  • CREATE VIEW; 创建视图
  • SHOW CREATE VIEW viewname;查看创建视图的语句
  • DROP VIEW viewname;删除视图
  • CREATE OR REPLACE VIEW 视图存在，则更新；不存在，则创建

CREATE VIEW aaa AS SELECT ~ 创建一个视图

接下来，可以对视图进行操作，例如：SELECT * FROM aaa;

*视图可以用于数据的更新、插入和删除，但是一般来说，应该只将视图用于数据的检索

10.存储过程

创建存储过程：

CREATE PROCEDURE aaa()
BEGIN
    SELECT a FROM b;
END;

DELIMITER // 把//作为新的语句结束符

DELIMITER ; 重新把;作为MySQL语句结束符号

调用上面的存储过程：
CALL aaa();他将会执行定义的操作过程

删除过程：DROP PROCEDURE aaa;
优化版本：DROP PROCEDURE IF EXISTS aaa;

SHOW CREATE PROCEDURE aaa; 显示存储过程的CREATE语句

SHOW PROCEDURE STATUS; 显示存储过程的详细信息，可以通过 LIKE 语句对过程名进行过滤

如何使用一个存储过程：

CREATE PROCEDURE ordertotal(
    IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
    SELECT sum(item_price*quantity)
    FROM orderitems
    WHERE order_number = onumber
    INTO ototal;
END;

CALL ordertotal(20005, @total);

SELECT @total;

上面的操作中：

第一步是创建一个存储过程，并且IN表明这是一个输入的参数，OUT表明这是一个要输出的参数。INTO ototal表示把下面查询操作所检索出来的值放到ototal中。

第二步是调用存储过程，把20005传入，存储过程传出的参数用变量@total来保存

第三步是取得变量@total的值

11.游标

  1. 声明（定义）游标

  2. 打开游标

  3. 操作

  4. 关闭游标

    DECLARE ordernumbers CURSOR FOR ~
OPEN ordernumbers;
CLOSE ordernumbers;

游标的操作要依附于存储过程来进行。

12.触发器

当发生DELETE、INSERT、UPDATE时，会自动进行某些操作

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT "Product added";--创建触发器

DROP TRIGGER newproduct;  --删除触发器

只有表才支持触发器，视图不支持触发器。

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT NEW.product;  -- 检索最新插入的那条数据的product列
CREATE TRIGGER deleteorder BEFORE DELETE ON orders FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num) VALUES(OLD.oreder_num);
END;

– UPDATE 的触发器：NEW代表更新之后的值，OLD表示跟新之前的值

CREATE TRIGGER updateevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

13.事务处理

START TRANSACTION;
DELETE FROM ordertotals;
ROLLBACK;/COMMIT;

SAVEPOINT delete1;
ROLLBACK TO delete1;

SET autocommit = 0;

关闭自动提交（只对每一个连接有效，对数据库服务器无效）。