SQL

Posted on | | 阅读数 次
参考：http://www.runoob.com/sql/sql-syntax.html

数据仓库

四个特点

  • 面向主题  
    操作型数据库的数据组织面向事务处理任务，各个业务系统之间各自分离，而数据仓库中的数据是按照一定的主题域进行组织。主题是一个抽象的概念，是指用户使用数据仓库进行决策时所关心的重点方面，一个主题通常与多个操作型信息系统相关。
  • 集成的
    面向事务处理的操作型数据库通常与某些特定的应用相关，数据库之间相互独立，并且往往是异构的。而数据仓库中的数据是在对原有分散的数据库数据抽取、清理的基础上经过系统加工、汇总和整理得到的，必须消除源数据中的不一致性，以保证数据仓库内的信息是关于整个企业的一致的全局信息。
  • 相对稳定的
    操作型数据库中的数据通常实时更新，数据根据需要及时发生变化。数据仓库的数据主要供企业决策分析之用，所涉及的数据操作主要是数据查询，一旦某个数据进入数据仓库以后，一般情况下将被长期保留，也就是数据仓库中一般有大量的查询操作，但修改和删除操作很少，通常只需要定期的加载、刷新。
  • 反映历史变化  
    操作型数据库主要关心当前某一个时间段内的数据，而数据仓库中的数据通常包含历史信息，系统记录了企业从过去某一时点(如开始应用数据仓库的时点)到目前的各个阶段的信息，通过这些信息，可以对企业的发展历程和未来趋势做出定量分析和预测。

常用模型

  • 维度模型
  • 星型模型
  • 雪花模型
  • 星座模型
  • 关系模型
  • 范式建模

数据库基础

数据库操作语言(Data Manipulation Language, DML)

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

数据库定义语言(Data Definition Language, DDL)

DDL比DML要多，主要的命令有CREATE、ALTER、DROP、TRUNCATE等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

数据库控制语言(Data Control Language, DCL)

用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL语句。

查询筛选

题目：  
筛选出人口数量大于25000000的国家，并显示他们的所有信息。
表World:

name continent area population gdp
Afghanistan Asia 652230 25500100 20343000
Albania Europe 28748 2831741 12960000
Algeria Africa 2381741 37100000 188681000
Andorra Europe 468 78115 3712000
Angola Africa 1246700 20609294 100990000

通过SQL查询语句

1
2
3
SELECT name, population, area
FROM World
WHERE population > 25000000

得到查询结果：

name population area
Afghanistan 25500100 652230
Algeria 37100000 2381741

处理重复数据

找出重复项

题目：  
选出重复邮件。  
表Person:

Id Email
1 a@b.com
2 c@d.com
3 a@b.com

SQL语句：

1
2
3
4
select Email
from Person
group by Email
having count(*) > 1

得到：

Email
a@b.com

删除重复项

题目：  
删除重复的电子邮箱。  
表Person:（Id 是这个表的主键）

Id Email
1 john@example.com
2 bob@example.com
3 john@example.com

SQL语句：

1
2
3
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

得到：

Id Email
1 john@example.com
2 bob@example.com

排序

题目：
筛选出id为奇数且描述为不无聊的，按照评分由高到低。

表 cinema:

id movie description rating
1 War great 3D 8.9
2 Science fiction 8.5
3 irish boring 6.2
4 Ice song Fantacy 8.6
5 House card Interesting 9.1

SQL语句：

1
2
3
4
SELECT *
FROM cinema
WHERE (id % 2 = 1) AND (description <> 'boring')#<>不等于，筛选条件有歧义一定要加括号
ORDER BY rating DESC

结果：

id movie description rating
5 House card Interesting 9.1
1 War great 3D 8.9

更新表

题目：
将sex互换。

表salary:

id name sex salary
1 A m 2500
2 B f 1500
3 C m 5500
4 D f 500

SQL语句：

1
2
UPDATE salary 
SET sex= CHAR(ASCII('f')+ASCII('m')-ASCII(sex));
id name sex salary
1 A f 2500
2 B m 1500
3 C f 5500
4 D m 500

删除

DELETE、DROP、TRUNCATE都可以删除表内的数据。
不同的是：

  • TRUNCATE和DELETE只删除数据不删除表的结构
    DROP语句将删除表的结构，被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。
  • DELETE是DML语句，DROP和TRUNCATE是DDL语句
  • DELETE语句是数据库操作语言(DML),这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
  • DROP和TRUNCATE是数据库定义语言(DDL),操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。
  • DELETE语句不会自动提交，DROP和TRUNCATE执行后会自动提交，所以要小心使用DROP和TRUNCATE语句。
  • 速度：DROP> TRUNCATE > DELETE

DELETE

DELETE语句是DML语句，不会自动提交，万一操作失误，还可以通过回滚恢复数据。  
不带WHERE子句的DELETE语句与TRUNCATE的功能相同，两者都是删除表中的全部行，但是TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

DROP

drop 语句将表所占用的空间全部释放。
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

TRUNCATE

TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETE。如果要删除表定义及其数据，请使用DROP TABLE 语句。
TRUNCATE TABLE删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用DELETE。如果要删除表定义及其数据，请使用DROP TABLE 语句。

连接

  • 等值连接
    在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
  • 不等值连接
    在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
  • 自然连接
    在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

示例：  
book表：

id bookname sutid
1 python编程 1
2 sql编程 2
3 winform 3
4 web教程 4
6 前端教程 10

stu表：

sutid stuname
1 张三
2 李四
3 王五
4 任六
5 赵七

内连接

内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。

1
2
3
select * from book as a,stu as b where a.sutid = b.stuid

select * from book as a inner join stu as b on a.sutid = b.stuid
id bookname sutid sutid stuname
1 python编程 1 1 张三
2 sql编程 2 2 李四
3 winform 3 3 王五
4 web教程 4 4 任六

外连接

左连接

以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为NULL

1
select * from book as a left join stu as b on a.sutid = b.stuid
id bookname sutid sutid stuname
1 python编程 1 1 张三
2 sql编程 2 2 李四
3 winform 3 3 王五
4 web教程 4 4 任六
6 前端教程 10 NULL NULL

右连接

以右表为基准，将a.stuid = b.stuid的数据进行连接，然以将右表没有的对应项显示，左表的列为NULL

1
select * from book as a right join stu as b on a.sutid = b.stuid
id bookname sutid sutid stuname
1 python编程 1 1 张三
2 sql编程 2 2 李四
3 winform 3 3 王五
4 web教程 4 4 任六
NULL NULL NULL 5 赵七

全连接

完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

1
select * from book as a full outer join stu as b on a.sutid = b.stuid
id bookname sutid sutid stuname
1 python编程 1 1 张三
2 sql编程 2 2 李四
3 winform 3 3 王五
4 web教程 4 4 任六
6 前端教程 10 NULL NULL
NULL NULL NULL 5 赵七