MYSQL BASIC COMMANDS
Create a database
create database Prwatech;
List all database on the server
show databases;
Switch to a database
use prwatech;
Create a table in the database
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
To see all the tables in the database
show tables;
To see a table field format
describe persons;
To delete a database
drop database sys;
To delete a table in the database
drop table persons;
To see the column information in the table of the database
show columns from Institute;
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′);
Adding a column into a table
ALTER TABLE Institute
-> ADD Email varchar(255);
To delete a column in a table
ALTER TABLE Institute
-> DROP COLUMN Email;
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
);
show columns from persons;
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);
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);
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’;
SQL CREATE INDEX STATEMENT
The CREATE INDEX statement is used to create indexes in tables.
AUTO INCREMENT Field
Auto-increment allows a unique number to be generated automatically .
