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
MySQL Articles
Page 351 of 355
ALTER TABLE to add a composite primary key in MySQL?
To add composite primary key, use the ALTER command. Let us first create a demo table The query to create a table. mysql> create table CompositePrimaryKey -> ( -> Id int, -> StudentName varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.56 sec) Haven’t added composite primary key above till now. Let us now check with the help of desc command. mysql> desc CompositePrimaryKey; The following is the output. +-------------+--------------+------+-----+---------+-------+ | Field ...
Read MoreAlter a MySQL column to be AUTO_INCREMENT?
Let’s say we have a table and now there is a requirement to add AUTO_INCREMENT on column name. For that, use the MODIFY command. Here, we will create a demo table first. mysql> create table AddingAutoIncrement -> ( -> Id int, -> Name varchar(200), -> Primary key(Id) -> ); Query OK, 0 rows affected (0.47 sec) We have created a table above and now let us alter the table to add AUTO_INCREMENT on column name ‘Id’. The syntax is as follows − ...
Read MoreHow can I return 0 for NULL in MySQL?
We can return 0 for NULL in MySQL with the help of IFNULL() method. The syntax of IFNULL() is as follows. IFNULL(YOUREXPRESSION, 0); Let us see an example. First, we will create a table. mysql> create table NullDemoWithZero -> ( -> id varchar(200) -> ); Query OK, 0 rows affected (0.65 sec) After creating a table, let us insert some records in the table using the INSERT command. The query is as follows − mysql> insert into NullDemoWithZero values(); Query OK, 1 row affected (0.16 sec) ...
Read MoreHow 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 MoreHow to write Python CGI program to interact with MySQL?
suppose you want to login into you account using Python CGi script, below is the details login.html email: password: login.py #!C:\Python27\python.exe import MySQLdb import cgi import Cookie # Open database connection db = MySQLdb.connect("localhost", "root", "", "student" ) # prepare a ...
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 More