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
MySQLi Articles
Page 328 of 341
MySQL 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 MoreMySQL update query to remove spaces?
You can use TRIM() function to remove spaces. The syntax is as follows −UPDATE yourTableName SET yourColumnName=TRIM(yourColumnName);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into removeSpaceDemo(UserId, ...
Read MoreWhy is “LIMIT 0” even allowed in MySQL SELECT statements?
As you know if you use LIMIT 0 in MySQL SELECT statement, it returns an empty set.The LIMIT can be used when you want a specified number of rows from a result rather than the entire rows. If you use any MySQL API, then the job of LIMIT is to acquire the type of result columns.LIMIT 0 can be used to check the validity of a query. For more details use the following link −https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.htmlHere is the demo of LIMIT 0. The query to create a table is as follows −mysql> create table Limit0Demo -> ( -> Id ...
Read MoreMySQL update query to remove spaces between letters?
If you have spaces between letters then you can use REPLACE() function to remove spaces.The syntax is as follows −UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName, ’ ‘, ’’);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removeSpaceDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserId varchar(20), -> UserName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.81 sec)Now insert some records in the table using insert ...
Read MoreREGEX Match integers 6 through 10 in MySQL?
Here you can use BETWEEN operator. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName BETWEEN 6 AND 10;You can use regular expression like this. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName REGEXP '10|[6-9]';To understand the both syntax, let us create a table. The query to create a table is as follows −mysql> create table RegularExpressionDemo -> ( -> Id int -> ); Query OK, 0 rows affected (1.11 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into RegularExpressionDemo values(1); Query OK, ...
Read MoreConvert one base number system to another base system in MySQL
The CONV() function can be used to convert one base number system to another base system.For Example, The 16 is one base system and 10 is another base system. The 16 base system is hexadecimal and 10 is a decimal.The syntax is as follows −SELECT CAST(CONV('yourColumnName', 16, 10) AS UNSIGNED INTEGER) 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 castTypeToBigIntDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Value varchar(100), -> PRIMARY KEY(Id) -> ); Query OK, ...
Read MoreRemove first two characters of all fields in MySQL?
To remove the first two characters of all fields, you need to use SUBSTRING() function from MySQL. The syntax is as follows −UPDATE yourTableName SET yourColumnName=SUBSTRING(yourColumnName, 3) WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RemoveFirstTwoCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> StringValue varchar(30), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.04 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into RemoveFirstTwoCharacterDemo(StringValue) values('U:100'); Query OK, 1 ...
Read MoreGet the record of a specific year out of timestamp in MySQL?
You can get year out of timestamp using YEAR() function. The syntax is as follows −SELECT yourColumnName FROM yourTableName WHERE YEAR(yourTimestampColumnName)='yourYearValue’';To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table getYearOut -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> yourTimestamp timestamp default current_timestamp, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.56 sec)Insert some records in the table using INSERT command−mysql> insert into getYearOut(Name, yourTimestamp) values('John', now()); Query OK, 1 row affected (0.26 sec) ...
Read More