MySQL Learning note 008

Get information about databases and tables

Normally a normal programmer or DBA would suddenly think of a series of questions when they hit the code: Who am I? What am I doing here? Where's my database? What about my watch? How do I create a table? What should I do? You might think of show DATABASES; Command. But, this command is a list of databases managed by MySQL. I don't know where I'm going to order. Which order is it?

My left someone is looking through the ancient classics to find such an order:

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'm going to have. And then there's going to be a bunch of 25. Q: If I didn't get into any database, what would it show?

Mysql> SELECT DATABASE ();
+------------+
| DATABASE () |
+------------+
| NULL |
+------------+
1 row in Set (0.00 sec)

Mysql>

Of course it's null, what else can there be?

Now we have found the database in use (test). Then, you should look for the table, for example (PET). According to the records above, the following commands should be used:

SHOW TABLES;

Mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| Event |
| Pet |
+----------------+
2 rows in Set (0.00 sec)

Mysql>

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

DESCRIBE Pet;

Mysql> DESCRIBE Pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar (20) | YES | | NULL | |
| Owner | varchar (20) | YES | | NULL | |
| Species | varchar (20) | YES | | NULL | |
| sex | char (1) | YES | | NULL | |
| Birth | Date | YES | | NULL | |
| Death | Date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in Set (0.00 sec)

Mysql>

The old driver is generally written by Jane.

DESC Pet;

Field indicates column name

Type represents the data type of the column

NULL indicates whether NULL is possible

Key indicates whether it is indexed

Default indicates a value for the field

If the table has an index, show index from TBL_NAME displays the indexed information.

Examples of common queries

Before you do anything, you must first build a table: Suppose there is a table (shop) to store the price () of each item () of a merchant (). (item, Merchant as primary key)

The operation is as follows:

Mysql> CREATE TABLE Shop (
-Article INT (4) UNSIGNED zerofill DEFAULT ' 0000 ' not NULL,
-Dealer CHAR (+) DEFAULT ' not NULL,
, Price DOUBLE (16,2) The 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>

Take a look at the table:

Mysql> SELECT * from shop;
+---------+--------+-------+
| Article | dealer | Price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7 Rows in Set (0.00 sec)

Mysql>

And then we can learn what's behind it.

The maximum value of the column

Example: 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: Find the most expensive item

The operation is as follows:

SELECT MAX from shop;

Mysql> SELECT MAX (price) from shop;
+------------+
| MAX (Price) |
+------------+
| 19.95 |
+------------+
1 row in Set (0.00 sec)

Mysql>

Know what the max () function is doing.

The row that owns the maximum value for a column

Chestnuts: Find information on the most expensive items

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 one more action:

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, which is displayed only one according to the price sort.

Maximum column value: by group

Chestnuts: What is the maximum price for 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 that has the largest value between groups in a field

Don't understand what the title means ....

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

The operation is as follows:

SELECT article, dealer, price
From shop s1
WHERE Price = (The 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 here does not write why, I do not quite understand. The big guy in the comment area explains ●﹏●.

Using User Variables

Chestnut: Find the item with the highest price or intoxication

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-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 [e-mail protected]_price OR price = @max_price;
+---------+--------+-------+
| Article | dealer | Price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
2 rows in Set (0.09 sec)

Mysql>

About the user variables will be there, curious bustard Bustard can Baidu.

Using foreign keys

More said inadvertently directly on the operation, there is a portal, speaking very good.

CREATE TABLE Person (

ID SMALLINT UNSIGNED not NULL auto_increment,

Name CHAR () is not NULL,

PRIMARY KEY (ID)

);

CREATE TABLE Shirt (

ID SMALLINT UNSIGNED not NULL auto_increment,

Style ENUM (' t-shirt ', ' polo ', ' dress ') not NULL,

The Color ENUM (' Red ', ' blue ', ' orange ', ' white ', ' black ') is not NULL,

Owner SMALLINT UNSIGNED not NULL REFERENCES person (ID),

PRIMARY KEY (ID)

);

INSERT into Person VALUES (NULL, ' Antonio Paz ');

SELECT @last: = last_insert_id ();

INSERT into Shirt VALUES

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

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

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

INSERT into Person VALUES (NULL, ' Lilliana Angelovska ');

SELECT @last: = last_insert_id ();

INSERT into Shirt VALUES

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

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

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

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

SELECT * from person;

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

| ID | name |

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

| 1 | Antonio Paz |

| 2 | Lilliana Angelovska |

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

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 |

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

SELECT s.* from person p, shirt s

WHERE p.name like ' lilliana% '

and S.owner = P.id

and S.color <> ' white ';

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

| ID | Style | Color | Owner |

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

| 4 | Dress |     Orange | 2 |

| 5 | Polo |     Red | 2 |

| 6 | Dress |     Blue | 2 |

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



I was wrong and the net was broken. Had to copy the code on the book.

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 ') is 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>

Foreign key actually nothing to say, also on a blog thing (manual funny)

To be Continued ...

MySQL Learning note 008