单表查询

单表查询
查询所有列

SELECT * FROM product;

查询指定列

SELECT pro_name,price,pinpai FROM product;

添加常量列

SELECT pro_name AS '产品名称',price FROM product;

创建学生表

CREATE TABLE stu(
     sid  INT,
     sname  VARCHAR(10),
     sex  VARCHAR(2),
     servlet  DOUBLE,
     jsp   DOUBLE,
     html  DOUBLE 
)
SHOW TABLES;
INSERT INTO stu VALUES(1,'佩奇','女',100,60,80);
INSERT INTO stu VALUES(2,'乔治','男',25,58,100);
INSERT INTO stu VALUES(3,'薛之谦','男',100,100,100);
INSERT INTO stu VALUES(4,'李荣浩','男',90,90,90);
INSERT INTO stu(sid,sname,servlet,html) VALUES(5,'于文文',90,90);
INSERT INTO stu(sid,sname,servlet,html) VALUES(6,'',90,10);
SELECT * FROM stu;

查询时合并列

SELECT sname,(servlet+jsp+html) AS '总成绩' FROM stu;

查询时去除重复记录

SELECT DISTINCT sex FROM stu;

条件查询

SELECT * FROM stu WHERE sex='男' AND sname='薛之谦';
SELECT * FROM stu WHERE sex='女' OR sname='薛之谦';

查询大于70分的学生

SELECT * FROM stu WHERE servlet>60;

查询jsp成绩不等于60分的学生

SELECT * FROM stu WHERE jsp<>60;

查询html成绩在60和100之间的学生

SELECT * FROM stu WHERE html BETWEEN 60 AND 100;
SELECT * FROM stu WHERE html >= 60 AND html<=100;

查询sex为null的学生

SELECT * FROM stu WHERE sex IS NULL;
SELECT * FROM stu WHERE sex IS NOT NULL;

 查询sname是空字符串的学生

SELECT * FROM stu WHERE sname='';
SELECT * FROM stu WHERE sname<>'';

查询sex不为空的学生

SELECT * FROM stu WHERE sex IS NOT NULL AND sex<>'';

查询所有产品中带有索尼的产品

SELECT * FROM product WHERE pro_name LIKE '%索尼%';

 查询班级中所有两个字的同学

SELECT * FROM stu WHERE sname LIKE '__';

 聚合函数：查询stu表中所有学生的servlet总成绩

SELECT SUM(servlet) FROM stu;

聚合函数：查询stu表中所有学生的servlet平均成绩

SELECT AVG(servlet) FROM stu;

 聚合函数：查询stu表中jsp课程中的最高分

SELECT MAX(JSP) FROM stu;

最低分

SELECT MIN(jsp) FROM stu;

查询stu表中有多少人

SELECT COUNT(*) FROM stu;（每列统计 取最大值）

 分页

SELECT * FROM stu LIMIT 0,2;

查询排序

SELECT * FROM stu ORDER BY html ASC;（升序）
SELECT * FROM stu ORDER BY html DESC;(倒序)

查询stu表中所有男同学的html成绩排序

SELECT * FROM stu WHERE sex='男' ORDER BY html;
SELECT * FROM stu ORDER BY jsp ASC,html DESC;

查询男女人数

SELECT sex,COUNT(*) FROM stu GROUP BY sex; 

查询总人数大于2的性别

SELECT SEX,COUNT(*) FROM STU GROUP BY sex HAVING COUNT(*)>2;