认识mysql(3)

2019-11-14 17:33栏目:网站首页

认知mysql第三篇,发出的内容适合初大方,要是能持续关心自身的博客,能够完备的牵线mysql的常用知识,后续笔者也会时断时续发生python相关的学问,关心本人,和自家总共进步吧!

安排hive时出现不可能加载本身改过的hive-site.xml等布置文件的标题。发掘它连接加载暗中认可的配备文件。
解决:
hadoop的布局文件hadoop-env.sh中加上export HADOOP_CLASSPATH=$HIVE_HOVE/conf:$HADOOP_CLASSPATH
再有三个主题材料:运维其余hadoop子项目时总会冒出找不到类公事,必得把相关jar包拷贝到hadoop的lib目录下,以致其lib目录会愈发大。
现今不了然怎么将其他jar包参预到classpath中,网络说用export HADOOP_CLASSPATH=“”,然则好像特别
hive --config /root/etc/hive(注:/root/etc/hive是指hive-site.xml的寄存目录卡塔 尔(英语:State of Qatar)

1、SQL查询
  1、施行顺序
    3、select ...聚合函数 from 表名
      1、where ...
      2、group by ...
      4、having ...
      5、order by ...
      6、limit ...
  2、group by
    1、成效 :给查询结果开展分组
    2、示例
      1、查询表中累加有多少个国家

HiveQL以分行截至。能够跨行。
在hive的shell上得以采取dfs命令施行HDFS文件操作。
dfs -ls /user/hive/warehouse;

      2、总计每一种国家的平均攻击力
      select country,avg(gongji) from sanguo
      group by country;
    3、查找全部国家中挺身数量最多的前2名的 国家名称和大胆数量
      select country,count(id) as number from sanguo
      group by country
      order by number desc
      limit 2;
3、注意
  1、group by之后的字段名一定要为select之后的字段名
  2、若是select之后的字段名和group by之后的字段不平等,则必得对该字段举办联谊管理(聚合函数)
  3、having语句
1、作用
对查询的结果进行更进一层筛选
2、示例
  1、寻觅平均攻击力>105的国家的前2名,展现国家名和平均攻击力
  select country,avg(gongji) as pjgj from sanguo
  group by country
  having pjgj>105
  order by pjgj DESC
  limit 2;
3、注意
  1、having语句平时和group by语句联合使用,过滤由group by语句返回的记录集
  2、where只可以操作表中实际存在字段,having可操作由聚合函数生成的来得列
4、distinct
  1、功用 :不显得字段重复值
  2、示例
1、表中都有如何国家
  select distinct country from sanguo;
2、计算孙吴大器晚成共有个别许个英豪
  select count(distinct id) from sanguo
  where country="蜀国";
3、注意
  1、distinct和from之间全数字段都同大器晚成才会去重
  2、distinct不可能对任何字段做聚合管理
5、查询表记录时做数学生运动算
1、运算符
   - * / %
2、示例
1、查询时全部大胆攻击力翻倍
select id,name,gongji*2 as gj from sanguo;

hive语句必需以分行“;”截止。
不扶助立异,索引和专门的学问。
表名,列名不区分轻重缓急写。
在hive的shell上得以行使dfs命令试行HDFS文件的操作。>>dfs -ls /user/hive/warehouse/;
查阅和设置一时变量:>>set fs.default.name[=hdfs://zhaoxiang:9000];
导入jar包: >>add jar hivejar.jar;
始建函数: >>create temporary function udfTest as 'com.cstore.udfExample';
【在pig中选取UDF,先用register语句注册jar文件,之后方可因而一点一滴的java类名调用,恐怕用define语句为UDFding 钦命叁个称呼:
register pigjar.jar;
define UPPER org.pigjar.string.UPPER();
B = foreach a generate UPPER($0); 】

## sudo apt-get install python3-pip
## sudo pip3 install pymysql
2、约束
  1、功效 :保险数据的完整性、生龙活虎致性、有效性
2、约束分类
  1、暗中同意限定(default)
  1、插入记录,不给该字段赋值,则应用暗许值
2、非空节制(not NULL)
  1、差别意该字段的值有NULL记录
  sex enum("M","F","S") not null defalut "S"

能够在本地命令行运转hive的shell:
$ hive -e 'select * from userinfo' (执行hiveQL语句)
$ hive --config /hive-0.9.0/conf (重新载入新的陈设文件)
$ hive --service hiveserver 50000(运行服务)

3、索引
  1、定义
  对数据库表的一列或多列的值实行排序的豆蔻梢头种结构(Btree情势)
  2、优点
  加速数据检索速度
3、缺点
  1、占用物理存款和储蓄空间
  2、当对表中多少更新时,索引要求动态维护,收缩数据保养速度
4、索引示例
  1、开启运转时刻检查评定 :set profiling=1;
  2、推行查询语句
  select name from t1 where name="lucy99999";
3、查看施行时间
  show profiles;
4、在name字段创造索引
  create index name on t1(name);
5、再实行查询语句
  select name from t1 where name="lucy88888";
6、查看推行时间
  show profiles;
5、索引
1、普通索引(index)
1、使用法则
1、可安装多个字段
2、字段值无束缚
3、key标志 :MUL
2、创建index
1、创造表时
  create table 表名(...
  index(字段名),index(字段名));
2、已有表
  create index 索引名 on 表名(字段名);
  create index name on t3(name);
3、查看索引
  1、desc 表名; --> KEY标志为:MUL
  2、show index from 表名G;
  4、删除索引
  drop index 索引名 on 表名;
2、独一索引(unique)
  1、使用准则
    1、可安装几个字段
    2、节制 :字段值不允许再度,但可为 NULL
    3、KEY标志 :UNI
    2、创建
    1、创造表时创立
    unique(字段名),
    unique(字段名)
2、已有表
  create unique index 索引名 on 表名(字段名);
3、查看、删除 同普通索引
3、主键索引(primary key)
自增进属性(auto_increment,协作主键一同行使)
1、使用法则
1、只好有八个主键字段
2、节制 :不允许再一次,且不可能为NULL
3、KEY标志 :PRI
4、平常设置记录编号字段id,能唯后生可畏锁定一条记下
2、创建
1、创建表时
  (id int primary key auto_increment,
  )auto_increment=10000;##设置自拉长起首值
本来就有表增加自增进属性:
  alter table 表名 modify id int auto_increment;
本来就有表重新钦命开首值:
  alter table 表名 auto_increment=20000;
2、已有表
  alter table 表名 add primary key(id);
3、删除
1、删除自增加属性(modify)
  alter table 表名 modify id int;
2、删除主键索引
  alter table 表名 drop primary key;
4、外键索引
4、数据导入
1、成效 :把文件系统的原委导入到数据库中
2、语法
load data infile "/var/lib/mysql-files/文件名"
into table 表名
fields terminated by "分隔符"
lines terminated by "n";
3、将scoretable.csv文件导入到数据库的表中
1、在数据库中创建对应的表
create table scoretab(
id int,
name varchar(15),
score float(5,2),
number bigint,
class char(7)
);
2、把公文拷贝到数据库的私下认可寻觅路线中
1、查看暗许搜索路线
  show variables like "secure_file_priv";
  /var/lib/mysql-files/
2、拷贝文件
  sudo cp ~/scoretable.csv /var/lib/mysql-files/
3、施行多少导入语句
load data infile "/var/lib/mysql-files/scoretable.csv"
into table scoretab
fields terminated by ","
lines terminated by "n";
4、文件权限
rwxrw-rw- 1 tarena tarena scoretable.csv
所有者 所属组
rwx : tarena用户
rw- : 同组其余顾客
rw- : 其余组的其余客户(mysql客户)

create table cite(citing int, cited int) row format delimited fields terminated by ',' stored as textfile; //sequencefle

r -> 4
w -> 2
x -> 1
chmod 644 文件名 rw-r--r--
5、Excel表格怎样转变为CSV文件
1、展开Excel文件 -> 另存为 -> CSV(逗号分隔)
6、改革文件编码格式
1、用记事本/编辑器 展开,文件->另存为->选用编码
5、数据导出
1、作用
将数据库中表的记录导出到系统文件里
2、语法格式
select ... from 表名
into outfile "/var/lib/mysql-files/文件名"
fields terminated by "分隔符"
lines terminated by "n";
3、把MOSHOU库下的sanguo表大侠的人名、攻击值、国家导出来,sanguo.txt
select name,gongji,country from MOSHOU.sanguo
into outfile "/var/lib/mysql-files/sanguo.txt"
fields terminated by " "
lines terminated by "n";
$ sudo -i
$ cd /var/lib/mysql-files/
$ ls
$ cat sanguo.txt
4、将mysql库下的user表中 user、host多个字段的值导出到 user.txt
select user,host from mysql.user
into outfile "/var/lib/mysql-files/user.txt"
fields terminated by " "
lines terminated by "n";

load data (local) inpath 'cite75_99.txt' overwrite into table cite;//若不加local则默以为HDFS路线

本节完!

select * from cite limit 10;

show tables;
describe cite;

select count(1)/count() from cite; //count(1)相当于SQL中的count()

create table cite_count (cited int, count int);

insert overwrite table cite_count select cited , count(citing) from cite group by cited;

select * from cite_count where count > 10 limit 10;

drop table cite_count;

create table page_view(viewTime int, userid bigint,
page_url string, referrer_url string,
ip string comment 'ip address of user')
comment 'this id the page view table'
partitioned by (dt string, country string)//注意table中的列不能够和partition中的列重合
clustered by (userid) into 32 buckets //桶
row format delimited
fields terminated by ','
collection items terminated by '\002'
map keys terminated by '\003'
lines terminated by 'n'
stored as textfile;

//取样
select avg(viewTime) from page_view tablesample(bucket 1 out of 3 [on id]);

//创制外界表,钦命目录地点,删除此之外界表时会去除元数据,表中的数码需手动删除
create external table page_view(viewTime int, userid bigint,
page_url string, referrer_url string,
ip string comment 'ip address of user')
location 'path/to/existing/table/in/HDFS';
//修改表
alter table page_view rename to pv;
alter table pv add columns (newcol string);
alter table pv drop partition (dt='2009-09-01');

show tables 'page_.*';

load data local inpath 'page_view.txt'
overwrite into table page_view
partition (dt='2009-09-01',country='US');

在hive的shell上实行unix命令:命令前加惊叹号(!),命令尾加分号(;).
hive> ! ls ;
hive> ! head hive_result;

//实践查询:
insert overwrite table query-result; //eg: insert overwrite table query_result select * from page_view where country='US';
insert overwrite (local) directory '/hdfs-dir(local-dir)/query_result' query;
select country , count(distinct userid) from page_view group by countey;

//子查询,只可以在from子句中现身子查询
select teacher, max(class-num) from
(select teacher, count(classname) as class-num from classinfo group by teacher)subquery
group by teacher;

//连接
select pv., choice., f.friends from page_view pv
join user u on (pv.userid=u.id)
join friends-list f on (u.id=f.uid);

//多表插入
create table mutil1 as select id, name from userinfo;
create table mutil2 like mutil1;
from userinfo insert overwrite table mutil1 select id, name
insert overwrite table mutil2 select count(distint id),name group by name;

//成立视图
create view teacher_classnum as select teacher, count(classname) from classinfo group by teacher;

版权声明:本文由威尼斯人app发布于网站首页,转载请注明出处:认识mysql(3)