文科生脑回路也学得会|SQL语法（简）自学笔记0605

SQL是什么

SQL 

(Structured Query Language:结构化查询语言)，能用来访问和操作数据库系统。

数据库≈盘子，数据≈盘里的菜，SQL≈筷子。

SQL语句 

用于取回和更新数据库中的数据，可与数据库程序协同工作，如：MS Access、DB2、Informix、MS SQL Server、Oracle、Sybase 及其他数据库系统。

SQL能做什么

1. 面向数据库（Oracle, Sybase, SQL Server, DB2, Access）执行查询
2. 可从数据库取回数据
3. 可在数据库中插入新的记录
4. 可更新数据库中的数据
5. 可从数据库删除记录
6. 可创建新数据库
7. 可在数据库中创建新表
8. 可在数据库中创建存储过程
9. 可在数据库中创建视图
10. 可设置表、存储过程和视图的权限

SQL发展历程

1. 1986年，ANSI首次制定了SQL的标准，之后又进行了数次修订。
2. 1987年，成为国际标准化组织（ISO）标准，称为标准SQL。
3. 1986年，ANSIX3.135-1986，ISO/IEC9075:1986，SQL-86
4. 1989年，ANSIX3.135-1989，ISO/IEC9075:1989，SQL-89
5. 1992年，ANSIX3.135-1992，ISO/IEC9075:1992，SQL-92（SQL2）
6. 1999年，ISO/IEC9075:1999，SQL:1999（SQL3）
7. 2003年，ISO/IEC9075:2003，SQL:2003
8. 2008年，ISO/IEC9075:2008，SQL:2008
9. 2011年，ISO/IEC9075:2011，SQL:2011
10. 2016年，ISO/IEC9075:2016，SQL:2016截止目前，最新的为SQL:2016。

必记法则

法则1：col

法则2：select

SELECT col,col,col 找什么？

FROM table 从哪找？

WHERE col 条件 条件是啥？

条件1：数字(where)

当查找条件col是数字

select * from table where col = 1;

Operator	Condition	SQL Example	解释
=, !=, < ,<=, >, >=	Standard numerical operators	col != 4	等于 大于 小于
BETWEEN … AND …	Number is within range of two values (inclusive)	col BETWEEN 1.5 AND 10.5	在 X 和 X之间
NOT BETWEEN … AND …	Number is not within range of two values (inclusive)	co NOT BETWEEN 1 AND10	不在 X 和 X之间
IN (…)	Number exists in a list	col IN (2, 4, 6)	在 X 集合
NOT IN (…)	Number does not exist in a list	col NOT IN (1, 3, 5)	不在 X 集合

条件2：文本(where)

当查找条件col是文本

select * from table where col like '%jin';

Operator	Condition	SQL Example	解释
=	Case sensitive exact string comparison (notice the single equals)	col = "abc"	等于
!= or <>	Case sensitive exact string inequality comparison	col != "abcd"	不等于
LIKE	Case insensitive exact string comparison	col LIKE "ABC"	等于
NOT LIKE	Case insensitive exact string inequality comparison	col NOT LIKE "ABCD"	不等于
%	Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE)	col LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS")	模糊匹配
_	Used anywhere in a string to match a single character (only with LIKE or NOT LIKE)	col LIKE "AN_" (matches "AND", but not "AN")	模糊匹配单字符
IN (…)	String exists in a list	col IN ("A", "B", "C")	在集合
NOT IN (…)	String does not exist in a list	co NOT IN ("D", "E", "F")	不在集合

排序(rows)

需要对结果rows排序和筛选部分rows

select * from table where col > 1 order by col asc limit 2 offset 2

Operator	Condition	SQL Example	解释
ORDER BY	.	ORDER BY col ASC/DESC	按col排序
ASC	.	ORDER BY col ASC/DESC	升序
DESC	.	ORDER BY col ASC/DESC	降序
LIMIT OFFSET	.	LIMIT num_limit OFFSET num_offset	从offset取limit
ORDER BY	.	ORDER BY col1 ASC,col2 DESC	多列排序

