MySQL Cheat Sheet, Cheat Sheet of Principles of Database Management

Download MySQL Cheat Sheet and more Cheat Sheet Principles of Database Management in PDF only on Docsity! 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 // New method chaining used for executing an SQL SELECT statement // Recommended way for executing queries var employees = db.getTable('employee'); var res = employees.select(['name', 'age']). where('name like :param'). orderBy(['name']). bind('param', 'm%').execute(); // Traditional SQL execution by passing an SQL string // It should only be used when absolutely necessary var result = session.sql('SELECT name, age ' + 'FROM employee ' + 'WHERE name like ? ' + 'ORDER BY name').bind('m%').execute(); MySQL Cheat Sheet Ready to advance your coding skills and master databases? Great! Then you will find our MySQL cheat sheet absolutely handy. Sounds promising? Let’s jump in then! MySQL is a popular, open-source, relational database that you can use to build all sorts of web databases — from simple ones, cataloging some basic information like book recommendations to more complex data warehouses, hosting hundreds of thousands of records. Learning MySQL is a great next step for those who already know PHP or Perl. In this case, you can create websites that interact with a MySQL database in real-time and display searchable and categorized records to users. MySQL 101: Getting Started How to Connect to MySQL Create a new MySQL User Account Create a New Database Delete a MySQL Database Essential MySQL Commands Working with Tables Working With Table Columns Data Types Working With Indexes Working with Views Working with Triggers Stored Procedures for MySQL Logical Operators Aggregate Functions Arithmetic, Bitwise, Comparison, and Compound Operators SQL Database Backup Commands Conclusions 03 03 03 04 04 04 05 06 08 12 12 14 15 16 17 18 18 18 Table of Contents Working with Tables Create a New Simple Table Delete a Table View 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. The code snippet below features a table for a list of movies that we want to organize by different attributes: Use this command to create a new table: To get rid of the table specify the table name in the following command: 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 information of the column in your table. CREATE TABLE [IF NOT EXISTS] table_name( column_list ); DROP TABLE tablename; CREATE TABLE movies( title VARCHAR(100), year VARCHAR(100), director VARCHAR(50), genre VARCHAR(20), rating VARCHAR(100), ); MySQL Cheat Sheet 5 WebsiteSetup.org - MySQL Cheat Sheet Working With Table Columns Add New Column Delete/Drop a Column Insert New Row Select Data from The Row Use columns to store alike information that shares the same attribute (e.g. movie director names). Columns are defined by different storage types: ALTER TABLE table ADD [COLUMN] column_name; ALTER TABLE table_name DROP [COLUMN] column_name; INSERT INTO table_name (field1, field2, ...) VALUES (value1, value2, ...) Specify what kind of information you want to retrieve from a certain row. SELECT value1, value2 FROM field1 When designing columns for your database, your goal is to select the optimal length to avoid wasted space and maximize performance. Below are the key commands for working with tables. An in-depth overview comes in the next section! • CHAR • VARCHAR • TEXT • BLOB • EUT • And others. MySQL Cheat Sheet 6 WebsiteSetup.org - MySQL Cheat Sheet Add an Additional Selection Clause Delete a Row Update Rows Edit a Column Include an additional pointer that indicates what type of data do you need. Use SELECT FROM syntax and WHERE clause to specify what rows to delete. Similarly, you can use different clauses to update all or specified rows in your table. To update all rows: You can alter any existing column with the following snippet: You can also update, select or delete rows using 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: To update data only in a specified set of rows you can use WHERE clause: SELECT * FROM movies WHERE budget=’1’; SELECT * FROM movies WHERE year=’2020’ AND rating=’9’; DELETE FROM movies WHERE budget=’1’; UPDATE table_name SET column1 = value1, ...; ALTER TABLE movies MODIFY COLUMN number INT(3) UPDATE table_name INNER JOIN table1 ON table1.column1 = table2.column2 SET column1 = value1, WHERE budget=’5’ UPDATE table_name SET column_1 = value_1, WHERE budget=’5’ MySQL Cheat Sheet 7 WebsiteSetup.org - MySQL Cheat Sheet Blob and Text Data Types Text Storage Formats BLOB binary range enables you to store larger amounts of text data. The maximum length of a BLOB is 65,535 (216 − 1) bytes. BLOB values are stored using a 2-byte length prefix. NB: Since text data can get long, always double-check that you do not exceed the maximum lengths. The system will typically generate a warning if you go beyond the limit. But if nonspace characters get truncated, you may just receive an error without a warning. • TINYBLOB — sets the maximum column length at 255 (28 − 1) bytes. TINYBLOB values are stored using a 1-byte length prefix. • MEDIUMBLOB — sets the maximum column length at 16,777,215 (224 − 1) bytes. MEDIUMBLOB values are stored using a 3-byte length prefix. • LONGBLOB — sets the maximum column length at 4,294,967,295 or 4GB (232 − 1) bytes. LONGBLOB values are stored using a 4-byte length prefix. Note: The max length will also depend on the maximum packet size that you configure in the client/server protocol, plus available memory. TEXT does the same job but holds values of smaller length. A TEXT column can have a maximum length of 65,535 (216 − 1) characters. However, the max length can be smaller if the value contains multibyte characters. TEXT value is also stored using a 2-byte length prefix. • TINYTEXT — store a value using a 1-byte length prefix. The maximum supported column length is 255 (28 − 1) characters. • MEDIUMTEXT — store a value using a 3-byte length prefix. The maximum supported column length is 16,777,215 (224 − 1) characters. • LONGTEXT — store a value using a 4-byte length prefix. The maximum supported column length is 4,294,967,295 or 4GB (232 − 1) characters. Note: Again, the length cap will also depend on your configured maximum packet size in the client/server protocol and available memory. • CHAR — specifies the max number of non-binary characters you can store. The range is from 0 to 255. • VARCHAR — store variable-length non-binary strings. The maximum number of characters you can store is 65,535 (equal to the max row size). • VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data, unlike CHAR values. • BYNARY — store binary data in the form of byte strings. Similar to CHAR. • VARBYNARY — store binary data of variable length in the form of byte strings. Similar to VARCHAR. • ENUM — store permitted text values that you enumerated in the column specification when creating a table. • ENUM columns can contain a maximum of 65,535 distinct elements and have > 255 unique element list definitions among its ENUM. • SET — another way to store several text values that were chosen from a predefined list of values. • SET column can contain a maximum of 64 distinct members and have > 255 unique element list definitions among its SET. MySQL Cheat Sheet 10 WebsiteSetup.org - MySQL Cheat Sheet Date and Time Data Types As the name implies, this data type lets you store the time data in different formats. • DATE — use it for values with a date part only. MySQL displays DATE values in the ‘YYYY-MM- DD’ format. • Supported data range is ‘1000-01-01’ to ‘9999-12-31’. • DATETIME — record values that have both date and time parts. The display format is ‘YYYY- MM-DD hh:mm:ss’. • Supported data range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’. • TIMESTAMP — add more precision to record values that have both date and time parts, up till microseconds in UTC. • Supported data range is ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. • TIME — record just time values in either ‘hh:mm:ss’ or ‘hhh:mm:ss’ format. The latter can represent elapsed time and time intervals. • Supported data range is ‘-838:59:59’ to ‘838:59:59’. • YEAR — use this 1-byte type used to store year values. • A 4-digit format displays YEAR values as 0000, with a range between 1901 to 2155. • A 2-digit format displays YEAR values as 00. The accepted range is ‘0’ to ‘99’ and MySQL will convert YEAR values in the ranges 2000 to 2069 and 1970 to 1999. MySQL Cheat Sheet 11 WebsiteSetup.org - MySQL Cheat Sheet How to Create an Index How to Delete an Index in MySQL How to Create a New View The basic syntax is as follows: Use the DROP command for that: Working With Indexes Working with Views 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. 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. You can also create a unique index — one that enforces the uniqueness of values in one or more columns. CREATE INDEX index_name ON table_name (column1, column2, ...); DROP INDEX index_name; CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; CREATE UNIQUE INDEX index_name ON table_name(index_column_1,index_column_2,...); MySQL Cheat Sheet 12 WebsiteSetup.org - MySQL Cheat Sheet 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 Review All Stored Procedures How to Delete a Stored Procedure Here’s how to create a simple stored procedure with no additional parameters: Similarly to triggers, you can review all stored procedures with LIKE and WHERE: To get rid of a stored procedure you no longer need, use DROP: And here’s another stored procedure example featuring WHERE clause: CREATE PROCEDURE procedure_name AS sql_statement GO; SHOW PROCEDURE STATUS [LIKE ‘pattern’ | WHERE search_condition]; DROP PROCEDURE [IF EXISTS] procedure_name; CREATE PROCEDURE SelectAllMovies @Title varchar(30) AS SELECT * FROM Movies WHERE Title = @Title GO; MySQL Cheat Sheet 15 WebsiteSetup.org - MySQL Cheat Sheet 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. MySQL Cheat Sheet 16 WebsiteSetup.org - MySQL Cheat Sheet 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 COUNT AVG SUM MAX Find the smallest value of the selected column in your table: Call up several rows that meet the specified criteria: Get the average value of a numeric column that you selected: Receive a total sum of a numeric column that you selected: Does the opposite and returns the largest value of the selected column: SELECT MIN (column_name) FROM table_name WHERE condition; SELECT COUNT (column_name) FROM table_name WHERE condition; SELECT AVG (column_name) FROM table_name WHERE condition; SELECT SUM(column_name) FROM table_name WHERE condition; SELECT MAX (column_name) FROM table_name WHERE condition; MySQL Cheat Sheet 17 WebsiteSetup.org - MySQL Cheat Sheet