1.查询去重：SELECT 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;(支持先按价格排序，然后同一价格的按名字排序)

排序默认是升序，降序需指定 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必须在每个想倒序的关键字后面才生效)

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优先级：AND优先级更高，所以当需要找出符合三个条件的时候这个语句会有误差：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对应 BETWEEN)

11.通配符%：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name LIKE "jet%";(找出所有以 jet 开头的结果)

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

SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_name LIKE "j%e";(找出所有 j 开头 e 结尾的结果)

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

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

13.正则表达式：SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '.000';(表示以000结尾的，类似 like，前面的.表示一个字符)

区分大小写：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 必知必会》

匹配多个实例：①SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '\([0-9] sticks?\)';(?表示前面这个 s 出现0/1次)

②SELECT vend_id,vend_name,vend_price FROM table1 WHERE vend_id REGEXP '[[:digit:]]{4}';([:digit:]匹配任意数字，{4}要求前面的任意字符出现4次）表示连续4个数字的所有结果。也可以这么写：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\.]';(表示以小数开头的结果)

^{文本的开始(在集合中表示相反的[}vvv]) $文本的结尾 [[:<:]]词的开始 [[:>:]] 词的结尾

测试正则： 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; 给返回结果取别名vend_title

17.简单计算：select id,item_cat_id,id*item_cat_id AS sum from tb_item_param;

18.函数：

①转为大写：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个参数）：select username, Locate('g',username,2) as uppername from tb_user order by username;

⑤ 转为小写：select username, Lower(username) as uppername from tb_user order by username;

⑥计算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");(牛批...根据发音找出相似的全部)

⑦截取子串（3表示长度，可以只写2个参数）：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月所有数据

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()当前日期，当前时间

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

日期

19.数值处理：

数值处理

20.数据汇总处理（聚集函数）：
①求平均值：select order_id,avg(order_id) as fiii from tb_order_item where vend_id=1003;
②计数：select count(order_id) as count from tb_order_item;（order_id 列计数，忽略 null）；
select count(*) as count from tb_order_item;（整张表行数计数）
③求最大值/最小值：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;（可以在分组基础上做统计汇总）
③过滤分组：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.语句关键字顺序：
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.联结表：
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;（结果和👇语句一样）
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表行数之积)
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;（多表联结）
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;(同表联结必须指定别名)

25.外部联结：
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;（会有个排序值）

查询扩展：select receiver_address from test_tb_order_shipping where Match(receiver_address) AGAINST ("西二旗" WITH QUERY EXPANSION); 可以搜到相关的结果

布尔搜索：select receiver_address from test_tb_order_shipping where Match(receiver_address) AGAINST ("西二旗 -西三*" in boolean mode);（搜索含西二旗且排除掉所有以西三开头的结果）

截图 (2).png

28.插入数据：

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');

从一个表复制数据到另一个表：INSERT INTO tb_user_new (id, username, password, phone, email, created, updated)

select id, username, password, phone, email, created, updated from tb_user;（字段是对应的位置）

29.更新：

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

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

30.删除：

删除整表数据：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 ;（创建存储）

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);(调用存储)

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

创建存储例子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(查看是谁创建的存储过程)

show create PROCEDURE ordertotalnew;(查看创建语句)

34.事务：

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;

创建用户账号：create user ben identified by “password“;

重命名账号：rename user ben to amily;

删除用户：drop user amily;

设置访问权限：show grants for amily;

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

SHOW PROCESSLIST（显示所有活动进程）

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 ****