HIVE——数据仓库

分布式的数据仓库,结构化的数据文件映射为一张表，并提供类SQL查询功能。Hive的设计目的是让精通SQL技能但Java编程技能相对较弱的分析师能够对存放在HDFS中的大规模数据集执行查询

本质是：将HQL转化成MapReduce程序

1）Hive处理的数据存储在HDFS

2）Hive分析数据底层的实现是MapReduce

3）执行程序运行在Yarn上

Hive集群安装（基于Mysql）

1）安装jdk（略）

2）安装hadoop（略）

3）安装mysql数据库

//下载wget
yum  install wget
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
[root@hadoopNode1 soft]# rpm -ivh mysql-community-release-el7-5.noarch.rpm
//下载Mysql服务
[root@hadoopNode1 soft]# yum install mysql-server
 //启动服务
[root@hadoopNode1 soft]# systemctl start mysqld
//开机自启
[root@hadoopNode1 soft]# systemctl  enable  mysqld   #启用系统服务

初始化root用户

mysql  -u root 
mysql>use mysql;
mysql> update user set password=password('123456') where user='root';    //新密码为123456
mysql> select host,user from   user;
//赋予权限
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "123456";
//更新权限
mysql> flush privileges;
mysql> exit;

4）mysql数据库增加hive用户，并授权

mysql> create user 'hive' identified by '123456';
mysql> CREATE DATABASE hive;
mysql> use hive;
mysql> GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'%' IDENTIFIED BY '123456';
mysql>GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'hadoop1' IDENTIFIED BY '123456';   //写为自己的集群的mysql所在节点
mysql>GRANT ALL PRIVILEGES ON hive.* TO 'hive'@'localhost' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql>exit
//重启服务
systemctl restart mysqld

验证授权:

select host,user from user;

5)验证MYSQL配置正确

在windows下登陆MySQL

tar包:

[ambow@hadoopNode1 hive-2.3.2]$ tar -zxvf ~/soft/hive-1.1.0-cdh5.14.2.tar.gz  -C ~/app/

7）配置环境变量

修改.bash_profile文件

HIVE_HOME

PATH

HIVE_HOME=/home/ambow/app/hive-1.1.0-cdh5.14.2
JAVA_HOME=/home/ambow/app/jdk1.8.0_112
HADOOP_HOME=/home/ambow/app/hadoop-2.6.0-cdh5.14.2
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin
export PATH
export JAVA_HOME
export HADOOP_HOME
export HIVE_HOME

8)修改配置文件：

（1）进入HIVE_HOME/conf目录下，复制hive-env.sh.template

$>cp hive-env.sh.template hive-env.sh

$>vi hive-env.sh

修改：HADOOP_HOME=/home/ambow/app/hadoop

export HIVE_CONF_DIR=/home/ambow/app/hive-1.1.0-cdh5.14.2/conf

（2）进入HIVE_HOME/conf目录下，编辑hive-site.xml

[ambow@hadoopNode1 conf]$ vi   hive-site.xml

hive-1.1.0-cdh5.14.2版本：

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://hadoop1:3306/hive?createDatabaseIfNotExist=true</value>
    </property>
    <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
    </property>
    <property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>root</value>
    </property>
    <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>123456</value>
    </property>
    <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
    </property>
    <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
    </property>
</configuration>

9)将MySQL的连接驱动包拷贝至HIVE_HOME/lib下

wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.27/mysql-connector-java-5.1.27.jar

10）启动hadoop集群

 start-all.sh

11）启动hive客户端验证

HIVE基本数据类型

Hive数据类型	Java数据类型	长度	例子
TINYINT	byte	1byte有符号整数	20
SMALINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
BOOLEAN	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP		时间类型
BINARY		字节数组
VARCHAR		可变长度字符串
CHAR		固定长度字符串
DATE	date	日期
decimal	bigdecima	任意精度有符号小数

HIVE基本用法练习

一、数据仓库基本操作

1.创建’校园教育数据仓库’

创建数据仓库
CREATE DATABASE school_edu_base;

2.在school_edu_base数据仓库下分别创建学生表、课程表、教师表、成绩表，表结构如下：

