Mysql之旅 一

  第一次正式学习MySQL，fighting.

  1，查询的基本知识

#查看当前MySQL的版本与当前日期。
  select version(),current_date;
+---------------------+--------------+
| version()           | current_date |
+---------------------+--------------+
| 5.0.22-community-nt | 2008-03-26   |
+---------------------+--------------+
#注意
1,mysql用表格(行和列)方式显示查询输出。第一行包含列的标签，随后的行是查询结果。通常，列标签是你取自数据库表的列的名字。如果你正在检索一个表达式而非表列的值(如刚才的例子)，mysql用表达式本身标记列。
2,另外输入的命令大小写是无关紧要的。
  selEct verSion(),CurRent_dAte;命令将得到同样的输出。
#可以将mysql作为一个简单的计算器。
+------------------+---------+
| sin(PI()/4)      | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 |      25 |
+------------------+---------+
3,可以在一行命令中输入多个语句。
select version();select now();
+---------------------+
| version()           |
+---------------------+
| 5.0.22-community-nt |
+---------------------+
+---------------------+
| now()               |
+---------------------+
| 2008-03-26 15:00:31 |
+---------------------+
4,一个命令可以分多行写，mysql判定一个命令是否结束的依据是分号。
mysql> select
    -> user(),
    -> current_date;
+----------------+--------------+
| user()         | current_date |
+----------------+--------------+
| root@localhost | 2008-03-26   |
+----------------+--------------+
5，如果你决定不想执行正在输入过程中的一个命令输入“\c”取消它。
mysql> select
    -> \c
mysql>
6，mysql的提示符
  mysql  准备好接受新的命令
