SQL语句之查询数据（MySQL）

刺骨的言语ヽ痛彻心扉 2021-09-22 11:36 504阅读 0赞

查询数据在数据库的操作中相当的重要，下面简单的介绍一下比较基础的SQL查询语句。

**以下列的表为例进行操作：**

CREATE TABLE student(
    	id INT,
    	NAME VARCHAR(20),
    	chinese FLOAT,
    	english FLOAT,
    	math FLOAT
    );
    
    DELETE FROM student;
    
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90);
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95);
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'王也',87,78,77);
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'李奎',88,98,92);
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'李宝',82,84,67);
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'张楚岚',55,85,45);
    INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

-- 查询所有数据
    SELECT * FROM student;

![70][]

-- 查询指定列数据
    SELECT id,NAME FROM student;

![70 1][]

-- 查询指定列并添加别名(as可以省略)
    SELECT id AS '编号',NAME AS '姓名' FROM student;
    或（SELECT id '编号',NAME '姓名' FROM student;）

![70 2][]

-- 查询时添加常量列(添加一个年级列)
    SELECT *,'三年级'AS '年级' FROM student;

![70 3][]

-- 查询时合并相关列（chinese，english，math三个的总分）
    -- 注意：合并列时只能合并数值类型的字段
    SELECT id,NAME,(chinese+english+math) AS '总成绩' FROM student;

![70 4][]

-- 在表中添加新的字段,方便后面使用
    ALTER TABLE student ADD gender CHAR;
    UPDATE student SET gender='男';

![70 5][]

-- 查询时除去重复记录（distinct）[查询表中学生的性别]
    SELECT DISTINCT gender FROM student;
    SELECT DISTINCT (gender) FROM student;-- 可以在字段处打上括号

![70 6][]

**-- 条件查询（where）**

-- 查询id为2且姓名为李进的学生(and)
    SELECT * FROM student WHERE id=2 AND NAME='李进';

![70 7][]

-- 查询id为3，或姓名是王也的学生(or)
    SELECT * FROM student WHERE id=4 OR NAME='王也';

![70 8][]

-- 查询English分数在80到95之间的学生
    SELECT * FROM student WHERE english>=80 AND english<=95;
    或
    SELECT * FROM student WHERE english BETWEEN 80 AND 95;

![70 9][]

-- 查询学生性别为空的记录
    
    UPDATE student SET gender=NULL WHERE id=7 OR id=6;-- 先置空
    
    SELECT * FROM student WHERE gender IS NULL OR gender='';-- （包括null和空字符串）

![70 10][]

-- 查询性别不为空的
    
    SELECT * FROM student WHERE gender IS NOT NULL ;

![70 11][]

-- 注意区分这两条sql语句
    SELECT * FROM student WHERE gender IS NOT NULL AND gender<>'';

![70 12][]

-- 模糊的条件查询
    -- %：表示任意个字符
    -- _:表示单个字符
    
    -- 查询姓“张”的学生
    SELECT * FROM student WHERE NAME LIKE '张%';

![70 13][]

-- 查询姓“李”的且姓名只有两个字的
    SELECT * FROM student WHERE NAME LIKE '李_';

![70 14][]

-- 聚合查询
    
    -- 常用的聚合函数：sum()，avg(),max(),min(),count()
    
    -- 查询学生Chinese的总分
    SELECT SUM(chinese) FROM student;

![70 15][]

-- 查询学生math的平均分
    SELECT AVG(math) FROM student;

![70 16][]

-- 查询英语的最高分的学生
    SELECT NAME,english FROM student WHERE english=(SELECT MAX(english) FROM student);

![70 17][]

-- 查询数学的最低分
    SELECT MIN(math) FROM student;

![70 18][]

-- 统计有多少学生
    SELECT COUNT(*) FROM student;

![70 19][]

-- 注意：当count（字段）时，是统计该字段不为空的记录，而count(*)是统计每个字段的记录并取最大的值
    SELECT COUNT(gender) FROM student;

![70 20][]

-- 分页查询（limit 起始行（从0开始），查询几行）
    SELECT * FROM student LIMIT 0,2;
    SELECT * FROM student LIMIT 2,2;

![70 21][]

![70 22][]

-- 查询排序(order by)
    -- asc:正序排序 ，desc：反序排序
    SELECT * FROM student ORDER BY math ASC;
    
    SELECT * FROM student ORDER BY english DESC;

![70 23][]

![70 24][]

把数据修改成如图所示：

![70 25][]

-- 分组查询（group by）
    -- 查询男女的人数
    SELECT gender ,COUNT(*) FROM student GROUP BY gender;

![70 26][]

-- 查询总人数大于3的性别
    SELECT gender FROM student GROUP BY gender HAVING COUNT(*)>3;

![70 27][]

[70]: /images/20210920/b5e1c4e4548c46668ba54ba6cb44cafd.png
[70 1]: /images/20210920/5a211eaa1938432d800a0bedc24fa83f.png
[70 2]: /images/20210920/d58cdc167818409b97702b13516bffe6.png
[70 3]: /images/20210920/2a39b25669fe4c3a83be58d1b3d71e07.png
[70 4]: /images/20210920/d1d52bf2d1614fab8a1db41177eaae9a.png
[70 5]: /images/20210920/ce7747ad97dd4044aa3a2a43daab8ff3.png
[70 6]: /images/20210920/748e50346e754e108fbf49df04c0c9b9.png
[70 7]: /images/20210920/ee55c2ea2a7a40aaba385796b5b3383c.png
[70 8]: /images/20210920/e06f04f97890451d8998bbc43e8fb8e1.png
[70 9]: /images/20210920/33e837b118ca4adf85e1fe6f80684b35.png
[70 10]: /images/20210920/6b537a4d024b4764b1bfe513ed14ad38.png
[70 11]: /images/20210920/966284e10189475989e4e0e64be6f0dc.png
[70 12]: /images/20210920/6987c1db7e9940d7bd9276d9a28e7acb.png
[70 13]: /images/20210920/ce8fbd0915734ca187c447ca5fc483f5.png
[70 14]: /images/20210920/40a09a557ef24b27a1663351e85ac4b2.png
[70 15]: /images/20210920/e7db5259263142378b93e7a183a718f6.png
[70 16]: /images/20210920/c1f38dfcb446444f853363a4a750f2bc.png
[70 17]: /images/20210920/548276e798174c8e883aa4035c068173.png
[70 18]: /images/20210920/2643a8905316476a92d29010fcd921c5.png
[70 19]: /images/20210920/c68dcc26c3564c1a915d59507b54e4bc.png
[70 20]: /images/20210920/7cc0527d1f5b43afbc2e27fb8cafc817.png
[70 21]: /images/20210920/17f4372ae8624f10ad3819472ee2958b.png
[70 22]: /images/20210920/f4278bd3586249bcbc12d8856fbfe1a1.png
[70 23]: /images/20210920/1d7ab132ccf74517b9343ef035cade27.png
[70 24]: /images/20210920/025fdfa2a9574ad38b5a029624413165.png
[70 25]: /images/20210920/7e0b59ae93c4428e8c51ba53b3b894bb.png
[70 26]: /images/20210920/f163e96562eb483982b73df02d2515a0.png
[70 27]: /images/20210920/c830b09eddff43d4b538a897e6dd328d.png