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
Database Articles
Page 536 of 547
Is the primary key automatically indexed in MySQL?
Yes, primary key is automatically indexed in MySQL because primary key, index, etc gets stored into B-trees. All engines including InnoDB as well as MyISAM automatically supports the primary key to be indexed. The primary key is implicitly indexed in InnoDB, MyISAM, and other engines. Let us create a table with primary key − mysql> create table DemoIndex -> ( -> Id int not null, -> primary key(Id) -> ); Query OK, 0 rows affected (1.21 sec) In the above table, Id is implicitly indexed.
Read MoreHow to disable ONLY_FULL_GROUP_BY in MySQL?
You can enable ONLY_FULL_GROUP_BY in MySQL as shown in the following query − mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.01 sec) As shown above, we can enable ONLY_FULL_GROUP_BY with the help of SET command. To disable ONLY_FULL_GROUP_BY with the help of the following query − mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); Query OK, 0 rows affected (0.04 sec) We have disabled ONLY_FULL_GROUP_BY successfully.
Read MoreHow to take backup of a single table in a MySQL database?
The backup of a table can be made with the help of backup table as well as mysqldump utility. The backup table concept was used in MySQL version 5.0 and its earlier version. Here, I am performing backup with the help of mysqldump. Firstly, we will open cmd with the help of shortcut key. The mysqldump will run at the cmd. Therefore, firstly open cmd with the help of shortcut key − windowskey+R; Here is the snapshot − Now, cmd will open − In this, the MySQL bin folder is present at the following location − ...
Read MoreERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'?
In the system, the root is defined by another name as well as password. Then the user is created as a root with the help of the create command. This will result in the ERROR 1396. The query for this is given as follows − mysql> create user 'root'@'localhost' identified by 'root123'; After executing the above query, the following error is obtained − ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost' The user can be created with another name and password successfully. This is given as follows − mysql> create user 'John'@'localhost' identified by ...
Read MoreAdvantages of using MySQLi over MySQL?
MySQLi is also known as MySQL improved Extension. It is a relational SQL database management system. It is often used inside PHP to provide an interface with the MySQL databases. Some of the reasons why MySQLi is famous are given below − MySQLi uses the standard form of the SQL language. MySQLi is free as it is released under an open source license. MySQLi can be easily used with PHP which is the most famous language for web development. MySQLi is a very powerful language and it can handle the functionality of powerful database packages. MySQLi can work with ...
Read MoreWhich one is preferred between a large table or multiple small tables in MySQL?
It is very difficult to say whether to prefer one large table or multiple small tables. It depends − On the application we are using. On database normalization However, there are many key points, through which we can say that multiple small tables are good in that situation. Suppose many developers are going to develop multiple tables, then there is a need to split them into multiple small tables. A situation when you are giving authority to many developers. This authority is for different parts of data. In this case, a need arise to split into multiple small ...
Read MoreOpen MySQL root access from all hosts?
To open root access from all hosts, we need to change the database to “mysql” with the help of USE command. The syntax of USE command is as follows − USE anyDatabasename; Now, I will use predefined database ‘mysql’, which is as follows − mysql> use mysql; Database changed I have changed the database above. Here is the query to get root access from the entire host − mysql> UPDATE user set host='%' where host='localhost'; Query OK, 6 rows affected (0.19 sec) Rows matched: 6 Changed: 6 Warnings: 0
Read MoreMyISAM versus InnoDB in MySQL?
Both are engine types. Here is the query by which we can get to know which engine type and tables are being used. Firstly, we will choose the database with the help of USE command − mysql> USE business; Database changed Here is the query through which we can know which table or engine is being used − mysql> SHOW table status; The following is the output +------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+ |Name | Engine | Version | Row_format | ...
Read MoreError 1046 No database Selected, how to resolve?
The 1046 error occurs if you forget to select any database before creating a table. Let us see how and why this error occurs. We will try to create a table without selecting a database − mysql> CREATE table MyTable1 -> ( -> id int -> ); ERROR 1046 (3D000): No database selected Or mysql> INSERT into sample values(1); ERROR 1046 (3D000): No database selected Look at the output above, we are getting the same 1046 error: “No database selected” Now, we can resolve this error after selecting any ...
Read MoreHow to raise an error within MySQL?
MySQL has introduced signals similar to an exception in other languages. Let us first see the syntax of signal. SIGNAL SQLSTATE ' PredefinedValueforSignalError' SET MESSAGE_TEXT = 'AnyMessageInformation'; Above, we have set our own error message text as well. We will apply the above query to get an error message with the help of signals. mysql > SIGNAL SQLSTATE '42927' SET MESSAGE_TEXT = 'Error Generated'; The following is the output of the above query. ERROR 1644 (42927): Error Generated In the above output, ‘42927’ is a SQLSTATE and ‘Error Generated’ is an error message, which we added.
Read More