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.
Comentarios