地球上最全的SQL命令 珍藏版.docx

1、地球上最全的SQL命令 珍藏版Oracle SQL day1Oracle SQL(Oracle 9i 9.2.0.1.0)一、DataBase 保存数据，以表的形式表现数据二、SQL SQL(structure query language 结构化查询语言)，是操作关系型数据库中的对象 DDL（data definition language 数据定义语言），用于建表或删表操作，以及对表约束进行修改。 create table , alter table , drop table 对表结构的增、删、改操作。 DML（Data manipulation language 数据操作语言），向表中插

2、入纪录，修改纪录 insert , update , delete transaction,事务控制语言，由DML语句组成的，commit; ,rollback，savepoint select 查询语句 dcl 数据控制语言，授权语句 grant,revoke alter session alter system三、Oracle DB (database) DBMS 数据库管理系统(database management system) DBA 数据库管理员 数据库管理系统有：ms微软的sql server, IBM的db2，imformix, mysql, oracle的oracle920

3、, sybase的ase四、相关操作 echo $PATH 配置环境变量，加/oracledata/oracle/product/9.2.0/bin,把oracle命令加上 echo $ORACLE_SID 查看Oracle数据库的实例，实例是访问数据库的方法。缺省端口号是：1521 1、sqlplus 访问数据库命令（本地访问/远程访问），和数据库建立连接的命令，是数据库操作的环境 sqlplus 用户名/密码 2、show user 显示当前用户的用户名 改变身份可以直接connect 用户名/密码 - 这个是sqlplus命令 在sqlplus中可以使用 ! 可以在shell和sqlpl

4、us间切换，!shell命令 可以在sqlplus中使用shell命令。 实际上是sqlplus开了子进程来执行shell命令。 3、Oracle数据库中的表分两类：用户表（用户使用操作的表），系统表（数据库系统维护的表，也叫数据字典） 对用户表的DDL操作触发了对系统表的DML操作！五、基本语法!1、select查询语句 对一个表进行查询操作首先要熟悉表结构。 select table_name from user_tables;（查询系统表） 以上的查询语句就是查询本用户下所拥有的所有表的表名。 投影操作，只查看选择的字段的信息。 select 选择操作，查看字段中的特定某些信息。 whe

5、re 联接操作，多表查询，通过表间连接，查寻出多表中的信息（1）select table_name from user_tables;（查询系统表） 以上的查询语句就是查询本用户下所拥有的所有表的表名。（2）sqlplus的buffer中会缓存最后一条sql语句，可以使用/来执行这最后一条sql语句，也可以使用 edit命令来编辑最后一条sql语句。 l命令（list）（sqlplus命令）可以显示buffer中最后一条命令。 （3）desc 表名 这是一条sqlplus命令，注意他不是sql语句，这条命令用于查看表的结构。describe的缩写 字段名 字段的类型，这是使用完desc命令后显

6、示的表结构。 例：desc s_emp 员工表 显示结果：Name Null Type-ID NOT NULL NUMBER(7)LAST_NAME NOT NULL VARCHAR2(25)FIRST_NAME VARCHAR2(25)USERID VARCHAR2(8)START_DATE DATECOMMENTS VARCHAR2(255)MANAGER_ID NUMBER(7)TITLE VARCHAR2(25)DEPT_ID NUMBER(7)SALARY NUMBER(11,2)COMMISSION_PCT NUMBER(4,2)Name是字段名，Null是此字段是否允许为空，no

7、t null是不允许为空。Type是类型，number是数字类型、varchar2是字符类型，date是日期类型。表中字段的含义： ID ID LAST_NAME 姓 FIRST_NAME 名 START_DATE 入职日期 COMMENTS 说明 MANAGER_ID 经理ID TITLE 职务 DEPT_ID 部门 SALARY 月薪 COMMISSION_PCT 提成 desc s_dept 部门表 Name Null? Type- - -ID NOT NULL NUMBER(7)NAME NOT NULL VARCHAR2(25)REGION_ID (地区号) NUMBER(7)des

