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 56 of 355
Avoid placing password on command line with MySQL Utilities?
First you need to reach the location of “my.cnf” with the help of below query for MySQL Utilities. The query is as follows −mysql> select @@datadir;The following is the output that display where “my.conf” is −+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Follow the above path in order to open the “my.cnf” file. The snapshot of “my.cnf” location is as follows −Now you can put the below MySQL Utilities in my.cnf which is as ...
Read MoreHow to resolve the ERROR 1115 (42000): Unknown character set: 'utf8mb4'?
You will get this type of error when your MySQL version is below 5.5.3. This is because “utf8mb4” introduced in MySQL version 5.5.3.Firstly, you need to check the current version. If its less than 5.5.3, then you need to upgrade to solve the above error.Check the current version −mysql> select version();Here, our MySQL version is over 5.5.3 −+-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec)Now the same query that gave an error 1115, will display correct result. To check all character set in MySQL now, use the below query.mysql> show character set;The ...
Read MoreHow to subtract 3 hours from a datetime in MySQL?
Subtract 3 hours from DateTime in MySQL, using any of the following ways. The first approach is as follows −Case 1 − Using DATE_ADD()select date_add(yourColumnName, interval -3 hours) from yourTableName;Case 2 − Using DATE_SUB()select date_sub(yourColumnName, interval 3 hours) from yourTableName;Firstly, use now() to get the current date-time −mysql> select now();The following is the output −+---------------------+ | now() | +---------------------+ | 2018-11-30 10:13:23 | +---------------------+ 1 row in set (0.00 sec)DATE_ADDThe query to subtract 3 hours from DateTime is as follows. With date_add, we have set a negative date −mysql> select date_add(now(), interval -3 ...
Read MoreSimple way to toggle a value of an int field in MySQL
To toggle a value of an int field, you can use update command with if(). The syntax is as follows −update yourTableName set yourColumnName = IF(yourColumnName = 0, 1, 0);To understand the above toggle syntax, create a table with some int value. The query to create a table is as follows −mysql> create table ToggleDemo −> ( −> IsOnOrOff int −> ); Query OK, 0 rows affected (0.53 sec)Let us insert int values in the table with the help of insert command. The query is as follows −mysql> insert into ToggleDemo values(1); Query OK, 1 ...
Read MoreHow to find the highest number in a column?
You can find the highest number in a column with the help of aggregate function MAX. The syntax is as follows −select max(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us create a table with an int column. The following is the query to create a table.mysql> create table HighestNumberDemo −> ( −> BigNumber int −> ); Query OK, 0 rows affected (0.87 sec)Now insert some values in the table. The query to insert records are as follows −mysql> insert into HighestNumberDemo values(1234); Query OK, 1 row affected (0.43 sec) mysql> insert ...
Read MoreMaintaining order in MySQL "IN" query?
You can maintain the order in MySQL IN query with the help of field command. The syntax is as follows −select *from yourTableName anyVariableName where anyVariableName.yourColumnName in(value1, value2, ......N) order by field(anyVariableName.yourColumnName, value1, value2, ......N);To implement the above syntax let us create a table −mysql> create table OrderInDemo −> ( −> Id int, −> Name varchar(100), −> Age int −> ); Query OK, 0 rows affected (1.24 sec)Now let us insert some records in the table. The query to insert records are as follows −mysql> insert into OrderInDemo values(90, ...
Read MoreHow to change Table Engine in MySQL?
You can change table engine with the help of alter command. The syntax is as follows −alter table yourTableName ENGINE = yourEngineName;To understand the above syntax let us create a table with engine MyISAM. Later you can change any other engine. The following is the query to create a table.mysql> create table ChangeEngineTableDemo −> ( −> MovieId int, −> MovieName varchar(100), −> IsPopular bool −> )ENGINE = 'MyISAM'; Query OK, 0 rows affected (0.37 sec)Look at the above query, the table engine is MyISAM, now you can change it to any other engine. Here, we will change ...
Read MoreGet date format DD/MM/YYYY with MySQL Select Query?
Use the STR_TO_DATE() function from MySQL to set a date format for displaying DD/MM/YYYY date. The syntax is as follows −SELECT STR_TO_DATE(yourColumnName, ’%d/%m/%Y) as anyVariableName from yourTableName.To understand the above syntax, let us create a table −mysql> create table DateFormatDemo −> ( −> IssueDate varchar(100) −> ); Query OK, 0 rows affected (0.54 sec)Inserting some string dates into the table. The query to insert date is as follows −mysql> insert into DateFormatDemo values('26/11/2018'); Query OK, 1 row affected (0.14 sec) mysql> insert into DateFormatDemo values('27/11/2018'); Query OK, 1 row affected (0.18 sec) mysql> ...
Read MoreHow to cast DATETIME as a DATE in MySQL?
To cast DATETIME as a DATE in MySQL, use the CAST() function. The syntax is as follows −select cast(yourColumnName as Date) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table −mysql> create table ConvertDatetimeToDate −> ( −> YourDatetime datetime −> ); Query OK, 0 rows affected (0.95 sec)Inserting datetime into the table with the help of insert command. The query is as follows −mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.17 sec) mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval -1 day)); Query OK, 1 row affected (0.15 sec) ...
Read MoreCreate a new table with the properties of an old table and without duplicates using MySQL LIKE Operator?
To achieve this with LIKE operator, the following is the syntax −CREATE TABLE yourTableName2 LIKE yourTableName1;To understand the syntax, let us create a table and insert some records into it. The following is the query to create a table −mysql> create table Employee −> ( −> EmployeeId int −> , −> EmployeeName varchar(100) −> ); Query OK, 0 rows affected (0.54 sec)Inserting records into the table with the help of insert command. The query is as follows −mysql> insert into Employee values(1, 'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into Employee values(2, 'John'); ...
Read More