MySQL-表的复制与去重、合并查询、外连接、约束和自增长

蠕虫复制

为了对sql语句进行测试，需要海量数据时可以使用蠕虫复制为表创建海量数据。

测试代码

# 表的复制
CREATE TABLE my_table(
		id INT UNSIGNED NOT NULL DEFAULT 0,
		`name` VARCHAR(32) NOT NULL DEFAULT ' ',
		salary DECIMAL(15,4) UNSIGNED NOT NULL DEFAULT 0,
		job VARCHAR(64) NOT NULL DEFAULT ' ',
		deptno INT UNSIGNED NOT NULL);
DESC my_table;

-- 表的自我复制
-- 1.把emp表复制到my_table 表中
INSERT INTO my_table 
		(id, `name`,salary, job, deptno)
		SELECT empno, ename, sal, job, deptno FROM emp;
SELECT * FROM my_table;
-- 2.自我复制
INSERT INTO my_table
	SELECT * FROM my_table;
SELECT * FROM my_table;
SELECT COUNT(*) FROM my_table;

表中数据去重

# 表的复制
CREATE TABLE my_table(
		id INT UNSIGNED NOT NULL DEFAULT 0,
		`name` VARCHAR(32) NOT NULL DEFAULT ' ',
		salary DECIMAL(15,4) UNSIGNED NOT NULL DEFAULT 0,
		job VARCHAR(64) NOT NULL DEFAULT ' ',
		deptno INT UNSIGNED NOT NULL);
DESC my_table;

-- 表的自我复制
-- 1.把emp表复制到my_table 表中
INSERT INTO my_table 
		(id, `name`,salary, job, deptno)
		SELECT empno, ename, sal, job, deptno FROM emp;
SELECT * FROM my_table;
-- 2.自我复制
INSERT INTO my_table
	SELECT * FROM my_table;
SELECT * FROM my_table;
SELECT COUNT(*) FROM my_table;

-- 表中数据去重
CREATE TABLE my_table01 LIKE emp;
DESC my_table01;
INSERT INTO my_table01 
		SELECT * FROM emp;
SELECT * FROM my_table01;

-- 模拟去重步骤
-- 1.创建新表，与原表结构相同
CREATE TABLE my_table02 LIKE my_table01;
-- 2.把原表的数据通过distinct关键字处理后把记录复制到新表中
INSERT INTO my_table02
		SELECT DISTINCT * FROM my_table01;
-- 3.清楚原表中的数据
DELETE FROM my_table01;
-- 4.把新表中的数据复制到原表中
INSERT INTO my_table01
		SELECT * FROM my_table02;
-- 5.drop掉临时的新表 
DROP TABLE my_table02;

SELECT * FROM my_table01;

合并查询

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

2）union操作符用于取得两个结果集的并集，使用该操作符会取消重复行。

# 合并查询
SELECT ename, sal, job FROM emp
		WHERE sal > 2000
		
SELECT ename, sal, job FROM emp
		WHERE job = 'MANAGER';
		
-- union all 将两个查询结果合并（不会去重)
SELECT ename, sal, job FROM emp WHERE sal > 2000 
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
		
-- union 将两个查询结果合并（去重)。
SELECT ename, sal, job FROM emp WHERE sal > 2000 
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';

外连接

1）左外连接（左侧的表完全显示称为左外连接）

SELECT ... FROM 左表名 LEFT JOIN 右表名
        ON 条件

2）右外连接（右侧的表完全显示称为右外连接）

SELECT ... FROM 左表名 RIGHT JOIN 右表名
        ON 条件

# 外连接
-- 查询出部门名称和部门员工名称和工作，同时查询出没有员工的部门
SELECT dname, ename, job FROM dept, emp
		WHERE dept.`deptno` = emp.`deptno`
		ORDER BY dname;
		
#创建学生表和学生成绩表
CREATE TABLE stu(
		id INT,
		`name` VARCHAR(32));
CREATE TABLE exam(
		id INT,
		grade DECIMAL(4,2));
		
INSERT INTO stu VALUES(1,'jack'), (2,'tom'), (3,'kity'), (4, 'nono');
INSERT INTO exam VALUES (1,56), (1,76), (11,8);
SELECT * FROM stu;
SELECT * FROM exam;

-- 使用左连接查询所有人的成绩，如果没有成绩也要显示该学生的姓名和id
-- 子查询
SELECT stu.id, `name`, grade FROM stu,exam
		WHERE stu.`id` = exam.`id`;
-- 左外连接
SELECT stu.`id`, `name`, grade FROM stu LEFT JOIN exam
		ON stu.`id` = exam.`id`;
-- 右外连接(显示所有成绩，如果没有姓名则显示空)
SELECT exam.`id`, `name`, grade FROM stu RIGHT JOIN exam  -- stu.id 无法显示 exam表中的独有的id
		ON stu.`id` = exam.`id`;
		
-- 列出部门名称和这些部门员工信息（名字和工作），同时列出那些没有员工的部门名称
-- 右连接
SELECT dname, ename, job FROM emp RIGHT JOIN dept
		ON emp.`deptno` = dept.`deptno`;
