To understand SQL, you must first understand DBMS (database management systems) and databases in general. Data refers to a set of facts regarding a specific object. Whereas, a database refers to a set of small data units organized in a logical order. An RDBMS (Relational Database Management System) is a set of tools that allow users to visualize, organize, and manage the contents of a database while adhering to a set of standard rules that allow the database and the user to respond quickly. If interested in joining some training, learn more about SQL through MySQL Training. Below is the comprehensive version of SQL cheat sheet for interview for quick revision.
You can finally learn about SQL after being introduced to the concepts of RDBMS/DBMS, databases, and data. SQL (Structured Query Language) refers to the language that the users use to communicate with databases and extract the information that users need. It is used to store, manipulate, and retrieve data from databases. In this article, you will find a helpful SQL cheat sheet that records some of the more regularly used SQL elements, as well as a few less popular ones. We hope that it would assist developers, both novice and experienced, in improving their comprehension of the SQL language. Check out these top database courses to enhance your grasp in this domain and add value to your skills.
Infographic Version of SQL Cheat Sheet
Here is the infographic version with ample information about the SQL injection cheat sheet. Your search will end here if you were trying to search “my SQL cheat sheet” on the internet. Let’s get into this.
Here is the SQL Windows Function Cheat Sheet infograhics.
Keywords
A list of SQL keywords, together with a description, and where it is applicable is provided below. Later in the my SQL commands cheat sheet, some of the more complicated terms get their own section. When MySQL is mentioned next to an example, it signifies that the example applies only to MySQL databases as against any other type of database system.
1. ADD: A new column is added to an already available table.
Example: It creates a new column called ‘email_address’ in the ‘users’ database.
2. ADD CONSTRAINT: It adds a new constraint to an already available table, which can be used to establish rules for any of the table’s data.
Example: On the columns ID and SURNAME, a new primary key restriction named ‘user’ is added.
3. ALTER TABLE: A table’s columns can be added, deleted, or edited. It can even be used in order to remove and add constraints from a table.
Example: To a table called ‘deals’, it adds a new boolean column called ‘approved’
4. ALTER COLUMN: Substitutes the data type of a column in a table.
Example: Make the ‘incept date’ column in the ‘users’ table into a ‘datetime’ type.
5. ALL: If every subquery value satisfies the passed criteria, true is returned.
Example: Returns users in the HR department that have more tasks as compared to the users with the most tasks.
6. AND: Used to connect multiple conditions with a WHERE clause.
Example: Returns events in the United Kingdom, London.
7. ANY: Subquery values meet any condition, this function returns true.
Example: Products return from the table of products that received orders - situated in the orders table - in quantities greater than 5.
8. AS: Renames a column or table with alias value that exists as long as the query.
Example: Aliases south_west_user_subscription column
9. ASC: Uses 'ORDER BY' keyword to return data in an increasing order.
Example: Apricot, Berries, Plum, Raddish.
10. BETWEEN: Chooses values in a given range.
Example: Chooses stocks between 50 and 100.
11. CASE: Changes the output of the query based on certain conditions.
Example: Users and Subscriptions, as well a new column known as activity_levels makes a conclusion depending on the number of subscriptions.
12. CHECK: Inserts a constraint that creates a value limit that can be added to a column.
Example: Users who are on the user table are above the age if 18
13. CREATE DATABASE: Creating a new Database.
Example: Create a new database called 'setupwebsite'.
14. CREATE TABLE: Creating a new table.
Example: Create a table called 'users' in the 'setupwebsite' database
15. DEFAULT: Puts a default value for a column.
16. DELETE: Deleting data from a table.
17. DESC: Used along ORDER BY to return data in a decreasing order.
18. DROP COLUMN: Deleting column from a table.
19. DROP DATABASE: Deleting entire Database.
20. DROP DEFAULT: Deletes default value from a column.
21. DROP TABLE: Deletes table
22. EXISTS: Checks the subquery for the existence of any record.
23. FROM: Specifies the table from which to select or delete data.
24. IN: As a shorthand for multiple OR conditions, it is used alongside a WHERE clause
25. INSERT INTO: Addition of new rows in a table
26. IS NULL: Test for empty values
27. IS NOT NULL: Tests for values that are not empty
28. LIKE: The operand value matches a pattern, this function returns true.
29. NOT: If a record does not meet the condition, it returns true
Example: Comes true if the user’s first name does not end in ‘son’.
30. OR: Used with WHERE to include data if the condition is true or not.
Example: Users who live in Manchester or Sheffield.
31. ORDER BY: The DESC and ASC keywords are used to separate the data results in a default (ascending) or descending order.
Example: Putting countries in an alphabetical order.
32. ROWNUM: The row number matches the condition passed.
Example: Returns the top ten countries from the table of countries.
33. SELECT: Used to retrieve data from a database and return it as a set of results.
Example: All columns from all users are selected.
34. SELECT DISTINCT: The same as SELECT, however duplicate values are not allowed.
Example: Creating a table-backup by using data from the users table.
35. SELECT INFO: Data from one table is copied and inserted into another.
Example: Returns all nations from the users table, eliminating any entries that are duplicated.
36. SELECT TOP: Allows to retrieve a specific number of records from a table.
Example: Returns the top three automobiles from the cars table.
37. SET: Used with UPDATE to update data in a table.
Example: In the orders table, update the quantity and values for an order with an id of 642.
38. SOME: Same as ANY.
39. TOP: Used in conjunction with SELECT to retrieve a specific amount from a table of records.
Example: The top 5 users are returned from the users table.
40. TRUNCATE TABLE: It is like DROP, except instead of destroying the table and all of its data, this merely deletes the data.
Example: Unloads the table session but does not affect the table itself.
41. UNION: Returns only separate values after combining the results of two or more SELECT operations.
Example: Returns the cities from subscribers and events tables.
42. UNION ALL: As similar as UNION, but with additional values
43. UNIQUE: This requirement makes sure that each column’s values are unique.
Example: When building a new users table, it adds a different constraint to the id column.
44. UPDATE: Existing data in a table is updated.
Example: In the vehicles table, updates will be done regarding the serviceDue values and mileage for a vehicle with an id of 45.
45. VALUES: To insert new values to a table, use it in conjunction with the INSERT INTO keyword.
Example: Adding a brand new car to the table of cars.
46. WHERE: Filters the results to just show data that satisfies the specified criteria.
Example: Orders containing a quantity where greater than one item are returned.
Data Types
You must mention the data type that each column accepts while editing an existing table or creating a new one. Now let us see the data types in SQL as per the SQL basics cheat sheet for your reference.
1. String Data Types
Includes CHAR (size), VARCHAR (size), BINARY (size), VARBINARY (size), TINYBLOB, TINYTEXT, TEXT (size), BLOB (size), MEDIUMTEXT, MEDIUMBLOB, LONGTEXT, LONGBLOB, ENUM (a, b, c, etc…), SET (a, b, c, etc…)
2. Exact/Approximate Numeric Data Types
Includes BIT (size), TINYINT (size), BOOL, BOOLEAN, SMALLINT (size), MEDIUMINT (size), INT (size), INTEGER (size), BIGINT (size), FLOAT (p), DOUBLE (size, d), DECIMAL (size, d), DEC (size, d).
3. Date / Time Data Types
Includes DATE, DATETIME (fsp), TIMESTAMP (fsp), TIME (fsp), YEAR.
4. Binary Data types
It includes Variable/Fixed binary data types such as maximum length of 8000 bytes.
Views
A view is a result of an SQL set that is saved in the database under a label so that it can be accessed later without having to run the query again. These mainly come useful if there is a costly SQL query that may be required multiple times; instead of running it repeatedly to generate the same results set, you can do it once and save it like a view. The types of views are as follows:
- Creating Views: Creating a particular view.
- Replacing Views: With the REPLACE or CREATE command, a view can be replaced or updated.
- Deleting Views: With the DROP VIEW command, a view can be simply deleted.
Querying
Transact SQL or T-SQL, is the query language used by Microsoft SQL Server. It can assist with tasks such as retrieving multiple rows, inserting new rows, and retrieving data from a single row. The SQL Server uses it as a procedural language. Functions of SQL querying are:
- Aggregate Functions
- Ranking Functions
- Rowset Functions
- Scalar Functions
Indexes
Indexes refer to qualities that can be allocated to frequently searched columns in order to make the retrieval of data faster and more efficient. This does not imply that each column needs to be put as an index, as a column that has an index generally takes more time to update as compared to a column without an index. This is due to the fact that when columns with indexes are updated, the index must also be updated. The types of indexes are as follows:
An index is created under the name ‘idx_test’ on the first_name and sur_name columns of the table. In this situation, values are allowed to be duplicated.
It is identical to CREATE INDEX. However, in this instance values are not allowed to be duplicated.
Deletes/Removes/Drops an Index.
Triggers
Triggers refer to SQL codes that are executed on their own in response to some events on a specific table. These are used to ensure the data's integrity. A SQL trigger functions similarly to a real-world trigger. Types of triggers as per the SQL commands cheat sheet:
- Data Manipulation Language (DML): As previously stated, they respond to DML commands. These are DELETE, UPDATE, and INSERT.
- Data Definition Language (DDL): Triggers of this type, as expected, will respond to DDL commands such as DROP, ALTER, and CREATE.
- Logon Triggers: This class responds to LOGON events.
Keys
In databases that are relations, there is an element of foreign, candidate, alternate, and primary keys. In SQL tables these keys are included as constraints. In this sense, a table will consist of any of the keys in any combination.
1. Primary Key
A primary key identifies every record in a table uniquely. Only one primary key every table is allowed, out of which it can be assigned to any combination or single column. This, however, implies that every value in the columns must be distinct.
The primary key in a table is generally an ID column that is combined with the AUTO INCREMENT keyword. This refers to the fact that the value grows on its own as more records are added.
2. Foreign Key
A foreign key is generally applied to either multiple or single columns and is used to connect two tables in a relational database. The foreign key is referred to as the child key, whereas the table containing the candidate key, or referenced key, is referred to as the parent table.
As a foreign key prevents invalid data from being inserted that is not present in the parent table, it mainly means that the data column is split between two tables.
3. Candidate Key
It is a collection of data or attributes that defines the unique tuple in a table. These keys are as strong as primary (being unique). Any data which can be set as the primary key is the candidate key.
4. Alternate Key
In simpler words, all the candidate keys minus the primary keys are alternate keys of the tuple in a relation. The alternate key can be absent or present in a table. There will be zero or no alternate key if there is only one candidate key in the tuple.
There are other keys as well, including composite key (when a primary key has the two or more attributes), artificial key (when the primary key is long & complex, then artificial key is created for the tuple), and super key. These three are the variations of the above four.
Operators
1. Arithmetic Operators
- + , Add
- - , Subtract
- * , Multiply
- / , Divide
- % , Modulo
2. Bitwise Operator
- & ,Bitwise AND
- | ,Bitwise OR
- ^ ,Bitwise exclusive OR
- Comparison Operators
- =, Equal to
- >, Greater than
- <. Less than
- >=, Greater than or equal to
- <=, Less than or equal to
- <>, Not equal to
3. Compound Operators
- +=, Add equals
- -=, Subtract equals
- *=, Multiply equals
- /=, Divide equals
- %=, Modulo equals
- &= Bitwise AND equals
- ^-= Bitwise exclusive equals
- |*=, Bitwise OR equals
Functions
1. String Functions
- ASCII:
- CHAR_LENGTH:
- CHARACTER_LENGTH:
- CONCAT:
- CONCAT_WS:
- FIELD:
- FIND IN SET
- FORMAT
- INSERT
- ISTR
- LCASE
- LEFT
- LENGTH
- LOCATE
- LOWER
- LPAD
- LTRIM
- MID
- POSITION
- REPEAT
- REPLACE
- REVERSE
- RIGHT
- RPAD
- RTRIM
- SPACE
- STRCMP
- SUBSTR
- SUBSTRING
- SUBSTRING_INDEX
- TRIM
- UCASE
- UPPER
2. Numeric Functions
- ABS
- ACOS
- ASIN
- ATAN
- ATAN2
- AVG
- CEIL
- CEILING
- COS
- COT
- COUNT
- DEGREES
- DIV
- EXP
- FLOOR
- GREATEST
- LEAST
- LN
- LOG
- LOG10
- LOG2
- MAX
- MIN
- MOD
- PI
- POW
- POWER
- RADIANS
- RAND
- ROUND
- SIGN
- SIN
- SQRT
- SUM
- TAN
- TRUNCATE
3. Date Functions
- ADDDATE
- ADDTIME
- CURDATE
- CURRENT_DATE
- CUURENT_TIME
- CURRENT_TIMESTAMP
- CURTIME
- DATE
- DATEDIFF
- DATE_ADD
- DATE_FORMAT
- DATE_SUB
- DAY
- DAYNAME
- DAYOFWEEK
- DAYOFYEAR
- EXTRACT
- FROM DAYS
- HOUR
- LAST DAY
- LOCALTIME
- LOCALTIMESTAMP
- MAKEDATE
- MAKETIME
- MICROSECOND
- MINUTE
- MONTH
- MONTHNAME
- NOW
- PERIOD_ADD
- PERIOD_DIFF
- QUARTER
- SECOND
- SEC_TO_TIME
- STR_TO_DATE
- SUBDATE
- SUBTIME
- SYSDATE
- TIME
- TIME_FORMAT
- TIME_TO_SEC
- TIMEDIFF
- TIMESTAMP
- TO_DAYS
- WEEK
- WEEKDAY
- WEEKOFYEAR
- YEAR
- YEARWEEK
4. Misc Functions
- BIN
- BINARY
- CAST
- COALESCE
- CONNECTION_ID
- CONV
- CONVERT
- CURRENT_USER
- DATABASE
- GROUP BY
- HAVING
- IF
- IFNULL
- ISNULL
- LAST_INSERT_ID
- NULLIF
- SESSION_USER
- SYSTEM_USER
- USER
- VERSION
Conclusion
The SQL commands cheat sheet allows you to construct and manipulate a wide range of database objects with the distinct CREATE, ALTER, and DROP commands. The data can then be loaded into those database objects using commands like INSERT. A comprehensive range of commands, including SELECT, DELETE, and TRUNCATE, as well as cursor commands like DECLARE, OPEN, FETCH, and CLOSE, can be used to alter the data. We hope that your search for the comprehensive SQL syntax cheat sheet and SQL query cheat sheet should have ended here.
SQL is a very significant tool in the data management domain for which you can join the Knowledgehut MySQL Training.