Table Data can be accessed with MySQL SELECT statement. It is one of the DML statements in MySQL. Data can be accessed from one or more tables. You can select only the required columns by listing them in SELECT statement. You can select records from table(s) which satisfy some specific condition given in the WHERE clause. In this tutorial we will just discuss how to select data from a single table. Accessing data from multiple tables requires joining the tables. This will be discussed in later tutorials.
Syntax of MySQL SELECT
SELECT [ALL | DISTINCT ] *|Column Name1 AS alias1,Column Name2 AS alias2,… FROM table name [WHERE condition expression]
- SELECT, AS, All, DISTINCT, FROM and WHERE are the keywords used in MySQL SELECT statement. ALL keyword will display duplicate records and DISTINCT keyword will display only distinct rows if duplicate rows are stored in the table.
- * is used to display all columns of the table.
- Column Name1, Column Name2,… – are the columns whose values you need in the output of SELECT statement. Columns names can be given in any order. Data will be presented in the sequence of columns specified in the MySQL Select statement overriding the sequence of columns in a table. AS keyword followed by an alias name is used to defined different heading names of output other than table column names.
- table name is the name of the table to access data from.
- Condition expression in the WHERE is the filter to chose specific rows.
Examples MySQL Select Statement
SELECT * FROM artists;
Some Specific Columns
SELECT artist_name, first_album, country FROM artists;
Column Aliases for defining Column Headings in Output
You can give different column header or alias in the output of MySQL Select statement. AS keyword is used to specify an alias for a column. You can give a multiple word alias by enclosing it in quotes after a column name in the SELECT Statement. Single word aliases can be given without quotes.
SELECT artist_name AS 'Name of the artist', first_album AS 'First Album Name', country AS Country FROM artists;
Filter Records with Where Clause
SELECT * FROM artists WHERE country='USA';
Points to remember
To select data using MySQL SELECT for a table the following things must be kept in mind.
- Selection operation does alter or remove data from table(s). MySQL Select statement just accesses the data and displays it as a reply to access request made to the database server.
- Alias does not change column name(s) in table definition. It just changes the heading of the data column in display of output to make data understandable if table column names are not descriptive.
- You can filter data on multiple conditions. For this WHERE clause can have multiple conditions combined with Logical Operators AND, OR and NOT.
Read more about MySQL SELECT statement here.