SQL查询语句（一）—— 单表查询

------------恢复内容开始------------

一、查询语句

语句执行顺序

FROM ——> SELECT

SELECT select_list
FROM table_name;

查询多列数据

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees;

二、排序数据

语句执行顺序

FROM --> SELECT --> ORDER BY

SELECT 
   select_list
FROM 
   table_name
ORDER BY 
   column1 [ASC|DESC], 
   column2 [ASC|DESC],
   ...;

如果未明确指定任何排序规则，默认（升序）使用ORDER BY ASC

升序 ASC
```
ORDER BY column1 ASC;
```
降序 DESC
```
ORDER BY column1 DESC;
```

多列对结果集进行排序

ORDER BY
   column1,
   column2;

第一个名字降序排列，然后将客户的姓氏排序

SELECT
	contactLastname,
	contactFirstname
FROM
	customers
ORDER BY
	contactLastname DESC,
	contactFirstname ASC;

对结果集进行排序

SELECT 
    orderNumber, 
    orderlinenumber, 
    quantityOrdered * priceEach
FROM
    orderdetails
ORDER BY 
   quantityOrdered * priceEach DESC;

按订单状态进行排序

SELECT 
    orderNumber, 
    status
FROM
    orders
ORDER BY 
    FIELD(status,
        'In Process',
        'On Hold',
        'Cancelled',
        'Resolved',
        'Disputed',
        'Shipped');

三、过滤数据

WHERE子句，为查询返回的行指定搜索条件

语句执行顺序

FROM --> WHERE-->SELECT --> ORDER BY

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle = 'Sales Rep';

WHERE子句与AND运算符

查找职务为Sales Rep且办公代码为1的员工

SELECT 
    lastname, 
    firstname, 
    jobtitle,
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' AND 
    officeCode = 1;

WHERE子句与OR运算符

查找职务为的Sales Rep员工或使用办公代码1查找办公室的员工

SELECT 
    lastName, 
    firstName, 
    jobTitle, 
    officeCode
FROM
    employees
WHERE
    jobtitle = 'Sales Rep' OR 
    officeCode = 1
ORDER BY 
    officeCode , 
    jobTitle;

WHERE子句与BETWEEN运算符

查找办公室代码为1到3的办公室中的员工

SELECT 
    firstName, 
    lastName, 
    officeCode
FROM
    employees
WHERE
    officeCode BETWEEN 1 AND 3
ORDER BY officeCode;

WHERE和LIKE运算符

查询查找姓氏以字符串结尾的员工'son'

SELECT 
    firstName, 
    lastName
FROM
    employees
WHERE
    lastName LIKE '%son'
ORDER BY firstName;

WHERE和IN操作符

查找位于办公室的办公室代码为1的员工

SELECT 
    firstName, 
    lastName, 
    officeCode
FROM
    employees
WHERE
    officeCode IN (1 , 2, 3)
ORDER BY 
    officeCode;

WHERE和 IS NULL 操作符

在数据库世界中，NULL是一个标记，指示一条信息丢失或未知。它不等于数字0或空字符串。

SELECT 
    lastName, 
    firstName, 
    reportsTo
FROM
    employees
WHERE
    reportsTo IS NULL;

运算符

操作	描述
=	等于
<>或！=	不等于
<	少于
>	大于
<=	小于或等于
> =	大于或等于

查询使用不等于（<>）运算符查找不是的所有员工Sales Rep

SELECT 
    lastname, 
    firstname, 
    jobtitle
FROM
    employees
WHERE
    jobtitle <> 'Sales Rep';

DISTINCT，结果集去重 (DISTINCT将所有NULL值都视为相同值)

SELECT DISTINCT
    select_list
FROM
    table_name;

选择雇员的唯一姓氏

SELECT 
    DISTINCT lastname
FROM
    employees
ORDER BY 
    lastname;

DISTINCT多个字段

