MySQL学习笔记（三）修改操作、查询数据表

操作数据表中的记录

插入操作

INSERT第一种
INSERT [INTO] tb_name [col_name,....] VALUES(...,...)[,(...,...)，...] 多行多个相对插入
expr--算数表达式or函数表达式；
default--创建表结构定义的值；
如果表结构中有主建primary key auto_increment，插入时对应值可以用NULL或default;

INSERT第二种（与第一种方式的区别在于，此方式可以使用子查询(SubQuery)）
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},col_name2={expr|DEFAULT},....单行多个插入

插入记录方法三
INSERT [INTO] tbl_name [(col_name, ... )] SELECT ...

SELECT--查找的结果

单表更新

单表更新：UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition];

当缺少WHERE时，所有记录都会更新。

LOW_PRIORITY：UPDATE的执行被延迟了，直到没有其它的客户端从表中读取为止。
IGNORE：即使在更新过程中出现错误，更新语句也不会中断。

举例：UPDATE users SET age=age+1; //整张表发生改变
UPDATE users SET age=age+id,sex=0;  //多个字段用逗号隔开。
UPDATE users SET sex=1 WHERE id%2=0;  //id为偶数的sex值设为1。求余为0即偶数

单表删除

DELETE FROM tbl_name [WHERE where_condition]
若不添加WHERE则删除全部记录
删除某条记录后，再插入一条新的记录，自动编号不会补到删除记录的编号上，而是基于原有记录最大编号继续增加

查询记录

SELECT select_expr[,select_expr...]
[
FROM table_references
[WHERE where_condition]
[GROUP BY{col_name|position} [ASC|DESC],..]
[HAVING where_condition]
[ORDER BY{col_name | expr | position}[ASC|DESC],...]
[LIMIT{[offset,]row_count|row_count OFFSET offset}]
]

select 查询的两个影响
1、字段出现的前后顺序会影响结果集的顺序
2、字段的别名会影响结果集的名字。

每一个表达式表示想要的一列，必须有至少一个。
多个列直接以英文逗号分隔。
星号（*）表示所有列。tabl_name.*可以表示命名表的所有列
查询表达式可以使用[AS]alias_name为其赋予别名。
别名可用于GROUP BY， ORDER BY 或者HAVING子句。
例子：SELECT id,username FROM users;
表示查询users表的id,username 字段的记录，左边数据是id,右边是username
SELECT username,id FROM users;则是左边是username，右边是id
SELECT id as userid,username as uname FROM users;
则左边名字是userid实则是id ,右边名为uname实则是username；
若SELECT id username FROM users;
则显示名为username的别名，但是实则记录是ID的记录。相当于省略AS

where 条件表达式：

