oracle学习笔记（1）

  1 //创建用户，用户名是test，密码是test，此时的用户不具有任何权限
  2 create user test identified by test;
  3 //分配基本权限，由system或者sys来授权
  4 grant connect to test;
  5 grant resource to test;
  6 //修改用户口令
  7 alter user test identified by pwd;--这种可以修改所有用户的
  8 //删除用户
  9 deop user test;
 10 //基本操作
 11 //1.显示所有表
 12 select * from tab;
 13 //查看系统时间
 14 select sysdate from dual;
 15 //查看当前用户
 16 select user form dual;/show user;
 17 //查看表结构
 18 desc test;
 19 //连接用户
 20 conn test/test;
 21 //退出
 22 exit/quit
 23 
 24 
 25 
 26 
 27 //数据类型
 28 char 
 29 nchar
 30 varchar2
 31 nvarchar2
 32 raw
 33 number
 34 binary_float
 35 binary_double
 36 long
 37 long_row
 38 date
 39 timestamp
 40 blob
 41 clob
 42 nclob
 43 
 44 //创建表
 45 create table test(
 46 id int,
 47 name varchar2(20)
 48 );
 49 //删除表
 50 drop table test;
 51 
 52 //alter修改
 53 //向表中插入新字段
 54 alter table test add(字段1 类型，字段2 类型……);
 55 //修改表中字段(对类型修改)
 56 alter table test modify(字段1 类型，字段2 类型……);
 57 //删除表中字段
 58 alter table test dorp (字段1 类型，字段2 类型……);
 59 //修改表中字段
 60 alter table test rename column PSWD to PSWD1;
 61 //修改表的名称
 62 rename test to test1;
 63 
 64 
 65 
 66 //employee员工表，有基本信息，员工号、姓名、部门号
 67 //department部门表，有部门号，部门名称，部门所在地
 68 //project项目表，有项目号，项目名称，项目预算
 69 //works_on具体工作表，有参加的员工号，项目名称，具体的工作职位，参加项目时间
 70 
 71 //创建表
 72 create table employee(
 73 emp_no number not null,
 74 emp_fname varchar2(20) not null,
 75 emp_lname varchar2(20) not null,
 76 dept_no varchar2(4)
 77 );
 78 //插入数据
 79 insert into employee values(25348,‘Mattew‘ ,‘Smith‘,‘d3‘);
 80 insert into employee values(10102,‘Ann‘ ,‘Jones‘,‘d3‘);
 81 insert into employee values(18316,‘John‘ ,‘Barrimore‘,‘d1‘);
 82 insert into employee values(29346,‘James‘ ,‘James‘,‘d2‘);
 83 insert into employee values(9031,‘Elsa‘, ‘Bertoni‘,‘d2‘);
 84 insert into employee values(2581,‘Elke‘, ‘Hansel‘,‘d2‘);
 85 insert into employee values(28559,‘Syhill‘, ‘Moser‘,‘d1‘);
 86 
 87 
 88 create table department(
 89 dept_no varchar2(4) not null,
 90 dept_name varchar2(25) not null,
 91 location varchar2(30)
 92 );
 93 //插入数据
 94 insert into department values(‘d1‘,‘research‘,‘Dallas‘);
 95 insert into department values(‘d2‘,‘accounting‘,‘Seattle‘);
 96 insert into department values(‘d3‘,‘marketing‘,‘Dallas‘);
 97 
 98 create table project(
 99 project_no varchar2(4) not null,
100 project_name varchar2(15) not null,
101 budget number(10,2)
102 );
103 //插入数据
104 insert into project values(‘p1‘,‘Apollo‘,‘120000.00‘);
105 insert into project values(‘p2‘,‘Gemini‘,‘95000.00‘);
106 insert into project values(‘p3‘,‘Mercury‘,‘186500.00‘);
107 
108 create table works_on(
109 emp_no number not null,
110 project_no varchar2(4) not null,
111 job varchar2(15),
112 enter_date date
113 );
114 //插入数据
115 insert into works_on values(10102,‘p1‘,‘analyst‘,to_date(‘1997.10.1‘,‘yyyy.mm.dd‘));
116 insert into works_on values(10102,‘p3‘,‘manager‘,to_date(‘1999.1.1‘,‘yyyy.mm.dd‘));
117 insert into works_on values(25348,‘p2‘,‘clerk‘,to_date(‘1998.2.15‘,‘yyyy.mm.dd‘));
118 insert into works_on values(18316,‘p2‘,null,to_date(‘1998.6.1‘,‘yyyy.mm.dd‘));
119 insert into works_on values(2581,‘p3‘,‘analyst‘,to_date(‘1998.10.15‘,‘yyyy.mm.dd‘));
120 insert into works_on values(9031,‘p1‘,null,to_date(‘1998.10.1‘,‘yyyy.mm.dd‘));
121 insert into works_on values(9031,‘p3‘,‘clerk‘,to_date(‘1997.11.15‘,‘yyyy.mm.dd‘));
122 insert into works_on values(29346,‘p1‘,‘clerk‘,to_date(‘1997.1.4‘,‘yyyy.mm.dd‘));
123 
124 
125 
126 //使用算术表达式
127 select last_name,salary,salary+300 from employees;
128 
129 //操作符优先级
130 select last_name,salary,12*salary+300 from employees;
131 
132 //使用圆括号
133 select last_name,salary,12*(salary+300) from employees;
134 
135 //计算成绩
136 select studentname , chinese+maths+english as result from studentResult;
137 
138 //定义nul值
139 Null表示不可用、未知的、没有赋值的、不适合的值！
140 Null不是0也不是空格！
141 select last_name,job_id,salary,commission_pct from employees;
142 
143 //使用连接操作运算
144 select last_name || job_id as "Employees" from employess; 
145 //使用字母字符
146 select last_name || ‘is a‘ || job_id as "Employee Details" from employees;
147 
148 //过滤重复行
149 select distinct department_id from employees;
150 
151 //显示表结构
152 desc employees;
153 
154 //字符函数
155 upper(str) ,转为大写
156 lower(str),转为小写
157 substr(str,n,m),从n位开始，截取m个字符
158 substr(str,n),从n位开始，截取后面字符
159 length(str),得到字符串的长度
160 
161 ltrim(str),去掉左边空格
162 rtrim(str),去掉右边空格
163 instr(str,c),得到字符c在str的位置
164 lpad(str,n,c),将str补足为n位长度，不足左边用字符c代替
165 rpad(str,n,c),将str补足为n位长度，不足右边以字符c代替
166 
167 //字符函数例子
168 --不区分大小写查询
169 select emp_code,emp_name from emp;
170 --去掉空格
171 select rtrim (ltrim(emp_code)) from emp;
172 
173 //数值函数
174 round(col,n),四舍五入
175 round(457.628,2) ==>457.63
176 round(457.628,-1) ==>460
177 trunc(col,n),截取数值
178 trunc(457.628,2) ==>457.62
179 trunc(457.628,-1) ==>450
180 
181 //日期函数
182 months_between(date1,date2) ==>两个日期间的月数
183 add_months(date,m) ==>增加m个月，m可以是负数，意为减少m个月
184 trunc,截断日期
185 last_day,当月最后一天
186 
187 //日期函数例子
188 --当前日期增加1个月
189 select add_months(sysdate,1) from duai;
190 --去年同月
191 select add(sysdate,-12) from dual;
192 --得到年初
193 select trunc(sysdate,‘YYYY‘) from dual;
194 --得到月初
195 select trunc(sysdate,‘MM‘) from dual;
196 --精确到天，截断小时分秒
197 select trunc(sysdate) from dual;
198 --当月最后一天
199 select last_day(sysdate) from dual;
200 
201 
202 
203 //转换函数
204 //日期转为字符
205 to_char(date1,‘format_model‘)
206 format_model:转换后的显示格式
207 YYYY年,MM月,DD日,HH24小时,MI分,SS秒
208 select to_char(sysdate,‘YYYY-MM-DD HH24:MI:SS‘) rq from dual;
209 
210 //字符转为日期
211 to_date(‘2007-11-11‘,‘YYYY-MM-DD‘)
212 //数值转为字符
213 select to_char(55676,‘fm99,999.0‘) from dual;--fm表示去掉前面的空格和0，==》55,676.00
214 
215 ASC:升序，默认的
216 DESC：降序
217 
218 
219 //聚合函数
220 count(*)  返回所有行记录数
221 count(列名) 返回表达式非null的行记录数
222 min(列名) 返回表达式最小值
223 max(列名) 返回表达式最大值
224 sum(列名) 返回表达式的总和
225 avg(列名) 返回表达式的平均值
226 --参数的数据类型：char,varchar2,number,date
227 
228 select count(*) from employees;
229 select count(job) from works_on;
230 select count(distinct job) from works_on;
231 select min(emp_no) from employees;
232 select max(emp_no) from employees;
233 select sum(budget) from project;
234 select avg(budget) from project;
235 
236 //分组查询
237 select depy_no min(emp_no) 最小员工编号 from employees
238 group by dept_no;
239 //每一个项目的总人数
240 selecy project_no ,count(*) 总人数 from works_on 
241 group by project_no;
242 
243 //多表查询
244 select e.emp_no from employees e;
245 //笛卡尔积
246 select * from employees , department;