SQL基础与常用技巧

SQL基础与使用技巧SQL基础知识数据库、表介绍基本概念和查询语句建数据库、建表进阶子句、函数的应用优化使用技巧SQL基础SQL进阶SQL技巧SQL基础数据库就是存放数据的仓库。是一组相关数据的集合。数据库由表、关系和操作对象组成。表——行和列行——记录（客观存在、可被描述）列——字段、属性DBMS：操纵和管理数据库的大型软件，用于建立、使用和维护数据库。常用：SYBASE、ORACLE、DB2、Mysql、SQLServer、Postgre。SQL：用于存取数据以及查询、更新和管理DBMS数据库的概念数据的存放数据库管理系统结构化查询语言SQL的版本和作用SQL是一门标准语言。但根据数据库操作系统的不同存在一些不同版本的SQL。换句话说，除了一些主要的关键词和语法外，各数据库管理系统中都有关于SQL的扩展语言。标准SQL在任何DBMS中都可以正常使用。SQL可以查询、插入记录。可以更新数据、删除记录。可以创建数据库。可以创建表、存储过程、视图。为表、存储过程、视图设置权限。数据库表初探每张表都有一个名字。每张表都有至少一个列。表用来存放记录，新建的表中没有记录，是一张空表。IdNameSexProvinceCity1赵阿土男台湾省台北市2钱夫人女福建省厦门市3孙小美女广东省广州市4李宝藏男江苏省苏州市如下表，表名为：Persons。该表含有Id、姓名、性别、省、市这5个列。该表包含4条记录，就是4行数据。SQL语言SQL语言是一种大小写不敏感的语言，书写SQL的时候理论上无需在乎大小写。在每一段完整SQL语句后增加分号（；）某些数据库会要求在每句SQL后加上分号，某些不需要。SQL单行注释是–（2个-）SQL多行注释是/*……*/但在实际开发中可以用：大写表名SQL关键字大写或首字母大写SELECT、FROM、WHERE等关键字前换行每句SQL后都以分号做结尾每句SQL均增加注释等方式增加SQL代码的可读性。SQL的DML和DDL数据操作语言DMLSELECT——从数据库表中获取数据UPDATE——更新数据库中表中的数据DELETE——从数据库表中删除数据INSERTINTO——向数据库表中插入数据数据定义语言DDLCREATEDATABASE——创建新数据库ALTERDATABASE——修改数据库CREATETABLE——创建新表ALTERTABLE——变更（改变）数据库表DROPTABLE——删除表CREATEINDEX——创建索引（搜索键）DROPINDEX——删除索引SQL语法1SELECT单列：SELECT列名FROM表名多列：SELECT列名,列名,…FROM表名所有列：SELECT*FROM表名DISTINCTSELECTDISTINCT列名FROM表名返回不重复的结果集WHERE用于规定选择的标准（返回结果集的条件）SELECT列名FROM表名WHERE列运算符值运算符：=、(!=)、、、=、=、between、like值若为字符串格式，则应该加单引号‘’为空判断独有的写法isnull、isnotnullSQL语法2AND和OR运算符在WHERE子句中将多个条件连接起来AND并且OR或者AND比OR拥有更高的优先级使用时注意配合括号ORDERBY语句用于根据指定的列对结果集进行排序。默认升序升序为ASC降序为DESCINSERTINTO语句用于向表格中插入新的行。INSERTINTO表名称VALUES(值1,值2,....)--所有列INSERTINTOtable_name(列1,列2,...)VALUES(值1,值2,....)--选择列SQL语法3Update语句用于修改表中的数据。语法：UPDATE表名SET列名=新值,列名=新值WHERE列名=某值;DELETE语句用于删除表中的行。DELETEFROM表名WHERE列名=值;删除部分数据使用delete时，最好先使用select查询，检查一下结果集是不是要删除的数据，没有问题再删LIKE操作符LIKE操作符用于在WHERE子句中搜索列中的指定模式。“%可用于定义通配符（模式中缺少的文字）。SELECT*FROMPersonsWHERENameLIKE‘赵%‘SELECT*FROMPersonsWHERENameLIKE‘%美‘SELECT*FROMPersonsWHERENameLIKE‘%夫%‘语法：SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameLIKEpatternSQL通配符通配符含义%替代一个或多个字符_仅替代一个字符[charlist]字符列中的任何单一字符[^charlist]或者[!charlist]不在字符列中的任何单一字符SELECT*FROMPersonsWHERENameLIKE'[赵钱孙]%‘SELECT*FROMPersonsWHERENameLIKE'[!赵钱孙]%‘在ORACLE中只有%和_这两种通配符。[]通配符在mySQL等数据库中使用。IN、BETWEEN操作符IN操作符在WHERE子句中规定多个值。SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameIN(value1,value2,...)不在规定多个值之中的使用NOTININ操作符在WHERE选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。（文本只支持字母且区分大小写）SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameBETWEENvalue1ANDvalue2开区间还是闭区间因不同数据库而异。不在范围内使用NOTBETWEENSQLAlias别名可以为列名或表名指定别名表：SELECTcolumn_name(s)FROMtable_nameASalias_name列：SELECTcolumn_nameASalias_nameFROMtable_name在ORACLE中可以省略AS关键字，在列或表名后面空格直接加别名。使用别名会使查询程序更容易阅读和书写。给使用了函数后形成的查询字段起别名可以缩短列宽且便于理解从多个表中取数据时使用别名表名更简单从多个表中取数据时可能碰到同名字段，别名有助于区分字段来源KEY键主键（PrimaryKey）是一个列，在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。主键PrimaryKey外键ForeignKeyselectlp.name,lo.order_nofromLESSON_ORDERSlo,LESSON_PERSONSlpwherelo.person_id=lp.id这种写法等同于INNERJOINJOIN连接为了得到完整的结果，需要从两个或更多的表中获取结果。这时就需要执行join。selectlp.name,lo.order_nofromLESSON_ORDERSloinnerjoinLESSON_PERSONSlponlo.person_id=lp.idINNERJOIN:如果表中有至少一个匹配，则返回行，也可写为JOINLEFTJOIN:即使右表中没有匹配，也从左表返回所有的行RIGHTJOIN:即使左表中没有匹配，也从右表返回所有的行FULLJOIN:只要其中一个表中存在匹配，就返回行UNION和UNIONALL操作符UNION操作符用于合并两个或多个SELECT语句的结果集。请注意，UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。SELECTcolumn_name(s)FROMtable_name1UNIONSELECTcolumn_name(s)FROMtable_name2UNIONALL操作符用法和意义与UNION相同。区别在于UNIONALL命令会列出所有的值。而UNION操作符选取不同的值。结果集中的列名总是等于UNION中第一个SELECT语句中的列名。SQLStatement1UNIONALLSQLStatement2TOP子句TOP子句用于规定要返回的记录的数目。注意：并非所有的数据库系统都支持TOP子句。SQLServer的语法：SELECTTOPnumber|percentcolumn_name(s)FROMtable_nameMySQL语法SELECTcolumn_name(s)FROMtable_nameLIMITnumberOracle语法SELECTcolumn_name(s)FROMtable_nameWHEREROWNUM=number例子：M：SELECT*FROMPersonsLIMIT5O：SELECT*FROMPersonsWHEREROWNUM=5S：SELECTTOP2*FROMPersonsSELECTTOP50PERCENT*FROMPersons创建数据库和表CREATEDATABASE用于创建数据库。CREATETABLE语句用于创建数据库中的表。CREATEDATABASEdatabase_nameCREATETABLE表名(列名1数据类型,列名2数据类型,列名3数据类型,....)数据类型数据类型描述•integer(size)•int(size)•smallint(size)•tinyint(size)仅容纳整数。在括号内规定数字的最大位数。•decimal(size,d)•numeric(size,d)容纳带有小数的数字。size规定数字的最大位数。d规定小数点右侧的最大位数。char(size)容纳固定长度的字符串（可容纳字母、数字以及特殊字符）。在括号中规定字符串的长度。varchar(size)容纳可变长度的字符串（可容纳字母、数字以及特殊的字）。在括号中规定字符串的最大长度。date(yyyymmdd)容纳日期。SQL约束约束(Constraints)用于限制加入表的数据的类型。可以在创建表时规定约束（通过CREATETABLE语句），或者在表创建之后也可以（通过ALTERTABLE语句）。NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECKDEFAULTNOTNULL约束NOTNULL约束强制列不接受NULL值。如果不向字段添加值，就无法插入新记录或者更新记录。CREATETABLELESSON_PERSONS(IdintNOTNULL,namenvarchar2(20)NOTNULL,Provincevarchar(255),Cityvarchar(255));表示第一列和第二列不接受空值。UNIQUE约束UNIQUE约束唯一标识数据库表中的每条记录。UNIQUE和PRIMARYKEY约束均为列或列集合提供了唯一性的保证。PRIMARYKEY拥有自动定义的UNIQUE约束。请注意，每个表可以有多个UNIQUE约束，但是每个表只能有一个PRIMARYKEY约束。ALTERTABLEPersonsADDUNIQUE(Id_P)ALTERTABLEPersonsADDCONSTRAINTuc_PersonIDUNIQUE(Id_P,LastName)撤销UNIQUE约束ALTERTABLEPersonsDROPCONSTRAINTuc_PersonIDPRIMARYKEY主键约束在创建表时定义PK，在最后一列的定义后增加如下代码：CONSTRAINTpk_namePRIMARYKEY(column1,column2…)表已存在，增加PK：ALTERTABLEtableNameADDCONSTRAINTpk_namePRIMARYKEY(column1,column2…)撤销PK：ALTERTABLEtableNameDROPCONSTRAINTpk_namePRIMARYKEY约束