我爱学编程Software Testing(软件测试)

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

2019-04-22  本文已影响64人  家和万事亨

一、单表

创建表 / 删除表

create table student(
    ID int,
    xingming varchar(10), 
    banji varchar(2),
    zhuanye varchar(10),
    xuehao varchar(8)
);

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 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 student set zhuanye = '英语' where xingming = '王五';
ID xingming banji zhuanye xuehao
3 王五 3 英语 null
4 赵六 3 电子工程 20181202
5 郑七 4 电子工程 20181201
6 胡八 3 null 20181203

查 —— 查询数据

  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 
'''展示三班、电子工程专业的所有学生的信息: '''
select * from student where banji = '3' and zhuanye = '电子工程';
ID xingming banji zhuanye xuehao
4 赵六 3 电子工程 20181202 
'''展示三班、电子工程专业的学生姓名: '''
select xingming as 姓名 where banji = '3' and zhuanye = '电子工程';
姓名
赵六
  1. 分组查询: select 字段1, 字段2 from 表名 group by 字段1; 
'''查询每个班级有几人(按班级分组, 且分别计算人数, 展示为两列): '''
select banji as 班级,count(*) as 人数 from student group by banji;
班级 人数
3 3
4 1
  1. 给分组增加过滤条件: ... group by 字段名 having 函数; 
'''对分组查询,增加过滤条件,使用 having + 条件, having 后接函数:'''
select banji as 班级 from student group by banji having count(*) >= 2;
班级
3
  1. 结果排序: 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
  1. 单表_综合查询:
    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

二、双表查询

上一篇下一篇

猜你喜欢

热点阅读