Sql基础入门.doc

Sql基础入门本教程用简单易懂的叙述，介绍了SQL语言中最基本的select语句的各种用法，从字段、记录、表的概念，到聚合函数和GROUP BY 和 HAVING 子句。然后介绍嵌套SELECT语句，从而提升初学者应用水平。最后介绍各种表连接，使读者真正掌握select语句的精华，解决学习SQL语言的难点问题，步入SQL高手行列。第一节 SQL语言简介SQL是结构化查询语言(Structured Query Language)的缩写。这种语言允许我们对数据库进行 复杂的查询。同时也提供了创建数据库的方法。SQL语言的使用范围非常广泛。许多数据库产品 都支持SQL语言，这意味着如果我们学会了SQL语言，我们可以把这种知识运用到MS Access 或 SQL Server, Oracle, DB2以及非常多的其它数据库中。 SQL语言运用在关系型数据库中。一个关系型数据库把数据存储在表（也称关系）中。每个 数据库的主要组成就是一组表。每个表又由一组记录组成每条记录在表中有相同的结构， 包含固定数量的具有一定类型的字段。 下面我们来看一个实际的数据库中的表。该表的表名为cia，包含250多条记录，每个记录代表 一个国家。表由5个字段组成，字段的值有的是字符串类型，有的是数字类型。 name region area population gdp - - - - - Yemen Middle East 527970 14728474 23400000000 Zaire Africa 2345410 44060636Zambia Africa 752610 9445723 7900000000 Zimbabwe Africa 390580 11139961下面我们可以用一些SQL语句来查询这个表中我们该兴趣的数据。 1. 中国的GDP是多少？ 查询用的SQL语句为： select gdp from cia where name=china 查询结果为： 4800000000000 2. 给出每个地区的国家数和人口总数。并且按地区的人口数从多到少排序。 查询用的SQL语句为： SELECT region, COUNT(name), SUM(population) FROM cia GROUP BY region ORDER BY 3 DESC 查询结果为： region COUNT(name) SUM(population) - - - Asia 14 2963031109 Africa 59 793382933 Europe 43 580590872 . 怎么样，对SQL语言有了基本的了解了吧，同时对数据库，表，记录，字段等一系列在SQL语言中常用的 感念也有大概的认识吧。如果不是很清楚也没关系，在接下来的内容中我们从SQL语言中最简单 的内容逐步给大家作介绍，并提供丰富的练习让大家实际操作。相信学完本系列教程，你可以成为一个 SQL语言的高手。第二节 网站简介为著名SQL网站在中国的主打网站。该网站和其它提供SQL教程网站 最大的区别就是提供了一个可以让学习者实际练习各种SQL语句的平台。这对SQL的初学者非常重要。 同样，为有一定工作经验的数据库管理员和数据库程序员提供了几乎所有主流数据库的参考资料。 同时提供各种数据库的后台支持，让大家在线执行各种SQL语句，验证同一个SQL语句在不同数据库中的 兼容性。 给初学者提供达几百道的SQL在线练习题，从最简单的SELECT到各种复杂的JOIN语句， 可供学习时间达几十小时。这些练习题由英国 Napier 大学的Andrew Cumming提供。 Andrew Cumming（http:/www.dcs.napier.ac.uk/andrew/）长期进行SQL语言的教学，这些练习题都是 他平常教学中给学生的实际材料，因此对学生非常有针对性，很有价值。 因为这些材料本来就是英国 Napier大学给他们本校学生使用的，因此对于在学校从事数据库和SQL语言教学的老师， 欢迎老师给学生使用、练习这些习题。并给提供宝贵的意见和建议第三节 最基本的SELECT命令select命令或语句用来获取一个或多个表中的记录信息，一般配合where子句使用，来取得满足 某些条件的记录，如果没有where子句，将返回所有记录。一般的使用方式如下： SELECT attribute-list FROM table-name WHERE condition attribute-list：返回内容的列表，每个内容用逗号分开。这里的内容可以为字段，包含字段的表达式 或更复杂的子查询。 table-name：表名，更复杂时可以为子查询。 condition：条件表达式，用来筛选满足该条件的记录。 在本节中我们使用下表作为试验之用： bbc(name, region, area, population, gdp) 表名为bbc，该表有个字段（columns），又称为属性（attributes） . name ：国家名 region:国家所在的地区 area: 面积 population ：人口 gdp：国民生产总值 SQL实例： 一、选出所有国家名，地区和人口 SELECT name, region, population FROM bbc 二、给出France的人口数 SELECT population FROM bbc WHERE name = France 三、哪些国家的名称以字符D开始？ SELECT name FROM bbc WHERE name LIKE D% 四、 国土大国（面积大于五百万平方公里）的国名和人口密度 SELECT name, population/area FROM bbc WHERE area 5000000 五、给出一些小（面积小于2000平方公里）而富有(国民生产总值大于50亿)的国家 SELECT name , region FROM bbc WHERE area 5000000000第四节 GROUP BY 和 HAVING 子句在介绍GROUP BY 和 HAVING 子句前，我们必需先讲讲sql语言中一种特殊的函数：聚合函数， 例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。 SELECT SUM(population) FROM bbc 这里的SUM作用在所有返回记录的population字段上，结果就是该查询只返回一个结果，即所有 国家的总人口数。 通过使用GROUP BY 子句，可以让SUM 和 COUNT 这些函数对属于一组的数据起作用。 当你指定 GROUP BY region 时， 属于同一个region（地区）的一组数据将只能返回一行值 也就是说，表中所有除region（地区）外的字段，只能通过 SUM, COUNT等聚合函数运算后返回一个值 HAVING子句可以让我们筛选成组后的各组数据 WHERE子句在聚合前先筛选记录也就是说作用在GROUP BY 子句和HAVING子句前 而 HAVING子句在聚合后对组记录进行筛选。 让我们还是通过具体的实例来理解GROUP BY 和 HAVING 子句，还采用第三节介绍的bbc表。 SQL实例： 一、显示每个地区的总人口数和总面积 SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region 先以region把返回记录分成多个组，这就是GROUP BY的字面含义。分完组后，然后用聚合函数对每组中 的不同字段（一或多条记录）作运算。 二、 显示每个地区的总人口数和总面积仅显示那些面积超过1000000的地区。 SELECT region, SUM(population), SUM(area) FROM bbc GROUP BY region HAVING SUM(area)1000000 在这里，我们不能用where来筛选超过1000000的地区，因为表中不存在这样一条记录。 相反，HAVING子句可以让我们筛选成组后的各组数据第五节 嵌套SELECT语句嵌套SELECT语句也叫子查询，形如： SELECT name FROM bbc WHERE region = (SELECT region FROM bbc WHERE name = Brazil) 一个 SELECT 语句的查询结果可以作为另一个语句的输入值。 上面的SQL语句作用为获得和Brazil（巴西）同属一个地区的所有国家。 子查询不但可以出现在Where子句中，也可以出现在from子句中，作为一个临时表使用， 也可以出现在select list中，作为一个字段值来返回。本节我们仅介绍的Where子句中的子查询。 在Where子句中使用子查询，有一个在实际使用中容易犯的错在这里说明一下。 通常，就像上面的例子一样，嵌套的语句总是和一个值进行比较。 语句 (SELECT region FROM bbc WHERE name = Brazil) 应该仅返回一个地区，即 Americas. 但如果我们在表中再插入一条地区为欧洲，国家名称为Brazil的记录，那会发生什么情况？ 这将会导致语句的运行时错误因为这个SQL语句的语法是正确的，所以数据库引擎就开始执行， 但当执行到外部的语句时就出错了。 因为这时的外部语句就像好像是 SELECT name FROM bbc WHERE region = (Americas, Europe)， 这个语句当然报错了。 那么有没有办法解决这个问题呢，当然有。有一些SQL查询条件允许对列表值（即多个值）进行操作。 例如IN 操作符，可以测试某个值是否在一个列表中。 下面的语句就可以安全的执行而不出错，不管表中有多少条包含Brazils的记录 SELECT name FROM bbc WHERE region IN (SELECT region FROM bbc WHERE name = Brazil) OK， 让我们再看看一些具体的实例， 一、给出人口多于Russia（俄国）的国家名称 SELECT name FROM bbc WHERE population (SELECT population FROM bbc WHERE name=Russia) 二、给出India（印度）, Iran（伊朗）所在地区的所有国家的所有信息 SELECT * FROM bbc WHERE region IN (SELECT region FROM bbc WHERE name IN (India,Iran) 三、给出人均GDP超过United Kingdom（英国）的欧洲国家 SELECT name FROM bbc WHERE region=Europe AND gdp/population (SELECT gdp/population FROM bbc WHERE name=United Kingdom)第六节 SELECT语句的表连接(join)为了从两个或多个表中选出数据，我们一般使用表连接来实现这个功能。 本节介绍join（连接）的概念. 为此我们准备了两个试验用表： album（专辑表） 和 track（曲目表）. 专辑表：包含200首来自Amazon的音乐CD的概要信息。 album(asin, title, artist, price, release, label, rank) 曲目表：每张专辑中的曲目（因为是音乐CD，所以也可叫歌曲）的详细信息。 track(album, dsk, posn, song) SQL短语 FROM album JOIN track ON album.asin=track.album 表示连接album和track表。 其中，album.asin表示专辑的惟一标识号，track.album表示曲目表中和专辑关联的专辑号。 连接后，得到一个临时表，该临时表中每条记录包含的字段由两部分组成， 除了专辑表中的对应字段album(title, artist .)，还包含曲目表的所有字段track(album, disk, posn and song)。 有了这张临时表，很多查询就容易实现了。 看看一些具体的实例， 一、列出歌名为Alison的专辑名称和作者 SELECT title, artist FROM album JOIN track ON (album.asin=track.album) WHERE song = Alison 显然，歌名、专辑名称和作者分别在两个表中，必需使用表连接来完成这个查询。 二、哪个artist录制了歌曲Exodus SELECT artist FROM album JOIN track ON (asin=album) WHERE song = Exodus 用作连接的两个字段asin，album因为在两个表中都是惟一的，所以不一定要加表名作为前缀。 但为了方便理解，建议使用前缀，形如：album.asin=track.album 三、列出曲目表中所有属于Blur专辑的歌曲 SELECT song FROM album JOIN track ON (asin=album) WHERE title = Blur 如果我们把album JOIN track ON (asin=album)看成一个临时表的话，join的概念就很好理解了。第七节 左连接、右连接和全连接上节我们介绍了表连接，更确切的说是inner joins內连接 內连接仅选出两张表中互相匹配的记录因此，这会导致有时我们需要的记录没有包含进来。 为更好的理解这个概念，我们介绍两个表作演示。苏格兰议会中的政党表(party)和议员表(msp)。 party(Code,Name,Leader) Code: 政党代码 Name: 政党名称 Leader: 政党领袖 msp(Name,Party,Constituency) Name: 议员名 Party: 议员所在政党代码 Constituency: 选区 在介绍左连接、右连接和全连接前，有一个数据库中重要的概念要介绍一下，即空值(NULL)。 有时表中，更确切的说是某些字段值，可能会出现空值, 这是因为这个数据不知道是什么值或根本就不存在。 空值不等同于字符串中的空格，也不是数字类型的0。因此，判断某个字段值是否为空值时不能使用=,这些 判断符。必需有专用的短语：IS NULL 来选出有空值字段的记录，同理，可用 IS NOT NULL 选出不包含空值的记录。 例如：下面的语句选出了没有领导者的政党。（不要奇怪，苏格兰议会中确实存在这样的政党） SELECT code, name FROM party WHERE leader IS NULL 又如：一个议员被开除出党，看看他是谁。(即该议员的政党为空值) SELECT name FROM msp WHERE party IS NULL 好了，让我们言归正传，看看什么叫左连接、右连接和全连接。 A left join（左连接）包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。 同理，也存在着相同道理的 right join（右连接），即包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。 而full join(全连接)顾名思义，左右表中所有记录都会选出来。 讲到这里，有人可能要问，到底什么叫：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。 Ok,我们来看一个实例： SELECT , FROM msp JOIN party ON party=code 这个是我们上一节所学的Join(注意：也叫inner join)，这个语句的本意是列出所有议员的名字和他所属政党。 你可以在/4.htm 亲自执行一下该语句，看看结果是什么。 很遗憾，我们发现该查询的结果少了两个议员：Canavan MSP, Dennis。为什么，因为这两个议员不属于任 和政党，即他们的政党字段(Party)为空值。那么为什么不属于任何政党就查不出来了？这是因为空值在 作怪。因为议员表中政党字段(Party)的空值在政党表中找不到对应的记录作匹配，即 FROM msp JOIN party ON party=code没有把该记录连接起来，而是过滤出去了。 在该短语中，msp在Join的左边，所有称为左表。party在Join的右边，所有称为右表。 Ok,现在再看看这句话，“包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录”， 意思应该很明白了吧。执行下面这个语句，那两个没有政党的议员就漏不了了。 SELECT , FROM msp LEFT JOIN party ON party=code 关于右连接,看看这个查询就明白了： SELECT , FROM msp RIGHT JOIN party ON msp.party=party.code 这个查询的结果列出所有的议员和政党，包含没有议员的政党，但不包含没有政党的议员。 那么既要包含没有议员的政党，又要包含没有政党的议员该怎么办呢，对了，全连接(full join)。 SELECT , FROM msp FULL JOIN party ON msp.party=party.code第八节 SELECT语句中的自连接到目前为止，我们连接的都是两张不同的表，那么能不能对一张表进行自我连接呢？答案是肯定的。 有没有必要对一张表进行自我连接呢？答案也是肯定的。 表的别名： 一张表可以自我连接。进行自连接时我们需要一个机制来区分一个表的两个实例。 在FROM clause（子句）中我们可以给这个表取不同的别名， 然后在语句的其它需要使用到该别名的地方 用dot（点）来连接该别名和字段名。 我们在这里同样给出两个表来对自连接进行解释。 爱丁堡公交线路， 车站表： stops(id, name) 公交线路表： route(num, company, pos, stop) 一、对公交线路表route进行自连接。 SELECT * FROM route R1, route R2 WHERE R1.num=R2.num AND R1.company=R2.company 我们route表用字段(num, company)来进行自连接 结果是什么意思呢？ 你可以知道每条公交线路的任意两个可联通的车站。 二、用stop字段来对route（公交线路表）进行自连接。 SELECT * FROM route R1, route R2 WHERE R1.stop=R2.stop; 查询的结果就是共用同一车站的所有公交线。这个结果对换乘是不是很有意义呢。 从这两个例子我们可以看出，自连接的语法结构很简单，但语意结果往往不是 那么容易理解。就我们这里所列出的两个表，如果运用得当，能解决很多实际问题， 例如，任意两个站点之间如何换乘。 SELECT R1.company, R1.num FROM route R1, route R2, stops S1, stops S2 WHERE R1.num=R2.num AND R1.company=R2.company AND R1.stop=S1.id AND R2.stop=S2.id AND S1.name=Craiglockhart AND S2.name=Tollcross最简便的MySql数据库备份方法使用MYSQL进行数据库备份，又很正规的数据库备份方法，同其他的数据库服务器有相同的概念，但有没有想过，MySQL会有更简捷的使用文件目录的备份方法，而且又快有好。 一、数据备份捷径 因为这个方法没有得到官方正式文档的验证，我们暂称为试验吧。 目的：备份hostA主机中一个mysql数据库TestA，并恢复到到hostB机中 试验环境： 操作系统：WinNT4.0,Mysql3.22.34,phpMyAdmin 2.1.0 在hostA中安装mysql数据库并建立TestA数据库 hostB机安装mysql数据库，没有TestA数据库 方法步骤： 启动phpMyAdmin察看HostA和HostB中的数据库列表，在HostB中没有TestA数据库 找到HostA中mysql的安装目录，并找到数据库目录data 在我的试验环境中，这个目录是 C:mysqldata 找到对应数据库名称的子目录 C:mysqldataTestA 粘贴拷贝到HostB的Data目录下，是HostA同HostB Mysql数据目录下的文件相同 刷新HostB的phpMyAdmin察看一下数据库列表，我们看到TestA已经出现，并且作查询修改等操作都正常，备份恢复恢复成功 试验结论：Mysql的数据库可以通过文件形式保存，备份，恢复只要将相应文件目录恢复即可，无需使用其它工具备份。 二、正规的方法（官方建议）： 导出要用到MySQL的mysqldump工具，基本用法是： mysqldump OPTIONS database tables 如果你不给定任何表，整个数据库将被导出。 通过执行mysqldump -help，你能得到你mysqldump的版本支持的选项表。 注意，如果你运行mysqldump没有-quick或-opt选项，mysqldump将在导出结果前装载整个结果集到内存中，如果你正在导出一个大的数据库，这将可能是一个问题。 mysqldump支持下列选项： -add-locks 在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。 -add-drop-table 在每个create语句之前增加一个drop table。 -allow-keywords 允许创建是关键词的列名字。这由在列名前面加表名的方法做到。 -c, -complete-insert 使用完整的insert语句(用列名字)。 -C, -compress 如果客户和服务器均支持压缩，压缩两者间所有的信息。 -delayed 用INSERT DELAYED命令插入行。 -e, -extended-insert 使用全新多行INSERT语法。（给出更紧缩并且更快的插入语句） -#, -debug=option_string 跟踪程序的使用(为了调试)。 -help 显示一条帮助消息并且退出。 -fields-terminated-by=. -fields-enclosed-by=. -fields-optionally-enclosed-by=. -fields-escaped-by=. -fields-terminated-by=. 这些选择与-T选择一起使用，并且有相应的LOAD DATA INFILE子句相同的含义。 LOAD DATA INFILE语法。 -F, -flush-logs 在开始导出前，洗掉在MySQL服务器中的日志文件。 -f, -force, 即使我们在一个表导出期间得到一个SQL错误，继续。 -h, -host=. 从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。 -l, -lock-tables. 为开始导出锁定所有表。 -t, -no-create-info 不写入表创建信息(CREATE TABLE语句） -d, -no-data 不写入表的任何行信息。如果你只想得到一个表的结构的导出