从customers表中获得城市和州的唯一组合

SELECT DISTINCT
    state, city
FROM
    customers
WHERE
    state IS NOT NULL
ORDER BY 
    state, 
    city;

GROUP BY在SELECT语句中使用该子句而不使用聚合函数，则该GROUP BY子句的行为类似于该DISTINCT 子句

SELECT 
    state
FROM
    customers
GROUP BY state;

DISTINCT和聚合函数

计算美国客户的唯一状态

SELECT 
    COUNT(DISTINCT state)
FROM
    customers
WHERE
    country = 'USA';

DISTINCT和LIMIT

选择表中的前五个非空唯一状态customers

SELECT DISTINCT
    state
FROM
    customers
WHERE
    state IS NOT NULL
LIMIT 5;

AND 运算符

它将两个或多个布尔表达式组合在一起，并且仅在两个表达式都为true时才返回true

AND 短路逻辑

SELECT 1 = 0 AND 1 / 0 ;

1 = 0 AND 1 / 0
---------------
0

OR 运算符

OR 短路逻辑

SELECT 1 = 1 OR 1 / 0;

1 = 1 OR 1 / 0
--------------
1

多个运算符顺序，先执行AND 再执行OR

SELECT true OR false AND false;

true OR false AND false
-----------------------
1

改变运算顺序使用括号

SELECT (true OR false) AND false;

(true OR false) AND false
-------------------------
0

获取位于美国或法国的客户

SELECT    
	customername, 
	country
FROM    
	customers
WHERE country = 'USA' OR 
      country = 'France';

位于美国或法国且信用额度大于100,000的客户

SELECT   
	customername, 
	country, 
	creditLimit
FROM   
	customers
WHERE(country = 'USA'
		OR country = 'France')
	  AND creditlimit > 100000;

IN运算符

SELECT 
    column1,column2,...
FROM
    table_name
WHERE 
	(expr|column_1) IN ('value1','value2',...);

位于美国和法国的办事处，则可以使用该IN运算符

SELECT 
    officeCode, 
    city, 
    phone, 
    country
FROM
    offices
WHERE
    country IN ('USA' , 'France');

OR运算符获得与以下查询相同

SELECT 
    officeCode, 
    city, 
    phone
FROM
    offices
WHERE
    country = 'USA' OR country = 'France';

IN与子查询一起使用

总价值是大于60,000的订单，按订单号升序

SELECT    
	orderNumber, 
	customerNumber, 
	status, 
	shippedDate
FROM    
	orders
WHERE orderNumber IN
(
	 SELECT 
		 orderNumber
	 FROM 
		 orderDetails
	 GROUP BY 
		 orderNumber
	 HAVING SUM(quantityOrdered * priceEach) > 60000
);

BETWEEN 运算符

expr [NOT] BETWEEN begin_expr AND end_expr;

使用BETWEEN运算符查找购买价格在90和之间的产品 100

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;

