MySql常用语句

-- 添加数据
INSERT into student(name,age) VALUES('李四',18);

-- 查询所有数据
SELECT * FROM student;

-- 修改数据
UPDATE student SET age = 52 WHERE id = 2

-- 一次修改多条数据
UPDATE student SET name= '李四',age=30 WHERE id = 3

-- 删除数据
DELETE from student WHERE id = 4

-- 查询指定列的数据
SELECT * FROM student;

-- 去除重复记录
SELECT DISTINCT name from student;

-- as 给表头列取别名
SELECT name as 姓名 FROM student;
 
--  条件查询
SELECT * FROM student WHERE age>20;

-- 多个条件查询
SELECT * from student WHERE age>20 AND age<50;

-- 区间条件查询
SELECT * from student WHERE age BETWEEN 10 AND 30;

-- 不等于查询
SELECT * from student WHERE age!=30;

-- 或者查询
SELECT * from student WHERE age=30 OR age=12;

-- 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 ORDER BY age ASC;

-- 排序查询  降序排列
SELECT * FROM student ORDER BY age DESC;

-- 排序查询  多字段排序
-- 先按降序，相同内容再id升序排列
SELECT * FROM student ORDER BY age DESC , id ASC;





-- 统计有多少条数据
SELECT COUNT(*) FROM student;
-- SELECT COUNT(id) FROM student

-- 查询最大年龄
SELECT MAX(age) FROM student;

-- 查询最小年龄
SELECT MIN(age) FROM student; 

-- 查询总年龄
SELECT SUM(age) FROM student; 

-- 查询平均年龄
SELECT AVG(age) FROM student; 




-- 分别查询男女的平均年龄
SELECT AVG(age),COUNT(*) FROM student GROUP BY sex;

-- 分别查询男女的平均年龄和人数
SELECT AVG(age),COUNT(*) FROM student GROUP BY sex;

-- 分别查询男女的人数，人数需要大于等于3
SELECT COUNT(*) FROM student GROUP BY sex HAVING COUNT(3)>=3;




-- 分页查询
-- 起始索引=（当前页码 - 1）*每页条数
-- 第一页的10条记录
SELECT * FROM student LIMIT 0 , 10;

-- 第二页的10条记录
SELECT * FROM student LIMIT 10 , 10;