笔记：ORACLE数据库基础学习 第一天

--SQL语句不区分大小写select sysdate from dual;--SQL语句分为:--DDL,DML,TCL,DQL,DCL--DDL:数据库定义语言--DDL用于维护数据库对象使用--数据库对象：表，试图，索引，序列--1：创建表CREATE TABLE employee_gl(  id NUMBER(4),  name VARCHAR2(20),  gender CHAR(1),  birth DATE,  salary NUMBER(6,2),  job VARCHAR2(30),  deptno NUMBER(2));--查看表结构DESC employee_gl--删除表DROP TABLE employee_gl--在数据库中，所有字段的默认值都是NULL，可以通过DEFAULT--关键字 指定一个默认值--对于字符串而言，数据库中字面量是使用单引号括起来的，这一--点需要与java区分记忆。并且，虽然SQL语句本身不区分大小写--但是字符串的值是区分大小写的。CREATE TABLE employee_gl(  id NUMBER(4),  name VARCHAR2(20),  gender CHAR(1)DEFAULT 'M',  birth DATE,  salary NUMBER(6,2) DEFAULT 3000,  job VARCHAR2(30),  deptno NUMBER(2));--NOT NULL约束--当一个字段被NOT NULL修饰后，该字段不允许为空。CREATE TABLE employee_gl(  id NUMBER(4),  name VARCHAR2(20)NOT NULL,  gender CHAR(1)DEFAULT 'M',  birth DATE,  salary NUMBER(6,2) DEFAULT 3000,  job VARCHAR2(30),  deptnp NUMBER(2));DESC employee_gl--修改表--1：修改表名--2：修改表结构--修改表名：--RENAME old_name TO new_name--如：RENAME employee_gl TO myemp_glDESC emp_gl--修改表结构：--1：添加新的字段--2：修改现有字段--3：删除现有字段--添加新的字段ALTER TABLE myemp_glADD(  deptno NUMBER(2))DESC myemp_gl--删除现有字段ALTER TABLE myemp_glDROP(DEPTNP)--修改现有字段--可以修改字段的类型，长度，默认值，非空约束--但是应当在表中没有数据的时候进行，否则尽量--不要修改类型，长度尽量只增不减。否则可能会修改失败！ALTER TABLE myemp_glMODIFY(  job VARCHAR2(40))--DML语句--DML是用来增、删、改表中的数据--DML伴随事务（TCL）控制的--INSERT语句--INSERT语句用于向表中插入新数据INSERT INTO myemp_gl(id,name,job,deptno)VALUES(1,'Gaolu','CLERK',10)COMMITSELECT * FROM myemp_gl--不指定字段则是全列插入，给定的值的类型与顺序与表中一致INSERT INTO myemp_glVALUES(2,'Rose','F','11-8月-96',5000,'STAFF',8)--插入日期类型数据时，可以使用数据库内置函数：--TO_DATE(CHAR1,CHAR2)--CHAR1:一个具体的日期的字符串--CHAR2:指定的日期格式INSERT INTO myemp_gl(id,name,birth)VALUES(4,'Jack',TO_DATE('1996-08-11','YYYY-MM-DD'))--UPDATE语句：修改表中数据--將ID為2的員工性別改为“M”，部门改为“20”UPDATE myemp_glset gender='M',deptno=20WHERE id=2SELECT * FROM myemp_gl--DELETE语句:删除语句DELETE FROM myemp_glWHERE salary<5000--UPDATE语句与DELETE语句都需要使用WHERE添加过滤条件--否则是对表中所有的数据进行操作--作业13：CREATE TABLE emp_gl(  empno NUMBER(4),  ename VARCHAR2(10),  job VARCHAR2(9),  mgr NUMBER(4),  hiredate DATE,  sal NUMBER(7,2),  comm NUMBER(7,2),  deptno NUMBER(2))CREATE TABLE dept_gl(  deptno NUMBER(2),  dname VARCHAR2(14),  loc VARCHAR2(13))--作业14:---------------------------emp插入数据-----------------------------------INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7369,'SMITH','CLERK',7902,TO_DATE('1980-12-17','YYYY-MM-DD'),800.00,20);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7499,'ALLEN','SALESMAN',7698,TO_DATE('1981-02-20','YYYY-MM-DD'),1600.00,300.00,30);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7521,'WARO','SALESMAN',7698,TO_DATE('1981-02-22','YYYY-MM-DD'),1250.00,500.00,30);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7566,'JONES','MANAGER',7839,TO_DATE('1981-04-02','YYYY-MM-DD'),2975.00,20);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7654,'MARTIN','SALESMAN',7698,TO_DATE('1981-09-28','YYYY-MM-DD'),1250.00,1400.00,30);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7698,'BLAKE','MANAGER',7839,TO_DATE('1981-05-01','YYYY-MM-DD'),2850.00,30);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7782,'CLARK','MANAGER',7839,TO_DATE('1981-06-09','YYYY-MM-DD'),2450,10);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7788,'SCOTT','ANALYST',7566,TO_DATE('1987-04-19','YYYY-MM-DD'),3000.00,20);INSERT INTO emp_gl(empno,ename,job,hiredate,sal,deptno)VALUES(7839,'KING','PRESIDENT',TO_DATE('1981-11-17','YYYY-MM-DD'),5000.00,10);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,comm,deptno)VALUES(7844,'TURNER','SALESMAN',7698,TO_DATE('1981-09-08','YYYY-MM-DD'),1500.00,0.00,30);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7876,'ADAMS','CLERK',7788,TO_DATE('1987-05-23','YYYY-MM-DD'),1100.00,20);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7900,'JAMES','CLERK',7698,TO_DATE('1981-12-03','YYYY-MM-DD'),950.00,30);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7902,'FORD','ANALYST',7566,TO_DATE('1981-12-03','YYYY-MM-DD'),3000.00,20);INSERT INTO emp_gl(empno,ename,job,mgr,hiredate,sal,deptno)VALUES(7934,'MILLER','CLERK',7782,TO_DATE('1982-01-23','YYYY-MM-DD'),1300.00,10);---------------------------删除emp数据-----------------------------------DELETE FROM emp_gl---------------------------查询emp数据-----------------------------------SELECT * FROM emp_gl---------------------------dept插入数据-----------------------------------INSERT INTO dept_gl(deptno,dname,loc)VALUES(10,'ACCOUNTING','NEW YORK');INSERT INTO dept_gl(deptno,dname,loc)VALUES(20,'RESEARCH','DALLAS');INSERT INTO dept_gl(deptno,dname,loc)VALUES(30,'SALES','CHICAGO');INSERT INTO dept_gl(deptno,dname,loc)VALUES(40,'OPERATIONS','BOSTON');---------------------------查询dept数据-----------------------------------SELECT * FROM dept_gl