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
MySQLi Articles
Page 338 of 341
How to get a list of MySQL user accounts?
To get the list of MySQL user accounts, we can use “SELECT USER”. The following is the query to display the list. SELECT User FROM mysql.user; Here is the output. +------------------+ | User | +------------------+ | John | | Mac | | Manish | | mysql.infoschema | | mysql.session ...
Read MoreMySQL's DESCRIBE command?
The MySQL’s DESCRIBE or DESC both are equivalent. The DESC is the short form of DESCRIBE command and used to dipslay the information about a table like column names and constraints on column name. The DESCRIBE command is equivalent to the following command − SHOW columns from yourTableName command. The following is the query that display information about a table with the help of DESCRIBE command. The query is as follows. mysql> DESCRIBE Student; Above, Student is the table name in my database. The above query generates the following output. +-------+--------------+------+-----+---------+-------+ | Field | Type ...
Read MoreWhat is the benefit of zerofill in MySQL?
ZEROFILL pads the displayed value of the field with zeros up to the display width set in the column definition. Let us understand the role of zero fill in MySQL using an example. Creating a table with two columns, one has zerofill and the second one does not. The query to create a table. mysql> create table ZeroFillDemo -> ( -> First int(18) zerofill, -> Second int(18) -> ); Query OK, 0 rows affected (0.63 sec) We can insert records in the table with the help ...
Read MoreHow to get the size of the tables of a MySQL database?
To get the size of the tables of a MySQL database, you can use the “information_schema.tables”. Here is the syntax to know the size of all tables. SELECT TABLE_NAME AS `ALLTABLESNAME`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `TABLESIZEIN(MB)` FROM information_schema.TABLES WHERE TABLE_SCHEMA = "yourDatabaseName" ORDER BY (DATA_LENGTH + INDEX_LENGTH) ASC; Let us apply the above syntax to get the size of the tables. mysql> SELECT TABLE_NAME AS `ALLTABLESNAME`, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `TABLESIZEIN(MB)` -> FROM information_schema.TABLES WHERE TABLE_SCHEMA = "business" ...
Read MoreAre MySQL database and table names case-sensitive?
Actually, the case sensitivity of database and table name depends a lot on the case sensitivity of the underlying operating system. Hence, we can say that such names are not case sensitive in Windows but are case sensitive in most varieties of Unix.
Read MoreHow to get the list of tables in default MySQL database?
As we know that the default MySQL database would be the database that is currently in use for subsequent queries. We can get the list of tables in that database by using SHOW TABLES statement. mysql> SHOW TABLES; +------------------+ | Tables_in_sample | +------------------+ | employee | | new_student | | student | +------------------+ 3 rows in set (0.00 sec) The above statement shows the list of table in Sampledatabase.
Read MoreHow can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
We can add a FOREIGN KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. Syntax ALTER TABLE table_name ADD FOREIGN KEY (colum_name) REFERENCES table having Primary Key(column_name); Example Suppose we want to add a FOREIGN KEY constraint on the table ‘Orders1’ referencing to the table ‘Customer’ which have column ‘Cust_Id’ as the Primary Key. It can be done with the help of the following query − mysql> Alter table orders1 add FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ...
Read MoreIn the query [SELECT column1, column2 FROM table_name WHERE condition; ] which clause among ‘SELECT’, ‘WHERE’ and ‘FROM’ is evaluated in the last by the database server and why?
As we know that SELECT clause is used to show all the rows and columns hence SELECT clause is evaluated in the last by the database server.
Read MoreWhat will happen if the MySQL AUTO_INCREMENT column reaches the upper limit of the data type?
When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us. For example, if we will use TINYINT then AUTO_INCREMENT would be able to generate only 127 sequence numbers and in case of UNSIGNED TINYINT, this value can be extended up to 255.
Read MoreHow can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
We can remove FOREIGN KEY constraint from a column of an existing table by using DROP keyword along with ALTER TABLE statement. Syntax ALTER TABLE table_name DROP FOREIGN KEY constraint_name Here constraint name is the name of foreign key constraint which we applied while creating the table. If no constraint name is specified then MySQL will provide constraint name which can be checked by SHOW CREATE TABLE statement. Example The following query will delete the FOREIGN KEY constraint from ‘orders’ table − mysql> Alter table orders DROP FOREIGN KEY orders_ibfk_1; Query OK, 0 rows affected (0.22 sec) ...
Read More