2) Database Management

Chapter 2

automated script

a series of SQL statements prepared in advance and saved in a file or as a database stored procedure, and is executed repeatedly

Data Definition Language (DDL)

SQL sublanguage that defines a database structure

Data Query Language (DQL)

SQL sublanguage that retrieves data

Data Manipulation Language (DML)

SQL sublanguage that inserts, updates, and deletes data

Data Transaction Language (DTL)

SQL sublanguage that manages transactions

Data Control Language (DCL)

SQL sublanguage that specifies user access to data

Literal

SQL language element, explicit value such as a character string or number

keyword

SQL language element, word with special meaning for the processor

identifier

SQL language element, name of a database object such as a column, database, or table

expression

SQL language element, sequence of literals, identifiers, and operations that evaluate to a single value

comment

SQL language element, text that is ignored by the language processor

statement

a complete, executable instruction ending with a semicolon

SQL standard

specifies the official syntax and behavior of the SQL statements, published by the ANSI and the ISO

database system instance

a single executing copy of a database system

CREATE DATABASE DatabaseName

creates a new database

DROP DATABASE DatabaseName

deletes a database, including all tables in the database

SHOW DATABASE

lists all databases in the database system

SHOW TABLES

lists all the tables from the default database

SHOW COLUMNS FROM TableName

lists all columns in the TableName table of the default database

SHOW CREATE TABLE TableName

shows the CREATE TABLE statement for the TableName table of the default database

table

has a name, a fixed sequence of columns, and a varying set of rows

column

has a name and a data type

row

has an unnamed sequence of values

cell

a single column in a single row

CREATE TABLE

creates a new table by specifying the table name, column names, and column data types

DROP TABLE

deletes a table, along with that tables rows, from the database

ALTER TABLE

adds, deletes, or modifies columns on an existing table

ADD

adds a column

CHANGE

modifies a column

DROP

deletes a column

data type

a named set of values from which column values are drawn

INT, SMALLINT

represents positive and negative numbers

FLOAT, DECIMAL

represents numbers with fractional values

CHAR, VARCHAR

represents textual characters

DATE, TIME, DATETIME, TIMESTAMP

represents time, date, or both

BLOB, BINARY, VARBINARY, IMAGE

stores data as the database appears in memory or computer files

POLYGON, POINT, GEOMETRY

stores geometric information, such as lines, polygons, and map coordinates

XML, JSON

contains textual data in a structured format

signed

a number that may be negative

unsigned

a number that cannot be negative

NULL

a special value to represent unknown or inapplicable data

NOT NULL

prevents a column from having a NULL value

INSERT

adds rows to a table

INSERT INTO

names the table and columns where data is to be added

VALUES

specifies the column values to be added

DEFAULT

optional keywords that can follow the column name and data type in a CREATE TABLE statement

UPDATE

modifies existing rows in a table

SET

specifies the new column values

DELETE

deletes existing rows in a table

FROM

keyword followed by the table name whose rows are to be deleted

TRUNCATE

deletes all rows from a table

primary key

a column, or group of columns, used to identify a row

simple primary key

consists of a single column

composite primary key

consists of multiple columns, must be: unique, not NULL, and minimal

auto-increment column

a numeric column that is assigned an automatically incrementing value when a new row is inserted

foreign key

a column, or group of columns, that refer to a primary key in another table

referential integrity

a relational rule that requires foreign key values

RESTRICT

rejects an insert, update, or delete that violates preferential integrity

SET NULL

sets invalid foreign keys to the foreign key default value

CASCADE

propagates primary key changes to foreign keys

constraint

a rule that governs allowable values in a database

column constraint

appears after a column name and data type in a CREATE TABLE statement

table constraint

appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns

UNIQUE

ensures that values in a column, or group of columns, are unique

CHECK

specifies an expression on one or more columns of a table