sql语句知识.docx
《sql语句知识.docx》由会员分享，可在线阅读，更多相关《sql语句知识.docx（21页珍藏版）》请在冰豆网上搜索。
sql语句知识
1.常见的数据库：
SqlServer（微软）
Oracle（甲骨文）
mysql（sun公司）
DB2（IBM）
2.建议将oracle数据库的程序在在服务中改为手动启动，只有需用到Oracle数据库时才将服务器打开，只需要打开两个服务即可，一个是OracleOradb10g——homelel,另外一个是OracleSericeOrcl
3.可以直接在cmd中输入sqlplus再输入用户名和密码进行连接！
4.数据库中的table作用：
起到了Java中类的作用：
存储具有相同属性的一类事物的信息
sql语句的分类
a）DDL，数据定义语言createtabledroptable等（创建删除表）
b）DML，数据操纵语言（必须commit）insertupdatedelete（对数据库的字段进行增删改查）
c）DQL，数据查询语言select（查询语句）
d）DCL，数据控制语言dba（数据库管理员）
SQL语句：
1select查询字段from表名
2where条件（and或or）
3groupby分组的字段
4having分组的限制条件
5orderby字段（asc或者desc）
创建表：
1.Createtableperson（
idnumber（6,0），namevarchar2（30）,
sexnumber（2,0）,birthdaydate）;
2.descperson展现person的字段、类型、长度。
删除表：
droptableperson;
追加列：
altertablepersonadd（nicknamevarchar2（30））;
虚表dual：
查找与自定义表无关的内容，但是显示的个数是个自定义的元素数一样多！
！
！
因此查找函数或是与自定义列表无关的数据或ASCII编码值时候用虚表查询：
如：
selectsysdatefromdual（虚表）;
selectchr（67）fromdual;
selectascii（'A'）fromdual;
修改表中字段的类型/值：
1）altertablepersonmodify（修改）（nicknamenumber
（2））;
2）updatetablenamesetcolumn1=value1,column2=value2where+条件
给列重命名：
altertablepersonrenamecolumnnicknametonick;
删除列：
altertablepersondrop（nick）;
给表重命名：
renamepersontoperple;
数据查询语句：
select要查询的字段列表fromtablename
要查询的字段列表：
*代表查询该表中所有字段或者是要查询的字段的列表格式为（column1,column2,column3....）
起别名：
selectempnoas"biaohao"/bianhaofromemp;
（严格区分大小写，或有特殊的标识符的时候需要双引号）
selectempno编号，ename姓名，sal月薪;
起别名不是可以用在查询语句时候；
distanct：
筛选（任何一个查询语句的结果都可以看成是一张新表）
distinctid,name,sexfromperson;
distinct可以过滤出各个元素值都相同的！
只保留一个！
selectdistinctnamefromstudent;
orderby:
orderbysaldesc降序
orderbysalasc升序
orderby放在各个条件的后面最后执行。
orderbysal,enamedesc先按照薪水的升序排列，再按照ename的降序排列。
拼凑字段：
selectename||sal||jobfromperson;
selectconcat（ename,sal）fromemp;（只允许平凑两个）
求字符串的长度：
selectlength（ename）fromemp;
截取字符串：
selectsubstr（ename,n,m）fromemp;
从第n位开始往后截取n位。
ASCII查询，字符对应的ASCII编码值，以及ASCII编码值对应的字符是？
selectchr（67）fromdual;
selectascii（'A'）fromdual;
四舍五入：
selectround（199.76,n）fromdual保留n位小数
to_char对数字进行格式化：
9：
代表一位数字，如果该位没有数字则，不进行显示，但是对于小数点后面的部分仍然会强制显示。
selectename,to_char（sal,'$999,999,999.999'）;
0：
代表一位数字，如果该位没有数字则强制进行显示0。
（左对齐）
selectename,to_char（sal,'$000,000,000.000'）;
to_char对日期进行格式化：
selectto_char（sysdate,'YYYY-MM-DDHH24:
MI:
SS'）fromdual;
selectto_date（'1985-05-15','YYYY-MM-DD'）fromdual；
nvl:
selectnvl（comm,0）,comm,fromemp;
查询包含奖金：
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_sal,jobfromemp;
基本函数：
可以用在select和from之间的查询字段的列表处，也可以用在where条件语句中,eg:
lower,upper,substr,to_char（数值和日期进行格式化）,to_date等
lower小写；upper大写
selectto_char（sal,）
selectto_char（sysdate,'YYYY-MM-DDHH24:
MI:
SS'）fromdual;
selectto_date（'1985-05-15','YYYY-MM-DD'）fromdual；
组函数：
avg（返回平均值）
count（返回记录的数量）
max（返回最大值）
min（返回最小值）
sum（返回总值）
nvl（comm,0）将奖金为空的所有的comm赋值为0；
组函数求的是一个结果，不能放在where语句中。
在使用groupby时，select语句中，所有字段，如果有没有出现在组函数中，就必须出现在groupby语句中。
having,过滤掉不符合条件的数据，专门用在分组查询里面。
1.selectcomm,nvl（comm,0）fromemp;
selectavg（sal）fromemp;
2.selectcount（empno）fromemp;（求出部门总数是？
）
3.selectsum（（sal+nvl（comm,o））*12）fromemp;（求出公司一年总的支出，工资方面）
组函数查询的是唯一的一个结果，所以必须单独计算，如果要和别的字段一起查询，那么另外的那个字段也必须是放在组函数中。
groupby:
对语句进行分组。
出现在select列表中的字段，如果不在组函数中，那么必须出现在groupby子句中。
eg：
selectdeptno,avg（sal）,count（empno）fromempgroupbydeptno;
eg:
selectdeptno,empno,avg（sal）groupbydeptno;错了
当题目中要求不可以使用组函数的时候，要想到利用any,some,all
伪字段：
rownum（只支持<=）
查询表中前6～10人的信息：
selectrownum,ename,job,salfromempwhererownum<=5;错误
select*from（selectrownumr,ename,job,salfromemp）whererbetween6and10
数据库的分页，根据每页显示的信息的总数，以及用户选择的页号聚顶从数据库中提取那些数据？
mysql的分页：
select*fromuserorderbydesclimitn,m.
<备注>：
n表示从第n条数据开始查找，查找m条数据，mysql索引从0开始。
Oracle的分页利用rownum伪字段来实现rbetween？
and？
第一页：
rbetween1and10
第二页：
rbetween11and20
第三页：
rbetween（n-1）*size+1andn.size
Oracle分页利用rownum伪字段需要注意和orderby使用的策略.
Oracle的分页：
首先，要求出查询语句（不包含分页的）该语句为“sql子查询”，然后，select*from（selectrownumr,a.*from（?
）
a）whererbetweennandm.
注意,第一个问号是代表查询语句，第二个问号是代表从第几行开始查询，第三个问号是带表查询的行号
<备注>：
n表示从第n个开始查找，查找到m条，oracle索引从1开始。
sqlserver的分页利用top
视图：
1.建立视图的条件：
某条子查询多次使用。
2.建立视图的目的：
1）降低操作复杂度。
视图是预编译的查询操作，一次定义，之后可以多次快速的调用。
2）提高系统安全性。
视图作为数据库的对象，可以将其权限独立出来赋给用户，可以避免
用户对基表的盲目为想的操作，同时，屏蔽一部分的私密的属性列。
缺点：
如果视图建立太多，会给书库的维护升级造成很大的麻烦。
1）.查询数据库当前的时间oracle的是sysdate
oracle中的虚表dual:
查询与具体表没有关系的字段，比如数据库当前时间或者是一些表达式的使用。
selectsysdatefromdual;
2）.查询出所有员工的编号，姓名，职位，月薪，年薪（不包含奖金），入职日期并且给字段起别名
3）.查询出所有员工的编号，姓名，职位，月薪，年薪（包含奖金），入职日期并且给字段起别名
含有任何null值的数学表达式最后的结果都为空值
数据中含有空值时往往会增加程序的处理难度,强烈建议在实际当中开发数据库应用系统的时候不要使用空值
4）.查询出月薪大于1500的人的编号，姓名，职位，月薪，年薪（不包含奖金），入职日期
5）.查询出奖金为空的人的姓名，职位，月薪，奖金，年薪，入职日期
select*fromempwherecomm=null;
6）.查询出工资大于等于1500小于等于3000的人的姓名，职位，月薪，奖金，年薪（包含奖金），入职日期（两种语句写出）
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_sal,jobfromempwheresalbetween1500and3000;
7）.查询出编号为7369，7844,7902三个人的信息（两种写法）
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwhereempno=7369orempno=7902orempno=7902;
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwhereempnoin（7369,7782,7902）;
8）.查询出编号不为7369，7844,7902人的信息（两种写法）
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwhereempno!
=7369andempno!
=7902andempno!
=7902;
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwhereempnonotin（7369,7782,7902）;
9）.查询出名字中包含A的人的信息
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwhereenamelicke'%A%';
10）.查询出名字中倒数第三个字符为A的人的信息
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwhereenamelike'%A__';
11）.查询出薪水大于等于2000小于等于3000并且职位不等于ANALYST的人的编号，名字，职位，月薪，奖金，年薪
12）×.查询出入职日期28-9月-81之后入职并且奖金不为空的人的编号，名字，职位，月薪，奖金，年薪
selectempno,sal,comm,sal*12annual_salfromempwherehibernate>'28-9月-81'andcommisnotnull;
13）.按照员工月薪由高到低的顺序进行排列后的员工的信息
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromemporderbysal*12/annual_saldesc;
（order的后面可以用字段的别名，他是按照查询的结果进行排序的，所以可以使用）
14）.查询出薪水大于1500的人的编号，姓名，薪水，年薪并且按照薪水的降序进行显示
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwheresal>1500orderbysaldesc;
15）.查询出薪水在1000到4000之间的人的信息并且按照入职时间的先后进行显示
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwheresalin（1000,4000）andorderbyhiredateasc;
16）.查询出薪水在1000到4000并且名字中包含A并且奖金为空的人的信息并且按照薪水的降序进行排列
Selectempno,ename,sal,（sal+nvl（comm,0））*12annual_sal,hiredate
wheresalin（1000,4000）andenamelike'%A%'andcommisnullorderbysaldesc;
17）.求出名字中包含a（不区分大小写）的人的信息
注意：
基本函数（lower,upper,substr,to_char,to_date等）可以用在select和from之间的查询字段的列表处也可以用在where条件语句中
selectemp,ename,sal,（sal+nvl（comm,0））*12annual_salfromempwherelower（ename）like'%a%'
18）.查询出薪水在1000到4000并且名字中包含A并且奖金为空的人的信息并且按照薪水的降序进行排列
19）.查询出名字前两个字符为al的人的信息（两种写法实现）
selectemp,ename,salfromempwherelower（ename）like'th%';
selectemp,ename,salfromempwherelower（substr（ename,1,2））
19-5）.查询出名字中后两位字符为er人的信息（两种写法实现）
selectemp,ename,salfromempwherelower（ename）like'%er';
Selectemp,ename,salfromempwherelower（substr（ename,length（ename）-2,2））='th';
20）.查询出薪水大于1200并且入职日期在1981-04-02之后的人的编号，名字，月薪，年薪（不包
含奖金）,入职日期,格式化中国人能接受的日期形式
Selectempno,sal,ename,（（sal+nvl（comm,0））*12）annual_sal,to_char（hiredate,'YYYY-MM-DD'）hirefromemp
whereto_char（hiredate,'YYYY-MM-DD'）>'1981-04-02'andsal>1200;
selectename,to_char（hiredate,'YYYY-MM-DD'）hiredate,sal,fromemp
wherehiredate>to_date（'1981-02-12','YYYY-MM-DD'）andsal>1200;
21）.求出名字中包含a并且入职日期在1982-06-01之后入职的人编号，名字，月薪，年薪（不包含奖金）,入职日期,格式化中国人能接受的日期形式
并且按照月薪的由高到低的顺序进行显示（两种形式to_char和to_date）
selectename,to_char（hiredate,'YYYY-MM-DD'）hiredate,sal,sal*12total_salfromempwhereto_char（hiredate,'YYYY_MM_DD'）>'1982-06-01'andenamelike'%a%'andorderbysaldesc;
selectename,to_char（hiredate,'YYYY-MM-DD'）hiredate,sal,sal*12total_salfromemphiredate>to_date（'1982-06-01','YYYY_MM_DD'）>andenamelike'%a%'andorderbysaldesc;
22）.查询出月薪在公司的平均工资之上人的信息
组函数（avg,count,max,min,sum）只可以用在查询的列表不可以用在where的条件语句中，如果在查询列表出现组函数那么通常情况下不能单独出现其他字段除非其他字段也放到相应的组函数中才可
selectdeptno,avg（sal）,count（empno）fromemp
wheresal>（selectavg（sal）fromemp）
23）.求出部门编号为10的所有员工的平均工资
selectavg（sal）fromempwhereempno=10;
24）.求出公司每年总的支出，以及平均薪水，以及总人数
selectsum（（sal+nvl（cumm,0））*12）total_sal,avg（sal）avg_sal,count（empno）emp_numfromemp;
25）.求出公司中每个部门的平均薪水
在使用groupby时，有一个规则需要遵守，即出现在select列表中的字段，如果没有在组函数中，那么必须出现在groupby子句中。
selectdeptno,avg（sal）groupbydeptno;
26）.求出每个部门的部门编号，每年总的支出，以及该部门的平均薪水，以及该部门的人数
Selectdeptno,sum（（sal+nvl（comm,0））*12）sum_sal,avg（sal）,count（empno）fromempgroupbydeptno
27）.查询出部门平均薪水大于1600的部门的部门编号，平均薪水，人数？
selectdeptno,avg（sal）,count（empno）fromempgroupbydeptnohavingavg（sal）>1600;
28）.求出每个部门的部门编号，每年总的支出，以及该部门的平均薪水，以及该部门的人数
并且按照平均薪水由高到低的顺序进行排列
selectdeptno,sum（（sal+nvl（comm,0））*12）sum_sal,avg（sal）avg_sal,count（empno）groupbydeptnoorderbyavg_saldesc;
小结：
sql语句select的语法
selectcolumn1,column2,.....fromtablenamewhere过滤条件（andor）groupbycolumnhaving分组的限制条件
orderbycolumn（最后进行排序）
注意：
where过滤条件中只允许使用普通函数不可以使用组函数但是having分组限制条件中可以使用组函数
29）*.查询出工资大于1200，并且入职日期在1981-09-09以后的部门里面的人的平均薪水
大于2000的部门的平均工资及部门编号并且将其结果按照平均工资进行降序的排列
selectdeptno,avg（sal）fromempwheredeptnoin（
selectdeptno,avg（sal）fromempwheresal>1200andhiredate>to_date（'1981-09-09',YYYY_MM_DD）groupbydeptnohavingavg（sal）>2000）groupbydeptno;
30）.查询出公司所有人里面工资在部门编号为30最高工资之上的人信息
selectempno,ename,salfromempwheresal>（selectmax（sal）fromempwheredeptno=30）
31）.查询出工资大于公司的平均工资并且入职日期大于1981-02-16，并且名字中包含a的人的
编号，名称，月薪，年薪并且按照年薪进行降序排列
selectempno,ename,sal,sum（（sal+
升级会员 