[MySQL] 零基础学MySQL 07

本博客是【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门视频的学习笔记

表复制

自我复制数据（蠕虫复制）

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

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

思考题：如何删除掉一张表的重复记录？

思路

1. 先创建一张临时表temp，该表的结构和mytable02相同
2. 把mytable02的记录通过distinct关键字的处理后，把记录复制到temp表中
3. 删除mytable02的记录
4. 把temp表的记录复制到mytable02中
5. drop掉temp表

CREATE TABLE temp LIKE mytable02;
INSERT INTO temp
	SELECT DISTINCT * FROM mytable02;

DELETE FROM mytable02;
INSERT INTO mytable02
	SELECT * FROM temp;
DROP TABLE temp;

SELECT * FROM mytable02;

这个temp表就像交换函数中的临时变量一样。

合并查询

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

1.union all
该操作符用于取得两个结果集的并集。当使用该操作符时，不会取消重复行。

SELECT ename,sal,job FROM emp WHERE sal>2500
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

#合并上面两条查询结果
#union all就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER'
#union会自动去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

MySQL表外连接

1.我们前面学习的查询，是利用where子句对两张表或者多张表形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的就不显示。

2.比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。

3.使用学习过的多表查询语句，看看效果如何。

SELECT dname,ename,job
	FROM emp,dept
	WHERE emp.deptno=dept.deptno
	ORDER BY dname
#显示出的只有三个部门，但实际上我们有四个部门

外连接

1.左外连接（如果左侧的表和右边的表即使没有匹配的记录，也会完全显示左边的表的记录，我们就说是左外连接）

2.右外连接（如果右侧的表和左边的表即使没有匹配的记录，也会完全显示右边的表的记录，我们就说是右外连接）

#提前准备的表
#创建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;

3.使用左外连接（显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号，成绩显示为空）

#这样只要jack和tom不符合条件
SELECT `name`,stu.id,grade
	FROM stu,exam
	WHERE stu.id=exam.id;

#改成左外连接
SELECT `name`,stu.id,grade
	FROM stu LEFT JOIN exam
	ON stu.id=exam.id;	

左外连接基本语法：
select … from 表1 left join 表2 on 条件
表1就是左表，表2就是右表

4.使用右外连接（显示所有成绩，如果没有名字匹配，显示空）

#使用右外连接
#即使右边的表(exam)和左表没有匹配记录，也会把右表的记录显示出来
SELECT `name`,stu.id,grade
	FROM stu RIGHT JOIN exam
	ON stu.id=exam.id;

练习

列出部门名称和这些部门的员工的信息（名字和工作），同时列出那些没有员工的部门名。

1.使用左外连接实现
2.使用右外连接实现

#使用左外连接
SELECT dname,ename,job
	FROM dept LEFT JOIN emp
	ON dept.deptno=emp.deptno
	ORDER BY ename;
#使用右外连接
SELECT dname,ename,job
	FROM emp RIGHT JOIN dept
	ON dept.deptno=emp.deptno
	ORDER BY ename;

在实际的开发中，我们绝大多数情况下使用的是前面学过的连接。

约束

约束用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括：not null、unique、primary key、foreign key和check五种。

primary key（主键）

字段名 字段类型 primary key

用于唯一的标识表行的数据，当定义主键约束后， 该列不能重复

#primary key
CREATE TABLE t17
	#表示id这列是主键
	(id INT PRIMARY KEY,
	`name` VARCHAR(32),
	email VARCHAR(32));
#主键列的值是不可以重复的
INSERT INTO t17
	VALUES(1,'jack','jack@sohu.com');
INSERT INTO t17
	VALUES(2,'tom','tom@sohu.com');
#下面这条语句会报错
INSERT INTO t17
	VALUES(1,'lhq','lhq@sohu.com');
SELECT * FROM t17;

主键的细节

1.primary key不能重复而且不能为null

2.一张表中最多只能有一个主键，但可以是复合主键

