MySQL必知必会

一、使用MYSQL——USE命令，SHOW命令

USE 数据库名;   //打开数据库

SHOW DATABASES;  //返回可用数据库的一个列表

SHOW TABLES;   //返回当前选择的数据库内可用表的列表

SHOW COLUMNS FROM 表名;   //对表内每个字段返回一行，航中包括字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息

DESCRIBE 表名;  //同上，是上一条语句的快捷方式

进一步了解SHOW命令，可在mysql命令行中执行 HELP SHOW

二、检索数据

SELECT 列名 FROM 表名;  //返回某列数据

SELECT 列名1,列名2...,列名n FROM 表名;  //返回多列数据

SELECT * FROM 表名;  //检索所有列  （性能会降低）

SELECT DISTINCT 列名 FROM 表名;  //返回某列数据 不同的行 （DISTINCT应用与所有列而不仅是前置它的列。）

SELECT DISTINCT 列名1，列名2 FROM 表名;  //除非指定的两个列都不同，否则所有行都将被检索出来

限制结果（行数是从0开始的）
SELECT 列名 FROM 表名 LIMIT 5;  //返回数据不多于5行

SELECT 列名 FROM 表名 LIMIT 3,4;  //返回从行3开始的4行，第一个数为开始位置，第二个数为要检索的行数

SELECT 列名 FROM 表名 LIMIT 4 OFFSET 3;  //从行3开始 取4行，同上

三、排序检索数据

SELECT prod_name(列名) FROM products(表名) ORDER BY prod_name;  //对prod_name列以字母顺序排序数据（根据其他列进行排序也可以）

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;  //检索三个列，并按其中两个列对结果进行
排序————首先按价格，然后在按名称排序。（仅在price值相同时才会按名字继续排序，否则不会按名字排序）

默认是 升序排序。降序排序 用DESC 关键字。
SELECT prod_name FROM products ORDER BY prod_name DESC;  //对prod_name列 降序排序

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;  //先按price进行降序排列，price
相同的在按name升序排列

如果行在多个列上进行降序排列，必须对每个列指定DESC关键字。升序是ASC，默认就是 升序，所以没啥用。

要注意，ORDER BY要在FROM之后，LIMIT要在ORDER BY之后。 子句的次序很重要。

四、过滤数据

WHERE在FROM子句之后给出。ORDER BY 位于WHERE之后。

匹配时，默认不区分大小写。

SELECT prod_name FROM products WHERE prod_price = 2.50;  
支持等于，不等于，大于，小于，大于等于，小于等于，BETWEEN 等等。

SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10; //价格在5-10之间的，包括5和10。

SELECT cust_id FROM customers WHERE cust_email IS NULL; //空值检查

五、数据过滤

SELECT prod_id , prod_price,prod_name FROM products WHERE vend = 1003 AND prod_price <= 10; //由供应商1003制造且价格小于等于10的

SELECT prod_price,prod_name FROM products WHERE vend = 1003 OR vend = 1002; //由供应商1003或1002制造的

AND优先级 高于 OR，要用括号。

SELECT prod_price,prod_name FROM products WHERE vend = 1003 OR vend = 1002 AND prod_price >= 10; //由供应商1003制造的或1002制造的且价格大于等于10的

SELECT prod_price,prod_name FROM products WHERE (vend = 1003 OR vend = 1002) AND prod_price >= 10; //由供应商1003或1002制造的且价格大于等于10


IN操作符后跟有逗号分隔的合法值清单，整个清单必须括在圆括号内。（IN 和 OR 功能相同）

IN ： WHERE子句中用来指定要匹配值的清单的关键字

SELECT prod_price,prod_name FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name; //由供应商1002或1003制造的所有商品

SELECT prod_price,prod_name FROM products WHERE vend_id  = 1002 OR vend_id = 1003 ORDER BY prod_name; //同上


NOT ： WHERE子句中用来否定后跟条件的关键字

SELECT prod_price,prod_name FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name; //除了由供应商1002或1003制造的所有商品

六、用通配符进行过滤

通配符 %：表示任何字符出现任意次数。（%不能匹配NULL）

SELECT prod_price,prod_name FROM products WHERE prod_name LIKE ‘jet%’; //找出所有name以jet开头的产品

SELECT prod_price,prod_name FROM products WHERE prod_name LIKE ‘%anvil%’; //找出所有name包含anvil的产品

SELECT prod_price,prod_name FROM products WHERE prod_name LIKE ‘s%e’; //找出所有name以s开头e结尾的产品

通配符 下划线_ : 只匹配单个字符

