SQL入门

SQL

SQL即structured query language 结构性查询语言

DDL (table structure)

DDL 即Data Definition Language
–操作数据库对象：表，视图，索引，队列
–create ,alter ,drop, truncate
–column ,datatype,width,constraints

//表结构
DESC  employee;
//创建表
CREATE  TABLE  employee_lfyeah (
id  NUMBER(4),
name  VARCHAR2(20)  NOT  NULL,
gender CHAR(1)  DEFAULT  ‘M’,
birth  DATE,
salary  NUMBER(6,2),
job  VARCHAR2(30),
deptno  NUMBER(2)
);
//删除表
DROP   TABLE  employee;
//改表名
RENAME  employee_lfyeah  TO  employee;
//增加列
ALTER  TABLE  employee  
            ADD( hiredate  DATE  DEFAULT  SYSDATE ,
                 deptno  NUMBER(4));
//删除列
ALTER  TABLE  employee
            DROP(hiredate ,deptno);
//修改列           
ALTER   TABLE  employee
            MODIFY(job  VARCHAR2(40) DEFAULT  ‘CLERK’);

DML (table data)

DML即Data Manipulation Language
–操作表中的数据
–insert,delete,update

//插入数据
INSERT  INTO  myemp (id,name,gender,birth,salary) 
        VALUES(123,  ’rose’,   ’M’,   TO_DATE(‘2003-09-03’,’YYYY-MM-DD’), 3232);
//更新数据  
UPDATE  myemp  SET(name=’rose’)  WHERE  id=123;

TCL

TCL即Transaction Control Language
–事务控制，维护数据的一致性
–commit,rollback,savepoint

DQL

DQL即Data Query Language
–数据查询
–select

//检查表是否存在
select   1  as  a   from  dual;
//简单查询
select * from emp;