-- 比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
SELECT dname,ename,job
	FROM emp,dept
	WHERE emp.deptno = dept.deptno
	ORDER BY dname  #同时要求显示出那些没有员工的部门。
			#这个做不到，因为没有匹配emp.deptno = dept.deptno的，就不会显示
			#因为前面我们学习的查询，根据关联条件，显示所有匹配的记录，匹配不上的，不显示

#外连接  782
-- 创建stu   783
/*
id name
1 Jack
2 Tom
3 Kity
4 nono
*/
CREATE TABLE stu(
	id INT,
	`name` VARCHAR(32))
INSERT INTO stu 
	VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
SELECT * FROM stu; -- 创建 exam
/*
id grade
1 56
2 76
11 8
*/
CREATE TABLE exam(
	id INT,
	grade INT);
INSERT INTO exam 
	VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;
-- 使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)  783
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

-- 使用右外连接(显示所有成绩，如果没有名字匹配，显示空)
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
-- 改成右外连接
SELECT `name`,stu.id,grade
	FROM stu RIGHT JOIN exam
	ON stu.id = exam.id

-- 使用右外连接(显示所有成绩，如果没有名字匹配，显示空)   783
-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来
-- 改成右外连接
SELECT `name`,stu.id,grade
	FROM stu RIGHT JOIN exam
	ON stu.id = exam.id


-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名。
-- 1.使用左外连接实现
SELECT dname,ename,job
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno
-- 2.使用右外连接实现
SELECT dname,ename,job
	FROM emp RIGHT JOIN dept
	ON dept.deptno = emp.deptno

