SQL语句 | MySQL_增删改查_基本语句

一、单表

（一）、创建表 / 删除表

  • 创建表: create table 表名(列名1 类型(size), 列名2 类型(size),...); 
create table student(
   ID int,
   xingming varchar(10), 
   banji varchar(2),
   zhuanye varchar(10),
   xuehao varchar(8)
);
  • 删除表: drop table 表名; 
drop table student;

（二）、“增” —— 插入数据

  1. 对 指定列 插入数据: insert into 表名 (字段 类型,) values(值,);
  2. 对 所有列 插入数据insert into 表名 values(值,);
  3. 所有串类型值,都要用''号或""号括上.
insert into student(ID,xingming,banji,zhuanye,xuehao) values(1,'张三','2','英语','20180220');
insert into student(ID,xingming,banji,zhuanye,xuehao) values(2,'李四','2','英语','20180221');
insert into student(ID,xingming,banji) values(3,'王五','3',null,null); -- 未注明的项,都填null

insert into student values(4,'赵六','3','电子工程','20181202');
insert into student values(5,'郑七','4','电子工程','20181201');
insert into student values(6,'胡八','3',null,'20181203');
ID xingming banji zhuanye xuehao
1 张三 2 英语 20180220
2 李四 2 英语 20180221
3 王五 3 null null
4 赵六 3 电子工程 20181202
5 郑七 4 电子工程 20181201
6 胡八 3 null 20181203

（三）、“删” —— 删除数据

  • 删除符合条件的数据: delete from 表名 where 条件; 
delete from student where ID = 1;
delete from student where xingming = '李四';
ID xingming banji zhuanye xuehao
3 王五 3 null null
4 赵六 3 电子工程 20181202
5 郑七 4 电子工程 20181201
6 胡八 3 null 20181203

（四）、“改” —— 修改数据

  • 修改符合条件的数据: update 表名 set 字段名 = 新值 where 条件; 
- 将'王五'的专业名改为'英语': 
update student set zhuanye = '英语' where xingming = '王五';
ID xingming banji zhuanye xuehao
3 王五 3 英语 null
4 赵六 3 电子工程 20181202
5 郑七 4 电子工程 20181201
6 胡八 3 null 20181203

（五）、“查” —— 查询数据

  • 5.1 普通单表查询: select 内容 form 表名 where 条件; 
'''展示表内所有的数据: '''
select * from student;
ID xingming banji zhuanye xuehao
3 王五 3 英语 null
4 赵六 3 电子工程 20181202
5 郑七 4 电子工程 20181201
6 胡八 3 null 20181203 
'''展示3班所有学生的数据: '''
select * from student where banji = '3';
ID xingming banji zhuanye xuehao
3 王五 3 英语 null
4 赵六 3 电子工程 20181202
6 胡八 3 null 20181203 
'''展示3班、电子工程专业学生的所有信息: '''
select * from student where banji = '3' and zhuanye = '电子工程';
ID xingming banji zhuanye xuehao
4 赵六 3 电子工程 20181202 
'''展示3班、电子工程专业学生的姓名: '''
select xingming as 姓名 where banji = '3' and zhuanye = '电子工程';
姓名
赵六
  • 5.2 分组查询: select 字段1, 字段2 from 表名 group by 字段1; 
'''查询每个班级有几人(按班级分组, 且分别计算人数, 展示为两列): '''
select banji as 班级,count(*) as 人数 from student group by banji;
班级 人数
3 3
4 1
  • 5.3 给分组增加过滤条件: ... group by 字段名 having 函数; 
'''对分组查询,增加过滤条件,使用 having + 条件, having 后接函数:(将人数超过2人的班级，以班级分组显示)'''
select banji as 班级 from student group by banji having count(*) >= 2;
班级
3

点击查看：常用的函数：例如 “聚合函数” min()、max()、avg()、count() ...

常用的聚合函数 作用
min() 最小值
max() 最大值
avg() 平均值
count() 总和
  • 5.4 结果排序: order by 字段名 asc/desc;按照某字段, 正序 / 逆序排列. (默认正序)
步骤: 
    1. 创建一个表scores: 
    create table scores(
        ID int,
        xingming varchar(10),
        kemu varchar(10),
        score int
    );

    2. 插入对应的数据: 
    insert into scores values(1,'赵一','数学',98);
    insert into scores values(2,'钱二','语文',99);
    insert into scores values(3,'孙三','数学',99);
    insert into scores values(4,'李四','数学',98);
    insert into scores values(5,'周五','语文',99);
    insert into scores values(6,'吴六','数学',97);
ID xingming kemu score
1 赵一 数学 98
2 钱二 语文 99
3 孙三 数学 99
4 李四 数学 98
5 周五 语文 99
6 吴六 数学 97 
    3. 查询: 将学生的数学成绩,按照ID由大到小排序(逆序排列): 
    select * from scores where kemu = '数学' order by ID desc;
ID xingming kemu score
6 吴六 数学 97
4 李四 数学 98
3 孙三 数学 99
1 赵一 数学 98
  • 5.5 单表综合查询:

1. 建表 + 增加数据:

create table books(
    id int primary key, 
    shuhao varchar(20), 
    shuming varchar(20) not null, 
    zuozhe varchar(20), 
    yeshu int, 
    chubanshe varchar(20), 
    chubanriqi date, 
    zhuangtai varchar(20)
); 
insert into books values(1, 001, '高等数学', '张三', 430, '中华书局', 1992-08-01, '上架' ); 
insert into books values(2, 002, '线性代数', '张三', 380, '华南书局', 1993-08-01, '上架' ); 
insert into books values(3, 003, '实用英语', '李四', 600, '中华书局', 1992-08-01, '上架' ); 
insert into books values(4, 004, '商务英语', '李四', 500, '山东书局', 1992-08-01, '上架' ); 
insert into books values(5, 005, '材料科学', '王五', 800, '华南书局', 1992-08-01, '上架' ); 
insert into books values(6, 006, '微积分学', '王五', 880, '中华书局', 1992-08-01, '上架' ); 
insert into books values(7, 007, '化学工业', '王五', 680, '中华书局', 1992-08-01, '上架' );
id shuhao shuming zuozhe yeshu chubanshe chubanriqi zhuangtai
1 001 高等数学 张三 430 中华书局 1991-08-01 上架
2 002 线性代数 张三 380 华南书局 1992-08-01 上架
3 003 实用英语 李四 600 中华书局 1991-08-01 上架
4 004 商务英语 李四 500 山东书局 1993-08-01 上架
5 005 材料科学 王五 800 华南书局 1991-08-01 上架
6 006 微积分学 王五 880 中华书局 1992-08-01 上架
7 007 化学工业 王五 680 中华书局 1993-08-01 上架

2. 查询: 和英语相关的书籍有哪些?

select shuming as 书籍名称 from books where shuming like '%英语%';
书籍名称
实用英语
商务英语

3. 查询: [高等数学]和[实用英语]的书号是多少?

select shuming as 书籍名称,shuhao as 书号 
from books 
where shuming = '高等数学' or shuming = '实用英语';
书籍名称 书号
高等数学 001
实用英语 003

4. 查询: 哪些书的[页数]在300~499之间?

select shuming as 书籍名称,yeshu as 页数
from books
where yeshu between 300 and 499;
书籍名称 页数
高等数学 430
线性代数 380

5. 查询: 每个出版社发行图书的数量是多少?

select chubanshe as 出版社,count(*) as 发行的图书数量
from books
group by chubanshe;
出版社 发行的图书数量
中华书局 4
华南书局 2
山东书局 1

6. 查询: 哪个出版社至少发行过2本书?

select chubanshe as 出版社,count(*) as 发行图书数
from books
group by chubanshe
having count(*) >= 2;
出版社 发行图书数
中华书局 4
华南书局 2

7. 计算: 王五比张三多出了几本书?（嵌套查询）

select 
(select count(*) from books where zuozhe = '王五')-
(select count(*) from books where zuozhe = '张三');

3 - 2 = 1(本)

8. 查询: 哪本书的[页数]比线性代数多?（嵌套查询）

