数据库原理与应用

qjaylee

已于 2023-03-18 10:04:11 修改

阅读量142

点赞数 2

文章标签：数据库

于 2023-03-18 10:03:48 首次发布

本文链接：https://blog.csdn.net/liqingjiea/article/details/129624085

版权

数据库原理与应用

文档下载地址

数据库创建

文章目录

数据库原理与应用

一、概念部分

1、数据库

存放数据的仓库；

数据库技术：科学组织和管理数据

2、数据库地位

 ①基本所有应用软件和后台都需要数据库

②占用空间小，易保存 

 ③存储安全

④容易维护和升级

⑤易移植

⑥简化了对数据的操作

3、大数据

4、云计算

5、数据库技术发展

①人工管理

②文件管理

③数据库管理

④新型数据库管理

6、数据库管理系统

访问数据库的客户端软件

小型 Mysql 成本低，开源

中型 Sqlsever 性价比较高

大型Oracle 大型公司

7、数据库三种模式

外模式（用户模式）：一个数据库可以有多个外模式；每个用户只能看到或者访问对应外模式的数据。
模式（概念模式/逻辑模式）：一个数据库只有一个概念模式，数据库中全部数据的逻辑结构和特征的描述。
内模式（存储模式/物理模式）：一个数据库只有一个内模式，组成最优的物理模式，提高数据的存取效率。

外模式/概念模式映射：多个

概念模式/内模式映射：一个

映射-对应关系

数据的独立性及安全性

8、数据模型的三要素：

**数据结构：**类型以及关系
数据操作：哪些人可以做什么操作。增删查改
数据的完整性约束：性别、身份证号

9、创建数据库阶段：

需求分析—概念设计—逻辑设计—物理设计—数据库实施—运行和维护

10、实体、实体集、属性、类型与值、实体之间的联系

（1:1, 1：n; m:n）

（1）实体：客观存在并可以相互区分的事物叫实体。

（2）实体型：用实体名及其属性名集合来抽象和刻画同类实体称为实体型。

（3）实体集：同型实体的集合称为实体集。

（4）实体之间的联系：通常是指不同实体型的实体集之间的联系，实体之间的联系有一对一，一对多和多对多等多种类型。

11、关系型数据库

1）主键：能够唯一识别某事物，以此作为与其他事物的区分的属性

2）外键：描述事物间关系，来自fk_foreign key

3）属性——列——字段：一个事物的某个静态特征

4）记录——行——元组：字段的组合，表示的是一个具体事物、

5）二维表——同一类事物的集合。

12、关系

①关系定义：表与表之间的联系。二维表

②分类：一对一，一对多，多对多

③实现

一对一（很少用）

既可以把表A的主键充当表B的外键，

也可以把表B的主键充当表A的外键

一对多

在多的一方的表里添加外键，外键来自1那一方。

多对多

必须通过单独新建一张表来表示关系。

班级是一张表。

教师是一张表；

班级和教师的关系再新建一张表。

13、约束

（1）分类：

实体完整性

参照完整性

用户定义完整性

**（2）定义：**对一个表中的属性操作的限制叫约束。

（3）具体分类：

① 主键约束：不允许重复，不允许为空；

② 外键约束：通过外键约束从语法上保证了本事物所关联的其他事物一定存在。

③ Check约束：保证事物属性的取值在合法范围之内。

Create table student
(
Stu_id int primary key,
Stu_sal int,
Stu_sal int not null,
Stu_sex nchar(1) default ‘男’，
	Stu_sal int check (stu_sal>=1000 and stu_sal<=8000),
…
)

④default约束：保证事物的属性一定会有个值。

Stu_sex nchar(1) default ‘男’，

⑤唯一约束：保证事物属性的取值不重复。

Stu_name nvarchar(200) unique,

14、关系运算

选择是从一个表里找自己想要记录组建一个新表；投影是从一个表里找自己想要的字段组建一个新表（可按照自己意愿排序）；连接是从关联的表里找自己想要的组建一个新表。

15、创建数据库阶段

需求分析——概念设计——逻辑设计-物理设计-数据库实施-运行和维护

需求分析：

（1）有多少数据？数据在哪？是否有已有数据资源？

（2）必须保存哪些数据？数据类型？

（3）谁使用数据？如何使用？

（4）数据是否经常修改？如何修改？什么时候修改？

（5）某个数据是否依赖另一个数据或被其他数据引用？

（6）某个信息是否要唯一？

（7）哪些数据是组织内部的？哪些是外部数据？

（8）哪些业务活动与数据有关？如何支撑？

（9）数据访问的频度和增长幅度如何

（10）谁可以访问数据？如何保护数据？

概念设计：

设计局部E-R图

设计全局E-R图

合并并消除冲突、冗余

冲突：属性冲突、命名冲突、结构冲突

逻辑结构设计：

将E-R图中的实体、属性、联系转换为关系模式。

实体类型——关系模式

实体属性——关系属性

不同联系不同处理

① 1：1——在任意一个关系模式中加入另一个关系模式的主键和联系类型的属性。

例：

方案一：

职工（职工号，姓名，年龄，产品号）

产品（产品号，产品名，价格）

方案二：

职工（职工号，姓名，年龄）

产品（产品号，产品名，价格，职工号）

方案二优。

在这里插入图片描述

② 1：n——在n端实体类型转换成的关系模式中加入1端的实体类型的主键及关系的属性。例：

在这里插入图片描述

仓库（仓库号，地点，面积）——主键表

产品（产品号，产品名，价格，仓库号，数量）——外键表

在这里插入图片描述

职工（职工号，姓名，年龄，领导工号）

领导工号

③ m:n——联系类型转换为关系模式，其属性为两端实体类型的主键和联系类型的属性。例：

在这里插入图片描述

商店（店号，店名，店址，店经理）

商品（商品号，商品名，单价，产地）

经营（店号，商品号，月销售量）

存在问题

在这里插入图片描述

① 数据冗余太大。系名、系主任名重复多次。

② 插入异常。一个新系没有招生时，或者没有选修课程时，系名或系主任名无法插入。

③ 删除异常。当某系学生全部毕业而又没新招生时，删除学生信息同时系及系主任名随之删除。

④ 更新异常。若更换系主任，数据库中的相关学生记录全部修改。

解决方法：

模式分解

S（sno,sname,sage,asex,sdept）
SC（sno,cname,score）
DEPT(sdept,mname)

二、数据库的创建及维护

1、创建数据库

①图形化操作：数据库——右键——新建数据库

.mdf .ldf .ndf

类型	缩写	中文意思
mdf	primary data file	主要数据文件
ndf	secondary data files	次要数据文件
ldf	Log data files	事务日志文件

②使用SQL语句

create database testdb02
	on primary(
	name='testdb02',
	filename='c:\testdb\testdb02.mdf',
	size=10mb,
	maxsize=100mb,
	filegrowth=5mb)
log on(
	name='testdb02_log',
	filename='c:\testdb\testdb02_log.ldf',
	size=10mb,
	maxsize=100mb,
	filegrowth=5mb)

2、数据库的分离和附加

**分离：**选中要分离的数据库-右键-任务-分离-删除链接，更新

**附加：**数据库——右键-附加-找到需要附加的文件-刷新

3、数据库的删除

图形化操作：右键——删除-删除现有连接

语句：drop database testdb01;

4、数据库的修改

图形化操作：右键——属性——文件—

语句：

exec sp_helpdb testdb02;--查看数据库设置

alter database testdb01
   modify name=testdb02;   --修改数据库名字

