You can create a table from view using create table select syntax. The syntax is as follows −CREATE TABLE yourTableName AS SELECT yourColumnName1, yourColumnName2, yourColumnName3, ........N from yourViewName;To run the above query, first you need to create a table and after that you need to create a view on that table. After that run the query.First, you need to create a table. The query to create a table is as follow −mysql> create table StuedntInformation    -> (    -> Id int,    -> Name varchar(100)    -> ); Query OK, 0 rows affected (0.54 sec)Above, we have created a ... Read More

You can achieve this with the help of INFORMATION_SCHEMA.TABLES. Use the date_sub() with interval. The syntax is as follows −SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE DATE_SUB(NOW(), INTERVAL -1HOUR) < ‘UPDATE_TIME’;Now you can check the above syntax. Here is the query to find the tables modified in the last hour −mysql> select table_name from `INFORMATION_SCHEMA`.`TABLES` -> WHERE -> DATE_SUB(NOW(), INTERVAL 1 HOUR) < `UPDATE_TIME`;Output+---------------------+ | TABLE_NAME          | +---------------------+ | innodb_table_stats  | | innodb_index_stats  | | employeeinformation | +---------------------+ 3 rows in set (0.37 sec)The above query selects only table name. If you want information like table schema, table ... Read More

You can use SELECT IF statement with OR. To understand select with OR, let us create a table. The query to create a table is as follows −mysql> create table EmployeeInformation    -> (    -> EmployeeId int,    -> EmployeeName varchar(100),    -> EmployeeStatus varchar(100)    -> ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into EmployeeInformation values(1, 'Sam', 'FullTime'); Query OK, 1 row affected (0.23 sec) mysql> insert into EmployeeInformation values(2, 'Mike', 'PartTime'); Query OK, 1 row affected (0.14 sec) mysql> ... Read More

In MySQL, there is no way to truncate with condition. You cannot use truncate statement with where clause.If you want the condition, use delete command −DELETE FROM yourTableName WHERE youCondition;The above syntax is fine but if you want a faster solution, then DELETE is not good in comparison to Truncate. The advantage with truncate is that it does not write to the logs.Let us create a table. The query to create a table is as follows −mysql> create table DeleteDemo -> ( -> Id int, -> Name varchar(100) ... Read More

Whenever you retrieve datetime from a table, the datetime gives ‘YYYY-MM-DD’ format. If you want to change the output, then you need to use in-built date_format() from MySQL.The syntax is as follows −SELECT DATE_FORMAT(yourDatetimeColumnName, yourFormat) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UserDateFormat -> ( -> ProductId int, -> ProductDeliverDate datetime -> ); Query OK, 0 rows affected (0.93 sec)Insert some records in the table using insert command. The ... Read More

SQLSQL is a type of language that can be used to utilize your database. It is a base language for databases like MySQL, SQL Server, Oracle etc. SQL stands for Structure Query Language and it can be used to utilize the relational database management system.This can also be used for accessing, manipulating and updating data in the database using some commands. The SQL commands are as follows −SELECTUPDATE, etc.SQL can also be used in the creation of schema as well as controlling the data access.MySQLMySQL is a relational database management system that utilizes the SQL command. MySQL provides the tools ... Read More

The number 2 and 1 in TINYINT(2) vs TINYINT(1) indicate the display width. There is no difference between tinyint(1) and tinyint(2) except the width.If you use tinyint(2) or even tinyint(1), the difference is the same. You can understand the above concept using zerofill option.tinyint(1) zerofilltinyint(2) zerofillLet us create a table. The query to create a table is as follows −mysql> create table tinyIntDemo    -> (    -> Number1 tinyint(1) zerofill,    -> Number2 tinyint(2) zerofill    -> ); Query OK, 0 rows affected (0.62 sec)Insert record in the table using insert command. The query is as follows −mysql> insert ... Read More

You can use in-built function date_add() from MySQL. The syntax is as follows −UPDATE yourTableName SET yourDateColumnName=DATE_ADD(yourDateColumnName, interval 1 year);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table UpdateDate -> ( -> Id int, -> DueDate datetime -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command. The query to insert record is as follows −mysql> insert into UpdateDate values(1001, '2012-5-21'); Query OK, 1 row affected (0.17 ... Read More

To set user variable from result of query in MySQL, you need to move that variable into the assignment.To understand the above concept, let us first create a table. The query to create a table is as follows −mysql> create table UserVariable -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command. The query is as follows −mysql> insert into UserVariable values(101, 'John'); Query OK, 1 row affected (0.17 sec) mysql> insert ... Read More

You can use alter command. The syntax is as follows −ALTER TABLE yourTableName MODIFY COLUMN yourColumNam enum(yourOldValue1, yourOldValue2, ....N, yourNewValue1, yourNewValue2, ....N);To understand the above syntax, let us first create a table. The query to create a table is as follows −mysql> create table EnumDemo -> ( -> AllColors enum('RED', 'GREEN') -> ); Query OK, 0 rows affected (0.66 sec)Now you have two members in AllColors of enum type. If you want to add more members to your enum type, use the following query −mysql> alter table EnumDemo -> modify column ... Read More