目录
一、create(表的创建)
本序号内所有示例代码以student表为例
mysql> create table student(
-> id int unsigned primary key auto_increment,
-> sn int not null unique key comment '学号',
-> name varchar(20) not null,
-> qq varchar(20) unique key);
1.指定列插入数据
insert into 表名称(指定列1、指定列2……) values(数据1、数据2……);
insert into student(sn,name,qq) values(2250801201,'周一',312113);
2.全列插入
insert into 表名称 values(数据1、数据2……);
insert into student values(2,22808002,'周二',7893127);
3.多行数据+全列插入
insert into 表名称 values(第一行数据),(第二行数据),……,(第n行数据);
insert into student values(3,22508003,'周三',7070213),(4,22508004,'周四',69839843),(5,22508005,'周五',58596312);
4.插入否则更新
如果当前插入数据与已存在数据发生主键或唯一键冲突,则更新已存在数据;
如果没有冲突,则正常插入数据
insert into …… on duplicate key update 字段1=……,字段2=……,字段3=……;
insert into student values(6,22508006,'周六',8170470) on duplicate key update sn=22508006;
5.插入否则替换
如果当前插入数据与也存在数据发生主键或唯一键冲突,则删除所有冲突数据再插入数据(冲突数据可能是不同字段不同行数据,只有存在冲突数据,则冲突数据所在行的所有数据都删除);
如果没有冲突,则正常插入数据
replace into 表名称(字段1、字段2……) values(数据1、数据2……);
replace into student values(5,22508005,'周五',111222333);
二、select(表的查询)
本序号内所有示例代码以exam_result表为例
mysql> create table exam_result(
-> id int unsigned primary key auto_increment,
-> name varchar(20) not null comment '同学姓名',
-> chinese float default 0.0 comment '语文成绩',
-> math float default 0.0 comment '数学成绩',
-> english float default 0.0 comment '英语成绩');
1.全列查询
通常情况下不建议使用 * 进行全列查询,因为查询的列越多传输的数据量越大,且可能会影响索引的使用
select * from 表名称;
select * from exam_result;
2.指定列查询
指定列查询不需要按照表字段的顺序,任意顺序都可
select 指定列1,指定列2…… from 表名称;
select chinese,english,name from exam_result;
3.查询表达式
select后面可以接表达式,select会直接计算出表达式的结果
select 表达式 from 表名称;
select name,chinese+math+english from exam_result;
4.select为查询结果指定别名
select 指定列1 别名1,指定列2 别名2,…… from 表名称;
select name 姓名,chinese+math+english 总分 from exam_result;
5.查询结果去重
select distinct 指定列 from 表名称;
select distinct chinese from exam_result;
6.select查询+where筛选条件
运算符 | 说明 |
>,>=,<,<= | 大于,大于等于,小于,小于等于 |
= | 等于(不安全,例如NULL=NULL结果为NULL) |
<=> | 等于(安全,NULL<=>NULL结果为1) |
!=,<> | 不等于 |
between a0 and a1 | 范围匹配,[a0,a1],a0<=value<=a1则返回1 |
in | in(option,......)如果是option中的任意一个,返回1 |
is null | 是NULL |
is not null | 不是NULL |
like | 模糊匹配 %表示任意多个字符(包括0个) _表示任意一个字符 |
运算符 | 说明 |
and | 多个条件必须都为1,结果才为1 |
or | 任意一个条件为1,结果为1 |
not | 否定 |
查询语文成绩在[80,90]之间的同学及其语文成绩
select name,chinese from exam_result where chinese between 80 and 90;
查询数学成绩是58,59,98或99的同学及其数学成绩
select name,math from exam_result where math in(58,59,98,99);
查询姓张的同学
select name from exam_result where name like '张%';
查询张某同学
select name from exam_result where name like '张_';
查询总分在250分以下的同学
select * from exam_result where chinese+math+english<250;
查询语文成绩大于80且不姓张的同学
select * from exam_result where chinese > 80 and name not like '张%';
查询张某同学,否则要求其总分大于200并且语文成绩<数学成绩,英语成绩大于80
select * from exam_result where name like '张_' or (chinese+math+english>250 and chinese<math and english>80);
null的查询: 以student表为例
查询qq号已知的同学姓名
select name from student where qq is not null;
7.查询结果排序
asc为升序,desc为降序。默认为asc升序
查询同学姓名及其数学成绩,并按其数学成绩升序排序
select name,math from exam_result order by math asc;
查询同学姓名及其QQ号,并按QQ号降序排序(NULL视为比任何值都小)
select name,qq from student order by qq desc;
查询同学姓名及其各门成绩,并依次按数学降序,英语升序,语文升序
select name,math,english,chinese from exam_result order by math desc,english asc,chinese asc;
查询同学总分,由高到低
select name,chinese+math+english total from exam_result order by total desc;
查询姓孙同学或姓曹同学的数学成绩,并按数学成绩降序排序
select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;
8.筛选分页结果
起始下标从0开始
limit n:从0开始筛选n条结果
limit s, n:从s开始筛选n条结果
limit n offset s:从s开始筛选n条结果
select * from student limit 5;
select * from student limit 3,3;
select * from student limit 3 offset 0;
三、update(表的修改)
语法:
update 表名 set 列名1=...[,set 列名2=...] [where...] [order by...] [limit...];
将张涛同学数学成绩改为100
update exam_result set math=100 where name='张涛';
update exam_result set math=60,chinese=70 where name='周末';
update exam_result set math=math+30 order by chinese+math+english asc limit 3;
update exam_result set chinese=chinese*2;
四、delete(表数据的删除)
删除王力同学的成绩
delete from exam_result where name='王力';
删除总分倒数第一同学的成绩
delete from exam_result order by chinese+math+english asc limit 1;
删除整张表数据
delete删除整张表不会重置auto_increment项;truncate删除整张表会重置auto_increment项
delete from 表名称;
truncate 表名称;
五、插入查询结果
insert into no_duplicate_table select distinct * from duplicate_table;
六、聚合函数
函数 | 说明 |
count | 返回查询数据的数量 |
sum | 返回查询数据总和(查询数据必须是数字) |
avg | 返回查询数据平均值(查询数据必须是数字) |
max | 返回查询数据最大值(查询数据必须是数字) |
min | 返回查询数据最小值(查询数据必须是数字) |
select count(*) from student;
select count(qq) from student;
select count(math) from exam_result;
select count(distinct math) from exam_result;
select sum(math) from exam_result;
select avg(chinese+math+english) 平均总分 from exam_result;
select max(english) from exam_result;
select min(math) from exam_result where math>70;
七、group by子句的使用
select deptno,avg(sal),max(sal) from emp gruop by deptno;
select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
select deptno,avg(sal) from emp gruop by deptno having avg(sal)<2000;