alter database testdb02   --修改文件
   modify file(
   name='testdb01_log',
   size=100mb,
   maxsize=200mb,
   filegrowth=10mb);

三、数据表的创建及操作

1、数据类型

①数字类型：int 、float

②时间类型：date/smalldatetime/datetime

③字符串类型：char,n,var

2、新建表

①可视化操作：

表-右键——新建-表

主键——选中需设置为主键的字段——右键——设为主键

外键——（外键表）——右键——关系——添加——表和列规范——…——选定外键表和主键表，选定好外键字段

②语句操作：

create table dept
	(dept_id int primary key,
	dept_name nvarchar(100) not null,
	dept_address nvarchar(100)
)

③ identity自动增长

主键自动增长，用户不需要给主键赋值

未设置自动增长

create table student
	(stu_id int primary key,
	stu_name nvarchar(20) not null
)

insert into student values(1,'张三')

insert into student values(2,'李四')

insert into student values(1,'张三')—error

insert into student (stu_name)values('张三') --error

-- 必须给主键赋非重复值

设置自动增长

create table student1
	(stu_id int primary key identity(1000,5),--从1000开始，每次增长5，如不写，从1开始，每次增长1
	stu_nmae nvarchar(20) not null
)

insert into student1 (stu_nmae)values('张三')--自动填充为1000

insert into student1 values('李四')--自动填充为1005

delete from student1 where stu_nmae='李四'

insert into student1 values('王五')--自动填充为1010

delete from student1 where stu_name='王五'

--重置,重置的数为需要写的上一个数
dbcc checkident('student1',reseed,1000)--下次填充从1005开始

insert into student1 values('王五')

--默认不允许手动给主键赋值,手动插入
set identity_insert student1 on 

insert into student1 (stu_id,stu_nmae)values (1,'liming')

④外键设置

select * from dept ——查询表

create table emp
	(emp_id int primary key,
    emp_name nvarchar(100) not null,
	emp_sex nchar(1),
	dept_id int references dept(dept_id)
)

insert into dept values(10,'market','beijing')

⑤check约束

可视化操作：选中需要约束的字段——右键——check约束——添加——添加条件

语句：

create table student1
	(stu_id int primary key,stu_sal int check(stu_sal>=1000 and stu_sal<=8000)
)

⑥Default约束

可视化操作：选中需设置的字段，下边——默认值或绑定——输入默认值

语句：

create table student2
	(stu_id int primary key,
	stu_sal int check(stu_sal>=1000 and stu_sal<=8000),
	stu_sex nchar(1) default '男'
)

insert into student2 (stu_id,stu_sal) values(2,1000)

注释：单行-- ;

多行 / */ ;*

⑦Unique约束

图形化操作：

右键——索引/键——添加——选择列——唯一选为是

语句：

create table student3
	(stu_id int primary key,
	stu_sal int check(stu_sal<=8000 and stu_sal>=1000),
	stu_sex nchar(1)default'男',
	stu_name nvarchar(50) unique
)

3、修改表结构

可视化操作：

右键：重命名、删除

设计：修改列名、修改数据类型、删除列、添加列、设置/删除主键/外键/check约束/default约束/unique

SQL语句：

① 更改表名：

exec sp_rename 'fanxin','student3'

/*exec sp_rename '原表名'，'新表名'*/

② 更改字段名字：

exec sp_rename 'student3.stu_id','stu_no','column';

/*exec sp_rename '表名.原列名','新列名','column'*/

③添加字段：

alter table student3
	add stu_age varchar(10)

--alter table 表名 add 字段名 数据类型 是否允许为空

④删除字段：

alter table student3 
	drop column stu_age

/*alter table 表名 drop column 列名*/

⑤修改字段类型：

Alter table student3
	alter column stu_name nvarchar(50)

⑥修改字段是否为空：

alter table student4
	alter column stu_id int not null

alter table student4
	alter column stu_id int null

alter table student4
	alter column stu_id int

/*alter table 表名
	alter column 字段名 数据类型 not null/null/空着*/

⑦添加/删除主键：

添加主键

alter table student3
	add constraint hhh primary key(stu_id)

/*alter table 表名
	add constraint 约束名 primary key(字段名)*/

删除主键

exec sp_helpconstraint N'student3'--查看约束名

alter table student3
	drop constraint hhh

/*alter table 表名
	drop constraint 约束名

⑧添加/删除CHECK约束：

添加CHECK约束

alter table student4
	add constraint kkk check(stu_sal<=8000 and stu_sal>=1000)

/*alter table 表名
	add constraint 约束名 check(表达式)*/

删除check约束

exec sp_helpconstraint N'student3'

/*alter table 表名
	drop constraint 约束名

⑨添加/删除DEFAULT约束

添加

alter table student4
	add constraint ppp default '男' for stu_sex

/*alter table 表名
	add constraint 约束名 default 默认值 for 列名*/

删除

exec sp_helpconstraint N'student4'

/*alter table 表名
	drop constraint 约束名

⑩添加/删除unique约束：

添加

alter table student4
	add constraint lll unique(stu_name)

删除

exec sp_helpconstraint N'student4'

/*alter table 表名
	drop constraint 约束名

⑪添加/删除外键

添加

alter table emp
	add constraint aaa foreign key(dept_id)references dept(dept_id)

删除

查看约束名：

exec sp_helpconstraint N'student3'

删除约束：alter table 表名

drop constraint 约束名

总结：约束的添加和删除

添加：

alter table 表名
	add constraint 约束名 约束的表达

删除：

exec sp_helpconstraint N'表名'

alter table 表名
	drop constraint 约束名

4、删除表

drop table 表名

四、数据表记录的相关操作

1、新增表记录

可视化操作：编辑前200行。

①插入一行

所有列给数：

insert into student3 values(3,4000,'女','孙苹')

部分列给数：

insert into student3(stu_no,stu_sal) values(4,4000)

insert into student3 values(4,4000)—error

②插入多行

insert into student3 values(5,4000,'女','曹红红'),(6,4000,'男','樊鑫')

insert into 表名 values(记录1的值),(记录2的值),()…

① 复制表的记录

复制的全部列

insert into student4 select * from student3

insert into 目标表的名字 select * from 源表名

复制的部分列

insert into student4(stu_id,stu_name) select stu_no,stu_name from student3

② 导入一个表

选中数据库——右键——任务——导入数据——下一步——数据源（选excel，找到路径）——目标（选SQL Sever native client 11.0,选择/新建数据库）——编辑（源：修改字段的名字，数据类型，是否为空；目标：改表的名字，选择表）——下一步——完成。

刷新

2、修改表记录

①修改一个属性

update student  --指定需要修改的表

set stu_id=5 where stu_name='王伟'  --在哪改啥

②修改多个属性

update student 
	set stu_name='sunping',stu_id=7 where stu_id=3

--一次性修改多个属性
--修改表记录也要遵循约束条件

3、删除表记录

delete from student  —— 删除所有记录
	where stu_id=5—删除符合条件的记录

4、查询表记录

select准备工作

熟悉SCOTT在附件中

Dept（部门表）：

字段名：deptno(部门编号)\dname(部门名称)\loc，5行

Emp（员工表）：

字段名：empno(员工编号)/ename(员工名称)/job(工作)/mgr(上司的员工编号)/hiredate(入职时间)/sal(月薪)/comm(奖金)/deptno，14行

SALGRADE（工资等级表）:

字段名： grade/losal(最低工资)/hisal(最高工资)，5行

（1）计算列

select *from emp

/*select-选择，*-所有字段，from-从 表的名字*/

