Part I MySQL for beginners: Frequent commands

I am dabbling my feet in SQL after a long time. I studied RDBMS in academics and worked briefly on SQLite.

Following is a list of commands, errors and suggestion, I have come across. This information is collection from various independent sources.

  • SQL Server Table Column Types:
bigint    Integer value (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)    2^63
int    smaller Integer value (-2,147,483,648) - (2,147,483,647)    2^31
smallint    smaller Integer value (-32,768) - (32,767)    2^15
tinyint    smaller Integer values 0 - 255    2^8
bit    Integer data value (either 1 or 0 value)    1 or 0
decimal    Decimal values from -10^38 - 10^38    10^38
numeric    Decimal values from -10^38 - 10^38    10^38
money    Money values (-922,337,203,685,477.5808) - (922,337,203,685,477.5807)    2^63
smallmoney    Smaller Money Values (-214,748.3648) - (214,748.3647)    2^31
datetime    Date value (January 1, 1753) - (December 31, 9999)    
smalldatetime    Smaller Date Value (January 1, 1900) - (June 6, 2079)    
timestamp    Unique Number Value (updates when row is updated)    
char    Character String Value (max 8,000 characters)    
varchar    Character String Value maximum of 8,000 characters, unless otherwise noted)    
nchar    Character String Value (max 4,000 characters)    
nvarchar    Character String Value (max 4,000 characters)    
text    Character String Value (max 2,147,483,647 characters)    2^31
ntext    Character String Value (max 1,073,741,823 characters)    2^30
binary    Binary Value (max 8,000 bytes)    
varbinary    Binary Value (max 8,000 bytes)    
image    Binary Value (max 2,147,483,647 bytes)    2^31
uniqueidentifier    Global Unique ID (GUID)
  • Deleting a foreign key from a table

mysql> alter table address drop foreign KEY id;

  • View a table in detail

mysql> show create table address;

  • Adding primary key to a table

mysql> alter table address add primary KEY(addr_id);

  • adding foreign key to a table

mysql> alter table address ADD FOREIGN KEY(id) REFERENCES entity(id) ;

  • Change a column name

mysql> alter table entity change name fname varchar(128);

  • Adding auto-increment

mysql> alter table meta_entity modify id int not null AUTO_INCREMENT;


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: