Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Let’s say the current date is −2019-07-22Let us first create a table −mysql> create table DemoTable705 (ShippingDate datetime); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable705 values('2019-01-21 23:59:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable705 values('2019-07-22 00:00:30'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable705 values('2019-07-21 12:01:30'); Query OK, 1 row affected (0.44 sec)Display all records from the table using select statement −mysql> select *from DemoTable705;This will produce the following output -+---------------------+ | ShippingDate | ... Read More
Let us first create a table −mysql> create table DemoTable704 (SubjectName text); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable704 values('Introduction to MySQL'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable704 values('Introduction to MongoDB'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable704 values('Introduction to MySQL'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable704 values('Introduction to Java'); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable704 values('Introduction to MongoDB'); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More
For this, you can use CASE statement. Let us first create a table −mysql> create table DemoTable703 (Price int); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable703 values(102); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable703 values(null); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable703 values(0); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable703 values(500); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable703 values(100); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable703 values(null); Query ... Read More
You cannot give table name references because it is a reserved keyword. Wrap it using backticks, for example, `references`.Let us first create a table −mysql> create table `references`(Subject text); Query OK, 0 rows affected (0.44 sec)Insert some records in the table using insert command −mysql> insert into `references` values('Introduction To MySQL'); Query OK, 1 row affected (0.28 sec) mysql> insert into `references` values('Introduction To MongoDB'); Query OK, 1 row affected (0.15 sec) mysql> insert into `references` values('Introduction To Spring and Hibernate'); Query OK, 1 row affected (0.13 sec) mysql> insert into `references` values('Introduction To Java'); Query OK, 1 row affected ... Read More
Let us first create a table −mysql> create table DemoTable702 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), StudentScore int ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable702(StudentName, StudentScore) values('Chris', 56); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable702(StudentName, StudentScore) values('Robert', 21); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable702(StudentName, StudentScore) values('Mike', 89); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable702(StudentName, StudentScore) values('David', 99); Query OK, 1 row affected (0.20 sec)Display all records from ... Read More
Here, we will see an example wherein we are inserting datetime and updating them while using INSERT query.Let us first create a table −mysql> create table DemoTable816 (DueDate datetime); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command. Here is the query to add (minutes / hours / days / months / years) to date when performing INSERT −mysql> insert into DemoTable816 values(date_add(now(), interval 3 minute)); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable816 values(date_add('2018-01-21 00:00:00', interval 3 Hour)); Query OK, 1 row affected (0.52 sec) mysql> insert into DemoTable816 values(date_add('2016-11-11 ... Read More
To copy from one column to another, you can use INSERT INTO SELECT statement.Let us first create a table −mysql> create table DemoTable1 (PlayerScore int); Query OK, 0 rows affected (0.46 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(98); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1 values(81); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1 values(76); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values(88); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This ... Read More
Use ALTER table to set the auto_increment column to 0 or reset with another valueALTER TABLE yourTableName AUTO_INCREMENT=0;The above syntax will begin from 1.Let us first create a table −mysql> create table DemoTable698 ( ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY ) auto_increment=109; Query OK, 0 rows affected (0.88 sec)Here is the query to reset the auto_increment value to 0 −mysql> alter table DemoTable698 AUTO_INCREMENT=0; Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0Insert some records in the table using insert command. Here, we haven’t inserted anything since we want to display auto increment value ... Read More
To select date from timestamp in MySQL, you need to use DATE().Let us first create a table −mysql> create table DemoTable697( Id varchar(100), Title varchar(100), BatchTime timestamp ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable697 values('10', 'Java', '2019-01-21 10:34:56'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable697 values('11', 'Spring', '2019-03-11 11:14:16'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable697 values('12', 'Hibernate', '2019-07-21 12:04:00'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> ... Read More
You cannot return table from MySQL function. The function can return string, integer, char etc. To return table from MySQL, use stored procedure, not function.Let us first create a table −mysql> create table DemoTable696 ( Id int, Name varchar(100) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable696 values(100, 'Mike'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable696 values(101, 'Sam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable696 values(102, 'Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable696 ... Read More