/*错误的
CREATE TABLE t18
	#表示id这列是主键
	(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@qq.com');
INSERT INTO t18 VALUES(1,'jack','jack@qq.com');
INSERT INTO t18 VALUES(2,'tom','tom@qq.com');
#下面这条语句是错误的
INSERT INTO t18 VALUES(1,'tom','abc@qq.com');
SELECT * FROM t18;

3.主键的指定方式有两种
  (1)直接在字段名后指定：字段名 primary key
  (2)在表定义最后写primary key(列名)

CREATE TABLE t19
	(id INT,
	`name` VARCHAR(32) PRIMARY KEY,
	email VARCHAR(32));

CREATE TABLE t20
	(id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(`name`));

4.使用desc 表名，可以看到primary key的情况

DESC t20;

在实际开发中，每个表往往都会设计一个主键。

not null

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

字段名 字段类型 not null

unique（唯一）

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

CREATE TABLE t21
	(id INT UNIQUE,
	`name` VARCHAR(32),
	email VARCHAR(32));
INSERT INTO t21 VALUES(1,'jack','jack@qq.com');
#下面的语句是错误的
INSERT INTO t21 VALUES(1,'tom','tom@qq.com'); 

unique使用细节
1.如果没有指定not null，则unique字段可以有多个null

INSERT INTO t21 VALUES(NULL,'tom','tom@qq.com'); 
#空值可以有多个
INSERT INTO t21 VALUES(NULL,'tom','tom@qq.com'); 
SELECT * FROM t21;

如果一个列（字段）是unique not null，则使用效果类似于primary key

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

CREATE TABLE t22
	(id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE,
	email VARCHAR(32));

foreign key（外键）

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

图片来自【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门

学生表（从表），班级表（主表）

把从表的class_id作为外键约束后，如果添加一个学生的班级编号为300，300号在主表里并不存在，那么添加就会失败。
如果外键约束已经形成了，例如学生表的jack的class_id 200和班级表的id 200已经形成外键约束，那么直接删掉班级表的200编号一行会失败。除非先删掉学生表的对应位置，再删掉班级表的对应位置。

外键使用及细节

FOREIGN KEY (本表字段名) REFERENCES 主表名(主表名或unique字段名)

如果主表的字段不是主键或者unique的，那么就不唯一，如果有两个相同的id，外键就不知道指向哪一个。
所以外键指向另一个表的列时，要求另一个表的列必须是主键或者unique的。

#外键约束
#先创建主表 班级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));
#测试数据
INSERT INTO my_class VALUES(100,'java'),(200,'web');
SELECT * FROM my_class;

INSERT INTO my_stu VALUES(1,'tom',100);
INSERT INTO my_stu VALUES(2,'jack',200);
#下面的语句会报错 因为300号班级在主表里不存在
INSERT INTO my_stu VALUES(3,'lhq',300);
SELECT * FROM my_stu;

细节说明

1. 外键指向的表的字段，要求是主键或者unique
2. 表的类型是innodb，这样的表才支持外键
3. 外键字段的类型要跟主键字段的类型一致（长度可以不同）
4. 外键字段的值，必须在主键字段中出现过，或者为null（前提是外键字段允许为null）
5. 一旦建立主外键的关系，数据就不能随意删除了

check

用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000-2000之间。如果不在1000-2000之间就会提示出错。

oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

#check的使用
CREATE TABLE t23
	(id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN('man','woman')),
	sal DOUBLE CHECK(sal > 1000 AND sal <2000)
	);
#添加数据 没有真的生效
INSERT INTO t23
	VALUES(1,'jack','mmid',1);
SELECT * FROM t23;

商店售货系统表设计案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：
商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商provider）
客户customer（客户号customer_id，姓名name，住址address，电邮email，性别sex，身份证card_id）
购买purchase（购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums）

1.建表，在定义中要求声明[进行合理设计]
（1）每个表的主外键
（2）客户的姓名不能为空值
（3）电邮不能够重复
（4）客户的性别只能是男或女
（5）单价 unitprice 在1.0-9999.99之间（check）

#shop_db

CREATE DATABASE shop_db;
#商品表
CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 
		CHECK(unitprice>= 1.0 AND unitprice<=9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT '' 
	);
#客户表
CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(64) NOT NULL DEFAULT '' ,
	address VARCHAR(64) NOT NULL DEFAULT '' ,
	email VARCHAR(64) UNIQUE NOT NULL DEFAULT '' ,
	sex ENUM('man','woman') NOT NULL ,#这里使用的是枚举类型，是生效的
	card_id CHAR(18) UNIQUE
	);
#购买表(订单表) 从表
CREATE TABLE purchase(
	order_id INT UNSIGNED PRIMARY KEY,
	customer_id INT NOT NULL,
	#外键约束在后
	goods_id INT NOT NULL DEFAULT 0,
	#外键约束在后
	nums INT NOT NULL DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
	);