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

The alias name can be used as a variable name in MySQL as shown in the below syntax −select count(*) AS anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable695 (    FirstName varchar(100) ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable695 values('Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable695 values('Robert'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable695 values('David'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable695 values('Mike'); Query OK, 1 row affected (0.16 ... Read More

To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.Let us first create a table −mysql> create table DemoTable694 (    EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    EmployeeName varchar(100),    EmployeeSalary int ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable694(EmployeeName, EmployeeSalary) values('Chris', 457647); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable694(EmployeeName, EmployeeSalary) values('Robert', 90883); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable694(EmployeeName, EmployeeSalary) values('David', 123532); Query OK, 1 row ... Read More

Before beginning, let us try to set ‘when’ as column name while using CREATE TABLE statement −mysql> create table DemoTable693(    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentName varchar(100),    When datetime );This will produce the following output. An error would be visible:ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'When datetime at line 5You need to wrap the reserved word using backticks, for example, `when`. Let us first create a table and implement the same:mysql> create table ... Read More

To get a list of MySQL databases, following is the syntax -show databases;To get the server version, you can use the below syntax -select version();Let us implement the above syntax to get a list of MySQL databases and version -mysql> show databases;This will produce the following output displaying all the databases -+---------------------------+ | Database                  | +---------------------------+ | bothinnodbandmyisam       | | business                  | | commandline               | | customer-tracker          | | customer_tracker_database ... Read More