|
CREATE USER
|
Creates users to access databases. |
CREATE USER UserName IDENTIFIED BY 'Password';
|
CREATE USER Dzone123 IDENTIFIED BY 'DzoneRefcard@123';
|
|
CREATE DATABASE
|
Creates a new database. |
CREATE SCHEMA DatabaseName;
|
CREATE SCHEMA InsuranceDetails;
|
|
CREATE TABLE
|
Creates a new table. |
CREATE TABLE TableName (
Column_1 Datatype,
Column_2 Datatype,
....
);
|
CREATE TABLE CarInsurance
(
PolicyID int,
PolicyName varchar(255),
EffectiveDate datetime,
ExpiryDate datetime,
PaymentOption varchar(255),
Amount double,
Status bool
);
|
|
DROP USER
|
Delete users from the database server. |
|
|
|
DROP DATABASE
|
Deletes the database along with the data present in it. |
DROP SCHEMA DatabaseName;
|
DROP SCHEMA InsuranceDetails;
|
|
DROP TABLE
|
Deletes the table along with the data present in it. |
|
|
|
SHOW USER
|
Displays a list of users who have access to the database. |
SELECT USER FROM
MYSQL.DatabaseName;
|
SELECT USER FROM MYSQL.InsuranceDetails;
|
|
SHOW DATABASE
|
Displays a list of databases created until present. |
|
|
|
SHOW TABLE
|
Displays a list of tables created in the database until present. |
|
USE InsuranceDetails;
SHOW TABLES;
|
|
SHOW COLUMNS
|
Displays all the columns present in the database. |
SHOW COLUMNS FROM TableName FROM DatabaseName;
Or:
SHOW COLUMNS FROM DatabaseName.
TableName;
|
SHOW COLUMNS FROM CarInsurance FROM InsuranceDetails;
Or:
SHOW COLUMNS FROM InsuranceDetails.
CarInsurance;
|
|
USE DATABASE
|
Tells the system which database to be chosen to perform actions. |
|
|
|
ALTER TABLE
|
Modifies tables by adding, deleting, or modifying data in columns. |
ALTER TABLE TableName
ADD Column_1 datatype;
|
ALTER TABLE CarInsurance
ADD PolicyCreatedDate datetime;
|
|
DESCRIBE TABLE
|
Displays the structure of the table consisting of column names, data types, keys, and default values. |
|
|
|
TRUNCATE TABLE
|
Deletes only the data present in tables without deleting the table. |
TRUNCATE TABLE TableName;
|
TRUNCATE TABLE CarInsurance;
|
|
RENAME DATABASE
|
Renames a database. |
RENAME TABLE Database1 TO Database2;
|
RENAME TABLE InsuranceDetails TO Insurances;
|
|
RENAME TABLE
|
Renames a table. |
RENAME TABLE Table1 TO Table2;
|
RENAME TABLE CarInsurance TO CarInsuranceDetails;
|
|
CHANGE COLUMNS
|
Changes column names of a table. |
ALTER TABLE TableName
CHANGE COLUMN OldColumn NewColumn DataType;
Or:
To change multiple column names with the CHANGE query:
ALTER TABLE TableName (
CHANGE COLUMN OldColumn NewColumn DataType,
CHANGE COLUMN OldColumn NewColumn DataType);
|
ALTER TABLE CarInsurance
CHANGE COLUMN PolicyName PName VARCHAR(255);
Or:
To change multiple column names with the CHANGE query:
ALTER TABLE CarInsurance(
CHANGE COLUMN PolicyName PName VARCHAR(255),
CHANGE COLUMN EffectiveDate EDate DateTime);
|
|
RENAME COLUMNS
|
Renames columns present in a table. |
ALTER TABLE TableName
RENAME COLUMN OldColumn TO NewColumn;
Or:
To change multiple column names with RENAME query:
ALTER TABLE TableName (
RENAME COLUMN OldColumn TO NewColumn,
RENAME COLUMN OldColumn TO NewColumn);
|
ALTER TABLE CarInsurance
RENAME COLUMN PolicyName TO PName;
Or:
ALTER TABLE CarInsurance(
RENAME COLUMN PolicyName TO PName,
RENAME COLUMN EffectiveDate TO EDate);
|
|
INSERT INTO
|
Inserts new records into a table. |
INSERT INTO TableName (Column_1, Column_2, ...)
VALUES (Value_1, Value_2,...);
Or:
INSERT INTO TableName
VALUES (Value_1, Value_2);
|
|
|
UPDATE TABLE
|
Modifies the existing data items in a table. |
UPDATE TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE condition;
|
UPDATE CarInsurance
SET PaymentOption = 'Credit Card'
WHERE PolicyID = '567';
|
|
DELETE COLUMNS
|
Deletes column in the table. |
ALTER TABLE TableName
DROP COLUMN Column_1;
Or:
To drop multiple columns:
ALTER TABLE TableName (
DROP COLUMN Column_2,
DROP COLUMN Column_3);
|
ALTER TABLE CarInsurance
DROP COLUMN PaymentOption;
Or:
To drop multiple columns:
ALTER TABLE TableName (
DROP COLUMN PolicyName,
DROP COLUMN ExpiryDate);
|
ADD COLUMN |
Adds a new column in the table. |
ALTER TABLE TableName
ADD COLUMN NewColumn_1 DataType;
Or:
To add after a particular column:
ALTER TABLE TableName
ADD COLUMN NewColumn_1 DataType AFTER Column_2;
Or:
To add multiple columns:
ALTER TABLE TableName (
ADD COLUMN NewColumn_1 DataType AFTER Column_2,
ADD COLUMN NewColumn_1 DataType AFTER Column_2 );
|
ALTER TABLE CarInsurance
ADD COLUMN DOB datetime;
Or:
To add after a particular column:
ALTER TABLE CarInsurance
ADD COLUMN DOB datetime AFTER PaymentOption;
Or:
To add multiple columns:
ALTER TABLE CarInsurance (
ADD COLUMN DOB datetime AFTER PaymentOption,
ADD COLUMN Description varchar(255));
|
|
SELECT
|
Selects data values from a database and stores the data returned as output in the result set. |
SELECT Column_1, Column_2, ...
FROM TableName;
|
SELECT PolicyID, PolicyName FROM CarInsurance;
|
|
SELECT FROM
|
Retrieves data values from a table. |
SELECT Column_1, Column_2, ...
FROM TableName;
|
SELECT PolicyID, PolicyName FROM CarInsurance;
|
SELECT DISTINCT |
Returns only distinct values from the database. |
SELECT DISTINCT Column_1, Column_2, ...
FROM TableName;
|
SELECT PolicyID, PolicyName FROM CarInsurance;
|
|
SELECT WHERE
|
Filters data based on conditions. |
SELECT Column_1, Column_2, ...
FROM TableName WHERE Conditions;
|
SELECT PolicyName, EffectiveDate
FROM CarInsurance WHERE PolicyID = '567';
|
