Mysql cheat sheet

By

MY SQL is a programming language used to manage and manipulate databases. If you'd like to know more about SQL, I can help with topics like:



*SQL Basics*: How to create tables, insert data, update records, and query databases.

*Advanced SQL*: Topics like JOIN operations, subqueries, stored procedures, and triggers.

*Database Design*: Normalization, database relationships, and indexing.


let's see step by step for the first step


 Create Data Base 

[ CREATE DATABASE UMESH ;]

To view database 

[mysql>  Show Database;]

To delete Database 

[Drop Database Umesh;]


Essential Mysql Commands

Select:- Choose a specific data from your Database. 

Update:- Update Data in your Database.

Delete:- Delete data from your Database.

Insert into:- insert new data into the database.

Create Database:- Generate a new Database.

Alter Database:- modify the existing database.

Create Table:- Create a new table in a database.

 Alter table:- change the Selected table.

Drop-Table:- Delete a Table.

Create Index:- Create an index

Delete Index:-  delete an index


Working with Tables

Tables are the key element of MySQL databases as they let you store all the information together in organized rows. Each row consists of columns that feature a specified data type. You have plenty of options for customization using the commands below.

Create a New Simple Table

 Use this command to create a new table:

    [     CREATE TABLE [IF NOT EXISTS] table_name( column_list);   ]

The code snippet below features a table for a list of movies that we want to organize by different attributes:
[
CREATE TABLE movies();
title VARCHAR(100),
year VARCHAR(100),
 director VARCHAR(50),
 genre VARCHAR(20),
 rating VARCHAR(100));   ]

View Tables

Use the next commands to get more information about the tables stored in your database.

show tables - call a list of all tables associated with a database.
DESCRIBE table_name; — see the columns of your table.
DESCRIBE table_name column_name; - review the column information in your table.

Delete a Table

To get rid of the table specify the table name in the following command:
 [ DROP TABLE tablename] 

Add an Additional Selection Clause

Include an additional pointer that indicates what type of data you need.

SELECT * FROM movies WHERE budget='1';
SELECT * FROM movies WHERE year='2020' AND rating='9';


Delete a Row


Use SELECT FROM syntax and WHERE clause to specify what rows to delete.
DELETE FROM movies WHERE budget='1';


Update Rows

Similarly, you can use different clauses to update all or specified rows in your table. To update all rows:
UPDATE table_name
SET column1 = value1,...;

To update data only in a specified set of rows you can use the WHERE clause:

UPDATE table_name
SET column_1 = value_1,
WHERE budget='5'

You can also update, select or delete rows using the JOIN clause. It comes particularly handy when you need to manipulate data from multiple tables in a single query.
Here's how to update rows with JOIN:

UPDATE table_name
INNER JOIN table1 ON table1.column1 = table2.column2
SET column1 = value1
WHERE budget='5'

Edit a Column

 you can alter any existing column with the following  command: 

ALTER table  movies Modify Column  Number  INT(3)




Sort Entries in a Column


You can sort the data in all columns and rows the same way you do in Excel e.g. alphabetically or from ascending to descending value.


SELECT * FROM users ORDER BY last_name ASC; SELECT * FROM users ORDER BY last_name DESC;


Search Columns

Here's how you can quickly find the information you need using WHERE and LIKE syntax:
SELECT * FROM movies WHERE genre LIKE 'com%'; SELECT * FROM movies WHERE title LIKE '%';

You can also exclude certain items from the search with NOT LIKE:
SELECT * FROM movies WHERE genre NOT LIKE 'hor%';

Select a Range

Or you can bring up a certain data range using the next command:
SELECT * FROM movies WHERE rating BETWEEN 8 AND 10;

Concentrate Columns

You can mash up two or more columns together with the CONCAT function:
SELECT CONCAT(first_name, '', last_name) AS 'Name', dept FROM users;

Data Types

Data types indicate what type of info you can store in a particular column of your table .
MYSQL has 3 types of Data types:  Numeric, text  and date/time

Working With Indexes

SQL indexes are special database structures that improve the speed of data retrieval operations. Think of them as a table of contents for a database, allowing the system to locate data quickly without scanning every row. Here's what you need to know about SQL indexes:

- *Purpose of Indexes*: Indexes speed up SELECT queries and other operations that retrieve data. However, they can slow down INSERT, UPDATE, and DELETE operations because the index needs to be updated whenever data changes.

 *Types of Indexes*:
  *Primary Index*: Created automatically when a primary key is defined. It ensures unique values and fast lookups.
  *Secondary Index*: Created on other columns besides the primary key. Useful for improving query performance on commonly searched columns.
  *Unique Index*: Ensures all values in the indexed column(s) are unique.
  *Composite Index*: Index on multiple columns. Helps when queries commonly use those specific combinations.

*Index Operations*:
  *CREATE INDEX*: Use this command to create an index on a column or set of columns.
  *DROP INDEX*: Removes an existing index.
  *ALTER INDEX*: Modify existing indexes.
  *INDEX ANALYSIS*: Tools and queries to analyze index efficiency and usage.

These are some basic concepts of SQL indexes. If you'd like specific examples or additional insights, let me know.
  Indexes are the core element of your database navigation. Use them to map the different types of data in your database, so that you don't need to parse all the records to find a match. NB: You have to update an index every time you are creating, changing or deleting a record in the table. Thus, it's best to create indexes only when you need to and for frequently searched columns. 

How to Create an Index

