MySQL Articles - Page 232 of 355

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 More

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 More

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 More

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 More

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 More

You can use IFNULL() property or simple IF() with IS NULL property. The syntax is as follows −INSERT INTO yourTableName(yourColumnName1, yourColumnName2) VALUES('yourValue’', IF(yourColumnName1 IS NULL, DEFAULT(yourColumnName2), 'yourMessage'));To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table Post -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> UserName varchar(10), -> UserPostMessage varchar(50) NOT NULL DEFAULT 'Hi Good Morning !!!' -> ); Query OK, 0 rows affected (0.67 sec)Now you can ... Read More

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 More

Use the DATE_FORMAT(), not FORMATDATE() in MySQL to format date. The correct syntax is as follows −SE LECT *, DATE_FORMAT(yourDateCoumnName, ’yourFormat’) as anyAliasName FROM yourTableNameTo understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table DateFormatDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> UserName varchar(10),    -> UserLoginDate date    -> ); Query OK, 0 rows affected (0.94 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into DateFormatDemo(UserName, UserLoginDate) values('Mike', curdate()); Query OK, 1 ... Read More

The max_heap_table_size is a system variable that has both read/write property.Initially, max_heap_table_size has size 16 MB. First, check the value of max_heap_table_size, which is in bytes.The query is as follows −mysql> select @@max_heap_table_size;The following is the output −+-----------------------+ | @@max_heap_table_size | +-----------------------+ | 16777216 | +-----------------------+ 1 row in set (0.00 sec)Now let us how the value 16777216 byte = 16 MB −1 MB = 1024KB 1KB = 1024 Bytes 1MB = 1024*1024 bytes. To convert 16777216 byte to MB you need to divide 1024*1024. =16777216/(1024*1024) =16777216/1048576 ... Read More

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 More