MySQL使用实例



#使用数据库

USE test;

#建表 student
CREATE TABLE student(
 id INT,
 named VARCHAR(20) NOT NULL COMMENT '学生姓名',
 chinese FLOAT DEFAULT '0',
 english FLOAT DEFAULT '0',
 math FLOAT DEFAULT '0'
);

#插入数据

INSERT INTO student(id,named,chinese,english,math) VALUES(1,'小明',89,78,90);
INSERT INTO student(id,named,chinese,english,math) VALUES(2,'李明',67,53,95);
INSERT INTO student(id,named,chinese,english,math) VALUES(3,'张三',87,78,77);
INSERT INTO student(id,named,chinese,english,math) VALUES(4,'李四',88,98,92);
INSERT INTO student(id,named,chinese,english,math) VALUES(5,'王五',82,84,67);
INSERT INTO student(id,named,chinese,english,math) VALUES(6,'张凯凯',55,85,45);
INSERT INTO student(id,named,chinese,english,math) VALUES(7,'王红',75,65,30);

 #查询姓名为李一的学生成绩
 SELECT chinese,english,math FROM student WHERE named = '李一';
 
 #查询英语成绩大于等于90分的同学
 SELECT named FROM student WHERE english >=90;
 
 #查询总分大于200分的所有同学
 SELECT named FROM student WHERE (chinese+english+math) >200;
 
 #模糊查询WHERE 列名 like
 #查询所有姓李的学生英语成绩。
 SELECT named,english FROM student WHERE named LIKE '李%';
 
 #查询所有姓李的,两个字学生英语成绩。
 SELECT named,english FROM student WHERE named LIKE '李_';
 
 #查询所有名字中有财学生英语成绩。
 SELECT named,english FROM student WHERE named LIKE '_财_';
 
 #查询所有姓李并且名字为3个字的学生英语成绩。
 SELECT named,english FROM student WHERE named LIKE '李__';
 
 #查询英语>80或者总分>200的同学
 SELECT named,english FROM student WHERE english>80 || (chinese+english+math)>200;
 
 #统计每个学生的总分
 SELECT id AS '编号',Named AS '姓名',chinese AS '语文',english AS '英语',Math AS '数学',  (chinese+english+math) AS '总分' FROM student;

#分页查询
 #第二行开始，查两行
 SELECT * FROM STUDENT LIMIT 1,2;

#排序
#  倒序 desc 正序 asc
 SELECT * FROM student ORDER BY chinese DESC; 
 SELECT * FROM student ORDER BY chinese ASC;