/**
northWind
1、查询员工中是销售经理的人
2、查询1994年雇用的员工
3、按工龄由大到小排列员工信息
4、查询家住London的员工
5、查询还没有标明地区的员工
6、按以下格式查询家住Seattle的员工并按降序排列name字段
employeeId name=firstName lastName ‘他的职务是’ Title address city
7、查询50年代出生的员工并按员工编号排序
8、查询50年代出生的并在1993年被雇用的员工并按雇用年限由小到大排序
9、查询employees中不在‘London’、’ Seattle’、’ Tacoma’居住的员工。(northWind)
10、查询员工名字中最后一个字母是t的员工
11、查询员工名字中以A开头的员工
12、查询员工头衔中不以S开头的员工并按firstName排序
13、查询住在London或者Seattle的员工
14、查询lastName中或者有A或者有x的员工
15、查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值
16、查询“NSW”地区由销售代表负责的供应商的ID、公司名称、地区、城市和电话
17、查询“NSW”和“Melbourne”地区供应商的ID、公司名称、地区、城市和电话
18、查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值,并将查询结果按雇员的“姓氏”和“名字”字段的升序排列,“姓氏”和“名字”值相同的记录按“订单 ID”的降序排列
19、查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称
20、统计各地区客户的总数量
**/
show databases;
use northwind;
select distinct title from employees;
-- 1
select * from employees where title = 'Sales Manager';
-- 2
SELECT * FROM employees
WHERE YEAR(hireDate) = 1994;
-- 3
SELECT * FROM employees
ORDER BY hireDate DESC;
-- 4
select * from employees where City = 'London';
-- 5
select * from employees where City IS NULL;
-- 6
SELECT employeeId, CONCAT(firstName, ' ', lastName) AS name, '他的职务是' AS 职务, Title, address, city
FROM employees
WHERE city = 'Seattle'
ORDER BY lastName DESC;
-- 7
SELECT * FROM employees
WHERE YEAR(birthdate) BETWEEN 1950 AND 1959
ORDER BY employeeId;
-- 8
SELECT * FROM employees
WHERE YEAR(birthdate) BETWEEN 1950 AND 1959
AND YEAR(hireDate) = 1993
ORDER BY hireDate ASC;
-- 9
SELECT * FROM employees
WHERE city NOT IN ('London', 'Seattle', 'Tacoma');
-- 10
SELECT * FROM employees
WHERE RIGHT(firstName, 1) = 't' OR RIGHT(lastName, 1) = 't';
-- 11
SELECT * FROM employees
WHERE LEFT(firstName, 1) = 'A';
-- 12
SELECT * FROM employees
WHERE LEFT(Title, 1) <> 'S'
ORDER BY firstName;
-- 13
select * from employees where City = 'London' or City = 'Seattle';
-- 14
SELECT * FROM employees
WHERE lastName LIKE '%A%' OR lastName LIKE '%x%';
-- 15
SELECT OrderDate, OrderID, CustomerID, EmployeeID
FROM Orders
WHERE OrderDate >= '1996-07-01' AND OrderDate <= '1996-07-15';
-- 16
SELECT SupplierID, CompanyName, Region, City, Phone
FROM suppliers
WHERE Region = 'NSW' AND ContactName= '销售代表';
-- 17
SELECT SupplierID, CompanyName, Region, City, Phone
FROM suppliers
WHERE Region IN ('NSW', 'Melbourne');
-- 18
SELECT OrderDate, OrderID, CompanyName, LastName, ShipName
FROM orders,suppliers,employees
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-15'
ORDER BY LastName ASC, ShipName ASC, OrderID DESC;
-- 19
SELECT Orders.OrderID, Shippers.CompanyName, Products.ProductName
FROM Orders
JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Orders.OrderID IN (10248, 10254);
-- 请注意,上述示例假设你的订单数据存储在名为"Orders"的表中,运货商数据存储在名为"Shippers"的表中,订单详情数据存储在名为"OrderDetails"的表中,产品数据存储在名为"Products"的表中。你需要将查询语句适应你的数据库结构和表名。
-- 20
SELECT Region, COUNT(*) as 客户总数
FROM customers
GROUP BY Region;
以上需要对应自己数据库表名和内容