select empno,ename from emp--选择部分列

select ename,sal from emp--选择姓名/月薪

select ename,sal*12 from emp--得到姓名/年薪

select ename,sal*12 as "年薪" from emp

select ename”姓名”,sal”月薪”,sal*12 “年薪”from emp

/–全部，字段别名用“”引起来，as可以省略。/

（2）distinct

【不同的，剔重的】

select deptno from emp;--输出14行记录，不剔重

select distinct deptno from emp;--输出剔重后的deptno,3行

select distinct comm from emp;--5行

--distinct 能够剔重null

select distinct comm,deptno from emp;

--对comm 和dept 的组合进行剔重

select comm,distinct deptno from emp;--error,逻辑有冲突

（3）between

[在某个范围内]

select * from emp where 3>2;
--where 时进行过滤的，如果后边永远为真，那就不过滤。
select * from emp where sal=3000;

–把工资为3000的输出

–查找工资在1500-3000之间（包括1500和3000）的所有员工信息

select * from emp where sal>=1500 and sal<=3000;

--where 对sal进行了过滤，筛选出1500-3000的
--等价于
select * from emp where sal between 1500 and 3000;

–查询工资小于1500或大于3000的员工信息

select * from emp where sal<1500 or sal>3000;

--等价于
select * from emp where sal not between 1500 and 3000;

（4）in

【属于若干个孤立的值】

①select * from emp where sal in(1500,3000,5000)

--输出的时sal=1500或者3000或者5000的员工信息

--等价于

select * from emp where sal=1500 or sal=3000 or sal=5000;

②–输出的sal不是1500且不是3000且不是5000的员工信息

select* from emp where sal not in(1500,3000,5000)
--等价于

select * from emp where sal !=1500 and sal<>3000 and sal!=5000;

--or取反是and,and取反是or
--不等于的表示：!=,<>

（5）top的用法

【选取最前边的若干条记录】

select top 2 * from emp;
select top 2 * from 表名;
--输出emp 前两行记录的所有字段

select top 15 percent *from emp
--输出前15%的记录，小数向上取整

顺序！！！

–输出工资在1500到3000之间的前2个员工的信息

select top 2 * from emp 
	where sal between 1500 and 3000;

--先执行where,再执行top

–把工资在[1500，3000]的员工中工资最高的前2个人的信息输出

select top 2 *from emp 
	where sal between 1500 and 3000
	order by sal desc;

顺序：

–先执行from，再where，再order by,最后top

select* into emp2  from  (select top 4 * from emp)“a”;

select *from emp2 where sal between 1500 and 3000 order by sal desc;

 

select *  from 
 (select top 4 * from emp)"a"
 where sal between 1500 and 3000 order by sal desc

（6）null

【没有值，空值】

select * from emp  where comm <>null;
 --null 不能参加<>,!=,=运算
select * from emp  where comm is null

select * from emp  where comm is not null
--null可以参与is ，is not运算

–输出每个员工的姓名、月薪、年薪（包含奖金）

 select ename"员工姓名",sal"月薪",sal*12+comm"年薪" 

 from emp --error，好多结果为空，null不能参与数据运算

 

 select ename"员工姓名",sal"月薪",
 	comm"奖金",sal*12+isnull(comm,0)"年薪"
    from emp;
 /*isnull函数：isnull(字段名，0)如果字段是null,

 则返回0，否则返回具体值。*/

（7）order by

【以某个字段排序】

select * from emp order by sal;
select * from emp order by sal asc;
 --升序asc,可以省略
 
select * from emp order by sal desc;
 --desc,降序

思考：

 select * from emp order by deptno,sal;
 --先按照部门排序，再按照工资排序

 select * from emp order by deptno desc,sal;
 --desc只对deptno产生了影响

 select * from emp order by deptno,sal desc;
 --desc只对sal 产生影响

总结：

	order by a,b;--a,b均是升序

   order by a,b desc;--a升序，b降序

   order by a desc,b;--a降序，b升序

   order by a desc,b desc;--a,b均为降序

如果不指定排序标准，那默认的就是升序，用asc，可以省略。

为一个字端指定的排序标准不影响其他字段。

（8）模糊查询

select * from emp where ename like '%A%'

–ename 只要含有字母A就输出

/*格式：select 字段的集合 from

表名 where 某个字段 like ‘匹配的条件’*/

通配符：

% 表示任意0个或者多个字符

_ 下划线，任意单个字符

[a-f] a到f中的任意单个字符，只能是a,b,c,d,e,f中的一个

[a,f] a或f，单个字符

[^a-f]不是a到f中的任意一个字符

[^a,f]不是a，也不是f

select * from emp where ename like '%A%'
--ename里包含字母A的全部输出

select * from emp where ename like 'A%';
--首字母是A的全部输出

select * from emp where ename like '%A';

--尾字母是A的全部输出

select * from emp where ename like '_A%';
--第二个字母是A的全部输出

select * from emp where ename like '_A_';
--只有三个字母，中间字母为A的输出

select * from emp where ename like 'AL%';
--首字母是A,第二个字母是L的全部输出

select * from emp where ename like '[a-f]%';
--第一个字母是a到f中的任意一个字母的输出

select * from emp where ename like '_[a-f]%';
--第二个字母是a到f中的任意一个字母的输出

select * from emp where ename like '[a,f]%';
--第一个字母是a或者是f中的输出

select * from emp where ename like '[^a-f]%';
--第一个字母不是a到f中的任意一个字母的输出

select * from emp where ename like '[^a,f]%';
--第一个字母不是a也不是f中的输出

select * from emp5 where ename like '鲍%';
--把姓鲍的输出

select * from emp5 
	where ename like '%\%%' 

escape '\'; --escape特殊化,把含有%的输出

select * from emp5 

where ename like '%\_%' 

escape '\';--escape特殊化，把含有_的输出

（9）聚合函数

函数的分类：

**单行函数：**每一行都返回一个值

select lower(ename) from emp;
--lower()大写转换为小写，小写转大写upper（）

**多行函数：**多行返回一个值，聚合函数属于多行函数

select max(sal) from emp;
--max()求最大值，返回一行数据

常用的聚合函数：

Max()最大值

Min()最小值

Avg()平均值

Count()计数，求个数

Sum()

Count函数

select count(*) from emp;
--返回一行，返回14，emp表的所有记录的个数

select count(deptno) from emp;
--返回14，deptno的重复记录有效计入

select count(distinct deptno) from emp;
--返回3，deptno 剔重后再统计

select count(comm) from emp;
--返回4，comm为null的记录不被当作有效记录

**总结：**count(*)返回表中所有记录的个数

Count（字段名字）返回的是字段值非空的记录的个数，重复的记录也被当做有效值。

Count（distinct字段名字）返回的是字段不重复且非空的记录的个数。

需要注意：

select max(sal)"最高工资",min(sal)"最低工资",
	avg(sal)"平均工资",count(*)"员工人数" from emp;
--OK，全部为多行函数

select lower(ename),max(sal) from emp;
--error，单行函数和多行函数不能混用

select ename,max(sal) from emp;
--error

（10）group by

1）–输出每个部门的编号和该部门的平均工资

select deptno,avg(sal)--①选出需要的字段
	from emp    --②确定进行操作的表
	group by deptno;--③分组
--顺序：②-③-①

2）

select deptno,avg(sal),ename 
	from emp 
	group by deptno
--error,ename不是聚合函数也没有出现在group by，属于组内信息

总结：

