数据库所有操作总结1课案

1、查询语句基本数据查询select内容from表名；select distinct job from emp ； 去除重复行限定查询(查询满足条件的行)SELECT * |列名FROM 表名 WHERE 条件;SELECT emp no,e name,commFROM empWHERE comm IS NOT NULL;限定查询-IS NULL 和IS NOT NULL 的使用SELECT emp no,e name,commFROM empWHERE comm IS NULL查询谁没有奖金限定查询-AND 的使用查询工资大于1500,并且可以领取奖金的雇员SELECT emp no,e nam

2、e,sal,commFROM EMPWHERE sal 1500 AND comm IS NOT NULL;限定查询-OR的使用查询工资大于1500和可以领取奖金的雇员SELECT emp no,e name,sal,commFROM EMPWHERE sal 1500 OR comm IS NOT NULL;限定查询-使用NOT对条件整体取反查询工资不大于1500并且不能领取资金的雇员SELECT empno,ename,sal,commFROM empWHERE NOT (sal1500 AND comm IS NOT NULL);限定查询-BETWEEN . AND.的使用查询基本工资大

3、于等于 1500并且小于等于3000的雇员SELECT emp no,e name,sal,commFROM empWHERE sal BETWEEN 1500 AND 3000限定查询-IN的使用查询出雇员编号是 7369，7499，7521的雇员的具体信息SELECT *FROM empWHERE emp no IN(7369,7499,7521);限定查询-NOT IN 的使用查询出雇员编号不是 7369，7499，7521的雇员的具体信息SELECT *FROM empWHERE emp no NOT IN(7369,7499,7521);限定查询-LIKE 的使用查询中雇员的名字第二

4、个字符是M的雇员信息SELECT emp no,e name,comm,salFROM empWHERE en ame LIKE _M%;说明：_匹配一个字符，匹配0个或多个字符限定查询-LIKE 匹配日期查询1982年入职的所有雇员的信息SELECT *FROM empWHERE hiredateLIKE %82%限定查询-LIKE 匹配数字查询工资中包含 5的雇员信息SELECT *FROM empWHERE salLIKE %5%对结果排序-ORDER BY带有ORDER BY 子句的SQL语句基本格式SELECT 列FROM 表 WHERE 条件 ORDER BY 列查询员工资大于15

5、00的信息，按工资排序SELECT * FROM emp WHERE sal1500 ORDER BY sal查询工资大于1500员工的信息，按工资降序，按雇佣日期升序排序SELECT * FROM emp WHERE sal1500ORDER BY salDESC,hiredate ASC说明：ASC排序，DESC降序，默认 ASC左、右外连接查询员工编号，姓名，所在部门号，部门名称，将没有员工的部门也显示出来SELECT e.e name,d.dep tn o,d.d nameFROM emp e,dept dWHERE e.dep tn o(+)=d.dept no;注：(+)在左边，表

6、示右连接，会列出右表中出现但是没有在左表中出现的行交叉连接(CROSS JOIN):用来产生笛卡尔积的SELECT * FROM emp CROSS JOIN dept;自然连接(NATURAL JOIN):自动进行关联字段的匹配SELECT * FROM emp NA TURAL JOIN dept;USING子句：直接指定操作关联列SELECT * FROM emp JOIN dept USING(dep tn o);ON子句：用户自己编写连接条件SELECT * FROM empJOIN deptON emp.dept no=dept.dept no;RIGHT JOIN :右外连接SE

