SQL Cheat Sheet

Donald Green Donald Green
Feb 4, 2022 - 21:18
0 60
SQL Cheat Sheet

Create database and command tables

team Description
CREATE DATABASE; Create database
CREATE DATABASE DB1 does NOT EXIST; IF NOT EXISTS, you can instruct the MySQL server to check for the existence of a database with a similar name before creating the database.
CREATE DATABASE IF DATABASE DOES NOT EXIST1 CHARACTER SET the Latin1 character set uses the latin1_swedish_ci collation, which is Swedish case-insensitive.
SHOW DATABASES You can see the list of existing databases by running the following SQL command.
CREATE TABLE [IF NOT EXISTS] TableName (dataType field name [optional]) ENGINE = engine storage; Create Table Syntax

 

DATA TYPES

Numeric Data Types

team Description
TINYINT() -128 to 127, normal 0 to 255, UNSIGNED.
SMALLINT() -32768 to 32767, typically 0 to 65535 UNSIGNED.
AVERAGE () -8388608 to 8388607, typically 0 to 16777215 UNSIGNED.
int() -2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT() -9223372036854775808 to 9223372036854775807 OK 0 to 18446744073709551615 UNSIGNED.
FLOAT A small approximate floating point number.
DOUBLE (,) Large floating point number.
DECIMAL( , ) DOUBLE is stored as a string that accepts a fixed decimal point. Choice for storing currency values.

 

Text data types

team Description
char() Fixed section 0 to 255 characters long.
varchar() Variable section from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65535 characters.
blob A string with a maximum length of 65535 characters.
MEDIUM TEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONG TEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.

 

Data Types Date / Time

team Description
DATE YYYY-MM-DD
DATETIME YYYY-MM-DD HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
TIME HH:MM:SS

 

Other data types

team Description
ENUM The text value to store is selected from a list of predefined text values.
KIT This is also used to store text values ​​selected from a list of predefined text values. May have multiple meanings.
BOOL Synonym for TINYINT(1), used to store boolean values
BINARY Similar to CHAR, the difference is that texts are stored in binary format.
VARBINARY Like VARCHAR, the difference is that the texts are stored in binary format.

MySQL SELECT statement command

