mysql学习（视图）

1、创建一个视图VIEW1，用于查询所有1990年以后出生的学生的学号、姓名、性别、入学成绩、以及他们所选修的课程号、课程名、任课教师姓名等信息。
CREATE view view1
as
SELECT stu.stuid,stuname,sex,rx_score,couname,teachername
from stu join dep on stu.depid=dep.depid
					join scores on stu.stuid=scores.stuid
					join course on course.couid=scores.couid
where year(stu.birthday)<1990;
2、 要求将视图VIEW1修改为用于查询所有男生的学生的学号、姓名、系名以及他们所选修的课程名、任课教师姓名等信息。
ALTER view view1
as
SELECT stu.stuid,stuname,sex,rx_score,couname,teachername
from stu join dep on stu.depid=dep.depid
					join scores on stu.stuid=scores.stuid
					join course on course.couid=scores.couid
where sex='男';


3、创建一个视图VIEW2，用于查询电话号码以‘189’开头的并且班级中包含‘软件’字符的学生姓名和班级、电话号码。
CREATE view view2
as 
select stuname,class,phone
from stu 
where phone like "133%" and class like '%软件%';


4、向VIEW2插入一条记录（周舟，18软件2班，18998879999）。
INSERT into view2(stuname,class,phone)VALUES('周舟','18软件2班','13398879999');

5、在VIEW2中将刚刚插入的update view2
set phone=13311111111
where stuname='周舟';记录的电话号码修改为18911111111
update view2
set phone=13311111222
where stuname='周舟';

6、在VIEW2中将刚刚插入的记录删除
DELETE from view2
where stuname='周舟 ';

7、删除视图VIEW2
drop view view2;

8、创建视图VIEW3，要求查询出学生的最高入学成绩高于500的系部信息
CREATE VIEW view3
as 
select dep.depid,depname,director
from stu join dep on stu.depid=dep.depid
where rx_score>500;

9、创建视图VIEW4，使其显示每个系、每门课程的平均成绩，结果格式为：
系名     	课程名    	平均成绩 
ALTER VIEW view4
as
select dep.depname 系名,couname as 课程名,avg(score) as 平均分
from stu join  dep on stu.depid=dep.depid
				join scores on stu.stuid=scores.stuid
				join course on course.couid=scores.couid
GROUP BY dep.depname ,course.couid;
10、通过VIEW4查看信息系每门课程的平均成绩。
select *
from view4
where 系名='信息系';
11、创建视图VIEW5，使其显示所选修的每门课程都大于80分的学生姓名 
create view view5
as 
select stu.stuname
from stu join scores on stu.stuid=scores.stuid
GROUP BY scores.score
HAVING (sum(score)/count(stu.stuid))>80;

12、创建视图VIEW6，要求查询出全体男生的平均年龄，要求将结果保留到小数点后2位。
alter view view6 
as 
select avg(int(year(birthday)))
from stu
where sex='男';
13、创建视图VIEW7，要求统计出所有学生的平均选课门数，将结果保存到小数点1位。
CREATE view view7
AS
select truncate(count(*) / count(DISTINCT(stu.stuid)),1) as 平均选课门数
from scores right join stu on scores.stuid = stu.stuid
						join course on course.couid = scores.couid;

14、创建视图VIEW8，要求查询所有系的平均学生人数，将结果保存到小数点后1位。
create view view8 
as
SELECT TRUNCATE(count(*)/count(DISTINCT dep.depid)
from stu join dep on stu.depid=dep.depid;

15、创建视图VIEW9，要求查询出每个姓氏的学生人数。
create view view9
as 
select left(stuname,1) as 姓 ，count(*) as 学生人数
from stu
GROUP BY left(stuname,1);

16、创建视图VIEW10，要求查询出姓氏人数最多的前2个姓及其人数。
CREATE view view10
as
select left(stuname,1) ,count(*) 
from stu 
GROUP BY left(stuname,1) limit 0,2;

17、创建视图VIEW11，要求查询出名字为3个字且第2字是“小”的学生人数。
CREATE view ciew11
AS
select count(*) as 学生人数
from stu
where char_length(stuname) = 3 and right(substring(stuname,1,2),1) = "小"

18、创建视图VIEW12，请查询出姓刘的学生人数占全班总人数的百分比,要求精确到小数点后两位.
create view view12
AS
select truncate(count(*) / 10 ,2)
from stu
where left(mid(stuname,1),1) = "刘";

19、先创建如下所示的学生表，然后写一条删除命令删除除了自动编号不同,其他都相同的学生冗余信息

自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001 张三 0001      数学    69
2        2005002 李四 0001      数学    89
3        2005001 张三 0001      数学    69
20、计算从2000-1-1与当前日期之间相差的天数及年数。
select datediff('2001-1-1',now()) 
select year('2000-1-1') - year(now())

21、 求“You are a student”字符串中从11开始，长度为7的子串。
SELECT mid('You are a student',11,7)

22、分别计算“You are  students”和“我们是学生”字符串中字符的个数。
select CHAR_LENGTH('You are  students')
SELECT CHAR_LENGTH('我们是学生')

23、分别得到字符串“I am a teacher and you are students” 中左边14个和右边16个字符组成的字符串。
select left('I am a teacher and you are students',14)
select right('I am a teacher and you are students',16)

25、请将字符串'ereurereurw'中从第2位开始的连续8个字符替换位'mn'
select replace('ereurereurw',mid('ereurereurw',2,8),'mn')

26、请写出能返回0-100之间随机整数的表达式
select ceiling(rand()*100)

27、请求出当前日期加上300天所得的日期是多少
select date_add(now(),interval 300 day)

28、计算18除以5的余数
select 18%5

29、从字符串‘Nice to meet you!’中获取子字符串‘meet’。
select mid('Nice to meet you!',9,4)

30、将字符串’family’逆序输出
select reverse('family')

31、将字符串’abc’,’12’,’cd’拼接成一个新字符串，拼接的分隔符为‘-’
select concat_ws('-','abc','12','cd')