大家好，欢迎来到IT知识分享网。

数据库学习

sql server数据库基本概念

使用文件保存数据存在几个缺点：

1、文件的安全性问题；

2、文件不利于查询和对数据的管理；

3、文件不利于存放海量数据

4、文件在程序中控制不方便。

 

数据库的定义(1)

严格地说，数据库是“按照数据结构来组织、存储和管理数据的仓库”。在经济管理的日常工作中，常常需要把某些相关的数据放进这样的“仓库”，并根据管理的需要进行相应的处理。例如，企业或事业单位的人事部门常常要把单位职工的基本情况(职工号、姓名、年龄、性别、籍贯、工资、简历等)存放在表中，这张表就可以看成一个数据库。有了这个“数据仓库”我们就可以根据需要随时查询某职工的基本情况，也可以查询工资在某个范围内的职工人数等等。这些工作如果都能在计算机上自动进行，那我们的人事管理就可以达到极高的水平。此外，在财务管理、仓库管理、生产管理中也需要建立众多的这种“数据库”，使其可以利用计算机实现财务、仓库、生产的自动化管理。

 

数据库的定义(2)

J.Martin组数据库下了一个比较完整的定义：数据库是存储在一起的相关数据的集合，这些数据是结构化的，无有害的或不必要的冗余，并为多种应用服务；数据的存储独立于使用它的程序；对数据库插入新数据，修改和检索原有的数据均能按一种公用的和可控制的方式进行。当某个系统中存在结构上完全分开的若干个数据库时，则该系统包含一个“数据库集合”。

 

数据库的基本结构

数据库的基本结构分三个层次，反映了观察数据库的三种不同角度。

1、物理数据层

    它是数据库的最内层，是物理存贮设备上实际存储的数据的集合。这些数据的原始数据，是用户加工的对象，由内部模式描述的指令操作处理的位串、字符和字组成。

2、概念数据层

    它是数据库的中间一层，是数据库的整体逻辑表示。指出了每个数据的逻辑定义及数据间的逻辑联系，是存贮记录的集合。它所涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。

3、逻辑数据层

    它是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合，即逻辑记录的集合。

数据库不同层次之间的联系是通过映射进行转换的。

 

数据库的基本特点

1、实现数据共享

    数据共享包含所有用户可同时存取数据库中的数据，也包括用户可以用各种方式通过接口使用数据库，并提供数据共享。

2、减少数据的冗余度

    同文件系统比，数据库实现了数据共享，从而避免了用户各自建立应用文件。减少了大量重复数据，减少了数据冗余，维护了数据的一致性

3、数据实现集中控制

    文件管理方式中，数据处于一种分散的状态，不同的用户或同一用户在不同处理中其文件之间毫无关系。利用数据库可对数据进行集中控制和管理，并通过数据模型表示各种数据的组织以及数据间的联系。

4、数据一致性和可维护性，以确保数据的安全性和可靠性。

5、故障恢复

 

目前主流数据库

微软：              Sql Server和Access

瑞典MySQL：         AB公司MySql

IBM公司：           DB2

美国Sybase公司：   Sybase

IBM公司：           Informix

美国Oracle公司：   Oracle

 

数据库选择：

1、成本；2、功能；3、并发性要求；4、安全性；

 

为什么学习SqlServer？

简单易学

一般来讲，学习数据库很多人都是从微软的sql server数据库开始的，我们知道微软的产品以简单易用见长，事实也是如此，从sqlserver开始学习数据库是正确的选择，后面我们还要带领大学学习oracle数据库，如果一上手就学习oracle这种大型数据库，会很吃力。

 

sql server介绍(1)

SQL是英文Structured Query Language的缩写，意思为结构化查询语言。SQL语言的主要功能就是同各种数据库建立联系，进行沟通。按照ANSI(美国国家标准协会)的规定，SQL被作为关系型数据库管理系统的标准语言。SQL语句可以用来执行各种各样的操作，例如更新数据库中的数据，从数据库中提取数据等。目前，绝大多数流行的关系型数据库管理系统，如Oracle、Sybase、Microsoft SQL Server、Access等都采用了SQL语言标准。

 

sql server介绍(2)