8、c s_region 地区表（4）select 表的字段名1,表的字段名2, . from 表名; select * from 表名; 查询表中所有字段的信息 （5）关键字不能拆分，sql语句，以及表名，字段名是大小写不敏感的。 sql语句要以;结尾，来表示sql语句结束，如果不加;系统不会执行此条sql语句，并提示。 在Oracle中字符显示是左对齐，数值右对齐。（6）在select 语句中可以使用数学表达式。 select 表达式（必须包含本表字段名）,.,. from 表名； 运算的优先级的先乘除后加减，同级自左向右运算，括号改变优先级。 select first_name,salary

9、*12 from s_emp; 找出每个人的年薪（7）别名 select 字段名或表达式 别名，. .,. from 表名； 可以通过在字段名或表达式后加空格别名，可以给列，或者表达式结果其别名。 表达式别名若有空格必须加双引号。 select first_name,salary*12 annual_salary from s_emp;（8）字符串拼接使用|符号 select 目标字段名| |目标字段名 from 表名； 注意：在Oracle中的字符或字符串要用单引号，双引号用来起别名 别名中需要使用空格，或是大小写敏感时需要用.包含。 练习：自己写一条SQL语句，执行的结果是select *

10、 from .;其中.是每张表的表名即在每张表的表名前加“select * from” ，后加“;” select select * from |table_name|; from user_tables; 2、处理错误执行查询时 无效标识一般表示字段名错误 表或视图不存在表示表名错误（1）!oerr ora 错误号 ，系统可以显示错误的原因和如何修改。如果命令错误输入可以使用edit或ed来修改输入错误。 实际上是在编辑缓存文件中的最后一条sql语句。 也可以使用 (change) c /错误字段/正确字段，来进行替换操作进行修改。 只有在Linux平台使用 ! 相当于 host ，没有断连

11、接，只是切换了一下，执行shell命令（2）edit命令来编辑最后一条sql语句。3、sqlplus设置 set pause on 回车响应，分屏显示，只在本会话中有效 set pause off 关闭分屏显示。 set pause . 设置分屏显示的提示信息。 set pause on 先输出提示信息，回车响应，分屏显示 set head off 提头输出关闭 set feed off 结尾输出关闭 set echo off 回写关闭 spool 文件名.sql 写入指定文件 spool off 关闭写入。 4、sql脚本 也就是在文件中写有sql语句的文件，可以在sqlplus中运行。 引

12、入sql脚本 sqlplus 用户名/密码 sql脚本 （注意：在用户名密码输入结束后一定要加空格然后再写sql脚本） 在脚本中最后一行写上“exit”，则运行完脚本以后，回到shell上5、Oracle中的空值 空值会当无穷大处理，其实空值根本就不会存储，只是看作是无穷大。 Oracle中空值处理函数 NVL(字段名，值)，如果这个字段为空，替换为第二个参数的指定值，如果不为空，则会返回其原值。例：select first_name,salary*12*(1+NVL(commission_pct,0)/100) total salary from s_emp; 查询每个员工一年的总收入6、d

13、istinct关键字，去掉重复行（这个关键字会触发排序操作）例： select distinct dept_id from s_emp; 查询员工都分配在哪些不同的部门 select distinct dept_id,title from s_emp; 查询部门都有哪些不同的职位 distinct 可以使dept_id与title的联合唯一 注意：distinct，关键字之后会对from之前的字段进行排重操作。六、选择操作 1、order by 排序是按照存储顺序输出的，order by后面可以跟字段名，别名，位置，也可以有多个字段，当第一个字段的值一样时，按第二个字段排序 排序子句 ASC（

14、默认，升序） DESC（降序） order by 目标列名（也可以是别名） 排序顺序（不写排序顺序，会默认为升序排序） 例：select first_name from s_emp order by first_name; 等价于 select first_name from s_emp order by 1; 此处的1表示first_name。 select first_name from s_emp order by first_name desc; select first_name,salary from s_emp order by salary; 按照工资的升序排序 select

