Useful SQL Command (MySQL)

To list users and access status from host:
mysql> select user,host from mysql.user;

Show privileges:
mysql> show grants for 'user'@'host';

Show Port number in localhost:
mysql> SHOW VARIABLES WHERE Variable_name = 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

Show Host name:
mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| hostname      | test.xyz.com       |
+---------------+--------------------+

Show Current User:
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

Check Current Setting of Database:
mysql>  SHOW VARIABLES;
Example: 
mysql> SHOW VARIABLES LIKE '%max_connect_errors%';

To See a list of options provided by MySQL:
shell> mysql --help

Show Character Set in MySQL:
mysql> SHOW CHARACTER SET LIKE '%utf%';
+---------+----------------+--------------------+--------+
| Charset | Description    | Default collation  | Maxlen |
+---------+----------------+--------------------+--------+
| utf8    | UTF-8 Unicode  | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode  | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode | utf16_general_ci   |      4 |
| utf32   | UTF-32 Unicode | utf32_general_ci   |      4 |
+---------+----------------+--------------------+--------+


Modify Column in MySQL:
mysql> alter table table_name modify column_name column_type;

Example: 
mysql> alter table employee modify employee_name varcar(200);

Add new column in Existing table:
mysql> ALTER TABLE <table_name> ADD <column_name> VARCHAR( 100 ) NOT NULL ;
Exmample: 
MYSQL>ALTER TABLE employee ADD address VARCHAR( 100 ) NOT NULL ;

If you want to add the new column after some particular column:
mysql>ALTER TABLE <table_name> ADD <column_name> VARCHAR( 100 ) NOT NULL AFTER <existing_column_name>;
Example: 
MYSQL>ALTER TABLE employee ADD address VARCHAR( 100 ) NOT NULL AFTER name ;


Rename Table in MySQL:
mysql> rename table <old_name> to <new_name>;

Example: 
mysql> rename table story to story_detail;



Comments