CS计算机代考程序代写 SQL database Structured Query Language: Part 1

Structured Query Language: Part 1

SQL 1: Defining & Modifying Tables
Jianjun Chen

Contents
SQL stands for “Structured Query Language”.
SQL consists of two parts:
Data definition language (DDL).
Data manipulation language (DML).
Today, we will cover the DDL and a small portion of DML:
Create tables and their constraints.
Changing columns/constraints of tables.
Adding/Updating/Removing tuples.

SQL Format in the Slides
SQL statements will be written in
BOLD COURIER FONT
SQL keywords are not case-sensitive, but we’ll write SQL keywords in uppercase for clarity.
However, table names, column names etc. are case sensitive. For example:
SELECT (sName) FROM Student;

Database Containment Hierarchy
A computer may have one or more clusters.
A cluster is a database server.
= a computer can run multiple database servers.

Each cluster contains one or more catalogs.
Catalog is just another name for “database”.

Each catalog consists of set of schemas.
Schema is a namespace of tables, and security boundary.

A schema consists of tables, views, domains, assertions, collations, translations, and character sets. All have same owner.

More about Catalog and Schema
A very clear discussion is available here.
But It can be confusing at the current stage because you haven’t learned so much about database.

Feel free to come back later.

Content of the link (In case you cannot access stackoverflow)

Answer 1:

From the relational point of view :
“The catalog is the place where–among other things–all of the various schemas (external, conceptual, internal) and all of the corresponding mappings (external/conceptual, conceptual/internal) are kept.
In other words, the catalog contains detailed information (sometimes called descriptor information or metadata) regarding the various objects that are of interest to the system itself.
For example, the optimizer uses catalog information about indexes and other physical storage structures, as well as much other information, to help it decide how to implement user requests. Likewise, the security subsystem uses catalog information about users and security constraints to grant or deny such requests in the first place. “
—–An Introduction to Database Systems, 7th ed., C.J. Date, p 69-70.

From the SQL standard point of view :
“Catalogs are named collections of schemas in an SQL-environment. An SQL-environment contains zero or more catalogs. A catalog contains one or more schemas, but always contains a schema named INFORMATION_SCHEMA that contains the views and domains of the Information Schema.”
—– Database Language SQL, (Proposed revised text of DIS 9075), p 45

From the SQL point of view :
“A catalog is often synonymous with database. In most SQL dbms, if you query the information_schema views, you’ll find that values in the “table_catalog” column map to the name of a database.
If you find your platform using catalog in a broader way than any of these three definitions, it might be referring to something broader than a database–a database cluster, a server, or a server cluster. But I kind of doubt that, since you’d have found that easily in your platform’s documentation.”

