SQL，简单的常用SQL

 

SELECT IN (1,2,3)中使用子查询

-- 查询id在1,2,3中的学生
SELECT * FROM tb_student WHERE id IN (1，2，3)
-- 等同于
SELECT * FROM tb_student WHERE id IN (SELECT id FROM tb_student WHERE id >2);//子查询中表名可以不一样

　　

返回逗号分隔的字符串1,2,3,4

-- 把学生表的id全部查出来，放到字段ids中，ids的值为1,2,3,4
SELECT GROUP_CONCAT(id) AS ids
FROM tb_student

 

查询tb_student表时，从tb_class表查班级名称

-- 查询tb_student表时，从tb_class表查班级名称
SELECT A.*,(SELECT class_name FROM tb_class WHERE class_id = A.class_id) AS class_name
FROM tb_student A

　　

-- 查博客的时候，根据user_type的值，分别从学生表和教师表中，查作者姓名
SELECT A.*,
	CASE 
    WHEN A.user_type = 0 THEN (SELECT `name` FROM tb_student WHERE tb_student.id=A.user_id)
    WHEN A.user_type = 1 THEN (SELECT `name` FROM tb_teacher WHERE tb_teacher.id=A.user_id)
	END AS author
FROM tb_blog A

 

-- 生成10条简单的测试记录（存储过程）
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10 DO
        INSERT INTO tb_blog (title, cont, user_id, user_type,create_time)
        VALUES ('Title', 'Content', FLOOR(RAND()*(100-1+1))+1, 1,NOW());
        SET i = i + 1;
    END WHILE;
END

　　

-- 同时查询tb_student和tb_class_student表以显示学生所属班级的名称（共3个表）
-- tb_student 学生表  tb_class_student 班级学生关系表  tb_class 班级表
SELECT
	s.id, 
	s.name AS student_name, 
	c.class_name AS class_name
FROM tb_student AS s
LEFT JOIN tb_class_student AS cs ON s.id = cs.student_id
LEFT JOIN tb_class AS c ON cs.class_id = c.id;

　　

-- 查学生表时，同时查询学生-班级关系表，班级-年级关系表，显示出学生所在班级和所在年级名称（共5表）
-- 学生表，班级表，年级表，学生-班级关系表，班级-年级关系表
SELECT 
    s.id, 
    s.name AS student_name, 
		cg.class_id,		
    c.class_name AS class_name, 
		cg.grade_id,
    g.grade_name
FROM tb_student AS s
LEFT JOIN tb_class_student AS cs ON s.id = cs.student_id
LEFT JOIN tb_class AS c ON cs.class_id = c.id
LEFT JOIN tb_class_grade AS cg ON c.class_id = cg.class_id
LEFT JOIN tb_grade AS g ON cg.grade_id = g.id;