SQL基础笔记

一些重要的 SQL 命令

SELECT – 从数据库中提取数据
UPDATE – 更新数据库中的数据
DELETE – 从数据库中删除数据
INSERT INTO – 向数据库中插入新数据
CREATE DATABASE – 创建新数据库
ALTER DATABASE – 修改数据库
CREATE TABLE – 创建新表
ALTER TABLE – 变更（改变）数据库表
DROP TABLE – 删除表
CREATE INDEX – 创建索引（搜索键）
DROP INDEX – 删除索引

use xxx; 命令用于选择数据库。
set names utf8; 命令用于设置使用的字符集。
从表中选取所有记录：
SELECT * FROM table_name;

SQL 对大小写不敏感：如SELECT 与 select 是相同的。
某些数据库系统要求在每条 SQL 语句的末端使用分号。

1.SQL SELECT 语句

SELECT column_name,column_name
FROM table_name;
从表中选择指定列
SELECT * FROM table_name;
从表中选取所有列

2.SQL SELECT DISTINCT 语句

SELECT DISTINCT column_name,column_name
FROM table_name;
从表的指定列中选取唯一不同的值，也就是去掉指定列的重复值

3.SQL WHERE 子句

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;

(1)WHERE子句

SELECT * FROM table_name
WHERE column_name=’xxx’;
从表中选取指定列名为 “xxx” 所在行的所有内容
SELECT * FROM table_name
WHERE id=123;
从表中选取列名为123所在行的所有内容

SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）
如果是数值字段，不使用引号。

(2)WHERE子句中的运算符

= 等于
<> 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN 在某个范围内
LIKE 搜索某种模式
IN 指定针对某个列的多个可能值

(3)笔记

①Where 子句
搜索 empno 等于 7900 的数据：
select * from table_name where empno=7900;

②Where +条件（筛选行）
条件：列，比较运算符，值
比较运算符包涵：= , > , < , >= , <= , != ,<> 表示（不等于）
搜索 ename 等于“SMITH”的数据：
select * from table_name where ename=’SMITH’;
例子中的 SMITH 用单引号引起来，表示是字符串，字符串要区分大小写。

③逻辑运算
And:与 同时满足两个条件的值。
查询 emp 表中 sal 列中大于 2000 小于 3000 的值：
select * from emp where sal > 2000 and sal < 3000;

Or:或 满足其中一个条件的值。
查询 emp 表中 sal 大于 2000 或 comm 大于500的值：
select * from emp where sal > 2000 or comm > 500;

Not:非 满足不包含该条件的值。
查询 emp 表中 sal 小于等于 1500 的值：
select * from emp where not sal > 1500;

逻辑运算的优先级（从左到右依次降低）：
() not and or

④特殊条件
空值判断： is null
查询 emp 表中 comm 列中的空值：
select * from emp where comm is null;

between … and … (在…和…之间的值)
查询 emp 表中 sal 列中大于 1500 的小于 3000 的值：
select * from emp where sal between 1500 and 3000;
注意：大于等于 1500 且小于等于 3000， 1500 为下限，3000 为上限，
下限在前，上限在后，查询的范围包涵有上下限的值。

in
查询 emp 表 sal 列中等于 5000，3000，1500 的值：
select * from emp where sal in (5000,3000,1500);

like模糊查询
查询 emp 表中 ename 列中有 M 的值，M 为要查询内容中的模糊信息：
select * from emp where ename like ‘M%’;

% 表示多个字值，_ 下划线表示一个字符。
M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。

⑤不带比较运算符的 WHERE 子句：

WHERE 子句并不一定带比较运算符，当不带运算符时，会执行一个隐式转换。
当 0 时转化为 false，1 转化为 true。

SELECT column_name FROM table_name WHERE 0
则会返回一个空集。因为每一行记录 WHERE 都返回 false。

SELECT column_name FROM table_name WHERE 1
则返回表的所有行中指定列的值。因为每一行记录 WHERE 都返回 true。

4.SQL AND & OR 运算符

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

(1)AND 运算符

从指定表中选取列名1为 “xxx” 且列名2数字大于50的所有行：
SELECT * FROM table_name
WHERE column_name1=’xxx’
AND column_name2 > 50;

