数据库原理-第3章-结构化查询语言SQL.ppt

createtableTeacher(TnointegerPrimaryKey,Tnamechar(6)notnull,Titlechar(6),Deptchar(10));insertintoTeachervalues(101,‘李华’,‘讲师’,‘计算机’);insertintoTeacher(Tno,Tname,Dept)values(104,李春生,计算机);select*fromteacherwheredept=通信;updateteachersetdept=通信工程wheredept=通信;selectTname,Deptfromteacherwheredept=‘通信’;select*fromteacherwheredept=‘通信’andTitle=‘讲师’;deletefromteacherwheredept=计算机;createtableTeacher(TnointegerPrimaryKey,Tnamechar(6)notnull,Titlechar(6),Deptchar(10));3操作系统10333网络10123网络10223.5数据库10313.5数据库1011学分Credit课程名称Cname教师编号Tno课程编号Cno课程表－CoursecreateTableCourse(Cnointegernotnull,Tnointegernotnull,Cnamechar(10)notnull,creditnumeric(3,1)notnull,Primarykey(cno,tno));insertintoCoursevalues(1,101,‘数据库’,3.5);insertintoCoursevalues(2,101,网络,3);insertintoCoursevalues(2,102,网络,3);selectdistinctcnamefromcourse;select*fromcoursewherecredit3;select*fromcoursewherecreditbetween2and3;createTableCourse(Cnointegernotnull,Tnointegernotnull,Cnamechar(10)notnull,creditnumeric(3,1)notnull,Primarykey(cno,tno));select*fromteacherwheredeptin(计算机,自动化);select*fromteacherwheredeptnotin(计算机);select*fromteacherwheretnamelike李%;select*fromteacherwheretitleisnull;select*fromteacherorderbytnodesc;select*fromteacherorderbytitle;selectcount(*)fromteacher;selectcount(distinctcname)fromcourse;3操作系统10333网络10123网络10223.5数据库10313.5数据库1011学分Credit课程名称Cname教师编号Tno课程编号Cno自动化王华英105计算机李春生104计算机助教刘力伟103通信讲师张丽102计算机讲师李华101专业Dept职称Title姓名Tname教师编号Tno多表查询问:李华本学期讲授那几门课程?createTableCourse(Cnointegernotnull,Tnointegernotnull,Cnamechar(10)notnull,creditnumeric(3,1)notnull,Primarykey(cno,tno));createtableTeacher(TnointegerPrimaryKey,Tnamechar