一.概述

首先这是之前一版极其琐碎的blog的链接.然后这篇博客不会涉及存储过程触发器等内容(后续会补充),仅仅按照增删改查这四个点来做基本的介绍以及记录,毕竟工作中很大一部分需要的也是这些内容~

二.素材准备

为这次的记录建好database以及user.(表结构素材来自mysql必知必会)

create database notereview;

create user review@localhost identified by 'xxxxx';

grant all on notereview.* to review@localhost;

flush privileges;

三.查询

基础语句

查询子语句有一个基本的模板:

    FROM xxx WHERE xxx GROUP BY xxx HAVING xxxx ORDER BY xxx LIMIT xxxxx

WHERE和条件连接

这里的WHERE就是查询条件,简单的条件过滤比如 =,<>,!=,<,>,<=,>=,BETWEEN,IN,NOT等.

还有就是对于NULL的检查：IS NULL,IS NOT NULL

上面是使用单条子语句所可能用到的关键词,如果是多条子语句可以通过AND 或者 OR 连接.其中 AND 的优先级是高于 OR的.也就是说:

SELECT a,b,c FROM tableName WHERE conditionA OR conditionB AND conditionC;

相当于是查询同时满足条件B和C,或者满足条件A的数据.举个例子:

create table if not exists products(
    prod_id varcahr(10) not null auto_increment,
    vend_id int not null,
    prod_name varchar(255) not null,
    prod_price decimal(10,2) not null,
    prod_desc text not null,
    primary key(prod_id)
)engine = InnoDb default charset=utf8mb4 comment = '产品表';

SELECT prod_name,vend_id,prod_price FROM products  WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

其中Fuses的价格为3.42不满足大于等于10的要求但是满足了vend_id为1002的要求.

LIMIT 和 ORDER BY

然后是LIMIT以及ORDER BY,基础模板为:

    xxx ORDER BY xxxx [DESC][,xxxx [desc]] LIMIT index[,offset];

也就是说按照一定规则排序后,从index行开始(包含index),返回最多offset行数据,这里需要注意的是index是从0开始计算的,也就是说LIMIT 1,1其实返回的是第二行.

ORDER BY 默认是升序,如果是DESC则是降序,其中可以按照多个字段排序,实际结果是按照排序字段的出现的优先级来确定的,举个例子:

SELECT prod_name,vend_id,prod_price FROM products where prod_price >10 ORDER BY prod_price DESC,vend_id ASC;

这边的结果可以看出虽然 JetPack 1000 的vend_id 也是1005但是排序的第一条件是按照prod_price 降序,所以他不能处在第一位,又因为第二个条件是按照 vend_id 升序,所以他最终处在第三位.

SELECT prod_name,vend_id,prod_price FROM products where prod_price >10 ORDER BY prod_price DESC,vend_id ASC limit 2,10;

这里的结果对比上面的可以看出LIMIT 2,10 确实是从第三条开始返回的,如果数据不够则会返回他所能返回的最多的数据.

GROUP BY 和 HAVING

按照上面的顺序,接下来是分组查询GROUP BY 和 HAVING.这两者一般配合使用.HAVING 可以当成是在使用GROUP BY时的WHERE,用来过滤分组后的数据.其中SELECT 后接的表字段(聚合函数除外)必须出现在GROUP BY后.举个例子,查询products表中能够提供多种产品的商户(即产品多余一种)，列出商户id以及所能提供的产品数量并按照产品种类数降序排序列出.

SELECT vend_id,count(*) AS prods from products GROUP BY vend_id HAVING prods > 1 ORDER BY prods DESC;

结果如下:

这里列出几个常用的聚合函数:

like 和正则

这边 like和正则简单提下,因为效率问题推荐放在搜索条件的最后面

WHERE xxxx  like 'xxx'

SELECT  prod_id FROM products WHERE prod_id LIKE 'JP%';

%表示这里可以出现0,1或多个字符.这条语句相当于查询JP开头的数据(注意NULL不会匹配)

同样也可以通过正则来达到一样的效果:

SELECT  prod_id FROM products WHERE prod_id REGEXP 'JP*';

# 这个则不行，因为binary相当于是大小写敏感匹配
SELECT  prod_id FROM products WHERE prod_id REGEXP BINARY 'jp*';

联结查询

等值联结/内部联结

这个其实就是我们常用的 INNER JOIN或者多表条件查询

#等值联结 
SELECT a.x,b.x FROM a,b WHERE a.xxx = b.xxx;
#内联结
SELECT a.x,b.x FROM a INNER JOIN b ON a.xxx = b.xxx;

这里相当于是那表a的每一行和表b的每一行去做匹配并留下符合条件的数据

这里有个特殊的比如自联结:

SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = 'DTNTR';

这里使用了别名是因为是表products和自己联结防止指代不情.这句sql的结果是挑选DTNTR的商户所能生产的所有商品

结果如下:

另外的LEFT JOIN,RIGHT JOIN就是所谓的左联结右联结.什么联结就是以那边的数据为准,那边的数据最全,如果右边没有匹配的数据则以NULL的形式展示.

组合查询

UNION:其实是相当于将多条SELECT的结果组合。默认是去除重复行的,UNION ALL 则是不去重哒

# 结果见表1
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5;

#结果见表2
SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);

#结果见表3
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);

#结果见表4
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION  ALL SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN(1001,1002);

表1:

表2:

表3:

表4:

这里因为是将结果合并,所以查询字段必须一致~

四.插入

这个则是insert语句

 INSERT INTO targetName(columnName[,columnName]…) values(columnValue[,columnValue])[,(…)];

这里再提下迁移数据到新表:

INSERT INTO newTable SELECT * FROM oldTable;

#如果结构不一致的话

INSERT INTO 新表(字段1,字段2,.......) SELECT 字段1,字段2,...... FROM 旧表;

五.删除

DELETE FROM tableName WHERE xxx = xxx;

这里记得注意WHERE条件~
然后 TRUNCATE TABLE tableName 不过这个需要注意的是他不会激活触发器,并且不会涉及事务,所以效率很高但是注意备份数据,毕竟不能回滚.

六.更新

UPDATE targetName SET columnName = xxx [,columnName2 = xxxx] WHERE xxx = xxxx;
UPDATE products SET prod_price = 35.00 WHERE prod_id ='SAFE';

同样不要忘记WHERE 条件哈~

七.几个练习(from leetcode-cn)

交换工资(?性别)

给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。

# 这里使用了 CASE WHEN
UPDATE salary
SET
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;


# if的话
UPDATE salary
SET 
 sex = IF(sex='m','f','m');

# 这里就不涉及代码思路解决了比如字符串变换

超过经理收入的员工

Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

SELECT  a.Name AS Employee   FROM Employee AS a,Employee AS b where a.ManagerId = b.Id AND a.Salary > b.Salary;

不订购任何东西的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

Orders 表：

SELECT Name AS Customers from Customers  where Id NOT IN (select CustomerId from Orders);