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
MySQLi Articles
Page 264 of 341
Set Optimal MySQL configuration in my.cnf?
First, you need to open my.cnf file. The following is the query to get the directory location of the config file on Windows −mysql> select @@datadir;Output+---------------------------------------------+ | @@datadir | +---------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ | +---------------------------------------------+ 1 row in set (0.00 sec)Here is the snapshot of the directory −Now open my.cnf file. The snapshot is as follows −If you want to add more data to cache, ...
Read MoreHow to achieve case sensitive uniqueness and case insensitive search in MySQL?
You can achieve case sensitive uniqueness and case insensitive search with the help of the following two ways −VARBINARY data type_bin collationVARBINARY data typeTo work with the VARBINARY data type, let us first create a table. The query to create a table is as follows −mysql> create table SearchingDemo2 -> ( -> UserId VARBINARY(128) NOT NULL, -> UNIQUE KEY index_on_UserId2(UserId ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; Query OK, 0 rows affected, 1 warning (0.99 sec)Keep in mind UserId has data type VARBINARY(128) and Index(‘index_on_UserId2’) on a column ‘UserId’._bin ...
Read MoreHow to check whether a stored procedure exist in MySQL?
Let us first create a stored procedure −mysql> DELIMITER // mysql> CREATE PROCEDURE ExtenddatesWithMonthdemo(IN date1 datetime, IN NumberOfMonth int ) -> BEGIN -> SELECT DATE_ADD(date1, INTERVAL NumberOfMonth MONTH) AS ExtendDate; -> END; -> // Query OK, 0 rows affected (0.20 sec) mysql> DELIMITER ;Now you check whether the stored procedure exists with the help SHOW CREATE command.The query is as follows −mysql> SHOW CREATE PROCEDURE ExtenddatesWithMonthdemo; The following is the output displaying the details of the stored procedure we created above: +--------------------------+--------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+ | Procedure | sql_mode ...
Read MoreHow to know the exact number of table and columns in a MySQL database?
To get the exact number if table and columns in a MySQL database, use the DISTINCT inside COUNT().Let’s say we have a database ‘sample’ and we need to work on it to get the exact number of table and columns.To achieve it, the query is as follows −mysql> SELECT COUNT(DISTINCT TABLE_NAME) AS TotalTable, Count(Column_Name) AS TOTALColumn -> FROM INFORMATION_SCHEMA.COLUMNS -> WHERE TABLE_SCHEMA = 'sample';The following is the output displaying the count of table and columns in the database ‘sample’ −+------------+-------------+ | TotalTable | TOTALColumn | +------------+-------------+ | ...
Read MoreHow to upgrade MySQL server from command line?
First, you need to open the CMD with the help of shortcut key Windows+R key.After typing cmd, press the OK button. On pressing, you will get a command prompt. The screenshot is as follows −After that, you need to reach the /bin directory. Follow the below instructions. If you are a Windows user, then use the below query to reach the /bin directory.The query is as follows −mysql> select @@datadir;The following is the output displaying the path −+---------------------------------------------+ | @@datadir ...
Read MoreCan we add a column to a table from another table in MySQL?
Yes, we can add a column to a table from another table. Let us first create two tables. The query to create a table is as follows −mysql> create table FirstTable -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (1.48 sec)Now create the second table. The query to create the second table is as follows −mysql> create table SecondTable -> ( -> UserId int, -> UserAge int -> ); Query OK, 0 rows affected (1.57 sec)Now, add column Age to the first table. Firstly, ...
Read MoreMySQL query to check if database is empty or not?
You can use INFORMATION_SCHEMA.COLUMNS to check if a database is empty or not. The syntax is as follows −SELECT COUNT(DISTINCT `TABLE_NAME`) AS anyAliasName FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `table_schema` = 'yourDatabaseName';The above syntax returns 0 if the database has notable otherwise it returns the number of tables. For our example, we are using the databases ‘sample’ and ‘test3’, which we created before.The first database ‘sample’ has more tables, therefore the above query will return a number of tables. The second database ‘test3’ does not have any tables, therefore the above query will return 0.Case 1 − Database sampleThe query is as follows ...
Read MoreHow can I make a table in MySQL called "order"?
As you know, order is a keyword in MySQL, you cannot give table name order directly. You need to use backtick around the table name order. Backtick allow a user to consider the keyword as table or column name.The syntax is as followsCREATE TABLE `order` ( yourColumnName1 dataType, yourColumnName2 dataType, yourColumnName3 dataType, . . . . N );Let us create a table. The query to create a table is as followsmysql> create table `order` - > ( - > Id int, - > Price int - > ); ...
Read MoreGet total in the last row of MySQL result?
To get total in the last row of MySQL result, use the following syntax −( SELECT yourColumnName1, yourColumnName2, yourColumnName3, . . N FROM yourTableName ) UNION ( SELECT "yourMessage" AS anyAliasName1, SUM(yourColumnName1) AS anyAliasName2, SUM(yourColumnName2) AS anyAliasName3, . . N FROM yourTableName );To understand the above syntax, let us create a table. The query to create a table is as follows −mysql> create table ProductDemo -> ( -> ProductId varchar(10), -> ProductQuantity int, -> ProductValue int -> ); Query OK, 0 ...
Read MoreHow to select most recent date out of a set of several possible timestamps in MySQL?
You can select most recent date out of a set of several possible timestamps with the help of ORDER BY clause.The syntax is as followsSELECT yourColumnName1, yourColumnName2, ...N FROM yourTableName ORDER BY yourTimestampColumnName DESC LIMIT 1;To understand the above syntax, let us create a table. The query to create a table is as followsmysql> create table MostRecentDateDemo - > ( - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, - > Name varchar(10), - > ShippingDate timestamp - > ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command. ...
Read More