1.数据库设计范式
为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的
规则。于是存在3种范式
- 第一范式:要求列保持原子性特征(不可拆分)
- 第二范式:要有主键,要求其他字段依赖于主键(有一个列能够保持唯一),便于查找.
- 第三范式:消除传递依赖,方便理解,消除数据冗余.
2. 外键
外键:引用另外一个数据表的某条记录。
- 外键的类型因与主键类型保持一致,数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键(foreign key)建立起来的。
约束规则:
ALTER TABLE 表名 ADD CONSTRAINT 名称(自定义) FOREIGN KEY (表中的相关列) REFERENCES 相关表格(相关列)
类如:
CREATE TABLE basketmember(
编号 INT PRIMARY KEY AUTO_INCREMENT,
名字 VARCHAR(10),
生日 DATE,
身高 DOUBLE(3,2) CHECK(身高>1.8),
体重 INT,
位置 VARCHAR(5)
)
INSERT INTO basketmember(名字,生日,身高,体重,位置)
VALUES('德怀恩.韦德','1982-1-17',1.93,96,'null'),
('勒布朗.詹姆斯','1984-12-30',2.03,113,'前锋'),
('科比.布莱恩特','1978-8-23',1.92,99,'后卫'),
('德科.诺维斯基','1978-6-19',2.13,111,'null'),
('艾里斯.保罗','1985-5-6',1.82,79,'后卫'),
('托尼.帕克','1982-5-17',1.87,83,'后卫'),
('凯文.加内特','1981-7-14',2.12,113,'null'),
('保罗.皮尔斯','1977-10-13',2.00,106,'前锋'),
('迈克尔.乔丹','1963-2-17',1.98,98,'前锋'),
('德怀特-霍华德','1985-12-18',2.10,120,'中锋'),
('姚明','1980-9-12',2.29,140,'中锋'),
('沙奎尔.奥尼尔','1972-3-6',2.15,147,'中锋')
ALTER TABLE basketmember ADD COLUMN id INT
CREATE TABLE basketid(
id INT PRIMARY KEY AUTO_INCREMENT,
身体素质 DOUBLE(3,1),
投篮 DOUBLE(3,1),
控球 DOUBLE(3,1)
)
DROP TABLE basketid
INSERT INTO basketid(身体素质,投篮,控球)
VALUES(9.5,9.5,9.0),
(10.0,9.5,9.0),
(9.5,10.0,9.5),
(7.5,9.5,7.5),
(9.0,8.5,10.0),
(8.5,8.5,9.5),
(8.5,8.0,8.0),
(7.5,8.5,8.5),
(10.0,10.0,10.0),
(10.0,7.5,7.5),
(9.0,9.0,7.5),
(9.0,7.5,7.0)
ALTER TABLE basketmember ADD CONSTRAINT id_fk FOREIGN KEY (id) REFERENCES basketid(id)
如果两张表格关联成功后会出现下面一个图标
1、当主表中没有对应的记录时,不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录,不能从主表中删除该行
4、删除主表前,先删从表
3.多表关联查询
1.笛卡尔乘积现象:表1有m行,表2有n行,结果=m*n(如果没有特定的条件约束,就会发生笛卡尔现象)
2.关联查询:
- 内连接:满足两张表的交集求出来 语句:
select 结果 from表1 inner join 表2 on 表1.column1 = 表2.column2
- 左外关联: 把前一个表的全部元素全部打印出来 语句:
select 结果 from表1 left join 表2 on 表1.column1 = 表2.column2
- 右外连接:把后一个表的全部元素全部打印出来 语句:
select 结果 from表1right join 表2 on 表1.column1 = 表2.column2
3.子查询:
含义:出现在其他语句中的select语句,称为子查询或内查询;外部的查询语句,称为主查询或
外查询.
分类:
- 按子查询出现的位置:select后面:支持标量子查询 from后面:支持表子查询 where:支持标量子查询,列子查询
- 按功能、结果集的行列数不同:标量子查询(结果集只有一行一列)列子查询(结果集只有一列多行)表子查询(结果集一般为多行多列)
例题:
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(10),
dept_decs VARCHAR(10),
dept_date DATE
)
DROP TABLE dept
CREATE TABLE member(
id INT PRIMARY KEY AUTO_INCREMENT,
name_m VARCHAR(10),
gender VARCHAR(10),
age VARCHAR(10),
money VARCHAR(10),
deptid INT
)
DROP TABLE member
INSERT INTO member(name_m,gender,age,money,deptid)
VALUES('王五','男',25,3000,1),
('李明','男',23,2500,1),
('王小二','男',23,2356,2),
('陈发','男',22,3600,2),
('小明','男',21,3100,3),
('苏奇','男',24,2800,3),
('王丽','女',19,1800,1),
('李芳','女',18,1900,2)
1. SELECT m.id,m.name_m,m.gender,m.age,m.money,d.dept_name FROM member m INNER JOIN dept d ON m.deptid=d.id
2. SELECT * FROM member ORDER BY money DESC LIMIT 1
3. SELECT name_m,gender,age,money,deptid FROM member m INNER JOIN dept d ON m.deptid=d.id WHERE money IN(SELECT MAX(money)FROM member GROUP BY deptid)
4.select SUM(money)FROM member WHERE gender='男'
SELECT AVG(money)FROM member WHERE gender='男'
SELECT MAX(money)FROM member WHERE gender='男'
5. SELECT*,d.dept_name FROM member ,dept d WHERE money>(SELECT AVG(money)FROM member)
6. SELECT name_m,gender FROM member WHERE name_m LIKE '王%'
7. SELECT name_m,age FROM member ORDER BY age DESC LIMIT 3
8. SELECT dept_name,SUM(money)FROM member m INNER JOIN dept d ON m.deptid=d.id GROUP BY deptid
9. SELECT dept_name,COUNT(deptid) FROM member m INNER JOIN dept d ON m.deptid=d.id GROUP BY deptid