三篇基础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_NAME
3.修改字段类型
  标准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 null
is 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或任意多个字符，_表示任意一个字符。表示模糊查询的关键字是like
where 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 table
create 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_date
create table t_days(
days date
)
select * from t_days;
insert into t_days values(to_date('1987 03 04','yyyy mm dd'));
commit;
--to_cahr
select 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_number
select 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_cost
from 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_cost
from 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()函数
拼接运算符 ||
消除重复行 distinct
in 
like

单行函数
组函数--操作在一组行记录上

*/