操作數(shù)據(jù)表中的記錄

插入操作

INSERT第一種
INSERT [INTO] tb_name [col_name,....] VALUES(...,...)[,(...,...)，...] 多行多個相對插入
expr--算數(shù)表達式or函數(shù)表達式；
default--創(chuàng)建表結(jié)構(gòu)定義的值；
如果表結(jié)構(gòu)中有主建primary key auto_increment，插入時對應值可以用NULL或default;

INSERT第二種（與第一種方式的區(qū)別在于，此方式可以使用子查詢(SubQuery)）
INSERT [INTO] tb_name SET col_name={expr|DEFAULT},col_name2={expr|DEFAULT},....單行多個插入

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

SELECT--查找的結(jié)果

單表更新

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

當缺少WHERE時，所有記錄都會更新。

LOW_PRIORITY：UPDATE的執(zhí)行被延遲了，直到?jīng)]有其它的客戶端從表中讀取為止。
IGNORE：即使在更新過程中出現(xiàn)錯誤，更新語句也不會中斷。

舉例：UPDATE users SET age=age+1; //整張表發(fā)生改變
UPDATE users SET age=age+id,sex=0;  //多個字段用逗號隔開。
UPDATE users SET sex=1 WHERE id%2=0;  //id為偶數(shù)的sex值設為1。求余為0即偶數(shù)

單表刪除

DELETE FROM tbl_name [WHERE where_condition]
若不添加WHERE則刪除全部記錄
刪除某條記錄后，再插入一條新的記錄，自動編號不會補到刪除記錄的編號上，而是基于原有記錄最大編號繼續(xù)增加

查詢記錄

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、字段出現(xiàn)的前后順序會影響結(jié)果集的順序
2、字段的別名會影響結(jié)果集的名字。

每一個表達式表示想要的一列，必須有至少一個。
多個列直接以英文逗號分隔。
星號（*）表示所有列。tabl_name.*可以表示命名表的所有列
查詢表達式可以使用[AS]alias_name為其賦予別名。
別名可用于GROUP BY， ORDER BY 或者HAVING子句。
例子：SELECT id,username FROM users;
表示查詢users表的id,username 字段的記錄，左邊數(shù)據(jù)是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 后各種根據(jù)條件(>、<、=、>=、<=、!=、<>、IS NOT NULL),
根據(jù)邏輯(and,or），
根據(jù)結(jié)合方式left join、right join等，
根據(jù)模式匹配(IN、NOT IN、like、not like、regexp)，使用各種MySQL函數(shù)和表達式，從表集合中篩選記錄。

查詢結(jié)果分組 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語句中第一個出現(xiàn)的字段，即位置。
建議BY后寫列名稱，不寫位置，因為位置還要人為數(shù)。

HAVING 分組條件

沒有 HAVING 時，GROUP BY 是對全體記錄進行分組并顯示結(jié)果。
有 HAVING 時，對全體記錄分組后只把符合HAVING條件的記錄顯示出來。
舉例：SELECT username,age FROM users GROUP BY age HAVING count(id)>=2;
這句話的意思是：按照age分組后，把組員數(shù)量大于等于2的組顯示出來

如果有HAVING作為分組條件,后面必須跟聚合函數(shù)(MAX最大值，MIN最小值，AVG平均值，SUM求和，count（）計數(shù)...只有一個返回值), 
或者保證后面跟的字段出現(xiàn)在這條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：對查詢結(jié)果進行排序

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

1、對查詢結(jié)果進行排序，默認是升序ASC，DESC為降序：
例如：select * from users order by id desc;
2、可以同時按多條字段進行排序，規(guī)則是先按前面的字段排，在基礎上再按后面字段排。
如：SELECT * FROM users ORDER BY age,id DESC; 先按照age進行默認【ASC】排序，如果age有重復的，重復的字段里按id進行【DESC】排序

LIMIT：限制查詢結(jié)果的數(shù)量

[LIMIT {[offset,] row_count|row_count OFFSET offset}]
SELECT語句從0開始編號
offset：偏移量
row_count：返回結(jié)果的數(shù)目

eg. SELECT * FROM users LIMIT 2; // 從第一條開始（第一個為0），返回兩條
    SELECT * FROM users LIMIT 2,3 ;//從第三條開始（第一個為0），返回三條

第三種insert，數(shù)據(jù)從一個表插入到另一個表 將查詢結(jié)果寫入到另一個數(shù)據(jù)表中：
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后的條件要么是聚合函數(shù)，要么字段在前面出現(xiàn);
而插入用的是WHERE 沒有以上提到的兩個限制
}

知識點小結(jié)

記錄操作：增，改，刪，查
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://更新數(shù)據(jù)
單表更新
UPDATE [LOW_PRIORITY][IGNORE] table_reference SET col_name1={expr1|DEFAULT},[col_name2={expr2|DEFAULT}]...[WHERE where_condition]
多表更新

DELETE : //刪除數(shù)據(jù)
單表刪除
DELETE FROM tbl_name [WHERE where_condition}
多表刪除

SELECT: //查詢
SELECT select_expr [,select expr2...] 只查找某一個函數(shù)或表達式
[
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}] 限制返回數(shù)量
]

子查詢與連接

關(guān)于插入數(shù)據(jù)中文亂碼問題

建表時，應當加上CREATE TABLE IF NOT EXISTS $tableName($paramArray) default charset=utf8選項；
插入數(shù)據(jù)前，應當先設置編碼:SET NAMES utf8|gbk|gb2312;
然后插入數(shù)據(jù)：INSERT $tableName($param2insertArray) VALUES($valueArray);

