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;

多表查询

-- 多表查询

-- 隐式内连接查询
SELECT * FROM student,class WHERE student.class_id = class.id;

SELECT student.name,class.class_name FROM student,class WHERE student.class_id = class.id;

-- 显式内连接查询
SELECT * from student INNER JOIN class on student.class_id = class.id;


-- 左外连接 
-- 查询student所有数据和对应的部门信息
SELECT * FROM student LEFT JOIN class ON student.class_id = class.id;

-- 右外连接
SELECT * FROM student RIGHT JOIN class ON student.class_id = class.id;


-- 嵌套查询
-- 单行多列
SELECT * FROM student WHERE age > (SELECT age FROM student WHERE id=1);

-- 多行单列
-- 查询班级id等于或等于2
SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE id = 1 or id = 2);

-- 多行多列
-- 查询学生id=20的信息和部门信息
SELECT * FROM (SELECT * FROM student WHERE id =20) stu,class WHERE stu.class_id = class.id;