mysql数据库练习题

有一段时间想好好的练习一下数据库 就去网上找了好多练习题目。但是这些练习题目都感觉好少 所以我把我搞到的所有数据库练习题 综合了一下起来。喜欢的小好伙伴们赶紧收藏好了撒。一次够你练习几个小时了 别坚持不下去了撒偷笑   那我就贴出来了


//登录和退出数据库
1:mysql -u root -p//输入密码
2: exit
3: mysql -u root -p


create table if not exists student  
(  
    SNO varchar(20) primary key,  
    SNAME varchar(20) character set gbk,  
    AGE int,  
    SEX  char(2) character set gbk CHECK(SEX IN('男','女'))   
);  
insert into student values('1','李强',23,'男');  
insert into student values('2','刘丽',22,'女');  
insert into student values('5','张友',22,'男');  
  
create table if not exists course  
(  
    CNO varchar(20) primary key,  
    CNAME varchar(20) character set gbk,  
    TEACHER varchar(20) character set gbk  
);  
insert into course values('K1','C语言','王华');  
insert into course values('K5','数据库原理','程军');  
insert into course values('K8','编译原理','程军');  
  
create table if not exists sc  
(  
    SNO varchar(20) NOT NULL,  
    CNO varchar(20) NOT NULL,  
    SCORE int NOT NULL,  
    primary key (SNO,CNO),  
    foreign key (SNO) references student(SNO),  
    foreign key (CNO) references course(CNO)  
);  
insert into sc values('1','K1',83);  
insert into sc values('2','K1',85);  
insert into sc values('5','K1',92);  
insert into sc values('2','K5',90);  
insert into sc values('5','K5',84);  
insert into sc values('5','K8',80);  


select sname from student  
where not exists  
(  
   select * from course  
   where teacher='程军' and not exists  
   (  
       select * from sc   
       where sc.sno=student.sno and sc.cno=course.cno  
    )  
);  


select course.cno from course  
where course.cno not in  
(  
select sc.cno from sc,student  
where student.sname='李强' and student.sno=sc.sno  
);  


SELECT sc.sno  
FROM sc  
GROUP BY sc.sno HAVING count(*)>=3  
;  




SELECT sname FROM student  
WHERE NOT EXISTS  
(  
SELECT * FROM course  
WHERE NOT EXISTS  
    (  
        SELECT * FROM sc  
        WHERE course.cno=sc.cno and student.sno=sc.sno  
    )  
);  


SELECT * FROM student  
WHERE sno not in  
(  
    SELECT sc.sno FROM sc,course  
    WHERE course.cno=sc.cno  
);  


SELECT * FROM course  
WHERE teacher='程军';  


SELECT score FROM student,sc  
WHERE student.sname='李强' AND student.sno=sc.sno  
(3)查询课程名为“C语言”的平均成绩;
[sql] view plain copy
SELECT AVG(score) FROM sc,course  
WHERE course.cname='C语言' AND course.cno=sc.cno  
(4)查询选修了所有课程的同学信息。
[sql] view plain copy
SELECT * FROM student   
WHERE NOT EXISTS  
(  
    SELECT * FROM course  
    WHERE NOT EXISTS  
    (  
         SELECT * FROM sc  
         WHERE course.cno=sc.cno AND student.sno=sc.sno  
    )  
);  


SELECT cno,cname FROM course WHERE teacher LIKE '王%';  


(2)检索年龄大于23岁的男学生的学号和姓名。
[sql] view plain copy
SELECT sno,sname FROM student  
WHERE age>23;  


