We can also add multiple columns to an existing table with the help of ALTER command. The syntax for it would be as follows −SyntaxAlter table table-name ADD (column-name1 datatype, column-name2 datatype,… column-nameN datatype);ExampleIn the example below, with the help of ALTER Command, columns ‘Address’, ‘Phone’ and ‘Email’ are added to the table ‘Student’.mysql> Alter Table Student ADD(Address Varchar(25), Phone INT, Email Varchar(20)); Query OK, 5 rows affected (0.38 sec) Records: 5 Duplicates: 0 Warnings: 0

Suppose we want to store the date such as February 30 in a MySQL table then we must have to first set ALLOW_INVALID_DATES mode enabled.For example, I am trying to add, without enabling ALLOW_INVALID_DATES mode, such kind of date in a table then MySQL will give an error as follows −mysql> Insert into date_testing(date) values('2017-02-30'); ERROR 1292 (22007): Incorrect date value: '2017-02-30' for column 'Date' at row1Now we need to enable ALLOW_INVALID_DATES mode enabled as follows −mysql> SET sql_mode = 'ALLOW_INVALID_DATES'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into date_testing(date) values('2017-02-30'); Query OK, 1 row affected (0.14 ... Read More

In MySQL, we can insert current date and time automatically to a column on inserting the NULL values in other columns by declaring that column as DEFAULT CURRENT_TIMESTAMP. In this case, we cannot declare the column NOT NULL in which we want to insert NULL values.mysql> Create Table Testing1(Name Varchar(20), RegStudent TIMESTAMP DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.15 sec)Above query will create a table ‘Testing1’ with a column named ‘Name’(not declared ‘NOT NULL’) and other column named ‘RegDate’ declared as DEFAULT CURRENT_TIMESTAMP. Now, on inserting the NULL values Name column, the current date and time will be inserted ... 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

For inserting the values in the column without specifying the names of the columns in INSERT INTO statement, we must give the number of values that matches the number of columns in the table along with taking care about the data type of that column too.ExampleIn the example below we have inserted the values without specifying the name of the column.mysql> Insert into student values(100, 'Gaurav', 'Ph.D'); Query OK, 1 row affected (0.08 sec) mysql> Select * from student; +--------+--------+--------+ | RollNO | Name   | Class  | +--------+--------+--------+ | 100    | Gaurav | Ph.D   | +--------+--------+--------+ ... Read More

The reason behind this concept is that MySQL requires the unit keywords to be singular, regardless of the English grammar rules. If we will try to supply intervals like 7 days, 2 hours etc then MySQL will produce syntax error as follows −mysql> Select '2017-02-25 05:04:30' + INTERVAL 2 days; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'days' at line 1

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

When we run the INSERT INTO statement without giving the columns name/s and values both then MySQL will store NULL as the value of the column/s of table. Consider the example given below in which we have created a table ‘Student’ with the following query −mysql> Create table Student(RollNO INT, Name Varchar(20), Class Varchar(15)); Query OK, 0 rows affected (0.17 sec)Now, we can run INSERT INTO statement without giving the columns name/s and values both as follows −mysql> Insert into Student() Values(); Query OK, 1 row affected (0.02 sec)We can see from the query below MySQL stores NULL as the ... Read More

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.

If we want to get more details about the column of an existing table then we need to use SHOW FULL COLUMNS statement. Consider the example below in which SHOW FULL COLUMNS statement has been applied on ‘Employee’ table and MySQL returns result set with some extra details like Collation, Privileges, and Comment, about the columns of the table −mysql> SHOW FULL COLUMNS FROM EMPLOYEE\G *************************** 1. row ***************************      Field: Id       Type: int(11)  Collation: NULL       Null: YES        Key:    Default: NULL      Extra: Privileges: select, insert, update, references ... Read More