《MySQL必知必会》读书笔记

最新推荐文章于 2023-02-09 23:48:55 发布

DOONDO

最新推荐文章于 2023-02-09 23:48:55 发布

阅读量198

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_41866744/article/details/85246186

版权

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

数据库（database）的概念：数据库是一个以某种有组织的形式存储的数据集合，也可以称为 “保存有组织数据的容器”。

表（table）：某种特定类型数据的结构化清单，同一个数据库中不能有两张相同名字的表。

模式（schema）：关于数据库和表的布局及特性的信息（比如，定义数据在表中如何存储；存储什么样的数据；如何分解；各部分信息如何命名等。）

列（column）：表中的一个字段，所有的表都是由一个或多个列组成。

数据类型（datatype）：所容许的数据的类型，每个表列都有相应的数据类型，它限制（容许）该列种存储的数据。数据类型可帮助正确的排序，优化磁盘的使用，在创建表时，需特别关注数据类型的选择。

行（row）：表中的记录是按行存储的，行就是表中的一个记录。

主键（primary key）：每一行的唯一标识用以区分每一个独立的行，而这个列称为主键，没有主键，更新或删除特定的行很困难，因为没有安全的方法保证只涉及相关的行。

主键规则：应该总是定义主键；任意两行都不具有相同的主键；每一行都必须有有一个主键值（主键值不允许为null值）

主键的几个好习惯：1.不更新主键列中的值 2.不重用主键列种的值 3.不再主键列种使用会更改的值（比如 name 就不适合）。

自动增量：某些表列需要唯一值，例如：订单编号、雇员ID等；每个行添加到表中时，MYSQL可以自动的为每个行分配一个可用编号、不用再添加时手动分配唯一值，这个功能就是所谓的自动增量。

create.sql:

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table creation scripts
########################################


########################
# 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);

数据sql:

########################################
# MySQL Crash Course
# http://www.forta.com/books/0672327120/
# Example table population scripts
########################################


##########################
# Populate customers table
##########################
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10001, 'Coyote Inc.', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Lee', 'ylee@coyote.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10002, 'Mouse House', '333 Fromage Lane', 'Columbus', 'OH', '43333', 'USA', 'Jerry Mouse');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10003, 'Wascals', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'rabbit@wascally.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES(10004, 'Yosemite Place', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Y Sam', 'sam@yosemite.com');
INSERT INTO customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES(10005, 'E Fudd', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'E Fudd');


########################
# Populate vendors table
########################
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');


#########################
# Populate products table
#########################
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');



#######################
# Populate orders table
#######################
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20005, '2005-09-01', 10001);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20006, '2005-09-12', 10003);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20007, '2005-09-30', 10004);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20008, '2005-10-03', 10005);
INSERT INTO orders(order_num, order_date, cust_id)
VALUES(20009, '2005-10-08', 10001);


###########################
# Populate orderitems table
###########################
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'ANV01', 10, 5.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'ANV02', 3, 9.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 3, 'TNT2', 5, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 4, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'JP2000', 1, 55);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'TNT2', 100, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'FC', 50, 2.50);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'FB', 1, 10);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'OL1', 1, 8.99);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'SLING', 1, 4.49);
INSERT INTO orderitems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 4, 'ANV03', 1, 14.99);

