MySQLi Articles - Page 184 of 341

You can use IN() to display all records except one in MySQL. Let us first create a table −mysql> create table DemoTable (    Id int,    FirstName varchar(20) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 'Larry'); Query OK, 1 row affected (0.31 sec) mysql> insert into DemoTable values(10, 'Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(110, 'Robert'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(90, 'David'); Query OK, 1 row affected (0.20 sec)Following is the query ... Read More

In order to get the greatest of two columns values in MySQL, you need to use GREATEST() function. Following is the syntax:select greatest(yourColumnName1, yourColumnName2) AS anyAliasName from yourTableName; Let us first create a table:mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Number1 int,    Number2 int ); Query OK, 0 rows affected (0.63 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(Number1, Number2) values(1000, 10000); Query OK, 1 row affected (0.49 sec) mysql> insert into DemoTable(Number1, Number2) values(600, 900); Query OK, 1 row affected (0.12 sec) mysql> ... Read More

Let us first create a table with Id as auto_increment −mysql> create table DemoTable (    UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(20) ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(UserName) values('John'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(UserName) values('Larry'); Query OK, 1 row affected (0.35 sec) mysql> insert into DemoTable(UserName) values('Chris'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(UserName) values('Bob'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(UserName) values('Carol'); Query OK, 1 row affected ... Read More

You can use SHOW command for this. Following is the syntax −show columns from yourTableName;Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentFirstName varchar(20),    StudentLastName varchar(20),    StudentAge int,    StudentAddress varchar(200) ); Query OK, 0 rows affected (0.54 sec)Following is the query to get the names of my MySQL table columns −mysql> show columns from DemoTable;This will produce the following output −+------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null ... Read More

You can use ORDER BY IF() to get records in a certain order. Let us first create a table:mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(20),    Branch varchar(20) ); Query OK, 0 rows affected (1.96 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(FirstName, Branch) values('John', 'CS'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(FirstName, Branch) values('Carol', 'ME'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable(FirstName, Branch) values('David', 'ME'); Query OK, 1 row affected (0.11 sec) mysql> ... Read More

You can use TRUNCATE() method to floor the values instead of round. Let us first create a table −mysql> create table DemoTable (    Value DECIMAL(20, 8) ); Query OK, 0 rows affected (0.54 sec)Insert records in the table using insert command −mysql> insert into DemoTable values(23.5654433); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(12.345542211); Query OK, 1 row affected, 1 warning (0.21 sec) mysql> insert into DemoTable values(12345.678543); Query OK, 1 row affected (0.22 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the ... Read More

To change the file extension in the text column, you can use UPDATE command along with REPLACE() function. Let’s say we have some columns with extensions and we need to replace all of them. For that, let us first create a table with the extension columns set as text type:mysql create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    ProgramExtension1 text,    ProgramExtension2 text,    ImageExtension text ); Query OK, 0 rows affected (0.52 sec)Following is the query to insert records in the table using insert command:mysql> insert into DemoTable(ProgramExtension1, ProgramExtension2, ImageExtension)values('.java', '.c', '.jpeg'); Query OK, ... Read More

You can use aggregate function COUNT() from MySQL to count horizontal values on a database. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstValue int,    SecondValue int,    ThirdValue int,    FourthValue int ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstValue, SecondValue, ThirdValue, FourthValue) values(-18, 45, 0, 155); Query OK,  1 row affected (0.22 sec) mysql> insert into DemoTable(FirstValue, SecondValue, ThirdValue, FourthValue) values(0, 235, null, 15); Query OK,  1 row affected (0.20 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following ... Read More

You can use aggregate function SUM() from MySQL for this. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Amount int ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Amount) values(400); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable(Amount) values(10); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Amount) values(50); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(Amount) values(500); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Amount) ... Read More

Yes, we can ignore duplicate rows in COUNT using DISTINCT. Following is the syntax:select count(distinct yourColumnName) from yourTableName;In MySQL, COUNT() will display the number of rows. DISTINCT is used to ignore duplicate rows and get the count of only unique rows.Let us first create a table:mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(10) ); Query OK, 0 rows affected (0.47 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable(FirstName) values('Larry'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable(FirstName) values('John'); Query OK, ... Read More