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 516 of 546
Returning 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 MoreCan we use str_replace in MySQL?
The str_replace version in MySQL is the replace() function. Let us first create a table to understand the function −mysql> create table StringReplaceDemo −> ( −> Id int, −> URL varchar(200) −> ); Query OK, 0 rows affected (0.38 sec)Insert some records in the table with the help of insert command. The query is as follows −mysql> insert into StringReplaceDemo values(1001, 'https://www.google.co.in'); Query OK, 1 row affected (0.09 sec) mysql> insert into StringReplaceDemo values(1002, 'https://www.facebook.com'); Query OK, 1 row affected (0.11 sec) mysql> insert into StringReplaceDemo ...
Read MoreOrder strings by length of characters IN mYsql?
You can order by length of characters with the help of CHAR_LENGTH() function from MySQL. The function returns the number of characters i.e. 4 for the following string −AMITTo order strings by length of characters, the following is the syntax −select *from yourTableName order by CHAR_LENGTH(yourColumnName);To understand the above concept, let us first create a table. The following is the query to create a table −mysql> create table OrderByCharacterLength −> ( −> BookName varchar(200) −> ); Query OK, 0 rows affected (1.97 sec)Insert some records in the table with the help of insert command. The query is ...
Read MoreHow to query between two dates in MySQL?
You can query between dates with the help of BETWEEN statement. The syntax is as follows −select *from yourTableName where yourColumnName between ‘yourStartingDate’ and curdate().Use curdate() or now(), both these functions will work. To understand the above syntax, let us create a table −mysql> create table BetweenDateDemo −> ( −> StartDate datetime −> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table with the help of the following query −mysql> insert into BetweenDateDemo values(date_add(now(), interval -1 year)); Query OK, 1 row affected (0.11 sec) mysql> insert ...
Read MoreHow to return the nth record from MySQL query?
To get the nth record from MySQL query, you can use LIMIT. The syntax is as follows −select *from yourTableName order by yourColumnName limit n, 1;To understand the above syntax, let us create a table. The following is the query to create a table −mysql> create table NthRecordDemo −> ( −> Id int, −> Name varchar(200) −> ); Query OK, 0 rows affected (0.92 sec)Insert some records in the table using the following query −mysql> insert into NthRecordDemo values(100, 'John'); Query OK, 1 row affected (0.09 sec) ...
Read MoreHow to perform Increment in MySQL Update?
To update values incrementally in MySQL, you need to create a variable with the help of SET command. The syntax to create a variable is as follows −set @anyVariableName := 0;To update value, you need to use UPDATE command. Let us begin with creating a table. The query to create a table −mysql> create table UpdateValueIncrementally −> ( −> ProductId int −> ); Query OK, 0 rows affected (0.90 sec)Insert records in the table with the help of select statement. The query is as follows −mysql> insert into UpdateValueIncrementally values(10); Query ...
Read MoreSHOW TABLE statement with multiple LIKE values in MySQL?
You can use WHERE clause and OR operator to show table with multiple LIKE. The syntax is as follows:show table from yourDatabaseName where tables_in_yourDatabaseName Like ‘%anyTableName%’ or tables_in_yourDatabaseName Like ‘%anyTableName2%’ or tables_in_yourDatabaseName Like ‘%anyTableName3%’ . . . . or tables_in_yourDatabaseName Like ‘%anyTableNameN%’In the above syntax, only the table name in the database is displayed.Here the database ‘test’ and the tables in the same database is considered. The query to show tables with multiple LIKE is as follows -mysql> show tables from test -> where tables_in_test like '%userrole%' -> or tables_in_test like '%view_student%' -> or tables_in_test like '%wholewordmatchdemo%';The following is the ...
Read MoreHow to make MySQL's NOW() and CURDATE() functions use UTC?
To make MySQL’s NOW() and CURDATE() functions use UTC, you need to write my.cnf file. Write the below instruction in my.cnf −[mysqld_safe] timezone = UTCFirstly, reach the directory with the help of the following query −mysql> select @@datadir;The following is the output −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Now reach the directory for which the link ...
Read More