郝斌老师 sql 语句笔记

2023-12-28 00:07:43
一、数据库是如何解决数据存储问题的
1
从三个方面来学习数据库
a) 数据库是如何存储的
i.
字段,记录,表,约束 ( 主键,外键,唯一键,非空, check, default, 触发器 )
b) 数据库是如何操作数据的
i.
Insert, update, delete, T-SQL, 存储过程,函数, 触发器
c) 数据库是如何显示数据的
i.
select( 重点中的重点 )
必备的一些操作
d) 如何建数据库
i.
.mdf 是数据文件
.LDF 是日志文件
ii.
右键 -> 新建数据库 -> 输入名字 -> 确定
e) 如何删除一个库;
如何附加和分离数据库
f) 设置登录用户名和密码;
建用户
用鼠标建立的第一个数据库 Test
g) nvarchar()
国际化编码的可变的字符串。 n 表示国际化,对汉字也支持; var
变量,字符的长度是可变化的
h)
,其中的 dbo 表示一个系统的身份
i) update emp set emp_id = 1001
j)
存在外键的表叫外键表
k) 当要选择是否可以为空时,用空格键来进行控制,即: 可以用空格来选择
l) PK primary 主要的;
FK, foreign 外部的
通过 sql 命令建表和主外键约束
m) sql server 里最后一句写不写逗号都可以,但是在 oracle 里不可以写逗号,因此
在写命令时,最后一个字段的后面就不要写逗号了
n) create table dept
(
dept_id int primary key,
-- 在此处设置了主键
dept_name nvarchar(100) not null ,
-- 设置了非空
dept_address nvarchar(100)
)
create table emp
(
-- 这个是注释
emp_id int constraint PK_emp_id_haah primary key, -- 此处给主键起了
-- 一个名字,红字部分可以不写
emp_name nvarchar(20) not null,
emp_sex nchar(1),
dept_id int constraint FK_dept_id_hd foreign key references dept(dept_id) 2
-- 在此处建立了一个外键,使用了 references
)
约束
o) 约束的分类
i.
主键约束
不允许重复元素,避免了数据的冗余(实体完整性)
ii.
外键约束
通过外键约束从语法上保证了本事物所关联的其他事物一定是
存在的(引用完整性)
事物和事物之间的关系是通过外键来体现的
p) check 约束
i.
create table student
{
stu_id int primary key,
stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000)
)
在此处使用了 check 约束,限定了 stu_sal 的值在 1000 8000 之间
ii.
保证事物属性的取值在合法的范围之内
q) default 约束
i.
数据库中的 单引号用来标识一个字符串,双引号用来模拟一个数据的名字
ii.
保证事物的属性一定有一个值
iii.
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 (1, 1000)
insert into student2 values (2, 5000, ' ')
--insert into student2 values (3, 10000) 这样写是不对的
在此处,声明了三个属性,在第一个命令中,指定了二个属性,所以填入了两
个值;第二个命令中,没有指定要填写几个属性,所以要填写全部的三个值;
第三个命令中,没有指定要填写几个属性,不过只写了两个值,这是不对的
r) unique 约束(唯一约束)
i.
保证了事物的取值不允许重复,
ii.
create table student5
(
stu_id int primary key,
stu_sal int check (stu_sal >= 1000 and stu_sal <= 8000),
stu_sex nchar(1) default' ',
stu_name nvarchar(200) unique
)
insert into student5 values (1, 6000, ' ', ' 张三 ' ) -- 此句话正确
insert into student5 values (2, 6000, ' ', ' 张三 ' ) -- 违反了唯一键约束,不正确
insert into student5 values (null, 6000, ' ', ' 王五 ')
-- 主键不能为空,不正确 insert into student5 values (3, 6000, '
3
', null )
-- 唯一键可以为空
iii.
unique 键是否允许多列为空?
1. Sql Server 2012 只允许一个 unique 列为空
2. Oracle11G 允许多个 unique 列为空
s) 主键和唯一键区别及其两者配合使用举例
i.
不要用业务逻辑作主键(编号不是一个事物的属性)
ii.
把编号(代理主键)当主键,
iii.
create table student6
(
stu_id int primary key identity ,
stu_name nvarchar(50) not null unique,
stu_email nvarchar(50) not null,
stu_address nvarchar(50)
)
drop table student6
-- 这一句是删除一个表
在此处, identity 是自增的意思;设定了一个编号当作主键,把用户名作
为唯一键;通常不会删除一个用户,一般每个用户都有一个标识位, 1 或者 0
如果注消一个用户的话可以把他的标识位改一个,就不能使用此帐户了
t) not null (可以当作一个约束)和 default 的区别
i.
not null 要求用户必须为该属性赋一个值,否则语法出错
ii.
如果有默认值,如果不指定要为哪一个属性赋值的话,必须为全部属性赋值
iii.
如果指定了为哪些属性赋值,有一个属性没在括号内且可以为空的话,这个值
就是 NULL ;如果指定了为哪些属性赋值,有一个属性没在括号内且有默认值,
那么这个值就是默认值
表和约束的区别(他自己的理解)
u) 数据库是通过表来解决事物的存储问题的
v) 数据库是通过约束来解决事物取值的有效性和合法性的问题
w) 建表的过程就是指定事物属性及其事物属性各种约束的过程
什么是关系
x) 定义:表和表之间的联系
y) 实现方式:通过设置不同形式的外键来体现表和表的不同关系
z) 分类:一对一,一对多,多对多
aa) 一对一:
i.
可以把表 A 的主键充当表 B 的外键,也可以把表 B 的主键当充当表 A 的主键
bb) 一对多:
i.
在多的一方添加外键
cc) 多对多:
i.
任何一个表都有一个主键,但不一定都有外键
ii.
多对多的关系需要另外一张表来表示(通常至少有两列)
iii.
-- 班级表
create table banji
( 4
banji_id int primary key,
banji_num int not null,
banji_name nvarchar(100)
)
-- 教师表
create table jiaoshi
(
jiaoshi_id int primary key,
jiaoshi_name nvarchar(200)
)
-- 第三张表 用来模拟班级和教师的关系
create table banji_jiaoshi_mapping
(
banji_id int constraint fk_banji_id foreign key references banji(banji_id) ,
jiaoshi_id int foreign key references jiaoshi(jiaoshi_id) ,
kecheng nvarchar(20),
constraint pk_banji_id_jiao_id primary key(banji_id, jiaoshi_id, kecheng)
)
-- 删除表
drop table banji_jiaoshi_mapping
在第三张表里面, constraint fk_banji_id banji_id 这个外键设置了一个别
名; foreign key references banji(banji_id) 这句话,设置了一个外键,其中“一”
的一方是 banji 里的 banji_id ;下面一条命令同样设置了一外键,不过是省略了
外 键 的 名 字 ; 最 后 一 条 命 令 设 置 了 一 个 联 合 主 键 , 设 其 名 字 为
pk_banji_id_jiao_id ,只能用这种方法来设置联合主键,不能一个一个地单独写
主键;在第三张表里面,删除了这个关系表
dd) 数据库关系图的使用
i.
在一个库里右击数据库关系图,新建数据库关系图,选中要选择的表
ii.
在删除一个关系时,不会把属性同时删除
iii.
要放大和缩小关系图, Ctrl+ 鼠标滚轮
iv.
利用
可以快速改变视图的位置
主键的具体定义及注意事项
ee) 主键定义:能够唯一标识一个事物的一个字段或者多个字段的组合
ff) 主键特征:
i.
含有主键的表叫做主键表
ii.
主键通常都是整数,不建议使用字符串当主键(如果主键是用于集群式服务,
才可以考虑用字符串当主键)
iii.
主键的值通常都不允许修改,除非本记录被删除
iv.
主键不要定义成 id ,而要定义成“表名 id ”或者“表名 _id
v.
要用代理主键,不要用业务主键
1. 任何一张表,强烈建议不要使用有业务含义的字段充当主键
2. 我们通常都是在表中单独添加一个整型的编号充当主键字段 5
外键的具体定义及要注意的问题
定义:如果一个表中的若干个字段是来自另外若干个表的主键或唯一键,则这若干
个字段就是外键
注意:
vi.
外键通常是来自另外表的主键而不是唯一键,因为唯一键可能为 null
vii.
外键不一定是来自另外的表,也可能来自本表的主键
viii.
含有外键的表叫外键表,外键字段来自的那一张表叫做主键表
gg) 问题:先删主键表还是外键表?
先删外键表
二、查询(顺序)
计算列(scott 库)
? select * from emp;
? select empno, ename from emp;
? select ename, sal*12 as "年薪" from emp;
? 这里所写的 sal*12 是指把一个月的工资乘以 12 之后的结果; as 在这里写不写
都可以;年薪这里要写的是 双引号 ,不要写其他的符号,值是 5
? select 888 from emp;
? 这里写 888 ,查询出来的没有列名,输出的行数是 emp 表的行,每行只有一
个字段
? select 5;
? 这里写的 5 ,查询出来的是没有列名,只有一个记录(不推荐这样写)
? Oracle 里不能通过
? 注意:在 Oracle 中字段的别名不允许用单引号括起来,但是 SqlServer2012 却允许,
因此为了兼容性,最好 将字段的别名用双引号括起来
distinct(不允许重复的)的用法
? select distinct deptno from emp; 这里, distinct deptno 会过滤掉重复的 deptno
? select distinct comm from emp; distinct 也可以过滤掉重复的 null ,或者说如果有多
null ,只输出一个
? select distinct comm, deptno from emp;
comm deptnor 的组合进行过滤
? select deptno, distinct comm from emp;
逻辑上有冲突
? select ename, * from emp; 这个在 SqlServer 里正确,在 Oracle 里不正确
between(在某个范围)的用法
? select * from emp;
在这里,先执行 from emp ,知道要在哪个表里查找,然后
执行 select * ,知道要找哪些数据
? 查找工资在 1500 3000 之间(包括 1500 3000 )的所有的员工的信息
? select * from emp where sal >= 1500 and sal <= 3000 ;
等价于:
? select * from emp where sal between 1500 and 3000 ; ?
6
查找工资小于 1500 或大于 3000 的所有的员工的信息
? select * from emp where sal < 1500 or sal > 3000;
等价于:
? select * from emp where sal not between 1500 and 3000
in(属于若干个孤立的值)
? select * from emp where sal in (1500, 3000) ;
emp 表中取出 sal 1500 3000
的所有记录,等价于:
? select * from emp where sal = 1500 or sal = 3000 ;
? select * from emp where sal not in (1500, 3000) ; emp 表中取出 sal 既不是 1500
不是 3000 的记录,等价于:
? select * from emp where sal <> 1500 and sal <> 3000 ;
? 在数据库中不等于有两种表示方式: != <> 推荐使用第二种
? 对或取反是并且,对并且取反是或
top(最前面的若干个记录,专属于 SqlServer 的语法,不可移
植到其他数据库中)
? select top 2 * from emp;
选出 emp 表中的前两个记录
? select top 15 percent * from emp;
输出 emp 表中的前 15% 个,如果不是整数的
话,就进位。如, 14 * 15% = 2.1 ,则输出 3 个值
? select top 2 from emp;
这里没有 * 会出错
? 把工资在 1500 3000 之间(包括 1500 3000 )的员工中工资最高的 4 个人的
信息输出
? select top 5 * from emp where sal between 1500 and 3000 order by sal desc ;
? 其中 desc 是降序的意思,默认情况下是升序
null(没有值 空值)
? 零和 null 是不一样的, null 表示空值,没有值;零表示一个确定的值
? null 不能参与如下运算: <>
!= =
? null 可以参与如下运算: is
is not
? 例如:
? select * from emp where comm <> null;
-- 错误
? select * from emp where comm != null;
-- 错误
? select * from emp where comm = null;
-- 错误
? select * from emp where comm is null ;
-- 输出奖金为空的员工的信息
? select * from emp where comm is not null ; -- 输出奖金不为空的员工的信息
? 任何类型的数据都允许为 null , 如:
? create table t1(name nvarchar(20), cnt int , riqi datetime),
insert into t1 values (null, null, null);
? 任何数字与 null 参与数学运算的结果永远是 null ,例如:
? 输出第个员工的姓名年薪(包含了资金), comm 假设是一年的资金
select empno, ename, sal * 12 +comm “ 年薪 ” from emp;
如果有 null 参与运算的话,结果全部为 null
? 正确的写法是: ?
7
select ename, sal * 12 + isnull(comm, 0) “ 年薪 ” from emp ;
? isnull(comm, 0) 表示:如果 comm null ,就返回零否则返回 comm 的值
order by 的用法(以某个字段排序)
? 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 表示 ,默认可以省略不写,
降序用 desc 表示
? 为一个字段指定的排序标准并不会对另一个字段产生影响
? 强烈建议为第一个字段都指定排序的标准
? 例子:
? select * from emp order by sal; 如果不加什么排序标准,默认是按照升序排列
? select * from emp order by deptno, sal ;
先 按 照 deptno 排 序 , 如 果
deptno 相同,则再按照 sal 来排序,默认都是升序
? select * from emp order by deptno desc, sal ;
这里 deptno 里面写了 desc ,则
deptno 按照降序来排列;如果 deptno 相同,后面的 sal 没有写排序类型,则
sal 按照升序来排序( Oracle 也是这样)
? select * from emp order by deptno, sal desc;
与上相同,先按 deptno 升序,
如果 deptno 相同,则再按 sal 的降序来排列;不会对 deptno 产生影响
模糊查询[搜索时经常使用]
? 格式: select 字段的集合 from 表名 where 某个字段的名字 like 匹配条件
? 匹配的条件通常含有 通配符
? % (百分号)表示任意 0 多个 字符
? select * from emp where ename like '%A%' ; --ename 只要含有 A 就输出
? select * from emp where ename like 'A%'' ; --ename 只要首字母为 A 就输出
? _ (下划线)表示任意 单个字符
? select * from emp where ename like '_A%'; --ename 第二个字母是 A 就输出
? [a-f] a f 中的任意 单个字符 ,只能是 a, b, c, d, e, f 中的任意一个字符
? select * from emp where ename like '_[A-F]%';-- ename 中第二个字母是
A, B, C, D, E, F 中的任意一个字符的记录输出
? [a, f] a f
? [^a-c] 不是 a ,也不是 b ,也不是 c 的记录
? select * from emp where ename like '_[^A-C]%';-- ename 中第二个字母不
A ,不是 B ,也不是 C 的记录输出
? 注意:匹配的条件必须用单引号括起来,不能省略,也不能改用双引号
? 单引号:字符串
双引号:对象的名字 ( 自己的理解 )
? 模糊查询中通配符作为普通字符使用的问题
? select * from student where name like ‘%\_%’ escape ‘\’
? 这表示把所有记录中 name 列包含下划线 ‘_’ 字符的记录输出
? escape ‘\’ 表示把 ‘\’ 字符当做转义字符的标志 ?
8
sql 中可以把任意的字符当做转义字符的标志,具体是把哪个字符当做转义
字符,这是由 escape ‘ 要制定为转义字符的字符 来决定的
聚合函数[多行记录返回一个值,通常用于统计分组的信息]
? 函数的分类:
? 单行函数, 每一行返回一个值
? 多行函数, 多行返回记录一个值 ;聚合函数是多行函数
? 例子: select lower(ename) from emp;
-- 最终返回的是 14 lower() 是单行
函数( lower() 是将大写的转换成小写的; upper() 将小写的转换成大写的)
select max(sal) from emp;
返回行 max() 是多行函数
?
聚合函数的分类: (当聚合函数作用某个字段时,会忽略所有的 NULL 行)
? 常用的聚合函数:
? AVG(ALL|DISTINCT expression) 数字表达式中所有值的平均值
? SUM(ALL|DISTINCT expression) 表达式中所有值的平均值
? COUNT(ALL|DISTINCT expression) 表达式中值的个数
? COUNT(*)
选定的行数
? MAX(expression)
表达式中的最高值
? MIN(expression)
表达式中的最低值
? max()
适用于数值型,字符型和日期型的列,对于列值为 NULL 的列, MAX
不将其列为对比的对象
? min()
avg() (平均值)
? count() (求个数)
? count(*) -- 返回 emp 表所有记录的个数
a) select count(*) from emp;
-- 返回 emp 表所有记录的个数
? count ( 字段名 )
-- 返回字段值非空的记录的个数,重复的记录也会被当
做有效的记录
a) select count(deptno) from emp; -- 返回值是 14 ,说明 deptno 重复的
记录也被当做有效值
b) select count(comm) from emp;
-- 返回值是 4 ,说明 comm null
记录不会被当做有效的记录
? count (distinct 字段名 )
-- 返回字段不重复并且非空的记录的个数
a) select count(distinct deptno) from emp;
-- 返回值是 3
统计 deptno
中不重复的个数
b) select count (distinct comm) from emp;
-- 返回值是 4 ,这说明 comm
null 的记录不会被当做有效的记录
? 注意的问题:
? select max(sal) " 最高工资 ", min(sal) " 最低工资 ", count(*) " 员工人数 " from emp;
-- 这样写没有冲突
? select max(sal), lower(sal) from emp; -- 错误,单行函数和多行函数不能混用
grout by 用法[ 分组统计查询 ][重难点]
? 从一个数据库切换到另一个数据库,可以使用 use scott;
? 格式: group by 字段的集合
? 功能:把表中的记录按照字段分成不同的组 ?
9
例子:
? 查询不同部门的平均工资
select deptno, avg(sal) as “ 部门平均工资
from emp
group by deptno
? 理解: group by a, b, c 的用法
? 先按 a 分组, 如果 a 相同,再按 b 分组;如果 b 相同, 再按 c 分组;最终统
计的是最小分组的信息
? 下面几行语句是错误的:
? select deptno, avg(sal) as” 部门平均工资 ”, ename
from emp
group by deptno
在这里按照部门 deptno 来分组, 只能写组的信息,不能写组内部的信息。这
样写部门分组只有三组,名字却有 14 个,所以不行
? select deptno, ename
from emp
group by deptno
? select deptno, job, sal
from emp
group by deptno, job
? 使用了 group by 之后 select 只能出现分组后每组的整体信息,不能出现组内
的详细信息
使用 group by select 后面的字段只能写可以描述每条记录属性的字段
use scott ;
select *
from emp
-- 输出每个部门的编号和该部门的平均工资
select deptno as " 部门编号 " , avg ( sal ) as " 部门平均工资 "
from emp
group by deptno -- 按部门编号分组 , 统计分组每组整体信息
select deptno , ename --error 不能写组内部信息 ,ename 不能写
from emp
group by deptno
select deptno" 部门编号 " , job" 职称 "
from emp
group by deptno , job -- 先按部门编号分组,部门编号相同,按职称分组
select deptno" 部门号 " , job" 职称 " , avg ( sal ) " 同一部门相同职位的平均工资 "
from emp
group by deptno , job --
10
对上面进行排序输出
select deptno" 部门编号 " , job" 职称 " , avg ( sal ) " 同一部门相同职称的平均工资 "
from emp
group by deptno , job
order by deptno desc , avg ( sal )
--select 后面的字段只能写可以描述每条记录属性的字段
select deptno" 部门编号 " , job" 职称 " , count (*) " 同一部门相同职称员工数 "
from emp
group by deptno , job
order by deptno desc , avg ( sal )
select deptno , avg ( sal ), count (*) --count(*) 为相同组的员工人数
from emp
group by deptno
order by deptno
谨记:使用 group by select 后面的字段只能写可以描述每条记
录属性的字段
? 出现在 SELECT 列表中的字段,如果不是包含在分组函数中,那么该字段必须同时
GROUP BY 子句中出现。但是在 GROUP BY 子句中的字段则不必须出现在 SELECT
列表中 ,例如:
? select avg(sal)
from emp
group by deptno;
--OK
? select deptno, avg(sal)
from emp
group by deptno; --OK
? select ename, avg(sal)
from emp
group by deptno --error 一定要明白该句为什么是错误的
having( 用来对分组之后的信息进行过滤
? having 子句是用来对分组之后的数据进行过滤,
因此使用 having 时通常都会先使用 group by
? 如果没有使用 group by 但使用了 having ,则意味着把所有的记录当做一组来进行
过滤。一般很少这样用 select count(*) from emp having avg(sal) > 1000;
? having 子句出现的字段必须得是分组之后的 组的整体信息, having 不允许出现
内的详细的信息
? 尽管 select 字段中可以出现别名,但是 having 子句中不能出现字段的别名 ,只能
使用字段最原始的名字
? having where 的异同
? 相同点:
? 都是对数据过滤,只保留有效的数据 ?
11
where having 一样, 都不允许出现字段的别名 ,只允许出现最原始的字
段名字,在 SqlServer2012 Oracle 中都成立
? 不同点:
? where 是对原始的记录过滤, having 是对分组之后的记录过滤
? where 必须得写在 having 的前面,顺序不可颠倒,否则运行出错
? where 是对 原始的数据 过滤 ,不能使用聚合函数, 因为还没有分组
? 例子:
? select deptno, avg(sal)
from emp
where sal > 2000
group by deptno
having avg(sal) > 3000; (正确)
? select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 3000;
where sal > 2000; (不正确)
--------------------------------------------
-- having-- 对分组后进行过滤
use scott ;
select * from emp ;
-- 输出部门平均工资大于的部门的编号 , 以及该部门的平均工资
select deptno , avg ( sal )
from emp
group by deptno
having avg ( sal )> 2000 ; -- 对分组后的记录过滤
--------------------------------------------
select deptno" 编号大于的部门 " , avg ( sal ) " 该部门的平均工资 "
from emp
group by deptno
having deptno > 10 ; -- 对分组后的记录过滤
--------------------------------------------
select deptno , ename
from emp
group by deptno , ename
order by deptno
--------------------------------------------
select deptno , avg ( sal ), count (*) --count(*) 为相同组的员工人数
from emp
group by deptno 12
order by deptno
--------------------------------------------
select deptno , avg ( sal )
from emp
group by deptno
having count (*)>= 5 -- 对分组后的记录过滤
--------------------------------------------
--having where 的区别
-- 把姓名不包含 A 的所有员工按部门编号分组
-- 统计输出平均工资大于的部门的编号以及该部门的平均工资
select deptno" 部门编号 " , ename" 名字不含 A 的员工 "
from emp
where ename not like '%A%' -- 对原始记录过滤 -- 排除部门中名字中含 A 的员工
group by deptno , ename
--------------------------------------------
select deptno" 部门编号 " , ename" 名字不含 A 的员工 " , sal
from emp
where ename not like '%A%' -- 对原始记录过滤 -- 排除部门中名字中含 A 的员工
group by deptno , ename , sal
having sal > 1500 -- 对分组后的记录过滤
--------------------------------------------
select deptno" 部门编号 " , count (*) " 部门人数 " , avg ( sal ) " 部门平均工资 " ,
min ( sal ) " 最少工资 " , max ( sal ) " 最多工资 "
from emp
where ename not like '%A%' -- 排除部门中名字中含 A 的员工
group by deptno
--------------------------------------------
select deptno" 部门编号 " , count (*) " 部门人数 " , avg ( sal ) " 部门平均工资 " ,
min ( sal ) " 最少工资 " , max ( sal ) " 最多工资 "
from emp
where ename not like '%A%' -- 排除部门中名字中含 A 的员工
group by deptno
having avg ( sal )> 2000 -- 对分组后的记录过滤
-------------------------------------------- 13
? 总结:
select list
into new_table_name -- 把查询结果放入新表
from tablename
where search conditions
group by list
having search conditions
order by list
union 运算符可以合并多个查询结果
连接查询( 多表查询 )
? 定义: 将两个或者两个以上的表以一定的连接条件连接起来,
从中检索出满足条件的数据
分类: 内连接,外连接,完全连接,交叉连接,自连接,联合
? 内连接(重点,但不难)
? select …from A, B 的用法
? 产生的结果:行数是 A B 的乘积,列数是 A B 之和;或者说:把 A
(或 B )表的每一条记录都和 B (或 A )表的每一条记录组合在一起,形
成的是个 笛卡尔积
? 注意: select * from A, B 输出结果和 select * from B, A 的结果是一样的
? 例子: 14
--------------------------------------------
--emp 14 行列 dept 5 行列
select * from emp , dept -- 输出行列 70=14×5 11=8+3
---------------------------------------------
? select …from A, B where … 的用法
? select … from A, B 产生的笛卡尔积用 where 中的条件进行过滤 A B
互换输出结果一样)
? 例子:
--------------------------------------------
select *
from emp , dept
where empno = 7369 -- 输出行列 5=1×11=8+3
--------------------------------------------
这条语句可以理解成:将 emp 中的 empno 7369 的记录分别与 dept
中的每一条记录相连,其中 dept 表中只有 5 行,所以结果只有 5 行记录
? select …from A join B on … 的用法 ( join, 必须有 on)
? A B 互换输出结果不变
? on 中是连接条件
--------------------------------------------
select *
from emp "E" -- emp 表起个别名 E
join dept "D" -- emp 表和 dept 表连接起来
on 1 = 1 --on 表示连接条件“真”
输出行列 -- 因为连接条件永远成立就和没有一样了
---------------------------------------------
select "E" . ename " 员工姓名 " , "D" . dname " 部门姓名 "
from emp "E"
join dept "D"
on 1 = 1 ; -- 输出行列
---------------------------------------------
select deptno -- 俩张表中都有 deptno error
from emp "E"
join dept "D"
on 1 = 1 ;
----------------------------------------------
select "E" . deptno --ok
from emp "E"
join dept "D"
on 1 = 1 ; -- 输出 70 1
----------------------------------------------- --
15
这样俩张表中相同部门就对着连接起来了
select *
from emp "E"
推荐使用
join dept "D"
on "E" . deptno = "D" . deptno -- 输出 14 11
-------------------------------------------------------------------------------------------
-- 与上面输出结果一样
select *
from emp , dept
where emp . deptno = dept . deptno -- 输出行列
------------------------------------------------------------------------------------------
? select …from A, B where … select …from A join B on … 的比较
--------------------------------------------------
-- 把工资大于的员工的姓名和部门的编号 , 部门名称输出
select "E" . ename , "D" . deptno , "D" . dname , "E" . sal
from emp "E" , dept "D"
where "E" . sal > 2000 and "E" . deptno = "D" . deptno
select "E" . ename , "E" . deptno , "D" . dname , "E" . sal
from emp "E"
join dept "D"
on "E" . deptno = "D" . deptno
where "E" . sal > 2000
---------------------------------------------------
-- 把工资大于的员工的姓名 , 部门的名称和工资的等级输出
-- 涉及 3 个表 emp dept SALGRADE
select *
from emp "E"
join dept "D"
on "E" . deptno = "D" . deptno --emp dept 成一张临时表了
join SALGRADE "G" -- 与第三张表连接
on "E" . sal >= "G" . LOSAL and "E" . sal <= "G" . HISAL -- 三表
成一表
select "E" . ename , "D" . deptno , "E" . sal , "G" . GRADE
from emp "E"
join dept "D"
on "E" . deptno = "D" . deptno
join SALGRADE "G"
on "E" . sal >= "G" . LOSAL and "E" . sal <= "G" . HISAL
where "E" . sal > 2000
---------------------------------------------------
select *
from emp "E" , dept "D" , SALGRADE "G" -- 迪卡尔积 16
where "E" . deptno = "D" . deptno and
( "E" . sal >= "G" . LOSAL and "E" . sal <= "G" . HISAL ) and
"E" . sal > 2000
---------------------------------------------------
- - 输出姓名不包含 A 的工资前三名的员工的姓名工资工资等级部门名称
select top 3 "E" . ename , "E" . sal , "G" . GRADE , "D" . dname
from emp "E"
join dept "D"
on "E" . deptno = "D" . deptno
join SALGRADE "G"
on "E" . sal between "G" . LOSAL and "G" . HISAL
where "E" . ename not like '%A%' -- 只能写这
order by "E" . sal desc
? select from where join on group by order by top
having 的混合使用 (having 里面只能有原始字段的名字 )
? 查询的顺序:
select top ...
from A
join B
on ...
join C
on ...
where ...
group by ...
having ...
order by...
? 例子: -- 把工资大于 1500 的所有的员工按部门分组 把部门平均工资大于
2000 的最高前 2 个的部门的编号 部门的名称 部门平均工资的等级
------------------------------------------------
select top 2 "E" . deptno , avg ( sal ) "avg_sal"
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
------------------------------------------------
Select "D" . deptno , "D" . dname , "S" . grade" 部平工资等级 "
from dept"D"
join(
select top 2 "E" . deptno , avg ( sal ) "avg_sal" 17
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
) "T"
on "D" . deptno = "T" . deptno
join salgrade "S"
on "T" . avg_sal between "S" . losal and "S" . hisal
---------------------------------------------
select "T" .*, "D" . dname , "S" . grade
from dept "D"
inner join (
select top 2 deptno , avg ( sal ) as
"avg_sal"
from emp
where sal > 1500
group by deptno
having avg ( sal ) > 2000
order by "avg_sal" desc
) "T"
on "D" . deptno = "T" . deptno
inner join salgrade "S"
on "T" . "avg_sal" between "S" . losal and
"S" . hisal
---------------------------------------------
? 习题:
? 判断以下语句输出是几行:
a) select * from emp, dept where emp.deptno = 10
--15
b) select * from emp, dept where dept.deptno = 10
--14 行这里是过
-- 滤条件不是连接条件
如何把 select * from emp, dept where dept.deptno = 10 sql99 标准来完成
select * from emp join dept on 1 = 1 where dept. deptno = 10;
? 求出每个员工的姓名、部门编号、薪水和薪水的等级
----------------------------------------------
select "E" . ename , "E" . deptno , "E" . sal , "G" . GRADE
from emp "E"
join SALGRADE "G"
on "E" . sal between "G" . LOSAL and "G" . HISAL ----------------------------------------------
18
? 查找每个部门的编号、该部门所有员工的平均工资、平均工资的等级
----------------------------------------------
select "T" . deptno , "T" . avg_sal , "G" . GRADE
from (
select "E" . deptno , avg ( sal ) "avg_sal"
from emp "E"
group by "E" . deptno
--order by "E".deptno--error 不能加
) "T" -- 这是一张临时表
join SALGRADE "G"
on "T" . avg_sal between "G" . LOSAL and "G" . HISAL
-- 等价于 ---------------------------------------
select "T" . deptno , "T" . avg_sal , "G" . grade
from SALGRADE "G"
join(
select deptno , avg ( sal ) "avg_sal"
from emp
group by deptno
) "T"
on "T" . avg_sal between "G" . losal and "G" . hisal
-- 等价于 ---------------------------------------
select "T" . deptno , "T" . avg_sal , "G" . grade
from salgrade "G" , (
select deptno , avg ( sal ) "avg_sal"
from emp
group by deptno
) "T"
where "T" . avg_sal between "G" . losal and "G" . hisal
----------------------------------------------
? 查找每个部门的编号、部门名称、该部门所有员工的平均工资、平均工
资的等级
----------------------------------------------
select "T" . deptno , "T" . avg_sal , "G" . grade , "D" . dname
from SALGRADE "G"
join(
select deptno , avg ( sal ) "avg_sal"
from emp
group by deptno
) "T"
on "T" . avg_sal between "G" . losal and "G" . hisal 19
join dept "D"
on "T" . deptno = "D" . deptno
----------------------------------------------
? 求出 emp 表中所有领导的信息
----------------------------------------------
select *
from emp
where empno in ( select mgr from emp )
----------------------------------------------
select *
from emp
where empno not in( from mgr from emp ) —error
-- 因为里面有 null ,属于 in null 的组合带来的问题
----------------------------------------------
? 求出平均薪水最高的部门的编号和部门的平均工资
a) 第一种写法:
---------------------------------------------
select top 1 deptno , avg ( sal ) "avg_sal"
from emp
group by deptno
order by avg ( sal ) desc
---------------------------------------------
b) 第二种写法: ( 可以跨数据库 )
---------------------------------------------
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
) "T"
)
---------------------------------------------
? 把工资大于所有员工中工资最低的人中,前 3 个人的姓名、工资、部门
编号、部门名称、工资等级输出
----------------------------------------------
select min ( sal ) from emp 20
select *
from emp
where sal > ( select min ( sal ) from emp )
-- 到此生成一个临时表
-- 不能 join on
----------------------------------------------
select TOP 3 "T" . ename , "T" . sal , "T" . deptno ,
"D" . dname , "S" . GRADE
from (
select *
from emp "E"
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 asc
----------------------------------------------
? 外连接
不但返回 满足连接条件的所有记录,
? 定义:
而且会返回 部分不满足条件的记录
? 分类:左(右)外连接:不但返回满足连接条件的所有记录,而且
会返回左(右)表不满足连接条件的记录
? 左外连接运行原理:
select * from emp “E”
left join dept “D”
on E.deptno = D.deptno
? 用左表的第一行分别和右表的所有行进行连接
如果有匹配的行,则一起输出,
如果右表有多行匹配,则结果集输出多行,
如果没有匹配行,则结果集中只输出一行,该输出行左边为左表第一行内
容,右边全部输出 null (内连接不输出)
? 然后再用左表第二行和右边所有行进行连接,
如果有匹配的行,则一起输出,
如果右表有多行匹配,则结果集输出多行,
如果没有匹配行,则结果集中只输出一行,该输出行左边为左表第二行内
容,右边全部输出 null
? 以此类推,直至左边所有行连接完毕
? 因为右边很可能出现有多行和左边的某一行匹配,所以左连接产生的结果
集的行数 至少大于 left join 左边表的记录的总数 ?
21
帮助文档:左向外连接的结果集包括 LEFT OUTER 子句中指定的左表的所
有行,而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配
行,则在相关联的结果集行中右表的所有选择列表列均为空值
? 实际上左连接产生的结果集的行数很至少大于左边表的记录的总数,不
能理解为:左边表有 n 行,最终产生的记录也是 n 行。实际上左连接产
生的结果集的行数至少大于左边表的记录的总数
----------------------------------------------
select *
from dept"D"
left join emp "E"
on E . deptno = D . deptno
----------------------------------------------
结果有 16 行,左面的 dept 5 行( 10 20 30 40 50 ),前三行可以
emp 表中的值进行连接,将有 14 行,其中 40 50 与右面的表没有
连接成功,分别输出 dept 表中的第 15 16 行,右面都为 null
? 左外连接的实际意义
? 一个事物 及其 该事物的相关信息 如果该事物 没相关信息 ,则 输出 null
? 例子:
a) 已知条件:
productStocks 货物库存表
orderForm 订单表
pId 产品的编号
? sql 语句:
a) select productStocks.*, orderForm.*
from productStocks
left join orderForm
on productStocks.pId = orderForm.pId
? 实际意义:
返回仓库中现存 货物的信息表 及其该 货物的订单信息表 ,如果货物
没有订单信息 ,在把该 货物的订单信息全部输出为 null
? 完全连接( 左外连接与右外连接的结合
? 例子:
select * from productStocks
full join orderForm
on productStocks.pId = orderForm.pId
? 结果集中包含二部分内容
? 两个表匹配的所有行记录
? 左表中那些在右表中找不到匹配的行的记录,这些记录的右边全为 null
? 右表中那些在左表中找不到匹配的行的几率,这些记录的左边全为 null
? 交叉连接
select * from emp cross join dept
等价于
select * from emp, dept ?
22
自连接
? 定义:一张表 自己 自己连接 起来查询数据
? 例子:不准用聚合函数,求薪水最高的员工的信息
----------------------------------------------
-- 用聚合函数,求薪水最高的员工的信息
select *
from emp
where sal = ( select max ( sal ) from emp ) - - 注意 =
-- 不用聚合函数,求薪水最高的员工的信息
select "E1" . empno , "E1" . sal , "E2" . sal
from emp "E1"
join emp "E2" -- 自连接
on "E1" . sal < "E2" . sal
-- 输出不包含那一行
----------------------------------------------
select "E1" . empno , "E2" . sal , "E2" . sal
from emp "E1"
left join emp "E2" -- 自连接
on "E1" . sal < "E2" . sal
-- 输出包含那一行,且右边为 null
----------------------------------------------
-- 所以
select *
from emp
where empno not in ( -- 注意不能用 =
select distinct "E1" . empno
from emp "E1"
join emp "E2" -- 自连接
on "E1" . sal < "E2" . sal
)
----------------------------------------------
? 联合( 纵向 连接
? 定义:表和表之间的数据以 纵向的方式 连接在一起
? 我们以前讲的所有的连接是以横向的方式连接在一起的
? 例子:输出每个员工的姓名,工资,上司的姓名
----------------------------------------------
select *
from emp "E1"
join emp "E2"
on "E1" . mgr = "E2" . empno
-- 输出 13 行,因为有一行 king 没有上司为 null
--------------------------------------------- --
23
此时使用联合
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 子句要联合成功的话,必须得满足两个条件,
? 这若干个 select 子句输出的列数必须是相等的
? 这若干个 select 子句输出列的数据类型至少是兼容的
identity
? identity【 主键自动增长 ,用户不需要为 identity 修饰的主键赋值】
? identity 表示该字段的值会自动更新,不需要我们维护,通常
情况下我们不可以直接给 identity 修饰的字符赋值,否则编译
时会报错
? 语法格式为
? identity(m, n)
? m 表示的是初始值, n 表示的是每次自动增加的值
? 要么同时指定 m n 的值,要么 m n 都不指定,不能只写其中一个值;如
m n 都未指定,则取默认值 (1, 1)
---------------------------------------------------
create table student2
(
student_id int primary key , - - 必须手动为主键赋值
student_name nvarchar ( 200 ) not null
)
insert into student2 values ( 1 , ' 张三 ' )
insert into student2 values ( 2 , ' 李四 ' )
insert into student2 values ( 3 , ' 王五 ' )
select * from student2
-----------------------------------------------
create table student3
(
student_id int primary key identity , - - 从增长自动
--indentity(100, 5) 从开始自增 24
student_name nvarchar ( 200 ) not null
)
insert into student3 ( student_name ) values (' 赵六 ')
select * from student3
insert into student3 values (' 李四 ') -- 可以忽略主键
select * from student3
delete from student3 where student_name = ' 李四 '
select * from student3
insert into student3 values (' 张三 ')
select * from student3 -- 主键值为 3 ,
-----------------------------------------------
? 数据类型是整型的列才能被定义成标识列
? int, bigint, smallint 列都可以被定义成 identity
? 不含有小数位的 decimal numeric 也可以被标记为 identity, 如: decimal,
decimal(6, 0) 字段都可以被标记为 identity ,但是 decimal(6, 2) 字段就不能被标
记为 identity
? 标识列通常与 primary key 约束一起用作表的唯一行标识符(非
主键也是可以被定义为 identity 的,但不推荐)
? 如何重新设置 identity 字段的值
create table emp(
empid identity(1, 1),
ename nvarchar(20) not null,
);
insert into emp values(‘aaaa’);
insert into emp values(‘bbbb’);
insert into emp values(‘cccc’);
insert into emp values(‘dddd’);
--8
select * from emp
delete from emp where empid = 4
-- 删除 empid 4 的记录
select * from emp
insert into emp values(‘eeee’)
-- 因为执行 8 行时 empid 4 ,所以执行
-- 本句时, empid 5
select * from emp
delete from emp where empid = 5
dbcc checkident(‘emp’, reseed, 3)
--16 行,把 emp 表中 identity 字段的初始
-- 值重新设置为 3
insert into emp values(‘eeee’)
-- 此时插入记录时, empid 4 ,因为 16
行代码已经把 empid 设置成了 3
select * from emp
? dbcc checkident(‘emp’, reseed, 0) ?
25
种子的值也可以是零,这样设置的话,用户插入值时,种子的初始值将从 1
开始
? 如何向 identity 字段插入数据(不是重点)
? 通常 identity 标记的字段我们是不需要插入数据的,即我们不需要维护 identity
字段的值,它会自动更新,如果我们需要向 identity 修饰的字段插入值,则必
须满足如下两点:
? 先得执行 setidentity_insert[database.[owner]] {bable} {on|off}
? 插入数据时必须得指定 identity 修饰的字段的名字
? identity 字段插入数据示例:
? create database Test
? use Test
? create bable dept(
a) deptid decimal(6, 0) identity,
b) deptname varchar(20)
? );
? set identity_insert test.dbo.dept on
i.
执行本句的目的是:希望可以各 identity 修饰的字段插入值
ii.
不可以改为 set identity _insert dept on
iii.
不可以改为 set identity _insert dbo.test.dept on
iv.
不可以改为 set identity _insert dbo.test.dept.on
? insert into dept(deptid, deptname) values (1, ‘zhangsan’)
i.
不能改为: insert into dept values(1, ‘zhangsan’)
视图
? 为什么需要视图:简化查询(避免了代码的冗余,改名了书写
大量重复的 sql 语句)
------------------------------------------------------
-- 求出平均工资最高的部门的编号和部门的平均工资
select *
from (
select deptno , avg ( sal ) "avg_sal"
from emp
group by deptno
) "T"
where "T" . "avg_sal" = (
select max ( "E" . "avg_sal" )
from (
select deptno , avg ( sal ) "avg_sal"
from emp
group by deptno 26
) "E"
)
------------------------------------------------------
-- 使用视图避免了代码的冗余
create view v$_emp_1
as
select deptno , avg ( sal ) "avg_sal"
from emp
group by deptno
select * from v$_emp_1
------------------------------------------------------
select * from v$_emp_1
where avg_sal = ( select max ( avg_sal ) from v$_emp_1 )
------------------------------------------------------
? 什么是视图:
? 视图 从代码上看是一个 select 语句
? 视图 从逻辑上看被当做一个 虚拟表 看待
? 如何创建视图:
? create view 视图的名字
as
–select 的前面不能添加 begin
select 语句
–select 的后面不能添加 end
? 视图的优点:简化查询,增加数据的保密性(隐藏)
------------------------------------------------
-- 隐藏工资 , 入职年费
create view v$_emp2
as
select empno , ename , job , mgr , comm , deptno from emp
select * from v$_emp2
------------------------------------------------
? 视图的缺点:增加了数据库维护的成本
只是简化了查询,但是并不能加快查询的速度
? 注意的问题: ?
27
创建视图的 select 语句必须为所有的计算列指定别名
------------------------------------------------
create view v$_a
as
select avg ( sal ) from emp --error
------------------------------------------------
create view v$_a
as
select avg ( sal ) "avg_sal" from emp --OK
-- 不使用分组默认全部为一组
------------------------------------------------
? 视图不是物理表,是虚拟表
? 不建议通过视图更新视图所依附的原始表的数据或结构
事务【重要,初学者重在理解概念】
? 初学者必须理解的三个概念:
? 事务是用来研究什么的 , 为什么需要事物
保证数据的合理性( 要么成功要么失败
? 事务主要用来
并发处理的能力( 多个用户访问怎么办
? 通俗点说:
a) 事务可以保证避免数据处于一种不合理的中间状态
要么全部执行失败,
要么全部执行成功,
不能出现半对半错的情况。
例如 银行转帐
b) 利用事务可以实现 多个用户 对共享资源的 同时访问
? 事务和线程的关系:
? 事务 是 通过 来解决并发访问的
? 线程同步 也是 通过锁 来解决并发访问的 synchronized
? 所谓并发访问是指:多用户同时访问同一个数据
? 事务和第三方插件的关系
? 直接使用事务库技术难度大,很多人是借助第三方插件来实现,因
此一般人不需要细细研究数据库中事务的语法细节
? 第三方插件要想完成预期的功能,一般必须得借助数据库中的事物
机制来实现 ?
28
T-SQL 使用下列语句来管理事务(不重要) 异常机制
? 开始事务: begin transaction
? 提交事务: commit transaction
? 回滚(撤销)事务: rollback transaction
? 一旦事务提交或回滚,则事务结束
? 事务三种运行模式:
? 显式事务 :(一般使用)
? 每个事务均以 begin transaction 语句显式开始
? commit rollback 语句显式结束
? 自动提交事务:
? 第条单独的语句都是一个事务。如果成功执行,则自动提交;如果错误,
则自动回滚;这是 sql server 2005 默认的模式
? 隐性事务:
? 在前一个事务完成时 新事务隐式启动 ,但每个事务仍以 commit rollback
语句
? 事务的四大特性(简称 ACID 属性,一般在面试的时候能用到):
? 原子性:事务是一个完整的操作。事务的各步操作是不可分的;
要么都执行,要么都不执行
? 一致性:当事务完成时,数据必须处于一致状态,
要么处于开始状态
要么处于结束状态,
不允许出现中间状态
? 隔离性:指当前的事务与其他未完成的事务是隔离的。在不同的隔
离级别下,事务的为读取操作,可以得到的结果是不同的
? 持久性:事务完成后,它对数据库的修改被永久保持,事务日志能
够保持事务的永久性
? 注意问题:不能在 sql server 中单独使用 commit, rollback 语句
索引(可以加快查询)
? 类似于字典的目录
存储过程
游标
TL-SQL 29
触发器
分页查询
假设每而显示 n 条记录,当前要显示的是第 m 而,表名是 A ,主键是 A_id
select top n *
from A
where A_id not in (select top (m - 1) * n A _id from emp )
--------------------------------------------------
-- 分页 --- 打开网页下面可以一页一页的看,有下一页上一页按钮等
-- 输出工资最高的前三个
select * from emp order by sal
select top 3 * -- 后执行
from emp
order by sal desc -- 先执行
-- 工资从高到低排序,输出工资排名第 -6 的员工信息
select top 3 *
from emp
where empno not in (
select top 3 empno
from emp
order by sal desc
) -- 找到工资最高的三个排除
order by sal desc
-- 工资从高到低排序,输出工资排名第 -9 的员工信息
select top 3 *
from emp
where empno not in (
select top 6 empno
from emp
order by sal desc
) -- 找到工资最高的六个排除
order by sal desc
-- 工资从高到低排序,输出工资排名第 -12 的员工信息
select top 3 *
from emp
where empno not in ( 30
select top 9 empno
from emp
order by sal desc
) -- 找到工资最高的九个排除
order by sal desc
-- 工资从高到低排序,输出工资排名第 -15 的员工信息
select top 3 *
from emp
where empno not in (
select top 12 empno
from emp
order by sal desc
) -- 找到工资最高的个排除
order by sal desc

文章来源:https://blog.csdn.net/u014045237/article/details/135257235
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。