Tables are the very important parts of a MySQL Database and one must know the commands of handling tables and records in it to operate a MySQL database on a Web Hosting server. In this post I have mentioned many MySQL commands which are related to table with their short descriptions.
mysql> SELECT * FROM [table name];
This is a very basic command which a users learns at the start of learning MySQL. It is used for displaying all the records of a table.
mysql> show columns from [table name];
This command returns the columns and other information about them related to given table name.
mysql> SELECT * FROM [table name] WHERE [field name] = “0999”;
This command returns the certain selected rows which contains value “0999” as the field name.
mysql> SELECT * FROM [table name] WHERE name = “abc” AND number = ‘1234567’;
This command returns all records after checking the given conditions for two fields.
mysql> SELECT * FROM [table name] WHERE name != “abc” AND number = ‘1234567’ order by phone_number;
Returns all records which do match with the given conditions.
mysql> SELECT * FROM [table name] WHERE name like “abc%” AND number = ‘123’;
Returns all records which starts with particular letters ‘abc’ AND the number ‘123’, its useful to be used a find with uncertainty of the records.
mysql> SELECT * FROM [table name] WHERE name like “abc%” AND phone_number = ‘1234567’ limit 1,9;
Very similar to the previous command but just applying the limitation on the display of number of records.
mysql> SELECT * FROM [table name] WHERE rec RLIKE “^a”;
With the use of this command you can extract all the records starting with smaller case ‘a’ that means you can search the records starting with a particular letter being conditional to case sensitive.
mysql> SELECT DISTINCT [column name] FROM [table name];
By using this command you can find records which are unique.
mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;
This command is used to display the records from particular fields and sort then in the required order according to given field, i.e. with the above query you can find the records of column 1 and column 2 and they will be displayed in descending order as per the records of column 2.
mysql> SELECT COUNT(*) FROM [table name];
This command returns the number of rows (records) in a table.
mysql> SELECT SUM(*) FROM [table name];
You can perform the sum of records of columns of a table.