mysql

一 查询：

students学生表，grade 成绩表

1.查询全部数据

select * from students

2.查询表的别名

select s.name,s.age from students s

3.多表查询

select s.name,s.age g.score,g.kemu from students s join grade g  on s.id=g.id

4.内连接 (查询students表和grade表的交集)

select * from students inner join grade on students.id=grade.id

5.外连接（left join 和 right join ）

select * from students left join grade on students.id=grade.id  (查询students表的全部+grade表匹配的值)

select * from students right join grade on students.id=grade.id  (查询grade表的全部students表匹配的值)

6.删除数据

delete from students where id=11001

7.修改数据

update students set age=20 where id=11001

8.新增数据

INSERT INTO `mysql`.`students` (`id`, `name`, `age`) VALUES ('11004', '王二', '20');

9.排序

 

select * from grade order by score asc 升序

select * from grade order by score desc 降序

 

10 分组

select class,sum(amount) as m from origin GROUP BY class