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 234 of 341
Select last 3 rows from database order by id ASC?
You can use subquery. Following is the syntax −SELECT * FROM ( SELECT * FROM yourTableName ORDER BY yourIdColumnName DESC LIMIT 3 ) anyAliasName ORDER BY yourIdColumnName;Let us first create a table −mysql> create table DemoTable ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(100) ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ClientName) values('Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(ClientName) values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Bob'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(ClientName) values('David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(ClientName) values('Carol'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(ClientName) values('Robert'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(ClientName) values('Sam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(ClientName) values('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 ...
Read MoreFind size of text stored in a specific MySQL column?
You can use length() from MySQL to find the size of text stores in a specific column. Let us first create a tablemysql> create table DemoTable ( CustomerName longtext ); Query OK, 0 rows affected (0.67 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('Robert'); 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−+--------------+ | CustomerName | +--------------+ | Robert | +--------------+ 1 row in set (0.00 sec)Here is the query to find the size ...
Read MoreIs there a MAX function for rows and not for columns in MySQL?
Yes, you can use GREATEST() from MySQL to check maximum from rows (not columns). Let us first create a table −mysql> create table DemoTable ( Value1 int, Value2 int, Value3 int ); Query OK, 0 rows affected (0.58 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(190, 395, 322); Query OK, 1 row affected (0.16 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output−+--------+--------+--------+ | Value1 | Value2 | Value3 | +--------+--------+--------+ | 190 | 395 ...
Read MoreAdd data to existing data in a MySQL Database?
You can use CONCAT() function for this. Let us first create a table −mysql> create table DemoTable ( UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, UserName varchar(100) ); Query OK, 0 rows affected (0.43 sec)Insert records in the table using insert command −mysql> insert into DemoTable(UserName) values('John'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(UserName) values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserName) values('Robert'); Query OK, 1 row affected (0.11 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------+----------+ ...
Read MoreHow do you fill in or pad a column with zeros using a MySQL query?
You can use ZEROFILL for column to fill in or pad with zeros. Let us first create a table−mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.58 sec)Following is the query to add zerofill attribute for Number column−mysql> alter table DemoTable change Number Number int(10) zerofill not null; Query OK, 0 rows affected (1.13 sec) Records: 0 Duplicates: 0 Warnings: 0Insert records in the table using insert command −mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(12); Query OK, 1 row affected (0.53 sec) ...
Read MoreSelect last day of current year in MySQL?
In order to get last day of current year, you can use LAST_DAY() from MySQL. The syntax is as follows−SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH));Let us implement the above syntax to know the last day of current year−mysql> SELECT LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH));This will produce the following output −+-------------------------------------------------------------------+ | LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 12-MONTH(CURDATE()) MONTH)) | +-------------------------------------------------------------------+ | 2019-12-31 | +-------------------------------------------------------------------+ 1 row in set (0.00 sec)
Read MoreHow do I exclude a specific record in MySQL?
You can exclude a specific record in SQL using not equal to operator(!=). Let us first create a table−mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ClientName varchar(20), ClientCountryName varchar(10) ); Query OK, 0 rows affected (0.64 sec)Insert records in the table using insert command −mysql> insert into DemoTable(ClientName, ClientCountryName) values('John', 'US'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(ClientName, ClientCountryName) values('David', 'AUS'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(ClientName, ClientCountryName) values('Mike', 'UK'); Query OK, 1 row affected (0.14 sec)Display all records from the ...
Read MoreCan we get total number of rows in a MySQL database?
To get the total number of rows in a MySQL database, you can use aggregate function SUM() along with inbuilt column TABLE_ROWS from INFORMATION_SCHEMA.TABLES.The syntax is as follows−SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database();Let’s say we are using the database with the name ‘sample’.Now we will get the total number of rows in a MySQL database−mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = database();This will produce the following output−+-----------------+ | SUM(TABLE_ROWS) | +-----------------+ | 2043 | +-----------------+ 1 row in set (22.11 sec)
Read MoreMySQL select to convert numbers to millions and billions format?
You can use FORMAT() from MySQL to convert numbers to millions and billions format. Let us first create a table−mysql> create table DemoTable ( Value BIGINT ); Query OK, 0 rows affected (0.74 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(78000000000); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10000000000); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(90000000000); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(450600000000); Query OK, 1 row affected (0.41 sec)Display all records from the table using select statement ...
Read MoreHow to update MySQL column with random value?
To update column with random value, you can use the below syntax−update yourTableName set yourColumnName =round(1+rand()*100);The above syntax will generate a value between 1 to 100. Let us see an example and create a table−mysql> create table DemoTable ( Number int ); Query OK, 0 rows affected (0.46 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(80); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(100000); ...
Read More