[大数据] CHAPTER 5 Advanced Querying

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> CHAPTER 5 Advanced Querying -> 正文阅读

[大数据]CHAPTER 5 Advanced Querying

Use nicknames,or aliases, in queies to save typing and allow a table to be used more than once in a query.
Aggregate data into groups so you can discover sums,averages,and counts.
Join Tables in different ways.
Use nested queries
Save query results in variables so they can be reused in other queries.

1.Aliases

?Column Aliases

mysql>?
mysql> SELECT first_name as 'First Name',last_name as 'Last Name'
? ? -> FROM actor LIMIT 5;
ERROR 1146 (42S02): Table 'sakila_new.actor' doesn't exist
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database ? ? ? ? ? |
+--------------------+
| information_schema |
| employees ? ? ? ? ?|
| lu ? ? ? ? ? ? ? ? |
| lu;cy ? ? ? ? ? ? ?|
| lucy ? ? ? ? ? ? ? |
| mysql ? ? ? ? ? ? ?|
| performance_schema |
| sakila ? ? ? ? ? ? |
| sakila_new ? ? ? ? |
| sys ? ? ? ? ? ? ? ?|
| world ? ? ? ? ? ? ?|
+--------------------+
11 rows in set (0.00 sec)

mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> SELECT CONCAT(first_name, ' ', last_name, ' played in ', title) AS movie
? ? -> FROM actor JOIN film_actor USING (actor_id)
? ? -> JOIN film USING (film_id)
? ? -> ORDER BY movie LIMIT 20;
+--------------------------------------------+
| movie ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+--------------------------------------------+
| ADAM GRANT played in ANNIE IDENTITY ? ? ? ?|
| ADAM GRANT played in BALLROOM MOCKINGBIRD ?|
| ADAM GRANT played in DISCIPLE MOTHER ? ? ? |
| ADAM GRANT played in FIREBALL PHILADELPHIA |
| ADAM GRANT played in GLADIATOR WESTWARD ? ?|
| ADAM GRANT played in GLORY TRACY ? ? ? ? ? |
| ADAM GRANT played in GROUNDHOG UNCUT ? ? ? |
| ADAM GRANT played in HAPPINESS UNITED ? ? ?|
| ADAM GRANT played in IDOLS SNATCHERS ? ? ? |
| ADAM GRANT played in LOSER HUSTLER ? ? ? ? |
| ADAM GRANT played in MARS ROMAN ? ? ? ? ? ?|
| ADAM GRANT played in MIDNIGHT WESTWARD ? ? |
| ADAM GRANT played in OPERATION OPERATION ? |
| ADAM GRANT played in SEABISCUIT PUNK ? ? ? |
| ADAM GRANT played in SPLENDOR PATTON ? ? ? |
| ADAM GRANT played in TADPOLE PARK ? ? ? ? ?|
| ADAM GRANT played in TWISTED PIRATES ? ? ? |
| ADAM GRANT played in WANDA CHAMBER ? ? ? ? |
| ADAM HOPPER played in BLINDNESS GUN ? ? ? ?|
| ADAM HOPPER played in BLOOD ARGONAUTS ? ? ?|
+--------------------------------------------+
20 rows in set (0.02 sec)