SQL Server是一个关系数据库管理系统。它最初是由Microsoft Sybase和Ashton-Tate三家公司共同开发的，于1988年推出了第一个OS/2版本。在Windows NT推出后，Microsoft与Sybase在SQLServer的开发上就分道扬镳了，Microsoft将SQLServer移植到Windows NT系统上，专注于开发推广SQLServer的Windows NT版本。Sybase则较专注于SQL Server在UNIX操作系统上的应用。

SQL Server2000是Microsoft公司推出的SQLServer数据库管理系统，该版本继承了SQL

Server7.0版本的优点，同时又比它增加了许多更先进的功能。

 

sql server开发工具

sql server2000为我们提供了两种开发工具

1、企业管理器

企业管理器是微软提供的图形界面方式操作sql server2000的工具

2、查询分析器

查询分析器是微软提供的用命令行(sql语句)操作sqlserver2000的工具

 

企业管理器的使用(1)

数据库用户和管理员的概念

1、管理员sa的介绍

2、如何修改sa的密码

 

企业管理器的使用(2)

为了让大家能迅速掌握企业管理器的使用，我们使用管理器。

以一个梁山好汉管理为例

1、建库

2、建表

英雄表 hero

[这里涉及到对表的几个重要概念：a、字段；b、字段类型(简单介绍)；c、表行；d、表列；e、记录]

3、对表进行增加、删除、修改、查询的操作

这里可以针对真实班级的学生来做，并增加一定的互动，比如用点名机来抽学生操作，或是用点名机看看那个学生被删除或是添加、修改…这样可能学生更有兴趣。

 

查询分析器的使用(1)

    企业管理器给用户提供了一个很方便的图形界面管理工具，大家用起来感觉直观方便，可是它也有缺点，当一个表的记录非常大的时候，对表的各种操作，都显得不方便了，比如：

1、要求从1000行记录中查询是否存在名为“孙小明”的人

2、要求把1000行记录中工资低于100的人，工资增加10%

3、要求把年龄大于30的人从数据表中删除

这时，你会发现使用企业管理器是很不方便的

所以微软为我们提供另外一个操作数据库的工具，查询分析器。查询分析器可以非常方便的完成上述任务。

 

查询分析器的使用(2)–sql语句

使用查询分析器，我们就必需要了解sql语句。

 

什么是sql语句呢？

人与人交流是使用语言进行交流。我们与sql数据库交流就要使用对应的计算机语言进行交流，所以访问sql数据库要使用sql语句进行指令的发送。

查询分析器的使用(3)–sql语句

SQL全称是“结构化查询语言(Structured Query Language)”

SQL(Structured QueryLanguage)是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。构化查询语言(Structured Query Language)最早是IBM的圣约瑟研究实验室为其关系数据库管理系统SYSTEM R开发的一种查询语言，它的前身是SQUARE语言。SQL语言结构简洁，功能强大，简单易学，所以自从IBM公司1981年推出以来，SQL语言得到了广泛的应用。如今无论是像Oracle、Sybase、Informix、SQL Server这些大型的数据库管理系统，还是像Visual Foxpro、PowerBuilder这些PC上常用的数据库开发系统，都支持SQL语言作为查询语言。

 

查询分析器的使用(4)–sql语句

SQL语言包含4个部分：

数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。

数据操作语言(DML)，例如：INSERT、UPDATE、DELETE语句。

数据查询语言(DQL)，例如：SELECT语句。

数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等语句。

 

查询分析器的使用(5)

使用查询分析器再次完成梁山好汉管理数据库

1、建库

2、建表

英雄表 hero

[这里涉及到对表的几个重要概念：a、字段；b、字段类型(简单介绍)；c、表行；d、表列；e、记录]

1、 对表进行增加、删除、修改、查询的操作

 

查询分析器中创建数据库

—创建数据库

create database LiangshanHero2

 

—创建表

use LiangshanHero2—使用指定数据库

go—执行

create table hero—表名

(heroId  int ,—英雄排名

heroName  varchar(50),—名字

heroNickName  varchar(50),—绰号

sex char(2),—性别

sal int

)

 

—删除一张表(把表的结构和表的数据删除)

drop table hero

—使用sql语句来添加数据

insert into hero  values(1,’宋江’,’及时雨’,’男’,20000)

insert into hero   values(2,’卢俊义’,’玉麒麟’,’男’,15000)

insert into hero  values(3,’吴用’,’智多星’,’男’,30)

