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
MySQLi Articles
Page 226 of 341
How to set different auto-increment ids for two tables with a user-defined variable?
For this, you can use LAST_INSERT_ID(). Let us first create a table. Here, we have set the auto_increment id to StudentId column −mysql> create table DemoTable1 (StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(null); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following output −+-----------+ | StudentId | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec)Following is the query to ...
Read MoreIs MySQL's SLEEP() function a busy-wait? How to implement it?
No, MySQL sleep function is not busy-wait. Let us first create a table and implement the SLEEP() function −mysql> create table DemoTable(FirstName varchar(100)); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Adam'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Bob'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.16 sec)Display all records ...
Read MoreMySQL order by field using CASE Statement
To order by field, use CASE statement. Let us first create a table −mysql> create table DemoTable(StudentId varchar(100)); Query OK, 0 rows affected (1.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('STU-980'); Query OK, 1 row affected (0.28 sec) mysql> insert into DemoTable values('STU-1029'); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable values('STU-189'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values('STU-890'); Query OK, 1 row affected (0.38 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+-----------+ | ...
Read MoreGetting count of two different sets of rows in a table and then dividing them in MySQL
For this, use count(*) and the divide the count of two different sets of rows. Let us first create a table −mysql> create table DemoTable(isMarried tinyint(1)); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.17 ...
Read MoreCopy all rows of a table to another table in MySQL?
To copy all rows of a table to another table, use the below syntax −insert into yourTableName2(yourColumnName1, ...N) select yourColumnName1, ..N from yourTableName1;Let us first create a table −mysql> create table DemoTable1(FirstName varchar(100)); Query OK, 0 rows affected (1.11 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values('John'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable1 values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1 values('Bob'); Query OK, 1 row affected (0.40 sec)Display all records from the table using select statement −mysql> select *from DemoTable1;This will produce the following ...
Read MoreMySQL query to sum the values of similar columns from two different tables for a particular ID
Let’s say we have two tables and both of them have two columns PlayerId and PlayerScore. We need to add the PlayerScore from both these tables, but only for a particular PlayerId.For this, you can use UNION. Let us first create a table −mysql> create table DemoTable1(PlayerId int, PlayerScore int); Query OK, 0 rows affected (9.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(1000, 87); Query OK, 1 row affected (3.12 sec) mysql> insert into DemoTable1 values(1000, 65); Query OK, 1 row affected (1.29 sec) mysql> insert into DemoTable1 values(1001, 10); Query OK, 1 ...
Read MoreFilter query by current date in MySQL
Let us first create a table −mysql> create table DemoTable(DueDate datetime); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-07-10 04:20:00'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-07-10 05:10:40'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-07-10 09:00:20'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-07-10 10:01:04'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values('2019-07-10 12:11:10'); Query OK, 1 row affected (0.19 sec)Display all records from the table using select statement −mysql> ...
Read MoreHow to check an empty table already in a MySQL database?
To check an empty table is in a database, you need to extract some records from the table. If the table is not empty then the table records would be returned.Let us first create a table −mysql> create table DemoTable(Id int, Name varchar(100), Age int); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1001, 'John', 23); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(1002, 'Chris', 21); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(1003, 'David', 22); Query OK, 1 row affected ...
Read MoreHow to order by auto_increment in MySQL?
Let us first create a table −mysql> create table DemoTable(Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100)); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName) values('Chris'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(FirstName) values('Robert'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(FirstName) values('David'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(FirstName) values('Mike'); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(FirstName) values('Adam'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(FirstName) values('Carol'); Query OK, ...
Read MoreFacing difficulty in removing the apostrophe in MySQL stored procedure?
To remove apostrophe, replace it. For this, you can use REPLACE(). Following is the syntax −SET anyVariableName = REPLACE(yourVaribleName , ''', '');To understand the above syntax, let us create a stored procedure to remove the apostrophe in MySQL −mysql> DELIMITER // mysql> CREATE PROCEDURE remove_Apostrophe(IN Value VARCHAR(200)) BEGIN SET Value = REPLACE(Value , ''', ''); SELECT CONCAT("AFTER REMOVING APOSTROPHE THE STRING IS= ", Value); END // Query OK, 0 rows affected (0.15 sec) mysql> DELIMITER ;Call the stored procedure using CALL command −mysql> CALL remove_Apostrophe("Introduction to My'SQL");This will produce the following ...
Read More