SQL学习笔记(04)——数据更新、查询操作

一、数据更新操作

1.添加数据（insert插入语句）

插入的值和列要保持一一对应的关系

-- 方式一：使用多条insert插入语句
-- insert into 表名(列名1,列名2,列名3.....) value(值1,值2,值3...);
insert into students (id,name) values (0,'张飞');
insert into students (id,name) values (1,'赵云');

-- 方式二：通过一条insert插入多条数据
-- insert into 表名 value(值1,值2,值3...);
insert into heros value(0,'孙尚香','女',20),(1,'露娜','女',18);

-- 方式三：insert into 表名(列名1,列名2,列名3.....) value(值1,值2,值3...),(值1,值2,值3...);
insert into heros(id,name,sex,age) value(0,'孙尚香','女',20),(1,'露娜','女',18);

2.删除数据（delete删除语句）

delete和drop的区别：delete仅限于把表中数据给删除，表还在留着；drop是删除整个表

2-1.物理删除

-- 格式一：把表中整个数据删除
-- delete from 表名;
delete from students;

-- 格式二：根据条件删除数据
delete from 表名 where 条件;
and  条件是同时满足					delete from 表名 where 条件1 and 条件2 and 条件3....
or   多个条件只需要满足其中一个		delete from 表名 where 条件1 or  条件2 or  条件3....

2-2.逻辑删除

指通过设定一个字段来标识当前记录已经被删除 is_delete字段来标识，1表示删除，0表示未删除

update students set is_delete=1 where id=8; 

2-3.其他删除数据方式

-- truncate table表名（清除表里面所有的数据，但是表结构会保留，自增长字段的值会从1开始。)
truncate tablestudens;

-- drop table表名（删除数据表，包括数据和表结构)
drop table students;

3.修改数据

-- 格式一：
update 表名 set 列名=值;

-- 格式二：
update 表名 set 列名1=值1,列名2=值2...;

-- 格式三：附带条件的更新
update 表名 set 列名=值 where 条件;
update 员工表 set 工资=12000 where 姓名=小张";

 

二、数据查询操作

1.查询基本语法结构

• 查询部分字段的值

select 字段名1，字段名2 .... from 表名	(查询的为一部分的字段的信息)

-- 查询学生表中的姓名、性别、年龄的数据
select name,sex,age from students;

• 取别名（as 关键字）

-- 给表取别名
-- select别名.字段名1，别名.字段名2 ..... from 表名 as 别名
select s.name,s.sex,s.age from students as s;

--给字段取别名
-- select字段名1 as 别名1，字段名2 as 别名2 .... from表名;
select name as 姓名, sex as 性别,age as 年龄 from students;

• 去重（distinct 关键字）

select distinct 字段名1,字段名2 .... from 表名;

--根据学生的年龄去重
select distinct age from students;

2.条件查询

2-1.逻辑运算符

• and（同时满足条件），or（满足其中一个条件），not（不符合该条件）

-- 语法结构
select 列名 from 表名 where 条件1 逻辑运算符 条件2 逻辑运算符 条件3....;

-- 示例
-- 1.查询上海或北京的天气
select * from weather where city='上海' or city='北京';
-- 2.查询除了上海以外的天气
select * from weather where not city='上海';
select * from weather where city!='上海';

2-2.比较运算符

• 比较运算符：大于(>)、等于(=)、小于(<)、大于等于(>=)、小于等于(<=)、不等于(<>或者!=)

-- 1.查询年龄小于18岁的女生
select * from students where age < 18;
-- 2.查询成绩大于且等于85分以上的学生
select * from students where grade >= 85;
-- 3.查询所有性别为女生的姓名
select * from students where sex <> '男';
select * from students where sex != '男';

2-3.范围查询

• between …and…：针对查询结果满足某个范围内的数据在某个范围之内

select 列名 from 表名 where 列名 between 开始值 and 结束值;

