1. mysql 表外连接 782
1.1 提出一个问题
1.前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛
根据关联条件，显示所有匹配的记录，匹配不上的，不显示
2.比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
3. 使用我们学习过的多表查询的SQL,看看效果如何?
代码在E:\java学习\初级\course160\db_
outer
-- 比如:列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。
SELECT dname,ename,job
FROM emp,dept
WHERE emp.deptno = dept.deptno
ORDER BY dname #同时要求显示出那些没有员工的部门。
#这个做不到，因为没有匹配emp.deptno = dept.deptno的，就不会显示
#因为前面我们学习的查询，根据关联条件，显示所有匹配的记录，匹配不上的，不显示1.2 外连接 782-783
1. 左外连接(如果左侧的表完全 显示我们就说是左外连接)
2. 右外连接(如果右侧的表完全显示我们就说是右外连接)为了讲清楚，我们
1.2.1 举例说明 783
使用左连接(显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)
语法
select .. from 表1 left join 表2
on条件[表1:就是左表 表2:就是右表]
使用右外连接(显示所有成绩，如果没有名字匹配，显示空)
语法
select .. from表1 right join 表2
on条件[表1:就是左表 表2:就是右表]
代码在E:\java学习\初级\course160\db_
outer
#外连接 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.id1.3 练习
列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名。
1.使用左外连接实现
2.使用右外连接实现
小结:在实际的开发中， 我们绝大多数情况下使用的是前面学过的连接
代码在E:\java学习\初级\course160\db_
outer
-- 使用右外连接(显示所有成绩，如果没有名字匹配，显示空) 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.deptno2. Mysql约束 784
2.1 基本介绍
约束用于确保数据库的数据满足特定的商业规则。在mysq|中，约束包括: not null、 unique,
primary key,foreign key,和check五种.
2.2 primary key(主键)-基本使用 784
用于唯一的标示表行的数据，当定义主键约束后，该列不能重复
代码在E:\java学习\初级\course160\db_
primary
#主键的使用 784
CREATE TABLE t17(
id INT PRIMARY KEY,#表示id列是主键
`name` VARCHAR(32),
email VARCHAR(32))
-- 主键列的值是不可以重复的
INSERT INTO t17
VALUES(1,'jack','[email protected]')
INSERT INTO t17
VALUES(2,'tom','[email protected]')
INSERT INTO t17
VALUES(1,'lzl','[email protected]') #主键列的值是不可以重复的
SELECT * FROM t17
2.2.1 primary key(主键)-细节说明
1. primary key不能重复而且不能为null。
2.一张表最多只能有一个主键，但可以是复合主键
3.主键的指定方式有两种
●直接在字段名后指定:字段名primakry key
●在表定义最后写primary key(列名);
4.使用desc表名，可以看到primary key的情况.
5.提醒:在实际开发中，每个表往往都会设计一个主键.
代码在E:\java学习\初级\course160\db_
primary
-- 主键使用的细节讨论 784
-- 1. primary key不能重复而且不能为null。
INSERT INTO t17
VALUES(NULL,'lzl','[email protected]')
-- 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','[email protected]')
INSERT INTO t18
VALUES(1,'jack','[email protected]')#可以添加
INSERT INTO t18
VALUES(1,'jack','[email protected]')#添加失败，违反了复合主键
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.提醒:在实际开发中，每个表往往都会设计一个主键.2.3 not null(非空) 785
如果在列上定义了not null,那么当插入数据时，必须为列提供数据。
2.4 unique(唯一)
当定义了唯一约束后，该列值是不能重复的。
2.4.1 unique 细节(注意) 785
1.如果没有指定not null ,则unique字段可以有多个null
2.张表可以有多个unique字段
代码在E:\java学习\初级\course160\db_
unique
#unique的使用 785
CREATE TABLE t21(
id INT UNIQUE,-- 表示id列是不可重复的
`name` VARCHAR(32),
email VARCHAR(32)
);
INSERT INTO t21
VALUES(1,'jack','[email protected]')
INSERT INTO t21
VALUES(1,'tom','[email protected]')#添加失败，违反了unique约束
-- unique使用细节 785
-- 1.如果没有指定not null ,则unique字段可以有多个null
-- 如果一个列(字段)， 是 unique not null 使用效果类似 primary key
INSERT INTO t21
VALUES(NULL,'tom','[email protected]') #添加成功,切可以有多个null
SELECT * FROM t21
-- 2.张表可以有多个unique字段
CREATE TABLE t22(
id INT UNIQUE,-- 表示id列是不可重复的
`name` VARCHAR(32) UNIQUE,
email VARCHAR(32)
);
DESC t223. foreign key(外键) 786
3.1 基本介绍 786
用于定义主表和从表之间的关系:外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null (学生/班级图示)
代码在E:\java学习\初级\course161\db_
foreign
#外键演示 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_stu3.2 foreign key(外键)-细节说明(创建小表演示) 787
1.外键指向的表的字段，要求是primary key或者是unique
2.表的类型是innodb,这样的表才支持外键
3.外键字段的类型要和主键字段的类型一致(长度可以不同)
4.外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
5.一旦建立主外键的关系，数据不能随意删除了.
代码在E:\java学习\初级\course161\db_
foreign
-- 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形成的外键断掉了，就可以删除了4. check 788
4.1 基本介绍
用于强制行数据必须满足的条件,假定在sal列，上定义了check约束,并要求sal列值在1000 ~ 2000之间如果不再1 000 ~ 2000之间就会提示出错。
提示oracle 和sql server均支持check ,但是mysql5.7目前还不支持check ,只做语法校验，但不会生效。
在mysq|中实现check的功能，一般是在程序中控制，或者通过触发器完成。
代码在E:\java学习\初级\course161\db_
check
#演示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 t235. 商店售货系统表设计案例 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
代码在E:\java学习\初级\course161\db_
shop_db
#使用约束的练习 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 purchase6. 自增长 790
6.1 自增长基本介绍 一个问题
在某张表中，存在一个id列(整数),我们希望在添加记录的时候该列从1开始，自动的增长，怎么处理? increment.sql
代码在E:\java学习\初级\course161\db_
increment
#演示自增长的使用 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 t246.2 自增长使用细节 790
1. 一般来说自增长是和primary key配合使用的
2.自增长也可以单独使用[但是需要配合一个unique]
3.自增长修饰的字段为整数型的(虽然小数也可以但是非常非常少这样使用
4.自增长默认从1开始，你也可以通过如下命令修改alter table表名auto increment =新的开始值;
5.如果你添加数据时，给自增长字段(列)指定的有值，则以指定的值为准如果指定了自增长，一般来说，就按照自增长的规则来添加数据
代码在E:\java学习\初级\course161\db_
increment
-- 自增长的细节 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