-- 左连接
SELECT dname, ename, job FROM dept LEFT JOIN emp
		ON emp.`deptno` = dept.`deptno`;

MySQL约束

基本介绍

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

primary key(主键约束)

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

字段名 字段类型 primary key

细节说明

1）primary key不能重复而且不能为null；

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

3）主键的指定方式有两种：

        ①直接在字段名后指定：字段名 primary key

        ②在表定义最后写 primary key(列名)；

4）查看表结构（desc 表名），可以看到primary key 的情况；

5）在实际开发中每个表往往都有主键。

# 主键使用

CREATE TABLE people(
		id INT PRIMARY KEY,		-- 表示id为主键，主键是不能重复的
		`name` VARCHAR(32),
		email VARCHAR(64));
		
DESC people;

-- 向表中添加数据
INSERT INTO people VALUES
		(1,'tom','tom@outlook.com');
SELECT * FROM people;
INSERT INTO people VALUES
		(2,'jack','jack@outlook.com');
SELECT * FROM people;
-- INSERT INTO people VALUES
--		(1,'pero','pero@outlook.com');	-- 报错：Duplicate entry '1' for key 'PRIMARY'
-- INSERT INTO people VALUES
--		(null,'pero','pero@outlook.com');	-- 报错:Column 'id' cannot be null
-- 定义为主键的列数据不可以重复

-- CREATE TABLE num(
-- 		id INT PRIMARY KEY,		-- 表示id为主键，主键是不能重复的
-- 		-- 报错：Multiple primary key defined（一张表最多只能有一个主键）
-- 		`name` VARCHAR(32) primary key,  
-- 		email VARCHAR(64));

-- 复合主键
CREATE TABLE peo(
		id INT,
		`name` VARCHAR(32),
		email VARCHAR(64),
		PRIMARY KEY(id, `name`));		-- 在表定义后指定主键
DESC peo;	-- 可以显示约束情况

INSERT INTO peo VALUES
		(1,'tom','tom@outlook.com');
SELECT * FROM peo;
INSERT INTO peo VALUES
		(1,'jack','jack@outlook.com');
SELECT * FROM peo;
-- 报错：Duplicate entry '1-tom' for key 'PRIMARY'（当id与name都相同时，违反了主键原则）		
-- INSERT INTO peo VALUES
-- 		(1,'tom','tomplus@outlook.com');
-- SELECT * FROM peo;

not null（非空约束）

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

字段名 字段类型 not null

unique（唯一约束）

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

字段名 字段类型 unique

细节说明

1）如果没有指定not null，则unique字段可以有多个null

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

# unique的使用
CREATE TABLE t5(
		id INT UNIQUE,	-- 表示id列是不可以重复的
		`name` VARCHAR(32),
		email VARCHAR(64));

SELECT * FROM t5;

INSERT INTO t5 VALUES (1,'tom','tom@outlook.com');

-- 报错：Duplicate entry '1' for key 'id' ，违反unique约束
INSERT INTO t5 VALUES (1,'jack','jack@outlook.com');

-- 如果没有指定not null，则unique字段可以有多个null
INSERT INTO t5 VALUES (NULL,'lucy','lucy@outlook.com');
INSERT INTO t5 VALUES (NULL,'smith','smith@outlook.com');
INSERT INTO t5 VALUES (NULL,'jery','jery@outlook.com');
SELECT * FROM t5;
-- 如果一个列是unique not null 使用效果类似 primary key

-- 一张表可以有多个unique字段
CREATE TABLE t6(
		id INT UNIQUE,	
		`name` VARCHAR(32) UNIQUE,
		email VARCHAR(64) UNIQUE);
DESC t6;

foreign key（外键约束）

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

FOREIGN KEY （本表字段名） REFERENCES 主表名 （主键名或unique字段名）

细节说明

1）外键指向的表的字段，要求是primary key或者是unique；

2）表的类型是innodb，这样的表才支持外键；

3）外键字段的类型要和主键字段的类型一致（长度可以不同）；

4）外键字段的值必须在主键字段中出现过，或者为null（前提是外键字段允许为null）；

5）一旦建立主外键的关系，数据不能随意删除了。

# 外键约束

-- 1.先创建主表 class表
CREATE TABLE class(
		class_id INT PRIMARY KEY,
		class_name VARCHAR(32) NOT NULL DEFAULT ' ');

CREATE TABLE students(
		stu_id INT PRIMARY KEY,
		stu_name VARCHAR(32) NOT NULL DEFAULT ' ',
		class_id INT,		
		-- 指定外键关系
		FOREIGN KEY (class_id) REFERENCES class(class_id));
		
INSERT INTO class VALUES (100,'java'),(200,'web');
SELECT * FROM class;

INSERT INTO students VALUES (1,'tom',100), (2,'jack',200);

