MySQL数据库入门(六)数据增删改查及单表查询

数据的增删改查

数据库创建

create database day3;  # 创建练习数据库3

表创建

use day3;  # 切换当前数据库为day3
create table t1(
    id int unsigned primary key auto_increment,  # id字段整数、无符号、自增、主键
    name char(12) not null,  # name名字字段固定长度12字符型、不能为空、可重复 
    gender enum('男','女') default '男',  # gender性别字段单选“男”、“女”，默认“男”
    hobby set('python','mysql','java') not null,  # hobby爱好多选字段“python”、“mysql”、“java”
    post char(10) not null,  # post岗位字段固定长度10字符型，不能为空，可重复
    salary int unsigned not null  # salary月薪字段整数、无符号、不为空
);

增

• 一次增加单条数据：

  insert into t1 value
      (1,'张三','男','python,mysql','运行部',8800);
  

• 一次增加多条数据：

  insert into t1 values
      (2,'李四','男','python','技术部',8000),
      (3,'王五','男','java','技术部',8000);
  

• 指定字段添加数据：

  insert into t1
      (name,hobby,post,salary) values
      ('赵六','mysql','运行部',7800),
      ('钱大','java,mysql','技术部',9000),
      ('钱二','java,mysql','技术部',8900),
      ('钱三','java,mysql','技术部',8800);
  

• 从其它表中导入数据：

  create table t2(
      id int,
      name char(12)
      );  # 创建新表
  insert into t2(id,name)
      select id,username
      from t1;  # 把t1的数据导入t2
  

删

• 清空表但保留自增offset(偏移量)值：

  delete from t2;
  

• 快速清空表且不保留自增offset值：

  truncate table t2;
  

• 删除指定记录：

  delete from t1
      where id=7;  # 筛选id=2的记录
  

  删除指定数据必须带条件，delete from 表 where 条件;

  建议先select查询数据，确认删除条件无误后再执行删除，切不可直接删除。

改

update t1
    set salary=8888
    where id=4;  # 修改id为4的员工月薪为7888
    
update t1
    set salary=8888,hobby='java,mysql'
    where id=3;  # 修改id为4的员工爱好为java和mysql，月薪为8888

查

简单查询

select * from 表名;  # 查全部数据

select id,name,gender
    from t1;  # 查指定字段

字段重命名

方式一：

select id 序号,name 姓名
    from t1;  # 字段空格新名字即表示对该字段重命名

方式二：

select id as 序号,name as 姓名
    from t1;  # 字段空格新名字即表示对该字段重命名

查询去重

select distinct gender
    from t1;  # distinct关键字表示查询结果去重

函数

select concat('ab','bc','cd');  # 字符串拼接函数，显示结果是abbccd
select concat(name,':',hobby)
    from t1;  # 拼接显示多个字段的内容
select concat_ws('|','张三','男','18岁');  # 指定|作为分隔符拼接多个字段

四则运算

select name 姓名,salary*12 年薪
    from t1;  # 对salary月薪乘以12计算年薪

条件判断

select(
    case
    when name = '张三' then
        concat(name,'-副总监')
    when name = '赵六' then
        concat(name,'-总监')
    else
        concat(name, '-程序员')
    end
    ) as new_name
    from t1;

case语句块内可以对内容进行判断处理。case是条件判断语句块开始，end是条件判断语句块结束；when 后面跟判断条件，then后面接处理内容；as是给字段重命名

查表练习

1 查出所有员工的名字，薪资,格式为
<名字:name> <薪资:3000>

select
    concat('<名字:',name,'>') 名字,
    concat('<薪资:',salary,'>') 薪资
    from t1;

2 查出所有的岗位(去掉重复)

select distinct post
    from t1;

3 查出所有员工名字，以及他们的年薪,年薪的字段名为“年薪”

select
    name 名字,
    salary*12 年薪
    from t1;

where条件

where可以使用的条件：

• 比较运算符

  • <、>、=、<=、>=、!=
  • 查询薪资>8800的员工姓名

    select name
        from t1
        where salary>8800;  # 查询薪水大于8800的
    

