CREATE Table
The PostgreSQL CREATE TABLE statement is used to create a new table in any of the given database.
CREATE TABLE
employe_data(
ID INT PRIMARY
KEY NOT NULL,
NAME
TEXT NOT NULL,
AGE
INT NOT NULL,
ADDRESS
CHAR(50),
SALARY
REAL
);
CREATE TABLE
DEPARTMENT(
ID INT PRIMARY
KEY NOT NULL,
DEPT
CHAR(50) NOT NULL,
EMP_ID
INT NOT NULL
);
INSERT INTO
employe_data (ID,NAME,AGE,ADDRESS,SALARY)
VALUES
(100,'harish',32,'bagalore',20000.00)
(101,'swami',35,'bagalore',30000.00),
(102,'naveen',35,'mysore',25000.00),
(103,'vinay',34,'mysore',38000.00),
(104,'manju',30,'mandya',40000.00),
(105,'naveen',30,'ballari',30000.00),
(106,'arun',35,'mysore',40000.00),
(107,'anu',27,'mangalore',38000.00),
(108,'manu',27,'mandya',40000.00),
(109,'nakul',26,'hasan',26000.00),
(110,'avi',34,'tumkur',32000.00),
(111,'kishor',27,'mangalore',38000.00),
(112,'manoj',27,'mandya',40000.00);
insert into
department(id,dept,emp_id) values
(1, 'IT Billing',
102),
(2, 'Finance', 105),
(3, 'Engineering',
115),
(4,'testing', 108);
select * from
employe_data
select * from
department;
CREATE VIEW
CREATE VIEW
employe_VIEW AS
SELECT ID, NAME, AGE
FROM employe_data;
SELECT * FROM
employe_VIEW;
CREATE VIEW WITH CROSS JOIN
A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to only use them when appropriate.
CREATE VIEW
employe_VIEW3 AS
SELECT name,
age,dept FROM employe_data CROSS JOIN department;
insert into
employe_view(id, name, age)
values(122, 'ravi',
32);
select * from
employe_view; select * from employe_data;
Aggregate function
An aggregate function produced a single result for an entire group or table.Aggregate functions are used to produce summarized results. They operate on sets of rows. They return results based on groups of rows. By default, all rows in a table are treated as one group. The GROUP BY clause of the select statement is used to divide rows into smaller groups. List of aggregate functions
|
Name |
Description |
|---|---|
|
COUNT |
This function returns the number or rows or non NULL values for
a column |
|
SUM |
This function returns the sum of a selected column. |
|
MAX |
This function returns the largest value of a specific column.
|
|
MIN |
This function returns the smallest value of a specific column.
|
|
AVG |
This function returns the average value for a specific column.
|
COUNT function
The PostgreSQL COUNT function counts a number of rows or non-NULL values against a specific column from a table. When an asterisk(*) is used with count function the total number of rows returns.SELECT COUNT(*) FROM employe_data;
count
specific column : SELECT COUNT(salary) FROM employe_data;
count
distinct: SELECT COUNT ( DISTINCT name) FROM
employe_data;
COUNT with GROUP BY: SELECT age,COUNT(*) AS "Number of employees" FROM employe_data GROUP BY age;
COUNT with WHERE CLAUSE :
If we want to get the number of employees age in employees table who draws the monthly salary below 40000, the following SQL can be used.
SELECT
age,COUNT(*) AS "Number of employees"
FROM
employe_data
WHERE
salary<40000 group by;
COUNT with HAVING clause:
If
we want to get those age, where at least 3 employees and draw a
monthly salary below 40000, the following SQL can be used.
SELECT age,COUNT(*)
AS "Number of employees"
FROM employe_data
WHERE salary<45000
GROUP BY age
HAVING COUNT(*)>=3;
COUNT
with GROUP BY and ORDER BY:
SELECT name,COUNT(*) AS "Number of employees"
FROM employe_data
WHERE salary<45000
GROUP BY name
HAVING COUNT(*)>=1
ORDER BY COUNT(*) DESC;
Find the no. of
employees from each dept ?
SELECT dept,COUNT(*)
AS "Number of employees"
FROM department
group by dept ;
Display dept
name,highest salary from each dept?
select dept,
max(salary)
from employe_data
e,department d
group by dept
Second highest
salary:
select
max(salary) from employe_data
where salary<
(select max(salary) from employe_data)
or
select max(salary)
from employe_data
where salary not
in(select max(salary) from employe_data)
Third
highest salary:
select salary, dept
from employe_data a, department
where 3=(select
count(distinct(salary))
from employe_data b
where
a.salary<=b.salary)
order by a.salary
or
select salary from
employe_data a, department
where 3=(select
count(distinct(salary))
from employe_data b
where
a.salary<=b.salary)
order by a.salary
Dept name, no. of
employees from each dept whose salary greater than 30000 ?
SELECT dept,COUNT(*) AS "Number of employees"
FROM department, employe_data
WHERE salary<30000 group by dept
Display name of
the employee whose first name is duplicate.?
SELECT name,Address,age,salary
FROM employe_data
GROUP BY Name,Address,age,salary
HAVING COUNT(*) > 1;
SQL - Date Functions
|
Name |
Description |
|---|---|
|
ADDDATE() |
Adds dates |
|
ADDTIME() |
Adds time |
|
CONVERT_TZ() |
Converts from one timezone to another |
|
CURDATE() |
Returns the current date |
|
CURRENT_DATE(),
CURRENT_DATE |
Synonyms for CURDATE() |
|
CURRENT_TIME(),
CURRENT_TIME |
Synonyms for CURTIME() |
|
CURRENT_TIMESTAMP(),
CURRENT_TIMESTAMP |
Synonyms for NOW() |
|
CURTIME() |
Returns the current time |
|
DATE_ADD() |
Adds two dates |
|
DATE_FORMAT() |
Formats date as specified |
|
DATE_SUB() |
Subtracts two dates |
|
DATE() |
Extracts the date part of a date or datetime expression |
|
DATEDIFF() |
Subtracts two dates |
|
DAY() |
Synonym for DAYOFMONTH() |
|
DAYNAME() |
Returns the name of the weekday |
|
DAYOFMONTH() |
Returns the day of the month (1-31) |
|
DAYOFWEEK() |
Returns the weekday index of the argument |
|
DAYOFYEAR() |
Returns the day of the year (1-366) |
|
EXTRACT |
Extracts part of a date |
|
FROM_DAYS() |
Converts a day number to a date |
|
FROM_UNIXTIME() |
Formats date as a UNIX timestamp |
|
HOUR() |
Extracts the hour |
|
LAST_DAY |
Returns the last day of the month for the argument |
|
LOCALTIME(),
LOCALTIME |
Synonym for NOW() |
|
LOCALTIMESTAMP,
LOCALTIMESTAMP() |
Synonym for NOW() |
|
MAKEDATE() |
Creates a date from the year and day of year |
|
MAKETIME |
MAKETIME() |
|
MICROSECOND() |
Returns the microseconds from argument |
|
MINUTE() |
Returns the minute from the argument |
|
MONTH() |
Return the month from the date passed |
|
MONTHNAME() |
Returns the name of the month |
|
NOW() |
Returns the current date and time |
|
PERIOD_ADD() |
Adds a period to a year-month |
|
PERIOD_DIFF() |
Returns the number of months between periods |
|
QUARTER() |
Returns the quarter from a date argument |
|
SEC_TO_TIME() |
Converts seconds to 'HH:MM:SS' format |
|
SECOND() |
Returns the second (0-59) |
|
STR_TO_DATE() |
Converts a string to a date |
|
SUBDATE() |
When invoked with three arguments a synonym for DATE_SUB() |
|
SUBTIME() |
Subtracts times |
|
SYSDATE() |
Returns the time at which the function executes |
|
TIME_FORMAT() |
Formats as time |
|
TIME_TO_SEC() |
Returns the argument converted to seconds |
|
TIME() |
Extracts the time portion of the expression passed |
|
TIMEDIFF() |
Subtracts time |
|
TIMESTAMP() |
With a single argument, this function returns the date or
datetime expression. With two arguments, the sum of the arguments |
|
TIMESTAMPADD() |
Adds an interval to a datetime expression |
|
TIMESTAMPDIFF() |
Subtracts an interval from a datetime expression |
|
TO_DAYS() |
Returns the date argument converted to days |
|
UNIX_TIMESTAMP() |
Returns a UNIX timestamp |
|
UTC_DATE() |
Returns the current UTC date |
|
UTC_TIME() |
Returns the current UTC time |
|
UTC_TIMESTAMP() |
Returns the current UTC date and time |
|
WEEK() |
Returns the week number |
|
WEEKDAY() |
Returns the weekday index |
|
WEEKOFYEAR() |
Returns the calendar week of the date (1-53) |
|
YEAR() |
Returns the year |
|
YEARWEEK() |
Returns the year and week |
ADDDATE (date,INTERVAL expr unit), ADDDATE(expr,days)
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
When invoked with the days form of the
second argument, MySQL treats it as an integer number of days to be
added to expr.
mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
ADDTIME(expr1,expr2)
ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CONVERT_TZ(dt,from_tz,to_tz)
This converts a datetime value dt from the time zone given by from_tz to the time zone given by to_tz and returns the resulting value. This function returns NULL if the arguments are invalid.mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+---------------------------------------------------------+
| 2004-01-01 13:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+---------------------------------------------------------+
| 2004-01-01 22:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CURDATE()
Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.mysql> SELECT CURDATE(); +---------------------------------------------------------+ | CURDATE() | +---------------------------------------------------------+ | 1997-12-15 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CURDATE() + 0; +---------------------------------------------------------+ | CURDATE() + 0 | +---------------------------------------------------------+ | 19971215 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
CURRENT_DATE and CURRENT_DATE()
CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE()CURTIME()
Returns the current time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.mysql> SELECT CURTIME(); +---------------------------------------------------------+ | CURTIME() | +---------------------------------------------------------+ | 23:50:26 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT CURTIME() + 0; +---------------------------------------------------------+ | CURTIME() + 0 | +---------------------------------------------------------+ | 235026 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
CURRENT_TIME and CURRENT_TIME()
CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP() are synonyms for NOW().DATE(expr)
Extracts the date part of the date or datetime expression expr.mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 2003-12-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATEDIFF(expr1,expr2)
DATEDIFF() returns expr1 . expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
These functions perform date arithmetic. date is a DATETIME or DATE value specifying the starting date. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a '-' for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted.The INTERVAL keyword and the unit specifier are not case sensitive.
The following table shows the expected form of the expr argument for each unit value;
|
unit Value |
ExpectedexprFormat
|
|
MICROSECOND |
MICROSECONDS |
|
SECOND |
SECONDS |
|
MINUTE |
MINUTES |
|
HOUR |
HOURS |
|
DAY |
DAYS |
|
WEEK |
WEEKS |
|
MONTH |
MONTHS |
|
QUARTER |
QUARTERS |
|
YEAR |
YEARS |
|
SECOND_MICROSECOND |
'SECONDS.MICROSECONDS' |
|
MINUTE_MICROSECOND |
'MINUTES.MICROSECONDS' |
|
MINUTE_SECOND |
'MINUTES:SECONDS' |
|
HOUR_MICROSECOND |
'HOURS.MICROSECONDS' |
|
HOUR_SECOND |
'HOURS:MINUTES:SECONDS' |
|
HOUR_MINUTE |
'HOURS:MINUTES' |
|
DAY_MICROSECOND |
'DAYS.MICROSECONDS' |
|
DAY_SECOND |
'DAYS HOURS:MINUTES:SECONDS' |
|
DAY_MINUTE |
'DAYS HOURS:MINUTES' |
|
DAY_HOUR |
'DAYS HOURS' |
|
YEAR_MONTH |
'YEARS-MONTHS' |
The values QUARTER and WEEK are available beginning with MySQL 5.0.0.
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL... |
+---------------------------------------------------------+
| 1998-01-01 00:01:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR) |
+---------------------------------------------------------+
| 1999-01-01 01:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE_FORMAT(date,format)
Formats the date value according to the format string.The following specifiers may be used in the format string. The '%' character is required before format specifier characters.
|
Specifier |
Description |
|
%a |
Abbreviated weekday name (Sun..Sat) |
|
%b |
Abbreviated month name (Jan..Dec) |
|
%c |
Month, numeric (0..12) |
|
%D |
Day of the month with English suffix (0th, 1st, 2nd, 3rd, .) |
|
%d |
Day of the month, numeric (00..31) |
|
%e |
Day of the month, numeric (0..31) |
|
%f |
Microseconds (000000..999999) |
|
%H |
Hour (00..23) |
|
%h |
Hour (01..12) |
|
%I |
Hour (01..12) |
|
%i |
Minutes, numeric (00..59) |
|
%j |
Day of year (001..366) |
|
%k |
Hour (0..23) |
|
%l |
Hour (1..12) |
|
%M |
Month name (January..December) |
|
%m |
Month, numeric (00..12) |
|
%p |
AM or PM
|
|
%r |
Time, 12-hour (hh:mm:ss followed by AM or PM) |
|
%S |
Seconds (00..59) |
|
%s |
Seconds (00..59) |
|
%T |
Time, 24-hour (hh:mm:ss) |
|
%U |
Week (00..53), where Sunday is the first day of the week |
|
%u |
Week (00..53), where Monday is the first day of the week |
|
%V |
Week (01..53), where Sunday is the first day of the week; used
with %X
|
|
%v |
Week (01..53), where Monday is the first day of the week; used
with %x
|
|
%W |
Weekday name (Sunday..Saturday) |
|
%w |
Day of the week (0=Sunday..6=Saturday) |
|
%X |
Year for the week where Sunday is the first day of the week,
numeric, four digits; used with %V
|
|
%x |
Year for the week, where Monday is the first day of the week,
numeric, four digits; used with %v
|
|
%Y |
Year, numeric, four digits |
|
%y |
Year, numeric (two digits) |
|
%% |
A literal .%. character |
|
%x |
x, for any.x. not listed above |
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') |
+---------------------------------------------------------+
| Saturday October 1997 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
-> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00....... |
+---------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(date,INTERVAL expr unit)
This is similar to DATE_ADD() function.DAY(date)
DAY() is a synonym for DAYOFMONTH().DAYNAME(date)
Returns the name of the weekday for date.mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05') |
+---------------------------------------------------------+
| Thursday |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAYOFMONTH(date)
Returns the day of the month for date, in the range 0 to 31.mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAYOFWEEK(date)
Returns the weekday index for date (1 = Sunday, 2 = Monday, ., 7 = Saturday). These index values correspond to the ODBC standard.mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DAYOFYEAR(date)
Returns the day of the year for date, in the range 1 to 366.mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03') |
+---------------------------------------------------------+
| 34 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
EXTRACT(unit FROM date)
The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.mysql> SELECT EXTRACT(YEAR FROM '1999-07-02'); +---------------------------------------------------------+ | EXTRACT(YEAR FROM '1999-07-02') | +---------------------------------------------------------+ | 1999 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03'); +---------------------------------------------------------+ | EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03') | +---------------------------------------------------------+ | 199907 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
FROM_DAYS(N)
Given a day number N, returns a DATE value.mysql> SELECT FROM_DAYS(729669); +---------------------------------------------------------+ | FROM_DAYS(729669) | +---------------------------------------------------------+ | 1997-10-07 | +---------------------------------------------------------+ 1 row in set (0.00 sec)Use FROM_DAYS() with caution on old dates. It is not intended for use with values that precede the advent of the Gregorian calendar (1582).
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)
Returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone. unix_timestamp is an internal timestamp value such as is produced by the UNIX_TIMESTAMP() function.If format is given, the result is formatted according to the format string, which is used the same way as listed in the entry for the DATE_FORMAT() function.
mysql> SELECT FROM_UNIXTIME(875996580); +---------------------------------------------------------+ | FROM_UNIXTIME(875996580) | +---------------------------------------------------------+ | 1997-10-04 22:23:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
HOUR(time)
Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values. However, the range of TIME values actually is much larger, so HOUR can return values greater than 23.mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03') |
+---------------------------------------------------------+
| 10 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LAST_DAY(date)
Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.mysql> SELECT LAST_DAY('2003-02-05');
+---------------------------------------------------------+
| LAST_DAY('2003-02-05') |
+---------------------------------------------------------+
| 2003-02-28 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LOCALTIME and LOCALTIME()
LOCALTIME and LOCALTIME() are synonyms for NOW().LOCALTIMESTAMP and LOCALTIMESTAMP()
LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().MAKEDATE(year,dayofyear)
Returns a date, given year and day-of-year values. dayofyear must be greater than 0 or the result is NULL.mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32); +---------------------------------------------------------+ | MAKEDATE(2001,31), MAKEDATE(2001,32) | +---------------------------------------------------------+ | '2001-01-31', '2001-02-01' | +---------------------------------------------------------+ 1 row in set (0.00 sec)MAKETIME(hour,minute,second)Returns a time value calculated from the hour, minute, and second arguments.
mysql> SELECT MAKETIME(12,15,30); +---------------------------------------------------------+ | MAKETIME(12,15,30) | +---------------------------------------------------------+ | '12:15:30' | +---------------------------------------------------------+ 1 row in set (0.00 sec)
MICROSECOND(expr)
Returns the microseconds from the time or datetime expression expr as a number in the range from 0 to 999999.mysql> SELECT MICROSECOND('12:00:00.123456');
+---------------------------------------------------------+
| MICROSECOND('12:00:00.123456') |
+---------------------------------------------------------+
| 123456 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MINUTE(time)
Returns the minute for time, in the range 0 to 59.mysql> SELECT MINUTE('98-02-03 10:05:03');
+---------------------------------------------------------+
| MINUTE('98-02-03 10:05:03') |
+---------------------------------------------------------+
| 5 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MONTH(date)
Returns the month for date, in the range 0 to 12.mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MONTHNAME(date)
Returns the full name of the month for date.mysql> SELECT MONTHNAME('1998-02-05');
+---------------------------------------------------------+
| MONTHNAME('1998-02-05') |
+---------------------------------------------------------+
| February |
+---------------------------------------------------------+
1 row in set (0.00 sec)
NOW()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.mysql> SELECT NOW(); +---------------------------------------------------------+ | NOW() | +---------------------------------------------------------+ | 1997-12-15 23:50:26 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
PERIOD_ADD(P,N)
Adds N months to period P (in the format YYMM or YYYYMM). Returns a value in the format YYYYMM. Note that the period argument P is not a date value.mysql> SELECT PERIOD_ADD(9801,2); +---------------------------------------------------------+ | PERIOD_ADD(9801,2) | +---------------------------------------------------------+ | 199803 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.mysql> SELECT PERIOD_DIFF(9802,199703); +---------------------------------------------------------+ | PERIOD_DIFF(9802,199703) | +---------------------------------------------------------+ | 11 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
QUARTER(date)
Returns the quarter of the year for date, in the range 1 to 4.mysql> SELECT QUARTER('98-04-01');
+---------------------------------------------------------+
| QUARTER('98-04-01') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SECOND(time)
Returns the second for time, in the range 0 to 59.mysql> SELECT SECOND('10:05:03');
+---------------------------------------------------------+
| SECOND('10:05:03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SEC_TO_TIME(seconds)
Returns the seconds argument, converted to hours, minutes and seconds, as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.mysql> SELECT SEC_TO_TIME(2378); +---------------------------------------------------------+ | SEC_TO_TIME(2378) | +---------------------------------------------------------+ | 00:39:38 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
STR_TO_DATE(str,format)
This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts or a DATE or TIME value if the string contains only date or time parts.mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y') |
+---------------------------------------------------------+
| 2004-04-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)
When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBTIME(expr1,expr2)
SUBTIME() returns expr1 . expr2 expressed as a value in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time.mysql> SELECT SUBTIME('1997-12-31 23:59:59.999999',
-> '1 1:1:1.000002');
+---------------------------------------------------------+
| SUBTIME('1997-12-31 23:59:59.999999'... |
+---------------------------------------------------------+
| 1997-12-30 22:58:58.999997 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SYSDATE()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.mysql> SELECT SYSDATE(); +---------------------------------------------------------+ | SYSDATE() | +---------------------------------------------------------+ | 2006-04-12 13:47:44 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
TIME(expr)
Extracts the time part of the time or datetime expression expr and returns it as a string.mysql> SELECT TIME('2003-12-31 01:02:03');
+---------------------------------------------------------+
| TIME('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 01:02:03 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMEDIFF(expr1,expr2)
TIMEDIFF() returns expr1 . expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
-> '1997-12-30 01:01:01.000002');
+---------------------------------------------------------+
| TIMEDIFF('1997-12-31 23:59:59.000001'..... |
+---------------------------------------------------------+
| 46:58:57.999999 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.mysql> SELECT TIMESTAMP('2003-12-31');
+---------------------------------------------------------+
| TIMESTAMP('2003-12-31') |
+---------------------------------------------------------+
| 2003-12-31 00:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMESTAMPADD(unit,interval,datetime_expr)
Adds the integer expression interval to the date or datetime expression datetime_expr. The unit for interval is given by the unit argument, which should be one of the following values: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER or YEAR.The unit value may be specified using one of keywords as shown or with a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02'); +---------------------------------------------------------+ | TIMESTAMPADD(MINUTE,1,'2003-01-02') | +---------------------------------------------------------+ | 2003-01-02 00:01:00 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
Returns the integer difference between the date or datetime expressions datetime_expr1 and datetime_expr2. The unit for the result is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); +---------------------------------------------------------+ | TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') | +---------------------------------------------------------+ | 3 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
TIME_FORMAT(time,format)
This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes and seconds.If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l') |
+---------------------------------------------------------+
| 100 100 04 04 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIME_TO_SEC(time)
Returns the time argument converted to seconds.mysql> SELECT TIME_TO_SEC('22:23:00');
+---------------------------------------------------------+
| TIME_TO_SEC('22:23:00') |
+---------------------------------------------------------+
| 80580 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TO_DAYS(date)
Given a date, returns a day number (the number of days since year 0).mysql> SELECT TO_DAYS(950501); +---------------------------------------------------------+ | TO_DAYS(950501) | +---------------------------------------------------------+ | 728779 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD.mysql> SELECT UNIX_TIMESTAMP();
+---------------------------------------------------------+
| UNIX_TIMESTAMP() |
+---------------------------------------------------------+
| 882226357 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00') |
+---------------------------------------------------------+
| 875996580 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
UTC_DATE, UTC_DATE()
Returns the current UTC date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.mysql> SELECT UTC_DATE(), UTC_DATE() + 0; +---------------------------------------------------------+ | UTC_DATE(), UTC_DATE() + 0 | +---------------------------------------------------------+ | 2003-08-14, 20030814 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
UTC_TIME, UTC_TIME()
Returns the current UTC time as a value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or numeric context.mysql> SELECT UTC_TIME(), UTC_TIME() + 0; +---------------------------------------------------------+ | UTC_TIME(), UTC_TIME() + 0 | +---------------------------------------------------------+ | 18:07:53, 180753 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
UTC_TIMESTAMP, UTC_TIMESTAMP()
Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0; +---------------------------------------------------------+ | UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0 | +---------------------------------------------------------+ | 2003-08-14 18:08:04, 20030814180804 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
WEEK(date[,mode])
This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used|
Mode |
First Day of week |
Range |
Week 1 is the first week. |
|
0 |
Sunday |
0-53 |
with a Sunday in this year |
|
1 |
Monday |
0-53 |
with more than 3 days this year |
|
2 |
Sunday |
1-53 |
with a Sunday in this year |
|
3 |
Monday |
1-53 |
with more than 3 days this year |
|
4 |
Sunday |
0-53 |
with more than 3 days this year |
|
5 |
Monday |
0-53 |
with a Monday in this year |
|
6 |
Sunday |
1-53 |
with more than 3 days this year |
|
7 |
Monday |
1-53 |
with a Monday in this year |
mysql> SELECT WEEK('1998-02-20');
+---------------------------------------------------------+
| WEEK('1998-02-20') |
+---------------------------------------------------------+
| 7 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
WEEKDAY(date)
Returns the weekday index for date (0 = Monday, 1 = Tuesday, . 6 = Sunday).mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
+---------------------------------------------------------+
| WEEKDAY('1998-02-03 22:23:00') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
WEEKOFYEAR(date)
Returns the calendar week of the date as a number in the range from 1 to 53. WEEKOFYEAR() is a compatibility function that is equivalent to WEEK(date,3).mysql> SELECT WEEKOFYEAR('1998-02-20');
+---------------------------------------------------------+
| WEEKOFYEAR('1998-02-20') |
+---------------------------------------------------------+
| 8 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
YEAR(date)
Returns the year for date, in the range 1000 to 9999, or 0 for the .zero. date.mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03') |
+---------------------------------------------------------+
| 1998 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
YEARWEEK(date), YEARWEEK(date,mode)
Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.mysql> SELECT YEARWEEK('1987-01-01');
+---------------------------------------------------------+
| YEAR('98-02-03')YEARWEEK('1987-01-01') |
+---------------------------------------------------------+
| 198653 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Note that the week number is different from what the WEEK() function
would return (0) for optional arguments 0 or 1, as WEEK() then
returns the week in the context of the given year
DECODE Function
Table Store_Information
|
Store_Name |
Sales |
Txn_Date |
|
Los Angeles |
1500
|
Jan-05-1999 |
|
San Diego |
250
|
Jan-07-1999 |
|
San Francisco |
300
|
Jan-08-1999 |
|
Boston |
700
|
Jan-08-1999 |
To display 'LA' for 'Los Angeles', 'SF' for 'San Francisco', 'SD' for 'San Diego', and 'Others' for all other cities, we use the following SQL,
SELECT DECODE (Store_Name,
'Los Angeles', 'LA',
'San Francisco', 'SF',
'San Diego', 'SD',
'Others') Area, Sales, Txn_Date
FROM Store_Information;
"Area" is the name given to the column that the DECODE function operates on.
Result:
|
Area |
Sales |
Txn_Date |
|
LA |
1500
|
Jan-05-1999 |
|
SD |
250
|
Jan-07-1999 |
|
SF |
300
|
Jan-08-1999 |
|
Others |
700
|
Jan-08-1999 |
To achieve what DECODE does in MySQL and SQL Server, we would use the CASE function.
SQL >
Advanced SQL
> Case
CASE is used to provide if-then-else type of logic to SQL.
There are two formats: The first is a Simple CASE expression,
where we compare an expression to static values. The second is a
Searched CASE expression, where we compare an expression to
one or more logical conditions.
Simple
CASE Expression Syntax
SELECT Name, CASE Name
WHEN 'naveen' THEN salary * 2
WHEN 'harish' THEN salary * 1.5
ELSE Salary
END
"New Salary"
FROM employe_data;
Table Store_Information
|
Store_Name |
Sales |
Txn_Date |
|
Los Angeles |
1500
|
Jan-05-1999 |
|
San Diego |
250
|
Jan-07-1999 |
|
San Francisco |
300
|
Jan-08-1999 |
|
Boston |
700
|
Jan-08-1999 |
To multiply the sales amount from 'Los Angeles' by 2 and the sales amount from 'San Diego' by 1.5 while keeping the sales amount for other stores the same, we would use the following SQL statement using CASE:
SELECT Store_Name, CASE Store_Name
WHEN 'Los Angeles' THEN Sales * 2
WHEN 'San Diego' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Txn_Date
FROM Store_Information;
"New Sales" is the name given to the column with the CASE statement. This is an example of a simple CASE expression, because the conditions listed, 'Los Angeles' and 'San Diego', are static values.
Result:
|
Store_Name |
New Sales |
Txn_Date |
|
Los Angeles |
3000
|
Jan-05-1999 |
|
San Diego |
375
|
Jan-07-1999 |
|
San Francisco |
300
|
Jan-08-1999 |
|
Boston |
700
|
Jan-08-1999 |
Searched CASE Expression Syntax
SELECT
name, salary, CASE
WHEN Salary >= 30000 THEN '20% tax'
WHEN Salary >= 20000 THEN '10% tax'
ELSE '0% tax'
END
"Salary Status"
FROM employe_data;
We use the same Store_Information
above. If we want to define the status of a store's sale based on the
following rules:
-
If Sales >= 1,000, it's a "Good Day"
-
If Sales >= 500 and < 1,000, it's an "OK Day"
-
If Sales < 500, it's a "Bad Day"
WHEN Sales >= 1000 THEN 'Good Day'
WHEN Sales >= 500 THEN 'OK Day'
ELSE 'Bad Day'
END
"Sales Status"
FROM Store_Information;
Result:
|
Store_Name |
Txn_Date |
Sales Status |
|
Los Angeles |
Jan-05-1999 |
Good Day |
|
San Diego |
Jan-07-1999 |
Bad Day |
|
San Francisco |
Jan-08-1999 |
Bad Day |
|
Boston |
Jan-08-1999 |
OK Day |
Length
Function :
-
MySQL: LENGTH( )
-
Oracle: LENGTH( )
-
SQL Server: LEN( )
* SELECT Name,length(address) as LengthOfAddress
FROM employe_data;Table Geography
Region_Name |
Store_Name |
|
East |
Boston |
|
East |
New York |
|
West |
Los Angeles |
|
West |
San Diego |
Example 1
SELECT Length (Store_Name)FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:
|
Length (Store_Name) |
|
11
|
Example 2
SELECT Region_Name, Length (Region_Name)FROM Geography;
Result:
|
Region_Name |
Length (Region_Name) |
|
East |
4
|
|
East |
4
|
|
West |
4
|
|
West |
4
|
SQL - String Functions
SQL string functions are used
primarily for string manipulation. The following table details the
important string functions:
|
Name |
Description |
|---|---|
|
ASCII() |
Returns numeric value of left-most character |
|
BIN() |
Returns a string representation of the argument |
|
BIT_LENGTH() |
Returns length of argument in bits |
|
CHAR_LENGTH() |
Returns number of characters in argument |
|
CHAR() |
Returns the character for each integer passed |
|
CHARACTER_LENGTH() |
A synonym for CHAR_LENGTH() |
|
CONCAT_WS() |
Returns concatenate with separator |
|
CONCAT() |
Returns concatenated string |
|
CONV() |
Converts numbers between different number bases |
|
ELT() |
Returns string at index number |
|
EXPORT_SET() |
Returns a string such that for every bit set in the value bits,
you get an on string and for every unset bit, you get an off
string |
|
FIELD() |
Returns the index (position) of the first argument in the
subsequent arguments |
|
FIND_IN_SET() |
Returns the index position of the first argument within the
second argument |
|
FORMAT() |
Returns a number formatted to specified number of decimal
places |
|
HEX() |
Returns a string representation of a hex value |
|
INSERT() |
Inserts a substring at the specified position up to the
specified number of characters |
|
INSTR() |
Returns the index of the first occurrence of substring |
|
LCASE() |
Synonym for LOWER()
|
|
LEFT() |
Returns the leftmost number of characters as specified |
|
LENGTH() |
Returns the length of a string in bytes |
|
LOAD_FILE() |
Loads the named file |
|
LOCATE() |
Returns the position of the first occurrence of substring |
|
LOWER() |
Returns the argument in lowercase
|
|
LPAD() |
Returns the string argument, left-padded with the specified
string |
|
LTRIM() |
Removes leading spaces |
|
MAKE_SET() |
Returns a set of comma-separated strings that have the
corresponding bit in bits set |
|
MID() |
Returns a substring starting from the specified position |
|
OCT() |
Returns a string representation of the octal argument |
|
OCTET_LENGTH() |
A synonym for LENGTH() |
|
ORD() |
If the leftmost character of the argument is a multi-byte
character, returns the code for that character |
|
POSITION() |
A synonym for LOCATE() |
|
QUOTE() |
Escapes the argument for use in an SQL statement |
|
REGEXP |
Pattern matching using regular expressions |
|
REPEAT() |
Repeats a string the specified number of times |
|
REPLACE() |
Replaces occurrences of a specified string |
|
REVERSE() |
Reverses the characters in a string |
|
RIGHT() |
Returns the specified rightmost number of characters |
|
RPAD() |
Appends string the specified number of times |
|
RTRIM() |
Removes trailing spaces |
|
SOUNDEX() |
Returns a soundex string |
|
SOUNDS
LIKE |
Compares sounds |
|
SPACE() |
Returns a string of the specified number of spaces |
|
STRCMP() |
Compares two strings |
|
SUBSTRING_INDEX() |
Returns a substring from a string before the specified number
of occurrences of the delimiter |
|
SUBSTRING(),
SUBSTR() |
Returns the substring as specified |
|
TRIM() |
Removes leading and trailing spaces |
|
UCASE() |
Synonym for UPPER() |
|
UNHEX() |
Converts each pair of hexadecimal digits to a character |
|
UPPER() |
Converts to uppercase |
ASCII(str)
Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255. * SELECT ASCII('4'); * SELECT ASCII('dx');
BIN(N)
Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.SQL> SELECT BIN(12); +---------------------------------------------------------+ | BIN(12) | +---------------------------------------------------------+ | 1100 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
BIT_LENGTH(str)
Returns the length of the string str in bits.* SELECT BIT_LENGTH('text');
CHAR(N,... [USING charset_name])
CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.SQL> SELECT CHAR(77,121,83,81,'76'); +---------------------------------------------------------+ | CHAR(77,121,83,81,'76') | +---------------------------------------------------------+ | MySQL | +---------------------------------------------------------+ 1 row in set (0.00 sec)
CHAR_LENGTH(str)
Returns the length of the string str measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.SQL> SELECT CHAR_LENGTH("text");
+---------------------------------------------------------+
| CHAR_LENGTH("text") |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:SQL> SELECT CONCAT('My', 'S', 'QL');
+---------------------------------------------------------+
| CONCAT('My', 'S', 'QL') |
+---------------------------------------------------------+
| MySQL |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CONCAT_WS(separator,str1,str2,...)
CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.SQL> SELECT CONCAT_WS(',','First name','Last Name' );
+---------------------------------------------------------+
| CONCAT_WS(',','First name','Last Name' ) |
+---------------------------------------------------------+
| First name,Last Name |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.SQL> SELECT CONV('a',16,2);
+---------------------------------------------------------+
| CONV('a',16,2) |
+---------------------------------------------------------+
| 1010 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
ELT(N,str1,str2,str3,...)
Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().SQL> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo'); +---------------------------------------------------------+ | ELT(1, 'ej', 'Heja', 'hej', 'foo') | +---------------------------------------------------------+ | ej | +---------------------------------------------------------+ 1 row in set (0.00 sec)
EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character .,.). The number of bits examined is given by number_of_bits (defaults to 64).SQL> SELECT EXPORT_SET(5,'Y','N',',',4); +---------------------------------------------------------+ | EXPORT_SET(5,'Y','N',',',4) | +---------------------------------------------------------+ | Y,N,Y,N | +---------------------------------------------------------+ 1 row in set (0.00 sec)
FIELD(str,str1,str2,str3,...)
Returns the index (position starting with 1) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.SQL> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
+---------------------------------------------------------+
| FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
FIND_IN_SET(str,strlist)
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.SQL> SELECT FIND_IN_SET('b','a,b,c,d');
+---------------------------------------------------------+
| SELECT FIND_IN_SET('b','a,b,c,d') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
FORMAT(X,D)
Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.SQL> SELECT FORMAT(12332.123456, 4); +---------------------------------------------------------+ | FORMAT(12332.123456, 4) | +---------------------------------------------------------+ | 12,332.1235 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
HEX(N_or_S)
If N_or_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).If N_or_S is a string, returns a hexadecimal string representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits.
SQL> SELECT HEX(255); +---------------------------------------------------------+ | HEX(255) | +---------------------------------------------------------+ | FF | +---------------------------------------------------------+ 1 row in set (0.00 sec) SQL> SELECT 0x616263; +---------------------------------------------------------+ | 0x616263 | +---------------------------------------------------------+ | abc | +---------------------------------------------------------+ 1 row in set (0.00 sec)
INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.SQL> SELECT INSERT('Quadratic', 3, 4, 'What');
+---------------------------------------------------------+
| INSERT('Quadratic', 3, 4, 'What') |
+---------------------------------------------------------+
| QuWhattic |
+---------------------------------------------------------+
1 row in set (0.00 sec)
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.SQL> SELECT INSTR('foobarbar', 'bar');
+---------------------------------------------------------+
| INSTR('foobarbar', 'bar') |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LCASE(str)
LCASE() is a synonym for LOWER().LEFT(str,len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.SQL> SELECT LEFT('foobarbar', 5);
+---------------------------------------------------------+
| LEFT('foobarbar', 5) |
+---------------------------------------------------------+
| fooba |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LENGTH(str)
Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.SQL> SELECT LENGTH('text');
+---------------------------------------------------------+
| LENGTH('text') |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.
As of SQL 5.0.19, the character_set_filesystem system variable controls interpretation of filenames that are given as literal strings.
SQL> UPDATE table_test
-> SET blob_col=LOAD_FILE('/tmp/picture')
-> WHERE id=1;
...........................................................
LOCATE(substr,str), LOCATE(substr,str,pos)
The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.SQL> SELECT LOCATE('bar', 'foobarbar');
+---------------------------------------------------------+
| LOCATE('bar', 'foobarbar') |
+---------------------------------------------------------+
| 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping.SQL> SELECT LOWER('QUADRATICALLY');
+---------------------------------------------------------+
| LOWER('QUADRATICALLY') |
+---------------------------------------------------------+
| quadratically |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.SQL> SELECT LPAD('hi',4,'??');
+---------------------------------------------------------+
| LPAD('hi',4,'??') |
+---------------------------------------------------------+
| ??hi |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LTRIM(str)
Returns the string str with leading space characters removed.SQL> SELECT LTRIM(' barbar');
+---------------------------------------------------------+
| LTRIM(' barbar') |
+---------------------------------------------------------+
| barbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
MAKE_SET(bits,str1,str2,...)
Returns a set value (a string containing substrings separated by .,. characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.SQL> SELECT MAKE_SET(1,'a','b','c'); +---------------------------------------------------------+ | MAKE_SET(1,'a','b','c') | +---------------------------------------------------------+ | a | +---------------------------------------------------------+ 1 row in set (0.00 sec)
MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).OCT(N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.SQL> SELECT OCT(12); +---------------------------------------------------------+ | OCT(12) | +---------------------------------------------------------+ | 14 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
OCTET_LENGTH(str)
OCTET_LENGTH() is a synonym for LENGTH().ORD(str)
If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:(1st byte code) + (2nd byte code . 256) + (3rd byte code . 2562) ...If the leftmost character is not a multi-byte character, ORD() returns the same value as the ASCII() function.
SQL> SELECT ORD('2');
+---------------------------------------------------------+
| ORD('2') |
+---------------------------------------------------------+
| 50 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
POSITION(substr IN str)
POSITION(substr IN str) is a synonym for LOCATE(substr,str).QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotes and with each instance of single quote ('), backslash ('\'), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word 'NULL' without enclosing single quotes.SQL> SELECT QUOTE('Don\'t!');
+---------------------------------------------------------+
| QUOTE('Don\'t!') |
+---------------------------------------------------------+
| 'Don\'t!' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
NOTE: Please check if your installation has any bug with this
function then don't use this function.expr REGEXP pattern
This function performs a pattern match of expr against pattern. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. REGEXP is not case sensitive, except when used with binary strings.SQL> SELECT 'ABCDEF' REGEXP 'A%C%%'; +---------------------------------------------------------+ | 'ABCDEF' REGEXP 'A%C%%' | +---------------------------------------------------------+ | 0 | +---------------------------------------------------------+ 1 row in set (0.00 sec)Another example is:
SQL> SELECT 'ABCDE' REGEXP '.*'; +---------------------------------------------------------+ | 'ABCDE' REGEXP '.*' | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)Let's see one more example:
SQL> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line'; +---------------------------------------------------------+ | 'new*\n*line' REGEXP 'new\\*.\\*line' | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)
REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.SQL> SELECT REPEAT('SQL', 3);
+---------------------------------------------------------+
| REPEAT('SQL', 3) |
+---------------------------------------------------------+
| SQLSQLSQL |
+---------------------------------------------------------+
1 row in set (0.00 sec)
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.SQL> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+---------------------------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww') |
+---------------------------------------------------------+
| WwWwWw.mysql.com |
+---------------------------------------------------------+
1 row in set (0.00 sec)
REVERSE(str)
Returns the string str with the order of the characters reversed.SQL> SELECT REVERSE('abcd');
+---------------------------------------------------------+
| REVERSE('abcd') |
+---------------------------------------------------------+
| dcba |
+---------------------------------------------------------+
1 row in set (0.00 sec)
RIGHT(str,len)
Returns the rightmost len characters from the string str, or NULL if any argument is NULL.SQL> SELECT RIGHT('foobarbar', 4);
+---------------------------------------------------------+
| RIGHT('foobarbar', 4) |
+---------------------------------------------------------+
| rbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.SQL> SELECT RPAD('hi',5,'?');
+---------------------------------------------------------+
| RPAD('hi',5,'?') |
+---------------------------------------------------------+
| hi??? |
+---------------------------------------------------------+
1 row in set (0.00 sec)
RTRIM(str)
Returns the string str with trailing space characters removed.SQL> SELECT RTRIM('barbar ');
+---------------------------------------------------------+
| RTRIM('barbar ') |
+---------------------------------------------------------+
| barbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SOUNDEX(str)
Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.SQL> SELECT SOUNDEX('Hello');
+---------------------------------------------------------+
| SOUNDEX('Hello') |
+---------------------------------------------------------+
| H400 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
expr1 SOUNDS LIKE expr2
This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).SPACE(N)
Returns a string consisting of N space characters.SQL> SELECT SPACE(6); +---------------------------------------------------------+ | SELECT SPACE(6) | +---------------------------------------------------------+ | ' ' | +---------------------------------------------------------+ 1 row in set (0.00 sec)
STRCMP(str1, str2)
Compares two strings and returns 0 if both strings are equal, it returns -1 if the first argument is smaller than the second according to the current sort order otherwise it returns 1.SQL> SELECT STRCMP('MOHD', 'MOHD');
+---------------------------------------------------------+
| STRCMP('MOHD', 'MOHD') |
+---------------------------------------------------------+
| 0 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Another example is:SQL> SELECT STRCMP('AMOHD', 'MOHD');
+---------------------------------------------------------+
| STRCMP('AMOHD', 'MOHD') |
+---------------------------------------------------------+
| -1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Let's see one more example:SQL> SELECT STRCMP('MOHD', 'AMOHD');
+---------------------------------------------------------+
| STRCMP('MOHD', 'AMOHD') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.SQL> SELECT SUBSTRING('Quadratically',5);
+---------------------------------------------------------+
| SSUBSTRING('Quadratically',5) |
+---------------------------------------------------------+
| ratically |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL> SELECT SUBSTRING('foobarbar' FROM 4);
+---------------------------------------------------------+
| SUBSTRING('foobarbar' FROM 4) |
+---------------------------------------------------------+
| barbar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL> SELECT SUBSTRING('Quadratically',5,6);
+---------------------------------------------------------+
| SUBSTRING('Quadratically',5,6) |
+---------------------------------------------------------+
| ratica |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.SQL> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+---------------------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
+---------------------------------------------------------+
| www.mysql |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)
TRIM([remstr FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.SQL> SELECT TRIM(' bar ');
+---------------------------------------------------------+
| TRIM(' bar ') |
+---------------------------------------------------------+
| bar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
+---------------------------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxbarxxx') |
+---------------------------------------------------------+
| barxxx |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
+---------------------------------------------------------+
| TRIM(BOTH 'x' FROM 'xxxbarxxx') |
+---------------------------------------------------------+
| bar |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SQL> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
+---------------------------------------------------------+
| TRIM(TRAILING 'xyz' FROM 'barxxyz') |
+---------------------------------------------------------+
| barx |
+---------------------------------------------------------+
1 row in set (0.00 sec)
UCASE(str)
UCASE() is a synonym for UPPER().UNHEX(str)
Performs the inverse operation of HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string.SQL> SELECT UNHEX('4D7953514C');
+---------------------------------------------------------+
| UNHEX('4D7953514C') |
+---------------------------------------------------------+
| SQL |
+---------------------------------------------------------+
1 row in set (0.00 sec)
The characters in the argument string must be legal hexadecimal
digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If UNHEX() encounters any
non-hexadecimal digits in the argument, it returns NULL.UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping.SQL> SELECT UPPER('Allah-hus-samad');
+---------------------------------------------------------+
| UPPER('Allah-hus-samad') |
+---------------------------------------------------------+
| ALLAH-HUS-SAMAD |
+---------------------------------------------------------+
1 row in set (0.00 sec)
No comments:
Post a Comment