CPT103-Introduction to Databases小豪的笔记

这里通过 XAMPP来用 PhpMyAdmin 创建数据库

1. Table and Data

1.1 Creating a Database

• First, we need to create a schema

CREATE SCHEMA name;

CREATE DATABASE name;

• If you want to create tables in this schema, you need to tell MySQL to “enter” into this schema, type:

Use name;

1.2 Creating Tables

• Syntax

CREATE TABLE [IF NOT EXISTS] name (
    col-name datatype [col-options],
    :
    col-name datatype [col-options],
    [constraint-1],
    :
    [constraint-n]
);
-- Contents between [] are optional

###1.3 Data Types

• For floating-point and fixed-point data types, M is the total number of digits that can be stored.

Data Type	属性
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]	2 Bytes, signed range: -32768 to 32767
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]	3 Bytes
INT[(M)] [UNSIGNED] [ZEROFILL]	4 Bytes
BIGINT[(M)] [UNSIGNED] [ZEROFILL]	8 Bytes
DECIMAL / DEC / NUMERIC / FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]	M大小 1~65
FLOAT§ [UNSIGNED] [ZEROFILL]	automatically choose single precision or double precision based on the value of p
CHAR[(M)]	1 Byte, M is the length, 0 ~ 255
VARCHAR(M)	A variable-length string, M is 0 to 65,535

• Things between [] are optional

• Date and Time

数据类型	描述
DATE	YYYY-MM-DD
DATETIME[(fsp)]	‘YYYY-MM-DD hh:mm:ss[.fraction]’
TIMESTAMP	UTC time

###1.4 Column Options

col-name datatype [col-options]

• NOT NULL
  • values of this column cannot be null.
• UNIQUE
  • each value must be unique (candidate key on a single attribute)
• DEFAULT value
• AUTO_INCREMENT = baseValue
  • Must be applied to a key column (primary key, unique key)
  • a value (usually max(col) + 1) is automatically inserted when data is added.
  • You can also manually provide values to override this behaviour.
  • ALTER TABLE Persons AUTO_INCREMENT = 100;

1.5 Tuple Manipulation

1.5.1 INSERT

INSERT INTO tablename (col1, col2, …)
    VALUES (val1, val2, …),
    	:
    	(val1,val2,val3);

• If you are adding a value to every column, you don’t have to list them

INSERT INTO tablename VALUES (val1, val2, …);

1.5.2 UPDATE

UPDATE table-name
    SET col1 = val1 [,col2 = val2…]
    [WHERE condition]

1.5.3 DELETE

DELETE FROM
    table-name
    [WHERE condition]

• • If no condition is given then ALL rows are deleted.

2. Table Constraints

CREATE TABLE name (
    col-name datatype [col-options],
    :
    col-name datatype [col-options],
    [constraint-1],
    :
    [constraint-n]
);

2.1 Syntax of Constraints

CONSTRAINT name TYPE details;

• Constraint name is created so that later this constraint can be removed by referring to its name.
  • If you don’t provide a name, one will be generated.
• MySQL provides following constraint types
  • PRIMARY KEY
  • UNIQUE
  • FOREIGN KEY
  • INDEX

2.2 Domain Constraints

• A domain constraint can be defined along with the column or separately:

CREATE TABLE People (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sex CHAR NOT NULL CHECK (sex IN ('M','F')),
    CONSTRAINT id_positive CHECK (id > 0)
);

2.2.1 UNIQUE

CONSTRAINT name UNIQUE (col1, col2, …)

2.2.2 Primary Key

CONSTRAINT name PRIMARY KEY (col1, col2 …)

2.2.3 Foreign Key

CONSTRAINT name
    FOREIGN KEY
        (col1, col2, ...)
    REFERENCES
        table-name
    (col1, col2, ...)
    [ON UPDATE ref_opt
    ON DELETE ref_opt]

-- ref_opt: RESTRICT | CASCADE | SET NULL | SET DEFAULT

• The Binary Keyword
• The BINARY keyword instructs MySQL to compare the characters in the string using their underlying ASCII values rather than just their letters.

CREATE TABLE `branch` (
    `branchNo` char(4) BINARY NOT NULL,
    PRIMARY KEY (`branchNo`),
...);

2.3 Reference Options

• RESTRICT – stop the user from doing it

  • The default option

• CASCADE – let the changes flow on

• SET NULL – make referencing values null

• SET DEFAULT – make referencing values the default for their column

• These options can be applied to one or both kinds of the table updates:

  • ON DELETE
  • ON UPDATE

CONSTRAINT `FK_staff_branchNo`
    FOREIGN KEY (`branchNo`)
    REFERENCES `branch` (`branchNo`)
        ON DELETE SET NULL
        ON UPDATE CASCADE

3. Altering Table

3.1 Add column

ALTER TABLE table_name
	ADD column_name datatype [options like UNIQUE …];

3.2 Drop colum

ALTER TABLE table_name DROP COLUMN column_name;

3.3 Modify column name and definition

ALTER TABLE table_name
    CHANGE COLUMN
    col_name new_col_name datatype [col_options];

3.4 Modify column definition only

ALTER TABLE table_name
    MODIFY COLUMN
    column_name datatype [col_options]; 

###3.5 Adding Constraints

ALTER TABLE table-name
    ADD CONSTRAINT name definition;

3.6 Removing Constraints

ALTER TABLE table-name
DROP INDEX name | DROP FOREIGN KEY name | DROP PRIMARY KEY
-- INDEX - Can be used to drop unique keys

3.7 Deleting Tables

DROP TABLE [IF EXISTS] table-name1, table-name2…;

4. SQL Select Ⅰ

SELECT [DISTINCT | ALL]
    column-list FROM table-names
    [WHERE condition]
    [ORDER BY column-list]
    [GROUP BY column-list]
    [HAVING condition]

4.1 SELECT

SELECT col1[,col2…] FROM table-name;

4.2 DISTINCT and ALL

• Using DISTINCT after the SELECT keyword removes duplicates

4.3 Expressions in SELECT

select a, b, a+b as sum
	from dup_test;

4.4 Where

SELECT * FROM table-name
	WHERE predicate;

• Asterisk (*) means getting all columns of that table.

4.5 Word Search

4.5.1 LIKE

• We can use the LIKE keyword to perform string comparisons in queries

SELECT * FROM books
	WHERE bookName LIKE '%crypt%'
;

• Like is not the same as ‘=’ because it allows wildcard characters

• It is NOT normally case sensitive

• The % character can represent any number of characters, including none

• The _ character represents exactly one character

4.5.2 Dealing with Date and Time

• like numbers

SELECT * FROM table-name
	WHERE date-of-event < '2012-01-01'; 

• like a string

SELECT * FROM table-name
	WHERE date-of-event LIKE '2014-11-%';

4.6 Select and Cartesian Product

SELECT * FROM Table1, Table2;

• If the tables have columns with the same name

TableName.ColumnName

4.7 Aliases

• Column alias

SELECT column [AS] new-col-name

• Table alias

SELECT * FROM table [AS] new-table-name

Note: You cannot use a column alias in a WHERE clause

• Aliases and ‘Self-Joins’

SELECT A.Name FROM
    Employee A,
    Employee B
WHERE
	A.Dept = B.Dept
AND
	B.Name = 'Andy';
</