insert into hero  values(4,’公孙胜’,’入云龙’,’男’,80)

 

—使用sql语句查询数据,最基础的查询语句

select * from hero

 

–1、查询工资低于100的同志

select * from hero  where sal<100

 

–2、把工资低于100的人工资提高10%(update)

—语法结构：update表名 set 字段名1=？,字段名2=？…where 条件语句

update hero setsal=sal*1.1 where sal<100

 

–3、请删除工资性别为女的

delete from hero  where sex=’男’

 

表的管理—表名和列的命名规则

1、必需以字母,_开头

2、长度不能超过128个字符

3、不要使用sql server的保留字

4、只能使用如下字符A-Z，a-z，0-9，$，#，_等

 

表的管理–支持的数据类型

字符型

char 定长 最大8000字符(非unicode编码)

char(10)’小韩’前四个字符放’小韩’，后添6个空格补全

varchar变长 最大8000字符(非unicode编码)

varchar(10)’小寒’sql server分配四个字符，这样可以节省空间

ntext可变长度Unicode数据的最大长度为2的30次方-1(1,073,741,823)个字符

text可变长度非Unicode数据的最大长度为2的31次方-1(2,147,483,647)个字符

区别：

1、text是字节格式存储英文的，也可以存中文但有时候会显示成乱码

2、ntext是多字节格式存储unicode的，也就是存储各种文字用的。

在什么时候使用char型而不使用varchar,在知道字段定长固定时就使用char

 

字符型

nchar 定长 最大4000字符(unicode编码)

nchar(10)’小韩’前四个字符放’小韩’，后添6个空格补全

nvarchar 变长 最大4000字符(unicode编码)

nvarchar(10)’小寒’sql server分配四个字符，这样可以节省空间

特别说明：

1、一般带有汉字的字段用nvarchar，全英文或符号的用varchar，因为nvarchar为unicode字符集，该类型的字段无论是单个字母还是单个汉字都占两个字节，而varchar，字母占一个字节，汉字占两个，nvarchar处理汉字或其它unicode字符集的速度要比varchar字段快。

2、如果有一些特殊字符在nvarchar中没有的，如日文的某些名片，那当然只能选合适的代码页用varchar了，而且这些特有语言特有的字符转换到nvarchar会消失

 

表的管理–支持的数据类型

数字型

1、bit范围 0到1

2、int范围 负的2的31次方到正的 2的31次方-1

3、bigint范围 负的2的63次方到正的 2的63次方-1

4、float存放小数，不推荐使用

5、numeric小数

强烈建议 如果要去存放小数最好使用numeric

 

日期类型

datetime(表示日期)

timestamp(时间戳)

一般情况下 用datetime表示日期

–sqlserver为我们提供一个专门的时间函数

getdate()

create table spname—建表

(bir datetime)—字段

insert into spnamevalues(getdate())—添加时间

 

图片

image保存图片，但是用的很少，一般用路径保存图片，在软件公司往往使用图片服务器和图床技术

 

视频

binary字段可以存放，但是我们往主，将视频文件保存在文件服务器上，sql server中只保留文件路径，存取效率高。

 

表的管理–怎样创建表

建表(公司管理系统)

–职员表

sql server建议表的设计者，最好给表定义一个主键，用来标示唯一的一条记录。

1、主键(primary key)，不能够重复出现；

2、主键必需给值，换言之主键不能为null

3、主键可以修改，但不能修改为同名的，同时要明确字段不有重复，否则报错。

 

表的管理–查询

查询表是对表操作的最重要的操作，这个后面详细讲解

 

表的管理–添加数据

所有字段都插入

insert into emp  values(7521,’ward’,’salesman’,7698,’1981-2-22′,1250.00,500.00,30)

 

插入部分字段(语法)

insertinto 表名 (字段名1,字段名2,…) values (对应字段数据1,对应字段数据2,…)

 

表的管理–修改数据

改一个字段(语法)

update 表名 set字段名=’新值’ where 字段=’值’

例：update  student set sex=’女’ where xh=’A001′

    update clerk set age=38 where cleName=’贾政’

 

修改多个字段(语法)

update 表名 set字段名1=’新值’,字段名2=’新值’… where 字段=’值’

