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
Database Articles
Page 328 of 547
Parse a string to get a number from a large string separated by underscore
Let us first create a table −mysql> create table DemoTable1961 ( Title text ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1961 values('You_can_remove_the_string_part_only-10001-But_You_can_not_remove_the_numeric_parts'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1961;This will produce the following output −+------------------------------------------------------------------------------------+ | Title ...
Read MoreA single MySQL query to combine strings from many rows into a single row and display the corresponding User Id sum in another column?
For this, you can use GROUP_CONCAT(). Use SUM() to add the User Id. Let us first create a table −mysql> create table DemoTable1960 ( StudentId int, StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1960 values(100, 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1960 values(101, 'Bob'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1960 values(102, 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1960 values(103, 'Mike'); Query OK, 1 row affected (0.00 sec)Display ...
Read MoreCreate a dynamic table name from current year in MySQL like 2019
To create a table name like year (2019), use PREPARE statement. Let us first create a table −mysql> create table DemoTable1959 ( UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1959 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1959 values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1959 values('Bob'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1959;This will produce the following output −+----------+ | UserName ...
Read MoreDisplay auto increment user id sequence number to begin from 001 in MySQL?
For this, use ZEROFILL and alter the table to begin from the same sequence −alter table yourTableName change yourColumnName yourColumnName int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY;To understand the above syntax, let us first create a table −mysql> create table DemoTable1958 ( UserId int, UserName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Here is the query to alter generated sequence number to begin from 001:mysql> alter table DemoTable1958 change UserId UserId int(3) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0Let ...
Read MoreDivide a column to get monthly salary of employees in a MySQL Query?
Let us first create a table −mysql> create table DemoTable1957 ( EmployeeId int, EmployeeName varchar(20), EmployeeSalary int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1957 values(1, 'Chris', 240000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(2, 'Bob', 120000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(3, 'David', 180000); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1957 values(4, 'Mike', 650000); Query OK, 1 row affected (0.00 sec)Display all records from the table using ...
Read MoreSelect a column if condition is met in MySQL to fetch records from current date and current date + 1
Let us first get the current date −mysql> select curdate();This will produce the following output −+------------+ | curdate() | +------------+ | 2019-12-15 | +------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable1956 ( ProductId int, ProductName varchar(20), CustomerName varchar(20), ShippingDate date ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1956 values(101, 'Product-1', 'Sam', '2019-10-11'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1956 values(102, 'Product-2', 'Carol', '2018-12-01'); Query OK, 1 row affected (0.00 sec) ...
Read MoreMySQL - How can I fix an auto increment field with deleted rows from 1,2,3,4,5 to 1,3,5). Now we want it to be 1,2,3
Let us first create a table −mysql> create table DemoTable1955 ( UserId int NOT NULL AUTO_INCREMENT , PRIMARY KEY(UserId) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1955 values(); Query OK, 1 row affected (0.00 sec)Display ...
Read MoreDisplay custom text in a new column on the basis of null values in MySQL?
Let us first create a table −mysql> create table DemoTable1953 ( StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1953 values('Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1953 values(NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1953 values('David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1953 values(NULL); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1953;This will produce the following output −+-------------+ | ...
Read MoreSplit float value in two columns of a MySQL table?
To split float value in two columns, the first column will have a value before decimal. The second column will have a value after decimal. For this, you can use SUBSTRING_INDEX() along with CAST(). Let us first create a table −mysql> create table DemoTable1951 ( Value1 varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1951 values('100.50'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1951 values('70.90'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1951 values('1000.55'); Query OK, 1 row affected ...
Read MoreCombine SUM and FORMAT in MySQL to format the result
Let us create a table −mysql> create table DemoTable1950 ( Amount float ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1950 values(45.60); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1950 values(101.78); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1950 values(75.90); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1950 values(89.45); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1950;This will produce the following output −+--------+ | Amount ...
Read More