1.学生表（student）

字段	类型	说明
s_id	String	学生id
s_name	String	姓名
s_birth	String	出生日期
s_sex	String	性别
s_age	String	年龄

2.课程表（curriculum）

字段	类型	说明
c_id	String	课程id
c_name	String	课程名称
t_id	String	教师id

3.教师表（Teacher）

字段	类型	说明
t_id	String	教师id
t_name	String	教师姓名

4.成绩表（Score）

字段	类型	说明
s_id	String	学生id
c_id	String	课程id
s_score	String	成绩

选择使用的数据仓库
USE school_edu_base;
创建学生表
create  table  `Student`  (
`s_id`  string  COMMENT  'id',
`s_name`  string  COMMENT  '姓名',
`s_birth`  string  COMMENT  '出生日期',
`s_sex`  string  COMMENT  '性别',
`s_age`  int  COMMENT  '年龄'
)row format delimited fields terminated by '\t';
创建课程表
create  table  `curriculum`  (
`c_id`  string  COMMENT  '课程id',
`c_name`  string  COMMENT  '课程名称',
`t_id`  string  COMMENT  '教师id'
)row format delimited fields terminated by '\t';
创建教师表
create  table  `Teacher`  (
`t_id`  string  COMMENT  '教师id',
`t_name`  string  COMMENT  '教师姓名'
)row format delimited fields terminated by '\t';
创建成绩表
create  table  `Score`  (
`s_id`  string  COMMENT  '学生id',
`c_id`  string  COMMENT  '课程id',
`s_score`  string  COMMENT  '成绩'
)row format delimited fields terminated by '\t';

3.分别给学生表、课程表、教师表、成绩表添加相应数据

3.1、在/usr/local/下 创建student.txt文件 准备学生数据
    01    赵雷    1990-01-01    男    20
    02    钱电    1990-12-21    男    20
    03    孙风    1990-05-20    男    20
    04    李云    1990-08-06    男    20
    05    周梅    1991-12-01    女    19
    06    吴兰    1992-03-01    女    18
    07    郑竹    1989-07-01    女    21
    08    王菊    1990-01-20    女    20
3.2、添加学生数据
load data local inpath '/usr/local/student.txt' into table student;
3.3、在/usr/local/下 创建curriculum.txt文件 准备学生数据
    01    语文    02
    02    数学    01
    03    英语    03
3.4、添加课程数据
load data local inpath '/usr/local/curriculum.txt' into table curriculum;
3.5、在/usr/local/下 创建teacher.txt文件 准备学生数据
    01    张三
    02    李四
    03    王五
3.6、添加教师数据
load data local inpath '/usr/local/teacher.txt' into table teacher;
3.7、在/usr/local/下 创建score.txt文件 准备学生成绩数据
    01    01    80
    01    02    90
    01    03    99
    02    01    70
    02    02    60
    02    03    80
    03    01    80
    03    02    80
    03    03    80
    04    01    50
    04    02    30
    04    03    20
    05    01    76
    05    02    87
    06    01    31
    06    03    34
    07    02    89
    07    03    98
添加学生成绩数据
load data local inpath '/usr/local/score.txt' into table score;

4.分别查看学生信息、课程信息、教师信息、学生成绩信息

查看学生表数据
SELECT * FROM Student;
查看课程表数据
SELECT * FROM curriculum;
查看教师表数据
SELECT * FROM Teacher;
查看成绩表数据
SELECT * FROM Score;

5.分别将学生表中姓名为’’郑竹”的学生性别修改为“男”，教师表中姓名为“王五”的修改为“赵六”

5.1、将student.txt中郑竹的性别改为‘男’
01    赵雷    1990-01-01    男    20
02    钱电    1990-12-21    男    20
03    孙风    1990-05-20    男    20
04    李云    1990-08-06    男    20
05    周梅    1991-12-01    女    19
06    吴兰    1992-03-01    女    18
07    郑竹    1989-07-01    男    21
08    王菊    1990-01-20    女    20
5.2、重新加载覆盖数据
load data local inpath '/usr/local/student.txt' overwrite into table student;
5.3、将teacher.txt中王五的姓名改为‘赵六’
01    张三
02    李四
03    赵六
5.4、重新加载覆盖数据
load data local inpath '/usr/local/teacher.txt' overwrite into table teacher;