mysql> SELECT CONCAT(first_name, ' ', last_name, ' played in ', title) AS movie FROM actor JOIN film_actor USING (actor_id) JOIN film USING (film_id) ORDER BY CONCAT(first_name, ' ',last_name, ' played in
', title) LIMIT 20;
+--------------------------------------------+
| movie ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
+--------------------------------------------+
| ADAM GRANT played in ANNIE IDENTITY ? ? ? ?|
| ADAM GRANT played in BALLROOM MOCKINGBIRD ?|
| ADAM GRANT played in DISCIPLE MOTHER ? ? ? |
| ADAM GRANT played in FIREBALL PHILADELPHIA |
| ADAM GRANT played in GLADIATOR WESTWARD ? ?|
| ADAM GRANT played in GLORY TRACY ? ? ? ? ? |
| ADAM GRANT played in GROUNDHOG UNCUT ? ? ? |
| ADAM GRANT played in HAPPINESS UNITED ? ? ?|
| ADAM GRANT played in IDOLS SNATCHERS ? ? ? |
| ADAM GRANT played in LOSER HUSTLER ? ? ? ? |
| ADAM GRANT played in MARS ROMAN ? ? ? ? ? ?|
| ADAM GRANT played in MIDNIGHT WESTWARD ? ? |
| ADAM GRANT played in OPERATION OPERATION ? |
| ADAM GRANT played in SEABISCUIT PUNK ? ? ? |
| ADAM GRANT played in SPLENDOR PATTON ? ? ? |
| ADAM GRANT played in TADPOLE PARK ? ? ? ? ?|
| ADAM GRANT played in TWISTED PIRATES ? ? ? |
| ADAM GRANT played in WANDA CHAMBER ? ? ? ? |
| ADAM HOPPER played in BLINDNESS GUN ? ? ? ?|
| ADAM HOPPER played in BLOOD ARGONAUTS ? ? ?|
+--------------------------------------------+
20 rows in set (0.03 sec)

mysql> select first_name as name from actor where name = 'ZERO CAGE';
ERROR 1054 (42S22): Unknown column 'name' in 'where clause'
mysql> select actor_id as id FROM actor WHERE first_name = 'ZERO';
+----+
| id |
+----+
| 11 |
+----+
1 row in set (0.00 sec)

mysql> SELECT actor_id id from actor where first_name = 'ZERO';
+----+
| id |
+----+
| 11 |
+----+
1 row in set (0.00 sec)

mysql>?

Tables Aliases

mysql> SELECT ac.actor_id,ac.first_name,ac.last_name,fl.title from actor as ac inner join film_actor as fla using (actor_id) inner join film as fl using (film_id) where film.title = 'AFFAIR PREJUDICE';
ERROR 1054 (42S22): Unknown column 'film.title' in 'where clause'
mysql>?
mysql> select * from film where title = title limit 5;
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+
| film_id | title ? ? ? ? ? ?| description ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features ? ? ? ? ? ? ? ? | last_update ? ? ? ? |
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+
| ? ? ? 1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies ? ? ? ? ? ? ? ? ? ? ?| ? ? ? ? 2006 | ? ? ? ? ? 1 | ? ? ? ? ? ? ? ? NULL | ? ? ? ? ? ? ? 6 | ? ? ? ?0.99 | ? ? 86 | ? ? ? ? ? ?20.99 | PG ? ? | Deleted Scenes,Behind the Scenes | 2006-02-15 05:03:42 |
| ? ? ? 2 | ACE GOLDFINGER ? | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China ? ? ? ? ? ? ? ? ?| ? ? ? ? 2006 | ? ? ? ? ? 1 | ? ? ? ? ? ? ? ? NULL | ? ? ? ? ? ? ? 3 | ? ? ? ?4.99 | ? ? 48 | ? ? ? ? ? ?12.99 | G ? ? ?| Trailers,Deleted Scenes ? ? ? ? ?| 2006-02-15 05:03:42 |
| ? ? ? 3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory ? ? ? ? ? ? ? ? ? ? ?| ? ? ? ? 2006 | ? ? ? ? ? 1 | ? ? ? ? ? ? ? ? NULL | ? ? ? ? ? ? ? 7 | ? ? ? ?2.99 | ? ? 50 | ? ? ? ? ? ?18.99 | NC-17 ?| Trailers,Deleted Scenes ? ? ? ? ?| 2006-02-15 05:03:42 |
| ? ? ? 4 | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank ? ? ? ? ? ? ? ? ? ? ? ? ?| ? ? ? ? 2006 | ? ? ? ? ? 1 | ? ? ? ? ? ? ? ? NULL | ? ? ? ? ? ? ? 5 | ? ? ? ?2.99 | ? ?117 | ? ? ? ? ? ?26.99 | G ? ? ?| Commentaries,Behind the Scenes ? | 2006-02-15 05:03:42 |
| ? ? ? 5 | AFRICAN EGG ? ? ?| A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico | ? ? ? ? 2006 | ? ? ? ? ? 1 | ? ? ? ? ? ? ? ? NULL | ? ? ? ? ? ? ? 6 | ? ? ? ?2.99 | ? ?130 | ? ? ? ? ? ?22.99 | G ? ? ?| Deleted Scenes ? ? ? ? ? ? ? ? ? | 2006-02-15 05:03:42 |
+---------+------------------+-----------------------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+
5 rows in set (0.00 sec)

mysql>?

mysql> select m1.film_id,m2.title from film as m1,film as m2 where m1.title = m2.title and m1.film_id <> m2.film_id limit 5; ? ? ? ?
Empty set (0.00 sec)

mysql>?

2.Aggregating Data?

? The DISTINCT Clause

mysql>?

The GROUP BY Clause

mysql>?

Aggregate functions

count()

avg()

max()

min()

STD(),STDDEV(),STDDEV_POP()

SUM()

The Having Clause

mysql>?
mysql>?
mysql> select title,count(rental_id) as num_rented FROM?
? ? -> film inner join inventory using (film_id)
? ? -> inner join rental using (inventory_id)
? ? -> group by title
? ? -> having num_rented > 30
? ? -> order by num_rented desc limit 5;
+---------------------+------------+
| title ? ? ? ? ? ? ? | num_rented |
+---------------------+------------+
| BUCKET BROTHERHOOD ?| ? ? ? ? 34 |
| ROCKETEER MOTHER ? ?| ? ? ? ? 33 |
| JUGGLER HARDLY ? ? ?| ? ? ? ? 32 |
| RIDGEMONT SUBMARINE | ? ? ? ? 32 |
| FORWARD TEMPLE ? ? ?| ? ? ? ? 32 |
+---------------------+------------+
5 rows in set (0.02 sec)
mysql> select first_name,last_name,count(film_id) as film_cnt from actor inner join film_actor using (actor_id) group by actor_id,first_name,last_name having first_name = 'EMILY' AND
?last_name = 'DEE';?
+------------+-----------+----------+
| first_name | last_name | film_cnt |
+------------+-----------+----------+
| EMILY ? ? ?| DEE ? ? ? | ? ? ? 14 |
+------------+-----------+----------+
1 row in set (0.01 sec)

mysql> select first_name,last_name,count(film_id) as film_cnt from?
? ? -> actor inner join film_actor using (actor_id)
? ? -> where first_name = 'EMILY' and last_name = 'DEE'
? ? -> group by actor_id,first_name,last_name;
+------------+-----------+----------+
| first_name | last_name | film_cnt |
+------------+-----------+----------+
| EMILY ? ? ?| DEE ? ? ? | ? ? ? 14 |
+------------+-----------+----------+
1 row in set (0.00 sec)

mysql>?

Advanced Joins

The inner join?

The keyphrase INNER JOIN can be replaced with JOIN or STRAIGHT JOIN ;They all do the same thing.

mysql>?

The Union

The UNION operator has several limitations:

?The output is labeled with the names of the columns or expressions from the first query.
The queries must output the same number of columns.If you try using different numbers of columns ,MySQL will report an error.
All matching columns must have the same type .
The result returned are unique,as if you'd applied a distinct to the overall result set.

The Left and Right Joins

The Natural Join

mysql>?

?Constant Expressions in Joins

?Nested Queries

Nested Query Basics

The ANY,SOME,ALL,IN ,and NOT IN Clauses

Using ANY and IN

mysql> SELECT emp_no, first_name, last_name, hire_date

? ? ? ? ? -> FROM employees JOIN titles USING (emp_no)

? ? ? ? ? -> WHERE title = 'Assistant Engineer'

? ? ? ? ?-> AND hire_date < ANY (SELECT hire_date FROM

? ? ? ? ?-> employees JOIN titles USING (emp_no)

? ? ? ? ?-> WHERE title = 'Manager');

mysql> select hire_date from?
? ? -> employees join titles using (emp_no)
? ? -> where title = 'Manager';

?mysql> select emp_no,first_name,last_name
? ? -> from employees join titles using (emp_no)
? ? -> where title = 'Manager'
? ? -> and emp_no = ANY(select emp_no from employees join titles using (emp_no) where title <> 'Manager');

?Using ALL

We can use the alias NOT IN in place of <> ANY or != ANY.?

? Writing row subqueries

mysql> select mgr.emp_no,YEAR(mgr.from_date) as fd
? ? -> from titles as mgr,titles as other
? ? -> where mgr.emp_no = other.emp_no
? ? -> and mgr.title = 'Manager'
? ? -> and mgr.title <> other.title
? ? -> and year(mgr.from_date) = Year(other.from_date);
+--------+------+
| emp_no | fd ? |
+--------+------+
| 110765 | 1989 |
| 111784 | 1988 |
+--------+------+
2 rows in set (0.15 sec)

mysql> select emp_no,Year(from_date) as fd
? ? -> from titles where title = 'Manager' and?
? ? -> (emp_no,year(from_date)) IN
? ? -> (select emp_no,year(from_date)
? ? -> from titles where title <> 'Manager');
+--------+------+
| emp_no | fd ? |
+--------+------+
| 110765 | 1989 |
| 111784 | 1988 |
+--------+------+
2 rows in set (0.15 sec)

The EXISTS and NOT EXISTS Clauses

EXISTS and NOT EXISTS basics

mysql> select first_name,last_name from staff?
? ? -> where exists (select * from customer ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? -> where customer.first_name = staff.first_name? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
? ? -> and customer.last_name = staff.last_name);
Empty set (0.00 sec)

mysql> INSERT INTO customer(store_id,first_name,last_name,email,address_id,create_date) values(1,'Mike','Hillyer','Mike.Hillyer@sakilastaff.com',3,now());
Query OK, 1 row affected (0.01 sec)

mysql> select first_name,last_name from staff
? ? -> where exists (select * from customer
? ? -> where customer.first_name = staff.first_name
? ? -> and customer.last_name = staff.last_name);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Mike ? ? ? | Hillyer ? |
+------------+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from film where exists
? ? -> (select film_id from inventory
? ? -> where inventory.film_id = film.film_id
? ? -> group by film_id having count(*) >= 2);
+----------+
| count(*) |
+----------+
| ? ? ?958 |
+----------+
1 row in set (0.01 sec)

mysql>?

Use the In and finds managers who also had some other position:

mysql> select emp_no,first_name,last_name from employees join titles using (emp_no) where title = 'Manager' and emp_no IN (select emp_no from employees join titles using (emp_no) where title <> 'Manager');

?rewritten query:

mysql> select emp_no,first_name,last_name
? ? -> from employees join titles using (emp_no)
? ? -> where title = 'Manager'
? ? -> and exists (select emp_no from titles
? ? -> where titles.emp_no = employees.emp_no
? ? -> and title <> 'Manager');

Nested Queries in the FROM Clause

mysql> select emp_no,monthly_salary from?
? ? -> (select emp_no,salary/12 as monthly_salary from salaries) as ms limit 5;
+--------+----------------+
| emp_no | monthly_salary |
+--------+----------------+
| ?10001 | ? ? ?5009.7500 |
| ?10001 | ? ? ?5175.1667 |
| ?10001 | ? ? ?5506.1667 |
| ?10001 | ? ? ?5549.6667 |
| ?10001 | ? ? ?5580.0833 |
+--------+----------------+
5 rows in set (0.00 sec)

mysql> # derived table must have an alias,even if we don''t use the alias in our query.
mysql>?
mysql> select emp_no,monthly_salary from
? ? -> (select emp_no,salary/12 as monthly_salary from salaries) limit 5;
ERROR 1248 (42000): Every derived table must have its own alias
mysql>?

User Variables

The following query finds the title of a film and saves the result in a user variable:

mysql> SELECT @film:=title from film where film_id = 1;
+------------------+
| @film:=title ? ? |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

mysql> select @film;
+------------------+
| @film ? ? ? ? ? ?|
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

mysql> select @film:=title from film where film_id = 1;
+------------------+
| @film:=title ? ? |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

mysql> show warnings\G
Empty set (0.02 sec)

mysql> set @film := (select title from film where film_id = 1);
Query OK, 0 rows affected (0.01 sec)

mysql> select @film;
+------------------+
| @film ? ? ? ? ? ?|
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

mysql>?

?Second,we can use the SELECT INTO statement:

mysql> select title into @film from film where film_id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select @film;
+------------------+
| @film ? ? ? ? ? ?|
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

mysql>?

You can explicity set a variable using the set statement without a select.Suppose you want to initialize a counter to zero.

mysql> set @counter := 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @counter = 0,@age := 23;
Query OK, 0 rows affected (0.00 sec)

mysql> select 0 into @counter;
Query OK, 1 row affected (0.00 sec)

mysql> select 0, 23 into @counter,@age;
Query OK, 1 row affected (0.00 sec)

mysql> select max(rental_date) from rental
? ? -> join customer using (customer_id)
? ? -> where email = 'WESLEY.BULL@sakilacustomer.org';
+---------------------+
| max(rental_date) ? ?|
+---------------------+
| 2005-08-23 15:46:33 |
+---------------------+
1 row in set (0.00 sec)

mysql> select title from film
? ? -> join inventory using (film_id)
? ? -> join rental using (inventory_id)
? ? -> join customer using (customer_id)
? ? -> where email = 'WESLEY.BULL@sakilacustomer.org'
? ? -> and rental_date = '2005-08-23 15:46:33';
+-------------+
| title ? ? ? |
+-------------+
| KARATE MOON |
+-------------+
1 row in set (0.01 sec)

mysql> select max(rental_date) into @recent from rental
? ? -> join customer using (customer_id)
? ? -> where email = 'WESLEY.BULL@sakilacustomer.org';
Query OK, 1 row affected (0.00 sec)

mysql> select title from film
? ? -> join inventory using (film_id)
? ? -> join rental using (inventory_id)
? ? -> join customer using (customer_id)
? ? -> where email = 'WESLEY.BULL@sakilacustomer.org'
? ? -> and rental_date = @recent;
+-------------+
| title ? ? ? |
+-------------+
| KARATE MOON |
+-------------+
1 row in set (0.00 sec)

mysql>?