MySQL数据库简单练习题

桃扇骨 2022-06-02 04:06 234阅读 0赞

**简单巩固：**

create table student(

id int(10) primary key auto\_increment,  
name varchar(20) unique,  
age int(5) default 16,  
sex char(2) not null  
  
  
);  
show databases;  
create database libry;  
use libry;  
show tables;  
create table books(  
b\_id int(11) primary key,  
b\_name varchar(50),  
Authers varchar(100),  
Price Float,  
pubdate Year,  
Note varchar(100),  
Num int(11)  
);  
desc libry;  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(1,'Tal of AAA','Dickes',23,1995,'novel',11);  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(2,'EmmaT','Jane lura',35,1993,'Joke',22);  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(3,'Story of Jane','Jane Tim',40,2001,'novel',0);  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(4,'Lovey Day','George Byron',20,2005,'novel',30);  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(5,'Old land','Honore Blade',30,2010,'Law',0);  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(6,'The Battle','Upton Sara',30,1999,'medicine',40);  
insert into books(b\_id,b\_name,Authers,Price,pubdate,Note,Num) values(7,'Rose Hood','Richard haggard',28,2008,'cartoon',28);  
select \* from books where Note='novel'like update books set Price+5;  
  
  
  
  
create table books(  
b\_id int(11) primary key,  
b\_name varchar(50),  
Authers varchar(100),  
Price Float,  
pubdate Year,  
Note varchar(100) not null,  
Num int(11)  
);  
  
  
  
  
  
  
show databases;  
create database employee;  
use employee;  
show tables;  
create table einfo(  
id int(11) not null primary key auto\_increment,  
name varchar(50) not null,  
sex char(2) default '女',  
tel varchar(50),  
addr varchar(100),  
salary Float  
);  
insert into einfo(id,name,sex,tel,addr,salary) values(10001,'张一一','男','13456789000','广东韶关',1001.58);  
insert into einfo values(10002,'刘小红','女','13454319000','广东江门',1201.21);  
insert into einfo values(10003,'李四','男','0751-1234567','广东佛山',1004.11),(10004,'刘强','男','0755-5555555','广东深圳',1501.23),(10005,'王艳','女','020-1232133','广东广州',1405.16);  
  
  
  
  
**三**、按照以下题目要求实现操作，并将sql语句记录在记事本中。  
create table customers(  
c\_num int(11) primary key not null unique auto\_increment,  
c\_name varchar(50),  
c\_contact varchar(50),  
c\_city varchar(50),  
c\_birth datetime not null  
  
  
);  
alter table customers modify c\_contact varchar(50) after c\_birth;  
alter table customers change c\_name c\_name varchar(70);  
alter table customers change c\_contact c\_phone varchar(50);  
alter table customers add c\_gender char(1) not null;  
alter table customers rename customers\_info;  
  
  
alter table customers\_info drop c\_city;  
  
  
  
  
六、 按照以下题目要求实现操作，并将sql语句记录在记事本中。  
show databases;  
create database wangchuanlu;  
use wangchuanlu;  
show tables;  
  
  
create table department(  
depid int(10) primary key,  
depname varchar(20),  
depinfo varchar(100)  
);  
  
  
create table employee(  
empid int(10) primary key,  
name varchar(5),  
sex char(2),  
title varchar(20),  
birthday date,  
depid int(10)  
);  
  
  
create table salary(  
empid int(10) primary key,  
basesalary Float,  
titlesalary Float,  
deduction Float  
);  
  
  
insert into department(depid,depname) values(111,'生产部');  
insert into department(depid,depname) values(222,'销售部');  
insert into department(depid,depname) values(333,'人事部');  
  
  
  
  
insert into employee(empid,name,sex,title,birthday,depid) values(1001,'张三','男','高级程师','1975-01-01',111);  
insert into employee(empid,name,sex,title,birthday,depid) values(1002,'李四','女','助工','1985-01-01',111),(1003,'王五','男','工程师','1978-11-11',222),(1004,'赵六','男','工程师','1979-01-01',222);  
  
  
  
  
insert into salary(empid,basesalary,titlesalary,deduction) values(1001,2200,1100,200),(1002,1200,200,100),(1003,1900,700,200),(1004,1950,700,150);  
  
  
alter table department change depinfo deincl varchar(100);  
alter table department add deincl varchar(100);  
alter table employee add constraint foreign key(depid) references department(depid);  
update salary set basesalary=1700,titlesalary=600 where empid=(select empid from employee where name='李四');  
delete from department where depname='人事部';  
select empid,basesalary+titlesalary-deduction as 实发工资,basesalary+titlesalary as 应发工资 from salary;  
select \* from employee where name like '张%' and (year(curdate())-year(birthday))<40;  
  
  
select e.name,s.basesalary from employee e,salary s where depid=(select depid from department where depname='销售部') and e.empid=s.empid;  
  
  
select count(title) as '人数',title as '职称' from employee group by title;  
  
  
**七、** 按照以下题目要求实现操作，并将sql语句记录在记事本中。  
create table cus(  
顾客编号 varchar(10) primary key,  
顾客名 varchar(20),  
地址 varchar(50),  
余额 Float  
);  
  
  
insert into cus(顾客编号,顾客名,地址,余额) values('001','Zhang','Usa',5000);  
insert into cus(顾客编号,顾客名,地址,余额) values('002','Liu','China',10000),('003','Liu','Frech',50000),('004','Wen','Jap',250000);  
alter table cus change 商品编号 商品编号 varchar(10);  
  
  
create table pro(  
供应厂家编号 int(10) primary key,  
供应厂家名称 varchar(20),  
商品编号 int(10),  
商品价格 Float  
);  
  
  
insert into pro(供应厂家编号,供应厂家名称,商品编号,商品价格) values(1001,'Oracle',100023,1500),(1002,'Ibm',100024,3000),(1003,'Baidu',100025,2900),(1004,'Google',100026,5000);  
  
  
create table goods(  
商品编号 int(10) primary key,  
商品名称 varchar(20),  
商品数量 int(10)  
);  
  
  
insert into goods(商品编号,商品名称,商品数量) values(100023,'Nokia',5),(100024,'Htc',7),(100025,'Mi',10),(100026,'Asus',3);  
  
  
create table ord(  
商品编号 int(10) primary key,  
顾客编号 varchar(10),  
订单日期 date  
);  
insert into ord(商品编号,顾客编号,订单日期) values(100023,'001','1998-2-1'),(100024,'002','1999-3-12'),(100025,'003','2000-10-21'),(100026,'004','2000-11-02');  
  
  
select 供应厂家名称 from pro;  
select 顾客编号,顾客姓名,余额 from cus where 地址='Usa';  
  
  
  
  
**sql4作业数据代码**  
teacher:  
insert into teacher(tno,tname) values(1, '张老师');  
insert into teacher(tno,tname) values(2, '王老师');  
insert into teacher(tno,tname) values(3, '李老师');  
insert into teacher(tno,tname) values(4, '赵老师');  
insert into teacher(tno,tname) values(5, '刘老师');  
insert into teacher(tno,tname) values(6, '向老师');  
insert into teacher(tno,tname) values(7, '李文静');  
insert into teacher(tno,tname) values(8, '叶平');  
  
  
student:  
insert into student(sno,sname,sage,ssex) values(1, '张三', '1980-01-23', '男');  
insert into student(sno,sname,sage,ssex) values(2, '李四', '1982-12-12', '男');  
insert into student(sno,sname,sage,ssex) values(3, '张飒', '1981-09-09', '男');  
insert into student(sno,sname,sage,ssex) values(4, '莉莉', '1983-03-23', '女');  
insert into student(sno,sname,sage,ssex) values(5, '王弼', '1982-06-21', '男');  
insert into student(sno,sname,sage,ssex) values(6, '王丽', '1984-10-10', '女');  
insert into student(sno,sname,sage,ssex) values(7, '刘香', '1980-12-22', '女');  
  
  
course:  
insert into course(cno,cname,tno) values(1, '企业管理', 3);  
insert into course(cno,cname,tno) values(2, '马克思', 1);  
insert into course(cno,cname,tno) values(3, 'UML', 2);  
insert into course(cno,cname,tno) values(4, '数据库', 5);  
insert into course(cno,cname,tno) values(5, '物理', 8);  
  
  
sc:  
insert into sc(sno,cno,score) values(1, 1, 80);  
insert into sc(sno,cno,score) values(1, 2, 86);  
insert into sc(sno,cno,score) values(1, 3, 83);  
insert into sc(sno,cno,score) values(1, 4, 89);  
insert into sc(sno,cno,score) values(2, 1, 50);  
insert into sc(sno,cno,score) values(2, 2, 36);  
insert into sc(sno,cno,score) values(2, 4, 59);  
insert into sc(sno,cno,score) values(3, 1, 50);  
insert into sc(sno,cno,score) values(3, 2, 96);  
insert into sc(sno,cno,score) values(3, 4, 69);  
insert into sc(sno,cno,score) values(4, 1, 90);  
insert into sc(sno,cno,score) values(4, 2, 36);  
insert into sc(sno,cno,score) values(4, 3, 88);  
insert into sc(sno,cno,score) values(5, 1, 90);  
insert into sc(sno,cno,score) values(5, 2, 96);  
insert into sc(sno,cno,score) values(5, 3, 98);  
insert into sc(sno,cno,score) values(5, 4, 99);  
insert into sc(sno,cno,score) values(6, 1, 70);  
insert into sc(sno,cno,score) values(6, 2, 66);  
insert into sc(sno,cno,score) values(6, 3, 58);  
insert into sc(sno,cno,score) values(6, 4, 79);  
insert into sc(sno,cno,score) values(7, 1, 80);  
insert into sc(sno,cno,score) values(7, 2, 76);  
insert into sc(sno,cno,score) values(7, 3, 68);  
insert into sc(sno,cno,score) values(7, 4, 59);  
insert into sc(sno,cno,score) values(7, 5, 89);  
  
