MySQL read 查询语句1

实例

****创建新的表(student)********
CREATE TABLE student(
    id INT NOT NULL DEFAULT 1,
    NAME VARCHAR(20) NOT NULL DEFAULT '',
    chinese FLOAT NOT NULL DEFAULT 0.0,
    english FLOAT NOT NULL DEFAULT 0.0,
    math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
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,90);
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 `name`,english FROM student ;
-- 过滤英语重复数据，查询的列都相同才会去重
SELECT DISTINCT english FROM student ;
-- 统计每个学生的总分
SELECT `name`, (chinese + english + math) FROM student ;
-- 在所有学生总分上加10
SELECT `name`, (chinese + english + math + 10) FROM student ;
-- 使用别名表示分数
SELECT `name`, (chinese + english + math + 10) AS toal_score FROM student ;
SELECT * FROM student;

-- where子句
-- 查询姓名为赵云的
SELECT * FROM student
    WHERE `name` = '赵云';
-- 查询英语成绩大于200分的所有同学
SELECT * FROM student
    WHERE english > 90;
-- 查询总分大于200的所有同学
SELECT * FROM student
    WHERE (chinese + english + math) > 200;
-- 查询math大于60并且（and）id > 4的学生成绩
SELECT * FROM student
    WHERE math > 60 AND id > 4;
-- 查询英语大于语文成绩的同学
SELECT * FROM student
    WHERE english > chinese;
-- 查询总分大于200，并且数学<语文的姓韩的同学
SELECT * FROM student
    WHERE (chinese + english + math) > 200 AND
    math < chinese AND `name` LIKE '韩%';
-- 查询语文在70-80之间的同学
SELECT * FROM student
    WHERE chinese BETWEEN 70 AND 80;
-- 查询总分为189，790，191的同学
SELECT * FROM student
    WHERE (chinese + english + math) IN (189,190,191);
-- 查询所有姓李和姓宋成绩
SELECT * FROM student
    WHERE `name` LIKE '李%' OR `name` LIKE '宋%';
-- 查询数学比语文多1分的同学
SELECT * FROM student
    WHERE math >= (chinese + 1);
    
-- 数学成绩升序输出
SELECT * FROM student
    ORDER BY math;
-- 总分从高到低顺序输出  可以使用别·名排序
SELECT `name`, (chinese + english + math) AS total_score FROM student
    ORDER BY total_score ASC;
-- where + order排序
SELECT `name`, (chinese + english + math) AS total_score FROM student
    WHERE `name` LIKE '李%'
    ORDER BY total_score ASC; #默认升序
    
-- 统计有多少学生
SELECT COUNT(*) FROM student;
-- 统计总分大于250的人数有多少
SELECT COUNT(*) FROM student
    WHERE (chinese + english + math) > 250;
-- 统计数学成绩大于90的学生有多少
SELECT COUNT(*) FROM student
    WHERE math > 90;
-- count(*)与count(列)的区别
-- count(*)返回满足条件的记录的行数
-- count(列) 返回满足条件的某列有多少，但会排除为null
 
-- sum使用
-- 数学和语文的合计分
SELECT SUM(math),SUM(chinese) FROM student;
-- avg的使用
SELECT AVG(math) FROM student;

-- max/min的使用
SELECT MAX(math) AS high_score ,MIN(math) AS low_score FROM student;