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:
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;
|