Database Articles - Page 455 of 671

You can verify SET FOREIGN KEY CHECKS is set to = 1 or not with the help of variable@@foreign_key_checks;The syntax is as follows −select @@foreign_key_checks;You can use SHOW VARIABLES command. The syntax is as follows −show variables like 'foreign%';Now you can implement both the syntaxes.Case 1 − Using variable @@foreign_key_checks.The query is as follows −mysql> SELECT @@foreign_key_checks; Here is the output −+----------------------+ | @@foreign_key_checks | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec)Case 2 − Using SHOW commandThe ... Read More

Yes, to query for a field in an object in the array with MongoDB, use the following syntax −db.yourCollectionName.find({"yourOuterFieldName": { $elemMatch: { "yourInnerFieldName": "yourValue" } } } ).pretty();To understand the above concept, let us create a collection with the document. The query to create a collection with a document is as follows −> db.objectInAnArrayDemo.insertOne({ "StudentDetails": [{    "StudentName": "John", "StudentMessage": "Hi"}, {"StudentName": "Larry", "StudentMessage": "Hello"}]}) {    "acknowledged" : true,    "insertedId" : ObjectId("5c92635d36de59bd9de06381") } > db.objectInAnArrayDemo.insertOne({ "StudentDetails": [{    "StudentName": "Carol", "StudentMessage": "Hello"}, {"StudentName": "David", "StudentMessage": "Good Morning"}]}) {    "acknowledged" : true,    "insertedId" : ObjectId("5c92637936de59bd9de06382") }Display all documents ... Read More

Ampersands work in Oracle. To work it in MySQL, use @ as shown in the following syntax −SET @yourVariableName1 = yourValue, @yourVariableName2 = yourValue, @yourVariableName3 =yourValue, .........N; insert into yourTableName values(@yourVariableName1, @yourVariableName2, @yourVariableName3, ........N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Student_Information    -> (    -> StudentId int,    -> StudentName varchar(100),    -> StudentAge int,    -> StudentMarks int,    -> StudentCountryName varchar(10)    -> ); Query OK, 0 rows affected (0.75 sec)Here is the query with values prepended by @. Insert some records ... Read More

Use IF() function to return null for date_format when input is null in MySQL. The syntax is as follows −SELECT IF(yourDateColumnName, date_format(yourDateColumnName, '%d/%m/%Y'), NULL) FROM yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table returnNullWhenInputIsNullDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> ShippingDate datetime    -> ); Query OK, 0 rows affected (1.21 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into returnNullWhenInputIsNullDemo(ShippingDate) values('2019-01-21'); Query OK, 1 row affected ... Read More

You can use printjson() method to print to console an object in a MongoDB script. The syntax is as follows −printjson({yourFieldName”:yourValue”, ........N});You can use JSON.stringify() along with print() function. The syntax is as follows minus;print ( JSON.stringify( { {yourFieldName”:yourValue”, ........N} } ));Let us implement the above syntax to print object in Mongo script. The query is as follows −>printjson({"UserId":101, "UserName":"John", "UserCoreSuject":["Java", "MongoDB", "MySQL", "SQL Server"]});The following is the output −{    "UserId" : 101,    "UserName" : "John",    "UserCoreSuject" : [       "Java",       "MongoDB",       "MySQL",       "SQL Server"   ... Read More

To query MySQL on the current week, you can use YEARWEEK() function.The syntax is as followsSELECT *FROM yourTableName WHERE YEARWEEK(yourDateColumnName) = YEARWEEK(NOW());To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table currentWeekDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(20), -> UserPostDate date -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into currentWeekDemo(UserName, ... Read More

Use DateTime to convert PHP variable “11:00 AM: to MySQL time format.The PHP code is as follows −$phpTime = '11:00 AM'; echo('The PHP Time Format is ='); echo ($phpTime); $timeFormat = DateTime::createFromFormat( 'H:i A', $phpTime); $MySQLTimeFormat = $timeFormat->format( 'H:i:s'); echo (' '); echo('The MySQL Time Format is ='); echo ($MySQLTimeFormat);The snapshot of PHP code is as follows −Here is the output −The PHP Time Format is =11:00 AM The MySQL Time Format is =11:00:00

You can use lc_messages to change MySQL error message language. The syntax is as follows −SET lc_messages = 'yourLanguage';To understand the concept, let us create a table with some error and check the error message language.Here, we have set the local message to French. Let us first create a table −mysql> create table errorMessagelanguage    -> (    -> Error_MessageId int,    -> Error_Message varchar(100),    -> );The error message is as follows −ERROR 1064 (42000): Erreur de syntaxe près de ')' à la ligne 5Now you can set the error message to be in English language. The query is ... Read More

First check all the user and host from MySQL.user table with the help of select statement as shown belowmysql> select user, host from MySQL.user;The following is the output+------------------+-----------+ | user             | host      | +------------------+-----------+ | Bob              | %         | | Manish           | %         | | User2            | %         | | mysql.infoschema | %         | | mysql.session    | %   ... Read More

To retrieve a value from MongoDB by its key name, use the following syntax −db.yourCollectionName.find({}, {"yourFieldName":1}).pretty();To understand the above syntax, let us create a collection with the document. The query to create a collection with a document is as follows −> db.retrieveValueFromAKeyDemo.insertOne({"CustomerName":"Larry", "CustomerAge":21, "CustomerCountryName":"US"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c9163b5a56efcc0f9e69048") } > db.retrieveValueFromAKeyDemo.insertOne({"CustomerName":"Chris", "CustomerAge":24, "CustomerCountryName":"AUS"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c9163c4a56efcc0f9e69049") } > db.retrieveValueFromAKeyDemo.insertOne({"CustomerName":"Mike", "CustomerAge":26, "CustomerCountryName":"UK"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5c9163d3a56efcc0f9e6904a") }Display all documents from a collection with the help of find() method. The query is as follows −> db.retrieveValueFromAKeyDemo.find().pretty();The ... Read More