【SQL】基础概念
1.. In order to find the rows where the value for a column is or is not NULL, you would use IS NULL or IS NOT NULL. ex: select * from family_members where favorite_book is not null;
2.
---------
SQL基础
本文参照：http://www.w3school.com.cn/sql/
SQL 结构化查询语言(Structured Query Language)。
对于大小写不敏感。
SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。如果是数值，请不要使用引号。
SQL 语句后面的分号
某些数据库工具要求在每条 SQL 命令的末端使用分号。分号是在数据库系统中分隔每条 SQL 语句的标准方法。看到分号就标志着本条sql语句结束了。这样就可以在对服务器的相同请求中执行一条以上的语句。
在程序中写SQL不需要加分号，在程序之后编译器会把分号当做SQL本身的一部分，会有报错
SQL DDL 和 DML
SQL 分为两个部分：数据定义语言 (DDL) 和 数据操作语言 (DML)。
SQL 的 DDL 部分：
创建或删除表格，定义索引（键），规定表之间的链接，以及施加表间的约束。
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更（改变）数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引（搜索键）
- DROP INDEX - 删除索引
SQL 的 DML 部分：
查询、更新、插入和删除记录的语法
- SELECT - 从数据库表中获取数据
- UPDATE - 更新数据库表中的数据
- DELETE - 从数据库表中删除数据
- INSERT INTO - 向数据库表中插入数据
- SELECT 列名称 FROM 表名称
- SELECT * FROM 表名称
SELECT 语句
星号（*）是选取所有列的快捷方式。
大多数数据库软件系统都允许使用编程函数在结果集中进行导航。详见
ADO教程 http://www.w3school.com.cn/ado/index.asp
PhP教程 http://www.w3school.com.cn/php/index.asp
SELECT DISTINCT 语句
关键词 DISTINCT 用于返回唯一不同的值
- SELECT DISTINCT 列名称 FROM 表名称
WHERE 子句
WHERE 子句用于规定选择的标准。
- SELECT 列名称 FROM 表名称 WHERE 列 运算符 值
|
操作符 |
描述 |
|
= |
等于 |
|
<> |
不等于 在某些版本的 SQL 中，操作符 <> 可以写为 != |
|
> |
大于 |
|
< |
小于 |
|
>= |
大于等于 |
|
<= |
小于等于 |
|
BETWEEN |
在某个范围内 |
|
LIKE |
搜索某种模式 |
AND & OR 运算符
AND 和 OR用于基于一个以上的条件对记录进行过滤。
可在 WHERE 子语句中把两个或多个条件结合起来。
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
- 例：
SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William')
AND LastName='Carter'
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 默认升序排序。 ORDER BY ASC
ORDER BY DESC 降序排列
- SELECT A列，B列 FROM 表 order by A列 DESC ,B列 ASC
注意：：在以上的结果中有两个相等的A列值时。只有这一次，在A列中有相同的值时，B列是以升序排列的。如果A列中有些值为 nulls 时，情况也是这样的。
INSERT INTO 语句
- INSERT INTO 表名称 VALUES (值1, 值2,....) 直接指定新行每一列的值
- INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....) 指定所要插入数据的列
Update 语句用于修改表中的数据
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
- UPDATE 表名称 SET 列名称=新值 WHERE 列名称 = 某值
- DELETE FROM 表名称 WHERE 列名称 = 值 指定删除行
- DELETE FROM table_name
DELETE 语句用于删除表中的行
或DELETE * FROM table_name 可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的
TOP 子句
- SELECT TOP N * FROM 表 从表中选取头N条数据
- SELECT TOP N PERCENT * FROM 表 从表中选取头N%的数据
MySQL语法： SELECT * FROM 表 LIMIT 5
Oracle语法：SELECT * FROM 表 ROWNUM<= 5
LIKE 用于在 WHERE 子句中搜索列中的指定模式。
- SELECT FROM 表 WHERE 列名称 like 值
提示："%" 可用于定义通配符（模式中缺少的字母）。替代一个或多个字符
like 'N%' 从表中选取以N开头的记录
like '%g' 从表中选取以ｇ结尾的记录
like '%lon%' 从表中选取包含lon的记录
not like '' 从表中选取不包含 的记录
使用 _ 通配符，替代一个字符
- SELECT * FROM 表 WHERE 列 LIKE '_bc'
--从表中选取名字的第一个字符之后是 "bc" 的人
[charlist] 代表字符列中任何单一字符
- SELECT * FROM 表 WHERE 列 LIKE '[CP]%' 查找A列值是以C或P开头的记录
- SELECT * FROM 表 WHERE A列 LIKE '[C-P]xyz' 查找A列值是以介于Ｃ与Ｐ之间的任何单个字符开始且以xyz结尾的记录
- SELECT * FROM 表 WHERE A列 LIKE 'de[^1]' 查找A列值是de开始且其后的字母不为1的记录
- SELECT * FROM 表 WHERE 列 LIKE '[!CP]%' 查找A列值不以C或P开头的记录
- SELECT 列名 FROM 表 WHERE 列名 IN (value1,value2,...)
- SELECT 列名 FROM 表 WHERE 列名 BETWEEN value1 AND value2
[^charlist] 或 [!charlist] 不在字符列中的任何单一字符
IN 操作符允许在where子句中规定多个值
BETWEEN…and 操作符，选取结余两个值（数值、文本、日期）之间的数据范围
重要事项：不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的。
某些数据库会列出介于 " value1 " 和 " value2" 之间的人，但不包括 " value1 " 和 " value2" ；
某些数据库会列出介于 " value1 " 和 " value2" 之间并包括 " value1 " 和 " value2" 的人；
而另一些数据库会列出介于 " value1 " 和 " value2" 之间的人，包括 " value1 " ，但不包括 " value2" 。
- SELECT 列名 FROM 表 WHERE 列名 NOT BETWEEN value1 AND value2
显示在范围之外的记录
As 可以为列名称和表名称指定别名，使查询程序易于阅读和书写
- SELECT 列 FROM 表 AS 表别名
- SELECT 列 AS 列别名 FROM 表
join 用于根据两个或多个表中的列之间的关系，从这些表中查询数据。
主键（Primary Key）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。
- JOIN: 如果表中有至少一个匹配，则返回行
- INNER JOIN: 等值连接，只返回两个表之后连接字段想等的行
Select * from 表A inner join 表B on A.XX=B.XX
重要事项：类似正常的查询。不以任何表为基础。只显示符合搜索条件的数据。
- LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 （ LEFT OUTER JOIN）
Select * from 表A left join 表B on A.XX=B.XX
重要事项：以A表为基础，即左表的记录全部显示出来。而表B只显示符合搜索条件的记录出来。即右表根据条件显示，不足的以NULL填充显示。
LEFT JOIN 关键字会从左表返回所有的行，即使在右表中没有匹配的行。
- RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行 （ RIGHT OUTER JOIN）
Select * from 表A right join 表B on A.XX=B.XX
重要事项：效果与left join正好相反，以右表的数据为基础，显示右表全部数据，而只显示左表符合搜索条件的数据。
RIGHT JOIN 关键字会从右表那里返回所有的行，即使在左表中没有匹配的行。
- FULL JOIN: 只要其中一个表中存在匹配，就返回行 （ FULL OUTER JOIN）
重要事项：产生A和B的并集。但是需要注意的是，对于没有匹配的行记录，空值列则会以null做为值。
FULL JOIN 关键字会从左表和右表那里返回所有的行。如果 左表 中的行在表 右表 中没有匹配，或者如果 右表 中的行在表 左表 中没有匹配，这些行同样会列出。
参照：http://jingyan.baidu.com/article/e6c8503c31d2f7e54e1a1851.html
http://www.nowamagic.net/librarys/veda/detail/936
UNION 操作符用于合并两个或多个 SELECT 语句的结果集
注意：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
- SELECT 列 FROM 表A
UNION
SELECT 列 FROM 表B
默认地，UNION 操作符选取不同的值。得出的结果集中已经去重，若存在多条值相同的数据，只显示一条。
- SELECT 列 FROM 表A
UNION ALL
SELECT 列 FROM 表B
UNION ALL是允许重复的值。若得出的结果中存在多条值相同的数据，会显示多条记录。
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
SELECT INTO 语句用于创建表的备份复件
从一个表中选取数据，然后把数据插入另一个表中
- SELECT * INTO 新表名B FROM 旧表名A --从表A中备份所有到表B
- SELECT * INTO 表名A in ‘备份数据库bk.mdb’ FROM 表名A --in子句可向另一个数据库中拷贝表
- SELECT 列A，列B，列C INTO 新表名B FROM 旧表名A --拷贝指定列插入新表
Select into 可以和 where 、join等搭配使用
Create Database创建数据库
- CREATE DATABASE database_name
- CREATE TABLE 表名称
Create Table创建数据库中的表
(
列名称1 数据类型 NOT NULL, // NOT NULL约束指强制列1不接受空值
列名称2 数据类型 NOT NULL UNIQUE, //约束列2的值要有唯一性
列名称3 数据类型 PRIMARY KEY, //约束列3的值为主键
....
UNIQUE (列名称1) //约束列1的值要有唯一性
CONSTRAINT 约束名 UNIQUE (列1，列2) //同时为多个列定义UNIQUE唯一性
PRIMARY KEY (列4) //约束列4为主键，主键自动具有非空属性
CONSTRAINT 约束名 UNIQUE (列5，列6) //设置列5、6为联合主键。单列可以有相同值，组合后的主键具有唯一性 ps:一个表中只能有一个主键，此处为举例，没分多表
)
增加 UNIQUE 约束
- Alter table 表名 ADD UNIQUE (列名1)
- Alter table 表名 ADD CONSTRAINT约束名 UNIQUE (列名1, 列名2)
撤销 UNIQUE 约束
- Alter table 表名 DROP INDEX 约束名 --mySQL
- Alter table 表名 DROP CONSTRAINT 约束名 --SQL server,Oracle ,MS Access
增加 PRIMARY KEY 约束
- Alter table 表名 PRIMARY KEY (列名1)
- Alter table 表名 ADD CONSTRAINT 主键名 PRIMARY KEY (列名1, 列名2)
注释：如果您使用 ALTER TABLE 语句添加主键 PRIMARY KEY，必须把主键列声明为不包含 NULL 值（在表首次创建时）。
撤销 PRIMARY KEY约束
- Alter table 表名 DROP PRIMARY KEY --mySQL
- Alter table 表名 DROP CONSTRAINT 主键名 --SQL server,Oracle ,MS Access
SQL中最常用的数据类型：
|
数据类型 |
描述 |
|
integer(size) int(size) smallint(size) tinyint(size) |
仅容纳整数。在括号内规定数字的最大位数。 |
|
decimal(size,d) numeric(size,d) |
容纳带有小数的数字。 "size" 规定数字的最大位数。"d" 规定小数点右侧的最大位数。 |
|
char(size) |
容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。 在括号中规定字符串的长度。 |
|
varchar(size) |
容纳可变长度的字符串（可容纳字母、数字以及特殊的字符）。 在括号中规定字符串的最大长度。 |
|
date(yyyymmdd) |
容纳日期。 |
FOREIGN KEY 约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
- CREATE TABLE 表名称A
(
列名称1 数据类型 FOREIGN KEY REFERENCES 表B(列B1),
//设定列1为外键关联表B （SQL Server / Oracle / MS Access）
列名称2 数据类型
FOREIGN KEY (列2) reference表B(列B1),
/ /设定列1为外键关联表B （mysql）
....
CONSTRAINT 外键名 FOREIGN KEY (列A2) REFERENCES表B(列B2)
//设定列A2为关键关联表B 列B2 （MySQL / SQL Server / Oracle / MS Access）
)
创建 FOREIGN KEY 约束
- Alter table 表名 ADD FOREIGN KEY (列名1) REFERENCES表B(列B2)
Alter table 表名 ADD CONSTRAINT外键名 FOREIGN KEY (列名1) REFERENCES表B(列B2)
撤销 FOREIGN KEY 约束
- Alter table 表名 DROP FOREIGN KEY 外键名 --mySQL
- Alter table 表名 DROP CONSTRAINT 外键名 --SQL server,Oracle ,MS Access
SQL CHECK 约束
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制
- CREATE TABLE 表名称A
(
列名称1 int NOT NULL CHECK (列1>0) //（SQL Server / Oracle / MS Access）
列名称2 数据类型
....
CHECK (列2>0) //mysql
)
…..更多详细介绍，参照 http://www.w3school.com.cn/sql/sql_check.asp
DEFAULT 约束
用于向列中插入默认值。如果没有规定其他的值，那么会将默认值添加到所有的新记录。
- CREATE TABLE 表名称A
(
列名称1 数据类型
列名称2 数据类型 DEFAULT ‘AAAAA’
....
OrderDate date DEFAULT GETDATE()
//使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值
)
DATE 函数
用于向列中插入默认值
MySQL Date 函数
下面的表格列出了 MySQL 中最重要的内建日期函数：
|
函数 |
描述 |
|
返回当前的日期和时间 |
|
|
返回当前的日期 |
|
|
返回当前的时间 |
|
|
提取日期或日期/时间表达式的日期部分 |
|
|
返回日期/时间按的单独部分 |
|
|
给日期添加指定的时间间隔 |
|
|
从日期减去指定的时间间隔 |
|
|
返回两个日期之间的天数 |
|
|
用不同的格式显示日期/时间 |
SQL Server Date 函数
下面的表格列出了 SQL Server 中最重要的内建日期函数：
|
函数 |
描述 |
|
返回当前日期和时间 |
|
|
返回日期/时间的单独部分 |
|
|
在日期中添加或减去指定的时间间隔 |
|
|
返回两个日期之间的时间 |
|
|
用不同的格式显示日期/时间 |
AVG函数 ，返回平均值
- SELECT AVG(column_name) FROM table_name
count函数 ，返回匹配指定条件的行数
- SELECT count(column_name) FROM table_name 返回指定列的值的数目
- SELECT count(distinct column_name) FROM table_name 返回指定列的不同值的数目
- SELECT count(*) FROM table_name 返回表中的记录数
FIRST函数 ，返回指定字段之后第一个记录的值
提示：可使用 ORDER BY 语句对记录进行排序。
- SELECT first(column_name) FROM table_name
Last函数 ，返回指定字段之后第一个记录的值
- SELECT last(column_name) FROM table_name
提示：可使用 ORDER BY 语句对记录进行排序。
MID函数 ，从指定列提取字符
- SELECT MID(列名必填,开始位置必填起始值为1[,结束位置]可选输入要返回的字符数) FROM table_name
SELECT MID(City,1,3) as SmallCity FROM Persons 从 "City" 列中提取前 3 个字符。
len函数 ，返回文本字段之后值的长度
SELECT len(列名 ) FROM table_name
round函数 ，把数值字段摄入为指定的小数位数
- SELECT MID(列名必填要舍入的字段,decomals要返回的小数位数) FROM table_name
max函数 ，返回最大值
- SELECT max(列名) FROM table_name
min函数 ，返回最小值
- SELECT min(列名) FROM table_name
sum函数 ，返回列总和
- SELECT sum(列名) FROM table_name
ucase函数 ，upper 把字段值转化为大写
- SELECT ucase(列名) FROM table_name
lcase函数 ，把字段值转化为小写
- SELECT lcase(列名) FROM table_name
Group by语句 ，根据一个列或多个列队结果集进行分组，配合聚合函数使用
- SELECT * FROM table_name where 条件1 group by 列名
Select name,sum(score) where xx group by name --查询出每个人的分数总和
不配合聚合函数使用时，group by 后需要列出所有的列，否则会有报错…虽不会报错，但是group也没有任何意义了，没发挥作用。
Having by语句 ，根据一个列或多个列队结果集进行分组，配合聚合函数使用
- SELECT * FROM table_name where 条件1 group by 列名 having 条件2
WHERE语句在GROUPBY语句之前；SQL会在分组之前计算WHERE语句。
HAVING语句在GROUPBY语句之后；SQL会在分组之后计算HAVING语句。
当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：
--执行where子句查找符合条件的数据；
--使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组。
--having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.
--having子句和where子句都可以用来设定限制条件以使查询结果满足一定的条件限制。
--having子句限制的是组，而不是行。where子句中不能使用聚集函数，而having子句中可以。
SQL常用
select *
from 表
where 条件1 order by 列A desc limit 20;
SELECT
count(1)
FROM 表A
WHERE 条件1
AND EXISTS(
SELECT 0
FROM 表B
WHERE 条件2
)
And 条件3
select date_add(operate_time, interval 15 day), #状态变更时间+15天
NOW(), #当前时间
DATEDIFF(date_add(operate_time, interval 15 day),NOW()) #截止时间（当前-状态变更时间）
from 表
where 条件1 order by 列1 DESC
date_add(operate_time, interval 3 month), #状态变更时间+3个月
