This is the very basic operation of every database system. In fact this operation is the reason for creating the database.
MySQL Select refers to:
Select the database to work on.
Fetch the data / information from some table.
Select the parameters of MySQL server like date, version etc.
Select the variables in user defined functions / Procedures.
Selecting the Database
After database creation we have to select the database to create tables in it. Every operation that is performed after the database selection affects the only selected database unless you mention database name before the table name in query. Database selection can be done in two ways:
- USE database_name ;
- \u database_name ;
Fig: showing use database command.
Survey is the database name in above example.
NOTE: In the first command semicolon adding (;) does not affect the statement, means “use databasename;”and “use databasename”are the same. But in second command semicolon gives an error (ERROR 1049 (42000): Unknown database 'survey;'), means just use “\u databasename”.
Selecting the data from tables
Selecting the data means fetching information from the table. We can use select command to fetch the data in following ways:
- All the data of the table
- Particular rows or tuples based on a condition.
- Particular columns or fields from the table.
- Fetch data from more than one table using Joins and unions.
Here are some examples of select statement:
SELECT * FROM agent limit 5;
Here agent is the table name and limit is condition which means only first 5 rows are returned. To fetch all the data we have to specify it by a star (*) in the select statement.
SELECT `Name of Agent`,`Phone Number` FROM agent limit 5;
Here selected fields (Name and phone number) are fetched.
SELECT results.company, results.product, policies_my.STATUS FROM results INNER JOIN policies_my ON results.policy_num = policies_my.policy_num
Here the data are fetched from two tables named results and policies_my using JOIN on the basis of common field policy_num.
Selecting the MySQL server parameters
We can select the parameters like current date time, version, variables etc. of MySQL by the select statement. We can also use algebraic functions in the select statements. For example:
- Select now();
- Select CURDATE();
- SELECT VERSION();
- SELECT ‘22 + 32’;
Here the output of above commands: