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
MySQL Articles
Page 235 of 355
Display only the default values set for columns in MySQL
Let us first create a table and set default values −mysql> create table DemoTable803(UserId int DEFAULT 101, UserName varchar(100) DEFAULT 'Chris'); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert command. For the values we are not inserting, the default values will get set automatically −mysql> insert into DemoTable803 values(102, 'Chris'); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable803(UserName) values('Mike'); Query OK, 1 row affected (0.48 sec) mysql> insert into DemoTable803(UserId) values(103); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable803 values(); Query OK, 1 row affected (0.22 sec) mysql> ...
Read MoreHow to extract from datetime column in MySQL by comparing only date and ignoring whitespace?
To extract from datetime column, you can use date() along with trim(). Here, trim() is used to remove whitespace while comparing. Let us first create a table −mysql> create table DemoTable661(Duedate datetime); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable661 values(' 2019-01-21 12:02:21'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable661 values(' 2019-07-11 11:55:59 '); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable661 values('2019-11-21 04:00:59 '); Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select ...
Read MoreIs there a way to subtract number of days from a date in MySQL?
Yes, you can use date_sub() to subtract number of days from a date. Following is the syntax −select date_sub(yourColumnName, Interval yourAmountOfDays day) from yourTableName;Let us first create a table −mysql> create table DemoTable660(AdmissionDate datetime); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable660 values('2018-01-24'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable660 values('2019-07-10'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable660 values('2020-11-20'); Query OK, 1 row affected (0.59 sec)Display all records from the table using select statement −mysql> select *from DemoTable660;This will produce the ...
Read MoreHow to fetch a specific row when values are the same in MySQL?
To fetch a specific row when values are the same, use GROUP BY. Let us first create a table −mysql> create table DemoTable659(Id int, Name varchar(100), Score int); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable659 values(11, 'John', 45); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable659 values(15, 'John', 59); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable659 values(15, 'Sam', 61); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable659;This will produce the ...
Read MoreHow to convert a MySQL TIME value to days and hours form?
Here, we are converting time value, for example 150:50:10 to days and hours form, i.e. 6 days 6 hours.You can use CONCAT() along with HOUR() for this. Let us first create a table −mysql> create table DemoTable657(DueTime time); Query OK, 0 rows affected (3.68 sec)Insert some records in the table using insert command. Here, we have inserted the records in the form of total hours −mysql> insert into DemoTable657 values('120:30:00'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable657 values('150:50:10'); Query OK, 1 row affected (0.27 sec)Display all records from the table using select statement −mysql> select *from ...
Read MoreMySQL Stored procedure won't fetch the whole table?
You can use SELECT *FROM yourTableName in stored procedure. Let us first create a table −mysql> create table DemoTable654 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentFirstName varchar(100) ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable654(StudentFirstName) values('John'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable654(StudentFirstName) values('Sam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable654(StudentFirstName) values('Mike'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable654(StudentFirstName) values('Robert'); Query OK, 1 row affected (0.12 sec)Display all records from the table using ...
Read MoreHow to change order of items in MySQL?
To change order of items in MySQL, use ORDER BY alias name. Let us first create a table −mysql> create table DemoTable653 (Product1Amount int, Product2Amount int); Query OK, 0 rows affected (0.42 sec)Insert some records in the table using insert command −mysql> insert into DemoTable653 values(400, 250); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable653 values(500, 300); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable653 values(40, 400); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable653 values(200, 450); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable653 values(50, 20); Query ...
Read MoreSearch record with a specific value in MySQL using LIKE or REGEXP?
Let us first create a table −mysql> create table DemoTable651 (Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value varchar(100)); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable651(Value) values('Title'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable651(Value) values('course'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable651(Value) values('subject'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement −mysql> select *from DemoTable651;This will produce the following output −+----+---------+ | Id | Value | +----+---------+ | 1 | ...
Read MoreMySQL query to count the number of times a specific integer appears in a column for its corresponding value in another column
For this, use aggregate function COUNT(). Let us first create a table −mysql> create table DemoTable650 (Value1 int, Value2 int); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into DemoTable650 values(100, 500); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable650 values(100, 500); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable650 values(100, 500); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable650 values(100, 500); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable650 values(200, 500); Query OK, 1 row affected (0.12 sec)Display ...
Read MoreDisplay records from MySQL stored Procedure with IF...THEN...END IF statements
Let us first create a table −mysql> create table DemoTable643 (ClientId int); Query OK, 0 rows affected (0.86 sec)Insert some records in the table using insert command −mysql> insert into DemoTable643 values(1000); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> select *from DemoTable643;This will produce the following output −+----------+ | ClientId | +----------+ | 1000 | +----------+ 1 row in set (0.00 sec)Here is the query to MySQL stored procedure with IF THEN END IF −mysql> DELIMITER // mysql> CREATE PROCEDURE IF_DEMO(argument int) BEGIN DECLARE firstArgument int; DECLARE ...
Read More