sql-server 查询

二大部分. 查询 !!  数据库建表 三大范式！！！！！use kingcreate table temp(id int,sex nvarchar(1) check(sex in('M','W')) default('M'),constraint pk_id primary key(id))update temp set sex ='W' where id = 2;1.计算列select * from emp;select ename from emp;select ename, sal from emp;select ename, sal*12 as "年薪" from emp; 年薪 不带双引号也可以select ename, sal*12 as "年薪", sal "月薪" from emp;select ename, sal*12 as "年薪", sal "月薪", job from emp;select ename, * from emp;在sql-server可以 在Oracle出错 有奇异性2.distinct 不允许重复的select deptno from emp; 有重复的输出select distinct from emp; 重复的只输出一次select distinct comm from emp; NULL的也只显示一次select distinct comm, deptno from emp; 将comm 跟deptno 组合来输出组合后不一样的数据select comm, distinct deptno from emp; 不对3.between 某个范围和某个范围之间 between 小 and 大--查找工资在1500到3000之间(包含1500跟3000)的所有员工信息select * from emp where sal>=1500 and sal<=3000 跟下面等价select * from emp where sal between 1500 and 3000--查找工资在小于1500或大于3000之间(包含1500跟3000)的所有员工信息select * from emp where sal<1500 or sal>3000select * from emp where sal not between 1500 and 30004.in 属于若干孤立的值select * from emp where sal in (1500,3000)等价下面select * from emp where sal=1500 or sal=3000select * from emp where sal not in (1500,3000) --把sal中既不是1500又不是3000记录输出select * from emp where sal!=1500 and sal!=3000--等价上面--数据库中不等于两种表示 !=  <>--对或取反是并且  非并且是或5.topselect * from emp;select top 2 * from emp;select top 15 percent * from emp;自动安装全部*15% 往上取整数select top 5 * from emp where sal>=1500 and sal<=3000分页查询后面讲order by XXX  以什么为顺序输出 descselect top 2 * from emp where sal>=1500 and sal<=3000  select top 2 * from emp where sal>=1500 and sal<=3000 order by sal  --价格小到大输出select top 2 * from emp where sal>=1500 and sal<=3000 order by sal desc -- 价格大到小6.null 没有值 为空select * from  emp where comm <> null --errorselect * from  emp where comm !=(=) null --error--null 不能参与 <> != =可以参与is  not is select * from emp where comm is null;select * from emp where comm is not null;任何类型数据可以为nullcreate into t1 (name nvarchar(10), id int, riqi datetime)insert into t1 values(null,null,null)--输出每个员工的姓名年薪包含奖金 comm假设是一年奖金select empno,ename, sal*12 + comm "年薪" from emp; --null跟任何数据类型运算结果为空select empno, ename,sal*12 + ISNULL(comm, 0)"年薪" from emp;-- isnull(comm,0)如果comm是null 则返回0 否则返回comm值in 和null 共用 Oracle讲7.order by 按照什么样条件 哪个字段 排序不指定排序标准时默认为升序 asc desc 建议为每个字段都设置好显示排序标准select * from emp order by sal --默认按照升序排序  --asc升序意思 默认可以不写select * from emp order by deptno, sal --先按照deptno排序 deptno一样的按照sal 排序select * from emp order by deptno desc, sal --先按照deptno降序排序 deptno一样的按照 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都降序8.模糊查询 被搜索时候经常用到 like 格式为 select 字段集合 from 表名 某个字段 like 匹配的条件匹配的条件通常含有通配符匹配条件必须''扩起来通配符：1.%表示任意多个或零个字符select * from emp where ename like '%A%'select * from emp where ename like 'A%'select * from emp where ename like '%A'2._ 这个是下划线不是减号表示任意的单个字符select * from emp where ename like '_A%'3.[a-f] a到fselect * from emp where ename like '_[A-F]%'把ename中第二个为abcdef任意一个符合的记录输出4.[a,f] a或Fselect * from emp where ename like '_[A,F]%'把ename中第二个为a或f任意一个符合的记录输出5.[^a-c]select * from emp where ename like '_[^A-F]%'把ename中第二个不是a到f任意一个符合的记录输出查找含有%的该怎么处理？insert into student values('a%b',20)insert into student values('a_b',20)select * from student where name like '%\%%' escape '\'       把名字中含有%的输出select * from student where name like '%\_%' escape '\'       把名字中含有_的输出escape 特殊的意思 上面也可以用 注意escape的作用select * from student where name like '%m_%' escape 'm'       把名字中含有_的输出9.聚合函数  聚合函数是对一组值执行计算并返回单一的值的函数函数的分类：单行函数:每一行返回一个值多行函数:多行返回一个值聚合函数是多行函数select LOWER(ename)"名字" from emp --最终返回的是行lowerupper 函数把字段的值转换为大写。lower 函数把字段的值转换为小写select max(sal) from emp;聚合函数分类   聚合函数是对一组值执行计算并返回单一的值的函数sum()max() 最大select MAX(sal) from empmin() 最小select min(sal) from empavg() 平均值averageselect avg(sal) from empcount()求个数count(*) 返回表中所有记录个数select count(*) from emp; --返回emp表所有记录个数count(字段名) 返回字段值非空的记录个数 重复也要记录select count(deptno) from emp;--重复记录也记录select count(comm) from emp --返回值是4 这说明comm记录为null的数据不会当有效记录count(distinct 字段)select count(distinct deptno) from emp; --重复不多次记录判断如下正确性select MAX(sal),MIN(sal),COUNT(*) from emp; 对select MAX(sal)"最高工资",MIN(sal)"最低工资",COUNT(*)"员工人数" from emp;select max(sal), lower(ename) from emp; 返回一个 返回多个 错误10.group by  1-9是简单的 下面是难点分组 以什么什么分组格式: group by 字段的集合功能: 把表中记录按照group 分成不同组--输出每个部门编号 和该部门平均工资select deptno,AVG(sal) as "部门平均工资" from emp group by deptno重点理解下 group by a, b, c 用法先按照a分组 如果a相同再按b分组 依次类推 最终select和from 之间操作的是最小分组的信息--输出每个部门编号 和该部门平均工资select deptno, job, AVG(sal)"平均工资",COUNT(*)"部门人数",SUM(sal)"工资和", MIN(sal)"工资最小" from emp group by deptno, job order by deptno明白下面为什么错误：select deptno,AVG(sal) as "部门平均工资",ename from emp group by deptno 错 输出条数不一样select deptno, ename from emp group by deptno 已经分成group组了 select deptno, job, sal from emp group by deptno, job 记住: 使用group by 之后select中只能出现分组后整体信息 不能出现组内详细信息理解分组后的整体信息 和详细信息--comm中出现null的话按照同一个算select comm, count(*) from emp group by comm11.having  对分组之后信息进行过滤 二次筛选 重点1. having用来对分组后数据进行过滤。因此使用having时 通常先使用group by 2. 没有使用group by 但使用了having 意为着having把所有记录当一组来处理。很少用select count(*) from emp having avg(sal) >10003. having 之后出现的字段必须是分组之后整体信息 having之后不允许出现组内详细信息4. 尽管select 字段中可以出现as 别名  但是having 后不能出现as的别名having和where的异同相同地方： 都是对数据过滤  只保留有效数据不同之处：where是对原始数据过滤 ，having是对分组后数据进行过滤，where跟having顺序不能颠倒。where必须写在having的前面例子：select deptno, AVG(sal) as "平均工资",  count(*) "部门人数", max(sal) "最高工资" from emp where sal>2000 group by deptno having AVG(sal)>3000where 后面不应跟聚合函数 avg() 等12.连接查询 最重要地方定义： 将两个或者以上的表以一定的条件连接起来， 从中检索出满足条件的数据。分类内连接 【重点的重点 难点的难点】inner join  inner 可以不写1. select ... from A,B 的用法产生的结果：行数是A行和B行乘积列数是A列和B列之和--emp 14行 8列 dept表 5行 3列  笛卡尔集 select * from emp,dept --14*5 8+3 输出70行 11列2. select ... from A,B where... 的用法对select...from A,B 产生的笛卡尔集  用where 进行过滤select * from emp,dept where empno = 7369  --输出5行 11列3. select ... from A join B on... 的用法    别名用" "  字符串用''from emp "E"  join dept "D" on "E".deptno = "D".deptno"E"是别名 join是连接的意思  on 后面跟连接的条件   join 跟on 成对出现 select ename from emp join dept on 1=1   --from 前要有具体含义 不可是表中都存在的 如果都存在则要指明所在表位置4. select ... from A,B where ... 和select... from A join B on ...区别--where 和 join on 区别select * from emp,dept where emp.deptno = dept.deptno   --是sql 92 标准select * from emp join dept on emp.deptno = dept.deptno --是sql 99 标准  推荐用join on sql 99更容易理解  join on(连接条件) 和where(连接后再筛选) 分工不同 --select * from emp,dept where emp.deptno = dept.deptno   --是sql 92 标准--select * from emp join dept on emp.deptno = dept.deptno --是sql 99 标准--把工资大于2000的员工姓名跟部门名称输出 sql 92 最后筛选deptno想等select * from emp "E",dept "D" where "E".sal>2000 and "E".deptno = "D".deptno--sql 99 是最后筛选sal>2000select * from emp "E" join dept "D" on "E".deptno = "D".deptno where sal>2000--把工资大于2000的员工姓名跟部门名称输出和 工资的等级   考虑到emp dept grade三个表 sql 92 最后筛选deptno想等select "E".ename, "D".dname,"S".grade from emp "E",dept "D",salgrade "S"where "E".sal>2000 and "E".deptno = "D".deptno and("E".sal>="S".losal and "E".sal<="S".hisal)--sql 99select "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>2000where是否能放在join on  前面 ？不行   重点记忆下select "E".ename, "D".dname, "S".grade from emp "E" where "E".sal>2000 join dept "D" on "E".deptno ="D".deptno join salgrade "S" on "E".sal>="S".losal and "E".sal<="S".hisal select "T".num, "T".avg_sal, "S".grade from( select deptno "num",AVG(sal)"avg_sal" from emp group by deptno)"T" join salgrade "S" on "T".avg_sal between "S".losal and "S".hisalselect "T".num "编号", "T".avg_sal "平均工资", "S".grade "工资等级", "D".dname "部门名称" from( select deptno "num",AVG(sal)"avg_sal" from emp group by deptno)"T"   --临时表join salgrade "S" on "T".avg_sal between "S".losal and "S".hisaljoin dept "D" on "T".num = "D".deptno;--输出工资最高的前三名的每个员工的姓名  工资  工资等级 部门名称select top 3 "E".ename, "E".sal, "S".grade, "D".dname fromemp "E" join dept "D" on "E".deptno = "D".deptno join salgrade "S"on "E".sal between "S".losal and "S".hisal where "E".ename not like '[%A%]' order by "E".sal desc;select ... from  emp join ... on select "T".num, "T".avg_sal, "S".grade from( select deptno "num",AVG(sal)"avg_sal" from emp group by deptno)"T" join salgrade "S" on "T".avg_sal between "S".losal and "S".hisalselect "T".num "编号", "T".avg_sal "平均工资", "S".grade "工资等级", "D".dname "部门名称" from( select deptno "num",AVG(sal)"avg_sal" from emp group by deptno)"T" join salgrade "S" on "T".avg_sal between "S".losal and "S".hisaljoin dept "D" on "T".num = "D".deptno--求出emp表中所有领导的姓名select ename from emp where empno in(select mgr from emp)--求出平均薪水最高的部门编号和部门平均工资select top 1 deptno, AVG(sal)"部门工资" from  emp group by deptno order by AVG(sal) desc--把工资大于所有员工中工资最低的人中前三个人的 姓名 工资 部门编号 部门名称 工资等级 输出select min(sal) from emp select * from emp where sal > (select min(sal) from emp) 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;join(里面也可以是中间产生的临时表)临时表名select top .. from A join B on... join C on .... where... group by ... having ... order by ...select * from emp, dept where emp.deptno=10外连接  left right 内连接是返回所有符合条件的信息， 外连接是不仅返回符合条件的 还有部分不符合条件的select * from dept D left join emp E on D.deptno = E.deptnoA left  join B  产生的是结果行数大于等于左边行数  A中每一项跟B 中每一项对不 符合输出 不符合输出A 空Bright join  产生的是结果行数大于等于右边行数完全连接 fullfull join 完全连接 类似与 left 跟right 结合体 交叉连接 corss join select * from emp corss join dept 等价 select * from emp, dept 笛卡尔集自连接 自己跟自己连接 有时表外键是表本身一列select * from emp where sal =(select MAX(sal) from emp)select top 1 * from emp  order by sal desc;select * from emp where empno not in(select distinct E1.empno from emp E1 join emp E2 on E1.sal < E2.sal)集合 联合1. 集合属性A B 的列数必须相同  2. 集合属性没列的数据类型必须相同union  和集  A union B  union自动排序重复的记录一次  union all 重复的也要记录intersect 交集  A intersect Bexcept   除集  A except B   select 'IND' type_id, cust_id from individual union(这个可以换成三个中任意一个)select 'BUS' type_id, cust_id from businessA union B union all C  跟 A union all B union C 结果不一样！select E1.ename, E1.sal, E2.ename from emp E1 join emp E2 on E1.mgr = E2.empnounion  select ename, sal, 'boss' from emp where mgr is null分页查询  --分页查询  假设分页显示 n 条记录  当前要显示的是第 m 页-- 关键在用not in (....)select top n * from 表名  where 表_id not in (select top (m-1)*n 表_id from 表名 order by sal desc)order by sal desc;select top 3 * from emp where empno not in(select top 6 empno from emp order by sal desc)order by sal desc;identity 主键自动增长，用户不需要为identity修饰主键赋值create table student1(stu_id int  primary key  identity(1,1),sut_name nvarchar(20) not null)insert into student1 values('李白') --1insert into student1 values( '张三三');--2delete from student1 where sut_name = '张三三'--delete 2dbcc checkident('student1',reseed,1); --重点记忆下 使初始值为1 那么下面插入的就是2了insert into student1 values( '王五');--2select * from student113.嵌套查询oracle中讲视图 view  一个select语句的别名  逻辑上当成一个虚拟表为什么需要视图避免代码重复书写 减少代码冗余求出平均工资最高的部门编号和部门的平均工资select deptno, AVG(sal) from emp group by deptno--SQL-serverselect top 1 * from (select deptno, AVG(sal)avg_sal from emp group by deptno)T --通用如下select * from (select deptno, AVG(sal) avg_sal from emp group by deptno)Twhere T.avg_sal =(select MAX(E.avg_sal) from (select deptno, AVG(sal) avg_sal from emp group by deptno)E)-- 视图就是一个select 语句create view TEMPVIEWas select deptno, AVG(sal) avg_sal from emp group by deptnoselect * from TEMPVIEW where avg_sal = (select MAX(avg_sal) from TEMPVIEW)什么是视图视图从代码上看是select语句视图从逻辑上讲是一个虚拟表看待视图的格式create view 视图名 as select语句视图的优点简化查询，增加数据保密性视图的缺点增加数据库维护成本，删除本体表时 本体表产生的视图表不会跟随删除但是无法使用视图只是直观上简化了代码查询，但是并不能加快查询速度视图注意问题1.创建视图的select语句必须为所有计算列指定别名create view 视图名 as select avg(sal) from emp group by deptno; 不对create view 视图名 as select avg(sal)avg_sal from emp group by deptno; 对2.视图不是物理表，只是一个虚拟表3. 不建议通过视图更新视图所依附的原始表的数据或结果 有很多语法限制 删除 更新很复杂事务 重要！！ 类似线程中的 锁保证数据合理性跟并发处理状态1.避免数据处于一种不合理的状态转账 A减少 B增加  AB 就数据一个事务同时完成或不做2. 利用事务实现多个用户对共享资源的同时访问多个用户同时访问操作 如何实现正确性事务和线程的关系事务也是通过锁来实现并发问题线程同步也是通过锁来解决的 synchronized事务跟第三方插件的关系直接使用数据库难度太大 很多人都用数据库辅助插件完成访问我们普通人把数据库事务当工具用就好，而第三方插件需要考虑这种东西。T-SQL 开始事务 begin transaction提交事务 commit transaction回滚(撤销)事务 rollback transaction开始事务try {..... throw (抛出异常)}catch{撤销事务}提交事务事务特性 ACID属性1.原子性  事务是一个完整操作，不会执行一半不执行了2.一致性  事务执行完毕后数据处于一致状态  要么开始 要么结束  不会中间3.隔离性  当前事务跟其他没完成的事务是隔离的 在不同隔离级别下 事务的读取 可以得到不同结果4.持久性  事务完成后 对数据库修改完成  日志也要有效保存。SQL-SQL-server 例子：create database Testuse Testcreate table bank(customerEname nvarchar(200),currentMoney money )insert into bank values ('张三', 1000)insert into bank values ('李四', 1)--alter 修改 表alter table bank add constraint check_currentMoney check(currentMoney>=1)update bank set currentMoney=currentMoney-1000 where customerEname='张三'update bank set currentMoney=currentMoney+1000 where customerEname='李四'begin transactiondeclare @errorSum intset @errorSum = 0update bank set currentMoney=currentMoney-1000 where customerEname='张三'set @errorSum = @errorSum + @@error --(上面语句执行后自动把能否执行放入@@error中)update bank set currentMoney=currentMoney+1000 where customerEname='李四'set @errorSum = @errorSum + @@errorif (@errorSum <> 0)beginprint '转账失败'rollback transactionendelsebeginprint '转账成功'commit transactionend