例：update student   set sex=’男’,birthday=’1980-04-01′ where xh=’A001′

    update clerk set cleName=’薛蟠’,age=40 where cleId=2

 

修改含有null值的数据(语法)

update 表名 set字段名=’新值’ where 字段 isnull

例：update clerkset cleName=’薛宝钗’ where age is null

 

表的管理–删除数据

删除全部数据

deletefrom 表名

 

删除指定数据

deletefrom 表名 where字段名=’值’

 

删除多个指定数据(and是条件满足，or是前提条件不满足时使用)

deletefrom 表名 where字段名1=’值’ and 字段名2=’值’ and或or …

 

表的基本查询–介绍(重点)

select语句在软件编程中非常的有用，要好好掌握。

 

介绍主键和外键

主键，一张表中只能有一个主键

1、主键(primary key)，不能够重复出现；

2、主键必需给值，换言之主键不能为null

3、主键可以修改，但不能修改为同名的，同时要明确字段不有重复，否则报错。

 

外键

1、外键只能指向主键

2、外键和主键的数据类型要一致

 

数据库例解主键、外键、多功能查询，见下例：

—建库

create database test

 

—创建dept表

create table dept—部门表

(deptno int primary key,—部门编号

dname nvarchar(30),—部门名称

loc nvarchar(30)—所在地

)

 

—建表emp

create table emp—表名

(empno int primarykey,—编号

ename nvarchar(30),—名字

job nvarchar(30),—职位

mgr int,—上级编号

hiredate datetime,—入职日期

sal numeric(10,2),—薪水

comm numeric(10,2),—奖金

deptno int foreign key references dept(deptno)—部门编号(做成外键)

)

 

—针对外键，请注意：

–1、外键只能指向主键

–2、外键和主键的数据类型要一致

 

—向dept表中添加数据

insert into deptvalues (10,’accounting’,’new york’)

insert into deptvalues (20,’research’,’dallas’)

insert into deptvalues (30,’sales’,’chicago’)

insert into deptvalues (40,’operations’,’boston’)

 

—向emp表中添加数据

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7369,’smith’,’clerk’,7902,’1980-12-17′,800.00,20)

insert into empvalues(7499,’allen’,’salesman’,7698,’1981-2-20′,1600.00,300.00,30)

insert into empvalues(7521,’ward’,’salesman’,7698,’1981-2-22′,1250.00,500.00,30)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7566,’jones’,’manager’,7839,’1981-4-2′,2975.00,20)

insert into empvalues(7654,’martin’,’salesman’,7698,’1981-9-28′,1250.00,1400.00,30)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7698,’blake’,’manager’,7839,’1981-5-1′,2850.00,30)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7782,’clark’,’manager’,7839,’1981-6-9′,2450.00,10)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7788,’scott’,’analyst’,7566,’1987-4-19′,3000.00,20)

insert into emp(empno,ename,job,hiredate,sal,deptno) values (7839,’king’,’president’,’1981-11-17′,5000.00,10)

insert into empvalues (7844,’turner’,’salesman’,7698,’1981-9-8′,1500.00,0.00,30)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7876,’adams’,’clerk’,7788,’1987-5-23′,1100.00,20)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7900,’james’,’clerk’,7698,’1981-12-3′,950.00,30)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7902,’ford’,’analyst’,7566,’1981-12-3′,3000.00,20)

insert into emp(empno,ename,job,mgr,hiredate,sal,deptno) values (7934,’miller’,’clerk’,7782,’1982-1-23′,1300.00,10)

 

—查看dept表内容

select * from dept

—查看emp表内容

select * from emp

 

—查询所有列

—语法：select * from表名 where 条件

select * from emp

 

—查询指定列(sql sever不区分大小写,oracle区分大小写)

—语法：select字段1,字段2 from 表名 where 条件

 

—查询smith的薪水，工作，所在部门

selectsal,job,deptno from emp where ename=’smith’

 

—取消重复行(distinct只能消除完全一样的行，保留一行)

—语法：select distinct字段 from 表名 where 条件

 

—统计共有多少个部门编号

select distinct  deptno from emp

 

—使用算数表达式

—显示每个雇员的年工资+奖金

select ename”姓名”,sal*13+isnull(comm,0)*13″年收入” from emp

 

—使用where子句

—如何显示工资高于3000的员工

