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