1-选择语句|The SELECT Statement
- USE sql_store;
- SELECT *
- FROM customers
- WHERE customer_id=1
- ORDER BY first_name
- “USE sql_store”表示选择当前数据库;或者双击选中
- Ctrl+Shift+Enter 执行快捷键
- “--WHERE customer_id=1”代表注释,SQL不会执行
2-选择子句|The SELECT Clause
- SELECT
- last_name,
- first_name,
- points+10 AS discount_factor
- FROM customers
- +、-、*、/、%(除法的余数)
- discount_factor等同于’discount factor’
- 改动表格时,Click Apply twice.
- 当想得到唯一值时,SELECT DISTANT state FROM customers
3-WHERE子句|The WHERE Clause
- SELECT *
- FROM customers
- WHERE state=’VA’
- 注意这里给VA加了引号,因为这是字符串,即文本数据;VA无谓大小写
- WHERE birth_date>’1990-01-01’——此时也要用引号,即使日期不算字符串
- 比较运算符包括>、 < 、>= 、<= 、=、!=、<>(后两者都是不等)
4-AND,OR,NOT运算符|The AND,OR and NOT Operators
- SELECT *
- FROM customers
- WHERE (NOT) (birth_date>’1990-01-01’ OR points>1000 AND state=’VA’)
- 以上同义于WHERE birth_date<=’1990-01-01’ AND (points<=1000 OR state!=’VA’)
- AND:两个条件均满足;OR:两个条件满足其一。AND相比于OR有更高优先级,必要时加括号
5-IN运算符|The IN Operator
- SELECT *
- FROM customers
- WHERE state IN (’VA’ ,’GA’ ,’LA’)
- 以上同义于WHERE state =’VA’ OR ’GA’ OR ’LA’吗?
否,SQL中不能将字符串同Boolean表达式结合。
正确表达为WHERE state=’VA’ OR state=’GA’ OR state=’LA’
6-BETWEEN运算符|The BETWEEN Operator
- SELECT *
- FROM customers
- WHERE points BETWEEN 1000 AND 3000
- 以上同义于WHERE points>=1000 AND Points <=3000
7-LIKE运算符|The LIKE Operator
- SELECT *
- FROM customers
- WHERE last_name LIKE ‘b%’
- 以上得到所有姓氏以B打头的顾客,B无谓大小写
- SELECT *
- FROM customers
- WHERE last_name LIKE ‘%b%’
- 以上得到所有姓氏含B的顾客,B无谓大小写
- SELECT *
- FROM customers
- WHERE last_name LIKE ‘_ _ _b’
- 以上“_”代表单字符
8-REGEXP运算符|The REGEXP Operator(正则表达式,regular expression)
- SELECT *
- FROM customers
- WHERE last_name REGEXP ‘field’
- 以上同义于WHERE last_name Like ‘%field%’
- SELECT *
- FROM customers
- WHERE last_name REGEXP ‘^field’
- 用^表示字符串的开头,表示姓氏必须以field打头
- WHERE last_name REGEXP ‘field$’
- 用$表示字符串的开头,表示姓氏必须以field结尾
- WHERE last_name REGEXP ‘field|mac|^rose’
- 以上“|”代表OR
- WHERE last_name REGEXP ‘[gim]e’
- 以上代表在e前要有g或者i或者m
- WHERE last_name REGEXP ‘[a-h]e’
- 以上代表在e之前可以有从a到h的任意字母
9-IS NULL运算符|The IS NULL Operator(如何搜索缺失了属性的记录)
- SELECT *
- FROM customers
- WHERE phone IS (NOT) NULL
- 以上表示查找缺失phone number的customer
10-ORDER BY子句|The ORDER BY Clause
首先解释下为什么第一列的customer_id是默认排序列:
点击工具图标,得到:
此时页面可以操作任意列的增删改
第一行的customer_id左侧的黄键表示此列为这张表的主键列,该列的值要能够唯一识别表里的记录。这张表默认以customer_id进行排序
- SELECT *
- FROM customers
- ORDER BY first_name (DESC)
- DESC代表降序
- SELECT *
- FROM customers
- ORDER BY state,first_name
- 以上表示多列排序
- SELECT first_name,last_name,10 AS points
- FROM customers
- ORDER BY points,first_name
- MYSQL与其他数据库的区别在于,MYSQL中可以用任何列排序数据,不论该列是否在SELECT子句中
11-LIMIT子句|The LIMIT Clause(如何限定查询返回的记录)
- SELECT *
- FROM customers
- Limit 3
- 此时仅返回前三位customer
- SELECT *
- FROM customers
- Limit 6,3
- 此时可以选择性添加一个offset(偏移量),用于数据分页。比如一个网页可以让用户查看到所有的customer,但每页只显示3位customer:page1(1-3)、page2(4-6)、page3(7-9)……当查看page3时则有如上指令,6为偏移量。“Limit 6,3”表示跳过6条记录并选取接下来的3条记录。
- 注意:LIMIT子句永远要放在最末