MySQL常用命令

目录

1.查找

2.添加

3.修改

4.删除

5.批量删除

6.where子语句&运算符

7.between..and..(闭区间)

8.in(包含)

9.is null

10.and:并且 or:或者

11.模糊查找

12.分页查询(限制查询)

13.排序

14.分组函数和聚合函数

(1)聚合函数

(2)分组函数

15.数据库连表查询

16.多表查询

17.连表查询

---

1.查找

select * from student

select name,sex from student

2.添加

insert into student(name,sex,age) values("小周","男",21)

insert into student(id,name,sex,age) values("8","小张","男",22)

3.修改

update student set name ="张三",sex="女",age=29 where id = 7

4.删除

delete from student where id=8

5.批量删除

delete from student where id in(1,2,3)

6.where子语句&运算符

 =:等于 :大于 =:大于等于 !=或者<>:不等于

select * from student where name="张三"

select * from student where age

select * from student where age>19

select * from student where age

select * from student where age>=20

select * from student where age<>20

select * from student where age!=20

-- 关键字

7.between..and..(闭区间)

select * from student where age between 21 and 30

8.in(包含)

select * from student where id in(1,3,5)

select * from student where age in (20,21)

9.is null

select * from student where age is null

-- 逻辑运算符

10.and:并且 or:或者

select * from student where age < 15 or age >28

select * from student where age 15 and sex = "女"

11.模糊查找

-- like 模糊查找,需要配合占位符一起使用

-- _:代表一位字符; %:代表任意字符

select * from shop where name like "%平板%"

12.分页查询(限制查询)

-- limit a,b

-- limit b offset a

-- a:开始行数-1（开始行数=a+1）

-- b:查询的行数

-- 有where有limit, where在前limit在后

-- limit总放在后面

select * from student limit 4,5

select * from student where age > 20 limit 0,3

13.排序

-- order by 列名[desc降序， asc升序]

-- 不写默认升序(asc)

-- order by 子语句在where之后，在limit之前

select * from student order by age desc

select * from student where age >20 order by age desc

14.分组函数和聚合函数

-- sum:求和

-- avg：平均值

-- max：最大值

-- min：最小值

-- count：数目

(1)聚合函数

select id from student

select sum(age) from student

select avg(age) from student

select max(age) from student

select count(*) from student

(2)分组函数

-- group by : 分组

-- 分完组之后筛选用having

select classid,sum(age) from student group by classid having classid=1

15.数据库连表查询

 

两张表里有相同的字段 表名.字段名

-- 起别名 as+别名 as可以省略

16.多表查询

select * from student,class where class.id = student.classid and class.id=1

select * from student as 学生表,class as 班级表 where 班级表.id = 学生表.classid

select student.*,class.name from student,class where class.id = student.classid

17.连表查询

-- 内连接 join...on(join相当于逗号)

-- 外连接

-- 左外连接 left join...on

-- 右外连接 right join...on

select * from student join class on class.id = student.classid

select * from student join class on class.id = student.classid where classid=2

select * from student left join class on class.id = student.classid

select * from student right join class on class.id = student.classid

（1）select 学生表.name,课程表.name,score from 学生表,课程表,选课表 where 选课表.sid = 学生表.id and 选课表.cid = 课程表.id

（2）select * from 学生表 join 选课表 on 选课表.sid = 学生表.id join 课程表 on 选课表.cid = 课程表.id

（3）select * from 学生表 join 课程表 join 选课表 on 选课表.sid = 学生表.id and 选课表.cid = 课程表.id