6.分别删除学生表、教师表、课程表、成绩表中的所有数据

删除学生表数据
truncate table Student;
删除程表数据
truncate table curriculum;
删除教师表数据
truncate table Teacher;
删除成绩表数据
truncate table Score;

7.分别删除学生表、教师表、课程表、成绩表

删除学生表
DROP TABLE Student;
删除除程表
DROP TABLE curriculum;
删除教师表
DROP TABLE Teacher;
删除成绩表
DROP TABLE Score;

8.删除’校园教育数据仓库’

删除数据仓库
DROP DATABASE school_edu_base;

二、表结构修改

一：修改表信息

1.修改表名

#  新建一个学生会数据库
create database student_union;
#  新建一个usr表
create table usr(id int, name string,age int,sex string,address string) row format delimited fields terminated by ',';  
#  新建一个student表
create table student(id int, name string,age int,sex string,class string)
row format delimited fields terminated by ',';  
#    修改用户表表名
alter table usr rename to usrs;
#    修改学生表表名
alter table student rename to students;

2.修改表注释

alter table usrs SET TBLPROPERTIES('comment' = '用户信息表');
alter table students SET TBLPROPERTIES('comment' = '学生信息表');

二：修改字段信息

1.修改字段类型和注释

alter table students CHANGE COLUMN class class int comment '学生所属班级'
alter table usrs CHANGE COLUMN address address string comment '家庭地址'

2.修改字段类型

alter table students CHANGE COLUMN class class string;
alter table usrs CHANGE COLUMN address address int;

3.修改字段名称

alter table students change name student_name string COMMENT '用户名称';
alter table usrs change name usr_name string COMMENT '学生名称';

4.增加一个字段，设好数据类型，添加注释

alter table students add  columns(url string  comment '学校地址') ;
alter table usrs add  columns(hobby string  comment '兴趣爱好') ;
alter table usrs add  columns(work string,sal string) ;

5.删除字段

#    删除aid字段
alter table students replace columns(id int, name string,age int,sex string,class string,url string);
#    删除aid字段
alter table usrs replace columns(id int, name string,age int,sex string,address string,hobby string);
#    删除url字段
alter table students replace columns(id int, name string,age int,sex string,class string);
#    删除hobby字段
alter table usrs replace columns(id int, name string,age int,sex string,address string);

6.在表头新增字段

#    在学生表头新增字段
alter table students add columns(school string  comment '学校名称') ;
alter table students change school school string first;
#    在用户表头新增字段
alter table usrs add columns(school string  comment '学校名称') ;
alter table usrs change school school string first;

7.在某个字段后增加字段

#     在最后面添加字段  
alter table students add columns(url string comment '学校地址');
#    将字段调整位置导某个字段后
alter table students change url url string after id;
#     在最后面添加字段  
alter table usrs add columns(hobby string comment '兴趣爱好');
#    将字段调整位置导某个字段后
alter table usrs change hobby hobby string after id;

8.调整字段顺序

#    将students表的url字段调整到age后
alter table students change url url string after age;
#    将students表的sex字段调整到url后
alter table students change sex sex string after url;
#    将usrs表的address字段调整到id后
alter table usrs change address address string after id;
#    将usrs表的sex字段调整到address后
alter table usrs change sex sex string after address;

三、关联表创建

一：关联表的创建

1.创建数据仓库“education”

create database education;
use education;

2.创建课程表并插入课程数据

2.1、创建表
create  table  `course`  (
`id`  string  COMMENT  '课程id',
`name`  string  COMMENT  '课程名称',
`isdelete` int COMMENT    '是否删除:0-否，1-是'
)row format delimited fields terminated by '\t';
2.2、在/usr/local/下创建course.txt文件，准备数据
    01    Hadoop大数据基础    0
    02    大数据概论    0
    03    Hive数据仓库应用    0
