SQL学习-unit1-2(基础查询语句)

大小写都可以；分号终止；换行符 大间隔 和 tab 在执行SQL语句时都没有作用;

1-选择语句|the select statement(在单一表格中检索数据）

USE sql_store; SELECT * -- 选择所有列 -- from customers #从customers表中 -- where customer_id = 1 #返回一行 -- order by first_name #根据first_name给表排序

一般两条子句就可以选择；

子句的顺序是有影响的，先有select，然后from，再然后是where，最后是order by,不能改变这些子句的顺序，不然会出现语法错误；

from，where，order by 都能进行选择；

2-选择子句|the select clause

SELECT last_name, first_name, points, (points+10)*100 AS 'discount factor' -- as取名字，‘’或“”把名字括起来之后才能空一格 FROM customers

我们可以用*返回全部列或者，我们指定想要返回的列;

我们可以用算术表达式，可以选择给每列和结果集一个别名;

SELECT distinct state FROM customers

distinct在选择时可以去掉重复值;

3-where 子句|the where clause

SELECT * from customers -- where points > 3000 -- where state <>'VA' where birth_date > '1990-01-01'

比较运算符：>,>=,<,<=,=,!=/<>;

4-and,or,not运算符|the AND,OR and NOT operators

SELECT * from customers where birth_date > '1990-01-01' or points > 1000 and state = 'VA'

and有优先级;

SELECT * from customers where not (birth_date > '1990-01-01' or points > 1000)

5-in 运算符|the IN operator

SELECT * from customers -- where state = 'VA' or state = 'GA' or state = 'FL' -- where state in ('VA','GA','FL') where state not in ('VA','GA','FL')

当你想要同一系列值比较一个属性，就可以用in运算符;

6-between运算符|the between operator

SELECT * from customers -- where points >=1000 and points <= 3000 where points between 1000 and 3000

7-like 运算符|the like operator

如何检索遵循特定字符串模式的行;

SELECT * from customers -- where last_name like 'b%' -- where last_name like '%b%' -- where last_name like '%y' -- where last_name like '_____y' where last_name like 'b____y'

'_'表示一个单字符，'%'表示任意字符数;

8-regexp运算符|the REGEXP operator

是正则表达式（regular expression)的缩写;

SELECT * from customers -- where last_name like '%field%' -- where last_name regexp 'field' -- where last_name regexp '^field' -- where last_name regexp 'field$' -- where last_name regexp 'field$|mac|rose' -- where last_name regexp '[gim]e' -- where last_name regexp 'e[fmp]' where last_name regexp '[a-h]e'

"^"符号表示字符串开头;

"$"符号表示字符串末尾;

"|"符号表示逻辑或;

[abc]:匹配 a /b/c 任意单个指定字符;

[a-d]:匹配 a～d 范围内任意单个字符;

9-is null运算符|the IS NULL operator

如何搜索缺失了属性的记录;

SELECT * from customers -- where phone is null where phone is not null

10-order by子句|the ORDER BY clause

为数据排序;

SELECT first_name,last_name,10 as points from customers -- order by state desc,first_name -- order by birth_date -- order by points,first_name order by 1,2

可多字段排序：先排前面的字段，相同再排后面的字段;

可根据没被选择的列排序;

可用字母代替排序;

desc降序，asc升序;

11-limit子句|the LIMIT clause

如何限制查询返回的记录;

limit子句永远放在最后;

SELECT * from customers limit 6,3 -- 跳过前6条记录，然后获取3条记录 -- page 1:1-3 -- page 2:4-6 -- page 3:7-9