MySQL数据库学习资料（十）

对之前所学的SQL语句进行总结，下面是一些实例操作：

查询student表中所有学生的全部的信息

SELECT * FROM my_db.student;

查询student表中所有同学的姓名，性别，所在系

USE my_db;

SELECT sname, ssex, sdept FROM student;

查询student表中所有同学的姓名，并在sname前面都加上‘姓名’

SELECT '姓名', sname FROM student;

查询student表中所有同学的姓名和年龄

SELECT '姓名', sname,'年龄',TIMESTAMPDIFF(DAY,sbirthday, CURDATE()) AS AGE

FROM student;

查询student表中是计算机系的同学的姓名和所在系

SELECT sname, sdept FROM student WHERE sdept = '计算机系';

查询student表中所有性别为女的同学的所有信息

SELECT * FROM student WHERE ssex = '女';

查询student表中生日在'1997-01-01' 到 '1997-12-31'的同学的全部信息

SELECT * FROM student WHERE sbirthday BETWEEN '1997-01-01' AND '1997-12-31';

查询student表中生日不在'1997-01-01' 到 '1997-12-31'的同学的全部信息

SELECT * FROM student

WHERE sbirthday NOT BETWEEN '1997-01-01' AND '1997-12-31';

查询course表中学分小于2或者大于3的课名，学分，学期

SELECT cname, credit, semester FROM course

WHERE credit < 2 OR credit >3;

查询course表中学分小于2或者大于3的课名，学分，学期

SELECT cname, credit, semester FROM course

WHERE credit NOT BETWEEN 2 AND 3;

查询计算机系或机电系的同学的学号，姓名，所在系

SELECT sno, sname, sdept

FROM student

WHERE sdept IN ('计算机系', '机电系');

查询不是计算机系和机电系的同学的学号，姓名，所在系

SELECT sno, sname, sdept

FROM student

WHERE sdept NOT IN ('计算机系', '机电系');

查询姓李的同学的学号，姓名，所在系

SELECT sno, sname, sdept

FROM student

WHERE sname LIKE '李%';

查询名字第二字是‘冲’的同学的学号，姓名，所在系

SELECT sno, sname, sdept

FROM student

WHERE sname LIKE '_冲%';

查询学号不以3结尾也不以2结尾的同学的学号，姓名，所在系

SELECT sno, sname, sdept

FROM student

WHERE sno NOT LIKE '%3' and sno NOT LIKE '%2';

查询成绩还没有出来（成绩为空）的同学的学号，课程号

SELECT sno, cno

FROM sc

WHERE grade IS NULL;

查询备注不为空的同学的学号，姓名，备注

SELECT sno, sname, memo

FROM student

WHERE memo IS NOT NULL;

查询备注不为空并且是机电系的同学的学号，姓名，所在系，备注

SELECT sno, sname, sdept, memo

FROM student

WHERE memo IS NOT NULL AND sdept ='机电系';

查询成绩小于60的同学的学号

SELECT sno

FROM  sc

WHERE Grade < 60;

查询成绩小于60的同学的学号，并且去掉重复的学号

SELECT DISTINCT sno

FROM  sc

WHERE Grade < 60;

查询课程号为‘C01’的课程号和成绩，并且按成绩从高到低（降序）排，默认为降序DESC

SELECT cno, grade

FROM sc

WHERE cno='C01'

ORDER BY grade;

查询学号为‘060101’的同学的课程号和成绩，并按成绩降序排列，空值放最后

SELECT cno, grade

FROM sc

WHERE sno = '060101'

ORDER BY grade DESC;

查询学生总人数，数总记录数

SELECT COUNT(*) as '学生总人数'

FROM student;

查询本列的属性值数，按sno数记录数，并去掉重复的sno

SELECT COUNT(distinct sno) as '本列属性值数'

FROM sc;

查询学号为‘060101’的同学的总成绩，sum(grade)把grade的值加起来

SELECT  sum(grade)  as 总成绩

FROM sc

WHERE sno='060101';

查询学号为‘060101’的同学的平均分，AVG(grade)求grade的平均分

