查询数据在数据库的操作中相当的重要,下面简单的介绍一下比较基础的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;
-- 查询指定列数据
SELECT id,NAME FROM student;
-- 查询指定列并添加别名(as可以省略)
SELECT id AS '编号',NAME AS '姓名' FROM student;
或(SELECT id '编号',NAME '姓名' FROM student;)
-- 查询时添加常量列(添加一个年级列)
SELECT *,'三年级'AS '年级' FROM student;
-- 查询时合并相关列(chinese,english,math三个的总分)
-- 注意:合并列时只能合并数值类型的字段
SELECT id,NAME,(chinese+english+math) AS '总成绩' FROM student;
-- 在表中添加新的字段,方便后面使用
ALTER TABLE student ADD gender CHAR;
UPDATE student SET gender='男';
-- 查询时除去重复记录(distinct)[查询表中学生的性别]
SELECT DISTINCT gender FROM student;
SELECT DISTINCT (gender) FROM student;-- 可以在字段处打上括号
-- 条件查询(where)
-- 查询id为2且姓名为李进的学生(and)
SELECT * FROM student WHERE id=2 AND NAME='李进';
-- 查询id为3,或姓名是王也的学生(or)
SELECT * FROM student WHERE id=4 OR NAME='王也';
-- 查询English分数在80到95之间的学生
SELECT * FROM student WHERE english>=80 AND english<=95;
或
SELECT * FROM student WHERE english BETWEEN 80 AND 95;
-- 查询学生性别为空的记录
UPDATE student SET gender=NULL WHERE id=7 OR id=6;-- 先置空
SELECT * FROM student WHERE gender IS NULL OR gender='';-- (包括null和空字符串)
-- 查询性别不为空的
SELECT * FROM student WHERE gender IS NOT NULL ;
-- 注意区分这两条sql语句
SELECT * FROM student WHERE gender IS NOT NULL AND gender<>'';
-- 模糊的条件查询
-- %:表示任意个字符
-- _:表示单个字符
-- 查询姓“张”的学生
SELECT * FROM student WHERE NAME LIKE '张%';
-- 查询姓“李”的且姓名只有两个字的
SELECT * FROM student WHERE NAME LIKE '李_';
-- 聚合查询
-- 常用的聚合函数:sum(),avg(),max(),min(),count()
-- 查询学生Chinese的总分
SELECT SUM(chinese) FROM student;
-- 查询学生math的平均分
SELECT AVG(math) FROM student;
-- 查询英语的最高分的学生
SELECT NAME,english FROM student WHERE english=(SELECT MAX(english) FROM student);
-- 查询数学的最低分
SELECT MIN(math) FROM student;
-- 统计有多少学生
SELECT COUNT(*) FROM student;
-- 注意:当count(字段)时,是统计该字段不为空的记录,而count(*)是统计每个字段的记录并取最大的值
SELECT COUNT(gender) FROM student;
-- 分页查询(limit 起始行(从0开始),查询几行)
SELECT * FROM student LIMIT 0,2;
SELECT * FROM student LIMIT 2,2;
-- 查询排序(order by)
-- asc:正序排序 ,desc:反序排序
SELECT * FROM student ORDER BY math ASC;
SELECT * FROM student ORDER BY english DESC;
把数据修改成如图所示:
-- 分组查询(group by)
-- 查询男女的人数
SELECT gender ,COUNT(*) FROM student GROUP BY gender;
-- 查询总人数大于3的性别
SELECT gender FROM student GROUP BY gender HAVING COUNT(*)>3;