1、指定列查询
1.1、查询所有记录
1.2、查询学号、学生名字
select sno,sname from student;
1.3、定义列的别名
select sno as 学号,sname as 学生名字 from student;
+--------+--------------+
| 学号 | 学生名字 |
+--------+--------------+
| 95001 | 洛燕妮 |
| 95002 | 欧阳炎 |
1.4、查询并去重处理
select distinct sno from student;
1.5、查询第5，6条记录
select sno from student limit 4,2;
1.6、查询前10条记录
select sno from student limit 10;
1.7、排序查询
-- asc 升序
-- desc 降序
select * from student order by sno desc;
2、条件查询
2.1、查询操作符
2.2、常见查询方法
2.2.1、=
-- =
select * from student where sno='95001';
2.2.2、>= <=
-- >= <=
select * from student where age>=20 and age<=25;
2.2.3、is not null
-- is not null
select * from student where age is not null;
2.2.4、between and
-- between and
select * from student where age between 18 and 21;
2.2.5、in
-- in
select * from student where sno in('95002','95003');
2.2.6、like
-- like
select * from student where sname like '欧%';
3、regexp运算符
3.1、 regepx常用的查询
-- regexp 以什么结尾
select * from student where sname regexp '升$';
-- regepx 以95、94、93开头
select * from student where sno regexp '^9[543]';
4、统计查询
4.1、时间的运算
-- 增加生日的字段
alter table student add birthday datetime;
-- 插入一条生日的数据
insert into student values('95007','test','男','20','计算机系','2002-01-02 12:30:12');
-- now函数
select now();
+---------------------+
| now() |
+---------------------+
| 2022-11-10 22:54:16 |
+---------------------+
-- year、month、day、hour、minute、second函数
select year(birthday) from student;
-- 计算年龄
select year(now())-year(birthday) from student;
5、聚合函数
MySQL中提供5个聚合函数
avg -- 平均值
max、min -- 最大值、最小值
sum -- 求和
count -- 计数
5.1、avg
-- 平均值
select avg(age) from student;
5.2、max、min
-- 最大值、最小值
select max(age),min(age) from student;
5.3、sum
-- 求和
select sum(age) from student;
5.4、count
-- 计数
select count(*) from student;
6、分组统计
6.1、group by
-- group by 按性别分组
select ssex,count(*) from student group by ssex;
6.2、having
-- having 按部分分组并且把结果大于4取出
select sdept,count(*) from student group by sdept having count(*)>4;
6.3、with rollup
-- with rollup 合计
select sdept,count(*) from student group by sdept with rollup having count(*)>0;
6.4、group_concat
6.4.1、去重，分组统计，列出具体的数量
-- group_concat,去重，分组统计，列出具体的数量
select sdept,count(distinct age) as 去重后部门年龄数,group_concat(distinct age) as 列出去重年龄具体数据 from student group by sdept;
+----------------------------------+--------------------------+--------------------------------+
| sdept | 去重后部门年龄数 | 列出去重年龄具体数据 |
+----------------------------------+--------------------------+--------------------------------+
| 020-电子信息工程系 | 3 | 18,19,21 |
| 学院-020-电子信息工程系 | 1 | 25 |
| 计算机系 | 1 | 20 |
| 软件技术 | 1 | 29 |
+----------------------------------+--------------------------+--------------------------------+
6.4.2、分组统计，列出具体的数量
-- group_concat,分组统计，列出具体的数量
select sdept,count(age) as 去重后部门年龄数,group_concat(age) as 列出去重年龄具体数据 from student group by sdept;
+----------------------------------+--------------------------+--------------------------------+
| sdept | 部门年龄数 | 列出年龄具体数据 |
+----------------------------------+--------------------------+--------------------------------+
| 020-电子信息工程系 | 4 | 19,21,18,21 |
| 学院-020-电子信息工程系 | 1 | 25 |
| 计算机系 | 1 | 20 |
| 软件技术 | 1 | 29 |
+----------------------------------+--------------------------+--------------------------------+
7、多表查询
连接查询分类:inner join[内连接]、outer join【right join、left join、full join】[联合查询]、cross join[外连接]
7.1、测试表
-- 班级表
create table student(
sno varchar(10),
name varchar(20),
departid int
);
insert into student values('1000','张三',1),('1001','李四',2),('1002','王五',3),('1003','老王',4);
-- 部门表
create table department(
departid int primary key,
departedname varchar(20)
);
insert into department values(1,'学生会'),(2,'计算机协会');
7.2、内连接【inner join】默认join
两张表在进行连接时，连接列字段的名称可以不同，但要求必须具有相同数据类型，长度和精度,且表达同一范畴的意义，通常连接列字段一般是数据表的主键和外键。
7.2.1、inner join 内连接
select s.sno,s.name,d.departedname from student as s inner join department as d on s.departid=d.departid;
7.2.2、inner join 内连接 + where
select s.sno,s.name,d.departedname from student as s inner join department as d on s.departid=d.departid where s.name='张三';
7.2.3、inner join + using
-- 当连接条件是由两张表相同名称且类型也相同的字段相连时，可以使用USIN
select s.sno,s.name,d.departedname from student as s inner join department as d using(departid);
7.3、外连接【outer join】
7.3.1、左外连接[left join]
-- left join 右边不匹配，则为null
select * from student as s left join department as d on s.departid=d.departid;
+------+--------+----------+----------+-----------------+
| sno | name | departid | departid | departedname |
+------+--------+----------+----------+-----------------+
| 1000 | 张三 | 1 | 1 | 学生会 |
| 1001 | 李四 | 2 | 2 | 计算机协会 |
| 1002 | 王五 | 3 | NULL | NULL |
| 1003 | 老王 | 4 | NULL | NULL |
+------+--------+----------+----------+-----------------+
7.3.2、右外连接[right join]
-- right join ,左边不匹配，则null
select * from department as d right join student as s on s.departid=d.departid;
+----------+-----------------+------+--------+----------+
| departid | departedname | sno | name | departid |
+----------+-----------------+------+--------+----------+
| 1 | 学生会 | 1000 | 张三 | 1 |
| 2 | 计算机协会 | 1001 | 李四 | 2 |
| NULL | NULL | 1002 | 王五 | 3 |
| NULL | NULL | 1003 | 老王 | 4 |
+----------+-----------------+------+--------+----------+
7.4、完连接[full join]
-- full join ，交叉链接，两边数据相乘
select * from student full join department;
+------+--------+----------+----------+-----------------+
| sno | name | departid | departid | departedname |
+------+--------+----------+----------+-----------------+
| 1000 | 张三 | 1 | 1 | 学生会 |
| 1000 | 张三 | 1 | 2 | 计算机协会 |
| 1001 | 李四 | 2 | 1 | 学生会 |
| 1001 | 李四 | 2 | 2 | 计算机协会 |
| 1002 | 王五 | 3 | 1 | 学生会 |
| 1002 | 王五 | 3 | 2 | 计算机协会 |
| 1003 | 老王 | 4 | 1 | 学生会 |
| 1003 | 老王 | 4 | 2 | 计算机协会 |
+------+--------+----------+----------+-----------------+
7.5、联合查询 union 或 union all
7.5.1、准备测试数据
-- 联合查询的注意事项:
-- 1﹑两个查询的列数目必须相同
-- 2﹑并且对应列的数据类型相互兼容
-- 增加老师表，用于union，演示
create table teacher(
sno varchar(10),
name varchar(20),
departid int
);
insert into student values('8000','张老师',1),('8001','李老师',2);
7.5.2、union
-- 按字段联合查询 union distinct 会去重显示
select sno,name,departid from student
union
select sno,name,departid from teacher;
7.5.3、union all
-- 按所有字段联合查询，不用去重
select * from student
union all
select * from teacher;
8、子查询
8.1、集合查询
8.1.1、in
-- in 包含
select * from student where age in (21,19);
8.1.2、not in
-- not in 不包含
select * from student where age not in (21,19);
8.1.3、any|some
-- any或some 年龄值大于21或19，则查询出来
select * from student where age>any(select distinct age from student where age in (21,19));
8.1.4、all
-- all 年龄的值大于21且19，则查询出来
elect * from student where age>all(select distinct age from student where age in (21,19));
8.1.5、exists
-- exists 查询学生表在课程表有记录的学生
select * from student where exists(select * from score where student.sno=score.sno);
8.2、子查询-增
-- 复制表结构
create table stu_exists like student;
-- 查询结果，并且写入表格中
insert into stu_exists select * from student where sno in(select distinct sno from score);
8.3、子查询-删
-- 删除学生表信息
delete from student where sno in (select distinct sno from score);
8.4、子查询-改
-- 修改成绩表
update score set grade=85 where sno=(select sno from student where sno='95001') and courseid=1;
8.5、子查询-查
-- 查询有成绩的学生信息
select * from student where sno in(select distinct sno from score);
