MySQL笔记(二)
4.設計範式
-
第一範式
數據庫中的每一個字段都是不可分割的原子值
CREATE TABLE student( id INT PRIMARY KEY, name VARCHAR(20), address VARCHAR(20)); INSERT INTO student VALUES(1,'A','中國陝西省延安市寶塔區'); INSERT INTO student VALUES(2,'B','中國陝西省延安市安塞區'); SELECT * FROM student; +----+------+-----------------------------------+ | id | name | address | +----+------+-----------------------------------+ | 1 | A | 中國陝西省延安市寶塔區 | | 2 | B | 中國陝西省延安市安塞區 | +----+------+-----------------------------------+ 上面的address換可以拆分，因此不滿足第一範式。 那如何才滿足呢？ CREATE TABLE student2 ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), country VARCHAR(20), province VARCHAR(20), city VARCHAR(20), details VARCHAR(20) ); INSERT INTO student2 VALUES(1,'A','中國','陝西省','延安市','寶塔區'); INSERT INTO student2 VALUES(2,'B','中國','陝西省','延安市','安賽區'); SELECT * FROM student2; +----+------+---------+-----------+-----------+-----------+ | id | name | country | province | city | details | +----+------+---------+-----------+-----------+-----------+ | 1 | A | 中國 | 陝西省 | 延安市 | 寶塔區 | | 2 | B | 中國 | 陝西省 | 延安市 | 安賽區 | +----+------+---------+-----------+-----------+-----------+ 該表對地址拆分的很詳細，便於後期可以直接根據countr或者province等字段進行操作。 範式涉及的越詳細，對於某些操作可能更好，但不一定都好，要根據實際項目。 -
第二範式
第二範式必須滿足第一範式的前提。第二範式要求除主鍵的每一列，都必須完全依賴主鍵。
一般在聯合主鍵下，容易出現不完全依賴的情況。
CREATE TABLE myorder( product_id INT, customer_id INT, product_name VARCHAR(20), customer_name VARCHAR(20), PRIMARY KEY(product_id, customer_id); ); 該表是一個聯合主鍵的情況，product_name依賴於product_id，而customer_name依賴與customer_id，這樣就不滿足第二範式。 如何才能滿足第二範式？可以對上表進行拆分！ CREATE TABLE myorder( order_id INT PRIMARY KEY, product_id INT, customer_id INT ); 此表product_id與customer_id完全依賴於order_id CREATE TABLE product( id INT PRIMARY KEY, product_id INT ); 此表product_id依賴於id。 CREATE TABLE customer( id INT PRIMARY KEY, customer_id INT ); -
第三範式
必須先滿足第二範式！除了主鍵列的其他列不能相互依賴
customer_phone，也CREATE TABLE myorder( order_id INT PRIMARY KEY, product_id INT, customer_id INT, customer_phone VARCHAR(20) ); 從上表來看，通過customer_id也可以確定customer_phone，也就是說customer_id和customer_phone存在相互依賴關系。因此不滿足第三範式。 還是可以通過拆分，讓其滿足第三範式。 customer_phone，也CREATE TABLE myorder( order_id INT PRIMARY KEY, product_id INT, customer_id INT, ); CREATE TABLE customer( id INT PRIMARY KEY, name INT， customer_phone VARCHAR(20) );
5.查詢
5.1 建立個表
新建一个查询用的数据库:selectTest
CREATE DATABASE selectTest;
选择该数据库:
USE selectTest;
建立學生表
学生表:
student
学号
姓名
性别
出生日期
所在班级
CREATE TABLE student(
s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
s_birthday DATETIME COMMENT'学生生日',
s_class VARCHAR(20) COMMENT'学生所在的班级'
);
建立教師表
teacher
教师编号
教师名字
教师性别
出生日期
职称
所在部门
CREATE TABLE teacher(
t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
t_birthday DATETIME COMMENT'教师生日',
t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
);
建立課程表
课程表:
course
课程号
课程课程名称
教师编号
CREATE TABLE course(
c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',
FOREIGN KEY(t_no) references teacher(t_no)
);
建立成績表
srore
学号
课程号
成绩
CREATE TABLE score (
s_no VARCHAR(20),
c_no VARCHAR(20),
sc_degree DECIMAL,
foreign key(s_no) references student(s_no),
foreign key(c_no) references course(c_no),
PRIMARY KEY(s_no,c_no)
);
查看创建的表以及架构
SHOW TABLES;
+----------------------+
| Tables_in_selecttest |
+----------------------+
| course |
| score |
| student |
| teacher |
+----------------------+
查看student表结构 DESCRIBE student;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| s_no | varchar(20) | NO | PRI | NULL | |
| s_name | varchar(20) | NO | | NULL | |
| s_sex | varchar(10) | NO | | NULL | |
| s_birthday | datetime | YES | | NULL | |
| s_class | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
查看teacher表结构 DESCRIBE teacher;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| t_no | varchar(20) | NO | PRI | NULL | |
| t_name | varchar(20) | NO | | NULL | |
| t_sex | varchar(20) | NO | | NULL | |
| t_birthday | datetime | YES | | NULL | |
| t_rof | varchar(20) | NO | | NULL | |
| t_depart | varchar(20) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
查看course表结构 DESCRIBE course;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_no | varchar(20) | NO | PRI | NULL | |
| c_name | varchar(20) | NO | | NULL | |
| t_no | varchar(20) | NO | MUL | NULL | |
+--------+-------------+------+-----+---------+-------+
查看score表结构 DESCRIBE score;
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| s_no | varchar(20) | NO | PRI | NULL | |
| c_no | varchar(20) | NO | MUL | NULL | |
| sc_degree | decimal(10,0) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
向表中添加数据
--学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');
--教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');
--添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');
--添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');
數據呈現
student:
SELECT * FROM student;
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birthday | s_class |
+------+--------+-------+---------------------+---------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
+------+--------+-------+---------------------+---------+
teacher:
SELECT * FROM teacher;
+------+--------+-------+---------------------+--------+------------+
| t_no | t_name | t_sex | t_birthday | t_rof | t_depart |
+------+--------+-------+---------------------+--------+------------+
| 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍 | 女 | 1972-05-05 00:00:00 | 助教 | 计算机机系 |
| 831 | 刘冰 | 女 | 1977-08-14 00:00:00 | 助教 | 电子工程系 |
| 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+------+--------+-------+---------------------+--------+------------+
score:
SELECT * FROM score;
+------+-------+-----------+
| s_no | c_no | sc_degree |
+------+-------+-----------+
| 103 | 3-105 | 92 |
| 103 | 3-245 | 86 |
| 103 | 6-166 | 85 |
| 105 | 3-105 | 88 |
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
| 109 | 6-166 | 81 |
+------+-------+-----------+
course:
SELECT * FROM course;
+-------+------------+------+
| c_no | c_name | t_no |
+-------+------------+------+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
| 9-888 | 高等数学 | 831 |
+-------+------------+------+
5.2 查詢練習
-
查询student表中所有的记录
SELECT * FROM student; +------+--------+-------+---------------------+---------+ | s_no | s_name | s_sex | s_birthday | s_class | +------+--------+-------+---------------------+---------+ | 1 | admin | 男 | 1999-09-09 00:00:00 | 95033 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +------+--------+-------+---------------------+---------+ -
查询student表中所有记录的s_name,s_sex和s_class列
SELECT s_no,s_name,s_class FROM student; +------+--------+---------+ | s_no | s_name | s_class | +------+--------+---------+ | 1 | admin | 95033 | | 101 | 曾华 | 95033 | | 102 | 匡明 | 95031 | | 103 | 王丽 | 95033 | | 104 | 李军 | 95033 | | 105 | 王芳 | 95031 | | 106 | 陆军 | 95031 | | 107 | 王尼玛 | 95033 | | 108 | 张全蛋 | 95031 | | 109 | 赵铁柱 | 95031 | +------+--------+---------+ -
查询教师所有的单位但是不重复的t_depart列
去重用distinct
SELECT distinct(t_depart) FROM teacher; +------------+ | t_depart | +------------+ | 计算机系 | | 计算机机系 | | 电子工程系 | +------------+ -
查询score表中成绩在60-80之间所有的记录(sc_degree)
SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79; SELECT * FROM score WHERE sc_degree < 80 AND sc_degree > 60 ; +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | +------+-------+-----------+ 注:between..and.. 默認閉區間 -
查询score表中成绩为85, 86, 或者88的记录(sc_degree)
在...之中的用IN
SELECT * FROM score WHERE sc_degree IN(85, 86, 88); +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | +------+-------+-----------+ -
查询student表中'95031'班或者性别为'女'的同学记录
或用OR語句
SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女'; +------+--------+-------+---------------------+---------+ | s_no | s_name | s_sex | s_birthday | s_class | +------+--------+-------+---------------------+---------+ | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +------+--------+-------+---------------------+---------+ -
以class降序查询student表中所有的记录
排序用ORDER BY語句，desc降序，asc升序。
SELECT * FROM student ORDER BY s_class desc; +------+--------+-------+---------------------+---------+ | s_no | s_name | s_sex | s_birthday | s_class | +------+--------+-------+---------------------+---------+ | 1 | admin | 男 | 1999-09-09 00:00:00 | 95033 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | +------+--------+-------+---------------------+---------+ -
以c_no升序.sc_degree降序score表中所有的数据
意思是當默認按c_no升序排序，如果c_no相同，按照sc_degree降序
排序用ORDER BY語句，desc降序，asc升序。
SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC; +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 3-105 | 92 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 103 | 6-166 | 85 | | 109 | 6-166 | 81 | | 105 | 6-166 | 79 | +------+-------+-----------+ -
查询'95031'班的学生人数
計數用count
SELECT COUNT(s_class) FROM student WHERE s_class='95031'; +----------------+ | count(s_class) | +----------------+ | 5 | +----------------+ -
查询score表中的最高分数的学生号和课程号.(子查询或者排序)
方法1： SELECT s_no,c_no FROM score WHERE sc_degree=(SELECT MAX(sc_degree) FROM score); +------+-------+ | s_no | c_no | +------+-------+ | 103 | 3-105 | +------+-------+ 方法2：用排序法 這種方法不能區別多個最高值的問題 SELECT s_no,c_no FROM score ORDER BY sc_degree DESC LIMIT 0,1; LIMIT 0,1 表示從第0條記錄開始，一共選擇1條記錄。 -
查询每门课的平均成绩
平均成績是以課程爲單位計算的，因此需要根據課程號c_no來計算。
最後希望展示出的結果是一個課程對應一個平均成績，因此select後面是c_no,AVG(sc_degree)
AVG()是用來求平均值的
SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no; +-------+----------------+ | c_no | avg(sc_degree) | +-------+----------------+ | 3-105 | 85.3333 | | 3-245 | 76.3333 | | 6-166 | 81.6667 | +-------+----------------+ -
查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
對於復雜的查詢問題，可以將起拆解開來： 1.查詢score表中學生的平均分 SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no; 2.每個課程的選擇人數要至少爲2，並且課程名字以3開頭 having count(c_no)>=2 c_no like '3%'，用like來做匹配，'3%'表示開頭是3，%表示通配符，後面任意。 綜上： SELECT c_no, AVG(sc_degree) FROM score GROUP BY c_no HAVING COUNT(c_no)>=2 and c_no LIKE '3%'; +----------------+-------+ | AVG(sc_degree) | c_no | +----------------+-------+ | 85.3333 | 3-105 | | 76.3333 | 3-245 | +----------------+-------+ -
查询分数大于70但是小于90的s_no列
方法1： SELECT s_no,sc_degree FROM score WHERE sc_degree>70 and sc_degree<90; 或者 SELECT s_no,sc_degree FROM score WHERE sc_degree BETWEEN 70 AND 90; BETWEEN... AND...默認閉區間 +------+-----------+ | s_no | sc_degree | +------+-----------+ | 103 | 86 | | 103 | 85 | | 105 | 88 | | 105 | 75 | | 105 | 79 | | 109 | 76 | | 109 | 81 | +------+-----------+ -
查询所有的学生 s_name , c_no, sc_degree列
s_name來自於表student，c_no, sc_degree來自於表score，這裏也涉及兩個表的關聯查詢。 處理方法換是拆分，將單個表的情況列出來，在從中找對應關系 SELECT s_no,s_name FROM student; +------+-----------+ | s_no | s_name | +------+-----------+ | 101 | 曾华 | | 102 | 匡明 | | 103 | 王丽 | | 104 | 李军 | | 105 | 王芳 | | 106 | 陆军 | | 107 | 王尼玛 | | 108 | 张全蛋 | | 109 | 赵铁柱 | +------+-----------+ SELECT s_no,c_no,sc_degree FROM score; +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 3-245 | 75 | | 105 | 6-166 | 79 | | 109 | 3-105 | 76 | | 109 | 3-245 | 68 | | 109 | 6-166 | 81 | +------+-------+-----------+ 可以發現，兩個表字都有s_no，可以根據s_no來匹配。 SELECT s_name,c_no,sc_degree FROM student,score WHERE student.s_no=score.s_no; +-----------+-------+-----------+ | s_name | c_no | sc_degree | +-----------+-------+-----------+ | 王丽 | 3-105 | 92 | | 王丽 | 3-245 | 86 | | 王丽 | 6-166 | 85 | | 王芳 | 3-105 | 88 | | 王芳 | 3-245 | 75 | | 王芳 | 6-166 | 79 | | 赵铁柱 | 3-105 | 76 | | 赵铁柱 | 3-245 | 68 | | 赵铁柱 | 6-166 | 81 | +-----------+-------+-----------+ -
查询所有学生的s_no, c_name, sc_degree列
這裏也涉及兩個表的關聯查詢 在course表中，有：c_no，c_name，t_no 在score表中，有：s_no，c_no，sc_degree 可以發現兩個表中都有c_no，因此可以依據c_no來實現 SELECT c_no,c_name FROM course; +-------+-----------------+ | c_no | c_name | +-------+-----------------+ | 3-105 | 计算机导论 | | 3-245 | 操作系统 | | 6-166 | 数字电路 | | 9-888 | 高等数学 | +-------+-----------------+ SELECT c_no,s_no,sc_degree FROM score; +-------+------+-----------+ | c_no | s_no | sc_degree | +-------+------+-----------+ | 3-105 | 103 | 92 | | 3-245 | 103 | 86 | | 6-166 | 103 | 85 | | 3-105 | 105 | 88 | | 3-245 | 105 | 75 | | 6-166 | 105 | 79 | | 3-105 | 109 | 76 | | 3-245 | 109 | 68 | | 6-166 | 109 | 81 | +-------+------+-----------+ SELECT s_no,c_name, sc_degree FROM score,course WHERE score.c_no=course.c_no; +------+-----------------+-----------+ | s_no | c_name | sc_degree | +------+-----------------+-----------+ | 103 | 计算机导论 | 92 | | 103 | 操作系统 | 86 | | 103 | 数字电路 | 85 | | 105 | 计算机导论 | 88 | | 105 | 操作系统 | 75 | | 105 | 数字电路 | 79 | | 109 | 计算机导论 | 76 | | 109 | 操作系统 | 68 | | 109 | 数字电路 | 81 | +------+-----------------+-----------+ -
查询所有的学生 s_name , c_name, sc_degree列
student中有 s_name，s_no course中有c_name，c_no score有s_no c_no sc_degree 涉及三表關聯查詢，這裏就不能以一個字段作爲中間的關聯字段了，應該使用一個表作爲中間的關聯表 SELECT s_name,c_name,sc_degree FROM student,course,score WHERE student.s_no=score.s_no AND course.c_no=score.c_no; +-----------+-----------------+-----------+ | s_name | c_name | sc_degree | +-----------+-----------------+-----------+ | 王丽 | 计算机导论 | 92 | | 王丽 | 操作系统 | 86 | | 王丽 | 数字电路 | 85 | | 王芳 | 计算机导论 | 88 | | 王芳 | 操作系统 | 75 | | 王芳 | 数字电路 | 79 | | 赵铁柱 | 计算机导论 | 76 | | 赵铁柱 | 操作系统 | 68 | | 赵铁柱 | 数字电路 | 81 | +-----------+-----------------+-----------+ SELECT s_name,c_name,sc_degree FROM student,course,score WHERE student.s_no=score.s_no AND course.c_no=score.c_no; -
查询班级是'95031'班学生每门课的平均分
select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no; select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no; SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ; +-------+-------------------+ | c_no | AVG(sc.sc_degree) | +-------+-------------------+ | 3-105 | 82.0000 | | 3-245 | 71.5000 | | 6-166 | 80.0000 | +-------+-------------------+ 进阶,加入课程名称: SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ; +-------+------------+-------------------+ | c_no | c_name | AVG(sc.sc_degree) | +-------+------------+-------------------+ | 3-105 | 计算机导论 | 82.0000 | | 3-245 | 操作系统 | 71.5000 | | 6-166 | 数字电路 | 80.0000 | +-------+------------+-------------------+ -
查询选修"3-105"课程的成绩高于'109'号同学'3-105'成绩 的所有同学的记录
SELECT * FROM student AS s, score AS sc WHERE sc.c_no = '3-105' AND sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no; +------+--------+-------+---------------------+---------+------+-------+-----------+ | s_no | s_name | s_sex | s_birthday | s_class | s_no | c_no | sc_degree | +------+--------+-------+---------------------+---------+------+-------+-----------+ | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | 103 | 3-105 | 92 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | 105 | 3-105 | 88 | +------+--------+-------+---------------------+---------+------+-------+-----------+ -
查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录
SELECT * FROM score WHERE sc_degree >(SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105'); +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 103 | 6-166 | 85 | | 105 | 3-105 | 88 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +------+-------+-----------+ 不过视频中仅仅查出来了score记录,但是并没有学生的信息,按照上面的来修改: SELECT * FROM student AS s, score AS sc WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no; 还可以再进一步: SELECT s.s_no AS'学生学号', s.s_name AS'学生姓名', s_sex AS'性别', s_class AS'班级', c.c_no AS'课程编号', c.c_name AS'课程名称' ,sc.sc_degree AS'分数' FROM student AS s, score AS sc ,course AS c WHERE sc.sc_degree > (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no AND sc.c_no = c.c_no; +----------+----------+------+-------+----------+------------+------+ | 学生学号 | 学生姓名 | 性别 | 班级 | 课程编号 | 课程名称 | 分数 | +----------+----------+------+-------+----------+------------+------+ | 103 | 王丽 | 女 | 95033 | 3-105 | 计算机导论 | 92 | | 103 | 王丽 | 女 | 95033 | 3-245 | 操作系统 | 86 | | 103 | 王丽 | 女 | 95033 | 6-166 | 数字电路 | 85 | | 105 | 王芳 | 女 | 95031 | 3-105 | 计算机导论 | 88 | | 105 | 王芳 | 女 | 95031 | 6-166 | 数字电路 | 79 | | 109 | 赵铁柱 | 男 | 95031 | 6-166 | 数字电路 | 81 | +----------+----------+------+-------+----------+------------+------+ -
查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101')); +------+--------+-------+---------------------+---------+ | s_no | s_name | s_sex | s_birthday | s_class | +------+--------+-------+---------------------+---------+ | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | +------+--------+-------+---------------------+---------+ -
查询 张旭 教师任课的学生的成绩
首先查一下張旭老師的編號 SELECT t_no FROM teacher WHERE t_name='张旭'; +------+ | t_no | +------+ | 856 | +------+ 再查一下張旭老師帶了那些課 SELECT c_no FROM course WHERE t_no=(SELECT t_no FROM teacher WHERE t_name='张旭'); +-------+ | c_no | +-------+ | 6-166 | 再查一下該課程對應的學生成績 SELECT * FROM score where c_no=(SELECT c_no FROM course WHERE t_no=(SELECT t_no FROM teacher WHERE t_name='张旭')); +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 6-166 | 85 | | 105 | 6-166 | 79 | | 109 | 6-166 | 81 | +------+-------+----------- -
查询选修课程的同学人数多余 5 人的教师姓名
插入一些數據便於操作 INSERT INTO score VALUES('101','3-105','90'); INSERT INTO score VALUES('102','3-105','91'); INSERT INTO score VALUES('104','3-105','89'); 首先找出選修的課程人數大於5人的課程 SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*)>5; 然後找出這個課程是由那個老師帶的 select * from teacher; +------+--------+-------+------------+-----------+-----------------+ | t_no | t_name | t_sex | t_birth | t_rof | t_dep | +------+--------+-------+------------+-----------+-----------------+ | 804 | 李诚 | 男 | 1958-12-02 | 副教授 | 计算机系 | | 825 | 王萍 | 女 | 1972-05-05 | 助教 | 计算机系 | | 831 | 刘冰 | 女 | 1977-08-14 | 助教 | 电子工程系 | | 856 | 张旭 | 男 | 1969-03-12 | 讲师 | 电子工程系 | +------+--------+-------+------------+-----------+-----------------+ c_no無法直接與teacher中的字段關聯 這裏利用一個course SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*)>5); +------+ | t_no | +------+ | 825 | +------+ 再找825課程對應的教師名字 SELECT t_name FROM teacher WHERE t_no IN (SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(*)>5)); +--------+ | t_name | +--------+ | 王萍 | +--------+ -
查询95033班和95031班全体学生的记录
SELECT * FROM student WHERE s_class IN ('95033','95031') ORDER BY s_class; +------+--------+-------+---------------------+---------+ | s_no | s_name | s_sex | s_birthday | s_class | +------+--------+-------+---------------------+---------+ | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | +------+--------+-------+---------------------+---------+ 注：ORDER BY s_class 按照s_class從小到大的順序排列 -
查询存在85分以上成绩的课程c_no
SELECT * FROM score where sc_degree > 85; +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 103 | 3-245 | 86 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | +------+-------+-----------+ -
查出所有'计算机系' 教师所教课程的成绩表
SELECT * FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no In (select t_no from teacher WHERE t_dep = '计算机系' )); +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 3-245 | 86 | | 105 | 3-245 | 75 | | 109 | 3-245 | 68 | | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +------+-------+-----------+ -
查询'计算机系'与'电子工程系' 不同职称的教师的name和rof
SELECT * FROM teacher WHERE t_depart = '计算机系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '电子工程系') UNION SELECT * FROM teacher WHERE t_depart = '电子工程系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '计算机系'); +------+--------+-------+---------------------+--------+------------+ | t_no | t_name | t_sex | t_birthday | t_rof | t_depart | +------+--------+-------+---------------------+--------+------------+ | 804 | 李诚 | 男 | 1958-12-02 00:00:00 | 副教授 | 计算机系 | | 856 | 张旭 | 男 | 1969-03-12 00:00:00 | 讲师 | 电子工程系 | +------+--------+-------+---------------------+--------+------------+ 注：UNION將兩個表拼接在了一起 -
查询选修编号为"3-105"课程且成绩至少高于选修编号为'3-245'同学的記錄,并且按照sc_degree从高到地次序排序
SELECT * FROM score WHERE sc_degree>ANY(SELECT sc_degree FROM score WHERE c_no='3-245') AND c_no='3-105' ORDER BY sc_degree DESC; +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 103 | 3-105 | 92 | | 102 | 3-105 | 91 | | 101 | 3-105 | 90 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | | 109 | 3-105 | 76 | +------+-------+-----------+ -
查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学的記錄
SELECT * FROM score WHERE sc_degree>All(SELECT sc_degree FROM score WHERE c_no='3-245') AND c_no='3-105'; +------+-------+-----------+ | s_no | c_no | sc_degree | +------+-------+-----------+ | 101 | 3-105 | 90 | | 102 | 3-105 | 91 | | 103 | 3-105 | 92 | | 104 | 3-105 | 89 | | 105 | 3-105 | 88 | +------+-------+-----------+ -
查询所有教师和同学的 name ,sex, birthday
由於兩個表的字段可能不相同，聯合在一起後，注意新的表的字段要變化
SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday FROM student WHERE s_sex = '女' UNION SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher WHERE t_sex = '女'; -
查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到）
SELECT s_class FROM student WHERE s_sex = '男' GROUP BY s_class HAVING COUNT(s_no) > 1; -
查询student 中每个学生的姓名和年龄(当前时间 - 出生年份
SELECT s_name, YEAR(NOW()) - YEAR(s_birth) AS age FROM student; +-----------+------+ | s_name | age | +-----------+------+ | 曾华 | 43 | | 匡明 | 45 | | 王丽 | 44 | | 李军 | 44 | | 王芳 | 45 | | 陆军 | 46 | | 王尼玛 | 44 | | 张全蛋 | 45 | | 赵铁柱 | 46 | +-----------+------+ 注：1.如果 YEAR(NOW()) - YEAR(s_birth) 不用age表示的話，則輸出的就是 +-----------+-----------------------------+ | s_name | YEAR(NOW()) - YEAR(s_birth) | +-----------+-----------------------------+ | 曾华 | 43 | | 匡明 | 45 | | 王丽 | 44 | | 李军 | 44 | | 王芳 | 45 | | 陆军 | 46 | | 王尼玛 | 44 | | 张全蛋 | 45 | | 赵铁柱 | 46 | +-----------+-----------------------------+ 2.NOW()表示現在的時間 -
查询student中最大和最小的 s_birthday的值
SELECT MAX(s_birthday),MIN(s_birthday) FROM student; +---------------------+---------------------+ | MAX(s_birthday) | MIN(s_birthday) | +---------------------+---------------------+ | 1977-09-01 00:00:00 | 1974-06-03 00:00:00 | +---------------------+---------------------+ -
以班级号和年龄从大到小的顺序查询student表中的全部记录
SELECt * FROM student ORDER BY s_class DESC, s_birthday; +------+--------+-------+---------------------+---------+ | s_no | s_name | s_sex | s_birthday | s_class | +------+--------+-------+---------------------+---------+ | 110 | 张飞 | 男 | 1974-06-03 00:00:00 | 95038 | | 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 | | 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 | | 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 | | 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 | | 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 | | 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 | | 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 | | 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 | | 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 | +------+--------+-------+---------------------+---------+ -
查询"男"教师 及其所上的课
SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_sex = '男'); +-------+----------+------+ | c_no | c_name | t_no | +-------+----------+------+ | 3-245 | 操作系统 | 804 | | 6-166 | 数字电路 | 856 | +-------+----------+------+ -
查询最高分同学的s_no c_no 和 sc_degree;
SELECT * FROM score WHERE sc_degree IN (SELECT MAX(sc_degree) FROM score);
6.事務
-
什麼是事務
在mysql中，事務是不可分割的最小工作單元，它保證了業務的完整性。
例如：銀行轉賬
a -> -100: update user set money=money-100 where name ='a'; b -> +100: update user set money=money+100 where name ='b';在實際程序中，可能只有一條語句成功了，而另外一條沒有成功？就會出現前後不一致的情況。
事務保證了業務的完整性，意思是保證要麼同時成成功，要麼同時失敗
-
如何控制事務
mysql默認自動提交
select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+自動提交的作用是當執行一個sql語句時，效果會立刻展現出來，且不能回滾
例如：
CREATE TABLE user1( id INT PRIMARY KEY, name VARCHAR(20), money INT ); INSERT INTO user1VALUES(1,'a',1000); +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | +----+------+-------+ 效果立刻顯現，此時我執行回滾操作：撤銷上一語句的效果 rollback; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 1000 | +----+------+-------+ 1 row in set (0.00 sec) 發現沒有改變 如何才能改變呢？ 設置mysql自動提交爲false； set autocommit=0; +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 這個時候就可以進行rollback的回滾操作。 如果自動提交關閉，可以時候用手動提交的操作：commit；這個是才使用rollback是無效的 也就是說事務提供了一個撤銷返回的機會 -
手動開啓事務
手動開始事務，除了set autocommit=0;還有2個方法:begin或者start transaction;
事務開啓，就可以使用rollback;
SELECT * FROM user1; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 900 | | 2 | b | 1100 | update user1 set money=money-100 where name ='a'; update user1 set money=money-100 where name ='b'; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 800 | | 2 | b | 1000 | +----+------+-------+ rollback; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 800 | | 2 | b | 1000 | +----+------+-------+ 無效，是因此此時autocommit爲1，效果會立刻展示，不支持回滾。 begin;(或者 start transaction) update user1 set money=money-100 where name ='a'; update user1 set money=money+100 where name ='b'; +----+------+-------+ | id | name | money | +----+------+-------+ | 1 | a | 700 | | 2 | b | 1100 | +----+------+-------+事務開啓之後，一旦commit，事務就已經結束了，就沒辦法回滾了。
-
事務的特徵(ACID)
A:原子性：事務是最小的單位，不可以在分割。
C:一致性：要求同一事務中的sql語句，必須保證同時成功或者失敗
I:隔離性：事務1和事務2是具有隔離
D:持久性：事務一旦結束，就不返回。
總結一下：
事務開啓：1.set autocommit=0;2.begin;3.start transaction;
提交：commit;
回滾:rollback;
-
隔離性
隔離性有4種，包括：
1.read uncommited; 讀未提交的
2.read commited; 讀已提交的
3.repeatable read; 可以重復讀
4.serializable; 串行化
查看隔離級別： A：系統級別：select@@global.tx_isolation; B:會話級別：select@@tx_isolation; +-----------------------+ | @@global.tx_isolation | +-----------------------+ | REPEATABLE-READ | +-----------------------+ 1 row in set (0.00 sec) +-----------------+` | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 修改隔離級別： SET @@global.tx_isolation='Read-Committed' 1.read uncommited 有事務a和事務b，a對事務進行操作，在操作過程中，事務沒有被提交，但是b可以看到a的操作。這樣就容易出現賍讀的情況，也就是 1一個事務讀到了另外一個事務未提交的數據。 2.read commited 讀取提交的數據，容易出現問題，也就是如果數據發生改變，這讀取前後數據不一致，所以沒辦法重復讀 3.repeatable read; 可以解決2的問題，容易出現幻讀的問題。事務A和事務B同時操作一張表，事務a提交的數據，不能被事務b讀取到，造成幻讀 4.serializable; 串行化 可以解決3的問題，兩邊操作按照串行的順序，事務A在操作的時候，事務b不能操作，直至事務a提交了。
7.參考
1.https://blog.csdn.net/weixin_42209572/article/details/98983741
浙公网安备 33010602011771号