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练习

SQL测试表关系

示例数据库表结构

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); //三表联合加子查询