sql 大于 小于 大于等于 小于等于 符号

SQL查询一 实验学时：2   实验类型：验证 实验要求：必修 一、实验目的 通过本实验使学生掌握单表查询并初步掌握多表查询的技能。 二、实验内容 使用实验一建立的银行贷款数据库和表，完成以下查询。 1-10题为单表查询，11-25为多表查询。 查询所有法人的法人代码、法人名称、经济性质和注册资金。 查询“B1100”银行的的银行名称和电话。 查询贷款金额在2000至4000万元之间的法人代码、银行代码、贷款日期和贷款金额。 查询2009年1月1日以后贷款且贷款期限是10年的法人代码。 查询贷款期限为5年、10年或15年的贷款信息。 查询经济性质为“私营“的所有法人的最高注册资金、最低注册资金和平均注册资金。 查询每种经济性质的法人的经济性质、最高注册资金、最低注册资金和平均注册资金。 统计每个法人的法人代码和贷款总次数，要求查询结果按贷款总次数的升序排列。 查询贷款次数超过3次的法人的平均贷款金额和贷款次数。  统计每种经济性质贷款的法人的总数和其平均贷款金额，列出平均贷款金额前三名的经济性质、法人总数和平均贷款金额。  查询贷款期限为5年、10年或15年的法人名称、银行名称、贷款日期、贷款金额和贷款期限。  查询经济性质为“国营”的法人在“上海”的银行贷款的信息，列出法人名称、银行名称和贷款日期。  查询与“B1100”银行在同一城市（假设银行名称的第5和第6个字符为城市名称）的其他的银行的名称。  查询哪些银行没有贷过款，列出银行号和银行名称。分别用多表连接和子查询两种方式实现。  查询贷过款的所有法人的名称，贷款银行名称，贷款日期，贷款金额，要求将查询结果放在一张新的永久表New_LoanT中，新表中的列名分别为：法人名称、银行名称、贷款日期和贷款金额。  分别查询经济性质“国营”和“私营”的法人名称，贷款银行名称，贷款日期，贷款金额，要求将这两个查询结果合并成一个结果集，并以法人名称、银行名称、贷款日期和贷款金额作为显示列名，结果按贷款日期的升序和贷款金额的降序显示。 使用实验一建立的学生数据库和表，完成以下查询 查询计算机系没有选课的学生的姓名和年龄。 统计‘VB’课程的考试最高分、最低分和平均分。 统计‘数据库’课程的选课人数。 统计计算机系学生’vb’课程考试的最高分、最低分、平均分。  统计每个系学生’vb’课程考试的最高分、最低分、平均分，列出系名和三个分数。 查询每个学生的修课总学分，并进行如下处理：如果总学分高于10分，则显示‘好学生’；如果总学分在6到10分间，则显示‘一般学生’；如果总学分低于6分，则显示‘不好学生’。 统计每个系每个学生的修课门数和平均成绩，如果修课门数大于3并且平均成绩高于90的显示‘优秀’；如果修课门数大于3并且平均成绩在80～90，则显示‘较好’；对于修课门数小于等于3的并且平均成绩高于90的显示‘较好’；如果修课门数小于等于3并且平均成绩在80～90，则显示‘一般’；其他情况显示为‘应努力’。列出系名、学号、原修课门数和平均成绩以及处理后的显示结果。 查询计算机系学生考试成绩最低的两个成绩所对应的学生的姓名、课程名和成绩。 列出没有选课的学生的学号、姓名和所在系。 三、思考题 1、创建表： 教师表（   tid 普通编码定长字符型，长度为10，主关键字   tname 普通编码定长字符型，长度为10，非空 zc 普通编码定长字符型，长度为6) 设此表数据如下： tid Tname zc T1 A 教授 T2 B 副教授 T3 C 教授 编写SQL语句，使查询的结果显示为如下形式： 教师号 教师名 教授 副教授 T1 A 教授 T2 B 副教授 T3 C 教授 2、创建表： 教师表（   tid 普通编码定长字符型，长度为10，主码   tname 普通编码定长字符型，长度为10，非空 zc 普通编码定长字符型，长度为6 Salary 工作，整型) 设此表数据如下： tid Tname zc Salary T1 A 教授 5000 T2 B 副教授 4000 T3 C 教授 5000 编写SQL语句，使查询的结果显示为如下形式： 教师号 教师名 教授工资 副教授工资 T1 A 5000 Null T2 B Null 4000 T3 C 5000 Null 四、实验报告 将实验结果反映在实验报告中，并对实验中遇到的问题及解决方案进行整理、分析总结，提出实验结论或自己的看法。

SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- 65 97 48 32 2.CHR 给出整数,返回对应的字符; SQL> select chr(54740) zhao,chr(65) chr65 from dual; ZH C -- - 赵 A 3.CONCAT 连接两个字符串; SQL> select concat('010-','88888888')||'转23' 高乾竞电话 from dual; 高乾竞电话 ---------------- 010-88888888转23 4.INITCAP 返回字符串并将字符串的第一个字母变为大写; SQL> select initcap('smith') upp from dual; UPP ----- Smith 5.INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置; C1 被搜索的字符串 C2 希望搜索的字符串 I 搜索的开始位置,默认为1 J 出现的位置,默认为1 SQL> select instr('oracle traning','ra',1,2) instring from dual; INSTRING --------- 9 6.LENGTH 返回字符串的长度; SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from .nchar_tst; NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)) ------ ------------ ---------------- ------------ --------- -------------------- 高乾竞 3 北京市海锭区 6 9999.99 7 7.LOWER 返回字符串,并将所有的字符小写 SQL> select lower('AaBbCcDd')AaBbCcDd from dual; AABBCCDD -------- aabbccdd 8.UPPER 返回字符串,并将所有的字符大写 SQL> select upper('AaBbCcDd') upper from dual; UPPER -------- AABBCCDD 9.RPAD和LPAD(粘贴字符) RPAD 在列的右边粘贴字符 LPAD 在列的左边粘贴字符 SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual; LPAD(RPAD('GAO',1 ----------------- *******gao******* 不够字符则用*来填满 10.LTRIM和RTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual; LTRIM(RTRIM(' ------------- gao qian jing 11.SUBSTR(string,start,count) 取子字符串,从start开始,取count个 SQL> select substr('13088888888',3,8) from dual; SUBSTR(' -------- 08888888 12.REPLACE('string','s1','s2') string 希望被替换的字符或变量 s1 被替换的字符串 s2 要替换的字符串 SQL> select replace('he love you','he','i') from dual; REPLACE('HELOVEYOU','HE','I') ------------------------------ i love you 13.SOUNDEX 返回一个与给定的字符串读音相同的字符串 SQL> create table table1(xm varchar(8)); SQL> insert into table1 values('weather'); SQL> insert into table1 values('wether'); SQL> insert into table1 values('gao'); SQL> select xm from table1 where soundex(xm)=soundex('weather'); XM -------- weather wether 14.TRIM('s' from 'string') LEADING 剪掉前面的字符 TRAILING 剪掉后面的字符 如果不指定,默认为空格符 15.ABS 返回指定值的绝对值 SQL> select abs(100),abs(-100) from dual; ABS(100) ABS(-100) --------- --------- 100 100 16.ACOS 给出反余弦的值 SQL> select acos(-1) from dual; ACOS(-1) --------- 3.1415927 17.ASIN 给出反正弦的值 SQL> select asin(0.5) from dual; ASIN(0.5) --------- .52359878 18.ATAN 返回一个数字的反正切值 SQL> select atan(1) from dual; ATAN(1) --------- .78539816 19.CEIL 返回大于或等于给出数字的最小整数 SQL> select ceil(3.1415927) from dual; CEIL(3.1415927) --------------- 4 20.COS 返回一个给定数字的余弦 SQL> select cos(-3.1415927) from dual; COS(-3.1415927) --------------- -1 21.COSH 返回一个数字反余弦值 SQL> select cosh(20) from dual; COSH(20) --------- 242582598 22.EXP 返回一个数字e的n次方根 SQL> select exp(2),exp(1) from dual; EXP(2) EXP(1) -------- --------- 7.3890561 2.7182818 23.FLOOR 对给定的数字取整数 SQL> select floor(2345.67) from dual; FLOOR(2345.67) -------------- 2345 24.LN 返回一个数字的对数值 SQL> select ln(1),ln(2),ln(2.7182818) from dual; LN(1) LN(2) LN(2.7182818) --------- --------- ------------- 0 .69314718 .99999999 25.LOG(n1,n2) 返回一个以n1为底n2的对数 SQL> select log(2,1),log(2,4) from dual; LOG(2,1) LOG(2,4) --------- --------- 0 2 26.MOD(n1,n2) 返回一个n1除以n2的余数 SQL> select mod(10,3),mod(3,3),mod(2,3) from dual; MOD(10,3) MOD(3,3) MOD(2,3) --------- --------- --------- 1 0 2 27.POWER 返回n1的n2次方根 SQL> select power(2,10),power(3,3) from dual; POWER(2,10) POWER(3,3) ----------- ---------- 1024 27 28.ROUND和TRUNC 按照指定的精度进行舍入 SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual; ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5) ----------- ------------ ----------- ------------ 56 -55 55 -55 29.SIGN 取数字n的符号,大于0返回1,小于0返回-1,等于0返回0 SQL> select sign(123),sign(-100),sign(0) from dual; SIGN(123) SIGN(-100) SIGN(0) --------- ---------- --------- 1 -1 0 30.SIN 返回一个数字的正弦值 SQL> select sin(1.57079) from dual; SIN(1.57079) ------------ 1 31.SIGH 返回双曲正弦的值 SQL> select sin(20),sinh(20) from dual; SIN(20) SINH(20) --------- --------- .91294525 242582598 32.SQRT 返回数字n的根 SQL> select sqrt(64),sqrt(10) from dual; SQRT(64) SQRT(10) --------- --------- 8 3.1622777 33.TAN 返回数字的正切值 SQL> select tan(20),tan(10) from dual; TAN(20) TAN(10) --------- --------- 2.2371609 .64836083 34.TANH 返回数字n的双曲正切值 SQL> select tanh(20),tan(20) from dual; TANH(20) TAN(20) --------- --------- 1 2.2371609 35.TRUNC 按照指定的精度截取一个数 SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; TRUNC1 TRUNC(124.16666,2) --------- ------------------ 100 124.16 36.ADD_MONTHS 增加或减去月份 SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual; TO_CHA ------ 200002 SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual; TO_CHA ------ 199910 37.LAST_DAY 返回日期的最后一天 SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual; TO_CHAR(SY TO_CHAR((S ---------- ---------- 2004.05.09 2004.05.10 SQL> select last_day(sysdate) from dual; LAST_DAY(S ---------- 31-5月 -04 38.MONTHS_BETWEEN(date2,date1) 给出date2-date1的月份 SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual; MON_BETWEEN ----------- 9 SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual; MON_BETW --------- -60 39.NEW_TIME(date,'this','that') 给出在this时区=other时区的日期和时间 SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time 2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual; BJ_TIME LOS_ANGLES ------------------- ------------------- 2004.05.09 11:05:32 2004.05.09 18:05:32 40.NEXT_DAY(date,'day') 给出日期date和星期x之后计算下一个星期的日期 SQL> select next_day('18-5月-2001','星期五') next_day from dual; NEXT_DAY ---------- 25-5月 -01 41.SYSDATE 用来得到系统的当前日期 SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual; TO_CHAR(SYSDATE,' ----------------- 09-05-2004 星期日 trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒 SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh, 2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual; HH HHMM ------------------- ------------------- 2004.05.09 11:00:00 2004.05.09 11:17:00 42.CHARTOROWID 将字符数据类型转换为ROWID类型 SQL> select rowid,rowidtochar(rowid),ename from scott.emp; ROWID ROWIDTOCHAR(ROWID) ENAME ------------------ ------------------ ---------- AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES 43.CONVERT(c,dset,sset) 将源字符串 sset从一个语言字符集转换到另一个目的dset字符集 SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual; conver ------ strutz 44.HEXTORAW 将一个十六进制构成的字符串转换为二进制 45.RAWTOHEXT 将一个二进制构成的字符串转换为十六进制 46.ROWIDTOCHAR 将ROWID数据类型转换为字符类型 47.TO_CHAR(date,'format') SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2004/05/09 21:14:41 48.TO_DATE(string,'format') 将字符串转化为ORACLE中的一个日期 49.TO_MULTI_BYTE 将字符串中的单字节字符转化为多字节字符 SQL> select to_multi_byte('高') from dual; TO -- 高 50.TO_NUMBER 将给出的字符转换为数字 SQL> select to_number('1999') year from dual; YEAR --------- 1999 51.BFILENAME(dir,file) 指定一个外部二进制文件 SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif')); 52.CONVERT('x','desc','source') 将x字段或变量的源source转换为desc SQL> select sid,serial#,username,decode(command, 2 0,'none', 3 2,'insert', 4 3, 5 'select', 6 6,'update', 7 7,'delete', 8 8,'drop', 9 'other') cmd from v$session where type!='background'; SID SERIAL# USERNAME CMD --------- --------- ------------------------------ ------ 1 1 none 2 1 none 3 1 none 4 1 none 5 1 none 6 1 none 7 1275 none 8 1275 none 9 20 GAO select 10 40 GAO none 53.DUMP(s,fmt,start,length) DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值 SQL> col global_name for a30 SQL> col dump_string for a50 SQL> set lin 200 SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name; GLOBAL_NAME DUMP_STRING ------------------------------ -------------------------------------------------- ORACLE.WORLD Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D 54.EMPTY_BLOB()和EMPTY_CLOB() 这两个函数都是用来对大数据类型字段进行初始化操作的函数 55.GREATEST 返回一组表达式中的最大值,即比较字符的编码大小. SQL> select greatest('AA','AB','AC') from dual; GR -- AC SQL> select greatest('啊','安','天') from dual; GR -- 天 56.LEAST 返回一组表达式中的最小值 SQL> select least('啊','安','天') from dual; LE -- 啊 57.UID 返回标识当前用户的唯一整数 SQL> show user USER 为"GAO" SQL> select username,user_id from dba_users where user_id=uid; USERNAME USER_ID ------------------------------ --------- GAO 25 58.USER 返回当前用户的名字 SQL> select user from dual; USER ------------------------------ GAO 59.USEREVN 返回当前用户环境的信息,opt可以是: ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE ISDBA 查看当前用户是否是DBA如果是则返回true SQL> select userenv('isdba') from dual; USEREN ------ FALSE SQL> select userenv('isdba') from dual; USEREN ------ TRUE SESSION 返回会话标志 SQL> select userenv('sessionid') from dual; USERENV('SESSIONID') -------------------- 152 ENTRYID 返回会话人口标志 SQL> select userenv('entryid') from dual; USERENV('ENTRYID') ------------------ 0 INSTANCE 返回当前INSTANCE的标志 SQL> select userenv('instance') from dual; USERENV('INSTANCE') ------------------- 1 LANGUAGE 返回当前环境变量 SQL> select userenv('language') from dual; USERENV('LANGUAGE') ---------------------------------------------------- SIMPLIFIED CHINESE_CHINA.ZHS16GBK LANG 返回当前环境的语言的缩写 SQL> select userenv('lang') from dual; USERENV('LANG') ---------------------------------------------------- ZHS TERMINAL 返回用户的终端或机器的标志 SQL> select userenv('terminal') from dual; USERENV('TERMINA ---------------- GAO VSIZE(X) 返回X的大小(字节)数 SQL> select vsize(user),user from dual; VSIZE(USER) USER ----------- ------------------------------ 6 SYSTEM 60.AVG(DISTINCT|ALL) all表示对所有的值求平均值,distinct只对不同的值求平均值 SQLWKS> create table table3(xm varchar(8),sal number(7,2)); 语句已处理。 SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('gao',1111.11); SQLWKS> insert into table3 values('zhu',5555.55); SQLWKS> commit; SQL> select avg(distinct sal) from gao.table3; AVG(DISTINCTSAL) ---------------- 3333.33 SQL> select avg(all sal) from gao.table3; AVG(ALLSAL) ----------- 2592.59 61.MAX(DISTINCT|ALL) 求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次 SQL> select max(distinct sal) from scott.emp; MAX(DISTINCTSAL) ---------------- 5000 62.MIN(DISTINCT|ALL) 求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次 SQL> select min(all sal) from gao.table3; MIN(ALLSAL) ----------- 1111.11 63.STDDEV(distinct|all) 求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差 SQL> select stddev(sal) from scott.emp; STDDEV(SAL) ----------- 1182.5032 SQL> select stddev(distinct sal) from scott.emp; STDDEV(DISTINCTSAL) ------------------- 1229.951 64.VARIANCE(DISTINCT|ALL) 求协方差 SQL> select variance(sal) from scott.emp; VARIANCE(SAL) ------------- 1398313.9 65.GROUP BY 主要用来对一组数进行统计 SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 10 3 8750 20 5 10875 30 6 9400 66.HAVING 对分组统计再加限制条件 SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having nt(*)>=5; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 20 5 10875 30 6 9400 SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by tno ; DEPTNO COUNT(*) SUM(SAL) --------- --------- --------- 20 5 10875 30 6 9400 67.ORDER BY 用于对查询到的结果进行排序输出 SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc; DEPTNO ENAME SAL --------- ---------- --------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300 20 SCOTT 3000 20 FORD 3000 20 JONES 2975 20 ADAMS 1100 20 SMITH 800 30 BLAKE 2850 30 ALLEN 1600 30 TURNER 1500 30 WARD 1250 30 MARTIN 1250 30 JAMES 950

当我们需要通过xml格式处理sql语句时，经常会用到< ，<=，>，>=等符号，但是很容易引起xml格式的错误，这样会导致后台将xml字符串转换为xml文档时报错，从而导致程序错误。 这样的问题在iBatiS中或者自定义的xml处理sql的程序中经常需要我们来处理。其实很简单，我们只需作如下替换即可避免上述的错误： 原符号   <    <=    >    >=     &      ‘       “ 替换符号 < <= > >= & ' 错误的xml格式： <?xml version=1.0 encoding=GBK?> <queryForm token=

Linux运维-运维课程d2-MySQL基本SQL语句（下）-10-WHERE子句之等于大于小于不等于

经典sql练习，链接给出题型： https://www.jobui.com/mianshiti/it/shujuku/5838/ 本意无需资源分数，但csdn变坏了，没有0分可以选，没有分数的，发qq邮箱索要1943680751

SQL查询二 2 通过本实验使学生掌握多表查询、子查询以及基本数据操作 二、实验内容 使用实验一建立的银行贷款数据库和表，完成以下查询。 1-4是多表查询和子查询，5-11是数据操作 查询经济性质为“国营”的法人在上海的银行贷款的信息，列出法人代码、银行代码和贷款日期，分别用多表连接和子查询两种方式实现。 查询在“建设银行上海分行”贷过款的法人名称，分别用多表连接和子查询两种方式实现。 查询在“工商银行北京A支行”贷款金额前三名（包括并列的情况）的法人的法人代码、法人名称和经济性质，分别用多表连接和子查询两种方式实现。 查询在“工商银行北京B支行”贷款、且贷款金额高于此银行的平均贷款金额的法人代码、贷款日期和贷款金额。 在银行表中插入如下数据：银行代码号为：B321B，银行名称为：建设银行上海B分行，电话为空值。 在法人表中插入如下数据：法人代码号为：E11，法人名称为：新法人，注册资金为：2350万元，经济性质使用默认值。 删除银行编号为“B321B”的银行信息。 删除2000年之前一次贷款金额最小的贷款记录。 删除从贷款日期到当前日期天数超过10年的贷款记录。 删除法人名称为“爱贝乐玩具有限公司”且贷款金额小于10万元的贷款记录，分别用子查询和多表连接两种方式实现。 将经济性质为“私营”的法人在“工商银行上海支行”贷款的所有贷款金额加5万元，分别用子查询和多表连接两种方式实现。 使用实验一建立的学生数据库和表，完成以下查询 12-15是多表查询和子查询，16-20是数据操作 查询计算机系年龄大于总平均年龄的学生的姓名和年龄。 查询计算机系年龄大于计算机系平均年龄的学生的姓名和年龄。 查询计算机系考试成绩小于总平均分的学生的学号、姓名。 将考试成绩最低的并且不及格学生的最低修课成绩改为60。 将数据库基础考试成绩最低的且成绩为不及格学生的数据库考试成绩改为60。 删除计算机系“计算机网络”课程的全部选课记录。 删除vb考试成绩最低的两个学生的vb考试记录。 对数据库考试成绩进行如下修改：如果成绩低于60分，则提高10％；如果成绩在60到80之间，则增加6％；如果成绩在80到95之间则提高4％，其他情况不提高。 对学分进行如下修改：如果是第1到第3学期开始的课程，则学分增加1分；如果是第4到第6学期开设的课程，学分增加2分，其他学期开始的课程学分增加3分。 以下查询必须用子查询完成： 查询男生年龄最大的学生的姓名和所在系。 查询选修了‘数据库基础’的学生的姓名、所在系。s 选修了第6学期开始的课程的学生的学号、姓名和所在系。 查询男生所修的课程的课程名。 查询年龄最小的学生所选的课程名。 三、实验报告 将实验结果反映在实验报告中，并对实验中遇到的问题及解决方案、进行整理、分析总结，提出实验结论或自己的看法。

1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query Language)简称SQL，是一种关系数据库查询语言，用于存取数据以及查询、更新和管理关系数据库系统。 1.2 语句结构 1.2.1 数据查询语言（DQL） 对数据库进行的信息查询，select。 1.2.2 数据操作语言（DML） 用于操作关系型数据库对象内部的数据，insert、update、delete。 1.2.3 数据定义语言（DDL） 用来建立及定义数据表、字段以及索引等数据库结构，create、alter、drop 。 1.2.4 数据控制语言（DCL） 用于控制对数据库里数据的访问，通常用于创建与用户访问相关的对象以及控制用户的权限，grant、revoke（撤销）。 1.2.5 事务控制命令（TPL） 用于管理数据库事务，commit、rollback、savepoint（在一组事务里创建标记点以用于回退）。 1.3 表的构成 1.3.1 字段 字段是表里的一列，用于保持每条记录的特定信息 1.3.2 记录 记录，也被成为一行数据，是表里的每一行 1.4 完整性的约束条件 1.4.1 实体完整性 关系模型的实体完整性在create table中用primary key约束实现，primary key约束用于定义主键，它保证主键的唯一性和非空性。 1.4.2 参照完整性 关系模型的参照完整性可以通过在create table中用foreign key (<外键>) references <被参照表名> (<与外键对应的主键名>)进行约束定义。 1.4.3 用户定义完整心 在create table语句中可以根据应用要求，定义属性以及元组上的约束。 常见的用户定义的完整性约束有： not null或null约束。 unique约束：唯一性约束。 default约束：默认值约束。 check约束：检查约束，check约束通过约束条件表达式设置列值应该满足的条件。 1.5 范式 1.5.1 第一范式 1.5.1.1 规范 无重复的列，确保每列保持原子性，即数据库表中的所有字段值都是不可分解的原子值。 1.5.1.2 举例 姓名 年龄 联系电话 地址 省 市 详细地址 1.5.2 第二范式 1.5.2.1 规范 属性完全依赖于主键，确保表中每列都与主键相关。 1.5.2.2 举例 订单表 订单Id 商品Id 总金额 商品名称 001 1 10 苹果 001 2 10 橘子 联合主键订单Id、商品Id => 商品表 商品Id 商品名称 单价 订单表 订单Id 总金额 1.5.3 第三范式 1.5.3.1 规范 属性不依赖于其它非主属性，确保数据表中的每一列数据都和主键直接相关，而不能间接相关，即要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 1.5.3.2 举例 党员表 党员Id 党员姓名 组织Code 符合3NF 党员表 党员Id 党员姓名 组织名称 不符合3NF 组织表 组织Code 组织名称 1.6 外连接 1.6.1 准备 create table student_A（ uuid varchar2(32), name varchar2(100)); create table student_B（ uuid varchar2(32), name varchar2(100)); insert into student_A values('1','小黄'); insert into student_A values('2','小黑'); insert into student_A values('3','小红'); insert into student_B values('1','大黄'); insert into student_B values('2','大黑'); insert into student_B values('4','大红'); insert into student_B values('4','大紫'); 1.6.2 左连接（left join） 1.6.2.1 说明 查询指定的左表的所有行，而不仅仅是联接列所匹配的行；如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。 1.6.2.2 语法 select A.*,B.* from student_A A left join student_B B on A.Uuid = B.Uuid; 1.6.2.3 结果 1.6.2.4 （+）表示 select A.*,B.* from student_A A,student_B B where A.Uuid = B.Uuid(+) 1.6.3 右连接（right join） 1.6.3.1 说明 查询指定的右表的所有行，而不仅仅是联接列所匹配的行；如果右表的某行在左表中没有匹配行，则在相关联的结果集行中左表的所有选择列表列均为空值。 1.6.3.2 语法 select A.*,B.* from student_A A right join student_B B on A.Uuid = B.Uuid; 1.6.3.3 结果 1.6.3.4 （+）表示 select A.*,B.* from student_A A,student_B B where A.Uuid(+) = B.Uuid 1.6.4 全外连接（full outer join） 1.6.4.1 说明 完整外部联接返回左表和右表中的所有行；当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值；如果表之间有匹配行，则整个结果集行包含基表的数据值。 1.6.4.2 语法 select A.*, B.* from student_A A full outer join student_B B on A.Uuid = B.Uuid; 1.6.4.3 结果 1.6.4.4 全外连接不支持（+）写法 1.6.5 （+） + 表示补充，即哪个表有加号，这个表就是匹配表。 1.7 运算符 1.7.1 比较 =、>,<,>=,<=,!=,<>, 1.7.2 确定范围 between and 、not between and 1.7.3 确定集合 in、not in 1.7.4 字符匹配 like（“%”匹配任何长度，“_”匹配一个字符） 1.7.5 转义字符 1.7.5.1 常规转义字符 “\”转义字符,“\%”则表示单纯的字符“%” 1.7.5.2 escape escape关键字经常用于使某些特殊字符，如通配符：'%','_'转义为它们原来的字符的意义，被定义的转义字符通常使用'\',但是也可以使用其他的符号。 select * from Student_a where name like '%$%%' escape '$'; 1.7.6 空值 is null、is not null 1.7.7 集合查询 union（并）、intersect（交）、minus（差） 1.7.8 多重条件 and、or、not 1.7.9 对查询结果分组 <group by 列名> 1.7.10 分组筛选条件 [having <条件表达式>] 1.7.11 字符串拼接 select 'A' || 'B' from dual; // || 拼接 1.8 函数 1.8.1 聚集函数 count、sum、avg、max、min 1.8.2 case when 1.8.2.1 语法 select t.uuid, t.score, case when t.score > 90 then '优秀' when t.score > 60 then '及格' else '不及格' end from exam_user_exam t 1.8.3 decode 1.8.3.1 语法 select decode(x,1,'x is 1', 2 , 'x is 2','others') from dual 1.8.3.2 说明 当x等于1时，则返回‘x is 1’，当x等于2时，则返回‘x is 2’，否则，返回‘others’。 1.8.4 nulls first（nulls last）排序 1.8.4.1 语法 select * from dy_info t order by t.degree nulls first 1.8.4.2 说明 控制null显示行位置 1.8.5 Nvl 1.8.5.1 语法 select nvl(t.sap,'空') from dy_info t; 1.8.5.2 说明 如果sap号为空，则返回‘空’，否则返回sap号。 1.8.6 递归查询 1.8.6.1 语法 select t.* from g_organ t start with t.organcode = '080' connect by prior t.parentcode = t.organcode; //递归查询父节点 select t.* from g_organ t start with t.organcode = '080' connect by t.parentcode = prior t.organcode; //递归查询子节点 1.8.7 union 和 union all 1.8.7.1 语法 select * from dual union all select * from dual 1.8.7.2 说明 union 会对查询数据进行去重并排序，union all只是简单的将两个结果合并。 1.8.8 wm_concat 1.8.8.1 语法 select wm_concat(t.role_name) from g_role t where t.role_name like '%书记%' 1.8.8.2 说明 拼接字符串，结果为：‘党工委副书记,总支副书记,党支部书记,党委书记,党委副书记,党总支书记,党工委书记,党总支副书记,党支部副书记’ 1.8.9 相似度 1.8.9.1 语法 select utl_match.edit_distance_similarity('aaaaa','bbaaaa') from dual; 1.8.10 去格式 1.8.10.1 oracle正则表达式：去除<></>格式 select REGEXP_REPLACE(title,'<[^>]*>','') title from exam_question 1.8.11 rank() over (partition by …) 1.8.11.1 语法 select organcode,score,ranknum from ( select t.organcode, t.score, rank() over (partition by t.organcode order by t.score desc) ranknum from exam_user_exam t) where ranknum < 4 1.8.11.2 说明 获取每个组织，考试成绩前三名。 1.9 存储过程 1.9.1 定义 存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 1.9.2 准备 create table t_user ( username varchar2(20), password varchar2(20) ); create table t_user_temp ( username varchar2(20), password varchar2(20) ); insert into t_user(username,password) values('小王','1111'); insert into t_user(username,password) values('小李','1111'); 1.9.3 创建存储过程 create or replace procedure adduser as begin insert into t_user_temp(username,password) select username,password from t_user t where t.username = '小李'; end adduser; 1.9.4 执行 begin adduser; end; 1.9.5 验证 select * from t_user; select * from t_user_temp 2 SQL深入 2.1 常用 2.1.1 表空间 2.1.1.1 创建表空间 create tablespace TS_DJY datafile 'd:/software/oracle/tablespace/ts_djy.dat' size 1024M autoextend on next 100M maxsize 2048M; 2.1.1.2 指定用户表空间 alter user cssdj default tablespace TS_DJY; 2.1.1.3 指定表的表空间 create table t_student( uuid varchar2(32) )tablespace TS_DJY; 2.1.2 自增sequence 2.1.2.1 创建 create sequence seq_student_uuid minvalue 1 maxvalue 999999999999999 start with 1 increment by 1 nocache; 2.1.2.2 使用 select seq_student_uuid.nextval from dual; //获取下一个 select seq_student_uuid.currval from dual; //获取当前 2.1.3 批量插入 2.1.3.1 查询结果批量插入 insert into tablea(cola,colb) select cola,colb from tableb; 2.1.3.2 创建相同的表结构并插入数据（备份表数据） create tab_new as select * from tab_old; 2.1.3.3 创建相同的表结构不插入数据 create tab_new as select * from tab_old where rownum=0; 2.1.4 伪列伪表 2.1.4.1 伪列 伪列不是表的真实列，但是你可以象使用真实列一样使用伪列，常用伪列：rowid行的绝对物理编号，每一行是唯一的。rownum序号，返回查询结果的每行序号。系统时间sysdate，返回系统当前时间。 2.1.4.2 伪表 select * from dual; 2.1.5 系统表 2.1.5.1 user_tables 当前用户表信息 2.1.5.2 user_tab_columns 当前用户表所有列信息，搜索列所在的表： select * from user_tab_columns t where t.column_name like '%ORGANCODE%'; 2.1.5.3 user_tablespaces 当前用户表空间 2.1.5.4 dba_users 数据库所有用户 2.1.5.5 dba_tables 数据库所有表 2.1.5.6 dba_tablespaces 数据库所有表空间 2.1.6 锁表解锁 2.1.6.1 查看锁表信息 select l.OBJECT_ID,s.SID,s.SERIAL#,s.USERNAME, s.MACHINE from V$locked_Object l,V$session s where l.SESSION_ID = s.SID; 2.1.6.2 解锁 alter system kill session 'sid,serial#'; 2.1.7 备份与恢复 2.1.7.1 导出 导出用户： exp cssdj/cssdj@zr owner=(cssdj,cssdj_zsy) file=d:/cssdj.dmp log=d:/cssdj.log 导出用户表： exp cssdj/cssdj@zr tables=(g_dict,g_dict_item) file=d:/cssdj.dmp log=d:/cssdl.log 2.1.7.2 导入 imp cssdj/cssdj@zr fromuser=cssdj_zsy touser=cssdj file=d:/cssdj.cmp log=d:/cssdj.log 2.1.7.3 数据泵 2.1.8 Dblink 2.1.8.1 创建 create database link orcllink connect to cssdj identified by cssdj using '(DESCRIPTION = (ADDRESS_LIST= (ADDRESS=(PROTOCOL = TCP)(HOST = 219.239.110.65)(PORT = 1521))) (CONNECT_DATA=(SID = orcl)))'; 2.1.8.2 删除 drop database link orcllink; 2.1.8.3 使用 select * from dy_info@orcllink; 2.2 Oracle与Mysql差异 2.2.1 Group by 2.2.1.1 Oracle select后面出现的列,如果没有使用集合函数,必须出现在group by 中。 select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select sno,sname,sum(grade) from student group by sno; //非法写法 select sno,min(sname),sum(grade) from student group by sno; //建议用这种写法,效率高些 2.2.1.2 Mysql select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select sno,sname,sum(grade) from student group by sno; //合法写法 2.2.2 分页 2.2.2.1 Oracle 使用rownum来表明分页位置，而且rownum只能小于某值，不能大于某值，故而rownum和where联用才能完成数据范围的控制 2.2.2.2 Mysql mysql的分页可以用limit startNum,pageNum 2.3 了解 2.3.1 事务 2.3.1.1 Rollback start transaction; --开始事务 insert into g_dict values('test','测试','1',''); --执行数据操作语言（DML） select * from g_dict where code = 'test'; --可以查看是否执行正确 rollback; --错误执行rollback操作 commit; --正确执行commit操作 2.3.1.2 Savepoint start transaction; --开始事务 insert into g_dict values('t1','测试','1',''); --执行数据操作语言（DML） savepoint pointA; insert into g_dict values('t2','测试','1',''); --执行数据操作语言（DML） select * from g_dict where code = 'test33'; --可以查看是否执行正确 rollback to savepoint pointA; 2.3.2 利用执行计划评估SQL语句的性能 2.3.2.1 工具 在PL/SQL Developer中写好一段SQL代码后，按F5，PL/SQL Developer会自动打开执行计划窗口，显示该SQL的执行计划。 2.3.2.2 查看总COST，获得资源耗费的总体印象 一般而言，执行计划第一行所对应的COST(即成本耗费)值，反应了运行这段SQL的总体估计成本，单看这个总成本没有实际意义，但可以拿它与相同逻辑不同执行计划的SQL的总体COST进行比较，通常COST低的执行计划要好一些。 2.3.2.3 了解执行计划的执行步骤 按照从左至右，从上至下的方法，了解执行计划的执行步骤； 执行计划按照层次逐步缩进，从左至右看，缩进最多的那一步，最先执行，如果缩进量相同，则按照从上而下的方法判断执行顺序，可粗略认为上面的步骤优先执行。每一个执行步骤都有对应的COST,可从单步COST的高低，以及单步的估计结果集（对应ROWS/基数），来分析表的访问方式，连接顺序以及连接方式是否合理。 2.3.2.4 分析表的访问方式 表的访问方式主要是两种：全表扫描（TABLE ACCESS FULL）和索引扫描(INDEX SCAN)，如果表上存在选择性很好的索引，却走了全表扫描，而且是大表的全表扫描，就说明表的访问方式可能存在问题；若大表上没有合适的索引而走了全表扫描，就需要分析能否建立索引，或者是否能选择更合适的表连接方式和连接顺序以提高效率。 2.3.2.5 分析表的连接方式和连接顺序 表的连接顺序：就是以哪张表作为驱动表来连接其他表的先后访问顺序。 表的连接方式：简单来讲，就是两个表获得满足条件的数据时的连接过程。主要有三种表连接方式，嵌套循环（NESTED LOOPS）、哈希连接（HASH JOIN）和排序-合并连接（SORT MERGE JOIN）。我们常见得是嵌套循环和哈希连接。 嵌套循环：最适用也是最简单的连接方式。类似于用两层循环处理两个游标，外层游标称作驱动表，Oracle检索驱动表的数据，一条一条的代入内层游标，查找满足WHERE条件的所有数据，因此内层游标表中可用索引的选择性越好，嵌套循环连接的性能就越高。 哈希连接：先将驱动表的数据按照条件字段以散列的方式放入内存，然后在内存中匹配满足条件的行。哈希连接需要有合适的内存，而且必须在CBO优化模式下，连接两表的WHERE条件有等号的情况下才可以使用。哈希连接在表的数据量较大，表中没有合适的索引可用时比嵌套循环的效率要高。 2.3.3 优化器 Oracle优化器分为基于规则的优化器（RBO）和基于代价的优化器（CBO）。 2.3.3.1 规则的优化器（RBO） RBO的优化方式，依赖于一套严格的语法规则，只要按照规则写出的语句，不管数据表和索引的内容是否发生变化，不会影响PL/SQL语句的"执行计划"。 2.3.3.2 基于代价的优化器（CBO） CBO计算各种可能"执行计划"的"代价"，即cost，从中选用cost最低的方案，作为实际运行方案。各"执行计划"的cost的计算根据，依赖于数据表中数据的统计分布，ORACLE数据库本身对该统计分布是不清楚的，须要分析表和相关的索引，才能搜集到CBO所需的数据。 2.3.4 表分析analysis 2.3.4.1 说明 analyze table，一般可以指定分析表、所有字段、所有索引字段、所有索引，若不指定则全部都分析。 2.3.4.2 表分析 analyze table dy_info compute statistics; 2.3.4.3 删除分析数据 analyze table dy_info delete statistics; 2.3.5 oracle添加强制索引 如果使用的是CBO的话，可能SQL不执行索引，则可以添加强制索引执行索引。 2.3.5.1 语法 /*+index(tablename indexname)*/ 2.3.5.2 举例 select /*+index(t INDEX_SAP)*/* from dy_info t where t.sap = 'T6000890'

说明： 1将数据库备份在本地的还原进行修复 *注意一定做好备份工作 *本地修复性能较好 *没有多台机器，建议使用虚拟机vmware 2此程序运行需要dotnet framework 2.0 支持 sql2000 sql2005 3设置数据库连接，需要使用sa用户，修复过程中使用正则表达，需要管理员权限。 4 ntext数据类型的字符串不能再直接替换，请选择ntext修复,通过设置陪需分段修复，建议之小于5000 分批处理每次不超过1000

　我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子: ... where not ...

A表中的某列数据和B表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴 自连接 数据都来自一张表,所以在from字句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名. 本质...

>= 大于等于 小于 小于等于 2、SQL 比较运算符 between…and… ：在两者之间。(BETWEEN 小值 AND 大值) 如：select last_name,salary from s_emp where salary between 1000 and 1500； --工资1000到1500的...