数据库的基础知识

范式

为了设计结构良好的数据库，需要遵守一些专门的规则，称为数据库的设计范式

第一范式(1NF)的目标：确保每列的原子性

第二范式(2NF)的目标：确保表中的每列，都和主键相关

第三范式(3NF)的目标：确保每列都和主键列直接相关，而不是间接相关

第一范式

第一范式的目标是确保每列的原子性
如果每列都是不可再分的最小数据单元(也称为最小的原子单元)，则满足第一范式(1NF)

第二范式

如果一个关系满足1NF（第一范式），并且除了主键以外的其他列，都依赖与该主键，则满足第二范式(2NF)

第二范式要求每个表只描述一件事情

第三范式

如果一个关系满足2NF（第二范式），并且除了主键以外的其他列都不传递依赖于主键列，则满足第三范式(3NF)

应用第三范式规范化

单词的含义

1、Create：创建

2、Insert：插入

3、Primary：主要的

4、Structured：结构化的

5、Log：日志

6、SQL：结构化查询语言

7、Alter：修改

8、Drop：删除 (使用 drop database 数据库名; 可删除此数据库 ；使用 drop table 表名; 可删除该表)

9、Character：字符

10、Engine：引擎

数据表的基础知识

长度

给列定义的大小部分指的是该列能接受多少个字符，比如 char 允许用户只输入一个字符，而另一些则不允许这些做，所以建议使列值尽可能小，显示列越小，表所占的空间也就越少。但是还有一个问题就是如果减少列的大小，MySQL将用截断数据以满足新的大小尺寸，所以很可能丢失有价值的信息数据

数据类型

数值类型 字符串类型 日期时间类型 enum类型和set类型 text类型和blob类型

数值类型

  1. 整数数据类型
  1. 浮点数据类型

浮点型也称近似类型。这种类型不能提供精确表示数据的精度，使用这种类型来存储某些数值时，有可能会损失一些精度

字符串类型

常用的字符串类型主要包括char、varchar、binary、 varbinary等类型

显示数据表信息命令

1
select *from 表名;

约束

概念：约束实际上就是表中数据的限制条件

作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值 (手机号) 不能为空，有些列的值 (身份证) 不能重复

修改字符段

1
2
3
4
5
6
7
8
9
10
11
12
create table stident
(
  sid int not null,
  score int,
  cid varchar(10),
  tid int,
  primary key(sid,cid)
);

alter table student modify sex varchar(4);  //修改学生表中性别的数据类型
alter table student add city char(15);      //在学生表中添加城市字符段
alter table student drop column city;       //删除学生表中城市的字符段

分类

  • 主键约束 (primary key) PK
  • 自增长约束 (auto_increment)
  • 非空约束 (not null)
  • 唯一性约束 (unique)
  • 默认约束 (default)
  • 零填充约束 (zerofill)
  • 外键约束 (foreign key) FK

主键约束

  • MySQL主键约束是一个列或者多个列的组合，其值唯一地标识表中的每一行，方便在RDBMS中尽快的找到某一行
  • 主键约束相当于 唯一约束+非空约束 的组合，主键约束列不允许重复，也不允许出现空值
  • 每个表最多只允许一个主键
  • 主键约束的关键字是：primary key
  • 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引

操作

  • 添加单列主键
  • 添加多列联合主键
  • 删除主键

单列主键

创建单列主键有两种方式，一种是在定义字段的同时指定主键，一种是定义完字段之后指定主键

方式一

1
2
3
4
5
6
7
8
use student; //使用哪个数据库
create table emp1
(
        eid int primary key,
	name varchar(20),
	deptId int,
	salary double
);

方式二

1
2
3
4
5
6
7
8
create table emp2
(
	eid int,
	name varchar(20),
	depId int,
	salary double,
	constraint pk1 primary key(eid) //constraint pk1 可以省略
);

联合主键

所谓联合主键，就是这个主键是由一张表中多个字段组成的

  1. 当主键是由多个字段组成时，不能直接在字段名后面声明主键约束

  2. 一张表只能有一个主键，联合主键也是一个主键

