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
Database Articles
Page 110 of 546
Add values of two columns considering NULL values as zero in MySQL
For this, use COALESCE() function from MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.51 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 200); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values(NULL, 90); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(NULL, NULL); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(600, NULL); Query OK, 1 row affected (0.12 sec)Display all records from ...
Read MoreHow can I get the number of times a specific word appears in a column with MySQL?
For this, you can use COUNT() function. Let us first create a table −mysql> create table DemoTable -> ( -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EmployeeName varchar(100) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeName) values('John'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable(EmployeeName) values('Carol'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(EmployeeName) values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(EmployeeName) values('Carol'); Query OK, 1 row affected ...
Read MoreFind a specific column in all the tables in a database?
For this, use COLUMN_NAME and set LIKE with that specific column name. Let us find a specific column in an unknown table in a database −mysql> SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE column_name LIKE '%StudentName%' -> AND table_schema = 'web';OutputThis will produce the following output −+-------------------+-------------+-----------+-------------+----------------+ | TABLE_NAME | COLUMN_NAME | DATA_TYPE | IS_NULLABLE |COLUMN_DEFAULT | +-------------------+-------------+-----------+-------------+----------------+ | demotable215 | StudentName | varchar | YES | NULL | | demotable221 | StudentName | varchar ...
Read MoreMySQL query to select top 10 records?
To select top 10 records, use LIMIT in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> PageNumber text -> ); Query OK, 0 rows affected (2.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Page-1'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values('Page-2'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Page-3'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('Page-4'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Page-5'); Query OK, ...
Read MoreConverting table from MyISAM to INNODB in MySQL?
For this, use ALTER command. Let us first create a table. The default engine is set as“MYISAM” −mysql> create table DemoTable -> ( -> ClientId int NOT NULL AUTO_INCREMENT, -> ClientName varchar(100), -> ClientAge int, -> ClientCountryName varchar(100), -> isMarried boolean, -> PRIMARY KEY(ClientId) -> )ENGINE=MyISAM; Query OK, 0 rows affected (0.67 sec)Following is the query to convert table from MyISAM to INNODB −mysql> alter table DemoTable ENGINE=InnoDB; Query OK, 0 rows affected (1.97 sec) Records: 0 Duplicates: 0 Warnings: 0Let us now check the status of table −mysql> show create table DemoTable;OutputThis will produce the following output displaying the ...
Read MoreCan 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 More