\#1.查询课程1的成绩 比 课程2的成绩高 的所有学生的学号.  
select a.sno from (select sno,score from sc where cno = 1) a,(select sno,score from sc where cno = 2) b  
where a.sno = b.sno and a.score>b.score;  
  
  
  
  
\#2.查询平均成绩大于60分的同学的学号和平均成绩；  
select sno,avg(score) from sc group by sno having avg(score)>60  
  
  
  
  
\#3.查询所有同学的学号、姓名、选课数、总成绩  
select s.sno,s.sname,count(sc.cno),sum(sc.score) from student s,sc where s.sno = sc.sno  
group by s.sno  
  
  
  
  
\#4.查询姓“李”的老师的个数  
select count(1) from teacher where tname like '李%'  
  
  
  
  
\#5.查询没学过“叶平”老师课的同学的学号、姓名；  
select sno,sname from student where sno not in  
(select sc.sno from teacher t,sc,course c where t.tno = c.tno and c.cno = sc.cno and t.tname = '叶平');  
  
  
\#7.查询学过“叶平”老师所教所有课程的所有同学的学号、姓名  
select sno,sname from student where sno in  
(select sc.sno from teacher t,sc,course c where t.tno = c.tno and c.cno = sc.cno and t.tname = '叶平');  
  
  
  
  
\#6.查询同时学过课程1和课程2的同学的学号、姓名  
select sno,sname from student where sno in(select sno from sc where cno = 1) and sno in (select sno from sc where cno =2)  
  
  
\#8.1查询所有课程成绩小于60分的同学的学号、姓名 1  
select sno,sname from student where sno in (select sno from sc where score<60)  
  
  
\#8.2查询所有课程成绩小于60分的同学的学号、姓名 2  
select sno,sname from student where sno in(  
select sno from sc where score <60 group by sno having count(1)>2)  
  
  
  
  
\#9查询没有学全所有课的同学的学号、姓名  
\#select s.sno,s.sname,count(sc.cno) from student s,sc where s.sno = sc.sno group by sno  
  
  
select s.sno,s.sname from student s,sc where s.sno = sc.sno group by sno having count(sc.cno) <(select count(1) from course)  
  
  
\#10.查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名  
select distinct s.sno,s.sname from student s,sc where s.sno = sc.sno and s.sno !=1 and sc.cno in (select cno from sc where sno = 1)  
  
  
  
  
\#11.查询各科成绩最高分和最低分：以如下形式显示：课程号，最高分，最低分  
select cno,max(score),min(score) from sc group by cno  
  
  
  
  
\#12.查询不同老师所教不同课程平均分, 从高到低显示  
  
  
\#select avg(sc.score),t.tname from teacher t,sc,course c where t.tno = c.tno and c.cno = sc.cno  
  
  
  
  
select avg(sc.score),t.tname from teacher t,sc,course c where t.tno = c.tno and c.cno = sc.cno group by t.tno order by avg(sc.score) desc  
  
  
  
  
\#13.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列  
  
  
\#select avg(score) from sc group by cno  
  
  
select avg(score) from sc group by cno order by avg(score) ,cno desc  
  
  
  
