--部门信息表 create table departments( department_id number(4), --部门编号 department_name varchar2(30), --部门名称 manager_id number(6), --管理者编号 location_id number(4) --部门位置编号 ) --员工信息表 create table employees( employee_id number(6), --员工编号 first_name varchar2(20), --员工姓 last_name varchar2(25), --员工名 email varchar2(25), --mail地址 phone_number varchar2(20), --电话 hire_date date, --雇佣时间 job_id varchar2(10), --职务 salary number(8,2), --月薪 commission_pct number(2,2), --奖金的百分比 manager_id number(6), --管理者编号 department_id number(4) --部门编号 ) --部门地理位置表 create table locations( location_id number(4), --地区编号 street_address varchar2(40), --街道名 postal_code varchar2(12), --邮编 city varchar2(30), --城市 state_province varchar2(25), --省市 country_id char(2) --国家编号 ) --薪水等级表 create table JOB_GRADES( grade_level char(2), --薪水级别 lowest_sal number(8,2), --最低限 highest_sal number(10,2) --最高限 ) --*************************************************************************************************** --创建表的语法 表是数据存储的基本单元。表(table)是关系数据库操作的基本对象,表是由行和列组成的, 每一列对应一个字段,每一行对应一条记录 建表语法: create table table_name ( column_name1 column_properties constraint_definition, column_name2 column_properties constraint_definition, …………..注意最后一列没有逗号。 ) --**************************************************************************************************** --oralce中常用的数据类型 * char(size) 定长字符,≤2000个字节 * varchar2(size) 变长字符,≤4000个字节 * date 日期数据,默认的格式是dd-mm-yy:如11-6月-06 * timestamp 日期数据,要比date数据更确切,包含时分秒。 * integer 整型数据 * number[(s,p)] 数字型,可存放实型和整型 ,精度(p)和范围(s) * s:整数部分的位数+小数部分的位数 * p:小数部分的位数 number(4) --最大值9999 number(4,2) --最大值99.99 number(3,0) --最大值999 number(3,1) --最大值99.9 --mysql中 double(s.p) double(3,1) --最大值99.9 --sqlserver numeric(4,2) * long 可变字符,≤2GB个字符 * float,real 是NUMBER的子类型 * blob 存放图形、声音和影像,大二进制对象,≤4GB --blob * clob 存放大文本文件,≤4GB --text --创建表test 包含(id number(4),name varchar2(12),sex char(6), birthday date)字段 create table test ( id number(4), name varchar2(12), sex char(6), birthday date ) --往test表中插入数据 insert into test(id,name,sex,birthday) values(1,'张无忌','男',null); SELECT id,sex FROM test; --查看表的结构 在命令窗口执行 desc test; --查看当前用户下哪些表 select * from user_tables --**************************************************************************************************** --select语句 --语法结构: SELECT 选择查询列表 FROM 提供数据源(表、视图或其他的数据源) 如果为 * 和创建表时的顺序一致。 可以自己调整顺序,在select后边加上要查询的列名 --select中的算术运算符 --查询员工姓 员工名,月工资,年薪 select first_name,last_name,salary,salary*12 from employees --运算符的优先级(比对) --查询员工姓 员工名,月工资,年薪 select first_name,last_name,salary,salary*12+100 from employees select first_name,last_name,salary,salary*(12+100) from employees --字符串的连接操作符 || 1、将列或字符与其它列连结 2、用双竖线表示 (||) 3、产生的结果列是另一个字符表达式 --查询员工的姓名 select first_name||last_name from employees --查询 "员工的姓名" is a "job_id" select first_name||last_name|| ' is a '|| job_id from employees --**************************************************************************************************** --空值 1、空值是指不可用、未分配的值,也就是没有值。 2、空值不等于零或空格 3、任意类型都可以支持空值,也就是说任何类型的字段都可以允许空值 作为值的存在 注意: * 包括空值的任何算术表达式都等于空 * 包括空值的连接表达式等于与空字符串连接,也就是原来的字符串 --查询 员工的姓名 月薪 年工资 select first_name||last_name,salary,salary*12 from employees --空值的算数运算 --查询 员工的姓名 月薪 ,提成% 月收入 select first_name||last_name,salary,commission_pct,salary*(1+commission_pct) from employees --空值的连接运算 --查询 员工的姓 员工的名 员工的姓名 月薪 select first_name,last_name,first_name||last_name,salary from employees --**************************************************************************************************** --定义字段的别名 --语法格式: 1、改变列的标题头 2、用于表示计算结果的含义 3、作为列的别名 4、如果别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都需加双引号 --定义字段别名改变列标题 --查询 first_name 姓名,salary 月薪,salary*12 年薪 select first_name 姓名,salary 月薪,salary*12 年薪 from employees --强制大小写,"" select first_name "Name",salary 月薪,salary*12 年薪 from employees --别名存在特殊字符 ,例如空格(first_name 姓 名) --查询 first_name 姓 名,salary 月薪,salary*12 年薪 select first_name "姓 名",salary "月 薪",salary*12 年薪 from employees --**************************************************************************************************** --文本字符串 1、文本字符串是一个包括在SELECT查询列表中的字符,表达式,或数字 2、日期和字符型文本字符串必须用单引号扩起来 3、每返回一条记录文本字符串被显示一次 --输出结果如下:查询员工姓名,职位 --诸葛亮 is a 人力资源经理 --欧阳 is a 保安部经理 select first_name||last_name|| 'is a' ||job_id from employees --**************************************************************************************************** --重复记录(distinct) 使用DISTINCT关键字可从查询结果中清除重复行 --全部输出department_id select department_id from employees --字段不重复 (distinct 后面的字段不重复) select distinct department_id from employees --行不重复 (用 * 表示 表中所有的字段都不重复) select distinct * from employees --distinct作用的是后面所有字段的组合 --查询job_id,department_id select distinct job_id,department_id from employees --**************************************************************************************************** --带限制条件的查询 --使用where子句限定返回的记录 --语法格式: SELECT [DISTINCT] {*, column [alias], ...} FROM table [WHERE condition(s)]; WHERE子句在 FROM 子句后 --查询员工的姓名 ,工资 ,职位 ,部门id ,条件是部门id=10 select first_name||last_name,salary,job_id,department_id from employees where department_id=10 --查询员工的详细信息 条件是 first_name 的值为Mike (表中的数据是区分大小写的) select * from employees where first_name='Mike' --dual是虚表,里边的数据没有意义,一般dual表被用于计算临时数据 --获取当前的日期 select sysdate from dual --使用dual执行计算 select 6*7 from dual --使用比较运算符 --查询工资小2000的所有员工信息 select * from employees where salary<2000 --其它的比较运算符 --between ? and ? 在两者之间(包含开始和结尾) --查询工资在3000-5000之间的所有员工信息 select * from employees where salary between 3000 and 5000 --使用IN运算符获得匹配列表值的记录,在IN操作符后跟着一个值的列表,可以应用日期,字符串数据类型 --in 后面是匹配的列表 in(?,?,?,?........) --查询employee_id 是 10001 10004 10007的员工信息 select * from employees where employee_id in(10001,10004,10007) --查询job_id 是 人力经理,办公室主任 秘书 的员工信息 select * from employees where job_id in('人力经理','办公室主任','秘书') --**************************************************************************************************** --使用like元素符 使用LIKE运算符执行通配查询 * 查询条件可包含文字字符或数字 * (%) 可表示零或多个字符 * ( _ ) 可表示一个字符 --查询first_name中含有a的员工信息 select * from employees where first_name like '%a%' --查询first_name中第三个字母是a的员工信息 select * from employees where first_name like '__a%' --查询员工姓名中含有%的员工信息 --escape '\' 表示 '\' 后面的字符是要查找的字符串中的一部分 select * from employees where first_name like '%\%%' escape '\' --查询员工姓名中第二个有%的员工信息 select * from employees where first_name like '_\%%' escape '\' --查询员工姓名中first_name=qin%k_g(第四个字符是% 第六个字符是_)员工信息 ddd%a_fff select * from employees where first_name like '___\%_\_%' escape '\' --**************************************************************************************************** --对于空值的等值比较不能用“=”,必需用IS NULL(IS NOT NUll) --查询job_id 不为null的员工信息 select * from employees where job_id is not null --**************************************************************************************************** --逻辑运算符 --查询departmemt_id=10 并且工资>1500的员工信息 select * from employees where department_id=10 and salary>1500 --查询departmemt_id=10 或者工资>1500的员工信息 select * from employees where department_id=10 or salary>1500; --查询员工job_id不是人力经理 计划部经理 保安部经理的员工信息 select * from employees where job_id not in('人力经理','计划部经理','保安部经理') --优先级顺序 --比较运算符 -> not -> and ->or --查询job_id(人力经理或秘书) 并且salary>1500的员工信息 select * from employees where job_id ='人力经理' or job_id ='秘书' and salary>1500 --使用括号改变优先级 --查询job_id是人力经理或job_id是秘书并且salary>1500的员工信息 select * from employees where (job_id ='人力经理' or job_id ='秘书') and salary>1500 --**************************************************************************************************** --排序 order by asc(默认)升序 desc降序 --语法格式 SELECT [DISTINCT] {*, column [alias], ...} FROM table [WHERE condition(s)] ORDER BY column --查询所有的员工信息按员工编号的降序排序 select * from employees order by employee_id desc --查询所有的员工信息按工资的升序排序 select * from employees order by salary asc --查询job_id='秘书' 员工信息,按工资降序排序 select * from employees where job_id='秘书' order by salary desc --使用列的别名排序 --查询员工的姓名,工资,年薪,并按年薪的降序排序 select first_name||last_name,salary,salary*12 newsalary from employees order by newsalary desc --按多字段排序(必须对每个字段设置排序方式) --查询部门id 工资 要求 按部门id降序, 工资升序 select department_id,salary from employees order by department_id desc, salary asc