join:连表(table)

当查找的数据在多张关联table里

select * from table1 left join table2 on table1.id = table2.id where col > 1

Operator	Condition	SQL Example	解释
JOIN .. ON ..	.	t1 JOIN t2 ON t1.id = t2.id	按ID连成1个表
INNER JOIN	.	t1 INNER JOIN t2 ON t1.id = t2.id	只保留id相等的row
LEFT JOIN	.	t1 LEFT JOIN t2 ON t1.id = t2.id	保留t1的所有row
RIGHT JOIN	.	t1 RIGHT JOIN t2 ON t1.id = t2.id	保留t2的所有row
IS/IS NOT NULL	.	col IS/IS NOT NULL	col是不是为null

算式(select / where)

当需要对select的col 或 where条件的col 经过一定计算后才能使用

select *,col*2 from table where col/2 > 1

Operator	Condition	SQL Example	解释
+ - * / %	.	col1 + col2	col加减乘除
substr	.	substr(col,0,4)	字符串截取
AS	.	col * 2 AS col_new	col取别名
...			还有很多

统计（select）

对查找的rows需要按col分组统计的情况

select count(*),avg(col),col from table where col > 1 group by col

Operator	Condition	SQL Example	解释
COUNT(*), COUNT(column)	A common function used to counts the number of rows in the group if no column name is specified. Otherwise, count the number of rows in the group with non-NULL values in the specified column.	count(col)	计数
MIN(column)	Finds the smallest numerical value in the specified column for all rows in the group.	min(col)	最小
MAX(column)	Finds the largest numerical value in the specified column for all rows in the group.	max(col)	最大
AVG(column)	Finds the average numerical value in the specified column for all rows in the group.	avg(col)	平均
SUM(column)	Finds the sum of all numerical values in the specified column for the rows in the group.	sum(col)	求和
GROUP BY	.	group by col,col2	分组
HAVING	.	HAVING col>100	分组后条件

子表 (table)

一次select的结果rows作为下一次select的临时table才能得到最终结果

select * from (select * from table where col > 1) as tmp where col < 1

Operator	Condition	SQL Example	解释
（select -）as tmp		（select -）as tmp	select结果做子表
in（select -）		in（select -）	select结果做条件
avg（select -）avg（select -）select结果做条件

​​​​​​SQL语法

1.基础查询

SELECT法则

SELECT col,col,col找什么?

FROM table           从哪找?

WHERE col          条件 条件是啥?

三步

1. 思考FROM后面的(即是表)
2. 思考WHERE条件(查手册即可)
3. 完善SELECT后面的(即老板要显示的数据)

简单查询

【查询列】

SELECT<列名>FROM<表名>

单个列查询

SELECT<列名>,<列名>,<列名>FROM<表名>

多个列查询

【查全表】

SELECT*FROM<表名>

注：*星号表示全部列

【数字条件】

SELECT*FROM<表名>WHERE <列名>=100

注：除了=,还可以用>,<,=,>=来比较

【文本条件】

SELECT*FROM<表名>WHERE<列名>="abc"

注：字符串用”“引号

SELECT*FROM<表名>WHERE<列名>LIKE"abc%"

注：%表示模糊匹配

【单纯计算】

SELECT1+2

不用FROM

运算

【算术运算符】

+-*/加减乘除

%取余数

【比较运算符】

=><!= 等于 大于 小于 不等于

【逻辑运算】

is null

等于null

is not null

不等于null

【集合运算】

in(<集合数据>)

in(1,2,3,4)

多个条件

AND

且

SELECT*FROM<表名>WHERE<列1>=100 AND<列2>=200

取出列1为100且列2为200的数据

OR

或

SELECT*FROM<表名>WHERE<列1>=100OR<列2>=200

取出列1为100或列2为200的数据

2.统计和排序

SELECT法则

SELECTcol,col,col找什么?

FROM table从哪找?

WHERE col  条件 条件是啥?

三步