① 使用了group by 之后，select中只能出现分组后的整体信息，不能出现组内的详细信息。

整体信息：group by后的字段或者聚合函数

② 分组后使用聚合函数，函数作用于最小分组

判断对错：

select (...)
	from emp 
	group by deptno
-- (...)是ename/job/*报错；
-- (...)是聚合函数，不报错，且作用于最小分组

3）Group by后边有多个字段

select deptno, job, count(*)"人数",avg(sal)"平均工资"
	from emp
	group by deptno,job
	order by deptno;

/*①先按照部门分组，再按照工作分组；
②聚合函数作用于最小分组；
③select 后边可以出现：deptno,job,聚合函数
不可以出现：*，ename*/

总结：

**格式：**group by 字段的集合

**功能：**把表中的记录按照字段分成不同的组

注意：

①group by a,b,c

先按照a分组，再按照b分组，最后按照c分组。最终统计的是最小分组的信息。

② select后边只能出现整体信息

整体信息：group by 后出现的字段、聚合函数

（11）having

【对分组之后的信息进行过滤】

–输出部门平均工资大于2000的部门的编号以及平均工资

select deptno,avg(sal)"平均工资"--④输出所需字段
	from emp--①找到表
	group by deptno--②分组
	having avg(sal)>2000;--③对分组后信息进行过滤

–判断

select deptno,avg(sal)"平均工资"
	from emp
	group by deptno
	having ename like'%A%';

--avg(sal)>2000，ok，聚合函数
--"平均工资" ，error,having后不能出现字段别名
--deptno>10 ,ok，deptno属于分组后的信息
--count(*)>3，ok，聚合函数，前边select没出现，having后边也能用
--ename like'%A%'，error,ename是组内的详细信息

select deptno,avg(sal)"平均工资"
	from emp
	group by deptno
	having avg(sal)>2000
	where ename not like '%A%';
--error,where位置固定，不能放到后边

练习：

/先把工资大于2000的员工找到并按照部门编号分组，将部门平均工资大于3000的部门编号、平均工资、人数、最高工资输出/

select deptno,avg(sal)"平均工资",count(*)"人数",max(sal)"最高工资"
	from emp
	where sal>2000
	group by deptno
	having avg(sal)>3000;

总结：

① having是用来对分组后的信息进行过滤，通常先有group by;如果没有使用group by，但是使用了having，表示把所有记录当作一组进行过滤。仅了解。

② Having后出现的字段必须是分组后的整体信息，不允许出现组内详细信息。

③ Select中可以出现字段别名，但having子句中不能出现别名，只能使用最原始的名字。

④ Having和where的异同

相同点：

都对数据进行过滤，保留有效数据

都不允许出现字段别名，使用原始名

不同点：

where是对原始数据过滤，having是对分组后的数据过滤

where必须写在having前面，顺序不能颠倒

顺序：

select select_list—选出所需字段或者函数

[into new_table_name]—产生新表

from table_list—确定筛选表

where search_conditions—分组前过滤

group by group_by_list—确定分组字段

having search_conditions—分组后过滤

order by order_list[asc/desc]—排序

（12）内连接

①select … from A,B的用法

select * from emp,dept;
--emp是14行8列，dept是5行3列，运行结果是70行11列

笛卡尔积：临时表

行数是A和B的乘积，列数是A和B之和

全部连接到一起，没有连接条件

B表的一行记录对应A的全部记录，A表的一行记录也对应B的全部记录。A的每一条记录和B的每一条记录相互组合。

select ename,dname from emp,dept;

select deptno from emp,dept;--报错，因为deptno不明确是哪个表的，需要写为emp.deptno或dept.deptno

②select … from A,B where…的用法—sql92标准

select * from emp ,dept where empno=7369;
--5行11列，对select...from A,B产生的笛卡尔积用where进行过滤

select * from emp ,dept where emp.deptno=10;
--15行11列，原emp里有3个员工部门编号是10，每个人对应dept的5行，3*5=15

select * from emp ,dept where dept.deptno=10;
--14行11列，原dept里有1行dept=10,这一行需要对应emp里边的14行

select * from emp ,dept where emp.deptno=dept.deptno;
--14行11列，输出一个emp部门编号和dept部门编号对应的临时表

select ename,dname from emp ,dept where emp.deptno=dept.deptno;
--14行2列，输出每个员工的姓名及部门名称



**select** **…** **from** **A,B** **where****…**

③select…from A join B on …—sql99标准

select *from emp 
	join dept--join 连接
	on 1=1;--on连接条件，不能省略，1=1永远真，相当于没有连接条件,全部连接，70行11列

--等价于
select *from emp,dept;

--把工资大于2000的员工姓名及其部门名称输出
--写法一，sql92标准：

select ename,dname,sal from emp ,dept 
	where emp.deptno=dept.deptno  and sal>2000;--where后边连接+过滤条件

–写法二，sql99标准：

select ename,dname,sal from emp 
	join dept
	on emp.deptno=dept.deptno--on后边为连接条件
	where sal>2000;--where后边为过滤条件

小结：

Select…from A,B where…—sql92标准

Select…from A join B on…where…—sql99标准

推荐sql99标准

在sql99标准里，on 和where 可以有不同分工

On 指定连接条件

Where 对连接后的临时表数据进行过滤

–关注格式：

select “E”.ename"员工姓名",“D”.dname

from emp"E"

join dept"D"

on “E”.deptno=“D”.deptno

–实现了输出员工姓名及部门名称

例1：–把工资大于2000的员工姓名及其部门名称输出

--sql92实现：
select "E".ename"员工姓名","D".dname
	from emp"E",dept"D"
	where "E".sal>2000 and "E".deptno="D".deptno

--sql99实现：
select "E".ename"员工姓名","D".dname 
	from emp"E"
	join dept"D"
	on "E".deptno="D".deptno--连接条件，挑选出有实际意义的连接
	where "E".sal>2000--过滤条件

例2：–把工资大于2000的员工姓名、部门名称、工资等级输出

/员工姓名在emp（53）,部门名称在dept（148）,工资等级在salgrade（53）三个表的笛卡尔积（5145）（3+8+3）=35014

emp和dept连接后的临时表再次与salgrade连接*/

--sql99实现
select * from emp"E" join dept"D" 
	on 1=1 
	join salgrade"S"
	on 1=1

--无任何连接条件，形成的是3个表的笛卡尔积，350*14

select * from emp"E" join dept"D"
   on "E".deptno="D".deptno
   join salgrade"S"
   on "E".sal>="S".losal and "E".sal<="S".hisal

--等价于"E".sal between "S".losal and "S".hisal
--三个表有效连接，14行14列

select "E".ename,"D".dname,"S".grade
	from emp"E"
	join dept"D"
	on "E".deptno="D".deptno--连接条件
	join salgrade"S"
	on "E".sal>="S".losal and "E".sal<="S".hisal--连接条件
	where "E".sal>2000;--过滤条件

--sql92实现

select * 
   from emp"E",dept"D",salgrade"S"
   where "E".deptno="D".deptno 
   and ("E".sal>="S".losal and "E".sal<="S".hisal)
   and "E".sal>200

SQL99

Select 字段集合
	From A
	Join B
	On 连接条件
	Join C 
	On 连接条件
	Where 过滤条件

SQL92

Select 字段集合
	From A,B,C
	Where A和B的连接条件
	And A和C的连接条件
	And 过滤条件

select * from emp,dept where dept.deptno=10;

--不等价于
select * from emp
	join dept
	on emp.deptno=dept.deptno
	having dept.deptno=10;

