数据库day02

day01回顾

一、数据库

存储、管理和维护数据的集合
DB
DBMS

二、SQL语言

DDL：对对象进行操作（数据库、表、列）

CREATE、ALTER、DROP

DML：对表中的数据进行操作

INSERT、UPDATE、DELETE

insert into 表名 (列名1，列名2，…) values(列值1，列值2，…);
update 表名 set 列名1=列值1，列名2=列值2 where 列名=列值;
delete from 表名 where 列名=列值;

DQL：查询

SELECT FROM WHERE GROUP BY HAVING ORDER BY

where条件使用的关键字：
= != <> > < >= <=
in
between and
is not null
is null
and
or
not
like 通配符：_ %

DCL：控制

---

1、数据完整性

作用：保证用户输入的数据保存到数据库中是正确的
确保数据的完整性 = 在创建表时给表中添加约束

1.1 实体完整性（行级）

实体：表中的一行（一条记录）代表一个实体entity
实体完整性的作用：标识每一行数据不重复

约束类型：主键约束（primary key）、唯一约束（unique）、自动增长列（auto increment）

1.1.1 主键约束primary key

每个表都要有一个主键，特点是数据唯一，且不能为null
添加方式
1、方式一
-- 标明id是主键
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(50)
);
2、方式二，联合主键（一个主键）
-- 方式二，适合创建联合主键
CREATE TABLE student(
classid INT,
id INT,
NAME VARCHAR(50),
PRIMARY KEY(classid,id)
);
3、方式三
CREATE TABLE student(
id INT,
NAME VARCHAR(50)
);
ALTER TABLE student ADD CONSTRAINT pk_id PRIMARY KEY(id);


1.1.2 唯一约束unique

可以为空值，但不能有多个
CREATE TABLE student(
id INT PRIMARY KEY,
NAME VARCHAR(50) UNIQUE
);

1.1.3 自动增长列auto increment

sqlserver:identity oracle:sequence
作用：给主键添加自动增长的数值，列值只能是整数类型
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) UNIQUE
);

1.2 域完整性（列级）

作用：限制此单元格的数据正确，不对照此列的其他单元格比较
域代表单元格
域完整性约束：数据类型、非空约束（not null）、默认值约束（default）、check约束（mysql不支持）、check(sex=’男’ or sex=’女’）

1.2.1 数据类型

数据类型、日期类型、字符串类型

1.2.2 非空约束NOT NULL

-- 非空约束
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) UNIQUE NOT NULL
);

1.2.3 默认值约束DEFAULT

-- 默认值约束
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50) NOT NULL,
address VARCHAR(50) DEFAULT '北京'
);
INSERT INTO student(NAME, address) VALUES('aaa',DEFAULT);
INSERT INTO student(NAME, address) VALUES('bbb','南京');
INSERT INTO student(NAME) VALUES('ccc');
INSERT INTO student VALUES(NULL,'ddd',DEFAULT);

1.3 引用完整性（参照完整性）

添加外键约束：FOREIGN KEY
语法：CONSTRAIN 外键名 FORIEGN KEY(当前表中字段名) REFERENCES 表名（字段名）
e.g.
第一种添加方式：
-- 引用完整性（外键约束）
-- 学生表
CREATE TABLE student(
stuid INT PRIMARY KEY,
stuname VARCHAR(50) NOT NULL
);
-- 分数表
CREATE TABLE score(
stuid INT,
score DOUBLE,
courseid DOUBLE,
CONSTRAINT fk_stu_sco FOREIGN KEY(stuid) REFERENCES student(stuid)
);
第二种添加方式：表事先已建好
-- 学生表
CREATE TABLE student(
stuid INT PRIMARY KEY,
stuname VARCHAR(50) NOT NULL
);
-- 分数表
CREATE TABLE score(
stuid INT,
score DOUBLE,
courseid DOUBLE
);
ALTER TABLE score ADD CONSTRAINT fk_stu_sco FOREIGN KEY(stuid) REFERENCES student(stuid)

注：图中关联线上的数字表示两表之间的关系：student—score是一对多的关系。

---

2、多表查询

表与表之间的关系

三种：一对一、一对多（多对一）、多对多
1、一对一：找准主从关系，如人和身份证，人是主表，而身份证是从表；
设计从表可以有两种方案：
1）在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束unique；
2）给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。