(3)检索至少选修王老师所授课程中一门课程的女学生姓名。
[sql] view plain copy
SELECT sname FROM student  
WHERE sex='女' AND sno IN  
(  
    SELECT distinct sno FROM sc,course  
    WHERE teacher LIKE '王%' AND sc.cno=course.cno  
);  
(4)检索李同学不学的课程的课程号。
[sql] view plain copy
SELECT course.cno FROM course  
WHERE course.cno NOT IN  
(  
    SELECT sc.cno FROM sc,student  
    WHERE student.sname LIKE '李%' AND sc.sno=student.sno  
);  
(5)检索至少选修两门课程的学生学号。
[sql] view plain copy
SELECT sno FROM sc  
GROUP BY sno HAVING (count(*)>=2);  
(6)检索全部学生都选修的课程的课程号与课程名。
[sql] view plain copy
SELECT course.cno,course.cname FROM course   
WHERE course.cno IN  
(  
    SELECT cno FROM sc  
    GROUP BY cno HAVing count(*)=(SELECT count(*) FROM student)  
);  
(7)检索选修课程包含王老师所授课的学生学号。
[sql] view plain copy
SELECT DISTINCT sno FROM sc  
WHERE cno IN  
(  
    SELECT cno FROM course  
    WHERE teacher LIKE '王%'  
);  
(8)统计有学生选修的课程门数。
[sql] view plain copy
SELECT count(*)  NumberOfCourse  
FROM   
(  
      SELECT DISTINCT cno FROM sc  
      GROUP BY cno  
) AS testTab;  
(9)求选修K1课程的学生的平均年龄。
[sql] view plain copy
SELECT AVG(age) AverageOfAge  
FROM student   
WHERE sno IN  
(  
    SELECT sno FROM sc  
    WHERE cno='K1'  
);  
(10)求王老师所授课程的每门课程的学生平均成绩。
[sql] view plain copy
SELECT sc.cno,AVG(sc.score)  
FROM sc  
WHERE sc.cno IN  
(  
SELECT course.cno FROM course  
WHERE course.teacher LIKE '王%'  
)  
GROUP BY sc.cno  
(11)统计每门课程的学生选修人数(超过2人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
[sql] view plain copy
SELECT cno,Num  
FROM   
(  
SELECT cno,count(*) as Num  
FROM sc  
GROUP BY cno HAVING (count(*) >= 2)  
) tb_temp  
ORDER BY Num DESC,cno ASC  
;  
(12)检索学号比李同学大,而年龄比他小的学生姓名。
[sql] view plain copy
SELECT stu1.sname   
FROM student stu1,  
(  
    SELECT max(sno) snoLi,min(age) ageLi FROM student   
    WHERE sname LIKE '李%'  
) AS stuLi  
WHERE (stu1.age < stuLi.ageLi) AND (stu1.sno>stuLi.snoLi)  
;   
(13)检索姓名以李打头的所有学生的姓名和年龄。
[sql] view plain copy
SELECT sname,age FROM student  
WHERE sname LIKE '李%'  
;  
(14)在SC中检索成绩为空值的学生学号和课程号。
[sql] view plain copy
SELECT sno,cno FROM sc  
WHERE score IS NULL;  
(15)求年龄大于女同学平均年龄的男学生姓名和年龄。
[sql] view plain copy
SELECT sname,age FROM student  
WHERE  
sex='男' AND age > (  
                      SELECT AVG(age)  
                      FROM student  
                      WHERE sex='女'  
                   )  
;  
(16)求年龄大于所有女同学年龄的男学生姓名和年龄。


[sql] view plain copy
SELECT sname,age  
FROM student  
WHERE sex='男' AND age > (  
                            SELECT max(age)  
                             FROM student  
                            WHERE sex='女'  
                         )  
;  






//数据库创建,删除,进入数据库
4:CREATE DATABASE `testdatabase`;
5: CREATE DATABASE `testdatabase`;
6:DROP DATABASE `testdatabase`;
7: CREATE DATABASE `testdatabase`;
8: USE testdatabase;
9: CREATE DATABASE `testdatabase2`;
10: USE DATABASE testdatabase2;
11: USE `testdatabase2`;
12: USE `testdatabase`;
13: DROP DATABASE `testdatabase2`;


//表的创建和删除,创建表同时设置属性
14: CREATE TABLE table1;
15: CREATE TABLE table1(No INT);
16: CREATE TABLE table2 (No INT,name VARCHAR(20),sex boolean);
17: DROP TABLE table1,table2;
18: CREATE TABLE tt1 (id INT(11) primary key);
19: CREATE TABLE tt2 (id int primary key, name varchar(20) unique, age int not null, sex boolean default '1');
    //boolean 实际上是一个int,因此值为0或1
20:CREATE TABLE tt3 (id int primary key, age int not null auto_increment);
21: CREATE TABLE tt3 (id int primary key auto_increment, age int not null);
    //一个表只能有一个auto_increment约束,且约束的属性必须是主键或主键的一部分
22:show tables;
23:drop table tt1,tt2,tt3;show tables;


//key键:
    //主键:
24:create table tt1 (id int primary key);
25: create table tt2 (id int,name varchar(20),primary key (id));
26: create table tt3 (id int,name varchar(20),age int,primary key(id,name));
27: create table tt4(id int primary key,age int primary key);//只可以有一个主键
28: drop table tt1,tt2,tt3,tt4;


    //外键:
29: create table tt1(id int(4),uid int(4));
30: create table tt2(id int(4),uid int(4),constraint nickname foreign key(uid) references tt1(uid));
31: drop table tt1;
    create table tt1(id int,uid int,primary key(id,uid));
32: create table tt2(id int,uid int,constraint nickname foreign key(uid) references tt1(id));
//由上四条可知外键必须依赖于父表的主键上
33:create table tt2(id int(4),uid int(4),constraint nickname foreign key(uid) references tt1(id));
    //外键连接必须连接的是主表的主键,如果连接主表主键的一部分,必须连接主键的第一个字段
34: create table tt3(id int,uid int,constraint nick name foreign key(id) references tt1(id));
35: drop table tt1,tt2,tt3;//此处会报错,因为有外键依赖要先删子表才能删父表,如果输入drop table tt3,tt2,tt1;则不会报错


36: create table tt1(Id int unsigned,uid int,primary key(id));
37: create table tt2(id int,uid int,constraint nickname foreign key (uid) references tt1(id));
38: create table tt2(id int,uid int unsigned,constraint foreign key (uid) references tt1(id));
    //外键关联的主从表字段数据类型必须完全一样,否则报错“foreign key constraint is incorrectly formed”




//查看表:
39:describe tt1;
    describe tt2;
40: desc tt1;
    desc tt2;
41: show create table tt1;
    show create table tt2;
42: show create table tt1 \G 
    show create table tt2 \G


//修改表:
    //修改表名
43:alter table tt1 rename tt2;
44: alter table tt1 rename temp;
45: desc tt1;
    desc temp;
46: alter table temp rename to tt1;
47: drop table tt1,tt2;


    //修改表属性数据类型
48: create table tt1(id int,name varchar(20));
49: alter table tt1 modify id varchar(20);
50: insert into tt2(id,name) values(1,'string');//插入和删除操作还没有涉及,在这里只是为了加入一条数据已说明下面的问题
51:alter table tt1 modify name int;//有值的情况下转换字段类型失败,因为字符串类型无法自动转型为整数类型
52:delete from tt1;//删除表中元素
53: alter table tt1 modify name int;
    desc tt1;




    //修改表属性
54:alter table tt1 change name newname int; 
    desc tt1;
55: alter table tt2 change newname name varchar(20);
    desc tt2;


    //增加字段
56: alter table tt1 add name tinytext after id;
    desc tt1;
57: alter table tt1 add age int not null after id;desc tt1;
58: alter table tt1 add no int unique first;desc tt1;


    //删除字段
59:alter table tt1 drop age;desc tt1;desc tt1;


    //修改属性排列顺序
60:alter table tt1 modify no int after name;desc tt1;
61: alter table tt1 modify no int first;desc tt1;
62: drop table tt1,tt2;


//索引:
    //创建带索引的数据库表
63:create table tt1(id int,name varchar(20),age int,index index1(id));
64: show create table tt1\G
65: explain select * from tt1 where id=1;//explain语句用来查看索引详细情况,参考博文:http://blog.csdn.net/zhuxineli/article/details/14455029
66:create table tt2(id int,name varchar(20),age int,unique index index2(id));//唯一索引,唯一索引指的是被索引的字段值唯一
67: show create table tt2\G
68: create table tt3(id int,name varchar(20),age int,fulltext index index3(id));//全文索引
69: create table tt3(id int,name varchar(20),age int,fulltext index index3(name));
70: show create table tt3\G
71: create table tt4(id int,age int,info varchar(50),index index4(info(10)));
72: show create table tt4\G
73: create table tt5(id int,name varchar(20),info varchar(50),index index5 (id,name));
74: show create table tt5\G
75: explain select * from tt5 where name="nnn"\G
76: explain select * from tt5 where id='1'\G


    //在已存在表上添加索引
//create [unique|fulltext|spatial] index indexname on tablename (columnname [(length)] [ASC|DESC]);


77: drop database testdatabase;
    create database testdatabase;
    use testdatabase;
78: create table tt1(id int,name varchar(20));
79: create index index1 on tt1 (id);
80: create index index1 on tt1 (name);//同一表的索引别名不能相同
81: create index index2 on tt1 (name);
82: drop index index2 on tt1;
83: create table tt2(id int,name varchar(20),age int,sex boolean,info varchar(50));
84: create index index1 on tt2 (id);//不同表的索引别名可以相同
85:create unique index index2 on tt2 (id);//不同索引可以重复使用相同字段
86: create fulltext index index3 on tt2 (name);
87: create index index4 on tt2 (sex,age);//多字段索引
88:show create table tt2;
89: drop table tt1,tt2;
90: show tables;


    //Alter语句添加索引
    //Alter table tablename add [unique|fulltext|spatial] index indexname (columename [(length)] [ASC|DESC]);
91: create table tt1(id int);
92: alter table tt1 add index index1 (id);
93: create table tt2 (id int,age int,name varchar(20),sex boolean, info varchar(50));
94:alter table tt2 add index index2 (id,name);
95: alter table tt2 add unique index index3 (sex);
96: alter table tt2 add index index4 (info(10));
97: alter table tt2 add fulltext index index5 (info(10));
    show create table tt2;


    //删除索引
98: drop index index1 on tt2;
    show create table tt2;//观察删除索引后的表信息
99:drop table tt1,tt2;
100: exit
//完成第一阶段练习 撒花








//第二阶段


//重建数据库
101, create database testdatabase;use database testdatabase;
102, create table tt1(id int, name varchar(20),age int,sex boolean);
103, show tables;desc tt1;


//插入
104, insert into tt1 values(1,"zhang",25,0);
105, insert into tt1 values(2,"wang",25,1);
106, select * from tt1;
107, insert into tt1(id,name,age,sex) values(3,"li",28,1);
108, insert into tt1(id,name,sex,age) values(4,"sun",0,22);
109, insert into tt1(id,name,sex) values(5,"zhao",30,1);
110, insert into tt1(id,age,name) values(6,"he",47,0);
111, insert into tt1(id,age,name) values(7,"chen",22,1),(7,"zhang",22,1),(7,"xie",32,1);
112, select * from tt1;


//修改
113, update tt1 set id=10,name="new",age=100,sex=0 where id=1; select * from tt1;
114, update tt1 set id=11,name="new" where id=2,age=25; select *from tt1;
115, update tt1 set id=12,sex=1 where id=7; select * from tt1;
116, update tt1 set sex=1 where id>3;
117, update tt1 set sex=0 where id<4;




//删除
118, delete from tt1 where id=1;select * from tt1;
119, delete from tt1 where id=12;select * from tt1;


//查询
120, alter table tt1 add address varchar(30);
121, update tt1 set address="Beijing" where sex=1;
122, update tt1 set address="Shanghai" where sex=0;
//简单查询
123, select id from tt1;
124, select id,name from tt1;
125, select id,name,address from tt1;
//条件查询
126, select id,name,address from tt1 where address="Beijing";
127, select * from tt1 where id in(2,3,4);
128, select * from tt1 where id not in(2,3,4);
129, select * from tt1 where id between 2 and 5;
130, select * from tt1 where id not between 2 and 5;
131, select * from tt1 where address like "beijing";
132, select * from tt1 where address like "bei";
133, select * from tt1 where address like "bei%";
134, select * from tt1 where address not like "bei%";
135, select * from tt1 where address is null;
136, select * from tt1 where address is not null;
137, select * from tt1 where age<20 and sex=1;
138, select * from tt1 where sex=0 or age>30;
//查询结果不重复
139, select distinct address from tt1;  
//查询结果排序
140, select * from tt1 order by age;
141, select * from tt1 order by age asc;
142, select * from tt1 order by age desc;
//分组查询
143, select * from tt1 group by sex;//单独使用group by 只会选择每个分组的一条记录
//group by 与 group_concat结合使用
144, select group_concat(name),sex from tt1 group by sex;
145, select group_concat(name),group_concat(age),sex from tt1 group by sex;
146, select group_concat(name,age),sex from tt1 group by sex;
//group by与集合函数结合使用
147, select sex,count(sex) from tt1 group by sex;
148, select sex,count(sex) from tt1 group by sex having sex>2;//having用于对分组后的结果加限制条件
149, select sex,count(sex) from tt1 group by sex having count(sex)>2;
//with rollup
150, select sex,count(sex) from tt1 group by sex with rollup;//在显示记录的最后一行加一条,记录是上面所有记录的和,通常用于合计数量
//limit显示限制
151, select * from tt1;
152, select * from tt1 limit 2;
153, select * from tt1 limit 3;
154, select * from tt1 limit 0,2;
155, select * from tt1 limit 1,2;
//使用集合函数查询
//为了更好的理解本问题,新建一个表
156, create table grade(id int,name varchar(10),subject varchar(10),score int,sex boolean);
157, insert into grade values(1,"wang","math",100,1),(1,"wang","english",96,1),(1,"wang","physics",90,1);
     insert into grade values(2,"li","math",96,1),(2,"li","english",85,1),(2,"li","physics",99,1);
     insert into grade values(3,"sun","math",85,0),(3,"sun","english",98,0),(3,"sun","physics",80,0);
158, select * from grade;
159, select count(*) from grade;
160, select id,name,sum(score) from grade where id=1;
161, select id,name,sun(score) from grade group by id;
162, select id,name,sum(score) from grade group by id order by sum(score) desc;
163, select id,name,avg(score) from grade where id=2;
164, select id,name,avg(score),sum(score) from grade where id =3;
165, select id,name,avg(score) from grade group by id;
166, select subject,avg(score) from grade group by subject;* from 
167, select subject,avg(score) from grade group by subject order by avg(score);
168, select name,max(score) from grade where subject="math";
169, select name,max(score) from grade where subject="english";
//连接查询
    //内连接
170, create table stu(id int,name varchar(10),age int,sex boolean);
171, insert into stu values(1,"wang",25,1),(2,"li",23,1),(3,"sun",23,0),(4,"zhou",27,1),(5,"zhang",22,0);
172, select id,name,age,sex,score from stu,grade where stu.id=grade.id;
173, select stu.id,stu.name,stu.age,stu.sex,score from stu,grade where stu.id=grade.id;
174, select stu.id,stu.name,stu.age,stu.sex,score from stu,grade where stu.id=grade.id and score>90;
    //外连接
175, select stu.id,stu.name,stu.age,stu.sex,score from stu left join grade on stu.id=grade.id;
176, select stu.id,stu.name,stu.age,stu,sex,score from stu right join grade on stu.id=grade.id;
177, insert into grade values(4,"hu","math",99,1),(5,"liu","english",97,0);
178, select stu.id,stu.name,stu.age,stu.sex,subject,score from stu left join grade on stu.id=grade.id;
179, select stu.id,stu.name,stu.age,stu.sex,subject,score from stu right join grade on stu.id=grade.id;
    //子查询
180, select * from stu where id in (select *from grade);
181, select * from grade where id in (select * from stu);
182, create table scholarship(level int,money int,score int);
183, insert into scholarship values(1,10000,90),(2,5000,85),(3,3000,80);
184, select id,name,score from grade where score>=(select score from scholarship where level=1);
185, select id,name,score from grade where score>=(select min(score) from scholarship);
    //exists子查询
186, select * from stu where exists (select name from grade where id=1);
187, select * from grade where score>any(select score from scholarship);
188, select * from grade where score>all(select score from scholarship);
//合并查询结果
189, select name from stu union select name from grade;
190, select name from stu union all select name from grade;
//别名
191, select * from scholarship s where s.level=1;
192, select * from scholarship s where s.money=5000;
193, select s.name from stu s,grade g where s.name=g.name;
194, select subject as su,score as sc from grade;
195, select * from stu where name regexp "^w";
196, select * from stu where name regexp "g$";
197, select * from stu where name regexp "^w.g$";
198, select * from stu where name regexp "^w..g$";
199, select * from stu where name regexp "[aeo]";
200, select * from stu where name regexp "[a-g]";
















#建学生信息表student
create table student
(
sno varchar(20) not null primary key,
sname varchar(20) not null,
ssex varchar(20) not null,
sbirthday datetime,
class varchar(20)


);
#建立教师表
create table teacher
(
tno varchar(20) not null primary key,
tname varchar(20) not null,
tsex varchar(20) not null,
tbirthday datetime,
prof varchar(20),
depart varchar(20) not null


);
#建立课程表course
create table course
(
cno varchar(20) not null primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher(tno)


);
#建立成绩表
create table score
(
sno varchar(20) not null primary key,
foreign key(sno) references student(sno),
cno varchar(20) not null,
foreign key(cno) references course(cno),
degree decimal


);


#添加学生信息
insert into student values('108','曾华','男','1977-09-01','95033');
insert into student values('105','匡明','男','1975-10-02','95031');
insert into student values('107','王丽','女','1976-01-23','95033');
insert into student values('101','李军','男','1976-02-20','95033');
insert into student values('109','王芳','女','1975-02-10','95031');
insert into student values('103','陆君','男','1974-06-03','95031');








#添加教师表
insert into teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');


#添加课程表
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
#添加成绩表


insert into score values('103','3-245','86');
insert into score values('105','3-245','75');
insert into score values('109','3-245','68');
insert into score values('103','3-105','92');
insert into score values('105','3-105','88');
insert into score values('109','3-105','76');
insert into score values('103','3-105','64');
insert into score values('105','3-105','91');
insert into score values('109','3-105','78');
insert into score values('103','6-166','85');
insert into score values('105','6-166','79');
insert into score values('109','6-166','81');


 


 


 


1、 查询Student表中的所有记录的Sname、Ssex和Class列。










1
 


select Sname,Ssex,Class from student
 


2、 查询教师所有的单位即不重复的Depart列。










1
 


select distinct Depart from teacher
 


3、 查询Student表的所有记录。










1
 


select *  from student
 


4、 查询Score表中成绩在60到80之间的所有记录。










1
 


select *  from Score where Degree between 60 and 80
 


5、 查询Score表中成绩为85,86或88的记录。










1
 


select *  from Score where Degree in(85,86,88)
 


6、 查询Student表中“95031”班或性别为“女”的同学记录。










1
 


select *  from Student where class='95031' or Ssex='女'
 


7、 以Class降序查询Student表的所有记录。










1
 


select *  from student order by class desc
 


8、 以Cno升序、Degree降序查询Score表的所有记录。










1
 


select *  from Score order by cno asc,degree desc
 


9、 查询“95031”班的学生人数。










1
 


select count(*)  from student where class='95031'
 


10、     查询Score表中的最高分的学生学号和课程号。(子查询或者排序)










1
 


select Sno,Cno  from Score where Degree=(select max(Degree) from Score)
 










1
 


select Sno,Cno  from Score order by  Degree desc limit 0,1
 


11、     查询每门课的平均成绩。










1
 


select Cno,avg(degree) from Score group by Cno
 


12、     查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。










1
 


select avg(Degree) from score where Cno like '3%' and Cno in (select Cno from score group by Cno having count(*)>=5) 用in 不用= 是因为可能会有多个
 








1
 


简单写法:select avg(Degree) from score where Cno like '3%' and group by Cno having count(*)>=5
 


13、     查询分数大于70,小于90的Sno列。










1
 


select Sno from Score where degree>70 and degree<90
 


14、     查询所有学生的Sname、Cno和Degree列。










1
 


select  Sname, Cno,Degree from Score , student where Score.Sno=student.Sno
 


15、     查询所有学生的Sno、Cname和Degree列。










1
 


select  Sno,Cname,Degree from Score , Course where Score.Cno=Course.Cno
 


16、     查询所有学生的Sname、Cname和Degree列。










1
 


select Sname,Cname,Degree from student,course,score where student.Sno=score.Sno and course.Cno=score.Cno
 








1
 


join .. on 写法:select Sname,Cname,Degree from student join score on student.Sno=score.Sno join course on course.Cno=score.Cno
 


17、     查询“95033”班学生的平均分。










1
 


select avg(degree) as 'class=95033' from Score where Sno in (select Sno from  Student where Class='95033' )  
 


18、 假设使用如下命令建立了一个grade表:


create table grade(low  int(3),upp  int(3),rank  char(1))


insert into grade values(90,100,’A’)


insert into grade values(80,89,’B’)


insert into grade values(70,79,’C’)


insert into grade values(60,69,’D’)


insert into grade values(0,59,’E’)


现查询所有同学的Sno、Cno和rank列。










1
 


select Sno,Cno,rank from Score,grade where degree between low and upp
 


19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。








1
 


<span style="color: #000000; font-size: 15px">109同学,选修是3-105课的</span>
 










1
 


select * from score where Cno='3-105' and degree>(select max(degree ) from Score where Sno='109' and Cno='3-105' )
 








1
 


<span style="font-size: 15px">109同学,没有选修3-105课</span>
 










1
 


select * from score where Cno='3-105' and degree>(select max(degree ) from Score where Sno='109')
 


and degree<( select max(degree ) from Score where sno in (select Sno from score group by Sno having count(*)>1))








1
 


选了多门课程并且是这个课程下不是最高分的
 










1
 


select * from score a where Sno in (select Sno from score group by Sno having count(*)>1) and degree<( select max(degree ) from Score b where b.cno = a.cno)
 


21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。










1
 


Select * from score where degree>(select degree from Score where Sno='109' and Cno='3-105' )
 


22、查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。










1
 


select sno,sname,sbirthday  from student where year(sbirthday) = (select year(sbirthday) from student where  sno='108') 
 










1
 


select sno,sname,sbirthday  from student where year(sbirthday) = (select year(sbirthday) from student where  sno='101')
 


23、查询“张旭“教师任课的学生成绩。










1
 


select Sno,degree from score,Course  where score.Cno=Course.Cno and Course.Tno= (select Tno from Teacher where Tname='张旭' )
 










1
 


select degree from score where Cno in (select cno from course where Tno= (select Tno from Teacher where Tname='张旭'  ) )
 


24、查询选修某课程的同学人数多于5人的教师姓名。










1
 


select Tname from Teacher,  Course where Teacher.Tno=Course.Tno and Course.Cno =(select Cno from Score group by Cno having count(*)>5)
 










1
 


select Tname from Teacher where tno=( select Tno from Course where cno=( select Cno from Score group by Cno having count(*)>5 ))
 


25、查询95033班和95031班全体学生的记录。










1
 


select * from  student where  class in ('95033','95031')
 


26、  查询存在有85分以上成绩的课程Cno.










1
 


select Cno from score where degree>85
 


27、查询出“计算机系“教师所教课程的成绩表。










1
 


select * from course  where cno in (select cno from course  where tno in (select tno from teacher where Depart='计算机系'))
 


28、查询“计算 机系”与“电子工程系“不同职称的教师的Tname和Prof。










1
 


select Tname,Prof from Teacher where Depart ='计算机系' and Prof  not in( select Prof from Teacher where Depart ='电子工程系')<br>union <br>select Tname,Prof from Teacher where Depart ='电子工程系' and Prof  not in( select Prof from Teacher where Depart ='计算机系')
 


29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。


any:代表括号中任意一个成绩就可以










1
 


select Cno,Sno,Degree from score where cno='3-105' and degree >any(select degree from score where cno='3-245' ) order by degree  desc
 


30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.


all:代表括号中的所有成绩










1
 


select Cno,Sno,Degree from score where cno='3-105' and degree >all(select degree from score where cno='3-245' ) order by degree desc
 


31、 查询所有教师和同学的name、sex和birthday.










1
 


select tname,tsex,tbirthday from Teacher union select sname,ssex,sbirthday from Student
 


32、查询所有“女”教师和“女”同学的name、sex和birthday.










1
 


select Tname,Tsex,Tbirthday from Teacher where Tsex='女' union select Sname,Ssex,Sbirthday from Student where Ssex='女'
 


33、 查询成绩比该课程平均成绩低的同学的成绩表。










1
 


select * from score a  where degree < (select avg(degree) from score b where b.cno=a.cno)
 


34、 查询所有任课教师的Tname和Depart.










1
 


select Tname,Depart from Teacher where tno in (select tno from course )
 


35 、 查询所有未讲课的教师的Tname和Depart.










1
 


select Tname,Depart from Teacher where Tno not in (select Tno from Course where cno in (select cno from score  ))
 


36、查询至少有2名男生的班号。










1
 


select class from student where ssex='男' group by class having count(*)>1
 


37、查询Student表中不姓“王”的同学记录。










1
 


select * from Student where Sname not  like '王%%'
 


38、查询Student表中每个学生的姓名和年龄。










1
 


select Sname, year(now())-year(sbirthday)  from Student
 


39、查询Student表中最大和最小的Sbirthday日期值。










1
 


select Max(Sbirthday ),Min(Sbirthday ) from Student
 


40、以班号和年龄从大到小的顺序查询Student表中的全部记录。










1
 


select * from Student  order by  class desc, Sbirthday
 


41、查询“男”教师及其所上的课程。










1
 


select Tname,Cname from course,teacher where course.tno= teacher.tno and teacher.Tsex='男'
 


42、查询最高分同学的Sno、Cno和Degree列。










1
 


select Sno,Cno,Degree from score where degree=(select max(degree) from score)
 


排序写法:










1
 


select Sno,Cno,Degree from score order by degree desc limit 0,1
 


43、查询和“李军”同性别的所有同学的Sname.










1
 


select Sname from Student where Ssex = (select Ssex from Student where Sname='李军')
 


44、查询和“李军”同性别并同班的同学Sname.










1
 


select Sname from Student where Ssex = (select Ssex from Student where Sname='李军' ) and class=( select class from student where Sname='李军')
 


45、查询所有选修“计算机导论”课程的“男”同学的成绩表。










1
 


select  Sno,Cno,degree from score where Cno=( select Cno from course where Cname='计算机导论') and Sno in (select Sno from student where Ssex='男')
 


好文要顶 








#查看服务器中的数据库
SHOW DATABASES;


#创建一个数据库
CREATE DATABASE IF NOT EXISTS test;


#使用test数据库
USE test;


#建表
-- 表名丌能超过 30 个字符
-- 表名、列名是自由定义的
-- 所有的 SQL 语句都是以“ ; ”结尾
CREATE TABLE user_test(
            id  int,
            password varchar(6),
            name varchar(20),
            phone varchar(11),
            email varchar(20)
);


#修改表的某一列
alter table user_test modify email varchar(50);


#插入数据
insert into user_test values(1001,'123456','张三','13468857714','linglongxin24@163.com');


#查询数据
SELECT * from user_test;


#查看表结构
desc user_test;


#查询姓名为张三的记录
SELECT name FROM user_test where name='张三';


#创建部门表
CREATE TABLE dept_test(
dept_id int(2),
dept_name CHAR(20),
dept_location CHAR(20)
);


#查看部门表结构
desc dept_test;




#给部门表插入数据
INSERT INTO dept_test VALUES(10,'developer','beijing');
INSERT INTO dept_test VALUES(20,'account','shanghai');
INSERT INTO dept_test VALUES(30,'sales','guangzhou');
INSERT INTO dept_test VALUES(40,'operations','tianjin');


#查看部门表数据
SELECT * FROM dept_test;


#创建员工表
CREATE TABLE emp_test(
            emp_id INT(4),
            name VARCHAR(20),
            job VARCHAR(20),
            salary FLOAT(7,2),
            bonus FLOAT(7,2),
            hire_date DATE,
            manager INT(4),
            dept_test_id INT(2)
);


#查看员工表结构
DESC emp_test;


#查看员工表数据
SELECT * FROM EMP_TEST;




#格式化日期用DATE_FORMAT(date,format)和Oracle有区别,Oracle使用用TO_DATE(ch, fmt)
INSERT INTO emp_test VALUES(1001,'张无忌','Manager','10000','2000',DATE_FORMAT('2010-01-12','%y-%m-%d'),1005,10);
INSERT INTO emp_test VALUES(1002,'刘苍松','Analyst', 8000 , 1000 ,DATE_FORMAT('2011-01-12','%y-%m-%d'),1001,10);
INSERT INTO emp_test VALUES(1003, '李翊' ,'Analyst',9000 , 1000 ,DATE_FORMAT('2010-02-11','%y-%m-%d'),1001,10);
INSERT INTO emp_test VALUES(1004,'郭芙蓉','Programmer',5000, NULL ,DATE_FORMAT('2010-02-11','%y-%m-%d'),1001,10);
INSERT INTO emp_test VALUES(1005 , '张三丰' , 'President' ,15000 , NULL ,DATE_FORMAT('2008-02-15','%y-%m-%d'),NULL,20);
INSERT INTO emp_test VALUES(1006 , '燕小六' , 'Manager' ,5000 ,400 ,DATE_FORMAT('2009-02-12','%y-%m-%d') , 1005 , 20);
insert into emp_test values(1007 , '陆无双' , 'clerk' ,3000 , 500 , DATE_FORMAT('2009-02-01','%y-%m-%d') , 1006 , 20) ;
insert into emp_test values(1008 , '黄蓉' , 'Manager' ,5000 , 500 , DATE_FORMAT('2009-11-01','%y-%m-%d') , 1005 , 30) ;
insert into emp_test values(1009 , '韦小宝' , 'salesman' ,4000 , null , DATE_FORMAT('2009-05-20','%y-%m-%d') , 1008 , 30) ;
insert into emp_test values(1010 , '郭靖' , 'salesman' ,4500 , 500 , DATE_FORMAT('2009-10-10','%y-%m-%d') , 1008 , 30) ;


#查看员工表数据
SELECT * FROM emp_test;


#查看部门表数据
SELECT * FROM dept_test;


#查看员工姓名、月薪、年薪
SELECT name,salary,salary*12 year_salary
    FROM emp_test;


#查看员工姓名、月薪、奖金、月实发工资
#非空判断用IFNULL(expr1,expr2)和Oracle有区别,Oracle用NVL(expr1, expr2)
SELECT name,salary,bonus,salary+ IFNULL(bonus,0) month_salary
    FROM emp_test;
    INSERT INTO emp_test (emp_id,name) VALUES(1011,'于泽成');


#查询 emp_test 表 , 如果没有职位 , 显示'no position' , 如果有职位 , 显示员工的职位
SELECT name,IFNULL(job,'no positon') job
            FROM emp_test;


#查询 emp_test 表 , 如果没有入职时间 , 显示为 2011 年 10 月 10 日 , 否则原样显示
SELECT name,IFNULL(hire_date,'2016-12-12') hire_date
            FROM emp_test;


#连接字符串用CONCAT(str1,str2,...)和Oracle有区别,Oracle用||
SELECT emp_id,CONCAT(name,' job is ',job) detail
            FROM emp_test;


#复制表
CREATE TABLE emp_test2 AS SELECT * FROM emp_test;
SELECT * FROM emp_test2;


#DISTINCT注意:distinct 必须( 只能 )跟在 select 后边,distinct 指所有列的唯一组合
#机构中有多少种职位?
SELECT DISTINCT job FROM emp_test;
#员工分布在哪些部门?
SELECT DISTINCT dept_test_id FROM emp_test;
#查询每个部门不重复的职位
SELECT DISTINCT job,dept_test_id FROM emp_test;


#薪水高亍 10000 元的员工数据?
SELECT * FROM emp_test WHERE salary>10000; 


#职位是 Analyst 的员工数据?SQL 语句大小写丌敏感 , 数据大小写敏感
SELECT * FROM emp_test WHERE LOWER(job)='analyst'; 


#薪水大亍 5000 并且小亍 10000 的员工数据?>=<=;between and
SELECT * FROM emp_test WHERE salary>=5000 AND salary<=10000; 
SELECT * FROM emp_test WHERE salary BETWEEN 5000 AND 10000; 


#入职时间在 2011 年的员工?
#错误写法
SELECT * FROM emp_test WHERE hire_date=DATE_FORMAT('2011','%y'); 
#正确写法
SELECT * FROM emp_test WHERE hire_date BETWEEN DATE_FORMAT('2011-01-01','%y-%m-%d') AND DATE_FORMAT('2011-12-31','%y-%m-%d') ; 


#列出职位是 Manager 或者 Analyst 的员工
SELECT * FROM emp_test WHERE job IN('Manager','Analyst');


#列出职位中包含有 sales 字符的员工数据?
SELECT * FROM emp_test WHERE LOWER(job) LIKE '%sales%'


#列出职位中第二个字符是 a 的员工数据?
SELECT * FROM emp_test WHERE LOWER(job) LIKE '_a%'


#查询数据库中有多少个名字中包含 'EMP' 的表?
#SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME LIKE '%EMP%';


insert into emp_test values(1012 , 'text_test' , 'salesman' ,4500 , 500 , DATE_FORMAT('2009-10-10','%y-%m-%d') , 1008 , 30) ;


SELECT * FROM emp_test;


#mysql-- 如果要查询的数据中有特殊字符( 比如_或% ),
-- 在做模糊查询时 ,
-- 需要加上\符号表示转义 , 如果是Oracle并且用 escape 短语指明转义字符\mysql不用
SELECT name FROM emp_test WHERE name LIKE '%\_%';
DELETE FROM emp_test WHERE emp_id=1012 


#查询哪些员工没有奖金?
SELECT * FROM emp_test WHERE bonus IS NULL;


#薪水不在 5000 至 8000 的员工?
SELECT * FROM emp_test WHERE salary NOT BETWEEN 5000 AND 8000;


#不是部门 20 和部门 30 的员工?
SELECT * FROM emp_test WHERE dept_test_id NOT IN(20,30);












CREATE  TABLE  student (


id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  ,


name  VARCHAR(20)  NOT NULL ,


sex  VARCHAR(4)  ,


birth  YEAR,


department  VARCHAR(20) ,


address  VARCHAR(50) 


);


创建score表。SQL代码如下:


CREATE  TABLE  score (


id  INT(10)  NOT NULL  UNIQUE  PRIMARY KEY  AUTO_INCREMENT ,


stu_id  INT(10)  NOT NULL ,


c_name  VARCHAR(20) ,


grade  INT(10)


);


为student表和score表增加记录
INSERT INTO student VALUES( 901,'张老大', '男',1985,'计算机系', '北京市海淀区');


INSERT INTO student VALUES( 902,'张老二', '男',1986,'中文系', '北京市昌平区');


INSERT INTO student VALUES( 903,'张三', '女',1990,'中文系', '湖南省永州市');


INSERT INTO student VALUES( 904,'李四', '男',1990,'英语系', '辽宁省阜新市');


INSERT INTO student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');


INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');






向score表插入记录的INSERT语句如下:


INSERT INTO score VALUES(NULL,901, '计算机',98);


INSERT INTO score VALUES(NULL,901, '英语', 80);


INSERT INTO score VALUES(NULL,902, '计算机',65);


INSERT INTO score VALUES(NULL,902, '中文',88);


INSERT INTO score VALUES(NULL,903, '中文',95);


INSERT INTO score VALUES(NULL,904, '计算机',70);


INSERT INTO score VALUES(NULL,904, '英语',92);


INSERT INTO score VALUES(NULL,905, '英语',94);


INSERT INTO score VALUES(NULL,906, '计算机',90);


INSERT INTO score VALUES(NULL,906, '英语',85);






mysql> SELECT * FROM student;




mysql> SELECT * FROM student LIMIT 1,3;


mysql> SELECT id,name,department FROM student;


mysql> SELECT * FROM student WHERE department IN ('计算机系','英语系');




mysql> SELECT id,name,sex,2013-birth AS age,department,address


    -> FROM student


    -> WHERE 2013-birth BETWEEN  18 AND 22;








mysql> SELECT id,name,sex,2013-birth AS age,department,address


    -> FROM student


    -> WHERE 2013-birth>=18 AND 2013-birth<=22;


mysql> SELECT c_name,MAX(grade) FROM score GROUP BY c_name;




mysql> SELECT c_name, grade


    ->      FROM score WHERE stu_id=


    ->  (SELECT id FROM student


    ->    WHERE name= '李四' );






mysql> SELECT student.id,name,sex,birth,department,address,c_name,grade


    -> FROM student,score


    ->  WHERE student.id=score.stu_id;




mysql> SELECT student.id,name,SUM(grade) FROM student,score


    -> WHERE student.id=score.stu_id


    -> GROUP BY id;




mysql> SELECT c_name,AVG(grade) FROM score GROUP BY c_name;




mysql> SELECT * FROM student


    -> WHERE id IN


    -> (SELECT stu_id FROM score


    -> WHERE c_name="计算机" and grade<95);






mysql> SELECT *  FROM student


    ->  WHERE id =ANY


    ->  ( SELECT stu_id FROM score


    ->  WHERE stu_id IN (


    ->          SELECT stu_id FROM


    ->          score WHERE c_name=  '计算机')


    ->  AND c_name= '英语' );






mysql> SELECT a.* FROM student a ,score b ,score c


    -> WHERE a.id=b.stu_id


    -> AND b.c_name='计算机'


    -> AND a.id=c.stu_id


    -> AND c.c_name='英语';






mysql> SELECT stu_id, grade


    ->  FROM score WHERE c_name= '计算机'


    ->  ORDER BY grade DESC;






mysql> SELECT id  FROM student


    -> UNION


    -> SELECT stu_id  FROM score;






mysql> SELECT student.id, name,sex,birth,department, address, c_name,grade


    -> FROM student, score


    -> WHERE


    ->  (name LIKE  '张%'  OR name LIKE  '王%')


    ->  AND


    ->  student.id=score.stu_id ;






mysql> SELECT student.id, name,sex,birth,department, address, c_name,grade


    -> FROM student, score


    -> WHERE address LIKE '湖南%'   AND


    ->  student.id=score.stu_id;



发布了231 篇原创文章 · 获赞 77 · 访问量 55万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 书香水墨 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览