三篇基础sql之1

create table student(id number,name varchar2(100));insert into student(name) values('java');insert into student(name) values('张三');select * from student;/*desc table_name;select 1 from dual;*/--desc student;select * from dual;select sysdate from dual;insert into student(name) values('jack ');/*1.增加字段     alter table docdsp     add dspcode char(200)2.删除字段     ALTER TABLE table_NAME DROP COLUMN column_NAME3.修改字段类型  标准SQL修改字段类型和长度语句：  ALTER TABLE tableName modify column columnName 类型;  例如Mysql的修改字段类型语句：  alter table test modify column name varchar(255);  Oracle修改字段类型和长度语句：  ALTER TABLE tableName modify(columnName 类型);  例如  alter table test modify(name varchar(255));4.sp_rename 改名     EXEC sp_rename '[dbo].[Table_1].[filedName1]', 'filedName2', 'COLUMN' 更改当前数据库中用户创建对象（如表、列或用户定义数据类型）的名称*/alter table student add age number(5);select * from student order by id;--使用了nvl函数select id,name,nvl(age,0) age from student order by id;update student set age=20 where id=1;select to_char(sysdate) time from dual;/*单引号是字符串的定界符，若单引号作为字符串的一部分，oracle的方式是用两个单引号表示。''''的含义是第一个和第四个字符表示定界符，第二个和第三个连起来表示单引号本身。*/select 'my name is '||''''||name||''''||' age is '||nvl(age,0) abc from student;insert into student(name,age) values('JiNan',200);select * from student order by id;select * from student where name='jinan';/*upper函数将字符串转换成大写，lower函数将字符串转换成小写，initcap函数将字符串转换成首字符大写。*//*round函数表示四舍五入，trunc函数表示截取*/select round(16.3) from dual;select round(16.5) from dual;select round(16.56,1) from dual;select round(16.56,-1) from dual;select round(116.56,-2) from dual;select trunc(16.5) from dual;select trunc(16.56,1) from dual;select trunc(16.56,-1) from dual;select * from student where lower(name)='jinan';/*is nullis not null<> 不等于in not in*//*order by colname asc，表示升序排列，asc可以省略；order by colname desc，表示降序排列；order by id ;order by id desc;order by后面可以跟表达式 order by base_cost*12 desc;order by后面可以跟列别名 select ...base_cost*12 ann_cost .... order by ann_cost desc;order by后面可以跟列位置 order by 2 desc;order by子句后面可以跟多列，结果集先按第一列排列，若列值一样，再按第二列排列 order by unix_host,create_date desc*/select * from student order by id;update student set age=9 where id=21 ;commit;select * from student order by age,id;select * from student order by age,id desc;select * from student order by age desc,id;/*where base_cost >= 5 and base_cost <= 10;where base_cost between 5 and 10;(是[])-------where base_cost = 5.9  or base_cost = 8.5  or base_cost = 10.5;where base_cost in (5.9,8.5,10.5);where base_cost in (5.9,8.5,10.5,null);（错误）where base_cost in (5.9,8.5,10.5) or base_cost is null--------该查询属于模糊查询。oracle中的通配符有两种，%表示0或任意多个字符，_表示任意一个字符。表示模糊查询的关键字是likewhere os_username like 'h%'*/select * from student where name like 'j%';insert into student (name,age) values ('h_apple',180);insert into student (name,age) values ('hello',180);select * from student where name like 'h_%';--此时的_并不表示下划线本身，而是任意一个字符，若想表示下划线本身，需要转义，用如下方式表示：select * from student where name like 'h\_%' escape '\';/*alter session set nls_date_format = 'yyyy mm dd hh24:mi:ss';*//*-- Create tablecreate table STUDENT(  id   NUMBER not null,  name VARCHAR2(200),  age  NUMBER(5))tablespace USERS  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64    minextents 1    maxextents unlimited  );-- Create/Recreate primary, unique and foreign key constraints alter table STUDENT  add constraint STUDENT_PK primary key (ID)  disable;   */--to_datecreate table t_days(days date)select * from t_days;insert into t_days values(to_date('1987 03 04','yyyy mm dd'));commit;--to_cahrselect to_char(days,'yyyy mm dd hh24:mi:ss') datetime from t_days;select to_char(days,'mm') from t_days;select to_char(days,'mm') from t_days;--若要在'mm'前增加'fm'。'fm'的含义是去掉前导0或两边的空格。select to_char(days,'fmmm') from t_days;--to_numberselect to_number(to_char(days,'fmmm')) from t_days;--昨天，今天，明天select sysdate-1,sysdate,sysdate+1 from dual;--十分钟之后select sysdate,sysdate + 1/144 from dual;-- 上个月的今天，今天，下个月的今天select add_months(sysdate,-1),sysdate,add_months(sysdate,1) from dual;-- 当前月的最后一天select last_day(sysdate) from dual; /*开通天数是一个计算值，用sysdate减去create_date，两者的时、分、秒的差距会使结果包含小数，用round函数保留到天。select create_date, round(sysdate - create_date) days from service;select create_date,  ound(sysdate - create_date) days from service order by sysdate - create_date desc;*//*case when用于解决不同记录需要不同处理方式的问题。when后面跟条件表达式，当所有when条件都不满足时，若有else，表达式的返回结果为其后的值，否则返回null值。select     base_duration,base_cost,            case when  base_duration = 20 then unit_cost + 0.05                 when  base_duration = 40 then unit_cost + 0.03            else                 unit_cost            end new_unit_costfrom cost;*/select * from student order by id;select * from student where age between 18 and 20 order by id;/*decode函数用于解决不同记录需要不同处理方式的问题。若base_duration= 20,那么函数返回值为unit_cost + 0.05;若base_duration= 30,那么函数返回值为unit_cost + 0.03;否则返回unit_cost值。若没有最后一个参数unit_cost，函数返回值为null。select base_duration,base_cost,           decode(base_duration,20,unit_cost + 0.05,                                 30,unit_cost + 0.03,                                unit_cost) new_unit_costfrom cost;*/--和sum()，平均值avg()，最大值max()，最小值min()以及个数的计算count()是通过组函数实现的--组函数处理所有的非空数据,即组函数处理数据时忽略null值/*若null值参与运算，必须将null值转换成非null值,avg(unit_cost)的计算用nvl()。count(unit_cost)统计某列非null值的个数，count(*)统计记录数。若unit_cost列中参与运算的数据都为null，avg(unit_cost)的函数值为null，count(unit_cost)的函数值为0*/--group by ... having ...select *  from student order by id;/*Oracle查表结构、属主、所有表等语句select * from dba_tables 查系统中所有表select * from all_tab_columns where table_name = 'TableName' and Owner = 'OwnerName' 查系统中某个具体表结构select * from tab where tabtype = 'TABLE' 查当前用户下的所有表，tabtype可变select   *   from   user_tables; 查当前用户下的所有表  dba_开头的：所有用户的（管理员用户）     all_开头的：当前用户可以访问的所有     user_开头的：当前用户创建的*/select * from dba_tables;select * from all_tables;select * from user_tables;select * from tab where tabtype = 'TABLE';select * from all_tab_columns where table_name = 'STUDENT' and Owner = 'QING' ;alter table student  add c_class varchar2(200);alter table student  add c_school varchar2(200);ALTER TABLE student DROP COLUMN c_school;select * from student order by id;update student set c_class='5', c_school='郑州' where id=1;update student set c_class='6', c_school='郑州' where id=2;update student set c_class='8', c_school='北京' where id=21;update student set c_class='8', c_school='北京' where id=24;update student set c_class='9', c_school='北京' where id=25;commit;alter table student modify(c_class varchar(100));--和sum()，平均值avg()，最大值max()，最小值min()以及个数的计算count()是通过组函数实现的select * from student order by id;select c_school,count(*) from student  group by c_school;--group by后面可以跟多列，这样分组的粒度更细，即组的个数多了，每组的记录少了select c_school,count(*) from student  group by c_school,c_class;select c_school,c_class,count(*) from student  group by c_school,c_class;--用having子句进行条件判断select c_school,c_class,count(*) from student  group by c_school,c_class having count(*)>1;select id,count(*) from student  group by id;select min(id) from student;/*number(5,1) 12345.1缺省值  defaule 1;列别名空值null处理nvl()函数拼接运算符 ||消除重复行 distinctin like单行函数组函数--操作在一组行记录上*/