Mysql Flashcards
CHAR(N)
CHAR(n) is a string of a fixed length of n characters. If it is CHARACTER SET utf8mb4, that means it occupies exactly
4*n bytes, regardless of what text is in it.
Most use cases for CHAR(n) involve strings that contain English characters, hence should be CHARACTER SET ascii.
(latin1 will do just as good.)
DATE
The DATE datatype comprises the date but no time component. Its format is ‘YYYY-MM-DD’ with a range of
‘1000-01-01’ to ‘9999-12-31’
DATETIME
The DATE datatype comprises the date but no time component. Its format is ‘YYYY-MM-DD’ with a range of
‘1000-01-01’ to ‘9999-12-31’
TIMESTAMP
The TIMESTAMP type is an integer type comprising date and time with an effective range from ‘1970-01-01 00:00:01’
UTC to ‘2038-01-19 03:14:07’ UTC.
YEAR
The YEAR type represents a year and holds a range from 1901 to 2155
TIME
The TIME type represents a time with a format of ‘HH:MM:SS’ and holds a range from ‘-838:59:59’ to ‘838:59:59’
Why not simply varchar(255)?
-> When a complex SELECT needs to create temporary table (for a subquery, UNION, GROUP BY, etc), the
preferred choice is to use the MEMORY engine, which puts the data in RAM. But VARCHARs are turned into CHAR
in the process. This makes VARCHAR(255) CHARACTER SET utf8mb4 take 1020 bytes. That can lead to needing
to spill to disk, which is slower
-> In certain situations, InnoDB will look at the potential size of the columns in a table and decide that it will be
too big, aborting a CREATE TABLE
VARCHAR versus TEXT
->Never use TINYTEXT.
->Almost never use CHAR – it is fixed length; each character is the max length of the CHARACTER SET (eg, 4
bytes/character for utf8mb4).
->With CHAR, use CHARACTER SET ascii unless you know otherwise.
->VARCHAR(n) will truncate at n characters; TEXT will truncate at some number of bytes. (But, do you want
truncation?)
->*TEXT may slow down complex SELECTs due to how temp tables are handled
INT as AUTO_INCREMENT
Any size of INT may be used for AUTO_INCREMENT. UNSIGNED is always appropriate
INTEGER TYPES
TINYINT 1 Bytes, SMALLINT 2 Bytes, MEDIUMINT 3 Bytes, INTEGER, INT 4 Bytes, BIGINT 8 Bytes
Fixed Point Types
DECIMAL, NUMERIC
Floating Point Types
FLOAT 4 bytes, DOUBLE 8 bytes
Bit Value Type
BIT
The BIT type is useful for storing bit-field values. BIT(M) allows storage of up to M-bit values where M is in the
range of 1 to 64
You can also specify values with bit value notation.
b’111’ -> 7
b’10000000’ -> 128
Sometimes it is handy to use ‘shift’ to construct a single-bit value, for example (1 «_space;7) for 128.
The maximum combined size of all BIT columns in an NDB table is 4096.
Decimal
hese values are stored in binary format. In a column declaration, the precision and scale should be specified
Precision represents the number of significant digits that are stored for values.
Scale represents the number of digits stored after the decimal
salary DECIMAL(5,2)
Comments
This comment continues to the end of line
– This comment continues to the end of line
/* This is an in-line comment /
/
This is a
multiple-line comment
*/
INSERT, ON DUPLICATE KEY UPDATE
INSERT INTO table_name
(index_field, other_field_1, other_field_2)
VALUES
(‘index_value’, ‘insert_value’, ‘other_value’)
ON DUPLICATE KEY UPDATEother_field_1 = ‘update_value’,other_field_2 = VALUES(other_field_2);
See the example above where other_field_1 is set to insert_value on INSERT or to
update_value on UPDATE while other_field_2 is always set to other_value
INSERT with AUTO_INCREMENT +
LAST_INSERT_ID()
INSERT INTO t (this, that) VALUES (..., ...); SELECT LAST_INSERT_ID() INTO @id;
GROUP BY using HAVING
Using GROUP BY … HAVING to filter aggregate records is analogous to using SELECT … WHERE to filter individual
records
Group By using Group Concat
Group Concat is used in MySQL to get concatenated values of expressions with more than one result per column.
Meaning, there are many rows to be selected back for one column such as Name(1):Score(*)
ROUND
For exact numeric values (e.g. DECIMAL): If the first decimal place of a number is 5 or higher, this function will round a number to the next integer away from zero. If that decimal place is 4 or lower, this function will round to the next integer value closest to zero. SELECT ROUND(4.51) -> 5 SELECT ROUND(4.49) -> 4 SELECT ROUND(-4.51) -> -5
CEIL, CEILING
To round up a number use either the CEIL() or CEILING() function SELECT CEIL(1.23) -> 2 SELECT CEILING(4.83) -> 5
FLOOR
To round down a number, use the FLOOR() function SELECT FLOOR(1.99) -> 1
POW, POWER
To raise a number x to a power y, use either the POW() or POWER() functions SELECT POW(2,2); => 4 SELECT POW(4,2); => 16
SQRT
Use the SQRT() function. If the number is negative, NULL will be returned SELECT SQRT(16); -> 4 SELECT SQRT(-3); -> NULL
RAND
To generate a pseudorandom floating-point number between 0 and 1, use the RAND() function
ABS
Return the absolute value of a number SELECT ABS(2); -> 2 SELECT ABS(-46); -> 46
SIGN
The sign of a number compares it to 0 -1 n < 0 SELECT SIGN(42); -> 1 0 n = 0 SELECT SIGN(0); -> 0 1 n > 0 SELECT SIGN(-3); -> -1 SELECT SIGN(-423421); -> -1
ASCII()
Return numeric value of left-most character
BIN()
Return a string containing binary representation of a number
SYSDATE()
This function returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format,
depending on whether the function is used in a string or numeric context. It returns the date and time in the
current time zone.
NOW()
This function is a synonym for SYSDATE().
This function returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ or YYYYMMDDHHMMSS format,
depending on whether the function is used in a string or numeric context. It returns the date and time in the
current time zone.
CURDATE()
This function returns the current date, without any time, as a value in ‘YYYY-MM-DD’ or YYYYMMDD format, depending
on whether the function is used in a string or numeric context. It returns the date in the current time zone.
Testing against a date range
Although it is very tempting to use BETWEEN … AND … for a date range, it is problematical. Instead, this pattern avoids
most problems:
WHERE x >= ‘2016-02-25’
AND x < ‘2016-02-25’ + INTERVAL 5 DAY
Advantages:
BETWEEN is ‘inclusive’ thereby including the final date or second.
23:59:59 is clumsy and wrong if you have microsecond resolution on a DATETIME.
This pattern avoid dealing with leap years and other data calculations.
It works whether x is DATE, DATETIME or TIMESTAMP
Using an index for a date and time lookup
n inefficient way to do that is this: WHERE DATE(x) = '2016-09-01' /* slow! */ A better way to do the operation is this WHERE x >= '2016-09-01' AND x < '2016-09-01' + INTERVAL 1 DAY Because it use the index
Retrieve the current date and time in a
particular time zone
SET time_zone='Asia/Kolkata'; SELECT NOW(); SET time_zone='UTC'; SELECT NOW();
CONVERT_TZ()
If you have a stored DATE or DATETIME (in a column somewhere) it was stored with respect to some time zone, but in
MySQL the time zone is not stored with the value. So, if you want to convert it to another time zone, you can, but
you must know the original time zone. Using CONVERT_TZ() does the conversion. This example shows rows sold in
California in local time.
SELECT CONVERT_TZ(date_sold,’UTC’,’America/Los_Angeles’) date_sold_local
FROM sales
WHERE state_sold = ‘CA’
Get difference between UTC and SYSTEM timestamp in minutes
CREATE TEMPORARY TABLE times (dt DATETIME, ts TIMESTAMP);
SET time_zone = ‘UTC’;
INSERT INTO times VALUES(NOW(), NOW());
SET time_zone = ‘SYSTEM’;
SELECT dt, ts, TIMESTAMPDIFF(MINUTE, dt, ts)offset FROM times;
DROP TEMPORARY TABLE times;
REGEXP / RLIKE
The REGEXP (or its synonym, RLIKE) operator allows pattern matching based on regular expressions.
Select all employees whose FIRST_NAME starts with N.
SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘^N’
Select all employees whose PHONE_NUMBER ends with 4569
SELECT * FROM employees WHERE PHONE_NUMBER REGEXP ‘4569$’
Select all employees whose FIRST_NAME does not start with N.
SELECT * FROM employees WHERE FIRST_NAME NOT REGEXP ‘^N’
Select all employees whose LAST_NAME contains in and whose FIRST_NAME contains a.
SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘a’ AND LAST_NAME REGEXP ‘in’
Select all employees whose FIRST_NAME starts with A or B or C
SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘^[ABC]’
Select all employees whose FIRST_NAME starts with A or B or C and ends with r, e, or i.
SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘^[ABC]|[rei]$’
VIEW
SQL statements to be packed in the views. It can be a SELECT statement to fetch data from one
or more tables
1064 error
Getting a “1064 error” message from MySQL means the query cannot be parsed without syntax errors. In other
words it can’t make sense of the query
Error code 1175
This error appears while trying to update or delete records without including the WHERE clause that uses the KEY
column.
Error code 1215
This error occurs when tables are not adequately structured to handle the speedy lookup verification of Foreign Key
(FK) requirements that the developer is mandating.
CURSORS
Cursors enable you to iterate the results of the query one by line. DECLARE command is used to init cursor and associate it
with a specific SQL query:
DECLARE student CURSOR FOR SELECT name FROM student;
PREPARE
prepares a statement for execution
EXECUTE
executes a prepared statement
DEALLOCATE PREPARE
releases a prepared statement
JSON_OBJECT
JSON_OBJECT creates JSON Objects:
SELECT JSON_OBJECT('key1',col1 , 'key2',col2 , 'key3','col3') as myobj;JSON_ARRAY
JSON_ARRAY creates JSON Array as well: SELECT JSON_ARRAY(col1,col2,'col3') as myarray;
Triggers action
BEFORE trigger activates before executing the request,
AFTER triggers fire after the change.
Events Triggers can be attached to
INSERT
UPDATE
DELETE
innodb_buffer_pool_size
his should be set to about 70% of available RAM (if you have at least 4GB of RAM; a smaller percentage if you have
a tiny VM or antique machine). The setting controls the amount of cache used by the InnoDB ENGINE. Hence, it is
very important for performance of InnoDB.
max_allowed_packet
If you need to store images or videos in the column then we need to change the value as needed by your
application
max_allowed_packet = 10M
M is Mb, G in Gb, K in Kb
group_concat_max_len
group_concat is used to concatenate non-null values in a group. The maximum length of the resulting string can be
set using the group_concat_max_len option:
SET [GLOBAL | SESSION] group_concat_max_len = val;
Setting the GLOBAL variable will ensure a permanent change, whereas setting the SESSION variable will set the value
for the current session.
Minimal InnoDB configuration
This is a bare minimum setup for MySQL servers using InnoDB tables. Using InnoDB, query cache is not required.
Reclaim disk space when a table or database is DROPed. If you’re using SSDs, flushing is a redundant operation
(SDDs are not sequential).
default_storage_engine = InnoDB
query_cache_type = 0
innodb_file_per_table = 1
innodb_flush_neighbors = 0
List of user privileges
Can be either ALL Or a combination of SELECT INSERT UPDATE DELETE CREATE DROP
Processlist
Processlist
This will show all active & sleeping queries in that order then by how long.
SELECT * FROM information_schema.PROCESSLIST ORDER BY INFO DESC, TIME DESC;
Get all store procedures searching
Stored Procedure Searching
Easily search thru all Stored Procedures for words and wildcards.
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%word%’;
Implicit/automatic casting
select '123' * 2; To make the multiplication with 2 MySQL automatically converts the string 123 into a number. Return value: 246 The conversion to a number starts from left to right. If the conversion is not possible the result is 0 select '123ABC' * 2 Return value: 246
SELECT
SELECT is used to retrieve rows selected from one or more tables.
DISTINCT
The DISTINCT clause after SELECT eliminates duplicate rows from the result set.
Select * -> Best practice
Best Practice Do not use * unless you are debugging or fetching the row(s) into associative arrays, otherwise schema changes (ADD/DROP/rearrange columns) can lead to nasty application errors. Also, if you give the list of columns you need in your result set, MySQL's query planner often can optimize the query.
Select * -> Pros
- When you add/remove columns, you don’t have to make changes where you did use SELECT *
- It’s shorter to write
- You also see the answers, so can SELECT *-usage ever be justified?
Select * -> Cons
- You are returning more data than you need. Say you add a VARBINARY column that contains 200k per row.
You only need this data in one place for a single record - using SELECT * you can end up returning 2MB per
10 rows that you don’t need - Explicit about what data is used
- Specifying columns means you get an error when a column is removed
- The query processor has to do some more work - figuring out what columns exist on the table (thanks
@vinodadhikary) - You can find where a column is used more easily
- You get all columns in joins if you use SELECT *
- You can’t safely use ordinal referencing (though using ordinal references for columns is bad practice in itself)
- In complex queries with TEXT fields, the query may be slowed down by less-optimal temp table processing
LIKE Performance notes
Performance Notes If there is an index on username, then
LIKE ‘adm’ performs the same as `= ‘adm’
LIKE ‘adm% is a “range”, similar to BETWEEN..AND.. It can make good use of an index on the column.
LIKE ‘%adm’ (or any variant with a leading wildcard) cannot use any index. Therefore it will be slow. On tables
with many rows, it is likely to be so slow it is useless.
RLIKE (REGEXP) tends to be slower than LIKE, but has more capabilities.
While MySQL offers FULLTEXT indexing on many types of table and column, those FULLTEXT indexes are not
used to fulfill queries using LIKE.
ALIAS
SQL aliases are used to temporarily rename a table or a column. They are generally used to improve readability.
SELECT with LIKE(_)
A _ character in a LIKE clause pattern matches a single character.
Query
SELECT username FROM users WHERE users LIKE ‘admin_’;
SELECT with date range
SELECT … WHERE dt >= ‘2017-02-01’
AND dt < ‘2017-02-01’ + INTERVAL 1 MONTH
Sure, this could be done with BETWEEN and inclusion of 23:59:59. But, the pattern has this benefits:
You don’t have pre-calculate the end date (which is often an exact length from the start)
You don’t include both endpoints (as BETWEEN does), nor type ‘23:59:59’ to avoid it.
It works for DATE, TIMESTAMP, DATETIME, and even the microsecond-included DATETIME(6).
It takes care of leap days, end of year, etc.
It is index-friendly (so is BETWEEN).
LIMIT
->the first argument represents the row from which the result set rows will be presented – this number is
often mentioned as an offset, since it represents the row previous to the initial row of the constrained result
set. This allows the argument to receive 0 as value and thus taking into consideration the first row of the nonconstrained result set.
->the second argument specifies the maximum number of rows to be returned in the result set (similarly to
the one argument’s example).
