1.1 在单一表格中检索数据

目录

1. 例子引入

（1）使用USE运行数据库

（2）使用SELECT获取数据库中的列

 （3）使用FROM获取表

（4）使用WHERE语句筛选结果

（5）使用ORDER BY进行排序

2. SQL语句具体使用

（1）SELECT语句

①SELECT 

②AS

③DISTINCT

【练习】写一段SQL，返回数据库中所有产品，结果集中需要包括"name"，“ unit price”和“new price”（new price= unit price * 1.1）

（2）WHERE语句

【练习】写一段SQL，查询2019年下的订单

（3）AND,OR,NOT运算符

①AND

②OR

（4）IN运算符

【练习】 写一段SQL，得到现货库存数量为49,38，72的产品

（5）BETWEEN运算符

【练习】写一段SQL，查询出生于1990年1月1日~2000年1月1日的顾客。

（6）LIKE运算符

【练习】写一段SQL，查询搜集尾号为0的顾客。

（7）REGEXP运算符

【练习】写一段SQL，获取first_name是ELKA或AMBUR的顾客。

【练习】写一段SQL，获取flast_name以ey和on结尾的顾客。

【练习】写一段SQL，获取last_name以my开头或者包含se的顾客。

【练习】写一段SQL，获取last_name是包含b，后面接r或u。

（8）IS NULL运算符

【练习】写一段SQL，查询获取所有还未发货的订单。

（9）ORDER BY运算符

【练习】写一段SQL，查询order_items表中order_id=2的信息，按照product_id*quantity的降序进行返回。

（10）LIMIT语句

【练习】写一段SQL，获取前三名忠实客户。

---

1. 例子引入

首先查看左侧导航面板可观察到，没有任何数据库被选中进行查询。

因此，第一步需要获取数据库中的数据。

（1）使用USE运行数据库

USE sql_store;

注：最好使用大写去写SQL的关键词，其他内容使用小写 

运行该代码，可看到【sql_store】数据库被加粗。

双击【sql_store】也可打开该数据库。

（2）使用SELECT获取数据库中的列

 SELECT *可以获取到所有的列

SELECT *；

 （3）使用FROM获取表

FROM customers;

表示从所有列*中获取我们需要的【customers】表，点击运行则可打开该表。

（4）使用WHERE语句筛选结果

如要得到id为1的顾客

USE sql_store;

SELECT *
	FROM customers 
	WHERE customer_id = 1

返回id为1的顾客信息。

（5）使用ORDER BY进行排序

给所有的顾客按照first_name进行排序。

USE sql_store;

SELECT *
	FROM customers 
	-- WHERE customer_id = 1
    ORDER BY first_name;

则可以获得按照first_name进行排序的顾客信息。

---

2. SQL语句具体使用

（1）SELECT语句

①SELECT 

可以指定想要打开的列（如果数据库中包括很多列、上百万条数据，减轻服务器、网络压力）。

USE sql_store;

SELECT 
	first_name, 
    last_name,
    points,
    points+10 AS discount_factor,
    points * 10 +100 AS "discount factor"
	FROM customers;

其中，first_name和last_name的顺序会影响返回的列的顺序；也可以对某一列进行运算（加减法、乘除法、取模运算 ），如points列+10；

②AS

可对新的列进行命名，新名称中包含空格时需加引号 。

③DISTINCT

删去重复项

可对比加入DISTINCT前后的结果，左侧有两个VA，右侧只显示一个VA。

【练习】写一段SQL，返回数据库中所有产品，结果集中需要包括"name"，“ unit price”和“new price”（new price= unit price * 1.1）

USE sql_store;

SELECT 
	name,
    unit_price,
    unit_price*1.1 AS "new price"
	FROM products;

（2）WHERE语句

如，需要选择【customers】表中积分高于3000的顾客。

SELECT *
	FROM customers
    WHERE points > 3000;

可观察到积分大于3000的顾客有2名。

如，需要选择【customers】表中state不为VA的顾客。

SELECT *
	FROM customers
    WHERE state != 'VA'

如，需要选择【customers】表中出生于1990年1月1日以后的的顾客。

SELECT *
	FROM customers
    WHERE birth_date > '1900-01-01'

注：SQL的默认日期形式为“xxxx-xx-xx”

【练习】写一段SQL，查询2019年下的订单

SELECT *
	FROM orders
    WHERE order_date >= '2019-01-01'

（3）AND,OR,NOT运算符

①AND

如，需要选择【customers】表中出生于1990年1月1日以后的的顾客，同时还需要他们的积分大于1000。

SELECT *
	FROM customers
    WHERE birth_date >= '1990-01-01' AND points > 1000

②OR

如，需要选择【customers】表中出生于1990年1月1日以后的的顾客，或者积分大于1000的顾客。

注：AND具有优先级

如，需要选择【customers】表中除（出生于1990年1月1日以后的的顾客，或者积分大于1000的顾客）以外。

SELECT *
	FROM customers
    WHERE NOT (birth_date >= '1990-01-01' OR points > 1000)

【练习】写一段SQL，从order_items中，获取订单号为6的项目，并且项目的总价格大于30。

SELECT *
	FROM order_items
    WHERE order_id = 6 AND (unit_price * quantity > 30)

（4）IN运算符

如，需要选择【customers】表中位于VA、GA和FL的顾客。

SELECT *
	FROM customers
    WHERE state IN ('VA','GA','FL')

注：SQL中不能将字符串同布尔表达式进行结合。

