CREATE TABLE class0328(

    id INT,

    cname VARCHAR(10),

    sex VARCHAR(5),

    age INT,

    birthday DATE,

    html DOUBLE,

    js DOUBLE,

    score DOUBLE

)

DESC class0328;

DROP TABLE class0328;

INSERT INTO class0328 VALUES(1,'董斌','男',25,'1993-09-13',80,10,60);

INSERT INTO class0328 VALUES(2,'张锐','男',25,'1993-09-25',81,50,60);

INSERT INTO class0328 VALUES(3,'王荣臣','男',23,'1996-07-07',30,55,65);

INSERT INTO class0328 VALUES(4,'韩凯','男',26,'1991-05-20',25,52,80);

INSERT INTO class0328 VALUES(5,'张禄','男',26,'1991-03-02',60,54,80);

INSERT INTO class0328 VALUES(6,'刘继勋','男',24,'1994-11-27',0.5,20,53);

INSERT INTO class0328 VALUES(7,'张维','男',29,'1989-02-24',100,50,45);

INSERT INTO class0328 VALUES(8,'牛攀','男',29,'1989-12-24',100,55,98);

INSERT INTO class0328 VALUES(8,'牛牛','女',29,'1989-12-24',100,55,98);

/*查询所有列*/

SELECT * FROM class0328

/*查询指定列*/

SELECT id,cname FROM class0328;

/*添加常量列(将id在查询时改名为编号显示....）*/

SELECT id AS '编号',cname AS '学生姓名' FROM class0328;

/*查询时合并列*/

SELECT cname,(html+js) AS '前端成绩' FROM class0328;

/*查询去除重复记录*/

SELECT sex FROM class0328(重复查询了）；

SELECT DISTINCT sex FROM class0328;

/*条件查询*/

SELECT * FROM class0328 WHERE id=1 AND sex='男';

/*，，*，，代表所有，可以替换单个属性例如姓名、年龄等*/

SELECT cname FROM class0328 WHERE id=1 AND sex='男';

/*或着查询*/

SELECT * FROM class0328 WHERE sex='男' OR age=25;

/*比较条件(>,<,>=,<=,=,<>(不等于))*/

SELECT cname FROM class0328 WHERE html>60;

SELECT *FROM class0328 WHERE js<>100;

/*下面俩意义一样*/

SELECT * FROM class0328 WHERE html>=0 AND html<=60;

SELECT * FROM class0328 WHERE html BETWEEN 0 AND 60;

INSERT INTO class0328 VALUES(8,'牛孙','',29,'1989-12-24',100,20,98);

/*判断性别不为空的数据*/

SELECT * FROM class0328 WHERE sex IS NULL;

SELECT * FROM class0328 WHERE sex <>'';

SELECT * FROM class0328 WHERE sex ='';

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

/*模糊查询(表里姓张的都查询）(%表示任意字符）（_表示一个任意字符）*/

SELECT * FROM class0328 WHERE cname LIKE '张%';

SELECT * FROM class0328 WHERE cname LIKE '王__';

SELECT * FROM class0328 WHERE cname LIKE '__臣';

/*聚合函数*/

SELECT SUM(html) AS 'htmlscore' FROM class0328;

SELECT SUM(html+js) FROM class0328;

/*平均成绩*/

SELECT AVG(html)AS'平均成绩' FROM class0328;

SELECT MAX(js)AS 'js最高分' FROM class0328;

SELECT cname,MIN(js)AS 'js低高分' FROM class0328;

/*有多少条*/

SELECT COUNT(sex) FROM class0328;

SELECT * FROM class0328 LIMIT 0,2;

SELECT * FROM class0328 LIMIT 3,2;