The basic syntax is as follows: CREATE INDEX index_name ON table_name (column, column2, .. You can also create a unique index — one that enforces the uniqueness of values in one or more columns. CREATE UNIQUE INDEX index_name ON table_nameCindex_column_1, index_column_2. 

How to Delete an Index in MySQL  

Use the DROP command for that: DROP INDEX index_name;



Working with Views

A view is a virtual representation of an actual table that you can assemble up to your liking (before adding the actual one to your database).
It features rows and columns, just like the real deal and can contain fields from one or more of the real tables from your database. 
In short, it's a good way to visualize and review data coming from different tables within a single screen.
How to Create a New View
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;



Update a View A view always displays fresh data since the database engine recreates it each time, using the view's SQL statement. To refresh your view use the next code: CREATE OR REPLACE VIEW view_name AS SELECT columni, column2, ... FROM table_name WHERE condition; Rename a View If you are dealing with multiple views at a time, it's best to give them distinctive names. Here's how that is done: RENAME TABLE view_name TO new_view_name; Show All Views To call up all current views for all tables from the database, use this snippet: SHOW FULL TABLES WHERE table_type = ‘VIEW’; Delete a View To delete a single view use the DROP command: DROP VIEW [IF EXISTS] view_name; You can also delete multiple views at a time: Drop Multiple views: DROP VIEW [IF EXISTS] view1, view2,


Working With Triggers

A trigger is a database object, associated with a table. It activates whenever a specific event happens for the table.
For example, you can set up triggers for events such as:
·
  • Row or delete updates
  • Row information inserts
This is a more advanced topic, so check the official MySQL trigger FAQ section for more details.

How to Create a Trigger

To create a simple trigger that will pop up before or after a certain operation such as INSERT, UPDATE or DELETE, use this code:

CREATE TRIGGER trigger_name
{BEFORE
AFTER} {INSERT | UPDATE DELETE }
ON table_name FOR EACH ROW
trigger_body;


Review All Triggers in Your Database

Search your database for all the active triggers using LIKE and WHERE clauses.
SHOW TRIGGERS
[{FROM IN} database_name]
[LIKE 'pattern' | WHERE search_condition];

How to Delete a Trigger

To remove a trigger, use the DROP command:
DROP TRIGGER [IF EXISTS] trigger_name;

Stored Procedures for MySQL

Stored procedures are reusable SQL code snippets that you can store in your database and use- as-needed over and over again. They save you tons of time since you don't need to write a query from scratch. Instead, you just call it to execute it.

How to Create a Stored Procedure in MySQL

Here's how to create a simple stored procedure with no additional parameters:
CREATE PROCEDURE procedure_name
AS
sql_statement
GO;
And here's another stored procedure example featuring the WHERE clause:
CREATE PROCEDURE SelectAllMovies @Title varchar(30) AS
SELECT * FROM Movies WHERE Title = @Title
GO;

Review All Stored Procedures

Similarly to triggers, you can review all stored procedures with LIKE and WHERE:
SHOW PROCEDURE STATUS
[LIKE 'pattern' | WHERE search_condition];

How to Delete a Stored Procedure

To get rid of a stored procedure you no longer need, use DROP:
DROP PROCEDURE [IF EXISTS] procedure name;

Logical Operators

Logical operators enable you to add more than one condition in WHERE clause. This makes them super handy for more advanced search, update, insert and delete queries.
In MySQL you have three main logical operators:

  • AND - use it to filter records that rely on 1+ condition. This way you can call records that satisfy all the conditions separated by AND.
  • OR - call records that meet any of the conditions separated by OR.
  • NOT review records that do not meet a certain condition (e.g. NOT blue). It's a handy operator from excluding certain data.
  • Plus, some additional special operators:

  • BETWEEN - select or search data between a range of set min and max values.
  • LIKE - compare one record to another. Handy operator for search.
  • IS NULL - compare some value with a NULL value.
  •  In - determine if a value or expression matches one of the values on your list.
  • ALL - compare a value or expression to all other values in a list.
  • ANY - compare a value or expression to any value in your list according to the specified condition.
  • EXISTS test if a certain record exists.

Aggregate Functions

Aggregate functions in MySQL allow you to run a calculation on a set of values and return a single scalar value. In essence, they are a great way to find the needed data faster and organize it better
using GROUP BY and HAVING clauses of the SELECT statement.
Below is an overview of these:

MIN
Find the smallest value of the selected column in your table:
SELECT MIN (column_name)
FROM table_name
WHERE condition;

MAX
Does the opposite and returns the largest value of the selected column:
SELECT MAX (column_name)
FROM table_name WHERE condition;

COUNT
Call up several rows that meet the specified criteria:
SELECT COUNT (column_name)
FROM table_name
WHERE condition;

AVG
Get the average value of a numeric column that you selected:
SELECT AVG (column_name)
FROM table_name
WHERE condition;

Arithmetic, Bitwise, Comparison, and
Compound Operators

Arithmetic Operators   +=*===
Bitwise Operators   &, 1, ^
Comparison Operators  = =, <, >, <=, >=, <>
Compound Operators   /=,=, &=, | *=

SQL Database Backup Commands
Finally, don't forget to regularly back up your progress as you are testing different commands and code snippets.

There are several easy ways to do it. To backup your database to an SQL file, use this code:
MySQL dump -u Username -p dbName YouWant > databasename_backup.sql

Then, to restore your work from a SQL backup, run the following line:
MySQL u Username -p dbNameYouWant < databasename_backup.sql






For Interview 

No comments:

Post a Comment