MySQL数据库基本语句

一、基础语句

1.查询数据

select <列名> from <表名> [where <查询条件>]

例：select * from student

2.插入数据

insert [into] <表名> [列名] values <值列表>

例：insert [into] student (name,sex,age,major) values("张三","male",19,"计算机")

3.更新语句

update <表名> set <列名=更新值> [where <更新条件>]

例：update student set age=20 where name="李四"

4.删除语句

delete from <表名> [where <删除条件>]

例：delete from student

二、高级查询

1.排序：order by

asc 表示升序排序，desc 表示降序排序

例：select * from student order by age asc,id desc

2.重命名：as

查询过程中，临时重命名，as可以省略

例如：select id as 编号,name as 姓名,sex 性别 from student

3.查询指定行数的数据：limit

只有一个数字表示行数，若有两个数字，第一个数表示从第几行开始，表示行数的索引，索引从0开始，第二个数表示查询几行数据

例：select * from student limit 3

       select * from student limit 1,3

4.模糊查询：like、between and、in

_表示一个字符，%表示零到多个

例：select * from student where name like "王_"

       select * from student where name like "%王%"

between and是闭区间

例：select * from student where age between 18 and 20

in是范围查询

例：select * from student where age in(16,20,21,22,25,26,28,30)

5.聚合函数

count统计表中有多少条数据

例：select count(*) from student

sum统计表中所有人的年龄和

例：select sum(age) from student

avg统计表中所有人的平均年龄

例：select avg(age) from student

max统计表中所有人的最大年龄

例：select max(age) from student

min统计表中所有人的最小年龄

例：select min(age) from student

6.分组查询：group by

having加分组的条件

例：select age from student group by age having count(age)>=2

7.多表联合查询

内联结：inner join

例：select * from student inner join teacher on student.id=teacher.s_id

左联结：left join

例：select * from student right join teacher on student.id=teacher.s_id

交叉联结：inner join

例：select * from student inner join teacher