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 27 of 341
Can we get records “Jone Deo” or “Deo Jone” with a single MySQL query?
For this, you can use the LIKE clause. Let us first create a table −mysql> create table DemoTable -> ( -> ClientName varchar(100) -> ); Query OK, 0 rows affected (0.85 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Smith'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Smith John'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Jone Deo'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Deo Jone'); Query OK, 1 row affected (0.14 sec) mysql> ...
Read MoreHow to add column and index in a single MySQL query?
Use ALTER for this with ADD. Following is the syntax −alter table yourTableName add yourColumnName DATETIME DEFAULT NOW(), add index(yourColumnName);Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(100), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.69 sec)Let us check the description of the table −mysql> desc DemoTable;OutputThis will produce the following output −+-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | Id | int(11) ...
Read MoreHow to search on a MySQL varchar column using a number?
Use INSERT() function from MySQL. It has the following parameters −ParameterDescriptionstrString to be modifiedpositionPosition where to insert str2numberNumber of characters to replacestr2String to insert into strLet us first create a table −mysql> create table DemoTable -> ( -> Code varchar(100) -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('958575/98') ; Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('765432/99'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('983456/91'); Query OK, 1 row affected (0.15 sec)Display all ...
Read MoreOrder by desc except a single value in MySQL
Use ORDER BY and set DESC to order by desc. However, to get all the values except a single value, use the not equal operator.Let us first create a table −mysql> create table DemoTable -> ( -> Name varchar(100) -> ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert ...
Read MoreMySQL query to first set negative value in descending order and then positive value in ascending order
For this, you can use UNION. Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (1.17 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(-9); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(-190); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(190); Query OK, 1 row affected ...
Read MoreMySQL query to increment one of the column values
Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> Score int -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name, Score) values('John', 68); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(Name, Score) values('Carol', 98); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable(Name, Score) values('David', 89); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Name, Score) values('Robert', 67); Query OK, 1 row affected ...
Read MoreGiven a column name how can I find which tables in a MySQL database contain that column?
Use the COLUMN_NAME to find which table in a database contains a specific column. Let us first create a table −mysql> create table DemoTable -> ( -> CustomerId int, -> CustomerName varchar(20), -> CustomerCountryName varchar(100) -> ); Query OK, 0 rows affected (1.05 sec)Following is the query to find in which tables a specific column “'CustomerCountryName'” is present −mysql> select *from information_schema.columns WHERE COLUMN_NAME = 'CustomerCountryName';OutputThis will produce the following output −+---------------+--------------+--------------+---------------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+-----------------+--------------+------------+-------+---------------------------------+----------------+-----------------------+-------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME |ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE |CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH |NUMERIC_PRECISION ...
Read MoreHow to order MySQL rows by multiple columns?
Let us first create a table −mysql> create table DemoTable -> ( -> FirstName varchar(20), -> LastName varchar(20) -> ); Query OK, 0 rows affected (1.44 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John', 'Smith'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable values('Chris', 'Brown'); Query OK, 1 row affected (0.37 sec) mysql> insert into DemoTable values('Carol', 'Taylor'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('David', 'Miller'); Query OK, 1 row affected (0.24 sec)Display all records from the table using ...
Read MoreSort a column ignoring a specific word in MySQL
Let us first create a table −mysql> create table DemoTable -> ( -> Name text -> ); Query OK, 0 rows affected (1.31 sec)Insert some records in the table using insert command. Here, we have inserted a name with a specific word “name”, which we need to ignore −mysql> insert into DemoTable values('John 7'); Query OK, 1 row affected (0.65 sec) mysql> insert into DemoTable values('John 6'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable values('John 9'); Query OK, 1 row affected (0.33 sec) mysql> insert into DemoTable values('name John 3'); ...
Read MoreHow to search a MySQL table for a specific string?
Use equal to operator for an exact match −select *from yourTableName where yourColumnName=yourValue;Let us first create a table −mysql> create table DemoTable -> ( -> FirstName varchar(100), -> LastName varchar(100) -> ); Query OK, 0 rows affected (0.70 secInsert some records in the table using insert command −mysql> insert into DemoTable values('John', 'Smith'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('John', 'Doe'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Chris', 'Brown'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Carol', 'Taylor'); Query OK, 1 row affected ...
Read More