• 范围

  • between and，范围包头包尾

    select * 
        from t1
        where salary between 8900 and 10000;  # 查询薪水在8900和1万之间的(含)
    

  • in

    select *
        from t1
        where salary in (8000,9000);  # 查询薪水正好是8千和9千的
    

• 模糊匹配

  • like：%表示任意长度的任意内容，_表示一个字符长度的任意内容，%和_只有在like字句内才是通配符。

    select * 
        from t1
        where name like '钱%';  # 查询名字是钱开头的
    

    select * 
        from t1
        where hobby like '%py%';  # 查询爱好包含py的
    

    select * 
        from t1
        where salary like '%8';  # 查询薪资以8结尾的
    

  • regexp
    注意：在sql中需要写双\转移符才生效，如想用\d匹配数字时必须写成\d才有效。

    select *
        from t1
        where salary regexp '^9';  # 查询薪水是以数字9开头的
    

    select *
        from t1
        where salary regexp '0$';  # 查询薪水是以数字0结尾的
    

    select *
        from t1
        where salary regexp '^88\\d\\d';  # 查询薪水是88开头的4位数的
    

• 逻辑运算，not优先级最高，or优先级最低。

  • not

    select *
        from t1
        where salary not in (8000,9000);  # 查询薪水不是8000和9000的
    

  • and

    select *
        from t1
        where salary not in (8000,9000) and post='技术部';  # 查询薪水不是8千和9千且部门是技术部的
    

  • or

    select *
        from t1
        where salary=8888 or post='运行部';  # 查询薪资是8888或部门是运行部的
    

group by分组

分组是查询表中指定字段不重复的值。例如：

select post
    from t1
    group by post;  # 查看t1表post字段有几种值

输出：
运行部
技术部

注意，下面的语句会报错：

select *
    from t1
    group by post;  # 查看t1表post字段有几种值

因为select的字段包含了group by里没有的字段。使用group by时select的字段只能是group by中有的字段。

再如，对hobby和post复合分组，此时group by包含hobby和post两个字段，那么select也可以使用hobby和post这两个字段：

select hobby,post
    from t1
    group by hobby,post;  # 同时对hobby和post两个字段进行分组

聚合函数

聚合是搭配分组使用的，常见聚合函数及使用方式如下：

• count，求个数

  select post,count(id)
      from t1
      group by post;  # 按post字段统计不同post分别有多少人
  

• max，求最大值

  select post,max(salary)
      from t1
      group by post;  # 显示所有部门的最高工资
  

• avg，求平均值

  select post,avg(salary)
      from t1
      group by post;  # 显示所有部门的平均工资
  

• min，求最小值

  select post,min(salary)
      from t1
      group by post;  # 显示所有部门的最低工资
  

• sum，求和

  select post,sum(salary)
      from t1
      group by post;  # 显示各部门的工资和
  

• concat，聚合显示

  select post,group_concat(name)
      from t1
      group by post;  # 显示不同部门的所有人名
  

注意：使用分组聚合统计时，group by根据指定字段分组，可以对该分组求个数、最大值、最小值、平均值、求和，但这个值只是和分组内容对应，和其它字段不对应。

having筛选

having中可以使用聚合函数，在where中不行。having适合筛选符合条件的一组数据，而不是某一行数据。
例如，找出平均工资大于8800的部门：

select post,avg(salary)
    from t1
    group by post
    having avg(salary)>8800;

order by排序

指定查询结果按指定字段排序，默认是升序asc，降序desc。

按工资从低到高排序，默认从小到大排序：

select *
    from t1
    order by salary;

按工资从高到低排序，desc指定从大到小排：

select *
    from t1
    order by salary desc;

limit限制

limit m,n：从m+1开始，取n个值。若只写1个数时该数被当成n且m默认为0。

limit n offsec m：从m项开始，取n个值。

找出工资最高的人：

select *
    from t1
    order by salary desc
    limit 1;

找出工资最高的前三人：

select *
    from t1
    order by salary desc
    limit 3;

找出工资第三高的那个人：
有两种写法：

select *
    from t1
    order by salary desc
    limit 2,1;  # 第一个数是表示从第三名开始取，第二个数表示次数

select *
    from t1
    order by salary desc
    limit 1 offset 2;  # 1表示取一次，2表示从第三名开始取。