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
MySQL Articles
Page 341 of 355
Using LIKE for two where clauses in MySQL?
You don’t need to use two where clauses. Use two conditions using the LIKE operator and AND operator.To understand how to use LIKE for this, let us create a table. The query to create a table is as follows −mysql> create table WhereDemo -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.56 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into WhereDemo values(101, 'Maxwell'); Query OK, 1 row affected (0.14 sec) mysql> insert into WhereDemo values(110, 'David'); Query ...
Read MoreResolve usage of quotes 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?
In MySQL, you can use two different types of quotation marks which is backtick and another is single quotes or double quotes. In this case, maybe you are using single quotes to the column name that’s why you are getting error. You need to use the backtick symbol (` `) instead of single quotes. Backtick can be used with column names while single quotes can be used for strings.To understand the above error, let us create a table. The query to create a table is as follows −mysql> create table Backtick_SymbolDemo -> ( -> Id int NOT NULL ...
Read MoreFind a list of invalid email address from a table in MySQL?
To find invalid email address, use the below syntax −SELECT yourColumnName FROM yourTableName WHERE yourColumnName NOT LIKE '%_@_%._%';The above syntax will give the list of all invalid email addresses. To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table FindInvalidEmailAddressDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> EmailAddress varchar(40), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.75 sec)Now you can insert some records in the table using insert command. We have inserted some invalid ...
Read MoreMySQL “not a variable or NEW pseudo-variable” message. What is this error in my Stored Procedure?
To get rid of this error message, let us see a sample example. But before that let us go through the concept to fix it.Use variable to get the value from stored procedure. The variable will prefix with @ symbol. The syntax is as follows −CALL yourStoredProcedureName(yourParameter1, yourParameter2, ..........N, @yourVariableName);To see the value of variable you need to use select statement. The syntax is as follows −SELECT @yourVariableName;To understand the above syntax, let us create a table and insert some records in the table.The query to create a table is as follows −mysql> create table StoredProcedureDemo -> ( ...
Read MoreGet the type of a variable in MySQL?
You cannot get the type of variable in MySQL. Cast the type of variable into another using CAST operator. The syntax is as follows −SET @yourVariableName:=’anyValue’Use the CAST operator to cast to another type. The syntax is as follows −SELECT CAST( @yourVariableName AS SIGNED);To understand the above syntax, let us cast to another type.Case 1: String to unsigned −mysql> set @StringToInt:='12345'; Query OK, 0 rows affected (0.00 sec)The query is as follows to another type −mysql> select CAST(@StringToInt as UNSIGNED);The following is the output −+--------------------------------+ | CAST(@StringToInt as UNSIGNED) | +--------------------------------+ | 12345 ...
Read MoreCreate MySQL column with Key=MUL?
You need to use ADD KEY to make a column with Key=MUL. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumnName data type, ADD KEY(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Instructor -> ( -> Instructor_Id int, -> Instructor_Name varchar(30), -> Instructor_CourseName varchar(100) -> ); Query OK, 0 rows affected (0.63 sec)Now you can look the table description of the table, the column KEY does not have any MUL key. The query is as follows to check the ...
Read MoreExtract the Day / Month / Year from a Timestamp in PHP MySQL?
To extract the Day/Month/Year from a timestamp, you need to use the date_parse() function. The syntax as follows −print_r(date_parse(“anyTimeStampValue”));The PHP code is as follows −$yourTimeStampValue="2019-02-04 12:56:50"; print_r(date_parse($yourTimeStampValue));The snapshot of PHP code is as follows −The following is the output −Array ( [year] => 2019 [month] => 2 [day] => 4 [hour] => 12 [minute] => 56 [second] => 50 [fraction] => 0 [warning_count] => 0 [warnings] => Array ( ) [error_count] => 0 [errors] => Array ( ) [is_localtime] => )The snapshot of the sample output −
Read MoreMySQL CREATE USER with a variable?
You can use a dynamic query for this. First set the variable name for username and variable name for a password. The syntax is as follows −SET @anyVariableName=’yourUserName’; SET @anyVariableName1=’yourpassword’;Now you can use the CONCAT() function from MySQL. The syntax is as follows −SET @yourQueryName = CONCAT (' CREATE USER "', @anyVariableName, '"@"localhost" IDENTIFIED BY "', @anyVariableName1, '" ' );Let us use the prepared statement PREPARE. The syntax is as follows −PREPARE yourStatementVariableName FROM @yourQueryName;Now you can execute the statement. The syntax is as follows −EXECUTE yourStatementVariableName;Deallocate the above using the DEALLOCATE PREPARE. The syntax is as follows −DEALLOCATE ...
Read MoreHow to DROP a database in MySQL with character '?' in its name?
To drop a database with the character ‘?’ in its name, you need to use backtick symbol around the database name. The syntax is as follows −DROP DATABASE `yourDatabaseName`;To understand the above syntax, let us create a database. The query to create a database is as follows −mysql> create database `test?data`; Query OK, 1 row affected (0.14 sec)So, I have a database with? character. The query to show all databases is as follows −mysql> show databases;The following is the output −+-----------------------+ | Database | +-----------------------+ | business | ...
Read MoreMySQL: delete all rows containing string “foo” in sample table “bar”?
To delete all rows containing string “foo” in table “bar”, you need to use LIKE operator.To understand the above syntax, let us create a sample table with name “bar”. The query to create a table is as follows. We will always insert records with string “foo” using INSERT command after creating the below table −mysql> create table bar -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Words longtext, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.61 sec)Now you can insert some records in the table using insert command. The string “foo” ...
Read More