Posts Tagged ‘column name’

Few MySQL helpful commands in MySQL maintenance (MySQL rename Table, Empty MySQL Table Contents / Null Table records, Get info about variables in a Table, Change record in existing MySQL table, Get MySQL table privileges info, Some basic commands for MySQL issues debugging)

Monday, March 29th, 2010

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 DESCRIBE table_name;

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:
SHOW warnings;
Which is always helpful in debugging in MySQL. And: SHOW status; SHOW processlist;
That two would inform you about the status of various key variables and could also be a precious debugging tool.