数据库SQL 表的基本操作（create，drop，alter，insert，update，delete，select）

 

•  varchar(2000字节),varchar2(4000),nvarchar(可变长字符串),long(大文本，2G文本量)
•  日期:date(长度不限) blob,clop  timestmp=System.curranttimeMill
•  number(19,2):总长19位，小数后两位

create table pet(
   pno number (10),
   pname varchar2(20),
   sex varchar2(2)
 )
 drop table pet

• 修改原始列的数据类型:

alter table pet modify pno number(10,2)

• 追加新列:

 alter table pet add ptype vachar2(20)

• 删除原始列:

alter table pet drop column sex 
create table stu(
   sName varchar2(10),
   sNum number(10),
   sMajor varchar2(20),
   sAge number(3)
 )
 
 drop table stu
 
 alter table stu add sAge number(3)
 
 alter table stu drop column sDate

• 增加:

insert into tablename（列名1，列名2...）values(列1，列2...)

begin
 insert into stu(sname,snum,smajor,sage)
 values ('小左',20434365,'计算机',2);
 insert into stu(sname,snum,smajor,sage)
 values ('小右',20434366,'计算机',20);
 commit;
 end;

• 删除：

delete from tablename 【where tableYS = "查询关键字"】

delete from stu where sname = '小左'

• 修改：

update tablename set tableYS1 = "更改内容" where tableYS2 = "查询关键字"

update stu set sAge = 19 where sname = '小左'

• 查询：

select *【查询全部列; 可写列名1,列名2;运算显示{nvl(YS,0):为0的实际为null,此函数转为0}】 from tablename

select * from stu
select snum,sage from stu
select sage*2 from stu

• 列的拼接：||

   

select 'name：'||sname as 姓名||'age='||sage as 年龄 from stu

• 条件查询：

select * tablename where"查询条件(组合条件and,or同时用时and优先级高)"

select * from stu where sage > 5 and sage <25

create table myt(
     username varchar2(10),
     address varchar2(30),
     salary number(8),
     departmenname varchar2(15)
   )

   begin
   insert into myt(username,address,salary,departmenname)
   values ('张三','哈尔滨',6000,'事业部');
   insert into myt(username,address,salary,departmenname)
   values ('李四','北京',8000,'事业部');
   insert into myt(username,address,salary,departmenname)
   values ('张武','大连',5000,'产业部');
   insert into myt(username,address,salary,departmenname)
   values ('张三丰','重庆',4000,'工业部');
   insert into myt(username,address,salary,departmenname)
   values ('张三丰','重庆',4000,'食堂部');
   insert into myt(username,address,salary,departmenname)
   values ('刘德华','上海',7000,'哈哈部');
   commit;
   end;

• 查询出所有人的姓名，地址，年薪

   

select username,address,salary from myt

• 假设person表中所有员工每个月还有100元的奖金，查询出所有人的姓名，地址，年薪

 select username,address,salary*12+1200 from myt

• 假设person表中所有员工每年有1000元的奖金，查询出所有人的姓名，地址，年薪

select username,address,salary*12+1000 from myt

• 按部门查询

   select address，username,salary from myt where departmenname = '事业部'
   
   select username,'居住于'||address as 居住地,salary from myt where departmenname = '事业部'
   
   select username as 姓名,address as 居住地,salary as 月薪,salary*12 as 年薪 from myt
   
   select username as "姓 名",address as "居 住 地",salary as "月 薪",salary*12 as "年 薪" from myt
   

• 请查询出所有工资为6000元的人的所有信息

  

 select * from myt where salary  = 6000

• 请查询出所有工资在6000-8000之间的所有人的信息

 select * from myt where salary  between 6000 and 8000

• 请查询出所有工资是6000，7000，8000的所有人的基本信息

 select * from myt where salary  in(6000,7000,8000)

• 请查询出所有工资在6000-8000之间，并且姓张的所有人的基本信息

select * from myt where username like '张%' and salary  between 6000 and 8000

• 请查询出所有工资为6000，7000，8000，姓张，并且不叫张三丰的所有人的基本信息

select * from myt where salary  in(6000,7000,8000) and username like '张%' and username !='张三丰'

• 请查询出所有工资为5000，或者叫张三的人的所有信息

 select * from myt where salary  = 5000 or username = '张三'

• 请查询出所有人的信息，按照工资的降序排列

select * from myt order by salary

• 请查询出所有人的信息，按照年薪的降序排列  

select * from myt order by salary*12

• 请查询出所有人的信息，按照年薪的升序

select * from myt order by salary desc