This days I’m playing with MySQL trying out stuff. I decided it could be helpful to somebody to share few things I learned. So there we go:
1. To Rename MySQL existent table name
RENAME TABLE old_table_name to new_table_name;
2. To completely wipe out the content of an existing Table in MySQL
TRUNCATE TABLE table_name; table_name = your table name to truncate
3. To RENAME column name in MySQL to another one
ALTER TABLE your_table_name CHANGE current_column_name new_column_name VARCHAR(100);
Note that in the above example to rename column in MySQL you should always specify the new column variable type e.g. VARCHAR(100) or anything else you like.
4. To get information about a table e.g. variables and there type in a MySQL table
5. To change some Value in a Column to another one based on another value UPDATE table_name column_name SET column_name=’Lecturer’ WHERE other_column_name=’some_value’; Here; column_name = is your column name other_column_name = is some other column_name which you’re going to search in for a certain some_value content
6. To get a thorougful information about MySQL table, it’s variables and the privileges
SHOW FULL COLUMNS from Table;
Here Table should be your table name.
7. To get information about privileges of some mysql user
SHOW GRANTS FOR your_user@host;
8. To create new user and grant certain privileges to some Database
grant CREATE,INSERT,DELETE,UPDATE,SELECT on database_name.* to username@localhost; set password for username@host = password('mysecretpassword');
Where: database_name = is your desired database username = is your user of choice CREATE,INSERT,DELETE,UPDATE,SELECT = is your preferred privileges to the database_name for the selected username In case if you want to grant all possible user privileges that could be assigned to a table use the following code:
GRANT ALL ON database_name.* TO username identified by 'mysecretpassword' with grant option;
9. Another really helpful few commands on daily basis whever you’re responsible for MySQL server are:
Which is always helpful in debugging in MySQL. And:
That two would inform you about the status of various key variables and could also be a precious debugging tool.