Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
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
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car1’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car1 (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car1 values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car2 (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car2 values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More
DEFAULT constraint is used set a default value for a column in MySQL table. If it is applied on a column then it will take the default value of not giving any value for that column. Its syntax would be as follows −SyntaxDEFAULT default_valueHere, default_value is the default value set for the column.ExampleThe query below will create a table named workers where we assign the column id a DEFAULT value 1000.mysql> Create table workers(Name Varchar(25), Id INT NOT NULL DEFAULT 1000); Query OK, 0 rows affected (0.47 sec) mysql> Insert into workers(Name, Id) values('Ram', 101); Query OK, 1 row ... Read More
As we know that MySQL supports foreign key for referential integrity but it does not support CHECK constraint. But we can emulate them by using triggers. It can be illustrated with the help of an example given below −ExampleSuppose we have a table named ‘car’ which can have the fix syntax registration number like two letters, a dash, three digits, a dash, two letters as follows −mysql> Create table car (number char(9)); Query OK, 0 rows affected (0.32 sec) mysql> Insert into car values('AB-235-YZ'); Query OK, 1 row affected (0.10 sec)The above value is a valid one but what ... Read More