数据库(Database):将大量数据保存起来,通过计算机加工而成的可以进行高效访问的数据集合。
数据库种类
- 层次数据库(Hierarchical Database,HDB)
- 关系数据库(Relational Database,RDB) 由行和列组成的二维表来管理数据,最广泛,通常使用客户端/服务器的系统结构。使用SQL(Structured Query Language, 结构化查询语言)对数据进行操作。通过从客户端向服务器发送SQL语句来实现数据库的读写操作。
RDBS:Oracle, SQL Server, DB2, PostgreSQL, MySQL - 面向对象数据库(Object Oriented Database,OODB)
- XML数据库(XML Database,XMLDB)
- 键值存储系统(Key-Value Store,KVS)
SQL语法基本准则
- 关系数据库通过表来管理数据,数据库中可以同时存储多个表。
- 根据SQL语句的内容返回的数据必须是二维表的形式。
- 关系数据库以行为单位进行数据读写。
- 一个单元格中只能输入一个数据。
- SQL语句以分号结尾。
- SQL语句关键字不区分大小写,插入到表中的数据区分大小写。
- 字符串和日期常数需要使用单引号括起来,数字常数直接书写。
- 单词间用半角空格或换行符分隔。
创建数据库
CREATE DATABASE <数据库名称>;
/*
数据库名称、表名和列名可以使用以下三种字符:半角英文字母、半角数字、下划线。
名称必须以半角英文字母开头,且名称不能重复。
*/
注:代码中 “ <…> ” 可直接用 “ … ” 替代,无需添加括号 “ < ” 和 “ > ”。
创建表
CREATE TABLE <表名>
(<列名> <数据类型> <该列所需约束>,
<列名> <数据类型> <该列所需约束>,
<列名> <数据类型> <该列所需约束>,
…,
<该表的约束1>, <该表的约束2>, …);
SQL语句使用换行符或者半角空格来分隔单词,在任何位置进行分隔都可以,但插入空行会出错。
数据类型:
INTEGER(size)
:整数型,size指定最大位数
CHAR(size)
:定长字符串,达不到长度用半角空格补齐,最多255字符
VARCHAR(size)
:可变长字符串,size为最大长度,最多255字符
DATE
:日期,格式:YYYY-MM-DD
约束:
NOT NULL
:必须输入数据
PRIMARY KEY (<列名>)
:主键约束,通过主键唯一确定一行
删除表
DROP TABLE <表名>;
添加列
ALTER TABLE <表名> ADD COLUMN <列名> <数据类型> <该列所需约束>;
删除列
ALTER TABLE <表名> DROP COLUMN <列名>;
基本SELECT语句
SELECT <列名>, … ,<列名>
FROM <表名>;
查询全部列
-- *代表全部列
SELECT *
FROM <表名>;
注释一行用 “–”,多行用 “/*” 和 “*/”。
为列设定别名
SELECT <列名> AS <别名>, … ,<列名> AS <别名>
FROM <表名>;
-- 别名使用中文时需要用双引号括起来
从查询结果中删除重复行
SELECT DISTINCT <列名>
FROM <表名>;
/*
对含NULL的数据会被保留
DISTINCT只能用在第一列前,在多列前使用时,每一列都相同的数据会被合并
*/
WHERE子句:指定查询数据的条件
SELECT <列名>, … ,<列名>
FROM <表名>
WHERE <条件表达式>;
SELECT子句中可以使用常数或表达式(WHERE子句的条件表达式也可使用计算表达式),对运算符两边的列或值进行运算。所有包含NULL的计算结果均为NULL。
比较运算符:“<>”表示不相等,“>=”,“<=”。可对日期进行比较。
字符串类型的数据按照字典顺序进行排序,比较运算时亦是按字典顺序。
希望选取NULL记录时,可使用IS NULL
运算符;希望选取不是NULL的记录时,需要在条件表达式中使用IS NOT NULL
运算符。可使用NOT
运算符用来否定条件,如放置在上述代码的WHERE后。
用AND或OR运算符组合多个查询条件
SELECT <列名>, … ,<列名>
FROM <表名>
WHERE <条件表达式1>
AND/OR <条件表达式2>;
AND运算符优先级高于OR运算符。想优先执行OR运算可以使用括号。
聚合查询
使用聚合函数可对表中的列进行计算合计值或者平均值等汇总操作。
COUNT(<列名>)
: 计算表中的记录数(行数),结果根据参数的不同而不同。COUNT(*)
会得到包含NULL的数据行数,COUNT(<列名>)
会得到NULL之外的数据行数。
SUM(<列名>)
: 计算表中数值列中数据的合计值
AVG(<列名>)
: 计算表中数值列中数据的平均值,排除NULL再进行计算平均值。
MAX(<列名>)
: 求出表中任意列中数据的最大值
MIN(<列名>)
: 求出表中任意列中数据的最小值
聚合函数(<列名>)会将该列为NULL的值排除在外。但COUNT(*)除外,不会排除NULL。
MAX和MIN几乎适用于所有数据类型的列,如日期、字符串。SUM和AVG只适用于数值类型的列。
在聚合函数中使用DISTINCT可以删除重复数据,如COUNT(DISTINCT <列名>)。
GROUP BY子句:分割后汇总
SELECT <列名1>, <列名2>, …
FROM <表名>
WHERE <条件表达式1>
GROUP BY <列名1>, <列名2>, … ;
/*
GROUP BY后的列称为聚合键或分组列,将结果按照聚合键进行分类。
聚合键中包含NULL时,在结果中会以空行形式表现。
*/
SQL子句书写顺序(不能变):SELECT
->FROM
->WHERE
->GROUP BY
->HAVING
->ORDER BY
。
SELECT语句执行顺序:FROM
->WHERE
->GROUP BY
->HAVING
->SELECT
->ORDER BY
。
使用WHERE 子句进行汇总处理时,会先根据WHERE 子句指定的条件进行过滤,然后再进行汇总处理。
- 使用GROUP BY子句时,SELECT子句中不能出现聚合键之外的列名。
- GROUP BY子句中不能使用SELECT子句中AS定义的别名。
- GROUP BY子句显示的结果是无序的。
- 只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数。
HAVING子句:为汇总后的组指定条件
GROUP BY子句可以得到将表分组后的结果,HAVING子句可通过指定条件来选取特定组。(WHERE子句只能指定记录(行)的条件,而不能用来指定组的条件。)
SELECT <列名1>, <列名2>, …
FROM <表名>
GROUP BY <列名1>, <列名2>, …
HAVING <分组结果对应的条件>;
HAVING子句可以使用:常数、聚合函数、GROUP BY子句中指定的列名。
聚合键所对应的条件既可以写在HAVING中也可以写在WHERE中,但最好WHERE指定行所对应的条件,HAVING指定组所对应的条件。
ORDER BY子句:对查询结果进行排序
SELECT <列名1>, <列名2>, …
FROM <表名>
GROUP BY <列名1>, <列名2>, …
HAVING <分组结果对应的条件>
ORDER BY <排序基准列1>, <排序基准列2>, … ASC/DESC; /*
默认ASC升序;DESC降序
ORDER BY子句通常位于语句末尾,其中书写的列名称为排序键
*/
排序键中包含NULL时,NULL会在开头或末尾进行汇总。
SELECT子句的执行顺序在GROUP BY之后,ORDER BY之前,因此ORDER BY可以使用别名。
ORDER BY子句可以使用SELECT子句中未使用的列和聚合函数。
ORDER BY子句中不要使用列编号。
INSERT语句:向表中插入单个数据(行)
INSERT INTO <表名> (列1, 列2, 列3, …) VALUES (值1, 值2, 值3, …);
-- 将列名和值用逗号隔开,分别括在()内,这种形式称为清单。列清单和值清单的列数必须保持一致。
INSERT语句:多行插入(除Oracle外)
INSERT INTO <表名> VALUES
(值1, 值2, 值3, …),
(值1, 值2, 值3, …), …;
/*
对表进行全列INSERT时可省略列清单
想给某一列赋NULL值时,可直接赋NULL。不能向设置了NOT NULL约束的列插入NULL。
*/
可以通过在创建表的CREATE TABLE语句中设置DEFAULT约束来设定默认值。通过DEFAULT<默认值>
来约束默认值。
/*插入默认值*/
-- 显式方法:VALUES后的值清单的值赋DEFAULT
INSERT INTO <表名> (列1, 列2, 列3, …) VALUES (值1, DEFAULT, 值3, …);
-- 隐式方法:在列清单和VALUES中省略设定了默认值的列
INSERT INTO <表名> (列1, 列3, …) VALUES (值1,值3, …);
INSERT语句:从表2中复制数据到表1
INSERT INTO <表名1> (列1, 列2, 列3, …)
SELECT <表达式1>, <表达式2>, …
FROM <表名2>
GROUP BY <列名>;
-- INSERT语句中可使用各种SELECT语句
DELETE语句
-- 保留数据表,仅删除全部数据行的DELETE语句,而不是删除列
DELETE FROM <表名>;
-- 删除部分行,通过WHERE子句指定对象条件
DELETE FROM <表名>
WHERE <条件>;
-- DELETE语句中不能使用GROUP BY、HAVING和ORDER BY三类子句
TRUNCATE语句:只能删除表中全部数据
TRUNCATE <表名>;
UPDATE语句:改变表中数据
UPDATE <表名>
SET <列名> = <表达式> -- 改变的是数据,不是列名
WHERE <条件>;
-- 给表达式赋NULL可将列更新为NULL,即NULL清空
-- 多列更新
UPDATE <表名>
SET <列名1> = <表达式1>, <列名2> = <表达式2>,…
WHERE <条件>;
UPDATE <表名>
SET (<列名1>, <列名2>,…) = (<表达式1>, <表达式2>,…)
WHERE <条件>;
事务
事务是需要在同一个处理单元中执行的一系列更新处理的集合。通过使用事务,可以对数据库中的数据更新处理的提交和取消进行管理。
事务开始语句;
DML语句1;
DML语句2;
DML语句3;
…
事务结束语句;
其中,事务开始语句由各个DBMS自己定义:
SQL Server、PostgreSQL: BEGIN TRANSACTION;
MySQL: START TRANSACTION;
Oracle、DB2: 无
事务结束语句有两种:
COMMIT;
: 提交处理
ROLLBACK;
: 取消处理,回到事务开始起点
事务遵循的特性:ACID。
- 原子性(Atomicity):在事务结束时,其中所包含的更新处理要么全部执行,要么完全不执行。
- 一致性(Consistency):事务中包含的处理要满足数据库提前设置的约束。
- 隔离性(Isolation):保证不同事务之间互不干扰的特性。该特性保证了事务之间不会互相嵌套。此外,在某个事务中进行的更改,在该事务结束之前,对其他事务而言是不可见的。
- 持久性(Durability):在事务(不论是提交还是回滚)结束后,DBMS能够保证该时间点的数据状态会被保存的特性。
参考资料
《SQL基础教程》(第2版)[日] MICK著;孙淼,罗勇译
更多推荐
SQL常用基础语句总结
发布评论