如，需要选择【customers】表中除（VA、GA和FL以外）的顾客。

SELECT *
	FROM customers
    WHERE state IN ('VA','GA','FL')

【练习】 写一段SQL，得到现货库存数量为49,38，72的产品

SELECT *
	FROM products
    WHERE quantity_in_stock IN (49, 38, 72)

（5）BETWEEN运算符

 如，需要选择【customers】表中积分介于1000和3000之间的顾客。

SELECT *
	FROM customers
    WHERE points  BETWEEN 1000 AND 3000

【练习】写一段SQL，查询出生于1990年1月1日~2000年1月1日的顾客。

SELECT *
	FROM customers
    WHERE birth_date  BETWEEN '1990-01-01' AND '2000-01-01';

（6）LIKE运算符

LIKE运算符可用于检索遵循特定字符串模式的行。

 如，需要选择【customers】表中获取姓氏以“B”开头的顾客。

SELECT *
	FROM customers
    WHERE last_name LIKE 'B%'; -- % 表示任意字符

如，需要选择【customers】表中获取姓氏以“Brush”开头的顾客。

SELECT *
	FROM customers
    WHERE last_name LIKE 'Brush%';

如，需要选择【customers】表中获取姓氏中含有B”的顾客。

SELECT *
	FROM customers
    WHERE last_name LIKE '%B%';

注：如果是将%换成_，则是匹配一个字符。如，B_____y，则表示以B为头，中间五个字符，末尾为y进行匹配。

综上：

        %代表任意字符

        _代表一个字符

【练习】写一段SQL，查询地址里包括trail或avenue.

SELECT *
	FROM customers
    WHERE 
		address LIKE '%trail%' OR 
		address LIKE '%avenue%' ; 

【练习】写一段SQL，查询搜集尾号为0的顾客。

SELECT *
	FROM customers
    WHERE phone LIKE '%9';

（7）REGEXP运算符

REGEXP为正则化表达regular expression的缩写，可以搜索更为复杂的模式。

如，需要选择【customers】表中获取姓氏含有“field”的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP 'field' ;

如，需要选择【customers】表中获取姓氏中以“field”开头的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP '^field' ;

如，需要选择【customers】表中获取姓氏中以“field”结尾的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP 'field$' ;

如，需要选择【customers】表中获取姓氏含有“field”和"mac"的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP 'field|mac' ; -- "|"表示多个搜寻模式

如，需要选择【customers】表中获取姓氏含有“e“，同时“g”、“i”、"m"任意一个在“e”之前的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP '[gim]e' ;

也就是说，[]可以选择范围。如，选择a到h，则可以写成[a-h]。

综上：

        ^代表字符串开头

        $代表字符串结尾

        |代表逻辑上的OR

        []匹配任意在括号里列举的单字符

        [x-y]代表x和y内的任意字母

【练习】写一段SQL，获取first_name是ELKA或AMBUR的顾客。

SELECT *
	FROM customers
    WHERE 
		first_name REGEXP 'ELKA|AMBUR' ;     

【练习】写一段SQL，获取flast_name以ey和on结尾的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP 'ey$|on$' ;     

【练习】写一段SQL，获取last_name以my开头或者包含se的顾客。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP '^my|se' ;    

【练习】写一段SQL，获取last_name是包含b，后面接r或u。

SELECT *
	FROM customers
    WHERE 
		last_name REGEXP 'b[ru]' ;            

（8）IS NULL运算符

IS NULL可用于搜索确实了属性的记录。

如，需要选择【customers】表,phone列中获取缺失信息的顾客。

SELECT *
	FROM customers
    WHERE phone IS NULL;

【练习】写一段SQL，查询获取所有还未发货的订单。

SELECT *
	FROM orders
    WHERE shipped_date IS NULL;

（9）ORDER BY运算符

该语句可以选取所有【customers】表中的顾客，按照某一要求进行排序。

注：默认排序按照customer_id。

原因：

点击此处进入【customers】表的设计模式。

在关系型数据库中，每一个表都有一个主键列，该列的值能够唯一识别表里的信息。

如，让【customers】表的信息按照first_name进行升序排序。

SELECT *
	FROM customers
    ORDER BY first_name;

如，让【customers】表的信息按照first_name进行降序排序。

SELECT *
	FROM customers
    ORDER BY first_name DESC;

如，让【customers】表的信息按照first_name、州的名字进行多列排序。

SELECT *
	FROM customers
    ORDER BY first_name, state;

如，让【customers】表的信息按照first_name、last_name顺序进行多列排序。

SELECT first_name, last_name
	FROM customers
    ORDER BY 1,2;

注：一般不使用该方法。

【练习】写一段SQL，查询order_items表中order_id=2的信息，按照product_id*quantity的降序进行返回。

SELECT *
	FROM order_items
    WhERE order_id= 2 
    ORDER BY quantity*unit_price DESC;

（10）LIMIT语句

LIMIT可用于限定查询返回的记录。

如，让【customers】表的信息返回前三个值。

SELECT *
	FROM customers
    LIMIT 3;

如，【customers】表的信息展示在3页，每页返回三个值，分别为第一页1-3，第二页4-6，第三页7-9，现在要求返回第三页的内容7-9，也就是说跳过前6个值。

SELECT *
	FROM customers
    LIMIT 6,3; -- 其中6为偏移量

【练习】写一段SQL，获取前三名忠实客户。

SELECT *
	FROM customers
    ORDER BY points DESC
    LIMIT 3;