What is MySQL SELECT Statement?
The MySQL SELECT statement in the SQL is used to select data from a database. The data returned is stored in a result table, called the result set.
Before proceeding further let’s create a Students table that has 5 columns as follows.
We can use MySQL workbench or toolkit to create the table.
Query to create a table:
CREATE TABLE `test_schema1`.`students` (
`roll_num` INT NOT NULL,
`name` VARCHAR(45) NULL,
`marks` INT NULL,
`class` INT NULL,
`gender` VARCHAR(1) NULL,
PRIMARY KEY (`RollNum`));
Once the table is created we will add some dummy data to the table as follows.
roll_num | name | marks | class | gender |
1 | KMS | 465 | 4 | M |
2 | MYG | 489 | 2 | F |
3 | BBH | 390 | 1 | F |
4 | DKS | 492 | 4 | M |
5 | PCY | 354 | 1 | M |
6 | KJN | 343 | 3 | F |
7 | ZYX | 450 | 3 | M |
8 | LPQ | 230 | 5 | F |
9 | KMS | 340 | 2 | M |
10 | OSH | 250 | 5 | M |
11 | KNJ | 380 | 3 | F |
12 | KTV | 400 | 4 | F |
13 | JJK | 372 | 4 | M |
14 | JJH | 230 | 3 | F |
15 | KJM | 342 | 2 | M |
16 | PJM | 283 | 3 | F |
Now we will See how we can use the select statement
Syntax:
SELECT column_name1, column_name2, ..., column_name_n FROM schema_name.table_name;
Note: if we have the schema selected then we can exclude the schema name.
- Select all columns:
select * from test_schema1.students;
This will return all the data present in the table with all the columns selected.
- Select some columns:
select roll_num, name, marks from students;
This will return only selected columns only with all the data returned as shown below
- SELECT DISTINCT Statement:
To get the distinct values from the lists we can use the select distinct statement as follows.
select distinct class from students;
This will select the distinct values for the particular column or group of columns from the table as shown below.
- Count the number of values.
SELECT COUNT(roll_num) FROM students;
This will return the count of students in the table.
If we want to count distinct classes from the table we can use the following query:
SELECT COUNT(distinct class) FROM students;