(2)OR 运算符

从指定表中选取列名1为 “xxx” 或者列名2为 “yyy” 的所有行：
SELECT * FROM table_name
WHERE column_name1=’xxx’
OR column_name2=’yyy’;

(3)结合 AND 与 OR

使用圆括号来组成复杂的表达式。
从指定表中选取列名1数字大于15且 列名2为 “xx” 或列名3为 “yy” 的所有行：
SELECT * FROM table_name
WHERE column_name1 > 15
AND (column_name2=’xx’ OR column_name3=’yy’);

5.SQL ORDER BY 关键字

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

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;

(1)ORDER BY

从指定表中选取所有行并按照指定列升序排序：
SELECT * FROM table_name
ORDER BY column_name;

(2)ORDER BY DESC

从指定表中选取所有行并按照指定列降序排序：
SELECT * FROM table_name
ORDER BY column_name DESC;

(3)ORDER BY 多列

从指定表中选取所有行并按照列名1和列名2排序：
SELECT * FROM table_name
ORDER BY column_name1,column_name2;
先按照column_name1排序，再按照column_name2排序…

order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序，B 升序排列
order by A ,B desc 这个时候 A 升序，B 降序排列
即 desc 或者 asc 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序。

笔记：

MySQL 里分页一般用 LIMIT 来实现:
select* from article LIMIT 1,3
与
select * from article LIMIT 3 OFFSET 1
上面两种写法都表示取 2、3、4 三条条数据：
当 LIMIT 后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量，
例如：
select * from article LIMIT 1,3
就是跳过 1 条数据，从第 2 条数据开始取，取 3 条数据，也就是取 2、3、4 三条数据。
当 LIMIT 后面跟一个参数的时候，该参数表示要取的数据的数量。
例如：
select * from article LIMIT 3 表示直接取前三条数据，类似 sqlserver 里的 top 语法。
当 LIMIT 和 OFFSET 组合使用的时候，
LIMIT 后面只能有一个参数，表示要取的的数量，OFFSET表示要跳过的数量 。
例如：
select * from article LIMIT 3 OFFSET 1
表示跳过 1 条数据，从第 2 条数据开始取，取3条数据，也就是取 2、3、4 三条数据。

6.SQL INSERT INTO 语句

INSERT INTO 语句可以有两种编写形式。

第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
INSERT INTO table_name
VALUES (value1,value2,value3,…);
没有指定要插入数据的列名的形式需要列出插入行的每一列数据

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

笔记：
insert into select 和select into from 的区别，例如：

insert into scorebak select * from socre where neza=’neza’
插入一行,要求表scorebak必须存在
select * into scorebak from score where neza=’neza’
也是插入一行,要求表scorebak不存在

7.SQL UPDATE 语句

UPDATE table_name
SET column1=value1,column2=value2,…
WHERE some_column=some_value;

从表中把列名3为 “xxx” 所在行的列名1更新为 5000，列名2改为 USA：
UPDATE table_name
SET column_name1=’5000′, column_name2=’USA’
WHERE column_name3=’xxx’;

Update 警告

如果我们省略了 WHERE 子句，例如：
UPDATE Websites
SET alexa=’5000′, country=’USA’;
执行以上代码会将 Websites 表中所有数据的 alexa 改为 5000，country 改为 USA。
因此执行没有 WHERE 子句的 UPDATE 要慎重！

笔记：

在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，
当该参数开启的情况下，必须在update 语句后携带 where 条件，否则就会报错。
set sql_safe_updates=1; 表示开启该参数

8.SQL DELETE 语句

DELETE FROM table_name
WHERE some_column=some_value;

注意 SQL DELETE 语句中的 WHERE 子句！
WHERE 子句规定哪条记录或者哪些记录需要删除。
如果省略了 WHERE 子句，所有的记录都将被删除！

从表中删除列名1为 “xx” 且列名2为 “yy” 的所有行。
DELETE FROM table_name
WHERE column_name1=’xx’ AND column_name2=’yy’;

删除所有数据

