初始MySQL(五)(自我复制数据,合并查询,外连接,MySQL约束:主键,not null,unique,foreign key)

目录

表复制

自我复制数据(蠕虫复制)

合并查询

union all(不会去重)

union(会自动去重)

MySQL表的外连接

左连接

右连接

MySQL的约束

主键

not null

unique(唯一)

foreign key(外键)

---

表复制

自我复制数据(蠕虫复制)

#为了对某个sql语句进行效率测试,我们需要海量数据时,可以使用此法为表创建海量数据

-- 演示表的复制
CREATE TABLE my_tab01
        ( id INT,
          `name` VARCHAR(32),
          sal DOUBLE,
          job VARCHAR(32),
          deptno INT);
DESC my_tab01;
SELECT * FROM my_tab01;
-- 演示如何自我复制
-- 1.先把emp表记录复制到my_tab01
INSERT INTO my_tab01 
        (id, `name` ,sal,job,deptno)
        SELECT empno,ename,sal,job,deptno FROM emp;
SELECT * FROM my_tab01;

-- 2.自我复制
INSERT INTO my_tab01
        SELECT * FROM my_tab01;
SELECT * FROM my_tab01;

-- 如何删除掉一张表重复记录

-- 1.先创建一张表 my_tab02

-- 2.考虑去重

-- 如何删除掉一张表重复记录
-- 1.先创建一张表 my_tab02
CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表结构(列) , 
                                -- 复制到my_tab02,并没有复制数据
DESC my_tab02;
SELECT * FROM my_tab02

INSERT INTO my_tab02
      SELECT *FROM emp
SELECT * FROM my_tab02;

-- 2.考虑去重
-- 思路:(1)先创建一个临时表 my_tmp,该表的结构和my_tab02一致
     -- (2)把my_tab02的记录通过distinct关键字处理后,把记录复制到my_tmp
     -- (3)清除掉my_tab02记录
     -- (4)把my_tmp 表的记录复制到my_tab02
     -- (5)drop掉 临时表my_tmp
-- (1)先创建一个临时表 my_tmp,该表的结构和my_tab02一致
CREATE TABLE my_tmp LIKE my_tab02
-- (2)把my_tab02的记录通过distinct关键字处理后,把记录复制到my_tmp
INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;
-- (3)清除掉my_tab02记录
DELETE FROM my_tab02;
-- (4)把my_tmp 表中的记录复制到my_tab02
INSERT INTO my_tab02 SELECT * FROM my_tmp
-- (5)drop掉 临时表my_tmp
DROP TABLE my_tmp
SELECT * FROM my_tab02;

合并查询

介绍 有时在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号  union , union all

union all(不会去重)

union(会自动去重)

MySQL表的外连接

# 创建stu 
CREATE TABLE stu(
id INT,
`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
SELECT * FROM stu;
#创建exam表
CREATE TABLE exam(
id INT,
grade INT);
INSERT INTO exam VALUES(1,56),(2,76),(11,8);
SELECT * FROM exam;
#笛卡尔集
SELECT * FROM stu,exam

左连接

练习要求:显示所有人的成绩,如果没有成绩,也要显示该姓名和id号,成绩显示为空

我们可以用左连接(我们以左边的表为基准去右边的表去找,找到一起显示,找不到显示NULL)

-- 改成左外连接,显示所有人的成绩,如果没有成绩,也要显示该人的姓名和id号,
-- 成绩显示空
-- select ... from 表1 left join 表2 on 条件   
-- 上面的表1是左表,表2是右表

SELECT  `name`,stu.id,grade  FROM stu LEFT JOIN exam
          ON stu.id = exam.id; -- 一左边为基准去右边找,找到显示,找不到显示NULL
          

右连接

再来一个练习

列出部门名称和那些部门的员工信息(名字和工作), 同时列出那些没有员工的部门名

-- 右外连接 select ... from 表1 right join 表2 on 条件   
SELECT ename ,dname 
         FROM emp RIGHT JOIN dept
         ON emp.deptno = dept.deptno
         ORDER BY dname

MySQL的约束

主键

在创建表的时候使用 基本语法:    字段名  字段类型  primary key

用于唯一的标示表行的数据,当定义主键约束后,该行的值不能重复

主键使用的细节

(1) primary key 不能重复而且不能为空

INSERT INTO t17 VALUES(NULL,'nb','nb克拉斯') -- Column 'id' cannot be null

(2)-- 一张表中最多只能有一个主键,但是可以设置复合主键

-- 一张表中最多只能有一个主键
CREATE TABLE t18(
id INT PRIMARY KEY,
`name` VARCHAR(32) PRIMARY KEY,
email VARCHAR(32));
-- 演示复合主键(id 和 name 做成复合主键)
CREATE TABLE t18(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY (id,`name`) -- 这里就是复合主键 ,表示这两列的值要都不相同
); 
INSERT INTO t18 VALUES(1,'tom','tom@nb.com');
INSERT INTO t18 VALUES(1,'lhq','lhq@znb.com');

SELECT * FROM t18;

只有设置复合主键中的值都相同的时候会报错误

desc来查看主键

not null

如果在列上定义了not null ,那么当插入数据时,必须为列提供数据

字段 字段类型 NOT NULL

说明我们输入id的时候不可以输入NULL值

unique(唯一)

当定义了唯一约束后,该列值是不能重复的

字段名 字段类型 unique

下面重复的id时候报错误的

unique使用细节

-- 1.如果没有指定not null ,则unique 字段可以有多个null

-- 如果一个列(字段),是 unique not null 使用效果类似于 primary key

--2.一张表中可以有多个unique字段

foreign key(外键)

啥叫外键?

用韩老师画的这张图就是说,我现在班级表设置成主表,id有主键约束,学生表中的class_id正好和班级表的id一样,我们将学生表中的class_id指向班级表的id,这两个表之间就产生了联系,我再往学生表中添加信息,如果class_id在主表的id中不存在,那就添加不上去,这就是我们的外键约束

用于定义主表和从表之间的关系: (1)外键约束要定义在从表上, (2)主表则必须具有主键约束或者unique约束,(3)当定义外键约束后,要求外键列数据在主表的主键列存在或为null

建立外键的时候,表的引擎必须是innodb才行,别的是建不起来的

就是在这

创建两个表

设置外键约束的时候,设置在从表中

#外键演示
-- 创建 主表 my_class
CREATE TABLE my_class(
id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '');


-- 创建 从表 my_stu
CREATE TABLE my_stu(
id INT PRIMARY KEY,
`name` VARCHAR(32) NOT NULL DEFAULT '',
class_id INT,
-- 下面指定外键关系
FOREIGN KEY (class_id) REFERENCES my_class(id));

一旦主外键的关系形成了,那么就不能随意删除

但是可以先删除完从表的,当主表中主键没有外键指向他了,那主键的这一行也是可以删除的