DBMS数据库管理系统
Database Management System
1.有5类
HDB 层次数据库
RDB 关系数据库
OOOB面向对象数据库
XMLDB
KVS 键值存储系统
2.RDBMS :Relational DBMS
有代表性的有5种
Oracle(甲骨文)
SQL Server(Microsoft)
DB2(IBM)
MySQL
PostgreSQL
SQL语句三剑客 关键字+表名+列名
DDL Data Definition Language 数据定义语言
指令：
CREATE DATABASE/TABLE
DROP DATABASE/TABLE
ALTER DATABASE/TABLE
DML Manipulation 操纵
SELECT
INSERT
UPDATE
DELETE (FROM)
DCL Control 控制
COMMIT
ROLLBACK
GRANT
REVOKE
SQL的书写规范
ANSI(美国国家标准协会)和ISO(国际标准化组织)每隔几年联合修订SQL的标准
关键字大写
表名首字母大写
列名等小写
常数: 字符串,日期,数字
字符串,日期需要用单引号括起来,AS别名时如为中文要用双引号
单词之间用半角空格或换行符分割
表的创建
1.约束可以在定义列时设置,也可以在语句的末尾设置(NOT NULL约束只能以列为单位设置)
2.product_id不能写作product-id,标准SQL不允许使用连字符- 作为列名等名称
常用4种数据类型
INTEGER
存储整型
CHAR(n)
定长字符串,不够n需要用半角空格补足
n是可以存储的字符串的最大长度,中文占两到三个字符,由编码集定
VARCHAR (Oracle中为VARCHAR2)
可变长字符串
DATE
存储年月日(Oracle中DATE还包含时分秒)
表定义的修改
ALTER TABLE <表名> ADD/DROP COLUMN <列的定义>
列的定义: 列名+类型+约束
(Oracle 和SqlServer中省略COLUMN)
变更表名
Oracle和SqlServer:ALTER TABLE p1 RENAME TO p2
MySQL:RENAME TABLE p1 to p2
匹配查询(query)
子句(clause)以SELECT和FROM等关键字作为起始的短语,书写时以子句为单位进行换行
删除重复行DISTINCT
1.只能写在第一个列名前
2.可以查询出NULL,NULL被视为一类
Oracle查询语句中不允许省略FROM子句,要用dual临时表
SELECT 常数
FROM dual
比较运算符
1.可以对几乎所有数据类型做比较,字符串的比较是按照字典序的
2.不能对NULL进行比较,它有特定的语法: IS (NOT) NULL
3.NULL / 0 结果为NULL ; 5 / 0 报错
4.标准SQL里 不等于是 <> 而不是 !=
逻辑运算符 NOT AND OR
1. 对比较运算符等返回的真值进行操作
2. 真值表(true table) 若真为1,假为0,则 AND称为逻辑积,OR称为逻辑和
3.SQL的逻辑运算称为三值逻辑,TRUE，FALSE,UNKNOWN
4.WHERE NOT price >1000 等同于 WHERE price <=1000
5.AND优先于OR执行,可通过括号强化处理
聚合查询
1.count(*) *星号代表所有的行,count(列名)会得到NULL之外的数据行数
2.所有的聚合函数,如果以列名为参数,那么在计算之前就已经把NULL排除在外了
3.SUM/AVG只能对数值类型的列使用,MAX/MIN适用于所有数据类型
4.count(distinct 列名) 计算行数前会删除指定列的重复数据
5.SELECT子句,HAVING子句可以指定的元素:常数,聚合函数,GROUP BY子句中指定的列名(称为聚合建);
ORDER BY子句可以使用SELECT子句中未出现的列或聚合函数,可以使用SELECT子句中定义的列的别名
MySQL方言:下面语句是可以执行的
select c1,c2,count(*)
from <表名>
group by c1;
6.可以使用聚合函数的子句：
SELECT
HAVING
ORDER BY
7.聚合操作时,DBMS内部会进行排序,只有尽可能减少排序的行数,才能提高处理速度
优化:在排序前通过WHERE过滤掉不用的数据,并为WHERE指定条件的列创建索引
8.ORDER BY 排序键中有NULL值时,会出现在结果集的开头或结尾
ASC(默认)ascendent上升的
DESC descendent下降的
数据插入
1.insert into <表名> (列1,列2,列3...)values(值1,值2,值3...)
列1,列2,列3... 称为列清单
值1,值2,值3... 称为值清单
2.多行insert (multi row)
insert into <表名> (列1,列2,列3...)values(值清单1),(值清单2),(值清单3);
3.全列insert时,可省略列清单
4.插入NULL时，显式传入NULl
5.插入默认值DEFAULT;隐式插入默认值,列清单里省略该列
6.INSERT...SELECT... 复制一张表
数据删除
1.DELETE FROM <表名> (WHERE<条件>) 指定WHERE时称为搜索型DELETE
2.TRUNCATE清空表,效率更高
数据更新
UPDATE <表名>
SET <列名1>=<表达式1>,<列名2>=<表达式2>
where <条件>
事务transaction
1.一个处理单元中执行的一系列更新处理的结合
2.ACID
原子性atomicity 要么全部执行,要么完全不执行
隔离性isolation 事务之间在没有提交之前,是无法查看到其他事务操作后的数据的
一致性consistency 不合法的SQL会回滚,要满足定义好的约束
持久性durability 执行记录保存在硬盘,也就是日志,可以恢复到某个时间点
3.默认为自动提交模式(每条SQL语句就是一个事务)
4.数据库连接时事务已经开始
视图VIEW
1.保存的只是SELECT语句,不保存数据,节省了磁盘空间
2.CERATE VIEW <视图名>(列名1,列名2)AS SELECT ...
3.通常将用来校验数据的SQL保存在视图里,直接SELECT * 查看
4.尽量使用单一视图,而不是多重视图
5.不能使用ORDER BY
6.视图的更新(略)
子查询
1.一次性视图:子查询在SELECT语句执行完后就会消失
2.子查询需要取别名
3.标量子查询 scalar subquery,标量:单一
结果是一行一列的,故可以比较运算符中
标量子查询的书写位置:能够使用常数或列名的位置
4.关联子查询 使用场景:组内进行比较时
结合条件写在子查询的WHERE子句中
SELECT xxx
FROM product p1
WHERE price >(
SELECT avg(price)
FROm product p2
WHERE p1.type=p2.type
GROUP BY type
);
SELECT xxx,
(SELECT avg(price)
FROM product P2
WHERE p1.type=p2.type
GROUP BY type
) AS avg_type
FROM product p1;
函数(函数中的函就是盒子:输入值经过盒子后得到输出结果)
根据用途分类:
算术函数
ABS(列名或常数):绝对值absolute value,不考虑数值的符号,表示一个数到远点的距离
MOD(被除数,除数):取余modulo,这里被除数和除数可以是列名或常数
ROUND(列名或常数,保留小数的位数)四舍五入,对指定保留位数的后一位做四舍五入
SELECT ROUND(27.34,1); -- 27.3
SELECT ROUND(27.345,2); -- 27.5
字符串函数
拼接字符串 c1 || c2 ||c3
MySQL CONCAT(c1,c2,c3)
SqlServer c1+c2+c3
长度 LENGTH(c1) 计算字符串c1包含多少个字符,汉字占两个字符,JAVA中一个char类型占2个字节,String类型里一个字符等于1个字节,汉字占2个字节
SqlServer: LEN()
LOWER()
UPPER()
替换:REPLACE(c1,str1,str2) 在c1中将str1替换成str2
截取:
MySQL: SUBSTRING(c1 FROM i FOR j) 在c1中从offset为i的位置开始截取j个字符
SqlServer: SUBSTRING(c1,i,j)
Oracle: SUBSTR(c1,i,j)
日期函数
CURRENT_DATE 年月日 (无参数,不需要带括号)
ORACLE: SELECT CURRENT_DATE FROM dual
SqlServer: CAST(CURRENT_TIMESTAMP AS DATE)
CURRENT_TIME 时分秒
SqlServer: CAST(CURRENT_TIMESTAMP AS TIME)
CURRENT_TIMESTAMP 日期+时间
截取日期EXTRACT(日期元素 FROM 日期)
EXTRACT(YEAR/MONTH/DAY FROM CURRENT_TIMESTAMP)
SqlServer: DATEPART(YEAR,CURRENT_TIMESTAMP)
转换函数
CAST 类型转换
CAST('2021-10-26' AS DATE)
将NULL值转换为其他值COALESCE() 返回第一个非NULL的值
聚合函数
谓语predicate
1.返回值为真值得函数
2.比较运算符,正式的名字就是比较谓语
3.LiKE 是否包含
LIKE '%dart%'
LIKE '_dart_'
4.范围查询 BETWEEN...AND...
5.IS(NOT)NULL
6.(NOT)IN 替换OR
IN的结果集中如有NULL,则整个SELECT子句返回NULL
IN通常跟子查询
7.(NOT)EXISTS 左侧无参数,只关心是否有记录存在
SELECT xxx
FROM product p1
WHERE EXISTS (
SELECT *
FROM order o1
WHERE o1.product_id=p1.id
AND o1.payment='2021-10-26'
)
CASE表达式(多行转一行)
1.条件分支
2.Oracle: DECODE;MySQL:IF
3.ELSE子句可省略不写,默认是ELSE NULL
4.两种写法:区别:简单CASE表达式只能对一列做判断
搜索型CASE表达式:
CASE WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
ELSE <表达式>
END
简单CASE表达式
CASE <表达式>
WHEN <表达式> THEN <表达式>
WHEN <表达式> THEN <表达式>
ELSE <表达式>
END
5.SUM(CASE xx WHEN xx THEN xx ELSE 0 END)
集合运算:表的加减法
1.UNION(表的加法),
INTERSECT(交集),
EXCEPT(表的减法):
Oracle:MINUS
MySQL不支持EXCEPT
2.两表的列数必须相同
3.包含重复行时带ALL选项
4.ORDER BY子句只能在最后一次使用
5.UNION ALL不需要去重,所以不会对结果集排序,性能好于UNION
案例:选取掌握所有3个领域的技术的员工
SELECT DISTINCT emp
FROM Emp t1
WHERE NOT EXISTS(
SELECT skill
FROM skills
EXCEPT
SELECT skill
FROM emp t2
WHERE t1.emp=t2.emp
)
联结
内联结 INNER JOIN
外联结 OUTER JOIN
交叉联结CROSS JOIN笛卡尔积(表的乘法)
过时语法的内联结
SELECT t1.product_name,t1.price,t2.amount
FROM product t1,order t2
WHERE t1.id=t2.product_id
AND t2.order_id='1001';
这样的语法不推荐使用的原因:
1.无法马上判断出是内,外联结
2.无法分辨出WHERE子句里哪部分是联结条件,哪部分是过滤条件
3.后续可能废弃这样的语法
窗口函数 OVER( PARTITION By <列清单> ORDER BY <列清单>)
1.能够作为窗口函数的聚合函数(SUM,AVG,COUNT,MAX,MIN)
2.RANK,ROW_NUMBER,DENSE_RANK等专用窗口函数(没有参数)
3.窗口函数不具备GROUP BY子句的汇总功能,因此不能减少记录行数
4.有相同位次时,排序不同 RANK: 1 1 3;DENSE_RANK: 1 1 2;ROW_NUMBER: 1 2 3
5. 移动平均 moving average(都是相对当前行的位置)
ORDER BY 列1 ROWS n PRECEDING: 前n行,带上当前行也就是最近的(n+1)行
ORDER BY 列1 ROWS n FOLLOWING: 后n行,带上当前行也就是此后的(n+1)行
ORDER BY 列1 ROWS BETWEEN m PRECEDING AND n FOLLOWING: 前m行到后n行
6. 如果想让结果集全局有序,则在结尾处使用ORDER BY子句
SELECT id,RANK(ORDER BY dt) AS RANKING
FROM t1
ORDER BY dt;
其他运算符
1.合计和小计:ROLLUP 卷起
一次计算出不同聚合键组合的结果
GROUP BY ROLLUP(c1,c2)
MySQL:GROUP BY t WITH ROLLUP
其实等同于
GROUP BY () 默认使用NULL作为聚合键,聚合的结果其实相当于未分组的总值.超级分组 super group row
GROUP BY c1
GROUP BY (c1,c2)
2.GROUPING(聚合键) 超级分组产生的NULL则返回1,其他为0
3.CUBE: 2^n
ROLLUP: n+1
4.GROUPING SETS 指定聚合键组合
一条SQL语句可以操作多行数据.但是像JAVA这样的程序语言来说,数据访问是逐行进行的,要想处理多行数据,就需要循环遍历
