MySQL Articles - Page 84 of 355

Let us first create a table −mysql> create table if not exists DemoTable1343    -> (    -> `_ClientId` int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ClientName varchar(40),    -> ClientProjectDeadline date    -> )ENGINE=MyISAM, AUTO_INCREMENT=1000; Query OK, 0 rows affected (0.21 sec)Insert some records in the table using insert command with a single query −mysql> insert into DemoTable1343(ClientName, ClientProjectDeadline) values('Chris', '2019-09-24'), ('Bob', '2015-12-09'),    -> ('Mike', '2017-01-20'), ('Carol', '2018-03-31'); Query OK, 4 rows affected (0.10 sec) Records: 4 Duplicates: 0 Warnings: 0Display all records from the table using select statement −mysql> select * from DemoTable1343;This will produce the ... Read More

For this, you can use MySQL row_number(). Let us first create a table −mysql> create table DemoTable1342    -> (    -> Score int    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1342 values(80); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1342 values(98); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1342 values(78); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1342 values(89); Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −mysql> select ... Read More

To fetch the substring after last dot, use substring_index(). Let us first create a table −mysql> create table DemoTable1341    -> (    -> Value varchar(60)    -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1341 values('John.123.@gmail.com' ); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1341 values('Carol.Taylor.gmail') ; Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable1341 values('C.MyFolder.Location') ; Query OK, 1 row affected (0.10 sec)Display all records from the table using select statement −mysql> select * from DemoTable1341;This will produce the following ... Read More

Let us first create a table −mysql> create table DemoTable1339    -> (    -> Name varchar(30),    -> Score int    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1339 values('Chris', 56); Query OK, 1 row affected (0.36 sec) mysql> insert into DemoTable1339 values('Bob', 46); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1339 values('Adam', 78); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1339 values('John', 90); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1339 values('Carol', 98); Query OK, 1 ... Read More

Let us first create a table −mysql> create table DemoTable1485     -> (     -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,     -> StudentName varchar(20),     -> StudentSubject varchar(20)     -> ); Query OK, 0 rows affected (0.72 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Chris', 'MySQL'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) values('Robert', 'MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1485(StudentName, StudentSubject) ... Read More

For this, use having clause instead of where. Let us first create a table −mysql> create table DemoTable1338    -> (    -> Name varchar(10),    -> Score int    -> ); Query OK, 0 rows affected (1.54 sec)Insert some records in the table using insert command. Here, we have inserted duplicate names with scores −mysql> insert into DemoTable1338 values('Chris', 8); Query OK, 1 row affected (0.80 sec) mysql> insert into DemoTable1338 values('Bob', 4); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1338 values('Bob', 9); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable1338 values('Chris', 6); ... Read More

For this, you can use MAX() along with substring(). Let us first create a table −mysql> create table DemoTable1337    -> (    -> Value varchar(50)    -> ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1337 values('Value400'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1337 values('Value345'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1337 values('Value567'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1337 values('Value489'); Query OK, 1 row affected (0.22 sec)Display all records from the table using select statement ... Read More

For this, you can use COALESCE(). Let us first create a table −mysql> create table DemoTable1336    -> (    -> FirstName varchar(20)    -> ,    -> SecondName varchar(20)    -> ); Query OK, 0 rows affected (0.64 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1336 values('John', NULL); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1336 values(NULL, 'Chris'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1336 values('David', 'Mike'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select * from DemoTable1336;This ... Read More

Let us first create a table −mysql> create table DemoTable1335    -> (    -> ArrivalTime datetime    -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command. We have inserted date time records here −mysql> insert into DemoTable1335 values('2019-09-19 22:54:00'); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:59:00'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:56:00'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1335 values('2019-09-19 22:52:00'); Query OK, 1 row affected (0.11 sec)Display all records from the table using ... Read More

Following is the syntax to insert values in two tables with a stored procedure −DELIMITER // CREATE PROCEDURE yourProcedureName(anyVariableName int)    BEGIN    insert into yourTableName1(yourColumnName1) values(yourVariableName);    insert into yourTableName2(yourColumnName2) values(yourVariableName);    END //Let us first create a table −mysql> create table DemoTable1    -> (    -> StudentScore int    -> ); Query OK, 0 rows affected (0.58 sec)Following is the second table −mysql> create table DemoTable2    -> (    -> PlayerScore int    -> ); Query OK, 0 rows affected (0.52 sec)Here is the query to create a stored procedure and insert values in two tables ... Read More