WORLD BEST OPEN SOURCE DATABASE MYSQL.

MySQL is world best open source database in this post we show you how to use SQL and MySQL for data storing and retrieval from database.

First of all we analyses the application requirement and then we create new database and tables.I am using phpMyAdmin for create a new database its easy , fast, powerful and user friendly .

You can download phpMyAdmin from phpMyAdmin web site its free.
Go to phpMyAdmin web site and download latest version.

How to install phpMyAdmin ?

After the download latest version install it in PC. you can access phpMyAdmin through any browser like Mozilla and IE and you will be type in web address bar "http://localhost/phpmyadmin".

After that this below screen will be come.

Write new database name in create new database field. i have created new database "Security" and its showing in left column.
Now you have ready for create new table in new database Security.
Suppose we create new table Users with six fields.

userid
password
firstname
lastname
emailid
register_date

CREATE TABLE `Users` (

`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`password` VARCHAR( 20 ) NOT NULL ,
`firstname` VARCHAR( 20 ) NOT NULL ,
`lastname` VARCHAR( 20 ) NOT NULL ,
`emailid` VARCHAR( 30 ) NOT NULL ,
`register_date` DATE NOT NULL

) ;

How to insert new records in Users table?

You can insert record through below sql script.

INSERT INTO `Users` (

`userid` ,
`password` ,
`firstname` ,
`lastname` ,
`emailid` ,
`register_date`

)
VALUES (

'abc123', 'aamir', 'khan', 'aamir_khan@yahoo.com', '2015-02-18'

);

INSERT INTO `Users` (

`userid` ,
`password` ,
`firstname` ,
`lastname` ,
`emailid` ,
`register_date`

)
VALUES (

'abc456', 'kashif', 'naseem', 'kashif_nasem@yahoo.com', '2015-02-18'

);

userid field is auto increment no need for write any value.

I have store two new records into Users table.

How we can Select data from simple SQL query?

Select * from Users;

After the run this query below result are will be come.

How we can Select data from SQL query with condition?

Select * from Users
Where userid=1;

After the run this query below result are will be come.

Select * from Users
Where firstname = 'kashif'
And lastname='naseem';

Another example of condition query.

SELECT *
FROM `Users`
WHERE register_date = '2015-02-18';

How to use Like and Not Like in SQL Query?

Select * from Users
Where firstname LIKE 'kashif%';

Select * from Users
Where firstname NOT LIKE 'kashif%';

How to sort data ascending and descending?

Select firstname, lastname from Users
order by firstname ;

Select firstname, lastname from Users
order by firstname ASC ;

Select firstname, lastname from Users

order by firstname DESC ;

How to limited query works?

Select * from Users
Limit 2 ;

SELECT * FROM Users
ORDER BY lastname ASC
LIMIT 2;
How to Update Data?

Update Users Set
firstname='Asif'
Where userid=1;

Update Users Set
firstname='Asif'

Where lastname='khan';

How to Delete Data?

Delete from Users
Where userid=1;

Delete from Users
Where firstname='Asif'
And lastname='khan';

Mysql Operators.

Name	Description
`BETWEEN ... AND ...`	Check whether a value is within a range of values
`COALESCE()`	Return the first non-NULL argument
`<=>`	NULL-safe equal to operator
`=`	Equal operator
`>=`	Greater than or equal operator
`>`	Greater than operator
`GREATEST()`	Return the largest argument
`IN()`	Check whether a value is within a set of values
`INTERVAL()`	Return the index of the argument that is less than the first argument
`IS NOT NULL`	NOT NULL value test
`IS NOT`	Test a value against a boolean
`IS NULL`	NULL value test
`IS`	Test a value against a boolean
`ISNULL()`	Test whether the argument is NULL
`LEAST()`	Return the smallest argument
`<=`	Less than or equal operator
`<`	Less than operator
`LIKE`	Simple pattern matching
`NOT BETWEEN ... AND ...`	Check whether a value is not within a range of values
`!=`, `<>`	Not equal operator
`NOT IN()`	Check whether a value is not within a set of values
`NOT LIKE`	Negation of simple pattern matching
`STRCMP()`	Compare two strings

Mysql String Functions.

Name	Description
`ASCII()`	Return numeric value of left-most character
`BIN()`	Return a string containing binary representation of a number
`BIT_LENGTH()`	Return length of argument in bits
`CHAR_LENGTH()`	Return number of characters in argument
`CHAR()`	Return the character for each integer passed
`CHARACTER_LENGTH()`	Synonym for CHAR_LENGTH()
`CONCAT_WS()`	Return concatenate with separator
`CONCAT()`	Return concatenated string
`ELT()`	Return string at index number
`EXPORT_SET()`	Return 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()`	Return the index (position) of the first argument in the subsequent arguments
`FIND_IN_SET()`	Return the index position of the first argument within the second argument
`FORMAT()`	Return a number formatted to specified number of decimal places
`HEX()`	Return a hexadecimal representation of a decimal or string value
`INSERT()`	Insert a substring at the specified position up to the specified number of characters
`INSTR()`	Return the index of the first occurrence of substring
`LCASE()`	Synonym for LOWER()
`LEFT()`	Return the leftmost number of characters as specified
`LENGTH()`	Return the length of a string in bytes
`LIKE`	Simple pattern matching
`LOAD_FILE()`	Load the named file
`LOCATE()`	Return the position of the first occurrence of substring
`LOWER()`	Return the argument in lowercase
`LPAD()`	Return the string argument, left-padded with the specified string
`LTRIM()`	Remove leading spaces
`MAKE_SET()`	Return a set of comma-separated strings that have the corresponding bit in bits set
`MATCH`	Perform full-text search
`MID()`	Return a substring starting from the specified position
`NOT LIKE`	Negation of simple pattern matching
`NOT REGEXP`	Negation of REGEXP
`OCT()`	Return a string containing octal representation of a number
`OCTET_LENGTH()`	Synonym for LENGTH()
`ORD()`	Return character code for leftmost character of the argument
`POSITION()`	Synonym for LOCATE()
`QUOTE()`	Escape the argument for use in an SQL statement
`REGEXP`	Pattern matching using regular expressions
`REPEAT()`	Repeat a string the specified number of times
`REPLACE()`	Replace occurrences of a specified string
`REVERSE()`	Reverse the characters in a string
`RIGHT()`	Return the specified rightmost number of characters
`RLIKE`	Synonym for REGEXP
`RPAD()`	Append string the specified number of times
`RTRIM()`	Remove trailing spaces
`SOUNDEX()`	Return a soundex string
`SOUNDS LIKE`	Compare sounds
`SPACE()`	Return a string of the specified number of spaces
`STRCMP()`	Compare two strings
`SUBSTR()`	Return the substring as specified
`SUBSTRING_INDEX()`	Return a substring from a string before the specified number of occurrences of the delimiter
`SUBSTRING()`	Return the substring as specified
`TRIM()`	Remove leading and trailing spaces
`UCASE()`	Synonym for UPPER()
`UNHEX()`	Return a string containing hex representation of a number
`UPPER()`	Convert to uppercase

Mysql Date/Time Functions.

Name	Description
`ADDDATE()`	Add time values (intervals) to a date value
`ADDTIME()`	Add time
`CONVERT_TZ()`	Convert from one timezone to another
`CURDATE()`	Return 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()`	Return the current time
`DATE_ADD()`	Add time values (intervals) to a date value
`DATE_FORMAT()`	Format date as specified
`DATE_SUB()`	Subtract a time value (interval) from a date
`DATE()`	Extract the date part of a date or datetime expression
`DATEDIFF()`	Subtract two dates
`DAY()`	Synonym for DAYOFMONTH()
`DAYNAME()`	Return the name of the weekday
`DAYOFMONTH()`	Return the day of the month (0-31)
`DAYOFWEEK()`	Return the weekday index of the argument
`DAYOFYEAR()`	Return the day of the year (1-366)
`EXTRACT()`	Extract part of a date
`FROM_DAYS()`	Convert a day number to a date
`FROM_UNIXTIME()`	Format UNIX timestamp as a date
`GET_FORMAT()`	Return a date format string
`HOUR()`	Extract the hour
`LAST_DAY`	Return the last day of the month for the argument
`LOCALTIME()`, `LOCALTIME`	Synonym for NOW()
`LOCALTIMESTAMP`, `LOCALTIMESTAMP()`	Synonym for NOW()
`MAKEDATE()`	Create a date from the year and day of year
`MAKETIME()`	Create time from hour, minute, second
`MICROSECOND()`	Return the microseconds from argument
`MINUTE()`	Return the minute from the argument
`MONTH()`	Return the month from the date passed
`MONTHNAME()`	Return the name of the month
`NOW()`	Return the current date and time
`PERIOD_ADD()`	Add a period to a year-month
`PERIOD_DIFF()`	Return the number of months between periods
`QUARTER()`	Return the quarter from a date argument
`SEC_TO_TIME()`	Converts seconds to 'HH:MM:SS' format
`SECOND()`	Return the second (0-59)
`STR_TO_DATE()`	Convert a string to a date
`SUBDATE()`	Synonym for DATE_SUB() when invoked with three arguments
`SUBTIME()`	Subtract times
`SYSDATE()`	Return the time at which the function executes
`TIME_FORMAT()`	Format as time
`TIME_TO_SEC()`	Return the argument converted to seconds
`TIME()`	Extract the time portion of the expression passed
`TIMEDIFF()`	Subtract time
`TIMESTAMP()`	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
`TIMESTAMPADD()`	Add an interval to a datetime expression
`TIMESTAMPDIFF()`	Subtract an interval from a datetime expression
`TO_DAYS()`	Return the date argument converted to days
`UNIX_TIMESTAMP()`	Return a UNIX timestamp
`UTC_DATE()`	Return the current UTC date
`UTC_TIME()`	Return the current UTC time
`UTC_TIMESTAMP()`	Return the current UTC date and time
`WEEK()`	Return the week number
`WEEKDAY()`	Return the weekday index
`WEEKOFYEAR()`	Return the calendar week of the date (0-53)
`YEAR()`	Return the year
`YEARWEEK()`	Return the year and week