where 后各种根据条件(>、<、=、>=、<=、!=、<>、IS NOT NULL),
根据逻辑(and,or），
根据结合方式left join、right join等，
根据模式匹配(IN、NOT IN、like、not like、regexp)，使用各种MySQL函数和表达式，从表集合中筛选记录。

查询结果分组 GROUP BY

[GROUP BY {col_name|position} [ASC|DESC],...]
ASC：升序，默认
DESC：降序
position：SELECT语句中列的序号

eg. SELECT sex FROM users GROUP BY sex;对users中的sex按sex进行分组
eg. SELECT * FROM users GROUP BY 1;(这里的1表示查询的第一个字段，这里查询所有字段信息，第一个字段就是id，
所以会按照id字段进行分组）
 1表示SELECT语句中第一个出现的字段，即位置。
建议BY后写列名称，不写位置，因为位置还要人为数。

HAVING 分组条件

没有 HAVING 时，GROUP BY 是对全体记录进行分组并显示结果。
有 HAVING 时，对全体记录分组后只把符合HAVING条件的记录显示出来。
举例：SELECT username,age FROM users GROUP BY age HAVING count(id)>=2;
这句话的意思是：按照age分组后，把组员数量大于等于2的组显示出来

如果有HAVING作为分组条件,后面必须跟聚合函数(MAX最大值，MIN最小值，AVG平均值，SUM求和，count（）计数...只有一个返回值), 
或者保证后面跟的字段出现在这条SELECT语句当中

eg. SELECT sex,age FROM users GROUP BY sex HAVING age>35;
eg. SELECT sex FROM users GROUP BY 1 HAVING count(id)>=2;

ORDER BY：对查询结果进行排序

[ORDER BY {col_name | expr | position} [ASC|DESC],...]
按照多个字段进行排列的意思是如果按照第一个字段排序不能完全排列成功时（例如id相同），就按照下一个字段进行排序。

1、对查询结果进行排序，默认是升序ASC，DESC为降序：
例如：select * from users order by id desc;
2、可以同时按多条字段进行排序，规则是先按前面的字段排，在基础上再按后面字段排。
如：SELECT * FROM users ORDER BY age,id DESC; 先按照age进行默认【ASC】排序，如果age有重复的，重复的字段里按id进行【DESC】排序

LIMIT：限制查询结果的数量

[LIMIT {[offset,] row_count|row_count OFFSET offset}]
SELECT语句从0开始编号
offset：偏移量
row_count：返回结果的数目

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

第三种insert，数据从一个表插入到另一个表 将查询结果写入到另一个数据表中：
INSERT table_name(column_name) SELECT ...
eg:
INSERT test SELECT username FROM users WHERE age >=30; // 字段不匹配提示
INSERT test（username） SELECT username FROM users WHERE age >=30;
{
对比分组:
SELECT sex, age FROM users GROUP BY 1 HAVING age > 35;
分组用的是HAVING要求HAVING后的条件要么是聚合函数，要么字段在前面出现;
而插入用的是WHERE 没有以上提到的两个限制
}

知识点小结

记录操作：增，改，删，查
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}] 限制返回数量
]

子查询与连接

关于插入数据中文乱码问题

建表时，应当加上CREATE TABLE IF NOT EXISTS $tableName($paramArray) default charset=utf8选项；
插入数据前，应当先设置编码:SET NAMES utf8|gbk|gb2312;
然后插入数据：INSERT $tableName($param2insertArray) VALUES($valueArray);

显示乱码解决
1. 在插入记录之前，通过SET NAMES gbk;设置客户端的编码格式，即敲入的是什么编码，客户端会自动将插入命令转换成utf8编码
2. 当然在查询记录之前，也需要通过SET NAMES gbk;设置客户端数据显示的编码，否则会出现乱码
3. SET NAMES gbk;只会影响客户端显示数据的编码，对源数据不会有任何影响

子查询

子查询是指出现在【其他SQL语句内】的SELECT子句

eg：
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
其中，SELECT * FROM t1 ...称为Outer Query[外查询](或者Outer Statement)
SELECT column1 FROM t2 称为Sub Query[子查询]

子查询指嵌套在【查询内部】，且必须始终出现在【圆括号内】。
子查询可以包含多个关键字或者条件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等
子查询的外层查询可以是：【SELECT,INSERT,UPDATE,SET或DO】
子查询可以返回值：标量、一行、一列或者子查询

单独分析筛选时：
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='超级本');

子查询---in not in

in 相当于=any
not in 相当于 ！=all 或者<>all ——不等于、不包含
子查询----exists not exists ——用得比较少
子查询返回任何行 exists 返回 true 反之 返回 false
-- = ANY 或 = SOME 等价于 IN

SELECT goods_id,goods_name,goods_price FROM tdb_goods
WHERE goods_price IN (SELECT goods_price FROM tdb_goods WHERE goods_cate = '超级本')


INSERT ... SET ...可以使用子查询
INSERT [INTO] tbl_name [(col_name,...)] SELECT ...将查询结果写入数据表
Eg：
INSERT INTO table_name [(column_name)] SELECT column_name2 FROM table_name2 GROUP BY column_name3;

子查询与连接——INSERT-SET子查询

1、INSERT tbl_name [(col_name,...)] （SELECT col2_name FROM tbl2 ...）
2、同样注意字段数量和类型匹配

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


SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; //列出所有品牌种类cate//


