Oracle基本语句.docx

创建表create table person(pid number(10),name varchar2(20),salary number(8,2))设置主键alter table person add constraint person _id primary key (pid)添加字段alter table personadd (grade number,phone varchar2(20))修改字段alter table personmodify (grade number(2),phone varchar2(50))删除字段alter table persondrop (grade, phone)重命名表rename old_name to new_name;rename person to newperson;删除表drop table newpersonDMLInsert插入insert into stuinfo (sid,sname,sex,age,telephone,address)values (1,张三,男,18,1351456977,沈阳)更新数据update stuinfoset sname=王五, age=21where sid=1删除数据delete from stuinfo where sid=1删除所有数据delete from stuinfoselect 用法select * from stutestselect sid,sname from stutestdistinct用法select distinct sex from stutestwhere 子句可以使用的操作符号=, , , =, =, !=not 用法select * from stutest where not sid=7select * from stutest where not(age=18 and age=25) and sex=‘女between用法select * from stutest where (grade between 80 and 100) and sex=‘男‘In用法select * from stutest where sid in(1,3,5,7,9)like模糊查询（‘_’一个字符，‘%’n个字符）select * from stutest where sname like ‘郭_order by排序用法（默认esc升序，sesc降序）select * from 表名 order by 字段 desc/ascselect * from stutest order by grade asc,sid desc（先按成绩升序，成绩相同后按学号降序排列）联合查询select * from student,lession where student.sclass=lession.sclass and sid=1select * from student join lession on student.sclass=lession.sclass where sid=1表别名select * from student s, lession lwhere s.sclass=l.sclass and sid=1聚合函数sum()作用：返回数字列或计算列的总和例：Select sum(sgrade) from gradeavg()作用：返回一个数字列的平均值例：Select avg(sgrade) from grademax()作用：返回一个数字列的最大值例：Select max(sgrade) from grademin()作用：返回一个数字列的最小值例：Select min(sgrade) from gradeCount()作用：返回查询的记录数Select count(*) from studentselect count(distinct sex) from studentGroup by 分组查询字段列表中有聚合函数并且有其他字段时，必须使用group by 分组查询select s.sname , sum(sgrade) from student s ,grade gwhere s.sid=g.sid and s.sid=1group by s.sname说明：1.字段列表中除了聚合函数字段外，其他有多少字段，在group by子句列出多少字段2.where条件，group by,order by的顺序Having用法group by 语句中筛选条件中包含聚合函数，使用having子句select sid, avg(sgrade) from grade group by sidhaving