sql select语句的多表查询的使用方法-百分百源码网

sql select语句的多表查询的使用方法

发布时间：01/15 来源：浏览：关键词：

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)

打赏