数据库-SQL

作者: DoggieX | 来源:发表于2019-08-06 22:16 被阅读0次

引言

SQL（Structured Query Language，结构化查询语言）可以用于数据库创建和数据库查询，实现数据库结构的增删改查和数据库记录的增删改查。

命令综述

SQL命令根据功能可以分为四个种类：

数据定义语言（Data Definition Language，DDL）
数据操纵语言（Data Manipulation Language，DML）
数据控制语言（Data Control Language，DCL）
事务控制语言（Transaction Control Language，TCL）

数据定义语言（DDL）

DDL用于创建和修改数据库结构。
DDL的目的在于实现关系模式（以及一些额外结构，如索引等约束），并得到一个实际的关系数据库。
常用的命令有：CREATE、ALTER、DROP

数据操纵语言（DML）

DML用于操作数据库中的数据，包括对数据的插入、修改、删除及检索等操作。
在数据库的生命周期中，允许有新数据的插入，并允许已有数据的修改和删除。
常用命令有：INSERT INTO、UPDATE、DELETE、SELECT

数据控制语言（DCL）和事务控制语言（TCL）

DCL和TCL语句可用于与数据库维护及管理相关的多种过程。
DCL命令帮助实现数据的存取控制。
TCL用于数据库中的事务管理。

SQL数据类型

数据类型	释义
CHAR（n）	n个字符的固定长度字符串
VARCHAR（n）	最大长度为n个字符的可变长度字符串
INT	整型
NUMERIC（x，y）	x位数字，小数点后的位数为y
DATE	日期值（年，月，日）

SQL语法简要说明

分号
紧跟在每一条SQL语句后面，表示一个SQL命令的结束。一系列SQL语句里，分号表示每条SQL语句何时结束。
SQL关键字
即SQL命令中所使用的表和列的名字，不区分大小写。
考虑到可读性，SQL关键字通常使用大写，表名和列名使用小写。

CREATE TABLE

SQL命令CREATE TABLE用于关系表的创建和连接。
使用方法：
CREATE TABLE <表名> ( 列名数据类型 [列约束] ，...，[PRIMARY KEY (主码列，...)] [REFERENCES 表名(外码列)])
圆括号中的后半部分是关系表的描述。
下面介绍具体用法：

// 单主码列
CREATE TABLE team (
        team_id INT NOT NULL,
        team_name VARCHAR(20) NOT NULL,
        PRIMARY KEY (team_id)
);
// 单一外键
CREATE TABLE player (
        player_id INT NOT NULL,
        player_name VARCHAR(15) NOT NULL,
        rank INT,
        career_start_date DATE NOT NULL,
        PRIMARY KEY (player_id),
        FOREIGN KEY (team_id) REFERENCES team(team_id),
        // FOREIGN KEY (team_id) REFERENCES team  后面表的主码可以被省略
);
// 复合主码，多个外键
CREATE TABLE solo_point (
        team_id INT NOT NULL,
        player_id INT NOT NULL,
        point INT NOT NULL,
        PRIMARY KEY (team_id, player_id),
        FOREIGN KEY (team_id) REFERENCES team,
        FOREIGN KEY (player_id) REFERENCES player
);

DROP TABLE

命令DROP TABLE用于从数据库移除关系表
DROP TABLE team
当关系表中存在依赖关系时，不能优先删除被依赖的关系表，要按照顺序，从最外层开始删除。

INSERT INTO

INSERT INTO语句用于向已创建好的关系表中填入数据
INSERT INTO <tablename> VALUES(value1,value2,...)
value按照顺序对应table中的列
如果想仅插入table表中的指定列，可以按照下面的写法：
INSERT INTO <tablename>(col1,col3,col5,...) VALUES(value1,value3,value5,...)

SELECT

SELECT语句用于从数据库关系中检索数据，是最常用的SQL语句

//检索player表中的所有内容 * 号代表所有列
SELECT * FROM player;
// 检索player表中的player_id和player_name列
SELECT player_id,player_name FROM player;
// 检索派生属性
SELECT rank * 10 FROM player

SELECT还可以与一下关键字组合使用：WHERE、GROUP BY、HAVING、ORDER BY等

WHERE

