mysq 基本语法（三）

从本节开始对单表的数据进行操作
本节所需数据库数据
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (1,‘刘德华’,18,‘男’,85,90,60,1);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (2,‘王思思’,17,‘女’,90,85,84,1);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (3,‘吴彦祖’,19,‘男’,74,79,90,1);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (4,‘彭于晏’,20,‘男’,91,93,97,2);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (5,‘胡歌’,18,‘男’,82,85,84,2);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (6,‘小龙女’,21,‘女’,80,60,94,1);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (7,‘小龙女’,NULL,‘女’,80,60,94,1);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (8,‘彭华’,16,‘女’,88,66,87,2);
INSERT INTO student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) VALUES (9,‘彭辉’,23,‘男’,81,63,90,2);

增加

insert into
student(id,NAME,age,sex,yuwen,shuxue,yingyu,dep_id) 
values
(1,'刘德华',18,'男',85,90,60,1);

删除

delete from student where id = 1;

-- 如果不加条件(where)则回删除表中所有的数据
delete from student;

-- 如果需要把表中所有的记录删除不推荐使用上面的sql,推荐使用如下的sql
truncate table student; -- 删除一张表，并且会创建一张一摸一样的表

修改

-- 修改 小龙女的年龄为 18
update student set age = 18 where id = 6; 

查询

全部查询

select * from student;

具体条件查询

select * from student where age = 18;

区间查询

-- 方式一
select * from student where age >= 18 and age <= 19;

-- 方式二
select * from student where age between 18 and 19;

包含查询

-- 方式一
select * from student where age = 18 or age = 19 or age = 20;

-- 方式二
select * from student where age in(18,19,20);

查询为 null 的记录

select * from student where age is null;

查询不是null的记录

select * from student where age is not null;

查询去掉重复之后的记录

select distinct age from student;

模糊查询

-- 查询名字为两个字的记录
select * from student where name like '__';

-- 查询名字中带有 华 的记录
select * from student where name like '%华%';

排序查询

-- 根据年龄从小到大排序，如果年龄相同则按照 语文成绩升序排序
select * from student order by age asc, yuwen desc;

-- 根据年龄从大到小排序,语文成绩升序排序
select * from student order by age desc, yuwen asc;

分页查询

-- 查询 当前页记录为 2 条数据，从0开始
select * from student limit 0,2;

聚合函数

-- 查询当前表中总共有几条记录
select count(id) from student;

-- 查询年龄的最大值
select max(age) from student;

-- 查询年龄的最小值;
select min(age) from student;

-- 查询年龄的平均值
select avg(age) from student;

-- 查询年龄的和
select sum(age) from student;

分组查询

select 
sex,
avg(age),
count(ifnull(id, 0)) 
from 
student 
where 
age < 20 
group by 
sex 
having 
count(ifnull(id, 0)) > 2;