SQL 语言学习笔记

SQL 语言学习笔记

SQL语言学习笔记《脑动力SQL语言实务速查效率手册》第二章SQL中的数据类型和运算符：数据类型SQL支持的数据类型主要包括预定义的数据类型和用户自定义的数据类型。其中，SQL预定一的数据类型主要包括数值类型、字符串类型、日期类型和布尔值等数据类型。数值类型之小数类型——NUMBER如果形式为Number（size），则表明最大数字的位数由括号中的参数size设置；如果形式为Number（size,d），则表明最大数字的位数由括号中的参数size设定，而括号中的参数d是设置小数点的位数。注意：不同的数据库管理系统所支持的数据类型不尽相同，例如在SQLServer中Number数据类型被写成Decimal或Numeric。字符串类型字符串类型是最常用的数据类型之一。字符型变量可分为固定长度字符和可变长度字符两种。固定长度字符变量的字符数在数据表创建时就指定了，并分配了存储空间。例如，指定通信录表姓名的字符数为10，如果输入的字符数超过10，那么数据库只记录前10个字符，如果输入的字符数少于10个，那数据库会自动在字符右边以空格填补到10个字符。可变长度字符串类型——Varchar、Text固定长度字符串类型——Char长度为n个字节，n的取值范围为不超过255个字节。例如：C＿Namechar（8）如果C＿Name只有4个字符，那么其余4个字符将是空格。日期类型Datetime和smalldatetime是最常见的两种日期数据类型。Datetime数据类型所占用的存储空间为8字节。其中一个4字节存储“基础日期”（即1900年1月1日）之前或之后的天数；另一个4字节存储日期的时间（以午夜后经过的毫秒数表示）。Smalldatetime的值是存储为4字节的整数。其中一个2字节存储1900年1月1日后的天数；另一个2字节存储午夜后经过的分钟数。日期输入格式（1）英文＋数字格式：月份可用英文全名或缩写，且不区分大小写；年和月日之间可不用逗号；年份可为4位或2位，当其为2位时，若值小于50，则视为20XX年，若大于或等于50，则视为19XX年；若日部分省略，则视为当月的1号。如June182010Oct12001January20102011February（2）数字＋分隔符格式：允许把斜杠（/）、连接符（-）和小数点（.）作为数字表示的年、月、日之间的分隔符。格式如下YMD：2012/1/222012-1-222012.1.22MDY:3/5/20103-5-201131.12.2011（3）纯数字格式：是以连续的4位、6位或8位数字来表示日期时间输入格式在输入时间时，必须按“小时、分钟、秒、毫秒”的顺序来输入，在其间用冒号“：”隔开。《脑动力SQL语言实务速查效率手册》第二章SQL中的数据类型和运算符：运算符\n在SQL语句中，可以运用各种运算符和函数直接对查询列的数据进行一些处理。将处理后的结果显示给用户。算术运算符“+”：表示数值相加运算“-”：表示数值想减运算“*”：表示数值相乘运算“/”：表示相除运算以上运算符的优先级顺序与算数运算中的优先级顺序基本相同。例如Selects＿title,s＿value,s＿trans,s＿value+s＿transasall＿valuesfromt＿sale其中的asall＿values是s＿value+s＿trans的别名算术运算符的组合使用如从销售信息表（t＿sale）中查询货物名称（s＿title）和销售获取总金额（s＿value与s＿trans之和再乘以s＿nums）信息Selects＿title,(s＿value+s＿trans)*s＿numsfromt＿sale逻辑运算符包括逻辑与（AND）运算符和逻辑或（OR）运算符逻辑与（AND）运算符表示查询在表达式中既符合条件1又符合条件2的数据。也就是只有同时满足两个查询条件，才能查询出符合条件的记录。如果不同时满足，则查询的结果为空值（NULL）说明：AND运算符也可以连接两个以上的查询条件。如condition1ANDcondition2ANDcondition3,此时需要同时满足3个查询条件。在一个where子句中，可以同时使用多个AND运算符连接多个查询条件。此时，必须满足所有的查询条件记录，才能查询出所需的数据信息。逻辑或（OR）运算符表示在表达式中查询符合条件1或符合条件2的数据说明：与AND运算符一样，OR运算符也可以连接两个以上的查询条件，此时，只要满足一个条件即可。逻辑运算符的组合使用AND和OR可以单独使用，也可以组合使用，但会涉及运算符的优先级问题，在SQL中，AND运算符的优先级要高于OR运算符。注意：括号（）的优先级要高于AND运算符和OR运算符的优先级。在将AND运算符与OR运算符进行组合使用时，建议采用括号（）来表现需要的执行顺序，这样可以增强程序的可读性。《SQL技术与网络数据库开发详解》第08章高级条件查询组合WHERE子句使用IN运算符NOT运算符实现模糊查询8.1组合WHERE子句使用AND和OR运算符设置高级查询条件的具体方法。AND和\nOR两个运算符可以将单独的条件表达式组合在一起。8.1.1AND运算符AND运算符只有当两边操作均为True时，最后结果才为True。使用AND描述“与”（而且）的关系，即，既满足第一个条件且满足第二个条件时才会通过审核。如：从Student表中查询计算机系的所有女生，并将结果按学号升序排序Select*fromstudentwhere所属院系=‘计算机系’AND性别=‘女’orderby学号如：从Student表中查询1975年出生的所有学生，并将结果按出生日期升序排序。（1）如果SQL运行环境为Access，则因为日期型数据中没有时间，可以使用如下SELECT语句查询：Select*fromstudentwhere出生日期﹥＝#01/01/1975#AND出生日期﹤＝#12/31/1975#orderby出生日期（2）如果SQL运行环境为SQLServer，则因为日期型数据中有时间，可以使用如下SELECT语句查询：Select*fromstudentwhere出生日期﹥＝‘01/01/1975’AND出生日期﹤‘01/01/1976’orderby出生日期（3）如果SQL运行环境为Oracle，也因为日期型数据中有时间，可以使用如下SELECT语句查询：Select*fromstudentwhere出生日期﹥＝‘01/JAN/1975’AND出生日期﹤‘01/JAN/1976’orderby出生日期根据需要可以使用多个AND组合条件，如下题从Student表中查询1975年出生的所有女生，并将结果按出生日期升序排序。假设SQL运行环境为SQLServer。Select*fromstudentwhere出生日期﹥＝‘01/01/1975’AND出生日期﹤‘01/01/1976’AND性别=‘女’orderby出生日期8.1.2OR运算符OR运算符只有当两边操作数均为False时，最后结果才为False，只有一边是True，则最后结果为True。根据OR的这种运算规则，使用OR运算符描述“或”（或者）的关系，即当满足任何一个条件就可以通过审核。从Student表中查询中文系的所有学生和外语系的所有学生，并将结果按学号升序排序分析：本题两个条件的关系其实是“或”，因为满足任何一个条件就可以通过审核Select*fromstudentwhere所属院系=‘中文系’OR所属院系=‘外语系’orderby学号8.1.3AND与OR的优先顺序问题WHERE子句中可以包含任意数量的AND和OR运算符，并且允许两者结合使用。如从Student表中查询中文系和外语系的所有女生Select*fromstudentwhere所属院系=‘中文系’OR所属院系=‘外语系’AND性别=‘女’orderby学号如果按上面的语句，可能会造成查询结果的错误，根源是运算符的优先级问题，在表达式中，如果出现了AND和OR两种运算符，则并非从左到右按顺序运算，而是优先执行AND，然后执行OR运算符。\n所以上面的条件表达式与下面的表达式等价：所属院系=‘中文系’OR（所属院系=‘外语系’AND性别=‘女’）而这表达式的意思是：中文系的所有学生和外文系的所有女生。为了让OR运算符优先执行，可以使用括号，该题正确的查询语句为Select*fromstudentwhere（所属院系=‘中文系’OR所属院系=‘外语系’）AND性别=‘女’orderby学号技巧：在有多种运算符的组合条件表达式中，尽量使用括号，即使计算机不需要使用这些括号，但这样会方便人们阅读和理解复杂的条件表达式，同时也会减少出错的概率。8.2使用IN运算符在查询中，遇到的查询任务是——指定的字段值只要属于某个集合，就将该记录查询出来，此时，会用到IN运算符。8.2.1使用IN运算符IN运算符的运算规则是：当X在集合{Value1，Value2，……ValueN}中时，表达式XIN（Value1，Value2，……ValueN）为True，而X不在集合{Value1，Value2，……ValueN}中时，上面的表达式为False。例如：8IN（2，5，8，13）因为8在集合中，所以表达式的值为True。对于7IN（2，5，8，13）因为7不在集合{2，5，8，13}中，所以表达式的值为False。例题：从Course表中查询学分为2、3、4的课程的信息，并按学分降序，课号升序排序。Select*fromcoursewhere学分IN（2，3，4）orderby学分DESC，课号说明：在IN运算符表达式中，集合必须用圆括号括住，并且各元素之间用逗号（，）分隔。例题：从从Student表中查询中文系、外语系和计算机系的所有学生，并按院系降序排列。Select*fromstudentwhere所属院系IN（‘中文系’，‘外语系’，‘计算机系’）orderby所属院系DESCIN运算符还有一个反向运算符——NOTIN，见下例从从Student表中，查询除中文系、外语系和计算机系以外的其他系的学生，并按院系降序排列。Select*fromstudentwhere所属院系NOTIN（‘中文系’，‘外语系’，‘计算机系’）orderby所属院系DESC8.2.2使用IN运算符的优点感觉IN运算符和OR运算符实现的功能是相同的，但IN运算符有如下优点：■当条件很多时，使用IN运算符会使语句更加简洁、清楚。如对上述例题：从从Student表中查询中文系、外语系和计算机系的所有学生，并按院系降序排列。使用OR运算符改写\nSelect*fromstudentwhere所属院系‘中文系’OR所属院系‘外语系’OR所属院系‘计算机系’orderby所属院系DESC跟使用IN运算符相比，有点繁琐。■IN运算符的执行速度比OR运算符更快■IN运算符最大的优点是：其后条件列表集合中，可以放置其他SELECT语句，即子查询。见下例：从Score表中，查询所有学生中的“心理学”的考试成绩和平时成绩，并按考试成绩降序排列，当考试成绩相同时按平时成绩降序排列。分析：因为Score表中没有课名只有课号，因此，必须从Course表中找到“心理学”的课号，然后根据这一课号从Score表中查询试成绩和平时成绩SELECT学好，考试成绩，平时成绩FROMScoreWHERE课号IN（SELECT课号FROMCOURSEWHERE课名=‘心理学’）ORDERBY考试成绩DESC，平时成绩DESC。8.3NOT运算符NOT运算符的作用是对其后的表达式求反。例如从Student表中查询来源地不是北京市和广东省的所有学生。SELECT*FROMSTUDENTWHERENOT(来源地=‘北京市’OR来源地=‘广东省’)如果本例中不使用NOT运算符，而采用不等于（﹤﹥）运算符，则大多数初学者可能会编写如下的SELECT语句SELECT*FROMSTUDENTWHERE来源地﹤﹥‘北京市’OR来源地﹤﹥‘广东省’但结果是错误，这跟OR运算符的规则有关，正确的应该写成SELECT*FROMSTUDENTWHERE来源地﹤﹥‘北京市’AND来源地﹤﹥‘广东省’NOT运算符不仅可以对表达式求反，还可以和一些特殊运算符结合使用，例如ISNOTNULL、NOTBETWEEN和NOTIN等就是结合使用的例子。例如从STUNTET表中，查询出生日期不在1978-1980之间（包含1978和1980年）的所有学生。（1）假设SQL运行环境为SQLSERVER，则其SELECT语句为SELECT*FROMSTUDENTWHERE出生日期NOTBETWEEN‘01/01/1978’AND‘12/31/1980’说明：当出生日期的时间部分都为0，所以上面的查询能够正确运行。如果时间部分不为0，则应编写另外的语句查询。看下面的实例\nSELECT*FROMSTUDENTWHEREDATEPART(YY，出生日期)NOTBETWEEN1978AND1980上式的DATEPART函数相关内容？（2）假设SQL运行环境为ACCESS，则其SELECT语句为SELECT*FROMSTUDENTWHERE出生日期NOTBETWEEN#01/01/1978#AND#12/31/1980#（3）假设SQL运行环境为ORACLE，则其SELECT语句为SELECT*FROMSTUDENTWHERE出生日期NOTBETWEEN‘01/0JAN/1978’AND‘31/DEC/1980’注意：在设置查询条件时，应尽量避免使用否定条件，例如NOTBETWEEN、NOTIN等，因为有些DBMS不能优化这些条件查询。8.4实现模糊查询8.4.1LIKE运算符结合使用LIKE运算符和通配符可以对表进行模糊查询，即仅仅使用查询内容的一部分查询数据库中存储的数据。需要注意的是LIKE运算符只支持字符型数据。从STUDENT表中查询中文系所有学生的信息，并按学号升序排序。Select*fromstudentwhere所属院系LIKE‘中文系’orderby学号LIKE运算符也可以和NOT结合使用，例如从STUDENT表中查询不是中文系的学生的信息，并按所属院系升序排序Select*fromstudentwhere所属院系NOTLIKE‘中文系’orderby所属院系本例中的NOTLIKE的功能与不等于（﹤﹥）运算符相同。8.4.2“％”通配符在SQL语言中，使用百分号（％）通配符代表0个或多个字符。见下面的典型例子下例演示了结合使用“％”和LIKE运算符，实现模糊查询功能的具体方法\n从STUDENT表中，查询所有姓名中包含“三”字的学生信息Select*fromstudentwhere姓名LIKE‘%三%’如果在数据表中插入两条新记录，插入语句如下：INSERTINTOStudent（学号，姓名，性别，出生日期）VALUES（‘0011’，‘周三丰’，‘男’，‘12/20/1981’）INSERTINTOStudent（学号，姓名，性别，出生日期）VALUES（‘0012’，‘三宝’，‘男’，‘05/15/1983’）执行后，查看插入结果Select*fromstudent再次运行下面查询Select*fromstudentwhere姓名LIKE‘%三%’如果将“%三%”中第一个%去掉，成为“三%”就表示查询头一个字为“三”的所有字符串，如果去掉后面的成为“%三”，那是查询最后一个字为“三”的所有字符串。8.4.3使用“%”通配符查询日期型数据使用“%”通配符查询日期时间型数据很方便。比如从STUDENT表中，查询出生于1980年的所有学生Select*fromstudentwhere出生日期LIKE‘%1980%’注意：本例中的%1980%不可以写成1980%或%1980从STUDENT表中，查询出生于9月份的所有学生Select*fromstudentwhere出生日期LIKE‘09%’从STUDENT表中，查询出生于1975年9月份的所有学生Select*fromstudentwhere出生日期LIKE‘09%1980%’虽然使用“%”通配符查询年份和月份比较方便，但是查询日却并不理想。慎用8.4.4“＿”通配符“%”通配符可以代表0个或多个字符，但是它不能代表指定个数的字符。例如，需要查询姓“周”，且名字由两个字组成的所有学生。这时就使用下划线（＿）通配符，它只代表任意一个字符（包括0个字符）。例如“周＿”代表以“周”字开头的，最多由两个汉字组成的字符串。说明：如果SQL运行环境为ACCESS，则使用问号（？）通配符代替下划线（＿）通配符。从STUDENT表中，查询姓“周”，而且名字最多由3个字组成的学生。Select*fromstudentwhere姓名LIKE‘周＿＿’\n注意：“周”后有两个下划线通配符从STUDENT表中，查询姓“周”，而且名字必须是3个字的学生。Select*fromstudentwhere姓名LIKE‘周＿＿’AND姓名NOTLIKE‘周＿’注意：第一个“周”后有两个下划线通配符，第二个“周”后有一个下划线通配符。“＿”通配符也可以不与字符组合，而单独使用。从STUDENT表中，查询名字最多由2个字组成的学生。Select*fromstudentwhere姓名LIKE‘＿’（下划线前面空格）8.4.5“[]”通配符如果SQL运行环境为SQLSERVER、ACCESS或SYBASEADAPTIVESERVER则可以在LIKE运算符中使用一种特殊的通配符——方括号（[]）从Student表中，查询姓“张”、“李”或“周”的所有学生，并按姓名升序排序。Select*fromstudentwhere姓名LIKE‘[张李周]%’ORDERBY姓名如果在方括号内的第一个位置输入符号“^”，则表示取反向值。如从Student表中，查询除姓“张”、“李”或“周”以外的所有学生，并按姓名升序排序。Select*fromstudentwhere姓名LIKE‘[^张李周]%’ORDERBY姓名从Student表中，查询1—5月出生的所有学生，并按出生日期升序排序。Select*fromstudentwhere姓名LIKE‘[0][1-5]%’ORDERBY出生日期8.4.6SQLSERVER、ACCESS和ORACLE中的通配符比较\n虽然ORACLE中没有方括号和反方括号，但有时可以使用逻辑表达式得到相同的效果。例如：字段名LIKE[a-z]%或字段名LIKE[^a-z]%可以使用逻辑表达式字段名﹥＝‘a’AND字段名﹤＝‘z’或字段名﹤＝‘a’OR字段名﹥＝‘z’得到相同的效果。8.4.7定义转义字符前面学习了几种通配符的使用方法，知道了“%5%”代表包含5的所有字符串，如果想要查询最后两个字符为5%的所有字符串呢？即将“%5%”中的第二个“%”看作普通字符，而不是通配符，此时应该定义和使用转义字符。在不同的环境下，定义转义字符的方法也不同。1.在SQLSERVER环境