MySQL基本查询

表的基本操作

• 一、插入

基本语法

insert [into] table_name
[(column [, column] ...)]
values (value_list) [, (value_list)] ...
value_list: value, [, value] ...

1. 单行数据+全列插入

注意事项：可以省去后边的所有字段名，但是value_list 数量必须和定义表的列的数量及顺序一致

//创建一张学生表
create table student(
	id int unsigned primary key auto_increment,
	sn int not null unique comment'学号',
	name varchar(20)not null,
	qq varchar(20)
	);
    
  //单行数据+全列插入（省去了字段名）
insert into student values(100,10000,'唐三藏',null);
insert into student values (101, 10001, '孙悟空', '11111');

//查看插入后结果
select*from student;  

2. 多行数据 + 指定列插入

注意事项：不可以省去字段名

// 多行数据 + 指定列插入
insert into student (sn,id,name)values
	(20001,102,'曹孟德'),
	(20002,103,'孙仲谋');

3. 插入否则更新

解释：由于主键或者唯一键对应的值已经存在而导致插入失败

• 主键冲突

INSERT INTO students (id, sn, name) VALUES (100, 12345, '啦啦啦');
ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY

• 唯一键冲突

INSERT INTO students (sn, name) VALUES (20001, 李雨珊');
ERROR 1062 (23000): Duplicate entry '20001' for key 'sn'

解决办法: 选择性的进行同步更新
语法规则–>

INSERT ... ON DUPLICATE KEY UPDATEcolumn = value [, column = value] ...

语句–>

//表中有冲突数据，但冲突数据的值和 update 的值相等
insert into student(id,sn,name)values (100,10010,'唐大师')
	on duplicate key update sn=10010,name='唐大师';
    
//表中没有冲突数据，数据被插入
insert into student(id,sn,name)values (100,10010,'唐大师')
	on duplicate key update id=105,sn=10030,name='张欣';
    
//表中有冲突数据，并且数据已经被更新
insert into student(id,sn,name)values (100,10010,'唐大师')
	on duplicate key update id=99,sn=10010,name='唐大师';

4. 替换

– 主键 或者 唯一键 没有冲突，则直接插入；
– 主键 或者 唯一键 如果冲突，则删除后再插入

replace into student (sn,name)values (20001, '曹阿瞒');

• 二、select

基本语法

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT

1.全列查询

//建表
create table exam_result (
	id int UNSIGNED primary key AUTO_INCREMENT,
	name varchar(20) not null comment '同学姓名',
	yuwen float default 0.0 comment '语文成绩',
	shuxue float default 0.0 comment '数学成绩',
	yingyu float DEFAULT 0.0 comment '英语成绩'
	);

//插入数据
INSERT INTO exam_result (name, yuwen, shuxue, yingyu) VALUES
	('唐三藏', 67, 98, 56),
	('孙悟空', 87, 78, 77),
	('猪悟能', 88, 98, 90),
	('曹孟德', 82, 84, 67),
	('刘玄德', 55, 85, 45),
	('孙权', 70, 73, 78),
	('宋公明', 75, 65, 30);

//全列查询	
select*from exam_result;	

2.指定列查询

注意：指定列的顺序不需要按定义表的顺序

//指定列查询
select id,name, yingyu from exam_result;

3. 查询字段为表达式

//表达式不包含字段
select id,name, 60 from exam_result;
//表达式包含一个字段
select id,name, yingyu+15 from exam_result;
//表达式包含多个字段
select id,name, yuwen+shuxue+yingyu from exam_result;

4.为查询结果指定别名

基本语法

SELECT column [AS] alias_name [...] FROM table_name;

语句

select id,name,yuwen+shuxue+yingyu 总分 from exam_result;
select id,name 名字,yuwen+shuxue+yingyu 总分 from exam_result;

5.结果去重

//原来的结果
select shuxue from exam_result;
//加上distinct,去重
select distinct shuxue from exam_result;

• 三、where条件

//查询英语成绩不及格的同学
select name,yingyu from exam_result where yingyu<60;
//语文成绩在 [80, 90] 分的同学
select name,yuwen from exam_result where yuwen>=80 and yuwen<=90;
select name,yuwen from exam_result where yuwen between 80 and 90;

//查询姓孙的同学          % 匹配任意多个（包括 0 个）任意字符
select name from exam_result where name like '孙%';
//查询孙某同学            _ 匹配严格的一个任意字符
select name from exam_result where name like '孙_';

//查询语文成绩好于英语成绩的同学
select name,yuwen,yingyu from exam_result where yuwen>yingyu;
//查询总分在 200 分以下的同学
select name, yuwen+shuxue+yingyu 总分 from exam_result where yuwen+shuxue+yingyu<200;

//查询语文成绩 > 80 并且不姓孙的同学
select name,yuwen from exam_result where yuwen>80 and name not like '孙%';

// 查询孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
select name,yuwen,shuxue,yingyu,yuwen+shuxue+yingyu 总分 from exam_result 
	where name like'孙_' or
	(yuwen+shuxue+yingyu >200 and yuwen<shuxue and yingyu>80);

//查询 qq 号已知的同学姓名
select name,qq from student where qq is not null;

• 四、结果排序

ASC 为升序（从小到大）
DESC 为降序（从大到小）
默认为 ASC**

基本语法

SELECT ... FROM table_name [WHERE ...] ORDER BY column [ASC|DESC], [...];

语句

//同学及数学成绩，按数学成绩升序显示
select name,shuxue from exam_result where order by shuxue asc;//写法错误，不能加where
select name,shuxue from exam_result  order by shuxue asc;//正确写法
//同学及 qq 号，按 qq 号排序显示
select name,qq from student order by qq;
select name,qq from student order by qq desc;

//查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
select name,shuxue,yingyu,yuwen from exam_result order by shuxue desc,yingyu,yuwen;
//查询同学及总分，由高到低
select name,yuwen+shuxue+yingyu 总分 from exam_result order by yuwen+shuxue+yingyu desc;//正确，使用了列别名
select name,yuwen+shuxue+yingyu  from exam_result order by yuwen+shuxue+yingyu desc;//正确，没有使用列别名

//查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示
select name,shuxue from exam_result 
	where name like '孙%' or name like '曹%'
	order by shuxue desc;

• 五、筛选分页结果

基本语法

-- 起始下标为 0
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 n 偏移处开始，筛选s条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s OFFSET n;

语句

–筛选分页结果

//从 0 开始，筛选 n 条结果
select id,name, yuwen,shuxue,yingyu from exam_result
	order by id limit 3;
//从 1开始，筛选3条结果	
select id,name, yuwen,shuxue,yingyu from exam_result
	order by id limit 1,3;

//从2偏移处开始，筛选1条结果	
select id,name, yuwen,shuxue,yingyu from exam_result 
	order by id limit 1 offset 2;
//从0偏移处开始，筛选2条结果	
select id,name, yuwen,shuxue,yingyu from exam_result 
	order by id limit 2 offset 0;

• 六、update

基本语法

UPDATE table_name SET column = expr [, column = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...]

语句

//将孙悟空同学的数学成绩变更为 80 分
//查看原数据
select name,shuxue from exam_result where name='孙悟空';
//更新
update exam_result set shuxue=80 where name='孙悟空';
//查看更新后数据
select name,shuxue from exam_result where name='孙悟空';

//将曹孟德同学的数学成绩变更为 66 分，语文成绩变更为 90 分
//查看原数据
select name,shuxue,yuwen from exam_result where name='曹孟德';
//更新
update exam_result set shuxue=66,yuwen=90 where name='曹孟德';
	


//将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
//查看原数据
select name,shuxue,yuwen+shuxue+yingyu 总分 from exam_result order by  总分 limit 3;
//更新
update exam_result set shuxue=shuxue+30 order by  yuwen+shuxue+yingyu limit 3;
//查看更新后数据
select name,shuxue,yuwen+shuxue+yingyu 总分 from exam_result order by  总分 limit 3;//错误，不能按照这种方式查看了，因为加了30分后这三个人的成绩可能不是倒数前三了
select name,shuxue,yuwen+shuxue+yingyu 总分 from exam_result where name in ('宋公明','刘玄德','孙权');//正确
//按总成绩排序后查询结果
select name,shuxue,yuwen+shuxue+yingyu 总分 from exam_result order by  总分 limit 3;



//将所有同学的语文成绩更新为原来的 2 倍
//查看所有同学原来的语文成绩
select name,yuwen from exam_result;
//更新
update exam_result set yuwen=yuwen*20;

• 七、delete

基本语法

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

语句

//删除孙悟空同学的考试成绩
//查看原数据
select*from exam_result where name='孙悟空';
//删除
delete from exam_result where name='孙悟空';

• 八、聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

//统计班级共有多少同学
select count(*)from student;
//统计班级收集的 qq 号有多少
select count(qq)from student;
//统计本次考试的数学成绩分数个数
select count(shuxue)from exam_result;
//统计数学成绩总分
select sum(shuxue) from exam_result;
//数学成绩 < 100 的总分
select sum(shuxue)from exam_result where shuxue<100;
//统计平均总分
select avg(yuwen + shuxue + yingyu) from exam_result;
//返回英语最高分
select max(yingyu)from exam_result;
//返回 > 70 分以上的数学最低分
select min(shuxue) from exam_result;

• 九、group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询

基本语法

select column1, column2, .. from table group by column;

语句

//显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal)from emp group by deptno;
//显示每个部门的每种岗位的平均工资和最低工资
select deptno,job,avg(sal),min(sal)from emp group by deptno,job;

//显示平均工资低于2000的部门和它的平均工资
select deptno,avg(sal) from emp  group by deptno;
//having和group by配合使用，对group by结果进行过滤
select avg(sal) from emp group by deptno having avg(sal)<2000;