create table dept(
?
?   did int primary key auto_increment,
?
?   dname varchar(20)
?
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
?
insert into dept values (null,'市场部');
?
insert into dept values (null,'人事部');
?
insert into dept values (null,'教研部');

1.2创建一个员工表

create table employee(
?
?   eid int primary key auto_increment,
?
?   ename varchar(20),
?
?   salary double,
?
?   birthday date,
?
?   sex varchar(10),
?
?   dno int
?
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
?
insert into employee values (null,'张三',8000,'1988-09-01','男',3);
?
insert into employee values (null,'李四',9000,'1988-09-01','男',1);
?
insert into employee values (null,'王五',6000,'1988-09-01','男',2);
?
insert into employee values (null,'赵六',10000,'1988-09-01','男',3);
?
insert into employee values (null,'孙七',10000,'1988-09-01','男',1);

2.多表查询的分类

2.1mysql 表子查询

2.1.1什么是子查询 subquery.sql

子查询是指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询

2.1.2 单行子查询

单行子查询是指只返回一行数据的子查询语句

2.1.3多行子查询

多行子查询指返回多行数据的子查询，使用关键字 in （）

2.1.3.1带in的子查询（在一个查询语句里面套另外一个查询，子查询会返回一个结果给外面的查询）

查询学生生日在91年之后的班级的信息。

select * from classes where cid in (SELECT cno FROM student WHERE birthday > '1991-01-01');

2.1.3.2 xists的子查询

查询学生生日大于91年1月1日，如果记录存在，前面的SQL语句就会执行

select * from classes where exists (SELECT cno FROM student WHERE birthday > '1991-01-01');

2.1.3.3 any的子查询

比较子查询里面的其中一个

SELECT * FROM classes WHERE cid > ANY (SELECT cno FROM student )

2.1.3.4 带all的子查询（比它所以的都要<,>,=）,也可以在子查询使用MAX

SELECT * FROM classes WHERE cid > ALL (SELECT cno FROM student)

2.1.2 多列子查询

查询和张三一样的性别和工号

SELECT * FROM employee 
 WHERE (sex,dno)=(SELECT sex,dno FROM employee
 WHERE ename='张三')AND ename!='张三'

2.2 合并查询

2.3 多表查询之外连接

想要那个表中的完整数据就将那个表放在对应的方向

主要应用与两个表中一些没有完全参与某个列的数据，对照之后就会为空，可以明显看出来

2.3.1外连接

SELECT * FROM classes c LEFT OUTER JOIN student s ON c.cid = s.cno;

2.3.2右外连接

select * from classes c right outer join student s on c.cid = s.cno;

2.4 约束

2.4.1主键约束

2.4.2 not null(非空)

2.4.3 unique(唯一)

2.4.4 foreign key(外键)

向员工表中插入一条记录（没有部门）

insert into employee values (null,'田八',10000,'1988-09-01','男',null);

删除一个人事部

delete from dept where did = 2;

向刚才做的这两个操作（插入一个没有部门的员工，删除一个带有员工的部门）。这种情况都是不应该发生。这个时候需要在多表之间添加外键约束。

在员工表上面添加外键约束

alter table employee add foreign key (dno) references dept(did);

设置外键为非空

alter table employee modify dno int not null;

2.4.5 check

2.4.6 总结练习

CREATE DATABASE shop_db;
-- 商品 goods 
CREATE TABLE goods (
    goods_id INT PRIMARY KEY,
    goods_name VARCHAR(64) 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 '');
    
    -- 客户 customer（客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,身份证 card_Id);
    CREATE TABLE customer(
        customer_id CHAR(8) PRIMARY KEY, -- 程序员自己决定 
        `name` VARCHAR(64) NOT NULL DEFAULT '',
        address VARCHAR(64) NOT NULL DEFAULT '',
        email VARCHAR(64) UNIQUE NOT NULL, 
        sex ENUM('男','女') NOT NULL , -- 这里老师使用的枚举类型, 是生效 
        card_Id CHAR(18));
        
        -- 购买 purchase（购买订单号 order_id，客户号 customer_id,商品号 goods_id,购买数量 nums);
    CREATE TABLE purchase( 
        order_id INT UNSIGNED PRIMARY KEY,
        customer_id CHAR(8) NOT NULL DEFAULT '', -- 外键约束在后
        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)); 
        DESC goods; 
        DESC customer; 
        DESC purchase;

2.5 自增长

在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动的增长，咋么处理？

细节

2.6内连接

2.6.1 显式内连接

select * from classes c inner join student s on c.cid = s.cno;

2.6.2 隐式内连接

SELECT * FROM classes c,student s WHERE c.cid = s.cno;

内连接和外连接的区别

3.表复制

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

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

3.1.1 将一个表的内容迁移到另外一个表中

首先我们先建立一个表

 CREATE TABLE employee_1(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(20),
	salary DOUBLE,
	birthday DATE,
	sex VARCHAR(10),
	dno INT
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

开始复制

INSERT INTO employee_1(eid,ename,salary,birthday,sex,dno)
        SELECT * FROM employee;--当然也可以只复制个别行列

查询结果

SELECT * FROM employee_1;

3.1.2 将一个表的结构迁移到另外一个表中

CREATE TABLE employee_2 LIKE employee_1;-- 这个语句是将结构给另外一个表

3.1.3自我复制（倍数增加）

数据会产生倍数增加

INSERT INTO employee_1
		SELECT * FROM employee_1;

3.2 去重

考虑去重 employee_1 的记录

/*

思路

(1) 先创建一张临时表 employee , 该表的结构和 employee_1一样

(2) 把 employee的记录 通过 distinct 关键字 处理后 把记录复制到 employee_1

(3) 清除掉 employee_1 记录

(4) 把 employee表的记录复制到employee_1

(5) drop 掉 临时表 employee

*/

-- (1) 先创建一张临时表 employee, 该表的结构和 employee_1 一样

create table employee like employee_1


-- (2) 把 employee 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp

insert into employee 

select distinct * fromemployee_1; 


-- (3) 清除掉 employee_1记录

delete from employee_1; 


-- (4) 把 employee 表的记录复制到 employee_1

insert into employee_1 

select * from employee; 


-- (5) drop 掉 临时表 employee

drop table employee; 

select * from employee_1;