2.3、加载数据
load data local inpath '/usr/local/course.txt' overwrite into table course;

3.创建班级表并插入班级数据

3.1、创建班级表
CREATE TABLE `clazz`  (
  `code` string  COMMENT '班级识别码',
  `name`  string COMMENT '班级名称',
  `createtime` string COMMENT '创建时间',
  `updatetime` string  COMMENT '修改时间',
  `createuser` string  COMMENT '创建人',
  `updateuser` string COMMENT '修改人'
) COMMENT = '班级表'
row format delimited fields terminated by '\t';
3.2、在/usr/local/下创建clazz.txt文件，准备数据
    2020131    建筑一班    2020-08-22 14:44:52    2020-08-22 14:44:52    张三    张三
    2020132    建筑二班    2020-08-22 14:44:52    2020-08-22 14:44:52    李四    李四
    2020133    建筑三班    2020-08-22 14:44:52    2020-08-22 14:44:52    王五    王五
3.3、加载数据
load data local inpath '/usr/local/clazz.txt' overwrite into table clazz;

4.创建课程班级关联表

4.1、创建关联表
CREATE TABLE `courseclazz`  (
  `code` string COMMENT '班级编码',
  `c_id` string COMMENT '课程id',
  `teacher` string COMMENT '授课教师',
  `createtime` string COMMENT '创建时间',
  `createuser` string COMMENT '创建人'
) COMMENT '课程班级关联表'
row format delimited fields terminated by '\t';
4.2、在/usr/local/下创建courseclazz.txt文件，准备数据
    2020131 01 张三 2020-08-22 14:44:52 张三
    2020131 02 李四 2020-08-22 14:44:52 李四
    2020131 03 赵六 2020-08-22 14:44:52 赵六
    2020132 01 张三 2020-08-22 14:44:52 张三
    2020132 02 李四 2020-08-22 14:44:52 李四
    2020132 03 赵六 2020-08-22 14:44:52 赵六
    2020133 01 张三 2020-08-22 14:44:52 张三
    2020133 02 李四 2020-08-22 14:44:52 李四
    2020133 03 赵六 2020-08-22 14:44:52 赵六
4.3、加载数据
load data local inpath '/usr/local/courseclazz.txt' overwrite into table courseclazz;

5.创建学生表并插入数据

5.1、创建表
CREATE TABLE `Student`(
`s_id` VARCHAR(20) COMMENT '学生id',
`s_name` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生输入',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别',
`s_age` int NOT NULL COMMENT '学生年龄',
PRIMARY KEY(`s_id`)
);
5.2、在/usr/local/下 创建student.txt文件 准备学生数据
    01    赵雷    1990-01-01    男    20
    02    钱电    1990-12-21    男    20
    03    孙风    1990-05-20    男    20
    04    李云    1990-08-06    男    20
    05    周梅    1991-12-01    女    19
    06    吴兰    1992-03-01    女    18
    07    郑竹    1989-07-01    女    21
    08    王菊    1990-01-20    女    20
5.3、添加学生数据
load data local inpath '/usr/local/student.txt' into table student;

6.创建学生班级表

