5.mysql的基本查询

mysql的基本查询

数据库的基本操作有四种,增删查改,增加数据,删除数据,查找数据,修改数据

insert插入语句

Create

创建表

create table t20(
	id int unsigned primary key auto_increment comment '主键',
    sn int unsigned not null unqiue comment '学号',
    name varchar(20) not null,
    qq varchar(12)
)engine=InnoDB default chsrset=utf8;

插入语句

单行插入

insert t20(sn,name,qq) values(123,'猪八戒','1234567');

多行行插入

insert t20(sn,name,qq) values(123,'猪八戒','1234567'),
(125,'hello','456789');

insert into t20(id,name,qq) values(5,'hsjl','585785');

当出现主键和唯一键冲突

insert into t10 values(1,123,'猪悟能','55555') on duplicate key name='猪悟能',qq='555555';
//该语句可以在出现主键或者唯一键冲突时进行更新

替换replace

主键或者唯一教案没有冲突，则直接插入
主键或者唯一键由冲突，则删除后再插入
replace  into t20  values(3.125,'李四','55618415');

select查询语句

查询语句

6.2.1select 查询

如下所示，我们建立一张学生成绩表，然后后面的例子以下面这张表为例子进行说明

-- 创建数据库
drop database if exists student;
create database if not exists student default character set utf8 collate utf8_general_ci;

-- 使用数据库
use student;

-- 创建数据库表
drop table if exists exam_result;
create table exam_result (
	id int not null primary key auto_increment comment '编号',
	name varchar(20) not null default '' comment '姓名',
	chinese float(3,1) not null default 0.0 comment '语文成绩',
	english float(3,1) not null default 0.0 comment '英语成绩',
	math float(3,1) not null default 0.0 comment '数学成绩'
);

-- 插入数据
insert into exam_result (name, chinese, english, math) values('李涛', 89,78, 90);
insert into exam_result (name, chinese, english, math) values('唐僧', 67,98, 56);
insert into exam_result (name, chinese, english, math) values('孙悟空', 87,78, 77);
insert into exam_result (name, chinese, english, math) values('老妖婆', 88,98, 90);
insert into exam_result (name, chinese, english, math) values('红孩儿', 82,84, 67);
insert into exam_result (name, chinese, english, math) values('如来佛祖', 55,85, 45);
insert into exam_result (name, chinese, english, math) values('菩萨', 75,65, 30);

-- 查询全部数据
select id ,name , chinese, english, math from exam_result;

全列查询

//通常情况下不建议使用*进行全列查询
select * from exam_result;

查询指定列

select name,math ,id from exam_result

查询字段包含表达式

mysql会自动计算表达式的值

select name,math,chinese,math+chinese+english as 总分 from exam_result;

为查询结果指定别名

select name as '姓名',age as '年龄' from student

去重distinct

重复的数据去除

select distinct math from exam_result;

条件筛选

select name,math from exam_result where math>80;

where条件

比较运算符

逻辑运算符

运算符	说明
AND	多个条件必须都为TRUE,结果才为TRUE
OR	任意一个条件为 true 结果为true
NOT	条件为true,结果为false

数学成绩是58，59或者98 ，99的同学

select name,math from exam_result where math in (58,59,98,99);

模糊匹配

关键字：like,% _

利用该关键字可以进行字符串的匹配

如下面的例子，找出姓孙的同学

’孙%‘：代表第一个字符为’孙‘后面可以有无限多个字符，只要第一个字符匹配就行

select name from exam_result where name like '孙%';

找到’孙某‘同学

注意这里孙某意味着只有两个字符，第一个字符为孙，后面只能有一个字符，_代表一个字符，‘孙 _’就代表只有两个字符并且姓孙的同学

select name from exam_result where name like '孙_';

as关键字

利用as关键字可以对列名进行重命名，例如下面我们将 chinese+englist+math重命名为‘总分’，就是在中间加入as关键字

挑选总分在200以下的同学

select name ,chinese+english+math as '总分' from exam_result where chinese+english+math<200

