学习SQL and SQLBolt知识点总结

学习SQL可以使用

xuesql.cn

完整的SQL查询

select distinct column, agg_func(column_or_expression), …
from mytable m
inner/left/right/full join another_table a
on m.column = a.column
where column is/is not null and/or another_condition and/or …
group by column
having constraint_expression
order by column,column1 asc/desc #多列排序
limit num_limit offset num_offset;

属性和表取别名

select column as better_column_name, …
from a_long_widgets_table_name as mywidgets
inner join widget_sales
on mywidgets.id = widget_sales.widget_id;

where 之后

id != 4
id between 1.5 与 10.5
id in (2,4,6)
col_name not in (1,3,5)

条件查询
Operator	Condition	Example
=, !=, < <=, >, >=	基础的 大于，等于等比较	col_name = "abc"
(not) between … and …	(不)在两个数之间	col_id (not) between 1.5 and 10.5
like	没有用通配符等价于 =	col_name like "ABC"
not like	没有用通配符等价于 !=	col_name not like "ABCD"
%	通配符，代表匹配0个以上的字符	col_name LIKE "%AT%"(matches "AT", "ATTIC", "CAT" or even "BATS")
_	和% 相似，代表1个字符	col_name LIKE "AN_"(matches "AND", but not "AN")
in (…)	在一个列表	col_name in ("A", "B", "C")
not in (…)	不在一个列表	col_name not in ("D", "E", "F")

连接

内连接：
将inner join简写为join。这两个是等效的，但我们将继续将这些联接称为内部联接

外连接：
将表A连接到表B时，left join无论是否在B中找到匹配的行，a都会简单地包含A中的行。right join相同，但是相反，无论在A中是否找到匹配项，都将行保留在B中。full join仅仅意味着保留两个表中的行，而不管另一个表中是否存在匹配的行。


当你计算一些行的平均值的时候，如果是0会参与计算导致平均值差错，是NULL则不会参与计算

带表达式的查询

