MySQL Articles - Page 428 of 439

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

With the help of following MySQL query, we can get the first day of next month −mysql> SELECT DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH,'%Y-%m-01') AS 'FIRST DAY OF NEXT MONTH'; +-------------------------+ | FIRST DAY OF NEXT MONTH | +-------------------------+ | 2017-11-01              | +-------------------------+ 1 row in set (0.00 sec)

With the help of following MySQL query, we can get the first day of previous month −mysql> SELECT DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,'%Y-%m-01') AS 'FIRST DAY OF PREVOIUS MONTH'; +-----------------------------+ | FIRST DAY OF PREVOIUS MONTH | +-----------------------------+ | 2017-09-01                  | +-----------------------------+ 1 row in set (0.00 sec)

With the help of following MySQL query, we can get the first day of the current month −mysql> SELECT DATE_SUB(LAST_DAY(NOW()),INTERVAL DAY(LAST_DAY(NOW()))- 1 DAY) AS 'FIRST DAY OF CURRENT MONTH'; +----------------------------+ | FIRST DAY OF CURRENT MONTH | +----------------------------+ | 2017-10-01                 | +----------------------------+ 1 row in set (0.00 sec)

With the help of CTAS i.e. “Create Table AS Select” script we can create a table from an existing table. It copies the table structure as well as data from the existing table. Consider the following example in which we have created a table named EMP_BACKUP from already existing table named ‘Employee’ −mysql> Select * from Employee; +------+--------+ | Id   | Name   | +------+--------+ | 100  | Ram    | | 200  | Gaurav | | 300  | Mohan  | +------+--------+ 3 rows in set (0.00 sec)The query above shows the data in table ’Employee’ and the query ... Read More

CREATE command is a DDL command which is used to create a table or a database. The syntax for creating table and database with CREATE command is as follows −The syntax for creating a database −Create database database-name;Examplemysql> Create database query; Query OK, 1 row affected (0.04 sec)In the example above we have created a database named ‘query’.The syntax for creating a table −Create table table-name(    column-name1 datatype1,    column-name2 datatype2,    column-name3 datatype3,    column-name4 datatype4    ------------------------------);Examplemysql> Create table Employee(Id INT, Name Varchar(20)); Query OK, 0 rows affected (0.19 sec)In the example above, we have created a ... Read More

No, MySQL does not have a preferred naming convention standard. If the name we have chosen is logical and consistent then it would be ok.Two major points need to be remembered, one is that no two tales/databases can have the same name and second we can choose any of the reserved words as the name of table/database.

It can be done by using either CURDATE() or NOW() in MySQL query as follows −mysql> Insert into year1(Year_Copyright) values (CURDATE()); Query OK, 1 row affected, 1 warning (0.06 sec) mysql> Select * from year1; +----------------+ | Year_Copyright | +----------------+ |           2017 | |           2017 | +----------------+ 2 rows in set (0.00 sec) mysql> Insert into year1(Year_Copyright) values (NOW()); Query OK, 1 row affected, 1 warning (0.06 sec) mysql> Select * from year1; +----------------+ | Year_Copyright | +----------------+ |           2017 | |           2017 | |           2017 | +----------------+ 1 rows in set (0.00 sec)

MySQL permits to declare a column YEAR type, with the help of which we can store year values in that column.mysql> Create table year1 (Year_Copyright YEAR); Query OK, 0 rows affected (0.21 sec) mysql> Insert into year1(Year_Copyright) values (2017); Query OK, 1 row affected (0.08 sec) mysql> Select * from year1; +----------------+ | Year_Copyright | +----------------+ |          2017  | +----------------+ 1 row in set (0.00 sec)

SQL language is divided into four types of primary language statements: DML, DDL, DCL and TCL. Using these statements, we can define the structure of a database by creating and altering database objects and we can manipulate data in a table through updates or deletions. We also can control which user can read/write data or manage transactions to create a single unit of work.The four main categories of SQL statements are as follows −DML (Data Manipulation Language)DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, ... Read More