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 260 of 341
How do I insert a special character such as ' (single quote) into MySQL?
To insert a special character such as “ ‘ “ (single quote) into MySQL, you need to use \’ escape character. The syntax is as follows −insert into yourTableName(yourColumnName) values(' yourValue\’s ');To understand the above syntax, let us create two tables. The query to create first table is as follows −mysql> create table AvoidInsertErrorDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Sentence text -> ); Query OK, 0 rows affected (0.53 sec)Now you can insert special character such as ‘ in the table using insert command. The query is as follows −mysql> insert into AvoidInsertErrorDemo(Sentence) values('a ...
Read MoreWorking with dates before 1970 in MySQL?
You need to use date type to work with date before 1970 because date stores value from 1000 to 9999. A date type can be used when you need to work with date part only not for time purpose.MySQL gives the data in the following format. The format is as follows −‘YYYY-MM-DD’The starting date range is as follows −1000-01-01The ending date range is as follows −9999-12-31To understand what we discussed above, let us create two tables. The query to create first table is as follows −mysql> create table DateDemo -> ( -> Id int ...
Read MoreThe best data type to store 0, 1, null values in MySQL?
You need to use tinyint(1) unsigned NULL to store the value 0, 1 and null values. The syntax is as follows −yourColumnName TINYINT(1) UNSIGNED NULL;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table StoreValue0and1orNULLDemo -> ( -> isDigit TINYINT(1) UNSIGNED NULL -> ); Query OK, 0 rows affected (0.63 sec)Now you can insert records 0, 1, and NULL in the table using insert command. The query is as follows −mysql> insert into StoreValue0and1orNULLDemo values(0); Query OK, 1 row ...
Read MoreHow to select MySQL rows in the order of IN clause?
You need to use FIND_IN_SET() function to select MySQL rows in the order of IN clause. The syntax is as follows −SELECT yourVariableName.* FROM yourTableName yourVariableName WHERE yourVariableName.yourColumnName IN(value1, value2, ...N) ORDER BY FIND_IN_SET( yourVariableName.yourColumnName, 'value1, value2, ...N');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InDemo -> ( -> CodeId int, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.95 sec)Insert some records in the table using insert command. The query is ...
Read MoreMultiple Inserts for a single column in MySQL?
The syntax for multiple inserts for a single column in MySQL is as follows −INSERT INTO yourTableName(yourColumnName) values(‘yourValue1'), (‘yourValue2'), (‘yourValue3'), (‘yourValue4'), .........N;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table InsertMultipleDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> UserName varchar(10), -> UserRole varchar(20) -> , -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (3.14 sec)Now you can insert some records in the ...
Read MoreHow to create a record in MySQL database subject to TTL (Time to live) option?
You need to use MySQL event scheduler. It manages the execution of events as well as scheduling.First, you need to create a table. After that you can create a event that will schedule every single day.Let us create a table. The query to create a table is as follows −mysql> create table EventDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> EventDateTime datetime -> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table using insert command. The query ...
Read MoreMySQL new user access denied even after giving privileges?
After creating a user and giving all privileges to the user, you need to FLUSH PRIVILEGES to set up and want the new settings to work correctly.The syntax is as follows −FLUSH PRIVILEGES;Here is the query to create a new user which has the name ‘Bob’ in my case. The query to create a new user is as follows −mysql> CREATE USER 'Bob'@'%' IDENTIFIED BY '123456'; Query OK, 0 rows affected (0.56 sec)Now given all privileges to user Bob −mysql> GRANT ALL PRIVILEGES ON *.* TO 'Bob'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.23 sec)Now flush the privileges. ...
Read MoreMySQL Merge selects together?
To merge selects together, you need to use GROUP BY clause. To understand the concept, let us create a table. The query to create a table is as follows −mysql> create table MergingSelectDemo -> ( -> RoomServicesId int, -> RoomId int, -> ServiceId int -> ); Query OK, 0 rows affected (1.98 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into MergingSelectDemo values(10, 10, 10); Query OK, 1 row affected (0.29 sec) mysql> insert into MergingSelectDemo values(20, 10, 20); Query OK, 1 row affected ...
Read MoreROW_NUMBER() equivalent in MySQL for inserting?
There is no equivalent of ROW_NUMBER() in MySQL for inserting but you can achieve this with the help of variable. The syntax is as follows −SELECT (@yourVariableName:=@yourVariableName + 1) AS `anyAliasName`, yourColumnName1, yourColumnName2, ...N FROM yourTableName ,(SELECT @yourVariableName:=0) AS anyAliasName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RowNumberDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. The ...
Read MoreSetting column values as column names in the MySQL query result?
To set column values as column names in the query result, you need to use a CASE statement.The syntax is as follows −select yourIdColumnName, max(case when (yourColumnName1='yourValue1') then yourColumnName2 else NULL end) as 'yourValue1', max(case when (yourColumnName1='yourValue2') then yourColumnName2 else NULL end) as 'yourValue2', max(case when yourColumnName1='yourValue3') then yourColumnName2 else NULL end) as 'yourValue3’, . . N from valueAsColumn group by yourIdColumnName order by yourIdColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table valueAsColumn -> ( -> UserId int, -> UserColumn1 varchar(10), -> ...
Read More