1
2
3
4
5
6
7
8
9
10
create table emp3
(
	name varchar(20),
	deptId int,
	salary double,
	constraint pk2 primary key(name,deptId)
);
insert into emp3 values('张三',10,2000); //数据1
insert into emp3 values('张三',20,2000); //数据2
insert into emp3 values('李四',10,2000); //数据3

通过修改表结构添加主键

主键约束不仅可以在创建表的同时创建，也可以在修改表时添加（比如当创建了表后忘了加主键就可以使用这种方法）

1
2
3
4
5
6
7
8
9
10
-- 提前创建好表emp4
create table emp4
(
	name varchar(20),
	age int,
	fox varchar(6),
	results double
);
-- 添加联合主键约束(也可以添加单个)
alter table emp4 add primary key(name,age);

删除主键

不需要指定哪个主键因为一个表就一个主键

1
alter table emp4 drop primary key; //删除emp4的主键

非空约束

MySQL 非空约束(not null) 字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错

方式一

1
2
3
4
5
6
create table t_use5
(
	id int,
	name varchar(20) not null,   -- 指定非空约束
	address varchar(20) not null -- 指定非空约束
);

方式二

1
2
3
4
5
6
7
8
create table t_user1
(
	id int,
	name varchar(20) ,
	address varchar(20)
);
alter table t_user1 modify name varchar(20) not null;
alter table t_user1 modify address varchar(20) not null;

查看是否为非空约束

1
desc t_user1; -- desc 后面的是你要查询的表名

删除非空约束

删除也是非常简单只需把 not null 去掉运行即可

1
2
alter table t_user1 modify name varchar(20);
alter table t_user1 modify address varchar(20);

唯一约束

唯一约束(Unique key) 是指所有记录中字段的值不能重复出现。例如，为 id 字段加上唯一性约束后，每条记录的 id 值都是唯一的，不能出现重复的情况

方式一

1
2
3
4
5
6
7
8
9
create table t_user2
(
	id int,
	name varchar(20) unique -- 指定唯一约束
);
insert into t_user2 values(100,'李四');
insert into t_user2 values(100,'李四2');
insert into t_user2 values(101,null); -- 在MySQL中 null 和任何值都不相同，甚至和自己也不相同
insert into t_user2 values(102,null);

方式二

1
2
3
4
5
6
create table t_user3
(
	id int,
	name varchar(20)
);
alter table t_user3 add constraint unique_pn unique(name);

删除唯一约束

1
2
alter table t_user3 drop index unique_pn; -- 这是方式二的删除约束方式，unique_pn代表是这个唯一约束的名字
alter table t_user2 drop index name;      -- 这是方式一的删除约束方式

默认约束

MySQL 默认值约束用来指定某列的默认值

方式一

1
2
3
4
5
6
7
8
create table t_user4
(
	id int,
	name varchar(20) default '小狗' -- 指定默认约束
);
insert into t_user4 values(123,'小米');
insert into t_user4(id) values(123);
insert into t_user4 values(123,null); -- 这个不会触发默认值，因为已经明确指定为null

方式二

1
2
3
4
5
6
7
8
9
create table t_user6
(
	id int,
	name varchar(20)
);
alter table t_user6 modify name varchar(20) default '张三';
insert into t_user6 values(123,'小米');
insert into t_user6(id) values(123);
insert into t_user6 values(123,null);

删除默认约束

1
2
alter table t_user6 modify name varchar(20) null;
insert into t_user6(id) values(7); -- 删除后再插入可以看到约束已经没了

自增长约束

在MySQL中，当主键定义为自增长后，这个主键的值就不再需要用户输入数据了，而由数据库系统根据定义自动赋值。每增加一条记录，主键会自动以相同的步长进行增长。

通过给字段添加 auto_increment 属性来实现主键自增长

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建表
create table t_user
(
	id int primary key auto_increment,
	name varchar(20)
);

