Chapter 1 of MySQL, creating S,C,T,SC,TC tables

Posted by fpbaum on Fri, 18 Feb 2022 04:27:15 +0100

MySQL computer practice Chapter 1

Computer purpose

Familiar with Mysql software installation;

2. Familiar with the code creation of database and data table;

Computer content

Write out five relational patterns in Teach database and mark the primary key and foreign key

S (Student Relations)

Sno Student number	Sn full name	Sex Gender	Age Age	Dept Department
S1	Zhao Yi	female	17	computer
S2	Chaner	male	18	information
S3	Sun Shan	female	20	information
S4	Li Si	male	21	automation
S5	Zhou Wu	male	19	computer
S6	Wu Li	female	20	automation

C (curriculum relationship)

Cno Course number	Cn Course name	Ct class hour
C1	Programming	60
C2	Microcomputer principle	80
C3	digital logic	60
C4	data structure	80
C5	Database principle	60
C6	Compilation principle	54
C7	operating system	60

T (teacher relations)

Tno Teacher number	Tn full name	Sex Gender	Age Age	Prof title	Sal wages	Comm Gangjin	Dept Department
T1	Li Li	male	47	professor	1500	3000	computer
T2	Wang Ping	female	28	lecturer	800	1200	information
T3	Liu Wei	male	30	lecturer	900	1200	computer
T4	Xue Zhang	female	51	professor	1600	3000	automation
T5	Zhang Lan	female	39	associate professor	1300	2000	information

SC (course selection relationship)

Sno Student number	Cno Course number	Score achievement
S1	C1	90
S1	C2	85
S2	C4	70
S2	C5	57
S2	C6	80
S2	C7
S3	C1	75
S3	C2	70
S3	C4	85
S4	C1	93
S4	C2	85
S4	C3	83
S5	C2	89

TC (teaching relationship)

Tno Teacher number	Cno Course number
T1	C1
T1	C4
T2	C5
T3	C1
T3	C5
T4	C2
T4	C3
T5	C5
T5	C7

Table S:

Listing	meaning	data type	Can it be blank	Main foreign key
SNo	Student number	char(10)	N	PK
SN	Student name	varchar(20)	N
Sex	Gender	char(2)	Y
Age	Age	int	Y
Dept	Department	varchar(20)	Y

Table C:

Listing	meaning	data type	Can it be blank	Main foreign key
CNo	Course code	char(10)	N	PK
CN	Course name	varchar(10)	N
CT	class hour	int	Y

Table T:

Listing	meaning	data type	Can it be blank	Main foreign key
TNo	Teacher number	char(10)	N	PK
TN	Teacher name	varchar(20)	N
Sex	Gender	char(2)	Y
Age	Age	int	Y
Prof	title	char(10)	Y
Sal	wages	int	Y
Comm	Post allowance	int	Y
Dept	Department	varchar(20)	Y

SC table:

Listing	meaning	data type	Can it be blank	Main foreign key
SNo	Student number	char(10)	N	PK，FK
CNo	Course code	char(10)	N	PK，FK
Score	achievement	decimal(5，2)	Y

TC table:

Listing	meaning	data type	Can it be blank	Main foreign key
TNo	Teacher number	char(10)	N	Pk，FK
CNo	Course code	char(10)	N	PK，FK

1. Create and use database

CREATE DATABASE IF NOT EXISTS Teach DEFAULT CHARACTER SET utf8;
USE Teach;

2. Create S table

CREATE TABLE IF NOT EXISTS S(
 Sno CHAR(10) PRIMARY KEY,
 Sn VARCHAR(20) NOT NULL,
 Sex CHAR(2),
 Age int,
 Dept VARCHAR(20)
)DEFAULT CHARSET=utf8;

3. Create table C

CREATE TABLE IF NOT EXISTS C(
 CNo CHAR(10) PRIMARY KEY,
 CN  VARCHAR(10) NOT NULL,
 CT INT
 )DEFAULT CHARSET=utf8;

