数据库的增删改查

回顾•数据类型分哪两种类型•装箱和拆箱的关系•简单类型以及其字节数与范围•变量命名规则•声明变量方法课程目标使用insert语句向表中插入数据使用update语句更新表中数据使用delete语句删除表中数据使用select语句进行查询示例数据库:学生--课程数据库•学生表：Student(Sno，Sname，Ssex，Sage，Sdept)•课程表：Course(Cno，Cname)•学生选课表：SC(Sno，Cno，Grade)2.1添加数据使用insert语句添加数据的一般格式：insert[into]表名[列名]values值列表省略列名--指定值的顺序必须与表中的列顺序一致只有部分列包含值insertinto员工信息表(姓名,性别,职务)values(‘张三','男','经理')insertinto员工信息表values(‘张三','经理','男')insertinto员工信息表(姓名,职务)values(‘李四',null)•使用selectinto语句复制表的结构和数据select*into普通职员表from员工信息表where职务='职员'•selectinto和insertinto都可以添加多行数据,两者的区别–insertinto语句中用于添加数据的目标表要求已经存在–selectinto语句会创建新表,要求不存在同名的表2.2更新数据update语句:可更新表中单行,行组或所有行的数据一般格式为：update表名set列名1=新值1,列名2=新值2…where条件update员工信息表set职务='职员'where姓名=‘张三’update图书信息set库存数量=库存数据+20where出版社编号=12.3删除数据•使用delete语句删除行delete[from]表名where条件deletefrom教师信息表where职务isnull•注意:如果delete语句没有where条件,则删除表中所有行,但保留表的结构delete新职员表•使用truncatetable删除表中所有行truncatetable新职员表•用truncate和delete都可以删除表的所有行,两者的区别–truncate语句不写日志,执行速度比delete语句快,但不可回滚数据查询是数据库中最常见的操作。SQL语言提供SELECT语句，通过查询操作可得到所需的信息。SELECT语句的一般格式为：2.4查询数据SELECT列名FROM表名[WHERE查询条件表达式][GROUPBY分组的列名[HAVING条件表达式]][ORDERBY排序的列名[ASC或DESC]]SELECT语句的执行过程是:根据WHERE子句的检索条件，从FROM子句指定的基本表或视图中选取满足条件的元组，再按照SELECT子句中指定的列，投影得到结果表。如果有GROUP子句，则将查询结果按照列名1相同的值进行分组。如果GROUP子句后有HAVING短语，则只输出满足HAVING条件的元组。如果有ORDER子句，查询结果还要按照列名2的值进行排序。[例3-23]查询全体学生的学号、姓名和年龄。SELECTSNo,SN,AgeFROMS[例3-24]查询学生的全部信息。SELECT*FROMS[例3-25]查询选修了课程的学生号。SELECTDISTINCTSNoFROMSC[例3-26]查询全体学生的姓名、学号和年龄。SELECTSNName,SNo,AgeFROMSSELECTSNASName,SNo,Age2.4.2条件查询运算符含义=,,,=,=,!=,比较大小AND,OR,NOT多重条件BETWEENAND确定范围IN确定集合LIKE字符匹配ISNULL空值2.4.2.1比较大小例2.5查询选修课程号为‘C1‘的学生的学号和成绩SELECTSNO,SCOREFROMSCWHERECNO=‘C1’例2.6查询成绩高于85分的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERESCORE852.4.2.2多重条件查询当WHERE子句需要指定一个以上的查询条件时，则需要使用逻辑运算符AND、OR和NOT将其连结成复合的逻辑表达式。其优先级由高到低为：NOT、AND、OR，用户可以使用括号改变优先级。例2.7查询选修C1或C2且分数大于等于85分学生的的学号、课程号和成绩。SELECTSNO，CNO，SCOREFROMSCWHERE（CNO=’C1’ORCNO=’C2’ANDSCORE=85）)2.4.2.3确定范围例2.8查询工资在1000至1500间的教师的教师号姓名及职称SELECTTNO,TN,PROFFROMTWHERESALBETWEEN1000AND1500等价于SELECTTNO,TN,PROFFROMTWHERESAL1000ANDSAL1500例3.9查询工资不在1000至1500之间的教师的教师号、姓名及职称。SELECTTNO,TN,PROFFROMTWHERESALNOTBETWEEN1000AND15002.4.2.4确定集合利用“IN”操作可以查询属性值属于指定集合的元组。例3.10查询选修C1或C2的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERECNOIN(‘C1’,‘C2’)此语句也可以使用逻辑运算符“OR”实现。SELECTSNO,CNO,SCOREFROMSCWHERECNO=‘C1’ORCNO=‘C2’利用“NOTIN”可以查询指定集合外的元组。例2.11查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。SELECTSNO,CNO,SCOREFROMSCWHERECNONOTIN(‘C1’,‘C2’)等价于：SELECTSNO,CNO,SCOREFROMSCWHERECNO!=‘C1’ANDCNO!=‘C2’最后一行也可以写成：WHERE（CNO‘C1’)AND(CNO‘C2’)2.4.2.5部分匹配查询上例均属于完全匹配查询，当不知道完全精确的値时，用户还可以使用LIKE或NOTLIKE进行部分匹配查询（也称模糊查询）。LIKE定义的一般格式为：属性名LIKE字符串常量属性名必须为字符型，字符串常量的字符可以包含如下两个特殊符号：通配符功能实例%代表0个或多个字符‘ab%’,’ab’后可连接任意字符串_下划线代表一个字符‘a_b’,’a’与‘b’间可以有一个字符[]表示在某一范围内的字符[0-9],0~9之间的字符[^]表示不在某一范围内的字符[^0-9],不在0~9之间的字符%：表示任意知长度的字符串；_：表示任意单个字符。例3.12查询所有姓张的教师的教师号和姓名。SELECTTNO,TNFROMTWHERETNLIKE‘张%’例3.13查询姓名中第二个汉字是“力”的教师号和姓名。SELECTTNO,TNFROMTWHERETNLIKE‘_力%’2.4.2.6空值查询某个字段没有值称之为具有空值（NULL）。通常没有为一个列输入值时，该列的值就是空值。空值不同于零和空格，它不占任何存储空间。例如，某些学生选课后没有参加考试，有选课记录，但没有考试成绩，考试成绩为空值，这与参加考试，成绩为零分的不同。例3.14查询没有考试成绩的学生的学号和相应的课程号。SELECTSNO,CNOFROMSCWHERESCOREISNULL注意：这里的空值条件为ISNULL，不能写成SCORE=NULL。2.4.3常用库函数及统计汇总查询SQL提供了许多库函数增强了基本检索能力。常用的库函数，如表3.2所示：函数名称功能AVG按列计算平均值SUM按列计算值的总和MAX求一列中的最大值MIN求一列中的最小值COUNT按列值同济个数例3.15求学号为S1学生的总分和平均分。SELECTSUM(SCORE)ASTotalScore,AVG(SCORE)ASAveScoreFROMSCWHERE(SNO='S1')注意：函数SUM和AVG只能对数值型字段进行计算。例2.16求选修C1号课程的最高分、最低分及之间相差的分数SELECTMAX(SCORE)ASMaxScore,MIN(SCORE)ASMinScore,MAX(SCORE)-MIN(SCORE)ASDiffFROMSCWHERE(CNO='C1')例3.17求计算机系学生的总数SELECTCOUNT(*)FROMSWHEREDEPT='计算机'例3.18求学校中共有多少个系SELECTCOUNT(DISTINCTDEPT)ASDeptNumFROMS注意：加入关键字DISTINCT后表示消去重复行，可计算字段“DEPT“不同值的数目。COUNT函数对空值不计算，但对零进行计算。例2.19统计有成绩同学的人数SELECTCOUNT(DISTINCTSNO)FROMSCWHERESCOREISNOTNULL上例中成绩为零的同学计算在内，没有成绩（即为空值）的不计算。例2.20利用特殊函数COUNT(*)求计算机系学生的总数SELECTCOUNT(*)FROMSWHEREDEPT=‘计算机’COUNT（*）用来统计元组的个数，不消除重复行，不允许使用DISTINCT关键字。2.4.4分组查询GROUPBY子句可以将查询结果按属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。例3.21查询各位教师的教师号及其任课的门数。SELECTTNO,COUNT(*)ASC_NUMFROMTCGROUPBYTNOGROUPBY子句按TNO的值分组，所有具有相同TNO的元组为一组，对每一组使用函数COUNT进行计算，统计出各位教师任课的门数。若在分组后还要按照一定的条件进行筛选，则需使用HAVIN子句。例2.22查询选修两门以上课程的学生学号和选课门数SELECTSNO,COUNT(*)ASSC_NUMFROMSCGROUPBYSNOHAVINGCOUNT(*)=2GROUPBY子句按SNO的值分组，所有具有相同SNO的元组为一组，对每一组使用函数COUNT进行计算，统计出每位学生选课的门数。HAVING子句去掉不满足COUNT（*）=2的组。当在一个SQL查询中同时使用WHERE子句，GROUPBY子句和HAVING子句时，其顺序是WHERE－GROUPBY－HAVING。WHERE与HAVING子句的根本区别在于作用对象不同：WHERE子句作用于基本表或视图，从中选择满足条件的元组；HAVING子句作用于组，选择满足条件的组，必须用于GROUPBY子句之后，但GROUPBY子句可没有HAVING子句。2.4.5查询的排序当需要对查询结果排序时，应该使用ORDERBY子句ORDERBY子句必须出现在其他子句之后。排序方式可以指定，DESC为降序，ASC为升序，缺省时为升序例3.23查询选修C1的学生学号和成绩，并按成绩降序排列。SELECTSNO,SCOREFROMSCWHERECNO='C1'ORDERBYSCOREDESC例3.24查询选修C2、C3、C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。SELECTSNO,CNO,SCOREFROMSCWHERECNOIN('C2','C3','C4','C5')ORDERBYSNO,SCOREDESC例2.25求选课在三门以上且各门课程均及格的学生的学号及其总成绩，查询结果按总成绩降序列出。SELECTSNO,SUM(SCORE)ASTotalScoreFROMSCWHERESCORE=60GROUPBYSNOHAVINGCOUNT(*)=3ORDERBYSUM(SCORE)DESC此语句为分组排序，执行过程如下：1.（FROM）取出整个SC2.（WHERE）筛选SCORE=60的元组3.（GROUPBY）将选出的元组按SNO分组4.（HAVING）筛选选课三门以上的分组5.（SELECT）