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
MySQL Articles
Page 273 of 355
Format date in MySQL SELECT * query uisng FORMATDATE() method?
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 MoreSort certain values to the top in MySQL?
You need to use ORDER BY clause to sort. The syntax is as follows −SELECT *FROM yourTableName ORDER BY yourColumnName='yourValue' DESC, yourIdColumnName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table SortCertainValues -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> CountryName varchar(10), -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SortCertainValues(Name, CountryName) values('Adam', 'US'); Query OK, 1 row ...
Read MoreInsert default into not null column if value is null in MySQL?
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 MoreMySQL Order By date column and integer column, but specify ordering rules of integer column? Is it possible?
You can achieve this with the help of ORDER BY CASE statement. The syntax is as follows −SELECT *FROM yourTableName ORDER BY CASE yourIntegerColumnName1 WHEN 2 THEN 1 ELSE 0 END DESC ,yourDateColumnName ASC;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table OrderByCaseDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> GroupId int, -> ArrivalDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.57 sec)Insert some records in the table using insert command. The query is as ...
Read MoreHow 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 More