2024年5月6日 mysql 学习笔记

2024年5月6日 mysql 学习笔记

1.字段的别名

语法： as 别名 或者字段名 别名

--通过as 给字段去一个别名
select card as 身份证, name as 姓名, sex as 性别；
select card 身份证, name 姓名, sex 性别；

2.表的别名

select * from students as stu;
select * from students stu;

3.distinct过滤重复记录

select distinct sex, class from students;

4.where子句

5.select语句

6.比较运算符

<,> ,>=,<=， <>

select * from students;
select name from students where sex="女";

7.逻辑运算符

and 条件

select * from students where sex="女" and age <30

or 或

select * from students where sex="女" or class="1班"

not 非

select * from students where not hometown = "天津" 

习

--练习1  查询老家是河北或河南的学生记录 （行）
select * from students where hometown="河南" or hometown = "河北 ";
--练习2：查询class为“1班”，且老家为北京的学生记录
select * from students where class="1班" and hometown ="北京";
--练习3 查询age非30的学生记录
select * from students where not age=30;
select * from students where age != 30;

8. 模糊查询

like %任意多个字符 _任意1个字符

--查询name 中以孙开头的学生记录
select * from students where name like "孙%"；
--询name 中以孙开头，且名字只有一个字的学生记录
select * from students where name like "孙_"；
--查询name 中任意姓，名字为“乔”的学生记录
select * from students where name like "%乔"；
--查询name 中有“白”的学生记录
select * from students where name like "%白%"  

练习

--查询name中为两个字的学生记录
select * from students where name like "__";
--查询name姓“白”且年龄大于30的学生记录
select * from students where name like "白%" and age >30;
--查询studentNo学号以1结尾的学生记录
select * from students where studentNo like "%1";

9.范围查找

in （值,值,值）

between 开始值 and 结束值   连续范围

--查询hometown为北京或上海或广东的学生记录
select * from students where hometown in ("北京"，"上海","广东")；
--查询年龄为25-30的学生记录
select * from students where age>=25 and age <=30;
select * from students where age between 25 and 30;

练习

select * from students where age in (20,25,30) and sex ="女";
select * from students where not age in between 25 and 30;

10.空判断 null

null 不是0，也不是null,代表空

判断空  is null

判断非空 is not null

select * from students where card is null; 
select * from students where card is not null; 

11. update and delete语句可与用到where之后

update 表 set 字段=“”

--修改age为25，且name为“孙尚香”的学生class为"2班"
update students set class ="2班" where age=25 and name ="孙尚香";
--删除class 为1班，且age》30的学生记录
delete from students where class="1班" and age >30;

练习

--修改students表中姓“孙”的同学，班级为1班
update students set class = "1班" where name like "孙%";
--删除年龄在20-30之间的所有男同学
delect from student where (age between 20 and 30) and sex ="男";

12.排序

order by 字段 desc asc

多个条件，有优先级

--查询学生记录，age按照小到大排序
select * from students order by age;
select * from students order by age asc;
--查询学生记录，age按照da到小排序
select * from students order by age desc;
--查询学生记录，age按照小到大排序，年龄相同时，再按studentNo小到大排序
select * from students order by age, studentNo;

** 练习:注意where在order by之前**

--查询所有男同学，class班级从小到大排序，班级相同是，再按studentNo从大到小排序
select * from students where sex ="男" order by class,studentNo desc;

13 聚合函数

1.count(*)

--查询有多少记录
select count(*) from students;
select count(name) from students;
select count(distinct sex) from students;
select count(distinct sex) from students;
--查询女同学的额记录
select count(*) from students where sex="女"；

2.max（值）

select max(age) from students;
select max(age) from students where sex="女";
select max(age) from students where class ="1班";

max 函数不能用在where语句中

3.min(字段名)最小值

select min(age) from students;

4.sum求和

select sum(age) from students;
select sum(age) from students where sex="女";

5.avg (字段值) 平均值

select avg(age) from students;
select avg(age) from students where sex="女";

练习

--所有学生查询最大、最小、平均age
select min(age) from students;
select max(age) from students;
select avg(age) from students;
select min(age),max(age),avg(age) from students where class ="1班";
--查询1班有多少学生
select count(*) from students where class ="1班";
--查询3班中age 小于30岁的同学有几个
select count(*) from students where class ="3班" and age <30;

14.数据分组

1.配合聚合函数使

语法：select` 字段  聚合函数 from 表名 where 条件 group by 字段；

select count(*) from students where sex="男";
select count(*) from students where sex="女";
select sex, count(*) from students group by sex ;

练习

--统计各个班级
select class, min(age),max(age),avg(age),count(*) from students group by class;
--统计各个班级，但不在3班，结果按照班级名称从大到小排序
select class, min(age),max(age),avg(age),count(*) from students where class !="3班" group by class order by class desc;

2.分组后的数据筛选

两种方式，where and having

where 后面不能使用聚合函数，having可以

where是对原始数据进行筛选，having对分组之后的数据进行筛选

--查询男生总数
--先筛选符合条件的记录，再总计记录
select count(*) from students where sex ="男"；
-先分组，再筛选
select sex,count(*) from students group by sex having sex ="男"；
--求班级人数大于3人的班级名字
select class from students group by class having count(*) > 3; 

练习

--查询班级总人数大于2人的班级名称以及对应的总人数
select class,count(*) from students group by class having count(*)>2;
--查询平均年龄大于30的班级名称和班级总人数
select class,count(*) from students group by class having avg(age) >30;

15.显示制定的记录数

1.记录

select * from students where 条件 group by 字段 order by 字段 limit start  count；

--查询年龄最大的同学的name
select name from students order by age desc limit 1;
--查询年龄最小的女同学信息
select * from students where sex ="女" order by age asc limit 1;

2.分页显示

m每页显示多少条记录，n 第n页

(n-1)*m,m 

把计算结果写在limit后面

--每页显示4条记录，第3页的结果
select * from students limit 8,4;
--每页显示4条记录，第2页的结果
select * from students limit 4,4;

已知每页书，求页数

--每页显示5条数据，一共需要所少爷
select count(*) from students limit 

16.连接查询：内连接（交集），左连接（A+）和右连接

内连接

语法1：

select * from 表1 inner join 表2 on 表1.字段=表2.字段 （标准语法）

语法2：

select * from 表1,表2 where 表1.字段=表2.字段 (隐式内连接)

select * from a inner join b on a.id=b.id;
select * from students st inner join scores sc on st.studentNo=sc.studentNo;
select name, courseNo  from students st inner join scores sc on st.studentNo=sc.studentNo;

今天的笔记就到这里了！