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 197 of 341
Display records by first fixing the first two values in a column and then using DISTINCT to display other values in MySQL
Let us first create a table −mysql> create table DemoTable ( FirstName varchar(100) ); Query OK, 0 rows affected (0.96 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Mike'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David'); 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 −+-----------+ | FirstName | ...
Read MoreHow can I count unique records from a column in MySQL database?
For this, use aggregate function count(*) to count to GROUP BY to group. Let us first create a table −mysql> create table DemoTable ( UserName varchar(100), UserPostMessage text ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 'Hi'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('David', 'Hello'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Chris', 'Awesome'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('Chris', 'Amazing'); Query OK, 1 row affected (0.17 sec) mysql> insert ...
Read MoreMySQL query to display records ordered by DESC while skipping some?
Let us first create a table −mysql> create table DemoTable ( Id int, Name varchar(100) ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10, 'Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(1, 'David'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(4, 'Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(6, 'Sam'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(7, 'Mike'); Query OK, 1 row affected (0.10 sec) mysql> insert ...
Read MoreFind the next lowest number higher than a certain number in MySQL?
For this, use aggregate function MIN() along with WHERE clause. Let us first create a table −mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(11); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(9); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(12); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This ...
Read MoreMySQL query to change a string by displaying only the part of string after underscore?
Let us first create a table −mysql> create table DemoTable ( UserName varchar(100) ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Smith_John'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('Smith_Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('Smith_David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('Smith_Mike'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-------------+ | UserName ...
Read MoreMySQL query to find expiry date (record) from the next 2 days?
For this, you can use BETWEEN keyword. Let us first create a table −mysql> create table DemoTable ( ExpiryDate date ); Query OK, 0 rows affected (0.55 sec)Note − Let’s say the current date is 2019-08-18.Insert some records in the table using insert command −mysql> insert into DemoTable values('2018-01-21'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('2019-08-20'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable values('2018-08-20'); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable values('2019-08-21'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select ...
Read MoreMySQL query to find the average of rows with the same ID
Let us first create a table −mysql> create table DemoTable ( StudentId int, StudentMarks int ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1000, 78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(1001, 88); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(1000, 89); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(1000, 67); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(1000, 90); Query OK, 1 row affected (0.21 sec) mysql> insert ...
Read MoreHow can I create a MySQL boolean column and assign value 1 while altering the same column?
To assign value 1 while altering, use the MySQL DEFAULT. This will itself enter 1 if nothing is inserted in the same column while using the INSERT command.Let us first create a table −mysql> create table DemoTable ( isAdult int ); Query OK, 0 rows affected (1.39 sec)Following is how you can assign value 1 default to the already created column −mysql> alter table DemoTable CHANGE isAdult isAdult BOOLEAN DEFAULT '1' NOT NULL; Query OK, 0 rows affected (1.18 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable values(); ...
Read MoreMySQL query to find the number of rows in the last query
For this, use the FOUND_ROWS in MySQL. Following is the syntax −SELECT SQL_CALC_FOUND_ROWS TABLE_NAME FROM `information_schema`.tables WHERE TABLE_NAME LIKE "yourValue%" LIMIT yourLimitValue;Here, I am using the database ‘web’ and I have lots of tables, let’s say which begins from DemoTable29. Let us implement the above syntax to fetch only 4 of such rows −mysql> SELECT SQL_CALC_FOUND_ROWS TABLE_NAME FROM `information_schema`.tables WHERE TABLE_NAME LIKE "DemoTable29%" LIMIT 4;This will produce the following output −+--------------+ | TABLE_NAME | +--------------+ | demotable29 | | demotable290 | | demotable291 | | demotable292 | +--------------+ 4 rows in set (0.01 sec)Here is the query ...
Read MoreGet only the date from datetime in MySQL?
To get only the date from DateTime, use the date format specifiers −%d for day %m for month %Y for yearLet us first create a table −mysql> create table DemoTable ( AdmissionDate datetime ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-07-21 12:34:56'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2016-08-18 10:00:02'); Query OK, 1 row affected (0.51 sec) mysql> insert into DemoTable values('2018-01-03 11:02:20'); Query OK, 1 row affected (0.12 sec)Display all records from the table using select statement −mysql> select *from ...
Read More