VF第6章sql查询语言ppt课件

.,第六章SQL查询语言的使用,.,学习目标：（1）了解SQL的基本知识（2）掌握SQL的数据查询操作（3）熟悉SQL的数据定义功能（4）熟悉SQL的数据操纵功能,.,6.1SQL基础知识,SQL（StructuredQueryLanguage）结构化查询语言，是一种介于关系代数与关系运算之间的语言。,SQL语言动词,.,6.2SQL的数据定义功能,一、创建表,格式1：CREATETABLEFREE(类型(长度),类型(长度),功能：创建一个以为表的名字，以指定的字段属性定义数据表。,说明：定义表的各个属性时，需要指明其数据类型及长度。,.,例1：创建新表stu2，其结构和学生表相同。,creattablestu2(学号C(8)，姓名C(8)，性别C(2)，出生日期D，入校总分N(3，0)，三好生L，特长M，照片G）,格式2：CREATETABLEFREE(类型(长度),类型(长度)NULL|NOTNULLCHECKERROR提示信息DEFAULT,.,FREE：说明定义的表是自由表。,NULL：允许一个字段为空值。如果一个或多个字段允许包含空值，一个表最多可以定义254个字段。,NOTNULL：不允许字段为空值，即字段必须取一个具体的值。,CHECK：定义字段级的有效性规则。是逻辑型表达式。,.,ERROR提示信息：定义字段的错误信息。当字段中的数据违背了字段的完整性约束条件时，VisualFoxPro就会显示“提示信息”定义的出错信息。,DEFAULT：定义字段的默认值，的数据类型必须和字段类型一致。,.,例2：在数据库xsgl.DBC中，使用命令建立“jsj.DBF”表，表结构包括(学号C(8),姓名C(6),笔试N(5,1),上机N(5,1)，并设置学号为主索引，笔试和上机的有效性规则是0，如果违反有效性规则，系统提示：“成绩必须大于0”。,creatdatabasexsgl.DBC,creattablejsj.DBF(学号C(8)primarykey,姓名C(6),笔试N(5,1)check笔试0error“成绩必须大于0”,上机N(5,1)check上机0error“成绩必须大于0”),.,二、修改表的结构,1、增加字段,格式：ALTERTABLEADD(,)；NULL|NOTNULLCHECKERROR提示信息；PRIMARYKEY|UNIQUE,功能：为指定的表的指定字段进行添加指定的字段。,.,例3：为xsdb表添加两个字段：“平均分”字段N(5,1)和“总分”字段N(5,1)。,altertablexsdb.dbfadd平均分N(5,1)check平均分80error“平均分要大于80!”altertablexsdb.dbfadd总分N(5,1),注意：check对非数据库表（.dbc）不可用。,.,2、修改字段,格式：ALTERTABLEALTER类型(长度)NULL|NOTNULLSETCHECKERROR提示信息,功能：为指定的表的指定字段进行修改指定的字段。,.,例4：在xsdb表中，修改两个字段：“学号”字段C（10）和“英语”字段N（6,1）。,altertablexsdb.DBFalter学号C(10)altertablexsdb.DBFalter英语N(6,1),例5：修改或定义上机字段的有效性规则。,altertablejsj.dbfalter上机setcheck上机0error上机应该大于0!,.,3、删除字段,ALTERTABLEDropDrop;,功能：删除指定的表中指定字段。,例6：删除xsdb表中的平均分、奖学金和备注字段。,altertablexsdb.dbfdrop平均分drop奖学金drop备注,.,4、修改字段名,格式：altertablerenamecolumnto,功能：将表中的名字修改为。,例7：将jsj表的笔试字段名改为笔试成绩。,altertablejsj.dbfrenamecolumn笔试to笔试成绩,.,三、删除表,格式：DROPTABLE,功能：从数据库和磁盘上将表直接删除掉。,例8：删除jsj表,droptablejsj.dbf,.,四、视图的定义和删除,格式:CREATEVIEWASSELECT-SQL命令。功能：按照AS子句中的SELECT-SQL命令提出的查询要求，创建一个本地或远程的SQL视图。视图的名称由命令中指定。,1、定义视图,.,例9：从“xsgl”数据库所含有的学生和选课两个表中抽取学号、姓名、和课程号3个字段，组成名称为“myview”的SQL视图。,opendatabasexsgl,creatviewmyviewasselect学生.学号，学生.姓名，选课.课程号from学生.dbf，选课.dbfwhere学生.学号=选课.学号,.,2、查询视图,例10：查询“xsgl”数据库中的“myview”视图，要求显示学生姓名和课程号,opendatabasexsgl,select姓名，课程号frommyview,.,3、删除视图,格式：dropview,功能：删除数据库中指定的视图。,例11：删除数据库“xsgl”中的“myview”视图,opendatabasexsgl,dropviewmyview,.,6.3SQL的数据查询功能,一、SELECT语句格式,SELECTALL|DISTINCT.AS;FROM联接方式JOIN表名ON联接条件;WHERE;ORDERBYASC/DESC;GROUPBY;HAVING分组筛选条件;INTO;功能：查询。,.,二、投影查询,1、查询部分字段,在select语句后列出要查询的字段，之间用逗号隔开。,例1：从学生表中查询学号，姓名，性别和出生日期。,select学号，姓名，性别，出生日期from学生,.,2、查询全部字段,可以在select后面列出全部字段，也可以用星号“*”来表示全部字段。,例2：查询教师表中的全部字段,select*from教师,select教师号，姓名，性别，职称，工资，政府津贴from教师,或者,.,3、取消重复记录,使用distinct取消查询结果中的重复记录,例3：查询选课表中有成绩记录的学生学号。,selectdistinct学号from选课,.,4、查询经过计算的表达式,查询的列可以是字段，也可以是计算后的表达式。,例4：从教师表中查询教师的姓名，工资和涨50%以后的工资。,select姓名，工资，工资*1.5from教师,select姓名，工资，工资*1.5as涨后工资from教师,或者,as用来修改查询结果中指定列的列名。其中as可以省略,.,三、条件查询,使用where来指定查询条件，常用的比较运算符如表,.,.,1、简单条件查询,例5：从学生表中查询三好生的信息,select*from学生where三好生=.t.,例6：从学生中查询7月份所生的学生的学号、姓名、性别和出生日期,select学号，姓名，性别，出生日期from学生wheremonth（出生日期）=7,.,例7：从学生表中查询入校总分大于600分的女生或者入校总分小于600的男生的学号、姓名和入校总分。,select学号，姓名，入校总分from学生where（入校总分600and性别=“女”）or（入校总分1990/01/01and(入校总分=600or入校总分80,.,六、查询的排序,使用orderby可以按字段值排序，asc为升序，desc为降序，默认为升序。,1、单列排序,例21：查询学生表中的学生信息，并按照入校总分的降序排列。,select*from学生orderby入校总分desc,.,例22：统计选课表中每门课程的平均成绩，并按照平均分数的降序排列,select课程号，avg（成绩）as平均成绩from选课groupby课程号orderby平均成绩desc,select课程号，avg（成绩）as平均成绩from选课groupby课程号orderby2desc,或者,2表示以查询结果的第2列作为排序依据,.,2、多列排序,例23：查询学生表中的学生信息，并按照入校总分的降序排列，若入校总分相同，按照学号的升序排列。,select*from学生orderby入校总分desc，学号,.,七、内连接查询,连接条件：两表的公共字段值相等，常用书写格式是：表1.公共字段=表2.公共字段,例23：从授课表和课程表中，查询各个教师担任的课程的课程号及课程名。,select授课.教师号，授课.课程号，课程.课程名from授课，课程where授课.课程号=课程.课程号,select授课.教师号，授课.课程号，课程.课程名from授课join课程on授课.课程号=课程.课程号,或者,.,例23：学生表和选课表中查询学号为s0803004和s0803005的学生的选课情况，显示其学号、课程号和成绩。,select学生.学号，选课.课程号，选课.成绩from学生，选课where学生.学号=选课.学号and（学生.学号=“s08030004”or学生.学号=“s08030005”）,select学生.学号，选课.课程号，选课.成绩from学生join选课on学生.学号=选课.学号where（学生.学号=“s08030004”or学生.学号=“s08030005”）,或者,.,八、自连接查询,将同一个表与其自身进行连接，称为自连接。在自连接查询涉及的字段前面，用别名加以限制。定义表别名的语法是：,例24：查询入校总分大于谢小芳的学生姓名、入校总分。,select学生.姓名，学生.入校总分from学生，学生bwhere学生.入校总分b.入校总分andb.姓名=“谢小芳”,.,九、修改查询去向,1、intodbfintotable将查询结果保存到以命名的永久表中，并打开该表作为当前文件,2、intocursor将查询结果保存到以命名的临时表中，该文件为只读表，当关闭查询相关的表文件时，该临时文件会被自动删除。,.,3、intofileaddtive将查询结果保存到以命名的文本文件中，扩展名为txt，如果有addtive，查询结果追加到文本文件的尾部，否则覆盖该文本文件。,4、intoarray将查询结果保存到指定的数组中，一般将存放查询结果的数组作为二维数组来使用。,5、toprinter将查询结果输出到打印机。,.,十、嵌套查询,1、带比较运算符的嵌套查询,例25：查询入校总分大于谢小芳的学生姓名、入校总分。,（1）查询谢小芳的入校总分,select入校总分from学生where姓名=“谢小芳”,此结果的显示为：谢小芳入校总分为610,.,（2）查询入校总分大于610分的学生姓名、入校总分。,select姓名，入校总分from学生where入校总分（select入校总分from学生where姓名=“谢小芳”）,（3）将610用第（1）步中的select语句替换,select姓名，入校总分from学生where入校总分610,.,2、in谓词嵌套查询,例26：查询有选课记录的学生信息。,（1）在选课表中查询选了课的学生学号,select(distinct学号)from选课,查询结果为“s0803002”，“s0803003”，“s0803004”，“s0803005”,（2）查询学号为“s0803002”，“s0803003”，“s0803004”，“s0803005”的学生信息。,select*from学生where学号in（“s08030002”，“s08030003”，“s08030004”，“s08030005”）,.,（3）将“s0803002”，“s0803003”，“s0803004”，“s0803005”用第（1）步中的select语句替换,select*from学生where学号in（selectdistinct学号from选课）,.,3、exists谓词嵌套查询,例27：查询学号为s0803002的学生选修的课程名称,select课程名from课程whereexists（select*from选课where选课.课程号=课程.课程号and学号=“s0803002”）,例28：查询学号为s0803002的学生没有选修的课程名称,select课程名from课程wherenotexists（select*from选课where选课.课程号=课程.课程号and学号=“s0803002”）,.,4、any、all、some谓词嵌套查询,例29：查询入校总分大于某个三好生的女生的信息。,select*from学生where入校总分any(select入校总分from学生where三好生=.t.)and性别=“女”,例30：查询入校总分大于所有三好生的学生信息。,select*from学生where入校总分all(select入校总分from学生where三好生=.t.),此处的any也可以用some替换,.,十一、集合查询,用union可以进行集合查询,例31：查询入校总分大于600分或者小于590分的学生姓名。要求使用集合查询。,select姓名from学生where入校总分600unionselect姓名from学生where入校总分590,注意：使用union集合查询时，会自动取消重复记录。,.,6.4SQL的数据操纵功能,一、插入数据,格式：insertinto(,)values(,),例1、在课程表中插入一条新记录（“c170”，“操