MySQL學習筆記No.03

示例數據庫表結構

CREATE TABLE `customers` (
`customerNumber` int(11) NOT NULL,
`customerName` varchar(50) NOT NULL,
`contactLastName` varchar(50) NOT NULL,
`contactFirstName` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`salesRepEmployeeNumber` int(11) DEFAULT NULL,
`creditLimit` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`customerNumber`),
KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL,
`lastName` varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`extension` varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
`officeCode` varchar(10) NOT NULL,
`reportsTo` int(11) DEFAULT NULL,
`jobTitle` varchar(50) NOT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `reportsTo` (`reportsTo`),
KEY `officeCode` (`officeCode`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_no` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `offices` (
`officeCode` varchar(10) NOT NULL,

`city` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`postalCode` varchar(15) NOT NULL,
`territory` varchar(10) NOT NULL,
PRIMARY KEY (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `orderdetails` (
`orderNumber` int(11) NOT NULL,
`productCode` varchar(15) NOT NULL,
`quantityOrdered` int(11) NOT NULL,
`priceEach` decimal(10,2) NOT NULL,
`orderLineNumber` smallint(6) NOT NULL,
PRIMARY KEY (`orderNumber`,`productCode`),
KEY `productCode` (`productCode`),
CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`),
CONSTRAINT `orderdetails_ibfk_2` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `orders` (
`orderNumber` int(11) NOT NULL,
`orderDate` date NOT NULL,
`requiredDate` date NOT NULL,
`shippedDate` date DEFAULT NULL,
`status` varchar(15) NOT NULL,
`comments` text,
`customerNumber` int(11) NOT NULL,
PRIMARY KEY (`orderNumber`),
KEY `customerNumber` (`customerNumber`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `payments` (
`customerNumber` int(11) NOT NULL,
`checkNumber` varchar(50) NOT NULL,
`paymentDate` date NOT NULL,
`amount` decimal(10,2) NOT NULL,
PRIMARY KEY (`customerNumber`,`checkNumber`),
CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `productlines` (
`productLine` varchar(50) NOT NULL,
`textDescription` varchar(4000) DEFAULT NULL,
`htmlDescription` mediumtext,
`image` mediumblob,
PRIMARY KEY (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `products` (
`productCode` varchar(15) NOT NULL DEFAULT '' COMMENT '產品代碼',
`productName` varchar(70) NOT NULL COMMENT '產品名稱',
`productLine` varchar(50) NOT NULL COMMENT '產品線',
`productScale` varchar(10) NOT NULL,
`productVendor` varchar(50) NOT NULL,
`productDescription` text NOT NULL,
`quantityInStock` smallint(6) NOT NULL COMMENT '庫存',
`buyPrice` decimal(10,2) NOT NULL COMMENT '價格',
`MSRP` decimal(10,2) NOT NULL COMMENT '建議零售價',
PRIMARY KEY (`productCode`),
KEY `productLine` (`productLine`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
​
DROP TABLE IF EXISTS `tokens`;
CREATE TABLE `tokens` (
`s` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表關係

表關係
- customers`: 存儲客戶的數據。
- `products`: 存儲汽車的數據。
- `productLines`: 存儲產品類別數據。
- `orders`: 存儲客戶訂購的銷售訂單。
- `orderdetails`: 存儲每個銷售訂單的訂單產品數據項。
- `payments`: 存儲客戶訂單的付款數據信息。

- `employees`: 存儲所有員工信息以及組織結構，例如，直接上級(誰向誰報告工作)。
- `offices`: 存儲銷售處數據，類似於各個分公司
​
productlines.productLine=products.productLine
products.productCode=orderdetails.productCode
orderdetails.orderNumber=orders.orderNumber
orders.customerNumber=customers.customerNumber
customers.customerNumber=payments.customerNumber
customers.salesRepEmployeeNumber=employees.employeeNumber
employees.officeCode=offices.officeCode
​
​

單表查詢

過濾條件 where、AND、OR、BETWEEN、LIKE、IN、LIMIT、IS NULL
​
> show tables;
> show create table customers; //查看錶結構
> select * from customers limit 1 ; //用*記得加約束條件LIMIT
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber>490 ; //WHERE條件
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.city='London' ; //WHERE條件
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber between 480 and 490 ; //BETWEEN AND 查詢
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber>480 and customers.customerNumber<490 ; //AND多條件查詢
​
> select * from customers where customers.phone like '%13%' ; //LIKE模糊查詢
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber=480 or customers.customerNumber=489; //OR查詢
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber IN (489,481,486,487); //IN查詢
>
​
排序 ORDER BY
select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber IN (489,481,486,487) ORDER BY customers.customerNumber; //ORDER BY
​

分組 GROUP BY 、HAVING
SELECT status,count(orderNumber) FROM orders GROUP BY status; //GROUP BY分組
SELECT status,count(orderNumber) FROM orders GROUP BY status having count(orderNumber) = 6; //HAVING
SELECT status FROM orders GROUP BY status having count(orderNumber) = 6;
​

聯合查詢（兩表）

聯合查詢(多表查詢) INNER JOIN、LEFT JOIN、RIGHT JOIN、
不帶條件的聯合查詢
select * from productlines as cs left join products as ps on cs.productLine=ps.productLine limit 1\\G
select * from products left join orderdetails on products.productCode=orderdetails.productCode limit 1\\G
select * from orderdetails left join orders on orderdetails.orderNumber=orders.orderNumber limit 1 \\G
select * from orders left join customers on orders.customerNumber=customers.customerNumber limit 1 \\G
select * from customers left join payments on customers.customerNumber=payments.customerNumber limit 1 \\G
select * from customers left join employees on customers.salesRepEmployeeNumber=employees.employeeNumber limit 1 \\G
select * from employees left join offices on employees.officeCode=offices.officeCode limit 1 \\G
​
​
帶條件的聯合查詢
select * from employees left join offices on employees.officeCode=offices.officeCode where email like '%tiger%';
​
​

多表聯合查詢

select * from customers left join orders on orders.customerNumber=customers.customerNumber left join orderdetails on orderdetails.orderNumber=orders.orderNumber where orderdetails.productCode IN (select products.productCode from products where products.buyPrice>100); //三表聯合加子查詢

SELECT精進操作開始

模擬企業實際數據庫結構進行select練習

示例數據庫表結構

CREATE TABLE `customers` (
`customerNumber` int(11) NOT NULL,
`customerName` varchar(50) NOT NULL,
`contactLastName` varchar(50) NOT NULL,
`contactFirstName` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`salesRepEmployeeNumber` int(11) DEFAULT NULL,
`creditLimit` decimal(10,2) DEFAULT NULL,
PRIMARY KEY (`customerNumber`),
KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`),
CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`salesRepEmployeeNumber`) REFERENCES `employees` (`employeeNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `employees` (
`employeeNumber` int(11) NOT NULL,
`lastName` varchar(50) NOT NULL,
`firstName` varchar(50) NOT NULL,
`extension` varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
`officeCode` varchar(10) NOT NULL,
`reportsTo` int(11) DEFAULT NULL,
`jobTitle` varchar(50) NOT NULL,
PRIMARY KEY (`employeeNumber`),
KEY `reportsTo` (`reportsTo`),
KEY `officeCode` (`officeCode`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`reportsTo`) REFERENCES `employees` (`employeeNumber`),
CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`officeCode`) REFERENCES `offices` (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`item_no` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `offices` (
`officeCode` varchar(10) NOT NULL,

`city` varchar(50) NOT NULL,
`phone` varchar(50) NOT NULL,
`addressLine1` varchar(50) NOT NULL,
`addressLine2` varchar(50) DEFAULT NULL,
`state` varchar(50) DEFAULT NULL,
`country` varchar(50) NOT NULL,
`postalCode` varchar(15) NOT NULL,
`territory` varchar(10) NOT NULL,
PRIMARY KEY (`officeCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `orderdetails` (
`orderNumber` int(11) NOT NULL,
`productCode` varchar(15) NOT NULL,
`quantityOrdered` int(11) NOT NULL,
`priceEach` decimal(10,2) NOT NULL,
`orderLineNumber` smallint(6) NOT NULL,
PRIMARY KEY (`orderNumber`,`productCode`),
KEY `productCode` (`productCode`),
CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`),
CONSTRAINT `orderdetails_ibfk_2` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `orders` (
`orderNumber` int(11) NOT NULL,
`orderDate` date NOT NULL,
`requiredDate` date NOT NULL,
`shippedDate` date DEFAULT NULL,
`status` varchar(15) NOT NULL,
`comments` text,
`customerNumber` int(11) NOT NULL,
PRIMARY KEY (`orderNumber`),
KEY `customerNumber` (`customerNumber`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `payments` (
`customerNumber` int(11) NOT NULL,
`checkNumber` varchar(50) NOT NULL,
`paymentDate` date NOT NULL,
`amount` decimal(10,2) NOT NULL,
PRIMARY KEY (`customerNumber`,`checkNumber`),
CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `productlines` (
`productLine` varchar(50) NOT NULL,
`textDescription` varchar(4000) DEFAULT NULL,
`htmlDescription` mediumtext,
`image` mediumblob,
PRIMARY KEY (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
CREATE TABLE `products` (
`productCode` varchar(15) NOT NULL DEFAULT '' COMMENT '產品代碼',
`productName` varchar(70) NOT NULL COMMENT '產品名稱',
`productLine` varchar(50) NOT NULL COMMENT '產品線',
`productScale` varchar(10) NOT NULL,
`productVendor` varchar(50) NOT NULL,
`productDescription` text NOT NULL,
`quantityInStock` smallint(6) NOT NULL COMMENT '庫存',
`buyPrice` decimal(10,2) NOT NULL COMMENT '價格',
`MSRP` decimal(10,2) NOT NULL COMMENT '建議零售價',
PRIMARY KEY (`productCode`),
KEY `productLine` (`productLine`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​
​
​
DROP TABLE IF EXISTS `tokens`;
CREATE TABLE `tokens` (
`s` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表關係

表關係
- customers`: 存儲客戶的數據。
- `products`: 存儲汽車的數據。
- `productLines`: 存儲產品類別數據。
- `orders`: 存儲客戶訂購的銷售訂單。
- `orderdetails`: 存儲每個銷售訂單的訂單產品數據項。
- `payments`: 存儲客戶訂單的付款數據信息。

- `employees`: 存儲所有員工信息以及組織結構，例如，直接上級(誰向誰報告工作)。
- `offices`: 存儲銷售處數據，類似於各個分公司
​
productlines.productLine=products.productLine
products.productCode=orderdetails.productCode
orderdetails.orderNumber=orders.orderNumber
orders.customerNumber=customers.customerNumber
customers.customerNumber=payments.customerNumber
customers.salesRepEmployeeNumber=employees.employeeNumber
employees.officeCode=offices.officeCode
​
​

單表查詢

過濾條件 where、AND、OR、BETWEEN、LIKE、IN、LIMIT、IS NULL
​
> show tables;
> show create table customers; //查看錶結構
> select * from customers limit 1 ; //用*記得加約束條件LIMIT
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber>490 ; //WHERE條件
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.city='London' ; //WHERE條件
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber between 480 and 490 ; //BETWEEN AND 查詢
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber>480 and customers.customerNumber<490 ; //AND多條件查詢
​
> select * from customers where customers.phone like '%13%' ; //LIKE模糊查詢
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber=480 or customers.customerNumber=489; //OR查詢
​
> select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber IN (489,481,486,487); //IN查詢
>
​
排序 ORDER BY
select customerNumber,contactLastName,city,creditLimit from customers where customers.customerNumber IN (489,481,486,487) ORDER BY customers.customerNumber; //ORDER BY
​

分組 GROUP BY 、HAVING
SELECT status,count(orderNumber) FROM orders GROUP BY status; //GROUP BY分組
SELECT status,count(orderNumber) FROM orders GROUP BY status having count(orderNumber) = 6; //HAVING
SELECT status FROM orders GROUP BY status having count(orderNumber) = 6;
​

聯合查詢（兩表）

聯合查詢(多表查詢) INNER JOIN、LEFT JOIN、RIGHT JOIN、
不帶條件的聯合查詢
select * from productlines as cs left join products as ps on cs.productLine=ps.productLine limit 1\\G
select * from products left join orderdetails on products.productCode=orderdetails.productCode limit 1\\G
select * from orderdetails left join orders on orderdetails.orderNumber=orders.orderNumber limit 1 \\G
select * from orders left join customers on orders.customerNumber=customers.customerNumber limit 1 \\G
select * from customers left join payments on customers.customerNumber=payments.customerNumber limit 1 \\G
select * from customers left join employees on customers.salesRepEmployeeNumber=employees.employeeNumber limit 1 \\G
select * from employees left join offices on employees.officeCode=offices.officeCode limit 1 \\G
​
​
帶條件的聯合查詢
select * from employees left join offices on employees.officeCode=offices.officeCode where email like '%tiger%';
​
​

多表聯合查詢

select * from customers left join orders on orders.customerNumber=customers.customerNumber left join orderdetails on orderdetails.orderNumber=orders.orderNumber where orderdetails.productCode IN (select products.productCode from products where products.buyPrice>100); //三表聯合加子查詢