day04 一对一和 sql语句补充，查询的关键字

day04 一对一和 sql语句补充，查询的关键字

昨日内容复习

MySQL基本数据类型之日期相关类型

date		年月日
datetime     年月日时分秒
time		时分秒
year		年

MySQL基本数据类型之枚举与集合类型

# 枚举
	多选一(爱好)
    enum('eat','sleep','play')
 
# 集合
	多选多(多选一)  爱好
    set('eat','football')
    
    	'''插入数据的时候如果有多个逗号隔开即可'''
    	

约束条件

1.unsigned   #忽略正负号
2.zerofill   #0填充
3.not null   #非空
4.default    #默认值
5.unique     #唯一，不能重复  分为单列唯一，多列唯一
6.primary key #主键 非空且唯一
7.auto_increment  #自增  通常配合主键一起使用

外键

判断表与表之间关系  换位思考  一个可以一个不可以   多对一    两个都可以  多对多
格式	foreign key(本表外键字段) references 另外一张表名(主键字段)
on update cascade #级联更新
on delete cascade #级联删除

1.在创建表的时候先创建被关联表  没有外键的表
2.在录入数据的时候也是先录入被关联表   没有外键的表

今日内容概要

• 表关系之一对一和没有关系

• 修改表的SQL语句补充

• 表查询关键字

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

  今日内容详细

表关系之一对一和没有关系

#两个都是不可以   我不可以对应多个你   你不可以对应多个我   则为一对一 或者没关系
例如：用户表  和  用户详情表
外键建在哪：理论上建在任何一方都可以但是推荐建在查询频率较高的表中

操作表的sql语句补充

1.修改表名称  rename 
	alter table a1 rename a2;  
	rename table a2 to a3;   #可以多个表修改 后面加逗号 a4 to a5 这种
	
2.添加表字段
	alter table a3 add pwd char(16);   
	alter table a3 add age int after id;  #添加字段age在id的后面
	alter table a3 add age int first;   #添加字段age 在第一个位置
3.删除表字段
	alter table a3 drop age;
4.修改字段名和字段类型
	alter table a3 modify age tinyint ;  #modify 只能修改字段类型
	alter table a3 change age pwd varchar(16); #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);

查询关键字之where

# 1.查询id大于等于3小于等于6的数据
	select * from emp where id>=3 and id<=6;   #笨方法
	select * from emp where id between 3 and 6; #简单方法   between 指定范围
	
# 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 is NULL;  #

查询关键字之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.获取每个部门的员工姓名
	#group_concat 分组完某个字段的数据 as 别名  concat用于分组之前的拼接操作 把两个字段数据合到一起 可以添加分隔符  自己理解
	select post,group_concat(name,'|',salary) from emp group by post;
		
    # group_concat用于分组之后获取分组以外的字段数据并支持拼接    官方话术
    # concat用于分组之前的拼接操作 官方话术
    	select id,concat(name,'|',salary) as '姓名和薪资' from emp;
                +----+--------------------+
                | id | 姓名和薪资         |
                +----+--------------------+
                |  1 | zhang|7300.33      |
                |  2 | tom|1000000.31     |
    # concat_ws当查询多个字段连接符时候使用  多个字段连接符相同的情况下推荐使用
    	select id,concat_ws('|',name,sex,salary,age) from emp;
    
"""
在查看结果的时候可以给字段起别名
	select post as '部门',max(salary) as '最高薪资' from emp group by post;
	as可以省略但是为了语义更加明确建议不要省略
"""

获取每个部门的员工姓名 和薪资

聚合函数

max()         #最大值
min()		 #最小值
sum()		#求和
count()		#计数总共多少个
avg()		#平均值
# 上述聚合函数都是在分组之后使用 用于操作整体数据

练习题

1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资


select post,group_concat(name) from emp group by post;
select post,count(id) from emp group by post;
select sex,count(id) from employee group by sex;
select post,avg(salary) from emp group by post;
select post,max(salary) from employee group by post;
select post,min(salary) from employee group by post;
select sex,avg(salary) from employee group by sex;

查询关键字之having过滤

where与having都是用来筛选数据的
	但是where用于分组之前的筛选、having用于分组之后的筛选
    为了人为的区分开 我们将where用筛选来形容 having用过滤来形容
# 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
	#一步一步来做
	#先统计各部门年龄在30岁以上的员工平均工资
	select post,avg(salary) from emp where age>30 group by post;
	#并且保留平均工资大于10000的部门
	select post avg(salary) from emp  where age>30 group by post having avg(salary)>10000;

查询关键字之distinct去重

"""
去重的前提示是存在一模一样的数据
	如果存在主键肯定无法去重,因为主键非空切唯一
	
"""
# 对有重复的展示数据进行去重操作 一定要是重复的数据
select distinct id,age from emp;   #这个去不了  因为id不重复
select distinct post from emp;		#正常去除	
    +-----------------------------+
    | post                        |
    +-----------------------------+
    | 世界第一帅形象代言          |
    | teacher                     |
    | sale                        |
    | operation                   |
    +-----------------------------+

查询关键字之order by排序

order by默认是升序  默认的关键字是asc（默认自动加，所以不带就可以）
	select * from emp order by salary ; #和下面这句话一样意思 根据工资排序 升序 从少变多
	select * from emp order by salary asc;
	也可以改为降序 desc
    select * from emp order by salary desc; #根据工资排序 降序排列
 
# order by排序支持多个字段组合(第一个不行 就往后继续排)   如果age有重复的，重复的这些就按照第二个给定的条件排序 
	select * from emp order by age,salary;
    select * from emp order by age asc,salary desc;