->     等待多行命令的下一行
’>     等待下一行，等待以单引号(“'”)开始的字符串的结束。
">     等待下一行，等待以双引号(“"”)开始的字符串的结束。
`>     等待下一行，等待以反斜点(‘`’)开始的识别符的结束。
/*>    等待下一行，等待以/*开始的注释的结束。
 
  当看到一个 '> 或 "> 提示符时，这意味着已经输入了包含以‘'’或‘"’括号字符开始的字符串的一行，但是还没有输入终止字符串的匹配引号。这显示你粗心地省掉了一个引号字符。例如：
select * from my_table where nme='smith and age>30;
  走到这一步，你该做什么？最简单的是取消命令。然而，在这种情况下，你不能只是输入\c，相反，应输入关闭的引号字符(这样mysql知道你完成了字符串)，然后输入\c：
  select * from my_table where nme='smith and age>30;
'>'\c
mysql>

  2，创建并使用数据库。

1，显示服务器中所有的数据库。当前用户必须具备相关的权限。
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| test               |
+--------------------+
2，使用某个数据库(也可以在登录数据库是选择，下面会写到)
use test;（与quit类似这里可以不加分号，另外use语句有一个特殊的地方，该命令不能分行描述。）
  当你无权使用该数据库时，可以要求管理员使用如下命令：
grant all on test.* to 'your_user_name'@'your_client_host';

创建并选择数据库
1，登录时选取数据库
  mysql -h host -u user -p test;
  Tip：刚才显示的命令行中的menagerie不是你的 密码。如果你想要在命令行上在-p选项后提供 密码，则不能插入空格(例如，如-pmypassword，不是-p mypassword)。但是，不建议在命令行输入密码，因为这样会暴露 密码，能被在机器上登录的其它用户窥探到。

  2，创建表以及表的相关操作

1，创建表pet
  mysql> create table pet (name varchar(20),owner varchar(20),
    -> species varchar(20),sex char(1),birth date,death date)
    -> ;
使用show tables;describe pet;命令查看该table.
+----------------+
| Tables_in_demo |
+----------------+
| pet            |
| user           |
+----------------+
+---------+-------------+------+-----+---------+-------+
| 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    |       |
+---------+-------------+------+-----+---------+-------+
2,使用Table pet.

装载数据
i,使用.txt文件一次装载。
  每行包含一个记录，用定位符(tab)把值分开，并且以CREATE TABLE语句中列出的列次序给出。对于丢失的值(例如未知的性别，或仍然活着的动物的死亡日期)，你可以使用NULL值。为了在你的文本文件中表示这些内容，使用\N（反斜线，字母N）。
如c:/pet.txt
Fluffy    Harold    cat    f    1993-02-04    \N
Claws    Gwen    cat    m    1994-03-17    \N
Buffy    Harold    dog    f    1989-05-13    \N
Fang    Benny    dog    m    1990-08-27    \N
Bowser    Diane    dog    m    1979-08-31    1995-07-29
Chirpy    Gwen     bird    f    1998-09-11    \N
Whistler    Gwen    bird    \N    1997-12-09    \N
Slim    Benny    snake    m    1996-04-29    \N
使用如下命令：
  mysql> load data local infile 'c:/pet.txt' into table pet
    -> lines terminated by '\r\n';
注意第二行不可缺少。
ii，使用insert语句一次装载一个记录
  mysql> insert into pet
    -> values('Puffball','Diane','hamster
    '> ','f','1993-03-30',null);

从表中检索信息
i，一次检索所有信息
mysql>select * from pet;
  这时你发现Bowser正确的出生年是1989，而不是1979。至少有两种修正方法：
  1，编辑文件“pet.txt”改正错误，然后使用DELETE和LOAD DATA清空并重新装载表:
  mysql> DELETE FROM pet;
  mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
  2，用一个UPDATE语句仅修正错误记录：
  mysql> update pet set birth='1989-08-31' where name='Bowser';

ii,选择特殊行
  可以从表中只选择特定的行。例如，如果你想要验证你对Bowser的生日所做的更改，按下述方法选择Bowser的记录：
  mysql> select * from pet where name='Bowser';
  Tip:字符串比较时通常对大小些不敏感，因此你可以将名字指定为"bowser"、"BOWSER"等，查询结果相同。
  你可以在任何列上指定条件,例如，如果你想要知道哪个动物在1998以后出生的，测试birth列：
  mysql> select * from pet where birth>'1988-1-1';
+----------+--------+---------+------+------------+-------+
| name     | owner  | species | sex  | birth      | death |
+----------+--------+---------+------+------------+-------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL  |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL  |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL  |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL  |
+----------+--------+---------+------+------------+-------+
  可以组合条件，例如，找出雌性的狗：
  mysql> select * from pet where species='dog' and sex='f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+
  上面的查询使用AND逻辑操作符，也有一个OR操作符：
  mysql> select * from pet where species='bird' or species='snake'; +----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+
  AND和OR可以混用，但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    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

iii，选择指定列
  mysql> select name,sex  from pet;
  +----------+------+
  | name     | sex  |
  +----------+------+
  | Fluffy   | f    |
  | Claws    | m    |
  | Buffy    | f    |
  | Fang     | m    |
  | Bowser   | m    |
  | Chirpy   | f    |
  | Whistler | NULL |
  | Slim     | m    |
  +----------+------+
  找出谁拥有宠物，使用这个查询：
  mysql>select owner from pet;
  注意这里输出的结果中会有重复的记录，为了使输出减到最少，增加关键字DISTINCT检索出每个唯一的输出记录：
  mysql> select distinct owner from pet;
  +--------+
  | owner  |
  +--------+
  | Harold |
  | Gwen   |
  | Benny  |
  | Diane  |
  +--------+
  可以使用一个WHERE子句结合行选择与列选择。例如，要想查询狗和猫的出生日期，使用这个查询：
  mysql> select name,species,birth from pet where species='dog' or
      -> species='cat';
  +--------+---------+------------+
  | name   | species | birth      |
  +--------+---------+------------+
  | Fluffy | cat     | 1993-02-04 |
  | Claws  | cat     | 1994-03-17 |
  | Buffy  | dog     | 1989-05-13 |
  | Fang   | dog     | 1990-08-27 |
  | Bowser | dog     | 1979-08-31 |
  +--------+---------+------------+

iv，对检索结果进行排序
  了排序结果，使用ORDER BY子句。这里是动物生日，按日期排序：
  mysql> select name,birth from pet order by birth;
  +----------+------------+
  | name     | birth      |
  +----------+------------+
  | Bowser   | 1979-08-31 |
  | Buffy    | 1989-05-13 |
  | Fang     | 1990-08-27 |
  | Fluffy   | 1993-02-04 |
  | Claws    | 1994-03-17 |
  | Slim     | 1996-04-29 |
  | Whistler | 1997-12-09 |
  | Chirpy   | 1998-09-11 |
  +----------+------------+
  另外排序是不区分大小写的。对于等同但大小写不同的列，并未定义其顺序。对于某一列，可以使用BINARY强制执行区分大小写的分类功能，如：ORDER BY BINARY col_name.
  默认排序是升序，最小的值在第一。要想以降序排序，在你正在排序的列名上增加DESC（降序 ）关键字：
  mysql> select name ,birth from pet order by birth desc;
  +----------+------------+
  | name     | birth      |
  +----------+------------+
  | Chirpy   | 1998-09-11 |
  | Whistler | 1997-12-09 |
  | Slim     | 1996-04-29 |
  | Claws    | 1994-03-17 |
  | Fluffy   | 1993-02-04 |
  | Fang     | 1990-08-27 |
  | Buffy    | 1989-05-13 |
  | Bowser   | 1979-08-31 |
  +----------+------------+
  可以对多个列进行排序(每个列都必须制定排序的方式升序还是降序)，并且可以按不同的方向对不同的列进行排序。例如，按升序对动物的种类进行排序，然后按降序根据生日对各动物种类进行排序（最年轻的动物在最前面），使用下列查询：
  mysql> select name,species,birth from
    -> pet order by species,birth desc;
  +----------+---------+------------+
  | name     | species | birth      |
  +----------+---------+------------+
  | Chirpy   | bird    | 1998-09-11 |
  | Whistler | bird    | 1997-12-09 |
  | Fluffy   | cat     | 2000-01-01 |
  | Claws    | cat     | 1994-03-17 |
  | Fang     | dog     | 1990-08-27 |
  | Buffy    | dog     | 1989-05-13 |
  | Bowser   | dog     | 1979-08-31 |
  | Slim     | snake   | 1996-04-29 |
  +----------+---------+------------+
v，日期计算
  要想确定每个宠物有多大，可以计算当前日期的年和出生日期之间的差。如果当前日期的日历年比出生日期早，则减去一年。以下查询显示了每个宠物的出生日期、当前日期和年龄数值的年数字。
  mysql> select name,birth,curdate(),
    -> (year(curdate())-year(birth))
    -> -(right(curdate(),5)    -> as age
    -> from pet;
  +----------+------------+------------+------+
  | name     | birth      | curdate()  | age  |
  +----------+------------+------------+------+
  | Fluffy   | 2000-01-01 | 2008-03-26 |    8 |
  | Claws    | 1994-03-17 | 2008-03-26 |   14 |
  | Buffy    | 1989-05-13 | 2008-03-26 |   18 |
  | Fang     | 1990-08-27 | 2008-03-26 |   17 |
  | Bowser   | 1979-08-31 | 2008-03-26 |   28 |
  | Chirpy   | 1998-09-11 | 2008-03-26 |    9 |
  | Whistler | 1997-12-09 | 2008-03-26 |   10 |
  | Slim     | 1996-04-29 | 2008-03-26 |   11 |
  +----------+------------+------------+------+
  为了按age而非name排序输出，只要再加上一个ORDER BY子句：
  可以使用一个类似的查询来确定已经死亡动物的死亡年龄。
  mysql> select name,year(curdate()-birth)-(right(curdate(),5)-
    -> right(birth,5)) as age from pet where death is not null
    -> order by age;
  +--------+------+
  | name   | age  |
  +--------+------+
  | Bowser | NULL |
  +--------+------+
 
  如果你想要知道哪个动物下个月过生日，怎么办？对于这类计算，年和天是无关的，你只需要提取birth列的月份部分。MySQL提供几个日期部分的提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。在这里MONTH()是适合的函数。为了看它怎样工作，运行一个简单的查询，显示birth和MONTH(birth)的值：
  mysql> select name ,birth ,month(birth) from pet ;
  +----------+------------+--------------+
  | name     | birth      | month(birth) |
  +----------+------------+--------------+
  | Fluffy   | 2000-01-01 |            1 |
  | Claws    | 1994-03-17 |            3 |
  | Buffy    | 1989-05-13 |            5 |
  | Fang     | 1990-08-27 |            8 |
  | Bowser   | 1979-08-31 |            8 |
  | Chirpy   | 1998-09-11 |            9 |
  | Whistler | 1997-12-09 |           12 |
  | Slim     | 1996-04-29 |            4 |
  +----------+------------+--------------+
  找出下个月生日的动物也是容易的。假定当前月是4月，那么月值是4，你可以找在5月出生的动物 (5月)，方法是：
  mysql> select name,birth ,curdate() as date from pet
    -> where month(birth)=4;
  +------+------------+------------+
  | name | birth      | date       |
  +------+------------+------------+
  | Slim | 1996-04-29 | 2008-03-26 |
  +------+------------+------------+
  如果当前月份是12月，就有点复杂了。你不能只把1加到月份数(12)上并寻找在13月出生的动物，因为没有这样的月份。
  你甚至可以编写查询，不管当前月份是什么它都能工作。采用这种方法不必在查询中使用一个特定的月份，DATE_ADD( )允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，结果产生生日所在月份：
  mysql> SELECT name, birth FROM pet
      -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
  完成该任务的另一个方法是加1以得出当前月份的下一个月(在使用取模函数(MOD)后，如果月份当前值是12，则“回滚”到值0)：
mysql>select name,birth from pet
    ->where month(birth)=mod(month(curdate),12)+1;

vi，null值的使用
  概念上，NULL意味着“没有值”或“未知值”，为了测试NULL，你不能使用算术比较 操作符例如=、<或!=。
  mysql> select 1=null,1<>null,1null;
  +--------+---------+--------+--------+
  | 1=null | 1<>null | 1null |
  +--------+---------+--------+--------+
  |   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 |
  +-----------+---------------+
  TIP:在MySQL中，0或 NULL意味着假而其它值意味着真。布尔运算的默认真值是1。
  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 |
+-----------+---------------+------------+----------------+
  这意味着0或者‘’是非null值。

vii，模式匹配
  SQL模式匹配允许你使用“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。在 MySQL中，SQL的模式默认是忽略大小写的。下面给出一些例子。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE比较操作符。
  1，找出以b开头的名字：
  mysql> select name ,owner ,species,sex from pet where
    -> name like 'b%';
  +--------+--------+---------+------+
  | name   | owner  | species | sex  |
  +--------+--------+---------+------+
  | Buffy  | Harold | dog     | f    |
  | Bowser | Diane  | dog     | m    |
  +--------+--------+---------+------+
  2，以“fy”结尾的名字：
  mysql> select * from pet where name like '%fy';
  +--------+--------+---------+------+------------+-------+
  | name   | owner  | species | sex  | birth      | death |
  +--------+--------+---------+------+------------+-------+
  | Fluffy | Harold | cat     | f    | 2000-01-01 | NULL  |
  | Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
  +--------+--------+---------+------+------------+-------+
  要想找出包含“w”的名字：‘%w%’
  要想找出正好包含5个字符的名字，使用“_”模式字符：‘_____’
  由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。
  ·         ‘.’匹配任何单个的字符。
  ·         字符类“[...]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
  ·         “ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]*”匹配任何数量的数字，而“.*”匹配任何数量的任何字符。
  ·          如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于LIKE模式匹配，只有与整个值匹配，模式才匹配)。
  ·          为了定位一个模式以便它必须匹配被测试值的开始或结尾，在模式开始处使用“^”或在模式的结尾用“$”。
  下面使用REGEXP重写上面所示的LIKE查询：
  mysql> select * from pet where name regexp '^b';
  如果需要强制匹配区分大小写，可以使用binary：
  mysql> select * from pet where name regexp binary '^b';
  mysql> select * from pet where name regexp 'fy$';
  mysql> select * from pet where name regexp 'w';
  为了找出包含正好5个字符的名字，使用“^”和“$”匹配名字的开始和结尾，和5个“.”实例在两者之间：
  mysql> select * from pet where name regexp '^.....$';
  你也可以使用“{n}”“重复n次”操作符重写前面的查询：
  mysql> select * from pet where name regexp '^.{5}$';