select ename”员工姓名”,sal”工资高于3000″ from emp where sal>3000

 

—如何查找1982-1-1后入职的员工

select ename”员工姓名”,hiredate”1982-1-1后入职日期” from emp where hiredate>’1982-1-1′

 

–如何显示工资在2000到2500的员工情况

select * from empwhere sal>=2000 and sal<=2500

select * from empwhere sal between 2000 and 2500

 

—如何使用like操作符(模糊查询)

–%：表示0到多个字符 _:表示单个字符

—如何显示首字符为S的员工姓名和工资

select ename,salfrom emp where ename like ‘s%’

 

—如何显示第三个字符为o的所有员工的姓名和工资

select ename,salfrom emp where ename like ‘__O%’

 

—在where条件中使用in

—如何显示empno为123,345,800…的雇员情况

select * from empwhere empno in(123,345,800)

 

—使用is null的操作符

—如何显示没有上级的雇员的情况

select * from empwhere mgr is null

 

—使用逻辑操作符号

—查询工资高于500或是岗位为manager的雇员，同时还要满足他们的姓名首写字母为大写的J

select * from empwhere (sal>500 or job=’manager’) and ename like ‘J%’

 

—使用order by字句(asc默认是升序排列,desc为降序排列)

—如何按照工资的从低到高的顺序显示雇员的信息

select sal from emporder by sal asc

—按照部门号升序而雇员的工资降序排列

–order by可以根据不同的字段排序

select deptno,salfrom emp order by deptno,sal desc

 

—使用列的别名排序(别名需要使用””号圈中)

select  ename,sal*12″年薪”from emp order by “年薪”

 

表的复杂查询

说明：

    在实际应用中经常需要执行复杂的数据统计，经常需要显示多张表的数据，现在我们给

大家介绍较为复杂的select语句

数据分组-max(最大),min(最小),avg(平均),sum(和),count(统计)

 

见下例：

—如何显示所有员工中最高工资和最低工资

select  min(sal)”最低工资”,max(sal)”最高工资” from emp

—显示最低工资并显示出雇员名字

select  ename”姓名”,sal”工资” from emp where sal=(select min(sal) from emp)

 

—显示所有员工的平均工资和工资总和

select avg(sal)”平均工资”,sum(sal)”工资总和” from emp

—把高于平均工资的雇员的名字和他的工资显示出来

select ename,salfrom emp where sal>(select avg(sal) from emp) order by sal

 

—计算共有多少员工

select   count(*)”雇员数”from emp

 

—扩展要求

—请显示工资最高的员工的名字、工作岗位

select ename,job  from emp where sal=(select max(sal) from emp)

 

—请显示工资高于平均工资的员工信息,并显示平均工资(效率不高)

select ename”姓名”,job”岗位”,sal”工资”,(select avg(sal)from emp)”平均工资” from emp where sal>(selectavg(sal) from emp) order by sal

 

表的复杂查询

group by和having子句

group by用于对查询的结果分组统计

having子句用于限制分组显示结果

 

例：

–group by和having子句使用(having与group by结合使用，可以对分组后的查询结果进行筛选)

—如何显示每个部门的平均工资和最高工资

select deptno”部门号”,avg(sal)”平均工资”,max(sal)”最高工资”,min(sal)”最低工资” from emp group by deptno

—如何显示每个部门的平均工资和最高工资并显示部门名称(多表查询)

select  emp.deptno”部门号”,avg(sal)”平均工资”,max(sal)”最高工资”,min(sal)”最低工资”,dept.dname”部门名称” from  emp,dept where emp.deptno=dept.deptno group by emp.deptno,dept.dname

 

—显示每个部门的每种岗位的平均工资和最低工资

select deptno”部门号”,job”岗位”,avg(sal)”平均工资”,min(sal)”最低工资” from emp group by deptno,job order by deptno

 

—显示平均工资低于2000的部门号和它的平均工资

select deptno”部门号”,avg(sal)”平均工资” from emp group by deptno having avg(sal)<2000

 

—显示平均工资大于2000的部门号和它的平均工资并按升序排列

select deptno”部门号”,avg(sal)”平均工资” from emp group by deptno having avg(sal)>2000 order by”平均工资”

 

表的复杂查询

对数据分组的总结

1、分组函数只能出现在选择列表，having、order by子句中

