java达内数据库oracle exday掌握连接服务器命令telnet以及使用Sqlplus工具进

1掌握连接服务器命令net,以及使用Sqlplus工具进入数据2create3掌握插入数据语句4了解常用的Sqlplus命令*5*6*78注 练习1：把用户数据存入数据库的表中,并作查 练习2：建部门表(Dept)和员工表(Emp 练习3：学习查询语句 distinct关键 练习4：学习查询语句 大小写问题upper函数&&lower函 betweenand关键 in(列表 模糊匹配like isnull&&isnot 1User_xxx接接 输入用户名、(不显示SqlPlusOracle如果提示SQLOracleSQL>createtable password

--表 SQL>insertintoSQL>insertintoSQL>SQL>selectname fromuser_xxx;SQL>selectSQL>selectname fromwhereid= andpassword='1234'2DeptEmpDeptdroptabledept_xxxcreatetable number(2) char(20),locationchar(20))insertintodept_xxxvalues(10,'developer','beijing');insertintodept_xxxvalues(20,'account','shanghai')insertintodept_xxxvalues(30,'sales','guangzhou')insertintodept_xxxvalues(40,'operations','tianjin')commitselect*fromdept_xxxEmpSQL>SQL>createtable )SQL>insertintoemp_xxx 10000,2000,'12-MAR-10',1005,SQL>insertintoemp_xxx 8000,1000,'01-APR-11',1001,SQL>insertintoemp_xxx 9000,1000,'11-APR-10',1001,SQL>insertintoemp_xxx5000,null,'01-JAN-11',1001,SQL>insertintoemp_xxx 15000,null,'15-MAY-08',null,SQL>insertintoemp_xxx 5000,400,'01-FEB-09',1005,SQL>insertintoemp_xxx3000,500,'01-FEB-09',1006,SQL>insertintoemp_xxx 5000,500,'1-MAY-09',1005,SQL>insertintoemp_xxx 4000,null,'20-FEB-09',1008,SQL>insertintoemp_xxx 4500,500,'10-MAY-09',1008,SQL>setlinesize150SQL>colempnofor9999SQL>colmgrfor9999SQL>coldeptnofor99SQL>colsalaryfor

empno4mgr4deptno2salarySQL>select*fromdept_xxx;SQL>select*fromemp_xxx3：学习查询语句SQL>SQL>selectename,salary,salary*12fromemp_xxx nvlSQL>selectSQL>selectenamesalarybonussalary+bonusmonth_salfromemp_xxx; SQL>SQL>selectename,salary,bonus,salary+nvl(bonus,0)month_salfromemp_xxx;bonusnullSQL>insertintovalues(1011SQL>insertintovalues(1011, ',null,null,null,null,null,null)SQL>SQL>insertintoemp_xxx(empno,ename)values(1011,' ');7emp_xxx'nopositionSQL>SQL>selectename,nvl(job,'noposition')fromemp_xxx8emp_xxx20111010SQL>SQL>selectename,nvl(hiredate,'10-OCT-11')fromemp_xxx表【案例9】表emp_YYY为SQL>SQL>createtableemp_xxxasselect*fromemp_yyydistinctSQL>SQL>selectdistinctjobfromemp_xxxSQL>SQL>selectdistinctdeptnofromemp_xxx4：学习查询语句SQL>SQL>select*fromemp_xxxwheresalary>10000SQL>SQL>select*fromemp_xxxwherejob= yst'ystSQL>SQL>select*fromemp_xxxwherelower(job)= yst'SQL>SQL>select*fromemp_xxxwhereupper(job)= YST'betweenandSQL>select*fromSQL>select*fromemp_xxxwheresalarybetween5000and10000SQL>SQL>select*fromemp_xxxwheresalary>=5000andsalary<=10000SQL>SQL>select*fromwherehiredatebetween'01-JAN-11'and'31-DEC-11'01-JAN-1131-DEC-SQL>select*fromwherejobin('Manager'SQL>select*fromwherejobin('Manager', yst')SQL>SQL>select*fromwherejob='Manager'orjob= yst'模糊匹配likeSQL>SQL>select*fromemp_xxxwherelower(job)like'%sales%'SQL>select*fromemp_xxxwherejoblike'_a%'21EMPSQL>selectcount(*)fromuser_tableswheretable_namelike'%EMP%'22S_'SQL>selectcount(*)fromuser_tableswheretable_namelike'S\_%'escapeisnull&&isnot【案例SQL>SQL>select*fromemp_xxxwhereb