SELECT  AVG(grade)  as 平均分

FROM sc

WHERE sno='060101';

查询所有课目中单科成绩最高分

SELECT  MAX(grade)  as 最高分

FROM sc;

查询所有课目中单科成绩最低分

SELECT  MIN(grade)  as 最低分

FROM sc;

查询课程号为‘C01’的课程的最高分和最低分

SELECT MAX(grade) as 最高分, MIN(grade) as 最低分

FROM sc

WHERE cno = 'C01';

以课程号分组，统计每一组的选课人数

SELECT cno AS 课程号, COUNT(sno) AS 选课人数

FROM sc

GROUP BY cno;

以学号分组，查询每一个同学的学号，选课门数，平均成绩

SELECT sno 学号,count(cno) 选课门数,avg(grade) 平均成绩

from sc

group by sno;

查询性别为‘男’的同学，以sdept分组，统计每一个sdept的男生人数

SELECT sdept, COUNT(*) AS 男生人数

FROM student

WHERE ssex='男'

GROUP BY sdept;

查询选课门数大于3的同学按学号分组，统计学号和选课门数

SELECT sno 学号, COUNT(*)  AS 选课门数

FROM sc

GROUP BY sno

HAVING COUNT(*) >3;

以sdept分组，查询在计算机系或者机电系中系的总人数

SELECT sdept, COUNT(*)

FROM student

GROUP BY sdept

HAVING sdept IN ('计算机系','机电系');

以sdept分组，查询在计算机系或者机电系中系的总人数

SELECT sdept, COUNT(*)

FROM student

WHERE sdept IN('计算机系','机电系')

GROUP BY sdept;

查询mysql当前版本和当前时间

SELECT VERSION(), CURRENT_DATE;

查询当前数据库名

SELECT DATABASE();

查询当前数据库下有哪些表

SHOW TABLES;

查看sc这个表的构成，字段，数据类型，空值，主键，默认值等

DESCRIBE sc;

查看可以导入本地数据开关是否打开

SHOW GLOBAL VARIABLES LIKE 'local_infile';

把student表和sc表根据sno合成一个大表，查询全部信息（会重复两次sno字段）

SELECT *

FROM student

INNER JOIN sc

ON student.sno=sc.sno;

把student表和sc表根据sno合成一个大表，查询部分信息（只出现一次sno字段）

SELECT student.sno,sname,ssex,sbirthday,sdept,memo,cno,grade

FROM student INNER JOIN sc

ON student.sno = sc.sno;

将student，sc和course三个表连起来，查询计算机系且选了数据库原理的同学

SELECT sname, cname, grade

FROM student S

INNER JOIN sc ON S.sno=sc.sno

INNER JOIN course C ON sc.cno=c.cno

WHERE sdept='计算机系' AND cname='数据库原理';

将student，sc和course三个表连起来，查询选了数据库原理的同学的姓名和所在系

SELECT sname,sdept

FROM student AS s INNER JOIN sc ON s.sno = sc.sno

INNER JOIN course AS c ON sc.cno = c.cno

WHERE cname = '数据库原理';

将student和sc连接起来，查询每个系的平均分

SELECT sdept, AVG(grade)

FROM student  s INNER JOIN sc

ON s.sno=sc.sno

GROUP BY sdept;

三表连接，查询课程名，选课人数，平均分，最高分，最低分

SELECT c.cname,COUNT(sc.cno) 选课人数,AVG(grade),MIN(grade),MAX(grade)

FROM student AS s INNER JOIN sc ON s.sno=sc.sno

INNER JOIN course AS c ON c.cno = sc.cno

WHERE sdept = '计算机系'

GROUP BY sc.cno;

查询数据库原理的先修课

SELECT c1.cname 课程名, c2.cname 先修课程名

FROM course c1 INNER JOIN course c2

ON c1.precno = c2.cno

WHERE c1.cname="数据库原理";

查询所有课程的先修课

SELECT c1.cname 课程名, c2.cname 先修课程名

