学习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 !="人事部"