MySQL基础（《MySQL必知必会》简单总结）

使用MySQL

# 选择数据库
USE database_name;
# 显示数据库
SHOW DATABASES;
# 显示当前数据库的表
SHOW TABLES;
# 显示特定表有哪些列
SHOW COLUMNS FROM customers;

检索数据

# SELECT语句
SELECT prod_name FROM products;
SELECT prod_id,prod_name,prod_price FROM products;
SELECT * FROM products;
# 配合DISTINCT检索不同的行
SELECT DISTINCT vend_id FROM products;
# 配合LIMIT限制结果
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5,5;
# 完全限定的表名或列名
限定列名：SELECT products.prod_name FROM products;
限定表名：SELECT products.prod_name FROM crashcourse.products;

排序

# 单列排序
SELECT prod_name FROM products ORDER BY prod_name;
# 多列排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
# 降序
SELECT prod_id， prod_price, price_name FROM products ORDER BY prod_price DESC;
# 升序和降序组合
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;
# ORDER BY和LIMIT组合（WHERE在ORDER BY之前，ORDER BY在LIMIT之前）
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

过滤数据1（WHERE）

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.5;
SELECT prod_name, prod_price FROM prod_price BETWEEN 5 AND 10;
# 空值检索
SELECT prod_name FROM products WHERE prod_price IS NULL;
# AND操作符
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
# OR操作符
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;
# AND优先级比OR要高
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
上句相当于：SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR (vend_id = 1003 AND prod_price >= 10);
# IN操作符
SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
# NOT操作符
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

过滤数据2（LIKE配合通配符）

# %通配符（表示任意字符出现任意次数）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
# _通配符（表示单个任意字符）
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';

过滤数据3（正则表达式）

SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
# .表示任意一个字符
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
# |类似于OR
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;
# 匹配几个字符之一：[]
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
# 匹配范围
SELECT prod_id, prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;
# 匹配特殊字符（用\进行转义）
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\.' ORDER BY vend_name;
# 匹配重复字符
SELECT prod_name FROM products WHERE prod_name REGEXP '\([0-9] sticks?\)' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;
# 定位元字符
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\.]' ORDER BY prod_name;

使用计算字段

# 拼接字段
SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;
# （别名）
SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title FROM vendors ORDER BY vend_name;
# 算术计算
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num = 20005;

使用数据处理函数

# 字符处理函数
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;
SELECT cust_name, cust_contact FROM customers WHERE customers WHERE Soundex(cust_contact) = Soundex('Y Lie');
# 日期处理函数
SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-01';
SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

汇总数据（通过函数）

