SQL常用知识总结

SQL

SQL语言是数据库的核心语言

SQL语言的分类

1. 数据查询语言DQL

数据查询语言DQL基本结构是由select子句、from字句、where子句组成。（简而言之，就是对数据库表的查询语句）
SELECT <表字段名>
FROM<表或视图名>
WHERE<查询条件>

2. 数据操纵语言DML

数据操作语言DML就是对数据库表内容的操作。它有主要的三种形式：
1）插入：INSERT
2）更新：UPDATE
3）删除：DELETE

3. 数据定义语言DDL

数据定义语言DDL用来创建数据库中的各种对象——表、视图、索引、同义词、聚簇等

CREATE TABLE	CREATE VIEW	CREATE INDEX	CREATE SYN	CREATE CLUSTER
表	视图	索引	同义词	簇

4. 数据控制语言DCL

数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

1）GRANT:授权

2）ROLLBACK:回滚

ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点
回滚命令使数据库状态回到上次最后提交的状态。其格式为：
SQL>ROLLBACK;

3）COMMIT[WORK]:提交

在数据库的插入、删除和修改操作时，只有当事务在提交到数据
库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
到所做的事情，别人只有在最后提交完成后才可以看到。
提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
别说明这三种类型。

(1）显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：
SQL>COMMIT；

(2)隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
系统将自动进行提交，这就是自动提交。其格式为：
SQL>SET AUTOCOMMIT ON；

SQL解析

SQL对大小写不敏感：SELECT与select是相同的

一些重要的SQL命令

• 创建数据库-CREATE DATABASE <库名>
• 删除数据库-DROP DATABASE <库名>
• 修改数据库-ALTER DATABASE <库名>
• 查看数据库-SHOW DATABASE
• 使用数据库USE <库名>
• 创建表-CREATE TABLE <表名> （字段名 字段名类型 字段描述符，字段名 字段名类型 字段描述符…）
• 删除表-DROP TABLE <表名>
• 修改表-ALTER TABLE <表名>
• 查看表或表结构-SHOW TABLES/DESC <表名>
• 插入数据-INSERT INTO <表名> VALUES ()
• 删除数据-DELETE 字段名 FROM <表名> WHERE 条件
• 更新(修改)数据-UPDATE<表名> SET 更改的字段名=值 where 条件
• 查询数据-SELECT (字段名) FROM <表名>
• 创建索引-CREATE INDEX
• 删除索引-DROP INDEX

注意：
1.UPDATE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要更新。如果您省略了 WHERE 子句，所有的记录都将被更新！
2.DELETE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要删除。如果您省略了 WHERE 子句，所有的记录都将被删除！

SQL用法

1.SELECT DISTINCT：去重

SELECT DISTINCT：用于查询表中其唯一不同的值，其用法和SELECET 用法一样。

2.AND和OR运算符

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。（和Java中“&&”差不多）
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。（和Java中“||”差不多）
在WHERE后面使用，可以组合使用如：SELECT * FROM 表名 WHERE 条件1 AND （条件2 OR 条件2）

3.ORDER BY：排序

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。
ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字

单列就算了，多列如下：
SELECT * FROM 表名 ORDER BY 字段1 DESC,字段2;

4.INSERT INTO：插入

INSERT INTO 语句用于向表中插入新记录
两种用法如下：

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可（数据列表要一一对应）
INSERT INTO table_name
VALUES (value1,value2,value3,...);

第二种形式需要指定列名及被插入的值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

5.LIKE模糊查询

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式

通配符

通配符	描述
%	替代零个或多个字符
_	替代一个字符
[ charlist]	字符列中的任何单一字符
[^ charlist] 或 [! charlist]	不在字符列中的任何单一字符

例：1.选取 name 以 "G"、"F" 或 "s" 开始的所有信息：SELECT * FROM table_name WHERE name REGEXP '^[GFs]‘; 2.name 以 A 到 H 字母开头的信息:SELECT * FROM table_name WHERE name REGEXP '^[A-H]'; 3.选取 name 不以 A 到 H 字母开头的网站：SELECT * FROM table_name WHERE name REGEXP '^[^A-H]';

SELECT 字段名
FROM 表名
WHERE 字段名 LIKE 模糊条件;

6.IN(value1,value2…)和BETWEEN AND

IN 操作符允许您在 WHERE 子句中规定多个值

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

IN 与 = 的异同
相同点：均在WHERE中使用作为筛选条件之一、均是等于的含义
不同点：IN可以规定多个值，等于规定一个值

JOIN(连接查询)

join 用于把来自两个或多个表的行结合起来
下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法

INNER JOIN(内连接)

INNER JOIN 关键字在表中存在至少一个匹配时返回行。(INNER 可省略)

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

OUTER JOIN(外连接)

OUTER JOIN分为LETF OUTER JOIN和RIGHT OUTER JOIN
OUTER JOIN 关键字从其中一表（table1）中返回所有的行，即使另一个表（table2）中没有匹配。如果左(右)表中没有匹配，则结果为 NULL。（OUTER可省略）

SELECT column_name(s)
FROM table1
LEFT/RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

7.INSERT INTO SELECT(复制表数据)

INSERT INTO SELECT 语句从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受影响。

一个表中复制所有的列插入到另一个已存在的表中:
INSERT INTO table2
SELECT * FROM table1;

只复制希望的列插入到另一个已存在的表中：
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

8.SQL约束（Constraints）

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

9.CREATE INDEX（创建索引）和DROP INDEX（删除索引）

CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。

创建一个简单的索引。允许使用重复的值：
CREATE INDEX index_name
ON table_name (column_name);

创建一个唯一的索引。不允许使用重复的值：唯一的索引意味着两个行不能拥有相同的索引值.
CREATE UNIQUE INDEX index_name
ON table_name (column_name);

DROP TABLE用法：
MySQL:
	ALTER TABLE table_name DROP INDEX index_name;
DB2/Oracle:
	DROP INDEX index_name;

10.ALTER TABLE(修改表)

ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

在表中添加列：
ALTER TABLE table_name
ADD column_name datatype;

删除表中的列:
ALTER TABLE table_name
DROP COLUMN column_name;

改变表中列的数据类型:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

视图

视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

您可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样

创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据

更新视图
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

查询视图
SELECT * FROM view_name;

删除视图
DROP VIEW view_name;

函数

Aggregate函数

Aggregate 函数计算从列中取得的值，返回一个单一的值。

• AVG()----返回平均值
• COUNT()----返回行数
• FIRST()----返回第一个记录的值
• LAST()----返回最后一个记录的值
• MAX()----返回最大值
• MIN()----返回最小值
• SUM()----返回总和

Scalar函数

Scalar 函数基于输入值，返回一个单一的值。

• UCASE() ---- 将某个字段转换为大写
• LCASE() ----将某个字段转换为小写
• MID() ---- 从某个文本字段提取字符，MySql 中使用
• SubString(字段，1，end) ---- 从某个文本字段提取字符
• LEN() ---- 返回某个文本字段的长度
• ROUND() ---- 对某个数值字段进行指定小数位数的四舍五入
• NOW() ---- 返回当前的系统日期和时间
• FORMAT() ---- 格式化某个字段的显示方式

Date函数

• NOW()----返回当前的日期和时间
• CURDATE()----返回当前的日期
• CURTIME()----返回当前的日期
• DATE()----提取日期或日期/时间表达式的日期部分
• EXTRACT()----返回日期/时间的单独部分
• DATE_ADD()----向日期添加指定的时间间隔
• DATE_SUB()----从日期中减去指定的时间间隔
• DATEDIFF()----返回两个日期之前的天数
• DATE_FORMAT()----用不同的格式显示日期/时间

MySQL与Oracle中不同的用法

1.分页查询

用于规定要返回的记录的数目
MySQL 支持 LIMIT 语句来选取指定的条数数据， Oracle 可以使用 ROWNUM 来选取。
1）LIMIT用法

SELECT 字段名
FROM 表名
LIMIT 起始值,记录数量;（起始值可省略，默认为0，对应的id则为1）

2）ROWNUM用法具体使用看这里

SELECT 字段名
FROM 表名
WHERE ROWNUM <= number;

2.FULL JOIN（全连接）

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。
MySQL不支持FULL JOIN,而Oracle和SQL Server支持

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

3.SELECT INTO（复制数据信息）

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。
MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT。当然可以用AS来拷贝表结构及数据。CREATE TABLE 新表 AS SELECT * FROM 旧表

SELECT INTO的用法：

SELECT */column_name(s)
INTO newtable [IN externaldb]
FROM table1;
提示：新表将会使用 SELECT 语句中定义的列名称和类型进行创建。您可以使用 AS 子句来应用新名称。

4.约束方面

在UNIQUE、PRIMARY KEY、FOREIGN KEY、
CHECK、DEFAULT的使用中（就是除NOT NULL非空）有些些许不同。具体看这里

5.删除索引

Mysql查询优化（show profiles）

查看 profile功能是否开启
SELECT @@profiling;
若profiling的值为0；则未开启；可通过赋值方式开启。
SET profiling = 1;