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='英文';