数据库

作者: 廖少少 | 来源:发表于2016-09-17 11:06 被阅读149次

数据库

四个基本概念

数据(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)。

基本表的定义、修改和删除

SQL（Structured Query Language）是1974年由IBM的Ray Boyce和Don Chamberlin依据E.F.Codd关系数据库的12条准则的数学定义提出来的。SQL具有简单的关键字语法和强大的功能包括数据查询、数据操纵、数据定义和数据控制，是一种综合的、通用的、高度非过程化的关系数据库语言，是关系数据库预言的标准。以下是SQL中最常用查询语法。

定义基本表：

  CREATE TABLE <表名>
  (
  <列名> <数据类型> [ <列级完整性约束条件> ]
  [，<列名> <数据类型> [ <列级完整性约束条件>] ] …
  [，<表级完整性约束条件> ] 
  );

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级。

- 数据类型
    - SQL中域的概念用数据类型来实现
    - 定义表的属性时 需要指明其数据类型及长
    度
    - 选用哪种数据类型
        - 取值范围
        - 要做哪些运算
    - 不同的数据库产品支持的数据类型不完全相同,下面是Microsoft SQL Server常用数据类型
        ![](https://img.haomeiwen.com/i2106579/73e4b66f4534ed28?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

- 在列级完整性约束定义处可以定义的约束
    - 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
        );

- 约束

![UNIQUE KEY 约束](https://img.haomeiwen.com/i2106579/2f69820c930160ee.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
![PRIMARY KEY 约束](https://img.haomeiwen.com/i2106579/b381d411742b434b.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
![FOREIGN KEY 约束](https://img.haomeiwen.com/i2106579/c1668027b2137689.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

修改基本表
- 标准语法：
  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转义字

连接查询

待续...

数据库

数据库

四个基本概念

基本表的定义、修改和删除

单表查询

连接查询

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

华南理工大学无线电爱好者协会软件小组