获得数据库和表的信息

一般正常的程序员或dba都会在敲代码的时候突然想到这样的一系列问题：我是谁?我在哪？我在干什么？ 我的数据库呢?我的表呢？我表怎么创建的？我该怎么办呢？你可能会想到show databases; 命令。but, 这个命令是列出由mysql管理的databases. 不是知道我再哪的命令。到底哪个命令是呢？

 

 

我左某人在翻阅上古的典籍的时候查到这样的一个命令：

SELECT DATABASE();

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

mysql>

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

 

 

显而易见，这是一个告诉我我再哪个数据库的命令。然后肯定会有一群二五仔问：我要是没有进入任何数据库那会显示什么呢？

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

mysql>

当然是NULL了，还能有什么？

 

 

现在，我们找到了正在使用的数据库（test） 。然后，该找要找的表了，比如说（pet）。 根据古籍上面的记载应该使用如下的命令：

SHOW TABLES;

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

mysql>

 

 

And then I want 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>

 

 

老司机一般都简写成

DESC pet;

Field 表示列名字

Type表示列的数据类型

Null表示能否为NULL

Key表示是否被索引

Default表示字段的默认值

如果表有索引，SHOW INDEX FROM tbl_name 显示索引的信息。

 

 

常用查询的例子

 

 

在搞事情之前，肯定要先建一个表:假定有一个表（shop）来存储某商人（）的每件物品（）的价格（）。（物品、商人作为主键）

操作如下：

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>

然后插入一些数据：

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>

查看一下表：

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>

 

然后我们就可以学习后面的内容了

列的最大值

 

 

举例：在shop中的最大的物品号？

操作如下：

SELECT MAX(article) FROM shop;

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

mysql>

 

 

举例：找最贵的商品

操作如下：

SELECT MAX(price) FROM shop;

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

mysql>

知道MAX()函数是干啥的了吧。

 

 

拥有某个列的最大值的行

 

 

栗子：查询最贵的商品的信息

操作如下：

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>

还有一种操作：

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>

前者是一个嵌套查询，后者是根据价格排序只显示一个。

 

 

列的最大值：按组

 

 

栗子：每项物品（article）的最高价格是多少?

操作如下：

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>

 

 

拥有某个字段的组间最大值的行

 

 

并不明白标题是啥意思。。。。

栗子：对每项物品，找出最贵价格的物品的经销商。

操作如下：

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)

这里书上没写为什么，自己也不是十分理解。求懂的大佬在评论区讲解●﹏●。

 

 

使用用户变量

 

 

栗子：找出价格最高或最低的物品

操作如下：

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>

关于用户变量后面会有的，好奇的鸨鸨可以百度。

 

 

使用外键

 

 

多说无意 直接上操作， 上面有一个传送门，讲的很不错了。

 

 

 

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>

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 |

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



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

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>