**sql5作业代码：**  
student:  
sno,sname,ssex,sage  
course:  
cno,cname,hours  
sc:  
sno,cno,grade  
  
  
select \* from student,sc where student.sno=sc.sno;  
select student.sno,course.cno,sc.grade from student,sc,course where grade between 70 and 80;  
\#select cname,cno from course;  
select sdept,count(sno) from student group by sdept;  
select sc.sno,sum(grade) from sc group by sno having sum(grade)>200;  
update sc set grade=grade+10 where cno='c01';  
select s.sname,s.sdept from student s,sc where sc.sno=s.sno and cno='c02';  
  
  
delete from sc where grade<50 or grade=null;  
  
  
**sql6作业代码：**  
select \* from user where name='李%';  
select u.id,u.name,l.info,l.createdate from user u,log l where u.id=l.uid and name='李四';  
  
  
select info from log;  
select u.name from log l,user u where u.id=l.uid group by uid having count(uid)>2;  
select info from log limit 1,2;

select u.name,l.info from user u,log l where u.id=l.id and dept='测试部';

**三、按照以下题目要求实现操作，并将sql语句记录在记事本中。**  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
create database Market;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
create table customers(  
c\_num int(11) primary key auto\_increment,  
c\_name varchar(50) not null,  
c\_contact varchar(50) not null,  
c\_city varchar(50) not null,  
c\_birth datetime  
  
  
);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table customers modify c\_contact varchar(50) after c\_birth;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table customers change c\_name c\_name varchar(70);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table customers change c\_contact c\_phone varchar(50);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table customers add c\_gender char(1);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table customers rename customers\_info;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table customers\_info drop c\_city;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
**四、按照以下题目要求实现操作，并将sql语句记录在记事本中。**  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
create database company;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
create table office(  
officeCode int primary key unique not null,  
City varchar(30) not null,  
address varchar(50),  
country varchar(50) not null,  
postalCode varchar(25) unique  
);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
create table employees(  
empNum int(11) primary key not null unique auto\_increment,  
lastName varchar(50) not null,  
firstName varchar(50) not null,  
mobile varchar(25),  
Code int not null ,  
jobTutle varchar(50) not null,  
birth Datetime not null,  
Note varchar(255),  
Sex varchar(5)  
);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table employees modify mobile varchar(25) after Code;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table employees change birth employee\_birth Datetime;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table employees change sex sex varchar(5) not null;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table employees drop Note;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table employees add favoriate\_activity varchar(100);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
alter table employees rename employees\_info;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
  
  
**六、按照以下题目要求实现操作，并将sql语句记录在记事本中。**  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
create table department(  
depid int(10) primary key,  
depname varchar(10)  
);  
  
  
create table emoloyee(  
empid int(10) primary key,  
name varchar(10),  
sex varchar(10),  
title varchar(10),  
birthday Datetime,  
depid int(10),  
foreign key(depid) references department(depid)  
);  
  
  
create table salary(  
empid int(10) primary key,  
basesalary Float,  
titlesalary Float,  
deduction Float  
);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
insert into department values(111,'生产部',null),(222,'销售部',null),(333,'人事部',null);  
\---------------------------------  
insert into emoloyee values(1001,'张三','男','高级工程师','1975-1-1',111),(1002,'李四','女','助工','1985-1-1',111),(1003,'王五','男','工程师','1978-11-11',222),(1004,'赵六','男','工程师','1979-1-1',222);  
\---------------------------------  
insert into salary values(1001,2200,1100,200),(1002,1200,200,100),(1003,1900,700,200),(1004,1950,700,150);  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
update salary set basesalary=1700,titlesalary=600 where empid=(select empid from emoloyee where name='李四');  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
delete from department where depid=333;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
select empid as '雇员编号',basesalary+titlesalary-deduction as '实发工资',basesalary+titlesalary as '应发工资' from salary;  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
select \* from employee where name like '张%' and (year(curdate())-year(birthday))<40;  
select e.name,s.basesalary from employee e,salary s where depid=(select depid from department where depname='销售部') and e.empid=s.empid;  
select count(title) as '人数',title as '职称' from employee group by title;

**五：**

![Center][]

\#将李四的基本工资改为1700元，职务工资为600  
update salary set basesalary=1700,titlesalary=600 where empid = (select empid from emoloyee where name = '李四')  
  
  
  
  
\#删除人事部门的部门记录。  
delete from department where depname = '人事部'  
  
  
  
  
\#查询出每个雇员的雇员编号，实发工资（基本工资+职务工资-扣除），应发工资（基本工资+职务工  
资）。  
  
  
select empid '雇员编号',(basesalary+titlesalary-deduction) '实发工资',(basesalary+titlesalary) '应发工资'  
from salary;  
  
  
  
  
\#查询姓“张”且年龄小于40的员工的记录。  
select empid,name,sex,title,birthday from emoloyee where name like '张%' and (year(sysdate())-year(birthday))<40  
  
  
  
  
select year(sysdate())-year(birthday) from emoloyee  
  
  
  
  
\#查询销售部门的雇员姓名及其基本工资。  
select d.depname,e.name,s.basesalary from department d,emoloyee e,salary s  
where d.depid = e.depid and s.empid = e.empid and d.depname ='销售部'  
  
  
  
  
\#统计各职称的人数  
select count(1) from emoloyee group by title  
  
  
\#从商品表中查询商品名称倒数第二个字母为i的商品名称及数量，并按数量降序排序  
select name,num from goods where name like '%i\_' order by num desc  
  
  
  
  
\#从商品表中查询最多商品数量，最小商品数量及商品总数量的记录信息  
  
  
select max(num),min(num),sum(num) from goods  
  
  
  
  
\#找出所有其家庭地址中含有“北京”的教师的教工号及部门号，要求显示结果中各列标题用中文表示。  
select number '教工号',depno '部门号' from teacher where address like '%北京%'  
  
  
  
  
\#获得Teacher表中工资最高的教工号和姓名。  
select number,name from teacher where salary = (select max(salary) from teacher)  
  
  
  
  
\#找出所有收入在2500～4000之间的教工号。  
select number from teacher where salary between 2500 and 4000  
  
  
  
  
\#查找在网络技术系工作的教师的姓名、性别和工资。  
select t.name,t.sex,t.salary from teacher t,department d where d.depno = t.depno and d.depname='网络技术系'  
  
  
  
  
\#计算男性教师和女性教师的平均工资。  
select avg(salary),sex from teacher group by sex  
  
  
\#视图Teacher\_view，视图包含教工号、姓名、部门号和工资。  
create view teacher\_view as select number,name,depno,salary from teacher;  
  
  
select salary from teacher\_view where name = 'lucy'  
  
  
  
  
  
  
update teacher set salary= salary+300 where depno = 603  
  
  
\#求所有男员工的姓名和部门名称。  
  
  
select t.name,d.depname from teacher t,department d where d.depno = t.depno and t.sex = '男'

