distinct
select distinct country from website;
limit
select * from website limit 2;
SQL通配符:% ,_ (代替一个字符), [charlist]
select * from website where name like '_oogle'
select * from website where name REGEXP '^[GFS]'
IN操作符(与where相关,用于嵌套查询): 在where中规定多个值
select * from website where name in ('Google','菜鸟')
Having操作符(与group by相关)
嵌套查询:https://www.cnblogs.com/shengulong/p/10217146.html
别名:
select cloumn_name as alias_name;
select name,CONCAT(url,',',country) as site_info from website;
JOIN...ON: inner join、left join、right join、full join
select * from A inner join B on A.xx = B.xx
UNION:合并多个select语句结果集
select country from A
union
select country from B
order by country;
union / union all 区别:对重复结果的处理
Select into:表数据复制到另一个表
create table xxx select * from yyy
约束:NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT
- create table (...,...,unique(column_name));
- CONSTRANT xx unique(column1,column2);
- drop constrant xx
- 添加约束:alter table xxx add unique(column_name) drop unique ... primary key ...
- foreign key (column_name) references A(xx)
- check(column >0)
- alter table A alter column B set default 'xxx'
索引:
简单索引:create index index_name on table_name(column_name);
唯一索引:create unique index index_name on table_name(column_name);
撤销索引:drop index table_name.index_name
修改:
修改表名:alter table table_name rename to xxx
增加列:alter table table_name alter column column_name datatype(类型)
删除列:alter table table_name drop column column_name datatype
视图:
创建视图:create view xx as ...
更新视图:create or replace view xx as...
撤销视图:drop view view_name
SQL Aggregate函数
AVG、COUNT、FIRST(第一个记录值)、LAST(列中最后一个记录)、MAX、MIN、GROUP BY、HAVING、UCASE(字段大写)、LCASE(字段小写)、MID(从文本字段中提取字段)、LEN、ROUND、FORMAT
典型场景:https://blog.rxliuli.com/p/73dac442/
- 行转列:select xx max(case xx when ...... then ... else 0 end) as xxx ............ from table group by xx
- 列转行: union all\
Hive:https://www.cnblogs.com/lucas-zhao/p/12023193.html
行转列:行转列,COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;
列转行:
LATERAL VIEW udtf(expression) tableAlias AS columnAlias
用于和split, explode等UDTF一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
select deptno,name from emp3 lateral view explode(names) tmp_tbl as name;