Computer Science · Ch 9 — Structured Query Language (SQL)
Summary
Summary
- A database is a collection of related tables; MySQL is a 'relational' DBMS.
- DDL (Data Definition Language) includes SQL statements such as
CREATE TABLE,ALTER TABLE, andDROP TABLE. - DML (Data Manipulation Language) includes SQL statements such as
INSERT,SELECT,UPDATE, andDELETE. - A table is a collection of rows and columns, where each row is a record and the columns describe the features of that record.
- The
ALTER TABLEstatement is used to change the structure of a table — adding, removing, or changing the data type of column(s). - The
UPDATEstatement is used to modify existing data in a table. - The
WHEREclause in an SQL query is used to enforce condition(s), filtering which rows are affected or returned. - The
DISTINCTclause eliminates repetition, displaying each value only once. - The
BETWEENoperator defines a range of values, inclusive of both boundary values. - The
INoperator selects values that match any value in a given list. NULLvalues are tested usingIS NULLandIS NOT NULL.- The
ORDER BYclause displays SQL query results in ascending or descending order with respect to a specified attribute; ascending is the default. - The
LIKEoperator is used for pattern matching, using two wildcard characters:%(zero or more characters) and_(a single character). - A function performs a particular task and returns a value as a result.
- Single row functions work on a single row of a table and return a single value per row (the numeric, string, and date functions of Section 9.8.1).
- Multiple row (aggregate) functions work on a set of records as a whole and return a single value for the group — examples include
COUNT,MAX,MIN,AVG, andSUM. …