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)
Show size of all databases
SELECT
s.schema_name AS 'Schema',
SUM(t.data_length) AS Data,
SUM( t.index_length ) AS Indexes,
SUM(t.data_length) + SUM(t.index_length) AS 'Mb Used',
IF(SUM(t.data_free)=0,'',SUM(t.data_free)) As 'Mb Free',
IF( SUM(t.data_free)=0,
'',
100 * (SUM(t.data_length) + SUM(t.index_length)) / ((SUM(t.data_length)+SUM(t.index_length) + SUM(IFNULL(t.data_free,0))) )
) AS 'Pct Used',
COUNT(table_name) AS Tables
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
GROUP BY s.schema_name
WITH ROLLUP
+--------------------+------------+-----------+------------+----------+----------+--------+ | Schema | Data | Indexes | Mb Used | Mb Free | Pct Used | Tables | +--------------------+------------+-----------+------------+----------+----------+--------+ | information_schema | 0 | 11264 | 11264 | | | 72 | | mysql | 704941 | 102400 | 807341 | 1905 | 99.7646 | 28 | | performance_schema | 0 | 0 | 0 | | | 52 | | NULL | 1018268212 | 531165184 | 1549433396 | 12584817 | 99.1943 | 161 | +--------------------+------------+-----------+------------+----------+----------+--------+
Show tables with rows
select table_name, table_rows from information_schema.tables where table_schema = '<your_schema>' and table_rows is not NULL;
+---------------------------------------+------------+ | TABLE_NAME | TABLE_ROWS | +---------------------------------------+------------+ | CHARACTER_SETS | 0 | | CHECK_CONSTRAINTS | 0 | | COLLATIONS | 0 | | COLLATION_CHARACTER_SET_APPLICABILITY | 0 | | COLUMNS | 0 | +---------------------------------------+------------+