大佬教程收集整理的这篇文章主要介绍了MySQL55--SQL语句练习01,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
SHOW DATABASES;
CREATE DATABASE test_2106 character set utf8;
USE test_2106;
Sno varchar(20) NOT NULL 学生编号
Sname varchar(20) NOT NULL 学生姓名
sex varchar(20) NOT NULL 性别
age INT NOT NULL 年龄
dept varchar(20) NOT NULL 系别
CREATE TABLE student
(Sno varchar(20) NOT NULL,
Sname varchar(20) NOT NULL,
sex varchar(20) NOT NULL,
age INT NOT NULL,
dept varchar(20) NOT NULL
);
Cno varchar(20) NOT NULL 课程编号
Cname varchar(20) NOT NULL 课程姓名
hours varchar(20) NOT NULL 学课时长
CREATE TABLE course
(Cno varchar(20) NOT NULL,
Cname varchar(20) NOT NULL,
hours VARCHAR(20) NOT NULL
);
Sno varchar(20) NOT NULL 学生编号
Cno varchar(20) NOT NULL 课程编号
grade INT 成绩
CREATE TABLE SC
(Sno varchar(20) NOT NULL,
Cno varchar(20) NOT NULL,
grade INT
);
Sno Sname sex age dept
9512101 李勇男19 计算机系
9512102 刘晨男20 计算机系
9512103 王敏女20 计算机系
9521101 张立男22 信息系
9521102 吴宾女21 信息系
9521103 张海男20 信息系
9531101 钱小力女18 数学系
9531102 王大力男19 数学系
INSERT INTO student VALUES ('9512101','李勇','男',19,'计算机系');
INSERT INTO student VALUES ('9512102','刘晨','男',20,'计算机系');
INSERT INTO student VALUES ('9512103','王敏','女',20,'计算机系');
INSERT INTO student VALUES ('9521101','张立','男',22,'信息系');
INSERT INTO student VALUES ('9521102','吴宾','女',21,'信息系');
INSERT INTO student VALUES ('9521103','张海','男',20,'信息系');
INSERT INTO student VALUES ('9531101','钱小力','女',18,'数学系');
INSERT INTO student VALUES ('9531102','王大力','男',19,'数学系');
Cno Cname hours
C01 计算机文化学70
C02 VB 90
C03 计算机网络80
C04 数据库基础108
C05 高等数学180
C06 数据结构72
INSERT INTO course VALUES ('C01','计算机文化学','70');
INSERT INTO course VALUES ('C02','VB','90');
INSERT INTO course VALUES ('C03','计算机网络','80');
INSERT INTO course VALUES ('C04','数据库基础','108');
INSERT INTO course VALUES ('C05','高等数学','180');
INSERT INTO course VALUES ('C06','数据结构','72');
Sno Cno grade
9512101 C01 90
9512101 C02 86
9512101 C06 NULL
9512102 C02 78
9512102 C04 66
9521102 C01 82
9521102 C02 75
9521102 C04 92
9521102 C05 50
9521103 C02 68
9521103 C06 NULL
9531101 C01 80
9531101 C05 95
9531102 C05 85
INSERT INTO SC VALUES ('9512101','C01',90);
INSERT INTO SC VALUES ('9512101','C02',86);
INSERT INTO SC VALUES ('9512101','C06',NULL);
INSERT INTO SC VALUES ('9512102','C02',78);
INSERT INTO SC VALUES ('9512102','C04',66);
INSERT INTO SC VALUES ('9521102','C01',82);
INSERT INTO SC VALUES ('9521102','C02',75);
INSERT INTO SC VALUES ('9521102','C04',92);
INSERT INTO SC VALUES ('9521102','C05',50);
INSERT INTO SC VALUES ('9521103','C02',68);
INSERT INTO SC VALUES ('9521103','C06',NULL);
INSERT INTO SC VALUES ('9531101','C01',80);
INSERT INTO SC VALUES ('9531101','C05',95);
INSERT INTO SC VALUES ('9531102','C05',85);
SELECT * FROM student;
SELECT * FROM course;
SELECT *
FROM SC
WHERE grade between 70 and 80;
SELECT max(grade)
FROM SC
WHERE Cno = 'C01'
GROUP BY Cno;
SELECT CnoFROM SC GROUP BY Cno;
SELECT AVG(grade),MAX(grade),MIN(grade)
FROM SC
WHERE Cno = 'C02'
group BY Cno;
SELECT dept,count(*)
FROM student
GROUP BY dept;
SELECT Cno, count(*),max(grade)
FROM SC
group BY Cno;
SELECT Sno, count(*)
FROM SC
GROUP BY Sno
ORDER BY count(*);
SELECT Cno, count(*), avg(grade)
FROM SC
GROUP BY Cno;
SELECT Sno, count(*),avg(grade)
FROM SC
GROUP BY Sno
HAVING count(*)> 2;
SELECT Sno,sum(grade)
FROM SC
GROUP BY Sno
HAVING sum(grade)> 200;
SELECT Sname,dept
FROM student
WHERE Sno in (SELECT Sno FROM sc WHERE Cno = 'C02');
SELECT s.sname,o.cno,o.grade
FROM student s,sc o
WHERE s.sno=o.sno and o.grade>80
ORDER BY o.grade desc;
SELECT s.sname,s.sex,o.grade
FROM student s,sc o,course c
WHERE s.sno=o.sno and o.cno=c.cno and s.dept='计算机系' and s.sex='男' and c.cname='数据库基础';
SELECT DISTINCT s1.sname,s2.age
FROM student s1,student s2
WHERE s1.sname!=s2.sname and s1.age=s2.age;
SELECT Cno,Cname
FROM course
WHERE Cno NOT in (SELECT Cno FROM sc GROUP BY Cno);
CREAFTE TABLE new_sc
SELECT s.sname,c.cname,o.grade
FROM student s,course c,sc o
WHERE s.sno=o.sno and o.cno=c.cno;
SHOW TABLES;
SELECT * FROM new_sc;
SELECT s.sname,s.sex,c.cname,o.grade
FROM student s,course c,sc o
WHERE s.sno=o.sno and o.cno=c.cno and s.dept in ('信息系','计算机系');
SELECT sname,dept
FROM student
WHERE sno in(select sno from course where cno='c01');
SELECT sno,sname
FROM student
WHERE sno in(select sno from sc where grade>80) and dept='数学系';
SELECT cname
FROM course
WHERE cno in(select cno from sc where sno in(select sno from student where dept='计算机系'));
SELECT sname 学生姓名,cname 课程名称,cno 课程编号
FROM (
SELECT s.sname,c.cname,o.cno
FROM student s,course c,sc o
WHERE s.sno=o.sno and o.cno=c.cno and o.grade>80)
as new01;
CREATE TABLE new02
SELECT s.sname,c.cname,o.cno
FROM student s,course c,sc o
WHERE s.sno=o.sno and o.cno=c.cno and o.grade>80;
SELECT * FROM new02;
DELETE from sc
WHERE grade<=50;
UPDATE sc set grade=grade+10
WHERE cno='c01';
SELECT * FROM sc;
以上是大佬教程为你收集整理的MySQL55--SQL语句练习01全部内容,希望文章能够帮你解决MySQL55--SQL语句练习01所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。