參考資料來源于Bilibili《一天學(xué)會(huì)MYSQL數(shù)據(jù)庫》
- 條件查詢
- 排序
- 子查詢
- 其它（按所選字段查詢、查詢數(shù)目）
1、準(zhǔn)備數(shù)據(jù)
創(chuàng)建四個(gè)表：學(xué)生表、教師表、課程表和成績表。
學(xué)生表(學(xué)號(hào) 姓名 性別 出生日期 所在班級(jí))
CREATE TABLE student(s_no VARCHAR(10) PRIMARY KEY,
s_name VARCHAR(20) NOT NULL,
s_sex CHAR(2) NOT NULL,
s_birth DATETIME NOT NULL,
s_class VARCHAR(10) NOT NULL);
教師表(教師編號(hào)、教師名字、教師性別、出生日期、職稱、所在部門)
CREATE TABLE teacher(t_no VARCHAR(10) PRIMARY KEY,
t_name VARCHAR(20) NOT NULL,
t_sex CHAR(2) NOT NULL,
t_birth DATETIME NOT NULL,
t_prof VARCHAR(20) NOT NULL,
t_depart VARCHAR(20) NOT NULL);
課程表(課程號(hào),課程課程名稱,教師編號(hào))
CREATE TABLE course(c_no VARCHAR(10) PRIMARY KEY,
c_name VARCHAR(20) NOT NULL,
t_no VARCHAR(10),
FOREIGN KEY(t_no) REFERENCES teacher(t_no));
成績表(學(xué)號(hào),課程號(hào),成績)
CREATE TABLE score(s_no VARCHAR(10) ,
c_no VARCHAR(10),
grade DECIMAL,
FOREIGN KEY(s_no) REFERENCES student(s_no),
FOREIGN KEY(c_no) REFERENCES course(c_no),
PRIMARY KEY(s_no,c_no));
向表中添加數(shù)據(jù)
--學(xué)生表數(shù)據(jù)
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');
--教師表數(shù)據(jù)
INSERT INTO teacher VALUES('804','李誠','男','1958-12-02','副教授','計(jì)算機(jī)系');
INSERT INTO teacher VALUES('856','張旭','男','1969-03-12','講師','電子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','計(jì)算機(jī)系');
INSERT INTO teacher VALUES('831','劉冰','女','1977-08-14','助教','電子工程系');
--添加課程表
INSERT INTO course VALUES('3-105','計(jì)算機(jī)導(dǎo)論','825');
INSERT INTO course VALUES('3-245','操作系統(tǒng)','804');
INSERT INTO course VALUES('6-166','數(shù)字電路','856');
INSERT INTO course VALUES('9-888','高等數(shù)學(xué)','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');
2、查詢練習(xí)
a.查詢表中所有或者部分字段的數(shù)據(jù)
SELECT * FROM 表名；（所有字段）
SELECT 字段1，字段2，字段3... FROM 表名
SELECT s_no,s_name,s_sex from student;
--返回結(jié)果
+------+--------+-------+
| s_no | s_name | s_sex |
+------+--------+-------+
| 101 | 曾華 | 男 |
| 102 | 匡明 | 男 |
| 103 | 王麗 | 女 |
| 104 | 李軍 | 男 |
| 105 | 王芳 | 女 |
| 106 | 陸軍 | 男 |
| 107 | 王尼瑪 | 男 |
| 108 | 張全蛋 | 男 |
| 109 | 趙鐵柱 | 男 |
+------+--------+-------+
b.查詢表中不重復(fù)的字段
SELECT DISTINCT 字段名 FROM 表名;(使用關(guān)鍵字DISTINCT)
SELECT DISTINCT t_depart FROM teacher;
--返回結(jié)果
+------------+
| t_depart |
+------------+
| 計(jì)算機(jī)系 |
| 電子工程系 |
+------------+
c.查詢值在某一范圍內(nèi)的記錄
SELECT 字段名 FROM 表名 WHERE 值的條件；
值的條件：
- 邏輯運(yùn)算符
- BETWEEN AND
- IN 值的取值集合
SELECT * FROM score WHERE grade BETWEEN 60 AND 80;
--between and 包含邊界
--result
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 105 | 3-245 | 75 |
| 105 | 6-166 | 79 |
| 109 | 3-105 | 76 |
| 109 | 3-245 | 68 |
+------+-------+-------+
也可以
SELECT * FROM score WHERE grade < 60 AND grade >80;
SELECT * FROM score WHERE grade in (76,79);
d.按所選字段值升序或者降序排列
SELECT 字段名 FROM 表名 ORDER BY 選中的字段 DESC(ASC);
SELECT * FROM student ORDER BY s_class DESC;
--result
+------+--------+-------+---------------------+---------+
| s_no | s_name | s_sex | s_birth | s_class |
+------+--------+-------+---------------------+---------+
| 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 |
+------+--------+-------+---------------------+---------+
以字段1升序，字段2降序
SELECT 字段名 FROM 表名 ORDER BY 字段1 ASC, 字段2 DESC;
SELECT * FROM score ORDER BY c_no ASC, grade DESC;
--result
+------+-------+-------+
| s_no | c_no | grade |
+------+-------+-------+
| 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 |
+------+-------+-------+
e.查詢符合某個(gè)字段的記錄數(shù)目
SELECT COUNT(*) FROM 表名 WHRER 條件；
查詢班級(jí)為”95031”的學(xué)生人數(shù)；
SELECT COUNT(*) FROM student WHERE s_class='95031';
--result
+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
f.子查詢
查詢score表中成績最高分的學(xué)生學(xué)號(hào)
SELECT s_no FROM score WHERE grade=(SELECT MAX(grade) FROM score);
--result
+------+
| s_no |
+------+
| 103 |
+------+
上述語句select max(grade) from score 可能會(huì)出現(xiàn)一個(gè)問題，即最大值有兩個(gè)及以上，為了避免這種現(xiàn)象，我們可以用limit x,y限制查詢記錄的個(gè)數(shù)。
如：
SELECT s_no, c-no FROM score ORDER BY grade LIMIT 0,1;
--result
+------+-------+
| s_no | c_no |
+------+-------+
| 109 | 3-245 |
+------+-------+
