- Trending Categories
- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Found 4219 Articles for MySQLi
558 Views
To set auto increment initial value for MySQL table, use ALTER command. The first step would bealter table yourTableName modify yourColumnName int NOT NULL AUTO_INCREMENT PRIMARY KEY, add index(yourColumnName);The second step is as followsalter table yourTableName AUTO_INCREMENT=yourStartingValue;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table setAutoIncrementDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.75 sec)Now implement the above two steps to set auto increment initial value for MySQL table.Step 1 -The query is as followsmysql> alter ... Read More
581 Views
Let us see an example and create a table first.mysql> create table Add1ToExistingValue -> ( -> Value int -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into Add1ToExistingValue values(10); Query OK, 1 row affected (0.12 sec) mysql> insert into Add1ToExistingValue values(13); Query OK, 1 row affected (0.15 sec) mysql> insert into Add1ToExistingValue values(15); Query OK, 1 row affected (0.13 sec) mysql> insert into Add1ToExistingValue values(16); Query OK, 1 row affected (0.14 sec) mysql> insert into Add1ToExistingValue values(20); Query OK, 1 row affected (0.16 sec) mysql> insert into Add1ToExistingValue values(40); Query OK, 1 row affected (0.15 sec) mysql> insert into Add1ToExistingValue values(50); Query OK, 1 row affected (0.11 sec) mysql> insert into Add1ToExistingValue values(55); Query OK, 1 row affected (0.17 sec) mysql> insert into Add1ToExistingValue values(56); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement.The query is as followsmysql> select *from Add1ToExistingValue;The following is the output+-------+ | Value | +-------+ | 10 ... Read More
641 Views
This type of error occurs when number of columns does not match whenever you are inserting records in the destination table. For a demo example, let us create a tablemysql> create table errorDemo -> ( -> User_Id int NOT NULL AUTO_INCREMENT, -> User_Name varchar(20), -> PRIMARY KEY(User_Id) -> ); Query OK, 0 rows affected (0.47 sec)The error is as followsmysql> insert into errorDemo values('John'); ERROR 1136 (21S01): Column count doesn't match value count at row 1To avoid this type of error, you need to use the following syntaxinsert into yourTableName(yourColumnName1, yourColumnName2, ...N)values(yourValue1, yourValue2, ....N);Insert some ... Read More
149 Views
To revert rows to default column value, let us first create a demo tablemysql> create table defaultDemo -> ( -> Id int -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into defaultDemo values(10); Query OK, 1 row affected (0.25 sec) mysql> insert into defaultDemo values(20); Query OK, 1 row affected (0.13 sec) mysql> insert into defaultDemo values(30); Query OK, 1 row affected (0.14 sec) mysql> insert into defaultDemo values(40); Query OK, 1 row affected (0.11 sec) mysql> insert into defaultDemo values(80); Query OK, ... Read More
1K+ Views
Let us first create a tablemysql> create table recordsDemo -> ( -> UserId int, -> Value int -> ); Query OK, 0 rows affected (0.52 sec)Now insert some records in the table using insert command.The query is as followsmysql> insert into recordsDemo values(1, 10); Query OK, 1 row affected (0.17 sec) mysql> insert into recordsDemo values(3, 598); Query OK, 1 row affected (0.18 sec) mysql> insert into recordsDemo values(5, 786); Query OK, 1 row affected (0.25 sec) mysql> insert into recordsDemo values(7, 189); Query OK, 1 row affected (0.16 sec) mysql> insert into recordsDemo values(9, 345); ... Read More
178 Views
Let us see how to loop through a stored procedure in MySQLmysql> DELIMITER // mysql> CREATE PROCEDURE do_WhileDemo(LastValue INT) -> BEGIN -> SET @loop = 0; -> REPEAT -> SET @loop= @loop+ 1; -> select @loop; -> UNTIL @loop >LastValue -> END REPEAT; -> END // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Now call the stored procedure with the help of CALL command.The query is as followsmysql> call do_WhileDemo(10);The following is the output+-------+ | ... Read More
100 Views
You need to use rand() function to select random result from MySQL.The syntax is as followsselect *from yourTableName order by rand() limit 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table selectRandomRecord -> ( -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> StudentName varchar(20) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into selectRandomRecord(StudentName) values('John'); Query OK, 1 row affected (0.17 sec) mysql> insert into selectRandomRecord(StudentName) values('Carol'); Query OK, ... Read More
83 Views
Let us firs create a tablemysql> create table LimitWithStoredProcedure -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(10) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into LimitWithStoredProcedure(Name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into LimitWithStoredProcedure(Name) values('Chris'); Query OK, 1 row affected (0.15 sec) mysql> insert into LimitWithStoredProcedure(Name) values('Maxwell'); Query OK, 1 row affected (0.28 sec) mysql> insert into LimitWithStoredProcedure(Name) values('Bob'); Query OK, 1 row affected (0.24 sec) mysql> insert into LimitWithStoredProcedure(Name) values('David'); Query ... Read More
466 Views
The syntax is as followsupdate yourTableName set yourColumnName1=yourValue where yourColumnName2=yourValue order by yourIdColumnName DESC LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table UpdateWithHighestDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserStatus tinyint, -> UserRank int -> ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command.The query is as followsmysql> insert into UpdateWithHighestDemo(UserStatus, UserRank) values(1, 78); Query OK, 1 row affected (0.12 sec) mysql> insert into UpdateWithHighestDemo(UserStatus, UserRank) values(0, 118); Query ... Read More
975 Views
You can use format() function for the separators. It will work in MySQL version greater than or equal to 5.5. We are using the version 8.0.12mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)The syntax is as followsSELECT FORMAT(yourColumnName, valueAfterDecimalPoint, 'de_DE') AS anyAliasNamefrom yourTableName;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table formatNumberDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Number DECIMAL(19, 1) ... Read More