Useful SQL Command (MySQL)
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+
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:
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;