2、如果在select语句中同时包含有group by，having，ovrderby那么他们的顺序是group by，having，order by

3、在选择列中如果有列、表达式、和分组函数，那么这些列和表达式必需有一个出现在group by子句中，否则就会出错

如：selectdeptno,avg(sal),max(sal) from emp group by deptno having avg(sal)<2000

这里deptno就一定要出现在group by中

 

表的复杂查询–多表查询

说明：

    多表查询是指基于两个或两个以上的表或是视图的查询，在实际应用中，查询单个表可能不能满足你的需求，(如显示sales部门位置和其员工的姓名)，这种情况下需要使用到(dept表和emp表)

 

—多表查询

—如果多张表都有相同名字的字段，则需要带表名(别名)

—显示sales部门位置和其员工的姓名

select  emp.ename”员工姓名”,dept.dname”部门名称”,dept.loc”部门所在地” from  emp,dept where dept.dname=’sales’ and emp.deptno=dept.deptno

 

—显示雇员名，雇员工资及所在部门的名字

select  emp.ename”雇员名”,emp.sal”雇员工资”,dept.dname”部门名称” from  emp,dept where emp.deptno=dept.deptno

 

—如何显示部门号为10的部门名、员工名和工资

select  emp.deptno”部门号”,dept.dname”部门名称”,emp.ename”员工名称”,emp.sal”工资” from emp,dept where emp.deptno=dept.deptno and emp.deptno=10

 

—显示雇员名、雇员工资及所在部门的名字并按部门排序

select e.ename”雇员名字”,e.sal”工资”,d.dname”部门名称” from empe,dept d where e.deptno=d.deptno order by d.dname

 

表的复杂查询–多表查询

自连接

自连接是指在同一张表的连接查询

例：

—自连接

—显示某个员工的上级领导的姓名，比如显示”ford”的上级

select (select enamefrom emp where ename=’ford’)”员工姓名”,ename”上级领导” from emp where  empno=(select mgr from emp where ename=’ford’)

 

—显示公司每个员工姓名和他的上级的名字

—分析，把emp表看成两张表分别是worker/boss

—外连接(左外连接、右外连接)

select  worker.ename”员工名字”,boss.ename”领导名字” from emp worker,emp boss where worker.mgr=boss.empno

 

表的复杂查询–子查询

什么是子查询

子查询是批嵌入在其它sql语句中的select语句，也叫嵌套查询

 

单行子查询

单行子查询是指只返回一行数据的子查询语句

—如何显示与smith同一部门的所有员工？

select deptno”部门号”,ename”员工名字” from emp where deptno=(select deptno from emp where  ename=’smith’)

 

多行子查询

多行子查询指返回多行数据的子查询

—如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号

select * from emp  where job in(select distinct job from emp where deptno=10)

 

在from子句中使用子查询

—如何显示高于部门平均工资的员工名字、薪水、部门的平均工资

—分析：1、首先要知道各个部门的平均工资

select  avg(sal)”部门平均工资”,deptno  from emp group by deptno

–2、把上面的查询结果当作一个临时表对待

select e.ename”员工名字”,e.sal”薪水”,temp.myavg”部门平均工资”,e.deptnofrom emp e,(select avg(sal) myavg,deptno from emp group by deptno) temp wheree.deptno=temp.deptno and e.sal>temp.myavg

 

在from子句中使用子查询

这里需要说明的当在from子句中使用子查询时，该子查询会被作为一个临时表来对待，当在from子句中使用子查询时，必需给子查询指定别名

 

分面查询

按雇员的id号升序取出

—请显示第5个到第10个入职的雇员信息(按照入职的时间先后顺序查找)

—分析：1、显示第1个到第4个入职的雇员

select  top 4 * from  emp order by hiredate

–top后的数表示要取出几条记录

select top 6 * from  emp where empno not in(select top 4 empno from emp order by hiredate) order byhiredate

 

—请显示第11个到13个入职的雇员信息

select top 3 * from  emp where empno not in(select top 10 empno from emp order by hiredate) order byhiredate

 

—请显示第5个到9个入职的雇员信息(按薪水高低排序)

select top 5 * from  emp where empno not in(select top 4 empno from emp order by sal desc) order bysal desc

 

用查询结果创建新表

这个命令是一种快捷的建表方法

