MySQL的增删改查详细命令教程

1、select基本查询语句

select * from students;

select name, gender from students;

2、where查询

select name, height from students where height=180;

3、where多条件查询

select name, gender from students where id=1 or id=3;

select * from students where gender='女' and cls_id=3;

select * from students where gender='女' and cls_id=2;

select name as '姓名', height as '身高' from students where height=180;

4、like模糊查询

【 - 】：一个字符

select * from students where name like '小-';

【 % 】：任意长度的字符串

select * from students where name like '小%';

【 [] 】：括号中所指定范围内的一个字符

select * from students where name like '[1-25]';

【 [^] 】：不在括号中所指定范围内的一个字符

select * from students where name like '[^王]';

5、in关键字查询

select * from students where id in (1,5,6,10);

select * from students where id not in (1,5);

select * from students where id in (8,9,10);

6、between关键字

select * from students where id between 8 and 10;

7、distinct关键字查询【去重】

select distinct gender from students;

select distinct cls_id from students;

8、order by

select * from students order by height;

# asc 升序排序
select * from students order by height asc;

# desc 降序排序
select * from students order by height desc;

9、limit关键字查询

# 只看前2条学生信息
select * from students limit 2;

# 分页效果，每页6个数据
select * from students limit 0,6;
select * from students limit 6,6;
select * from students limit 12,6;

10、连接查询，跨表

【内连接】语法格式：select 字段 from 表1 inner join 表2 on 连接条件 where 条件;

# 显式内连接查询
select s.name, c.name from students s inner join classes c on s.cls_id=c.id;

select s.name, c.name from students s inner join classes c on s.cls_id=c.id and c.id=1;

select t.name, c.name from classes c inner join teachers t on c.teacher_id=t.id;

select t.name, c.name from classes c inner join teachers t on c.teacher_id=t.id and c.id=1;

# 隐式内连接查询
# 语法格式：select 字段 from 表1,表2 where 表1.条件=表2.条件

select s.name as '学生姓名', c.name as '班级名称' from students s, classes c where s.cls_id=c.id;

【外连接】

# 左外连接查询
# 语法格式：select 字段 from 表1 left [outer] join 表2 on 连接条件 where 条件

select c.name, t.name from classes c left join teachers t on c.teacher_id=t.id;

# 右外连接查询
# 语法格式：select 字段 from 表1 right [outer] join 表2 on 连接条件 where 条件

select c.name, t.name from classes c right join teachers t on c.teacher_id=t.id;

11、聚合函数

# avg()平均值
select avg(height) as '平均身高' from students;

# max() 最大值
select max(height) as '最高身高' from students;

# min()最小值
select min(height) as '最低身高' from students;

# count()计数
select count(id) as '学生数量' from students;

# sum()求和
select sum(age) as '总年龄' from students;

12、子查询

# 子查询，是在一个查询的内部包括另一个查询的查询方式
select * from students where cls_id=(select cls_id from students where name='刘德华');

# any / some子查询
select * from students where cls_id = any(select id from classes where teacher_id = (select id from teachers where name='赵老师'));

13、exists查询

# 查看存在李老师的班级表
select * from classes where exists (select * from teachers where name='李老师');