MySQLi Articles - Page 58 of 341

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

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

To display only hour and minutes, use DATE_FORMAT() and set format specifiers as in the below syntax −select date_format(yourColumnName, '%H:%i') as anyAliasName from yourTableName;Let us first create a table −mysql> create table DemoTable1527    -> (    -> ArrivalDatetime datetime    -> ); Query OK, 0 rows affected (0.76 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1527 values('2019-01-10 12:34:45'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1527 values('2018-12-12 11:00:34'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1527 values('2019-03-21 04:55:56'); Query OK, 1 row affected (0.18 sec)Display all records from ... Read More

Let us first create a table −mysql> create table DemoTable1526    -> (    -> CustomerName varchar(20),    -> PurchaseDate date    -> ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command. Here, we have inserted 2019 dates −mysql> insert into DemoTable1526 values('Adam', '2019-06-01'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1526 values('Sam', '2019-04-26'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1526 values('Chris', '2019-05-24'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1526 values('David', '2019-10-10'); Query OK, 1 row affected (0.23 sec) mysql> insert into ... Read More

To select date and format, use SELECT DATE_FORMAT(). Following is the syntax −Syntaxselect date_format(yourColumnName, '%e %b %y') from yourTableName;Let us first create a table −mysql> create table DemoTable    -> (    -> DueDate date    -> ); Query OK, 0 rows affected (0.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-11'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('2019-12-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-09-15'); Query OK, 1 row affected (0.07 sec)Display all records from the table using select statement −mysql> select *from ... Read More

To group rows in an array, use GROUP_CONCAT() along with the ORDER BY clause. Let us first create a table −mysql> create table DemoTable    -> (    -> Id int,    -> FirstName varchar(20)    -> ); Query OK, 0 rows affected (0.78 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(101, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(102, 'Bob'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(101, 'David'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(101, 'Adam'); Query OK, ... Read More

To get last value in group concat, use SUBSTRING_INDEX(). Let us first create a table −mysql> create table DemoTable1525    -> (    -> ListOfSubjects text    -> ); Query OK, 0 rows affected (1.13 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1525 values('MongoDB, C'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1525 values('Java, C++, MySQL'); Query OK, 1 row affected (0.25 sec) mysql> insert into DemoTable1525 values('Python, C++, C, Java'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select * from DemoTable1525;This will ... Read More

In this article, we will learn how to count the number of columns in a MySQL table using JDBC. We will be using the ResultSetMetaData to get details of the table by using simple examples. What is ResultSetMetaData? The ResultSetMetaData is an interface that is present in the java.sql package. Using ResultSetMetaData, we can get information about the table, for example, what are the column names of each and every table, and how many columns are there?. To create the object for ResultSet: ResultSet rs=st.executeQuery("Select * from Student"); The executeQuery method writes the records, which are then stored in the ... Read More