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 |
+---------------------------------------+------------+

String functions Reference


 

Subject