一般正常的程式設計師或DBA都會在敲程式碼的時候突然想到這樣的一系列問題：我是誰?我在哪裡？我在幹什麼？ 我的資料庫呢?我的表呢？我表怎麼創建的？我該怎麼辦呢？你可能會想到SHOW DATABASES; 指令。 But, 這個指令是列出由mysql管理的databases. 不是知道我再哪的指令。到底哪個命令是呢？
我左某人在翻閱上古的典籍的時候查到這樣的一個命令：
SELECT DATABASE();
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+----------- -+
| test |
+------------+
1 row in set (0.00 sec)
mysql>
#
顯而易見，這是一個告訴我我再哪個資料庫的指令。然後一定會有一群二五仔問：我要是沒有進入任何資料庫會顯示什麼呢？
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 |
| owner | varchar(20) | YES |
| owner | varchar(20) | YES |
| owner | (20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | YES | | NULL | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---- -----+-------+
Field 表示列名字
Type表示列的資料型別
Null表示能否為NULL
Key表示是否被索引
Default表示欄位的預設值
如果表有索引，SHOW INDEX FROM tbl_name 顯示索引的資訊。
<br>
常用查詢的範例
### ######在搞事情之前，一定要先建造一個表:假定有一個表（shop）來存放某商人（）的每件物品（）的價格（）。 （物品、商人作為主鍵）######操作如下：######mysql> CREATE TABLE shop(### -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT N,,### # -> dealer CHAR(20) DEFAULT '' NOT NULL,### -> price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,#ooo# PRI-> PRIle;(gtic); ###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 |
| | 1.45 |
| 1 | 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 -> 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 |# +---------+- -------+-------+
4 rows in set (0.00 sec)
這裡書上沒寫為什麼，自己也不是十分理解。求懂的大佬在評論區解說●﹏●。
mysql> SELECT @min_price:=MIN(price), @max_price:=MAX(price) FROM shop;
+-------------------- ----+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price ) |
+------------------------+------------------- -----+
| 1.25 | 19.95中 |
#+----------------+------- 19.95 |
# -----------------+
mysql> SELECT * FROM shop WHERE price=@min_price OR price = @ max_price;
+---------+--------+-------+
| article | dealer | price |
+--- ------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+-- -------+--------+-------+
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 |
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>
以上是總結和Mysql相關的知識的詳細內容。更多資訊請關注PHP中文網其他相關文章！
