1.查詢?nèi)ブ兀篠ELECT DISTINCT vend_id FROM table1;

2.指定查詢范圍：SELECT vend_id FROM table1 LIMIT 5; SELECT vend_id FROM table1 LIMIT 5,5;(從第五行開始查詢5行)

3.排序：SELECT vend_id FROM table1 ORDER BY vend_id; SELECT vend_id,vend_name,vend_price FROM table1 ORDER BY vend_price,vend_name;(支持先按價格排序，然后同一價格的按名字排序)

排序默認(rèn)是升序，降序需指定 DESC:SELECT vend_id FROM table1 ORDER BY vend_id DESC;SELECT vend_id,vend_name,vend_price FROM table1 ORDER BY vend_price DESC,vend_name DESC;(DESC必須在每個想倒序的關(guān)鍵字后面才生效)

4.排序和 limit 可以找出最大或最小的值：SELECT vend_id FROM table1 ORDER BY vend_id DESC LIMIT 1;

5.不匹配查詢：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id <>1002;(找出所有不是1002的行)

6.指定范圍查詢：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id BETWEEN 5 AND 10;

7.null 值查詢：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id IS NULL;(=NULL 不管用),同時像5中不匹配查詢時返回值里不包含為 null 的行

8.查詢條件OR：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id = 1002 OR vend_id = 1003;

SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id IN (1002,1003);

9.AND和 OR優(yōu)先級：AND優(yōu)先級更高，所以當(dāng)需要找出符合三個條件的時候這個語句會有誤差：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id = 1002 OR vend_id = 1003 AND vend_price >5;正確的寫法：SELECT vend_id,vend_name,vend_price FROM table1 WHERE (vend_id = 1002 OR vend_id = 1003) AND vend_price >5;

10.取反條件查詢：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id NOT IN (1002,1003);(EXISTS對應(yīng) BETWEEN)

11.通配符%：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name LIKE "jet%";(找出所有以 jet 開頭的結(jié)果)

SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name LIKE "%jet%";(找出所有包含 jet 的結(jié)果)

SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name LIKE "j%e";(找出所有 j 開頭 e 結(jié)尾的結(jié)果)

ps：注意首尾空格的情況，有時候匹配不上，需要在后面加%；以及不能 成功匹配到 null

12.通配符_:SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name LIKE "_jet";(下劃線只能匹配一個字符)

13.正則表達(dá)式：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '.000';(表示以000結(jié)尾的，類似 like，前面的.表示一個字符)

區(qū)分大小寫：SELECT vend_id,vend_name,vend_price FROM table1 WHERE prod_name REGEXP BINARY 'JetPack .000';

OR正則匹配：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '1000|2000';

匹配幾個字符之一：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name REGEXP '[123] ton';(會正則匹配1 ton 或2 ton 或3 ton) ps：①[^123]匹配除1，2，3之外的任何；② [0123456789] = [0-9] ；[a-z]同理 ；

