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 122 of 341
Only display specified values inside the IN clause with MySQL?
For this, you can use IN() along with ORDER BY clause. Let us first create a table −mysql> create table DemoTable1986 ( Number int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1986 values(50); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(60); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(100); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(200); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1986 values(350); Query OK, 1 row ...
Read MoreDate format to convert dates like Jan 2017, May 2018 to complete date in MySQL
For this, use STR_TO_DATE() along with DATE_FORMAT(). Let us first create a table −mysql> create table DemoTable1985 ( DueDate varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1985 values('Jan 2017'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1985 values('May 2018'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1985 values('Aug 2015'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1985;This will produce the following output −+----------+ | DueDate | ...
Read MoreHow to insert a row with a timestamp "X days ago" in MySQL?
To insert a row like this use the following syntax, wherein we are using CURRENT_TIMESTAMP −insert into yourTableName values(CURRENT_TIMESTAMP - INTERVAL ABS(RAND() * 100) DAY);To understand the above syntax, let us create a table −mysql> create table DemoTable1983 ( DueDate timestamp ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1983 values(CURRENT_TIMESTAMP - INTERVAL ABS(RAND() * 100) DAY); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1983 values(CURRENT_TIMESTAMP - INTERVAL ABS(RAND() * 100) DAY); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1983 ...
Read MoreMySQL - changing table engine from innoDB to MyISAM?
Let us first create a table −mysql> create table DemoTable1982 ( StudentId int , StudentName varchar(20), StudentAge int ); Query OK, 0 rows affected (0.00 sec)Let us check the table engine type -mysql> show create table DemoTable1982;This will produce the following output −+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
Read MoreMySQL query to calculate sum from 5 tables with a similar column named "UP"?
For this, use UNION ALL along with SUM(). Let us create 5 tables −mysql> create table DemoTable1977 ( UP int ); Query OK, 0 rows affected (0.00 sec) mysql> insert into DemoTable1977 values(10); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1977 values(20); Query OK, 1 row affected (0.00 sec) mysql> select * from DemoTable1977; +------+ | UP | +------+ | 10 | | 20 | +------+ 2 rows in set (0.00 sec) mysql> create table DemoTable1978 ( UP int ); Query OK, 0 rows affected (0.00 sec) mysql> ...
Read MoreUpdate a column A if null, else update column B, else if both columns are not null do nothing with MySQL
For this, use IF() with IS NULL property. Let us first create a table −mysql> create table DemoTable1976 ( FirstName varchar(20), LastName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1976 values('John', 'Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1976 values('John', NULL); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1976 values(NULL, 'Miller'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1976 values('Chris', 'Brown'); Query OK, 1 row affected (0.00 sec)Display all records from the table ...
Read MoreMySQL query to count all the column values from two columns and exclude NULL values in the total count?
Let us first create a table −mysql> create table DemoTable1975 ( StudentName varchar(20), StudentMarks int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1975 values('John', 45); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1975 values('Chris', 67); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1975 values('David', 59); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1975 values('Bob', NULL); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1975;This ...
Read MoreDynamic SQL to get a parameter and use it in LIKE for a new table created inside a stored procedure
For this, use prepared statement. Let us first create a table −mysql> create table DemoTable1973 ( StudentId int, StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1973 values(101, 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1973 values(102, 'John Doe'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1973 values(103, 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1973 values(104, 'John Smith'); Query OK, 1 row affected (0.00 sec)Display all records from the table using ...
Read MoreMySQL query to get a specific row from rows
Let us first create a table −mysql> create table DemoTable1972 ( Section char(1), StudentName varchar(20) ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1972 values('D', 'Chris'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1972 values('B', 'David'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1972 values('A', 'Mike'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1972 values('C', 'Carol'); Query OK, 1 row affected (0.00 sec)Display all records from the table using select statement −mysql> select * from DemoTable1972;This ...
Read MoreMySQL procedure with SELECT to return the entire table
Let us first create a table −mysql> create table DemoTable1971 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(20), StudentPassword int ); Query OK, 0 rows affected (0.00 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1971(StudentName, StudentPassword) values('John', '123456'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1971(StudentName, StudentPassword) values('Chris', '123456'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1971(StudentName, StudentPassword) values('David', '123456'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1971(StudentName, StudentPassword) values('Mike', '123456'); Query OK, 1 row affected (0.00 sec)Display all ...
Read More