#############################
# Populate productnotes table
#############################
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(101, 'TNT2', '2005-08-17',
'Customer complaint:
Sticks not individually wrapped, too easy to mistakenly detonate all at once.
Recommend individual wrapping.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(102, 'OL1', '2005-08-18',
'Can shipped full, refills not available.
Need to order new can if refill needed.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(103, 'SAFE', '2005-08-18',
'Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(104, 'FC', '2005-08-19',
'Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(105, 'TNT2', '2005-08-20',
'Included fuses are short and have been known to detonate too quickly for some customers.
Longer fuses are available (item FU1) and should be recommended.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(106, 'TNT2', '2005-08-22',
'Matches not included, recommend purchase of matches or detonator (item DTNTR).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(107, 'SAFE', '2005-08-23',
'Please note that no returns will be accepted if safe opened using explosives.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(108, 'ANV01', '2005-08-25',
'Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(109, 'ANV03', '2005-09-01',
'Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(110, 'FC', '2005-09-01',
'Customer complaint: rabbit has been able to detect trap, food apparently less effective now.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(111, 'SLING', '2005-09-02',
'Shipped unassembled, requires common tools (including oversized hammer).'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(112, 'SAFE', '2005-09-02',
'Customer complaint:
Circular hole in safe floor can apparently be easily cut with handsaw.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(113, 'ANV01', '2005-09-05',
'Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead.'
);
INSERT INTO productnotes(note_id, prod_id, note_date, note_text)
VALUES(114, 'SAFE', '2005-09-07',
'Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.
Comment forwarded to vendor.'
);

#################检索数据###################
/ / 检索单个列 
SELECT 
  prod_name 
FROM
  products ;

/ / 检索多个列 
SELECT 
  prod_id,
  prod_name,
  prod_price 
FROM
  products ;

/ / 检索所有列 
SELECT 
  * 
FROM
  products ;

/ / 检索不同行（查询products表中产品的所有供应商ID） 
SELECT 
  vend_id 
FROM
  products ;

SELECT DISTINCT 
  vend_id 
FROM
  products ;

-- 使用 DISTINCT 去重
/ / 使用 DISTINCT 去重多列时，查出的是唯一的一行 只有AAA BBB CCC 都不同 才会被过滤 否则所有行都将被检索出来 
SELECT DISTINCT 
  AAA,
  BBB,
  CCC 
FROM
  table_name ;

/ / 检索前5行 带一个值得LIMIT总是从第一行开始检索 
SELECT 
  prod_name 
FROM
  products 
LIMIT 5 ;

/ / 检索第二个5行 (
  从第5行开始的后5行数据
) 第一个数为开始位置 第二个数为检索行数 
SELECT 
  prod_name 
FROM
  products 
LIMIT 5, 5 ;

/ / 行0 检索出来的第一行是0而不是行1 因此LIMIT (1, 1) 检索出来的是第1行 / / 行数不够时：LIMIT指定检索的行数为检索的最大行数，如果没有足够的行 (
  例如：给出LIMIT 10,
  5 但只有13行
) ，MYSQL将只返回它能返回的那么多行 / / LIMIT 4 OFFSET 3 从行3开始取4行数据 / / 完全限定列名 (表名.列名) 表名（库名.表名） 
SELECT 
  products.`prod_name` 
FROM
  test.`products` ;

#################排序检索数据###################
/ / 关系型数据库设计理论认为，如果不规定排序顺序，则不应该假定检索出来的顺序有意义 
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 ;

/ / 指定排序方向 默认时升序（A - Z） 降序（Z - A） 使用desc关键字 按价格降序检索 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
ORDER BY prod_price DESC ;

/ / 多个列降序排序 以降序排序产品（最贵的在前面）,
然后再对产品名排序 / / 下面只有prod_price 按照降序，prod_name仍然默认排序，如果要按照多个列排序 每个列必须都要指定 DESC 关键字 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
ORDER BY prod_price DESC,
  prod_name / / 使用 
ORDER BY 和 
LIMIT 组合找出一个列中最高或最低的值 
  SELECT 
    prod_price 
  FROM
    products 
  ORDER BY prod_price DESC 
  LIMIT 1 ;

#################过滤数据###################
/ / 检索prod_price = 2.50 的行的 prod_name prod_price列数据 
SELECT 
  prod_name,
  prod_price 
FROM
  products 
WHERE prod_price = 2.50 ;

/ / 检索prod_name = 'fuses' 的一行 mysql在执行匹配时不区分大小写 
SELECT 
  prod_name,
  prod_price 
FROM
  products 
WHERE prod_name = 'fuses' ;

/ / 检索价格在5美元和10美元之间的所有产品 (
  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 ;

SELECT 
  * 
FROM
  customers 
WHERE cust_email IS NULL ;

/ / 
AND 操作符 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE vend_id = 1003 
  AND prod_price <= 10 ;

/ / 
OR 操作符 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE vend_id = 1002 
  OR vend_id = 1003 ;

/ / 
OR 和 
AND 一起使用时 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE (vend_id = 1002 
    OR vend_id = 1003) 
  AND prod_price >= 10 ;

SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE (vend_id = 1002 
    AND prod_price >= 10) 
  OR (vend_id = 1003 
    AND prod_price >= 10) / / IN操作符 检索1002 和 1003 产商生产的产品 
  SELECT 
    prod_id,
    prod_price,
    prod_name 
  FROM
    products 
  WHERE vend_id IN (1002, 1003) ;

SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE vend_id NOT IN (1002, 1003) ;

/ / LIKE % 任意字符出现任意次数 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE prod_name LIKE 'jet%' ;

/ / _ 匹配单个字符 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE prod_name LIKE '_ ton anvil' ;

SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE prod_name LIKE '% ton anvil' ;

/ / 正则表达式匹配 REGEXP 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE prod_name REGEXP '1000' ;

/ / .匹配任意一个字符 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE prod_name REGEXP '.000' ;

/ / | 匹配两个中的任意一个 
SELECT 
  prod_id,
  prod_price,
  prod_name 
FROM
  products 
WHERE prod_name REGEXP '1000|2000' ;

/ / 匹配几个字符之一 [ 123 ] 定义一组字串 匹配1或2或3 
SELECT 
  prod_name 
FROM
  products 
WHERE prod_name REGEXP '[123] ton' 
ORDER BY prod_name ;

SELECT 
  prod_name 
FROM
  products 
WHERE prod_name REGEXP '[1-5] ton' 
ORDER BY prod_name ;

/ / 正则匹配特殊字符.使用 \\ 进行转义（一般时一个反斜杠 mysql两个 一个mysql自己解释 一个正则表达式要求） 
SELECT 
  prod_name 
FROM
  products 
WHERE prod_name REGEXP '\\.' 
ORDER BY prod_name ;

#################计算字段###################
/ / 拼接字段 把两个单列值拼接 RTRIM() 删除括号内字符串右侧多余空格 LTRIM() 删除括号内字符串左侧多余空格 TRIM() 去掉两边的空格 
SELECT 
  CONCAT(
    vend_name,
    '(',
    RTRIM(vend_country),
    ')'
  ) AS 供应商名称及地址 
FROM
  vendors 
ORDER BY vend_name ;

SELECT 
  CONCAT(
    '(',
    vend_id,
    ',',
    vend_name,
    ',location:',
    vend_country,
    ')'
  ) 
FROM
  vendors 
ORDER BY vend_name ;

/ / 算数操作 / / 检索出订单表产品id,
物品数量，物品单价 
SELECT 
  prod_id,
  quantity,
  item_price 
FROM
  orderitems 
WHERE order_num = 20005 ;

/ / 检索出订单表产品id,
物品数量，物品单价 并汇总价格 
SELECT 
  prod_id,
  quantity,
  item_price,
  quantity * item_price AS '每种物品价格总计' 
FROM
  orderitems 
WHERE order_num = 20005 ;

#################使用数据处理函数###################
/ / UPPER() 将文本转换为大写 
SELECT 
  vend_name,
  UPPER(vend_name) AS 大写的供应商名 
FROM
  vendors ;

/ / 日期和时间处理函数 
SELECT 
  cust_id,
  order_num 
FROM
  orders 
WHERE order_date = '2005-09-01' ;

/ / 这样比较更精确 只比较日期部分 
SELECT 
  * 
FROM
  orders 
WHERE DATE(order_date) = '2005-09-01' ;

/ / 监测2005年9月下的所有订单 
SELECT 
  * 
FROM
  orders 
WHERE DATE(order_date) BETWEEN '2005-09-01' 
  AND '2005-09-30' ;

或 
SELECT 
  * 
FROM
  orders 
WHERE YEAR(order_date) = 2005 
  AND MONTH(order_date) = 9 ;

/ / 聚合函数 / / AVG 平均值 忽略为NULL的行 
SELECT 
  prod_id,
  vend_id,
  prod_name,
  prod_price,
  AVG(prod_price) AS 所有产品的平均价格,
  prod_desc 
FROM
  products ;

/ / COUNT(*) 对表中的行数目进行计数，不管表列中包含的是空值 (NULL) 还是非空值 
SELECT 
  COUNT(*) AS num_cust 
FROM
  customers ;

/ / 使用 COUNT(COLUMN) 对特定列中具有值的行进行计数 不管行中各列有什么值 
SELECT 
  COUNT(cust_email) AS num_email_cust 
FROM
  customers ;

/ / MAX(COLUMN) 返回指定列中的最大值 MIN(COLUMN) 返回指定列最小值 都忽略null值 
SELECT 
  MAX(prod_price) AS max_price 
FROM
  products ;

/ / SUM() / / 用于检索物品总数 或者计算值的总和 
SELECT 
  SUM(item_price * quantity) AS total_2005_mt 
FROM
  orderitems 
WHERE order_num = 20005 ;

/ / 聚集函数使用 
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 ;

/ / 数据分组 GROUP BY / / 返回供应商1003提供的产品数目 
SELECT 
  * 
FROM
  products ;

/ / 返回各个供应商提供的产品数目 
SELECT 
  vend_id,
  COUNT(*) AS num_prods 
FROM
  products 
GROUP BY vend_id ;

/ / 使用 WITH ROLLUP 关键字 可以得到每个分组以及每个分组汇总级别 
SELECT 
  VEND_ID,
  COUNT(*) AS num_prods 
FROM
  products 
GROUP BY vend_id WITH ROLLUP ;

/ / 过滤分组 WHERE 过滤行 HAVING 过滤分组 where是在数据分组前进行过滤 having是在数据分组后进行过滤 / / 检索至少有两个订单的顾客 
SELECT 
  cust_id,
  COUNT(*) AS orders 
FROM
  orders 
GROUP BY cust_id 
HAVING COUNT(*) >= 2 ;

/ / 检索在过去163月内具有两个以上订单的顾客 
SELECT 
  cust_id,
  COUNT(*) AS num_order 
FROM
  orders 
WHERE PERIOD_DIFF(
    DATE_FORMAT(NOW(), '%Y%m'),
    DATE_FORMAT(order_date, '%Y%m')
  ) <= 163 
GROUP BY cust_id 
HAVING COUNT(*) >= 2 ;

/ / 检索价格10（含）以上，具有2个（含）以上的供应商 
SELECT 
  vend_id,
  COUNT(*) AS num_prods 
FROM
  products 
WHERE prod_price >= 10 
GROUP BY vend_id 
HAVING COUNT(*) >= 2 ;

//分组排序
//检索总计订单大于等于50的订单号和总计订单价格 并按照总计订单价格asc排序
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子句顺序  SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

//子查询 1.利用子查询做数据过滤
//检索需要订购物品TNT2的所有客户

//1.先查出订单明细表中 订购TNT2的订单编号
SELECT order_num FROM orderitems WHERE prod_id='TNT2';
//2.根据order_num 可以在订单表中 查询出cust_id
SELECT cust_id FROM orders  WHERE order_num IN 
  (SELECT order_num FROM
    orderitems 
  WHERE prod_id = 'TNT2'); 
//3.根据cust_id查询出customers中顾客信息
SELECT * FROM customers 
WHERE cust_id IN 
  (SELECT cust_id FROM orders 
   WHERE order_num IN 
	(SELECT order_num FROM orderitems 
         WHERE prod_id = 'TNT2'));
//作为计算字段使用子查询
//检索customers表中每个客户的订单总数

//1.先检索订单表中每个客户有多少个订单
SELECT COUNT(*) order_total FROM orders GROUP BY cust_id;
//2.在customers表中，根据cust_id检索出每个客户的订单数目
SELECT cust_id,cust_name,(SELECT COUNT(*) order_total FROM orders WHERE orders.`cust_id` = customers.`cust_id`) AS '订单总数'
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`
ORDER BY vend_name,prod_name;

//多表联结
SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE orderitems.`prod_id` = products.`prod_id`
AND products.`vend_id` = vendors.`vend_id`
AND order_num = 20005;


//使用多表查询检索需要订购物品TNT2的所有客户（子查询,多表联结查询）
SELECT cust_name,cust_contact FROM customers 
WHERE customers.`cust_id` IN 
  (SELECT cust_id FROM orders 
	INNER JOIN orderitems 
	ON orders.`order_num` = orderitems.`order_num` 
	AND orderitems.`prod_id` = 'TNT2');

SELECT cust_name,cust_contact FROM customers,orders,orderitems
WHERE customers.`cust_id` = orders.`cust_id`
AND orders.`order_num` = orderitems.`order_num`
AND orderitems.`prod_id` = 'TNT2';

//创建高级联结
//1.使用列别名 使用别名引用被检索的表列，列别名可以返回到程序客户机  好处：1.缩短sql,可读性增强 2.允许单挑select语句多次使用相同表
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM vendors ORDER BY vend_name;

//使用表别名进行限定，表别名不返回客户机
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 oi.`prod_id` = 'TNT2';

//自联结
//检索物品为DTNTR的供应商生产的所有物品
//1.使用子查询检索
SELECT prod_id,prod_name FROM products
WHERE products.`vend_id` IN (SELECT vend_id FROM products AS p
WHERE p.prod_id = 'DTNTR');

//从相同表检索数据，大多数情况下自联结查询效率会比子查询快得多 返回的列必须指定那张表的，即使两张表一样，否则mysql找不到
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 c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price
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 = 'FB'

//检索所有客户及其订单
SELECT customers.`cust_id`,orders.`order_num`
FROM customers INNER JOIN orders
ON customers.`cust_id` = orders.`cust_id`;

//检索所有客户包括那些没有订单的客户 
//左外部联结和右外部联结（左外就是用join左边所有行，右外就是join右边所有行）
SELECT customers.`cust_id`,orders.`order_num`
FROM customers LEFT OUTER JOIN orders
ON customers.`cust_id` = orders.`cust_id`;

//使用带聚集函数的的联结
SELECT customers.`cust_name`,customers.`cust_id`,COUNT(orders.`order_num`) AS total_order
FROM customers INNER JOIN orders
ON customers.`cust_id` = orders.`cust_id`
GROUP BY customers.`cust_id`;

//使用聚集函数外部联结 检索每个客户下单数目，包括至今尚未下单的用户
SELECT customers.`cust_name`,customers.`cust_id`,COUNT(orders.`order_num`) AS total_order
FROM customers LEFT OUTER JOIN orders
ON customers.`cust_id` = orders.`cust_id`
GROUP BY customers.`cust_id`;

//组合查询（复合查询） UNION 会自动去除重复的 
//检索价格小于等于5的所有物品 而且还想包括供应商1001和1002生产的所有物品(不考虑价格)
SELECT vend_id,prod_name,prod_price FROM products
WHERE prod_price <=5
UNION
SELECT vend_id,prod_name,prod_price FROM products
WHERE vend_id IN (1001,1002);

//多个where子句
SELECT vend_id,prod_name,prod_price FROM products
WHERE prod_price<=5 OR vend_id IN (1001,1002);

//UNION ALL 返回所有匹配行
SELECT vend_id,prod_name,prod_price FROM products
WHERE prod_price <=5
UNION ALL
SELECT vend_id,prod_name,prod_price FROM products
WHERE vend_id IN (1001,1002);

//对组合查询结果进行排序
SELECT vend_id,prod_name,prod_price FROM products
WHERE prod_price <=5
UNION
SELECT vend_id,prod_name,prod_price FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;

//最常用的引擎为 MYISAM(支持全文本搜索) 和 INNODB
//全文索引 FULLTEXT 不要再导入数据的时候开启全文索引 因为更新索引花费时间
//使用 MATCH() 指定搜索列 和 AGAINST()指定搜索内容 全文本索引排序会按照含词等级来，等级值高的排在前面

SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');

//查看全文索引排序等级
SELECT note_text,MATCH(note_text) AGAINST('rabbit') AS rank FROM productnotes;


//未使用查询拓展
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils');

//查询拓展 查询匹配的相关内容
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

//布尔文本搜索 不依赖全文索引 但数据量大性能低  IN BOOLEAN MODE 没有指定布尔操作符
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy' IN BOOLEAN MODE);	

//检索包含heavy但不包含任意rope开始的词的行
SELECT note_text FROM productnotes WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);

//全文索引 短词被忽略(3个或3个以下) 词列表里的词被忽略 50%规则 一个词超过50%以上行忽略


//INSERT操作可能很耗时，而且它可能降低等待处理的select语句性能，如果数据检索最重要，可以在 INSERT 和 INTO 中间添加关键字 LOW_PRIORITY update和delete 也适应此规则
// INSERT 将一条select语句的结果插入到表中  select后面的列不需要和insert的列名一致，mysql仅仅把查询结果当作值来处理
INSERT INTO tableA(a,b,c)SELECT a,b,c FROM tableB;

//UPDATE 如果更新多条发生错误会进行回滚 如果不想回滚可使用 IGNORE  例如：update IGNORE tableA

//如果删除表中指定行用delete 所过是想删除表中所用行 用 TRUNCATE TABLE  完成相同的工作 但更快 原理：删除原来的表 建立新表

//创建表： CREATE TABLE IF NOT EXISTS tableName 
//主键：唯一标识每一行数据的列，唯一且不为null

SELECT LAST_INSERT_ID(cust_id) FROM customers;

//引擎:
   INNODB :一个可靠的事务引擎，但不支持全文本搜索
   MEMORY :功能上等同于 MYISAM ,但数据存储在内存，不是磁盘 速度很快
   MYISAM :性能极高的引擎，支持全文本搜索，但不支持事务处理

//视图 虚拟的表 本身没有数据 而是一种需要检索的数据的查询 视图不能被索引
//创建视图
cerate VIEW viewname;
//查看创建的视图
SHOW cerate VIEW viewname;
//删除视图
DROP VIEW viewname;
//更新视图
可以先drop 再create 也可以使用create OR REPLACE VIEW
//视图的用途：1.简化复杂联结 2.格式化检索的结果数据（比如拼接字段） 3.过滤不想要的数据 4.使用视图获取计算字段结果集

SELECT cust_name,cust_contact FROM customers,orders,orderitems
WHERE customers.`cust_id` = orders.`cust_id`
AND orderitems.`order_num` = orders.`order_num`
AND prod_id = 'TNT2';

//创建视图
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`;

//删除
DROP VIEW productcustomers

SELECT cust_name,cust_contact FROM productcustomers WHERE prod_id = 'TNT2';