正則匹配特殊符查找：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '//.';(//表示查找) . | [] / 都一樣

匹配字符類：參見69頁《MySQL 必知必會》

匹配多個實(shí)例：①SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '\([0-9] sticks?\)';(?表示前面這個 s 出現(xiàn)0/1次)

②SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '[[:digit:]]{4}';([:digit:]匹配任意數(shù)字，{4}要求前面的任意字符出現(xiàn)4次）表示連續(xù)4個數(shù)字的所有結(jié)果。也可以這么寫：WHERE vend_id REGEXP '[0-9][0-9][0-9][0-9]';

定位符：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '^[0-9\.]';(表示以小數(shù)開頭的結(jié)果)

^{文本的開始(在集合中表示相反的[}vvv]) $文本的結(jié)尾 [[:<:]]詞的開始 [[:>:]] 詞的結(jié)尾

測試正則： select 'hello' REGEXP '[0-9]'; 返回0 0--不匹配；1--匹配

14.拼接列值返回：SELECT Concat(vend_name,'(',vend_country,')') FROM table1 ORDER BY vend_price; 返回 vend_name(vend_country)格式

15.去掉字符串兩邊的空格：Trim() SELECT Concat(RTrim(vend_name),'(',vend_country,')') FROM table1 ORDER BY vend_price;(去掉 name 右邊的空格) LTrim()去掉左邊的空格

16.取別名：SELECT Concat(vend_name,'(',vend_country,')') AS vend_title FROM table1 ORDER BY vend_price; 給返回結(jié)果取別名vend_title

17.簡單計(jì)算：select id,item_cat_id,id*item_cat_id AS sum from tb_item_param;

18.函數(shù)：

①轉(zhuǎn)為大寫：select username, Upper(username) as uppername from tb_user order by username;

②從左（右）邊開始返回 n 個字符的子串：select username, Left(username,2) as uppername from tb_user order by username; Right()

③返回串的長度：select username, Length(username) as uppername from tb_user order by username;

④查看子串在字符串里的位置（2表示從第幾個開始，可以只寫2個參數(shù)）：select username, Locate('g',username,2) as uppername from tb_user order by username;

⑤ 轉(zhuǎn)為小寫：select username, Lower(username) as uppername from tb_user order by username;

⑥計(jì)算Soundex值：select username, Soundex(username) as uppername from tb_user order by username;

SELECT vend_id,vend_name,vend_price FROM table1 WHERE Soundex(vend_name)=Soundex("Y Lie");(牛批...根據(jù)發(fā)音找出相似的全部)

⑦截取子串（3表示長度，可以只寫2個參數(shù)）：select username, SubString(username,2,3) as uppername from tb_user order by username;

⑧日期類型：select * from tb_user where Date(created) = '2015-04-06'; 只匹配日期部分

select * from tb_user where TIME(created) = '17:03:55'; 只匹配時間部分

select * from tb_user where Year(created) = '2015' AND Month(created) = '8'; 2015年8月所有數(shù)據(jù)

select created,AddDate(created,3) as final from tb_user where Year(created) = '2015' AND Month(created) = '8'; 增加3天

select created,AddTime(created,'1 1:30:0.4444444') as final from tb_user where Year(created) = '2015' AND Month(created) = '8'; 增加時間

SELECT NOW(),CURDATE(),CURTIME()當(dāng)前日期，當(dāng)前時間

select created,DateDiff(created,'2015-05-04') as final from tb_user where Year(created) = '2015' AND Month(created) = '8'; 計(jì)算日期差

日期

19.數(shù)值處理：

數(shù)值處理

20.數(shù)據(jù)匯總處理（聚集函數(shù)）：
①求平均值：select order_id,avg(order_id) as fiii from tb_order_item where vend_id=1003;
②計(jì)數(shù)：select count(order_id) as count from tb_order_item;（order_id 列計(jì)數(shù)，忽略 null）；
select count(*) as count from tb_order_item;（整張表行數(shù)計(jì)數(shù)）
③求最大值/最小值：select max(order_id) as max from tb_order_item;（min）
④求和：select sum(order_id) as sum from tb_order_item;
⑤DISTINCT:用于去重之后再處理 select AVG(DISTINCT order_id) as sum from tb_order_item; (只能用于列)

21.分組：
①select order_id,count() as count from tb_order_item group by order_id;
GROUP BY 必須 在 WHERE 之后，ORDER BY之前
②select order_id,count() as count from tb_order_item group by order_id with rollup;（可以在分組基礎(chǔ)上做統(tǒng)計(jì)匯總）
③過濾分組：select order_id,count() as count from tb_order_item group by order_id having count()>1;
select order_id,count() as count from tb_order_item where order_id > 100546 group by order_id having count()>1;
select order_id,count() as count from tb_order_item where order_id > 100546 group by order_id having count()>=1 order by count desc;

22.語句關(guān)鍵字順序：
select -> from ->where -> group by -> having -> order by -> limit

23.子查詢：
select order_id,(select count(*) from tb_order_item where tb_order_item.order_id = tb_order.order_id) as count from tb_order order by count desc;

24.聯(lián)結(jié)表：
select item_id,title,payment from tb_order_item,tb_order where tb_order_item.order_id = tb_order.order_id order by item_id,payment desc;（結(jié)果和??語句一樣）
select item_id,title,payment from tb_order_item inner join tb_order on tb_order_item.order_id = tb_order.order_id;
select item_id,title,payment from tb_order_item,tb_order order by item_id,payment desc;(笛卡爾積,返回2表行數(shù)之積)
select title,payment,param_data from tb_order_item,tb_order,tb_item_param_item where tb_order_item.order_id = tb_order.order_id and tb_order_item.item_id = tb_item_param_item.item_id and price = 2000;（多表聯(lián)結(jié)）
select p1.item_id,p1.order_id from tb_order_item as p1,tb_order_item as p2 where p1.order_id = p2.order_id and p2.price = 2000;(同表聯(lián)結(jié)必須指定別名)

25.外部聯(lián)結(jié)：
select tb_order_item.item_id,tb_order.payment from tb_order_item RIGHT outer join tb_order on tb_order_item.order_id = tb_order.order_id; left 表示outer join 左邊的表選擇所有行，而 right 表示從右邊的表選擇所有行

26.組合查詢（union）：
select order_id,payment_type from tb_order where order_id in(100550,100549,100548) UNION select order_id,payment_type from tb_order where payment_type <2 order by order_id,payment_type;
select order_id,payment_type from tb_order where order_id in(100550,100549,100548) UNION ALL select order_id,payment_type from tb_order where payment_type <2;（不去重）

27.全文本搜索：
select receiver_address from test_tb_order_shipping where Match(receiver_address) AGAINST ("西二旗");（必須指定了文本搜索： FULLTEXT KEY receiver_address (receiver_address)）

select receiver_address,Match(receiver_address) AGAINST ("西二旗") as rank from test_tb_order_shipping;（會有個排序值）

查詢擴(kuò)展：select receiver_address from test_tb_order_shipping where Match(receiver_address) AGAINST ("西二旗" WITH QUERY EXPANSION); 可以搜到相關(guān)的結(jié)果

布爾搜索：select receiver_address from test_tb_order_shipping where Match(receiver_address) AGAINST ("西二旗 -西三*" in boolean mode);（搜索含西二旗且排除掉所有以西三開頭的結(jié)果）

截圖 (2).png

28.插入數(shù)據(jù)：

INSERT INTO tb_order_item (id, item_id, order_id, num, title, price, total_fee, pic_path)

VALUES("11", X'313531373439323930353039383639', X'313030353438', 10, X'E59487E8868F', 1900, 9500, X'687474703A2F2F3132372E302E302E312F66747066696C652F323031382F30322F30312F313531373439323530353239393134352E706E67'),("12", X'313531373439323930353039383639', X'313030353439', 11, X'E59487E8868F', 1900, 9500, X'687474703A2F2F3132372E302E302E312F66747066696C652F323031382F30322F30312F313531373439323530353239393134352E706E67');

從一個表復(fù)制數(shù)據(jù)到另一個表：INSERT INTO tb_user_new (id, username, password, phone, email, created, updated)

select id, username, password, phone, email, created, updated from tb_user;（字段是對應(yīng)的位置）

29.更新：

update tb_user_new set username = "wenny",phone = "177674444444" where id = 7;

刪除指定的列：update tb_user_new set username = null where id = 7;

30.刪除：

刪除整表數(shù)據(jù)：truncate tb_user_new（原理是先刪掉表，再重建表）

31.操作表：

select LAST_INSERT_ID() as id （找出某個表主鍵的最后一個值）

alter table tb_user add column test char(20)(加列字段)

alter table tb_user drop column test（刪除某個字段）

alter table tb_order_item1 add constraint fk_tb_order_item1_tb_order foreign key (order_id) references tb_order (order_id);(增加外鍵)

rename table tb_order_item1 to test;(重命名)

32.視圖：

create view testing as select order_id,payment,payment_type from tb_order where payment_type=2;

select * from testing where payment = 95

33.存儲過程：

DELIMITER //

create procedure paymenting()

begin

select Avg(payment) as paymentaverage from tb_order;

end //

DELIMITER ;（創(chuàng)建存儲）

call paymenting()(使用存儲)

DROP PROCEDURE IF EXISTS paymenting;(刪除存儲)

DELIMITER //

create procedure paymenting(

OUT p1 decimal(8,2),

OUT ph decimal(8,2),

OUT pa decimal(8,2)

)

begin

select min(payment) into p1 from tb_order;

select max(payment) into ph from tb_order;

select Avg(payment) into pa from tb_order;

end //

DELIMITER ;（修改存儲）

call paymenting(@p1,

@ph,

@pa);(調(diào)用存儲)

select @p1,@ph,@pa;(查詢)

創(chuàng)建存儲例子2：DELIMITER //

create procedure ordertotal(

in onumber int,

out ototal decimal(8,2))

begin

select sum(payment*payment_type) from tb_order where payment_type = onumber into ototal;

end; //

DELIMITER

使用存儲例子2：CALL ordertotalnew (2,@total);select @total;

show PROCEDURE status(查看是誰創(chuàng)建的存儲過程)

show create PROCEDURE ordertotalnew;(查看創(chuàng)建語句)

34.事務(wù)：

select * from table1;

start transaction;

delete from table1;

select * from table1;

rollback;

select * from table1;(回滾)

select * from table1;

start transaction;

delete from table1 where id =1;

delete from table1 where id =2;

commit;(提交)

35.用戶管理：

查看用戶列表：use xiaoben;select user from user;

創(chuàng)建用戶賬號：create user ben identified by “password“;

重命名賬號：rename user ben to amily;

刪除用戶：drop user amily;

設(shè)置訪問權(quán)限：show grants for amily;

更改密碼：SET PASSWORD for ben =Password("新密碼");

SHOW PROCESSLIST（顯示所有活動進(jìn)程）

explain select * from tb_user where id = 7;

索引：alter table external_spot_relation add unique index(name,spot_id);

建表之前可以加一句：DROP TABLE IF EXISTS wechat_micro_operation_record;create ****