Found 4219 Articles for MySQLi

You can add a not null column at the time of table creation or you can use it for an existing table.Case 1 − Add a not null column at the time of creating a table. The syntax is as followsCREATE TABLE yourTableName (    yourColumnName1 dataType NOT NULL,    yourColumnName2 dataType    .      .    .    N );The query to create a table is as followsmysql> create table NotNullAtCreationOfTable    -> (    -> Id int not null,    -> Name varchar(100)    -> ); Query OK, 0 rows affected (0.60 sec)In the above table, we ... Read More

You can combine the insert, values and select statement using below syntaxinsert into yourFirstTableName(yourColumnName1, yourColumnName2, .......N) select yourColumnName1, yourColumnName2, .......N from yourSecondTableName where yourCondition;To understand the above syntax, let us create two tables in which first table will get the record from the second table.Let us create the first table without any records. The query to create a table is as followsmysql> create table CombiningInsertValuesSelect    -> (    -> EmployeeId varchar(10),    -> EmployeeName varchar(100),    -> EmployeeAge int    -> ); Query OK, 0 rows affected (6.95 sec)Now you can create the second table with some records. The ... Read More

To insert decimal into MySQL, you can use DECIMAL() function from MySQL. The syntax is as followsyourColumnName DECIMAL(TotalDigit, DigitAfterDecimalPoint);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table DecimalInsert    -> (    -> Id int,    -> Name varchar(100),    -> Amount DECIMAL(4, 2)    -> ); Query OK, 0 rows affected (0.65 sec)Insert the decimal value using insert command. The query is as followsmysql> insert into DecimalInsert values(1, 'John', 12.4); Query OK, 1 row affected (0.15 sec) mysql> insert into DecimalInsert values(2, 'Carol', 12.34); Query OK, ... Read More

You can declare a variable using @anyVariablename which is a session variable. To create a session variable, you need to use SET command.The syntax is as followsSET @anyVariableName:=anyValue;You can declare a local variable using DECLARE command. The syntax is as followsDECLARE yourVariableName datatypeYou can set the default value at the time of creating a variable. The syntax is as followsDECLARE yourVariableName datatype default ‘yourValue’Here is the demo of session variable. To understand it, let us create a table.The query to create a table is as followsmysql> create table SessionVariableDemo    -> (    -> EmployeeId varchar(10),    -> EmployeeName varchar(30), ... Read More

You can use backward slash followed by G i.e. \G instead of semicolon(;). The syntax is as follows to display database names vertically in MySQL command lineSHOW DATABASES \GTo display all database names vertically, you need to use \G. The query is as followsmysql> show databases\GThe following is the output*************************** 1. row *************************** Database: business *************************** 2. row *************************** Database: database1 *************************** 3. row *************************** Database: databasesample *************************** 4. row *************************** Database: education *************************** 5. row *************************** Database: hello *************************** 6. row *************************** Database: information_schema *************************** 7. row *************************** Database: javadatabase2 *************************** 8. row *************************** Database: javasampledatabase *************************** 9. row ... Read More

You can use backward slash followed by G i.e. \G instead of semicolon(;). The syntax is as follows to show records vertically in MySQL command line.SELECT *FROM yourTableName\GTo understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table showRecordsVertically -> ( -> Id int, -> Name varchar(100), -> Age int -> ); Query OK, 0 rows affected (2.10 sec)Insert some records in the table using insert command. The query is as followsmysql> insert into showRecordsVertically ... Read More

You can use field() function with ORDER BY clause to sort by order of values. The syntax is as followsSELECT *FROM yourTableName WHERE yourColumnName IN(Value1, Value2, Value3, .......N); ORDER BY FIELD(yourColumnName ,Value1, Value2, Value3, .......N);To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table SelectInDemo    -> (    -> StudentId int,    -> StudentName varchar(100),    -> StudentAge int    -> ); Query OK, 0 rows affected (1.04 sec)Insert records in the table using insert command. The query is as followsmysql> insert into SelectInDemo values(1, 'Mike', 23); Query ... Read More

The DDL stands for Data Definition Language. To generate the table DDL via query, you can use show create command.The syntax is as followsSHOW CREATE TABLE yourTableName;The above syntax is MySQL specific. Suppose, we have a table with the name ‘DDLOfTableStudent’.First, create a table with the name ‘DDLOfTableStudent’. The query to create a table is as followsmysql> create table DDLOfTableStudent -> ( -> StudentId int, -> StudentFirstName varchar(100), -> StudentLastName varchar(100), -> StudentAddress varchar(200), -> StudentAge int, -> StudentMarks ... Read More

to know if MySQL binary log is enabled through SQL command, you can use show variables command.The syntax is as followsshow variables like ‘yourPatternValue’;In place of ‘yourPatternValue’, you can use log_bin to check the binary log is enabled using SQL command show.The query is as followsmysql> show variables like 'log_bin';The following is the output that displays whether it is enabled or not+---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.03 sec)

Here is a stored procedure that takes one parameter for input (IN) and second parameter for output (OUT)mysql> delimiter // mysql> create procedure Sp_SQRT(IN Number1 INT, OUT Number2 FLOAT) -> Begin -> set Number2=sqrt(Number1); -> end; -> // Query OK, 0 rows affected (0.24 sec) mysql> delimiter ;Call the stored procedure and send the value to the user variable. The syntax is as followsCALL yourStoredProcedureName(anyIntegerValue, @anyVariableName);Check what value is stored in the variable @anyVariableName. The syntax is as followsSELECT @anyVariableName;Created the stored procedure with the name ‘Sp_SQRT’. The ... Read More