MySQLi Articles - Page 68 of 422

Let us first create a table −mysql> create table DemoTable    -> (    -> Value int    -> ); Query OK, 0 rows affected (1.33 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(25); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(75); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select * from DemoTable;This will produce the following output −+-------+ | Value | +-------+ |    25 | |   ... Read More

For this, use INET_ATON() in MySQL. The INET_ATON() method would allow a user to convert IP Address records to a number and then we can use ORDER BY to order them.Let us first create a table −mysql> create table DemoTable    -> (    -> IpAddress varchar(50)    -> ); Query OK, 0 rows affected (1.36 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('192.168.110.78'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('192.168.110.87'); Query OK, 1 row affected (0.27 sec) mysql> insert into DemoTable values('192.168.110.75'); Query OK, 1 row affected (0.26 ... Read More

For this, use sub query along with MIN() and MAX(). To display both the maximum and minimum value, use UNION ALL. Let us first create a table −mysql> create table DemoTable    -> (    -> EmployeeName varchar(20),    -> EmployeeSalary int    -> ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Bob', 8800); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values('Chris', 9800); Query OK, 1 row affected (0.63 sec) mysql> insert into DemoTable values('David', 7600); Query OK, 1 row affected (0.11 sec) mysql> ... Read More

To count rows from multiple tables in MySQL, the syntax is as follows −Select     (select count(*) from yourTableName1) as anyAliasName1,     (select count(*) from yourTableName2) as anyAliasName2     from dual;Let us first create a table −mysql> create table DemoTable1    -> (    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY    -> ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(), (), (), (), (), (); Query OK, 6 rows affected (0.24 sec) Records: 6  Duplicates: 0  Warnings: 0Display all records from the ... Read More

The MySQL equivalent to sybase ASE command is EXPLAIN keyword. Let us first create a table −mysql> create table DemoTable1531    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20)    -> ); Query OK, 0 rows affected (2.92 sec) mysql> create index Name_index1 on DemoTable1531(StudentName); Query OK, 0 rows affected (0.99 sec) Records: 0  Duplicates: 0  Warnings: 0Insert some records in the table using insert command −mysql> insert into DemoTable1531(StudentName) values('Chris'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable1531(StudentName) values('Robert'); Query OK, 1 row affected (0.10 sec) mysql> insert into ... Read More

For this, use AVG(). To find the maximum average value, use MAX() and group by id. Let us first create a table −mysql> create table DemoTable    -> (    -> PlayerId int,    -> PlayerScore int    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(1, 78); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(2, 82); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable values(1, 45); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values(3, 97); ... Read More

For this, use INSERT INTO SELECT statement. Let us first create a table −mysql> create table DemoTable1    -> (    -> PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> PersonName varchar(20),    -> PersonAge int,    -> PersonCountryName varchar(20)    -> ); Query OK, 0 rows affected (0.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1(PersonName, PersonAge, PersonCountryName) values('Chris Brown', 24, 'US'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1(PersonName, PersonAge, PersonCountryName) values('John Doe', 26, 'UK'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable1(PersonName, PersonAge, PersonCountryName) values('David ... Read More

Let us first create a table −mysql> create table DemoTable    -> (    -> ClientId int,    -> Value int    -> ); Query OK, 0 rows affected (0.79 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10, 678); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(20, 678); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(30, 678); Query OK, 1 row affected (0.09 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------+-------+ | ClientId | Value ... Read More

Let us first create a table −mysql> create table DemoTable1528    -> (    -> StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> StudentName varchar(20),    -> StudentSubject varchar(20)    -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1528(StudentName, StudentSubject) values('Chris', 'MongoDB'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable1528(StudentName, StudentSubject) values('Bob', 'MySQL'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1528(StudentName, StudentSubject) values('David', 'Java'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1528(StudentName, StudentSubject) values('Carol', 'C'); Query OK, 1 ... Read More

To insert records with double quotes, use the backslash (\) as in the below syntax −Syntaxinsert into yourTableName values('\"yourValue\"');Let us first create a table −mysql> create table DemoTable    -> (    -> Name varchar(20)    -> ); Query OK, 0 rows affected (0.63 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('\"John\"'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable values('\"Chris\"'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values('\"Adam Smith\"'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values('\"Carol\"'); Query OK, 1 row affected ... Read More