|
3.3. Creating and Using a Database 你只要知道了怎么输入命令，就可以访问数据库了。 假设你家里有几个动物照片，并且你想知道关于他们的各种信息。你可以通过创建表格保存数据并填入你想要的数据。然后你就可以通过从表格里查询数据来回答有关你的动物的不同问题。 本节的主要内容为：
这个动物园数据库很简单，但是不难想象现实情况下可能会用到相似类型的数据库。例如，农民可能使用一个类似的数据库来了解家畜，或兽医用来了解病号记录。以下几个中用到的例子可能包括样本数据的查询，这些资料可以从以下地址下载。 (http://www./Downloads/Contrib/Examples/menagerie.tar.gz) and Zip (http://www./Downloads/Contrib/Examples/menagerie.zip) formats. 功能1：使用 SHOW 语句来了解现在服务器上有哪些数据库： mysql> SHOW DATABASES; +----------+ | Database | +----------+ | mysql | | test | | tmp | +----------+ mysql 和 test 数据库是系统自带的。其他的是你自己创建的。mysql 数据库是必须的，因为它描述了用户的访问权限。Test数据库通常作为一个用户的试验空间。 注意：如果你没有SHOW DATABASES权限，你将看不到所有的数据库。相关信息请看Section 13.5.1.3, “GRANT and REVOKE Syntax”. 功能2：如果有test数据库，请试着访问它： mysql> USE test Database changed 注意：USE命令也不用以分号结束。如果你想加上分号也可以。 注意：USE命令必须在单独的一行内结束。 功能3：管理员用户给服务器用户授权： mysql> GRANT ALL ON menagerie.* TO ‘your_mysql_name‘@‘your_client_host‘; your_mysql_name 是 MySQL分配给你的用户名。 username assigned to you and your_client_host 是你连接到数据库的位置（即你的主机IP）。 如果管理员给你授权时创建了数据库，你可以直接使用。否则需要你自己创建。 功能4：创建数据库 mysql> CREATE DATABASE menagerie; 在 Unix中, 数据库名字是大小写敏感的，所以每次你提到你的数据库时必须是menagerie,而不是 Menagerie, MENAGERIE,或其他变量。对于表名来说也是一样的。在windows下没有这样的限制。但是由于种种原因，我们建议您一直使用创建时的大小写一致的名字 注意：创建了一个数据库并不等于已经选中了它来使用了。你必须使用以下命令明确说明 功能5-1：使用数据库 mysql> USE menagerie Database changed 数据库只要创建一次就可以了。但是每次使用前你都要使用上述命令。另外， 功能5-2当你调用mysql时，你可以在命令行上选择这个数据库。只要在连接参数之后指明数据库名字就可以了。 shell> mysql -h host -u user -p menagerie Enter password: ******** 注意：上述命令后面的menagerie不是密码。如果你想在同一行输入密码，在-p之后不能有任何空格。请使用格式：-pmypassword ，而不是-p mypassword 。 然而不推荐在同一行输入密码。因为这样容易密码被盗。 目前创建的数据库仍然是空的。 功能1：浏览数据库中的所有表格 mysql> SHOW TABLES; Empty set (0.00 sec) 最难的部分是确定你的数据库结构应该是什么样的：需要什么样的表，每个表中有些什么列。 你需要一个包含你的每一个宠物的记录的表。则这个表可以叫做pet table。并且这个表应该包括每一个宠物的名字。因为名字本身没有多少兴趣，所以这个表应该包含其他信息。例如，如果如果你家里有多于一个人养宠物了，那么你可能想列出每个动物的主任。你也可能想要记录某个特定的描述信息，如种类和性别。 如何记录年龄？把年龄直接存储在数据库里是不行的。因为随着时间的变化，年龄也在变化。这样你需要经常更新你的数据库中的年龄信息。如果记录一个固定值，如出生日期将是一种很好的办法。这样，当你需要查询年龄时，你可以通过计算当前时间和记录时间的差异来得到年龄。MySQL提供了日期计算，所以这是很好实现的。记录出生日期比记录年龄有优势：
也许你认为宠物表中的其他信息比较有意义，但是目前有以下字段就足够了： name, owner, species, sex, birth, and death. 功能2：使用 CREATE TABLE语句来指定你的表格框架： mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); VARCHAR类型对于长度变化的name, owner, 和 species 字段是很好的选择。这些字段的长度不需要一致，也不需要是20。在MySQL 5.0中, 通常你可以选择从1到65535的任意长度，只要是你觉得合理就行。（注意：MySQL 有几种方式来表示动物记录中的sex值。如‘m‘ 和 ‘f‘, 或 ‘male‘ 和‘female‘. 最简单的是使用单个字符‘m‘ 和 ‘f‘. 表示生日和死亡日期的DATA类型字段有相当多的选择。 功能3：一旦你创建了表， SHOW TABLES 命令会产生一些输出: mysql> SHOW TABLES; +---------------------+ | Tables in menagerie | +---------------------+ | pet | +---------------------+ 功能4：如果想检查是否创建了一个你预期的表的结构，可以使用DESCRIBE 命令: mysql> DESCRIBE pet; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | species | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 任何时候你都可以使用 DESCRIBE, 如, 你忘记了你的表中的字段名字的时候。 创建了表之后，你需要输入数据。对此可以使用LOAD DATA 和INSERT 语句来完成。 假设你的宠物记录可以描述如下。(注意 MySQL用 ‘YYYY-MM-DD‘ 格式表示日期format;这可能和你过去用的格式不同)
因为你开始的表格是空的，所以，一个简单的填充表的方法是：创建一个包含你的每个宠物的记录行的文件，然后使用一个语句把文件内容装到你的table中。 你可以创建一个text文件pet.txt。文件中每行包括一个记录，值用Tab键分割，顺序和表格中字段的顺序一致。对于漏掉之的字段，可是使用NULL值。为了在text文件中表示，使用\N(backslash, capital-N).如， Whistler 的记录如下： (值之间的空格是一个tab符):
功能5：要把这个文件装入pet表，使用如下命令： mysql> LOAD DATA LOCAL INFILE ‘/path/pet.txt‘ INTO TABLE pet; 注意：如果你在Windows系统中使用以\r\n结尾行的编辑器你应使用以下命令： mysql> LOAD DATA LOCAL INFILE ‘/path/pet.txt‘ INTO TABLE pet -> LINES TERMINATED BY ‘\r\n‘; (在运行OS X的苹果机上，你要用 LINES TERMINATED BY ‘\r‘.) 在LOAD DATA语句中，如果愿意，你可以指定列值的分隔符和行的结束符，但是默认值是tab和换行linefeed。这些对于读取文件pet.txt就足够了。 如果这个语句失败了，可能是因为 MySQL安装默认没有local文件功能。 参考 Section 5.6.4, “Security Issues with LOAD DATA LOCAL” 知道如何改变这个功能。 功能6：如果你想添加一个新的记录，INSERT语句是很有用的。它最简单的格式下，你可以为按照你创建的表格中的字段的顺序每个字段指定一个值。假设如果你有一个新的名为 "Puffball"的鼠类。你可以使用INSERT语句如下：(关键字按照大写字符没有出错。但是我用小写就有错。不知道为什么？) mysql> INSERT INTO pet -> VALUES (‘Puffball‘,‘Diane‘,‘hamster‘,‘f‘,‘ 注意：这里的日期用引号括起来作为字符串。在INSERT语句中可以直接插入NULL值来表示一个未知值。不用使用\N来表示了。 在这个例子中，如果想把数据输入表中，需要多个INSERT语句而不像LOAD DATA一样只要一个语句。F SELECT语句用来从表中取出数据。这个语句的一般格式如下： SELECT what_to_select FROM which_table WHERE conditions_to_satisfy; what_to_select 表示你想看到的内容。这可以是一些列，或*表示所有列。which_table 表示你要读取数据的那个表。WHERE 子句是可选的。如果这个子句出现了， conditions_to_satisfy 指定一个或多个满足读取数据的条件分的行。 功能7：SELECT语句的最简单格式是： mysql> SELECT * FROM pet; +----------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +----------+--------+---------+------+------------+------------+ | Fluffy | Harold | cat | f | | Claws | Gwen | cat | m | | Buffy | Harold | dog | f | | Fang | Benny | dog | m | | Bowser | Diane | dog | m | | Chirpy | Gwen | bird | f | | Whistler | Gwen | bird | NULL | | Slim | Benny | snake | m | | Puffball | Diane | hamster | f | +----------+--------+---------+------+------------+------------+ 当你想读取表中的所有内容时，这种格式的SELECT是很有用的。 如，当你刚刚把所有数据导入表中时。如你碰巧想起有关Bowser的生日可能不对。本来是1989，但是写成了1979 。解决方法有以下几种。 1 编辑文件pet.txt来纠正错误，然后使用DELETE和LOAD DATA来清空表并重新导入数据。 mysql> DELETE FROM pet; · mysql> LOAD DATA LOCAL INFILE ‘pet.txt‘ INTO TABLE pet; 然而，如果这样做了，你必须重新输入Puffball的记录. 2 使用UPDATE语句解决上述问题: · mysql> UPDATE pet SET birth = ‘ UPDATE 只改变了提到的记录，所以不用重新导入数据。 功能8：如前所述，很容易访问正张表。只要漏掉SELECT语句中的WHERE子句就可以了。下面介绍如何访问表中特定行的数据。 加入你只想知道Bowser的生日。你可以选择Browser的记录： mysql> SELECT * FROM pet WHERE name = ‘Bowser‘; +--------+-------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+------------+ | Bowser | Diane | dog | m | +--------+-------+---------+------+------------+------------+ 通常，字符串比较是大小写不敏感的。所以你可以指定名字为 ‘bowser‘, ‘BOWSER‘, 等. 查询结果一样。 你也可以指定任意列的条件。如，任意晚于1998年出生的动物： mysql> SELECT * FROM pet WHERE birth > ‘ +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | | Puffball | Diane | hamster | f | +----------+-------+---------+------+------------+-------+ 功能9：复合条件符：AND mysql> SELECT * FROM pet WHERE species = ‘dog‘ AND sex = ‘f‘; +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Buffy | Harold | dog | f | +-------+--------+---------+------+------------+-------+ 功能10：或条件符:OR mysql> SELECT * FROM pet WHERE species = ‘snake‘ OR species = ‘bird‘; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | Chirpy | Gwen | bird | f | | Whistler | Gwen | bird | NULL | | Slim | Benny | snake | m | +----------+-------+---------+------+------------+-------+ 功能11：AND 和 OR 也可以一起使用。 mysql> SELECT * FROM pet WHERE (species = ‘cat‘ AND sex = ‘m‘) -> OR (species = ‘dog‘ AND sex = ‘f‘); +-------+--------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +-------+--------+---------+------+------------+-------+ | Claws | Gwen | cat | m | | Buffy | Harold | dog | f | +-------+--------+---------+------+------------+-------+ 功能12：也可以选择一行中的特定列，而不必所有列都显事； mysql> SELECT name, birth FROM pet; +----------+------------+ | name | birth | +----------+------------+ | Fluffy | | Claws | | Buffy | | Fang | | Bowser | | Chirpy | | Whistler | | Slim | | Puffball | +----------+------------+ 查询宠物的主人： mysql> SELECT owner FROM pet; +--------+ | owner | +--------+ | Harold | | Gwen | | Harold | | Benny | | Diane | | Gwen | | Gwen | | Benny | | Diane | +--------+ 功能13：注意到上述命令中有的宠物主人出现了不止一次。要想使得他们不重复出现，要使用DISTINCT: mysql> SELECT DISTINCT owner FROM pet; +--------+ | owner | +--------+ | Benny | | Diane | | Gwen | | Harold | +--------+ 功能14：你可以使用WHERE 子句来限制列的选择： mysql> SELECT name, species, birth FROM pet -> WHERE species = ‘dog‘ OR species = ‘cat‘; +--------+---------+------------+ | name | species | birth | +--------+---------+------------+ | Fluffy | cat | | Claws | cat | | Buffy | dog | | Fang | dog | | Bowser | dog | +--------+---------+------------+ 功能14：上述语句中的结果显式是没有顺序的。要想使结果按照一定的顺序出现，可以使用ORDER BY子句。 按照日期来显示动物生日： mysql> SELECT name, birth FROM pet ORDER BY birth; +----------+------------+ | name | birth | +----------+------------+ | Buffy | | Bowser | | Fang | | Fluffy | | Claws | | Slim | | Whistler | | Chirpy | | Puffball | +----------+------------+ 功能15：在字符类型的列中，排序－如比较操作－是大小写不敏感的。你可以可以通过以下命令使其大小写敏感： ORDER BY BINARY col_name. 默认顺序是升序。要想降序排列，要加上关键字： DESC（加在要降序的字段名之后） mysql> SELECT name, birth FROM pet ORDER BY birth DESC; +----------+------------+ | name | birth | +----------+------------+ | Puffball | | Chirpy | | Whistler | | Slim | | Claws | | Fluffy | | Fang | | Bowser | | Buffy | +----------+------------+ 功能16：升，降序联合使用 mysql> SELECT name, species, birth FROM pet -> ORDER BY species, birth DESC; +----------+---------+------------+ | name | species | birth | +----------+---------+------------+ | Chirpy | bird | | Whistler | bird | | Claws | cat | | Fluffy | cat | | Fang | dog | | Bowser | dog | | Buffy | dog | | Puffball | hamster | | Slim | snake | +----------+---------+------------+ 我们可以看到：只有birth列是按DESC排列的，不影响其他列。因为只有birth列使用了关键字DESC MySQL 提供了几个用来计算日期的功能。如，计算年龄或提取日期的某些部分。 功能17：为了计算你的每个宠物的年龄，先计算宠物生日和当前日期的年份之间的差别，然后，如果现在的日子比生日中的日子早，就减去1。下面的查询显示了每个宠物的生日，当前日期及其年龄。 mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet; +----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Fluffy | | Claws | | Buffy | | Fang | | Bowser | | Chirpy | | Whistler | | Slim | | Puffball | +----------+------------+------------+------+ 这里， YEAR()取出日期里的年份， RIGHT() 取出日期里最右面的5个字符（即日期里MM-DD格式的部分）。比较MM-DD部分的值是1或0。这用来调整年份的差别（当前日期比生日那年早的时候使用1）。整个表达式是很难看的。所以使用age标签来是输出更加有意义。 功能18：查询结果按照某给定顺序会更加易于查看。这要使用ORDER BY关键字： mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet ORDER BY name; +----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Bowser | | Buffy | | Chirpy | | Claws | | Fang | | Fluffy | | Puffball | | Slim | | Whistler | +----------+------------+------------+------+ 为了按照age排序，要使用以下子句： mysql> SELECT name, birth, CURDATE(), -> (YEAR(CURDATE())-YEAR(birth)) -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) -> AS age -> FROM pet ORDER BY age; +----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Chirpy | | Puffball | | Whistler | | Slim | | Claws | | Fluffy | | Fang | | Bowser | | Buffy | +----------+------------+------------+------+ 可以使用相似的语句来查询宠物死的时候的年龄。首先确定那些没有死的宠物，然后多于那些death值不为NULL的宠物计算其寿命。 mysql> SELECT name, birth, death, -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5)) -> AS age -> FROM pet WHERE death IS NOT NULL ORDER BY age; +--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | +--------+------------+------------+------+ 查询death值是NOT NULL而不是NULL的，是因为 NULL 是一个特殊的不能用一般的比较运算符比较的值。稍后在Section 3.3.4.6, “Working with NULL Values”. 讨论 功能19：如果你想知道下个月是那些宠物的生日？这类查询是和年，日无关的。你只需要抽取生日中的月值就可以了。MySQL提供了以下函数： YEAR(), MONTH(),DAYOFMONTH()分别对应日期里面的年份，月份和日子。这里应该用MONTH()。 mysql> SELECT name, birth, MONTH(birth) FROM pet; +----------+------------+--------------+ | name | birth | MONTH(birth) | +----------+------------+--------------+ | Fluffy | | Claws | | Buffy | | Fang | | Bowser | | Chirpy | | Whistler | | Slim | | Puffball | +----------+------------+--------------+ 如果下个月是5月，可以直接查询5月生日的宠物： mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5; +-------+------------+ | name | birth | +-------+------------+ | Buffy | +-------+------------+ 功能20：你可以写一个与当前月份无关的查询，以便于你不必为特定月份使用数字。DATE_ADD() 允许你加一个时间段值（interval值）到一个给定的日子上。如果你加了一个值给CURDATE(), 然后从 MONTH()中减一个数, 结果就是你查询的生日的月份： mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH)); 功能21：另一种方式是取得下个月的值。使用MOD函数。 mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1; MONTH 返回一个1到12之间的值。MOD(something,12) 返回一个0到11之间的值。所以加操作必须在MOD（）之后。否则降只得到1到11之间的月份。 概念上讲， NULL 意味着“一个未知的遗漏的值”，并且对它的处理不同于其他值。为了测试NULL，你不可以使用一般的算术运算符。如， =, <, or <>. 你可以使用以下命令来测试: mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 显然，你得不到有意义的值。应该使用IS NULL和IS NOT NULL。 mysql> SELECT 1 IS NULL, 1 IS NOT NULL; +-----------+---------------+ | 1 IS NULL | 1 IS NOT NULL | +-----------+---------------+ | 0 | 1 | +-----------+---------------+ 注意：在 MySQL中, 0 或 NULL意味着false ，其他任何值意味着true.默认的布尔值true是1。 两个NULL可以认为是等价的。使用GROUP BY关键字。 当执行ORDER BY时, 首先写NULL然后是： ORDER BY ... ASC 或ORDER BY ... DESC. 处理NULL时的一个常见错误是认为可以给NULL值的列插入一个0或空字符串。但是事实不是这样的。因为NULL意味着没有值。测试如下： mysql> SELECT 0 IS NULL, 0 IS NOT NULL, ‘‘ IS NULL, ‘‘ IS NOT NULL; +-----------+---------------+------------+----------------+ | 0 IS NULL | 0 IS NOT NULL | ‘‘ IS NULL | ‘‘ IS NOT NULL | +-----------+---------------+------------+----------------+ | 0 | 1 | 0 | 1 | +-----------+---------------+------------+----------------+ 因此可以给非NULL的列插入0或空串。详见 Section A.5.3, “Problems with NULL Values”. |
|
|
来自： 元傲一 > 《mysql5.0手册中文翻译》
