sql

use simulation;
# 多条SQL语句必须以分号分隔
show DATABASES ;
show TABLES ;
show COLUMNS  from customers;

# SQL语句不区分大小写
SHOW COLUMNS  FROM products;
SHOW COLUMNS  FROM vendors;
SHOW COLUMNS FROM orders;
SHOW COLUMNS FROM productnotes;
SHOW COLUMNS FROM orderitems;
show STATUS ;
show GRANTS ;
show errors;
show WARNINGS;
show CREATE DATABASE simulation; # 怎么创建simulation数据库
show CREATE TABLE vendors;  #查看怎么创建vendors表
SELECT  prod_name FROM products;
SELECT  prod_id FROM products;
SELECT prod_id,prod_name,prod_price FROM products;
SELECT * FROM products;

# DISTINCT 不能部分使用，它是全部使用
SELECT vend_id FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT DISTINCT   prod_id, prod_name FROM products;
SELECT  prod_id, prod_name FROM products;


SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5,7; # 从第5行开始的7行
SELECT prod_name FROM  products LIMIT 0,1; # 第1行的编号是0而不是1
SELECT prod_name FROM  products LIMIT 1,1 ; # 检索出来第2行

SELECT products.prod_name FROM  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_name,prod_id,prod_price FROM products ORDER BY prod_price DESC ;
#DESC 只能应用到直接位于其前面的列名
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC ,prod_name;

#如果想在多个列上进行降序排序，必须对每个列指定DESC关键字
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC ,prod_name DESC ;

# 找出最贵的价格
SELECT prod_price FROM products ORDER BY prod_price DESC  LIMIT 1;
# 找出最便宜的价格
SELECT prod_price FROM products ORDER BY prod_price   LIMIT 1;

### 过滤数据
SELECT prod_name,prod_price FROM  products WHERE prod_price = 2.50;

#在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后
SELECT prod_price FROM products WHERE prod_price >10 ORDER BY prod_price DESC ;

#MySQL 在执行匹配时默认不区分大小写,将值与串类型的列进行比较，则需要限定引号
SELECT prod_name,prod_price FROM products WHERE prod_name='fuses';

# 不匹配检查
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

# 范围检查 BETWEEN, 表示两个闭区间，也就是说是 >= && <=
SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 9.99 AND 10;

#寻找空值
SELECT prod_name FROM products WHERE  prod_price IS NULL ;
SELECT cust_id FROM customers WHERE cust_email IS NULL ;

## 组合WHERE子句
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id=1003 AND prod_price <=10;
SELECT prod_name, prod_price FROM  products WHERE  vend_id=1002 OR vend_id=1003;

# IN 操作符一般比OR 操作符清单执行更快，
# IN的其最大优点是可以包含其他SELECT语句，使得能够更动态的创建 WHERE 子句
SELECT vend_id,prod_name,prod_price FROM products WHERE  vend_id in(1002,1003);

# WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。
SELECT vend_id,prod_name,prod_price FROM products WHERE vend_id NOT  IN(1002,1003) ORDER BY prod_name;
# MySQL 支持使用NOT MySQL 支 持 使 用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反，

### 通配符(wildcard)
# % 表示任意字符出现任意(0,1或者多）次数
# % 不可以匹配NULL
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';

# like 是全字匹配，而正则表达式是部分匹配
SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

# 正则表达式,进行或匹配
SELECT prod_name FROM  products WHERE  prod_name REGEXP '1000|2000' GROUP BY prod_name;

# 正则表达式，匹配几个字符之一
SELECT prod_name FROM products WHERE  prod_name REGEXP  '[123]000' GROUP BY  prod_name;
SELECT prod_name FROM products WHERE  prod_name REGEXP  '[123] Ton' GROUP BY  prod_name;

SELECT vend_name FROM vendors WHERE vend_name REGEXP '.';  # . 匹配任意字符

# 特殊字符需要转义
SELECT vend_name FROM vendors WHERE vend_name REGEXP '\\.';

SELECT prod_name FROM  products WHERE prod_name REGEXP '\\([0-9] sticks?\\)' ORDER BY prod_name;

### 创建计算字段
# concat 用来拼接串
SELECT  concat(vend_name, '(', vend_city ,')') FROM vendors ORDER BY  vend_name;

# rtrim 用来删除右边多余空格
SELECT  concat(rtrim(vend_name),'(', rtrim(vend_city),')') FROM vendors ORDER BY  vend_name;

# 使用别名
SELECT concat(rtrim(vend_name),'(', rtrim(vend_city),')') AS vend_titile FROM vendors ORDER BY vend_name;

show COLUMNS  FROM orderitems;
SELECT prod_id, quantity, item_price,quantity*item_price  as price FROM orderitems WHERE order_num=2005;


### 使用聚集函数
SELECT avg(prod_price) AS avg_price FROM products;
SELECT avg(prod_price) AS avg_price FROM products WHERE vend_id=1003;

#使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空 值（NULL）还是非空值。
SELECT count(*) AS  num_cust FROM customers;

# 使用 COUNT(column) 对特定列中具有值的行进行计数， 忽略NULL值
SELECT count(cust_email) AS num_cust FROM customers;

# MAX 返回指定列中的最大值,MAX 和MIN 都忽略值为NULL的行

SELECT max(prod_price) AS maxPrice FROM products;
SELECT min(prod_price) AS minPrice FROM products;



# 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
# GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前
SELECT vend_id, count(*) AS num_prods FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING count(*)>=2;

# 唯一的差别是 WHERE过滤行，而HAVING过滤分组。
# 这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
SELECT cust_id,count(*) AS  mycount FROM orders GROUP BY cust_id HAVING count(*)>=2;

SHOW COLUMNS  FROM products;
SHOW COLUMNS  FROM vendors;

### 表的联结
# 由没有联结条件的表关系返回 的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘 以第二个表中的行数。
SELECT vend_name, prod_name,prod_price FROM vendors,products ORDER BY  vend_name,prod_name;

# 内部联结（inner join)
SELECT vend_name, prod_name,prod_price FROM vendors,products venders INNER JOIN products ON venders.vend_id=products.prod_id;

#无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被 联结的列）。
# 标准的联结（前一章中介绍的内部联结）返回所有数据，甚 至相同的列多次出现。
# 自然联结排除多次出现，使每个列只返回一次
# 怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。
# 自然联结是这样一种联结，其中你只能选择那些唯一的列。

# 外联结
SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders ON customers.cust_id = orders.cust_id;