《SQL必知必会》读书笔记，30分钟入门SQL！（二）

2023-10-02 13:35:34

数据过滤

# 找到学号为1的学生
select * from student where number = 1;
# 找到学号为在 [1, 10] 的学生(闭区间)
select * from student where number between 1 and 10;
# 找到未设置电子邮箱的学生
# 注意不能使用 =
select * from student where email is null;
# 找到一班中大于23岁的学生
select * from student where class_id = 1 and age > 23;
# 找到一班或者大于23岁的学生
select * from student where class_id = 1 or age > 22;
# 找到一班与二班的学生
select * from student where class_id in (1, 2);
# 找到不是一班二班的学生
select * from student where class_id not in (1, 2);

计算字段

CONCAT

select concat(name, '(', age, ')') as nameWithAge from student;
select concat('hello', 'world') as helloworld;

Math

select age - 18 as relativeAge from student;
select 3 * 4 as n;

更多函数可以查看 API 手册，同时也可以自定义函数(User Define Function)。

可以直接使用 select 调用函数

select now();
select concat('hello', 'world');

数据汇总

聚集函数，一些对数据进行汇总的函数，常见有 COUNT， MIN， MAX， AVG， SUM 五种。

# 统计1班人数
select count(*) from student where class_id = 1;

数据分组

使用 group by 进行数据分组，可以使用聚合函数对分组数据进行汇总，使用 having 对分组数据进行筛选。

# 按照班级进行分组并统计各班人数
select class_id, count(*) from student group by class_id;
# 列出大于三个学生的班级
select class_id, count(*) as cnt from student
group by class_id having cnt > 3;

子查询

# 列出软件工程班级中的学生
select * from student where class_id in (
  select id from class where class_id = '软件工程'
);

关联联接

虽然两个表拥有公共字段便可以创建联接，但是使用外键可以更好地保证数据完整性。比如当对一个学生插入一条不存在的班级的时候，便会插入失败。一般来说，联接比子查询拥有更好的性能。

# 列出软件工程班级中的学生
select * from student, class
where student.class_id = class.id and class.name = '软件工程';

内联接

内联接又叫等值联接。

# 列出软件工程班级中的学生
select * from student
inner join class on student.class_id = class.id
where class.name = '软件工程';

自联接

# 列出与张三同一班级的学生
select * from student s1
inner join student s2 on s1.class_id = s2.class_id
where s1.name = '张三';

外联接

--列出每个学生的班级，弱没有班级则为null
select name, class.name from student
left join class on student.class_id = class.id;

插入数据

可以采用以下方法插入一条数据，不过严重依赖表中列的顺序关系，推荐指定列名插入数据，并且可以插入部分列。

# 插入一条数据
insert into student values(8, '陆小凤', 24, 1, 3);
insert into student(name, age, sex, class_id)
values(9, '花无缺', 25, 1, 3);

修改数据

更新

# 修改张三的班级
update student set class_id = 2 where name = '张三';

删除

# 删除张三的数据
delete from student where name = '张三';
# 删除表中所有数据
delete from student;
# 更快地删除表中所有数据
truncate table student;

创建表与更新表

# 创建学生表
create table student (
  id int(11) not null auto_increment,
  name varchar(50) not null,
  age smallint default 20,
  sex enum('male', 'famale'),
  score tinyint comment '入学成绩',
  class_id int(11),
  createTime timestamp default current_timestamp,
  primary key (id),
  foreign key (class_id) references class (id)
);
# 根据旧表创建新表
create table student_copy as select * from student;
# 删除 age 列
alter table student drop column age;
# 添加 age 列
alter table student add column age smallint;
# 删除学生表
drop table student;

码农公寓