学习MySQL需要多加练习，才能掌握各种查找的诀窍。
1：创建库：school
2：创建stu表：学生编号，姓名，年龄，性别，籍贯，不加任何约束；
3：添加主键约束
4：添加唯一约束
5：添加默认约束，添加非空约束
6：删除表
10：查询名字为XXX学生的信息
15：查询年龄不在20到25岁之间的学生信息
16：把学号为3的学生籍贯改为四川
17：把叫XXX学生、年龄为30的学生籍贯改为陕西
7：创建stu表：学生编号，姓名，年龄，性别，籍贯(可以为空)，添加3种约束；
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) UNIQUE,
sage INT,
ssex VARCHAR(2) DEFAULT "男",
address VARCHAR(50) NOT NUll
)charset='utf8';
/* add CONSTRAINT pk PRIMARY key(sid) ;
add CONSTRAINT pk UNIQUE(sname)*/
/*add CONSTRAINT psk CHECK(address is not null) ;*/
/* ALTER table school ALTER sname SET DEFAULT "五福路";*/
8：给表添加内容
INSERT INTO stu VALUES(1,"李四",19,"女","陕西省西安市");
INSERT INTO stu (sname,sage,address) VALUES("王麻子",20,"海南省海口市");
INSERT INTO stu (sname,sage,address) VALUES("雷哥",20,"陕西省周至县");
INSERT INTO stu VALUES(4,"李啊",19,"女","陕西省西安市");
INSERT INTO stu (sname,sage,address) VALUES("哥132",20,"河南省周至县");
INSERT INTO stu (sname,sage,address) VALUES("啊123",19,"四川省西安市");
9：查询学号为3的学生信息；
SELECT * FROM stu WHERE sid=3;
11：查询所有学生的学号，姓名，地址和年龄；
SELECT *FROM stu;
12：查询籍贯是陕西的女生名字和年龄，
SELECT * FROM stu GROUP BY sname,sage HAVING address LIKE "%陕西%" and ssex = "女";
13：查询籍贯为陕西，河南，四川同学的信息；
SELECT * FROM stu WHERE address LIKE "%陕西%" or address LIKE "%河南%"OR address LIKE "%四川%"
14：查询年龄在20到25岁之间男生的名字和籍贯
SELECT * FROM stu GROUP BY sname,address HAVING sage >=20 OR sage<=25
UPDATE stu set address = "天津" WHERE sid = 4;
SELECT * FROM stu WHERE sid = 4;
UPDATE stu set address = "西藏" WHERE sage = 19 AND sname = "李啊";
18：把籍贯为陕西的男生的年龄加1；
UPDATE stu set sage = sage+1 WHERE address LIKE "%陕西%"
· 19：删除学号为5的学生信息；
delete FROM stu WHERE sid =5
20：删除年龄 大于19的学生信息；
DELETE FROM stu WHERE sage >19
1，创建库：school
2：创建stu表：学生编号，姓名，年龄，性别，籍贯，每日零花钱，语文成绩，英语成绩，不加任何约束；
3：添加主键约束
4：添加唯一约束
5：添加默认约束
6：删除表
7：创建stu表：创建stu表：学生编号，姓名，年龄，性别，籍贯，每日零花钱，语文成绩，英语成绩，添加3种约束
8：给表添加对应的内容
1查询籍贯为空的男生信息
2查询年龄为20,21,22,23,24,25,26的学员信息
3查询姓张的学员信息
4查询名字带雷的学员信息
5查询名字第二个字为雷的学员信息
6查询第二条到第四条信息
7查询所有男生信息，按年龄倒序和正序
8查询班里最大，最小，平均年龄是多少
9查询年龄最大的前3位同学信息
10查询班里大于18岁的学生人数
11查询所有英语成绩比语文成绩高的学生名字
12显示学员总成绩大于120的姓名和总成绩
13查询每个省各住多少个学生，并显示所在省
14查询居住人数大于1的省份名称
15检索男学生的人数和平均年龄
16降低所有男生20%的零花钱
17查询所有陕西男生年龄最大的前2位学员信息
CREATE TABLE stu1(
sid INT AUTO_INCREMENT PRIMARY KEY ,
sname VARCHAR (30) not NULL,
sage INT,
ssex VARCHAR(2) DEFAULT '男',
saddr VARCHAR(200),
money FLOAT(5,2),
chiScore int,
engscore int
);
INSERT into stu1(sid,sname,sage,saddr,ssex,money,chiScore,engScore) VALUES(1,"张三",20,"陕西","男",50.22 ,50 ,60 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("李四",21,"四川","男",30.6 ,90 ,60 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("雷六",22,"浙江","男",20.9 , 80, 80);
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("赵无",23,"河南","女", 66.6, 50, 50);
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("赵雷",24,"北京","男", 55.5, 80,80 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("周树人",25,"俄罗斯","女", 77.7, 60,60 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("李逵",26,"中国香港","男",88.8 , 75,75 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("周里",25,"上海","女", 77.7, 59,60 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("吴京",26,"中国香港","男",100.00 , 100,100 );
INSERT into stu1(sname,sage,saddr,ssex,money,chiScore,engScore) VALUES("杨树林",26,"","男",60 , 60,70 );
INSERT into stu1(sname,sage, ssex,money,chiScore,engScore) VALUES("杨星",19 ,"男",60 , 60,70 );
/*1查询籍贯为空的男生信息*/
SELECT * FROM stu WHERE saddr ="";
/*2查询年龄为20,21,22,23,24,25,26的学员信息*/
/*3查询姓张的学员信息*/
/*4查询名字带雷的学员信息*/
/*5查询名字第二个字为雷的学员信息*/
/*6查询第二条到第四条信息*/
LIMIT 1,3
/*7查询所有男生信息，按年龄倒序和正序*/
SELECT * FROM stu WHERE ssex="男" ORDER BY sage DESC;
/*8查询班里最大，最小，平均年龄是多少*/
SELECT MAX(sage) AS "最大",MIN(sage) AS "最小" FROM stu;
/*9查询年龄最大的前3位同学信息*/
SELECT * FROM stu ORDER BY sage DESC LIMIT 0,3;
/*10查询班里大于22岁的学生人数*/
SELECT COUNT(*) FROM stu WHERE sage>22;
/*11查询所有英语成绩比语文成绩高的学生名字*/
SELECT sname FROM stu WHERE engScore>chiScore;
/*12显示学员总成绩大于120的姓名和总成绩*/
SELECT sname,chiScore+engScore AS "总成绩" FROM stu WHERE (chiScore+engScore)>120;
/*13查询每个省各住多少个学生，并显示所在省*/
SELECT COUNT(*),saddr FROM stu GROUP BY saddr;/*理解*/
SELECT * FROM stu GROUP BY saddr;
/*14查询居住人数大于1的省份名称*/
SELECT COUNT(*),saddr FROM stu GROUP BY saddr HAVING COUNT(*)>1;/*理解*/
/*15检索男学生的人数和平均年龄*/
SELECT AVG(sage),COUNT(*) FROM stu WHERE ssex="男";
/*16降低所有男生20%的零花钱*/
UPDATE stu SET money=money*80/100 WHERE ssex="男";
/*17查询所有陕西男生年龄最大的前2位学员信息*/
SELECT * FROM stu WHERE saddr="陕西" AND ssex="男" ORDER BY sage DESC LIMIT 0,2;
创建三张表
学生表：学号，姓名，性别，年龄，所在系
课程表：课程编号，课程名称，课程学分
选课表：学号，课程号，成绩
给创建的表中添加对应需要的内容
1：查询与刘晨在同一个系的学生信息
2：查询选修了课程名为‘java’的学生学号和姓名
3：查询选修课程编号为1和2的学生学号和姓名
4：查询比刘晨年龄小的学生信息
5：查询其他系中比信息系中刘晨年龄小的学生姓名和年龄
6：查询每个学生的最高成绩
7：查询选修了全部课程的学生学号；
8：查询选课人数不少于2人的课程号及选课人数
9：得到每门课的选修人数
10：查询门门课程都及格的学生的学号
11：查询平均分不及格的学生学号和姓名
12：查询哪门课程有大于3名学生选择。
13：得到每门课的选修人数
CREATE TABLE stu3(
sid int PRIMARY KEY,
sname VARCHAR(20),
ssex VARCHAR(2),
sage int,
smajor VARCHAR(30)
);
CREATE TABLE course(
cid INT PRIMARY KEY,
cname VARCHAR(20),
cscore INT
);
CREATE TABLE lesson(
lsid int ,
lcid int,
score int ,
FOREIGN KEY(lsid) REFERENCES stu3(sid),
FOREIGN KEY(lcid) REFERENCES course(cid)
);
INSERT INTO stu3 VALUES(6,"黑","男",16,"计科");
INSERT INTO stu3 VALUES(1,"刘晨","女",19,"计科");
INSERT INTO stu3 VALUES(2,"老周","男",18,"会计");
INSERT INTO stu3 VALUES(3,"老刘","男",40,"人文");
INSERT INTO stu3 VALUES(4,"老王","女",20,"人文");
INSERT INTO stu3 VALUES(5,"老黑","男",28,"计科");
INSERT INTO course VALUES(1,"java",5);
INSERT INTO course VALUES(2,"人工智能",5);
INSERT INTO course VALUES(3,"大数据",4);
INSERT INTO course VALUES(4,"前端",5);
INSERT INTO course VALUES(5,"Python",3);
INSERT INTO lesson VALUES(1,2,88);
INSERT INTO lesson VALUES(1,3,60);
INSERT INTO lesson VALUES(1,5,90) ;
INSERT INTO lesson VALUES(2,2,60);
INSERT INTO lesson VALUES(2,3,55);
INSERT INTO lesson VALUES(3,1,88);
INSERT INTO lesson VALUES(3,2,60);
INSERT INTO lesson VALUES(3,3,100) ;
INSERT INTO lesson VALUES(4,3,88);
INSERT INTO lesson VALUES(4,4,70);
INSERT INTO lesson VALUES(4,5,90) ;
INSERT INTO lesson VALUES(5,1,90);
INSERT INTO lesson VALUES(5,3,90);
INSERT INTO lesson VALUES(5,5,90);
/*1*/
SELECT * FROM stu3 WHERE smajor = (SELECT smajor FROM stu3 WHERE sname = "刘晨") and sname != "刘晨"
/*2*/
SELECT sid ,sname FROM stu3 WHERE sid IN (SELECT lsid FROM lesson WHERE lcid = (SELECT cid FROM course WHERE cname = "java"))
/*3*/
/*SELECT sid,sname FROM stu3 WHERE sid IN (SELECT lsid FROM lesson WHERE lcid=1 or lcid = 2)*/
SELECT * FROM lesson WHERE lcid = 1 or lcid = 2 GROUP BY lsid HAVING count(*)=2
/*4*/
SELECT * FROM stu3 WHERE sage<(SELECT sage FROM stu3 WHERE sname = "刘晨")
/*5*/
SELECT sname,sage FROM stu3 WHERE ((sage<(SELECT sage FROM stu3 WHERE sname = "刘晨")) and (smajor !=(SELECT smajor FROM stu3 WHERE sname = "刘晨")))
/*6*/
SELECT MAX(score) FROM lesson GROUP BY lsid
/*7*/
SELECT lsid FROM lesson GROUP BY lsid HAVING count(*)= (SELECT count(*) FROM course)
/*8*/
SELECT lcid,count(lcid) FROM lesson GROUP BY lcid HAVING count(lcid)>2
/*9*/
SELECT lcid ,count(lcid) FROM lesson GROUP BY lcid
/*10*/
SELECT lsid FROM lesson GROUP BY lsid HAVING MIN(score)>=60
/*11*/
SELECT sid,sname FROM stu3 WHERE sid IN(SELECT lsid FROM lesson GROUP BY lsid HAVING(AVG(score)<60))
/*12*/
SELECT lcid,count(*) FROM lesson GROUP BY lcid HAVING(COUNT(lsid)>2)
/*13*/
SELECT lcid,count(*) FROM lesson GROUP BY lcid
0，课程表中的学分改为授课老师
1，查询语文成绩在60到80之间的学生姓名，课程名和成绩
2，查询平均分大于80的男生学号及平均分
3，查询选修1号课程的学生姓名和成绩
4，查询选修1号课程或者2号课程的学生姓名和成绩
5，查询选修1号课程和2号课程的学生姓名和成绩
6，查询年龄在18至22周岁的学生所选修的课程名称
7，查询选修“数据库原理”且成绩在80分以上的学生姓名
8，查出张三老师的所有学生的姓名和选课名称及成绩
9，检索出每个学生选修的课程的课程号，课程名及学生名字
CREATE TABLE stu3(
sid int PRIMARY KEY,
sname VARCHAR(20),
ssex VARCHAR(2),
sage int,
smajor VARCHAR(30)
);
CREATE TABLE course(
cid INT PRIMARY KEY,
cname VARCHAR(20),
ctname VARCHAR(20)
);
CREATE TABLE lesson(
lsid int ,
lcid int,
score int ,
FOREIGN KEY(lsid) REFERENCES stu3(sid),
FOREIGN KEY(lcid) REFERENCES course(cid)
);
INSERT INTO stu3 VALUES(6,"黑","男",16,"计科");
INSERT INTO stu3 VALUES(1,"刘某","女",19,"计科");
INSERT INTO stu3 VALUES(2,"老周","男",18,"会计");
INSERT INTO stu3 VALUES(3,"老刘","男",40,"人文");
INSERT INTO stu3 VALUES(4,"老王","女",20,"人文");
INSERT INTO stu3 VALUES(5,"老黑","男",28,"计科");
INSERT INTO course VALUES(1,"java","赵老师");
INSERT INTO course VALUES(2,"人工智能","雷老师");
INSERT INTO course VALUES(3,"大数据",'王老师');
INSERT INTO course VALUES(4,"数据库",'李老师');
INSERT INTO course VALUES(5,"Python",'张老师');
INSERT INTO lesson VALUES(1,2,88);
INSERT INTO lesson VALUES(1,3,60);
INSERT INTO lesson VALUES(1,5,90) ;
INSERT INTO lesson VALUES(2,2,60);
INSERT INTO lesson VALUES(2,3,55);
INSERT INTO lesson VALUES(3,1,88);
INSERT INTO lesson VALUES(3,2,60);
INSERT INTO lesson VALUES(3,3,100) ;
INSERT INTO lesson VALUES(4,3,88);
INSERT INTO lesson VALUES(4,4,70);
INSERT INTO lesson VALUES(4,5,90) ;
INSERT INTO lesson VALUES(5,1,90);
INSERT INTO lesson VALUES(5,3,90);
INSERT INTO lesson VALUES(5,5,90);
/*查询------------------------------*/
/*1，查询语文成绩在60到90之间的学生姓名，课程名和成绩*/
SELECT stu3.sname,course.cname,lesson.score FROM stu3 ,lesson,course WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid and score>=60 and score<=90 and course.cname = "java"
/*2，查询平均分大于80的男生学号及平均分*/
SELECT lsid,AVG(score) FROM lesson,stu3 WHERE stu3.sid = lesson.lsid GROUP BY lsid HAVING AVG(score)>80
/*3，查询选修1号课程的学生姓名和成绩*/
SELECT stu3.sname,lesson.score FROM stu3,lesson,course WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid and lesson.lcid = 1
/*4，查询选修1号课程或者2号课程的学生姓名和成绩*/
SELECT stu3.sname,lesson.score FROM stu3,lesson,course WHERE
stu3.sid = lesson.lsid and course.cid = lesson.lcid and(lesson.lcid=1 or lesson.lcid = 2)
/*5，查询选修1号课程和2号课程的学生姓名和成绩*/
SELECT stu3.sname, lesson.score FROM stu3,lesson,course WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid and (lesson.lcid = 2 or lesson.lcid = 1) GROUP BY lcid HAVING count(*)=2/*-----------------------*/
/*6，查询年龄在18至22周岁的学生所选修的课程名称*/
SELECT cname FROM course,lesson,stu3 WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid and (stu3.sage >=18 or stu3.sage <=22) GROUP BY course.cid
/*7，查询选修“数据库原理”且成绩在60分以上的学生姓名*/
SELECT sname FROM course,lesson,stu3 WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid and score>=60 and cname = "数据库"
/*8，查出张三老师的所有学生的姓名和选课名称及成绩*/
SELECT sname,cname,score FROM course,lesson,stu3 WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid and ctname = "张老师"
/*9，检索出每个学生选修的课程的课程号，课程名及学生名字*/
SELECT cid ,cname,sname FROM course,lesson,stu3 WHERE stu3.sid = lesson.lsid and course.cid = lesson.lcid
根据dept和emp表查询
1，查询所有教学部职工的基本信息
2，查询人事部最早入职的员工信息
3，查询所有男职工的信息
4，查询所有女职工的姓名，出生年月和所在部门名称
5，查询所有入职时间在2016年后的女职工的编号，姓名入职时间
6，查询所有人事部和市场部员工编号，姓名和入职时间
7，查询所有不姓李的，并且不在人事部的员工信息
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(30)
);
CREATE TABLE emp(
eid INT PRIMARY KEY AUTO_INCREMENT,
did INT,
ename VARCHAR(30),
esex VARCHAR(2),
ebirthday VARCHAR(10),
edangyuan VARCHAR(10),
eindate DATE,
FOREIGN KEY(did) REFERENCES dept(did)
);
INSERT INTO dept(dname) VALUES("教学部");
INSERT INTO dept(dname) VALUES("人事部");
INSERT INTO dept(dname) VALUES("市场部");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (1,"张三","男","1990","是","2015-3-15");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (2,"李四","男","1993","否","2017-12-21");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (2,"周芷若","女","1996","否","2018-2-24");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (2,"杨过","男","1992","是","2016-5-18");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (3,"阳顶天","男","1991","否","2017-11-08");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (3,"灭绝","女","1988","否","2018-5-20");
INSERT INTO emp (did,ename,esex,ebirthday,edangyuan,eindate) VALUES (3,"李灭绝","女","1688","否","2088-5-20");
/*1，查询所有教学部职工的基本信息*/
SELECT * FROM emp
/*2，查询人事部最早入职的员工信息*/
SELECT * FROM emp,dept WHERE emp.did = dept.did and dname = "人事部" ORDER BY eindate LIMIT 1 ;
/*3，查询所有男职工的信息0*/
SELECT * FROM emp WHERE esex = "男"
/*4，查询所有女职工的姓名，出生年月和所在部门名称*/
SELECT ename,ebirthday,dname FROM emp,dept WHERE emp.did = dept.did and esex = "女"
/*5，查询所有入职时间在2016年后的女职工的编号，姓名入职时间*/
SELECT eid,ename,eindate FROM emp WHERE esex = "女" and eindate>2016-1-1
/*6，查询所有人事部和市场部员工编号，姓名和入职时间*/
SELECT eid,ename,eindate FROM emp,dept WHERE emp.did = dept.did and (dname = "人事部" OR dname = "市场部")
/*7，查询所有不姓李的，并且不在人事部的员工信息*/
SELECT * FROM emp,dept WHERE emp.did = dept.did and ename != '李%' and dname !="人事部"
679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
