#创建表
CREATE TABLE table_name (column_name int)
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
ENGINE 设置存储引擎，CHARSET 设置编码。
#删除表
DROP TABLE table_name ;
#插入
INSERT INTO class (id, name) VALUES (5,'一年5班')
#删除数据
DELETE FROM class WHERE id=6
#修改
UPDATE class SET name='一年五班',id=5 WHERE id=6
#查单表
SELECT * FROM class
SELECT name FROM class where id=1
#排序 降序desc ,升序 asc
SELECT * FROM score ORDER BY score.score DESC
SELECT * FROM score ORDER BY score.score ASC
#多表查询
#LEFT JOIN左联 ，左表数据全显示，右表没有匹配的置为空
#RIGHT JOIN右联，右表数据全显示，左表没有匹配的置为空
#INNER JOIN内联,左右表都有数据才显示
select st.id, st.name,c.name,sc.score from student st LEFT OUTER JOIN class c ON st.class_id=c.id LEFT OUTER JOIN score sc ON sc.student_id=st.id
select st.id, st.name,c.name,sc.score from student st INNER JOIN class c ON st.class_id=c.id INNER JOIN score sc ON sc.student_id=st.id
#子查询 查询班级id是2的学生表信息
SELECT * FROM score WHERE score.student_id IN (SELECT id FROM student WHERE class_id=2)
#GROUP BY 查看每个科目的人数大于2 ，科目id 升序排列
SELECT COUNT(id),score.course_id FROM score GROUP BY course_id ASC HAVING COUNT(id)>2
