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 252 of 341
How to count the number of occurrences of a specific value in a column with a single MySQL query?
For this, you can use GROUP BY clause along with IN(). Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.87 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(Name) values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(Name) values('Chris'); Query ...
Read MoreMySQL queries to update date records with NULL values
You can use IFNULL() for this. Let us first create a table −mysql> create table DemoTable -> ( -> added_date date, -> updated_date date -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-10', '2019-06-01'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-05-19', NULL); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(NULL, '2019-09-05'); Query OK, 1 row affected (0.18 sec)Display all records from the table using ...
Read MoreRetrieving MySQL Database structure information from Java?
Use DatabaseMetaData class to retrieve MySQL database structure. In this example, we will display all the table names of database “web” using Java with the help of getMetaData().Following is the Java code −Exampleimport java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import com.mysql.jdbc.DatabaseMetaData; public class getDatabaseInformationDemo { public static void main(String[] args) { Connection con = null; try { con = DriverManager.getConnection("jdbc:mysql://localhost:3306/web?useSSL=false", "root", "123456"); DatabaseMetaData information = (DatabaseMetaData) con.getMetaData(); String allTableName[] = { "TABLE" ...
Read MoreAdd a new column and index to an existing table with ALTER in a single MySQL query?
To add a new column to an existing table, use ADD. With that, to add a new index, use the ADD INDEX(). 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;This will produce the following output −+-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | Id ...
Read MoreExecute operations (plus, minus, multiply, divide) while updating a MySQL table?
Following is the syntax executing the plus (+) operator −update yourTableName set yourColumnName3=(yourColumnName1+yourColumnName2)The above syntax is only for plus operator. You need to change symbol like -, *, / for other operations. Let us first create a table −mysql> create table DemoTable -> ( -> Number1 int, -> Number2 int, -> AddResult int, -> MinusResult int, -> MultiplyResult int, -> DivideResult int -> ); Query OK, 0 rows affected (0.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Number1, Number2) values(40, 20); Query OK, 1 row affected (0.16 ...
Read MoreHow to find a value between range in MySQL?
For this, use BETWEEN operator in MySQL. Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Start int, -> End int -> ); Query OK, 0 rows affected (0.91 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Start, End) values(100, 200); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Start, End) values(400, 500); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(Start, End) values(210, 350); Query OK, 1 row affected (0.11 sec)Display all ...
Read MoreHow to correctly use INSERT INTO ... SELECT in MySQL to avoid Error 1064?
Let us first create a table −mysql> create table DemoTable1 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> FirstName varchar(100) -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1(FirstName) values('John'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1(FirstName) values('Chris'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+----+-----------+ | Id | FirstName | +----+-----------+ | 1 | John ...
Read MoreHow can we grant a user to access all stored procedures in MySQL?
Let us first display all users and host from the table MySQL.user −mysql> select user, host from Mysql.user;This will produce the following output −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Charlie | % | | Robert | % | | User2 | % ...
Read MoreWhy does MySQL evaluate "TRUE or TRUE and FALSE" to true?
MySQL evaluates “TRUE or TRUE and FALSE” to true because AND has the highest priority than OR i.e. AND is evaluated before OR.The MySQL evaluates the above statement like this. The AND operator gets evaluated first −(TRUE or (TRUE AND FALSE))The statement (TRUE AND FALSE) gives the result FALSE. Then the second statement evaluates like this −(TRUE or FALSE)The above statement gives the result TRUE.Let us implement one by one −mysql> select (TRUE AND FALSE); +------------------+ | (TRUE AND FALSE) | +------------------+ | 0 | ...
Read MoreGet the count of duplicate values from a single column in MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10); Query OK, ...
Read More