mysql数据的增删改查

一、插入数据

INSERT INTO USER VALUES(1,'admin','1995-12-30',99.8);

给指定字段加数据

INSERT INTO USER(sname,birth) VALUES('tom','2020-06-17');

二、修改数据

UPDATE USER SET sname='jerry' WHERE sid=1;

UPDATE USER SET sid=2,chengji=100 WHERE sname='tom';

三、删除数据

DELETE FROM USER WHERE sid=2;

清空表

DELETE FROM USER ;

TRUNCATE USER;

TRUNCATE USER;删除的更加彻底无法找回

四、查询数据

全部查询*指全部字节

SELECT * FROM student;

查询某个字节的信息

SELECT sname,birth FROM USER;

查询时并给查询的字节起一个暂时的名字

SELECT sname AS '姓名',sex AS '性别',address FROM student;

查询时数字类型的数据可以进行运算

SELECT sname,(html+css+js) FROM student;

查询时去除重复的

SELECT DISTINCT sex FROM student;

SELECT DISTINCT(sex) FROM student;

指定条件查询

SELECT * FROM student WHERE sid=2;

SELECT * FROM student WHERE sex='男';

指定条件的或者（or）与并且（and）

SELECT sname FROM student WHERE sex='男' AND address='山东淄博';

SELECT sname FROM student WHERE sex='女' OR address='山东淄博';

可以运用比较运算符 <>不等于 between数字域（包含边界）

SELECT sid FROM student WHERE html>60;

SELECT * FROM student WHERE html<60 AND js>60;

SELECT sname FROM student WHERE address<>'山东淄博';

SELECT * FROM student WHERE html BETWEEN 60 AND 70;

SELECT * FROM student WHERE sname IS NOT NULL AND sname<>'';

模糊查询 关键词like

SELECT * FROM student WHERE sname LIKE '小%';

SELECT * FROM student WHERE sname LIKE '%红%';

SELECT * FROM student WHERE sname LIKE '__';

SELECT * FROM student WHERE sname LIKE '海__';

运算函数

SELECT SUM(html) FROM student; 求和

SELECT AVG(html) FROM student; 平均值

SELECT MIN(js) FROM student; 最小值

SELECT MAX(css) FROM student;最大值

SELECT COUNT(*) FROM student;列的总数

分页显示 关键词 limit

SELECT * FROM student LIMIT 0,2;

SELECT * FROM student LIMIT 2,2;/*

（当前页-1）*每页显示数，每页显示的条数*/

SELECT * FROM student ORDER BY html;

SELECT * FROM student WHERE html>60 ORDER BY html DESC;

SELECT * FROM student ORDER BY js DESC LIMIT 0,3;

SELECT * FROM student ORDER BY html,js DESC;