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
-
Economics & Finance
MySQLi Articles
Page 311 of 341
How to rearrange MySQL columns?
To rearrange the MySQL columns, check the column arrangement with the help of show create command. The syntax is as follows −show create table yourTableName;The syntax to rearrange the MySQL columns is as follows −alter table yourTableName change column yourColumnName yourColumnName dataType firstFor the same purpose, you can use the after keyword. The syntax is as follows −alter table yourTableName change column yourColumnName yourColumnName dataType after yourSpecificColumnName;Let us first check the column arrangement for the already created table “AddColumn” −mysql> show create table AddColumn; The following is the output −+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
Read MoreWhich MySQL type is most suitable for "price" column?
The best type for price column should be DECIMAL. The type DECIMAL stores the value precisely.For Example - DECIMAL(10, 2) can be used to store price value. It means the total digit will be 10 and two digits will be after decimal point.To understand the type DECIMAL, let us create a table.mysql> create table PriceDemo −> ( −> ProductPrice DECIMAL(10, 2) −> ); Query OK, 0 rows affected (0.60 sec)Now insert some records in the table in the form of price. The query to insert records is as follows −mysql> insert into PriceDemo values(12345.67); Query OK, 1 row ...
Read MoreMySQL query to extract last word from a field?
To extract last word from a field, use in-built SUBSTRING_INDEX() function. The syntax is as follows −SELECT SUBSTRING_INDEX(yourColumnName, ’ ‘, -1) as anyVariableName from yourTableName;To understand the above concept, let us create a table. The following is the query to create a table −mysql> create table FirstWordDemo −> ( −> AllWords longtext −> ); Query OK, 0 rows affected (0.83 sec)Now insert some words in the table using insert command. The query is as follows −mysql> insert into FirstWordDemo values('This is the first MySQL Query'); Query OK, 1 row affected (0.11 ...
Read MoreHow to resolve the error that occurs while using a reserved word as a table or column name in MySQL?
This error occurs when you try to use a reserved word as a table or column name. It can occur due to −Case 1: Whenever you use reserved word as a table name −mysql> create table insert −> ( −> Id int −> );The error is as follows −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 'insert ( Id int )' at line 1The above error occurred because the word ‘insert’ is a keyword in MySQL.Case 2 − Whenever you ...
Read MoreHow to increase varchar size of an existing column in a database without breaking existing data in MySQL?
Increase the varchar size of an existing column in a database with the help of CHANGE command. The syntax is as follows −alter table yourTableName change yourColumnName yourColumnName dataType;Here, we are creating a table with a single column and varchar size 200 −mysql> create table IncreaseVarcharDemo −> ( −> StudentId varchar(200) −> ); Query OK, 0 rows affected (0.60 sec)Now insert record in the table. The query is as follows −mysql> insert into IncreaseVarcharDemo values('John123'); Query OK, 1 row affected (0.16 sec)Displaying all records from the table with the help of the following query −mysql> select *from ...
Read MoreHow to add 30 minutes to datetime in MySQL?
To add minutes to a datetime you can use DATE_ADD() function from MySQL. In PHP, you can use strtotime().To add 30 minutes in MySQL, the DATE_ADD() function is as follows −select date_add(yourColumnName, interval 30 minute) from yourTableName;To use the above syntax, let us create a table. The following is the query to create a table.mysql> create table Add30MinutesDemo −> ( −> YourTime datetime −> ); Query OK, 0 rows affected (0.67 sec)Insert records in the table with the help of following query −mysql> insert into Add30MinutesDemo values(now()); Query OK, 1 row ...
Read MoreDelete more than one rows from a table using id in MySQL?
You can use IN statement to delete more than one rows from a table using id in MySQL. The syntax is as follows −delete from yourTableName where yourColumnName in(value1, value2, .....valueN);To understand the above syntax, let us create a table. The following is the query to create a table.mysql> create table DeleteManyRows −> ( −> Id int, −> Name varchar(200), −> Age int −> ); Query OK, 0 rows affected (3.35 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into DeleteManyRows values(1, 'John', 23); ...
Read MoreReturning a value even if there is no result in a MySQL query?
You can use IFNULL() function from MySQL to return a value even if there is not result. Let us create a table. Te query to create a table.mysql> create table IfNullDemo −> ( −> Id int, −> Name varchar(100) −> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into IfNullDemo values(1, 'John'); Query OK, 1 row affected (0.18 sec) mysql> insert into IfNullDemo values(200, 'Sam'); Query OK, 1 row affected (0.21 sec) mysql> insert into IfNullDemo ...
Read MoreDelete all the records from a MySQL table?
To delete all the records from a MySQL table, you can use the TRUNCATE statement.The syntax is as follows −TRUNCATE TABLE yourTableName;The above syntax deletes all the records from the table. To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table DeleteAllFromTable −> ( −> PersonId int, −> PersonName varchar(200) −> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table with the help of insert command.The query is as follows −mysql> insert ...
Read MoreMYSQL select DISTINCT values from two columns?
To select distinct values in two columns, you can use least() and greatest() function from MySQL.Let us create a table with two columns −mysql> create table SelectDistinctTwoColumns −> ( −> StudentId int, −> EmployeeId int −> ); Query OK, 0 rows affected (0.60 sec)Now you can insert records in the table. The query to insert records is as follows −mysql> insert into SelectDistinctTwoColumns values(100, 101); Query OK, 1 row affected (0.39 sec) mysql> insert into SelectDistinctTwoColumns values(102, 103); Query OK, 1 row affected (0.13 sec) mysql> insert into SelectDistinctTwoColumns values(104, 105); Query OK, 1 ...
Read More