Sql学习

一、SQL简介

SQL 是一种 ANSI（American National Standards Institute 美国国家标准化组织）标准的计算机语言。
是结构化查询语言（Structured Query Language），可以访问和处理数据库，SQL 对大小写不敏感。
RDBMS指关系型数据库管理系统（Relational Database Management System），是 SQL的基础，也是所有现代数据库系统的基础。

二、sql语法

select * from emp where not sal > 1500; //查询EMP表中 sal 小于等于 1500 的值。
Select * from emp where sal between 1500 and 3000; //between and(在之间的值)

like
% 表示多个字值，_ 下划线表示一个字符；
M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
%M% : 表示查询包含M的所有内容。
%M_ : 表示查询以M在倒数第二位的所有内容。
and&or
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
order by
ORDER BY 关键字用于对结果集进行排序，默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。
order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序，B 升序排列
order by A ,B desc 这个时候 A 升序，B 降序排列
即 desc 或者 asc 只对它紧跟着的第一个列名有效，其他不受影响，仍然是默认的升序。
insert
insert into select 和select into from 的区别
insert into user_bak select * from user where userid=5 //插入一行,要求表scorebak 必须存在。Table ‘test.user_bak’ doesn’t exist
SELECT * INTO user_bak FROM USER WHERE userid=5; //也是插入一行,要求表scorebak 不存在。
由于mysql不支持select into from，故因此用CREATE TABLE user_bak(SELECT * FROM USER WHERE userid=5);代替。
update/delete
执行没有 WHERE 子句的 update/delete 要慎重，再慎重。
在 MySQL 中可以通过设置 sql_safe_updates 这个自带的参数来解决，当该参数开启的情况下，你必须在update 语句后携带 where 条件，否则就会报错。
set sql_safe_updates=1; 表示开启该参数。
UPDATE user_bak SET STATUS=1; DELETE FROM user_bak; //You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
总结
通过SHOW VARIABLES LIKE ‘sql_safe%’;//查看此参数开闭情况。
如果设置了sql_safe_updates=1，那么update语句必须满足如下条件之一才能执行成功
1)使用where子句,并且where子句中列必须为prefix索引列(主键、组合索引第一列)
2)使用limit
3)同时使用where子句和limit(此时where子句中列可以不是索引列)
delete语句必须满足如下条件之一才能执行成功
1)使用where子句,并且where子句中列必须为prefix索引列
2)同时使用where子句和limit(此时where子句中列可以不是索引列)

三、高级语法