team Description
SELECT [DISTINCT | ALL] {* | [fieldExpression [AS newName]} FROM tableName [alias] [WHERE clause] [GROUP BY fieldName (s)] [HAVING clause] ORDER BY fieldName (s) SQL SELECT statement syntax
SELECT * FROM table1; select table
SELECT t1, t2, t3, t4 FROM table1; we are only interested in getting only the fields t1, t2, t3 and t4.
SELECT Concat (t1, (, t3,)), t4 FROM table2; Getting table2
SELECT column_name | value | expression [AS] alias_name; Alias ​​field name syntax

MySQL WHERE clause with AND, OR, IN, NOT IN commands

team Description
SELECT * FROM tableName WHERE condition; WHERE Sentence syntax
SELECT * FROM table1 WHERE t1 = 2 AND t2 = 2008; WHERE clause combined with operator - AND LOGICAL
SELECT * FROM table1 WHERE t1 = 1 OR t1 = 2; WHERE clause combined with OR OR LOGICAL operator
SELECT * FROM table2 WHERE t1 IN (1,2,3); WHERE clause combined with the IN keyword
SELECT * FROM table2 WHERE t1 IS NOT IN (1,2,3); WHERE clause combined with the NOT IN keyword
SELECT * FROM table2 WHERE t3 = Female; WHERE clause combined with Equal (=) for COMPARE OPERATORS
SELECT * FROM table3 WHERE t3 > 2000; WHERE clause combined with greater than (>) for COMPARATIVE OPERATORS
SELECT * FROM table1 WHERE t1 <> 1; WHERE clause combined with (<>) COMPARATIVE OPERATORS

MySQL INSERT INTO Table command

team Description
INSERT INTO table_name (column_1, column_2, ...) VALUES (value_1, value_2, ...); basic SQL INSERT command syntax
INSERT INTO table1 (t1, t2, t3, t4) VALUES (X1, X2, X3, X4); Insert data into table
INSERT INTO table_1 SELECT * FROM table_2; Inserting into a table from another table

MySQL DELETE command

team Description
DELETE FROM table_name [WHERE condition]; Delete row in MySQL

Example: -
DELETE FROM table1 WHERE table1_id = 18;

(delete a record of 18
numeric IDs from table table1.) DELETE FROM table1 WHERE table1_id IN (20,21);

(remove entry from 20 and 21 table id number table1)

MySQL upgrade command

team Description
UPDATE table_name SET column_name = new_value [WHERE condition]; update command syntax

Example: -
SELECT * FROM table1 WHERE t1 = 1;

(get record for t1 = 1)
UPDATE table1 SET t4 = X1 WHERE t1 = 1;

(update t4 value in table)

ORDER BY in MySQL: DESC & ASC command

team Description
SELECT statement ... [WHERE condition | GROUP BY field_name (s) HAS condition] ORDER BY field_name (s) [ASC | Descending]; Sort By Paragraph Basic Syntax
SELECT {fieldName(s) | *} FROM tableName (s) [WHERE condition] ORDER BY by field names ASC / DESC [LIMIT N] DESC and ASC syntax

Example: -
for DESC (desc)

SELECT * FROM table1 ORDER BY t3 DESC;
For ASC (ascending)

SELECT * FROM table1 ORDER BY t3 ASC;

MySQL GROUP BY and HAVING clause command

Group by

team Description
SELECT ... GROUP BY column_name1 [, column_name2, ...] statements [HAVING condition]; GROUP BY Syntax

An example of grouping one column: —
SELECT t4 FROM table1;

SELECT t4 FROM table1 GROUP BY t4; (Suppose we want unique values ​​for t4.)

An example of grouping several columns: -
SELECT t1_id, t4 FROM table2;

SELECT t1_id, t4 FROM table2 GROUP BY t1_id, t4; (using group by method)

Grouping and aggregating functions

team Description
SELECT t2,COUNT(t1) FROM table1 GROUP BY t2; Suppose we want to get the total number of values ​​of column t2 in our database.

HAVING item

team Description
SELECT * FROM table2 GROUP BY t1_id, t4 HAVE t1_id = x1; all t4 for table2 t1 id x1. We would use the following script to achieve our results.

MySQL Wildcards commands for Like, NOT Like, Escape, (%), (_)

% percentage of wildcards in MySQL

team Description
SELECT ... WHERE statements field name LIKE xxx%; basic syntax for wildcard % percent

Example: - We would use the percent wildcard to pattern match on both sides of the word "X1" as part of t2 table1
SELECT * FROM table1 WHERE t2 LIKE% X1%;

SELECT * FROM table1 WHERE t2 LIKE %X1;
(percentage wildcard at the beginning of search criteria only)

SELECT * FROM table1 WHERE t2 LIKE X1%;
(wildcard percent to the end of the specified pattern to match.)

underscore character _

team Description
SELECT * FROM table1 WHERE t3 LIKE x2_; all table1 that were t3 in the year "x2"

NOT as a wildcard command

team Description
SELECT * FROM table1 WHERE t3 DON'T LIKE X2_; Suppose we want to get table1 that were not t3 in year X2_

Substitution Command with the Escape Keyword

team Description
LIKE 67 # %% ESCAPE #; we want to check the string "67%"

MYSQL Regular Expressions (REGEXP)

team Description
SELECT ... WHERE statements field name REGEXP pattern; basic regular expression syntax

Example: - all table1 t1 that contain the word X1. It doesn't matter if "X1" is at the beginning, middle, or end of the heading.
SELECT * FROM table1 WHERE t1 REGEXP X1;

Regular expression metacharacters

team Description
* The asterisk (*) metacharacter is used to match zero (0) or more occurrences of strings preceding it
+ The plus (+) metacharacter is used to match one or more string instances that precede it.
? The question metacharacter (?) is used to match zero (0) or one occurrence of strings that precede it.
. The dot (.) metacharacter is used to match any single character except newline.
[abc] Charlist [abc] is used to match any of the nested characters.
[^abc] The character list [^abc] is used to match any character other than the supplied ones.
[A-Z] [AZ] is used to match any capital letter
[a-z] [Az] is used to match any lowercase letter
[0-9] [0-9] is used to match any digit from 0 to 9.
^ The caret symbol (^) is used to start the match at the start.
| The vertical bar (|) is used to highlight alternatives.
[[:<:]] [[: <:]] Matches the beginning of words.
[[:>:]] [[:>:]] Matches the end of words.
[:class:] [:Class:] matches a character class, i.e. [:Alpha:] for matching letters, [:space:] for matching spaces, [:punct:] for matching punctuation, and [:upper:] for upper class letters.

SQL Function Commands

String Functions

team Description
SELECT t1_id, t2, UCASE(t2) FROM table1; function "UCASE" to do this. It takes a string as a parameter and converts all letters to upper case.

Numeric functions

team Description example
DIV Integer division SELECT 23 DIV 6;
/ separation CHOOSE 23/6;
- Subtraction CHOOSE 23 - 6;
+ addition CHOOSE 23 + 6;
* multiplication SELECT 23 * 6 AS multiplication_result;
% or MOD module CHOOSE 23% 6; or CHOOSE 23 MOD 6;
Floor this function removes decimal places from a number and rounds to the nearest smallest number. SELECT FLOOR (23/6) AS floor_result;
Round this function rounds a number with decimal places to the nearest whole number. SELECT ROUND(23/6) AS round_result;

Stored Functions

team Description
CREATE FUNCTION sf_name([parameter(s)])
RETURNS data type

DETERMINISTIC
STATEMENTS		 basic syntax for creating a stored function
CREATE FUNCTION sf_name([parameter(s)]) Required and tells the MySQL server to create a function named `sf_name' with optional parameters defined in parentheses.
RETURN data type Required and specifies the data type that the function should return.
DETERMINISTIC The function will return the same values ​​if given the same arguments.
STATEMENTS The procedural code that executes the function.

MySQL Aggregate Function Commands

team Description
SELECT COUNT(t1_id) FROM table1 WHERE t1_id = 2; COUNT function
SELECT MIN(t3) FROM table2; MIN function
SELECT MAX. (T3) FROM table2; Max function
SELECT SUM(t4) FROM table3; SUM function
SELECT AVG(t4) FROM table3; AVG function

MySQL NULL & IS NOT NULL commands

team Description
SELECT COUNT (t3) FROM table1;

(if t3 has a value of zero, which is ignored)		 Null as value
CREATE TABLE table2 (
t1_number int NOT NULL,

t2_names varchar(255),
t3 varchar(6)

);
 NOT NULL values
comlumn_name IS NULL

comlumn_name NOT NULL
 NULL Keywords Basic Syntax
SELECT * FROM table1 WHERE t2_number IS NULL; IS NULL Example
SELECT * FROM table1 WHERE t2_number is NOT NULL; IS NOT NULL Example

MySQL AUTO_INCREMENT commands

team Description
CREATE TABLE table1 (
t1_id int (11) AUTO_INCREMENT,

t2_name varchar (150) DEFAULT NULL,
t3 varchar (500) DEFAULT NULL,

PRIMARY KEY (t1_id)
);		 Autoincrement Syntax

MYSQL - ALTER, DROP, RENAME, MODIFY

team Description
ALTER TABLE table_name ADD COLUMN column_name data_type; Alternative syntax
DROP TABLE sample_table; DROP TABLE Syntax
Rename table current_table_name to new_table_name; Rename command syntax
ALTER TABLE table1 CHANGE COLUMN t1_names t1name char (250) NOT NULL; CHANGE KEYWORD
ALTER TABLE table1MODIFY t1name char(50) NOT NULL; MODIFY KEYWORD
ALTER TABLE table1 ADD t4 date NULL AFTER t3; AFTER KEYWORD

MySQL LIMIT & OFFSET

team Description
SELECT {fieldname(s) | *} FROM tableName (s) [WHERE condition] LIMIT N; LIMIT keyword syntax
SELECT * FROM table1 LIMIT 1, 2; OFF SET in LIMIT query

MySQL SubQuery Commands:

team Description
SELECT t1_name FROM table1 WHERE category_id = (SELECT MIN(t1_id) from table2); subqueries

MySQL JOINS commands

team Description
SELECT * FROM table1 CROSS JOIN table2 Cross JOIN
SELECT table1.t1, table1.t2, table2.t1
FROM table1, table2

WHERE table2.id = table1.table2_id
 INNER JOIN
SELECT A.t1, B.t2, B.t3
FROM table2 AS LEFT

JOIN table1 AS B
ON B.table2_id = A.id		 LEFT JOIN
SELECT A.t1, A.t2, B.t3

FROM table1 AS A
CORRECT JOIN table2 AS B

ON B.id = A.table2_id
 CORRECT CONNECTION
SELECT A.t1, B.t2, B.t3 FROM
Table 2 AS LEFT JOIN


 "ON" and "USING" positions

MySQL UNION commands

team Description
SELECT column1, column2 FROM table1 UNION Syntax
SELECT column1, column2 FROM table2; THE UNION IS DIFFERENT

MySQL commands in views

team Description
CREATE VIEW view_name AS SELECT statement; View syntax
DROP VIEW general_v_movie_rentals; Dropping Views

MySQL Index commands

team Description
CREATE INDEX id_index ON table_name(column_name); Add Basic Index Syntax
DROP INDEX index_id ON table_name; Remove Basic Index Syntax