SQL

1、DISTINCT关键字去掉结果中重复的行（作用于所有的列）

SELECT DISTINCT vend_id
FROM vendors;

放在SELECT的后面

2、LIMIT很重要的关键字在MyBatis用于分页

SELECT DISTINCT vend_id
FROM vendors
LIMIT 2,3;

返回的是从第二行开始的三行（行是从零开始标记的）

3、ORDER BY对结果排序

SELECT DISTINCT order_date
FROM orders
ORDER BY order_date DESC
LIMIT 2,3;

对时间降序排列，默认是ASC升序。

对多个列排序时，第二列在第一列有多个重复值时在排序。

4、IS NULL空值检查

SELECT cust_id
FROM customers
WHERE cust_email IS NULL;

5、范围查询

BETWEEN AND

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10
ORDER BY prod_price;

IN

SELECT prod_name, prod_price
FROM products
WHERE prod_price IN (5,10);

BETWEEN AND 与 IN的区别：前者匹配5~10之间的值，后者匹配5和10.

NOT取反MySQL支持NOT用于BETWEEN、IN与EXEISTS子句

6、LIKE模糊查询

SELECT vend_name, vend_address
FROM vendors
WHERE vend_address LIKE '%h%';

SELECT vend_name, vend_city
FROM vendors
WHERE vend_city LIKE '_ondon';

%通配多个字符，_通配一个字符。

 7、计算字段

 字段拼接

SELECT CONCAT(vend_name, ' (', vend_city ,') ') AS tittle
FROM vendors
ORDER BY vend_name DESC;

计算字段

SELECT prod_id, 
             quantity, 
             item_price,
             quantity*item_price AS total
FROM orderitems
WHERE order_num = 20005;

8、聚集函数

聚集函数作用于列返回单个的值。

AVG() 返回这一列的平均值
COUNT() 返回这一列的行数
MAX() 返回这一列的最大值
MIN() 返回这一列的最小值
SUM() 返回这一列的和

SELECT MAX(prod_price) AS max_price,
             MIN(prod_price) AS min_price,
             COUNT(*) AS count,
             SUM(prod_price) AS sum,
             AVG(prod_price) AS avg
FROM products;

COUNT(*)：对行进行统计，不忽略NULL

COUN(列)：对行的值进行统计，忽略NULL

DISTINCT：关键字可以作用于列计算不重复的值

SELECT MAX(prod_price) AS max_price,
             MIN(prod_price) AS min_price,
             COUNT(DISTINCT prod_price) AS count,
             SUM(DISTINCT prod_price) AS sum,
             AVG(DISTINCT prod_price) AS avg
FROM products;

9、group by

A    B        
a    b      
a    b        
a    b        
a    b        

如上面的表

select A , B, COUNT(B)
from table
group by A;

group by中我们只需要给出A或者B就好，因为这两列是1与1对应的关系

A    B
a    b1
a    b1
a    b2
a    b2

如上表，给出下面的SQL

select A , B, COUNT(B)
from table
group by A, B;

group by 语句则必须要将A, B列全部列出。

分组经常和聚集函数一起使用，聚集函数就作用于每个组。

SELECT order_num, SUM(item_price) AS sum, COUNT(*) AS count
FROM orderitems
GROUP BY order_num
HAVING sum > 20
ORDER BY order_num DESC;

HAVING一般和聚集函数字段一起过滤分组，WHERE子句过滤项。

10、联结

笛卡尔集

SELECT vend_name, prod_name, prod_price
FROM vendors, products
ORDER BY vend_name DESC, prod_name;

内部联结又称为等值联结

SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
ORDER BY vend_name DESC, prod_name;

相当于使用WHERE过滤笛卡尔集，另外一种写法如下：

SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id
ORDER BY vend_name DESC, prod_name;

自然联结就是把等值联结的重复列去掉，在SELECT语句中全部列出不重复的列table.*

自联结就是同一张表自己联结

左外联结：添加左边表没有匹配上的记录，用NULL填充左边表没有的字段。

SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

右外联结：添加右边表没有匹配上的记录，用NULL填充右边表没有的字段。

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

11、插入删除更新

INSERT INTO tablename(列)
VALUES(值),VALUES(值);

INSERT INTO
SELECT

DELETE FROM customers
WHERE cust_id = 10003;

UPDATE customers
SET cust_name = 'xxx'
WHERE cust_id = 10005;

12、创建表

CREATE TABLE customers
(
    cust_id  int NOT NULL AUTO_INCREMENT,
    cust_name char(50) UNIQUE,
    PRIMARY KEY (cust_id)
);

13、创建索引

CREATE INDEX index_name ON customers(cust_id);
CREATE UNIQUE INDEX index_name ON customers(cust_id);
CREATE PRIMARY KEY INDEX index_name ON customers(cust_id);