CHAPTER 5 Advanced Querying

DB架构

已于 2022-03-11 19:32:57 修改

阅读量612

点赞数

分类专栏：深入mysql的世界文章标签： mysql

于 2022-03-11 19:27:57 首次发布

本文链接：https://blog.csdn.net/u011868279/article/details/123109811

版权

深入mysql的世界专栏收录该内容

40 篇文章 0 订阅

订阅专栏

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> 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> 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> 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>