SQL CURDATE() returns the current date.
Example
mysql> SELECT CURDATE();
+————+
| CURDATE() |
+————+
| 2007-05-30 |
+————+
1 row in set (0.00 sec)
Daily Archives: May 30, 2012
SQL NOW()
SQL NOW() returns the current date and time.
Example
mysql> SELECT NOW();
+———————+
| NOW() |
+———————+
| 2007-05-30 14:47:17 |
+———————+
1 row in set (0.04 sec)
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 VIEW
A view is a virtual table based on the result-set of an SQL statement .
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
We can add fetch data from view using sql statements/function as if the data were coming from one single table.
Syntax
CREATE VIEW view_name AS SELECT * FROM table_name ;
Example
mysql> CREATE VIEW view_users AS SELECT * FROM users ;
Query OK, 0 rows affected (0.13 sec)
SQL AUTO INCREMENT
SQL AUTO INCREMENT allows a unique number to be generated when a new record is inserted into a table.
It usually used with primary keys
Example
CREATE TABLE IF NOT EXISTS `users` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`uid`)
)
SQL ALTER TABLE
SQL ALTER TABLE is used to add, delete, or modify columns in a table .
Add columns
Syntax
ALTER TABLE table_name ADD column_name datatype
Example
mysql> ALTER TABLE orders ADD price int ;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
Delete columns
Syntax
ALTER TABLE table_name DROP COLUMN column_name
Example
mysql> ALTER TABLE orders DROP COLUMN price;
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
Modify columns
Syntax
ALTER TABLE table_name CHANGE column_name column_name datatype
Example
mysql> ALTER TABLE orders CHANGE items items VARCHAR( 255 );
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
SQL TRUNCATE
SQL TRUNCATE can be used to delete records in table .
Example
mysql> TRUNCATE TABLE orders;
Query OK, 0 rows affected (0.00 sec)
SQL DROP DATABASE
SQL DROP DATABASE can be used to delete databases .
Example
mysql> DROP DATABASE database_name;
Query OK, 1 row affected (0.54 sec)
SQL DROP TABLE
SQL DROP TABLE can be used to delete tables .
Example
mysql> DROP TABLE user2;
Query OK, 0 rows affected (0.01 sec)
SQL DROP INDEX
SQL DROP INDEX can be used to delete indexes
Example
mysql> ALTER TABLE users DROP INDEX user_index;
Query OK, 5 rows affected (0.10 sec)
Records: 5 Duplicates: 0 Warnings: 0
Pramod T P 