MySQL Select Command


The SQL SELECT command is used to retrive data from the MySQL database. You can use this command at mysql> prompt as well as in any script like Python or PHP.

Syntax

Here is generic SQL syntax of the SELECT command to fetch data from the MySQL table −

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
[ORDER BY field1, field2 <ASC/DESC>]
[OFFSET M ][LIMIT N]
  • You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
  • You can fetch one or more fields in a single SELECT command.
  • You can specify star (*) in place of fields. In this case, SELECT will return all the fields.
  • You can specify any condition using the WHERE clause.
  • You can specify an offset using OFFSET from where SELECT will start returning records. By default, the offset starts at zero.
  • You can limit the number of returns using the LIMIT attribute.

Fetching Data from a Command Prompt

This will use SQL SELECT command to fetch data from the MySQL table student.

Example

The following example will return all the records from the student table −

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from student;

+-------+--------+-------------+--------------------+
| admno | name   | father_name | address            |
+-------+--------+-------------+--------------------+
|   101 | rakesh | jagdish     | c-100 brij vihar   |
|   102 | udit   | ramesh sono | B-200 surya nagar  |
|   103 | mohit  | suresh sono | DB-240 surya nagar |
+-------+--------+-------------+--------------------+
3 rows in set (0.00 sec)

mysql> select admno,name,address from student;
+-------+--------+--------------------+
| admno | name   | address            |
+-------+--------+--------------------+
|   101 | rakesh | c-100 brij vihar   |
|   102 | udit   | B-200 surya nagar  |
|   103 | mohit  | DB-240 surya nagar |
+-------+--------+--------------------+
3 rows in set (0.00 sec)

The above Example only fetches the supplied column(s) as we have not defined any condition using the WHERE clause thus all the rows from the table are appearing in the output.

Fetching Data from using Python Script

Python script can also be used to retrieve the data from the database table. Here we will use the same SELECT command to retrieve all the information. cursor.execute( ) method is used to run the SQL query that fetch the records and all these records are then stored in a list using fetchall( ) method.

from prettytable import PrettyTable
import mysql.connector
conn = mysql.connector.connect(host='localhost',user='root',password='',database="school")
cursor = conn.cursor()
cursor.execute("select admno,name,father_name,address from student")
results= cursor.fetchall()
t = PrettyTable(['admno', 'Student Name', 'Father Name', 'Address'])
for admno ,name ,fname ,addr in results:
    t.add_row([admno,name,fname,addr])
    #print(admno,name,fname,addr)
print(t)
conn.close()

rakesh@DESKTOP-1PBLCJ5 MINGW64 /c/python (master)
$ python -u "c:\Users\rakesh\Desktop\database_connection.py"
+-------+--------------+-------------+--------------------+
| admno | Student Name | Father Name |      Address       |
+-------+--------------+-------------+--------------------+
|  101  |    rakesh    |   jagdish   |  c-100 brij vihar  |
|  102  |     udit     | ramesh sono | B-200 surya nagar  |
|  103  |    mohit     | suresh sono | DB-240 surya nagar |
+-------+--------------+-------------+--------------------+

PrettyTable module has been used here to generate this table like the output.

t = PrettyTable(['admno', 'Student Name', 'Father Name', 'Address'])

command is used to design the heading of the output table.
While fetching data, you can write as complex a code as you like, but the procedure will remain the same as mentioned above.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.