6.1、创建表
CREATE TABLE `studentclazz`(
`s_id` VARCHAR(20) COMMENT '学生id',
`code` varchar(50) COMMENT '班级编码',
`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`createuser` varchar(50) NOT NULL COMMENT '创建人'
)COMMENT='学生表班级表';
6.2、在/usr/local/下 创建studentclazz.txt文件 准备学生数据
    01    2020131    2020-08-22 14:44:52    张三
    02    2020131    2020-08-22 14:44:52    张三
    03    2020131    2020-08-22 14:44:52    张三
    04    2020132    2020-08-22 14:44:52    李四
    05    2020132    2020-08-22 14:44:52    李四
    06    2020132    2020-08-22 14:44:52    李四
    07    2020133    2020-08-22 14:44:52    王五
    08    2020133    2020-08-22 14:44:52    王五
6.3、添加学生数据
load data local inpath '/usr/local/studentclazz.txt' into table studentclazz;

7.创建学生用户表并插入用户数据

创建表
CREATE TABLE `users`  (
  `ID` int(20) NOT NULL AUTO_INCREMENT,
  `USERNO` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户账号',
  `USERNAME` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名称',
  `PASSWORD` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '登录密码',
  `EMAIL` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `s_id` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生id',
  `STATUS` varchar(6) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '状态',
  `CREATERNO` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '数据创建者',
  `CREATEDATE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '数据创建时间',
  `MODIFIERNO` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '数据修改者',
  `MODIFIEDATE` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' COMMENT '数据修改时间',
  PRIMARY KEY (`ID`) USING BTREE
)COMMENT = '学生用户列表';
插入数据
INSERT INTO `users` VALUES (1, 'zhaolei', '赵雷', '123456', 'zhaolei@sina.com', '01', 'W00101', '张三', '2013-08-14 09:33:55', '张三', '2020-02-21 19:11:42');
INSERT INTO `users` VALUES (2, 'qiandian', '钱电', '123456', 'qiandian@sina.com', '02', 'W00101', '张三', '2020-06-18 21:39:48', '张三', '2020-06-18 21:39:48');
INSERT INTO `users` VALUES (3, 'shunfeng', '孙风', '123456', 'shunfeng@sina.com', '03', 'W00101', '张三', '2020-06-18 21:40:16', '张三', '2020-06-18 21:40:16');
INSERT INTO `users` VALUES (4, 'liyun', '李云', '123456', 'liyun@sina.com', '04', 'W00101', '李四', '2020-07-30 08:43:59', '李四', '2020-07-30 08:43:59');
INSERT INTO `users` VALUES (5, 'zoumei', '周梅', '123456', 'zoumei@sina.com', '05', 'W00101', '李四', '2020-08-18 11:07:05', '李四', '2020-08-18 11:07:05');
INSERT INTO `users` VALUES (6, 'wulan', '吴兰', '123456', 'wulan@sina.com', '06', 'W00101', '李四', '2020-08-20 10:22:59', '李四', '2020-08-20 10:22:59');
INSERT INTO `users` VALUES (7, 'zhengzu', '郑竹', '123456', 'zhengzu@sina.com', '07', 'W00101', '赵六', '2020-08-22 11:35:43', '赵六', '2020-08-22 11:35:43');
INSERT INTO `users` VALUES (8, 'wangju', '王菊', '123456', 'wangju@sina.com', '08', 'W00101', '赵六', '2020-08-20 10:22:59', '赵六', '2020-08-20 10:22:59');

四、基本查询操作

一：创建学生信息数据仓库database_student及数据表student，并插入相应数据

创建一个database_student数据库
```
create database database_student;
```
进入database_student数data据库
```
use database_student;
```

在database_student库中创建一个名为student的学生成绩表

create table student(name string,cj int)row format delimited fields terminated by '\t';

向student表中插入数据
列如以下学生成绩：

张国强    78
李峰    96
刘正    85
李奕辰    88
刘明    82
黄东    52
张董     28
黄晓     76
流觞     52
可乐     85
黄庭     56
黄山     25
张明     83
晓国     86
李栋     98
李强     92
王国峰    100
刘和刚    9
张七郎    85
黄东强    58
张国栋    98
辛温    95

使用数据加载语句把数据加载到表中

load data local inpath '/usr/local/hive/student.txt' into table student;

查询student表中所有的数据
```
select name,cj from student;
```

二：使用HSQL查询相应数据信息

查询成绩及格的数据

select name,cj from student where cj>=60;

查询成绩不及格的数据

select name,cj from student where cj<60;

查询成绩是满分的同学
```
select * from student where cj=100;
```
查询表前10条数据
```
select * from student limit 10;
```
5.查询所有姓’黄’同学的分数

select * from student where name like "%黄%";

三：使用HSQL聚合函数查询相应数据信息

统计表中有多少条数据
```
select count(*) from student;
```

统计及格的人数有多少

select count(*) from student where cj>=60;

统计表中的平均分是多少
```
select avg(cj) from student;
```
表中成绩最高分是多少
```
select max(cj) from student;
```
表中成绩最低分是多少
```
select min(cj) from student;
```

