沫言生活记录 - sql
http://6hi.cn/tag/sql/
我勒数据库笔记
http://6hi.cn/167.html
2024-09-29T14:30:00+08:009.20insert into student VALUES('张三丰','男',109,'1130-10-03','13134533333');
insert into student VALUES('张无忌','男',18,'1200-6-5','1111122315');
insert into student(NAME,sex) VALUES('张三丰 ','男');
UPDATE student set age=age+1;
UPDATE student set age=age-5 where sex='女';
UPDATE student set age=55,tel='123456789' WHERE name='';
综合练习
1、先把你的数据表清空
DELETE from student;
2、在表中分别插入这几个记录
insert into student VALUES('令狐冲','男',22,'1256-03-12','1122335566');
insert into student(name,sex) VALUES('赵敏','女');
insert into student(name,sex,age,birthday) VALUES('乔峰','男',25,'1115-05-17');
insert into student(name,sex,age,birthday) VALUES('虚竹','男',66,'1015-06-17');
insert into student VALUES('王语嫣','女',18,'1015-06-17','123455555');
SELECT * FROM student;
3、为所有男生的年龄加上2岁
UPDATE student set age=age+2 where sex='男';
SELECT * FROM student;
4、把18岁的那个女生姓名改为“阿紫”
UPDATE student set name='阿紫' where sex='女';
SELECT * FROM student;
5、给赵敏完善年龄、出生日期、电话信息为“21,1246-05-24,133323232”
UPDATE student set age=21,birthday='1246-05-24',tel='133323232' WHERE name='赵敏';
6、删除年龄大于25岁的男人的信息
DELETE from student where sex='男' and age>25;
SELECT * FROM student;
9.25INSERT into student(sno,name,sex,birthday) VALUES('2101003','王师傅','女',55,'1999-12-03','11122334455');
SELECT * from student;
DELETE from student;
-- 约束
-- 1、主键约束，能够阻止两条相同记录的插入
alter table student add PRIMARY key(sno);
-- 2、唯一性约束，能够阻止两条相同记录的插入
alter table student add constraint u_sfzh UNIQUE(sfzh);
-- 3、检查约束，规范插入的范围或符合实际
-- eg1：在age字段创建检查约束，使插入的值在0-200之间
alter TABLE student add constraint c_age check(age>=0 and age<=200);
-- eg2:在sex字段创建检查约束，使sex字段插入的值是男或女
alter table student add constraint c_sex check(sex='男' or sex='女');
-- 4、默认值约束，在某个字段上填充默认的数据
ALTER TABLE student modify tel char(11) DEFAULT'000000000';
-- 5、非空约束
ALTER TABLE student MODIFY age TINYINT not null;
9.27
-- 对字段操作
SELECT sname as 姓名,Ssex as 性别 from student;
-- 例1，查询表中所有女生的信息
SELECT * from student WHERE Ssex='女';
-- 练习1 查询班级编号是11010111班的所有学生信息
SELECT * from student WHERE Classno='11010111';
-- 练习2 查询班级编号是11010111班的学生的姓名和班级编号，字段名用中文显示
SELECT sname as 姓名,Classno as 班级编号 from student WHERE Classno='11010111';
-- 练习3 查询班级编号是11010111班的所有女生信息
SELECT * from student WHERE Classno='11010111' and ssex='女';
-- 练习4 查询1995年以后的所有女生的信息
SELECT * from student WHERE Sbirthday>'1995-12-31' and Ssex='女';
-- 例2 查询表中性王的学生的信息 %通配符 like模糊匹配
SELECT * from student WHERE Sname like '王%';
-- 练习5 查询表中性王的学生的信息
SELECT * from student WHERE Sname like '%萍%';
-- 练习6 查询表中江苏省的学生的信息
SELECT * from student WHERE address like '江苏省%';
-- 练习7 查询表中家中住镇江的学生的信息
SELECT * from student WHERE address like '%镇江市%';
-- 练习8 查询表中1995年出生学生的信息
SELECT * from student WHERE Sbirthday like '1995%';
9.29聚合函数
cont() 计数
sum() 求和
avg() 求平均
max() 求最大值
min() 求最小值
-- SELECT * FROM result
-- 例1 查询student表中男生的人数
SELECT count(sno) as 男生人数 from student WHERE ssex='男';
-- 练习1 在result表中,查询学号1201011101的学生平均成绩
SELECT avg(score) as 平均成绩 from result WHERE sno='1201011101';
-- 练习2 在result表中,查询课程编号是0102001的课程的最高分和最低分
SELECT max(score) as 最高分,min(score) as 最低分 from result WHERE cno='0102001';
year() 年
month()月
day()日
SELECT CURDATE()当前时间日期函数
DAYOFWEEK(date) 返回一周中的第几天
dayoyear(data) 返回一年中的第几天
DAYOFMONTH(date) 返回第几周
DAYNAME(date) 返回一周中的星期几
NOW() 获取当前的日期和时间
CURDATE() 获取当前的日期
例2
SELECT *,2024-year(sbirthday) as age from student;
练习3 在student表中,查询年龄最大的那个学生的信息,并且把年龄显示出来
SELECT *,max(year(CURDATE())- year(sbirthday)) as 最大年龄 from student;
10.9范围、域查询
例1、查询出生日期是1995-4-1,1995-9-1,1995-5-24号出生的学生的信息
-- SELECT
-- *
-- FROM
-- student
-- WHERE
-- sbirthday = '1995-4-1'
-- OR sbirthday = '1995-9-1'
-- OR sbirthday = '1995-5-24';
SELECT
*
FROM
student
WHERE
sbirthday IN (
'1995-4-1',
'1995-9-1',
'1995-5-24'
);
例2 查询年龄在30到34之间的学生信息
SELECT *,2024 - YEAR (sbirthday)
FROM
student
WHERE
2024 - YEAR (sbirthday) BETWEEN 30 and 34;
-- 当前日期 CURDATE()
-- 当前年份
select year(CURDATE());
SELECT * ,year(CURDATE())-year(sbirthday) as age from student;
concat(s1,s2...s) 把几个字符串连接起来
left(s,n) 获取字符串s左边的n个字符
right(s,n) 获取字符串s右边的n个字符
trim(s) 消除字符串s前后的空格
rtrim(s) 消除字符串s右面的空格
ltrim(s) 消除字符串s左面的空格
SUBSTR(str FROM pos FOR len) 从字符串str截取从pos开始的len长度的字符串
LOCATE(substr,str) 在str里查找substr第一次出现的位置
LOWER(str)
UPPER(str)
例3 查询年龄是35岁的学生，显示信息为'我叫'姓名'，今年35岁'
SELECT
CONCAT(
'我叫',
sname,
'，今年35岁'
) AS 信息
FROM
student
WHERE
YEAR (CURDATE()) - YEAR (sbirthday) = 33;
例4 查询所有姓王学生的信息
SELECT * from student WHERE left(sname,1)='王'
练习1，查询所有1995年出生的学生的信息，使用left函数
SELECT * from student WHERE left(Sbirthday,4)=199510.23SELECT *,year(CURDATE())-year(sbirthday) as age from student;
分组统计查询
例子1 在student表中查询男生和女生的人数
SELECT count(sno) from student WHERE ssex='女' or ssex='男';
SELECT ssex,COUNT(sno) as 人数 from student
GROUP BY ssex;
例子2 在student表中查询各个班级的人数
SELECT classno,COUNT(sno) as 人数 from student
GROUP BY classno;
例3 在student表中查询各个年份出生的人数
-- 1
SELECT left(Sbirthday,4),COUNT(sno) as 人数 from student
GROUP BY left(Sbirthday,4);
-- 2
SELECT year(sbirthday),COUNT(sno) as 人数 from student
GROUP BY year(sbirthday);
例4 在student表中查询每种姓氏的人数
SELECT left(sname,1),COUNT(sno) as 人数 from student
GROUP BY left(sname,1);
例5 在result表中查询每个学生的总成绩
SELECT sno,sum(score) as 总成绩 from result
GROUP by sno;
例6 在result表中查询每门课学生的平均分
SELECT sno,avg(score) as 总成绩 from result
GROUP by sno;
例7 在result表中查询每门课学生的最高分和最低分
SELECT sno,max(score)as 最高分,min(score)as 最低分 from result
GROUP by sno;
例子2（1）在student表中查询各个班级的女生人数
SELECT classno,COUNT(sno) as 人数 from student WHERE ssex='女'
GROUP BY classno;
例子2（2）在student表中查询班级人数大于10的班级编号和人数
SELECT classno,COUNT(sno) as 人数 from student
GROUP BY classno
HAVING COUNT(sno)>10;
-- HAVING只能用在GROUP BY后面。
例子4（1）在student表中查询每种姓氏的人数大于8的姓氏和人数
SELECT left(sname,1),COUNT(sno) as 人数 from student
GROUP BY left(sname,1)
HAVING COUNT(sno)>8;
例子6（1） 在student表中查询课程的平均分大于75的课程代号和平均分
SELECT sno,avg(score) as 总成绩 from result
GROUP by sno
HAVING avg(score)>75;
-- 查询女生人数
SELECT ssex,COUNT(sno) as 人数 from student WHERE ssex='女'
GROUP BY ssex;
例子12 在student表中查询女生人数大于4的班级编号和女生人数
SELECT ssex,COUNT(sno) as 人数 from student WHERE ssex='女'
GROUP BY ssex;
HAVING COUNT(sno)>4
10.251. 在student表中查询各个班级的人数
SELECT classno,COUNT(sno) as 人数 from student
GROUP BY classno;
2. 显示student表中出生日期年份中人数大于10的年份及人数
SELECT year(sbirthday) as 出生人数,COUNT(sno) as 人数 FROM student
GROUP BY year(sbirthday)
HAVING COUNT(sno)>10;
3. 统计显示student表中各个班级中姓名是3个字的学生人数
SELECT classno,COUNT(sno) as 人数 from student WHERE length(trim(sname))=9
GROUP BY classno
4. 统计显示student表中各种姓氏的人数（可以使用left函数求姓氏）
SELECT left(sname,1),COUNT(sno) as 人数 from student
GROUP BY left(sname,1);
5. 统计显示student表中各个班级的平均年龄
SELECT classno,AVG(year(CURDATE())-year(sbirthday)) as 平均年龄 from student
GROUP BY classno
6. 统计显示student表中各个班级中年龄最大数
SELECT classno,max(year(CURDATE())-year(sbirthday)) as 最大值 from student
GROUP BY classno
7. 统计显示student表中的班级编号及年龄大于等于33岁的班级人数
SELECT classno,count(sno) as 人数 from student WHERE year(CURDATE())-year(sbirthday) >= 33
GROUP BY classno
8. 统计显示student表中每个班级年龄大于30岁的女生人数
SELECT classno,COUNT(sno) AS 女生人数 FROM student WHERE TIMESTAMPDIFF(YEAR, sbirthday, CURDATE()) > 30 AND ssex = '女'
GROUP BY classno;
10.30排序
练习1、在result表中统计各门课程的平均成绩和学习该课程的人数，按照平均成绩从大到小的顺序进行显示
SELECT cno,avg(score),count(*)
FROM result
GROUP BY cno
ORDER BY AVG(score) desk
子查询：
例子1、在result表中查询所学课程编号是0102001
且成绩大于等于此门课程平均成绩的学生的学号和该门课程成绩
-- SELECT avg(score) FROM result WHERE cno='0102001'此门课程平均成绩
SELECT sno,score FROM result WHERE cno='0102001' and score>= (SELECT avg(score) FROM result WHERE cno='0102001')
练习2、查询某些学生，其年龄大于班级平均年龄的11010111班的学生的姓名和其年龄
-- SELECT avg(year(CURDATE())-year(sbirthday)) FROM student WHERE classno='11010111'
SELECT sname,year(CURDATE())-year(sbirthday)
FROM student
WHERE classno='11010111'
and year(CURDATE())-year(sbirthday)>(SELECT avg(year(CURDATE())-year(sbirthday)) FROM student WHERE classno='11010111')
练习3、查询学习某课程的人数大于等于10个人的课程的课程名称
SELECT cname FROM course WHERE cno in
(SELECT cno FROM result GROUP BY cno HAVING COUNT(*)>=10)
练习4、查询学习了四门课的学生的学号和姓名
SELECT sname FROM student
WHERE sno in(SELECT sno FROM result GROUP BY sno HAVING count(*)-4)
练习5、查询所有计算机应用技术专业的学生的姓名
SELECT sname from student
WHERE classno in
(SELECT classno from class
WHERE pno in
(SELECT pno FROM professional WHERE pname='计算机应用技术'))
11.6 在stdent表中查询平均成绩大于等于80分的学生的姓名
SELECT sname from student WHERE sno in
(SELECT sno FROM result GROUP BY sno HAVING avg(score) >=80)
例子1 查询‘计算机应用技术专业’的专业名称和它下面的班级名称
SELECT Pname,Classname
FROM professional,class
WHERE Pname='计算机应用技术' and professional.pno=Class.pno
例子3 查询‘计应1111’班的所有学生和他的姓名
SELECT sname,classname
From class,student
where classname='计应1111' and student.classno=class.classno
例子3 查询张劲同学的各门课程的成绩和他的姓名
FROM SELECT sname,score
FROM student,result
WHERE sname='张劲' and student.sno=result.sno
例子4 查询张劲同学的各门课程
SELECT sname,score,cname
FROM student,result,course
WHERE sname='张劲' and student.sno=result.sno and result.cno=course.cno
例子5 查询“计算机应用技术”专业的所有学生的姓名、班级名、专业名
SELECT pname,classname,sname
FROM professional,class,student
WHERE pname="计算机应用技术" and professional.pno=class.pno and class.classno=student.classno
例子6 查询”计算机应用技术“专业的所有的专业名、班级名、学生的姓名、课程名和对应的成绩
SELECT pname,classname,sname,cname,score
FROM professional,class,student,result,course
WHERE pname="计算机应用技术"
and professional.pno=class.pno
and class.classno=student.classno
and student.sno=result.sno
and result.cno=course.cno
练习1 查询”机电工程系“所有学生所在的学院名、专业名、班级名、姓名、成绩、对应课程名、课程对应教师名
SELECT deptname,pname,classname,sname,score,cname,tname
FROM department,professional,class,student,result,course,teching,teacher
WHERE deptname="软件与服务外包学院"
and department.deptno=professional.deptno
and professional.pno=class.pno
and class.classno=student.classno
and student.sno=result.sno
and result.cno=course.cno
and course.cno=teaching.cno
and teaching.tno=teacher.tno
SELECT deptname,pname,classname,sname
FROM department,professional,class,student
WHERE deptname="机电工程系"
and department.deptno=professional.deptno
and professional.pno=class.pno
and class.classno=student.classno
11.15内链接
例子1 查询学生的姓名、课程名及对应成绩
SELECT sname,cname,score
FROM student
INNER JOIN result on student.sno=result.sno
INNER JOIN course on result.cno=course.cno
外链接（左外链接，右外链接）
例子2 查询所有学生的姓名、对应成绩
SELECT sname,score
from
student left OUTER JOIN result on student.sno=result.sno
例子2 查看一下有哪门课没有老师教，显示教师姓名和课程名
SELECT cname,tname
from teacher
right outer join teaching on teacher.tno=teaching.tno
right outer join course on teaching.cno=course.cno
例子3 查看一下有哪个老师没有课教，显示教师姓名和课程名
SELECT tname,cname
from course
right outer join teaching on course.cno=teaching.cno
right outer join teacher on teaching.tno=teacher.tno