MySQL表的增删改查（进阶）（4）

MySQL表的增删改查（进阶）

1、数据库约束

1.1、约束类型

• not null：指示某列不能为 null
• unique：某列的每行必须有唯一的值
• default：没给值时为此默认值
• primary key：not null 和 unique 的结合。确保某列（或两个列多个列的集合）有唯一标识。有助于更容易更快速地找到表中的一个特定的记录。
• foregin key：保证一个表中的数据匹配另一个表中的值的参照完整性。
• check：保证列中的值符合指定的条件。对于MySQL数据库，对check子句进行分析，但是忽略
  check 子句。

1.2、null ：约束

创建表时，某列不能为 null

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

1.3、unique： 唯一约束

唯一的不重复的

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

1.4、default：默认约束

指定插入数据时，列为空，默认值为此值

create table student(
	id int unique,
    name varchar(20),
    gender varchar(2) default '女'
);

1.5、primary key：主键约束

• 唯一且不能为 null

• 一般经常和自增长auto_increment来使用。插入数据对应字段不给值时，使用最大值+1

-- 方式一：
create table student(
	id int primary key auto_increment,
    name varchar(20)
);

-- 方式二：
create table student(
	id int,
    name varchar(20),
    primary key(id) auto_increment
);

-- 方式三：
create table student(
	id int,
    name varchar(20)
);
alter table student add primary key(id) auto_increment;

联合主键

create table student(
	id int,
    name varchar(20),
    gender varchar(2),
    primary key(id,name)
);

删除主键

alter table student drop primary key;

1.6、foreign key：外键约束

外键用于关联其他表的主键或唯一键

foreign key (字段名) references 主表(列) 

-- 方式一：
create table class(
	id int primary key auto_increment,
    name varchar(20)
);
create table student(
	id int primary key auto_increment,
    sn int unique,
    name varchar(20),
    class_id int references class(id)
);

-- 方式二：
create table class(
	id int primary key auto_increment,
    name varchar(20)
);
create table student(
	id int primary key auto_increment,
    sn int unique,
    name varchar(20),
    class_id int,
    foreign key (class_id) references class(id)
);

-- 方式三：
create table class(
	id int primary key auto_increment,
    name varchar(20)
);
create table student(
	id int primary key auto_increment,
    sn int unique,
    name varchar(20),
    class_id int
);
alter table studnet add foreign key (class_id) references class(id);

1.7、check约束

create table test_user (
 id int,
 name varchar(20),
 sex varchar(1),
 check (sex ='男' or sex='女')
);

2、新增

insert into tablename [(column,column...)] select ...

-- 将学生表中的所有数据复制到用户表
insert into user(id,name,gender) select id,name,gender from student;

3、聚合函数

函数	说明
count([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

3.1、count

select count(*) from studnet;
select count(1) from studnet;
select count(name) from student;-- name 为 null 不算

3.2、sum

select sum(math) from score;

3.3、avg

select avg(math) from score;

3.4、max

select max(math) from score;

3.5、min

select min(math) from score;

4、group by 语句

select 中使用group by 语句可以对指定列进行分组查询。需要满足：使用 group by 进行分组查询时，select 指定的字段必须时 ‘分组依据字段’，其他字段若想出现在 select 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1;

select role,salary from emp group by role;-- 错误，
select role,salary from emp group by role,salary; -- 正确（具有相同role和salary字段值的记录放到一组）
select role,avg(salary) from emp group by role; -- 正确

5、having

group by 子句进行分组后，需要对分组结果进行条件过滤，不能使用 where 语句，需要用到 having

-- 显示平均工资低于1500的角色和它的平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) < 1500;

having 和 where 的区别

• 作用的对象不同：where作用于表和视图，having作用于组
• where分组前进行过滤，having 分组后进行过滤
• Where是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用聚合函数
• Having是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用聚合函数。

6、联合查询

6.1、笛卡尔积

select 字段 from studnet1,studnet2;

6.2、内连接

select 字段 from 表1 别名1，表名2 别名2 where 连接条件 and 其他条件
select 字段 from 表1 别名1 join 表名2 别名2 on 连接条件 and 其他条件

6.6、外连接

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

select 字段 from 表1 别名1 left join 表2 别名2 on 连接条件
select 字段 from 表1 别名1 right join 表2 别名2 on 连接条件

6.7、自链接

自连接是指在同一张表连接自身进行查询

select 字段 from 表 别名1 join 表 别名2 on 条件 and 其他条件

6.7、子查询

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

• 单行子查询：返回一行记录的子查询

select * from student where id = (select id from score where name = '张三')

• 多行子查询：返回多行记录的子查询

案例：查询“语文”或“英文”课程的成绩信息

​ 1、[not] in 关键字

-- 使用IN
select * from score where course_id in (select id from course where
name='语文' or name='英文');

-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

​ 2、[NOT] EXISTS关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou
where (name='语文' or name='英文') and cou.id = sco.course_id);

-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

• 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个
  子查询当做一个临时表使用。

6.8、合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致。

• union

  该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

  select * from course where id<3
  union
  select * from course where name='英文';
  

• union all

  该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

  -- 可以看到结果集中出现重复数据Java
  select * from course where id<3
  union all
  select * from course where name='英文';