Mysql Faq

[root@mybox doc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15 to server version: 4.0.15

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

mysql>
mysql> show databases;
+-----------+
| Database |
+-----------+
| kplaylist |
| mysql |
| opencrm |
| phpbb |
| test |
| tmp |
| wiki |
+-----------+
7 rows in set (0.22 sec)

mysql>
mysql> use opencrm;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

mysql> show tables;
+-------------------+
| Tables_in_opencrm |
+-------------------+
| action_type |
| actions |
| branches_type |
| clients |
| clients_nbr |
| contacts |
| contracts |
| countries |
| events_type |
| groups |
| interests_type |
| invoice_nbr |
| notes |
| priority_type |
| problem_notes |
| problem_status |
| problems |
| sales_summary |
| searchnames_type |
| sentfiles |
| session |
| solutions |
| solutions_type |
| status_type |
| systems |
| types |
| users |
+-------------------+
27 rows in set (0.00 sec)

mysql>

mysql> describe users;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| login | varchar(16) | YES | | NULL | |
| name | text | | | | |
| password | text | YES | | NULL | |
| groupID | int(11) | YES | | NULL | |
| email | text | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql>

mysql> select * from users;
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| id | login | name | password | groupID | email |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| 1 | Administrator | CRM Administrator | 60ec3511b1960ef46a80188fad0ec741 | 4 | root@localhost |
| 2000 | sales | [sales group] | no login | 0 | sales@be.stone-it.com |
| 2001 | technical | [technical group] | no login | 0 | technical@be.stone-it.com |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
3 rows in set (0.21 sec)

mysql>

mysql> select login,password from users;
+---------------+----------------------------------+
| login | password |
+---------------+----------------------------------+
| Administrator | 60ec3511b1960ef46a80188fad0ec741 |
| sales | no login |
| technical | no login |
+---------------+----------------------------------+
3 rows in set (0.00 sec)

mysql>


mysql> INSERT INTO users VALUES(3,"sriram","Sriram,Sreedhar","password","Technical","sriram@sriram.com");
Query OK, 1 row affected (0.08 sec)

mysql> select * from users;
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| id | login | name | password | groupID | email |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| 1 | Administrator | CRM Administrator | 60ec3511b1960ef46a80188fad0ec741 | 4 | root@localhost |
| 2000 | sales | [sales group] | no login | 0 | sales@be.stone-it.com |
| 2001 | technical | [technical group] | no login | 0 | technical@be.stone-it.com |
| 3 | sriram | Sriram,Sreedhar | password | 0 | sriram@sriram.com |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
4 rows in set (0.00 sec)

mysql>

mysql> select * from users;
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| id | login | name | password | groupID | email |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| 1 | Administrator | CRM Administrator | 60ec3511b1960ef46a80188fad0ec741 | 4 | root@localhost |
| 2000 | sales | [sales group] | no login | 0 | sales@be.stone-it.com |
| 2001 | technical | [technical group] | no login | 0 | technical@be.stone-it.com |
| 3 | sriram | Sriram,Sreedhar | password | 0 | sriram@sriram.com |
| 5 | admin | Admin User | | 0 | sriram@sriram.com |
| 6 | administer | Admin User | pass123 | 0 | sriram@sriram.com |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
6 rows in set (0.00 sec)

mysql>

mysql> update users set password="570a90bfbf8c7eab5dc5d4e26832d5b1" where id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

mysql>

mysql> select * from users;
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| id | login | name | password | groupID | email |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
| 1 | Administrator | CRM Administrator | 570a90bfbf8c7eab5dc5d4e26832d5b1 | 4 | root@localhost |
| 2000 | sales | [sales group] | no login | 0 | sales@be.stone-it.com |
| 2001 | technical | [technical group] | no login | 0 | technical@be.stone-it.com |
| 3 | sriram | Sriram,Sreedhar | password | 0 | sriram@sriram.com |
| 5 | admin | Admin User | | 0 | sriram@sriram.com |
| 6 | administer | Admin User | pass123 | 0 | sriram@sriram.com |
+------+---------------+-------------------+----------------------------------+---------+---------------------------+
6 rows in set (0.00 sec)



mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql>

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2005-12-17 12:10:11 |
+---------------------+
1 row in set (0.25 sec)

mysql>

mysql> select version();
+-----------+
| version() |
+-----------+
| 4.0.15 |
+-----------+
1 row in set (0.00 sec)

mysql>

How to delete a database

mysql> drop database phpgroupware;
Query OK, 0 rows affected (0.89 sec)

mysql> show databases;
+-----------+
| Database |
+-----------+
| kplaylist |
| mysql |
| opencrm |
| phpbb |
| test |
| tmp |
| wiki |
+-----------+
7 rows in set (0.00 sec)

mysql>