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.
More helpful Articles
Tags: column name, database name, grants, mysql table, Privileges, table contents, truncate table, variable type, variables
Mozilla/5.0 (Windows NT 6.1; WOW64; rv:28.0) Gecko/20100101 Firefox/28.0
Here is more from Anatoliy's Mysql Cheatsheet
- Add a column column_name to table table_name
- > alter table table_name add column_name column_type
- Modify column
- > alter table table_name modify column_name int unsigned NOT NULL auto_increment; > alter table comments change date date datetime;
- Delete column
- > alter table table_name drop column_name;
- Rename a column
- > alter table table_name change column_old_name column_new_name tinyint(3) unsigned;
- Add column as key index
- > alter table table_name add key(column_nam
- Set auto_increment for column column_name:
- > alter table table_name add key(column_name); > alter table table_name modify column_name int unsigned NOT NULL auto_increment;
- Set a column as primary key:
- alter table table_name add primary key(column_name);
- Inner join* with cartesian effect elimination
- > select * from table_name1, table_name2 where table_name1.column = table_name2.column; *inner join – only the entries which exist in both tables are included in the resulting table.
- Left (outer) join*
- > select * from table1 left outer join table2 ON table1.column = table2.column; *Left join – includes ALL the table entries in the left tables and then matches entries in the right table if they exist. An INNER JOIN will retrieve all records from both tables that have matching values for whatever column you're joining them on. Records from either table that don't match will not be retrieved. A LEFT JOIN will retrieve all records from the left (first) table, and only the records from the second table with matching values for the joining column, filling in any empty spaces with NULL values.
- Group by – sampling of results and getting the first result out of several repeating.
- DISTINCT ensures that only unique column values will be shown.
- WHERE cannot be used with a group column function, like count(). In such cases HAVING should be used. Example:
- > select tc.chapter,tc.chapter_name,count(ct.topic) from table_of_contents as tc LEFT OUTER JOIN chapter_topics as ct ON tc.chapter = ct.chapter GROUP BY tc.chapter HAVING count(ct.topic) >= 2 ;
- Using the ORDER BY clause on the column topic, the results were automatically sorted. SQL's ordering of a column depends on its type, so since topic is of a string type, SQL knew to sort in alphabetical order, rather than numerically or by date.
- LIMIT x[, y] – x represents the desired starting row in your results, y is optional and represents the number of rows to display.
View CommentView Comment