sql 练习

参考：https://www.jianshu.com/p/b673561b029b

创建需要的4张表

首先创建student、course、score、teacher这四张表。

• student表
  • 创建student表

CREATE TABLE IF NOT EXISTS student(
sno TINYINT UNSIGNED  NOT NULL,
sname VARCHAR(20) NOT NULL,
ssex ENUM('male', 'female') DEFAULT 'male',
sbirthday DATE,
class VARCHAR(20) NOT NULL,
PRIMARY KEY(sno)
);

添加数据

INSERT INTO student VALUES
(1, '阿信', DEFAULT, 19751206, 'class5'),
(2, '怪兽', DEFAULT, 19761128, 'class5'),
(3, '玛莎', DEFAULT, 19770425, 'class5'),
(4, '石头', DEFAULT, 19751211, 'class5'),
(5, '冠佑', DEFAULT, 19730728, 'class5'),
(6, '小马', DEFAULT, 19960628, 'class2'),
(7, '小兰', 'female', 19951126, 'class2'),
(8, '况儿子', DEFAULT, 19960715, 'class4'),
(9, '纯妞', 'female', 19960428, 'class4'),
(10, '豆豆', 'female', 19941211, 'class2');

course表

• 创建course表

• CREATE TABLE IF NOT EXISTS course(
  cno TINYINT UNSIGNED NOT NULL,
  cname VARCHAR(20) NOT NULL,
  tno TINYINT NOT NULL,
  PRIMARY KEY(cno)
  );

  添加数据

• INSERT INTO course VALUES
  (1, '数据结构与算法', 1),
  (2, '计算机网络', 2),
  (3, '计算机组成原理', 3),
  (4, '操作系统', 4);

score表

• 创建score表

CREATE TABLE IF NOT EXISTS score(
sno TINYINT UNSIGNED NOT NULL,
cno TINYINT UNSIGNED NOT NULL,
degree DECIMAL(4, 1)
);

添加数据

INSERT INTO score VALUES
(1, 1, 86),
(1, 2, 75),
(1, 3, 68),
(2, 2, 92),
(2, 3, 88),
(3, 4, 76),
(4, 1, 91),
(5, 1, 40),
(6, 3, 30),
(7, 3, 59),
(8, 4, 66),
(9, 1, 100),
(10, 1, 100),
(6, 1, 66),
(9, 2, 10),
(8, 3, 40),
(7, 1, 77),
(6, 4, 14);

teacher表

• 创建teacher表

CREATE TABLE IF NOT EXISTS teacher(
tno TINYINT UNSIGNED NOT NULL,
tname VARCHAR(10) NOT NULL,
tsex ENUM('male', 'female') DEFAULT 'male',
tbirthday DATE,
prof VARCHAR(26),
depart VARCHAR(10) NOT NULL,
PRIMARY KEY(tno)
);

添加数据

INSERT INTO teacher VALUES
(1, '卢本伟', 'male', 19581202, '副教授', '计算机系'),
(2, '五五开', 'male', 19690312, '讲师', '电子工程系'),
(3, '德云色', 'female', 19720505, '助教', '计算机系'),
(4, '卢本皇', 'female', 19770814, '助教', '电子工程系');

1. 查询student表中的所有记录的sname,ssex,class列。

SELECT sname, ssex, class FROM student;

2. 查询教师所有的单位即不重复的depart列。

SELECT DISTINCT depart FROM teacher;

3. 查询student表的所有记录

SELECT * FROM student;

4. 查询score表中成绩在60-80之间的所有记录

SELECT * FROM score WHERE degree BETWEEN 60 AND 80;

5. 查询score表中成绩为30,66,10的记录

SELECT * FROM score WHERE degree IN (10 ,30, 66);

6. 查询student表中'class5'班或性别为'female'的同学记录。

SELECT * FROM student WHERE class='class5' OR ssex='female';

7. 以class降序查询student表的所有记录

SELECT * FROM student ORDER BY class DESC;

8. 以cno升序，degree降序查询score表中的记录

SELECT * FROM score ORDER BY cno ASC, degree DESC;
asc 是升序

9. 查询“class5”班的学生人数

SELECT COUNT(*) FROM student WHERE class='class5';

count（*） 查询所有记录的条数

10.以cno升序，degree降序查询score表中的记录

SELECT * FROM score ORDER BY cno ASC, degree DESC;