MySQL 5 Useful Commands
MYSQL 5 Tutorial
The following is a summary of useful commands for mysql
[root@orasos jquery_update]# mysql -u root -p
Enter password:
mysql> select version(), current_date;
<pre>
+-----------+--------------+
| version() | current_date |
+-----------+--------------+
| 5.0.45 | 2008-03-27 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql> select user(), now(), current_date;
+----------------+---------------------+--------------+
| user() | now() | current_date |
+----------------+---------------------+--------------+
| root@localhost | 2008-03-27 10:44:30 | 2008-03-27 |
+----------------+---------------------+--------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Products |
| call_center |
| mediawiki |
| mysql |
| wordpress |
+--------------------+
6 rows in set (0.00 sec)mysql> use mysql Database changed
mysql> select Host, User from user;
+-----------------------+------------+
| Host | User |
+-----------------------+------------+
| 127.0.0.1 | admin |
| localhost | myuser |
| localhost | admin |
| localhost | wordpress1 |
+-----------------------+------------+
4 rows in set (0.00 sec)mysql> use Products 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> show tables;
+--------------------+
| Tables_in_Products |
+--------------------+
| Product |
| Product_type |
| brand |
| country |
| old_prod |
| product_stag |
+--------------------+
mysql> desc Product_type;
+---------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| Prod_typ_cd | varchar(3) | NO | | | |
| Prod_type_nam | varchar(100) | NO | | | |
+---------------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table Product_type;
+--------------+-------------------------------------------------+
| Table | Create Table |
+--------------+-------------------------------------------------+
| Product_type | CREATE TABLE `Product_type` (
`Prod_typ_cd` varchar(3) NOT NULL,
`Prod_type_nam` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+--------------+-------------------------------------------------+
1 row in set (0.00 sec)
Update based on sub query
UPDATE jobs AS toTable, jobs AS fromTable SET toTable.job_type_id = fromTable.job_type_id, toTable.job_company_id = fromTable.job_company_id, toTable.job_source = fromTable.job_source, WHERE (toTable.job_id = 6) AND (fromTable.job_id = 1)
Select where updated in last one day
SELECT * FROM `Product` WHERE DATEDIFF(CURDATE(), `update_dt`) < 2
mysql> repair table mw_searchindex quick;
+--------------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| mediawiki.mw_searchindex | repair | status | OK |
+--------------------------+--------+----------+----------+
1 row in set (0.06 sec)
mysql> optimize table mw_searchindex;
+--------------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| mediawiki.mw_searchindex | optimize | status | OK |
+--------------------------+----------+----------+----------+
1 row in set (0.01 sec)
Case Sensitivity for MySQL
String comparisons are case insensitive by default. To override this add COLLATE latin1_bin to the column name
mysql> select col1, col2 from table1 where col1 <> col2 COLLATE latin1_bin;
+------+------+
| Col1 | Col2 |
+------+------+
| ABC | abc |
+------+------+
1 row in set (0.01 sec)
mysql> show table status where data_free > 50000;
Show tables that may need to be optimized
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| accesslog | MyISAM | 10 | Dynamic | 1550 | 135 | 445568 | 281474976710655 | 122880 | 235660 | 7994 | 2008-08-28 21:59:48 | 2009-02-14 11:44:54 | NULL | utf8_general_ci | NULL | | |
| cache | MyISAM | 10 | Dynamic | 3 | 76826 | 417012 | 281474976710655 | 6144 | 186532 | NULL | 2008-08-28 21:13:05 | 2009-02-14 11:23:21 | NULL | utf8_general_ci | NULL | | |
| cache_filter | MyISAM | 10 | Dynamic | 13 | 10504 | 283644 | 281474976710655 | 6144 | 147088 | NULL | 2008-08-28 21:13:05 | 2009-02-14 11:21:36 | NULL | utf8_general_ci | NULL | | |
| cache_form | MyISAM | 10 | Dynamic | 0 | 0 | 517332 | 281474976710655 | 6144 | 517332 | NULL | 2008-08-28 21:13:05 | 2009-02-06 16:06:01 | NULL | utf8_general_ci | NULL | | |
| cache_page | MyISAM | 10 | Dynamic | 0 | 0 | 161500 | 281474976710655 | 6144 | 161500 | NULL | 2008-08-28 21:13:05 | 2009-02-14 10:29:43 | NULL | utf8_general_ci | NULL | | |
| sessions | MyISAM | 10 | Dynamic | 92 | 223 | 131512 | 281474976710655 | 67584 | 110932 | NULL | 2008-08-28 21:13:05 | 2009-02-14 11:44:54 | NULL | utf8_general_ci | NULL | | |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
6 rows in set (0.01 sec)