Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

SQL > Mysql > Flashcards

Mysql Flashcards

1
Q

CHAR(N)

A

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.)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

DATE

A

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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

DATETIME

A

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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

TIMESTAMP

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

YEAR

A

The YEAR type represents a year and holds a range from 1901 to 2155

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

TIME

A

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’

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Why not simply varchar(255)?

A

-> 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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

VARCHAR versus TEXT

A

->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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

INT as AUTO_INCREMENT

A

Any size of INT may be used for AUTO_INCREMENT. UNSIGNED is always appropriate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

INTEGER TYPES

A 
TINYINT          1 Bytes, 
SMALLINT      2 Bytes, 
MEDIUMINT   3 Bytes, 
INTEGER, INT 4 Bytes, 
BIGINT            8 Bytes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Fixed Point Types

A

DECIMAL, NUMERIC

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Floating Point Types

A

FLOAT 4 bytes, DOUBLE 8 bytes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Bit Value Type

A

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 &laquo_space;7) for 128.
The maximum combined size of all BIT columns in an NDB table is 4096.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Decimal

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Comments

A

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
*/

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

INSERT, ON DUPLICATE KEY UPDATE

A

INSERT INTO table_name
(index_field, other_field_1, other_field_2)
VALUES
(‘index_value’, ‘insert_value’, ‘other_value’)
ON DUPLICATE KEY UPDATE
other_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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

INSERT with AUTO_INCREMENT +

LAST_INSERT_ID()

A 
INSERT INTO t (this, that) VALUES (..., ...);
SELECT LAST_INSERT_ID() INTO @id;
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

GROUP BY using HAVING

A

Using GROUP BY … HAVING to filter aggregate records is analogous to using SELECT … WHERE to filter individual
records

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Group By using Group Concat

A

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(*)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

ROUND

A 
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

CEIL, CEILING

A 
To round up a number use either the CEIL() or CEILING() function
SELECT CEIL(1.23) -> 2
SELECT CEILING(4.83) -> 5
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

FLOOR

A 
To round down a number, use the FLOOR() function
SELECT FLOOR(1.99) -> 1
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

POW, POWER

A 
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

SQRT

A 
Use the SQRT() function. If the number is negative, NULL will be returned
SELECT SQRT(16); -> 4
SELECT SQRT(-3); -> NULL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

RAND

A

To generate a pseudorandom floating-point number between 0 and 1, use the RAND() function

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

ABS

A 
Return the absolute value of a number
SELECT ABS(2); -> 2
SELECT ABS(-46); -> 46
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

SIGN

A 
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

ASCII()

A

Return numeric value of left-most character

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

BIN()

A

Return a string containing binary representation of a number

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

SYSDATE()

A

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.

31
Q

NOW()

A

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.

32
Q

CURDATE()

A

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.

33
Q

Testing against a date range

A

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

34
Q

Using an index for a date and time lookup

A 
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
35
Q

Retrieve the current date and time in a

particular time zone

A 
SET time_zone='Asia/Kolkata';
SELECT NOW();
SET time_zone='UTC';
SELECT NOW();
36
Q

CONVERT_TZ()

A

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’

37
Q

Get difference between UTC and SYSTEM timestamp in minutes

A

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;

38
Q

REGEXP / RLIKE

A

The REGEXP (or its synonym, RLIKE) operator allows pattern matching based on regular expressions.

39
Q

Select all employees whose FIRST_NAME starts with N.

A

SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘^N’

40
Q

Select all employees whose PHONE_NUMBER ends with 4569

A

SELECT * FROM employees WHERE PHONE_NUMBER REGEXP ‘4569$’

41
Q

Select all employees whose FIRST_NAME does not start with N.

A

SELECT * FROM employees WHERE FIRST_NAME NOT REGEXP ‘^N’

42
Q

Select all employees whose LAST_NAME contains in and whose FIRST_NAME contains a.

A

SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘a’ AND LAST_NAME REGEXP ‘in’

43
Q

Select all employees whose FIRST_NAME starts with A or B or C

A

SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘^[ABC]’

44
Q

Select all employees whose FIRST_NAME starts with A or B or C and ends with r, e, or i.

A

SELECT * FROM employees WHERE FIRST_NAME REGEXP ‘^[ABC]|[rei]$’

45
Q

VIEW

A

SQL statements to be packed in the views. It can be a SELECT statement to fetch data from one
or more tables

46
Q

1064 error

A

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

47
Q

Error code 1175

A

This error appears while trying to update or delete records without including the WHERE clause that uses the KEY
column.

48
Q

Error code 1215

A

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.

49
Q

CURSORS

A

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;

50
Q

PREPARE

A

prepares a statement for execution

51
Q

EXECUTE

A

executes a prepared statement

52
Q

DEALLOCATE PREPARE

A

releases a prepared statement

53
Q

JSON_OBJECT

A 
JSON_OBJECT creates JSON Objects:
SELECT JSON_OBJECT('key1',col1 , 'key2',col2 , 'key3','col3') as myobj;
54
Q

JSON_ARRAY

A 
JSON_ARRAY creates JSON Array as well:
SELECT JSON_ARRAY(col1,col2,'col3') as myarray;
55
Q

Triggers action

A

BEFORE trigger activates before executing the request,

AFTER triggers fire after the change.

56
Q

Events Triggers can be attached to

A

INSERT
UPDATE
DELETE

57
Q

innodb_buffer_pool_size

A

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.

58
Q

max_allowed_packet

A

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

59
Q

group_concat_max_len

A

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.

60
Q

Minimal InnoDB configuration

A

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

61
Q

List of user privileges

A 
Can be either
ALL 
Or a combination of
SELECT
INSERT
UPDATE
DELETE
CREATE
DROP
62
Q

Processlist

A

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;

63
Q

Get all store procedures searching

A

Stored Procedure Searching
Easily search thru all Stored Procedures for words and wildcards.
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%word%’;

64
Q

Implicit/automatic casting

A 
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
65
Q

SELECT

A

SELECT is used to retrieve rows selected from one or more tables.

66
Q

DISTINCT

A

The DISTINCT clause after SELECT eliminates duplicate rows from the result set.

67
Q

Select * -> Best practice

A 
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.
68
Q

Select * -> Pros

A
  1. When you add/remove columns, you don’t have to make changes where you did use SELECT *
  2. It’s shorter to write
  3. You also see the answers, so can SELECT *-usage ever be justified?
69
Q

Select * -> Cons

A
  1. 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
  2. Explicit about what data is used
  3. Specifying columns means you get an error when a column is removed
  4. The query processor has to do some more work - figuring out what columns exist on the table (thanks
    @vinodadhikary)
  5. You can find where a column is used more easily
  6. You get all columns in joins if you use SELECT *
  7. You can’t safely use ordinal referencing (though using ordinal references for columns is bad practice in itself)
  8. In complex queries with TEXT fields, the query may be slowed down by less-optimal temp table processing
70
Q

LIKE Performance notes

A

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.

71
Q

ALIAS

A

SQL aliases are used to temporarily rename a table or a column. They are generally used to improve readability.

72
Q

SELECT with LIKE(_)

A

A _ character in a LIKE clause pattern matches a single character.
Query
SELECT username FROM users WHERE users LIKE ‘admin_’;

73
Q

SELECT with date range

A

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).

74
Q

LIMIT

A

->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).

SQL (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions