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个

内连接的另一种写法、外连接的写法都不多介绍了，就写这么多吧~
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

友情链接更多精彩内容