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 34 of 355
Regex to find string and next character in a comma separated list - MySQL?
To search in a comma separated list, use MySQL find_in_set(). The usage of Regex for this purpose isn’t required here. The syntax is as follows −select *from yourTableName where find_in_set(anyValue, yourColumnName);Let us create a table −mysql> create table demo17 −> ( −> id int not null auto_increment primary key, −> first_name varchar(50), −> value text −> ); Query OK, 0 rows affected (1.81 sec)Insert some records into the table with the help of insert command −mysql> insert into demo17(first_name, value) values('John', '50'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo17(first_name, value) values('David', ''); Query OK, 1 ...
Read MoreHow can I extract minute from time in BigQuery in MySQL?
Extract using extract() method along with cast(). Following is the syntax −select extract(minute from cast(yourColumnName as time)) as anyAliasName from yourTableName;Let us create a table −mysql> create table demo15 −> ( −> value time −> ); Query OK, 0 rows affected (2.11 sec)Insert some records into the table with the help of insert command −mysql> insert into demo15 values('10:30:45'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo15 values('06:34:55'); Query OK, 1 row affected (0.17 sec)Display records from the table using select statement −mysql> select *from demo15;This will produce the following output −+----------+ | value ...
Read MoreNeed help in deleting duplicate columns from a table in MySQL?
To delete duplicate columns, use DELETE with INNER JOIN. Following is the syntax −delete tbl1 from yourTableName anyAliasName1 inner join yourTableName anyAliasName2 where yourCondition1 and yourCondition2Let us create a table −mysql> create table demo14 −> ( −> id int not null auto_increment primary key, −> name varchar(30) −> ); Query OK, 0 rows affected (1.89 sec)Insert some records into the table with the help of insert command −mysql> insert into demo14(name) values('John'); Query OK, 1 row affected (0.14 sec) mysql> insert into demo14(name) values('David'); Query OK, 1 row affected (0.18 sec) mysql> insert into demo14(name) values('David'); Query OK, ...
Read MoreUpdate a table in MySQL and display only the initials name in a new column
To get the initial, use the concept of left() along with substring_index().Let us create a table −mysql> create table demo13 −> ( −> full_name varchar(100), −> short_name varchar(20) −> ); Query OK, 0 rows affected (1.18 sec)Insert some records into the table with the help of insert command −mysql> insert into demo13(full_name) values('John Smith'); Query OK, 1 row affected (0.27 sec) mysql> insert into demo13(full_name) values('David Miller'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo13(full_name) values('Chris Brown'); Query OK, 1 row affected (0.28 sec)Display records from the table using select statement −mysql> select *from demo13;This ...
Read MoreAdd some months to current date using Java with MySQL?
Following is the syntax to add months using INTERVAL with Java − MySQL.String query; query = "insert into yourTableName values(curdate()+interval howManyNumberOfMonths month)";Following is the current date −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2020-10-25 | +------------+ 1 row in set (0.00 sec)Let us create a table −mysql> create table demo12 −> ( −> created_date date −> ); Query OK, 0 rows affected (1.84 sec)Here, I am going to add 2 months to the current date from Java. Now, date will be inserted into the table 2020−12−25.The Java code is as followsExampleimport java.sql.Connection; import java.sql.DriverManager; import com.mysql.jdbc.Statement; public ...
Read MoreHow to get substring results from a table with file location recordsi in MySQL?
To fetch sub strings, use the substr() method in MySQL as in the below syntax −select substr(yourColumnName, startIndex, endIndex) from yourTableName limit anyValue; select substr(yourColumnName, startIndex+endIndex) from yourTableName limit anyValue;Let us create a table −mysql> create table demo11 −> ( −> id int NOT NULL AUTO_INCREMENT PRIMARY KEY, −> fileLocation text −> ); Query OK, 0 rows affected (2.60 sec)Insert some records into the table with the help of insert command −mysql> insert into demo11(fileLocation) values('E:/users/program/sample.sql'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo11(fileLocation) values('E:/users/data/db.sql'); Query OK, 1 row affected (0.32 sec) mysql> insert ...
Read MoreIs there a way to make a list from a MySQL table in Java?
Yes, for this, use the concept of ArrayList in Java. The syntax is as follows −ArrayList anyVariableName= new ArrayList();Let us create a table −mysql> create table demo10 −> ( −> id int not null auto_increment primary key, −> name varchar(20) −> ); Query OK, 0 rows affected (2.19 sec)Insert some records into the table with the help of insert command −mysql> insert into demo10(name) values('John'); Query OK, 1 row affected (0.23 sec) mysql> insert into demo10(name) values('Bob'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo10(name) values('David'); Query OK, 1 row affected (0.13 sec)Display records from ...
Read MoreGroup result in MySQL and show on list?
For this, use GROUP BY along with ORDER BY −select yourColumnName, count(*) as anyAliasName from yourTableName group by yourColumnName order by yourColumnName;Let us create a table −mysql> create table demo7 −> ( −> id int NOT NULL AUTO_INCREMENT, −> first_name varchar(50) −> , −> primary key(id) −> ); Query OK, 0 rows affected (1.22 sec)Insert some records into the table with the help of insert command −mysql> insert into demo7(first_name) values('John'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo7(first_name) values('David'); Query OK, 1 row affected (0.22 sec) mysql> insert into demo7(first_name) values('John'); Query OK, 1 row affected ...
Read MoreMySQL Error ERROR 1099 (HY000): Table was locked with a READ lock and can't be updated
To get rid of LOCK TABLES query, you need to use UNLOCK TABLES.Let us create a table −mysql> create table demo6 −> ( −> country_name varchar(100 −> ) −> ); Query OK, 0 rows affected (1.51 sec)Insert some records into the table with the help of insert command −mysql> insert into demo6 values('US'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo6 values('UK'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo6 values('AUS'); Query OK, 1 row affected (0.11 sec)Display records from the table using select statement −mysql> select *from demo6;This will produce the ...
Read MoreLimit total number of results across tables in MySQL?
For this, you can use UNION ALL along with LIMIT concept. For our example, we will create three tables.Let us create the first table −mysql> create table demo3 −> ( −> value int −> ); Query OK, 0 rows affected (1.39 sec)Insert some records into the table with the help of insert command −mysql> insert into demo3 values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into demo3 values(20); Query OK, 1 row affected (0.08 sec) mysql> insert into demo3 values(30); Query OK, 1 row affected (0.08 sec)Display records from the table using select statement −mysql> select ...
Read More