表数据增删改查

• 条件查询和聚合函数查询

-- 条件查询
SELECT * FROM student WHERE gender='男';

--  查询合格的范围内 and
SELECT * FROM student WHERE id BETWEEN 4 AND 12;

-- or 或者 in 效果哦相同
SELECT * FROM student WHERE id=16 OR id=6 OR id=10;
SELECT * FROM student WHERE id IN(16,6,10);

-- 判断为null
SELECT * FROM student WHERE age IS NULL;

-- 判断不为null值
SELECT * FROM student WHERE age IS NOT NULL;

-- 模糊查询%分号代表任意字符
SELECT * FROM student WHERE NAME LIKE '王%';

-- 一个下划线代表一任意字符
SELECT * FROM student WHERE NAME LIKE '_贵妃%';

-- 查询名字为两个的
SELECT * FROM student WHERE NAME LIKE '___';

-- 查询包含指定内容
SELECT * FROM student WHERE NAME LIKE '%贵妃%';

-- 聚合函数 统计数量
SELECT COUNT(*) FROM student;

-- max最大值
SELECT MAX(age) FROM student;

-- min 最小值
SELECT MIN(age) FROM student;

-- sum求和
SELECT SUM(age)FROM student;

-- avg求平均值
SELECT AVG(age)FROM student;

• 表数据增删改

-- 给指定列添加数据  INSERT INTO +表名 +类名 values +值
INSERT INTO student(NAME,age,gender)VALUES('赵云',33,'男');

-- 给全部列添加数据 INSERT INTO +表名 values +值
INSERT INTO student VALUES ('张飞',19,32,'男');

-- 给指定列批量添加数据  批量添加数据
INSERT INTO student VALUES ('关羽',22,32,'男'),('刘备',20,37,'男'),('曹操',21,39,'男');



-- 修改数据  UPDATE + 表名 SET 列名=值,列名=值 WHERE条件
UPDATE student SET NAME='诸葛亮',age=66 WHERE NAME='青霞';

-- 删除数据 DELETE FROM +表名 WHERE条件
DELETE FROM student WHERE NAME='张星';

• 表数据查询

-- 排序查询 SELECT * FROM +表名 +条件(筛选) ORDER BY +列名排序   常用与数字之间的升降排序
SELECT * FROM student ORDER BY age ASC;

-- 条件查询可配合排序查询使用 
SELECT * FROM student WHERE NAME LIKE '%贵妃%' ORDER BY age ASC;

-- 模糊查询筛选条件 若有列名值相同,则执行另一个排序方式
SELECT * FROM student WHERE NAME LIKE '诸葛亮%' ORDER BY id ASC,age=DESC;


-- 分组查询  SELECT +列名 + FROM +表名 +条件 
SELECT NAME ,SUM(age) FROM student GROUP BY NAME;


-- 分组查询前进行条件筛选
SELECT NAME, SUM(age) FROM student WHERE id > 10 GROUP BY NAME;

-- 条件筛选后进行分组,然后在通过HAVING进行二次筛选
SELECT NAME, SUM(age)AS getsum FROM student WHERE id > 10 GROUP BY NAME HAVING getsum>30;

-- 条件筛选后进行分组,然后在通过HAVING进行二次筛选 再通过 ORDER BY 进行升降序排序
SELECT NAME, SUM(age)AS getsum 
FROM student WHERE id > 10 
GROUP BY NAME HAVING getsum>30
ORDER BY getsum DESC;


-- 页面查询 当前页数=(当前页数-1)*显示的条数
SELECT * FROM student LIMIT 0,5;

SELECT * FROM student LIMIT 5,5;