7、LECT e.empno,e.ename,d.deptno,d.dname FROM emp e RIGHT JOIN dept d ON e.dept no=d.dept no;子查询-IN的使用查询和SMITH或JONES在同一部门，同一职位工作的员工SELECT * FROM emp WHERE (deptno,job) IN(SELECT deptno,job FROM emp WHERE en ameIN(SMITH,JONES);子查询-ANY的使用=ANY :与IN操作符的效果一致查询和SMITH或JONES在同一部门，同一职位工作的员工SELECT * FROM emp WHE

8、RE (dept no,job) =ANY (SELECT dept no,jopFROM emp WHERE en ame IN(SMITH,JONES);ANY :只要大于子查询中的任何一个值即可SELECT * FROM emp WHERE sal ANY(SELECT MIN(sal) FROM EMP GROUP BY dept no);ALL :比最大的值大SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY dept no);ANY :只要小于子查询中的任何一个值即可SELECT * FROM emp W

9、HERE sal ANY(SELECT MIN(sal) FROM emp GROUP BY dept no);ALL :比最小的值小SELECT * FROM emp WHERE salALL(SELECT MIN(sal) FROM emp GROUP BY dept no);插入语句intsert into表名values（值1值2值3.）插入的数值的字段类型要与定义的时候一样intsert into表名 values （值1，值2, null，值3）有不想写的可以用 null来标示intsert into表名（字段1字段2,字段3 ） values（值1值2值3） 类型对应注意：数值的

10、类型个数顺序不能超过最大值插入记录到表中INSERT的语法INSERT INTO 表名（字段名1,字段名2,）VALUES（值1,值2）;为MYEMP中增加一条记录INSERT INTO myemp（empno,ename,job,mgr,hiredate,sal,comm,deptno）V ALUES（7899,张三 清洁工,7369,14-2 月-1995,9000,300,40）注：给定的值要和指定的字段数一致当为表中的所有列都插入数据数，可以采用如下简单写法INSERT INTO 表名 VALUES（值 1,值 2）;INSERT INTO myemp V ALUES（7991,李四,

11、清洁工,7369,14-2 月-1995,9000,300,40）注：要求给定值的数目和表中列的数目一致，并且值的顺序和表中列的顺序一致。新增加一条记录到 MYEMP中，但是该员工没有奖金也没有上级领导 第一种写法：INSERT INTO myemp （emp no,en ame,job,hiredate,sal,dept no）VALUES（7899,张三,清洁工,14-2 月-1995,9000,40）第二种写法：（用null来指定没有值的列）INSERT INTO myemp V ALUES(7898,王五,清洁工,null,14-2 月-1995,9000, null,40)新增加一条

12、记录到 MYEMP中，但日期格式按1988-09-09的方式指定? TO_DATE函数的使用INSERT INTO myemp(emp no,en ame,job,hiredate,sal,dept no)VALUES(7896,赵六,清洁工,TO_DATE(1984-09-09,yyyy-mm-dd),9000,40);修改记录update 表名 set字段=值，字段 =值，字段= 值where 条件;注意：条件必须写删除记录delete from 表名 where 条件；注意：条件必须写ROLLBACK(回滚)在sqlplus中如果使用 ROLLBACK的话那么将撤销操作直到 上次执行co

13、mmit的时候的 状态标量函数的使用转化大小写select upper( sddfafrom dual select lower( ddfafrom dual将首字母大写其余小写select initcap (ename) from emp ； 连接字符串 select count (dept no) from dept;select con tact ( hello , woeld from dual;select abc| jkhSdffrom dual;求子串：select substr （字符串，起始位，结束位）from dual; 起始位写 0或者1都可以代表第一位select su

14、bstr （字符串，起始位）from dual;从起始位开始往后截取求字符串长度：select length （ ename）from emp;字符串替换select replace （原始字符串，想要被替换的字符串，替换字符串）from dual ；输出字符串的后三位select ename ， substr（ ename, length （ ename）-2）from dual ；select ename substr（ ename, -3）from dual ；四舍五入select round （ 132.78454）from dual ； 四舍五入取整select round （ 13

15、2.78345， 2）from dual ；四舍五入并且指定保留小数点的位数select round （1323.7858416，-2）from dual;小数不要 并且 整数位两位取整本代码得1300截断小数位select trunc(546541.151)from dual不管四舍五入直接截断小数5465541select trunc(789.536,2）from dual截断小数指定小数的数位789.53select trunc(789.536,-2)from dual截断小数700并且对整数进行截断取余select mod ( 10， 3)from dual ；取余数1运算规律：日期+

16、数字=日期 日期-数字=日期 日期一日期=数字 显示系统时间select sysdate from dual ；select to_char(sysdate,yyyy-mm-dd,hh24:mi:ss) from dual;显示某本门员工进入公司星期数：select emp no，en ame ， round ( sysdate-hiredate)/7) from emp where dept no=10 ； 在指定的日期加上指定的月数后的日期：select add_ mon ths(sysdate,2) from dual;求出给定日期范围的月数：select emp no en ame m

17、on th_betwee n( sysdate,hiredate) from emp;下一个给定的星期是那个日期：select next_day(sysdate,星期一 from dual;求出给定日期所在的月份的最后一天的日期select last_day(sysdate) from dual;转换函数转换成字符串(TO_CHAR)查询雇员号，姓名，以及入职的年份SELECT emp no,e name,TO_CHAR(hiredate,yyyy)FROM emp;说明：yyyy匹配年份，mm匹配月份，dd匹配日使用TO_CHAR设置日期的显示格式SELECT emp no,e name,T

18、O_CHAR(hiredate,yyyy-mm-dd)FROM emp;使用fm去掉前导0SELECT emp no,e name,TO_CHAR(hiredate,fmyyyy-mm-dd)FROM emp;通过TO_CHAR设置数值的格式SELECT emp no,e name,TO_CHAR(sal,99,999) FROM emp;说明：9代表1位数字显示金额SELECT emp no,e name,TO_CHAR(sal,$99,999) FROM emp;说明：$代表美元，L代表本地转换成数字(TO_NUMBER)SELECT TO_NUMBER(123) +TO_NUMBER(1

19、23) FROM dual;转换成日期(TO_DATE)SELECT TO_DATE(2009-07-31 ,yyyy-mm-dd) FROM dual;通用函数查询每个员工的年收入SELECT emp no,e name,(sal+NVL(comm,0)*12 in comeFROM emp; 说明：(COMM,O)当comm的值为null时，用0替换常用的列函数SUM(expression)求和MAX(expressio n)求最大值MIN(expressio n)求最小值COUNT(expression)统计记录数COUNT(DISTINCT COLNAME)统计去除重复行记录数SQL语

20、句的执行顺序FROMWHEREGROUP BYHAVINGSELECTORDER BY创建表create table表名（字段1数值类型 defult xx ,字段2数值类型， ）；defult是默认值可写可不写create table表名2 as表名1 复制表2至U表1中create table表名as （子查询）复制子查询结果这张表，当子查询结果没有记录时这时 只会复制 表结构（数据类型）而不会增加数据（有时目的只是复制表结构那么就使用这种方法 比如查询语句中使用select 1=2 from 表名）删除表drop table 表名;注意：delete是删除表里面的数据修改表结构增加一列：

21、alter table表名add （列名称 列数据类型 defult默认值）注意：defult和默认值 可写 可不写删除列：alter table 表名 drop column 歹U名称注意：不管有没有数据都会删除 修改列的数据类型：alter table表名modify （列名称数据类型defult默认值）注意：修改的时候（和原来相同数据类型）长度 要求不能小于数据中 数据的最小长度，但是能增大，要想 改变数据类型的话，必须将字段下的数据全部删除 才可以更改数据类型表名重命名rename表名（旧） to表名（新）；截断表truncate 表名；注意：清空表中的所有数据立即释放资源，该操作不可

22、 回滚ROWNUM表示行号，是一个伪列，可以在每一张表中出现应用：查询表中记录的前五行SELECT *FROM EMPWHERE ROWNUM BETWEEN =5;现在要查询从6到10条记录，使用子查询SELECT *FROM（SELECT ROWNUM num,empno,ename,salFROM emp） eeWHERE rownum BETWEEN 6 AND 10;视图创建视图语法：CREATE VIEW视图名称AS子查询例如：创建部门 20员工的雇员信息，包含 EMPNO,ENAME,SAL,DEPTNOCREATE VIEW empv20 ASSELECT emp no,e n

23、ame,sal,dept no FROM emp;注:当创建视图以后，可以像操作表一样的操作视图注：当创建视图时，视图的操作会影响到对表的操作，这是不安全的，我 们可以采用如下选项来创建视图? WITH CHECK OPTION 不能更新创建视图的条件? WITH READ ONL Y 创建只读视图删除视图语法：DROP VIEW视图名称DROP VIEW empv20;删除视图 empv20;注:当删除视图所在的表时，则视图也不能再被使用序列创建序列语法：CREATE SEQUENCE序列名称MAXV ALUE num | NOMAXV ALUEMINV ALUE num | NOMINV

24、ALUEINCREMENT BY num START WITH 10CACHE num | NOCACHE;CYCLE | NOCYCLE仓U建序列 CREATE SEQUENCE myseq;序列的操作nextVal :取得序列的下一个内容currVal:取得当前序列的内容select 序歹U名称.nextval from dual.创建表验证序列的操作? CREATE TABLE testseq(n ext NUMBER;CURR NUMBER;)INSERT INTO testseq V ALUES(myseq. nextVal,myseq.currVal);将以上插入语句，执行 5次，

25、观察效果，不难得出结论，nextVal每次都会加1,而currVal都是取当前值创建序列指定每次增长的增量CREATE SEQUENCE myseq INCREMENT BY 2;创建序列指定开始的序列，默认的序列从1开始。CREATE SEQUENCE myseq INCREMENT BY 2 START WITH 10;创建一个循环序列1, 3, 5, 7, 9。CREATE SEQUENCE myseq MAXV ALUE 9 INCREMENT BY 2START WITH 1 CACHE 2 CYCLE;数据库的常用操作语言:show user;察看当前连接的用户connect sc

26、ott/passwored采用scott的用户名/密码连接数据库 desc table_name;察看 tableName 表结构quit | exit ;退出disconnect;断开连接clear screen;清屏，相当于 Windows下的cls命令select * from tab;列出当前用户下的所有表。 path执行path指定的脚本文件Oracle常用基本数据类型varchar2/varchar 变长字符串char定长字符串Integer 整型number （ m, n）数字型 smallint短整型 float浮点数 decimal十进制数字 date日期型SQL分类DML（

27、Data Manipulation Language,数据操作语言）用于检索或修改数据DDL（Data Definition Language，数据定义语言）用于定义数据的结构，如创建修改或者删除数据库对象DCL（Data Control Language，数据控制语言）用于定义数据库用户的权限约束constraint约束名约束字段主键约束(PRIMARY KEY )CONSTRAINT persion_pid_pk PRIMARY KEY(pid);非空约束：name VARCHAR2(30) NOT NULL,唯一约束：name VARCHAR2(30) UNIQUE NOT NULL,在

28、创建表时声明CONSTRAINT person_name_uk UNIQUE(pid)在表的结尾声明检查约束：(check)age NUMBER(3) NOT NULL CHECK(age betwee n 1 and 150),主外键约束：(如一本书要属于一个人)CREATE TABLE perso n(pid VARCHAR2(18),name VARCHAR2(30) NOT NULL,age NUMBER(3) NOT NULL,birthdate DATE,sex VARCHAR2(2) DEFAULT 男CONSTRAINT person_pid_pk PRIMARY KEY(pi

29、d),CONSTRAINT person_name_uk UNIQUE( name),CONSTRAINT person_age_ck CHECK(age BETWEEN 1 AND 150),CONSTRAINT person_sex_ck CHECK(sex IN (男,女,中);CREATE TABLE book(bid NUMBER PRIMARY KEY NOT NULL,bn ame VARCHAR(30),bprice NUMBER(5,2),pid VARCHAR2(18),CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFER

30、ENCES perso n(pid) );增加约束ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段)例：分别增加主键约束和外键约束给book表ALTER TABLE book ADD CONSTRAINT book_bik_pk PRIMARY KEY(bik);ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGNKEY(pid) REFERENCES pers on( pid) ON DELETE CASCADE;约束的命名规范(建议)PRIMARY KEY :表名称_主键名称_pk;UNIQUE :表名称_字段名称_ukCHECK :表名称_字段名称_ck删除约束ALTER TABLE 表名称 DROP CO