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 184 of 355
How to add a column from a select query but the value from the new column will be the row count of the MySQL select query?
For this, you can use MySQL row_number(). Let us first create a table −mysql> create table DemoTable1342 -> ( -> Score int -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1342 values(80); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1342 values(98); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1342 values(78); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1342 values(89); Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −mysql> select ...
Read MoreFetch the substring after last dot in MySQL
To fetch the substring after last dot, use substring_index(). Let us first create a table −mysql> create table DemoTable1341 -> ( -> Value varchar(60) -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1341 values('John.123.@gmail.com' ); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1341 values('Carol.Taylor.gmail') ; Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1341 values('C.MyFolder.Location') ; Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select * from DemoTable1341;This will produce the following ...
Read MoreMySQL random rows sorted by a specific column name?
Let us first create a table −mysql> create table DemoTable1339 -> ( -> Name varchar(30), -> Score int -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1339 values('Chris', 56); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable1339 values('Bob', 46); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1339 values('Adam', 78); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1339 values('John', 90); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1339 values('Carol', 98); Query OK, 1 ...
Read MoreCount values based on conditions and display the result in different columns with MySQL?
Let us first create a table −mysql> create table DemoTable1485 -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20), -> StudentSubject varchar(20) -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Chris', 'MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) ...
Read MoreUsing MySQL where clause and ordering by avg() to find the average of duplicate individual elements
For this, use having clause instead of where. Let us first create a table −mysql> create table DemoTable1338 -> ( -> Name varchar(10), -> Score int -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. Here, we have inserted duplicate names with scores −mysql> insert into DemoTable1338 values('Chris', 8); Query OK, 1 row affected (0.80 sec) mysql> insert into DemoTable1338 values('Bob', 4); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1338 values('Bob', 9); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1338 values('Chris', 6); ...
Read MoreFetch maximum value from a column with values as string numbers like Value440, Value345, etc. in SQL
For this, you can use MAX() along with substring(). Let us first create a table −mysql> create table DemoTable1337 -> ( -> Value varchar(50) -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1337 values('Value400'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1337 values('Value345'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1337 values('Value567'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1337 values('Value489'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement ...
Read MoreSelect different fields in MySQL even if a field is set to null?
For this, you can use COALESCE(). Let us first create a table −mysql> create table DemoTable1336 -> ( -> FirstName varchar(20) -> , -> SecondName varchar(20) -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1336 values('John', NULL); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1336 values(NULL, 'Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1336 values('David', 'Mike'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable1336;This ...
Read MoreSelect entries with timestamp after X time in MySQL
Let us first create a table −mysql> create table DemoTable1335 -> ( -> ArrivalTime datetime -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command. We have inserted date time records here −mysql> insert into DemoTable1335 values('2019-09-19 22:54:00'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:59:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:56:00'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:52:00'); Query OK, 1 row affected (0.11 sec)Display all records from the table using ...
Read MoreInsert values in two tables with a single stored procedure call in MySQL
Following is the syntax to insert values in two tables with a stored procedure −DELIMITER // CREATE PROCEDURE yourProcedureName(anyVariableName int) BEGIN insert into yourTableName1(yourColumnName1) values(yourVariableName); insert into yourTableName2(yourColumnName2) values(yourVariableName); END //Let us first create a table −mysql> create table DemoTable1 -> ( -> StudentScore int -> ); Query OK, 0 rows affected (0.58 sec)Following is the second table −mysql> create table DemoTable2 -> ( -> PlayerScore int -> ); Query OK, 0 rows affected (0.52 sec)Here is the query to create a stored procedure and insert values in two tables ...
Read MoreMySQL row declarations for ZF?
The ZF stands for ZEROFILL i.e. row declarations for zero fill Let us first create a table. Here, we have set the int field size to be 10 −mysql> create table DemoTable1332 -> ( -> Number int(10) ZEROFILL NOT NULL DEFAULT 0 -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1332 values(); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1332 values(1); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1332 values(10); Query OK, 1 row affected (0.10 sec) mysql> insert ...
Read More