1. 思考FROM后面的(即表)
2. 思考WHERE条件(查手册即可)
3. 完善SELECT后面的(即老板要显示的数据)

统计

【count】

SELECT count(*)FROM<表名>

统计行数

SELECT count(<列名>)FROM<表名>

统计行数(不包含null的)

【其他统计】

SELECT <avg|sum|.>FROM<表名>

统计

avg()平均

min()max() 最小 最大

sum()求和

分组统计

【group by】

SELECT*FROM<表名>GROUP BY<列名>

按列名分组

SELECT*FROM<表名>GROUP BY<列名>,<列名2>

可以按多个列分组

【统计】

SELECT<count|avg|sum|min|max>FROM<表名>GROUP BY<列名>

分组后统计才有意义

【having】

WHERE关键字无法与聚合函数一起使用

having语句用于group by分组后的筛选

SELECT <count|avg|sum|min|max>FROM<表名>GROUP BY<列名>HAVING count(“)>100

分组统计之后，再筛选出计数大于100的

排序

DESC

降序

SELECT*FROM<表名>WHERE<条件>ORDER BY<列名>DESC

注：排序总是最后计算，也就是对最终结果降序

ASC

升序

SELECT*FROM<表名>WHERE<条件>ORDER BY<列名>ASC

注：排序总是最后计算

LIMIT0,1

limit对排序完的结果截取，注意0表示第1个位置，1表示取1个

多个列排序

SELECT*FROM<表名>WHERE<条件>ORDER BY<列名>ASC,<列2>DESC

注：多个字段可以排序&支持指定每个列怎么排序

3.复杂查询

SELECT法则

SELECT col,col,col找什么?

FROM table从哪找?

WHERE col  条件 条件是啥?

三步

1. 思考FROM后面的(即表)
2. 思考WHERE条件(查手册即可)
3. 完善SELECT后面的(即老板要显示的数据)

 子查询

【子查询用做表】

