对之前所学的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;
