MySQL Examples of Common Queries-优快云博客

本文链接：https://blog.youkuaiyun.com/u011868279/article/details/127275135

mysql> CREATE DATABASE carrefour;
Query OK, 1 row affected (0.01 sec)
mysql> use carrefour;
Database changed
mysql> 
mysql> CREATE TABLE shop (
    ->     article INT UNSIGNED  DEFAULT '0000' NOT NULL,
    ->     dealer  CHAR(20)      DEFAULT ''     NOT NULL,
    ->     price   DECIMAL(16,2) DEFAULT '0.00' NOT NULL,
    ->     PRIMARY KEY(article, dealer));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO shop VALUES
    ->     (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    ->     (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select database();
+------------+
| database() |
+------------+
| carrefour  |
+------------+
1 row in set (0.00 sec)

mysql> select * from shop order by article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql>

1.The Maximum Value for a Column

mysql> select max(article) as article from shop; 
+---------+
| article |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

mysql>

2 The Row Holding the Maximum of a Certain Column

mysql> select article,dealer,price from shop where price=(select max(price) from shop);
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.price < s2.price
    -> WHERE s2.article IS NULL;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | A      |  3.45 |
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | B      |  1.45 |
|       3 | C      |  1.69 |
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql> select article,dealer,price
    -> from shop
    -> order by price desc
    -> limit 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       4 | D      | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql>

3 Maximum of Column per Group

mysql> select article,max(price) as price 
    -> from shop
    -> group by article
    -> order by article;
+---------+-------+
| article | price |
+---------+-------+
|       1 |  3.99 |
|       2 | 10.99 |
|       3 |  1.69 |
|       4 | 19.95 |
+---------+-------+
4 rows in set (0.00 sec)

mysql>

4 The Rows Holding the Group-wise Maximum of a Certain Column

Task: For each article, find the dealer or dealers with the most expensive price.

mysql> select s1.article,s1.dealer,s1.price from shop s1 where s1.price=(select max(s2.price) from shop s2 where s2.article = s1.article) order by article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

mysql> SELECT s1.article, s1.dealer, s1.price
    -> FROM shop s1
    -> LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
    -> WHERE s2.article IS NULL
    -> ORDER BY s1.article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

mysql> WITH s1 AS (
    ->    SELECT article, dealer, price,
    ->           RANK() OVER (PARTITION BY article
    ->                            ORDER BY price DESC
    ->                       ) AS `Rank`
    ->      FROM shop
    -> )
    -> SELECT article, dealer, price
    ->   FROM s1
    ->   WHERE `Rank` = 1
    -> ORDER BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       1 | B      |  3.99 |
|       2 | A      | 10.99 |
|       3 | C      |  1.69 |
|       4 | D      | 19.95 |
+---------+--------+-------+
4 rows in set (0.00 sec)

mysql>

5 Using User-Defined Variables

mysql> 
mysql> select @min_price:=min(price),@max_price:=max(price) from shop;
+------------------------+------------------------+
| @min_price:=min(price) | @max_price:=max(price) |
+------------------------+------------------------+
|                   1.25 |                  19.95 |
+------------------------+------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> select * from shop where price=@min_price or price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|       3 | D      |  1.25 |
|       4 | D      | 19.95 |
+---------+--------+-------+
2 rows in set (0.00 sec)

mysql>

6 Using Foreign Keys

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

mysql> CREATE TABLE parent (
    ->     id INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=INNODB;

Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> 
mysql> CREATE TABLE child (
    ->     id INT,
    ->     parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     FOREIGN KEY (parent_id)
    ->         REFERENCES parent(id)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> insert into parent (id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from parent;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> insert into child (id,parent_id) values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into child (id,parent_id) values(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`carrefour`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
mysql> # The operation fails because the specified parent_id value does not exist in the parent table
mysql> DELETE FROM parent WHERE id= 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`carrefour`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
mysql> # This operation fails because the record in the child table contains the referenced id (parent_id) value
mysql> DROP TABLE child;

Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE child (
    ->     id INT,
    ->     parent_id INT,
    ->     INDEX par_ind (parent_id),
    ->     FOREIGN KEY (parent_id)
    ->         REFERENCES parent(id)
    ->         ON UPDATE CASCADE
    ->         ON DELETE CASCADE
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO child (id,parent_id) VALUES(1,1),(2,1),(3,1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         1 |
|    2 |         1 |
|    3 |         1 |
+------+-----------+
3 rows in set (0.01 sec)

mysql> UPDATE parent SET id = 2 WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from parent;
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql> SELECT * FROM child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |         2 |
|    2 |         2 |
|    3 |         2 |
+------+-----------+
3 rows in set (0.00 sec)

mysql> DELETE FROM parent WHERE id= 2;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM child;
Empty set (0.00 sec)

mysql>

7 Searching on Two Keys

OR AND

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'


SELECT field1_index, field2_index
    FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index
    FROM test_table WHERE field2_index = '1';

8 Calculating Visits Per Day

mysql> 
mysql> CREATE TABLE t1 (year YEAR, month INT UNSIGNED,
    ->              day INT UNSIGNED);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
    ->             (2000,2,23),(2000,2,23);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
    ->        GROUP BY year,month;
+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |     1 |    3 |
| 2000 |     2 |    2 |
+------+-------+------+
2 rows in set (0.01 sec)

mysql> # The query calculates how many different days appear in the table for each year/month combination, with automatic removal of duplicate entries.
mysql>

9 Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

mysql> 
mysql> create table animals(
    -> id mediumint not null auto_increment,
    -> name char(30) not null,
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO animals (name) VALUES
    ->     ('dog'),('cat'),('penguin'),
    ->     ('lax'),('whale'),('ostrich');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

mysql> INSERT INTO animals (id,name) VALUES(0,'groundhog');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | lax       |
|  5 | whale     |
|  6 | ostrich   |
|  7 | groundhog |
+----+-----------+
7 rows in set (0.00 sec)

mysql> INSERT INTO animals (id,name) VALUES(NULL,'squirrel');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM animals;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | dog       |
|  2 | cat       |
|  3 | penguin   |
|  4 | lax       |
|  5 | whale     |
|  6 | ostrich   |
|  7 | groundhog |
|  8 | squirrel  |
+----+-----------+
8 rows in set (0.00 sec)

mysql> INSERT INTO animals (id,name) VALUES(100,'rabbit');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
+-----+-----------+
9 rows in set (0.00 sec)

mysql> INSERT INTO animals (id,name) VALUES(NULL,'mouse');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+
10 rows in set (0.00 sec)

mysql>

To start with an AUTO_INCREMENT value other than 1, set that value with CREATE TABLE or ALTER TABLE , like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

10.Using MySQL with Apache

change the Apache logging format to be easily readable by MySQL by putting the following into the Apache configuration file:

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

# To load a log file in that format into MySQL, you can use a statement something like this:

LOAD DATA INFILE '/local/access_log' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'