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 274 of 341
How to add +1 to existing MySQL values?
Let us see an example and create a table first.mysql> create table Add1ToExistingValue -> ( -> Value int -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into Add1ToExistingValue values(10); Query OK, 1 row affected (0.12 sec) mysql> insert into Add1ToExistingValue values(13); Query OK, 1 row affected (0.15 sec) mysql> insert into Add1ToExistingValue values(15); Query OK, 1 row affected (0.13 sec) mysql> insert into Add1ToExistingValue values(16); Query OK, 1 row affected (0.14 sec) mysql> insert into Add1ToExistingValue values(20); Query OK, 1 row affected (0.16 sec) mysql> insert into Add1ToExistingValue values(40); Query OK, 1 row affected (0.15 sec) mysql> insert into Add1ToExistingValue values(50); Query OK, 1 row affected (0.11 sec) mysql> insert into Add1ToExistingValue values(55); Query OK, 1 row affected (0.17 sec) mysql> insert into Add1ToExistingValue values(56); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement.The query is as followsmysql> select *from Add1ToExistingValue;The following is the output+-------+ | Value | +-------+ | 10 ...
Read MoreMySQL replace values in a table?
To replace values in a table, use the CASE statement. To understand the concept, let us create a table. The query to create a table is as followsmysql> create table replaceValueDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(10), -> isGreaterThan18 varchar(10) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into replaceValueDemo(Name, isGreaterThan18) values('John', 'YES'); Query OK, 1 row affected (0.24 sec) mysql> insert into replaceValueDemo(Name, isGreaterThan18) values('Carol', 'NO'); Query OK, 1 row affected (0.16 sec) ...
Read MoreHow to alter a MySQL Column from varchar(30) to varchar(100)?
You need to use ALTER TABLE command along with MODIFYThe syntax is as followsALTER TABLE yourTableName MODIFY COLUMN yourColumnName varchar(100) NOT NULL;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table syntaxOfAlterCommandDemo -> ( -> UserId int, -> UserName varchar(30), -> UserAge int, -> UserCityName varchar(50) -> ); Query OK, 0 rows affected (0.51 sec)Let us check the description of the table.The query is as followsmysql> desc syntaxOfAlterCommandDemo;The following is the output+--------------+-------------+------+-----+---------+-------+ | Field | Type ...
Read MoreHow to SELECT all values from a table only once if they're duplicated?
You can use distinct keyword to select all values from a table only once if they are repeated.The syntax is as followsselect distinct yourColumnName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table displayOnlyDistinctValue -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(100), -> UserAge int -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command.The query is as follows.mysql> insert into displayOnlyDistinctValue(UserName, UserAge) values('Larry', 23); Query OK, 1 row affected ...
Read MoreGet the second last row of a table in MySQL?
You need to use ORDER BY clause to get the second last row of a table in MySQL.The syntax is as follows.select *from yourTableName order by yourColumnName DESC LIMIT 1, 1;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table secondLastDemo -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(10) -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command.The query is as follows.mysql> insert into secondLastDemo(StudentName) values('Larry'); Query OK, 1 row affected (0.15 ...
Read MoreHow to find strings with a given prefix in MySQL?
You can use LIKE operator to find strings with a given prefix.The syntax is as followsselect *from yourTableName where yourColumnName LIKE 'yourPrefixValue%';To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table findStringWithGivenPrefixDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserMessage text -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into findStringWithGivenPrefixDemo(UserMessage) values('Hi Good Morning !!!'); Query OK, 1 row affected (0.17 sec) mysql> insert into findStringWithGivenPrefixDemo(UserMessage) values('Hey ...
Read MoreHow to set all values in a single column MySQL Query?
To set all values in a single column MySQL query, you can use UPDATE command.The syntax is as follows.update yourTableName set yourColumnName =yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows.mysql> create table setAllValuesDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20), -> Amount int -> ); Query OK, 0 rows affected (0.64 sec)Now you can insert some records in the table using insert command.The query is as follows.mysql> insert into setAllValuesDemo(Name, Amount) values('John', 2345); Query OK, 1 row affected ...
Read MoreHow to get file extension of file as a result of MySQL query?
In order to get file extension of file as a result of SQL query, you can use SUBSTRING_INDEX().The syntax is as followsselect substring_index(yourColumnName, '.', -1) as anyAliasName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table getFileExtensionDemo -> ( -> File_Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> File_Name text -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into getFileExtensionDemo(File_Name) values('John.AllMySQLConcept.doc'); Query OK, 1 row affected (0.17 sec) mysql> ...
Read MoreUpdating a MySQL column that contains dot (.) in its name?
If the MySQL column contains dot (.) in its name, then you need to use backticks around the column name. To understand the above concept, let us create a table. The query to create a table is as followsmysql> create table UpdateDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> `User.FirstName.LastName` varchar(60) -> ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into UpdateDemo(`User.FirstName.LastName`) values('John Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into UpdateDemo(`User.FirstName.LastName`) values('Adam Smith'); Query OK, ...
Read MoreHow to select from MySQL table A that does not exist in table B?
You can use IN operator to select from one table that does not exist in another. To understand the above syntax, let us create a table.The first table name is A and second table name is B. The query to create a table is as followsmysql> create table A -> ( -> Value int -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command.The query is as followsmysql> insert into A values(10); Query OK, 1 row affected (0.23 sec) mysql> insert into A values(20); Query OK, 1 ...
Read More