-- 报错：Cannot add or update a child row: a foreign key constraint fails 
-- (`pero_db03`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) 
-- REFERENCES `class` (`class_id`))
-- INSERT INTO students VALUES (3,'lucy',300);
INSERT INTO students VALUES (4,'smith',NULL);	-- 外键没有写not null ，该行创建成功
SELECT * FROM students;

-- 一旦建立主外键的关系，数据不能随意删除了。
-- 如果外键没有数据指向主键中的数据，那么主键中的该数据就可以删除
-- 错误代码： 1451 
--  Cannot delete or update a parent row: 
--  a foreign key constraint fails (`pero_db03`.`students`,
-- CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) 
-- REFERENCES `class` (`class_id`))
-- delete from class
-- 		where class_id = 100;

check（检查约束）

用于强制行数据必须满足的条件，例如定义了check约束条件，如果数据不在列值的要求范围内就会提示错误；oracle和sql server均支持check，但是mysql5.7不支持check，只做语法校验但不会生效。

列名 类型 check(条件)

# check约束
CREATE TABLE t7(
		id INT PRIMARY KEY,
		`name` VARCHAR(32),
		sex CHAR(6) CHECK (sex IN('man','woman')), -- 使用枚举：sex enum('男', '女') not null
		sal DOUBLE CHECK (sal >1000 AND sal <2000));
DESC t7;
INSERT INTO t7 VALUES (1,'tom','people',5000);
SELECT * FROM t7;

练习代码

-- 数据库shop_db，记录客户及其购物情况，有三个表；
CREATE DATABASE shop_db;		-- 创建数据库
#创建表【进行合理设计】
-- 要求：1.每个表的主外键；2.客户姓名不能是null值；3.电邮不能重复；
-- 4.客户的性别【男|女】5.单价unitprice在1.0-9999.99之间check。

-- 商品goods（商品号goods_id，商品名goods_name，单价unitprice，商品类别category，供应商peovider）
CREATE TABLE goods(
		goods_id INT,
		goods_name VARCHAR(32),
		unitprice DECIMAL(6,2) CHECK(1.0 < unitprice AND unitprice < 9999.99),
		category VARCHAR(32),
		peovider VARCHAR(32),
		PRIMARY KEY(goods_id, goods_name));

-- 客户customer（客户号customer_id，姓名name，住址address，电邮email，性别sex，身份证号card_id）
CREATE TABLE customer(
		customer_id INT,
		`name` VARCHAR(32),
		address VARCHAR(32),
		email VARCHAR(32) UNIQUE,
		sex VARCHAR(6) CHECK(sex IN('man', 'woman')),  -- 使用枚举：sex enum('男', '女') not null
		card_id CHAR(18) UNIQUE,
		PRIMARY KEY(customer_id, `name`));
		
-- 购买purchase（购买订单号order_id，客户号customer_id，商品号goods_id，购买数量nums)
CREATE TABLE purchase(
		order_id INT,
		customer_id INT,
		goods_id INT,
		nums INT,
		FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
		FOREIGN KEY (goods_id) REFERENCES goods(goods_id));
		
DESC goods;
DESC customer;
DESC purchase;

自增长

在添加数据时表中的某列数据实现自行增长。

字段名 整型 primary key auto_increment

细节说明

1）一般来说自增长是和primary key配合使用的；

2）自增长也可以单独使用（但是需要配合unique）；

3）自增长修饰的字段为整数型的（虽然小数也可以但是很少使用）；

4）自增长默认从1开始，也可以通过命令alter table 表名 auto_increment = new_start_num;

5）如果添加数据时，给自增长字段指定的值，则以指定的值来存储数据。

CREATE TABLE t1(
		id INT PRIMARY KEY AUTO_INCREMENT,	-- 自增长
		`name` VARCHAR(32) NOT NULL DEFAULT ' ',
		email VARCHAR(32) NOT NULL DEFAULT ' ');
DESC t1;

-- 自增长添加数据
INSERT INTO t1 VALUES(NULL,'tom','tom@outlook.com'); -- id指定null，但是存储为1
INSERT INTO t1 VALUES(NULL,'jack','jack@outlook.com'); -- id指定null，但是存储为2
INSERT INTO t1 (`name`, email) VALUES('lucy','lucy@outlook.com'); -- id没有指定，存储为3
SELECT * FROM t1;

-- 修改默认的自增长值
CREATE TABLE t2(
		id INT PRIMARY KEY AUTO_INCREMENT,	-- 自增长
		`name` VARCHAR(32) NOT NULL DEFAULT ' ',
		email VARCHAR(32) NOT NULL DEFAULT ' ');
ALTER TABLE t2 AUTO_INCREMENT = 100;	-- 修改默认的自增长值为100
INSERT INTO t2 VALUES(NULL,'tom','tom@outlook.com');
SELECT * FROM t2;

INSERT INTO t2 VALUES(108, 'jack', 'jack@outlook.com');
SELECT * FROM t2;

-- 之后的值，从最近一次添加的id数值之上再自增长
INSERT INTO t2 VALUES(NULL, 'jack', 'jack@outlook.com');	-- id增长为109	
SELECT * FROM t2;