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 151 of 341
MySQL query to remove text between square brackets?
Let us first create a table −mysql> create table DemoTable -> ( -> Name text -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John [John] Smith'); Query OK, 1 row affected (0.30 sec) mysql> insert into DemoTable values('[Carol] Carol Taylor'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('David [Miller] Miller'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output−+-----------------------+ | Name ...
Read MoreInsert multiple data using SET clause in MySQL?
Let us first create a table −mysql> create table DemoTable1544 -> ( -> Id int , -> Name varchar(20) -> ); Query OK, 0 rows affected (2.47 sec)Insert some records in the table using insert command. We have inserted multiple data using SET clause −mysql> insert into DemoTable1544 set Id=101, Name='John Doe'; Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1544 set Id=102, Name='Adam Smith'; Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1544 set Id=103, Name='Chris Brown'; Query OK, 1 row affected (0.12 sec)Display all records from the table using select ...
Read MoreHow to replace a part of the string (domain name after @) using MySQL?
Let us first create a table −mysql> create table DemoTable -> ( -> EmailId varchar(30) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John123@example.com'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('John123@gmail.com'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values('John123@yahoo.com'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('John123@example.com'); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output ...
Read MoreMySQL query to count rows with mutual relation using JOIN?
For this, use aggregate function COUNT(*). Let us first create a table −mysql> create table DemoTable1543 -> ( -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1543 values(57, 60); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable1543 values(60, 68); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable1543 values(90, 98); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable1543 values(98, 90); Query OK, 1 row affected (0.20 sec)Display all records from ...
Read MoreHow to display the count from distinct records in the same row with MySQL?
For this, you can use GROUP_CONCAT(), COUNT() along with GROUP BY clause. Let us first create a table −mysql> create table DemoTable -> ( -> CompanyId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CompanyName varchar(20) -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(CompanyName) values('Amazon'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(CompanyName) values('Google'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable(CompanyName) values('Google'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(CompanyName) values('Microsoft'); Query OK, 1 ...
Read MoreFetch info with MySQL EXPLAIN KEYWORD?
Let us first create a table −mysql> create table DemoTable1541 -> ( -> EmployeeId int, -> EmployeeFirstName varchar(20) NOT NULL -> ); Query OK, 0 rows affected (0.94 sec)Here is the query to create an index on the column −mysql> create index emp_name_index on DemoTable1541(EmployeeFirstName); Query OK, 0 rows affected (0.75 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1541 values(1, 'Robert'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1541 values(2, 'Adam'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1541 ...
Read MoreHow to cast and update a numeric value from string column only where applicable in MySQL?
You can use the CEIL() function from MySQL. Let us first create a table. Here, we have taken the first column as VARCHAR −mysql> create table DemoTable -> ( -> Value varchar(20), -> UpdateValue int -> ); Query OK, 0 rows affected (1.08 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values('100'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Value) values('false'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Value) values('true'); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable(Value) values('1'); Query OK, 1 ...
Read MoreMySQL query to remove string from a column with values EMP1, EMP2, EMP3, etc.
To remove string from the values EMO1, EMP2, etc., you need to use RIGHT() along with LENGTH(). Let us first create a table −mysql> create table DemoTable1540 -> ( -> EmployeeCode varchar(20) -> ); Query OK, 0 rows affected (0.39 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1540 values('EMP9'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1540 values('EMP4'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1540 values('EMP8'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1540 values('EMP6'); Query OK, 1 row affected (0.12 sec)Display ...
Read MoreMySQL DATE function to return the difference between current date and joining date
At first, find the current date and get the difference between joining date and current date using the DATEDIFF().The current date is as follows −mysql> select curdate(); +------------+ | curdate() | +------------+ | 2019-10-26 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable -> ( -> JoiningDate varchar(40) -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10/10/1998'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('31/12/2010'); Query OK, 1 row affected (0.09 sec) ...
Read MoreCombine columns into rows with MySQL?
To combine columns into rows, use UNION ALL. Following is the syntax −Syntaxselect yourColumnName1 from yourTableName union all select yourColumnName2 from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Value1 int, -> Value2 int -> ); Query OK, 0 rows affected (0.88 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 200); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(500, 600); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will ...
Read More