| MySQL权威指南 | ||||||||||||||||
|
第一章，MySQL和SQL入门 很少有技术类书籍一开篇就讨论SELECT语句的，这是此书给我的第一个印象，本章把这个语句讲解得十分详细。我觉得第一章主要是讨论两个问题:通过两个样板数据库讲解对数据库理论的理解;基本命令的使用。比较高深的东西好像不多。 一，对数据库的理解 为什么要使用数据库呢，我们都看过许多说法，不外于其提供了强大的数据处理能力，如果仅仅是简单的数据列表，电子表格就完全胜任了。在数据的世界里，MySQL被划分为关系数据库管理系统的范畴内，我们可以把这个短语划分为以下几个部分: 数据库:存放信息的的资料库，其构造既简单又遵守一定的规律 数据库内的数据都存放在数据表(table)里 数据表是由数据行(row)和数据列(colume)构成的 一个数据行就是数据表内的一条记录(record) 一个记录一般包含多个信息，数据表中的每一个数据列都对应着一个信息 关系:把存放在某个数据表内的信息和存放在另一个数据表内的信息通过某种方式关联起来，而这种关联就是通过查找两个数据表有无共同的元素来实现的。 先看一个简单的例子: 你的站点上有大量的广告，每当客户浏览你的网页时，一旦点击你的广告，你就需要做记录来计算自己得到了多少广告费。从数据库的角度来说，你应该建立三个数据表。 1,各个广告的来源company表 ------------------------------------------------- Company Company_num Address phone SONY 13 马家沟 0451-21 CANON 14 新阳路 0451-22 2,各个广告的数据ad表 ---------------------------------------- Company_num Ad_num Hit_fee 12 48 001 13 49 003 14 50 004 3,每个广告的点击率hit表 ------------------------ Ad_num Date 3.12 48 49 3.14 48 3.12 50 3.14 看看我们能通过这三个数据表查询到什么信息, 1,你为多少家公司做广告, 查一查company表有多少行就知道了 2,你一共有多少个广告, 查一查ad表有多少行就知道了 3,在3.12这一天，你一共显示了多少个广告? 查一查hit表里面Date数据列的值是3.12的数据行共有多少, 4,在3.14这一天，CANON的广告被点击了几次, 首先在company表中找到CANON公司的公司代码(14)，根据这个代码在ad数据表中找到CANON公司的广告代码(50实际上可能是很多个)，然后分别在hit表中找到3.12日这个代码的点击率。 看到这里我们就能明白，为什么要费力气地把数据“分散”到多个表里面，而不是放到一个大表格中，数据库的非凡能力就表现在这里，把分散在多个数据表中的数据用一种相互匹配的方式迅速地搜集到一起得出结果，这就是数据库式的思维方式。我们必须慢慢习惯，然后自觉运用。是不是很简单, 如何让读者更好地理解这种“关系”的概念，书中给了两个样板数据库，听我慢慢道来: 你现在是美国历史研究会的秘书，这个协会是由一些对美国历史感兴趣的人自发地组织起来的，由于各人的爱好，他们将定期地交纳一定的会费来维持其会员资格，交上来的会费主要用于支付研究会的各种开支，如印刷会员刊物等。这个研究会目前建有一个小型的互联网站点，但这个站点还没有得到充分的开发利用，如果你的主要工作是用字处理来打印会员名录的话，数据库就没有必要的，但是许多的工作要求很复杂的操作:如，你希望能够根据不同的情况把会员名录输出为其它格式的资料;根据特定的条件来查询会员;能够根据某种限制得到统计数字，等等，如果把研究会的会员资料放到网站上，那么对会员自己来说，以在线方式修改自己的资料，查询其他人的资料就会容易得多。甚至会员们可以互相发布消息，写电子邮件，等等。 你是一名教师，在每学期中，你负责考试和测验，记录各种分数，期末时，你 要对学生们的成绩进行总评。并把学生们的总评成绩和出勤情况上报给校方，如果不使用数据库，你就必须每学期都手工完成这些工作。 如果用传统方法，手工来统计考试积分表，你的表格可能就会是这样: 学生 ----------------------------------------------- ID 姓名 分数 Q Q T Q Q T 1 李寻欢 14 10 90 15 26 80 2 荆无命 17 10 90 12 28 82 3 吕奉先 15 10 92 15 26 80 4 王怜花 14 10 90 15 26 80 … …
毫无疑问，从关系的角度来说，这样的表格是无法直接放到数据库里面的。我们必须对其进行分解。当然，分数表(score表 )是最先必须建立的。最简单的模型可能是这样: Name Date Score 李寻欢 9.3 14 荆无命 9.3 17 吕奉先 9.3 15 王怜花 9.3 14 李寻欢 9.6 10 荆无命 9.6 10 吕奉先 9.6 10 王怜花 9.6 10 我们很快就能发现这个表的问题:它丢失了一些数据，我们不能知道当天进行的是考试还是测验。看来需要加入一个数据列: Name Date Score Type 李寻欢 9.3 14 Q 荆无命 9.3 17 Q 吕奉先 9.3 15 Q 王怜花 9.3 14 Q 李寻欢 9.6 10 T 荆无命 9.6 10 T 吕奉先 9.6 10 T 王怜花 9.6 10 T 我们又发现了一个问题，那就是本表的多余数据太多了，同一天里面的Type数据列的值是相同的，这种数据冗余是完全没有必要的。把表拆分一下如何: score表 Name Date Score 李寻欢 9.3 14 荆无命 9.3 17 吕奉先 9.3 15 王怜花 9.3 14 李寻欢 9.6 10
荆无命 9.6 10 吕奉先 9.6 10 王怜花 9.6 10 event表 date Type 9.3 Q 9/6 Q 9/9 Q 9/16 T 9/23 T 10/1 T 看起来好多了，虽然数据表的数据多了一个，但对数据库来说完全不是问题，我们怎么办,我们需要在同一天内可以继续考虑我们的成绩表:如果一天内有两场考试记录两组分数～既然如此，可以为每一次考试或者测验分配一个独一无二的编号，用这个编号来关联两个表，这样就避开了日期重复的问题了。 score表 Name Event_id Score 李寻欢 1 14 荆无命 1 17 吕奉先 1 15 王怜花 1 14 李寻欢 2 10 荆无命 2 10 吕奉先 2 10 王怜花 2 10 event表 Event_id date Type 1 9.3 Q 2 9/6 Q 3 9/9 Q 4 9/16 T 5 9/23 T 6 10/1 T 这样就完成了数据表的规划吗,好像还不行，考虑一下，如果有重名的学生如何处理呢,看来，用学号来解决这个问题就行了。 1,student表 Name Sex Student_id 李寻欢 F 1 荆无命 F 2 吕奉先 F 3 王怜花 F 4 2,score表 Student_id Event_id Score 1 1 14 2 1 17 3 1 15 4 1 14 1 2 10 2 2 10 3 2 10 4 2 10 3,event表 Event_id Date 9.3 Q 9/6 Q 9/9 Q 9/16 T 9/23 T 10/1 T 终于完成了考试积分表的规划～我们学到了什么呢,数据库应该是什么样子，它应该包含哪些数据表，各个数据表应该有什么内容以及数据应该如何表示,我们看到的许多理论性很强的教材，就在讲述“数据库的分析与设计”，“实体关系图”，“规范化过程”，“第三范式”等等很枯燥的概念。至少在本章中作者没有提到这些东西，而是用一种比较自然，直观的途径让我们了解了一些数据库的基本知识，我觉得这一点很值得推崇。 数据库上,最常见的操作是对现有数据进行检索; ,比较常见的操作是插入新数据;比较不常见的操作是创建数据表;最不常见的操作是创建数据库。 我们学习的步骤是从,到先看看如何建立我们的样板数据库，一个命令就行了 CREATE DATABASE sampdb; 建立各个数据表 建立“美国历史研究会”里面的总统表 CREATE TABLE president ( last_name VARCHAR(15) NOT NULL, first_name VARCHAR(15) NOT NULL, suffix VARCHAR(5) NULL, VARCHAR(20) NOT NULL, city state VARCHAR(2) NOT NULL, birth DATE NOT NULL, death DATE NULL ); 建立“美国历史研究会”里面的会员表 CREATE TABLE member ( member_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (member_id), last_name VARCHAR(20) NOT NULL, first_name VARCHAR(20) NOT NULL, suffix VARCHAR(5) NULL, expiration DATE NULL DEFAULT '0000-00-00', email VARCHAR(100) NULL, street VARCHAR(50) NULL, city VARCHAR(50) NULL, state VARCHAR(2) NULL, zip VARCHAR(10) NULL, phone VARCHAR(20) NULL, interests VARCHAR(255) NULL ); 3,建立记分系统中的student表 CREATE TABLE student ( name VARCHAR(20) NOT NULL, sex ENUM('F','M') NOT NULL, student_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (student_id) ); 4,建立记分系统中的score表 CREATE TABLE score ( student_id INT UNSIGNED NOT NULL, event_id INT UNSIGNED NOT NULL, PRIMARY KEY (event_id, student_id), score INT NOT NULL ); 5,建立记分系统中的event表 CREATE TABLE event ( date DATE NOT NULL, type ENUM('T','Q') NOT NULL, event_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (event_id) ); 插入新数据,操作命令可以有多种格式，我们用最简单的student表举个例子 INSERT INTO student VALUES ('Megan','F',1); INSERT INTO student VALUES ('Joseph','M',2); INSERT INTO student VALUES ('Kyle','M',3); INSERT INTO student VALUES ('Katie','F',4); INSERT INTO student VALUES ('Abby','F',5);
需要详细介绍的是数据的检索，本章中，作者列举了,,种情况，我将在下次贴出，并对本章作个总结。 To be continued… 时间仓促，水平有限，请大家指教，谢谢～ 2004年2月26日 二，select 的使用 现在，数据表都已经创建起来了，假设我们已经插入了许多的数据，我们就可以用自己喜欢的方式对数据表里面的信息进行检索和显示了，比如说:可以象下面这样把整个数据表内的内容都显示出来 select * from president; 也可以只选取某一个数据行里的某一个数据列 select birth from president where last_name=?Eisenhower?; select语句的通用形式如下: select 你要的信息 from 数据表(一个或多个) where 满足的条件 select语句有几个子句，他们的各种搭配能帮你查出最感兴趣的信息，这些子句可以很简单，也可以很复杂,看看作者是如何详细讲解的 用各种操作符来设定检索条件 要想让select语句只把满足特定条件的记录检索出来，就必须给它加上where字句来设置数据行的检索条件。只有这样，才能有选择地把数据列的取值满足特定要求的那些数据行挑选出来。可以针对任何类型的值进行查找，比如说，对数值进行搜索 select * from score where score>95; //显示所有分数在,,分以上的信息 也可以针对字符串值进行查找 select last_name,first_name from president where last_name=?Tom?; //找出所有姓tom的总统 还可以对不同类型的值进行组合查找 select last_name,first_name,birth,state from president where birth<?1950-1-1? and (state=?VA? or state=?BA?); //找出1950年前出生于VA州或,,州的总统 可见 where子句中可以使用的是算术操作符(+-*/%),比较操作符(<>=)以及逻辑运算符，我们应该熟练理解这些操作符的含义(都很简单) NULL 值的特别处理 这是一种不属于任何类型的值。它通常用来表示“没有数据”“数据未知”“数据缺失”“数据超出取值范围”“与本数据列无关”“与本数据列的其它值不同”等多种含值是非常有用的。 义。在许多情况下，NULL我们的各种操作符是不能对NULL 值进行处理的，如果相对NULL 值进行查找，用的是 is null 或 is not null 来进行判断，举例如下: select last_name,first_name,birth,state from president where death is null; //找出所有没死的总统 在某些情况下，NULL 值是很有用的类型，大家慢慢就会理解的。 查询结果进行排序 一般说来，如果创建了一个数据表并向里面插入了一些记录，当发出一条select * from name命令的时候，数据记录在查询结果中的先后顺序通常与它们被插入时的先后顺序一样(这当然符合我们的思维习惯(但这只是一种:想当然:的假设而已，事实上，但记录被删除时，数据库中会产生一些空的区域，MYSQL会用新的记录来填补这些区域，也就是说，这个时候本假设就不正确了(因此我们必须记住一点，从服务器返回的记录行的先后顺序是没有任何保证的～如果想要按照一定的顺序，就必须使用order by 子句来设置这个顺序( select last_name,first_name,birth,state from president order by last_name; //让总统们的名字按字母顺序排列 还可以设置排列的升序降序 select last_name,first_name from president order by state DESC,last_name ASC; //先按照出生地的降序排列，同出生地的按照姓氏的升序排列 注意:如果结果中含有NULL 值，默认情况下他们总是出现在查询结果的开头。 限制查询结果中数据行个数 这个简单，只要用limit 子句就可以了，看两个例子: select last_name,first_name,birth,state from president order by birth limit 5; //只想看前,个 order by birth limit 10,5; //返回从第11个记录开始的5个记录(跳过了10个) 小技巧:从president表中随机找出一个总统来玩: select last_name,first_name,birth,state from president order by rand() limit 1; //这是用了表达式求值的方法，在哪里都管用 对输出列进行求值和命名 为了提高效率，MYSQL还可以把表达式的计算结果当作输出列的值。表达式可以很简单，也可以很复杂。例如:下面这个查询有两个输出列，前一个输出列对应一个非常简单的表达式(一个常数)，而后一个输出列则对应着一个使用了多个算术运算符和两个函数调用的复杂表达式。 Select 17,format(sqrt(3*3+4*4),0)) 输出:17 5 再看这个命令:把两个输出列合并成一个 _name),concat(city,?,?,state) from president; select concat(first_namem,? „,last 如果合并之后输出列的标题过长，则可以给其一个别名，如: select concat(first_namem,? „,last_name) as name, concat(city,?,?,state) as birth place from president;这样就比较美观了。 和日期有关的问题 首先记住:在MYSQL中，年份是放到最前面的～我们通常对日期进行下列操作: 按日期进行排序查找某个日期或日期范围 提取日期中的年，元，日各个部分计算两个日期的间隔 用一个日期求出另外一个日期看例子: select * from event where date=?2002-10-01? //看看这天有何考试信息, select last_name,first_name,birth,state from president where death>?1900-01-01? and death<?2000-01-01?; //看看上个世纪死了几个, 三个函数year,month,dayofmonth可以分别分离出日期中的年月日来。 select last_name,first_name,birth from president where month(birth)=3; //谁生在3月 , where month(birth)=7 and dayofmonth(birth) =6; //谁生在7月6日,(汤姆克鲁斯,) 函数to_days可以把日期转换为天数。 select last_name,first_name,birth to_days(death)-to_days(birth) as age from president 可以看看这帮家伙都活了多少天～你自己把它改为年吧。 日期值的减法运算还能帮我们计算出现在距离某个特定日期还有多长的时间，这正是我们用来找到需要在近期内缴纳会费的会员的办法: select last_name,first_name,expiration from member where (to_days(expiration)-to_days(curdate())<60; //有些人60天内需要花钱了～ 模式匹配 有些情况下，模糊查询是很必要的，我们使用like和not like加上一个带通配符的字符串就可以了。共有两个通配符”_”(单个字符)和”&”(多个字符) select concat(first_namem,? „,last_name) as name, where last_name like „W%?; //找到以,或w开头的人 where last_name like „,W%?; //找到名字里面,或w开头的人 设置和使用SQL变量 MYSQL 3.23.6以上的版本可以使用查询结果来设置变量，我们就能够方面的把一些结果保存起来以供他用。变量的命名规格是:@name, 赋值语法是 @name:=value ( pascal?) 使用起来也简单: select @birth:=birth from president where last_name =?adsltiger?; //执行完成后我们就就会有一个@birth变量可用 用一下试试: select concat(first_namem,? „,last_name) as name from president where birth<@birth order by birth; //看看那些人比我大～ 生成统计信息 单纯依靠手工来生成统计信息是一项既艰苦又耗时还容易出错的工作，如果我们能熟练掌握用数据库来生成各种统计信息的技巧，他就会成为很有威力的信息处理工具。作者在这里用了许多篇幅讲这个主题，为了便于大家理解，我分解开来论述: 9.1 找出一组数据中到底有多少种不同的值是一项比较常见的统计工作，而关键字distinct就可以把查询结果中的重复数据清除掉。如 select distinct state from president //看看美国总统们都来自那些州,(重复的不计) 9.2用count()函数来统计相关记录的个数，注意其使用方法:count(*)计算所有的，NULL也要;count(数据列名称) NULL值不计算在内。 select count(*) from president; 9.3如果我们想知道班级内的男女生数目,该如何查询呢,最简单的方法是 select count(*) from student where sex=“f”; select count(*) from student where sex=?m 但是如果使用count函数结合group by关键字，一行命令就搞定了 select sex,count(*) f rom student group by sex; 我们可以看到，与反复使用彼此类似的查询来分别统计某数据列不同取值出现次数的做法相比， 把count(*)和group by字句相结合使用有许多优点，主要表现在: 在开始统计自前，不必知道被统计的数据列里面有多少种不同的取值 因为只用了一个查询命令，我们可以对输出做排序的处理 select state,count(*) as count from president group by state order by count desc limt4; //看看出生总统最多的前四个州是哪几个, 9.4除了count()，我们还用其他一些统计函数，如求出最小值的min()，求最大值的max()，求和的sum()，求平均值的avg()，在实际工作中，这些函数时经常用到的～ 从多个表提取信息 我们目前的例子都是从一个表里面提取信息，但数据库的真正威力还在于用“关系”来综合多个数据表里面的记录，这种操作称之为“关联”或“结合”我们可以看到，select需要给出多个数据表里面的信息(不可重复);from需要知道从哪几个表里面做事;where则对几个表之间的关联信息作出详细的描述。 首先我们要学习最可靠的数据列引用方式:数据表名.数据列名。这样在查询中就一定不会混淆这个数据列到底在哪一个表里。 例子1:查询某一天内的学生们的考试成绩，用学号列出。 select scroe.student_id,event_date,score.score.event.type from event,score where event.date=?2003-09-12? and event.event_id=score.event_id 首先，利用event数据表把日期映射到一个考试事件编号，在利用这个编号把score表内相匹配的考试分数找出来。关联两个表，一个查询搞定。 例子2:查询某一天内的学生们的考试成绩，用姓名列出。 select student.name event.name,score.score,event.type form event,score,student where event.date=?2003-09-12? and event.event_id= score.event_id and scroe.student_id=student.student_id; 关联三个表，一个查询搞定。 例子3:查询一下缺席学生的名字，学号，缺席次数 select student.student_id,student_name count(absence.date) as absences from student,absence where student.student_id=absence.student_id //关联条件 group by student.student_id; 简单的关联操作就介绍到这里。事实上，对于关联的知识我们需要学的很多很多，比如说，我们怀疑某一个数据表内不存在和我们相关的数据，把么在关联查询的时候如何处理这个表呢,这就涉及到内联接，外联接，左联接，右联接的许多新概念了。不知道大家还有没有信心向下看我的笔记,在本书第四章里面，对关联进行了十分详细的论述，看来“在SQL里面，干粗活的是select”的说法再对不过了。 我们了解了select命令的如此之多的用法，感到了它的灵活性，许多字句的组合能够形成一个非常“精妙”的,,,语句，在基础没有打好之前，我等初学者目前还没有必要去钻研那些技巧性很高的东西，“一定程度的创造性是必要的，但太专业或充满技巧的代码则是各种 bug的发源地，同时也是若干个不眠之夜的前奏” 第一章 总结 知识量还是比较大的，我们学会了MYSQL中最最基础的知识:创建数据库和数据表;对数据表里面的记录进行插入，检索，修改，删除等操作，并且对select语句的用法作了一些相对复杂的讲解，不知道各位好学的朋友有何感想。 第二章:MYSQL数据库里面的数据 用想用好MYSQL，就必须透彻理解MYSQL是如何看待和处理数据的。本章主要讨论了两个问题:一是,,,所能处理的数据值的类型;二是这些数据类型在实际应用中需要注意的问题。 首先我们看看mysql能够支持的数据类型，和其它的数据库一样，我们可以处理各种数值(整型，浮点)，字符串型，日期,时间型，,,,,值等等。大家在使用的需要注意不同类型的数值的格式是不一样的。在这里，对字符串的处理有一个比较特殊的地方大家需要理解。因为字符串两端是需要用引号(单引号，双引号)括起来的，但是如果字符串本身里面也包括了引号(单引号，双引号)，我们应该怎么办呢。这时就必须用以下三种方法之一来标识这个特殊的字符串。 如果字符串内部的引号字符与字符串两端的引号字符相同，则双写该引号 „I can??t? “He said,””I told you so, “”? 用与字符串内部的引号字符不同的引号把该字符串引起来，此时，就不用双写字符串内部的引号了 “I can?t” „He said,”I told you so,”? 用反斜杠对字符串内部的引号字符进行转义，此时与字符串两端的引号无关 „I can?t? “He said,”I told you so,”” 下面我们看数据列类型，真的很奇怪，数据列里面放数据，两者类型不相同。事实上，把数据列类型叫做列类型也许更合适一些，数据类型只是一种含义广泛的分类方法，比如:数值，字符串等等。而列类型是对一个给定的数据列里面的值有哪些具体特点的准确描述。比如smallint或varchar(10)等。也就是说，数据列类型决定了mysql如何对待这些数据，你不能把abc放到一个数值列里面～如果你给你的数据找到一个合适的位置，下列问题时必须考虑的: 需要把哪些种类的数值保存到数据表里面, 这种类型的值要占用多少存储空间, 这种类型的值长度是否固定不变, 这种类型的值如何进行比较和排序, 这种类型的值可以用null值吗, 如何对这种类型的值进行索引呢, 当你对这些问题都能心中有数的时候，就可以选择具体的列类型了，当然，每一种列类型都有相应的特征表格大家可以查询。需要注意的是这几点: 1,显示宽度
sql需要用多少个字符来显示该列数值，与该整数需要的 整数列的显示宽度与my存储空间的大小都没有关系，比如，不管设定了显示宽度是多少个字符，bigint都要占用8个字节。 2,截短处理 数值类型列的取值范围是由它的具体类型决定的。如果想把一个超出列取值范围的值插入到各个列中，mysql会截短这个值，会先把这个值替换为该数据列取值范围的上限或下限，然后插入，这种截短仅与这个列的取值范围有关，与该列的显示宽度无关。比如说:把99999插入到一个类型为smallint(3)的数据列里面，就会被截短为32767 3,前导0的问题 如果想让某列里面的值都能全部整齐的按所定义的显示宽度显示出来，就需要使用zerofill属性，就可以在数值前面加入不定数目的0来显示数值，这个功能有些情况下很有用。但有一点需要注意，如果一个数值长度大于定义的显示宽度，这个数值也会正常显示，此时不受显示宽度的限制。 4,enum和set 需要记住，这两种数据列的列定义里面都要有一个列表，列表里面的元素就是该列的合法取值。如果你试图把一个不在这个列表里面数据放入本列，它就会被转换为空字符串～ 5,日期和时间 在许多情况下，希望能够在数据列里面创建一条记录的时候可以记录当时的日期和时间。还希望这个时间值不会因为以后的操作而改变，但令人遗憾的是，目前仍然没有一种mysql列类型能够直接满足这个需要，现在我们一般用两个办法来解决这个问题: 1,使用一个timestamp数据列,在需要创建一条新纪录的时候,把这个timestamp数据列设置为null.也就是把它初始化为当前的日期和实践. Insert into table_name (ts_col,…) values (null,…); 在以后对这条纪录进行修改的时候,要用这个列里面的现有数据对它进行赋值.这种明确赋值的做法将抑制住mysql 的时间戳机制,让timestamp数据列里面的值不会自动改变. Update table_name set ts_col=ts_col where … tetime数据列,在需要创建一条新纪录的时候,用now() 函数来初始化 2,使用一个da这个数据列: insert into table_name (dt_col,…) values (now(),…); 以后对这条纪录进行修改的时候,不要碰这个数据列. Update table_name set … anything but dt_col where … 6,关于年份的转换问题 如果你把一个两位数的年份值插入到一个year数据列里面, mysql 会自动地按照一下规则来进行转换: 年份值 00-69 转换为 2000-2069 年份值 70-99 转换为 1970-1999 需要注意的是00这个年份,如果你执行: insert into table_name values (00) 其结果就会变成 0000 因此,如果想使用一个没有世纪部分的值,结果得到2000年,就必须是用字符串格式 ?00? 如果想让mysql 把一个向year 数据列插入的值看作字符串而不是数值,就应该用 concat()函数把这个值转化为字符串,无论输出参数是一个字符串还是一个数值, concat() 都会返回一个字符串. 7,序列和编号 作者之所以把序列和编号的问题单独拿出来讲解,是因为许多应用程序都需要一个独一无二的编号来把一条记录和其他的分别开来. 在mysql 里面,这种独一无二的编号是通过数据列的auto_increment 属性而自动生成一组序列编号的办法来实现的.需要我们首先知道的是, mysql目前支持多种数据表类型.不同的数据表类型对于auto_increment 属性的处理机制是不一样的!所以我们不仅需要掌握有关auto_increment 属性的基本知识,还需要熟悉这种机制在各种的数据表类型中的差异. Mysql 3.23以前的版本仅仅支持比较基础的ISAM 数据表类型,在后来的版本中,其他几种数据表类型也逐步加了进来.包括 MyISAM 和 HEAP 类型等等.现在我们使用mysql的时候默认建立的数据表都是比较先进的MyISAM类型.我们先看比较基础的IASM表类型是如何看待数据列的auto_increment 属性. A 如果试图把一个null 值插入到一个auto_increment 数据列里面,Mysql会自动生成一个序列编号并插入, 一般从1开始,依次递增 B 不要把0插入auto_increment 数据列里面,我们不推荐这这样做. C 如果在需要创建一条新纪录的时候,对auto_increment 数据列明确制定了一个数,会有两种情况存在:1,某个纪录已经使用了这个数,此时Mysql 会报告错误.2,如果没有纪录使用这个数,那么纪录正常插入,以后再插入的新纪录就会以这个编号+1开始!也就是说,我们可以人为地”跳过”一些编号,举例来说,如果插入一个”假”纪录,编号为然后我们就可以删除”假”纪录了.这是个简单999 那么以后的纪录就会从1000开始的技巧. D 如果删除了编号最大的纪录.再插入新纪录的时候仍然会使用这个编号!这是ISAM表本身的特点,也就是说,如果把数据表里面的纪录全部干掉的话,新纪录编号将从1开始 E last_insert_id() 函数能够返回编号序列中最后一个生成的编号.在某些情况下,我们可以利用这个函数取得当前数据表中最后一个生成的编号. 下面要说的是Mysql 里面的,目前大部分用户使用的 MyISAM表类型对auto_increment 数据属性的处理方法,可以看到,这种新的表类型有更大的灵活性,消除了原始的IASM标在处理序列和编号方面的许多缺陷!下面我们看: A 在MyIASM表里面,一个自动生成的序列编号将严格地依次递增,即使被删掉也不会被再次使用! B 可以在建立数据表的时候,使用 auto_increment=n 选项为序列编号明确地设定一个初始值 C 可以用alter table 随时改变MyISAM表中auto_increment的开始值,比如,下面这条命令会让序列编号从2000开始 其他注意事项 1,Mysql 提供auto_increment的主要目的是生成一个正整数数据列,所以我们可以把该列声明为unsigned ,这样能够使用的编号范围就多了一倍 2, auto_increment 属性仍然受到具体的数据列的取值范围的限制,比如一个tinyint数据列,编号最大值也只能是127 3,清除一个表的全部内容会让序列从1开始重新生成. 最后的问题 当我们打算创建一个数据表的时候,面对这么多的数据列属性,如果做出正确的选择? A这个数据列将用来存放哪一种数据? 这是我们第一个需要考虑的问题，一般说来，这个问题的答案似乎很直观:把数值放到数据列里面;把字符串放到字符串列里面;如果是整数就用整数列，如果有小数就用浮点类型。但是，事情往往不是这么简单，只有洞察有关数据的本质，才能明智地选择出最佳类型，有时候的结论往往是这样:最不适合人类阅读和理解的方案往往是最适合数据库的～这是不是很有趣，总之，对数据的全面把握才是选择数据列的关键所在。大家在学习过程中慢慢的体会这一点吧～ B数据值是否都在某个具体的范围内? 在第一章，我们为考试积分项目创建了一个名为score 的数据表，其中有一个用来记录考试和测验成绩的score 数据列，当时我们使用了int 类型，现在我们考虑到了对于考试来说，成绩一般都在0-100之间，因此我们用 tinyint unsigned 是不是更合适呢,但是如果有一种特殊情况，那就是校方会用-1来表示某个学生因病没有来考试，这时unsigned 就不行了 C有没有性能和效率方面的问题? 一般来说: 数值操作比字符串快 近可能地使用enum类型,因为这种类型在MySQL内部是用数值表示的，速度较快～ 小类型的处理速度比大类型快 D打算如何对有关数据进行比较 E是否要在某个数据列商建立索引 这两个问题往往看似简单，但对于比较特殊的应用来说值得相当关注～ 有关数据类型转换的问题 这个问题是很重要的～尤其对于那些用来删改数据记录的delete和update语句，必须确保这些语句只能施加在想对之进行操作的那些记录上～举例来说，假设某个数据表中有这么一个char数据列 看这个命令 delete from table_name where char=00 我们的本意可能是想把包含有00那条记录删掉，可实际上本命令执行之后会把 所有的记录全部赶掉～原因就在于MySQL把00当作了一个数值(字符串和数值比较时，比较操作将把他的两个操作数都当作数值来看待，这种情况下，所有的字符串都不可避免的变成了 所以我们在执行delete语句之前一定要先用select 检查一下 where 带来的结论。 下面是最常见的类型转换功能，大家好好学学 1) 把数据加上一个0或0.0 能把它强制转换为数值型 2) floor函数会把浮点数强制转换为整数 3) 给一个整数加上一个0.0会强制转换为浮点数 4) concat函数能把任何类型强制转换为字符串 5) hex函数能把字符串强制转换为十六进制数 6) ascii函数能把字符转换为ascii码 7) data_add函数能够把字符串或者数值转换为日期和时间值 8) 给日期和时间值加上一个0,可以强制转换为数值 2004年4月21日 第三章，MySQL的语法及其使用 先看看MySQL支持的SQL语句的分类数据库的选取，创建，丢弃和变更 use create database drap database alter database 数据表和索引的创建，变更和丢弃 create table drop table create index drop index alter index 从数据表检索信息 select union 事务处理 begin commit rollback set autocommit 对数据表里面的信息进行修改 delete insert load data replace update 管理型命令 flush grant revoke 一,命名规则 1MySQL允许用在名字中的系统字符. 任何字母数字加上”_” 或 “$” 2名字的长度. 数据库,数据表,数据列,索引等名字最多64个字母 256别名最多256个字母 3名字的限定符 依据不同的上下文,有时需要给某些名字加上某个限制:如数据列的全限定,部分限定,以及无限制.这一点比较容易理解 select * from db_name.tbl_name… 二，MySQL中的大小写问题 关键字和函数名:不区别 数据库名数据表名:根据服务器主机系统而定 数据列名索引名:不区别 别名:区别大小写 一般来说，不管系统是否区分数据库名和数据表名中的字母大小写情况，我们都应该在同一个查询语句里面以前后一致的字母大小写形式来写出这些名字，这是一个非常好的编程习惯。 三，MySQL支持的名种数据表类型详解 1,ISAM数据表 这是3.23版本之前的MySQL支特的唯一一种表类型,目前己经过时,MyIASM处理程库逐步取代了ISAM处理程序,这种老式的表类型己经没有人在用了 2,MyIASM数据表 这是目前中MySQL默认使用的数据表类型。其优点是如果主机操作系统支持大尺寸文件,数据表长度就能够很大,就能客纳更多的数据. 数据表内容独立于硬件也就是说可以把数据表在机器之间随意拷贝 提高了索引方面的功能 提供了更好的索引键压缩效果 auto_incremnet能力加强 改进了对数据表的完整性检查机制 支持进行fulltext全文本搜索 3,Merge数据表 这是一种把相同结构的MyIASM数据表组织为一个逻辑单元的方法 4,HEAP数据表这是一种使用内存的数据表，而且各个数据行的长度固定，这两个特性使得这种类型数据表的检索速度非常快，作为一种临时性的数据表，HEAP在某些特定情况下很有用。 5,BDB数据表 这种数据表支持事务处理机制 具有良好的并发性能 6,InnoBDB数据表 这是最近加入MySQL的数据表类型，有许多新的特性 支持事务处理机制 崩溃后能够立刻恢复 支持外键功能，包括级联删除 具有并发功能 7这种数据表在硬盘上的文件存储方式 IASM Frm isd ism MyISAM Frm myd myi Merge Frm mrg Heap Frm BDB Frm db InnoBDB frm 8数据表的可移植性 通用方法:吧数据表的内容导出到一个文本文件中，然后拷贝到目的地硬盘上，在用脚本加载到数据库里面，这是首先我们应该掌握的方法。但就文件层次的操作来说，某些数据表是可以单独拷贝的。看表了 ISAM No MyIASM Yes BDB No InnoBDB Yes 四，索引的初步知识 1,索引是加快数据表内容访问性能的基本手段,其基本特性: 为可以索引单独的数据列也可以构造包含多个数据列的复合索引 索引可以包含重复键值可以为一个数据表建立多个索引 2,不同的数据表有着不同的索引特性使用的时候需要区别对待 3,如何创建索引 用alter table命令创建索引 用create index 命令创建索引 在create table 时创建索引 五，变更数据表的结构 当发现某个数据表的结构己经不能满足我们的使用要求时,就要对其结构进行变更.可能需要这个数据表存放比以前更多的信息;也可能是这个数据表里面的某些信息己经没用;了或许是现有的某个数据列宽度太窄…在这些情况下都要用到alter 语匀 重新命名数据表 alter table A rename to B //数据表A改名为, rename table A to B //数据表A改名为, rename A toC,B to A,C to A //数据表A和数据表,互换名字 alter table S.A rename to T.A //数据库S里面的表A移动到数据库B里面 rename table S.A to T.A //数据库S里面的表A移动到数据库B里面 2,改变数据列的类型 我们现在要把数据表A里面的一个smallint unsigned类型的数据列I再次改动为 mediumint unsigned 类型 alter table A motify I mediumint unsigned alter table A change I I mediumint unsigned 注意change子句的特点:不仅能够改变数据列的类型，还能改变数据列的名字。这是modify子句所不能完成的。下面就把这个数据列改名了。 alter table A change I J mediumint unsigned 3,将数据表由可变长度数据行转变成固定长度数据行 有的时候为了提高性能，需要做这样的转变，但有一点需要注意:必须用同一条alter命令来一次改变所有的数据列，不能仅仅改变一个数据列～举例如下: create table A(name varchar(40),address varchar(80)) 我们开始修改命令就应该是: alter table A modify name char(40),modify address char(80); 4,将数据表由固定长度数据行转变成可变长度数据行 如果觉得空间利用率不高，那就需要再转变回来，这个就很简单了，没有特别要求 alter table A modify name varchar(40) 5,转换数据表类型 我们知道，MySQL数据库存在多种数据表类型，但每一种类型的特性并不相同。 如果你想让你的数据表支持事务处理机制。那就必须把它搞成BDB或innoBDB格式 alter table A type= BDB alter table A type= InnoBDB 2004年6月11日 MySQL里面的子选择 一，子选择基本用法 1，子选择的定义 子迭择允许把一个查询嵌套在另一个查询当中。比如说:一个考试记分项目把考试事件分为考试(T)和测验(Q)两种情形。下面这个查询就能只找出学生们的考试成绩 select * from score where event_id in (select event_id from event where type=”T”); 2，子选择的用法(3种) 用子选择来生成一个参考值 在这种情况下，用内层的查询语句来检索出一个数据值，然后把这个数据值用在外层查询语句的比较操作中。比如说，如果要查询表中学生们在某一天的测验成绩，就应该使用一个内层查询先找到这一天的测验的事件号，然后在外层查询语句中用这个事件号在成绩表里面找到学生们的分数记录。具体语句为: select * from score where id=(select event_id from event where date=?2002-03-21? and type=?Q?); 需要注意的是:在应用这种内层查询的结果主要是用来进行比较操作的分法时，内层查询应该只有一个输出结果才对。看例子，如果想知道哪个美国总统的生日最小，构造下列查询 select * from president where birth=min(birth) 这个查询是错的～因为MySQL不允许在子句里面使用统计函数～min()函数应该有一个确定的参数才能工作!所以我们改用子选择: select * from president where birht=(select min(birth) from presidnet); exists 和 not exists 子选择 上一种用法是把查间结果由内层传向外层、本类用法则相反，把外层查询的结果传递给内层。看外部查询的结果是否满足内部查间的匹配径件。这种“由外到内”的子迭择用法非常适合用来检索某个数据表在另外一个数据表里面有设有匹配的记录先找两个表内都存在的数据 select i1 from t1 where exists(select * from t2 where t1.i1=t2.i2); 再找t1表内存在,t2表内不存在的数据 select i1 form t1 where not exists(select * from t2 where t1.i1=t2.i2); 需要注意:在这两种形式的子选择里，内层查询中的星号代表的是外层查询的输出结果。内层查询没有必要列出有关数据列的名字，田为内层查询关心的是外层查询的结果有多少行。希望大家能够理解这一点 in 和not in 子选择 在这种子选择里面，内层查询语句应该仅仅返回一个数据列，这个数据列里的值将由外层查询语句中的比较操作来进行求值。还是以上题为例 先找两个表内都存在的数据 select i1 from t1 where i1 in (select i2 from t2); 再找t1表内存在,t2表内不存在的数据 select i1 form t1 where i1 not in (select i2 from t2); 好象这种语句更容易让人理解，再来个例子 比如你想找到所有居住在A和B的学生。 from student where state in(„A?,?B?) select * 二， 把子选择查询改写为关联查询的方法。 1，匹配型子选择查询的改写 下例从score数据表里面把学生们在考试事件(T)中的成绩(不包括测验成绩～)查询出来。 Select * from score where event_id in (select event_id from event "where type=”T”); 可见，内层查询找出所有的考试事件，外层查询再利用这些考试事件搞到学生们的成绩。 这个子查询可以被改写为一个简单的关联查询: Select score.* from score, event where score.event_id=event.event_id and event.event_id=”T”; 下例可以用来找出所有女学生的成绩。 Select * from score where student_id in (select student_id form student where sex = “f”); 可以把它转换成一个如下所示的关联查询: Select * from score Where student _id =student.student_id and student.sex =“f”; 把匹配型子选择查询改写为一个关联查询是有规律可循的。下面这种形式的子选择查询: Select * from tablel Where column1 in (select column2a from table2 where column2b = value); 可以转换为一个如下所示的关联查询: Select tablel. * from tablel,table2 Where table.column1 = table2.column2a and table2.column2b = value; (2)非匹配(即缺失)型子选择查询的改写 子选择查询的另一种常见用途是查找在某个数据表里有、但在另一个数据表里却没有的东西。正如前面看到的那样，这种“在某个数据表里有、在另一个数据表里没有”的说法通常都暗示着可以用一个left join 来解决这个问题。请看下面这个子选择查询，它可以把没有出现在absence数据表里的学生(也就是那些从未缺过勤的学生)给查出来: Select * from student Where student_id not in (select student_id from absence); 这个子选择查询可以改写如下所示的left join 查询: Select student. * From student left join absence on student.student_id =absence.student_id Where absence.student_id is null; 把非匹配型子选择查询改写为关联查询是有规律可循的。下面这种形式的子选择查询: Select * from tablel Where column1 not in (select column2 from table2); 可以转换为一个如下所示的关联查询: Select tablel . * From tablel left join table2 on tablel.column1=table2.column2 Where table2.column2 is null; 注意:这种改写要求数据列table2.column2声明为not null。
|