2、一对多（多对一）：两者角度不同，本质一样；

3、多对多：需要创建中间表来处理对多对的关系。对于中间表给两个外键

为什么要拆表？：避免一个表中存在大量冗余数据

三种多表查询方式：
1）合并结果集：UNION、UNION ALL
2）连接查询：内连接、外链接、自然连接
3）子查询

2.1 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起
两种方式：
union:去除重复记录
union all:不去除重复记录
示例：
`– 合并结果集
CREATE TABLE A(
NAME VARCHAR(10),
score INT
);
CREATE TABLE B(
NAME VARCHAR(10),
score INT
);
INSERT INTO A VALUES(‘a’,10),(‘b’,20),(‘c’,30);
INSERT INTO B VALUES(‘a’,10),(‘b’,20),(‘d’,40);

SELECT * FROM A UNION SELECT * FROM B;
SELECT * FROM A UNION ALL SELECT * FROM B;`
要求：被合并的两个结果列数和列类型必须相同。

2.2 连接查询

连接查询就是求出多个表的乘积，如$t1$连接$t2$，那么查询结果就是$t1*t2$
连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。
必须使用条件过滤不想要的信息，即使用主外键关系作为条件去除无用信息
e.g.
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
SELECT * FROM student s, score c WHERE s.stuid=c.stuid;(将student和score取新表名s和c)

2.2.1 内连接(INNER) JOIN ON

SELECT * FROM emp INNER JOIN dept ON emp.deptno=dept.deptno;
SELECT * FROM student s JOIN score c ON s.stuid=c.stuid;
特点：查询结果必须满足条件，即ON后面的语句

2.2.2 外连接OUTER JOIN ON

特点：查询结果存在不满足条件的可能
1）左外连接 LEFT (OUTER) JOIN
先查询出左表（即以左表为主），然后查询右表，右表中满足ON条件的显示出来，不满足的显示为NULL
2）右外连接 RIGHT (OUTER) JOIN
先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。
3）全外连接（MySQL不支持） FULL JOIN

2.2.3 多表查询

-- 多表查询
CREATE TABLE course(
courseid INT PRIMARY KEY,
cname VARCHAR(50)
);
CREATE TABLE score(
stuid INT,
courseid INT,
score INT
);
CREATE TABLE student2(
stuid INT PRIMARY KEY,
sname VARCHAR(50)
);
SELECT s.stuid, s.sname,c.courseid,cc.cname,c.score FROM student2 s, score c, course cc WHERE s.stuid=c.stuid AND c.courseid=cc.courseid;
SELECT s.stuid, s.sname,c.courseid,cc.cname,c.score FROM student2 s JOIN score c ON s.stuid=c.stuid JOIN course cc ON c.courseid=cc.courseid;

2.2.4 自然连接 NATURAL JOIN

自动匹配条件，不会产生笛卡尔积，不需要使用主外键等式，自动寻找等式
将表中名称和类型完全一致的列作为条件
SELECT * FROM student NATURAL JOIN score

2.3 子查询(嵌套查询)**

一个select语句中包含另一个完成的select语句
1）子查询出现的位置：
where后：作为被查询的条件的一部分
from后：作为表
2）当子查询出现在where后作为条件时，还可以使用如下关键字：any、all
3）子查询结果集的形式：
单行单列（用于条件）；
单行多列（用于条件）；IN
多行单列（用于条件）；IN
多行多列（用于表）。
e.g.
单行单列
-- 查询工资高于JONES得员工信息
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES'

单行多列
-- 查询工作和工资与MARTIN（马丁）完全相同的员工信息
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='MARTIN'

多行单列
-- 有两个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2)

自连接

自己连接自己，起别名
--求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno, e1.ename, e1.mgr, e2.ename FROM emp e1, emp e2 WHERE e1.mgr=e2.empno AND e1.empno=7369;
e.g.
-- 求各个部门薪水最高得员工所有信息
SELECT e1.* FROM emp e1, (SELECT deptno, MAX(sal) maxSal FROM emp GROUP BY deptno) e2 WHERE e1.sal=e2.maxSal AND e1.deptno=e2.deptno;

MySQL中的函数

时间日期相关

字符串相关

数学相关