无限分类的数据表设计

   CREATE TABLE tdb_goods_types(
     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, //分类的id
     type_name VARCHAR(20) NOT NULL, //分类的名字
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 //父类的id
  ); 

数据表自身的连接：

---

      左表（父表p）                                   右表（子表s）

| type_id | type_name|              | type_name  | parent_id |

---

父表、子表可以互相调换：SELECT p.type_id,p.type_name,count(s.type_name) child_count【计数】 FROM tdb_goods_types  p LEFT JOIN tdb_goods_types s ON p.parent_id=s.type_id GROUP BY 【分组】p.type_name ORDER BY【排序】 p.type_id;

外连接

Ａ LEFT JOIN B join_condition

数据表Ｂ的结果依赖数据表A

数据表A的结果集根据左连接条件依赖所有数据表（B表除外）。

左外连接条件决定如何检索数据表Ｂ（在没有指定WHERE条件的情况下）

如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。

如果使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL, MySOL将在找到符合连接条件的记录后停止搜索更多的行。

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

是通过多张表连接显示，呈现出与单张表相同的记录。

连接就是外键的逆向操作。

左外连接：显示左表中的全部和右表中符合条件的记录。如果左表的某个字段右表中没有，则该字段为NULL。

右外连接：显示右表中的全部左右表中符合条件的记录。如果右表的某个字段左表中没有，则该字段为NULL。

使用 ON 来设定连接条件，使用WHERE 进行结果集的过滤。

内连接仅显示符合条件的记录。

SELECT goods_id, goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON  tdb_goods.cate_id = tdb_goods_cate.cate_id;

结果集中，由于商品中没有交换机、网卡等所以不会显示出来。

连接：A表 连接类型(内连接、左外连接、右外连接) B表 ON 两张表连接条件。

数据表参照（可以给表起别名）：

tbl_name [[AS] alias] | table_sabquery [AS] alias

数据表可以使用tbl_name AS alias_name或tbl_name alias_name 赋予别名。

table_subquery可以作为子查询使用在FROM子句中，必须起别名。

创建数据表同时将查询结果写入到数据表中

CREATE TABLE [IF NOT EXISTS] tbl_name[ ( create_definition,... ) ] select_statement;

CREATE TABLE tdb_goods_brands (
brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
brand_name VARCHAR(40) NOT NULL
) SELECT brand_name FROM tdb_goods GROUP BY brand_name;

多表更新：
UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

通过ALTER TABLE语句修改数据表结构
ALTER TABLE tdb_goods CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

外键，可以是物理的也可以是逻辑的。物理外键更能保证数据的完整性和一致性。
数字类型的字段占用的空间更小，查询的效率也更高。

多表更新（参照另外一张表更新本表的记录）：

UPDATE table_references SET col_name1={expr1 | DEFAULT}[,col_name2={expr2 | DEFAULT}] ... [WHERE where_condition]

表的参照关系：一张表通过INNER JOIN或者LEFT OUTER JOIN去连接另外的一张表并通过ON指定连接条件，也可以给表起别名。

table_references： {[INNER | CROSS] JOIN | {LEFT | RIGHT} [OUTER] JOIN} table_reference ON conditional_expr

UPDATE 表2 INNER JOIN 表1 ON 连接条件 SET 连接方式；

UPDATE tdb_goods(表2) INNER JOIN tdb_goods_cates(表1) ON goods_cate=cate_name(连接条件) SET goods_cate=cate_id(连接方式);

INSERT [INTO] tbl_name SET col_name={exprDEFAULT},...//可以使用子查询

INSERT [INTO] tbl_name [(col_name,...)] SELECT ...//将查询结果写入数据表

使用[NOT]IN的子查询:

语法结构：operand comparison_operator [NOT] IN (subquery）=ANY运算符与IN等效，！=ALL或<>ALL运算符与NOT IN等效。

使用[NOT]EXISTS的子查询（不经常使用）:

定义：如果子查询返回了结果，EXISTS将返回TRUE;否则为FALSE。

使用比较运算符的子查询，包括=、>、<、>=、<=、<>、!=、<=>。

语法结构：operand comparison_operator subquery

AVG聚合函数，求平均值，只有一个返回值。使用ROUND（）函数来进行四舍五入，并保留小数的位数。

SELECT AVG(goods_price) FROM tdb_goods; \\平均值为5636.3636364

SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;\\平均值为5636.36

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >=5636.36;

等价于

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

如果子查询返回的是多个结果的时候，可以使用关键字ANY、SOME、或者ALL修饰比较运算符

ANY和SOME是等价的：外查询符合其中一个即可。

ALL:外查询符合全部。

operand comparison_operator ANY（subquery）

operand comparison_operator SOME(subquery）

operand comparison_operator ALL（subquery）

使用ANY、SOME、ALL、的简单原则：

子查询是指在出现在其他SQL语句中的SELECT子句：

  SELECT * FROM t1 WHERE column1 = (SELECT column2 FROM t2);

  其中，SELECT * FROM t1 ...称为Outer Query[外层查询](或者Outer Statement),

  SELECT column2 FROM t2 称为Sub Query[子查询]。

 子查询是嵌套在外查询的内部，且必须出现在圆括号之间。有可能在子查询内部再嵌套子查询。

子查询可以包含多个关键字或条件：DISTINCT、GROUP BY、ORDER BY、LIMIT、函数等。

外层查询可以是：SELECT、INSERT、UPDATE、DELETE、SET或DO。

子查询可以返回标量、一行、一列、或子查询。

记录操作：增，改，删，查
INSERT://增加记录，有三种方法。
INSERT [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr|DEFAULT},...),(...),...
INSERT [INTO] tbl_name SET col_name={expr|DEFAULT},...
INSERT [INTO] tbl_name [(col_name,...)] SELECT...

UPDATE://更新数据
单表更新
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition]
多表更新

DELETE : //删除数据
单表删除
DELETE FROM tbl_name [WHERE where_condition}
多表删除

SELECT: //查询
SELECT select_expr [,select expr2...] 只查找某一个函数或表达式
[
FROM table_references 查询表名
[WHERE where_conditon] 查询条件
[GROUP BY {col_name|position} [ASC|DESC],...] 按某个字段进行分组，相同的只显示第一个
[HAVING where_conditon] 分组时，给出显示条件
[ORDER BY {col_name|expr|position} [ASC|DESC],...] 排序
[LIMIT {[offset,]row_count|row_count OFFSET offset}] 限制返回数量
]

限制查询结果返回数量
[LIMIT{[offset,]row_countrow_count OFFSET offset}]
SELECT * FROM users LIMIT 2; // 从第一条开始（第一个为0），返回第一条、第二条

SELECT * FROM users LIMIT 2,2 ;//从第三条开始（第一个为0），返回第三条、第四条

limit 0，2     ---0代表第一行，2代表显示几行

可以把查询结果放入指定的结果表中：

INSERT test(username) SELECT username FROM users WHERE age >=30;

OFFSET：进行分页的时候，就会利用LIMIT语句来进行分页效果的实现，在分页效果实现当中，偏移值的计算公式是已经计算好的，计算公式就是当前括号中的页码减1*每一页显示的记录数。

order by语句对查询结果排序（除了对分组条件进行指定，还可以对分组的结果进行排序）

语法：[ORDER BY{col_name | expr | position}[ASC | DESC], ...]

select * from users order by id desc; //对一个字段排序。
select * from users order by age,id desc; //两个字段同时排序，规则是先按前面的字段排，在基础上再按后面字段排。