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

MySQL表的增删改查（进阶）

1、数据库约束

1.1、约束类型

not null：指示某列不能为 nullunique：某列的每行必须有唯一的值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='英文';