-- 插入数据
insert into t_user values(null,'张三'); # 第一个参数写null就行了，但是不能空着
insert into t_user(name)  values('李四'); # 也可以指定字段赋值
insert into t_user  values(3,'王五'); # 也可以自己写id,但是不能重复
insert into t_user  values(7,'赵六'); # 也可以不需要按顺序写
insert into t_user  values(null,'小狗'); # 它会根据上一条数据的id自增长(每次加1)

特点

  • 默认情况下，auto_increment 的初始值是 1 ，每新增一条记录，字段值自动加 1 。
  • 一个表中只能有一个字段使用 suto_increment 约束，且该字段必须有唯一索引，以避免序号重复(即为主键或主键的一部分)。
  • auto_increment 约束的字段必须具备 NOT NULL 属性。
  • auto_increment 约束的字段只能是整数类型 (tinyint，smallint，int，bigint 等)

指定自增字段初始值

如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增。例如，如果表中插入的第一条记录的 id 值设置为 5 ，那么再插入记录时，id 的值就会从 5 开始往上增加

方式一

1
2
3
4
5
6
7
create table t_user2
(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment = 100;
insert into t_user2 values(null,'张三');
insert into t_user2 values(null,'张三');

方式二

1
2
3
4
5
6
7
8
create table t_use3 
(
			id int primary key auto_increment,
			name varchar(20)
);
alter table t_use3 auto_increment = 200;
insert into t_use3 values(null,'张三');
insert into t_use3 values(null,'李四');

delete 和 truncate 在删除后自增列的变化

  • delete 数据之后自动增长从断点开始
  • truncate 数据之后自动增长从默认起始值开始
1
2
3
delete from t_use3;
insert into t_use3 values(null,'张三');
insert into t_use3 values(null,'李四');

零填充约束

  • 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
  • zerofill 默认为 int(10)
  • 当使用 zerofill 时，默认会自动加 unsigned（无符号）属性，使用 unsigned 属性后，数值范围是原来的2倍，例如，有符号为 -128~+127，无符号为 0~256

操作

1
2
3
4
5
6
7
create table t_user7
(
	id int zerofill,
	name varchar(20)
);
insert into t_user7 values(111,'张三');
insert into t_user7 values(1,'李四');

删除零填充约束

1
alter table t_user7 modify id int;

总结

校园信息数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
create database studentinfo character set utf8;  //创建校园信息数据库

use studentinfo;     //使用该数据库
create table student //学生
  (
  sid int not null primary key, //学号，使用int型，not null表示非空，primary key表示主键
  sname varchar(15) not null,   //姓名，使用varchar型，因为名字是不定长的字符数据
  sex char(4),                  //性别，使用char型，因为性别是定长的字符数据
  age tinyint,                  //年龄，使用tinyint型，年龄占用字节小，所以这里使用占用一个字节的tinyint型
  depton smallint               //系编号，使用smallint型，使用占用两个字节的smallint型
  );
create table deptinfo //系
  (
  depton int not null primary key, //系编号，使用int型，not null表示非空，primary key表示主键
  deptname varchar(30)             //系名，使用varchar型，因为名字是不定长的字符数据
  );
create table teacgerinfo //教师
  (
  tno int not null primary key,  //教师编号，使用int型，not null表示非空，primary key表示主键
  tname varchar(15),             //教师名，使用varchar型，因为名字是不定长的字符数据
  depton smallint,               //系编号，使用smallint型，使用占用两个字节的smallint型
  title varchar(15)              //职称，使用varchar型，因为名字是不定长的字符数据
  );
create table courseinfo //课程
  (
  cno int not null primary key,  //课程号，使用int型，not null表示非空，primary key表示主键
  cname varchar(15)              //课程名，使用varchar型，因为名字是不定长的字符数据
  );
create table scourse //选课
  (
  sid int not null,      //学号，使用int型，not null表示非空
  cno int not null,      //课程号，使用int型，not null表示非空
  tno int,               //教师编号，使用int型
  primary key(sid cno)   //主键，多个主键的话使用括号括起来
  );

数据库和数据表的创建

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create database staff character set utf8;

create database mytest character set utf8; //创建mytset数据库
use mytest; //使用该数据库
create table staffinfo //创建数据表
(
  id int not null primary key,
  name varchar(20) not null,
  gender char(2),
  birthday date,
  email char(10) unique
);
alter table staffinfo add remark char(50);           //在数据表中添加列
alter table staffinfo change name sname varchar(20); //在数据表中重命名字段
alter table staffinfo modify gender char(6);         //在数据表中修改字段的数据类型

insert into staffinfo values  //在数据表中插入数据
('2022118', '张三', '男', '2003.1.23', '244579.com', ' '), //由于最后插入了remark一列，所以这里插入数据需要再加一列
('2020230', '李四', '男', '2004.4.18', '243476.com', ' ');

运行结果

数据查询语句 select

示例数据库

这个就是前面的校园信息数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
create database stucourse default charset utf8;

use stucourse;

create table student
(
    sid int not null primary key,
    sname varchar(15) not null unique,
    sex char(5),
    age int,
    dept varchar(15),
    check (age > 0 and age < 100)
);

create table bookinfo
(
    cbook char(15) not null primary key,
    bname char(50),
    bpublish char(15),
    bprice float,
    quantity int
);

create table courseinfo
(
    cid varchar(10) not null primary key,
    cname varchar(15) not null,
    cbook char(15),
    ctest datetime,
    dept varchar(15),
    foreign key(cbook) references bookinfo(cbook)
);

create table teacher
(
    tid int not null primary key,
    tname varchar(15) not null unique,
    title varchar(10),
    salary int,
    dept varchar(15)

);

create table scourse
(
    sid int not null,
    score int,
    cid varchar(10),
    tid int,
    primary key(sid, cid),
    foreign key(sid) references student(sid),
    foreign key(cid) references courseinfo(cid)
);

insert into student values
('1001', '宋江', '男', '25', '计算机系'),
('3002', '张明', '男', '23', '生物系'),
('1003', '李小鹏', '男', '26', '计算机系'),
('1004', '郑冬', '女', '25', '计算机系'),
('4005', '李小红', '女', '27', '工商管理'),
('5006', '赵紫月', '女', '24', '外语系');

insert into bookinfo values
('b1231', 'Image Processing', '人民大学出版社', '34.56', '8'),
('b1232', 'Signal Processing', '清华大学出版社', '51.75', '10'),
('b1233', 'Digital Signal Processing', '邮电出版社', '48.5', '11'),
('b1234', 'The Logic Circuit', '北大出版社', '49.2', '40'),
('b1235', 'SQL Techniques', '邮电出版社', '65.4', '20'),
('b1236', 'Electronic Techniques', '人民大学出版社', '38', '22');

insert into courseinfo values
('C1', '计算机基础', 'b1231', '2009-4-6', '计算机系'),
('C2', '工商管理基础', 'b1232', '2009-7-16', '工商管理'),
('C3', '生物科学', 'b1233', '2010-3-6', '生物系'),
('C4', '大学物理', 'b1234', '2009-4-26', '物理系'),
('C5', '数据库原理', 'b1235', '2010-2-6', '计算机系');

Iinsert into teacher values
('3102', '李明', '初级', '2500', '计算机系'),
('3108', '黄小明', '初级', '4000', '生物系'),
('4105', '张小红', '中级', '3500', '物理系'),
('5102', '宋力月', '高级', '3500', '地理系'),
('3106', '赵明阳', '初级', '1500', '生物系'),
('7108', '张丽', '高级', '3500', '生物系'),
('9103', '王彬', '高级', '3500', '计算机系'),
('7101', '王力号', '初级', '1800', '生物系');

insert into scourse values
('1001', '87', 'C1', '3102'),
('1001', '77', 'C2', '4105'),
('1001', '63', 'C3', '3108'),
('1001', '56', 'C4', '5102'),
('3002', '78', 'C3', '3108'),
('3002', '78', 'C4', '5102'),
('1003', '89', 'C1', '9103'),
('1004', '56', 'C2', '3106'),
('4005', '87', 'C4', '5102');

查询学生的姓名和年龄

查询属于计算机系的男生的信息

查询工资在 3000-4000之间的教师信息，并按工资排序

查询学生姓“张”的学号和姓名

查询有选修课的学生学号,每个学号显示一次

查询选修 C1或C2的学生的学号、课程号和成绩，并按成绩倒序排序

查询生物系的高级教师信息

聚合语句

示例代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
create database stucourse default charset utf8;

use stucourse;

create table student
(
    sid int not null primary key,
    sname varchar(15) not null unique,
    sex char(5),
    age int,
    dept varchar(15),
    check (age > 0 and age < 100)
);

create table bookinfo
(
    cbook char(15) not null primary key,
    bname char(50),
    bpublish char(15),
    bprice float,
    quantity int
);

create table courseinfo
(
    cid varchar(10) not null primary key,
    cname varchar(15) not null,
    cbook char(15),
    ctest datetime,
    dept varchar(15),
    foreign key(cbook) references bookinfo(cbook)
);

create table teacher
(
    tid int not null primary key,
    tname varchar(15) not null unique,
    title varchar(10),
    salary int,
    dept varchar(15)
);

create table scourse
(
    sid int not null,
    score int,
    cid varchar(10),
    tid int,
    primary key(sid, cid),
    foreign key(sid) references student(sid),
    foreign key(cid) references courseinfo(cid)
);

INSERT INTO student VALUES
('1001', '宋江', '男', '25', '计算机系'),
('3002', '张明', '男', '23', '生物系'),
('1003', '李小鹏', '男', '26', '计算机系'),
('1004', '郑冬', '女', '25', '计算机系'),
('4005', '李小红', '女', '27', '工商管理'),
('5006', '赵紫月', '女', '24', '外语系');

INSERT INTO bookinfo VALUES
('b1231', 'Image Processing', '人民大学出版社', '34.56', '8'),
('b1232', 'Signal Processing', '清华大学出版社', '51.75', '10'),
('b1233', 'Digital Signal Processing', '邮电出版社', '48.5', '11'),
('b1234', 'The Logic Circuit', '北大出版社', '49.2', '40'),
('b1235', 'SQL Techniques', '邮电出版社', '65.4', '20'),
('b1236', 'Electronic Techniques', '人民大学出版社', '38', '22');

INSERT INTO courseinfo VALUES
('C1', '计算机基础', 'b1231', '2009-4-6', '计算机系'),
('C2', '工商管理基础', 'b1232', '2009-7-16', '工商管理'),
('C3', '生物科学', 'b1233', '2010-3-6', '生物系'),
('C4', '大学物理', 'b1234', '2009-4-26', '物理系'),
('C5', '数据库原理', 'b1235', '2010-2-6', '计算机系');

INSERT INTO teacher VALUES
('3102', '李明', '初级', '2500', '计算机系'),
('3108', '黄小明', '初级', '4000', '生物系'),
('4105', '张小红', '中级', '3500', '物理系'),
('5102', '宋力月', '高级', '3500', '地理系'),
('3106', '赵明阳', '初级', '1500', '生物系'),
('7108', '张丽', '高级', '3500', '生物系'),
('9103', '王彬', '高级', '3500', '计算机系'),
('7101', '王力号', '初级', '1800', '生物系');

INSERT INTO scourse VALUES
('1001', '87', 'C1', '3102'),
('1001', '77', 'C2', '4105'),
('1001', '63', 'C3', '3108'),
('1001', '56', 'C4', '5102'),
('3002', '78', 'C3', '3108'),
('3002', '78', 'C4', '5102'),
('1003', '89', 'C1', '9103'),
('1004', '56', 'C2', '3106'),
('4005', '87', 'C4', '5102');

计算教师平均工资

计算计算机系的学生总人数

计算选课表的平均成绩

取得选课成绩的最高分和最低分

计算书本的平均价格