- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4378 Articles for MySQL
141 Views
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
746 Views
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
391 Views
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
2K+ Views
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
71 Views
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
How can we insert current date and time automatically on inserting values in other columns in MySQL?
2K+ Views
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
128 Views
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
654 Views
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.
111 Views
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
61 Views
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