一、数据库是如何解决数据存储问题的 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 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 deptno12 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_salbetween“G”.losaland “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”.hisal19 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 emp20 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 deptno26 )“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-SQL29 触发器 分页查询 假设每而显示 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