Mysql 基础用法

#创建表

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