SQL教程-b站
- Database: a database is a collection of data stored in a format that can easily be accessed.
- DBMS: Database Management System(连接到DBMS,下达查询或修改数据的指令,DBMS执行并返回)
- two types of DBMS: relational and nosql
- SQL(structured query language) or SEQUEL(structured english query language): 处理关系型DBMS的语言
- many types of RDBMS: MySQL, SQL Server, Oracle…
- NoSQL systems don’t understand SQL
- SQL大小写不敏感
SELECT *
选择from表的所有列,SELECT xxx
选择某个列
SELECT
first_name,
last_name,
points+10 AS discount --重命名列,如果重命名的名字有空格,需要单引号'discount factor'
FROM sql_store.customers --选择某个表
FROM
SELECT state --返回所有,包含重复
FROM customers
SELECT DISTINCT state --去掉重复项目
FROM customers
WHERE
里可以使用OR,AND逻辑运算符来进行条件筛选,AND优先级高于OR,NOT优先级最高
!= --不等于
<> --不等于
SELECT *
FROM Customers
WHERE price>3000
--筛选 eg:WHERE state <> 'VA'
--WHERE birth_date > '1990-01-01'
-- eg:WHERE NOT(birth_data>'1990-01-01') OR price>1000
- SQL不能将字符串同布尔(Boolean)结合。
IN
SELECT *
FROM customers
WHERE state IN('VA', 'FL','GA')
--WHERE state='VA' OR state='FL' OR state='GA'的简写
--也可以用NOT:WHERE state NOT IN('VA', 'FL','GA')
BETWEEN ... AND...
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
--WHERE point<=3000 AND point >=1000的另一种写法
- 模糊搜索
LIKE
- %:any number of characters
SELECT *
FROM customers
WHERE last_name LIKE 'b%'
--b%表示b+任意字符数(0,1,..)
--WHERE last_name LIKE '%b%'(名字中含有b的)
--WHERE last_name LIKE '%y'(y结尾的)
- _:single character
SELECT *
FROM customers
WHERE last_name LIKE '_y'
--两个字母组成的名字,第二个字母是y,_占一位
REGEXP
正则表达式 regular expression
^,$,|,[]
的用法- ^ : beginning
- $ : end
- | : logical or
- [abcd]
- [a-f]
SELECT *
FROM customers
WHERE last_name REGEXP 'filed'
--等价于WHERE last_name LIKE 'filed%'
--WHERE last_name REGEXP '^filed' 则表示必须要以filed为开头
--WHERE last_name REGEXP 'filed$' 则表示必须要以filed为结尾
--WHERE last_name REGEXP 'filed|mac' 则表示名字里含有filed或者含有mac的
--|表示多个搜索模式
--WHERE last_name REGEXP '[gim]e'表示含有ge,ie,me的
--WHERE last_name REGEXP 'e[fmq]'表示含有ef,em,eq的
--WHERE last_name REGEXP '[a-h]e'表示含有(a-h)+e的
IS NULL
SELECT *
FROM customers
WHERE phone IS NULL --得到电话值是空的顾客
-- WHERE phone IS NOT NULL 非空
ORDER BY
andDESC
SELECT *
FROM customers
ORDER BY first_name --按照first_name排序
-- ORDER BY first_name DESC 加入DESC得到降序排列
- 多列排序
SELECT *
FROM customers
ORDER BY state,first_name --现通过州排列,同州的用first_name排列
--ORDER BY state DESC,first_name DESC
在SQL中,可以使用任何列排序数据,不管列是不是在SELECT中
SELECT first_name,last_name
FROM customers
ORDER BY birth_date
根据列的顺序ORDER中引入
SELECT first_name,last_name, 10 AS points
FROM customers
ORDER BY 1,2
SELECT *, quantity*unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC --可以使用SELECT子句中的别名
LIMIT
SELECT *
FROM customers
LIMIT 300 --返回前300个结果,如果不到300就返回全部
-- LIMIT 6,3 跳过前六个数据返回第七,八,九条数据(3条)
- 子句顺序
SELECT
FROM
WHERE
ORDER BY
LIMIT