使用大于或等于(>=）和小于或等于（<=）运算符而不是BETWEEN运算符来获得相同的结果

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice >= 90 AND buyPrice <= 100;

查找购买价格不在$ 20到$ 100之间的产品，可将BETWEEN运算符与NOT运算符组合

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice NOT BETWEEN 20 AND 100;

使用小于（<），大于（>）和逻辑运算符（AND）来重写上面的查询

SELECT 
    productCode, 
    productName, 
    buyPrice
FROM
    products
WHERE
    buyPrice < 20 OR buyPrice > 100;

返回订单的日期为2003年1月1日至2003年3月1日 (安全起见，转成timestamp进行比较)

SELECT 
   orderNumber,
   requiredDate,
   status
FROM 
   orders
WHERE 
   requireddate BETWEEN 
     CAST('2003-01-01' AS DATETIME) AND 
     CAST('2003-01-31' AS DATETIME);

LIKE运算符(模糊匹配)

expression LIKE pattern ESCAPE escape_character

百分比（%）通配符与零个或多个字符的任何字符串匹配。
下划线（_）通配符匹配任何单个字符。

查找名字开头为a的员工

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    firstName LIKE 'a%';

雇员的姓氏以on结尾

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    lastName LIKE '%on';

姓氏包含on的所有员工

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    lastname LIKE '%on%';

找到员工，其名字开始 T与结束m，并包含之间的任何单个字符例如Tom，Tim

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    firstname LIKE 'T_m';

姓氏不是以B字符开头的员工

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    lastName NOT LIKE 'B%';

查找产品代码包含_20字符串的产品，则可以使用%\_20%

SELECT 
    productCode, 
    productName
FROM
    products
WHERE
    productCode LIKE '%\_20%';

或者可以使用$，ESCAPE子句

SELECT 
    productCode, 
    productName
FROM
    products
WHERE
    productCode LIKE '%$_20%' ESCAPE '$';

LIMIT子句

SELECT 
    select_list
FROM
    table_name
LIMIT [offset,] row_count;

这两个子句是等效的：

LIMIT row_count;

和

LIMIT 0 , row_count;

语句执行顺序

FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT

SELECT select_list
FROM table_name
ORDER BY order_expression
LIMIT offset, row_count;

获得信用度最高的前五名客户

SELECT 
    customerNumber, 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY creditLimit DESC
LIMIT 5;

ORDER BY子句中添加更多列以按唯一顺序约束行

SELECT 
    customerNumber, 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY 
    creditLimit, 
    customerNumber
LIMIT 5;

LIMIT分页

表中获取总行数

SELECT COUNT(*) FROM customers;

获取第1页的行，该行包含按客户名称排序的前10个客户

SELECT 
    customerNumber, 
    customerName
FROM
    customers
ORDER BY customerName    
LIMIT 10;

第11-20行

SELECT 
    customerNumber, 
    customerName
FROM
    customers
ORDER BY customerName    
LIMIT 10, 10;

LIMIT获得第n个最高或最低值

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT n-1, 1;

查找信用度第二高的客户

SELECT 
    customerName, 
    creditLimit
FROM
    customers
ORDER BY 
    creditLimit DESC    
LIMIT 1,1;

更为准确使用子查询

SELECT 
    customerName, 
    creditLimit
FROM
    customers
WHERE
		creditLimit = (SELECT 
                        customerName, 
                        creditLimit
                    FROM
                        customers
                    ORDER BY 
                        creditLimit DESC    
                    LIMIT 1,1));

更为准确要使用DENSE_RANK()函数（待补充）

IS NULL运算符

SELECT 1 IS NULL,  -- 0
       0 IS NULL,  -- 0
       NULL IS NULL; -- 1

检查值是否不是NULL

value IS NOT NULL

查找没有销售代表的客户

SELECT 
    customerName, 
    country, 
    salesrepemployeenumber
FROM
    customers
WHERE
    salesrepemployeenumber IS NULL
ORDER BY 
    customerName;

如果DATE或DATETIME列具有NOT NULL约束并且包含特殊日期'0000-00-00'，则可以使用IS NULL

CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT,
    title VARCHAR(255),
    begin_date DATE NOT NULL,
    complete_date DATE NOT NULL,
    PRIMARY KEY(id)
);

INSERT INTO projects(title,begin_date, complete_date)
VALUES('New CRM','2020-01-01','0000-00-00'),
      ('ERP Future','2020-01-01','0000-00-00'),
      ('VR','2020-01-01','2030-01-01');

SELECT * 
FROM projects
WHERE complete_date IS NULL;

posted @ 2021-03-04 15:34 深圳-逸遥阅读(820) 评论(0) 编辑收藏举报

刷新页面返回顶部

深圳-逸遥

SQL查询语句（一）—— 单表查询

一、查询语句

二、排序数据

三、过滤数据

公告