MySQL数据库的七大函数

连接类型
    INNER JOIN【内连接】：显示符合两个表的共同属性的记录。
    LEFT JOIN【左外连接】：显示左表全部和左右符合连接条件的记录。
    RIGHT JOIN【右外连接】：显示左右符合连接条件的记录和右表全部记录。
    若某字段只存在某一表，则另一表的里字段返回NULL。

ON与WHERE
    通常使用ON关键字来设定连接条件，
    使用WHERE关键字进行结果集记录的过滤。

（1）INSERT ... SELECT实现复制

INSERT tdb_goods(goods_name,cate_id,brand_id) 
SELECT goods_name,cate_id,brand_id 
FROM tdb_goods 
WHERE goods_id IN (19,20);

（2）查找重复记录

SELECT goods_id,goods_name 
FROM tdb_goods 
GROUP BY goods_name HAVING count(goods_name) >= 2;

（3） 删除重复记录

DELETE t1 
FROM tdb_goods AS t1 
LEFT JOIN (
    SELECT goods_id,goods_name 
    FROM tdb_goods 
    GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 
ON t1.goods_name = t2.goods_name 
WHERE t1.goods_id > t2.goods_id;

@MySQL---无限分类
即在同一张表中既有父类，又有子类。
通过对同一张数据表的自身连接来进行查询，需要对表标识别名。

eg:

查找所有分类及其父类

SELECT s.type_id ,s.type_name,p.type_name 
FROM tdb_goods_types s 
LEFT JOIN tdb_goods_types p 
ON s.parent_id=p.type_id;

查找所有分类及其子类

SELECT p.type_id ,p.type_name,s.type_name 
FROM tdb_goods_types p 
LEFT JOIN tdb_goods_types s 
ON p.type_id=s.parent_id;

查找所有分类及其子类的数目

SELECT p.type_id ,p.type_name,COUNT(s.type_name) 
FROM tdb_goods_types p 
LEFT JOIN tdb_goods_types s 
ON p.type_id=s.parent_id
GROUP BY p.type_name 
ORDER BY p.type_id;

外连接：

    A LEFT JOIN B join_condition
    数据表B的结果集依赖于数据表A
    数据表A的结果集根据左连接条件依赖所有数据表(B表除外)
    左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)
    如果数据表A的某条记录符合WHERE条件，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行
内连接：
    使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：column_name IS NULL 。如果 column_name 被指定为 NOT NULL，MySQL将在找到符合连接着条件的记录后停止搜索更多的行（查找冲突）

多表的连接跟两张表的连接一样
表的连接实质就是外键的逆向约束。
eg：

SELECT goods_id,goods_name,b.cate_name,c.brand_name,goods_price
FROM products AS a INNER JOIN products_cate AS b ON a.goods_cate = b.cate_id
INNER JOIN products_brand AS c ON a.brand_name = c.brand_id;

@MySQL---右外连接

@MySQL---左外连接

@MySQL---内连接

1.连接
MySQL在SELECT语句、多表更新、多表删除语句中支持JOIN操作。
语法结构
    table reference A
    {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
    table_reference B
    ON condition_expr
2.数据表参照
    table_reference
    tbl_name [[AS] alias] | table_subquery [AS] alias
    数据表可以使用tbl_name AS alias_name 或 tbl_name alias_name赋予别名。
    table_subquery可以作为子查询使用在FROM子句中，这样的子查询必须为其赋予别名。

@MySQL---CREATE SELECT多表更新之一步到位
1、语法
建表、查询、写入三合一：
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

2、eg:

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 tdb_goods 
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;//通过ALTER TABLE语句修改数据表结构

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

@MySQL---子查询之[NOT] IN/EXISTS(不)包含/存在
1、语法
operand comparison_operator [NOT] {IN|EXISTS} (subquery)
IN 相当于 =ANY 或 =SOME
NOT IN 相当于 !=ALL 或 <>ALL
子查询返任何行 EXISTS返true;反之,返false。

2、eg:

SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price IN (
    SELECT goods_price 
    FROM tdb_goods 
    WHERE goods_cate = '超级本');//获取价格在超级本的价格中的任意商品。

CREATE TABLE IF NOT EXISTS tdb_goods_cates(
cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cate_name VARCHAR(40) NOT NULL
);

@MySQL---子查询之比较运算符
1、语法
使用比较运算符的子查询：
=、>、<、>=、<=、<>、!=、<=>
语法结构
operand comparison_operator [ANY|SOME|ALL] subquery
当subquery的结果集是多条记录时使用[ANY|SOME|ALL]。
ANY: >ANY 表示至少大于一个值。
ALL: >ALL 表示大于每一个值。
ANY: =ANY 或者 SOME 都是等于他们本身

2、eg:
单独分析筛选时：

SELECT AVG(goods_price) FROM tdb_goods;              //AVG函数代表求其平均值//
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;     //round(@,#)代表输出格式为@数小数点后#位输出//
SELECT goods_id,goods_name,goods_price 
FROM tdb_goods 
WHERE goods_price>=5391.30; //输出价格大于5391.30的id、name、price//

综合起来运用子查询时：

SELECT goods_id,goods_name,goods_price 
FROM tdb_goods 
WHERE goods_price>=(SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);//查找价格大于平均值的商品//
SELECT goods_price FROM tdb_price WHERE goods_cate='超级本'\G;  //检索结果非唯一//

使用ANY、SOME、ALL的子查询

SELECT goods_id,goods_name,goods_price 
FROM tdb_goods 
WHERE goods_price>=ANY (
    SELECT goods_price 
    FROM tdb_goods 
    WHERE goods_cate='超级本');//大于等于最小值
SELECT goods_id,goods_name,goods_price 
FROM tdb_goods 
WHERE goods_price>=ALL (
    SELECT goods_price 
    FROM tdb_goods 
    WHERE goods_cate='超级本');//大于等于最大值
SELECT goods_id,goods_name,goods_price 
    FROM tdb_goods 
    WHERE goods_price>=SOME (
        SELECT goods_price 
        FROM tdb_goods 
        WHERE goods_cate='超级本');//大于等于最小值

@MySQL---子查询
1、语法
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement)
SELECT column1 FROM t2 称为Sub Query[子查询]
出现在【其他SQL语句内】的SELECT子句。
嵌套在【查询内部】，且必须始终出现在【圆括号内】。
包含多个关键字或者条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等。
外层查询可以是：【SELECT,INSERT,UPDATE,SET或DO】。
返回值：标量、一行、一列或者子查询。

2、eg：

SELECT * 
FROM test 
WHERE name = (
    SELECT name 
    FROM users 
    WHERE age=20);//查询users表age为20的name在test表中的信息

解决输入中文导致的乱码
1、建表时，应当加上CREATE TABLE IF NOT EXISTS $tableName($paramArray) default charset=utf8选项；
2、插入数据前，应当先设置编码:SET NAMES utf8|gbk|gb2312;
3、然后插入数据：INSERT $tableName($param2insertArray) VALUES($valueArray);