数据库
四个基本概念
-
数据(Data)
- 数据是数据库中存储的基本对象
- 数据(Data):描述事物的符号记录描述事物的符号可以是数字、文字、图形、图像、声音、语言等
-
数据库(Database)
- 数据库(Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据的集合。这些数据用于某企业的应用系统中（制造公司、银行、医院、大学、政府部门等）
-
数据库管理系统(DBMS)
- DBMS（ DataBase Management System ）
- 位于用户与操作系统之间系统软件
一组用以管理、维护、访问数据库的程序 - 基本目标
提供一个可以方便地、有效地存取数据库信息的环境 - 常见数据库管理系统：
- Oracle
- Access
- Sql Server
- DB2
- Sybase
- Infomix
- DBMS的主要功能：
- 数据定义功能
- 提供数据定义语言(DDL)
- 定义数据库中的数据对象
- 数据组织、存储和管理
- 分类组织、存储和管理各种数据
- 确定组织数据的文件结构和存取方式
- 实现数据之间的联系
- 提供多种存取方法提高存取效率
- 数据操纵功能
- 提供数据操纵语言(DML)
- 实现对数据库的基本操作 (查询、插入、删除和修改)
- 数据库的事务管理和运行管理
- 数据库在建立、运行和维护时由DBMS统一管理和控制
- 保证数据的安全性、完整性、多用户对数据的并发使用发生故障后的系统恢复
- 数据库的建立和维护功能(实用程序)
- 数据库初始数据装载转换
- 数据库转储
- 介质故障恢复
- 数据库的重组织
- 性能监视分析等
- 其它功能
- DBMS与网络中其它软件系统的通信
- 两个DBMS系统的数据转换
- 异构数据库之间的互访和互操作
- 数据定义功能
-
数据库系统(DBS)
- 什么是数据库系统（Database System，简称DBS）
在计算机系统中引入数据库后的系统构成 - 数据库系统的构成
- 数据库
- 数据库管理系统（及其开发工具）
- 应用系统(用户)
- 数据库管理员
- 数据库是为多用户共享的，因此需要有人进行规划，设计，协调，维护和管理，负责这些工作的人员称为DBA(Database Administrator)。
- 什么是数据库系统（Database System，简称DBS）
基本表的定义、修改和删除
SQL（Structured Query Language）是1974年由IBM的Ray Boyce和Don Chamberlin依据E.F.Codd关系数据库的12条准则的数学定义提出来的。SQL具有简单的关键字语法和强大的功能包括数据查询、数据操纵、数据定义和数据控制，是一种综合的、通用的、高度非过程化的关系数据库语言，是关系数据库预言的标准。以下是SQL中最常用查询语法。
-
定义基本表：
CREATE TABLE <表名> ( <列名> <数据类型> [ <列级完整性约束条件> ] [，<列名> <数据类型> [ <列级完整性约束条件>] ] … [，<表级完整性约束条件> ] );
如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。
- 数据类型
- SQL中域的概念用数据类型来实现
- 定义表的属性时 需要指明其数据类型及长
度
- 选用哪种数据类型
- 取值范围
- 要做哪些运算
- 不同的数据库产品支持的数据类型不完全相同,下面是Microsoft SQL Server常用数据类型
- 在列级完整性约束定义处可以定义的约束
- NOT NULL：限制列取值非空。
- DEFAULT：给定列的默认值。
- UNIQUE：限制列取值不重。
- CHECK：限制列的取值范围。
- PRIMARY KEY：指定本列为主码。
- FOREIGN KEY：定义本列为引用其他表的外码。
- 使用形式为：
[FOREIGN KEY(<外码列名>)]
REFERENCES <外表名>(<外表列名>)
>几点说明：
> - NOT NULL和DEFAULT只能是列级完整性约束；
> - 其他约束均可在表级完整性约束处定义。
> - 注意以下几点：
> 1. 如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；
> 2. 如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARY KEY(列1{[，列2 ] …})；
> 3. 如果在表级完整性约束处定义外码，则“FOREIGN KEY (<外码列名>)”部分不能省
- 建表示例：
CREATE TABLE Student
(
Sno CHAR(9) PRIMARY KEY, --设为主键（主码），列级完整性约束条件
Sname CHAR(20) UNIQUE, --Sname取唯一值
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR,
Cpno CHAR(4), --先修课
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno), --Cpno是外键，Course表的Cno是主键（Course是被参照表，Cno是被参照列）
);
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno, Cno), --主键由两个属性构成，必须作为表级完整性进行定义
FOREIGN KEY (Sno) REFERENCES Student(Sno), --表级完整性约束条件，Sno是外码，被参照表是Student
FOREIGN KEY (Cno) REFERENCES Course(Cno) --表级完整性约束条件， Cno是外码，被参照表是Course
);
- 约束
-
修改基本表
- 标准语法：
ALTER TABLE <表名>
[ ADD[COLUMN] <新列名> <数据类型> [ 完整性约束 ] ]
[ ADD <表级完整性约束>]
[ DROP [ COLUMN ] <列名> [CASCADE| RESTRICT] ]
[ DROP CONSTRAINT<完整性约束名>[ RESTRICT | CASCADE ] ]
[ALTER COLUMN <列名><数据类型> ] ; - 例子：
ALTER TABLE Student ADD S_entrance DATE; --向Student表增加“入学时间”列，其数据类型为日期型。不论基本表中原来是否已有数据，新增加的列一律为空值
ALTER TABLE Student ALTER COLUMN Sage INT; --将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。
ALTER TABLE Course ADD UNIQUE(Cname); --增加课程名称必须取唯一值的约束条件。
- 标准语法：
-
删除基本表
- 标准语法：
DROP TABLE <表名>［RESTRICT|CASCADE］； - RESTRICT：删除表是有限制的。
欲删除的基本表不能被其他表的约束所引用
如果存在依赖该表的对象，则此表不能被删除 - CASCADE：删除该表没有限制。
在删除基本表的同时，相关的依赖对象一起删除 - 例子：
DROP TABLE Student; --注意外键约束
- 标准语法：
单表查询
-
关于聚集函数、GROUP BY子句、HAVING子句
- 聚集函数
- 计数
- COUNT（[DISTINCT|ALL] *）
- COUNT（[DISTINCT|ALL] <列名>）
- 计算总和
- SUM（[DISTINCT|ALL] <列名>）
- 计算平均值
- AVG（[DISTINCT|ALL] <列名>）
- 最大最小值
- MAX（[DISTINCT|ALL] <列名>）
- MIN（[DISTINCT|ALL] <列名>）
- 上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。
- 计数
- GROUP BY子句
- 对查询结果分组
- GROUP BY子句将查询结果按某一列或多列值分组，值相等的为一组。
- 多列值的分组，按列的次序依次分组
- 将查询结果分组的目的是为了细化聚集函数的作用对象。
- GROUP BY子句分组：
- 未对查询结果分组，聚集函数将作用于整个查询结果
- 对查询结果分组后，聚集函数将分别作用于每个组
- 作用对象是查询的中间结果表
- 按指定的一列或多列值分组，值相等的为一组
-
例子：统计每个学生选修的课程中成绩高于70分的课程数
- 使用HAVING
- 如果分组后，还要求按一定的条件对这些组进行筛选，输出满足一定条件的组，则使用HAVING子句指定筛选条件。
- HAVING子句的条件一般应为分组的字段或进行某聚集函数的运算，在分组统计时进行判断。而WHERE子句的条件，在分组之前进行判断。
- HAVING用于对分组自身进行限制，它有点象WHERE子句，但它用于组而不是对单个记录。
- 例子：查询修课门数等于或大于4门的学生的平均成绩和选课门数。
SELECT Sno, AVG(Grade) 平均成绩,
COUNT() 修课门数
FROM SC
GROUP BY Sno
HAVING COUNT() >= 4 - 例子：查询选修计算机系各门课程的选课人数，要求按选课人数的多少降序排序(假设计算机系的课程号以CS开头)
SELECT Cno ,COUNT(Sno)
FROM dbo.SC
WHERE Cno like 'CS%'
GROUP BY Cno
ORDER BY COUNT(Sno) DESC - 例子：
-
例子：统计选修的课程中成绩高于70分的课程数超过2门的学生学号
- 聚集函数
-
各种查询语法示例（来自幕课网）：
/--------------------------------------------------
--SQL Server 2012 & T-SQL Lesson 3 - BasicQueries
----------------------------------------------------/--Topic 1 SELECT <table fields list> FROM <table names list> WHERE <row constraints specification> GROUP BY <grouping specification> HAVING <grouping selection specification> ORDER BY <order rules specification> --Topic 2 use [AdventureWorks2012] go select Top 100 * from [Production].[Product] --查询该表的前100行，尽量避免只用*查询数据，因为可能数据过于庞大 SELECT * FROM SALES.SALESORDERDETAIL --Topic 3 select * from [Production].[Product] select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Product select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Product order by listprice desc --desc=descending order ; asc=ascending order select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Product order by listprice desc,Name --按照listprice降序排列，再按照Name升序排列 select ProductID, Name, ProductNumber, Color, Size, ListPrice from Production.Product order by 2 --即按第2列（Name列）升序排列 --Topic 4 select ProductID, Name, ProductNumber, isnull(Color,''), isnull(Size,''), ListPrice --isnull函数用来让NULL值变为replacement_value from Production.Product --Topic 5 select ProductID, Name, ProductNumber, isnull(Color,'') as Color, isnull(Size,'') as Size123, --using an alias (本来列名为Name的，但为了更友好地输出，把Name变为ProductName输出，注意列名还是Name) ListPrice from Production.Product select ProductID, Name as ProductName, 'The list price for ' + ProductNumber + ' is $ ' + convert(varchar,ListPrice) +'.' ,--using the concatenation to join character end-to-end. 'The list price for ' + ProductNumber + ' is $ ' + convert(varchar,ListPrice) +'.' as [Description] --using brackets to let SQL server conside the strin as a column name from Production.Product --convert函数用来使某列强转为某数据类型 --Topic 6 select BusinessEntityID,rate from [HumanResources].[EmployeePayHistory] select BusinessEntityID ,rate*40*52 as AnnualSalary -- +/-/*/% ,round(rate*40*52,1) as AnnualSalary --round函数用来规定保留几位小数，在这里保留一位小数 ,round(rate*40*52,0) as AnnualSalary --这里不保留小数 from [HumanResources].[EmployeePayHistory] select BusinessEntityID ,(rate+5)*40*52 as AnnualSalary from [HumanResources].[EmployeePayHistory] --Topic 7 select * from [Sales].[SalesOrderHeader] select * from [Sales].[SalesOrderHeader] where SalesPersonID=275 select * from [Sales].[SalesOrderHeader] where SalesOrderNumber='so43670' select * from [Sales].[SalesOrderHeader] where TotalDue>5000 select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader] where SalesPersonID=275 and TotalDue>5000 --Comparison conditions: =,>,<,>=,<=,<> select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader] where SalesPersonID=275 and TotalDue>5000 and Orderdate between '2005-08-01' and '1/1/2006' --两种datetime的表示方式，between...and... 包括两个日期 select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader] where SalesPersonID=275 and TotalDue>5000 and Orderdate >= '2005-08-01' and Orderdate < '1/1/2006' --包括2005-08-01，不包括2006-1-1 select * from [Production].[Product] where name ='Mountain-100 Silver, 38' --Topic 8 --模糊查询 select * from [Production].[Product] where name like'Mountain' select * from [Production].[Product] where name like'%Mountain%' --Wildcard（通配符） % matches any zero or more characters （在通配符“%”的前后都可以有任意个字符） select * from [Production].[Product] where name like'mountain%' select * from [Production].[Product] where name like'_ountain%' -- "_" matches any single character --Topic 9 select * from [Production].[Product] where color in ('red','white','black') --查询出color为red/white/black的 select * from [Production].[Product] where size in ('60','61','62') select * from [Production].[Product] where class not in ('H') -- same as using: <> 'H' （小于大于，即不等于） --Topic 10 select * from [Production].[Product] where size is null select * from [Production].[Product] where size is not null --在工作中经常用到 --Topic 11 select * from [Production].[Product] where color ='white'or color ='black' --color为white或black的 select * from [Production].[Product] where color ='white'and color ='black' select SalesOrderID,OrderDate,SalesPersonID,TotalDue as TotalSales from [Sales].[SalesOrderHeader] where (SalesPersonID=275 or SalesPersonID=278) and TotalDue>5000 --不加括号 --Topic 12 select count(SalesPersonID) --统计[Sales].[SalesOrderHeader]表中SalesPersonID列不为空的数量，最后返回一个数字 from [Sales].[SalesOrderHeader] where SalesPersonID is not null select distinct(SalesPersonID) --查询[Sales].[SalesOrderHeader]表中SalesPersonID列不为空且互不相同的SalesPersonID列 from [Sales].[SalesOrderHeader] where SalesPersonID is not null select count(distinct(SalesPersonID)) --统计上面的行数量 from [Sales].[SalesOrderHeader] where SalesPersonID is not null --Topic 13 select Avg(TotalDue) as AverageTotalSales --aggregate functions（聚合函数） from [Sales].[SalesOrderHeader] select Avg(TotalDue) as AverageTotalSales ,Min(TotalDue) as MinimumTotalSales ,Max(TotalDue) as MaximumTotalSales ,Sum(TotalDue) as SummaryTotalSales from [Sales].[SalesOrderHeader] select SalesPersonID,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader] --Error Message: Column 'Sales.SalesOrderHeader.SalesPersonID' is invalid in the select list --because it is not contained in either an aggregate function or the GROUP BY clause. --解决办法，把SalesPersonID列放在group by里面 select SalesPersonID,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader] where SalesPersonID is not null group by SalesPersonID order by SalesPersonID select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader] where SalesPersonID is not null group by SalesPersonID,OrderDate --Remember to put all un-aggregated columns after "group by"!!! order by SalesPersonID select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader] where SalesPersonID is not null group by SalesPersonID,OrderDate having Max(TotalDue)>150000 --having是对聚合函数的限定，所以不能用一般的语法，必须对所用聚合函数（在这里是Max函数）限定） order by SalesPersonID ----The classical T-SQL query!!! select SalesPersonID,OrderDate,Max(TotalDue) as MaximumTotalSales from [Sales].[SalesOrderHeader] where SalesPersonID is not null and OrderDate >='2007/1/1' group by SalesPersonID,OrderDate having Max(TotalDue)>150000 order by OrderDate desc代码相关链接：SQL Server基础教程——幕课网
模糊查询相关链接：SQL模糊查询语句和Escape转义字
连接查询
待续...
