mysql/表sql语句补充/关键字查询

操作表的SQL语句补充

• alter

  关键字

  rename/重命名表

  add/添加字段

  change/重命名字段

  drop/删除字段

1.修改表名
	alter table 表名 rename 新表名；

2.新增字段
	alter table add 字段名 字段类型(数字) 约束条件
3.新增指定字段排在第一位
	alter table add 字段名 字段类型(数字) 约束条件 first;
4.新增字段指定字段的位置
	alter table add  字段名 字段类型(数字) 约束条件 after 已经存在字段；

5.修改字段
	alter table 表名 change 旧字段 新字段 字段类型（数字） 约束条件；

6.删除字段
	alter table 表名 drop  字段名；

• alter实战例子

create table T1_alter(id int primary key auto_increment,name varchar(32));
#修改表名
alter table T1_alter rename test_alter;
#新增字段
alter table test_alter add age int;
#新增字段排在第一位
alter table test_alter add name first; 
#新增字段指定位置
alter table test_alter add sex varchar(32) default 'male' after name;
#修改字段
alter table test_alter modify name varchar(32);
#删除字段
alter table drop 字段名；

mysql关键字查询书写顺序

select => from => where => Group by => having =>order by => limit

select 与from

• SQL语句的关键字编写顺序与执行顺序是不一致的!!!
  eg: select name from emp;
  ​ 肯定是先支持from确定表 之后执行select确定字段
  编写SQL语句针对select和from可以先写个固定模板
  ​ select * from 表名 其他操作
  select后的字段可能是实际的 也可能是通过SQL动态产生的 所以可以先用*占位最后再修改

  ---

• 数据练习

• 查询练习以以下数据为主

  #创建测试库
  	create table emp(
        id int not null unique 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);
  

where

查询关键字之where筛选

查询条件越多，查询出来的记录就会越少。因为，设置的条件越多，查询语句的限制就更多，能够满足所有条件的记录就更少。为了使查询出来的记录正是自己想要的，可以在 WHERE 语句中将查询条件设置的更加具体。

查询条件	关键字
比较	比较运算符和逻辑运算符
确定范围	between and关键字查询
确定集合	in not in
空值	is null
多条件查询（多重运算）	and ,or, XOR
字符匹配	like,not like 通配符%，_

• 1.查询id 大于3小于等于6的数据

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

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

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

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

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

• 4.查询员工姓名是由四个字符组的员工姓名与其薪资

  select name,salary from emp where name like '____'; #一个下划线是代表一个字符
  select name,salary from emp where name like char_lenth(name) = 4;
  

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

  select * from emp where id <3 or id>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;  #查询岗位描述不为空的

group by

分组：按照一些指定的条件将单个单个的数据分为一个个整体

​ 分组之后我们研究的对象应该是以组为单位，不应该再直接获取单个数据项，如果获取了应该直接报错，

​ select 后面直接填写的字段只能是分组的一句（其它字段需要借助一些方法才可以获取）

set global sql_mode = 'strict_trans_tables,only_full_group_by';

group by配合分组常见使用的聚合函数

max/最大值

min/最小值

sum/总和

count/计数

avg/平均

• 获取每个部门的最高工资 #表中post字段是组

  以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)

  每个部门的最高工资 # 也可以取别名 as，as可以省略

  select post,max(salary) from emp group by post;
  select post as '部门'，max(salary) as '最高工资' from emp group by post;
  

• 每个部门的最低工资

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

• 每个部门的平均工资

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

• 每个部门的工资总和

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

• 每个部门的人数

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

• 查询分组之后的部门名称和每个部门下所有的学生姓名

• group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用

select post,group_concat(name) from emp group by post;
select post,group_concat(name,"_SB") from emp group by post;
select post,group_concat(name,": ",salary) from emp group by post;
select post,group_concat(salary) from emp group by post;

having

where 与having 的功能其实是一样的，都是用来筛选数据
只不过where 用于分组之前的筛选，而having 用于分组之后的筛选
为了认为的区分，所以叫where是筛选，having是过滤

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

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

distinct

去重，去重前提数据必须是一模一样

select distinct age from emp;

order by

排序，升序或者降序

order by关键字

asc 默认升序

desc默认降序

• 工资升序排列

select * from emp order by salary asc;

• 工资降序排列

select * from emp order  by salary desc;

• 年龄降序

select * from emp order by age desc;

• 先按照age降排序，在年轻相同的情况下再按照薪资升序排

select * from emp oreder by age desc,salary asc;

• 统计各部门年龄在10以上的员工平均工资，并且保留平均工资大于1000的部门，然后对工资进行排序

mysql> select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by salary;

limit

limit关键字

limit A ；A 这里代指数字

limit A，B； （A,B必须为整数）A表示起始位，B表示展示几条

• 展示3条数据

select * from emp limit 3;

• 从第4条展示，并且只展示5条数据

select * from emp limit 3,5;   

regexp正则

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

多表查询

子查询
​ 将一张表的查询结果括号括起来当做另外一条SQL语句的条件
​ eg:类似以日常生活中解决问题的方式
​ 第一步干什么
​ 第二步基于第一步的结果在做操作 ...
连表操作
​ 先将所有涉及到结果的表全部拼接到一起形成一张大表 然后从大表中查询数据

#创建表
create table dep1(
    id int primary key auto_increment,
    name varchar(20) 
);

create table emp1(
    id int primary key auto_increment,
    name varchar(20),
    gender enum('male','female') not null default 'male',
    age int,
    dep_id int
);

##插入测试数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;

insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

子查询

查询jason的部门名称

1.先获取jason的部门编号
select dep_id from emp1 where name = 'jason';  # 200
2.根据部门编号获取部门名称
select name from dep1 where id = 200;

#子查询
mysql> select name from dep1 where id = (select dep_id from emp1 where name = 'jason');

连表操作

select * from emp1,dep1; #笛卡尔积

我们不会用笛卡尔积来求数据，效率太低，连表有专门的语法

名称	方法
inner join	内连接
left join	左连接
right join	右连接
union	全连接

select * from emp1 left join dep1 on emp1.dep_id = dep1.id;

select * from emp1 right join dep1 on emp1.dep_id = dep1.id;

练习题

2.完成下列练习题

2.完成下列练习题
1. 查询岗位名以及岗位包含的所有员工名字
	select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
	select post,count(post) from emp group by post;
3. 查询公司内男员工和女员工的个数
	select sex,count(sex) from emp group by sex;
4. 查询岗位名以及各岗位的平均薪资
	select post,avg(salary) from emp group by post;
5. 查询岗位名以及各岗位的最高薪资
	select post,max(salary) from emp group by post;
6. 查询岗位名以及各岗位的最低薪资
	select post,min(salary) from emp group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
	select sex,avg(salary) from emp group by sex;
8. 统计各部门年龄在30岁以上的员工平均工资
	select post,avg(salary) from emp where age>30 group by post;
	
9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>10000 order by avg(salary);