#acl merlyn:read,write All:read
----
<<TableOfContents>>
=== 安裝MySQL數據庫 ===
{{{#!bash
~ $ emerge -av mysql
}}}
==== 登錄報錯 ====
{{{
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'
}}}
解決方法：
mysqld_safe --user=mysql --skip-grant-tables --skip-networking&
{{{
mysql -u root mysql
mysql> update user set password=PASSWORD(103545) where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
brightmoon dotproject # mysql -u root -p

/etc/init.d/mysql restart即可
}}}
=== 修改root密碼并登錄數據庫 ===
{{{
~ $ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.66-log Gentoo Linux mysql-5.1.66

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
}}}
=== 查看/創建數據庫 ===
{{{
#!bash
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> CREATE DATABASE gentoo;
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| gentoo             |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)
}}}

=== 使用gentoo數據庫，并創建示例 ===
{{{
mysql> USE gentoo;
Database changed
mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
Query OK, 0 rows affected (0.13 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_gentoo |
+------------------+
| developers       |
+------------------+
1 row in set (0.00 sec)

mysql> DESCRIBE developers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(128) | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
| job   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO developers VALUES('Merlyn Chen', 'merlyncaulfield@gmail.com', 'toolchain');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe');
Query OK, 1 row affected (0.00 sec)

mysql> DESCRIBE developers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(128) | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
| job   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
}}}

=== 查看用戶 ===
{{{
mysql>  select User,Host from mysql.user;
+--------------+------------+
| User         | Host       |
+--------------+------------+
| root         | 127.0.0.1  |
|              | brightmoon |
| root         | brightmoon |
|              | localhost  |
| FreeSMonitor | localhost  |
| power_admin  | localhost  |
| poweradmin   | localhost  |
| powerdns     | localhost  |
| root         | localhost  |
+--------------+------------+

mysql> show grants for root@brightmoon;
+----------------------------------------------------------------------+
| Grants for root@brightmoon                                           |
+----------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'brightmoon' WITH GRANT OPTION |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show grants for FreeSMonitor@brightmoon;
ERROR 1141 (42000): There is no such grant defined for user 'FreeSMonitor' on host 'brightmoon'
mysql> show grants for FreeSMonitor@localhost;
+---------------------------------------------------------------------------------------------------------------------+
| Grants for FreeSMonitor@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'FreeSMonitor'@'localhost' IDENTIFIED BY PASSWORD '*2A032F7C5BA932872F0F045E0CF6B53CF702F2C5' |
| GRANT ALL PRIVILEGES ON `nms_v3`.* TO 'FreeSMonitor'@'localhost'                                                    |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
}}}

=== How to add a primary key to a MySQL table ===

{{{
ALTER TABLE goods ADD PRIMARY KEY(id)

ALTER TABLE groceries ADD COLUMN `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT ;
}}}