Commands in MySQL


Share

MySQL is a collection of databases and MySQL use various commands to create a database(tables).
MySQL database and tables, populated them with data, and added indexes to make them fast to search. Now it’s time to look at how these searches are performed, and the various commands and qualifiers available.


SELECT COMMAND


The SELECT command is used to extract data from a table

Synatx of select command
If you want display all column from table you can use *.

SELECT * FROM table_name;

If you want display select column. SELECT col1, col2,......FROM table_name;



Example:-

  SELECT name, age FROM students;

SELECT COUNT COMMAND

Another option for the something parameter is COUNT, which can be used in many ways.
It displays the number of rows in the table by passing * as a parameter, which means “all rows.”

Example:-

 SELECT COUNT(*) FROM students;

SELECT DISTINCT COMMAND

This qualifier (and its synonym DISTINCTROW) allows you to weed out multiple entries when they contain the same data.


Example:-
With & without DISTINCT qualifier-

SELECT age FROM students;

SELECT DISTINCT age FROM students;

DELETE COMMAND


When you need to remove a row from a table, use the DELETE command. Its syntax is similar to the SELECT command and allows you to narrow down the exact row or rows to delete using qualifiers such as WHERE and LIMIT.


Example:-

DELETE FROM students WHERE age='21';

WHERE COMMAND


The WHERE keyword enables you to narrow down queries by returning only those where a certain expression is true.


Example:-

SELECT name, age FROM students WHERE address="Uttarakhand";


You can also do pattern matching for your searches using the LIKE qualifier,which allows searches on parts of strings.

This qualifier should be used with a % character before or after some text. When placed before a keyword % means “anything before,” and after a keyword it means “anything after.”


Using the LIKE qualifier:-

SELECT name, age, address FROM students WHERE name LIKE "A%";

LIMIT


The LIMIT qualifier enables you to choose how many rows to return in a query, and where in the table to start returning them.

When passed a single parameter, it tells MySQL to start at the beginning of the results and just return the number of rows given in that parameter.

If you pass it two parameters, the first indicates the offset from the start of the results where MySQL should start the display, and the second indicates how many to return.


Example:-

SELECT name, age FROM students LIMIT 3;

UPDATE COMMAND

Update command is used to update column in atable in other word Update command used to modify the existing data in a table.

Syntax:-

UPDATE table_name 
SET col1=val1,val2...WHERE col;


Example:-

UPDATE students SET.address='UP' WHERE id= 2;

ORDER BY

ORDER BY sorts returned results by one or more columns, in ascending or descending order.


Example:-

SELECT name,age FROM students ORDER BY name; 

SELECT name,age FROM students ORDER BY name;

GROUP BY

In a similar fashion to ORDER BY, you can group results returned from queries using GROUP BY, which is good for retrieving information about a group of data .


Example:-

SELECT name,COUNT(age) FROM students GROUP BY name;