Found 4219 Articles for MySQLi

To achieve this with LIKE operator, the following is the syntax −CREATE TABLE yourTableName2 LIKE yourTableName1;To understand the syntax, let us create a table and insert some records into it. The following is the query to create a table −mysql> create table Employee −> (    −> EmployeeId int    −> ,    −> EmployeeName varchar(100) −> ); Query OK, 0 rows affected (0.54 sec)Inserting records into the table with the help of insert command. The query is as follows −mysql> insert into Employee values(1, 'Carol'); Query OK, 1 row affected (0.18 sec) mysql> insert into Employee values(2, 'John'); ... Read More

You can move rows from one table to another with the help of INSERT INTO SELECT statement.The syntax is as follows −insert into yourDestinationTableName select *from yourOriginalTable where someConditionTo understand the above syntax. let us create a table. The following is the query to create a table −mysql> create table StudentTable    −> (       −> Id int,       −> Name varchar(100)    −> ); Query OK, 0 rows affected (0.65 sec)Now, I will create second table. The query is as follows −mysql> create table Employee    −> (       −> EmployeeId int   ... Read More

To extract year from date format, you can use in-built function YEAR() from MySQL. The query is as follows −mysql> SELECT YEAR(curdate()) as OnlyYearFromCurrentDate;The following is the output −+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 | +-------------------------+ 1 row in set (0.00 sec)Or you can do in the following way −mysql> SELECT YEAR(date(now())) as OnlyYearFromCurrentDate;The following is the output −+-------------------------+ | OnlyYearFromCurrentDate | +-------------------------+ | 2018 ... Read More

You can set primary key on an existing column in MySQL with the help of alter command.The syntax is as follows to add primary key to an existing column.ALTER TABLE yourTableName ADD PRIMARY KEY(yourColumnName);To set existing column as primary key, let us first create a table. The query to create a table −mysql> create table AddingPrimaryKeyDemo    −> (       −> UniversityId int,       −> UniversityName varchar(200)    −> ); Query OK, 0 rows affected (1.16 sec)Look at the above query, I haven’t added primary key. Let us check the same with the help of DESC ... Read More

To get the number of columns, use the aggregate function count(*) with information_schema table from MySQL. The syntax is as follows to find the number of columns −SELECT COUNT(*) as anyVariableName from INFORMATION_SCHEMA.COLUMNS where table_schema = ’yourDatabaseName’ and table_name = ’yourTableName’;To understand the above syntax, let us create a table with some columns. The following is the query to create a table −mysql> create table CountColumns −> (    −> Bookid int,    −> BookName varchar(200),    −> BookAuthorName varchar(200),    −> BookPublishedDate datetime −> ); Query OK, 0 rows affected (0.69 sec)Now, we have total 4 columns in my ... Read More

You can use SHOW VARIABLES command to display current configuration variables. The syntax is as follows −SHOW VARIABLES;If you want any specific information, then implement the LIKE operator. The syntax is as follows −SHOW VARIABLES LIKE ‘%AnySpecificInformation%’;Now we will implement the above syntax −mysql> show variables like '%variable%';The following is the output −+--------------------------------+------------------------------------------------------------------------------------------+ | Variable_name                  | Value                                                                       ... Read More

To cast DATETIME as a DATE in MySQL, use the CAST() function. The syntax is as follows −select cast(yourColumnName as Date) as anyVariableName from yourTableName;To understand the above syntax, let us first create a table −mysql> create table ConvertDatetimeToDate −> (    −> YourDatetime datetime −> ); Query OK, 0 rows affected (0.95 sec)Inserting datetime into the table with the help of insert command. The query is as follows −mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval 1 day)); Query OK, 1 row affected (0.17 sec) mysql> insert into ConvertDatetimeToDate values(date_add(now(), interval -1 day)); Query OK, 1 row affected (0.15 sec) ... Read More

To list all triggers in a MySQL database, you can use the SHOW command. The query is as follows −mysql> show triggers;The following is the output −+----------------+--------+----------------------+--------------------------------------------------------------------+--------+------------------------+--------------------------------------------+---------+----------------------+----------------------+--------------------+ | Trigger        | Event  | Table                | Statement                                                          | Timing | Created                | sql_mode                             ... Read More

You can typecast NULL to 0 with the help of IFNULL() function. The syntax is as follows −select ifnull(yourColumnName) as anyVariableName from yourTableName;To understand the above concept, let us first create a table −mysql> create table TypecastDemo    −> (       −> AccountNumber int    −> ); Query OK, 0 rows affected (0.84 sec)Let us insert some records with NULL value. The query to insert records is as follows −mysql> insert into TypecastDemo values(NULL); Query OK, 1 row affected (0.13 sec) mysql> insert into TypecastDemo values(1234); Query OK, 1 row affected (0.14 sec) mysql> insert into ... Read More

The NOW() function gives current datetime as a timestamp while CURDATE() gives only current date, not time.Now let us work on both the functions with the help of select statement. The query is as follows −The following is a demo of NOW() function −mysql> select NOW();The following is the output −+---------------------+ |             now() | +---------------------+ | 2018-11-27 15:17:01 | +---------------------+ 1 row in set (0.00 sec)A demo of CURDATE().mysql> select CURDATE();The following is the output that displays only date, not time −+------------+ | curdate() | +------------+ | 2018-11-27 | +------------+ 1 row in set ... Read More