sql高级语法的应用

-- auto-generated definition
create table chouti_book
(
    id         integer     not null
        primary key autoincrement,
    title      varchar(64) not null,
    price      integer     not null,
    color      varchar(64) not null,
    page_num   integer,
    publish_id integer     not null
        references chouti_publish
            deferrable initially deferred
);

create index chouti_book_publish_id_31925803
    on chouti_book (publish_id);


---- SELECT ----显示表格中一个或数个字段的所有数据记录
语法：SELECT "字段" FROM "表名";
mysql> SELECT Store_Name FROM store_info;


---- DISTINCT ----不显示重复的数据记录
语法：SELECT DISTINCT "字段" FROM "表名";
mysql> select distinct Store_Name from store_info;


---- WHERE ----有条件查询
语法：SELECT "字段" FROM "表名" WHERE "条件";
mysql> SELECT Store_Name FROM store_info WHERE Sales > 1000;



---- AND OR ----且 或
语法：SELECT "字段" FROM "表名" WHERE "条件1" {[AND|OR] "条件2"}+ ;
mysql> select store_name from store_info where Sales > 1000 OR (Sales < 500 AND Sales > 200);

---- IN ----显示已知的值的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" IN ('值1', '值2', ...);

---- BETWEEN ----显示两个值范围内的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" BETWEEN '值1' AND '值2';
mysql> select * from store_info where date between '2020-12-06' AND '2020-12-10';

---- LIKE ----匹配一个模式来找出我们要的数据记录
语法：SELECT "字段" FROM "表名" WHERE "字段" LIKE {模式};
mysql> SELECT * FROM store_info WHERE Store_Name like '%os%';

---- ORDER BY ----按关键字排序
语法：SELECT "字段" FROM "表名" [WHERE "条件"] ORDER BY "字段" [ASC, DESC];
#ASC 是按照升序进行排序的，是默认的排序方式。
#DESC 是按降序方式进行排序
mysql> select store_name,sales,date from store_info order by sales DESC;


聚合函数
sum
max
min
count
avg

---- GROUP BY ----
对GROUP BY后面的字段的查询结果进行汇总分组，通常是结合聚合函数一起使用的
GROUP BY 有一个原则，凡是在 GROUP BY 后面出现的字段，必须在 SELECT 后面出现；
凡是在 SELECT 后面出现的、且未在聚合函数中出现的字段，必须出现在 GROUP BY 后面
语法：SELECT "字段1", SUM("字段2") FROM "表名" GROUP BY "字段1";

---- HAVING ----
用来过滤由 GROUP BY 语句返回的记录集，通常与 GROUP BY 语句联合使用
HAVING 语句的存在弥补了 WHERE 关键字不能与聚合函数联合使用的不足。
语法：SELECT "字段1", SUM("字段2") FROM "表格名" GROUP BY "字段1" HAVING (函数条件);
SELECT Store_Name, SUM(Sales) FROM Store_Info GROUP BY Store_Name HAVING SUM(Sales) > 1500;


---- 子查询 ----
连接表格，在WHERE 子句或 HAVING 子句中插入另一个 SQL 语句
语法：SELECT "字段1" FROM "表格1" WHERE "字段2" [比较运算符]              #外查询
(SELECT "字段1" FROM "表格2" WHERE "条件");                          #内查询
#可以是符号的运算符，例如 =、>、<、>=、<= ；也可以是文字的运算符，例如 LIKE、IN、BETWEEN
mysql> SELECT SUM(Sales) FROM store_info WHERE Store_Name IN  (SELECT Store_Name FROM location WHERE Region = 'West');
mysql> SELECT SUM(A.Sales) FROM store_info A WHERE A.Store_Name IN 
    -> (SELECT Store_Name FROM location B WHERE B.Store_Name = A.Store_Name);

---- EXISTS ----
用来测试内查询有没有产生任何结果，类似布尔值是否为真
#如果有的话，系统就会执行外查询中的SQL语句。若是没有的话，那整个 SQL 语句就不会产生任何结果。
语法：SELECT "字段1" FROM "表格1" WHERE EXISTS (SELECT * FROM "表格2" WHERE "条件");

mysql> SELECT SUM(Sales) FROM store_info WHERE EXISTS (SELECT * FROM location WHERE Region = 'West');
+------------+
| SUM(Sales) |
+------------+
|       3450 |
+------------+
————————————————


UPDATE Store_Info SET store_name='Washington' WHERE sales=300;
inner join(内连接)：只返回两个表中联结字段相等的行
left join(左连接)：返回包括左表中的所有记录和右表中联结字段相等的记录 
right join(右连接)：返回包括右表中的所有记录和左表中联结字段相等的记录

----INNER JOIN on
SELECT * FROM location A INNER JOIN store_info B on A.Store_Name = B.Store_Name ;

--------left JOIN on

--------right JOIN on

语法：SELECT “字段” FROM “表名” WHERE “字段” REGEXP {模式};