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
MySQL Articles
Page 265 of 355
Auto insert values into a MySQL table in a range?
For this, you can create a stored procedure. Let us first create a table.mysql> create table DemoTable -> ( -> Value int -> ); Query OK, 0 rows affected (0.55 sec)Following is the query to create a stored procedure to auto insert values to a table from range 10 to 20 −mysql> DELIMITER // mysql> CREATE PROCEDURE AutoInsertValuesToTable() -> BEGIN -> DECLARE startingRange INT DEFAULT 10; -> WHILE startingRange INSERT DemoTable(Value) VALUES (startingRange ); -> SET startingRange = startingRange + 1; -> ...
Read MoreDetermine what a MySQL DB's charset is set to
Let’s say we are creating a database “web” −mysql> SHOW CREATE DATABASE web;This will produce the following output displaying the default charset as well −+----------+-----------------------------------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------------------------------+ | web | CREATE DATABASE `web` /*!40100 DEFAULT CHARACTER SET utf8 COLLATEutf8_unicode_ci */ | +----------+-----------------------------------------------------------------------------------------+ 1 row in set (0.03 sec)If you want to know ...
Read MoreAveraging a total from a Score column in MySQL with the count of distinct ids?
You can use DISTINCT along with COUNT(). Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> Score int -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10, 90); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(10, 190); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(11, 230); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(11, 130); Query OK, 1 row affected (0.17 ...
Read MoreHow 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 More