DESC tdb_goods_cates; //显示出tdb_goods_cates表中的项目名称//
ps:desc tablename和show columns from tablename有啥区别？
desc 表示 description 也就是 对表的描述，这个命令会用一个表格描述一个数据表，而你的数据表的特征就是数据表的列名，所以用来描述的表格里面也会显示列名。
show columns from table 就是一段最简单的直译命令，就是说显示表的所有列名，它们代表的含义是完全不同的，但是显示结果是相同的。

INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
//在表tdb_goods_cates中插入tdb_goods中的cate种类//

多表更新：

UPDATE table_references SET col_name1={expr1 | DEFAULT} [,col_name2={expr2 | DEFAULT}]... [WHERE where_condition]
INNER JOIN,内连接
在MySQL中，JOIN, CROSS JOIN 和 INNER JOIN 是等价的。
LEFT [OUTER] JOIN ,左外连接
RIGHT [OUTER] JOIN,右外连接
update tdb_goods inner join tdb_goods_cates on goods_cate=cate_name set goods_cate=cate_id;

tdb_goods：想要更改的表名
inner join： 内连接
tdb_goods_cates： 关联的附表
goods_cate=cate_name 两个表对应列的关系

goods_cate=cate_id; 设置 值

多表更新之一步到位

建表、查询、写入三合一：
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;

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

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子句中，这样的子查询必须为其赋予别名。

1、内连接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等价的
2、外连接：LEFT JOIN左外连接；RIGHT JOIN右外连接
3、连接条件：使用ON设定连接条件，也可以用WHERE代替
· ON：设定连接条件
· WHERE：进行结果集记录的过滤
4：内连接是返回左表及右表符合连接条件的记录
5、例如：SELECT * FROM tabA JOIN tabB ON tabA.name = tabB.name;表示返回都含有的name值对应的字段
内连接 INNER JOIN ... ON....；
：SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cate ON tdb_goods.cate_id = tdb_goods_cate.cate_id;

OUTER JOIN
1、LEFT JOIN：显示左表全部和左右符合连接条件的记录
2、RIGHT JOIN：显示左右符合连接条件的记录和右表全部记录
3、若某字段只存在某一表，则另一表的里字段返回null

多表连接

SELECT col_name1,col_name2,col_name3,col_name4,col_name5 FROM tbl_name1 AS t1 
INNER JOIN tbl_name2 AS t2 ON join_condition
INNER JOIN tbl_name3 AS t3 ON join_condition\G;

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;

显示的结果和商品表tdb_goods的记录一样，不过之前是通过单表的查询来显示，这次是通过三张表的连接来显示。
多表的连接实际上是外键的逆向约束。外键把数据分开存储，多表连接又把数据联系在一起。

关于连接到几点说明

一.外连接：

以左外连接为例：
A LEFT JOIN B join_condition

1.数据表B的结果集依赖于数据表A :数据表A中的记录在B表中显示出来，否则B表中的记录不能显示

2.数据表A的结果集根据左连接条件依赖所有数据表(B表除外)

3.左外连接条件决定如何检索数据表B(在没有指定WHERE条件的情况下)

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

二.外连接冲突：

使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试一下操作：column_name IS NULL 。
如果 column_name 被指定为 NOT NULL，MySQL将在找到符合连接着条件的记录后停止搜索更多的行（查找冲突）

实际开发中，会对一个分类继续进行分类，这时候就需要无限分类表，
至少包含以下内容

CREATE TABLE tbl_name_type(
type_id SMALLINT UNSIGNER PRIMARY KEY AUTO_INCREMENT, //分类的编号
type_name VARCHAR(20) NOT NULL, //分类的名称
parent_type_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 //分类的父分类的编号
);
该表的查找通过自身连接来实现。
SELECT t1.type_name AS parent_name,t2.type_name AS child_name
FROM table_name AS t1 RIGHT JOIN table_name AS t2 ON t1.id=t2.parent_id;
要显示子类的所有内容，不含有父类时显示NULL，因此要朝向子类进行连接。

二.多表删除：

（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;