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 250 of 341
How to sort multiple columns with a single query?
Let us first create a table −mysql> create table DemoTable -> ( -> Id int, -> Value int -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 85885); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(101, 885995474); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(100, 895943); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+------+-----------+ | Id | ...
Read MoreSet AUTO_INCREMENT in a table while creating it in MySQL?
Let us first create a table. We have used AUTO_INCREMENT while creating the table to set auto increment for StudentId −mysql> create table DemoTable -> ( -> StudentId int NOT NULL AUTO_INCREMENT, -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> StudentAge int, -> StudentCountryName varchar(100), -> PRIMARY KEY(StudentId) -> )AUTO_INCREMENT=30; Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentFirstName, StudentLastName, StudentAge, StudentCountryName) values('John', 'Smith', 21, 'US'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable(StudentFirstName, StudentLastName, StudentAge, StudentCountryName) values('Chris', 'Brown', ...
Read MoreMySQL query to select records beginning from a specific id
Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Name) values('John'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Name) values('Carol'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Name) values('Bob'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(Name) ...
Read MoreMySQL query to count where more than three columns values are true?
To count where more than three column values are true, you can use WHERE clause. Let us first create a table −mysql> create table DemoTable -> ( -> isMarried boolean, -> isActive boolean, -> isMember boolean, -> isOn boolean -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(true, false, true, false); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(false, false, false, false); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(true, true, ...
Read MoreLimiting numbers to a maximum value in MySQL?
For this, you can use LEAST(). Following is the syntax −select least(yourColumnName, yourMaxValue) from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Number int -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(50); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(40); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(201); Query OK, 1 row affected (0.12 sec) ...
Read MoreHow to set a comma separated list as a table in MySQL?
You can use UNION ALL for this.Let us get list 10, 20, 30, 40, 50 as a table with UNION ALL −mysql> select 10 Number UNION ALL select 20 Number UNION ALL select 30 Number UNION ALL select 40 Number UNION ALL select 50 Number;Output+--------+ | Number | +--------+ | 10 | | 20 | | 30 | | 40 | | 50 | +--------+ 5 rows in set (0.00 sec)Let us see another example. To get the list 1,2,3 as a table, use the below query −mysql> SELECT 1 a UNION ALL SELECT 2 a UNION ALL SELECT 3 a;Output+---+ | a | +---+ | 1 | | 2 | | 3 | +---+ 3 rows in set (0.00 sec)
Read MoreHow to get age from DOB in MySQL?
To get age from DOB, you can use the TIMESTAMPDIFF() function. Following is the syntax −select TIMESTAMPDIFF(YEAR, yourColumnName, CURRENT_DATE) AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> DateOfBirth datetime -> ); Query OK, 0 rows affected (0.73 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(DateOfBirth) values('1998-06-04 12:30:00'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(DateOfBirth) values('2000-01-31 04:10:20'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(DateOfBirth) values('2010-12-01 03:50:45'); ...
Read MoreMySQL query to update string field by concatenating to it?
For concatenating a string field, use CONCAT() function. Let us first create a table −mysql> create table DemoTable -> ( -> SequenceId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentId varchar(100) -> ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentId) values('STU'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentId) values('STU1'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;Output+------------+-----------+ | SequenceId | StudentId | +------------+-----------+ | 1 ...
Read MoreMySQL query to order and display difference between dates from the current date
For this, use ORDER BY clause. The current date is as follows −mysql> select now(); +---------------------+ | now() | +---------------------+ | 2019-06-09 21:08:16 | +---------------------+ 1 row in set (0.00 sec)Let us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> DueDate datetime -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(DueDate) values('2019-06-12'); Query OK, 1 row affected (0.24 sec) ...
Read MoreHow to find current size (in memory) of table in MySQL?
To get the current size of a table, use the following that will display details about a table including the size −show table status like ‘yourTableName’\GLet us first create a table −mysql> create table DemoTable -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CustomerName varchar(20), -> CustomerAge int, -> CustomerCountryName varchar(20) -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(CustomerName, CustomerAge, CustomerCountryName) values('John', 24, 'US'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable(CustomerName, CustomerAge, CustomerCountryName) ...
Read More