亚洲欧洲国产欧美一区精品,激情五月亚洲色五月,最新精品国偷自产在线婷婷,欧美婷婷丁香五月天社区

程序員

DBA

一.什么是數(shù)據(jù)庫

軟件 = 數(shù)據(jù)倉庫 + 管理軟件

Database : 數(shù)據(jù)庫

DBMS: DataBase Management System

Oracle

DB2(IBM) Sybase(sybase)

mysql(Oracle)  sqlserver(MS)

sqlite(嵌入式數(shù)據(jù)庫)

access

二.訪問數(shù)據(jù)庫

1.telnet遠程登錄到數(shù)據(jù)庫服務器

C:>telent 192.168.0.26

openlab/open123

(這個步驟和數(shù)據(jù)庫無關)

2.sqlplus連接數(shù)據(jù)庫

sqlplus是數(shù)據(jù)庫的命令行客戶端工具

% sqlplus

數(shù)據(jù)庫的帳號/密碼: openlab/open123

SQL> 

3. 表(table)

1)建表

--最長30個字符.

--數(shù)字,字符,日期

create table users_ning(

id number,

pwd char(4),

name varchar(20),

phone varchar(30),

email varchar(50)

);

number  數(shù)字類型

char / varchar 字符類型

       varchar2

date    日期類型

2)插入數(shù)據(jù)

insert into users_ning

values(1001, '1234', 'liucs',

'13800000000',

'liucs@tarena.com.cn');

SQL語句: Structured Query Language

SQL>create table  建表

SQL>insert into...插入數(shù)據(jù)

SQL>select ...from... 查詢

SQL>commit  提交

三.查詢數(shù)據(jù)

1)準備數(shù)據(jù)

create table dept_ning1(

deptno number(2),

dname varchar2(20),

location varchar2(20)

);

insert into dept_ning1

values(10, 'developer', 'beijing');

insert into dept_ning1

values(20, 'finance', 'shanghai');

insert into dept_ning1

values(30, 'sales', 'guangzhou');

insert into dept_ning1

values(40, 'operation', 'tianjin');

commit;

select * from dept_ning;

create table emp_ning100(

empno number(4) primary key,

ename varchar2(20),

job varchar2(20),

salary number(7,2),

bonus number(7,2),

hiredate date default sysdate,

manager number(4),

deptno number(2)

);

insert into emp_ning100

values(1001, 'zhangwj', 'Manager',

10000, 2000, '12-MAR-10', 1005, 10);

--繼續(xù)插入其他9條記錄

insert into emp_ning100

values(1011, 'tom', null,

null, null, null, null, null);

--如果null值太多,可以簡寫為:

insert into emp_ning100

(empno, ename) values(1011,'tom');

commit;

--復制表

--將emp_ning100復制為emp_ning200

create table emp_ning200

as

select * from emp_ning100;

2)學習查詢語句

--按薪水排序,由高到低

 select * from emp_ning

 order by salary desc;

--null值被視作最大.

--按入職時間排序

select ename, hiredate from emp_ning

order by hiredate

public Integer nvl(

    Integer bonus,Integer value){

         if (bonus == null)

                   return value;

         else

                   return bonus;

}

--查詢員工的月收入,處理null值

--如果薪水或獎金是null值,按0計算

select ename, salary, bonus,

nvl(salary, 0) + nvl(bonus, 0)

as month_sal

from emp_ning

order by salary desc;

select ename,job from emp_ning

where upper(job) = 'ANALYST';

      lower(job) = 'analyst';

--in (list)

select ename, job

from emp_ning

where lower(job) in ('analyst',

'programmer', 'clerk');

--查找部門10或部門20的員工

select ename from emp_ning

where deptno in (10, 20);

--查找薪水在5000到8000之間的員工

--包括5000和8000.

select ename, salary

from emp_ning

where salary >= 5000

  and salary <= 8000;

--[5000, 8000] 閉區(qū)間

select ename, salary

from emp_ning

where salary between 5000

      and  8000;

--

#

空值:

select ename, salary

from emp_ning

order by salary desc;

select ename

from emp_ning

where salary is null;

select ename

from emp_ning

where salary is not null;

select count(*) from user_tables;

--所有名字中包含emp的表的個數(shù)

select count(*) from user_tables

where table_name like '%EMP%';

--查找書名里包含java的書

select book_name from books

where lower(book_name)

      like '%java%';

select ename, salary,

    salary * 0.12345678 as tax

from emp_ning;

round : 四舍五入

trunc : 截取

張三  6  5  7  8    6.5

李四  6  3  7  7    5.75 -> 6

                    5.2  -> 5

日期:

sysdate: 系統(tǒng)時間

'14-FEB-12' : oracle默認的日期格式

DD-MON-RR

now: 2012年

         YY      RR

95年    2095    1995

05年    2005    2005

select to_char(sysdate,

'yyyy-mm-dd hh24:mi:ss')

from dual;

select ename, hiredate,

to_char(hiredate, 'yyyy-mm-dd') h

from emp_ning;

小結(jié):

1.DDL(Data Definition Language):

         create / drop

2.DML(Data Manipulation language):

         insert / update / delete

3.DQL(Data Query Language):

         select

4.TCL(Transaction Control Language)

         commit

--java程序員

SCJP: sun certification

      java programmer

OCJP: Oracle ....

google: scjp 模擬題

--DBA

OCP: Oracle certification Professor