SELECT prod_price,prod_name FROM products WHERE prod_name LIKE ‘_ ton’; //匹配 1 ton 或2 ton 

七、用正则表达式进行搜索

REGEXP后跟的东西作为正则表达式处理

SELECT prod_price,prod_name FROM products WHERE prod_name REGEXP ‘1000’; //检索name中包含'1000'的所有行 

八、创建计算字段

Concat() :拼接函数
SELECT Concat(vend_name,'(',vend_country,')') FROM vendors ORDER BY vend_name; //把vend_name和vend_country拼接起来，用括号显示

RTrim()函数：删除数据右侧多余的空格

LTrim()函数：删除数据左侧多余的空格

Trim()函数：删除数据左右多余的空格

SELECT Concat(Trim(vend_name),'(',Trim(vend_country),')')  AS vend_title FROM vendors ORDER BY vend_name; //用AS为拼接生成的列指定新的列名

SELECT prod_id,quantity,item_price,quantity * item_price AS expanded_price FROM orderitems WHERE order_num = 210005; //计算数量*单价的值，并命别名

九、汇总数据

聚集函数

AVG() : 返回某列的平均值

COUNT() : 返回某列的行数

MAX() : 返回某列的最大值

MIN() : 返回某列的最小值

SUM() : 返回某列的和

十、分组数据

GROUP BY

GROUP BY必须在WHERE之后，ORDER BY之前

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id;  //表示按vend_id分组数据，对每个vend_id计算num_prods一次

HAVING过滤分组，与WHERE类似，WHERE过滤行。

WHERE在分组前进行过滤，HAVING在分组后进行过滤。

SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*) >= 2;  //过滤两个订单以上的那些分组

十一、使用子查询

十二、联结表

SELECT vend_name,prod_name,prod_price FROM venders,products WHERE vendors.vend_id = products.vend_id;

等值联结：也叫内部联结。可以用INNER JOIN...ON 子句。

SELECT vend_name,prod_name,prod_price FROM venders INNER JOIN products ON vendors.vend_id = products.vend_id;

十三、创建高级联结

十九、插入数据

INSERT INTO customers VALUES (NULL,'Pep','100','456');   //cust_id为自增，每列必须要填，所以填NULL

INSERT INTO customers (cust_name,cust_address,cust_city) VALUES ('Pep','100','newyork');   //列名和列值，更安全

INSERT INTO customers (cust_name,cust_address,cust_city) VALUES ('Pep','100','newyork'),('Pep2','1002','newyork');   //插入多行

INSERT INTO customers (cust_name,cust_address,cust_city) SELECT cust_name,cust_address,cust_city FROM custnew;   //插入从custnew中检索出的数据

对于INSERT...SELECT,不用列名相同。直接会将SELECT中的数据，第一列填充INSERT的第一列，第二列填充INSERT的第二列。

二十、更新和删除数据

UPDATE customers SET cust_email = 'sonya.@qq.com' WHERE cust_id = 1005;  //注意不要忘记WHERE子句，如果没有WHERE子句，将更新所有cust_email值。

UPDATE customers SET cust_email = 'sonya.@qq.com' ,cust_name = 'sonya' WHERE cust_id = 1005;  //更新多个列的值

UPDATE customers SET cust_email = NULL WHERE cust_id = 1005;  //删除列，将列置为NULL

DELETE FROM customers WHERE cust_id = 1006;  //删除一行，如果省略WHERE，则全部删除

DELETE是删除表的内容，而不是删除表。

TRUNCATE TABLE可以更快的删除，删除所有行。（实际上是删除原来的表，并重新创建一个表）

二十一、创建和操纵表

CREATE TABLE customers 
(
  cust_id int NOT NULL AUTO_INCREMENT,
  cust_name char(50) NOT　NULL,
  cust_address char(50) NULL,
  PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

NULL为默认设置。

CREATE TABLE customers 
(
  order_num int NOT NULL,
  order_item int NOT　NULL,
  cust_address char(50) NULL,
  PRIMARY KEY (order_num，order_item)  //多个列组成的主键
) ENGINE=InnoDB;

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如：通过使它成为主键）.

CREATE TABLE orderitems 
(
  order_num int NOT NULL,
  order_item int NOT　NULL,
  prod_id char(10) NOT NULL,
  quantity int NOT NULL DEFAULT 1,  //默认值
  PRIMARY KEY (order_num，order_item)  
) ENGINE=InnoDB;

引擎类型（ENGINE）：

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

更新表：ALTER

ALTER TABLE vendors ADD vend_phone CHAR(20);  //给vendors表添加一列vend_phone，必须明确其数据类型

ALTER TABLE vendors DROP COLUMN vend_phone;  //删除vend_phone列

ALTER TABLE的一种长阿金用途是定义外键。

ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

删除表：DROP TABLE

DROP TABLE custormers2;  //删除表custormers2 （没有确认，也不能撤销）

RENAME TABLE customers2 TO customers;  //重命名表

二十二、使用视图

CREATE VIEW  //创建视图

SHOW CREATE VIEW VIEWNAME  //查看创建视图的语句

DROP VIEW viewname  //删除视图

CREATE OR REPLACE VIEW  //更新视图

二十三、使用存储过程

存储过程是一种函数

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);   //执行名为productpricing的存储过程，他计算并返回产品的最低、最高和平均价格。
                    
