mysql学习之路二（转）
4 创建用户数据库
mysql> CREATE DATABASE MYTEST;
Query OK, 1 row affected (0.00 sec)
如果想改变MYSQL数据文件的默认路径，我们可以关闭MYSQL实例，修改配置文件”my.cnf”或”my.ini”（WINDOWS系统）中的datadir对应的参数值，然后把MYSQL默认安装的datadir下的内容拷贝到新的数据文件路径下，启动MYSQL实例这样我们再次创建数据库的时候数据文件就放在新的路径下了。
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| root |
+--------------------+
4 rows in set (0.01 sec)
mysql> USE MYTEST;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mytest |
+------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES;
Empty set (0.01 sec)
5 创建数据库用户
可以用两种方式创建MySQL账户：
1、 直接操作MySQL授权表user（不推荐）
2、 CREATE USER
mysql> create user test_cr identified by 'test_cr';
Query OK, 0 rows affected (0.23 sec)
3、直接使用GRANT语句
我们可以不用第二步的CREATE语法来执行，而直接GRANT就可以创建用户。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'LOCALHOST' IDENTIFIED BY 'TEST' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'%' IDENTIFIED BY 'TEST' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'LOCALHOST' IDENTIFIED BY 'TEST_NORMAL';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'%' IDENTIFIED BY 'TEST_NORMAL';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT USER FROM MYSQL.USER;
+-------------+
| USER |
+-------------+
| TEST |
| TEST_NORMAL |
| root |
| TEST |
| TEST_NORMAL |
| root |
+-------------+
6 rows in set (0.00 sec)
注意：其中两个账户有相同的用户名TEST和密码TEST。两个账户均为超级用户账户，具有完全的权限可以做任何事情。一个账户
('TEST'@'localhost')只用于从本机连接时。另一个账户('TEST'@'%')可用于从其它主机连接。请注意TEST的两个账户必须能
从任何主机以TEST连接。没有localhost账户，当TEST从本机连接时，mysql_install_db创建的localhost的匿名用户账户将占先。
结果是，TEST将被视为匿名用户。原因是匿名用户账户的Host列值比'TEST'@'%'账户更具体，这样在user表排序顺序中排在前面。
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.27-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
注意：用户名和密码区分大小写。
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mytest |
+------------+
1 row in set (0.00 sec)
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| TEST@localhost |
+----------------+
1 row in set (0.00 sec)
6 创建数据库对象
1 创建表
在测试MYSQL创建的时候还遇到了一些小问题，这里整理出来供大家参考：
mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> MC VARCHAR(60),DT DATE DEFAULT NOW());
ERROR 1067 (42000): Invalid default value for 'DT'
注意: AUTO_INCREMENT为MYSQL的自增类型。我们可以利用如下函数查询最后一个序列号的值：
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.06 sec)
本来想在创建表的时候给DT字段一个DEFAULT值(当前时间)，但是出现错误，尝试了几个MYSQL的函数(如CURDATE()等)都是如此，查阅了一下资料得出结论：
MYSQL的DEFAULT值只能是常量，如果想实现上述功能只有表中第一个TIMESTAMP类型字段可以做到。可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。自动更新第一个TIMESTAMP列在下列任何条件下发生：
A、列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。
B、列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。（注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。）
C、明确地设定TIMESTAMP列为NULL或NOW()。
格式：TIMESTAMP[（M）]
MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式来显示TIMESTAMP值，这主要取决于M值，它们分别为14(缺省值)\12\8\6。
mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> MC VARCHAR(60),DT TIMESTAMP);
Query OK, 0 rows affected (1.08 sec)
mysql> SHOW COLUMNS FROM TEST;
+-------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+----------------+
3 rows in set (0.17 sec)
mysql> insert into test(mc) values('ZhangSan');
Query OK, 1 row affected (0.13 sec)
mysql> select * from test;
+----+----------+---------------------+
| ID | MC | DT |
+----+----------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 |
+----+----------+---------------------+
1 row in set (0.06 sec)
注意：为表TEST新增加TIMESTAMP类型字段，我们发现不是表的第一个TIMESTAMP类型的字段的DEFAULT值不是系统时间。
mysql> ALTER TABLE TEST ADD RQ TIMESTAMP;
Query OK, 1 row affected (0.70 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM TEST;
+-------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | CURRENT_TIMESTAMP | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-------------+------+-----+---------------------+----------------+
4 rows in set (0.19 sec)
mysql> INSERT INTO TEST(MC) VALUES('LiSi');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM TEST;
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
+----+----------+---------------------+---------------------+
2 rows in set (0.01 sec)
mysql> INSERT INTO TEST(MC,RQ) VALUES('LiSi',NOW());
Query OK, 1 row affected (0.11 sec)
mysql> SELECT * FROM TEST;
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
| 3 | LiSi | 2007-05-25 10:03:29 | 2007-05-25 10:03:29 |
+----+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
注意：执行UPDATE操作,表中第一个TIMESTAMP字段自动修改为系统时间。
mysql> UPDATE TEST SET MC='WangWu' WHERE ID=3;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM TEST;
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
| 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 |
+----+----------+---------------------+---------------------+
1 rows in set (0.00 sec)
2 创建索引
MYSQL索引类型：
1、普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
2、唯一性索引
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种
方式创建：
创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
3、主键
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。
主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表
的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
4、全文索引
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型
的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER
TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，如要了解
更多信息，请参见MySQL documentation。
mysql> CREATE DATABASE MYTEST;
Query OK, 1 row affected (0.00 sec)
如果想改变MYSQL数据文件的默认路径，我们可以关闭MYSQL实例，修改配置文件”my.cnf”或”my.ini”（WINDOWS系统）中的datadir对应的参数值，然后把MYSQL默认安装的datadir下的内容拷贝到新的数据文件路径下，启动MYSQL实例这样我们再次创建数据库的时候数据文件就放在新的路径下了。
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| mytest |
| root |
+--------------------+
4 rows in set (0.01 sec)
mysql> USE MYTEST;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mytest |
+------------+
1 row in set (0.00 sec)
mysql> SHOW TABLES;
Empty set (0.01 sec)
5 创建数据库用户
可以用两种方式创建MySQL账户：
1、 直接操作MySQL授权表user（不推荐）
2、 CREATE USER
mysql> create user test_cr identified by 'test_cr';
Query OK, 0 rows affected (0.23 sec)
3、直接使用GRANT语句
我们可以不用第二步的CREATE语法来执行，而直接GRANT就可以创建用户。
mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'LOCALHOST' IDENTIFIED BY 'TEST' WITH GRANT OPTION;
Query OK, 0 rows affected (0.06 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'TEST'@'%' IDENTIFIED BY 'TEST' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'LOCALHOST' IDENTIFIED BY 'TEST_NORMAL';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON MYSQL.* TO TEST_NORMAL@'%' IDENTIFIED BY 'TEST_NORMAL';
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT USER FROM MYSQL.USER;
+-------------+
| USER |
+-------------+
| TEST |
| TEST_NORMAL |
| root |
| TEST |
| TEST_NORMAL |
| root |
+-------------+
6 rows in set (0.00 sec)
注意：其中两个账户有相同的用户名TEST和密码TEST。两个账户均为超级用户账户，具有完全的权限可以做任何事情。一个账户
('TEST'@'localhost')只用于从本机连接时。另一个账户('TEST'@'%')可用于从其它主机连接。请注意TEST的两个账户必须能
从任何主机以TEST连接。没有localhost账户，当TEST从本机连接时，mysql_install_db创建的localhost的匿名用户账户将占先。
结果是，TEST将被视为匿名用户。原因是匿名用户账户的Host列值比'TEST'@'%'账户更具体，这样在user表排序顺序中排在前面。
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uTEST -p mytest
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 5.0.27-community-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
注意：用户名和密码区分大小写。
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mytest |
+------------+
1 row in set (0.00 sec)
mysql> SELECT USER();
+----------------+
| USER() |
+----------------+
| TEST@localhost |
+----------------+
1 row in set (0.00 sec)
6 创建数据库对象
1 创建表
在测试MYSQL创建的时候还遇到了一些小问题，这里整理出来供大家参考：
mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> MC VARCHAR(60),DT DATE DEFAULT NOW());
ERROR 1067 (42000): Invalid default value for 'DT'
注意: AUTO_INCREMENT为MYSQL的自增类型。我们可以利用如下函数查询最后一个序列号的值：
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.06 sec)
本来想在创建表的时候给DT字段一个DEFAULT值(当前时间)，但是出现错误，尝试了几个MYSQL的函数(如CURDATE()等)都是如此，查阅了一下资料得出结论：
MYSQL的DEFAULT值只能是常量，如果想实现上述功能只有表中第一个TIMESTAMP类型字段可以做到。可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。自动更新第一个TIMESTAMP列在下列任何条件下发生：
A、列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。
B、列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。（注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。）
C、明确地设定TIMESTAMP列为NULL或NOW()。
格式：TIMESTAMP[（M）]
MySQL可以以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD、YYMMDD格式来显示TIMESTAMP值，这主要取决于M值，它们分别为14(缺省值)\12\8\6。
mysql> CREATE TABLE TEST(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> MC VARCHAR(60),DT TIMESTAMP);
Query OK, 0 rows affected (1.08 sec)
mysql> SHOW COLUMNS FROM TEST;
+-------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+----------------+
3 rows in set (0.17 sec)
mysql> insert into test(mc) values('ZhangSan');
Query OK, 1 row affected (0.13 sec)
mysql> select * from test;
+----+----------+---------------------+
| ID | MC | DT |
+----+----------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 |
+----+----------+---------------------+
1 row in set (0.06 sec)
注意：为表TEST新增加TIMESTAMP类型字段，我们发现不是表的第一个TIMESTAMP类型的字段的DEFAULT值不是系统时间。
mysql> ALTER TABLE TEST ADD RQ TIMESTAMP;
Query OK, 1 row affected (0.70 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SHOW COLUMNS FROM TEST;
+-------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------------------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| MC | varchar(60) | YES | | NULL | |
| DT | timestamp | NO | | CURRENT_TIMESTAMP | |
| RQ | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-------------+------+-----+---------------------+----------------+
4 rows in set (0.19 sec)
mysql> INSERT INTO TEST(MC) VALUES('LiSi');
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM TEST;
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
+----+----------+---------------------+---------------------+
2 rows in set (0.01 sec)
mysql> INSERT INTO TEST(MC,RQ) VALUES('LiSi',NOW());
Query OK, 1 row affected (0.11 sec)
mysql> SELECT * FROM TEST;
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
| 3 | LiSi | 2007-05-25 10:03:29 | 2007-05-25 10:03:29 |
+----+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
注意：执行UPDATE操作,表中第一个TIMESTAMP字段自动修改为系统时间。
mysql> UPDATE TEST SET MC='WangWu' WHERE ID=3;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM TEST;
+----+----------+---------------------+---------------------+
| ID | MC | DT | RQ |
+----+----------+---------------------+---------------------+
| 1 | ZhangSan | 2007-05-25 09:54:59 | 0000-00-00 00:00:00 |
| 2 | LiSi | 2007-05-25 10:02:47 | 0000-00-00 00:00:00 |
| 3 | WangWu | 2007-05-25 10:04:01 | 2007-05-25 10:03:29 |
+----+----------+---------------------+---------------------+
1 rows in set (0.00 sec)
2 创建索引
MYSQL索引类型：
1、普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );
2、唯一性索引
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种
方式创建：
创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );
3、主键
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。
主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表
的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。
4、全文索引
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型
的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER
TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，如要了解
更多信息，请参见MySQL documentation。