select shuming as 书名, yeshu as 页数
from books
where yeshu > (
  select sum(yeshu)
  from books
  where shuming = '线性代数'
  );
书名 页数
高等数学 430
实用英语 600
商务英语 500
材料科学 800
微积分学 880
化学工业 680

9. 查询: 张三出的两本书的平均页数是多少?

select avg(yeshu) as 平均页数
from books
where zuozhe = '张三';
平均页数
405

10. 查询: 和高等数学是相同初版日期的书籍信息?（嵌套查询）

select *
from books
where chubanriqi = (
  select chubanriqi
  from books
  where shuming = '高等数学'
  );
id shuhao shuming zuozhe yeshu chubanshe chubanriqi zhuangtai
1 001 高等数学 张三 430 中华书局 1991-08-01 上架
3 003 实用英语 李四 600 中华书局 1991-08-01 上架
5 005 材料科学 王五 800 华南书局 1991-08-01 上架

11. 查询: 高等数学的[页数]比线性代数多多少?（嵌套查询）

select yeshu - (
  select yeshu
  from books
  where shuming = '线性代数'
  )
from books
where shuming = '高等数学';

430 - 380 = 50(页)

12. 查询: 每个作者各出版了多少页的书籍?

select zuozhe as 作者,sum(yeshu) as 总页数
from books
group by zuozhe;
作者 总页数
张三 810
李四 1100
王五 2360

13. 查询: 哪个作者出版的所有书籍都少于500页?（嵌套查询）

select 作者 as 出版的所有书籍都少于500页的作者
from (
  select zuozhe as 作者,max(yeshu) as 最大页数
  from books
  group by zuozhe
  ) as T
where 最大页数 < 500;
出版的所有书籍都少于500页的作者
张三

T表如下:

作者 最大页数
张三 430
李四 600
王五 880

14. 查询: 哪个作者出版的图书最多?（多层嵌套查询）

select 作者 as 出版图书最多的作者
from (
  select zuozhe as 作者,count(*) as 出版图书数量
  from books
  group by zuozhe
  ) as U
where 出版图书数量 = (
  select max(出版图书数量)
  from (
    select zuozhe as 作者,count(*) as 出版图书数量
    from books
    group by zuozhe
    ) as U
  );
出版图书最多的作者
王五

U表:

作者 出版图书数量
张三 2
李四 2
王五 3

15. 查询: 页数最多的书籍是哪本书?（多层嵌套查询）

select 书名 as 页数最多的书籍
from (
  select shuming as 书名,yeshu as 页数
  from books
  ) as M
where 页数 = (
  select max(yeshu)
  from (
    select shuming as 书名,yeshu as 页数
    from books
    ) as M
  );
页数最多的书籍
微积分学

M表:

书名 页数
高等数学 430
线性代数 380
实用英语 600
商务英语 500
材料科学 800
微积分学 880
化学工业 680
  • 6.6 From子表查询

pro表：

产品名称 入库数量 入库时间
烧鸡 10 2010-10-10
烤鸭 12 2014-09-09
榴莲 01 2017-08-08
烧鸡 13 2017-07-07
榴莲 03 2017-05-08
榴莲 06 2017-08-09

问题：哪个产品入库数量最多？
*思路：算出每个产品的入库数量，找到数量最大的即可
1）查询每个产品的入库数量

       select 产品名称, sum(入库数量) as 总数
       from pro
       group by 产品名称
产品名称 总数
烧鸡 23
烤鸭 12
榴莲 10

把这个查询的结果命名为一个子表tmp：

      (select 产品名称, sum(入库数量) as 总数
      from pro
      group by 产品名称)  as  tmp

2）从tmp表中，查询最大的总数（23）

      select max(总数)
      from tmp

3）从tmp表中，查询哪个产品总数是23

      select *
      from tmp
      where 总数 = '23'

4）合并1

      select *
      from  tmp
      where 总数= ( 
                   select max(总数)
                   from tmp
                      )

5）合并2

       select *
       from  (
              select 产品名称, sum(入库数量) as 总数
              from pro
              group by 产品名称
              )  as  tmp
       where 总数= (
                    select max(总数)
                    from  (
                           select 产品名称, sum(入库数量) as 总数
                           from pro
                           group by 产品名称
                           )  as  tmp
                   )

