MySQL Basics Flashcards
Database
A repository designed for organizing and accessing information
Database Management System (DBMS)
Software designed to store, manipulate, and retrieve data in a database
Database Manipulation Language (DML)
Used to store and retrieve data from the database
Database Control Language (DCL)
Used to restrict access to data by certain users
Relational Model
Database contains a set of tables
Character String types
char(n) - always of num size
varchar(n) - no padding
Numeric types
- integer, smallint, bigint
- numeric(p,s) for signed fixed-point numbers with p digits and s decimal places
- decimal(p,s)
- real
- double precision
- float(p)
Temporal types
Datetimes and Intervals
Datetime subtypes
- Date YYYY-MM-DD
- Time HH:MM:SS
- Timestamp YYYY-MM-DD HH:MM:SS
Interval subtypes
Year-Month Interval
- ‘3-1’ Year to Month
Day-Time Interval
- ‘5 10:30:22.5’ Day to Second
Binary types
- Bit(L)
- BitVarying(L)
- BLOB(L)
Boolean Values
True, false, unknown
Primary Key (PK)
Values that uniquely identify a row
- The value of a PK for every row is unique
- No PK can be NULL
Foreign Key (FK)
Column(s) that refers to some other column(s) in some table
Types of table relations
Many-to-many, one-to-one, one-to-many
Schema
Collection of tables and other data description objects
Select
Gets information from the table
Select *
Gets every row from a table that meets the condition
Multi-line comments
/* */
Single-line comments
–
What does Select generate
A result table
Where clause
Used to specify a condition each row must satisfy to be in the query result
Operators
Traditional: =, <, >, <=, >=
- [not] Between
- Is [not] NULL
- [not] Like
- [not] In
- Not
Like Regex
% - Matches any substring with 0+ characters
_ - Matches any single character
How to change Like escape character
Like ‘%#_%’ Escape ‘#’ allows the use of _ as a char instead of a wildcard
Operator precedence
Comparison operators
Not
And
Or
What kind of range does Between specify
Inclusive range
How to rename and attribute in a Select
Select [attribute] as [name]
Distinct
Eliminates duplicate rows
Derived Attributes
Abs(n)
Ceil(n)
Exp(n)
Floor(n)
Ln(n)
Mod(n,d)
Power(b,e)
Sqrt(n)
String functions
- Substring( [source] from [start] {for length}
- Trim({{Leading | Trailing | Both} [to_trim] from} [source])
- Lower([source]
- Upper([source])
- Position([substr] in [source])
Order by
Order by <expression> [ASC | DESC] [NULLS FIRST | NULLS LAST]</expression>
Casting
Cast([source] as [result_type])
Conditional Results
Case [target expression]
when [candidate] then [result]
else [result]
END
