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 189 of 341
MySQL query to select three highest values and sort alphabetically on the basis of corresponding column with name
For this, you can use the ORDER BY clause. Let us first create a table −mysql> create table DemoTable ( Name varchar(40), Score int ); Query OK, 0 rows affected (1.11 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 45); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values('Bob', 98); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('David', 78); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Mike', 96); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Carol', 43); ...
Read MoreAdding records with REPLACE INTO to mimic DELETE and INSERT
You can use REPLACE INTO that works like DELETE + INSERT. Let us first create a table −mysql> create table DemoTable ( Id int, FirstName varchar(50) ); Query OK, 0 rows affected (0.60 sec)Following is the query to create a unique index −mysql> alter table DemoTable add unique id_index(Id); Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command. Since we have added duplicate records, the new record gets added i.e. replaced with the same Id with the previous record −mysql> replace into DemoTable values(100, 'Chris'); Query ...
Read MoreA single MySQL query to find the highest and lowest among two tables?
To find the highest and lowest from two tables, use MAX() and MIN(). Since the results are to be displayed from two tables, you need to use UNION. Let us first create a table −mysql> create table DemoTable1 ( UniqueId int NOT NULL AUTO_INCREMENT PRIMARY KEY, Score1 int ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1(Score1) values(56); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1(Score1) values(76); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1(Score1) values(65); Query OK, 1 row affected ...
Read MoreCan we skip column when inserting into MySQL?
If your first column is AUTO_INCREMENT, then you can skip the column and place the value NULL. Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(100), StudentAge int ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. Here, we have skipped the first column, since it is AUTO_INCREMENT −mysql> insert into DemoTable values(NULL, 'Robert', 21); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(NULL, 'Sam', 22); Query OK, 1 row affected (0.18 sec) mysql> insert into ...
Read MoreHow to optimize many SELECTs in a single table in MySQL?
To optimize many SELECTs, use it once and apply IN() to fetch multiple values. Let us first create a table −mysql> create table DemoTable ( Id int, Name varchar(100), Age int ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 'Chris', 23); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable values(2, 'David', 21); Query OK, 1 row affected (0.56 sec) mysql> insert into DemoTable values(3, 'Mike', 24); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values(4, 'Robert', 22); Query ...
Read MoreMySQL query to count the duplicate ID values and display the result in a separate column
To count the duplicate ID values, use aggregate function COUNT() and GROUP BY. Let us first create a table −mysql> create table DemoTable ( Id int, Name varchar(100) ); Query OK, 0 rows affected (1.30 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(50, 'Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(51, 'David'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(51, 'Mike'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(50, 'Sam'); Query OK, 1 row affected (0.17 sec)Display all records ...
Read MoreFetch maximum ID value from the first table and insert to all the IDs in another table with MySQL INSERT INTO select?
Let us first create a table −mysql> create table DemoTable1 ( Id int, Name varchar(100) ); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(1001, 'Chris'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1 values(999, 'Robert'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values(1003, 'Mike'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1 values(1002, 'Sam'); 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 ...
Read MoreCalculate average of column values and display the result with no decimals in MySQL
For this, you can use round() along with avg(). Let us first create a table −mysql> create table DemoTable ( Score int ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(98); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(97); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(91); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(86); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values(45); Query OK, 1 row affected (0.12 sec)Display all ...
Read MoreEasiest way to copy values of one column to a new table in MySQL?
For this, use AS select statement. Let us first create a table −mysql> create table DemoTable1 ( Score int ); Query OK, 0 rows affected (1.22 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1 values(98); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1 values(91); Query OK, 1 row affected (0.24 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-------+ | Score | +-------+ | 89 | ...
Read MoreFind minimum score from the entire four columns of a table in MySQL
To find a minimum score from the entire four columns, use MySQL LEAST() function. Let us first create a table −mysql> create table DemoTable( Score1 int, Score2 int, Score3 int, Score4 int ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(88, 76, 45, 56); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(99, 78, 87, 34); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(34, 32, 56, 98); Query OK, 1 row affected (0.44 sec)Display all records from ...
Read More