五、运算符

一：Hive算术运算符的使用

1.新建一个student学生信息表

create table student (s_id int,s_name string,s_age int,s_sex string,major string)row format delimited fields terminated by '\t';

2.导入student.txt数据

#    student
1910    张三    20      男      
1911    李四    21      男    
1912    王麻子  22      男      
1913    徐勇    23      男    
1914    张馨月  24      女      
1915    刘琳    25      女      
1916    刘洋    18      女     
1917    杨洋    20      男     
1918    周琦    22      男      
1919    张杰    19      男      
1920    范冰冰  20      女      
1921    mike    18      男    
load data local inpath '/usr/local/student.txt' into table student;

3.根据’学生信息表’年龄字段对关系运算符做展示

对年龄字段做加法处理
SELECT s_age+2 FROM student; 
对年龄字段做减法处理
SELECT s_age-1 FROM student; 
对年龄字段做乘法处理
SELECT s_age*1 FROM student; 
对年龄字段做除法处理
SELECT s_age/2 FROM student; 
SELECT s_age DIV 2 FROM student; 
对年龄字段做取余处理
SELECT s_age%2 FROM student;

二：Hive比较运算符的使用

1.根据’学生信息表’年龄字段对比较运算符做展示

查询年龄大于20岁的所有学生数据
SELECT * FROM student where s_age > 20; 
查询年龄小于20岁的所有学生数据  
SELECT * FROM student where s_age < 20; 
查询年龄不等于20岁的所有学生数据
SELECT * FROM student where s_age <> 20; 
SELECT * FROM student where s_age != 20; 
查询年龄等于20岁的所有学生数据 
SELECT * FROM student where s_age = 20; 
查询年龄小于等于20岁的所有学生数据  
SELECT * FROM student where s_age <= 20; 
查询年龄大于等于20岁的所有学生数据  
SELECT * FROM student where s_age >= 20; 
查询年龄在18-20之间的所有学生数据
SELECT * FROM student where s_age between 18 and 20; 
查询年龄在18,20,22,24中的所有学生数据
SELECT * FROM student where s_age in (18,20,22,24); 
查询年龄不在19,21,23,25,27中的所有学生数据
SELECT * FROM student where s_age not in (19,21,23,25,27); 
查询年龄为空的所有学生数据
SELECT * FROM student where s_age is NULL; 
查询年龄不为空的所有学生数据
SELECT * FROM student where s_age is NOT NULL; 
查询名字以张开头的所有学生数据
SELECT * FROM student where s_name like '张%'; 
查询名字有中刘字的所有学生数据
SELECT * FROM student where s_name REGEXP '刘';

三：Hive逻辑运算符的使用

1.根据’学生信息表’年龄、性别、学号字段对逻辑运算符做展示

查询年龄大于等于18岁且性别为男的所有学生数据
SELECT * FROM student 
where s_age >= 18
and s_sex = '男';
查询学号为01或学号为02的所有学生数据 
SELECT * FROM student 
where s_id = '1911'
or s_id = '1919';
查询性别不为男的所有学生数据
SELECT * FROM student 
where s_sex != '男';
查询性别不为女的所有学生数据
SELECT * FROM student 
where s_sex != '女';

六、正则表达式模糊查询

一：Hive正则表达式使用

1.新建一个“学生信息表”

create table student (s_id int,s_name string,s_age int,s_sex string,major string)row format delimited fields terminated by '\t';

2.导入student.txt数据

#    student数据
1910    张三    20      男      hadoop
1911    李四    21      男      linux
1912    王麻子  22      男      mysql
1913    徐勇    23      男      java
1914    张馨月  24      女      python
1915    刘琳    25      女      hive
1916    刘洋    18      女      hbase
1917    杨洋    20      男      c语言
1918    周琦    22      男      UI
1919    张杰    19      男      vue
1920    范冰冰  20      女      element
1921    mike    18      男      hadoop
load data local inpath '/usr/local/student.txt' into table student;

3.查询’学生信息表’中’姓名’字段以’王’开头的学生数据

