结构化查询语言SQL
SQL语法
- 特点
- 非过程化 ：用户不需要了解SQL的具体操作方法，通过SQL描述想要获取的结果集合的条件，结果如何取得是由数据库查询优化系统负责具体的执行
- 简单易学
- 易迁移
- 高度统一
- 划分为四种语言集
- DDL数据定义语言：（模式）定义修改删除，create、Alter、drop Table等操作
- DQL数据查询语言：查询数据，投影选择连接，以select命令开始
- DML数据操作语言：（外模式）插入删除更新，insert、delete、updata
- DCL数据控制语言：控制用户的访问权限，grant、revoke
数据类型
- 字符串
- char（n）n指定了定长字符串的长度。如果输入字符串长度小于n,则会在尾部用空格补齐到n个字符；如果大于n则会从右边自动截断，到仅剩n个字符为止
- varchar（n） n为字符串的最大长度，输入的字符串不会有补齐操作，以变长的形式
- 数值
- smallint 整型，长度是2个字节，取值范围[-32768~32767]
- interget 整型，长度是4个字节，取值范围[-2147483648~2147483647]
- bigint 整型，长度是8个字节，取值范围[+-9.22*10的18次方]
- float（p） 浮点类型，P代表的是浮点类型中小数点前后的总位数之和
- real 单精度浮点类型，取值范围
- numeric（p，s）p为小数点前后的总位数，s表示小数点后的位数
- decimal（p，s）精度不受p和s的限制，p、s决定精度下限
- double precision 双精度类型，长度为8个字节，取值范围为
- 日期
- date 由年月日组成，yyyy-mm-dd
- timestamp 由年月日时分秒组成 yyyy-mm-dd hh：mm：ss
- 常用类型
- blob/clob 大对象 blob是二进制文件，clob是文件类型
- boolean 布尔类型，true、false、unknown，unknow和null等价
表模式定义
- 创建数据库
create database db01; - 创建表
create TABLE stu01( s_id INT, s_name VARCHAR(10), s_age INT, s_city VARCHAR(10) ); - alter table修改表的基本模式
- add column 增加列
ALTER TABLE stu01 ADD COLUMN s_sex VARCHAR(10); - drop column 删除列
ALTER TABLE stu01 DROP COLUMN s_sex ; - alter column 修改列
- 删除表，有视图、触发器、外键则删除不成功
DROP TABLE stu01; - 强制删除，包括关联的视图
DROP TABLE stu01 CASCADE;
- add column 增加列
数据完整性检验
- 域完整性
- null约束：指定一列的值是否为null
- check约束：用来检查输入的值是否满足某一约束条件
- defaul约束：如果输入数据中没有指定该列具体的值，可以直接使用default约束指定的默认值
- 实体完整性
- 主键：值唯一，不能null
- unique：只能有唯一的值，可以null
- 参照完整性
- 外键：指定的键值组合和键值相对应
插入删除更新
- insert into
INSERT INTO stu01 VALUES(01,'chen**',21,'chengdu',07); - delete from
delete FROM stu01 WHERE(s_name='chen**'); - update
UPDATE stu01 SET s_name = 'cjn' WHERE s_name = 'chen**';
简单查询
- select from where
SELECT * FROM stu01 WHERE s_id = 1;
连接操作
- from后跟多个表
- 本质是多个表之间做笛卡尔积，衍生出自然连接，θ连接
SELECT * FROM t1,t2,t3 WHERE t1.c1 = 1; - 对t1,t2做笛卡尔积
SELECT * FROM t1 CROSS JOIN t2; - 等值内连接
SELECT * FROM t1 INNER JOIN t2 ON t1.c1=t2.c1; - 自然连接
SELECT *FROM t1 NATURAL JOIN t2; - 连接结果来看分为
- 等值连接或者叫内连接 inner join：只有符合条件的连接结果
- 外连接：外表就是左表，内表就是右表
- 左连接 left join，左外连接：返回包括左表中的所有记录和右表中连接字段相等的记录，右表中没有对应的值返回null。
- 右连接 right join，右外连接：返回包括右表中的所有记录和左表中连接字段相等的记录。
- 全连接、全外连接 full join：返回左右表中所有的记录和左右表中连接字段相等的记录。
- 半连接
- semi join
- anti-semi join
#创建A1表
-- CREATE TABLE A1(
-- id INT,
-- name VARCHAR(20));
-- INSERT INTO a1 VALUES(1,'小王'),(2,'小李'),(3,'小刘');
-- #创建B2表
-- CREATE TABLE B2(
-- id int,
-- A_id int,
-- job VARCHAR(20));
-- INSERT INTO B2(id,A_id,job) VALUES(1,2,'老师'),(2,4,'程序员');
#内连接、等值连接:只保留负责条件的结果
SELECT * FROM A1;
SELECT * FROM B2;
SELECT * FROM A1 INNER JOIN B2 on A1.id=B2.A_id;
SELECT a.name,b.job FROM A1 a INNER JOIN B2 b on a.id=b.A_id;
#左连接：保留左边和右边符合条件的结果
SELECT * FROM A1 LEFT JOIN B2 ON A1.id = B2.A_id;
SELECT a.name,b.job FROM a1 a LEFT JOIN b2 b ON a.id=b.A_id;
#右连接：保留右边和左边符合条件的结果
SELECT * FROM A1 RIGHT JOIN B2 on A1.id=B2.A_id;
SELECT a.NAME,b.job from a1 a RIGHT JOIN b2 b on a.id=b.A_id;
#全外连接：保留两边的结果 FULL JOIN
集合操作
- union:并，关键字两端的结果集并集操作
- 两个集合合并，去重
- except:差，从左侧的结果集中排出左侧的结果集
- 左边-右边相同的值，左边剩下的为结果
- intersect:交集，取两个集合中交集部分
- 保留两遍相同的值
- 默认隐含去重 distinct，不去重 all
SELECT * FROM T1;
SELECT * FROM T2;
SELECT * FROM T1 UNION SELECT * FROM T2;
SELECT * FROM T1 EXCEPT SELECT * FROM T2;
SELECT * FROM T1 INTERSECT SELECT * FROM T2;
SELECT * FROM T1 UNION DISTINCT SELECT * FROM T2;
SELECT * FROM T1 UNION ALL SELECT * FROM T2;
聚集于分组操作
- count([all/distinct]expression|*)
- count(*)统计所有元组包括null值的数量，count(colname)只统计非null值的数量。
- sum([all/distinct]expression)
- 求和指定列
- AVG 求平均值
- max求最大值，min最小
SELECT * FROM t1;
SELECT count(*) FROM T1;
SELECT count(c2) FROM T1;
SELECT COUNT(DISTINCT T1.c1) FROM T1;
SELECT SUM(c1) FROM t1;
SELECT avg(c1) FROM t1;
SELECT MAX(c1) FROM t1;
- order by 排序
SELECT column_list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
- group by 分组
- 基础语法
select column1，column2 from tablename GROUP BY column1/2
- 基础语法
SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2
索引
索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。
- 基本语法
CREATE INDEX index_name ON table_name; - 单列索引
CREATE INDEX index_name ON table_name (column_name); - 唯一索引
CREATE UNIQUE INDEX index_name on table_name (column_name);
视图与物化视图
数据库三种模式
- 模式：逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。又称概念模式或逻辑模式。是对所有用户数据逻辑结构和特征的所有描述。主要由数据库设计者进行DDL语言进行描述和定义。体现了数据库的整体观。
- 一个数据库只有一个模式；
- 是数据库数据在逻辑级上的视图；
- 数据库模式以某一种数据模型为基础；
- 定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系。
- 外模式：也称子模式或用户模式，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。对应于用户级，是某个或某几个用户所能看到的数据库的数据视图，是从模式导出的一个子集，故又称子模式。用户主要通过DML语言对外模式数据进行操作。外反应了数据库的用户观。
- 一个数据库可以有多个外模式；
- 外模式就是用户视图；
- 外模式是保证数据安全性的一个有力措施。
- 内模式：也称存储模式（Storage Schema），它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式（例如，记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储；索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何规定）。
- 一个数据库只有一个内模式；
- 一个表可能由多个文件组成，如：数据文件、索引文件。它是数据库管理系统(DBMS)对数据库中数据进行有效组织和管理的方法下一题;
- 为了减少数据冗余，实现数据共享；
- 为了提高存取效率，改善性能。
视图
- 视图是虚表，本身不保存数据，是一种外模式，基本表改变视图中的数据也将会改变，改变视图映射到原表。
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];
物化视图
- 本身保存数据，提高查询的访问效率
- 对物化视图的操作直接作用到物化视图本身。
- 基本表数据改变会同步物化视图
- 对基本表频繁的增删查改会增加物化视图的开销，降低性能
CREATE MATERIAL VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];