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
Articles by Arjun Thakur
Page 69 of 75
Changing year in MySQL date?
To change the year in MySQL date, you need to use DATE_FORMAT() function with UPDATE command. The syntax is as follows.UPDATE yourTableName SET yourDateColumnName = DATE_FORMAT(yourDateColumnName ,'yourYearValue-%m-%d');To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ChangeYear -> ( -> id int not null auto_increment, -> ArrivalTime date, -> PRIMARY KEY(id) -> ); Query OK, 0 rows affected (0.83 sec)Insert some records in the table using insert command −mysql> insert into ChangeYear(ArrivalTime) values(date_add(now(), interval -2 year)); Query OK, 1 row affected, 1 warning ...
Read MoreWhat is the data type for unix_timestamp in MySQL?
The best data type for unix_timestamp in MySQL is integer. The integer data type is as followsint(11);The integer data type is useful for condition checking like ( > , create table UnixTime -> ( -> DueTime datetime -> ); Query OK, 0 rows affected (0.55 sec)Insert records in the form of date using insert command. The query is as followsmysql> insert into UnixTime values(now()); Query OK, 1 row affected (0.15 sec) mysql> insert into UnixTime values('2010-10-14'); Query OK, 1 row affected (0.15 sec) mysql> insert into UnixTime values('2020-09-24'); Query ...
Read MoreSorting a VARCHAR column as FLOAT using the CAST operator isn't working in MySQL ?
If your cast does not work, then you can use yourColumnName*1 with ORDER BY clause.Using yourColumnName*1. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourColumnName*1 DESC;You can also use CAST() operator. The syntax is as follows:SELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY CAST(yourColumnName as DECIMAL(8, 2)) DESC;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table VarcharColumnAsFloatDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Amount varchar(20), -> PRIMARY KEY(Id) ...
Read MoreHow to alter the database engine of a MySQL database table?
First, determine the type of MySQL database i.e. whether its engine is InnoDB or MyISAM. To achieve this, use engine column from the information_schema.columns.tables.The syntax is as follows.SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ’yourDatabaseName’ AND TABLE_NAME = ’yourTableName’;Here, I have a table with the name ‘StudentInformations’ −mysql> create table StudentInformations -> ( -> StudentId int not null auto_increment, -> StudentFirstName varchar(20), -> StudentLastName varchar(20), -> Primary Key(StudentId) -> ); Query OK, 0 rows affected (0.57 sec)Now you can know the table is using InnoDB or MyISAM using the implementation of above syntax. Our ...
Read MoreHow to Auto Generate Database Diagram in MySQL?
To auto generate database diagram in MySQL, use MySQL workbench. For this, select the option from database as shown below −Database->Reverse EngineerHere is the snapshot showing the Database tab −After clicking “Database” above, choose the option “Reverse Engineer”. This state the “Reverse Engineer” mode.Clicking above will display the following table automatically. We had one table in the database “business”. The same table “tblstudent” is visible here −
Read MoreUsing "TYPE = InnoDB" in MySQL throws an exception?
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 MoreHow to update field to add value to existing value in MySQL?
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 MoreTwo columns as primary key with auto-increment in MySQL?
Achieve this using MyISAM Engine. Here is an example of two columns as primary key with auto-increment.Creating a table with two columns as primary key −mysql> create table TwoPrimaryKeyTableDemo -> ( -> Result ENUM('First', 'Second', 'Third', 'Fail') not null, -> StudentId int not null auto_increment, -> StudentName varchar(200) not null, -> Primary key(Result, StudentId) -> ) -> ENGINE=MyISAM; Query OK, 0 rows affected (0.20 sec)Inserting records into tablemysql> insert into TwoPrimaryKeyTableDemo(StudentName, Result) values('John', 'Fail'); Query OK, 1 row affected ...
Read MoreHow to run SQL script in MySQL?
To run SQL script in MySQL, use the MySQL workbench. First, you need to open MySQL workbench.The snapshot is as follows −Now, File -> Open SQL Script to open the SQL script.Alternatively, use the following shortcut key −Ctrl+Shift+OAfter that an option would be visible for you to choose your .sql file from the disk. In my system, the file is located on the Desktop. I will select the same “tblstudent” SQL file as shown in the following screenshot −After browsing .sql files, you need to select the option “Reconnect to database” as shown in the following screenshot −Now, it will ...
Read MoreWhat is the Maximum Value of smallint(6) unsigned in MySQL?
The maximum value of SMALLINT(6) unsigned in MySQL is 65535. The number 6 does not affect the actual range. It can only be used to display width on the command line.The Minimum Value signed is-32768The Maximum Value unsigned is65535The Maximum value signed is32767Let us understand this with zerofill and create a table using the following query.mysql> create table smallIntDemo -> ( -> FirstNumber smallint(6) zerofill -> ); Query OK, 0 rows affected (1.95 sec)Now you can insert records in the table using insert command. Whenever you insert beyond the range 65535, it will not insert in the table, since this ...
Read More