MySQL Articles - Page 427 of 439

We can see the create table statement of an existing table by using SHOW CREATE TABLE query.SyntaxSHOW CREATE TABLE table_name;Examplemysql> Show create table employee\G *************************** 1. row *************************** Table: employee Create Table: CREATE TABLE `employee` (    `Id` int(11) DEFAULT NULL,    `Name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)The query above gives the CREATE TABLE statement of ‘Employee’ table.

If we want to get more details about the column of an existing table then we need to use SHOW FULL COLUMNS statement. Consider the example below in which SHOW FULL COLUMNS statement has been applied on ‘Employee’ table and MySQL returns result set with some extra details like Collation, Privileges, and Comment, about the columns of the table −mysql> SHOW FULL COLUMNS FROM EMPLOYEE\G *************************** 1. row ***************************      Field: Id       Type: int(11)  Collation: NULL       Null: YES        Key:    Default: NULL      Extra: Privileges: select, insert, update, references ... Read More

Different unit values which can be used with MySQL INTERVAL keyword are as follows −MICROSECONDThis unit will be used for adding or subtracting the number of specified microseconds from the current time or as provided by the user.mysql> Select NOW()+INTERVAL 100 MICROSECOND +--------------------------------+ | NOW()+INTERVAL 100 MICROSECOND | +--------------------------------+ | 2017-10-28 18:47:25.000100     | +--------------------------------+ 1 row in set (0.00 sec)Above query will add 100 microseconds to the current date & time with the help of MySQL INTERVAL keyword.mysql> Select '2017-02-25 05:04:30' + INTERVAL 100 Microsecond; +--------------------------------------------------+ | '2017-02-25 05:04:30' + INTERVAL 100 Microsecond | +--------------------------------------------------+ | ... Read More

Suppose if we have stored a date value as ‘0000-00-00’ in MySQL table then on extracting year value from such kind of date, MySQL will return 0. It would not be in either Year(2) or Year(4) format. To understand it we are using the following data from ‘detail_bday’ table −mysql> Select * from detail_bday; +----+---------+------------+ | Sr | Name    | Birth_Date | +----+---------+------------+ | 1 | Saurabh  | 1990-05-12 | | 2 | Raman    | 1993-06-11 | | 3 | Gaurav   | 1984-01-17 | | 4 | Rahul    | 1993-06-11 | | 5 | Sonia   ... Read More

With the help of following MySQL query, we can get the last day of next month −mysql> SELECT LAST_DAY(now() + INTERVAL 1 MONTH) AS 'LAST DAY OF NEXT MONTH'; +------------------------+ | LAST DAY OF NEXT MONTH | +------------------------+ | 2017-11-30             | +------------------------+ 1 row in set (0.00 sec)

With the help of following MySQL query, we can get the last day of previous month −mysql> SELECT LAST_DAY(now() - INTERVAL 1 MONTH) AS 'LAST DAY OF PREVIOUS MONTH'; +----------------------------+ | LAST DAY OF PREVIOUS MONTH | +----------------------------+ | 2017-09-30                 | +----------------------------+ 1 row in set (0.00 sec)

Yes, we can use DESCRIBE or EXPLAIN statements instead of SHOW COLUMNS statement to get the list of the columns in an existing table. In the example below we have applied DESCRIBE and EXPLAIN statement on ‘Employee’ table and got the same result set as got after SHOW COLUMNS statement −mysql> DESCRIBE Employee\G *************************** 1. row ***************************   Field: Id    Type: int(11)    Null: YES     Key: Default: NULL   Extra: *************************** 2. row ***************************   Field: Name    Type: varchar(20)    Null: YES     Key: Default: NULL   Extra: 2 rows in set (0.05 sec) ... Read More

Suppose if we forgot the names of the columns in an existing table then we can use SHOW COLUMNS statement as follows to get the list of columns −mysql> SHOW COLUMNS from Employee\G *************************** 1. row ***************************   Field: Id    Type: int(11)    Null: YES     Key: Default: NULL   Extra: *************************** 2. row ***************************   Field: Name    Type: varchar(20)    Null: YES     Key: Default: NULL   Extra: 2 rows in set (0.07 sec)In the example above, we got the list of columns of ‘Employee’ table with the help of SHOW COLUMNS statement.

As we know that we can copy the data and structure from an existing table by CTAS script. If we want to copy data with some condition/s then we need to use WHERE clause with CTAS script. Consider the example below −mysql> Create table EMP_BACKUP2 AS SELECT * from EMPLOYEE WHERE id = 300 AND Name = 'Mohan'; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from EMP_BACKUP2; +------+-------+ | Id   | Name  | +------+-------+ | 300  | Mohan | +------+-------+ 1 row in set (0.00 sec)In the example ... Read More

As we know that we can copy the data and structure from an existing table by CTAS script. If we want to select some specific column/s from another table then we need to mention them after SELECT. Consider the following example in which we have created a table named EMP_BACKUP1 by selecting a specific column ‘name’ from already existing table ‘Employee’ −mysql> Create table EMP_BACKUP1 AS Select name from employee; Query OK, 3 rows affected (0.25 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> Select * from EMP_BACKUP1; +--------+ | name   | +--------+ | Ram    | | ... Read More