<MySQL必知必会>知识点总结:
1. 选择数据库相关命令:
- USE crashcourse;
- SHOW DATABASE;
- SHOW TABLES;
- SHOW COLUMNS FROM customers;
- SHOW STATUS;
- SHOW CREATE DATABASE;
- SHOW CREATE TABLES;
- SHOW GRANTS;
- SHOW ERRORS;
- SHOW WARNINGS;
2. 检索数据库:
- SELECT prod_name
- FROM products;
- SELECT prod_id, prod_name, prod_price
- FROM products;
- SELECT *
- FROM products;
- SELECT DISTINCT vend_id
- FROM products;
- SELECT prod_name
- FROM products
- LIMIT 5;
- SELECT prod_name
- FROM products
- LIMIT 5, 5;
MySQL 5支持LIMIT 的另一种替代语法,LIMIT 4 OFFSET 3 意为从行3开始取4行,就像LIMIT 3, 4 一样。
使用完全限定的表名:
- SELECT products.prod_name
- FROM products;
3. 排序检索数据
- 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, prod_name
- FROM products
- SELECT prod_id, prod_price, prod_name
- FROM products
- ORDER BY prod_price DESC
- LIMIT 1;
在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT,它必须位于ORDER BY之后。否则会产生错误信息。
4. 过滤数据
- SELECT prod_name, prod_price
- FROM products
- WHERE prod_price = 2.5;
WHERE子句操作符支持=, <>, !=, <, <=, >, >=, BETWEEN操作符。
- SELECT prod_name, prod_price
- FROM products
- WHERE prod_price BETWEEN 5 AND 10;
- SELECT prod_name
- FROM products
- WHERE prod_price IS NULL;
5. 数据过滤
组合WHERE子句,AND操作符, OR操作符。
计算次序的问题,SQL在处理OR操作符之前,优先处理AND操作符。
- SELECT prod_name, prod_price
- FROM products
- WEHRE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
- 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;
6. 用通配符进行过滤
百分号(%)通配符
- SELECT prod_id, prod_name
- FROM products
- WHERE prod_name LIKE 'jet%';
需要注意的是,%可以匹配0个字符。
下划线(_) 通配符
下划线的用途与%一样,但下划线只匹配单个字符而不是多个字符。
a) 不要过度使用通配符。如果其他操作符能达到相同的效果,应该使用其他操作符。
b) 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
7. 用正则表达式进行搜索
基本字符匹配
- SELECT prod_name
- FROM products
- WHERE prod_name REGEXP '1000'
- ORDER BY prod_name;
进行OR匹配,使用|。
- SELECT prod_name
- FROM products
- WHERE prod_name REGEXP '1000|2000'
- ORDER BY prod_name;
- SELECT prod_name
- FROM products
- WHERE prod_name REGEXP '[123] Ton'
- ORDER BY prod_name;
匹配特殊字符,可以用\\为前导。\\-表示查找-,\\.表示查找.。
8. 创建计算字段
- SELECT Concat(vend_name, ' (', vend_country, ')')
- FROM vendors
- ORDER BY vend_name;
Trim函数:RTrim去除串右边的空格,LTrim去除串左边的空格,以及Trim去除左右两边的空格。
- SELECT Concat(RTrim(vend_name), ' (', RTrim(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_upcase
- FROM vendors
- ORDER BY vend_name;
常用的数值处理函数包括Abs(), Cos(), Exp(), Mod(), Pi(), Rand(), Sin(), Sqrt(), Tan()。
10. 聚集函数
SQL聚集函数包括AVG(), COUNT(), MAX(), MIN(), SUM()。
COUNT()函数有两种使用方式:
a) 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的空值(NULL),还是非空值。
b) 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
MAX函数返回指定列中的最大值。MAX要求指定列名。
对非数值数据使用MAX():虽然MAX()一般用来找出最大的数值或日期值,但MySQL允许将它用来返回任意列中的最大值,包括文本列中的最大值。在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行。MIN()返回第一行。
SUM函数将忽略列值为NULL的行。
- SELECT AVG(DISTINCT prod_price) AS avg_price
- FROM products
- WHERE vend_id = 1003;
- SELECT COUNT(*) AS num_items,
- MIN(prod_price) AS price_min,
- MAX(prod_price) AS price_max,
- AVG(prod_price) AS price_avg
- FROM products;
11. 分组数据
- 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;
- SELECT vend_id, COUNT(*) AS num_prods
- FROM products
- WHERE prod_price >= 10
- GROUP BY vend_id
- HAVING COUNT(*) >= 2;
SELECT子句及其顺序
SELECT => FROM => WHERE => GROUP BY => HAVING => ORDER BY =>LIMIT
12. 使用子查询
- 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;
13. 组合查询
- 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必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔。
UNION中的每个查询必须包含相同的列,表达式或聚集函数。
列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型。
UNION默认行为是去除了重复的行。如果不要求去除重复的行,可以用UNION ALL。
后加ORDER BY 可以进行查询结果的排序。
14. DELETE 表数据
- USE mysql;
- SELECT user FROM user;
- CREATE USER ben IDENTIFIED BY 'xxxx';
- RENAME USER ben TO bforta; // 5.0之后支持。
- DROP USER bforta;
新创建的用户帐号没有访问权限。它们能登陆MYsql,但不能看到数据,不能支持任何数据库操作。