Tag Archives: Query

SQL

SQL DELETE

It delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name WHERE some_column=some_value

MySQL Table – users

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

Example

mysql> DELETE FROM users WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

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

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)

PHP

SQL SELECT

The SELECT statement is used to select data from a database.

SQL SELECT Syntax

SELECT field_names FROM table_name

Field name should be seperated with comma .

If you want the data from all the fields ,then just use “ * “

MySQL Table – users

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

Example

mysql> select * from users;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | PHP  |
|  3 | PHP  |
|  4 | PHP  |
|  5 | PHP  |
+—-+——+

mysql> select name  from users;
+——+
| name |
+——+
| PHP  |
| JS   |
| HTML |
| JSP  |
| ASP  |
+——+

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.