FROM course c1 INNER JOIN course c2

ON c1.precno = c2.cno;

左连接

SELECT s.sno, sname, sdept, sc.cno

FROM student S LEFT JOIN sc

ON s.sno=sc.sno

WHERE sdept='计算机系';

右连接

SELECT s.sno, sname, sdept, sc.cno

FROM student S RIGHT JOIN sc

ON s.sno=sc.sno

WHERE sdept='计算机系';

左连接查询sc表中的cno为空的同学的课程名和学号

SELECT cname, sc.sno

FROM course  c LEFT JOIN sc

ON c.cno=sc.cno

WHERE sc.cno IS NULL;

左连接查询同学的课程名和学号

SELECT cname, sc.sno

FROM course  c LEFT JOIN sc

ON c.cno=sc.cno;

-- 在MySQL，两个-加上一个空格可以注释

查询和钟文辉同学在同一个系的同学的信息

SELECT sno, sname, ssex, sdept

FROM student

WHERE sdept IN

       (SELECT sdept

       FROM student

       WHERE sname="钟文辉");

查询c04课程得分大于平均分的同学的信息

SELECT sno, cname, grade

FROM sc INNER JOIN course c ON sc.cno=c.cno

WHERE sc.cno='c04' and grade>

(SELECT AVG(grade)

FROM sc

WHERE cno='C04');

查询高等数学最高分同学的学号

SELECT sno

FROM sc

WHERE grade =

(SELECT MAX(grade)

FROM course INNER JOIN sc

ON course.cno=sc.cno

WHERE cname='高等数学');

查询c04课程中分数大于所有c03的同学的学号

SELECT sno, grade

FROM sc

WHERE cno='C04' AND grade > ALL

(SELECT grade

FROM sc

WHERE cno='C03');

查询分数大于C03最高分q且选了C04课程的同学的学号和成绩

SELECT sno, grade

FROM sc

WHERE cno='C04' AND grade >

(SELECT MAX(grade)

FROM sc

WHERE cno='C03');

查询选了C04课程且有比任意一位C03同学的分数高的同学的学号和成绩

SELECT sno, grade

FROM sc

WHERE cno='C04' AND grade > ANY

(SELECT grade

FROM sc

WHERE cno='C03');

查询两个表（计算机系的同学和机电系的同学）上下连接起来

(SELECT sno, sname, ssex, sdept

FROM student

WHERE sdept='计算机系')

UNION

(SELECT sno, sname, ssex,sdept

FROM student

WHERE sdept='机电系');

查询计算机系的同学或者机电系的同学

SELECT sno, sname, ssex, sdept

FROM student

WHERE sdept IN ('计算机系' ,'机电系');

查询两个表（选了C03课程的前三名和选了C04课程的前三名）上下连接起来

(SELECT sno, cno, grade

FROM sc

WHERE cno='c03'

ORDER BY grade DESC

LIMIT 3)

UNION

(SELECT sno, cno, grade

FROM sc

WHERE cno='c04'

ORDER BY grade DESC

LIMIT 3);

插入李明这个信息，没添加字段，所以按顺序插入

INSERT INTO student

VALUES

('070308','李明','男','1998-07-02','信息管理系',null);

插入李明这个信息，添加字段，所以可以按字段顺序插入

INSERT INTO student

    (sno, sname, ssex, sdept, sbirthday, memo)

    VALUES

('070308','李明','男','信息管理系','1998-07-02',null);

更新表，把机电系的所有学生的性别改成女的

UPDATE student SET ssex='女' WHERE sdept='机电系';

修改表，给student这个表添加一个字段sroom数据类型为INT

ALTER TABLE student ADD sroom INT;

修改表，把student这个表中sroom字段的数据类型改为CHAR（3）

ALTER TABLE student MODIFY sroom CHAR(3);

修改表，把student这个表中sroom字段删掉

ALTER TABLE student DROP sroom;

创建或者替换视图，查询相应的内容

CREATE OR REPLACE VIEW dingming

AS (SELECT sname,sno FROM student)

WITH CHECK OPTION;