select particle_speed / 2.0 as half_particle_speed(对结果做了一个除2）
from physics_data
where abs(particle_position) * 10.0 > 500;（条件要求这个属性绝对值乘以10大于500）

select col_expression as expr_description, …
from mytable;

select column as better_column_name, …
from a_long_widgets_table_name as mywidgets
inner join widget_sales
on mywidgets.id = widget_sales.widget_id;

operator	SQL Example	解释
+ - * / %	col1 + col2	col加减乘除
substr	substr(col,0,4)	字符串截取
AS	col * 2 AS col_new	col取别名

常见统计函数及表达式

MIN(column)

Function	Description
COUNT(*), COUNT(column)	计数！COUNT(*) 统计数据行数，COUNT(column) 统计column非NULL的行数.
MIN(column)	找column最小的一行.
MAX(column)	找column最大的一行.
AVG(column)	对column所有行取平均值.
SUM(column)	对column所有行求和.
length	求某一字段的长度

可以参考官方文档:mysql:https://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html

转换

整数转为小数 cast(xxx as float)
cast(xxx as decimal(3,1) 精度：3 小数：1
联合查询：union

查询执行顺序

1. from 和 join
   from 或 join会第一个执行，确定一个整体的数据范围. 如果要join不同表，可能会生成一个临时Table来用于下面的过程。
2. where
   确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行
3. group by
   group by 分组，那group by将对之前的数据进行分组，统计等，并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.
4. having
   如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选
5. select
   确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据
6. distinct
   如果数据行有重复DISTINCT 将负责排重.
7. order by
   在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名.
8. limit/offset
   最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.

复杂题

select title, maxt.max_s-(Domestic_sales+International_sales)
from(SELECT *
FROM Movies m
left join Boxoffice b on m.Id = b.Movie_id)as mb
cross join
(SELECT Domestic_sales+International_sales as max_s
FROM Movies m
left join Boxoffice b on m.Id = b.Movie_id
order by Domestic_sales+International_sales DESC
limit 1)as maxt

插入数据

insert into mytable
values (value_or_expr,another_value_or_expr,…), (value_or_expr2,another_value_or_expr2,…),…;

insert into mytable
(column,another_column,…)
values (value_or__2,another_value_or_expr_2,…),…;

insert into boxoffice
(movie_id,rating,sales_in_millions)
values (1,9.9,283742034/1000000); #可以插入表达式

insert into movies values (5,‘nihao’,‘wanggang’,2010,120)

更新数据

update mytable
set column=value_or_expr, other_column=another_value_or_expr,…
where condition;

update movies set year=1999 where title like ‘Toy Story 2’;

删除行

delete from mytable where condition; 不用where将会删除整个表

delete from movies where director like ‘Andrew Stanton’;

创建表

create table if not exists mytable(
column DataType TableConstraint default default_value,
another_column DataType TableConstraint default default_value,
…
);

数据类型	描述
integet,boolean	整数数据类型可以存储整个整数值，例如数字或年龄。在某些实现中，布尔值仅表示为仅0或1的整数值。
float,double,real	浮点数据类型可以存储更精确的数字数据，例如测量值或分数值。根据该值所需的浮点精度，可以使用不同的类型。
charracter(num_chars),varchar(num_chars),text	基于文本的数据类型可以在各种语言环境中存储字符串和文本。在使用这些列时，各种类型之间的区别通常等于底层数据库的效率。 CHARACTER和VARCHAR（可变字符）类型均指定了它们可以存储的最大字符数（较长的值可能会被截断），因此可以更有效地存储和查询大表。
date,datetime	SQL还可以存储日期和时间戳，以跟踪时间序列和事件数据。它们可能很难处理，尤其是在跨时区处理数据时。
blog	最后，SQL可以将二进制数据直接存储在数据库中的blob中。这些值对于数据库通常是不透明的，因此通常必须将它们与正确的元数据一起存储才能重新查询它们。

约束	描述
primary key	这意味着该列中的值是唯一的，并且每个值都可用于标识该表中的单个行。
autoincrement	对于整数值，这意味着该值将自动填充，并随每次行插入而增加。并非所有数据库都支持。
unique	这意味着此列中的值必须是唯一的，因此您不能在此列中插入与表中的另一行具有相同值的另一行。与`PRIMARY KEY`的不同之处在于，它不必是表中一行的键。
not null	这意味着插入的值不能为“ NULL”。
check(expression)	这使您可以运行更复杂的表达式来测试插入的值是否有效。例如，您可以检查值是正数还是大于特定大小，或者以特定前缀开头等等。
foreign key	这是一个一致性检查，可确保此列中的每个值都与另一个表中列中的另一个值相对应。 例如，如果有两个表，一个表按ID列出所有employee，另一个表列出其工资信息，则“ FOREIGN KEY”可以确保工资表中的每一行都对应于主Employee列表中的有效雇员。

create table movies( id integer primary key, title text, director text, year integer, length_minutes integer); # 更改表 ### 添加列 alter table mytable add column DataType OptionalTableConstraint default default_value;

alter table movies
add Language text
default English;

移除列

alter table mytable
drop column_to_be_deleted;

重命名表格

alter table mytable
rename to new_table_name;

删除表

drop table if exists mytable; 它可以从 数据库中完全删除表结构

drop table if exists movies;

子查询

select * from sales_associates
where salary>(select avg(revenue_generated) from sales_associates);

相关子查询

select * from employees
where salary>(select avg(revenue_generated) from employees as dept_employees where dept_employees.department=employees.department);

存在测试

select *,… from mytable
where column in/not in (select another_column from another_table):

unions,intersections&exceptions

select column,another_column from mytable
union/union all/intersect/except
select other_column,yet_another_column from another_table
order by column desc limit n;
union and union all运算符允许您将一个查询的结果附加到另一个查询中，前提是它们的列数，顺序和数据类型相同。如果使用UNION不带的ALL，则表之间的重复行将从结果中删除。