Answer 2: Probably from (http://en.wikipedia.org/wiki/PostgreSQL)

Cluster = A Postgres Installation

When you install Postgres on a machine, that installation is called a cluster. ‘Cluster’ here is not meant in the hardware sense of multiple computers working together. In Postgres, cluster refers to the fact that you can have multiple unrelated databases all up and running using the same Postgres server engine.

The word cluster is also defined by the SQL Standard in the same way as in Postgres. Closely following the SQL Standard is a primary goal of the Postgres project.

The SQL-92 (https://en.wikipedia.org/wiki/SQL-92) specification says:

“A cluster is an implementation-defined collection of catalogs.”

and

“Exactly one cluster is associated with an SQL-session”

That’s an obtuse way of saying a cluster is a database server (each catalog is a database).
Cluster > Catalog > Schema > Table > Columns & Rows

So in both Postgres and the SQL Standard we have this containment hierarchy:

A computer may have one cluster or multiple.
A database server is a cluster.
A cluster has catalogs. ( Catalog = Database )
Catalogs have schemas. (Schema = namespace of tables, and security boundary)
Schemas have tables.
Tables have rows.
Rows have values, defined by columns.
Those values are the business data your apps and users care about such as person’s name, invoice due date, product price, gamer’s high score. The column defines the data type of the values (text, date, number, and so on).

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;

After that, if you create tables, they will be created in this schema.
Same effect

XAMPP: must turn on apache as well to use admin functions (phpmyadmin)

http://localhost/phpmyadmin/
6

SQL: Table Definition
Syntax of “CREATE TABLE”
Data types of SQL

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

[xxx]: something optional.

Common Data Types (MySQL)
DataType keyword
Boolean BOOLEAN
Character CHAR(size) VARCHAR(size_limit) TEXT TINYTEXT
Number INTEGER(size) FLOAT(size, d) DOUBLE(size,d)
Date DATE DATETIME TIME

FLOAT(size,d) d

12345666666.7777

size

CHAR has fixed string length
VARCHAR has variable string length
Date: 1-oct-2015
Time: 21:05:02
col-name datatype [col-options]

Strings In SQL
Strings in SQL are surrounded by single quotes:
‘I AM A STRING’
Single quotes within a string are doubled or escaped using \
‘I”M A STRING’
‘I\’M A STRING’
” – is an empty string
In MySQL, double quotes also work (Not a standard)

Column Options

NOT NULL:
values of this column cannot be null.
UNIQUE:
each value must be unique (candidate key on a single attribute)
DEFAULT value:
Default value for this column if not specified by the user.
Does not work in MS Access.
col-name datatype [col-options]
Example: Age INT DEFAULT 12

Column Options
AUTO_INCREMENT = baseValue:

a value (usually max(col) + 1) is automatically inserted when data is added.
You can also manually provide values to override this behaviour:

Read the official manual here.
CREATE TABLE Persons (
Id INT AUTO_INCREMENT,
…
ALTER TABLE Persons AUTO_INCREMENT = 100;
You cannot use
“AUTO_INCREMENT = 2”
inside CREATE TABLE

Create Tables: Full Example
CREATE TABLE Persons (
ID INT UNIQUE NOT NULL,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Age INT,
City VARCHAR(255)
);

Try It Yourself
Convert the following relation into SQL query.
You need to choose appropriate data types as well.
No need to add tuples right now.

Constraints
Domain, Primary key, unique key, foreign key

Domain Constraints
You can limit the possible values of an attribute by adding a domain constraint.
A domain constraint can be defined along with the column or separately:

Unfortunately, MySQL does not support domain constraints, these constraints will be ignored.

CREATE TABLE People (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
sex CHAR NOT NULL CHECK (sex IN (‘M’,’F’))
);

https://stackoverflow.com/questions/2617941/what-is-the-difference-between-check-and-foreign-key

A foreign key constraint is more powerful than a CHECK constraint.
A foreign key constraint means that the column (in the current table) can only have values that already exist in the column of the foreign table (which can include the be the same table, often done for hierarchical data). This means that as the list of values changes – gets bigger or smaller – there’s no need to update the constraint.
A check constraint can not reference any columns outside of the current table, and can not contain a subquery. Often, the values are hard coded like BETWEEN 100 and 999 or IN (1, 2, 3). This means that as things change, you’ll have to update the CHECK constraint every time. Also, a foreign key relationship is visible on an Entity Relationship Diagram (ERD), while a CHECK constraint will never be. The benefit is that someone can read the ERD and construct a query from it without using numerous DESC table commands to know what columns are where and what relates to what to construct proper joins.
Best practice is to use foreign keys (and supporting tables) first. Use CHECK constraints as a backup for situations where you can’t use a foreign key, not as the primary solution to validate data.

Constraints
General Syntax:
CONSTRAINT name TYPE details;
If you don’t provide a name, one will be generated

MySQL provides following constraint types
PRIMARY KEY
UNIQUE
FOREIGN KEY
INDEX

UNIQUE
Usage:
CONSTRAINT name UNIQUE (col1, col2, …)
Same effect as the one specified with column options but can be applied to multiple columns and make them one candidate key.
The following candidate keys are different
One candidate key (a, b, c):
Tuples (1, 2, 3) and (1, 2, 2) are allowed
Separate candidate keys (a) (b) (c):
Tuples (1, 2, 3) and (1, 2, 2) are NOT allowed

Primary Key
Usage:
CONSTRAINT name PRIMARY KEY (col1, col2, …)

PRIMARY KEY also automatically adds UNIQUE and NOT NULL to the relevant column definition

Extended reading:
Difference between Primary Key and Unique.
More underlying mechanism.

https://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key

What is the difference between Clustered and Non-Clustered Indexes in SQL Server?

Primary Key: Example
CREATE TABLE Branch (
branchNo CHAR(4),
street VARCHAR(100),
city VARCHAR(25),
postcode VARCHAR(7),
CONSTRAINT branchPK
PRIMARY KEY (branchNo)
)

Foreign Key
To apply a foreign key, you need to provide
The columns which make up the foreign key
The referenced table
The columns which are referenced by the foreign key
You can optionally provide reference options
Usage:

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

Foreign Key
Important:
When a foreign key is applied, the value must either reference the other table or set to NULL.
In the following tables, the Staff.branchNo must either use a value from the branch table or set to null.
The referenced column must be a candidate key (or primary key)

Try it Yourself:
Write a SQL query to create the table for Staff. (optional)
Create a foreign key for Staff.branchNo that references Branch.branchNo.

CONSTRAINT name
FOREIGN KEY
(col1, col2, …)
REFERENCES
table-name
(col1, col2, …)

CREATE TABLE Branch (
branchNo CHAR(4),
street VARCHAR(100),
city VARCHAR(25),
postcode VARCHAR(7),
CONSTRAINT branchPK PRIMARY KEY (branchNo)
);

Foreign Key
The referenced column must be a candidate key (or primary key)

CREATE TABLE staff (
staffNo VARCHAR(8),
fName VARCHAR(20),
lName VARCHAR(20),
position VARCHAR(20),
sex VARCHAR(10),
DOB DATE,
salary INTEGER,
branchNo CHAR(4),
CONSTRAINT staffPK PRIMARY KEY (staffNo),
CONSTRAINT staffFK FOREIGN KEY (branchNo)
REFERENCES Branch (branchNo)
);
CREATE TABLE Branch (
branchNo CHAR(4),
street VARCHAR(100),
city VARCHAR(25),
postcode VARCHAR(7),
CONSTRAINT branchPK PRIMARY KEY (branchNo)
);

Foreign Keys and Tuple Updates
We know that adding non-existing branchNo to Staff will be rejected by DBMS.

But what happens when we change/delete existing branchNo in Branch that are being referenced by Staff?

What strategies can you think of if you were the designer?

Reference Options
There are several options when this occurs:
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

On Update/Delete Set NULL
Assume we delete in Branch table.
All ‘B005’ in the Staff table will be set to null.
If we change ‘B005’ to ‘B006’.
All ‘B005’ will be set to null…Good decision?

NULL

On Update/Delete Cascade
Assume we change ‘B005’ to ‘B006’ in Branch table
All ‘B005’ in Staff table will be changed to ‘B006’.
Seems reasonable

Assume we delete ‘B005’ in Branch table.
All tuples with ‘B005’ in Staff table will also be deleted!
Good decision?

On Update/Delete Set Default
Assume we delete or change ‘B005’ in Branch table.
All ‘B005’ in Staff table will be changed to the default value of Staff.branchNo.

This feature is not available in MySQL.

Final FK Definition in Staff Table
CONSTRAINT staffFK
FOREIGN KEY (branchNo)
REFERENCES Branch (branchNo)
ON DELETE SET NULL
ON UPDATE CASCADE

Alternative Ways of Writing
Primary keys, unique keys and foreign keys can also be directly defined along with attributes.
But this form has some limitations. You should find them out.
CREATE TABLE majors (
majorID VARCHAR(5) PRIMARY KEY
);

CREATE TABLE Students (
id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
majorID VARCHAR(5) REFERENCES majors(majorID),
sex CHAR NOT NULL CHECK (sex IN (‘M’,’F’))
);

Deleting Tables
You can delete tables with the DROP keyword
DROP TABLE [IF EXISTS] table-name1, table-name2…;

All tuples will be deleted as well.
Undoing is sometimes impossible.
Foreign Key constraints will prevent DROPS under the default RESTRICT option
To overcome this, either remove the constraint or drop the tables in the correct order (referencing table first)

Try It!
Try deleting our previously created tables (Branch, Staff)

Try both:
DROP TABLE IF EXISTS Branch, Staff;
DROP TABLE IF EXISTS Staff, Branch;

Altering Tables
Keyword ALTER

Change Tables: ALTER
ALTER is used to add, delete, or modify columns in an existing table.

Add column:
ALTER TABLE table_name ADD column_name datatype [options like UNIQUE …];
Drop column:
ALTER TABLE table_name DROP COLUMN column_name;

ALTER: Modify Columns
Modify column name and definition:
ALTER TABLE table_name
CHANGE COLUMN
col_name new_col_name datatype [col_options];

Modify column definition only:
ALTER TABLE table_name
MODIFY COLUMN
column_name datatype [col_options];

ALTER: Add Constraints
To add a constraint:
ALTER TABLE table-name
ADD CONSTRAINT name definition;

For instance:
ALTER TABLE branch
ADD CONSTRAINT ck_branch UNIQUE (street);
Changes street column of Branch to a candidate key.

ALTER: Remove Constraints
To remove a constraint:
ALTER TABLE table-name
DROP INDEX name | DROP FOREIGN KEY name | DROP PRIMARY KEY
Separator | means OR

For example:
ALTER TABLE staff DROP PRIMARY KEY;
Removes the primary key of staff

Try It Yourself
Change the data type of Branch.postcode to VARCHAR(12).

And then set (city, postcode) as a candidate key.

And then remove the previously added candidate key.
Hint: use drop index

ALTER TABLE branch
MODIFY COLUMN
postcode VARCHAR(12);

ALTER TABLE branch
ADD CONSTRAINT ck_branch UNIQUE (city, postcode);

ALTER TABLE branch DROP INDEX ck_branch;
40

SQL: Tuple Operations

INSERT
Inserts rows into the database with the specified values:
INSERT INTO table-name (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 table-name VALUES (val1, val2, …);

But then the ordering of values must match the ordering of attributes in the table.

INSERT: Example
Employee
ID Name Salary

INSERT INTO Employee
(ID, Name, Salary)
VALUES (2, ‘Mary’, 26000);

INSERT INTO Employee
(Name, ID)
VALUES (‘Mary’, 2);

INSERT INTO Employee
VALUES (2, ‘Mary’, 26000),
(3, ‘Max’, 233333);

INSERT: Example
INSERT INTO Employee
(ID, Name, Salary)
VALUES (2, ‘Mary’, 26000);

INSERT INTO Employee
(Name, ID)
VALUES (‘Mary’, 2);

INSERT INTO Employee
VALUES (2, ‘Mary’, 26000),
(3, ‘Max’, 233333);

Employee
ID Name Salary
2 Mary 26000
2 Mary
2 Mary 26000
3 Max 233333

Exercise:
Add ‘B005’ and ‘B002’ to Branch table
List columns when inserting ‘B005’.
Skip columns when inserting ‘B002’.
Can you add a tuple WITHOUT a branchNo?

INSERT INTO branch (branchNo, city, postcode, street)
VALUES (‘B005′, ’22 Deer Rd’, ‘London’, ‘SW1 4EH’);

INSERT INTO branch VALUES (‘B002′, ’65 Clover Dr’, ‘London’, ‘NW10 6EU’);
45

UPDATE
Changes values in specified rows based on WHERE conditions
UPDATE table-name
SET col1 = val1 [,col2 = val2…]
[WHERE condition]

All rows where the condition is true have the columns set to the given values
If no condition is given all rows are changed.
Values are constants or can be computed from columns

UPDATE: Example
Employee
ID Name Salary
1 John 25000
2 Mary 26000
3 Mark 18000
4 Anne 22000

UPDATE Employee
SET
Salary = 15000, Name = ‘Jane’
WHERE ID = 4;
UPDATE Employee
SET Salary =
Salary * 1.05;
Employee
ID Name Salary
1 John 25000
2 Mary 26000
3 Mark 18000
4 Jane 15000

Employee
ID Name Salary
1 John 26250
2 Mary 27300
3 Mark 18900
4 Anne 23100

UPDATE: Exercise
Change the salary of all Staffs whose salary is less than 15000 to 16000.

UPDATE staff
SET Salary = 16000
WHERE staff.salary < 15000; 48 DELETE Removes all rows, or those which satisfy a condition DELETE FROM table-name [WHERE condition] If no condition is given then ALL rows are deleted. DELETE: Example Employee ID Name Salary 1 John 25000 2 Mary 26000 3 Mark 18000 4 Anne 22000 DELETE FROM Employee WHERE Salary > 20000;
DELETE FROM Employee;
Employee
ID Name Salary
3 Mark 18000

Employee
ID Name Salary

Questions?

Additional Exploration
Check the following reference manuals:
Create table:
https://dev.mysql.com/doc/refman/5.7/en/create-table.html
Data types:
https://dev.mysql.com/doc/refman/5.7/en/data-types.html

Some other issues
Can you create a foreign key on an attribute of INT type that references a CHAR type?
Can you create multiple primary keys/unique keys on a same relation?

Please experiment these on your computer.

/docProps/thumbnail.jpeg

Related Posts