MySQL

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################
CREATE DATABASE CrashCourse;
USE CrashCourse;
########################
# Create customers table
########################
CREATE TABLE customers
(
  cust_id      int       NOT NULL AUTO_INCREMENT,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  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 orderitems table
#########################
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 orders table
#####################
CREATE TABLE orders
(
  order_num  int      NOT NULL AUTO_INCREMENT,
  order_date datetime NOT NULL ,
  cust_id    int      NOT NULL ,
  PRIMARY KEY (order_num)
) ENGINE=InnoDB;


#######################
# Create products table
#######################
CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB;


######################
# Create vendors table
######################
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB;


###########################
# Create productnotes table
###########################
CREATE TABLE productnotes
(
  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;




#####################
# Define foreign keys
#####################
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products (prod_id);
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id) REFERENCES customers (cust_id);
ALTER TABLE products ADD CONSTRAINT fk_products_vendors FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);


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;
SELECT prod_name FROM products LIMIT 4 OFFSET 3;-- MySQL5支持的LIMIT的另一种替代语法，意为从行3开始取4行，就像LIMIT 3,4一样
-- 使用完全限定的表名或列名
SELECT products.prod_name FROM products;-- 使用完全限定的名字引用列
SELECT products.prod_name FROM crashcourse.products;-- 表名也可以是完全限定
-- 排序数据  关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出的数据的顺序有意义
-- ORDER BY 用非检索的列排序数据也是合法的
SELECT prod_name FROM products ORDER BY prod_name;
-- 按多个列排序
-- 检索三个列，并按其中两个列对结果进行排序，首先按价格，然后按名称，若价格唯一，则不会按名称排序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;
-- 指定排序方向，默认为升序，降序需要使用DESC关键字，升序为ASC
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;
-- 用DESC排序多个列,DESC只应用到直接位于其前面的列名，若想在多个列上进行降序排序，需要对每个列指定DESC关键字
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name;
-- 使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值
-- 在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它须位于ODER BY之后，次序不对将产生错误消息
-- ORDER BY必须是SELECT语句中的最后一条子句
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;
-- 正则表达式
-- 下面语句检索列prod_name包含文本1000的所有行
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000'
ORDER BY prod_name;
-- 下面语句使用了正则表达式.000，它表示匹配任意一个字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000'
ORDER BY prod_name;
/*LIKE与REGEXP的差别：LIKE匹配整个列，如果被匹配的文本仅在
列值中出现，LIKE将不会找到它，相应的行业不被返回(除非使用通配符)，而REGEXP在列值内进行匹配。
REGEXP使用^和$定位符就可以匹配整个列。匹配不区分大小写，若要区分，可使用BINARY关键字，
如WHERE prod_name REGEXP BINARY 'JetPack .000'.*/
-- 进行OR匹配,为搜索两个串之一，使用|，多个OR条件可以并入单个正则表达式
-- 例如：'1000|2000|3000'
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'-- 匹配1或2或3
ORDER BY prod_name;
-- [^123]匹配除这些字符外的任何东西
-- 匹配范围
SELECT 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;
/*[0-9]匹配任意数字，sticks？匹配stick和sticks(s后的？使s可选，因为？匹配它前面的任何字符的0次或1次出现)，
\\)匹配) */
-- 匹配连在一起的4位数字
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][0-9][0-9][0-9]'
ORDER BY prod_name;
-- 定位符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'-- 使用了^定位符在开始处匹配
ORDER BY prod_name;
-- ^的双重用途。在集合中(用[和]定义)，用它来否定该集合，否则，用来指串的开始处。


-- 文本处理函数
SELECT vend_name,Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name; -- Upper()将文本转化为大写
-- 日期处理函数
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;
-- AVG()也可以用来确定特定列或行的平均值,只用于单个列且忽略列值为NULL的行
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id=1003;
-- COUNT()函数
-- 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值还是非空值
-- 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值
SELECT COUNT(*) AS num_cust-- 所有行
FROM customers;
SELECT COUNT(cust_email) AS num_cust-- 有电子邮件的
FROM customers;




SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
-- 使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别的值
SELECT vend_id,COUNT(*) AS num_prods
FROM products
GROUP BY vend_id WITH ROLLUP;
-- SELECT子句及其顺序
/*SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据； 
2、where子句基于指定的条件对记录行进行筛选； 
3、group by子句将数据划分为多个分组；  
4、使用聚集函数进行计算；  
5、使用having子句筛选分组；  
6、计算所有的表达式；   
7、使用order by对结果集进行排序。
8、LIMIT*/
-- 子查询，嵌套在查询中的查询，子查询总是从内向外处理

-- 相关子查询，涉及外部查询的子查询

/*数据库的三大范式：
第一范式： 行不重复，列不可再分。
第二范式： 所有非主键列必须依赖于主键列。
第三范式： 除了主键以外的其他列互不关联（非主键之间必须相互独立）
DDL  定义
DML  数据操作
DQL 查询语句
DLL 数据控制
系数据库的用处：数据库，相当于存储数据的仓库，可以去除冗余，可以快速查询想要的数据，更新，添加，查询。让数据更具有安全性，完整性，一致性。
关系数据库关系有：一对一关系，一对多关系，多对多关系。*/