Summarize knowledge related to Mysql

Get database and table information

Generally normal programmers or DBAs will suddenly think of a series of questions like this when typing code: Who am I? Where am I? What am I doing? Where is my database? Where is my table? How did I create my table? What should I do? You might think of the SHOW DATABASES; command. But, this command is to list the databases managed by mysql. It is not a command to know where I am. Which command is it?

I found this command while browsing ancient classics:

SELECT DATABASE();

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+----------- -+
| test |
+------------+
1 row in set (0.00 sec)

mysql>

Obviously, this is a command that tells me which database I am in. Then there will definitely be a group of young people asking: If I don’t enter any database, what will be displayed?

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+-------- ----+
| NULL |
+------------+
1 row in set (0.00 sec)

mysql>

Of course it is NULL, what else can it be?

Now, we find the database (test) we are using. Then, it's time to find the table you're looking for, such as (pet). According to the records in ancient books, you should use the following command:

SHOW TABLES;

mysql> SHOW TABLES;
+--------------- -+
| Tables_in_test |
+----------------+
| event |
| pet |
+----- -----------+
2 rows in set (0.00 sec)

mysql>

And then I want to know the structure of the table. What should I do?

DESCRIBE pet;

mysql> DESCRIBE pet;
+---------+----------- --+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+--------- +-------+
| name | varchar(20) | YES | | NULL |
| owner | varchar(20) | YES | (20) | YES | | NULL | sex | char(1) | YES | | NULL | birth | date | YES | NULL date | YES | | NULL |
+---------+-------------+------+-----+---- -----+-------+
6 rows in set (0.00 sec)

mysql>

Old drivers are generally abbreviated as

DESC pet;

Field indicates the column name

Type indicates the data type of the column

Null indicates whether it can be NULL

Key indicates whether it is indexed

Default indicates the default value of the field

If the table has an index, SHOW INDEX FROM tbl_name displays the index information.

Examples of common queries

Before doing anything, you must first build one Table: Suppose there is a table (shop) to store the price () of each item () from a merchant (). (Items and merchants are used as primary keys)

The operation is as follows:

mysql> CREATE TABLE shop(

-> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,

-> dealer CHAR(20) DEFAULT '' NOT NULL,

-> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,

-> PRIMARY KEY(article, dealer));
Query OK, 0 rows affected (0.56 sec)

mysql>

Then insert some data:

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.24 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql>

Check the table:

mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

mysql>

Then we can learn the following content

Maximum value of column

Example: What is the largest item number in the shop?

The operation is as follows:

SELECT MAX(article) FROM shop;

mysql> SELECT MAX(article) FROM shop;
+------ -------+
| MAX(article) |
+--------------+
| 4 |
+---- ----------+
1 row in set (0.00 sec)

mysql>

Example: To find the most expensive product

, do as follows:

SELECT MAX(price) FROM shop;

mysql> SELECT MAX(price) FROM shop;
+-- ----------+
| MAX(price) |
+------------+
| 19.95 |
+--- ---------+
1 row in set (0.00 sec)

mysql>

You know what the MAX() function does?

The row with the maximum value of a column

Chestnut: Query the most expensive Product information

The operation is as follows:

SELECT * FROM shop WHERE price = (SELECT MAX(price) FROM shop);

mysql> SELECT * FROM shop
-> WHERE price =
-> (SELECT MAX(price) FROM shop);
+---------+--------+---- ---+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+---------+--------+-------+
1 row in set (0.00 sec)

mysql>

There is another operation:

SELECT * FROM shop ORDER BY price DESC LIMIT 1;

mysql> SELECT * FROM shop
-> ; ORDER BY price DESC
-> LIMIT 1;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0004 | D | 19.95 |
+ ---------+--------+-------+
1 row in set (0.00 sec)

mysql>

The former is a nested query, and the latter only displays one based on price sorting.

Maximum value of column: by group

Chestnut: each item (article )?

The operation is as follows:

SELECT article, MAX(price) AS price FROM shop GROUP BY article;

