SQL 一些知识

关于sql的一些知识

目录

sql_safe_updates

drop、delete、truncate区别

insert into select 和select into  from 的区别

获取指定条数的数据

SQL Server / MS Access 语法

MySQL 语法

Oracle 语法

通配符

JOIN

UNION  和 UNION  ALL

约束（Constraints）

NOT NULL - 指示某列不能存储 NULL 值

创建PRIMARY KEY|UNIQUE约束

 撤销UNIQUE 约束

 撤销PRIMARY KEY|FOREIGN KEY |CHECK约束

索引

创建索引

删除索引(MYsql)

视图

创建视图

 查询视图

删除视图

---

 

sql_safe_updates

set sql_safe_updates=1;  // 安全模式打开状态
       set sql_safe_updates=0;  // 安全模式关闭状态 

如果设置了 sql_safe_updates=1，

update 语句必须满足如下条件之一才能执行成功

1) 使用 where 子句, 并且 where 子句中列必须为 prefix 索引列。

 2) 使用 limit。

delete 语句必须满足如下条件之一才能执行成功。

1) 使用 where 子句, 并且 where 子句中列必须为 prefix 索引列。

 2) 同时使用 where 子句和 limit (此时 where 子句中列可以不是索引列)。

drop、delete、truncate区别

相同点：drop、delete、truncate 都是删除表的内容。

不同点：

delete 语句是 dml, 这个操作会放到 rollback segement 中, 事务提交之后才生效; 如果有相应的 trigger, 执行的时候将被触发。

truncate, drop 是 ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚。 操作不触发 trigger

truncate 和 delete 只删除数据不删除表的结构(定义) 

drop 语句将删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index); 依赖于该表的存储过程/函数将保留, 

 delete：删除表的内容，表的结构还存在，不释放空间，

可以回滚恢复；

 drop：删除表内容和结构，释放空间，没有备份表之前要慎用；

 truncate：删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用；

执行速度：

drop > truncate > delete

insert into select 和select into  from 的区别

MySQL 数据库不支持 SELECT ... INTO 语句

         insert into scorebak select * from socre where neza='neza' --插入一行,要求表scorebak 必须存在

         select * into scorebak from score where neza='neza'           --也是插入一行,要求表scorebak 不存在

获取指定条数的数据

SQL Server / MS Access支持 SELECT TOP 语句。

MySQL 支持 LIMIT 语句来选取指定的条数数据

Oracle 可以使用 ROWNUM 来选取。

SQL Server / MS Access 语法

SELECT TOP number|percent column_name(s)  FROM table_name;

-- 前5行

select top 5 * from table

-- 50%数据

SELECT TOP 50 PERCENT * FROM Websites;

MySQL 语法

SELECT column_name(s) FROM table_name LIMIT number;

SELECT * FROM Persons LIMIT 5;

Oracle 语法

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

SELECT * FROM Persons WHERE ROWNUM <=5;

通配符

% 代表任意多个字符（可以是 0 个），_ 代表一个字符，__ 代表两个字符。

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

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

下面的 SQL 语句选取 name 以 A 到 H 字母开头的数据：

SELECT * FROM Websites WHERE name REGEXP '^[A-H]';

下面的 SQL 语句选取 name 不以 A 到 H 字母开头的数据：

SELECT * FROM Websites WHERE name REGEXP '^[^A-H]';

JOIN

 

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

• INNER JOIN：如果表中有至少一个匹配，则返回行（INNER JOIN 与 JOIN 是相同的。）
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

MySQL 暂不支持 FULL JOIN, 要实现完全外部链接需要额外处理。

-- MySQL实现完全外部链接，要使用 UNION 将一个左链接、和一个右链接去重合并。

1、 on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。

 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

UNION  和 UNION  ALL

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。不允许重复

UNION ALL 允许重复

约束（Constraints）

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

NOT NULL - 指示某列不能存储 NULL 值

删除 NOT NULL 约束   

ALTER TABLE 表名 MODIFY 列名 int NULL

创建PRIMARY KEY|UNIQUE约束

ALTER TABLE 表名 ADD [PRIMARY KEY|UNIQUE]  (列名)

定义多个列的 约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 [PRIMARY KEY|UNIQUE] (列名1,列名2)

 撤销UNIQUE 约束

MySQL:

ALTER TABLE 表名 DROP INDEX 约束名

 SQL Server / Oracle / MS Access：

ALTER TABLE 表名 DROP CONSTRAINT 约束名

 撤销PRIMARY KEY|FOREIGN KEY |CHECK约束

 MySQL：

ALTER TABLE 表名 DROP [PRIMARY KEY|(FOREIGN KEY 约束名)| (CHECK 约束名)]

索引

创建索引

CREATE INDEX 索引名 ON 表名 (列名1,列名2)

删除索引(MYsql)

ALTER TABLE 表名 DROP INDEX 索引名

视图

创建视图

CREATE VIEW <视图名> AS <SELECT语句> 

 查询视图

SELECT * FROM 视图名

DESCRIBE 视图名 （DESCRIBE 一般情况下可以简写成 DESC）

删除视图

DROP VIEW 视图名