MySQL notes

manual

http://dev.mysql.com/doc/mysql/ja/index.html

checking users

mysql> select Host,User,Password from user;
+-----------+--------+-------------------------------------------+
| Host      | User   | Password                                  |
+-----------+--------+-------------------------------------------+
| localhost | root   | *6AC44D290B438A954DD86046515B4A3F00B2FB04 |
| localhost | sample | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| %         | sample | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+--------+-------------------------------------------+
3 rows in set (0.00 sec)

hints...

C:\Program Files\MySQL\MySQL Server 4.1\bin>mysql sampledb -u sample -p
Enter password: ******** // password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 4.1.14-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select callid, queue, state from gr9calls where queue like '%abc%' an
d state != 'CLOSED';

mysql> create table mycalls select * from gr9calls where queue like '%abc%';

Query OK, 231 rows affected (1.97 sec)
Records: 231  Duplicates: 0  Warnings: 0

exporting data

use 'select ... into outfile 'filename'

loading data

mysql> load data infile 'c:/temp/arec.txt' into table gr9calls2;
Query OK, 1 row affected, 11 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 11

mysql> select * into outfile 'c:/temp/arecexp.txt' from gr9calls2;
Query OK, 1 row affected (0.00 sec)

renaming table

mysql> rename table gr9calls2 to gr9calls3;
Query OK, 0 rows affected (0.11 sec)

root op examples

$ mysql -u root -p
Passwd:
mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| sampledb |
| test     |
+----------+
3 rows in set (0.02 sec)

mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |

create a table

mysql> create database webapp;
Query OK, 1 row affected (0.06 sec)

creating a user

mysql> GRANT ALTER,CREATE,CREATE TEMPORARY TABLES,DELETE,DROP,
INDEX,SELECT,UPDATE ON webapp.* TO APP IDENTIFIED BY 'APP';
Query OK, 0 rows affected (0.09 sec)

mysql> select User,Host,Password,Select_priv from user;
+--------+-----------+-------------------------------------------+-------------+

| User   | Host      | Password                                  | Select_priv |

+--------+-----------+-------------------------------------------+-------------+

| root   | localhost | *6AC44D290B438A954DD86046515B4A3F00B2FB04 | Y           |

| sample | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N           |

| sample | %         | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | N           |

| APP    | %         | *CA936E88A09E9A09F1721253D77A66F42754C72E | N           |

+--------+-----------+-------------------------------------------+-------------+

mysql> select Host,Db,User,Select_priv from db;
+-----------+-----------------+--------+-------------+
| Host      | Db              | User   | Select_priv |
+-----------+-----------------+--------+-------------+
| %         | sampledb        | sample | Y           |
| localhost | sampledb        | sample | Y           |
| %         | appserv_samples | APP    | Y           |
| %         | webapp          | APP    | Y           |
+-----------+-----------------+--------+-------------+
4 rows in set (0.00 sec)