Tag Archives: Functions

SQL

SQL DATE_SUB()

SQL DATE_SUB() function subtracts a specified time interval from a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_SUB(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_SUB(date, INTERVAL 4 DAY) |
+——————————–+
| 20007-05-12 00:00:00            |
+——————————–+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL

SQL DATE_ADD()

SQL DATE_ADD() function adds a specified time interval to a date.

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE_ADD(date, INTERVAL 4 DAY) FROM orders;
+——————————–+
| DATE_ADD(date, INTERVAL 4 DAY) |
+——————————–+
| 2007-05-20 00:00:00            |
+——————————–+
1 row in set (0.01 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL

SQL EXTRACT()

SQL EXTRACT() return a single part of a date/time(likes year, month, day, hour, minute)

Table – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT EXTRACT(YEAR FROM date) FROM orders;
+————————-+
| EXTRACT(YEAR FROM date) |
+————————-+
|                    2007 |
+————————-+
1 row in set (0.00 sec)

Unit

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR

SQL

SQL DATE()

SQL DATE() function extracts the date part of a date.

Table  – orders

+—–+—–+——-+———————+
| oid | uid | items | date                |
+—–+—–+——-+———————+
|   1 |   1 | AAAA  | 2007-05-16 00:00:00 |
+—–+—–+——-+———————+

Example

mysql> SELECT DATE(date) as bill_date from orders ;
+————+
| bill_date  |
+————+
| 2007-05-16 |
+————+
1 row in set (0.00 sec)

SQL

MySQL Date Functions

SQL support many functions with which we can manage the dates efectively

Below given are the date functions is MySql

  • NOW()- Returns the current date and time
  • CURDATE()- Returns the current date
  • CURTIME()- Returns the current time
  • DATE()- Extracts the date part of a date or date/time expression
  • EXTRACT()- Returns a single part of a date/time
  • DATE_ADD()- Adds a specified time interval to a date
  • DATE_SUB() -Subtracts a specified time interval from a date
  • DATEDIFF()- Returns the number of days between two dates
  • DATE_FORMAT()- Displays date/time data in different formats
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.