SQL (Structured Query Language) 是具有数据操纵和数据定义等多种功能的数据库语言,使用SQL语言能够有效提高计算机应用系统的工作质量与效率。而数据管理作为近年来最热门的专业,也给SQL的学习带来了极大的热度。那么如何入门并快速学习SQL呢?我们就从掌握以下SQL基本语句开始。今天我们总结了SQL最基础也是最面试必备的SQL语句,希望对大家的SQL学习有所帮助。
关系型SQL
基本查看--查看数据库。表名和表结构-表数据和总数
show status
show databases;
use your_database;
show tables;
DESC your_table;
查询单列或者多列
select user_birthday from user
查看行以及行数
select * from your_table limit 3;
计数:select count(*) from your_table
排序
1.只排一列
select user_birthday from user order by user_birthday;
select user_birthday from user order by user_birthday DESC;
2.多列排序,升序ASC是默认的
select * from user order by user_id,user_birthday;
select * from user order by user_id DESC,user_birthday;
select * from user order by user_id ,user_birthday DESC;
筛选以及过滤
过滤行以及查找 where in not like
select user_birthday from user where id>3;
select user_birthday from user where id like '3%';
去重
select distinct user_birthday from user
字符串和数值操作
拼接:select name||id as tiele from tableA ;
求和:select sum(field1) as sumvalue from tableA
平均:select avg(field1) as avgvalue from tableA
最大:select max(field1) as maxvalue from tableA
最小:select min(field1) as minvalue from tableA
分组和汇总
select count(*) from tableA group by sex;
select id,count(*) from tableA group by sex;
过滤分组
select id,count(*) from tableA group by sex having count(*)>2;
嵌套查询
select name,sex,id from tableA where id in (select id from tableB where id >3)
联结查询--以列为单位对表进行联结
select name,sex,id from tableA ,tableB where tableA.id = tableB.id;
select name,sex,id from tableA inner join tableB on tableA.id = tableB.id;
select name,sex,id from tableA ,tableB,tableC where tableA.id = tableB.id and tableC.hot = tableB.hot;
组合查询--以行位单位对表进行操作
select name,sex,id from tableA union select name,sex,id from tableB where tableB.id>3;
select name,sex,id from tableA union all select name,sex,id from tableB where tableB.id>3;
使用索引以及存储过程
说明
join 是两张表做交连后里面条件相同的部分记录产生一个记录集,
union是产生的两个记录集(字段要一样的)并在一起,成为一个新的记录集
帮助系统
help
help index
?index
退出
Exit
Hive中SQL基本语句
HQL---进入Hive中类 SQL 查询语言-关系型分析查询作业
进入
su hdfs
hbase shell
查看已存在的表
show databases;
show tables;
SHOW TABLES 'page.*';
查看表中有多少分区:
show partitions logs;
查看invites的表的结构
DESCRIBE invites;
查看部分行--查询的结果是随机选择的
SELECT foo FROM invites limit 3;
查看分区部分行
SELECT a.foo FROM invites a limit 3;
查看行以及行数
SELECT * FROM invites limit 3;
计数 select count(*) from invites;
排序
在分布式中的排序有所不同---全局排序和reduced有关
order by
sort by
distribute by
cluster By
筛选以及过滤
SELECT a.foo FROM invites a WHERE a.ds='2008-08-15';
去重查询
Hive上一个典型表内除重的写法
select ad ,sum(plus),count(distinct name,id) from invites;
分组和汇总
SELECT a.bar, count(*) WHERE a.foo > 0 GROUP BY a.bar;
SELECT year(ymd), avg(price_close) FROM stocks WHERE exchange = 'NASDAQ' AND symbol = 'AAPL' GROUP BY year(ymd) HAVING avg(price_close) > 50.0;
联结
SELECT sales.*, things.* FROM sales JOIN things ON (sales.id = things.id);
SELECT sales.*, things.* FROM sales LEFT OUTER JOIN things ON (sales.id = things.id);
帮助系统
显示所有函数:
show functions;
查看函数用法:
describe function substr;
退出
exit;
由于专业性和英语学习,计算机专业对于留学生来说也是充满挑战的学科,想要进一步学好CS,可以寻求 online tutoring 等学术资源。
https://www.studygate.com/blog-cn/programming%E7%BC%96%E7%A8%8B%E4%BB%A3%E5%86%99/
https://www.studygate.com/blog-cn/%E4%BB%A3%E7%A0%81coding%E4%BB%A3%E5%86%99/