15、first_name,salary from s_emp order by salary desc; 按照工资的降序排序 select first_name,salary,commission_pct from s_emp order by salary,commission_pct; 先按工资排，工资一样的再按提成排序，升序显示 注意：升序空值在结果的末尾，降序空值在结果的前面。2、where子句（选择操作） where子句使用在 select . from . 后面，用来选择所需（符合条件的）的记录 例：select first_name,salary from s_emp where s

16、alary1500; 找出工资比1500高的员工 此处不能用where 21500 查询Carmen的工资： select first_name,salary from s_emp where first_name=Carmen; 注意:字符需要加引号，数字不需要。引号之内的内容是大小写敏感的 where后面跟的是表达式 也就是 XXX=XXX， XXX between X and X ，XXX in（X，X，X） between . and . ,表示结果在这之间，between and是一个闭区间，也就相当于. = . 。 找出工资在1500到2000之间的： select first_n

17、ame,salary from s_emp where salary between 1500 and 2000; 等价于：select first_name,salary from s_emp where salary =1500 and salary=2000; !=，=，这三个都标识不等于，=，=，这些运算符都可以使用。 . in (va1,val2,.) 判断结果是否在这个集合中存在，等于其中随变一个就行了，可以说in=any in中集合的数据是有顺序的，概率高的数据放在前面可提高效率 查找这三个部门里任意一个部门的员工 select first_name from s_emp whe

18、re dept_id in(41,42,32); 等价于 select first_name from s_emp where dept_id=any(41,42,32); 应该把出现频率较高的数据放在集合的前面会提高效率 not inall 没有顺序，not in 不等于 all 空值会对not in造成影响，也就是不等于任何值，但是空值例外。 like . 字符串通配查询，%表示0或任意多个字符，_，表示任意一个字符。 注意：转义的用法：like S_% escape 表名都是以大写存储的 例：找出表名是以S_开头的表 select table_name from user_tables

19、where table_name like S_% escape ; 把定义为转义字符 . is null 使用来判断值是否为空。is not null 判断值不为空 例：查看哪些人没有提成 select first_name from s_emp where commission_pct is null; 找出42部门每个员工的年薪并且按年薪从大到小的顺序显示： select first_name, salary*12*(1+nvl(commission_pct,0)/100) total_salary from s_emp where dept_id =42 order by 2 desc

20、; . and . 表示只有两个条件同时满足 . or . 表示条件只要满足其中只一就可以 all . 是要求都满足条件。 注意：Oracle中的字符串是严格区分大小写的。 （1）注意数据类型，数字类型直接写，字符用. ，缺省格式的Date可以用.，只有别名才用 包含。 （2）选择合适的运算符 2、单行函数（一个输入，一个输出） 多行函数又叫组函数（多个输入，一个输出）例平均值 1.字符函数 字符是大小写敏感的 转小写 lower(字段名) - 其中的参数可以是一个字符串常量或是一个字段名 例：select lower(first_name) from s_emp;把每一个字符都变成小写。 s

21、elect lower(Sql Course) from dual; 这个dual表可以用来测试。 select first_name,salary from s_emp where lower(first_name)=carmen; 通用性好 select first_name,salary from s_emp where first_name=Carmen; 效率高， 平时用的时候尽量写这种，当实在不知道数据是什么样时才用上面的。 转大写 upper(字段名) 首字母大写 initcap(字段名) 字符串拼接 concat(字段1, 字段2) 截取子串 substr(字段名, 起始位置，

22、取字符个数) 返回字符串长度length(字符串) dual表，是专门用于函数测试和运算的，他只有一条记录 可以使用-表示从右向左数，取的时候可以从左往右取。 例：select first_name,substr(first_name,length(first_name)-1,2) from s_emp; select substr(first_name,-2,2) sub from s_emp;（取后两个） select substr(first_name,2,2) sub from s_emp;（取前两个） nvl是一般单行函数，支持任何数据类型 2.数值函数 四舍五入 round(数据,

23、保留小数点后几位) 可以用负数表示小数点前，0，表示小数点后第一位，也就是保留个位，-1表示个位（保留到十位）。 例：select round(15.36,1) from dual; 截取数字函数 trunc(数据，保留的位数（小数点后位数）) 截取个位之后补0 例：select trunc(123.456,1) from dual; 这个网上有函数的参考文档 3.日期函数 日期格式， 全日期格式 世纪信息，年月日，时分秒。 缺省日期格式，日-月-年 dd-mon-rr 修改当前会话的日期格式，会按照指定的格式输出日期 alter session set nls_date_format=yyy

