Category Archives: SQL

SQL

SQL UPDATE

It can be used to modify the data .

SQL UPDATE Syntax

UPDATE table_name SET column1=value WHERE some_column=some_value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | AS   |
+—-+——+

Example

mysql> UPDATE users SET name=’ASP’ WHERE id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from users  ;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | ASP  |
+—-+——+
5 rows in set (0.00 sec)

SQL

SQL INSERT INTO

It insert a new row in a table

SQL INSERT INTO Syntax

It can be used in two form

1) Without mentioning column names . In that case we must enter values for all fields .

INSERT INTO table_name VALUES (value1, value2.)

2) By mentioning column names and values

INSERT INTO table_name (column1, column2) VALUES (value1, value2)

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> INSERT INTO users values(5,”AS”);
Query OK, 1 row affected (0.02 sec)

mysql> select * from users  ;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | AS   |
+—-+——+
5 rows in set (0.00 sec)

SQL

SQL ORDER BY

It sort the result-set by a specified column and by default it sort in ascending order

SQL ORDER BY Syntax

SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DES ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users ORDER BY name ;
+—-+——+
| id | name |
+—-+——+
|  3 | HTML |
|  2 | JS   |
|  1 | PHP  |
|  4 | PHP  |
+—-+——+
4 rows in set (0.02 sec)

mysql> select * from users ORDER BY name DESC;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  4 | PHP  |
|  2 | JS   |
|  3 | HTML |
+—-+——+
4 rows in set (0.00 sec)

SQL

SQL OR

It select the data if one of the conditions given is true

SQL OR Syntax

SELECT column_name(s) FROM table_name WHERE column1 operator value OR  column1 operator value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users where name=’PHP’ OR  id=4;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  4 | PHP  |
+—-+——+
2 rows in set (0.00 sec)

SQL

SQL AND

It select the data if all the conditions given are true

SQL AND Syntax

SELECT column_name(s) FROM table_name WHERE column1 operator value AND  column1 operator value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users where name=’PHP’ AND id=4;
+—-+——+
| id | name |
+—-+——+
|  4 | PHP  |
+—-+——+
1 row in set (0.00 sec)

SQL

SQL WHERE

It select the data based on the condition given .

SQL WHERE Syntax

SELECT column_name(s) FROM table_name WHERE column_name operator value ;

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select * from users where name=’PHP’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  4 | PHP  |
+—-+——+
2 rows in set (0.00 sec)

SQL

SQL DISTINCT

It returns the DISTINCT values  from a table.

SQL DISTINCT Syntax

SELECT DISTINCT(column_name) FROM table_name

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
+—-+——+

Example

mysql> select distinct(name) from users ;
+——+
| name |
+——+
| PHP  |
| JS   |
| HTML |
+——+
3 rows in set (0.00 sec)

SQL

SQL Statements / Clause / Functions / Operators

  • SQL Select – It selects data from a database
  • SQL Distinct – It selects DISTINCT values from a table.
  • SQL Where -It select the data based on the condition given .
  • SQL Having –  is used with aggregate functions
  • SQL And -It select the data if all the conditions given are true
  • SQL OR -It select the data if one of the conditions given is true
  • SQL Order By -It sort the result-set by a specified column
  • SQL Insert -It insert a new row in a table
  • SQL Update -It can be used to modify the data .
  • SQL Delete -It delete rows in a table.
  • SQL Limit – We can use this to specify the numbers of record sets to return
  • SQL Like – It searchs for a specified pattern in a column
  • SQL Wildcards – SQL wildcards can substitute for one or more characters
  • SQL In – SQL IN can be used to to have multiple values with WHERE clause
  • SQL Between – SQL BETWEEN can be used to select data between two values given .
  • SQL Alias – SQL AS  can be used to give alias name to a table or a column .
  • SQL Joins – SQL JOIN can be used to fetch data from more than one tables
  • SQL Inner Join – return rows when there is at least one match in both tables
  • SQL Left Join –   returns all rows from the left table even if there are no matches in the right table.
  • SQL Right Join –   returns all the rows from the right table even if there are no matches in the left table
  • SQL Full Join –  return rows when there is a match in one of the tables
  • SQL Union – Itcan be  used to combine the result  two or more SELECT statements
  • SQL Select Into – selects data from one table and inserts it into a different table
  • SQL Create DB – It is used to create a database
  • SQL Create Table – It is used to create a table.
  • SQL Constraints – Constraints are used to limit the type of data that can go into a tabl
  • SQL Not Null – It  make sure that a column does not accept null values
  • SQL Unique – uniquely identifies each record in a database table.
  • SQL Primary Key –  uniquely identifies each record in a database table.
  • SQL Foreign Key – SQL FOREIGN KEYin one table points to the primary key of another tabl
  • SQL Check – It   is used to limit the value range that can be placed in a column
  • SQL Default – It  is used to insert a default value into a column.
  • SQL Create Index – It is used to create indexes in tables
  • SQL Drop –  can be used to delete table,indexes and databases
  • SQL Truncate – can be used to delete records in table
  • SQL Alter – It  is used to add, delete, or modify columns in a table.
  • SQL Auto Increment – allows a unique number to be generated when a new record is inserted into a table
  • SQL Views – A view is a virtual table based on the result-set of an SQL statement
  • SQL NOW()– Returns the current date and time
  • SQL CURDATE()– Returns the current date
  • SQL CURTIME()– Returns the current time
  • SQL DATE()– Extracts the date part of a date or date/time expression
  • SQL EXTRACT()- Returns a single part of a date/time
  • SQL DATE_ADD()– Adds a specified time interval to a date
  • SQL DATE_SUB() -Subtracts a specified time interval from a date
  • SQL DATEDIFF()– Returns the number of days between two dates
  • SQL DATE_FORMAT()– Displays date/time data in different formats
  • SQL Nulls –  represent missing unknown data
  • SQL NULL Functions – We can use these function to manage the null values
  • SQL Data Types
  • SQL Functions
  • SQL avg() – returns the average value of a numeric column
  • SQL count() –  returns the number of rows
  • SQL first() –  returns the first value of the selected column.
  • SQL last() –  returns the last value of the selected column.
  • SQL max() – returns the largest value of the selected column.
  • SQL min() –  returns the smallest value of the selected column.
  • SQL sum() – returns the total sum of a numeric column.
  • SQL Group By – should be added with all aggregate functions
  • SQL ucase() –   converts the value of a field to uppercase.
  • SQL lcase() –  converts the value of a field to lowercase.
  • SQL mid() –  used to extract characters from a text field
  • SQL len() – returns the length of the value in a text field.
  • SQL round() –  used to round a numeric field to the number of decimals specified.
SQL

SQL

SQL referred to as Structured Query Language) is a special-purpose programming language designed for managing data in relational database management systems (RDBMS).
Originally based upon relational algebra and tuple relational calculus its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

SQL can be divided into two parts: The Data Manipulation Language (DML) and the Data Definition Language (DDL).

The query and update commands form the DML part of SQL:

SELECT – extracts data from a database
UPDATE – updates data in a database
DELETE – deletes data from a database
INSERT INTO – inserts new data into a database

The DDL part of SQL permits database tables to be created or deleted. It also defines indexes (keys), specifies links between tables, and imposes constraints between tables. The most important DDL statements in SQL are:

CREATE DATABASE – creates a new database
ALTER DATABASE – modifies a database
CREATE TABLE – creates a new table
ALTER TABLE – modifies a table
DROP TABLE – deletes a table
CREATE INDEX – creates an index (search key)
DROP INDEX – deletes an index