sql select語句的多表查詢的使用方法

SELECT 語句用於從表中選取數據。

結果被存儲在一個結果表中（稱為結果集）。

SQL SELECT 語法
SELECT 列名稱 FROM 表名稱以及：

SELECT * FROM 表名稱注釋：SQL 語句對大小寫不敏感。SELECT 等效於 select

實例

CREATE TABLE Manufacturers
(
   ManfID CHAR(8) NOT NULL PRIMARY KEY,
   ManfName VARCHAR(30) NOT NULL
)
ENGINE=INNODB;

INSERT INTO Manufacturers
VALUES ('abc123', 'ABCqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq'),
       ('def456', 'DEFwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww'),
       ('ghi789', 'GHIeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'),
       ('jkl123', 'JKLrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr'),
       ('mno456', 'MNOttttttttttttttttttttttttttttttttttttttttttttttttttttttt');

CREATE TABLE Parts
(
   PartID SMALLINT NOT NULL PRIMARY KEY,
   PartName VARCHAR(30) NOT NULL,
   ManfID CHAR(8) NOT NULL
)
ENGINE=INNODB;

INSERT INTO Parts
VALUES (101, 'DVD burnerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'abc123'),
       (102, 'CD driveeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 'jkl123'),
       (103, '80-GB hard diskkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk', 'mno456'),
       (104, 'Mini-towerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'ghi789'),
       (105, 'Power supplyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy', 'def456'),
       (106, 'LCD monitorrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'mno456'),
       (107, 'Zip driveeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 'ghi789'),
       (108, 'Floppy driveeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 'jkl123'),
       (109, 'Network adapterrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'def456'),
       (110, 'Network hubbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', 'jkl123'),
       (111, 'Routerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'mno456'),
       (112, 'Sound cardddddddddddddddddddddddddddddddddddd', 'ghi789'),
       (113, 'Standard keyboarddddddddddddddddddddddddddddd', 'mno456'),
       (114, 'PS/2 mouseeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee', 'jkl123'),
       (115, '56-K modemmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm', 'ghi789'),
       (116, 'Display adapterrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'mno456'),
       (117, 'IDE controllerrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr', 'def456');

SELECT PartName, ManfName
FROM Parts AS p, Manufacturers as m
WHERE p.ManfID = m.ManfID
ORDER BY PartName;

EXPLAIN SELECT PartName, ManfName
FROM Parts AS p, Manufacturers as m
WHERE p.ManfID = m.ManfID
ORDER BY PartName;

帶表達式

mysql教程> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM report;
+------------------------+------------------------+
| @min_price:=MIN(price) | @max_price:=MAX(price) |
+------------------------+------------------------+
|                   2.34 |                  21.29 |
+------------------------+------------------------+
1 row in set (0.00 sec)

最簡單select查詢

mysql> SELECT firstname, lastName from employee where age > 32;
+-----------+----------+
| firstname | lastName |
+-----------+----------+
| Mike      | Harper   |
+-----------+----------+
1 row in set (0.00 sec)