学习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 | 求某一字段的长度 |
转换
整数转为小数 cast(xxx as float)
cast(xxx as decimal(3,1) 精度:3 小数:1
联合查询:union
查询执行顺序
- from 和 join
from 或 join会第一个执行,确定一个整体的数据范围. 如果要join不同表,可能会生成一个临时Table来用于下面的过程。 - where
确定了数据来源 WHERE 语句就将在这个数据源中按要求进行数据筛选,并丢弃不符合要求的数据行 - group by
group by 分组,那group by将对之前的数据进行分组,统计等,并将是结果集缩小为分组数.这意味着 其他的数据在分组后丢弃. - having
如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选 - select
确定结果之后,SELECT用来对结果col简单筛选或计算,决定输出什么数据 - distinct
如果数据行有重复DISTINCT 将负责排重. - order by
在结果集确定的情况下,ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了。此时可以用AS别名. - 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列表中的有效雇员。 |
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,则表之间的重复行将从结果中删除。