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 260 of 341
How to disable "Establishing SSL connection without server's identity verification is not recommended" warning when connecting to MySQL database in Java?
To disable the warning while connecting to a database in Java, use the below concept −autoReconnect=true&useSSL=falseThe complete syntax is as follows −yourJdbcURL="jdbc:mysql://localhost:yourPortNumber/yourDatabaseName?autoReconnect=true&useSSL=false";Here is the warning message if you do not include “useSSL=false” −Wed Feb 06 18:53:39 IST 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore ...
Read MorePerform search/replace for only the first occurrence of a character in MySQL table records?
You can achieve this with the help of CONCAT() along with REPLACE() function. To find the first occurrences you need to use INSTR() function.The syntax is as follows −UPDATE yourTableName SET UserPost = CONCAT(REPLACE(LEFT(yourColumnName, INSTR(yourColumnName, 'k')), 'k', 'i'), SUBSTRING(yourColumnName, INSTR(yourColumnName, 'k') + 1));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table UserInformation -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(10), -> UserPost text -> ); Query OK, ...
Read MoreHow to correctly enclose subquery in MySQL?
You need to close the subquery in a parenthesis. The syntax is as follows −select if((select count(*) from yourTableName ), 'Yes', 'No') as anyAliasName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SelectIfDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(10) -> ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SelectIfDemo(Name) values('John'); Query OK, ...
Read MoreRename all tables and columns to lower case in MySQL?
You can achieve this with the help of INFORMATION_SCHEMA.COLUMNS. The syntax is as follows −SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `', LOWER(COLUMN_NAME), '` ', COLUMN_TYPE, ';') AS anyAliasName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ‘yourDatabaseName’;Now use the database which has two tables. The database name is as follows “bothinnodbandmyisam”. This database is having the following tables −employeestudentThe description of the employee table is as follows −mysql> desc employee;The following is the output. Let’s say we have the following columns in the employee table which are not in lowercase −+--------------+-------------+------+-----+---------+-------+ | Field | Type ...
Read MoreGet total number of rows while using LIMIT in MySQL?
To get the total number of rows when using LIMIT, use the following syntax −select SQL_CALC_FOUND_ROWS * FROM yourTableName LIMIT 0, yourLastValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table RowsUsingLimit -> ( -> Id int NOT NULL, -> Name varchar(10) -> ); Query OK, 0 rows affected (3.50 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into RowsUsingLimit values(10, 'Larry'); Query OK, ...
Read MoreHow 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 More