二、双表查询

【建表约束】

  • 创建表class、xsry_tel2，对其中的各个列名进行键值的限制：主键（PK）、外键（FK）、非空、唯一...
create table class(
    ID int primary key,                 -- 主键（唯一标识一条记录），缩写为：PK
    xingming varchar(20) not null,      -- 非空（必填）
    cardid varchar(20) unique,          -- 唯一（不能重复）
    shengao int,
    jiguan varchar(10) not null unique  -- 非空且唯一
    );

create table xsry_tel2(
    TEL_ID int,
    TEL_XS_ID int not null,
    TEL_HAOMA varchar(20) ,
    constraint  fk_xs_id foreign key(TEL_XS_ID)  references xsry(XS_ID),   -- 外键约束
    constraint  pk_TEL_ID  PRIMARY KEY(TEL_ID),                            -- 主键约束
    constraint  unq_TEL_HAOMA unique(TEL_HAOMA)                            -- 唯一约束
    );

双表查询

1、建表

create table  xsry(
    XS_ID  INT PRIMARY KEY ,
    XS_XINGMING VARCHAR(200),
    XS_XINGBIE  VARCHAR(10)
    );
insert INTO xsry  values(1,'麦兜','男');
insert INTO xsry  values(2,'柯南','男');
insert INTO xsry  values(3,'美羊羊','女');

create table xsry_tel(
    TEL_ID  int  ,
    TEL_XS_ID  int not null,
    TEL_HAOMA  varchar(20) ,
    constraint  fk_xs_id foreign key(TEL_XS_ID)  references xsry(XS_ID),  -- 外键约束
    constraint  pk_TEL_ID  PRIMARY KEY(TEL_ID),                           -- 主键约束
    constraint  unq_TEL_HAOMA unique(TEL_HAOMA)                           -- 唯一约束
    );
insert into xsry_tel values(1,1,'222');
insert into xsry_tel values(2,1,'333');
insert into xsry_tel values(3,2,'444');

销售人员信息表xsry（主表）：

XS_ID XS_XINGMING XS_ XINGBIE
1 麦兜 男
2 柯南 男
3 美羊羊 女

销售人员电话表xsry_tel（从表）：

TEL_ID TEL_XS_ID TEL_HAOMA
1 1 222
2 1 333
3 2 444
  • 查询每个人的个人信息及电话号码：
select xsry.*,xsry_tel.TEL_HAOMA    -- xsry表所有列的信息，xsry_tel表的TEL_HAOMA列的信息
from xsry,xsry_tel   -- 从两张表查询时，两张表用“,”号连接
where xsry.XS_ID = xsry_tel.TEL_XS_ID   -- 条件一定是两张表关联的信息
XS_ID XS_XINGMING XS_XINGBIE TEL_HAOMA
1 麦兜 男 222
1 麦兜 男 333
2 柯南 男 444
  • 查询柯南的电话号码
SELECT XS_XINGMING,TEL_HAOMA 
FROM xsry,xsry_tel
WHERE xsry.XS_ID = xsry_tel.TEL_XS_ID
AND XS_XINGMING = '柯南'
  • 查询每个人有几部电话
SELECT XS_XINGMING,COUNT(*) 
FROM xsry,xsry_tel
WHERE xsry.XS_ID = xsry_tel.TEL_XS_ID   -- 如果两张表的字段名有重复的，则必须写明是取自哪张表
GROUP BY XS_XINGMING   -- 以姓名分组显示
  • 查询哪个人至少有两部电话
select XS_XINGMING, count(*)
from  xsry,xsry_tel
where XS_ID = TEL_XS_ID
group by XS_XINGMING   -- 以姓名分组显示
having count(*)>=2;   -- 电话号码数≥2个

内连接的另一种写法、外连接的写法都不多介绍了，就写这么多吧~
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,222评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,455评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,720评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,568评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,696评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,879评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,028评论 3赞 409
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,773评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,220评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,550评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,697评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,360评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,002评论 3赞 315
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,782评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,010评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,433评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,587评论 2赞 350