SELECT语句可以包含WHERE条件，WHERE条件决定了应该检索哪些列、不检索哪些列

基础用法

//检索天梯积分大于8000的选手
SELECT * FROM player WHERE rank > 8000;
// = 等于
// < 小于
// > 大于
// <= 小于等于
// >= 大于等于
// != 不等于
// <> 不等于（可选标识）

一个WHERE子句中可以使用多个比较表达式，用布尔型逻辑操作符AND或OR进行连接

// 检索1队中积分大于8000的选手
SELECT * FROM player WHERE team_id = 1 AND rank > 8000;
// 检索所有选手中名叫“张三”和“李四”的
SELECT * FROM player WHERE player_name = '张三' OR player_name = '李四';

DISTINCT

DISTINCT关键字的作用是在SELECT语句执行后队结果进行去重

// 检索所有选手所在的省份
SELECT DISTINCT province FROM player;

ORDER BY

ORDER BY关键字的作用是对SELECT查询结果的某一列或多个列进行排序

// 对所有选手的rank积分进行排序  默认为升序排列
SELECT * FROM player ORDER BY rank;
// 降序排列
SELECT * FROM player ORDER BY rank DESC;
// 对多列排序 对积分降序  对年龄升序
SELECT * FROM player ORDER BY rank DESC,age;

LIKE

在WHERE子句中使用LIKE关键字检索某列的值与给定的条件匹配的记录

// %是一个通配符，表示一个空串或任意字符串
// 检索所有李姓选手  李为第一个字符  后面为任意字符%
SELECT * FROM player WHERE name LIKE '李%';
// _是另一个通配符，表示任意的一个字符
// 检索所有李姓单名的选手
SELECT * FROM player WHERE name LIKE '李_';

聚合函数

为了计算和统计查询结果，SQL提供了如下几个聚合函数（aggregate functions）：COUNT、SUM、AVG、MIN以及MAX

// 统计所有选手的数量
SELECT COUNT(*) FROM player;
// 统计1队中所有选手获得的MVP总数
SELECT SUM(mvp) FROM player WHERE team_id = 1;
// 统计1队中所有选手rank平均分
SELECT AVG(rank) FROM player WHERE team_id = 1;
// 检索1队中rank分最低的选手的分数
SELECT MIN(rank) FROM player WHERE team_id = 1;
// 检索1队中rank分最高的选手的分数
SELECT MAX(rank) FROM player WHERE team_id = 1;

COUNT函数可以用*也可以指定列名，通常情况下检索结果是相同的。当指定列名中存在空值时，指定列名会跳过该记录，而*不会，此时会得到不同的统计结果。

GROUP BY

聚合函数通常和GROUP BY关键字一起使用，这样就可以实现对组的聚合操作，组是一系列相关联的数据记录构成的集合

// 统计每个队伍的mvp总数以及rank平均分
SELECT team_id, SUM(mvp), AVG(rank) FROM player GROUP BY team_id;
// 还可以对多个列同时进行分组
SELECT team_id, SUM(mvp), AVG(rank) FROM player GROUP BY team_id, sex;

HAVING

HAVING子句决定GROUP BY的查询结果哪些展示哪些不展示，HAVING子句必须同时包含GROUP BY子句

// 查询队伍的mvp总数大于等于10个且rank平均分在7000之上的队伍
SELECT team_id, SUM(mvp), AVG(rank) FROM player GROUP BY team_id HAVING SUM(mvp) >= 10 AND AVG(rank) > 7000;

嵌套查询

嵌套查询也称为内部查询，包含了嵌套查询的查询称为外部查询

// 查询1队中rank分数低于平均值的选手
SELECT * FROM player WHERE team_id = 1 AND rank < (SELECT AVG(rank) FROM player);

IN

IN关键字用于单个值与多个值组合而成的集合之间的比较

// 查询rank分数为7000、7500、8000整的玩家
SELECT * FROM player WHERE rank IN (7000,7500,8000);
// 查询队伍冠军数量大于5个的所有选手
SELECT * FROM player WHERE team_id IN(SELECT team_id FROM team WHERE champion > 5)

JOIN

使用JOIN操作符可以实现多个表的查询

