1.基于这个ER模型，回答下面的问题

student：sno，sname，sage，ssex
sc: sno,cno,score
course: cno,cname,tno
teacher: tno,tname

01 统计zhang3，学习了几门课？

select student.sno,student.sname,count(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cnoe.cno where student.sname='zhang3' group by student.sno;

02 查询zhang3，学习的课程名称有哪些？

select student.sno,student.sname,group_concat(course.cname) from student join sc on student.sno=sc.sno join course on sc.cno=course.cno where student.sname='zhang3' group by student.sno;

03 查询xiaoA老师教的学生名？

select teacher.tno,teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno join student on sc.sno=student.sno where teacher.tname='xiaoA' group by teacher.tno;

04 查询xiaoA老师教课程的平均分数？

select teacher.tno,teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc..cno where teacher.tname='xiaoA' group by teacher.tno;

05 每位老师所教课程的平均分，并按平均分排序？

select teacher.tno,teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno;

select teacher.tno,teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno order by avg(sc.score); （降序表达）

select teacher.tno,teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tno order by avg(sc.score) desc; （升序表达）

06 查询xiaoA老师教的不及格的学生姓名？

select teacher.tno,teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc oncourse.cno=sc.cno join student on sc.sno=student.sno where teacher.tname='xiaoA' and sc.score<60 group by teacher.tno;

07 查询所有老师所教学生不及格的信息？

select teacher.tno,teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on
course.cno=sc.cno join student on sc.sno=student.sno where sc.score<60 group by teacher.tno;

2. 基础数据库语句

1.数据库服务密码管理

给mysql配置登陆密码，并使⽤新密码进⾏登录数据库

mysqladmin password '123'

还可以进入数据库修改(这里的root是需要修改的库名，localhost是白名单)（适用于mysql8.0）

alter user root@'localhost' identified by '123456';

2.数据库服务⽤⼾管理

创建数据库服务用户信息

create user lcq@'172.16.1.%' identified by '123';

授权⼀个⽹段主机均可远程登录数据库服务，表⽰这个⽹段中192.168.30.1~192.168.30.254均可访问

create user root@'192.168.30.0/255.255.255.0' identified by '123';

在数据库中识别默认掩码就是/24，其他掩码信息需要具体写明

create user root@'192.168.30.0/24' identified by '123';

查询数据库服务⽤⼾信息
select user,host,authentication_string from mysql.user;

查看当前登录系统的⽤⼾信息：
select user();

删除数据库服务⽤⼾信息
drop user 'lcq01'@'172.16.1.%';

修改数据库服务⽤⼾密码
alter user root@'localhost' identified by '123';