#主键的使用  784
CREATE TABLE t17(
	id INT PRIMARY KEY,#表示id列是主键
	`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,'lzl','lzl@sohu.com')	#主键列的值是不可以重复的	
SELECT * FROM t17

-- 主键使用的细节讨论  784
-- 1. primary key不能重复而且不能为null。
INSERT INTO t17
	VALUES(NULL,'lzl','lzl@sohu.com')
-- 2.一张表最多只能有一个主键，但可以是复合主键(比如 id+name)
CREATE TABLE t18(
	id INT PRIMARY KEY,#表示id列是主键
	`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`)-- 这里就是复合主键,这就意味着只有id和name同时相同才添加不进去
	);
INSERT INTO t18
	VALUES(1,'tom','tom@sohu.com')
INSERT INTO t18
	VALUES(1,'jack','jack@sohu.com')#可以添加
INSERT INTO t18
	VALUES(1,'jack','xx@sohu.com')#添加失败，违反了复合主键
SELECT * FROM t18
-- 3.主键的指定方式有两种
-- ●直接在字段名后指定:字段名primakry key
CREATE TABLE t19(
	id INT,
	`name` VARCHAR(32)PRIMARY KEY,
	email VARCHAR(32) 
	);
-- ●在表定义最后写primary key(列名);
CREATE TABLE t20(
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(`name`)
	);

-- 4.使用desc表名，可以看到primary key的情况.
DESC t20-- 用来查看t20表的结构，也会显示约束的情况
DESC t18
-- 5.提醒:在实际开发中，每个表往往都会设计一个主键.

#unique的使用  785
CREATE TABLE t21(
	id INT UNIQUE,-- 表示id列是不可重复的
	`name` VARCHAR(32),
	email VARCHAR(32)
	);
INSERT INTO t21
	VALUES(1,'jack','jack@sohu.com')
INSERT INTO t21
	VALUES(1,'tom','tom@sohu.com')#添加失败，违反了unique约束

-- unique使用细节  785
-- 1.如果没有指定not null ,则unique字段可以有多个null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
	VALUES(NULL,'tom','tom@sohu.com') #添加成功,切可以有多个null
SELECT * FROM t21
-- 2.张表可以有多个unique字段
CREATE TABLE t22(
	id INT UNIQUE,-- 表示id列是不可重复的
	`name` VARCHAR(32) UNIQUE,
	email VARCHAR(32)
	);
DESC t22

#外键演示  787
-- 先创建主表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');
INSERT INTO my_class
	VALUES(300, 'php')
	
SELECT* FROM my_class
INSERT INTO my_stu
	VALUES(1,'tom',100)-- 成功
INSERT INTO my_stu
	VALUES(2,'jack',200)-- 成功
INSERT INTO my_stu
	VALUES(3,'lzl',300)
INSERT INTO my_stu
	VALUES(4,'mary',400)  -- 这里会失败...因为 400 班级不存在
SELECT * FROM my_stu

-- 1.外键指向的表的字段，要求是primary key或者是unique
-- 2.表的类型是innodb,这样的表才支持外键
-- 3.外键字段的类型要和主键字段的类型一致(长度可以不同)
-- 4.外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
INSERT INTO my_stu
	VALUES(5,'king',NULL)-- 这个可以因为 外键 没有写 not null
-- 5.一旦建立主外键的关系，数据不能随意删除了.
DELETE FROM my_class
	WHERE id = 100 #不能删除因为形成了外键约束tom指向的，
			#如果非要删除就要先删除tom即和tom形成的外键断掉了，就可以删除了

#演示check的使用  788
-- 提示oracle 和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。
-- 学习 oracle, sql server, 这两个数据库是真的生效.
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','mid',1)#可以添加成功，因为mysql5.7目前还不支持check ,只做语法校验，但不会生效。
SELECT * FROM t23

#使用约束的练习  789
-- 现有一个商店的数据库shop_ _db, 记录客户及其购物情况，由下面三个表组成:
-- 商品goods (商品号goods_ id, 商品名goods_ name, 单价unitprice,商品类别category,供应商provider);
-- 客户customer (客户号customer_ id,姓 名name,住址address,电邮email性别sex,身份证card_ ld);
-- 购买purchase (购买订单号order id,客户号customer_ id,商 品号goods_ _id,购买数量nums);
-- 1 建表，在定义中要求声明[进行合理设计]:
-- (1)每个表的主外键;
-- (2)客户的姓名不能为空值;
-- (3)电邮不能够重复;
-- (4)客户的性别[男女] check 试试枚举..
-- (5)单价unitprice在1.0 - 9999.99之间check
CREATE DATABASE shop_db #创建数据库shop_db

-- 商品表 goods(商品号goods_ id, 商品名goods_ name, 单价unitprice,商品类别category,供应商provider);
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 NULL DEFAULT 0,
	provider VARCHAR(64) NOT NULL DEFAULT'');
-- 客户customer (客户号customer_ id,姓 名name,住址address,电邮email性别sex,身份证card_ ld);
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,-- 这里使用的是枚举，因为check不生效，没啥意思
	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

#演示自增长的使用 790
-- 创建表
CREATE TABLE t24(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT'',
	`name` VARCHAR(32) NOT NULL DEFAULT'')
DESC t24
-- 测试自增长的使用
INSERT INTO t24
	VALUES(NULL,'jack@com','jack');#解释id是自增长的虽然为null但是会自己增加所以一开始的值为1
INSERT INTO t24
	VALUES(NULL,'tom@com','tom');#tom的id为2
	
-- 另外一种方式写法
INSERT INTO t24(email,`name`)
	VALUES('lzl@com','lzl')#id为3
SELECT * FROM t24

-- 自增长的细节  790
-- 1. 一般来说自增长是和primary key配合使用的
-- 2.自增长也可以单独使用[但是需要配合一个unique]
-- 3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用
-- 4.自增长默认从1开始，你也可以通过如下命令修改alter table表名auto increment =新的开始值;
-- 修改自增长的默认开始值
CREATE TABLE t25(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT'',
	`name` VARCHAR(32) NOT NULL DEFAULT'')
ALTER TABLE t25 AUTO_INCREMENT = 100;#这里修改了t25表自增长的默认开始值为100
INSERT INTO t25(id,email,`name`)
	VALUES(NULL,'mary@com','mary')
SELECT * FROM t25

-- 5.如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准，如果指定了自增长
-- ，一般来说，就按照自增长的规则来添加数据
INSERT INTO t25(id,email,`name`)
	VALUES(666,'king@com','king')#这里我们制定了id值为666，所以就以666显示，自增长也开始以666增长了
INSERT INTO t25(id,email,`name`)
	VALUES(NULL,'nono@com','nono')#id为667
SELECT * FROM t25