Found 4219 Articles for MySQLi

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

With the help of CURDATE() and NOW() function, we can insert current date automatically in a column of MySQL table.ExampleSuppose we want to insert current date automatically in an OrderDate column of table year_testing the following query will do this −mysql> Insert into year_testing (OrderDate) Values(CURDATE()); Query OK, 1 row affected (0.11 sec) mysql> Select * from year_testing; +------------+ | OrderDate  | +------------+ | 2017-10-28 | +------------+ 1 row in set (0.00 sec) mysql> Insert into year_testing (OrderDate) Values(NOW()); Query OK, 1 row affected, 1 warning (0.12 sec) mysql> Select * from year_testing; +------------+ | OrderDate  | +------------+ ... Read More

We can store a date with only year value and have zero months as well as zero-days in MySQL table by disabling NO_ZERO_IN_DATE mode. If this mode is enabled then MySQL will count such kind of date as invalid date and stores all zeros.mysql> Insert into year_testing (OrderDate) values('2017:00:00'); Query OK, 1 row affected (0.09 sec) mysql> select * from year_testing; +------------+ | OrderDate  | +------------+ | 2017-00-00 | +------------+ 1 row in set (0.00 sec) mysql> SET sql_mode = 'NO_ZERO_IN_DATE'; Query OK, 0 rows affected (0.00 sec) mysql> Insert into year_testing(OrderDate) values('2017:00:00'); Query OK, 1 row ... Read More

Actually, there is no default database for the user. But we have default database for the current session. It can be seen from the following query −mysql> Select Database(); +------------+ | Database() | +------------+ | sample     | +------------+ 1 row in set (0.00 sec)The above result set shows that we are using ‘sample’ database currently. It is set for the current session. We can set another database, with the help of USE statement, also for the current session as follows −mysql> USE query; Database changed mysql> Select Database(); +------------+ | Database() | +------------+ | query     ... Read More

As we know that CURDATE() only returns the date unit so it would be ambiguous to use INTERVAL of time unit with CURDATE(). MySQL always represents current date with ‘00:00:00’ time hence when we use INTERVAL of time unit with CURDATE() then such kind of time arithmetic would take this time into consideration. Following examples will clarify it −mysql> Select CURDATE() + INTERVAL 0 hour; +-----------------------------+ | curdate() + Interval 0 hour | +-----------------------------+ | 2017-10-28 00:00:00         | +-----------------------------+ 1 row in set (0.00 sec) mysql> select CURDATE() + INTERVAL 1 hour; +-----------------------------+ | curdate() ... Read More