--on后边给出了连接条件，多余；having是对分组后的信息过滤，应使用where

--等价于
select * from emp join dept on 1=1
	where dept.deptno=10;

select * from emp,dept where 
	emp.deptno=dept.deptno
	and dept.deptno=10;

--等价于
select * from emp join dept
	on emp.deptno=dept.deptno
	where dept.deptno=10;

练习：

/*输出工资最高的前三名员工的姓名、

工资、工资等级、部门名称*/

select top 3 "E".ename,"E".sal,"S".grade,"D".dname
	from emp"E"
	join dept"D"
	on "E".deptno="D".deptno 
	join salgrade"S"
	on "E".sal>="S".losal and "E".sal<="S".hisal
	order by "E".sal desc;

/*输出工资最高的前三名员工的姓名（姓名不包含K）、

工资、工资等级、部门名称*/

select top 3 "E".ename,"E".sal,"S".grade,"D".dname
	from emp"E"
	join dept"D"
	on "E".deptno="D".deptno 
	join salgrade"S"
	on "E".sal>="S".losal and "E".sal<="S".hisal
	where "E".ename not like '%K%'
	order by "E".sal desc;

查询顺序

Select  top …
    From A
    Join B
    On ……
    Join C
    On….
    Where…
    Group by 
    Having …
    Order by…

（13）习题

–①输出每个员工的姓名、部门编号、工资、工资等级

select "E".ename,"E".deptno,"E".sal,"S".GRADE
    from emp"E"
    join salgrade"S"
    on "E".sal between "S".losal and "S".hisal

–②查找每个部门的编号，该部门所有员工的平均工资、工资等级

–第一步：输出每个部门的部门编号、平均工资

select deptno,avg(sal)"avg_sal"
    from emp 
    group by deptno

–第二步

select "T".deptno"部门编号","T".avg_sal,"S".GRADE
    from (select deptno,avg(sal)"avg_sal"from emp group by deptno
    )"T"
    join salgrade"S"
    on "T".avg_sal between "S".losal and "S".hisal

引申：

① /*select …from A join B on …等价于

select …from B join A on …*/

select "T".deptno"部门编号","T".avg_sal,"S".GRADE
    from salgrade"S"
    join (select deptno,avg(sal)"avg_sal"from emp group by deptno
    )"T"
    on "T".avg_sal between "S".losal and "S".hisal

–SQL92实现

select "T".deptno"部门编号","T".avg_sal,"S".GRADE
    from salgrade"S", (select deptno,avg(sal)"avg_sal"from emp group by deptno)"T"
    where "T".avg_sal between "S".losal and "S".hisal

/*③查找每个部门的编号、部门名称、该部门的平均工资、

工资等级*/

select "T".deptno"部门编号","D".dname,"T".avg_sal,"S".GRADE
    from (select deptno,avg(sal)"avg_sal"from emp group by deptno
    )"T"
    join salgrade"S"
    on "T".avg_sal between "S".losal and "S".hisal
    join dept"D"
    on "T".deptno="D".deptno

–④输出emp表中所有领导的姓名

select ename from emp where empno 
	in(select distinct mgr from emp)

–思考输出表中所有非领导的姓名

select ename from emp where empno 
	not in(select distinct mgr from emp)

–没有结果，not in和null带来的问题

select ename from emp where empno 
	not in(select distinct mgr from emp where mgr is not null)

 

select ename from emp
	where empno 
	not in(select isnull(mgr,0) from emp);

–⑤输出工资最高的部门编号和部门平均工资

–准备工作：找到工资最高的人，两种思路

--①工资降序排列，然后输出第一行
select * from emp

select top 1 * from emp order by sal desc;

--②用where过滤，工资等于最高工资的人输出
select * from emp
	where sal=(select max(sal) from emp)

–方法一：

select top 1 deptno"部门编号",avg(sal)"部门名称"
    from emp
    group by deptno
    order by avg(sal) desc;

–方法二：

select "E".*
    from(select deptno,avg(sal)"avg_sal"
        from emp
        group by deptno)"E"
        where "E"."avg_sal"=(select max("avg_sal")from
        (select deptno,avg(sal)"avg_sal"
        from emp
        group by deptno)"E")

/*⑥把工资最低的排除掉，剩下的人中工资最低的

前三个人的姓名、工资、部门编号、名称、工资等级输出*/

--1）排除掉工资最低的人,得到剩下的人

select * 
    from emp
    where sal>(select min(sal) from emp)

--2）把剩下的人按照升序排列，取出前3

select top 3 *
    from (select * 
    from emp
    where sal>(select min(sal) from emp))"T"
    order by "T".sal 

--3)在2）的基础上扩充字段，也就是连接
    select top 3 "T".ename,"T".sal,"T".deptno,"D".dname,"S".grade
    from (select * 
        from emp
        where sal>(select min(sal) from emp))"T"
        join dept"D"
        on "T".deptno="D".deptno
        join salgrade"S"
        on "T".sal between "S".losal and "S".hisal
        order by "T".sal

–内连接复习

select * from emp,dept--笛卡尔积70*11

select * from dept,emp--笛卡尔积70*11，同上

select * from emp,dept where 1=1--笛卡尔积，过滤无效，70*11

select * from emp,dept where empno=7369--5*11，7369对应dept所有行

select * from emp,dept where deptno=10--error，deptno不明确

select * from emp,dept where emp.deptno=10;--行数是5的倍数

select * from emp,dept where dept.deptno=10;--14*11，10部门对应emp的所有记录

--等价于
select * from emp join dept on 2=2
	where dept.deptno=10;

select * from emp join dept on 1=1;
--等价于
select * from emp,dept
--等价于
select * from dept,emp
--等价于
select * from dept join emp on 1=1;

select * from emp,dept where emp.deptno=dept.deptno

select * from emp join dept on emp.deptno=dept.deptno

select * from emp,dept,salgrade 
    where emp.deptno=dept.deptno 
    and emp.sal between salgrade.losal and salgrade.hisal

select * from emp"E"
    join dept"D"
    on "E".deptno="D".deptno
    join salgrade"S"
    on "E".sal between "S".losal and "S".hisal

select * from emp 
    join dept 
    on emp.deptno=dept.deptno and emp.deptno=10

--on中既可以写连接条件，也可以写过滤条件，不推荐
--等价于
select * from emp,dept 
	where emp.deptno=dept.deptno and emp.deptno=10
	
select * from emp join dept  --error，有join必有on

查询顺序：

Select top .. From A
    Join B
    On….
    Join C
    On…
    Where….
    Group by…
    Having…
    Order by…

/*把工资大于1500的所有员工按照部门分组，输出平均

工资大于2000的最高的2个部门的编号、名称、部门平

均工资、工资等级*/

select top 2 "E".deptno,"D".dname,avg("E".sal)"avg_sal","S".grade
from emp"E"
    join dept"D"
    on "E".deptno="D".deptno
    join salgrade"S"
    on "E".sal between "S".losal and "S".hisal
    where "E".sal>1500
    group by "E".deptno
    having avg("E".sal)>2000
    order by avg("E".sal) desc
    --error，dname,grade不是分组后的整体信息

select "T".*,"D".dname,"S".grade
from (select top 2 "E".deptno,avg("E".sal)"avg_sal"
    from emp"E"
    where "E".sal>1500
    group by "E".deptno
    having avg("E".sal)>2000
    order by avg("E".sal) desc)"T"
    join dept"D"
    on "T".deptno="D".deptno
    join salgrade"S"
    on "T"."avg_sal" between "S".losal and "S".hisal