SELECT * FROM student WHERE s_name REGEXP '^王';

4.查询名称中含有hadoop专业名称的学生信息

SELECT * FROM student WHERE major REGEXP '^Hadoop';

5.查询’学生信息表’中’姓名’字段以’洋’结尾的数据

SELECT * FROM student WHERE s_name REGEXP '洋$';

6.查询专业以nux结尾的专业数据

SELECT * FROM student WHERE major REGEXP 'nux$';

7.查询所有以1开头2结尾学号的学生数据

SELECT * FROM student WHERE s_id REGEXP '^1912$';

8.查询’学生信息表’中’姓名’字段包含’洋’的数据

SELECT * FROM student WHERE s_name regexp '洋*'; 
注：在指定字符之前必须有内容

9.匹配’学生信息表’中’姓名’字段包含’冰冰’的学生数据

SELECT * FROM student WHERE s_name regexp '冰冰';

10.查询名字中含有两个冰字的学生数据

SELECT * FROM student WHERE s_name regexp '(冰){2}';

11.查询学生’张三’、’李四’、’王麻子’的学生数据

SELECT * FROM student WHERE s_name regexp '^张三$|^李四$|^王麻子$';

12.查询以周开头或者以洋结尾的学生数据

SELECT * FROM student WHERE s_name regexp '^周|洋$';

13.查询除开’刘洋’之外学生数据

SELECT * FROM student WHERE s_name regexp '[^(刘洋)]';

14.查询学生中姓名不为中文的学生

 SELECT * FROM student WHERE s_name REGEXP '[u0391-uFFE5]+';

15.查询学生中姓名为中文的学生

 SELECT * FROM student WHERE s_name REGEXP '[^u0391-uFFE5]+'

16.查询年龄为整数的学生数据

SELECT * FROM student WHERE s_age regexp '[0]';

17.查询年龄不等于20的学生数据

SELECT * FROM student WHERE s_age regexp '[^(20)]';

18.查询年龄20出现1次以上的学生数据

SELECT * FROM student WHERE s_age regexp '20{1,}';
SELECT * FROM student WHERE s_age regexp '20+';

19.查询年龄在18或28的学生数据

SELECT * FROM student WHERE s_age regexp '[1-2]8';

七、子查询

一：子查询操作

进入database_student数据库
```
use database_student;
```

创建一个学生信息成绩表

create table studentcj(name string,age int,Gender String,class String,cj int)row format delimited fields terminated by '\t';

向studentcj表中录入一下数据
列如：

张飞    19    男    5班    95
刘备    21    男    2班    86
关羽    20    男    4班    60
小乔    18    女    1班    80
刘美乐    19    女    5班    87
刘郑国    21    男    4班    79
张国栋    20    男    3班    90
刘攀    18    男    2班    97
向会英    18    女    3班    45
王捷    18    男    4班    36
徐海英    18    女    5班    58
邵浙渝    19    女    1班    68
刘伟    20    男    2班    60
王颖佳    21    男    1班    76
扶齐    19    男    5班    62
周福亮    21    男    4班    51
冯兰    22    女    3班    66
张丹媚    20    女    2班    58
焦艳荣    19    女    5班    68
叶昌建    22    男    4班    98
胥迅    18    女    5班    76
周建武    19    男    2班    88
晏旖嫔    20    男    3班    97
高洁    19    女    5班    100
王爱卿    18    女    1班    99
于晓琴    19    女    3班    54

使用数据加载语句把数据加载到表中

load data local inpath '/usr/local/hive/studentcj.txt' into table studentcj;

查询成绩在80分以上的学生基本信息

select * from (select * from studentcj where cj>=80)a;

查询女学生中年龄大于18的女学生信息

SELECT * FROM (SELECT * FROM studentcj WHERE Gender = '女' ) as temp WHERE age>18;

查询成绩不及格的学生都在那些班级

select class from(select * from studentcj where cj<60)a;

查询年龄大于等于20的学生信息

select * from(select * from studentcj where age>=20)a;

9.查询5班的平均分是多少

select avg(cj) from(select * from studentcj where class="5班")a;