# AVG()函数
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
# COUNT()函数
SELECT COUNT(*) AS num_cust FROM customers;
SELECT COUNT(cust_email) AS num_cust FROM customers;
# MAX()函数
SELECT MAX(prod_price) AS max_price FROM products;
# MIN()函数
SELECT MIN(prod_price) AS min_price FROM products;
# SUM()函数
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
# 配合DISTINCT使用
SELECT AVG(DISINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

数据分组

# 创建分组：GROUP BY
SELECT  vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
# 过滤分组
SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
# 不要依赖分组的默认排序，直接用ORDER BY
SELECT order_num, SUM(QUANTITY*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;

使用自查询

# 使用自查询进行过滤
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'));
# 作为计算字段使用自查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) AS orders FROM customers ORDER BY cust_name;

表联结（联结有点不好理解--这章和下章）

# 创建联结
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
# 内部联结（返回数据和上个语句相同，只不过表达形式不同）
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
# 联结多个表
SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

高级联结

# 使用表别名
SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust.id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'TNT2';
# 自联结
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';
# 外部联结
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;
SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.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;

组合查询（UNION）

# 使用组合查询（默认去除重复行）
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 ALL）
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 
UNION ALL 
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);

全文本搜素（需要索引，MyISAM支持，InnoDB不支持）

# 启用全文本索引支持（创建表时，或之后也可以）
CREATE TABLE test 
    (note_id  int NOT NULL AUTO_INCREMENT,   
    prod_id  char(10)  NOT NULL,   
    note_date datetime  NOT NULL,   
    note_text text NULL, 
    PRIMARY KEY(note_id),   FULLTEXT(note_text) 
) ENGINE=MyISAM;
# 使用全文本搜索
SELECT note_text FROM productnotes WHERE Match(note_text) Against('effective');
SELECT note_text, Match(note_text) Against('rabbit') AS rank FROM productnotes;
# 使用扩展查询（会返回更多相关行）
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
# 布尔文本搜索（和全文本搜索不同，不需要数据库支持，但比较慢）
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

插入语句

# 插入单行
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
VALUES
(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
# 插入多行
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
VALUES
(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL),
('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');
# 插入索引出的数据（有点类似于复制）
INSERT INTO custormers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) 
SELECT cust_id, cust_contact, cust_email, 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;
# 更新多个列
UPDATE customers SET cust_name = 'The Fudds',  cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
# 删除某个列的值
UPDATE customers SET cust_email = NULL WHERE cust_id = 10005;
# 删除数据
DELETE FROM customers WHERE cust_id = 10006;

创建和操纵表

# 创建表
CREATE TABLE customers 
(
    cust_id int NOT NULL AUTO_INCREMENT, 
    cust_name char(50) NOT NULL, 
    cust_city char(50) NULL, 
    cust_state char(5) NULL, 
    cust_zip char(10) NULL, 
    cust_country char(50) NULL,
    cust_contact char(50) NULL, 
    cust_email char(255) NULL, 
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
// 多个列组成的主键
CREATE TABLE orderitems
(
    order_num int NOT NULL, 
    order_item int NOT NULL, 
    prod_id char(10) NOT NULL, 
    quantity int NOT NULL, 
    item_price decimal(8.2) NOT NULL, 
    PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;
// 指定默认值
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, 
    item_price decimal(8,2) NOT NULL, 
    PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

# 更新表
// 增加列
ALTER TABLE vendors ADD vend_phone CHAR(20);
// 删除列
ALTER TABLE vendors DROP COLUMN vend_phone;
// 增加外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);

# 删除表
DROP TABLE customers2;

# 重命名表
RENAME TABLE customers2 TO customers; 
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors, backup_products  TO products;

视图

# 创建视图
CREATE VIEW productcustomers AS 
SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order_num;

存储过程

# 创建存储过程
CREATE PROCEDURE productpricing() 
BEGIN 
SELECT avg(prod_price) AS priceaverage FROM products; 
END;
-- 调用存储过程
CALL productpricing();
# 删除存储过程
DROP PROCEDURE productpricing;

# 使用参数
CREATE DEFINER=`root`@`localhost` PROCEDURE `productpricing`(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(prod_price) INTO pl FROM products;
    SELECT Max(prod_price) INTO ph FROM products;
    SELECT Avg(prod_price) INTO pa FROM products;
END;
-- 调用存储过程
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
-- 使用
SELECT @pricehigh, @pricelow, @priceaverage;

# 一个复杂的例子
CREATE DEFINER=`root`@`localhost` PROCEDURE `ordertotal`(
    IN onumber INT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)
    COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    -- Declare variable for total
    DECLARE total DECIMAL(8,2);
    -- Declare tax percentage
    DECLARE taxrate INT DEFAULT 6;
    
    -- Get the order total
    SELECT Sum(item_price*quantity) FROM orderitems
    WHERE order_num = onumber INTO total;
    
    -- Is this taxable?
    IF taxable THEN
        SELECT total+(total/100*taxrate) INTO total;
    END IF;
    
    -- And finally, save to out variable
    SELECT total INTO ototal;
END;
-- 调用
CALL ordertotal(20005, 0, @total);
-- 使用
SELECT @total;

# 显示存储过程的信息
SHOW CREATE PROCEDURE ordertotal;
SHOW PROCEDURE STATUS LIKE 'ordertotal';

使用游标

CREATE DEFINER=`root`@`localhost` 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
	-- 获取数据 Get order number
	FETCH ordernumbers INTO o;
        -- Get the total for this order
        CALL ordertotal(o, 1, t);
        -- Insert order the total into ordertotals
        INSERT INTO ordertotals(order_num, total)
			VALUES(o, t);
    -- End of loop
    UNTIL done END REPEAT;
    
    -- Close the cursor
    CLOSE ordernumbers;
END;

# 调用包含游标的存储过程
CALL processorders;
SELECT * FROM ordertotals;

使用触发器

# 创建触发器
-- 在更新或删除时可以使用old虚拟表来访问之前的值
-- 在更新或插入时可以使用new虚拟表来访问新的值
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
CREATE TRIGGER t_afterinsert_on_tab1 
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
    INSERT INTO tab2(tab2_id) VALUES(new.tab1_id);
END;

# 删除触发器
DROP TRIGGER t_afterinsert_on_tab1;

事务处理

# 回滚
SELECT * FROM ordertotals;
START TRANSACTION;
SET sql_safe_updates=0;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

# 保留点
SAVEPOINT delete1;
ROLLBACK TO delete1;

# 更改默认的提交行为（非事务语句默认自动提交）
SET autocommit=0;

账号及权限管理

# 查看账号
USE mysql;
SELECT user FROM user;
# 创建账号（无权限）
CREATE USER jesse IDENTIFIED BY 'password';
# 更新用户名
RENAME USER jesse TO sai;
# 删除账号
DROP USER sai;
# 显示账号的权限
SHOW GRANTS FOR jesse;
# 设置权限
GRANT SELECT ON cx.* TO jesse;
撤销权限
# REVOKE SELECT ON crashcourse.* FROM jesse;
更改密码
SET PASSWORD FOR jesse = Password('n3w password');
-- 更改自己账号密码
SET PASSWORD = Password('n3w password');