Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
MySQLi Articles
Page 90 of 341
How can we add multiple columns, with single command, to an existing MySQL table?
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
Read MoreHow is it possible to store date such as February 30 in a MySQL date column?
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 MoreWhat are the different unit values that can be used with MySQL INTERVAL keyword?
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 MoreHow can I insert the values in columns without specifying the names of the column in MySQL INSERT INTO statement?
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 MoreHow can we insert current date and time automatically on inserting values in other columns in MySQL?
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 MoreHow to add columns to an existing MySQL table?
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
Read MoreWhat are the different commands used in MySQL?
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 MoreHow can I use INTERVAL keyword with MySQL NOW() and CURDATE() functions?
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 MoreHow can we convert TIME and DATETIME values to numeric form in MySQL?
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 MoreWhat is the difference between YEAR(2) and YEAR(4) in MySQL?
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