MySQL数据库(三)

一.MySQL数据库学习(三)

(一).数据表的约束

为防止错误的数据被插入到数据表，MySQL中定义了一些维护数据库完整性的规则；这些规则常称为表的约束。

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KEY	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

以上五种约束条件针对表中字段进行限制从而保证数据表中数据的正确性和唯一性。换句话说，表的约束实际上就是表中数据的限制条件。

1.主键约束

主键约束即primary key用于唯一的标识表中的每一行。被标识为主键的数据在表中是唯一的且其值不能为空。(这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的)
主键约束基本语法：

字段名数据类型 primary key;

//两种方式皆可以
create table student(
id int primary key,
name varchar(20)
);

-------------------------------
create table student01(
id int
name varchar(20),
primary key(id)
);

2.非空约束

非空约束即 NOT NULL指的是字段的值不能为空，基本的语法格式如下所示：

字段名数据类型 NOT NULL;

create table student02(
id int
name varchar(20) not null
);

3.默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插入默认值；其基本的语法格式如下所示：

字段名数据类型 DEFAULT 默认值；

create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

4.唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现，其基本的语法格式如下所示：

字段名数据类型 UNIQUE;

create table student04(
id int,
name varchar(20) unique
);

5.外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束。基本语法如下：

-- 在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)

-- 将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

关于外键约束需要注意的细节

1、从表里的外键通常为主表的主键
2、从表里外键的数据类型必须与主表中主键的数据类型一致
3、主表发生变化时应注意主表与从表的数据一致性问题

(在日常工作或者项目中大多数都使用逻辑外键进行约束)

(二).多表查询

1.内连接

内连接(Inner Join)又称简单连接或自然连接，是一种非常常见的连接查询。内连接使用比较运算符对两个表中的数据进行比较并列出与连接条件匹配的数据行，组合成新的记录。也就是说在内连接查询中只有满足条件的记录才能出现在查询结果中。其语法格式如下：

-- ============================= 内连接 ==========================
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select tb_emp.name,tb_dept.name
from tb_dept,tb_emp where tb_dept.id=tb_emp.dept_id;


-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select tb_emp.name,tb_dept.name
from tb_emp inner join tb_dept on tb_dept.id=tb_emp.dept_id;

-- 起别名
select e.name 员工姓名,d.name 部门名
from tb_emp e inner join tb_dept d on d.id=e.dept_id;

2.外连接

在使用内连接查询时我们发现：返回的结果只包含符合查询条件和连接条件的数据。但是，有时还需要在返回查询结果中不仅包含符合条件的数据，而且还包括左表、右表或两个表中的所有数据，此时我们就需要使用外连接查询。外连接又分为左(外)连接和右(外)连接。其语法格式如下：

(左外连接和右外连接可以相互转换,在日常使用中通常使用左外连接)

-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select e.name,d.name
from tb_emp e left join tb_dept d on e.dept_id=d.id;

-- 左外连接可以和右外连接相互替换

-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select e.name,d.name
from tb_emp e right join tb_dept d on e.dept_id=d.id;

3.子查询

子查询是指一个查询语句嵌套在另一个查询语句内部的查询；该查询语句可以嵌套在一个 SELECT、SELECT…INTO、INSERT…INTO等语句中。在执行查询时，首先会执行子查询中的语句，再将返回的结果作为外层查询的过滤条件。在子査询中通常可以使用比较运算符和IN、EXISTS、ANY、ALL等关键字。

标量子查询(子查询返回的值单行单列)

-- ========================= 子查询 ================================
-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息

select *
from tb_emp where dept_id=(select id from tb_dept where name='教研部');

-- B. 查询在 "方东白" 入职之后的员工信息
select *
from tb_emp where entrydate>(select entrydate from tb_emp where tb_emp.name='方东白');

列子查询(子查询返回的值多行单列)

-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
select *
from tb_emp where dept_id in (select id from tb_dept where tb_emp.name='教研部'or name='咨询部');

行子查询(子查询返回的值单行多列)

-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
select *
from tb_emp where entrydate=(select entrydate from tb_emp where name='韦一笑') and job=(select job from tb_emp where name='韦一笑');

select *
from tb_emp where (entrydate,job)=(select entrydate,job from tb_emp where name='韦一笑') ;

表子查询(子查询返回的值是一个临时表)

-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
select e.*,d.name
from (select * from tb_emp where entrydate>'2006-01-01') e,tb_dept d where e.dept_id=d.id;
*/

案例练习:

-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
-- 表:dish,category
-- SQL:
-- 内连接
select d.name 菜品名称, d.price 菜品价格, c.name 菜品的分类名称
from dish d,
     category c
where d.category_id = c.id
  and d.price < 10;

-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为'起售'的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
-- 表:dish,category
-- SQL:
-- 外连接
select d.name 菜品名称, d.price 菜品价格, c.name 菜品分类名称
from dish d
         left join category c on d.category_id = c.id
where d.price >= 10
  and d.price <= 50
  and d.status = 1;

-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
-- 表:dish,category
-- SQL:
-- 分组查询和聚合函数
select c.name 分类名称, max(d.price) 最贵菜品价格
from dish d,
     category c
where category_id = c.id
group by c.name;

-- 4. 查询各个分类下 状态为 '起售' , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
-- 表:dish,category
-- SQL:
-- 分组查询和聚合函数
select c.name 菜品分类, count(*) 菜品数量
from dish d,
     category c
where category_id = c.id
  and d.status = 1
group by c.name
having count(*) >= 3;

-- 5. 查询出 "商务套餐A" 中包含了哪些菜品 （展示出套餐名称、价格, 包含的菜品名称、价格、份数）.
-- 表:dish,category,setmeal_dish
-- SQL:
-- 内连接
select s.name 套餐名称, d.name 菜品名称, d.price 菜品价格, sd.copies 菜品份数
from setmeal s,
     dish d,
     chat.setmeal_dish sd
where s.id = sd.setmeal_id
  and d.id = sd.dish_id
  and s.name = '商务套餐A';

-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
-- 表:dish
-- SQL:
-- 子查询
select d.name 菜品名称, d.price 菜品价格
from dish d
where d.price < (select avg(price) from dish);