CREATE PROCEDURE productpricing()
BEGIN 
      SELECT Avg(prod_price) AS priceaverage
      FROM products;
END; 
//创建一个名为productpricing，不接受参数的存储过程。

CALL productpricing();  //使用上面创建的存储过程

DELETE PROCEDURE productpricing;  //删除上面创建的存储过程


CREATE PROCEDURE productpricing(
  OUT pl DECIMAL(8,2),  //OUT表示相应的参数用来从存储过程传出一个值
  OUT ph DECIMAL(8,2),
  OUT pa DECIMAL(8,2)
)
BEGIN 
      SELECT Min(prod_price) INTO pl FROM products;   //INTO表示把检索到的值存到pl变量里
      SELECT Max(prod_price) INTO ph FROM products;
      SELECT Avg(prod_price) INTO pa FROM products;
      FROM products;
END; 
//创建一个名为productpricing，带三个参数的存储过程。

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage); 
                    
所有mysql变量都必须以@开头。

调用后并不显示任何数据。他返回以后可以显示的变量。
SELECT @pricelow, @pricehigh, @priceaverage;  //显示数据

SHOW PROCEDURE STATUS ; //列出所有存储过程

二十四、使用游标

游标只能用于存储过程（和函数）。存储过程处理完成后，游标就消失（因为他局限于存储过程）。

CREATE PROCEDURE processorders()
BEGIN 
    DECLARE ordernumbers CURSOR  //定义游标
    FOR
    SELECT order_num FROM orders;
END;

OPEN ordernumbers;  //打开游标
CLOSE ordernumbers;  //关闭游标

例子1：从游标中检索单个行（第一行）

CREATE PROCEDURE processorders()
BEGIN 
    -- 定义局部变量   
    DECLARE o INT;
    
    DECLARE ordernumbers CURSOR  //定义游标
    FOR
    SELECT order_num FROM orders;
    
    OPEN ordernumbers;  //打开游标
    
    FETCH ordernumbers INTO o;  //取一行数据  
    
    CLOSE ordernumbers;  //关闭游标
END;

例子2：循环检索数据，从第一行到最后一行

CREATE PROCEDURE processorders()
BEGIN 
    -- 定义局部变量   
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    
    DECLARE ordernumbers CURSOR  //定义游标
    FOR
    SELECT order_num FROM orders;
    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  //当条件出现时，会执行此代码。当SQLSTATE='02000'时done置为 1，而SQLSTATE='02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续使用时，出现这个条件。是一种错误码。
    
    OPEN ordernumbers;  //打开游标
    
    //循环所有行
    REPEAT
          
        FETCH ordernumbers INTO o;  //取一行数据  
    
    UNTIL done END REPEAT;
    
    CLOSE ordernumbers;  //关闭游标
END;

DECLARE定义是有顺序的，变量要在游标的前面，句柄要在游标的后面

二十五、使用触发器

触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句）。 DELETE;

INSERT;

UPDATE;

其他MySQL语句不支持触发器。

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

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';  
//创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行。这里指定AFTER，所以会在INSERT语句成功执行后执行。指定FOR EACH ROW，因此代码对每个插入行执行。文本Product added将对每个插入的行显示一次。

DROP TRIGGER newproduct;  //删除一个触发器

二十六、管理事务处理

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

事务：一组SQL语句。

回退：撤销指定SQL语句的过程。

提交：将未存储的SQL语句结果写入数据库表。

保留点：事务处理中设置的临时占位符，你可以对它发布回退（与回退整个事务处理不同）

START TRANSACTION   //表示事务开始
ROLLBACK;  //回退
COMMIT //提交

SAVEPOINT delete1;  //创建保留点（占位符）

事务处理用来管理INSERT，UPDATE，DELETE语句。