// 查询所有选手的姓名以及队名
// 相同的列名需要用 表名.列名 区分
SELECT player.name, team.name FROM player, team WHERE player.team_id = team.team_id;

WHERE player.team_id = team.team_id 是一个JOIN条件，若没有JOIN条件，得出的结果是两个结果的笛卡尔积，将会有m x n行结果。

别名

查询中任何地方都可以使用短的别名来替代长的关系名

// 用别名重命名表的名称
SELECT p.name, t.name FROM player p, team t WHERE p.team_id = t.team_id;
// 用别名重命名列的名称
SELECT p.name pname, t.name tname FROM player p, team t WHERE p.team_id = t.team_id;
// 同使用AS关键字
SELECT p.name AS pname, t.name AS tname FROM player p, team t WHERE p.team_id = t.team_id;

注意：WHERE子句中的列名不能使用别名

多关系连接

一个查询可以包含多个JOIN来连接多个关系

ALTER TABLE

ALTER TABLE命令用于修改已创建的关系结构

// 增加一列 位置
ALTER TABLE player ADD(position varchar(10));
// 删除一列 位置
// ALTER TABLE player DROP(position);

UPDATE

SQL中UPDATE命令用于修改存于数据关系中大数据

// 修改1队队名为FGNB
UPDATE team SET team_name = 'FGNB' WHERE team_id = 1;

DELETE

SQL中DELETE命令用于删除数据库关系中队数据

// 删除姓名叫 李二狗 的队员数据
DELETE FROM player WHERE player_name = '李二狗';

CREATE VIEW 和 DROP VIEW

视图（View）是SQL中的一种机制，该机制允许查询的结构保存在RDBMS中。视图也称为虚表，因为它没有任何的物理存储的数据。

// 使用方法 
CREATE VIEW view_name_a SELECT ...
// 也可以和实表一样执行查询操作
SELECT * FROM view_name_a;
// 移除视图
DROP VIEW view_name_a;

集合运算

SQL同样支持标准的集合运算（set operator）：并运算（UNION）、交运算（INTERSECT）和差运算（EXCEPT/MINUS）。集合运算用于合并两条或多条能相容的查询结果。
定义两个集合相容，要求这两个集合列的数目相同，且每个列的数据类型相匹配。
使用方法：
SELECT A UNION SELECT B;
SELECT A INTERSECT SELECT B;
SELECT A MINUS SELECT B;

关系与自身的连接（自连接）

若一个关系有一个外码参照其本身的主码，则在查询中这个关系就可以和它自己建立连接。这样的连接也称为自连接（SELF-JOIN）。

OUTER JOIN

当一个关系外码的值和另一个关系主码的值相匹配时，JOIN运算能合并这两个关系记录。

OUTER JOIN (外连接)语句是JOIN(连接)的变体操作，它将保留那些在连接中不能匹配的记录。

有三种形式的外连接：LEFT OUTER JOIN（左外连接）、RIGHT OUTER JOIN（右外连接）和FULL OUTER JOIN（全外连接）。

左外连接
对左外连接来说，所有出现在左外连接运算之前（左边）关系中的记录都会被保留在查询结果中，包括那些没能满足匹配条件的记录。若关系中的记录以这种方式与其他关系的记录建立连接，那些不能匹配的记录将会被设为空值。
右外连接
对于右外连接来说，所有出现在有外连接运算之后（右边）关系中的记录都会被保留在查询结果中，包括那些没能满足匹配条件的记录，并用空值表示。
全外连接
对于全外连接来说，即使两个关系没有相匹配的记录，其所有记录也都会保留在查询结果里。

IS NULL

在某一查询中，包含某一列的值与空值相比较时，就需要在WHERE语句中使用IS NULL。

EXISTS

在查询的内查询（嵌套查询）中，若使用了外查询SELECT部分列出的某关系的某列或某些列，则将这个内查询称为相关子查询。这种情况下，EXISTS操作就可以用来测试内查询的结果是否为空。

SELECT * FROM table1 t1 WHERE EXISTS (SELECT * FROM table2 t2 WHERE t1.id = t2.id)

NOT

NOT操作配合条件比较使用，返回布尔值TRUE或FALSE。