mysql(进阶)

mysql(进阶)

练习

mysql基础练习题
已知：部门表（emp），表中的字段：部门编号（depno），工作（job），工资（sal）

约束

主键约束：唯一标识
1、数据库的每张表只能有一个主键，不可能有多个主键。
2、所谓的一张表多个主键，我们称之为联合主键。
注：联合主键：就是用多个字段一起作为一张表的主键
特点：（1）非空（2）唯一（3）被引用->(学习外键时，只有主键才能被引用)
primary key

1. 指定主键的两种方式
   eg:
   （1）
   CREATE TABLE emp(
   id INT PRIMARY KEY,
   NAME VARCHAR(20)
   );
   （2）
   CREATE TABLE emp(
   id INT,
   NAME VARCHAR(20),
   PRIMARY KEY(id)
   );
2. 已存在的表添加主键
   ALTER TABLE emp ADD PRIMARY KEY(NAME);
3. 已存在的表中删除主键（不需要指定哪个主键，因为只有一个）
   ALTER TABLE emp DROP PRIMARY KEY;

（1）每张表可以有多少个主键?

（2）一张表多个字段在一起作为主键，叫做什么？

（3）主键的三大特性是什么？

（4）指定主键的两种方式是什么？

（5）给已存在的表添加主键的方式是什么？

（6）已存在主键的表中删除主键的方式是什么？

主键自增长

主键自增长： auto_increment

CREATE TABLE emp(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(20)

);

INSERT INTO emp VALUES(NULL,“小明”);

INSERT INTO emp VALUES(NULL,“小红”);

结果：1.小明 2.小红

注意自增长的前面数据被删了，不会重新编号，而是会接着向下取号，像银行的取票机器一样

一般选取无意义的不重复的值作为主键，不建议选取自然主键（有意义的），如身份证。

自然主键有可能根据业务变更而发生变化，这会导致系统崩溃，所以使用代理主键

主键自增长在多台机、虚拟机上不好使用，这时候使用uuid做主键

自增长上一条记录删除了，下一条记录的编号是什么？

非空约束

not null

非空约束是什么？

唯一约束

unique

唯一约束是什么？

概念模型

对象模型：在java中是领域对象（domain） ，可以双向关联，而且引用的是对象，而不是主键！

关系模型：只能多方引用一方（从表引用主表），而且引用的只是主键而不是一整行记录。

几种关系模型的：

is a :继承

has a：成员（关联关系）

use a：作为参数

类型：

1对1

1对多（一个部门多个员工）

多对多（多个部门多个员工）

java（多一般使用集合）

主从关系：

谁少谁是主，多方引用少的

如：学生和班级，学生多，学生引用班级的主键作为自己的外键

如：user、teacher

数据（关系）模型：在数据库中是表

在表中如何实现多对一？

使用主外键关联

什么是外键？

引用另一张表的（也可以是自己）主键

外键的特性：

（1）外键要引用主键

（2）外键可以重复（结合学生班级理解）

（3）外键可以为空（学生还没选班）

一张表中可以有多个外键吗？

可以，关联多个表

（1）关系模型是什么？引用的是什么？

（2）在表中如何实现多对一？

（3）什么是外键？

（4）外键的三大特性是什么？

外键约束

constraint 外键名（一般fk_从表_主表）->定义在从表中 foreign key（从表）references 主表名（主键）

CONSTRAINT fk_people_department FOREIGN KEY(did) REFERENCES department(id)

CREATE TABLE department(

id INT,

NAME VARCHAR(20));

ALTER TABLE department ADD PRIMARY KEY(id);

ALTER TABLE department MODIFY id INT AUTO_INCREMENT;

INSERT INTO department VALUES(NULL,‘大堂’,‘经理’);

INSERT INTO department VALUES(NULL,‘公园’,‘保安’);

CREATE TABLE people(

pid INT PRIMARY KEY AUTO_INCREMENT,

pname VARCHAR(20),

did INT,

CONSTRAINT fk_people_department FOREIGN KEY(did) REFERENCES department(id)

);

验证：INSERT INTO people VALUES(NULL,‘小明’,‘3’);

给表添加外键约束:

ALTER TABLE people ADD CONSTRAINT fk_people_department FOREIGN KEY(did) REFERENCES department(id);

（1）创建表时添加外键约束的语句是什么？

（2）给表添加外键约束的语句是什么？

多对一关系

数据库表中的多对一关系,只需要在多方使用一个独立的列来引用1方的主键即可

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

多对一关系怎么设计？

一对一关系

一对一：方法：从表的主（外）键就是主表的主键，即两表的主键相同。

从表的主键即是主键又是外键！！

两字段都是主键，且相同满足（1）非空（2）唯一，且外键引用了另一个键，关联起来了

CREATE TABLE wife(

pid INT PRIMARY KEY,

pname VARCHAR(20)

);

CREATE TABLE husband(

hid INT PRIMARY KEY,

hname VARCHAR(20),

CONSTRAINT fk_hhusband_wife FOREIGN KEY(hid) REFERENCES wife(pid)

);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YxKpvllw-1642673651537)(C:\Users\Administrator\Desktop\java学习\mysql(进阶)].assets\image-20220120160300744.png)

一对一的主外键怎么设计？

多对多关系

