Found 4219 Articles for MySQLi

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

Different unit values which can be used with MySQL INTERVAL keyword are as follows −MICROSECONDThis unit will be used for adding or subtracting the number of specified microseconds from the current time or as provided by the user.mysql> Select NOW()+INTERVAL 100 MICROSECOND +--------------------------------+ | NOW()+INTERVAL 100 MICROSECOND | +--------------------------------+ | 2017-10-28 18:47:25.000100     | +--------------------------------+ 1 row in set (0.00 sec)Above query will add 100 microseconds to the current date & time with the help of MySQL INTERVAL keyword.mysql> Select '2017-02-25 05:04:30' + INTERVAL 100 Microsecond; +--------------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 100 Microsecond | +--------------------------------------------------+ | ... Read More

Suppose if we have stored a date value as ‘0000-00-00’ in MySQL table then on extracting year value from such kind of date, MySQL will return 0. It would not be in either Year(2) or Year(4) format. To understand it we are using the following data from ‘detail_bday’ table −mysql> Select * from detail_bday; +----+---------+------------+ | Sr | Name    | Birth_Date | +----+---------+------------+ | 1 | Saurabh  | 1990-05-12 | | 2 | Raman    | 1993-06-11 | | 3 | Gaurav   | 1984-01-17 | | 4 | Rahul    | 1993-06-11 | | 5 | Sonia   ... Read More