top of page
Writer's picturePankti Bhavsar

MySQL Basic Syntax

To create a database:

Create database <database_name>;

To get list available database :

Show database ;

To get list of all the available tables:

Show tables;

To see all the columns from the created table :

Show columns from <table_name> ;

To delete records of the table:

Truncate table <table_name>;

To insert records in the table :

a) Insert into <table_name> (col.name1, col.name2, ..) values (value1, value2, …);

Here, the value will be inserted row-wise. i.e value1--> col.1, value2àcol.2, …


b) Insert into <table_name> values ( value11, value21,...valueN1 )

( value12, value22,...valueN2 )

...........

( value1N, value2N,...valueNN );

Output:

Col.name1

Col.name2

Col.name3

​ . . . . . . .

value11

value21

Value31

. . . . . .

value12

value22

Value32

. . . . . . .

. . . . .

. . . . .

. . . . . .

Value1N

​Value2N

Value3N

. . . . . .

CRUD:

C: Create table

To create a table:

Create table <table_name> ( col.1 datatype(size), col.2 datatype(size), col.3 datatype(size), …….);

R : Read table

To see the whole table with its records :

Select * From <table_name>;

U : Update

To update table:

Update <table_name> set <col.name = new value> where <condition>;

D : Delete

To drop(delete) the table :

Drop table <table_name>;


ALTER TABLE

Add: To add column in the table

Alter table <table_name> add <new_col.name datatype(size);

Modify: To change the datatype or size of the column.

Alter table <table_name> modify <col.name new_datatype(new_size);

Drop: To delete a column from the existing table

Alter table < table_name> drop column <col.name>;

Rename: To change the name of the column

Alter table <table_name> rename column <old_col.name> to <new_col.name>;


KEY

PRIMARY KEY:

To create a table:

Create table <table_name> ( col.1 datatype(size), col.2 datatype(size), ……, Primary key(col.name*));

To add primary key go to existing table:

Alter table <table_name> add Primary key (col.name*);

To remove primary key:

Alter table <table_name> drop Primary key


*Use the col.name of which you want to make Primary key.

FOREIGN KEY:

Create table <table_name> ( col.1 datatype(size) auto_increment, col.2 datatype(size), ……, Foreign key (col.name) references <parent_table_name> (col.name));

UNOQUE KEY:

Create table <table_name> (( col.1 datatype(size) Unique, col.2 datatype(size), ……);


The above syntax will make col.1 unique. i.e col.1 can’t have duplicate value.


AUTO_INCREMENT :

Create table <table_name> (( col.1 datatype(size) auto_increment, col.2 datatype(size), ……, Primary key(col.name*));

The above syntax will make column one auto increment. i.e we don't need to insert value in the col.1.


NOT NULL:

Create table <table_name> (( col.1 datatype(size) Not null, col.2 datatype(size), ……);

The above syntax will make col.1 not null. i.e. col.1 can’t have ‘null’ value.

22 views0 comments

Recent Posts

See All

Comentarios


bottom of page