select *(这里可以选择字段) into另一个表名 from 表

 

—如何删除掉一张表重复记录

create table cat(

catId int,

catName varchar(40)

)

 

insert into catvalues(1,’aa’)

 

select * from cat

 

–1、把cat表的记录distinct后的结果，放到临时表中

select  distinct * into #temp from cat

–2、把cat表的记录清空

delete  from cat

–3、把临时表中的数据信息加入到cat表中

insert  into cat select * from #temp

–4、删除临时表

drop table#temp

 

 

–左外连接和右外连接

—思考题：显示公司每个员工和他的上级领导的名字

—内连接的处理方式(内连接只显示匹配的信息)

select  worker.ename”员工名字”,boss.ename”领导名字” from emp worker,emp boss where worker.mgr=boss.empno

 

—思考题：显示公司每个员工和他的上级领导的名字，没有上级领导的也要显示出来

—左外连接：指如果左边的表记录全部显示，如果没有匹配的记录，就用null填写

select  worker.ename”员工名字”,boss.ename”领导名字” from emp worker left join emp boss on worker.mgr=boss.empno

—右外连接：指如果右边的表记录全部显示，如果没有匹配的记录，就用null填写

select  worker.ename”员工名字”,boss.ename”领导名字” from emp worker right join emp boss on worker.mgr=boss.empno

 

维护数据的完整性–约束

约束用于确保数据库数据满足特定的商业规则。在sql server和oracle中，约束包括：not null、unique，primary key，foreign key和check五种

 

维护数据的完整性–使用

not null(非空)

如果在列上定义了not null，那么当插入数据时，必需为列提供数据。

—约束机制–not null(非空)

—创建一张表

create table test1

(test1Id int primarykey identity(1,1),

testname varchar(30)not null,–not null不能为空

testpass varchar(30)not null,

testage int —不写代表可以为空

)

 

create table test1

(test1Id int primarykey identity(1,1),–identity(1,1)自增长1条记录

testnamevarchar(30),

testpassvarchar(30),

testage int —不写代表可以为空

)

—删除表

drop table test1

—向表插入数据

insert into test1(testage) values (3)

insert into test1 (testname,testpass,testage)values (”,”,5)–”空与null空是不一样的

—查询表内容

select * from test1

 

unique(唯一)(一张表中可以有多个)

当定义了唯一约束后，该列值是不能重复的，但是可以为null,并只能有一个空值

—约束机制–unique(唯一)

—建表

create table test2

(test1Id int primarykey identity(1,1),–identity(1,1)自增长1条记录

testname varchar(30)unique, –unique唯一的，数据不允许重复,但可以为空

testpassvarchar(30),

testage int —不写代表可以为空

)

 

insert into test2(testname,testpass,testage) values (‘aa’,’123′,45)

insert into test2(testpass,testage) values (‘123’,45)

 

select * from test2

 

primarykey(主键)(一张表中只可以有一个主键)

用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null

需要说明的是：一张表最多只能有一个主键，但是可以有多个unqiue约束。

表可以有复合主键，有多个列构成一个主键

—复合主键

create table test3

(testId int,

testnamevarchar(30),

testpassvarchar(30),

testage int,

primary key(testId,testname)—复合主键，需单独声明

)

 

—行级定义和表级定义

create table test4

(testId int,—在字段中定义主键为行级定义.例：testId int primary key

testnamevarchar(30),

testpassvarchar(30),

testage int,

primarykey (testId,testname)–复合主键，需单独声明为表级定义

)

 

foreignkey(外键)(外键在从表上，要配合主表，但主表要有主键或unique约束)

用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必需具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必需在主表的主键列存在或是null

 

check(强制条件)

用于强制行数据必需满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间如果不再1000-2000之间就会提示出错。

–check(强制条件)

create table test5

(testId int,

testnamevarchar(30),

testpass varchar(30),

sal intcheck(sal>=1000 and sal<=2000),—规定sal的值在1000-2000之间

)

 

insert into test5values (4,’aa’,’aa’,1200)–可以加入sal值在范围之内

insert into test5values (5,’bb’,’bb’,2200)–不可加入sal值在范围之外

 

select * from test5

 

default使用(默认值)

–default使用

create table mes

(mesId int primarykey identity(1,1),

mesccn varchar(2000)not null,

mesDate datetimedefault getdate()—在不指定值是default可以直接取默认值，也可以由用户指定值

)

 

insert into  mes(mesccn) values(‘abc’)

insert into  mes(mesccn,mesDate) values(‘cba’,’1976-1-6′)

 

select * from mes

 

商店售货系统表设计案例

    现有一个商店的数据库，记录客户及其购物情况，由下面三个表组成：

商品goods(商品号goodsId，商品名goodsName，单价unitprice，商品类别category，供应商provider)；

客户customer(客户号customerId，姓名name，住址address，电邮email，性别sex，身份证cardId)；

购买purchase(客户号customerId，商品号goodsId，购买数量nums)；

请使用sql语言完成下列功能：

1、建表，在定义中要求声明：

1、每个表的主外键；

2、客户的姓名不能为空值；

3、单价必需大于0，购买数量大于0；

4、电邮不能够重复；

5、客户的性别必需是男或者女，默认为男

6、商品类别是’食物’，’日用品’

—商店售货系统表设计案例

create table goods—商品表

(goodsIdnvarchar(50) primary key,—商品Id为主键

goodsNamenvarchar(80) not null,—商品名不为空

unitpricenumeric(10,2) check (unitprice>0),—商品单价限制大于0

category nvarchar(3)check(category in(‘食物’,’日用品’)),—商品类别需在指定的范围内

provide nvarchar(50)—供应商

)

 

create tablecustomer—客户表

(custcmerIdnvarchar(50) primary key,—客户Id为主键

cusname nvarchar(50)not null,—客户名不能为空

address nvarchar(100),—地址

email nvarchar(100)unique,—邮件不为重复

sex nchar(1)check(sex in(‘男’,’女’)) default ‘男’,—性别只能为男或女，默认为男

cardId nvarchar(18)—身份证

)

 

create tablepurchase—购买表

(custcmerIdnvarchar(50) foreign key references customer(custcmerId),—客户Id为外键，并指向customer表的custcmerId主键

goodsId nvarchar(50)foreign key references goods(goodsId),—商品Id为外键，并指向goods表的goodsId主键

nums intcheck(nums>0)—购买数量限定大于0

)

 

表的管理—修改表

添加一个字段

ALTERTABLE distributors ADD COLUMN address varchar(30);

 

修改字段的类型/或是名字(不能有数据)

ALTERTABLE distributors

    ALTER COLUMN address TYPE varchar(80),

    ALTER COLUMN name TYPE varchar(100);

ALTERTABLE distributors RENAME COLUMN address TO city;

 

删除一个字段

ALTERTABLE distributors DROP COLUMN address RESTRICT;

 

修改表的名字

ALTERTABLE distributors RENAME TO suppliers;

 

删除表

drop table表名;

 

数据库的备份和恢复

使用企业管理器完成备份和恢复

使用企业管理器有两种方式完成备份和恢复

1、分离/附加

    分离完后，请到sql server安装的目录下去找两个文件数据库名.mdf和数据库名.ldf，这两个文件就是分离后的文件，数据库分离后，该数据库就不能再使用了。

    附加是指，当用户需要重新使用某个分离的数据库时进行的操作，就是让sql server数据库重新关联该数据库。

 

2、备份/恢复

    备份数据库是指，把某个数据库文件从sql server中备份出来，这样用户可以根据需要再使用(用于恢复、复用..)，备份数据库不会影响到源数据库的使用

    恢复数据库是指，当源数据库因为某种原因(比如源数据库毁坏、数据丢失、系统崩溃)需要恢复时进行的操作

 

使用查询分析器完成备份和恢复

    用企业管理器完成对数据库的备份和恢复简单直观，同样我们也可以在查询分析器中完成类似的任务。

—使用查询分析器对数据库进行备份和恢复

—数据库备份

—语法：backup database数据库名 to disk=’存储路径’

backup  database LiangshanHero2 to disk=’f:/liangshan.bak’

 

—数据库恢复

—语法：restore database数据库名 from disk=’读取路径’

restore  database LiangshanHero2 from disk=’f:/liangshan.bak’

 

—新建数据库

—语法：create datebase数据库名

create  database LiangshanHero2

 

—删除数据库

—语法：drop database数据库名

drop  database LiangshanHero2

 

*******************************************************************************