**三：**

![Center 1][]

\#查询student表中，名字里有龙的学生  
select sname from student where sname like '%龙%';

\#查询student表中，birthday大于2016-02-01的学生，并按id正序排序  
select sname from student where birthday < '2016-02-01' order by id  
  
  
  
  
\#8）查询student表中，birthday小于2016-02-02的学生的数量  
select count(id) from student where birthday > '2016-02-02'  
  
  
  
  
  
  
\#9）查询出所有学生总共分几个班级  
  
  
select count(m.id) from myclass m ,student s where s.cid = m.id group by m.id  
select count(id) from myclass where id in (select distinct m.id from myclass m ,student s where s.cid = m.id )  
  
  
  
  
select count(id),cname from myclass where id in (select distinct m.id from myclass m ,student s where s.cid = m.id ) group by id  
  
  
  
  
\#10）查询出班级的学生人数大于2的有哪些  
select count(s.cid),m.cname from myclass m ,student s where s.cid = m.id group by m.id having count(s.cid)>2  
  
  
\#只显示班级  
select m.cname from myclass m ,student s where s.cid = m.id group by m.id having count(s.cid)>2  
  
  
  
  
\#11）查询student表中，班级id为1,2的学生，通过in实现  
select sname from student where id in (1,2);  
  
  
  
  
\#12）查询和观世音同班的同学  
select sname from student where cid = (select distinct m.id from student s,myclass m where s.cid = m.id and s.sname = '观世音') and sname !='观世音'  
  
  
\#学生对应的班级id  
select m.id from student s,myclass m where s.cid = m.id  
  
  
\#13）全查student表，按分页处理，每页2条，查出第三页的学生  
select id,sname,birthday from student limit 4,2  
  
  
  
  
\#14）查询显示所有学生的两个信息：学生姓名，班级名字和教师名字  
select s.sname,m.cname,m.cteacher from student s,myclass m where s.cid = m.id  
  
  
select s.sname,m.cname,m.cteacher from student s right join myclass m on s.cid = m.id

[Center]: /images/20220602/319cc07f01fb43ccb7b08a24c9533fac.png
[Center 1]: /images/20220602/c729268d8b26459daef25a346253a486.png