10.查询所有成绩及格学生的信息

select * from(select * from studentcj where cj>=60)a;

11.统计表中所有的男生人数

select count(*) from(Select * from studentcj where gender='男')a;

12.查寻年龄在19岁中成绩最高的学生信息

select max(cj) from(select * from studentcj where age=19)a;

八、联合查询

一：内联查询操作

进入database_student数据库
```
use database_student;
```

创建专业表

create table zy(zybm int,zymc string)row format delimited fields terminated by '\t';

导入以下数据
列如：

1    建筑系
2    信息系
3    人工智能系
4    美术系
5    计算机系
6    表演系

使用数据加载语句把数据加载到表中

load data local inpath '/usr/local/hive/zy.txt' into table zy;

创建班级表

create table bj(bjmc string,fdy string,bjbm int,zybm int)row format delimited fields terminated by '\t';

导入以下数据
列如:

建筑工程一班    刘正    1    1
信息工程三班    河东    2    2
计算机二班    李全    3    5
人工智能一班    刘美    4    3
美术四班    张正    5    4
表演一班    李明    6    6

使用数据加载语句把数据加载到表中

load data local inpath '/usr/local/hive/bj.txt' into table bj;

创建学生表

create table xs(xsxm string,nl int,xb string,bjbm int)row format delimited fields terminated by '\t';

导入以下数据

张飞    19    男    5
刘备    21    男    2
关羽    20    男    4
小乔    18    女    1
刘美乐    19    女    6
刘郑国    21    男    4
张国栋    20    男    3
刘攀    18    男    2
向会英    18    女    3
王捷    18    男    6
徐海英    18    女    5
邵浙渝    19    女    1
刘伟    20    男    2
王颖佳    21    男    1
扶齐    19    男    5
周福亮    21    男    4
冯兰    22    女    3
张丹媚    20    女    6
焦艳荣    19    女    5
叶昌建    22    男    4
胥迅    18    女    5
周建武    19    男    2
晏旖嫔    20    男    6
高洁    19    女    5
王爱卿    18    女    1
于晓琴    19    女    3

使用数据加载语句把数据加载到表中

load data local inpath '/usr/local/hive/xs.txt' into table xs;

以内连的方式查询计算二班的学生信息

select * from xs as x inner join bj as b on x.bjbm=b.bjbm where b.bjbm=3;

查询专业人工智能系下的学生信息

select * from xs as x inner join bj as b on x.bjbm=b.bjbm inner join zy as z on b.zybm=z.zybm where z.zybm=3;

二：外联查询操作

使用HSQL的左连接查询张飞是那个班的学生

select x.*,b.bjmc from xs as x left join bj b on x.bjbm=b.bjbm where x.xsxm='张飞';

使用左连接查询学生扶齐的班级名称，辅导员，专业

select x.*,b.bjmc,b.fdy,z.zymc from xs as x left join bj b on x.bjbm=b.bjbm left join zy as z on b.zybm=z.zybm where x.xsxm='扶齐';

使用右连接查询人工智能一班学生信息

select b.bjmc,x.* from bj as b right join xs x on b.bjbm=x.bjbm where b.bjmc='人工智能一班';

三：union操作

分别查询男女生学生信息并以union将结果联合在一起

select * from xs where xb='男' union select * from xs where xb='女';

HIVE数据仓库

HIVE——数据仓库

Hive集群安装（基于Mysql）

HIVE基本数据类型

HIVE基本用法练习

一、数据仓库基本操作

二、表结构修改

一：修改表信息

二：修改字段信息

三、关联表创建

一：关联表的创建

四、基本查询操作

一：创建学生信息数据仓库database_student及数据表student，并插入相应数据

二：使用HSQL查询相应数据信息

三：使用HSQL聚合函数查询相应数据信息

五、运算符

一：Hive算术运算符的使用

二：Hive比较运算符的使用

三：Hive逻辑运算符的使用

六、正则表达式模糊查询

一：Hive正则表达式使用

七、子查询

一：子查询操作

八、联合查询

一：内联查询操作

二：外联查询操作

三：union操作