mysql语句

CREATE TABLE class_table(
    class_id INT(4) PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(20) NOT NULL
);

CREATE TABLE student_table(
    student_id VARCHAR(10) PRIMARY KEY,
    student_name VARCHAR(20) NOT NULL,
    student_birthday DATE ,
    class_id INT(4),
    CONSTRAINT FOREIGN KEY(class_id) REFERENCES class_table(class_id) ON DELETE SET NULL
)

-- 添加班级
INSERT INTO class_table(class_name) VALUES('java1');
INSERT INTO student_table(student_id , student_name, student_birthday, class_id
) VALUES('2017090933','xiaobai','1999-09-09',2);

-- 删除数据
DELETE FROM student_table WHERE student_id = '2017090900';
DELETE FROM class_table WHERE class_id = 1;

-- 修改数据
UPDATE student_table
SET student_name = 'ls', student_birthday = '2000-01-01' WHERE student_id = '2017090910';
UPDATE student_table
SET student_name = 'ls', class_id=3 WHERE student_id = '2017090910';
-- 查找数据
SELECT student_name, student_birthday FROM student_table WHERE student_id = '2017090910';

-- 排序查询
SELECT student_name, student_birthday FROM student_table
-- WHERE student_id = '2017090910'
ORDER BY student_birthday ASC;

SELECT * FROM student_table
-- WHERE student_id = '2017090910'
ORDER BY student_birthday ASC;

-- 分组查询
-- 每个班级的人数
-- count max min avg sum group by后面的列其他数据没有意义
SELECT COUNT(*),class_id
FROM student_table
-- where class_id = 3;
GROUP BY class_id
ORDER BY COUNT(*) ASC;

-- 多表查询
-- 学生名字、学生编号、班级名字
-- 查询学生信息，条件是班级名字是java1

SELECT s.student_name,s.student_id,c.class_name
FROM student_table s, class_table c -- 表别名只在当句有效
WHERE s.class_id = c.class_id;

SELECT *
FROM student_table s, class_table c
WHERE c.class_name = 'java1' AND s.class_id = c.class_id -- 条件顺序影响效率，多表查询消耗大