/*先不考虑连接，按照要求得到所需表，
拿这个表和其他表连接，补充所需字段*/

（14）外连接

内连接：

Select * from emp inner join dept on emp.deptno=dept.deptno

外连接：left join /right join

Select * from emp left join dept on emp.deptno=dept.deptno

部分不满足连接条件的也会返回

第一行：用左表的第一行和右表的所有行连接，如果有匹配行，输出匹配行，如果没有匹配的，只输出一行，输出行左边为左表第一行内容，右边全部为null;

第二行。。。。

–内连接

select * from emp join dept
--on 1=1  --70*11
--on 1>2  --0行

–外连接

select * from emp  left join dept
--on 1=1  --70*11
--on 1>2  --14*11

需要理解：

select * from emp
	left join dept
	on emp.deptno=dept.deptno--14行

select * from dept
    left join emp
    on emp.deptno=dept.deptno--16行
    
--等价于
select * from emp
    right join dept
    on emp.deptno=dept.deptno

实际意义：

举例：已知：productstocks货物库存表

Orderform订单表

pID产品编号

把仓库中库存信息及订单信息全部输出

Select productstocks.*, Orderform.*
    From productstocks
    Left join Orderform
    On productstocks. pID= Orderform. pID

意义：

返回仓库中现存货物的信息及货物的订单信息，如该货物没有订单，把该货物的订单信息全部输出null。

（15）完全连接

Inner join=join
Left outer join =left join
Right outer join=right join
Full outer join =full join

Select * from A  full join B on…

结果包含三部分：

1、两个表匹配的所有记录输出；

2、左表中在右表找不到匹配记录，输出一行，右边为null

3、右表中在左表找不到匹配记录，输出一行，左边为null

select * from dept
    full join emp
    on emp.deptno=dept.deptno;
--dept 和emp可互换

（16）交叉连接：产生笛卡尔积

Select * from A  cross join B   (没有0n)

等价于

Select * from A，B

Select * from A join B on 1=1;

（17）自连接

一张表和自己连接起来查询数据

例子：不使用聚合函数的情况下，输出工资最高的员工信息。

① 使用聚合函数

select * from emp
	where sal=(select max(sal)from emp)

② 不使用聚合函数

–①自连接

select * from emp"E1",emp"E2"--196行16列

–②过滤掉了工资最高的人

select distinct "E1".empno
    from emp"E1"
    join emp"E2"
    on "E1".sal<"E2".sal;

–③输出工资最高的人的信息

select * from emp
    where empno not in
    (select distinct "E1".empno
    from emp"E1"
    join emp"E2"
    on "E1".sal<"E2".sal);

（18）联合查询

例：输出每个员工的姓名、工资、上司的姓名

select "E1".ename,"E1".sal,"E2".ename"上司姓名"
    from emp"E1"
    join emp"E2"
    on "E1".mgr="E2".empno
    union 
    select ename,sal,'最大老板'
    from emp
    where mgr is null;

联合查询：

表和表之间的数据以纵向的方式连接在一起；

之前讲的所有连接是以横向的方式连接在一起。

注意：

若干个select 语句要联合在一起需满足：

1．若干个select语句输出的列数必须一致；

2．若干个select语句输出列的数据类型至少是兼容的；

（19）视图

为什么需要视图？

–创建视图:

create view v_emp
	as select deptno,avg(sal)"avg_sal"
	from emp
	group by deptno;

–查看视图:

select * from v_emp

–使用视图:

select * from 
    v_emp
    where avg_sal=(select max(avg_sal)from v_emp)

–删除视图:

drop view v_emp

总结：

① 为什么使用视图：简化查询，避免代码的冗余，避免大量书写重复的什么select语句

② 什么是视图？从代码角度看，视图代表了一个select语句；

从逻辑角度看，视图是一个虚拟表。

③ 格式：create view 视图的名字 as select…（所代表的select语句）

④ 优点：

1）简化查询

2）增加数据的保密性

create view v_emp_2
	as select empno,ename,job,mgr,comm,deptno 
from emp--创建视图时，隐藏了入职日期和工资，增加了数据的保密性

select * from emp--输出8列数据

select * from v_emp_2--输出6列数据，对部分数据做了保密

⑤ 缺点：

1）增加了数据库维护的成本；

视图创建时根据的表，如发生变化，需手动修改视图；

2）视图只是简化查询，并不能加快查询速度。

⑥ 注意

1）创建视图的select语句必须给所有的计算列指定别名；

create view v_emp
    as select deptno,avg(sal)"avg_sal" --字段别名必须有
    from emp
    group by deptno;

2）视图是虚拟表，不是物理表；

3）视图一般只做查询，不建议通过视图更新所依附的原始表。

五、 T-SQL语句

一、基本概念

（1）标识符：数据库对象的名称。例如：数据库、表、列、索引、过程、约束、视图等名称。

分类：

① 常规标识符（常用）

② 分隔标识符””[]

命名规则：

1）首字母必须是英文大小写字母或者_、@、#

2）后续字符：除了第一个字符可用的，还加上十进制数字、$

3）不能使用保留字。Insert、create

4）不允许嵌入空格或者其他特殊字符。

（2）批处理

一个或多个SQL语句，从应用程序一次性发送进行处理。

use scott;

select * from emp;

两条语句一次性发送，编译为一个可执行单元，形成执行计划，每次执行一条。

（3）注释

单行注释——

多行注释/* */

二、常量和变量

1、常量：保持不变的值，表示一个特定的值的符号；

①字符串常量：单引号括起来，包含数字、大小写字母、！@#等特殊符号。（ASCII字符串）

②Unicode字符串：前缀必须是大写的N，后边+字符串常量

③二进制常量：前缀是0X开头，后面是十六进制数字（0-F），不用引号

④bit常量：0，1

⑤datetime常量：‘April 15,2013’

⑥integer常量：没有引号，不包含小数点的数字

⑦decimal常量：没有引号，包含小数点的数字

⑧float和real常量：科学计数法的数字

⑨money常量：前缀一般为$

‘Z&Y*’

0X34567

‘04/15/2013’

954123

1983.04

198.6E3

$1233.06

2、变量：程序运行中可以改变的量。

①局部变量：用户可以自定义的变量。用declare声明，作用范围仅在程序内部，以@开头。

1）声明局部变量

Declare @variable_name datatype ,@…

例：declare @name varchar(10),@age int;

2）赋值：用set或者select语句进行赋值

Set @local_variable =expression

Set一次只能给一个变量赋值

Select @local_variable =expression,@…

Select一次可给多个变量赋值

3）输出

Print msg_srt/@local_variable/string_expr

例：print @name

print @age

不能写成print @name, @age

例：

declare @a int,@b int,@c int--声明变量

set @a=10--给变量赋值
set @b=5

--等价于select @b=5,@a=10，select可以给多个变量赋值
set @c=@a+@b
	print @c--输出变量C的值

②全局变量：系统实现定义好的变量，不允许用户创建或者修改，任何程序可以随时调用。名字以@@开头。

PRINT @@VERSION --查看SQL SEVER版本

PRINT @@CONNECTIONS--服务器上次启动以来已建立的连接数

三、运算符

①算数运算符

+ - * / %（取余）

–例：

declare @a int,@b int,@c int
    set @a=18
    set @b=4
    set @c=@a%@b
    print @c

–输出2，@a+@b输出22，@a/@b输出4

②赋值运算符”=”

把右边的值给左边

