4 修改表，复制表的SQL语句 + 表查询关键字

目录

• 修改表的SQL语句补充

• 复制表

• 表查询关键字

  基本关键字
      select
      from 
      where
      group by
      having
      distinct
      order by
      limit
      regexp
  多表查询关键字
      inner join
      left join
      right join
      union

 

一、操作表的SQL语句补充

 1.修改表名称

    alter table t1 rename t2;
    rename table t1 to t123;  # 关键字修改
    rename table t2 to t20,
                t3 to t30,
                t4 to t40;

 2.添加表字段

    alter table t9 add pwd int;
    alter table t9 add hobby varchar(32) after age;
    alter table t9 add nid int first;

 3.删除表字段

  alter table t9 drop nid;

 4.修改字段名和字段类型

   alter table t9 modify age tinyint;  # modify只能修改字段类型
    alter table t9 change pwd password int;  # change字段名和类型

复制表(了解)

# 查询语句执行的结果也是一张表，可以看成虚拟表
​
# 复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table new_service select * from service;

 

 

# 只拷贝表结构(不包含键)
create table new1_service select * from service where 1=2;  #条件为假，查不到任何记录
​

 

 

# 拷贝结构包含各种key
create table t4 like employees;

 

 

二、表查询关键字

      单表查询准备

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  sex enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);
​
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

 

1.查询关键字

select
    控制查询表中的哪些字段对应的数据
from
    控制查询的表

2.查询关键字之where

 where其实就是对数据进行筛选
​
 1.查询id大于等于3小于等于6的数据

    select id,name from emp where id >= 3 and id <= 6;
    select *  from emp where id between 3 and 6;  

 2.查询薪资是20000或者18000或者17000的数据

    select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
    select * from emp where salary in (20000,18000,17000);  # 简写
​

知识储备

     模糊查询
        关键字       like
        关键符号
                %:匹配任意个数的任意字符
                _:匹配单个个数的任意字符

 3.查询员工姓名中包含o字母的员工姓名和薪资

   select name,salary from emp where name like '%o%';

 4.查询员工姓名为四个字符组成的员工姓名和薪资

    select name,salary from emp where name like '____';
    select name,salary from emp where char_length(name) = 4;

 5.查询id小于3或者大于6的数据

 select *  from emp where id not between 3 and 6;

 6.查询薪资不在20000，18000，17000范围的数据

    select * from emp where salary not in (20000,18000,17000);
​

 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is

    select name,post from emp where post_comment = NULL;  # 查询为空！
    select name,post from emp where post_comment is NULL;
    select name,post from emp where post_comment is not NULL;

 

8.查询每个人的年薪 12薪

select name,salary*12 from emp;

 

3.查询关键字之group by分组

    分组

    按照某个指定的条件将单个单个的数据分为一个个整体
        eg:
            咱班按照座位横向分组
             咱班按照年龄分组
             咱班按照省份分组
    应用场景
        eg:
            求每个部门的平均薪资
            求每个国家的人均GDP
            求男女平均薪资
        

如何对数据进行分组

    关键字  group by 条件
"""
分组之后不再以单个个体为研究对象 也无法直接再获取单个个体的数据
研究对象应该是分组的整体
    分组之后默认只能直接获取到分组的依据 其他字段数据无法直接获取

 

 

 

 

 

如果需要实现上述要求 还是修改sql_mode

  set global sql_mode='only_full_group_by';

1.获取每个部门的最大薪资

select post,max(salary) from emp group by post;

2.统计每个部门的人数

 select post,count(id) from emp group by post;

3.获取每个部门的员工姓名

  select post,group_concat(name,'|',salary) from emp group by post;
    # group_concat用于分组之后获取分组以外的字段数据并支持拼接
    # concat用于分组之前的拼接操作
        select id,concat(name,'|',salary) as '嘿嘿' from emp;
    # concat_ws当多个字段连接符相同的情况下推荐使用
        select id,concat_ws('|',name,sex,salary,age) from emp;

别名as

在查看结果的时候可以给字段起别名
    select post as '部门',max(salary) as '最高薪资' from emp group by post;
    as可以省略但是为了语义更加明确建议不要省略

 

 

 

 

 

 

 

 

4.聚合函数

max()
min()
sum()
count()
avg()
# 上述聚合函数都是在分组之后使用 用于操作整体数据

5.查询关键字之having过滤

  where与having都是用来筛选数据的

    但是where用于分组之前的筛选、having用于分组之后的筛选
    为了人为的区分开 我们将where用筛选来形容 
                  having用过滤来形容

  
 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

    select post,avg(salary) from emp where age > 30 group by post having avg(salary)>10000;

"""
小技巧：将一个复杂的查询题拆分成多个简单的小题
   
"""

6.查询关键字之distinct去重

去重的前提示是存在一模一样的数据
    如果存在主键肯定无法去重（主键是not null unique）
"""
# 对有重复的展示数据进行去重操作 一定要是重复的数据
select distinct id,age from emp;         #无法去重
select distinct post from emp;

 

 

 

7.查询关键字之order by排序

order by默认是升序  默认的关键字是asc
    select * from emp order by salary asc;
    也可以改为降序 desc
    select * from emp order by salary desc;
 
# order by排序支持多个字段组合(第一个不行 就往后继续排)
    select * from emp order by age,salary;
  select * from emp order by age asc,salary desc;

 

 

 

 8.limit  分页

1.只跟一个数字
    select * from emp limit 5;  # 从头开始展示几行数据
2.只跟两个数字
    select * from emp limit 5,5;  # 第一个是起始位置 第二个是几行数据

 

 

 

 

 

 

求薪资最高的员工所有数据

1.先按照薪资降序排序

2.在使用limit限制取一行
    select * from emp order by salary desc limit 1;

 

 

 

 

 

9.regexp 正则表达式

查找出名字以j开头以n或者y结尾的所有信息

select * from emp where name regexp '^j.*(n|y)$';

 

 

 

10.exists  是否存在

    EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
    而是返回一个真假值，True或False。
    当返回True时，外层查询语句将进行查询
    当返回值为False时，外层查询语句不进行查询。
    select * from emp
        where exists
        (select id from emp where id > 3);

    select * from emp
        where exists
        (select id from emp where id > 250);