学习网址链接:https://www.bilibili.com/video/av39807944/?p=1
show databases; -- 查看数据库
use linyueyue; -- 使用linyueyue数据库
show tables; -- 查看数据库中的表
describe sc; -- 查看创建好的数据表结构
select * from student; -- 查看学生表数据内容
insert into student values (12,'王晓飞',23,'男'); -- 添加学生信息
delete from student where Sid = 12; -- 删除Sid为12的学生信息
update student set Sname = "沉浸" where Sname = "王晓飞"; -- 修改学生名字为王晓飞的信息
test101104-手动09
create table userdemo(
Sid int primary key,
Sname varchar(20));
– 联合主键约束
create table userdemo2(
Sid int,
Sname varchar(20),
PASSWORD varchar(20),
PRIMARY key (Sid,Sname)
);
– 自增约束
create table userdemo3(
id int PRIMARY key auto_increment,
Sname varchar(20));
insert into userdemo3(Sname) value("zhangsan");
SELECT * from userdemo3;
– 主键约束的的增加和删除
create table userdemo4(
Sid int,
Sname varchar(20));
desc userdemo4;
alter table userdemo4 add primary key (Sid);
alter table userdemo4 modify Sid int primary key;
alter table userdemo4 drop primary key;
– 唯一约束
– 约束修饰的字段的值不可重复
create table userdemo5(
Sid int,
Sname varchar(20));
desc userdemo5;
alter table userdemo5 add unique(Sname);
create table userdemo6(
Sid int,
Sname varchar(20),
unique(Sname));
desc userdemo6;
-- 删除唯一约束
alter table userdemo6 drop index Sname;
-- 通过modify添加位于约束
alter table userdemo6 modify Sname varchar(20) unique;
-- 非空约束
create table userdemo7(
Sid int,
Sname varchar(20) not null);
desc userdemo7;
– 默认值
create table userdemo8(
Sid int,
Sanme varchar(20),
Sage int default 10);
desc userdemo8;
insert into userdemo8(Sid,Sanme) values(3,"张晓");
select * from userdemo8;
– 外键约束
– 涉及到两个表:父表,子表
create table classes(
id int primary key,
Sname varchar(20));
create table student(
id int primary key,
Cname varchar(20),
class_id int,
foreign key(class_id) references classes(id));
-- 创建学生表
create table student1(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20));
-- 创建课程表
create table course1(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher1(tno));
-- 创建教师表
create table teacher1(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirtday datetime,
prof varchar(20) not null,
depart varchar(20) not null);
-- 创建成绩表
create table score1(
sno varchar(20),
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student1(sno),
foreign key(cno) references course1(cno),
primary key(sno,cno));
-- 添加学生表信息
insert into student1 values('101','曾华','男','1992-09-01','95033');
insert into student1 values('102','金正摁','男','1993-08-01','95031');
insert into student1 values('103','陈艳华','女','1992-06-06','95033');
insert into student1 values('104','张晓','男','1993-06-03','95031');
insert into student1 values('105','王尼玛','男','1992-09-01','95033');
insert into student1 values('106','刘全蛋','男','1993-08-01','95031');
insert into student1 values('107','赵铁柱','女','1992-06-06','95033');
insert into student1 values('108','李伊曼','男','1993-06-03','95031');
-- 添加教师表信息
insert into teacher1 values('804','李成','男','1958-12-01','副教授','计算机系');
insert into teacher1 values('805','王艳','女','1956-06-03','讲师','电子工程系');
insert into teacher1 values('806','刘平','男','1963-12-12','助教','电子工程系');
insert into teacher1 values('807','陈佳','女','1957-10-21','助教','计算机系');
-- 添加课程表
insert into course1 values('3-245','计算机导论','804');
insert into course1 values('4-655','操作系统','805');
insert into course1 values('5-245','数字电路','806');
insert into course1 values('6-701','高等数学','807');
-- 添加成绩表信息
insert into score1 values('103','3-245','86');
insert into score1 values('104','3-245','98');
insert into score1 values('105','3-245','66');
insert into score1 values('103','4-655','48');
insert into score1 values('104','4-655','93');
insert into score1 values('105','4-655','67');
insert into score1 values('103','6-701','55');
insert into score1 values('104','6-701','47');
insert into score1 values('105','6-701','66');
insert into score1 values('103','5-245','88');
insert into score1 values('104','5-245','91');
insert into score1 values('105','5-245','72');
题目集合
-- 1.查询student表的所有记录
-- 2.查询student表中的所有记录的sname,ssex和class列
-- 3.查询教师多有的单位及不重复的depart列
-- 4.查询score表中成绩在60到80之间的所有记录
-- 5.查询score表中成绩为66,88,98的记录
-- 6.查询student表中'95031'班或性别为'女'的同学记录
-- 7.以class降序查询student表的所有记录
-- 8.以cno升序,degree降序查询score表的所有记录
-- 9.查询'95031'班的学生人数
-- 10.查询score表中的最高分的学生学号和课程号.(子查询或者排序)
-- 11.查询每门课的平均成绩
-- 12.查询score表中至少有5名学生选修的并以3开头的课程的平均分数
-- 13.查询分数大于70,小于90的sno列
-- 14.查询所有学生的sname,cno和degree列
-- 15.查询所有学生的sname,cname和degree列
-- 16.查询所有学生的sname,cname和degree列
-- 17.查询95033班学生每门课程的平均分
-- 18.查询选修'3-245'课程的成绩高于103号同学'3-245'成绩的所有同学的记录
-- 19.查询成绩高于学号为103课程号为'3-245的成绩的所有记录.
-- 20.查询和学号为103,104的同学同年出生的所有学生的sno,sname,sbirthday列
-- 21.查询刘平教师任课学生的成绩
-- 22.查询选修课程人数多于2个人的教师姓名
-- 23.查询95031和95033班全体记录
-- 24.查询存在有85分以上成绩的课程cno
-- 25.查询出'计算机系'教师所教课程的成绩表
-- 26.查询'计算机系'与'电子工程系'不同职称的教师的tname和prof
-- 27.查询选修编号为3-245课程且成绩至少高于选修编号为5-245的同学的cno,sno和degree,并且按degree从高到地次序排列.
-- 28.查询选修编号为3-245且成绩高于选修编号为5-245课程的同学的cno,sno和degree
-- 29.查询所有教师和同学的name,sex和birthday
-- 30.查询所有'女'教师和'女'同学的的name,sex,birthday
-- 31.查询成绩比该课程平均成绩低的同学的成绩表
-- 32.查询所有任课教师的tname和depart
-- 33.查询至少有两名男生的班号
-- 34.查询student表中不姓王的同学记录
-- 35.查询student表中每个学生的姓名和年龄
-- 36.查询student表中最大和最小的sbirthday日期值
-- 37.以班号和年龄从大到小的顺序查询student表中的全部记录
-- 38.查询男教师及其所上的课程
-- 39.查询最高分同学的sno,cno和degree列
-- 40.查询和李伊曼同性别的所有同学的sname
-- 41.查询和李伊曼同性别并同班级的所有同学的sname
-- 42.查询所有选修计算机导论课程的'男'同学的成绩表
– 查询练习
– 1.查询student表的所有记录
select * from student1;
– 2.查询student表中的所有记录的sname,ssex和class列
select sname,ssex,class from student1;
– 3.查询教师多有的单位及不重复的depart列
select distinct depart from teacher1;
– 4.查询score表中成绩在60到80之间的所有记录
select * from score1 where degree between 60 and 80;
– 5.查询score表中成绩为66,88,98的记录
select * from score1 where degree in (66,88,98);
– 6.查询student表中’95031’班或性别为’女’的同学记录
select * from student1 where class = "95031" or ssex = '女';
– 7.以class降序查询student表的所有记录
select * from student1 order by class desc;
– 8.以cno升序,degree降序查询score表的所有记录
select * from score1 order by cno,degree desc;
– 9.查询’95031’班的学生人数
select count(sno) from student1 where class = '95031';
– 10.查询score表中的最高分的学生学号和课程号.(子查询或者排序)
select sno,cno from score1 where degree = (select max(degree) from score1);
select sno,cno from score1 order by degree desc limit 1;
– 11.查询每门课的平均成绩
select distinct cno,avg(degree) from score1 group by cno;
– 12.查询score表中至少有5名学生选修的并以3开头的课程的平均分数
select cno,avg(degree) from score1 group by cno having count(cno)>=3 and cno like '3%';
– 13.查询分数大于70,小于90的sno列
select sno,degree from score1 where degree > 70 and degree < 90;
select sno,degree from score1 where degree between 70 and 90;
– 14.查询所有学生的sname,cno和degree列
select sname,cno,degree from student1,score1 where student1.sno = score1.sno;
select sname,cno,degree from student1 s,score1 ss where s.sno = ss.sno;
– 15.查询所有学生的sname,cname和degree列
select sname,cname,degree from student1 s,course1 c,score1 ss where s.sno=ss.sno and c.cno=ss.cno;
– 16.查询所有学生的sname,cname和degree列
select sname,cname,degree from student1 s,course1 c,score1 ss where s.sno=ss.sno and c.cno=ss.cno;
– 17.查询95033班学生每门课程的平均分
select cno,avg(degree) from score1 where sno in (select sno from student1 where class='95033') group by cno;
– 18.查询选修’3-245’课程的成绩高于103号同学’3-245’成绩的所有同学的记录
select * from score1 where degree>(select degree from score1 where sno='103' and cno='3-245') and cno='3-245';
– 19.查询成绩高于学号为103课程号为’3-245的成绩的所有记录.
select * from score1 where degree>(select degree from score1 where sno='103' and cno='3-245');
– 20.查询和学号为103,104的同学同年出生的所有学生的sno,sname,sbirthday列
select sno,sname,sbirthday from student1 where year(sbirthday) in (select year(sbirthday) from student1 where sno in (103,104));
– 21.查询刘平教师任课学生的成绩
select ss.sno,ss.cno,ss.degree from score1 ss,course1 c,teacher1 t where ss.cno=c.cno and c.tno=t.tno and t.tno=(select tno from teacher1 where tname='刘平');
select * from score1 where cno=(select cno from course1 where tno=(select tno from teacher1 where tname='刘平'));
– 22.查询选修课程人数多于2个人的教师姓名
-- select t.tname from teacher1 t,course1 c,score1 ss where t.tno=c.tno and c.cno=ss.cno and (select cno from score1 group by cno having count(cno))>2;
select cno from score1 group by cno having count(cno)>2;
select * from teacher1 where tno in (select tno from course1 where cno in (select cno from score1 group by cno having count(cno)>5));
– 23.查询95031和95033班全体记录
select * from student1 where class in ('95031','95033');
select * from student1 where class='95031' or class='95033';
– 24.查询存在有85分以上成绩的课程cno
select distinct cno from score1 where degree>85;
– 25.查询出’计算机系’教师所教课程的成绩表
select tno from teacher1 where depart='计算机系';
select cno from course1 where tno in (select tno from teacher1 where depart='计算机系');
select * from score1 where cno in (select cno from course1 where tno in (select tno from teacher1 where depart='计算机系'));
– 26.查询’计算机系’与’电子工程系’不同职称的教师的tname和prof
select prof from teacher1 where depart='计算机系';
select * from teacher1 where depart ='电子工程系' and prof not in (select prof from teacher1 where depart='计算机系') union
select * from teacher1 where depart = '计算机系' and prof not in (select prof from teacher1 where depart='电子工程系');
– 27.查询选修编号为3-245课程且成绩至少高于选修编号为5-245的同学的cno,sno和degree,并且按degree从高到地次序排列.
select * from score1 where cno='3-245';
select * from score1 where cno='3-245' and degree>any(select degree from score1 where cno='5-245') order by degree desc;
– 28.查询选修编号为3-245且成绩高于选修编号为5-245课程的同学的cno,sno和degree
select * from score1 where cno='3-245' and degree>all(select degree from score1 where cno='5-245');
– 29.查询所有教师和同学的name,sex和birthday
select tname name,tsex sex,tbirtday birthday from teacher1
union
select sname,ssex,sbirthday from student1;
– 30.查询所有’女’教师和’女’同学的的name,sex,birthday
select tname name,tsex sex,tbirtday birthday from teacher1 where tsex='女'
union
select sname,ssex,sbirthday from student1 where ssex='女';
– 31.查询成绩比该课程平均成绩低的同学的成绩表
select cno,avg(degree) from score1 group by cno;
select * from score1 a where degree<(select avg(degree) from score1 b where a.cno=b.cno);
– 32.查询所有任课教师的tname和depart
select tno from course1;
SELECT tname,depart from teacher1 where tno in (select tno from course1);
– 33.查询至少有两名男生的班号
select class from student1 where ssex='男' group by class having count(*)>1;
– 34.查询student表中不姓王的同学记录
select * from student1 where sname not like '王%';
– 35.查询student表中每个学生的姓名和年龄
select sname,year(now())-year(sbirthday) from student1;
– 36.查询student表中最大和最小的sbirthday日期值
select max(sbirthday),min(sbirthday) from student1;
select sbirthday from student1 order by sbirthday desc;
– 37.以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student1 order by class ,sbirthday desc;
– 38.查询男教师及其所上的课程
select tno from teacher1 where tsex='男';
select * from course1 where tno in (select tno from teacher1 where tsex='男');
– 39.查询最高分同学的sno,cno和degree列
select * from score1 order by degree desc limit 1;
select * from score1 where degree=(select max(degree) from score1);
– 40.查询和李伊曼同性别的所有同学的sname
select sname from student1 where ssex=(select ssex from student1 where sname='李伊曼');
– 41.查询和李伊曼同性别并同班级的所有同学的sname
select sname from student1 where ssex=(select ssex from student1 where sname='李伊曼') and class=(select class from student1 where sname='李伊曼');
– 42.查询所有选修计算机导论课程的’男’同学的成绩表
select cno from course1 where cname='计算机导论';
select sno from score1 where cno=(select cno from course1 where cname='计算机导论');
select sno from student1 where ssex='男';
select * from score1 where cno=(select cno from course1 where cname='计算机导论') and sno in (select sno from student1 where ssex='男');
– 43.假设使用如下命令建立一个grade表
create table grade1(
low int(3),
upp int(3),
grade char(1));
insert into grade1 values(90,100,'A');
insert into grade1 values(80,89,'B');
insert into grade1 values(70,79,'C');
insert into grade1 values(60,69,'D');
insert into grade1 values(0,59,'E');
– 现查询多有同学的sno,cno,degree和grade列
select sno,cno,degree,grade from score1,grade1 where degree between low and upp;
– 44.SQL的四种连接
– 内连接(inner join或者join)
– 外连接
– 左连接(left join 或者 left outer join)
– 右连接(right join 或者 right outer join)
– 完全外连接(full join 或者 full outer join)
create table person(
id int,
pname varchar(30),
cardId int);
create table card(
id int,
cname varchar(20));
insert into person values(1,'张三',3);
insert into person values(3,'李四',2);
insert into person values(5,'王五',5);
insert into person values(1,'张三',3),(3,'李四',2),(5,'王五',5);
insert into card values(1,'饭卡');
insert into card values(2,'公交卡');
insert into card values(3,'地铁卡');
insert into card values(4,'银行卡');
insert into card values(5,'美容卡');
– inner join 就是两张表中的数据,通过某个字段相对,查询出相关的数据
SELECT * from person inner join card on person.cardId=card.id;
– left join 会把左边的表里面的所有数据取出,右表中有相等就取出,如果没有就null补齐
select * from person left join card on person.cardId=card.id;
select * from card left join person on card.id=person.cardId;
– right join
select * from person right join card on person.cardId=card.id;
select * from card right join person on card.id=person.cardId;
版权声明：本文为qq_42792477原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。