1）给变量赋值 set @b=4

2）where子句中提供查询条件

Select * from emp where sal=2000;

③位运算符：在两个表达式之间执行位操作，可用于任意两个整数数据类型的表达式。

&位与逻辑运算：两个都是1，结果为1，否则为0.

|位或逻辑运算：只要有一个是1，结果是1；

^位异或：两个不一样结果是1，否则为0.

11110000

00011110

④比较运算符

除text\ntext\image数据类型表达式不可用，其余均可。

= < > <= >= <> != !< !>

⑤逻辑运算符

对某个条件进行测试，返回的是布尔类型的值，true ,false

And 两个都为真，结果为真

Between 如果操作做数在某个范围内，结果为真

In 等于表达式中的一个，为true

Like 相匹配，结果为true

Not 对结果取反

Or 两个表达式中任何一个为真，结果为真

⑥连接运算符+：字符串连接运算符

declare @name char(20)

set @name=‘啥’

print ‘你说的’+@name

⑦一元运算符

+数值为正，-数值为负，~位反

-3，~（11110000）=00001111

⑧运算符的优先级

四、流控制语句

{}必须写 [] 可选或可写 |两边二选一

①BEGIN……END

将多个语句组合成一个逻辑块。Begin和end必须成对使用

Begin

{sql_statement–两条或者两条以上的语句块

}

End

例：声明两个变量，然后交换数值

declare @x int ,@y int,@t int
    set @x=5
    set @y=10
    begin
    set @t=@x
    set @x=@y
    set @y=@t
    end	--实现两个变量交换数据，形成一个逻辑块，用begin...end括起来
print @x
print @y

②IF……ELSE

如果条件为真，执行条件表达式后边的T-sql语句，为假，则执行ELSE后边的T-sql语句。

IF Boolean expression
{sql_statement| statement_block}
[ELSE{
sql_statement| statement_block}
]

例1：

declare @m int
set @m=0
if @m <3
print 'true'
else 
print 'false'

例2：

declare @m int ,@n int
set @m=3
set @n=7
if @m>@n
print '@m大于@n'
else 
print '@m不大于@n'

例3：–输入一个坐标值，然后判断在哪个象限

declare @x int,@y int
set @x=-3
set @y=-6
begin
if @x>0
 if @y>0
 print '第一象限'
 else
 print '第四象限'
else
 if @y>0
 print'第二象限'
 else
 print'第三象限'
end

③while 循环结构，当条件为真时，可重复执行循环语句。如果循环的时一组命令，需要使用BEGIN…END一起用过。

While Boolean_expression
    Begin
    { sql_statement| statement_block}
   End

例1：–求1-10之间的整数的和

declare @x int, @sum int
    set @x=1
    set @sum=0
    while @x<=10
    begin
    set @sum=@sum+@x
    set @x=@x+1
end

print @sum

例2：1-10之间的偶数相加

declare @x int, @sum int
    set @x=1
    set @sum=0
    while @x<=10
    begin
     if @x%2=0
     begin
     set @sum=@sum+@x
 end
 
set @x=@x+1
end

print @sum

④break和continue

While 子句用break和continue控制while循环语句的执行

语法结构：

While 条件表达式
    Begin
    { sql_statement| statement_block}
    [break]  --下边语句不再执行，跳出循环
    [coninue]-- 下边语句不再执行，回到循环开始的地方
    {sql_statement| statement_block}
End

例：求1-10（包括10）之间偶数的和

declare @x int,@sum int
    set @x=0
    set @sum=0
    while @x<10
    begin
    set @x=@x+1
    if @x%2=0
    set @sum=@sum+@x
    else
    continue
end

print @sum

–输出30

declare @x int,@sum int
    set @x=0
    set @sum=0
    while @x<10
    begin
    set @x=@x+1
    if @x%2=0
    set @sum=@sum+@x
    else
    break
end

print @sum

–输出0

⑤case:根据表达式的真假来确定是否返回某个值，case语句可以进行多个分支的选择。

Case
When Boolean_expression then  result_expression
[….]
[else else_result_expression]
End

例1：

/*根据学生的分数判定等级

[90-100]显示excellent

[70,90）显示good

[60,70)显示pass

其他的显示fail*/

declare @grade int,@message varchar(20)
set @grade=100
set @message=
case 
 when @grade>=90 and @grade<=100 then 'excellent'
 when @grade>=70 and @grade<90 then 'good'
 when @grade>=60 and @grade<70 then 'pass'
 else 'fail'
end

print @message

例2：根据输入的月份判断所归属季度

declare @month int,@num varchar(20)
set @month=13
set @num=
case 
 when @month>=1 and @month<=3 then'第一季度'
 when @month>=4 and @month<=6 then'第二季度'
 when @month>=7 and @month<=9 then'第三季度'
 when @month>=10 and @month<=12 then'第四季度'
 else '超出范围'
end

print @num

⑥waitfor:延迟语句将它之后的语句在一个时间间隔后执行或者在未来的某一指定时间执行。

Waitfor delay ‘time’|time ‘time’

Delay 用于设定等待时间，最多达24小时

Time用于设定结束的时间点

时间的数据类型必须时datetime格式：‘hh:mm:ss’

例：

waitfor delay '00:00:05'
print'好饿呀！'
waitfor time '11:57:00'
print'终于下课了！'

⑦goto用于改变程序执行的流程，使程序跳到标识符指定的程序，再继续执行。

Goto 标识符

例：

–用goto语句输出小于等于3的值

declare @x int
set @x=1
start:
print @x
set @x=@x+1
while @x<=3
goto start

⑧return:无条件退出，从查询或者过程中无条件退出，return之后的语句不再执行。

Return
declare @x int
set @x=3
if @x>0
print '遇到return之前'
return
print '遇到return之后'

⑨print

declare @x char(20)
set @x='天造地设'
print @x
print '最喜爱的歌曲是'+@x

五、存储过程

（1）定义：将一些预先编译的SQL语句集中起来由SQL sever数据库服务器来完成某个任务。

①类似函数；

②用来执行管理任务或应用复杂的业务规则；

③存储过程可以带参数，也可以返回结果。

（2）优点：

1、可以重复使用，减少工作量。

2、运行速度快；

3、主要在服务器上运行，减少客户机压力；

4、运行稳定，错误较少；

（3）分类

①系统存储过程

②自定义存储过程

（4）系统存储过程

系统自带的，存放在master中，类似系统函数，以sp_或者xp_开头

exec sp_helpconstraint 'dept'--查看表的约束

exec sp_databases--查看所有的数据库

exec sp_helpdb  --查看数据库信息

（5）自定义存储过程

①语法结构：

Create proc[edure] 存储过程名

[@参数1 数据类型=默认值 output,

….

@参数n 数据类型=默认值 output]

SQL语句

存储过程可以有参数，也可以没有参数分为输入参数和输出参数（output）

②示例：创建及执行

获取student表的信息

CREATE PROCEDURE getallstudent	
    AS
    BEGIN
        select * from student
    END
GO

执行存储过程：1）界面——选中存储过程，右键-执行存储过程

2）Exec getallstudent

③对于参数的理解

create procedure addstudent
    @stu_id int output,--输出参数，也可作为输入
    @stu_name nvarchar(50)--输入参数，参数名字最好和字段保持一致
    as
    begin
    insert into student values(@stu_id,@stu_name)--执行的时候通过输入参数将值传导到这里
    select @stu_id=max(stu_id) from student--@stu_id在这为输出参数
    end
go

执行

右键-1）执行存储过程-给出参数

