Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
In MySQL, we can insert the current date and time automatically to a column on inserting the values in another column by declaring that column as DEFAULT CURRENT_TIMESTAMP.Examplemysql> Create table testing -> ( -> StudentName varchar(20) NOT NULL, -> RegDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP -> ); Query OK, 0 rows affected (0.49 sec)Above query will create a table ‘testing’ with a column named StudentName and other column named ‘RegDate’ declared as DEFAULT CURRENT_TIMESTAMP. Now, on inserting the values i.e. names in StudentName column, the current date and time will be inserted in the other column automatically.mysql> Insert ... Read More
By using ALTER command we can add columns to an existing table.SyntaxAlter table table-name ADD (column-name datatype);Example In the example below, with the help of ALTER Command, column ‘GRADE’ is added to the table ‘Student’.mysql> Alter table Student ADD (Grade Varchar(10)); Query OK, 5 rows affected (1.05 sec) Records: 5 Duplicates: 0 Warnings: 0
We can see the create table statement of an existing table by using SHOW CREATE TABLE query.SyntaxSHOW CREATE TABLE table_name;Examplemysql> Show create table employee\G *************************** 1. row *************************** Table: employee Create Table: CREATE TABLE `employee` ( `Id` int(11) DEFAULT NULL, `Name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)The query above gives the CREATE TABLE statement of ‘Employee’ table.
SQL language is divided into four types of primary language statements: DML, DDL, DCL and TCL. Using these statements, we can define the structure of a database by creating and altering database objects and we can manipulate data in a table through updates or deletions. We also can control which user can read/write data or manage transactions to create a single unit of work.The four main categories of SQL statements are as follows −DML (Data Manipulation Language)DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, ... Read More
CREATE command is a DDL command which is used to create a table or a database. The syntax for creating table and database with CREATE command is as follows −The syntax for creating a database −Create database database-name;Examplemysql> Create database query; Query OK, 1 row affected (0.04 sec)In the example above we have created a database named ‘query’.The syntax for creating a table −Create table table-name( column-name1 datatype1, column-name2 datatype2, column-name3 datatype3, column-name4 datatype4 ------------------------------);Examplemysql> Create table Employee(Id INT, Name Varchar(20)); Query OK, 0 rows affected (0.19 sec)In the example above, we have created a ... Read More
With the help of CURDATE() and NOW() function, we can insert current date automatically in a column of MySQL table.ExampleSuppose we want to insert current date automatically in an OrderDate column of table year_testing the following query will do this −mysql> Insert into year_testing (OrderDate) Values(CURDATE()); Query OK, 1 row affected (0.11 sec) mysql> Select * from year_testing; +------------+ | OrderDate | +------------+ | 2017-10-28 | +------------+ 1 row in set (0.00 sec) mysql> Insert into year_testing (OrderDate) Values(NOW()); Query OK, 1 row affected, 1 warning (0.12 sec) mysql> Select * from year_testing; +------------+ | OrderDate | +------------+ ... Read More
INTERVAL keyword with NOW() and CURDATE() MySQL functions can be used in similar fashion as it can be used with time, date or datetime units of a date value.ExampleUsing INTERVAL with MySQL NOW()mysql> Select NOW() + INTERVAL 2 day; +------------------------+ | NOW() + INTERVAL 2 day | +------------------------+ | 2017-10-30 09:19:31 | +------------------------+ 1 row in set (0.00 sec) mysql> Select NOW() - INTERVAL 2 day; +------------------------+ | NOW() - INTERVAL 2 day | +------------------------+ | 2017-10-26 09:19:45 | +------------------------+ 1 row in set (0.00 sec) mysql> Select NOW() - INTERVAL 2 hour; +-------------------------+ | NOW() ... Read More
MySQL can perform date arithmetic with addition and subtraction operators by adding together INTERVAL keyword with a unit of time, date or datetime.Example1Adding 2 days to a particular date.mysql> Select '2017-05-20' + INTERVAL 2 day; +-------------------------------+ | '2017-05-20' + INTERVAL 2 day | +-------------------------------+ | 2017-05-22 | +-------------------------------+ 1 row in set (0.00 sec)Example2Subtracting 2 days from a particular date.mysql> Select '2017-05-20' - INTERVAL 2 day; +-------------------------------+ | '2017-05-20' - INTERVAL 2 day | +-------------------------------+ | 2017-05-18 | +-------------------------------+ 1 row in ... Read More
Conversion of TIME(N) and DATETIME(N) values to numeric form can be done by adding 0(+0) to them. Followings are the rules for such kind of conversion −Converted to INTEGERThe TIME(N) and DATETIME(N) values will be converted to an integer when N is 0.For example, the values of CURTIME() and NOW() can be converted to integer values as follows −mysql> SELECT CURTIME(), CURTIME()+0; +-----------+-------------------+ | CURTIME() | CURTIME()+0 | +-----------+-------------------+ | 19:42:54 | 194254 | +-----------+-------------------+ 1 row in set (0.04 sec) mysql> SELECT NOW(), NOW()+0; +-------------------------+----------------------------------+ | NOW() ... Read More
YEAR(2) stores a year in 2-digit format. For example, we can write 69 to store 1969 a year. In YEAR (2), the year can be specified from 1970 to 2069 (70 to 69).YEAR(4) stores a year in 4-digit format. For example, we need to write 19669 to store 1969 as a year. In YEAR (4), the year can be specified from 1901 to 2155.MySQL interprets 2-digit year values with the assistance of following rules:Year values within the vary 00-69 are converted to 2000-2069.Year values in the range 70-99 are converted to 1970-1999.We must not store date values as a 2-digit ... Read More