where后续的子句本身就是在我们select期间要进行作为条件筛选的

语文成绩>80并且不姓孙

select name ,chinese from exam_result where chinese>80 and name not like '孙%';

孙某同学，否则要求总成绩>200并且语文成绩<数学成绩并且英文成绩>80

select name chinese math english from exam_result  where like '孙_'or (english+math+chinese>200 and chinese<math and english>80);

结果排序

关键字：order by 
asc 升序，在不指定的情况下默认为升序
desc 降序
注意:order by没有子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序

select name,math from exam_result order by math;
//默认是升序排列
select name,math from exam_result order by math desc;

挑选同学及qq号，按照qq号码进行排序

select name,qq from t20 where sn is not null order by qq ;

查询各科成绩，一次按照数学降序，英语升序，语文升序的方式显示

select name,chinese ,math, english from exam_result  order by math desc,english asc,chinese asc;

select name,chinses+math+englist as total from exam_result where order by total;

筛选分页结果

limit

select * from  limit 3;
//显示前3行

select * from limit 3,2;
//从3号下标开始显示2个

select * from exam_result limit 2 offset 3;
//和上面那个其实是一样的作用

select * from exam_result limit 3;

select * from exam_result limit 3,2;

Update更新数据

语法

update table_name set column=expr [,column=expr][where...][order by][limit...] 

更新数据

修改数据之前必须先找到数据然后才能进行修改

将孙权同学的数学成绩变为89分

update exam_result set math=80 where name='孙权';

将孙悟空的math改为60分,Chinese改为70分

update exam_result set math=60,chinses=70 where name='孙悟空';

总成绩倒数前三的三名同学数学成绩+30分

update exam_result set math=math+30 order by chinese+math+english limit 3;

将所有人的语文成绩+10分

update exam_result set chinese=chinses+10;

delete删除数据

delete from table_name [where...][order by...][limit...]

删除

删除之前要找到数据

delete from exam_result where name='孙悟空';

删除总分是倒数三名的学生

delete from exam_result where name in(select name order by english+math+chinses desc limit 3);

清空表数据

delete from exam_result ;

截断表

truncate [table] table_name;

注意:这个操作慎用

1.只能对整表操作,不能像delete一样对部分数据操作

2.实际上MySQL不会对数据操作,所以比delete更快,但是truncate在删除数据的时候,并不经过真正的事务,所以无法回滚

3.此操作会重置AUTO_INCREMENT项

插入查询结果

create table if not exists dup_table(
	id int,
    name varchar(20)
);

insert into dup_table values(100,'aaa'),(200,'bbb'),(300,'ccc'),(100,'aaa');

表格去重

create table if not exists no_dup_table like dup_table;

insert into no_dup_table select distinct * from dup_table;

rename table dup_taable to dup_table_back,no_dup_table to dup_table;

聚合函数

count():统计次数
sum():求总和
avg():平均值
max():最大值
min():最小值

聚合函数一般与下面的group by子句一起使用

select * count(*) from exam_result;

计算每个课程的平均分
select avg(chinese) ,avg(math),avg(english) from exam_result;

group by子句的使用

group by可以进行分组查询

凡是在select后面的列名称，如果后续我们要进行group by分组，那么凡是在select中出现的原表中的列名称也必须在group by中出现

group by 是一个分组函数，要筛查的数据列，都应该要考虑，分组的时候，如果当前的分组条件相同，接下来的分组依据是什么？

例如有一张员工表，里面有不同的部门，我们按照部分进行分组，查看每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

having

having 和group by搭配使用，对分组进行筛选，作用有点像where

例如：选择平均工资低于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;

by中出现

group by 是一个分组函数，要筛查的数据列，都应该要考虑，分组的时候，如果当前的分组条件相同，接下来的分组依据是什么？

例如有一张员工表，里面有不同的部门，我们按照部分进行分组，查看每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;

having

having 和group by搭配使用，对分组进行筛选，作用有点像where

例如：选择平均工资低于2000的部门

select deptno,avg(sal) from emp group by deptno having avg(sal)<2000;