24、y mm dd hh24:mi:ss; 返回当前日期 sysdate 例：select sysdate from dual; 显示今天的日期 select sysdate+1 from dual; 获得明天的日期，加1，单位是天 例: 10分钟以后的时间 select sysdate,sysdate+1/144 from dual; 日期是格式敏感的 求两个日期间相隔了多少个月 months_between(date1,date2) 加减指定数量的月份 add_months(date,月数)，月数可以为负，负值就是减去相应的月数。 从date日期开始的第一个星期五 next_day(date,

25、FriDay) 返回月末的日期 last_day(date) round 不能使用缺省的日期格式 例：select round(to_date(02-JAN-07,DD-MM-RR),MONTH) from dual; 截取日期 trunc(date,年或月或日或时分秒) 例：select next_day(sysdate,2) from dual; 例：select trunc(add_months(sysdate,1),month) from dual; 练习： 返回下个月的第一天的日期 select round(last_day(sysdate),MONTH) from dual; se

26、lect trunc(last_day(sysdate)+1) from dual; select add_months(trunc(sysdate,MONTH),1) from dual; select trunc(add_months(sysdate,1),MONTH) from dual; 4.不同数据类型间转换函数 将日期转成字符 to_char(date,日期格式) 日期格式要用有效格式，格式大小写敏感 yyyy mm dd hh24:mi:ss（完整的日期可格式） year(全拼的年),mm(2位数字表示的月) month(全拼的月)，dy(3位字母表示的星期) ，day(星期的全

27、拼)，ddspth (日期的序数词) 例：显示完整的系统日期 select to_char(sysdate,yyyy mm dd hh24:mi:ss)from dual; 显示42号部门员工的入职日期：select first_name,to_char(start_date,yyyy mm dd hh24:mi:ss) from s_emp where dept_id=42; 找出3月份入职的员工：select first_name,start_date from s_emp where to_char(start_date,mm)=03; 查询3号入职的员工 select first_na

28、me,start_date from s_emp where to_char(start_date,dd)=03; select first_name,start_date from s_emp where to_char(start_date,fmdd)=3; fm是去掉前导0 创建表：create table hiloo(c1 date);向表中插入日期(hiloo是表名) insert into hiloo values(01-JAN-07); insert into hiloo values(to_date(2007 01 01 10:10:10,yyyy mm dd hh24:mi:

29、ss); 将字符转换成数字 to_number(.) ,缺省是按十进制来算，字符串只能是0-9的数字 select to_number(ab,xx) from dual; 把ab转成十六进制 将数字转字符to_char(number，fmt) fmt是数字格式 select to_char(salary,$99,999.99) from s_emp; select to_char(salary,$00,000.00) from s_emp; #表示越界，大于显示宽度 select to_char(salary,L00,000.00) from s_emp; 将字符串转成日期 to_date(.,日期格式) 例：select to_char(to_date(2006 11 03,yyyy mm dd),dd-month-yy) from dual； 5、对字段的显示格式化 column命令 - sqlplus命令 column命令 列格式的定义 column last_name clear;清除已定义的格式 column 目标列名 查看这个类是否定义了格式 column 目标列名 format A. 设置列宽。 column last_name heading Employee|Name FORMAT A15