索引  
        类似字典的目录  可以加快查询  视图view只能简化代码  

Oracle和SQL—server中将索引看成独立模式对象create index dept_name_idx on department (name);drop index dept_name_idx; (Oracle) 删除索引drop index dept_name_idx on department;(sql-server) 删除索引在表格创建时mysql 自动为主键生成索引show index from department --显示索引信息mysqlalter table department add index dept_name_idx (name);alter table department drop index dept_name_idx;删除索引drop index dept_name_idx on department;删除索引有时需要创建唯一索引 unique index 有效防止名字重复alter table department add unique index dept_name_idx (name);有时需要创建组合索引alter table department add index dept_name_idx (lname, fname);关于索引的使用跟下面语句类似select emp_id, fname, lname from employeewhere emp_id in(1,3,9,15);索引虽然可以加快查询时间， 但是要知道每一个索引都是一个表 操作原始表时 索引表也要跟着运动。 容易拖慢服务器速度。索引需要磁盘空间， 尽量避免乱用索引避免太多索引和太少索引

约束

constraint
primary key
unique  key
foreign key  references 
check
default

alter table product add constraint PK_id primary key(emp_id);
alter table product drop primary key(emp_id);
alter table product drop foreign key 外键名
alter table account add constraint account_un unique(cust_id, product_cd);

