My SQL Tutorial
-
Creating a Table & Inserting Records
-
Creating Table
-
Inserting Records into a Table
-
-
Retrieving Information from a Table
-
Selecting All Data
-
Selecting Particular Rows
-
Selecting Particular Columns
-
Sorting Rows
-
Date Calculations
-
Working with NULL Values
-
Pattern Matching
-
-
Counting Rows
-
Using More Than one Table
-
Getting Information About Databases and Tables
-
Using mysql in Batch Mode
-
Examples of Common Queries .
-
The Maximum Value for a Column
-
The Row Holding the Maximum of a Certain Column
-
Maximum of Column per Group
-
The Rows Holding the Group-wise Maximum of a Certain Column
-
Using User-Defined Variables
-
Using Foreign Keys
-
Searching on Two Keys
-
Calculating Visits Per Day
-
Using AUTO_INCREMENT
-
Using MySQL with Apache
Writing Your First Queries in MySQL
After successful starting of My SQL, the time to learn how to write queries in My SQL. What precautions you have to take care while writing queries? What types of prompts you will get in different condition i.e. when you given the correct SQL commands or given wrong SQL commands.
Checking Version of My SQL
mysql> SELECT VERSION ( );
+-----------+
| VERSION() |
+-----------+
| 8.0.12 |
+-----------+
1 row in set (0.01 sec)
mysql>
Now its time to understands about some basic concepts of My SQL query:
-
A query consists of an SQL statement followed by a semicolon. There are some exceptions where a semicolon may be omitted. like QUIT, USE DBNAME.
-
When you give a query, mysql sends it to the server for execution and displays the results, then prints another mysql> prompt to indicate that it is ready for another query.
-
MySQL displays query output in tabular form (rows and columns). The first row contains labels for the columns. The rows following are the query results. Normally, column labels are the names of the columns you fetch from database tables or expression itself you have written in query.
-
MySQL shows how many rows were returned and how long the query took to execute, which gives you a rough idea of server performance.
-
-
My SQL is not a case sensitive, so you can write Keywords/SQL commands in any lettercase.
Checking Current Date and Time of My SQL
mysql> SELECT CURRENT_DATE;
+--------------+
| CURRENT_DATE |
+--------------+
| 2019-10-20 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT NOW();
+----------------------+
| NOW() |
+----------------------+
| 2019-10-20 22:44:25 |
+----------------------+
1 row in set (0.00 sec)
Checking USER( )
mysql> SELECT USER;
+------------------+
| USER() |
+------------------+
| anjeev@localhost |
+------------------+
1 row in set (0.00 sec)
Writing Queries in different Ways-
Multiple Queries in one line
My SQL allows to write multiple queries in single line or statement, separated by semicolon.
mysql> SELECT USER(); SELECT NOW();
+------------------+
| USER() |
+------------------+
| anjeev@localhost |
+------------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2019-10-20 22:49:32 |
+---------------------+
1 row in set (0.05 sec)
Multi-line statement :
MySQL accepts free-format input. It means that, a lengthy query need not be given all on a single line, can be given in several lines. MySQL determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line.
Here is a simple multiple-line statement:
mysql> SELECT VERSION()
-> ;
+-----------+
| VERSION() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT CURRENT_DATE,
-> NOW(),
-> USER()
-> ,
-> VERSION()
-> ;
+--------------+--------------------+------------------+---------+
| CURRENT_DATE | NOW() | USER() |VERSION()|
+--------------+--------------------+------------------+---------+
| 2019-10-20 | 2019-10-20 22:57:15| anjeev@localhost | 8.0.15 |
+--------------+---------------------+------------------+--------+
1 row in set (0.00 sec)
My SQL as calculator :
You can use your mysql as a calculator also. Like -
mysql> SELECT 4+3, 5-3, 19*3, 43/21;
+-----+-----+------+--------+
| 4+3 | 5-3 | 19*3 | 43/21 |
+-----+-----+------+--------+
| 7 | 2 | 57 | 2.0476 |
+-----+-----+------+--------+
1 row in set (0.09 sec)
mysql> SELECT SQRT(3), POW(2,3), PI();
+--------------------+----------+----------+
| SQRT(3) | POW(2,3) | PI() |
+--------------------+----------+----------+
| 1.7320508075688772 | 8 | 3.141593 |
+--------------------+----------+----------+
1 row in set (0.00 sec)
Different Types of Prompt in mysql -
Prompt Meaning
mysql> Ready for new query
-> Waiting for next line of multiple-line query
'> Waiting for completion of a string that began with a single quote (')
"> Waiting for completion of a string that began with a double quote (")
Cancel a command -
If you decide you do not want to execute a query that you are in the process of entering, cancel it by typing \c (Note 'c' must be in small letter)
mysql> SELECT CURRENT_DATE,
-> VERSION()
-> \c
mysql>