MySQL DATE_ADD to Increment a Date Based on Another Column

Sharon Christine
Updated on 30-Jun-2020 11:47:25

775 Views

Let us first create a table with one of the columns as DueDate and another one “RepeatTime, which displays how many times, let’s say a user was reminded to submit the payment −mysql> create table DemoTable    -> (    -> DueDate date,    -> RepeatTime int    -> ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-23', 3); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-06-22', 6); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-03-28', 2); Query ... Read More

Change MySQL Column to Have Not Null Constraint

Sharon Christine
Updated on 30-Jun-2020 11:45:00

669 Views

To update the constraint, use the MODIFY command. Following is the syntax −alter table yourTableName modify yourExistingColumnName yourExistingDataType NOT NULL;  Let us first create a table −mysql> create table DemoTable    -> (    -> UserId int NOT NULL AUTO_INCREMENT,    -> UserFirstName varchar(100),    -> UserLastName varchar(100),    -> UserEmailId varchar(100),    -> UserPassword varchar(100),    -> PRIMARY KEY(UserId)    -> ); Query OK, 0 rows affected (0.91 sec)Following is the query to change the constraint of a column to NOT NULL −mysql> alter table DemoTable modify UserFirstName varchar(100) NOT NULL; Query OK, 0 rows affected (2.13 sec) Records: ... Read More

Default Order by Value in MySQL

Sharon Christine
Updated on 30-Jun-2020 11:35:40

237 Views

There is no default ORDER BY value in MySQL. You need to specify ORDER BY clause explicitly. Following is the syntax −ORDER BY ASC; OR ORDER BY DESC;Let us first create a table −mysql> create table DemoTable    -> (    -> StudentName varchar(100)    -> ); Query OK, 0 rows affected (0.82 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.15 sec) ... Read More

Calculate Salary by Getting the Difference Between Dates in MySQL

karthikeya Boyini
Updated on 30-Jun-2020 11:33:42

400 Views

Let’s say you need to get the difference between dates (JoiningDate – EndDate) of a month i.e. days to calculate the salary. The daily-wage salary is let’s say 300; therefore for 20 days, it will 6000. In the same way, for 27 days, it will be 8100.For our example, let us first create a tablemysql> create table DemoTable    -> (    -> JoinDate date,    -> EndDate date    -> ,    -> Value int    -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-01', '2019-01-31', ... Read More

Select Multiple Columns and Display in a Single Column in MySQL

karthikeya Boyini
Updated on 30-Jun-2020 11:30:41

5K+ Views

Use concat() for this. Let us first create a table −mysql> create table DemoTable    -> (    -> FirstName varchar(30),    -> LastName varchar(30)    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 'Brown'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Adam', 'Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('Carol', 'Taylor'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis will produce the following ... Read More

Add a New Column and Set Values in MySQL Based on Conditions

karthikeya Boyini
Updated on 30-Jun-2020 11:29:33

3K+ Views

To set values on the basis of conditions, use IF() method. Let us first create a table −mysql> create table DemoTable    -> (    -> Age int    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(19); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(16); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(17); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(22); Query OK, 1 row affected (0.19 sec)Display all records from ... Read More

Order By Timestamp in MySQL

karthikeya Boyini
Updated on 30-Jun-2020 11:28:26

1K+ Views

To order by timestamp, use the ORDER BY as in the following syntax −select *from yourTableName ORDER BY STR_TO_DATE(`yourColumnName`, '%m/%d/%Y%h:%i:%s %p');Let us first create a table −mysql> create table DemoTable    -> (    -> `timestamp` varchar(100)    -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('06/22/2019 01:10:20 PM'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('06/22/2019 12:00:27 PM'); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values('06/22/2019 06:56:20 AM'); Query OK, 1 row affected (0.23 sec) ... Read More

Fetch Single Ordered Date from a Column with MySQL LIMIT

Sharon Christine
Updated on 30-Jun-2020 11:24:45

112 Views

To fetch a single date from a column, use “LIMIT 1. To order it, use ORDER BY clause. Let us first create a table −mysql> create table DemoTable -> ( -> DueDate varchar(100) -> ); Query OK, 0 rows affected (1.16 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('10-06-2019'); Query OK, 1 row affected (0.42 sec) mysql> insert into DemoTable values('01-12-2016'); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable values('31-01-2018'); Query OK, 1 row affected (0.58 sec)Display all records from the table using select statement −mysql> select *from DemoTable;OutputThis ... Read More

Pad Displayed Value with Zeros in MySQL

Sharon Christine
Updated on 30-Jun-2020 11:23:59

98 Views

Use zerofill for this in MySQL. Zerofill pads the displayed value of the field with zeros up to the display width specified in the column definition. For example, if column is set int(8), therefore the width is 8. If the number is let’s say 29654, then zero will be padded on the left for total width i.e.8 −00029654Let us first create a table −mysql> create table DemoTable -> ( -> Number int(8) zerofill -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected ... Read More

Select Second p Element of Its Parent from Last Child

seetha
Updated on 30-Jun-2020 11:22:56

1K+ Views

Use the CSS :nth-last-of-type(n) selector to select every element that is the second element of its parent, counting from the last child.ExampleYou can try to run the following code to implement the :nth-last-of-type(n) selector:Live Demo                    p:nth-last-of-type(2) {             background: blue;             color: white;          }                     This is demo text 1.       This is demo text 2.       This is demo text 3.       This is demo text 4.       This is demo text 5.    

Advertisements