MySQL Essentials – DZone Refcardz

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.

DROP TABLE CarInsurance;

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';

Leave a Comment Cancel reply