Shulou(Shulou.com)06/03 Report--

I. Common database commands

1. Query which databases show databases

2. Query what data tables are available: show tables

3. Show all functions: show functions

4. Use use databasename; to switch to a certain database

Example (switch to test database): use test

5. View the current database: select current_database ()

6. What are the fields and field details of the query data table: describe tablename

Example (query staged_employees data Table): describe staged_employees

Can be abbreviated to desc staged_employees

The results are as follows:

7. Check what data tables are in the specified database: SHOW TABLES IN DbName

Example: see which tables are under the xiaoxiao database

SHOW TABLES IN xiaoxiao

8. Obtain the table creation statement of the table:

Example (see the statement method used to create the emailtest table): show create table emailtest

9. View the description information of the database and the file directory location path information

Example (view description information and file directory location information of datetest database): describe database datetest

Second, create a database

Create a xiaoxiao database:

Create database xiaoxiao

Second, create a data table

Create table staged_employees (

Id int comment 'id'

User_name string comment 'user name')

Delete the database

When deleting a database, it is not allowed to delete a database with data. If there is data in the database, an error will be reported. If you want to ignore these contents, add the CASCADE keyword after it, ignore the error report, and delete the database.

DROP DATABASE DbName CASCADE (optional)

DROP DATABASE IF EXISTS DbName CASCADE

Delete the data table

Drop table staged_employees

Delete all the contents of the data table

Delete everything in the emaitest data table.

Insert overwrite table emailtest select * from emailtest where 1: 0

Change the name of the table

1. Change the table name

-rename the table name ALTER TABLE table_name RENAME TO new_table_name

6. Add fields to the data table:

Alter table lemailtest add columns (time int comment 'now time')

7. HIVE statistical function

1. The result of count (1) is consistent with that of count (*), including null values. Count (field) does not calculate null values

2. Set statistical function

2.1count function: count

Syntax: count (*), count (expr), count (DISTINCT expr [, expr_.])

Return value: int

Description: count (*) counts the number of rows retrieved, including rows with null values; count (expr) returns the number of non-null values in the specified field; count (DISTINCTexpr [, expr_.]) Returns the number of different non-null values for the specified field

For example:

Hive > select count (*) from lxw_dual

twenty

Hive > select count (distinct t) from lxw_dual

ten

2.2 summation statistics function: sum

Syntax: sum (col), sum (DISTINCT col)

Return value: double

Description: the result of the addition of col in the statistical result set of sum (col); the result of the addition of different values of col in the statistical result of sum (DISTINCT col)

For example:

Hive > select sum (t) from lxw_dual

one hundred

Hive > select sum (distinct t) from lxw_dual

seventy

Insert data into the emailtest data table (append data to the original data table)

Read a row of fx01 data table data, and then insert into the emailtest data table, the original data of the emailtest data table will not be moved.

Insert into table emailtest

Select * from fx01 limit 1

8. Insert data into the inverted emailtest data table (overwrite the data of the original data table, which is equivalent to emptying the original data table before writing new data)

The data is read from the fx01 data table and written to the emailtest data table, and the original emailtest data table is emptied.

INSERT OVERWRITE TABLE emailtest

SELECT email,y,m,d

FROM fx01 where masks 06 and dongs 19

IX. Keyword matching-like

1. Find the number of rows containing "system" in the column content from the fx01 table.

Select * from fx01 where content like'% system%'

Match according to a keyword, and then set a new keyword case. When.. Method

1. Case usage 1: CASE conditional judgment function CASE a WHEN b THEN c [WHEN d THEN e] * [ELSE f] END

Returns c if an equals b, e if an equals d, and f otherwise

Select policy,case policy when 'abc' then' Test 'else' ccc' end as policy from fx01 limit 6

2. Case usage II:

If you want to use case when and use such features as like, that is, if the string contains' Chinese'on how, including 'math' on how, including 'English' on how, like is used for where, put in the case when inside is invalid, you can use instr () this function to find the location of these characters appear, instead of like function, so write it.

Case when instr (t.strdata 'Chinese') > 0 then 0

When instr (t.strdata 'Chinese') > 0 then 1

When instr (t.strdata 'Chinese') > 0 then 2

Else 3 end

Example:

Select t1.policyjue case when instr (t1.policyscoret 'trust') > 0 then 'trust'

When instr (t 1. Policy1 'Zhang San') > 0 then 'Zhang San 1'

When instr (t 1. Policy1'Li Si') > 0 then'Li Si 1'

When instr (t 1. Policypint 'Xiao Ming') > 0 then 'Xiao Ming 1' else 'Xiao Hong' end from (select distinct policy from fx01 limit 6) T1

11. Order by--TOP N

Hive implements topN, using the combination of order by and limit. Order by is sorted in descending order, and limit is selected. Sort by default in ascending order, if you want to sort at the end in descending order plus DESC,ASC is ascending order.

Take the data that ranks TOP 5.

Select distinct company,count (company) as num from fx01 where masks 05 group by company order by num DESC limit5

12. Data table multi-table join-join

Multi-table join-join more than two tables

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

Join supports left join (left connection), right join (right connection), full join (full connection)

1. Example of two tables associated with each other

Select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company

2. Example of three tables associated with each other

Select fx01_lanjie.company,fx01_lanjie.lanjie,fx01_yujing.yujing,fx01_fangxing.fangxing,fx01_lanjie.y,fx01_lanjie.m from fx01_lanjie left join fx01_yujing ON fx01_lanjie.company=fx01_yujing.company join fx01_fangxing on fx01_lanjie.company=fx01_fangxing.company

13. Realize the keyword statistics of a column of fields-- split+explode function

0 stu table data:

Stu:

Id name

Hello,you zm2008

Hello,me zm2015

1 achieve word count: (column to line)-- > split segmentation + explode (explode)

1.0 data is split into arrays

Select split (id,',') from stu; gets the array

[hello,you]

[hello,me]

1.1 continue splitting the array (the hive explode function continues to split the array into a single character)

Select explode (split (id,',')) from stu; form function

Hello

You

Hello

Me

1.2 Group Statistics:

Select t1.c1, count (1) from (select explode (split (id,',')) as C1 from stu) T1 group by t1.c1

Hello 2

You 1

Me 1

Case realization requirements: count the number of times each keyword appears in the "keyword" column, remove the numbers, and only count Chinese keywords.

Table name: Testtable

ID

Keword

Y

M

D

one

Beijing; Guangzhou; Shenzhen; Guizhou

2017

two

eight

two

Chongqing; Henan

2017

two

five

seven

12345555

2017

nine

five

Implementation statement:

Select explode (split (keyword,';') as C1 from testtable where keword' rlike'^ [\\ u4e00 -\\ u9fa5] + $'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.