SELECT<>FROM (SELECT>FROM<表><条件as表1

子查询的结果可以做表

【关联子查询】

SELECT<>FROM<表1>as主表WHERE<列>=(SELECT<列>FROM表2 as附表WHERE主表.列=附表.列)

注：主表可以和子查询里的表联合查询

【单一值】

SELECTOFROM<表>-WHERE列=(SELECT子查询)

注：select,where等出现列的地方都可以用，子查询必须返回单个值

SELECT一FROM<表>WHERE列IN (SELECT子查询)

注：select,where等出现列的地方都可以用，子查询必须返回单个值

执行顺序

【SQL执行顺序】

SELECT<计算>FROM<表名>LEFT JOIN<表2>ON<条件>GROUPBY<列>HAVING<条件>ORDER BY<列>UNION<第二个SQL>

注：所有语法都出现的SQL

1. FROM和JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT/OFFSET
8. UNION

4.联表

SELECT法则

SELECT col,col,col找什么?

FROM table从哪找?

WHERE col  条件 条件是啥?

三步

1. 思考FROM后面的(即表)
2. 思考WHERE条件(查手册即可)
3. 完善SELECT后面的(即老板要显示的数据)

表的联结

【inner join】

SELECT→FROM<表1>INNERJOIN<表2>ON 表1.<列>=表2.<列>

注：完全相等的连接到一起，必须用相同含义的列连接

【left join】

SELECT<>FROM<表1>LEFT JOIN<表2>ON表1.<列>=表2.<列>

注：表1的数据全部留下，连接不到的数据用null

【自连接】

SELECT<>FROM<表>as别名1,<表>as别名2
注：自连接就是表自己连自己，所以，一定要用两个别名，不用ON。

表的合并

【UNION】

SELECT<>FROM<表名>UNION SELECT<>FROM<表名>

注：将两次查询的数据前后合并到一起

可以多个查询合并

列数目必须相同

列类型必须相同

原始

a1 b1

合并

a2 b2

结果

a1 b1

a2 b2

5.函数

数学函数

abs()

绝对值计算

a%b

余数计算

round()

四舍五入算法

random()

随机算法

合字符串函数

concat (str1,str2)

字符串拼接

upper()lower()

大小写转换

length()

字符串长度计算

substr()

字符串截取函数

基础函数

【in/not in】

SELECT*FROM<表名>WHERE<列名>in(<集合>)

注：在集合里

SELECT*FROM<表名>WHERE<列名>not in(<集合>)

注：不在集合里

【like】

SELECT*FROM<表名>WHERE<列名>LIKE'abc

注：没有%和=是同样的

SELECT*FROM<表名>WHERE<列名>LIKE'%abc

注：%匹配任意字符

【between】

SELECT*FROM<表名>WHERE<列名>BETWEEN a AND b

注：在a和b之间，一般用于数字

CASE

在SELECT中使用

相当于if

CASE WHEN<条件>THEN值ELSE<条件>值END

用于将字段的值分类转化成别的值，比如：

注：字段是分数，用case将分数转成不及格、及格、优秀这样的值表示

6.建表

【数据库】

CREATE DATABASE<库名>

创建新的数据库

【语法】

CREATE TABLE<表名>(

<列1>数据类型约束，

<列2>数据类型约束，

<列3>数据类型约束，

);

【字段类型】

char(10)

字符，最大长度10

float

浮点数如3.14

int

整数

date

日期

【字段约束】

NOT NULL

非空字段

DEFAULT

值默认值

DROP

【语法】

DROP DATABASE<库名>

删库，跑路

DROP TABLE<表>

删表

DROP之后全部数据消失

注：危险

7.新增变更

INSERT

【基本语法】

INSERT INTO<表名>(列1,列2...)VALUES(值1,值2,..)

列和值必须1,1对应

INSERT INTO<表名>VALUES(值1,值2...)

不指明列名的话，值的顺序必须和表的列顺序一致

null

不填的字段默认值是null

【拷贝】

INSERT INTO<表名>(列1,列2...)SELECT<列>FROM<表2>

可以把表2的数据或表2查到的数据直接写入表一，不过注意列对应

唯—ID

唯一ID不能重复

UPDATE

【语法】

UPDATE<表名>SET<列1>=值，<列2>=值WHERE<条件>

可以多个列一起变更，where条件查出多少数据就更新多少UPDATE

SELECT FROM<表名>WHERE<条件>

update和select后面的条件写法一样的

DELETE

【语法】

DELETE FROM<表名>WHERE<条件>

删除where条件查出的，不写where删除全表

SELECT FROM<表名>WHERE<条件>

delete和select后面的条件写法一样的

8.各种符号

标点符号

逗号，

SQL语法，连接相同的单元

引号

"单引号“双引号表示字符串

SELECT'列名',列名

把列名用引号引起来，就会认为是字符串

SELECT’字符串，字符串

字符串不带引号，就会识别为列名

分号；

分号不要出现在一条SQL中间，;是SQL结尾符号

%

在LIKE里是通配符

在数字计算是取余数

字符串里怎么写单引号

单引号"

最外层的单引号是表示字符串

中间用两个单引号表示内容的一个单引号

点号.

table.col这种用法就是为了准确找到这个列

中文问题

语句中逗号，引号，分号都要用英文的，不要写中文会报错

不要用中文的符号

内容可以用中文，列名可以用中文

运算符号

+-*/%

数学运算，字符串不能计算

1+1可以，'a'+'b¹不可以

LIKE

只能字符串计算

=

列1=列2是允许的

WHERE列1=列2就是取两列的值相等的数据

null的计算

is null

is notnull

一定要注意数据为null的情况

count

count(列)不计算null

<>

不等于

AND OR

多个ANDOR有计算顺序问题

aAND bOR c

(a AND b)ORc加上括号避免计算顺序问题

distinct

唯一值计算

排除重复值

distinct uid

大小写

SELECT,select

关键字大小写没事

效果一样

字段的值AA和aa

字段的值区分大小写

WHERE列='AA'和WHERE列='aa'不一样