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

Mysql 基础用法

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

Mysql 基础用法

 

 

#子查询 查询班级id是2的学生表信息

SELECT * FROM score WHERE score.student_id IN (SELECT id FROM student WHERE class_id=2)

Mysql 基础用法

 

 

#GROUP BY 查看每个科目的人数大于2 ,科目id 升序排列

SELECT COUNT(id),score.course_id FROM score GROUP BY course_id ASC HAVING COUNT(id)>2

Mysql 基础用法

 

Mysql 基础用法

上一篇:windows环境下MySQL主从环境搭建 设置从库只读 以及搭建过程问题汇总


下一篇:CSS预处理器(less 和 sass)