Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
MySQL Articles
Page 271 of 355
Count rows having three or more rows with a certain value in a MySQL table
Let us first create a table −mysql> create table DemoTable -> ( -> UserId int -> ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(20); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(30); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10); Query OK, 1 row affected (0.09 sec) ...
Read MoreCommand to check read/write ratio in MySQL?
To check the read/write ratio, you need to use SHOW STATUS command. This will give all the ratios.Case 1 − The syntax is as follows to get the read/write ratio −SHOW STATUS LIKE ‘Com_%’;Case 2 − If you want the insert, update, select and delete ratio, use the below syntax −SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert' OR Variable_name = 'Com_select' OR Variable_name = 'Com_Update' OR Variable_name = 'Com_delete';Here is no need to do any changes in the above syntax. The query is as follows −mysql> SHOW GLOBAL STATUS WHERE Variable_name = 'Com_insert' OR Variable_name = 'Com_select' OR -> ...
Read MoreWhat is the use of setFetchSize() and setMaxRows() methods of the JDBC Statement Interface?
The setFetchSize(int) method defines the number of rows that will be read from the database when the ResultSet needs more rows. setFetchSize(int) affects how the database returns the ResultSet data.Whereas, setMaxRows(int) method of the ResultSet specifies how many rows a ResultSet can contain at a time. setMaxRows(int) affects the client side JDBC object.
Read MoreWhat is JDBC Clob data type? how to store and read data from it?
CLOB stands for Character Large Object in general, an SQL Clob is a built-in datatype and is used to store large amount of textual data. Using this datatype, you can store data up to 2, 147, 483, 647 characters.The java.sql.Clob interface of the JDBC API represents the CLOB datatype. Since the Clob object in JDBC is implemented using an SQL locator, it holds a logical pointer to the SQL CLOB (not the data).MYSQL database provides support for this datatype using four variables.TINYTEXT: A CLOB type with a maximum of 28-1 (255) characters.TEXT: A CLOB type with a maximum of 216-1 ...
Read MoreWrite a MySQL query equivalent to "SHOW TABLES" in sorted order?
Use INFORMATION_SCHEMA.TABLES to display tables in sorted order. The below syntax will give sorted list of tables in ascending order:select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA= 'yourDatabaseName' order by TABLE_NAME;Following is the query to implement the equivalent to SHOW TABLES:mysql> select TABLE_NAME from INFORMATION_SCHEMA.TABLES -> where TABLE_SCHEMA= 'sample' order by TABLE_NAME;This will produce the following output+------------------------------------+ | TABLE_NAME | +------------------------------------+ | a | | accumulateddemo ...
Read MoreWhat is the alias to Show Tables in MySQL Result?
You can use AS command for alias to show tables in MySQL result. Following is the syntax −SELECT TABLE_NAME AS anyAliasName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();Let us implement the above syntax −mysql> SELECT TABLE_NAME AS MY_TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE();This will produce the following output −+------------------------------------+ | MY_TABLE_NAME | +------------------------------------+ | a | | accumulateddemo | | add10minutedemo ...
Read MoreWhat does /* in MySQL means?
This is a type of comment. The /* is the beginning of a comment and */ is the end of comment.Let us implement and display how to create a commentmysql> /* This is the first MySQL Program */MySQL will ignore the above comment.Let us see an example. Here, we have written a comment with /* and */mysql> /*This table has information about person */ mysql> create table DemoTable ( PersonId int NOT NULL AUTO_INCREMENT PRIMARY KEY, PersonName varchar(20), PersonAge int ); Query OK, 0 rows affected (0.58 sec)
Read MoreRun SQL file in MySQL database from terminal?
To run SQL file in database, you need to use below syntax:mysql -u yourUserName -p yourDatabaseName < yourFileName.sqlTo understand the above syntax, let us open command prompt using windows+R shortcut key.The snapshot is as follows:After pressing OK button, you will get a command prompt. The snapshot is as follows:Now reach the bin directory, the snapshot is as follows:Here is my file ‘mydb.sql’ which is located in bin directory. The snapshot and content of SQL file is as follows:Now you can type the above syntax which I have discussed to run SQL file. The snapshot of command is as follows:Now you ...
Read MoreImplement MySQL INSERT MAX()+1?
You need to use COALESCE() function for this. The syntax is as follows:INSERT INTO yourTableName(yourColumnName1, yourColumnName2) SELECT 1 + COALESCE((SELECT MAX(yourColumnName1) FROM yourTableName WHERE yourColumnName2=’yourValue’), 0), ’yourValue’;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table InsertMaxPlus1Demo -> ( -> Id int, -> Name varchar(20) -> ); Query OK, 0 rows affected (1.27 sec)Now you can insert some records in the table using insert command. The query is as follows:mysql> insert into InsertMaxPlus1Demo(Id, Name) values(1, 'John'); Query OK, 1 row affected (0.12 sec) mysql> insert ...
Read MoreCan we select row by DATEPART() in MySQL? Is it possible?
There is no DATEPART() function in MySQL, you need to use MONTH() function to extract the month name from date column. The syntax is as follows:SELECT *FROM yourTableName WHERE MONTH(yourDateColumnName)=yourValue;To understand the above syntax, let us create a table. The query to create a table is as follows:mysql> create table SelectRowFromDatePart -> ( -> Id int NOT NULL AUTO_INCREMENT, -> LoginDate date, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (2.42 sec)Now you can insert some records in the table using ...
Read More