视图 view
一种简单的数据查询机制，不同于表，视图不涉及数据存储，因此试图不会存满磁盘空间
create view cust_vw
(cust_id, address,city)
as
select cust_id, cust_type_cd,address, city from customer;

seelct cust_id, address, city from cust_vw;
服务器真正执行的还是在customer表中进行查询。
将视图看为一个临时表 虚拟表 

视图的引入可以 保证数据的安全性， 因此语句复杂性
用户可以通过视图来修改原表数据 
但不是什么情况都可以修改 对于使用max()  groupby 子查询等 无法通过视图修改。
update cust_vw set city = 'WOOBURN' where city = 'wooburn';


MYSQL 对SQL 的扩展  
limit 选择前N个 
sql-server 中是 select top N .... from 
mysql 中用limit n   select... from ... limit n;
select emp_id count(*) many from account 
group by emp_id order by many limit 3;

limit 2,1;  从第三个开始(排除第0 1 2 个) 输出 1 个；
有时候用limit N，M;  是选择M个不过是从0开始到第N-1个排除在外的，

into outfile 打印表格到一个文件中   注意window下''中表示目录的要\\ 
select emp_id,fname,lname,start_date
into outfile 'C:\\temp\\emp_list.txt'
from employee;

      
    存储过程  
        一个可以把前面所有信息包含在内的  
          
    游标  
        如果我们对一个数集  分情况处理 此时用到游标  
          
    TL—SQL   
        SQL-server中的内容 第四代语言 不是过程型  笨拙   
        为此不同厂商如mysql sql-server 加入一些简单处理语句 让此时sql 介于3-4带语言之间  
        让C++  JAVA 实现一些数据操作功能。  
          
    数据库重点是：  
        数据库如何解决数据存储问题   
            字段  表   记录  属性  列  元素  约束  关系 主键  外键 check default  unique  
            not null  触发器  
            查询 distinct inner join on   in    内连接  视图   事务