【学习笔记】使⽤SQLyog连接MySQL数据库⼀、使⽤SQLyog创建数据库⽤来管理学⽣信息
1 #创建数据库student
2DROP DATABASE IF EXISTS Myschool;
3CREATE DATABASE Myschool;
4
5 #在数据库中新建四张数据表
6USE Myschool;
7
8 #创建年级表
9CREATE TABLE grade(
10 gradeID INT(4) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '年级编号',
11 gradeName VARCHAR(50) NOT NULL COMMENT '年级名称'
12 )COMMENT="年级表";
13
14 #创建学⽣信息表
15CREATE TABLE student(
16 studentNo INT(4) NOT NULL PRIMARY KEY COMMENT '学号',
17 loginPwd VARCHAR(20) NOT NULL DEFAULT'123' COMMENT '密码',
18 studentName VARCHAR(50) NOT NULL COMMENT '姓名',
19 sex CHAR(2) NOT NULL DEFAULT'男' COMMENT '性别',
20 gradeID INT(4) UNSIGNED COMMENT '年级编号',
21 phone VARCHAR(50) COMMENT '电话',
22 address VARCHAR(255) DEFAULT'地址不详' COMMENT '地址',
23 bornDate DATETIME DEFAULT NOW() COMMENT '出⽣⽇期',
24 email VARCHAR(50) DEFAULT'xx@163' COMMENT '邮件账号',
25 identityCard VARCHAR(18) COMMENT '⾝份证号'
26 )COMMENT="学⽣信息表";
27
28 #创建科⽬表
29CREATE TABLE `subject`(
30 subjectNo INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '课程编号',
31 subjectName VARCHAR(50) COMMENT '课程名称',
32 classHour INT(4) COMMENT '学时',
33 gradeID INT(4) UNSIGNED COMMENT '年级编号'
34 )COMMENT="科⽬表";
35
36 #创建成绩表
37CREATE TABLE result(
38 studentNo INT(4) NOT NULL COMMENT '学号',
39 subjectNo INT(4) NOT NULL COMMENT '课程编号',
40 examDate DATETIME DEFAULT NOW() NOT NULL COMMENT '考试⽇期',
41 studentResult INT(4) NOT NULL COMMENT '考试成绩'
42 )COMMENT="成绩表";
43
44 #为表添加外键（关系）
45
46 #在student表中添加gradeID外键
47ALTER TABLE student
48ADD CONSTRAINT fk_student_grade FOREIGN KEY(gradeID)
49REFERENCES grade(gradeID);
50
51 #在subject表中添加gradeID外键
52ALTER TABLE `subject`
53ADD CONSTRAINT fk_subject_grade FOREIGN KEY(gradeID)
54REFERENCES grade(gradeID);
55
56 #在result表中添加studentNo，subjectNo外键
57ALTER TABLE result
58ADD CONSTRAINT fk_result_student FOREIGN KEY(studentNo)
59REFERENCES student(studentNo);
60
61ALTER TABLE result
62ADD CONSTRAINT fk_result_subject FOREIGN KEY(subjectNo)
63REFERENCES `subject`(subjectNo);
64
65 #为表添加初始数据
66
67 #年级表grade的数据
68INSERT INTO grade(gradeID,gradeName)
69VALUES(1,'⼀年级'),(2,'⼆年级'),(3,'三年级'),(4,'四年级'),(5,'五年级');foreign key references用法
70
71 #科⽬表subject的数据
72INSERT INTO `subject`(subjectNo,subjectName,classHour,gradeID)
73VALUES(1,'Logic Java',220,1),(2,'HTML',160,1),(3,'Java OOP',230,2);
74
75 #学⽣信息表student的数据
76INSERT INTO student(studentNo,studentName,sex,gradeID,phone,address,bornDate)
77VALUES(10000,'郭靖','男',1,136********,'天津市河西区','1990-09-08'),
78 (10001,'李⽂才','男',1,136********,'地址不详','1994-04-12'),
79 (10002,'李斯⽂','男',1,136********,'河南洛阳','1993-07-23'),
80 (10003,'张萍','⼥',1,136********,'地址不详','1995-06-10'),
81 (10004,'韩秋洁','⼥',1,138********,'北京市海淀区','1995-07-15'),
82 (10005,'张秋丽','⼥',1,135********,'北京市东城区','1994-01-17'),
83 (10006,'肖梅','⼥',1,135********,'河北省⽯家庄市','1991-02-17'),
84 (10007,'秦洋','男',1,130********,'上海市卢湾区','1992-04-18'),
85 (10008,'何睛睛','⼥',1,130********,'⼴州市天河区','1997-07-23'),
86 (20000,'王宝宝','男',2,150********,'地址不详','1996-06-05'),
87 (20010,'何⼩华','⼥',2,133********,'地址不详','1995-09-10'),
88 (30011,'陈志强','⼥',3,136********,'地址不详','1994-09-27'),
89 (30012,'李露露','⼥',3,136********,'地址不详','1992-09-27');
90
91 #成绩表result的数据
92INSERT INTO result(studentNo,subjectNo,examDate,studentResult)
93VALUES(10000,1,'2016-02-15',71),
94 (10000,1,'2016-02-17',60),
95 (10001,1,'2016-02-17',46),
96 (10002,1,'2016-02-17',83),
97 (10003,1,'2016-02-17',65),
98 (10004,1,'2016-02-17',70),
99 (10005,1,'2016-02-17',95),
100 (10006,1,'2016-02-17',93),
101 (10007,1,'2016-02-17',23),
102 (20000,3,'2016-07-17',68),
103 (20010,3,'2016-07-17',90),
104 (20000,2,'2016-07-17',88),
105 (20010,2,'2016-07-17',78);
106
107 ##学⽣信息数据库备份##
⼆、使⽤SQLyog创建数据库制作图书馆管理系统
1 #建⽴⼀个图书馆管理系统的数据库来存放图书馆的相关信息，包括图书的基本信息、图书借阅的信息和读者的信息 2
3 #创建数据库Library
4CREATE DATABASE IF NOT EXISTS Library;
5USE Library;
6 #创建实体表
7 #图书信息表book
8CREATE TABLE book(
9 bookId VARCHAR(50) PRIMARY KEY NOT NULL COMMENT '图书编号',
10 bookName VARCHAR(255) NOT NULL COMMENT '图书名称',
11 author VARCHAR(50) COMMENT '作者姓名',
12 pubComp VARCHAR(50) COMMENT '出版社',
13 pubDate DATE COMMENT '出版⽇期',
14 bookCount INT(4) COMMENT '现存数量',
15 price DOUBLE COMMENT '单价'
16 )COMMENT="图书信息表";
17
18 #读者信息表reader
19CREATE TABLE reader(
20 readerId VARCHAR(50) PRIMARY KEY NOT NULL COMMENT '读者编号',
21 readerName VARCHAR(50) NOT NULL COMMENT '读者姓名',
22 lendNum INT(4) COMMENT '已借书数量',
23 readerAddress VARCHAR(255) COMMENT '联系地址'
24 )COMMENT="读者信息表";
25
26 #图书借阅表borrow
27CREATE TABLE borrow(
28 readerId VARCHAR(50) NOT NULL COMMENT '读者编号',
29 bookId VARCHAR(50) NOT NULL COMMENT '图书编号',
30 lendDate DATETIME DEFAULT NOW() NOT NULL COMMENT '借阅⽇期',
31 willDate DATE COMMENT '应归还⽇期',
32 returnDate DATE COMMENT '实际归还⽇期'
33 )COMMENT="图书借阅表";
34
35 #记录表penalty
36CREATE TABLE penalty(
37 readerId VARCHAR(50) NOT NULL COMMENT '读者编号',
38 bookId VARCHAR(50) NOT NULL COMMENT '图书编号',
39 pDate DATE NOT NULL COMMENT '⽇期',
40 pType INT COMMENT '类型：1-延期，2-损坏，3-丢失',
41 amount DOUBLE COMMENT '⾦额'
42 )COMMENT="记录表";
43
44 #删除信息表
45USE Library;
46DROP TABLE IF EXISTS book;
47DROP TABLE IF EXISTS reader;
48DROP TABLE IF EXISTS borrow;
49DROP TABLE IF EXISTS penalty;
50
51 #给borrow添加复合主键
52ALTER TABLE borrow
53ADD CONSTRAINT pk_borrow PRIMARY KEY(readerId,bookId,lendDate); 54
55 #给penalty添加复合主键
56ALTER TABLE penalty
57ADD CONSTRAINT pk_penalty PRIMARY KEY(readerId,bookId,pDate); 58
59 ##删除borrow和penalty的主键
60ALTER TABLE borrow
61DROP PRIMARY KEY;
62ALTER TABLE penalty
63DROP PRIMARY KEY;
64 ##删除borrow和penalty的外键
65ALTER TABLE borrow
66DROP FOREIGN KEY fk_borrow_book;
67ALTER TABLE borrow
68DROP FOREIGN KEY fk_borrow_reader;
69ALTER TABLE penalty
70DROP FOREIGN KEY fk_penalty_book;
71ALTER TABLE penalty
72DROP FOREIGN KEY fk_penalty_reader;
73
74 #给borrow添加外键
75ALTER TABLE borrow
76ADD CONSTRAINT fk_borrow_book FOREIGN KEY(bookId)
77REFERENCES book(bookId);
78ALTER TABLE borrow
79ADD CONSTRAINT fk_borrow_reader FOREIGN KEY(readerId)
80REFERENCES reader(readerId);
81
82 #给penalty添加外键
83ALTER TABLE penalty
84ADD CONSTRAINT fk_penalty_book FOREIGN KEY(bookId)
85REFERENCES book(bookId);
86ALTER TABLE penalty
87ADD CONSTRAINT fk_penalty_reader FOREIGN KEY(readerId)
88REFERENCES reader(readerId);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论