mysql> SELECT article, MAX( price) AS price
-> FROM shop
-> GROUP BY article;
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+
4 rows in set (0.00 sec)

mysql>

The row with the maximum inter-group value of a certain field

I don’t understand what the title is mean. . . .

Chestnut: For each item, find the dealer of the most expensive item.

The operation is as follows:

SELECT article, dealer, price
FROM shop s1
WHERE price = (SELECT MAX(price)
FROM shop s2
WHERE s1 .article = s2.article);

mysql> SELECT article, dealer, price
-> FROM shop s1
-> WHERE price = (SELECT MAX(s2.price)
-> FROM shop s2
-> WHERE s1.article = s2.article);
+---------+--------+---- ---+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+- -------+-------+
4 rows in set (0.00 sec)

The book doesn’t explain why, and I don’t quite understand it either. Those who want to know more can explain in the comment area●﹏●.

Using user variables

Chestnut: Find the item with the highest or lowest price

The operation is as follows:

SELECT @min_price:=MIN(price), @max_price:=MAX(price) FORM shop;

SELECT * FROM shop WHERE price = @min_price OR price = @max_price;

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 (0.13 sec)

mysql> SELECT * FROM shop WHERE price=@min_price OR price = @ max_price;
+---------+--------+-------+
| article | dealer | price |
+--- ------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+-- -------+--------+-------+
2 rows in set (0.09 sec)

mysql>

There will be more information about user variables later, curious bustards can Baidu.

Use foreign keys

If you don’t want to do the operation directly, there is a transmission above Door, that’s very good.

CREATE TABLE person (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    name CHAR(60) NOT NULL,

    PRIMARY KEY (id)

);

 <br>

CREATE TABLE shirt (

    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,

    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

    PRIMARY KEY (id)

);

 <br>

INSERT INTO person VALUES (NULL, 'Antonio Paz');

 <br>

SELECT @last := LAST_INSERT_ID();

 <br>

INSERT INTO shirt VALUES

(NULL, 'polo', 'blue', @last),

(NULL, 'dress', 'white', @last),

(NULL, 't-shirt', 'blue', @last);

 <br>

INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

 <br>

SELECT @last := LAST_INSERT_ID();

 <br>

INSERT INTO shirt VALUES

(NULL, 'dress', 'orange', @last),

(NULL, 'polo', 'red', @last),

(NULL, 'dress', 'blue', @last),

(NULL, 't-shirt', 'white', @last);

 <br>

SELECT * FROM person;

+----+---------------------+

| id | name                |

+----+---------------------+

|  1 | Antonio Paz         |

|  2 | Lilliana Angelovska |

+----+---------------------+

 <br>

r e

SELECT * FROM shirt;

+----+---------+--------+-------+

| id | style   | color  | owner |

+----+---------+--------+-------+

|  1 | polo    | blue   |     1 |

|  2 | dress   | white  |     1 |

|  3 | t-shirt | blue   |     1 |

|  4 | dress   | orange |     2 |

|  5 | polo    | red    |     2 |

|  6 | dress   | blue   |     2 |

|  7 | t-shirt | white  |     2 |

+----+---------+--------+-------+

 <br>

 <br>

SELECT s.* FROM person p, shirt s

 WHERE p.name LIKE 'Lilliana%'

   AND s.owner = p.id

   AND s.color <> 'white';

 <br>

+----+-------+--------+-------+

| id | style | color  | owner |

+----+-------+--------+-------+

|  4 | dress | orange |     2 |

|  5 | polo  | red    |     2 |

|  6 | dress | blue   |     2 |

+----+-------+--------+-------+



我错了，网断了。只好拷贝书上的代码了。

rrreeerrreee

mysql> show create table shirt\G

****** ********* ************ 1. row ***************************

Table: shirt
Create Table: CREATE TABLE `shirt` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`style` enum('t-shirt','polo','dress') NOT NULL,
`color` enum('red','blue','orange','white','black') NOT NULL,
`owner` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql>

The above is the detailed content of Summarize knowledge related to Mysql. For more information, please follow other related articles on the PHP Chinese website!