-
SQL is most commonly used for
- Created database and table structures
- performing data manipulation and administration
- querying the database to extract useful information
-
Is SQL procedural or nonprocedural?
nonprocedural because the user specifies what must be done, but now how, specific relationships matter but not where the data is stored
-
Commands that define a database, including creating, altering, and dropping tables and stored procedures, and establishing constraints
- Data Definition Language (DDL)
- CREATE TABLE, set PRIMARY KEY
-
Commonds that are used to manipulate data and extract information
- Data Manipulation Language (DML)
- Data retreval:SELECT
- Action queries: UPDATE, INSERT, DELETE
-
Primary commands for Data Definition Language
- CREATE TABLE / CREATE PROCEDURE
- ALTER TABLE
- *Impacts attributes or constraints
- -ADD (new attribute, primary key, NULL, etc)
- -MODIFY (data type, etc.)
- -DROP (column)
- DROP TABLE (DROP PROCEDURE0)
-
ANSI/ISO data types
- INTEGER/SMALLINT
- DECIMAL(precision, scale)
- CHAR(n) = fixed length character data
- VARCHAR(n) - variable length character data
- DATE - julian date format
-
MySQL data types: Primary numerica types
- TINYINT -128 to 127 (or 0 to 255) = 1 byte
- SMALLINT -32768 to 32767 (or 0 to …) = 2 bytes
- MEDIUMINT -8.39 x 106 to 8.39 x 106 = 3 bytes INT -2.15 x 109 to 2.15 x 109 = 4 bytes
- BIGINT -9.22 x 1018 to 9.22 x 1018 = 8 bytes
- DECIMAL(M, D) M total digits / D digits after decimal
-
MySQL data types: Primary date and time types
- DATE 'YYYY-MM-DD' format, range: '1000-01-01' to '9999-12-31'
- DATETIME 'YYYY-MM-DD HH:MM:SS' format, range: '… 00:00:00' to '… 23:59:59'
- Built in functions: NOW(), CURDATE(), DATEDIFF(), INTERVAL, DATE(), TIME(), DAY(), YEAR(), MONTH()
-
MySQL data types: Primary string types
- CHAR(n) - always allocates n bytes of storage
- VARCHAR(n) - only allocates used space (plus 1 byte)
- BLOB - used for large binary strings of data
- TEXT - used for large character strings of data
- ENUM - string object with a value chosen from a list of permitted values that is specified at table creation time
-
In what situations do you have to put quotes around the WHERE clause
- String data
- date time data
|
|