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