You can use date_format() to convert varchar to date. The syntax is as follows −SELECT DATE_FORMAT(STR_TO_DATE(yourColumnName, 'yourFormatSpecifier'), 'yourDateFormatSpecifier') as anyVariableName from yourTableName;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table VarcharToDate    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Created_Time varchar(100),    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (1.10 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into VarcharToDate(Created_Time) values('12/1/2016'); Query OK, 1 row affected (0.14 sec) mysql> insert ... Read More

If you want to select a single row on the basis of primary key, use the WHERE clause. The syntax is as follows −SELECT * FROM yourTableName WHERE yourPrimaryKeyColumnName = someValue;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table selectWithPrimaryKey    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(20),    -> Age int,    -> Marks int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command. The query is ... Read More

There is no difference between cast as Date and date() function in MySQL.The syntax of both cast() and date() is as follows −cast(yourDateTimeColumnName as Date) date(yourDateTimeColumnName)Both functions internally call Item_date_typecast. To check both the functions, let us create a table. The query to create a table is as follows −mysql> create table CastAsDateDemo    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> ArrivalTime datetime,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.71 sec)Now you can insert some records in the table using insert command. The query is as follows −mysql> insert into ... Read More

You can SELECT ….WHERE id IN(..) using field() function to order with any column. The syntax is as follows −SELECT *FROM yourTableName WHERE yourColumnName IN(‘value1’, ’value2’, .......N) ORDER BY FIELD(yourColumnName, value1’, ’value2’, .......N);To understand the above syntax, let us create a table −mysql> create table SelectOrderbyField    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(30),    -> Age int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into SelectOrderbyField(Name, Age) values('John', 23); Query OK, ... Read More

You can update field to add value to an existing value with the help of UPDATE and SET command. The syntax is as follows −UPDATE yourTableName SET yourColumnName = yourColumnName+integerValueToAdd WHERE yourCondition;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table addingValueToExisting    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Name varchar(30),    -> GameScore int,    -> PRIMARY KEY(Id)    -> ); Query OK, 0 rows affected (0.58 sec)Insert records in the table using insert command. The query is as follows −mysql> insert ... Read More

The problem with UNIX_TIMESTAMP() function is that it returns an integer while we want to insert custom date i.e. not any integer part to MySQL date.Do not use UNIX_TIMESTAMP() for your column defined as TIMESTAMP because UNIX_TIMESTAMP() returns an integer.Check the UNIX_TIMESTAMP. The query is as follows −mysql> select UNIX_TIMESTAMP( '2019-01-09 15 −48 −23') AS IntegerValue;The following is the output −+--------------+ | IntegerValue | +--------------+ | 1547029103   | +--------------+ 1 row in set (0.00 sec)Look at the sample output, the UNIX_TIMESTAMP() function returns an integer of corresponding date and time.The syntax is as follows to insert custom date for ... Read More

You can use ALTER command to remove primary key and auto_increment. The syntax is as follows −ALTER TABLE yourTableName DROP PRIMARY KEY, change yourColumnName yourColumnName data type;To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table removePrimaryKey    -> (    -> StudentId int NOT NULL AUTO_INCREMENT,    -> StudentFirstName varchar(20),    -> StudentLastName varchar(20),    -> PRIMARY KEY(StudentId)    -> ); Query OK, 0 rows affected (0.47 sec)Check the description of table using DESC command. The syntax is as follows −desc yourTableName;Check the description of the table ... Read More

You can add a new value to a column of data type enum using ALTER MODIFY command.If you want the existing value of enum, then you need to manually write the existing enum value at the time of adding a new value to column of data type enum.The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourEnumColumnName ENUM('yourExistingValue1', 'yourExistingValue2', ........N, 'yourNewValue1', ’yourNewValue2’, .....N);To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table AddingNewValueToEnumColumn    -> (    -> Id int NOT NULL AUTO_INCREMENT,    -> Colors ENUM('RED', ... Read More

You can use ENGINE = InnoDB in place of TYPE = InnoDB, since the usage of TYPE became obsolete in MySQL version 5.1.The version we are using for our example is MySQL version 8.0.12. Let us check the MySQL version. The query is as follows −mysql> select version();The following is the output −+-----------+ | version() | +-----------+ | 8.0.12    | +-----------+ 1 row in set (0.00 sec)Here is the example of TYPE = InnoDB. Error is visible in MySQL 8 −mysql> create table Product_Information    -> (    -> ProductId int,    -> ProductName varchar(10),    -> ProductDeliveryDate datetime ... Read More

To grant a user permission to only view a MySQL view, use the below syntaxGRANT SELECT ON yourDatabaseName.yourViewName TO ' yourUserName@'yourLocalHost';First you need to display all the view names from a table. The syntax is as follows −SHOW FULL TABLES IN yourDatabaseName WHERE TABLE_TYPE LIKE 'VIEW';Now implement the above syntax to display all views from a database. Here I am using the database name ‘test’. The query is as follows −mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';The following is the output −+-------------------------------+------------+ | Tables_in_test                | Table_type | +-------------------------------+------------+ | empidandempname_view ... Read More