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;