2）exec addstudent ‘TOM’,6–按照定义参数时的顺序给出值

④常规操作

1）增加一条记录，如上

2）修改记录

create procedure studentupdate
    @stu_id int,--,不能省略
    @stu_name nvarchar(50)
    as
    begin
    update student set stu_name=@stu_name
    where stu_id=@stu_id
    end
    go

执行：exec studentupdate 6,‘樊鑫’

3）删除记录

create procedure studentdelbyid
    @stu_id int
    as
    begin
    delete student where stu_id=@stu_id
    end
    go

执行：

exec studentdelbyid 5

4）查询记录-查询emp表的所有信息

create procedure getallemp
    as
    begin
    select * from emp
    end

执行：

exec getallemp

5）连接查询记录-查询员工姓名和部门名称

create procedure getenamedname
    as
    begin
    select emp.ename,dept.dname
    from emp
    join dept
    on emp.deptno=dept.deptno
    end

执行：

exec getenamedname

6）存储过程和视图的结合

create procedure getenamefromview
    @empno int
    as
    begin
    select ename from v_emp
    where empno=@empno
    end

执行：

exec getenamefromview 7369

⑤创建通用存储过程

1）通用简单查询：查询某个表的所有信息

create PROCEDURE sp_getdatabytablename
	@tablename nvarchar(100)
AS
BEGIN
	declare @sql nvarchar(500)
	set @sql='select * from '+@tablename
exec(@sql)
END

执行：

exec sp_getdatabytablename 'student2'

2）稍微复杂查询：查询某个表的指定字段

alter PROCEDURE sp_getcolumnsbytablename2
	@tablename nvarchar(100),
	@columns nvarchar(100)
AS
BEGIN
	declare @sql nvarchar(500)
	set @sql='select '+ @columns+ ' from '+@tablename
exec(@sql)
END

执行：

exec sp_getcolumnsbytablename2 'student2','*'

3）再稍微复杂查询

create PROCEDURE sp_getcolumnsbyconditions
	@tablename nvarchar(100),
	@columns nvarchar(100),
	@conditions nvarchar(100)
AS
BEGIN
	declare @sql nvarchar(500)
	set @sql='select '+ @columns+ ' from '+@tablename
	+' where 1=1 '+@conditions
exec(@sql)
END

执行：

exec sp_getcolumnsbyconditions 'student2','*',
    'and stu_id=1000'
    exec sp_getcolumnsbyconditions 'student2','*',
    ''
    exec sp_getcolumnsbyconditions 'student2','stu_name',
    ''
    exec sp_getcolumnsbyconditions 'student2','stu_name',
    'and stu_id=1000'

4）通用删除

CREATE PROCEDURE sp_deletedatabycondition
	@tablename nvarchar(100),
	@condition nvarchar(200)
AS
BEGIN
	declare @sql nvarchar(500)
	set @sql='delete from '+@tablename+' where '+@condition
exec(@sql)
END
执行：exec sp_deletedatabycondition 'student','stu_id=3'
exec sp_deletedatabycondition 'student2','stu_id=1'
--务必注意空格

5）通用修改

create PROCEDURE sp_updatetablebycondition
	@tablename nvarchar(100),
	@condition nvarchar(200),
	@columns nvarchar(200)
AS
BEGIN
	declare @sql nvarchar(500)
	set @sql='update '+@tablename+' set '+@columns
	+' where '+@condition
exec(@sql)
END

执行：exec sp_updatetablebycondition 'student','stu_id=1','stu_name=''TOM'''

补充：

六、索引

引入：

商场、图书馆找图书、火车站检索车次

图书馆示例：

①图书分类，同类图书放到一个架子上；

②给图书编号：类别号，书架号、层号，书号

③制成卡片放到卡片柜里—卡片就是索引

**特点：**数据变，索引也变——索引要按需使用，几条数据不使用索引

使用索引的时机：

①数据量越大越合适

②数据选择性越好越合适-筛选出的数据越少越好，where不能有太多and

③数据表的变化越小越适合

分类：

**聚集索引：**不通过表中记录地址定位到表，本身就包含表，表中数据按照索引字段进行排序。每张表最多有一个聚集索引。主键为聚集索引。

如果创建主键前，已存在聚集索引，那么主键为非聚集索引，否则，创建的主键为聚集索引。

**非聚集索引：**索引另外形成一张表，把指定列提到索引表。

创建：

右键-创建新索引-聚集性索引/非聚集性索引-选择列

Create [unique][clustered][noclusterered]INDEX

索引名 on 表或视图名（列） where子句

创建索引是为了让数据库查询数据的时候加快速度。

怎样查看某些语句是否使用了索引？

估算，不实际执行

执行完给出是否使用

查找或者seek——使用了索引

扫面或者scan——没有使用索引

只有把包含索引的列用于where条件时，才会使用该索引。

触发器

**定义：**一种特殊的存储过程。

存储过程通过存储过程名字被调用，触发器是通过事件触发而被执行。

触发器是对某一个表的一定操作，触发某种条件，从而执行一段程序。

分类：

DML触发器：

insert、delete**、**update、after 触发器、instead of触发器、before触发器

DDL触发器：

Create table、create database、alter、drop

两个表
在这里插入图片描述

After 触发器：操作执行完从而触发其他操作

Instead of 触发器执行前就替换为其他操作

create trigger tri_delete_student on student
    after delete
    as
    begin
    insert into studentbackup (stu_id,stu_name)
    select stu_id,stu_name from deleted
    end--创建触发器，埋雷

delete from student where stu_id=2--踩雷

select * from studentbackup--查看踩雷后果

语法：

create trigger 触发器名字 on 表名
    after（for）|instead of  delete|update|insert|( delete,update,insert)
    as
    begin
    …
    end

qjaylee

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

数据库原理与应用

数据库原理与应用

文章目录

一、概念部分

1、数据库

2、数据库地位

3、大数据

4、云计算

5、数据库技术发展

6、数据库管理系统

7、数据库三种模式

8、数据模型的三要素：

9、创建数据库阶段：

10、实体、实体集、属性、类型与值、实体之间的联系

11、关系型数据库

12、关系

13、约束

14、关系运算

15、创建数据库阶段

二、数据库的创建及维护

1、创建数据库

2、数据库的分离和附加

3、数据库的删除

4、数据库的修改

三、 数据表的创建及操作

1、数据类型

2、新建表

①可视化操作：

②语句操作：

③ identity自动增长

④外键设置

⑤check约束

⑥Default约束

⑦Unique约束

3、修改表结构

① 更改表名：

② 更改字段名字：

③添加字段：

④删除字段：

⑤修改字段类型：

⑥修改字段是否为空：

⑦添加/删除主键：

⑧添加/删除CHECK约束：

⑨添加/删除DEFAULT约束

⑩添加/删除unique约束：

⑪添加/删除外键

4、删除表

四、 数据表记录的相关操作

1、新增表记录

2、修改表记录

3、删除表记录

4、查询表记录

（1）计算列

（2）distinct

（3）between

（4）in

（5）top的用法

（6）null

（7）order by

（8）模糊查询

（9）聚合函数

（10）group by

（11）having

（12）内连接

（13）习题

（14）外连接

（15）完全连接

（16）交叉连接：产生笛卡尔积

（17）自连接

（18）联合查询

（19）视图

五、 T-SQL语句

一、基本概念

二、常量和变量

三、运算符

四、流控制语句

五、存储过程

六、索引

三、数据表的创建及操作

四、数据表记录的相关操作