Anka

Announcement

Welcome to my blog!

标签

788 字

4 分钟

Databases Week10 Lecture

2025-12-01

Databases

SQL

New Model#

Table of content#

Review Week9 Tutorial.
Having Clause.
Model extension.
Normal forms.

Current Structure#

上图为 Logical Model
在其基础上增加数据类型和数据大小即为 Physical Model

Querying Data#

1
select count(*) from customers;
2
select count(distinct full_name) from customers;
3
select count(distinct full_name, cinema) from customers;
4
-- Same full_name, different cinema still be considered as diffrent record

1
select full_name, cinema
2
from customers
3
order by 1 asc;

Querying Data (Distinct)#

1
select distinct full_name, cinema
2
from customers
3
order by full_name asc;

Querying Data (HAVING Clause)#

The MySQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those who’s the condition is TRUE.

1
select full_name,cinema,count(*) from customers
2
group by full_name,cinema
3
having count(*)>1
4
order by 1 asc;

NOTE
where 和 group by 一起时可能无法正常返回，需使用 having

Getting Additional Tables#

ccdetails.CSV (Credit Card Details)

Create a New Table in Brookes#

1
create table cc (
2
Full_Name VARCHAR(50),
3
Ccnumber BIGINT,
4
Cinema VARCHAR(15));

Load The Data#

1
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/ccdetails.csv'
2
INTO TABLE Cc
3
CHARACTER SET latin1
4
FIELDS TERMINATED BY ','
5
ENCLOSED BY '"'
6
LINES TERMINATED BY '\n'

Update An Existing Table#

Add a new column ccnumber to customers table.

1
alter table brookes.customers add column ccnumber bigint;

Fill the new column with the desirable values.

1
update customers as a set a.ccnumber =
2
(select b.ccnumber from cc as b
3
where
4
a.full_name = b.full_name and a.cinema = b.cinema)

TIP
update 操作非常耗费时间，可能会导致MySQLWorkbench连接超时，需要更改如下设置：

Alternative Method#

Erase the old values

1
Update customers set ccnumber = null;

Fill the new column with the desirable values.

1
update customers as a, cc as b set a.ccnumber = b.ccnumber
2
where
3
a.full_name = b.full_name and a.cinema = b.cinema;

这种方法理论上比上面那种快，因为它没有 select

Output#

Find the (new) problems#

1
select * from customers
2
where ccnumber is null;

Question#

How to get the full amount of rows and avoid getting the Null value?

Solution (ask for more data)#

Create a new table customers_tmp (just like customers).

1
create table customers_tmp as select * from customers;

(Copies the table data as well).

Revised code to copy the structure only:

1
create table customers_tmp as select * from customers where 1=2;

OR

1
create table customers_tmp as select * from customers limit 0;

Solution (load the new data)#

Load in the new data file customer_cc_update.CSV to customers_tmp (BEWARE no headings).

1
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customer_cc_update.csv'
2
INTO TABLE brookes.customers_tmp
3
CHARACTER SET latin1
4
FIELDS TERMINATED BY ','
5
ENCLOSED BY '"'
6
LINES TERMINATED BY '\n'

Showing All Data#

1
select * from customers where ccnumber is null union
2
select * from customers_tmp order by full_name, cinema

Replace the rows in the table#

Delete the null values from brookes.customers

1
delete from customers where ccnumber is null;

Replacing the data in brookes.customers

1
insert into customers (select * from customers_tmp);

Check!#

1
select count(distinct full_name, cinema) from customers;
2
select count(distinct ccnumber) from customers;

Indexing#

Indexes are used to retrieve data from the database more fast.

Index Example#

1
select cinema,count(*) from customers group by cinema;

Creating an index:

1
CREATE INDEX index_name ON table_name (column_name1, column_name2,..);
2
create index cust_cinema on customers (cinema);

Run the selection statement again.
- Have you noticed the difference?

Managing indexes#

Showing index for a table:

1
show index from table_name;
2
show index from customers;

Deleting an index

1
DROP index index_name on table_name;
2
drop index cust_cinema on customers;

Exporting Data#

1
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv'
2
INTO TABLE table_name
3
CHARACTER SET latin1
4
FIELDS TERMINATED BY ','
5
ENCLOSED BY '"'
6
LINES TERMINATED BY '\n’
7
IGNORE n LINES;

1
SELECT *INTO OUTFILE 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/filename.csv’
2
FIELDS TERMINATED BY ',’
3
OPTIONALLY ENCLOSED BY '“’
4
LINES TERMINATED BY '\n’
5
FROM tablename;

Exporting Data (example)#

1
SELECT * INTO OUTFILE 'c:/ProgramData/MySQL/MySQL Server 8.0/Uploads/DB.csv’
2
FIELDS TERMINATED BY ',’
3
OPTIONALLY ENCLOSED BY '“’
4
LINES TERMINATED BY '\n’
5
FROM customers;

Normal Forms#

Denormalized
- Impractical.
- Tough to insert, update and delete.
1NF
- Every column is unique and not empty.
- No duplicated rows or column.
- No multi-value fields (atomic values).
2NF
- Same as 1NF.
- No data redundancy.
- All related items are stored together.

Denormalized Data Table#

Name	Address	Subject	Age
Mark	Crewe	Physics	16
Caroline	Bolton	Biology, Maths	16
John	Norwich	Maths	17
Bob	Huddersfield	Physics, Biology	15

Insert, update, and delete problem

First Normal Form (1NF)#

StdID	Name	Address	Age	Subject
123	Mark	Crewe	16	Physics
124	Caroline	Bolton	16	Biology
132	John	Norwich	17	Maths
165	Bob	Huddersfield	15	Physics
124	Caroline	Bolton	16	Maths
165	Bob	Huddersfield	15	Biology

No duplicated data or multi values per row and field

Second Normal Form (2NF)#

Students

StdID	Name	Address	Age
123	Mark	Crewe	16
124	Caroline	Bolton	16
132	John	Norwich	17
165	Bob	Huddersfield	15

Subjects

SID	Subject
1	Physics
2	Biology
3	Maths

Assign

StdID	SID
123	1
124	2
132	3
165	1
124	3
165	2

Related attributes separated to appropriate tables

Databases Week10 Lecture

https://mizuki.anka2.top/posts/l5-db-week10-lecture/

作者

🐦‍🔥不死鸟Anka

发布于

2025-12-01

许可协议

MIT

部分信息可能已经过时

Software Development with C and Cpp Week10 Lecture

Innovative Product Development Week10 Lecture

Hi~ I'm Anka!

New Model#

Table of content#

Current Structure#

Querying Data#

Querying Data (Distinct)#

Querying Data (HAVING Clause)#

Getting Additional Tables#

Create a New Table in Brookes#

Load The Data#

Update An Existing Table#

Alternative Method#

Output#

Find the (new) problems#

Question#

Solution (ask for more data)#

Solution (load the new data)#

Showing All Data#

Replace the rows in the table#

Check!#

Indexing#

Index Example#

Managing indexes#

Exporting Data#

Exporting Data (example)#

Normal Forms#

Denormalized Data Table#

First Normal Form (1NF)#

Second Normal Form (2NF)#

Students

Subjects

Assign