顯示亂碼解決
1. 在插入記錄之前，通過SET NAMES gbk;設置客戶端的編碼格式，即敲入的是什么編碼，客戶端會自動將插入命令轉(zhuǎn)換成utf8編碼
2. 當然在查詢記錄之前，也需要通過SET NAMES gbk;設置客戶端數(shù)據(jù)顯示的編碼，否則會出現(xiàn)亂碼
3. SET NAMES gbk;只會影響客戶端顯示數(shù)據(jù)的編碼，對源數(shù)據(jù)不會有任何影響

子查詢

子查詢是指出現(xiàn)在【其他SQL語句內(nèi)】的SELECT子句

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

子查詢指嵌套在【查詢內(nèi)部】，且必須始終出現(xiàn)在【圓括號內(nèi)】。
子查詢可以包含多個關(guān)鍵字或者條件，如DISTINCT,GROUP BY,ORDER BY,LIMIT,函數(shù)等
子查詢的外層查詢可以是：【SELECT,INSERT,UPDATE,SET或DO】
子查詢可以返回值：標量、一行、一列或者子查詢

單獨分析篩選時：
SELECT AVG(goods_price) FROM tdb_goods;              //AVG函數(shù)代表求其平均值//
SELECT ROUND(AVG(goods_price),2) FROM tdb_goods;     //round(@,#)代表輸出格式為@數(shù)小數(shù)點后#位輸出//
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;  //檢索結(jié)果非唯一//


對于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 ...將查詢結(jié)果寫入數(shù)據(jù)表
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、同樣注意字段數(shù)量和類型匹配

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有啥區(qū)別？
desc 表示 description 也就是 對表的描述，這個命令會用一個表格描述一個數(shù)據(jù)表，而你的數(shù)據(jù)表的特征就是數(shù)據(jù)表的列名，所以用來描述的表格里面也會顯示列名。
show columns from table 就是一段最簡單的直譯命令，就是說顯示表的所有列名，它們代表的含義是完全不同的，但是顯示結(jié)果是相同的。

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,內(nèi)連接
在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： 內(nèi)連接
tdb_goods_cates： 關(guān)聯(lián)的附表
goods_cate=cate_name 兩個表對應列的關(guān)系

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語句修改數(shù)據(jù)表結(jié)構(gòu)
ALTER TABLE tdb_goods 
CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

PS：
外鍵，不一定是物理的外鍵，邏輯的外鍵也行，當然，物理外鍵更能保證數(shù)據(jù)的完整性和一致性。
數(shù)字類型的字段占用的空間更小，查詢的效率也更高。

1.連接

MySQL在SELECT語句、多表更新、多表刪除語句中支持JOIN操作。
語法結(jié)構(gòu)
table reference A
{[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN}
table_reference B
ON condition_expr

2.數(shù)據(jù)表參照

table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
數(shù)據(jù)表可以使用tbl_name AS alias_name 或 tbl_name alias_name賦予別名。
table_subquery可以作為子查詢使用在FROM子句中，這樣的子查詢必須為其賦予別名。

1、內(nèi)連接：在MySQL中JOIN,INNER JOIN,CROSS JOIN是等價的
2、外連接：LEFT JOIN左外連接；RIGHT JOIN右外連接
3、連接條件：使用ON設定連接條件，也可以用WHERE代替
· ON：設定連接條件
· WHERE：進行結(jié)果集記錄的過濾
4：內(nèi)連接是返回左表及右表符合連接條件的記錄
5、例如：SELECT * FROM tabA JOIN tabB ON tabA.name = tabB.name;表示返回都含有的name值對應的字段
內(nèi)連接 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;

顯示的結(jié)果和商品表tdb_goods的記錄一樣，不過之前是通過單表的查詢來顯示，這次是通過三張表的連接來顯示。
多表的連接實際上是外鍵的逆向約束。外鍵把數(shù)據(jù)分開存儲，多表連接又把數(shù)據(jù)聯(lián)系在一起。

關(guān)于連接到幾點說明

一.外連接：

以左外連接為例：
A LEFT JOIN B join_condition

1.數(shù)據(jù)表B的結(jié)果集依賴于數(shù)據(jù)表A :數(shù)據(jù)表A中的記錄在B表中顯示出來，否則B表中的記錄不能顯示

2.數(shù)據(jù)表A的結(jié)果集根據(jù)左連接條件依賴所有數(shù)據(jù)表(B表除外)

3.左外連接條件決定如何檢索數(shù)據(jù)表B(在沒有指定WHERE條件的情況下)

4.如果數(shù)據(jù)表A的某條記錄符合WHERE條件，但是在數(shù)據(jù)表B不存在符合連接條件的記錄，將生成一個所有列為空的額外的B行

二.外連接沖突：

使用內(nèi)連接查找的記錄在連接數(shù)據(jù)表中不存在，并且在WHERE子句中嘗試一下操作：column_name IS NULL 。
如果 column_name 被指定為 NOT NULL，MySQL將在找到符合連接著條件的記錄后停止搜索更多的行（查找沖突）

實際開發(fā)中，會對一個分類繼續(xù)進行分類，這時候就需要無限分類表，
至少包含以下內(nèi)容

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 //分類的父分類的編號
);
該表的查找通過自身連接來實現(xiàn)。
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;
要顯示子類的所有內(nèi)容，不含有父類時顯示NULL，因此要朝向子類進行連接。

二.多表刪除：

（1）INSERT ... SELECT實現(xiàn)復制
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;