SELECT TOP 50 PERCENT * FROM Websites;//从 websites 表中选取前面百分之 50 的记录：
select top 5 * from table;
并非所有的数据库系统都支持 SELECT TOP 语句
别名
在下面的情况下，使用别名很有用：
在查询中涉及超过一个表
在查询中使用了函数
列名称很长或者可读性差
需要把两个列或者多个列结合在一起
NULL 函数
无法比较 NULL 和 0；它们是不等价的。
始终使用 IS NULL 来查找 NULL 值
微软的 ISNULL() 函数用于规定如何处理 NULL 值。
NVL()、IFNULL() 和 COALESCE() 函数也可以达到相同的结果
约束
1.NOT NULL 约束
NOT NULL 约束在默认的情况下，表的列接受 NULL 值。NOT NULL 约束强制列不接受 NULL 值。
ALTER TABLE Persons MODIFY Age int NOT NULL;//添加 NOT NULL 约束
ALTER TABLE Persons MODIFY Age int NULL;//删除 NOT NULL 约束
2. UNIQUE 约束
UNIQUE 约束唯一标识数据库表中的每条记录。UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
ALTER TABLE Persons ADD UNIQUE (P_Id);//在 “P_Id” 列创建 UNIQUE 约束
ALTER TABLE Persons DROP INDEX uc_PersonID;//撤销 UNIQUE 约束 Mysql
ALTER TABLE Persons DROP CONSTRAINT uc_PersonID;//撤销 UNIQUE 约束 Oracle
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);//定义多个列的 UNIQUE 约束
3.PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。主键必须包含唯一的值。主键列不能包含 NULL 值。
ALTER TABLE Persons ADD PRIMARY KEY (P_Id) ;//在 “P_Id” 列上创建 PRIMARY KEY 约束
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName);//定义多个列的 PRIMARY KEY 约束
ALTER TABLE Persons DROP PRIMARY KEY;//撤销 PRIMARY KEY 约束 Mysql
ALTER TABLE Persons DROP PRIMARY KEY ALTER TABLE Persons DROP CONSTRAINT pk_PersonID;//撤销 PRIMARY KEY 约束 Oracle
4. FOREIGN KEY 约束
FOREIGN KEY 约束用于预防破坏表之间连接的行为。FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);//在 “P_Id” 列创建 FOREIGN KEY 约束
ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id);//定义多个列的 FOREIGN KEY 约束
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders;//撤销 FOREIGN KEY 约束 Mysql
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders;//撤销 FOREIGN KEY 约束 Oracle
5. CHECK 约束
CHECK 约束用于限制列中的值的范围。
ALTER TABLE Persons ADD CHECK (P_Id>0);//在 “P_Id” 列创建 CHECK 约束
ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City=‘Sandnes’);//定义多个列的 CHECK 约束
ALTER TABLE Persons DROP CHECK chk_Person;//撤销 CHECK 约束 Mysql
ALTER TABLE Persons DROP CONSTRAINT chk_Person;/撤销 CHECK 约束 Oracle
6. DEFAULT 约束
DEFAULT 约束用于向列中插入默认值。 ALTER TABLE Persons ALTER City SET DEFAULT ‘SANDNES’;//在 “City” 列创建 DEFAULT 约束 Mysql ALTER TABLE
Persons ALTER City DROP DEFAULT;//撤销 DEFAULT 约束 Mysql ALTER TABLE
Persons MODIFY City DEFAULT ‘SANDNES’;//在 “City” 列创建 DEFAULT 约束 Oracle
ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;//撤销 DEFAULT 约束 Oracle
**union**
UNION 操作符合并两个或多个 SELECT 语句的结果。UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。
UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

JOIN
INNER JOIN 关键字在表中存在至少一个匹配时返回行。INNER JOIN 与 JOIN 是相同的。
LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。
RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。
FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。在某些数据库中，FULL OUTER JOIN 称为FULL JOIN。可以使用union关键字来实现所谓的全连接。
natural join 自然连接默认是匹配两个表列名相同的，然后不重复显示。
cross join 笛卡尔积，就是第一个表的行数乘以第二个表的行数。

可以参照MySQL的Sql join
在这里插入图片描述

通配符
MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
SELECT * FROM USER WHERE username RLIKE ‘^ [bat]’;
SELECT * FROM USER WHERE username REGEXP ‘^ [bat]’;
SELECT * FROM USER WHERE username REGEXP ‘^ [^bat]’;

在这里插入图片描述

四、函数

MID() 函数
MID() 函数用于从文本字段中提取字符。Oracle 中没有 MID 函数，有 substr 函数有类似功能
SELECT MID(name,1,4) AS ShortTitle FROM Websites;//从 “Websites” 表的 “name” 列中提取前 4 个字符
SELECT substr(name,1,4) AS ShortTitle FROM Websites;//从 “Websites” 表的 “name” 列中提取前 4 个字符
LEN() 函数
LEN() 函数返回文本字段中值的长度。
SELECT name, LENGTH(url) as LengthOfURL FROM Websites;//从 “Websites” 表中选取 “name” 和 “url” 列中值的长度 Mysql
FORMAT() 函数
FORMAT() 函数用于对字段的显示进行格式化。
SELECT name, url, DATE_FORMAT(Now(),’%Y-%m-%d’) AS date FROM Websites;//从 “Websites” 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期
HAVING 子句
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites ON access_log.site_id=Websites.id) GROUP BY Websites.name HAVING SUM(access_log.count) > 200;//总访问量大于 200 的网站
ROUND() 函数
ROUND() 函数用于把数值字段舍入为指定的小数位数。