MySQLi Articles - Page 190 of 341

You can use DEFAULT command for this. Following is the syntax −alter table yourTableName change yourColumnName yourColumnName TINYINT(1) DEFAULT 1 NOT NULL;Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    UserName varchar(20),    UserAge int,    isMarried tinyint(1) ); Query OK, 0 rows affected (0.80 sec)Let us check the description of table −mysql> desc DemoTable;This will produce the following output −+-----------+-------------+------+-----+---------+----------------+ | Field     | Type        | Null | Key | Default | Extra          | +-----------+-------------+------+-----+---------+----------------+ | Id       ... Read More

You can use SUBSTRING() along with UPDATE command to delete last 4 letters. Let us first create a table −mysql> create table DemoTable (    StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    StudentSubject varchar(100) ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentSubject) values('Introduction to Java'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(StudentSubject) values('Introduction to C'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentSubject) values('Introduction to C++'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(StudentSubject) values('Spring And Hibernate'); ... Read More

There is no double equal sign concept. It can be used to compare two values. If you use double equal sign(==) in MySQL, you will get an error message.Let us verify the concept is true or not. Declare a variable −mysql> set @Number=10; Query OK, 0 rows affected (0.00 sec)Now, compare the above variable value with 10. If both the values are same then the result will be 1 otherwise 0.Using double equal sign −mysql> select 10==@Number;This will produce the following output i.e. an error −ERROR 1064 (42000): You have an error in your SQL syntax; check the manual ... Read More

Yes, you can use SUM() with IF() in MySQL. Let us first create a demo table:mysql> create table DemoTable (    Value int,    Value2 int ); Query OK, 0 rows affected (0.51 sec)Following is the query to insert some records in the table using insert command:mysql> insert into DemoTable values(100, 400); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(100, 400); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(400, 100); Query OK, 1 row affected (0.14 sec)Following is the query to display records from the table using select command:mysql> select *from DemoTable;This ... Read More

You can use aggregate function SUM() for this. Let us first create a table −mysql> create table DemoTable    (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstValue int,    SecondValue int,    ThirdValue int    ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstValue, SecondValue, ThirdValue) values(10, 20, 30); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable(FirstValue, SecondValue, ThirdValue) values(60, 50, 40); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable(FirstValue, SecondValue, ThirdValue) values(80, 90, 100); Query OK, 1 row affected ... Read More

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, 1 row affected (0.16 sec) mysql> insert into DemoTable(FirstName) values('Sam'); Query OK, 1 row affected (0.13 sec) ... Read More

You can use LIKE operator with wildcards to select the records where last value in a string = x, for example ‘10’, ‘15’, etc.Let us first create a table −mysql> create table DemoTable (    ClientId varchar(20) ); Query OK, 0 rows affected (0.68 sec)Insert records in the table using insert command −mysql> insert into DemoTable values('CLI-101'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('CLI-110'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('CLI-201'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('CLI-210'); Query OK, 1 row affected (0.13 sec) ... Read More

You can use the concept of IFNULL() for this. Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    Name varchar(100) DEFAULT 'Larry',    Age int DEFAULT NULL ); Query OK, 0 rows affected (0.73 sec)Insert records in the table using insert command −mysql> insert into DemoTable(Name, Age) values('John', 23); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(); Query OK, 1 row affected (0.34 sec) mysql> insert into DemoTable(Name) values('David'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable(Age) values(24); Query OK, 1 row ... Read More

You can use update along with lower() function for this. Let us first create a table −mysql> create table DemoTable (    Id varchar(100),    StudentFirstName varchar(20),    StudentLastName varchar(20),    StudentCountryName varchar(10) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('STU-101', 'John', 'Smith', 'US'); Query OK, 1 row affected (0.59 sec) mysql> insert into DemoTable values('STU-102', 'John', 'Doe', 'UK'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('STU-103', 'David', 'Miller', 'AUS'); Query OK, 1 row affected (0.19 sec)Following is the query to display all ... Read More

To convert string to bitset, use the CONV() method. Let us first create a table −mysql> create table DemoTable (    stringValue BIT(4) ); Query OK, 0 rows affected (3.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(CONV('1110', 2, 10) * 1); Query OK, 1 row affected (0.62 sec) mysql> insert into DemoTable values(b'1011'); Query OK, 1 row affected (0.14 sec)Following is the query to display all records from the table using select statement −mysql> select *from DemoTable;Following is the output that displays blank result because the type is bitset −Following is the query ... Read More