可以在不删除表的情况下，删除表中所有的行。这意味着表结构、属性、索引将保持不变：
DELETE FROM table_name;
在删除记录时要格外小心！因为不能重来！

笔记：
1.SQL关于删除的三个语句：DROP、TRUNCATE、 DELETE 的区别
DROP:
DROP test;
删除表test，并释放空间，将test删除的一干二净。
drop：删除表内容和结构，释放空间，没有备份表之前要慎用；

TRUNCATE:
TRUNCATE test;
删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在。
truncate：删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用；

DELETE:
delete：删除表的内容，表的结构还存在，不释放空间，可以回滚恢复；
(1)删除指定数据
例如：删除表test中年龄等于30且国家为US的数据
DELETE FROM test WHERE age=30 AND country=’US’;

(2)删除整个表
仅删除表test内的所有内容，保留表的定义，不释放空间。
DELETE FROM test 或者 DELETE FROM test;
DELETE * FROM test 或者 DELETE * FROM test;

2.truncate table
命令将快速删除数据表中的所有记录，但保留数据表结构。
这种快速删除与 delete from 数据表的删除全部数据表记录不一样，
delete 命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，
而 truncate 命令删除的数据是不可以恢复的。

相同点:
truncate 和不带 where 子句的 delete, 以及 drop 都会删除表内的数据。
不同点:
(1) truncate 和 delete 只删除数据不删除表的结构(定义) ，
drop 语句将删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index);
依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。
(2)delete 语句是 dml, 这个操作会放到 rollback segement 中, 事务提交之后才生效;
如果有相应的 trigger, 执行的时候将被触发。
truncate, drop 是 ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚。
操作不触发 trigger。
(3)delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动。
显然 drop 语句将表所占用的空间全部释放 。
truncate 语句缺省情况下见空间释放到 minextents 个 extent,
除非使用 reuse storage; truncate会将高水线复位(回到最开始)。
(4)速度：一般来说: drop > truncate > delete 。
(5)安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候。
使用上, 想删除部分数据行用 delete, 注意带上 where 子句。 回滚段要足够大。
想删除表, 当然用 drop。
想保留表而将所有数据删除。如果和事务无关, 用 truncate 即可。如果和事务有关, 或者想触发 trigger, 还是用 delete。
如果是整理表内部的碎片, 可以用 truncate 跟上 reuse stroage, 再重新导入/插入数据。

3.mysql 中可以通过参数 sql_safe_updates 来限制 update/delete，防止全表更新或删除。
以下 3 种情况在采用此参数的情况下都不能正常进行操作：
(1) 没有加where条件的全表更新操作;
(2) 加了 where 条件字段，但是 where 字段没有走索引的表更新;
(3) 全表 delete 没有加 where 条件或者 where 条件没有走索引。
这三种情况下都会抛出异常，无法执行。

下面是 sql_safe_updates 变量为 0 和 1 时的取值说明：
sql_safe_updates 有两个取值 0 和 1， 即 off 和 on。
sql_safe_updates = 1 (或 sql_safe_updates = on ) 时，
不带 where 和 limit 条件的 update 和 delete 操作语句是无法执行的，
即使是有 where 和 limit 条件但不带 key column 限制条件的 update 和 delete 也不能执行。
sql_safe_updates = 0 (或 sql_safe_updates = off ) 时，
无 where 和 limit 条件的 update 和 delete 操作将会顺利执行。
很显然，在一般的 mysql 中此参数的默认值是 1。
在 sql_safe_updates = on 时，采取删除或更新全表时抛出的错误码为 1175。

set sql_safe_updates=1; // 安全模式打开状态
set sql_safe_updates=0; // 安全模式关闭状态
如果设置了 sql_safe_updates=1，那么 update 语句必须满足如下条件之一才能执行成功：
(1) 使用 where 子句, 并且 where 子句中列必须为 prefix 索引列。
(2) 使用 limit。
(3) 同时使用 where 子句和 limit (此时 where 子句中列可以不是索引列)。
delete 语句必须满足如下条件之一才能执行成功：
(1) 使用 where 子句, 并且 where 子句中列必须为 prefix 索引列。
(2) 同时使用 where 子句和 limit (此时 where 子句中列可以不是索引列)。