MySQL基础操作

新建TXT文件，将下列代码复制到文本，重命名为test.sql，最后导入到数据库中

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50551
Source Host           : localhost:3306
Source Database       : stum

Target Server Type    : MYSQL
Target Server Version : 50551
File Encoding         : 65001

Date: 2018-10-10 11:08:21
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for courses
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
 `cno` char(4) NOT NULL,
 `cname` varchar(20) NOT NULL,
 `cperiod` tinyint(3) NOT NULL,
 `credit` tinyint(3) NOT NULL,
 `ctype` varchar(5) DEFAULT '必修',
 PRIMARY KEY (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('c01', '计算机基础', '68', '3', '必修');
INSERT INTO `courses` VALUES ('c02', '数据库', '72', '5', '必修');
INSERT INTO `courses` VALUES ('c03', 'C语言', '68', '4', '必修');
INSERT INTO `courses` VALUES ('c04', 'C#程序设计', '54', '3', '选修');
INSERT INTO `courses` VALUES ('c05', 'JAVA编程', '72', '4', '选修');
INSERT INTO `courses` VALUES ('c06', '操作系统', '68', '4', '必修');
INSERT INTO `courses` VALUES ('c07', '组成原理', '68', '2', '必修');
INSERT INTO `courses` VALUES ('c08', '软件工程', '54', '3', '选修');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
 `sno` char(12) NOT NULL,
 `cno` char(4) NOT NULL,
 `grade` decimal(5,2) DEFAULT '0.00',
 PRIMARY KEY (`sno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('2015001', 'c01', '92.00');
INSERT INTO `score` VALUES ('2015001', 'c02', '88.00');
INSERT INTO `score` VALUES ('2015001', 'c05', '70.00');
INSERT INTO `score` VALUES ('2015002', 'c01', '90.00');
INSERT INTO `score` VALUES ('2015002', 'c03', '66.00');
INSERT INTO `score` VALUES ('2015002', 'c05', '70.00');
INSERT INTO `score` VALUES ('2015002', 'c07', '81.00');
INSERT INTO `score` VALUES ('2015002', 'c08', '83.00');
INSERT INTO `score` VALUES ('2015003', 'c04', '97.00');
INSERT INTO `score` VALUES ('2015004', 'c06', '93.00');
INSERT INTO `score` VALUES ('2015004', 'c07', '78.00');
INSERT INTO `score` VALUES ('2015005', 'c02', '50.00');
INSERT INTO `score` VALUES ('2015005', 'c06', '41.00');
INSERT INTO `score` VALUES ('2015006', 'c05', '60.00');
INSERT INTO `score` VALUES ('2015006', 'c07', '89.00');
INSERT INTO `score` VALUES ('2015006', 'c08', '69.00');

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
 `sno` char(12) NOT NULL,
 `sname` char(8) NOT NULL,
 `sex` enum('男','女') DEFAULT '男',
 `sbirthday` date DEFAULT NULL,
 `sdept` varchar(10) DEFAULT NULL,
 `smajor` varchar(20) DEFAULT NULL,
 `spolitic` char(5) DEFAULT NULL,
 `saddress` varchar(20) DEFAULT NULL,
 `sphone` char(12) DEFAULT NULL,
 `sphoto` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('2015001', '孙悟空', '男', '1990-10-01', '计算机学院', '软件工程', '党员', '北京', '135111', 'd:1.jpg');
INSERT INTO `students` VALUES ('2015002', '猪八戒', '男', '1991-06-23', '数学学院', '数学分析', '党员', '上海', '135222', null);
INSERT INTO `students` VALUES ('2015003', '嫦娥', '女', '1990-10-21', '外语学院', '日语', '团员', '北京', '135333', 'd:3.jpg');
INSERT INTO `students` VALUES ('2015004', '唐三藏', '男', '1989-01-05', '计算机学院', '软件工程', '党员', '上海', '135444', null);
INSERT INTO `students` VALUES ('2015005', '哪吒', '男', '1988-03-11', '数学学院', '数学建模', '团员', '北京', '135555', null);
INSERT INTO `students` VALUES ('2015006', '玉皇大帝', '男', '1988-07-10', '外语学院', '英语教育', '群众', '上海', '135666', null);
INSERT INTO `students` VALUES ('2015007', '刘惠友', '男', '1991-09-03', '设计学院', '园林设计', '群众', '广州', '135777', 'd:7.jpg');
INSERT INTO `students` VALUES ('2015008', '张惠妹', '女', '1992-12-10', '音乐学院', '音乐教育', '团员', '天津', '135888', null);
INSERT INTO `students` VALUES ('2015009', '张学友', '男', '1991-11-21', '音乐学院', '声乐器乐', '党员', '北京', '135999', null);
INSERT INTO `students` VALUES ('2015010', '刘亦菲', '女', '1992-05-31', '计算机学院', '网络技术', '民盟', '广州', '136111', null);
INSERT INTO `students` VALUES ('2015011', 'marry', '女', '1990-06-23', '计算机学院', '数字媒体', '群众', '上海', '159111', null);
INSERT INTO `students` VALUES ('2015012', 'lily', '女', '1990-06-25', '数学学院', '数学分析', '团员', '深圳', '188666', null);

-- ----------------------------
-- Table structure for teach
-- ----------------------------
DROP TABLE IF EXISTS `teach`;
CREATE TABLE `teach` (
 `tno` char(8) NOT NULL,
 `cno` char(4) NOT NULL,
 `tscore` decimal(5,2) DEFAULT NULL,
 PRIMARY KEY (`tno`,`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teach
-- ----------------------------
INSERT INTO `teach` VALUES ('t01', 'c01', '99.00');
INSERT INTO `teach` VALUES ('t01', 'c02', '97.00');
INSERT INTO `teach` VALUES ('t02', 'c05', '92.00');
INSERT INTO `teach` VALUES ('t03', 'c01', '90.00');
INSERT INTO `teach` VALUES ('t04', 'c03', '91.00');
INSERT INTO `teach` VALUES ('t04', 'c05', '97.00');
INSERT INTO `teach` VALUES ('t04', 'c07', '89.00');

-- ----------------------------
-- Table structure for teachers
-- ----------------------------
DROP TABLE IF EXISTS `teachers`;
CREATE TABLE `teachers` (
 `tno` char(8) NOT NULL,
 `tname` char(10) NOT NULL,
 `dno` varchar(4) DEFAULT NULL,
 PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teachers
-- ----------------------------
INSERT INTO `teachers` VALUES ('t01', '张尚', 'D1');
INSERT INTO `teachers` VALUES ('t02', '张夏', 'D2');
INSERT INTO `teachers` VALUES ('t03', '王佐', 'D3');
INSERT INTO `teachers` VALUES ('t04', '王佑', 'D4');
INSERT INTO `teachers` VALUES ('t05', '赵乾', 'D1');
INSERT INTO `teachers` VALUES ('t06', '赵厚', 'D3');

以下针对此数据库进行操作

1. 查询stum数据库的学生表中部门为计算机学院的同学的学号，姓名和性别。

SELECT sno,sname,sex FROM students WHERE sdept = "计算机学院";

2. 查询学生表中的所有列。

SELECT * FROM students;

3. 查询学生表中上海的同学的学号、姓名和电话。

SELECT sno,sname,sphone FROM students WHERE saddress = '上海';

4. 列出学生表中的学号、姓名及学生年龄。

SELECT sno,sname,TIMESTAMPDIFF(year,sbirthday,CURDATE()) as age FROM students;

5. 对课程表只选择学分和课程类别，消除结果集中的重复行。

SELECT distinct credit,ctype FROM courses;

6. 从课程表中查找前3行的课程名称、学分。

SELECT cname,credit FROM courses ORDER BY cno LIMIT 3;

7. 查询c07号课程成绩在80分以上的学生的学号。

SELECT sno FROM score WHERE cno = 'c07' AND grade >= 80;

8. 查询出生时间在1982年8月8日以后出生的学生信息。

SELECT * FROM students WHERE sbirthday >= '1982-08-08';

9. 查询 “必修”课的课程名称、学时减去6之后的学时数并命名为“新学时”。

SELECT cname,cperiod - 6 AS '新学时' FROM courses WHERE ctype = "必修";

10.查询住址上海的男学生的姓名和生日。

SELECT sname,sbirthday FROM students WHERE saddress = '上海' AND sex = '男';

11.查询不是上海的也不是北京的的学生的姓名和电话。

SELECT sname,sphone FROM students WHERE saddress != '上海' AND saddress != '北京';

12.查询成绩在80与90之间的学生的学号及相应课程号。

SELECT sno,cno FROM score WHERE grade BETWEEN 80 AND 90;

13.查询1988-1-1与1990-12-31之间出生且未上传照片的学生的姓名和电话。

SELECT sname,sphone FROM students WHERE sbirthday BETWEEN'1988-1-1' AND  '1990-12-31' AND sphoto IS NULL;

14.查询课程学分为2、3、4、5的课程名称及课程类别。

SELECT cname,ctype FROM courses WHERE credit BETWEEN 2 AND 5;

15.查询学时不在68-102之间且学时在3-5之间的课程信息。

SELECT * FROM courses WHERE NOT cperiod>68&&cperiod<102 AND credit>3&&credit<5;

16.查询姓‘王’的学生信息。

SELECT * FROM students WHERE sname LIKE'王%';

17.查询名字中带‘小’的学生信息。

SELECT * FROM students WHERE sname LIKE'%小%';

18.查询姓名以“李”开头，第二个字符是“小”或“晓”的学生信息。

SELECT * FROM students WHERE sname REGEXP'^李[小,晓].*';

19.查询学生表中上传照片的女同学的姓名。

SELECT sname FROM students WHERE sex = '女' AND sphoto is NOT NULL;

20.查询所有男生的信息，并按出生日期升序排列。

SELECT * FROM students WHERE sex = '男' ORDER BY sbirthday;

21.查询2015002学生的成绩，并按成绩降序排序。

SELECT grade FROM score WHERE sno = 2015002 ORDER BY grade desc;

22.查询学生表中年龄最大的前三位同学的姓名和出生日期。

SELECT sname,sbirthday FROM students ORDER BY sbirthday LIMIT 3;

23.查询课程表中“必修”课的课程名称，学时，学分，按学分进行降序排列，学分相等的按学时升序排列。

SELECT cname,cperiod,credit FROM courses WHERE ctype = "必修" ORDER BY credit desc, cperiod asc;

24.统计(查询)学生表中的总人数。

SELECT COUNT(*) FROM students;

25.统计(查询)课程表中各类别的课程门数。

SELECT cname,COUNT(*) FROM courses GROUP BY ctype;

26.统计(查询)学生表中各部门的学生人数。

SELECT sdept,COUNT(*) FROM students GROUP BY sdept;

27.统计各门课程的选修人数，显示课程号和选修数，并按课程号降序排序。

SELECT cno,COUNT(*) FROM score GROUP BY sno ORDER BY cno DESC;

28.查询每位学生的最高成绩、最低成绩和平均成绩。

SELECT sno,MAX(grade),MIN(grade),AVG(grade) FROM score GROUP BY sno;

29.查询平均成绩高于90的学生学号和平均成绩。

SELECT sno,AVG(grade) FROM score WHERE grade >90 GROUP BY sno;