mysql改数据库内容_mysql 数据库 内容的增删改查

/*所有字段插入值*//*注意插入值数目要与字段值一致*/

INSERT INTO student VALUES(1,'熊大','123','2019-10-18',1200);

INSERT INTO student VALUES(3,'熊二','123','2019-10-18',1200);

/*部分字段插入值*/

INSERT  INTO student (uid,PASSWORD, money)VALUES(2,'123456',10000);

/*修改语句*/

UPDATE student SET uname='熊二' WHERE uid=2;

UPDATE student SET PASSWORD='123456' WHERE uname='熊二';

/*修改多个逗号分隔*/

UPDATE student SET uname='琪琪国王',PASSWORD='admin'WHERE uid=1;

UPDATE student SET birth='2019/11/29'WHERE uid=2;

*不带条件的删除：全表删除 能回滚 只能删除数据不删约束*/

DELETE FROM student

/*带条件的删除*/

DELETE FROM student WHERE uid=3

/*全表删除 不能回滚 数据约束全删*/

TRUNCATE TABLE student

/*查询所有数据*/

SELECT *FROM student;

/*查询指定字段的值*/

SELECT uname,PASSWORD FROM student

/*查询时添加常量列*/

SELECT uname AS n,PASSWORD AS p FROM student

/*查询时合并列*/

SELECT sname,(html+js+jquery)/3 AS savg FROM score

/*查询时保留小数位数*/

SELECT sname,FORMAT(html,3)AS html FROM score

/*查询时对某个字段的值进行去重*/

/*查询所有同学所在的班级都有那几个班级*/

SELECT DISTINCT sclass FROM score

SELECT DISTINCT(sclass) FROM score

/*条件查询*/

/*查询所有女生的所有信息*/

SELECT * FROM score WHERE sex='女'

/*查询所有男生的姓名，html成绩和jquery成绩*/

SELECT sname,html,jquery FROM score WHERE sex='男'

/*查询外婆在哪个班*/

SELECT sclass FROM score WHERE sname='外婆'

/*查询所有js成绩不合格的同学的姓名*/

SELECT sname FROM score WHERE js<60

/*查询jquery成绩合格的所有女同学信息*/

SELECT * FROM score WHERE jquery>=60 AND sex='女'

/*查询所有html成绩不合格的男同学姓名*/

SELECT sname FROM score WHERE html<60 AND sex='男'

/*查询js成绩合格或者是html成绩合格的同学信息*/

SELECT * FROM score WHERE js>=60 OR html>=60

/*查询性别为男或者是java1018的同学姓名*/

SELECT * FROM score WHERE js>=60 OR html>=60;

/*不等于 <> !=*/

SELECT * FROM score WHERE html<>10;

/*查询HTML成绩在70-100之间同学信息 包头包尾`student``student`*/

SELECT * FROM score WHERE html BETWEEN 70 AND 100;

/*查询学生表中 姓名不为空的学生信息*/

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

/*查询学生表中 mima为空的学生信息*/

SELECT * FROM student WHERE PASSWORD='' OR PASSWORD IS NULL;

/*查询score表中性大的信息*/

SELECT * FROM score WHERE sname LIKE '大%';

/*查询score表中性大两个的信息*/

SELECT * FROM score WHERE sname LIKE '大_';

/*查询score表中性两个的信息*/

SELECT * FROM score WHERE sname LIKE '__';

/*查询score表java1018 js 总成绩*/

SELECT SUM(js)AS js总成绩 FROM score WHERE sclass='java1018';

/*查询score表java0723 html 平均成绩*/

SELECT FORMAT(AVG(html),2) FROM score WHERE sclass='java0723';

/*查询score表java1018 jquery最高分*/

SELECT MAX(jquery) FROM score WHERE sclass='java1018';

/*查询score表所有学生总成绩的最低分*/

SELECT MIN(IFNULL(jquery,0)+IFNULL(js,0)+IFNULL(html,0)) FROM score;

/*查询score本班有多少人  COUNT(*)原理是把每一列都统计一下取最大值*/

SELECT COUNT(*)FROM score;

/*查询本班有多少人*/

SELECT sname,COUNT(*) FROM score

-- 分页查询

-- 每页显示两条

-- 第一页 limit 起始行(从零开始)，每页显示行数

-- 分页：limit(当前页-1)*每页显示的条数，每页显示的条数

SELECT * from score LIMIT 0,2;

/*第二页*/

SELECT * from score LIMIT 2,2;

/*第三页*/

SELECT * from score LIMIT 4,2;

-- 排序放到最后

-- 查询所有同学信息并且按照js成绩从大到小排

SELECT *from score ORDER BY js desc;

SELECT *from score ORDER BY js asc;

SELECT *from score ORDER BY js;

-- 查询所有同学信息并且按照js成绩从大到小排 jquery成绩正序

SELECT *from score ORDER BY js desc,jquery asc;

-- 查询男女的人数

select sex,COUNT(*) FROM score  GROUP BY sex ORDER BY COUNT(*);

--查询每个性别的js总成绩

SELECT sex,SUM(js)FROM score  GROUP BY sex ORDER BY sum(js);

--查询哪个性别的js高于60分总成绩

SELECT sex,SUM(js)FROM score  GROUP BY sex HAVING sum(js)>60 ;

SELECT sex,SUM(js)as tt FROM score  GROUP BY sex HAVING tt>60;

--查询java1018班每个性别的js总成绩且高于60分

SELECT sex,SUM(js)FROM score WHERE sclass='java1018' GROUP BY sex HAVING sum(js)>=60 ;

-- 查询那个姓别的人数大于一

SELECT sex,sum(js),sum(jquery),sum(html)FROM score GROUP BY sex;

SELECT sex FROM score GROUP BY sex HAVING sex<>'女'and count(*)>1;

SELECT SUM(IFNULL(js,0))AS js总成绩 FROM score WHERE sclass='java1018';