4. Create T table

CREATE TABLE IF NOT EXISTS T(
  TNo char(10) PRIMARY KEY,
  TN VARCHAR(20) NOT NULL,
  Sex char(2),
  Age INT,
  Prof char(10),
  Sal int,
  Comm int,
  Dept VARCHAR(20)
 )DEFAULT CHARSET=utf8;

5. Filling data in tables s, C and t

INSERT INTO S(Sno,Sn,Sex,Age,Dept)
VALUES
('S1','Zhao Yi','female',17,'computer'),
('S2','Chaner','male',18,'information'),
('S3','Sun Shan','female',20,'information'),
('S4','Li Si','male',21,'automation'),
('S5','Zhou Wu','male',19,'computer'),
('S6','Wu Li','female',20,'automation');

INSERT INTO C(Cno,Cn,Ct)
VALUES
('C1','Programming',60),
('C2','Microcomputer principle',80),
('C3','digital logic ',60),
('C4','data structure',80),
('C5','Database principle',60),
('C6','Compilation principle',54),
('C7','operating system',60);

INSERT INTO T(Tno,Tn,Sex,Age,Prof,Sal,Comm,Dept)
VALUES
('T1','Li Li','male',47,'professor',1500,3000,'computer'),
('T2','Wang Ping','female',28,'lecturer',800,1200,'information'),
('T3','Liu Wei','male',30,'lecturer',900,1200,'computer'),
('T4','Xue Zhang','female',51,'professor',1600,3000,'automation'),
('T5','Zhang Lan','female',39,'associate professor',1300,2000,'information');

6. Create SC table

CREATE TABLE IF NOT EXISTS SC(
 Sno CHAR(10),
 CNo CHAR(10),
 Score decimal(5,2),
 PRIMARY KEY(Sno,CNo)
 )DEFAULT CHARSET=utf8;

7.SC table filling data

INSERT INTO SC(Sno,CNo,Score)
VALUES
('S1','C1',90),
('S1','C2',85),
('S2','C4',70),
('S2','C5',57),
('S2','C6',80),
('S2','C7',NULL),
('S3','C1',75),
('S3','C2',70),
('S3','C4',85),
('S4','C1',93),
('S4','C2',85),
('S4','C3',83),
('S5','C2',89);

8. Create TC table

CREATE TABLE IF NOT EXISTS TC(
 TNo CHAR(10),
 CNo CHAR(10),
 PRIMARY KEY(TNo,CNo)
 )DEFAULT CHARSET=utf8;

9.TC table filling data

INSERT INTO TC(TNo,CNo)
VALUES
('T1','C1'),
('T1','C4'),
('T2','C5'),
('T3','C1'),
('T3','C5'),
('T4','C2'),
('T4','C3'),
('T5','C5'),
('T5','C7');

10. Use the SQL command to add the "birthday" field in the student information table, whose data type is DATE, and put it after the "Age" field

ALTER TABLE S ADD Birthdary DATE;
ALTER TABLE S MODIFY Birthdary DATE AFTER Age;

11. Update the field information of birthday in table S

UPDATE S
SET Birthdary=('1986-9-13')
WHERE Sno=('S1');

UPDATE S
SET Birthdary=('1984-2-23')
WHERE Sno=('S2');

UPDATE S
SET Birthdary=('1984-1-27')
WHERE Sno=('S3');

UPDATE S
SET Birthdary=('1986-9-10')
WHERE Sno=('S4');

UPDATE S
SET Birthdary=('1984-2-20')
WHERE Sno=('S5');

UPDATE S
SET Birthdary=('1984-3-20')
WHERE Sno=('S6');

Multiple simultaneous changes can be made according to personal operation habits

That's the end of the first chapter. The second chapter needs to be updated in a few days

Topics: Database MySQL

Programmer Think