数据表的管理PPT课件.ppt

第2章数据表的管理 1 目标 数据类型约束条件数据表的创建数据表的修改数据表的删除插入数据修改数据删除数据查询数据 2 数据类型 3 约束条件 4 数据表的创建 createuser student identifiedby student grant DBA to student 5 班级表 createtable student class class id varchar2 5 primarykey class name varchar2 20 uniquenotnull class place varchar2 20 notnull 6 学生表 createtable student student student id varchar2 5 primarykey name varchar2 10 notnull sex varchar2 2 check sex 男 or sex 女 notnull birthday datenotnull telephone number varchar2 20 address varchar2 30 class id varchar2 5 foreignkey class id references student class class id 7 课程表 createtable student course course id varchar2 5 primarykey course name varchar2 20 notnull course type varchar2 20 notnull hour number 10 notnull 8 课程表 createtable student score score id number 10 primarykey student id varchar2 5 course id varchar2 5 score number 4 1 notnull foreignkey student id references student student student id foreignkey course id references student course course id 9 数据表的修改 1 添加新列altertable student score add term number 2 notnull 2 修改数据类型altertable student score modify term number 1 3 添加约束altertable student score addconstraint ck term check term 0and term 10 4 删除约束altertable student score dropconstraint ck term 5 删除某列altertable student score dropcolumn term 10 数据表的删除 droptable student score droptable student student droptable student course droptable student class 11 插入数据 insertinto表名 字段名 n values 数据 n insertinto student class class id class name class place values b01 一班 301 insertinto student student student id name sex birthday telephone number address class id values c01 张三 男 to date 1990 1 1 yyyy mm dd 12345 北京 b01 insertinto student course course id course name course type hour values k01 C 编程 30 insertinto student score score id student id course id score values 1 c01 k01 95 12 修改数据 update表名set字段名 新数据 where条件 update student student set sex 女 where student id c01 13 删除数据 deletefrom表名 where条件 truncatetable表名 deletefrom student score where student id c01 truncatetable student score 14 查询数据 单表查询多表查询嵌套查询函数查询 15 单表查询 查询所有的记录查询所有记录的某些字段查询某些字段的不同记录单条件查询组合条件查询排序查询分组查询变换查询显示 16 查询所有的记录 setlinesize500 select from student class select from student student select from student course select from student score 17 查询所有记录的某些字段 select class name from student class select name sex birthday from student student select course name course type from student course select student id asstudent id score from student score 18 查询某些字段的不同记录 selectdistinct student id from student score 19 单条件查询 20 组合条件查询 21 排序查询 select from student score orderby score asc select from student score orderby score desc 22 分组查询 select sex from student student groupby sex select student id from student score groupby student id 23 变换查询显示 select class id as班级编号 class name as班级名称 class place as班级位置from student class 24 多表查询 无条件多表查询等值多表查询内连接查询外连接查询 25 无条件多表查询 select from student student student class select from student student student course student score 26 等值多表查询 select from student student s student class cwheres class id c class id selecta name b course name c score from student student a student course b student score cwherea student id c student id andb course id c course id 27 内连接查询 select from student student sinnerjoin student class cons class id c class id selecta name c course name b score from student student ajoin student score bjoin student course conb course id c course id ona student id b student id 28 外连接查询 select from student student sleftjoin student class cons class id c class id select from student student srightjoin student class cons class id c class id select from student student sfulljoin student class cons class id c class id 29 嵌套查询 简单嵌套查询带IN的嵌套查询带SOME的嵌套查询带ALL的嵌套查询带EXISTS的嵌套查询并操作的嵌套查询交操作的嵌套查询差操作的嵌套查询 30 简单嵌套查询 select from student student where student id select student id from student score where score 85 31 带IN的嵌套查询 select from student student where student id in select student id from student score where score 100 32 带SOME的嵌套查询 select from student score where score some select score from student score 33 带ALL的嵌套查询 select from student score where score all select score from student score 34 带EXISTS的嵌套查询 select from student student whereexists select from student score where score 100and student student id score student id 35 并操作的嵌套查询 insertinto student class class id class name class place values b03 三班 303 select class id from student class union select class id from student student 36 交操作的嵌套查询 select class id from student class intersect select class id from student student 37 差操作的嵌套查询 select class id from student class minus select class id from student student 38 函数查询 avg 函数count 函数min 函数max 函数sum 函数 39 avg 函数 selectavg score from student score select student id avg score from student score groupby student id 40 count 函数 selectcount from student student select sex count from student student groupby sex 41 min 函数 selectmin score from student score select student id min score from student score groupby student id 42 max 函数 selectmax score from student score select student id max score from student score groupby student id 43 sum 函数 selectsum score from student score select student id sum score from student score groupby student id 44 总结