MYSQL BASIC COMMANDS
Create a database
create database Prwatech;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-280-1.png)
List all database on the server
show databases;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-282.png)
Switch to a database
use prwatech;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-284.png)
Create a table in the database
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-286.png)
To see all the tables in the database
show tables;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-288.png)
To see a table field format
describe persons;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-290.png)
To delete a database
drop database sys;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-292.png)
To delete a table in the database
drop table persons;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-294.png)
To see the column information in the table of the database
show columns from Institute;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-299.png)
Inserting information to a table in the database;
INSERT INTO Institute (Course, Duration, Fees, Name, Country, Postalcode)
VALUES (‘Big Data’, ‘6 months’, ‘30000’, ‘George’, ‘India’,’560039′);
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-305.png)
Adding a column into a table
ALTER TABLE Institute
-> ADD Email varchar(255);
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-307.png)
To delete a column in a table
ALTER TABLE Institute
-> DROP COLUMN Email;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-309.png)
The following SQL ensures that the “ID”, “LastName”, and “FirstName” columns will NOT accept NULL values when the “Persons” table is created:
The NOT NULL constraint enforces a column to NOT accept NULL values.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-311.png)
show columns from persons;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-313.png)
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.
ALTER TABLE Persons
-> ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-315.png)
SQL Check on ALTER TABLE
To create a CHECK constraint on the “Age” column when the table is already created, use the following SQL:
ALTER TABLE Persons
-> ADD CHECK (Age>=33);
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-317.png)
SQL Default on ALTER TABLE
To create a DEFAULT constraint on the “City” column when the table is already created, use the following SQL:
ALTER TABLE training
-> ALTER CITY SET DEFAULT ‘Bangalore’;
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-319.png)
SQL CREATE INDEX STATEMENT
The CREATE INDEX statement is used to create indexes in tables.
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-321.png)
AUTO INCREMENT Field
Auto-increment allows a unique number to be generated automatically .
![](https://prwatech.in/blog/wp-content/uploads/2021/01/Screenshot-324.png)