两张表都是主表（没有外键），需要创建中间表作为从表，用中间表来存储关系，中间表也成为关系表。

CREATE TABLE teacher(

tid INT PRIMARY KEY,

tname VARCHAR(20)

);

INSERT INTO teacher(tid,tname) VALUES(1,‘赵老师’);

INSERT INTO teacher(tid,tname) VALUES(2,‘王老师’);

INSERT INTO teacher(tid,tname) VALUES(3,‘李老师’);

CREATE TABLE student(

sid INT PRIMARY KEY,

sname VARCHAR(20)

);

INSERT INTO student(sid,sname) VALUES(1,‘小红’);

INSERT INTO student(sid,sname) VALUES(2,‘小蓝’);

INSERT INTO student(sid,sname) VALUES(3,‘小绿’);

CREATE TABLE relation(

sid INT,

tid INT,

CONSTRAINT fk_relation_student FOREIGN KEY(sid) REFERENCES student(sid),

CONSTRAINT fk_relation_teacher FOREIGN KEY(tid) REFERENCES teacher(tid)

);

INSERT INTO relation VALUES(1,1);

INSERT INTO relation VALUES(1,2);

INSERT INTO relation VALUES(1,3);

INSERT INTO relation VALUES(2,1);

INSERT INTO relation VALUES(2,3);

INSERT INTO relation VALUES(3,3);

多对多关系怎么设计？

多表查询

（1）合并结果集（了解）

表显示的结果集的结构相同（类型、列数、一一对应），只是列名不同

说白了就是两张表的查询结果一起展示

语句

SELECT *FROM 表名1 UNION 【ALL】 SELECT *FROM 表名2;

若没有all，完全相同的行会被去除！

eg：

SELECT *FROM teacher UNION ALL SELECT *FROM student;

（2）连接查询：一次性查询多个表

（3）子查询： 查询里面又包含查询

查询的结果集怎么和合并？

连接查询

内连接：

方言：

外连接：

左外连接

右外连接

全外连接

自然连接

SELECT *FROM student,teacher;

求出来的是两表的全组合情况（笛卡尔积），因此要使用where来限定输出的结果

所以：

SELECT *FROM husband,wife WHERE husband.hid=wife.pid;

多表查询第一件事情：就是去笛卡尔积（where限定条件）

表也可以去别名：

SELECT *FROM husband h,wife w WHERE h.hid =w.pid

内连接

寻找两表中满足条件的的记录。

内连接的标准形式(重点)：

select *from 表1 别名1 inner join 表2 别名2 on 别名1.xx = 别名2.xx

eg：SELECT *FROM husband h INNER JOIN wife w ON h.hid = w.pid;

方言：

select *from 表1，表2 where 表1.xx = 表2.xx

内连接的标准形式是什么？

内连接的方言形式是什么？

自然连接

自动找表中名字相同的列进行匹配

SELECT *FROM husband NATURAL JOIN wife;

自然连接的形式是什么？

外连接

外连接：外连接有一主一次，左外即左表为主，即emp为主，那么主表中所有的记录无论满不满足条件，都打印出来，当不满足条件时，使用NULL来补位，右连接同理！

左连接：

SELECT*FROM 表1 LEFT OUTER JOIN 表2 ON husband.hid=wife.pid;

左连接的形式是什么？

右链接：

SELECT*FROM husband RIGHT OUTER JOIN wife ON husband.hid=wife.pid;

右连接的形式是什么？

全外（mysql不支持）：

SELECT*FROM husband FULL OUTER JOIN wife ON husband.hid=wife.pid;

全外连接的形式是什么？

mysql：使用全外的方法：合并结果集，去除重复行 union

mysql中实现全连接的形式是什么？

SELECT*FROM husband LEFT OUTER JOIN wife ON husband.hid=wife.pid

UNION

SELECT*FROM husband RIGHT OUTER JOIN wife ON husband.hid=wife.pid;

聚合函数不能使用在where的条件下！，但可以写在select 后面（where后面接查询前结果，而聚合函数是查询后的结果）

子查询

如何查查询工资最高的员工的详细信息？

->查询里面包查询

from、where后都可以包含子查询

from后包含子查询：（该子查询结果可以多行多列）

SELECT w.pid FROM (SELECT *FROM wife WHERE wife.pid=1) w;

where后包含子查询：（该子查询结果必须单结果（单行单列、单行多列（对象）、多行单列（集合））

SELECT *FROM wife WHERE pid=(SELECT hid FROM husband WHERE hname=‘洪湖’);

条件：

（1）单行单列：一般与>、=、>=、<=、!=

（2）多行单列：in、all、any

any：只要与集合中任意元素满足条件即可。

all：要与集合中的所有元素满足条件。

in：集合中

eg：

SELECT *FROM wife WHERE pid<ALL (SELECT pid FROM wife WHERE sal>200);

SELECT *FROM wife WHERE pid<ANY (SELECT pid FROM wife WHERE sal>200);

SELECT *FROM wife WHERE pid IN (SELECT pid FROM wife WHERE sal>200);

（3）单行多列：in，select后有多项

少用：

SELECT *FROM wife WHERE (pid,sal) IN (SELECT pid,sal FROM wife WHERE sal=200);

题：

（1）哪些关键字后可以跟select语句？

（2）where字查询后的条件为？

（3）all、any、in于select语句使用代表什么？