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 282 of 341
Declare syntax error in MySQL Workbench?
The DECLARE syntax must between BEGIN and END. The syntax is as follows −BEGIN DECLARE yourVariableName1 dataType, DECLARE yourVariableName2 dataType, . . . . ENDHere is the query to avoid DECLARE syntax error in MySQL −mysql> DELIMITER // mysql> create procedure declare_Demo() -> BEGIN -> DECLARE Name varchar(100); -> SET Name: ='John'; -> SELECT Name; -> END -> // Query OK, 0 rows affected (0.17 sec) mysql> DELIMITER ;Call the stored procedure with the help of CALL command. The syntax is as follows −CALL yourStoredProcedureName();The query is as follows −mysql> call declare_Demo();The following is ...
Read MoreWhat does a "set+0" in a MySQL statement do?
The set+0 converts the set value to integer. Let us see an example by creating a table −mysql> create table SetZeroDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> TechnicalSkills set('C', 'Spring Framework /Hibernate', 'Python', 'Django Framework', 'Core Java') NOT NULL -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SetZeroDemo(TechnicalSkills) -> values('C, Spring Framework /Hibernate, Python, Django Framework, Core Java'); Query OK, 1 row affected (0.20 sec)Display all records from the table using select statement. The ...
Read MoreReserving MySQL auto-incremented IDs?
To reserve MySQL auto-incremented IDs, the syntax is as follows −START TRANSACTION; insert into yourTableName values(), (), (), (); ROLLBACK; SELECT LAST_INSERT_ID() INTO @anyVariableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table reservingAutoIncrementDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command. The query is as follows −mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> insert into reservingAutoIncrementDemo values(), (), (), (); Query ...
Read MoreGet the number of days between current date and date field?
To get the number of days between current date and date field, the syntax is as follows −SELECT DATEDIFF(CURDATE(), STR_TO_DATE(yourColumnName, '%d-%m-%Y')) AS anyAliasName FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DateDifferenceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ArrivalDate varchar(100) -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DateDifferenceDemo(ArrivalDate) values('12-10-2011'); Query OK, 1 row affected (0.14 sec) mysql> insert ...
Read MoreIgnoring the year in MySQL Query with date range?
To ignore the year with date range, use the DATE_FORMAT() with the between clause. Let us first create a demo table. The query to create a table is as follows −mysql> create table igonreYearDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> ShippingDate date -> ); Query OK, 0 rows affected (0.75 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into igonreYearDemo(ShippingDate) values('2016-01-31'); Query OK, 1 row affected (0.16 sec) mysql> insert into igonreYearDemo(ShippingDate) values('2018-01-31'); Query OK, 1 row affected (0.13 sec) mysql> insert into ...
Read MoreHow to fix poor performance of INFORMATION_SCHEMA.key_column_usage in MySQL?
You can use GLOBAL variable as shown below −SET global innodb_stats_on_metadata =0;After including the above syntax, the INFORMATION_SCHEMA.key_column_usage will take less time and that would improve the performance.The query is as follows −mysql> set global innodb_stats_on_metadata =0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT REFERENCED_TABLE_NAME,TABLE_NAME,COLUMN_NAME,CONSTRAINT_SCHEMA -> FROM INFORMATION_SCHEMA.key_column_usage;The following is the output −It returns 674 rows in 0.28 seconds.
Read MoreHow to insert a row into a table that has only a single autoincrement column?
You can easily insert a row into a table that has only a single auto increment column. The syntax is as follows −insert into yourTableName set yourColumnName =NULL;You can use the below syntax −insert into yourTableName values(NULL);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table singleAutoIncrementColumnDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into singleAutoIncrementColumnDemo set UserId ...
Read MoreCorrectly implement the AND condition in MySQL
To implement AND condition, the syntax is as follows −select *from yourTableName where yourColumnName1 = yourValue1 AND yourColumnName2 = yourValue2;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table MySQLANDConditionDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.80 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into MySQLANDConditionDemo(Name, Age) values('Larry', 23); Query OK, 1 row affected (0.11 sec) mysql> ...
Read MoreWhat does % stand for in host column and how to change user's password?
The localhost means you can access from same machine while from % the remote host access is possible. The syntax is as follows to change the user password.SET PASSWORD FOR 'yourUserName'@'localhost' ='yourPassword';First check the user and host from MySQL.user table. The query is as follows −mysql> select user, host from MySQL.user;Here is the output −+------------------+-----------+ | user | host | +------------------+-----------+ | Bob | % | | Manish | % ...
Read MoreWhat is the smallest datatype for one bit in MySQL?
The smallest datatype for one bit can be bit(1). The syntax is as follows −yourColumnName bit(1)To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table bitDemo -> ( -> isValid bit(1) -> ); Query OK, 0 rows affected (0.49 sec)Now you can check all the details of table with the help of SHOW CREATE command. The query is as follows −mysql> show create table bitDemo;Here is the output −+---------+-----------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table ...
Read More