-- 查询分数在85-90之间的学生
select * from students where grade between 85 and 90;
select * from students where grade >= 85 and grade <= 90;

• not between … and…：不在某个范围之内的数据

select 列名 from 表名 where 列名 not between 开始值 and 结束值;

-- 查询分数不在85-90之间的学生
select * from students where grade not between 85 and 90;

• in：查询非连续范围内的数据，只要满足其中一个就会有查询结果

-- 查询城市为深圳或者广州或者上海的天气
select * from weather where city in('深圳','广州','上海');
select * from weather where city = '深圳' or city = '广州' or city = '上海';

2-4.模糊查询

• 模糊查询：like 关键字
• %代表的是零个或多个字符
• _代表的是一个字符

-- 1.查询姓王的学生
select * from students where name like '王%';

-- 2.查询姓王且名字是一个的学生
select * from students where name like '王_';

-- 3.查询姓名中包含'雨'的学生
select * from students where name like '%雨%';

-- 4.查询名字为两个字的学生
select * from students where name like '__';

-- 5.查询编号以5结尾的学生
select * from students where name like '%5';

2-5.空值查询

null和''空字符不一样

-- 判断为空：select 列名 from 表名 where 列名 is null;
-- 查询学生科目为空的信息
select * from students where subject is null;

-- 判断不为空：select 列名 from 表名 where 列名 is not null;
-- 查询学生科目不为空的信息
select * from students where subject is not null;

排序（order by）

• 默认为升序排列（从小到大）
• asc为升序排列（从小到大）
• desc为降序排列（从大到小）

-- 格式：select 列名 from 表名 order by 列名1 asc,列名2 desc;

-- 例子∶查询成绩表中所有学生的记录，要求按照成绩升序排列，成绩相同时，按照学号进行降序排列
select * from 成绩表 order by 成绩 asc,学号 desc;

分组和聚合函数

聚合函数

• sum（列名）       对某个列进行求和
• avg （列名）       对某个列进行求平均值
• max（列名）       对某个列求最大值
• min （列名）       对某个列求最小值
• count（*）        统计元组的个数(看一个表有多少条记录)
• count（列名）          统计某个列有多少个值

除了count(*)外，其他函数再做操作时，均忽略空值(null)

-- 查询'1班'学生的分数总和
select sum(grade) from students where class='1班';

-- 查询'1班'学生的分数平均分
select avg(grade) as 平均分 from students where class='1班';

-- 查询'1班'学生的分数最高分
select max(grade) as 最高分 from students where class='1班';

-- 查询'1班'学生最小年龄
select min(age) as 最小年龄 from students where class='1班';

-- 统计name下有多少个数
select count(name) from students;
-- 统计元组的个数
select count(*) from students;

分组查询

• 针对查询出来的结果进行分组

格式：select 列名,聚合函数 from 表名 group by 列名;

-- 查询不同性别的人数
select sex,count(*) from students group by sex;
-- 查询各个班级的人数
select class,count(*) from students group by class;
-- 查询各个班级中不同性别的人数
select class,sex,count(*) from students group by class,sex;

• 分组后的数据筛选
• 将分组之后的数据当成是一个表数据，然后再通过having 条件来对当前的表数据进行筛选。
• select 列名 from 表名 group by 列名 having 条件(一般情况下都是聚合函数当做条件);

注意:1.having必须和group by固定搭配;
   2.having后面可以写聚合函数，但是where后面不能写。

-- 查询女生的总数
select count(*) from students where sex='女';
select sex,count(*) from students group by sex having sex='女';

-- 查询每个班级女生的总数
select class,sex,count(*) from students group by class,sex having sex='女';

-- 查询所有班级中不同性别的记录数大于1的信息
select class,sex,count(*) from students group by class,sex having count(*)>1;

分页查询

数据库中从0开始索引

select 列名 from 表名 limit [指定开始查询的行,]查询的总行数;

-- 例如：查询学生表中学号从第3行开始，查询7行
select sno from students limit 2,7;