mysql,day04笔记

今日内容

• 表关系判断之一对一

• 修改表的SQL语句补充

• 复制表(了解)

• 单表查询关键字的使用

  select
  from 
  where
  group by
  having
  distinct
  limit
  order by
  regexp
  

• 多表查询

表关系判断之一对一

1.qq用户表
2.客户表和学生表



有时候一张表里面的数据量太多并且有大部分数据不是经常需要使用的，这个时候我们可以基于表关系的知识将一张表拆分为两张表，然后绑定表关系

以用户表和用户详情表为例
	1.先站在用户表的基础上
    	问一个用户能否对应多个用户详情信息
        	不可以
    2.再站在用户详情表的基础上
    	问一个用户详情能否对应多个用户信息
        	不可以
结论:双方都不可以，那么表关系就是"一对一"或者"没有关系"
针对一对一
    	外键字段建在任何一方都可以但是建议你建在查询频率较高的表中
    
    
SQL语句实现
create table user(
	id int primary key auto_increment,
    name varchar(32),
    age int,
    detail_id int unqiue,
    foreign key(detail_id) references userDetail(id)
);
create table userDetail(
	id int primary key auto_increment,
    addr varchar(32),
    phone int
)

表关系的总结

我们学习了如何判断表关系以及如何建立表关系，但是在实际工作中
遇到有关系的表不一定非要用外键去建立表关系
因为当表特别多的时候，如果频繁的使用外键会导致表关系非常的复杂，操作起来关联性太强耦合程度太高
我们可以在sql语句层面建立表与表之间的逻辑关系
	比如:在修改用户表的时候房屋表也要跟着改，那么我们只需要将
        修改用户表的sql语句和修改房屋表的sql语句放在一起执行

修改表的SQL语句补充

1.修改表的表名
	alter table t1 rename t2;
2.修改表的字段类型
	alter table t1 modify id varchar(32);
3.修改表的字段名
	alter table t1 change id nid int;
4.添加额外的字段  # 默认在尾部追加字段
	alter table t1 add password int;
5.添加额外的字段  # 自定义位置
	alter table t1 add gender enum('male','female') after name;
6.添加额外的字段  # 直接在表的开头
	alter table t1 add fid int first;
7.删除字段
	alter table t1 drop fid;

复制表（了解）

"""
sql语句的查询结果其实我们可以看成是一张存在于内存中的虚拟表
"""
create table t789 select * from dep;  # 将后面表的查询结果直接作为新表的数据
# 上述复制表的语法只复制表数据不复制表结构

create table t999 like dep;  # 复制表结构不要表数据

单表查询

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);

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

查询语句的书写顺序和执行顺序

select * from emp where id>3;
"""
关键字的书写顺序
select
from
where

关键字的执行顺序
from
where
select
"""
# 书写顺序和执行顺序不需要刻意的去记忆，你就按照书写的习惯从左往右书写即可

查询关键字之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);

# 3.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
	关键字 like
	关键符号
		%:匹配任意个数的任意字符
			eg:
				%j只要字符串末尾是j的都能匹配到
				%j%只要有j的都能匹配到
				
		_:匹配单个个数的任意字符
			eg:
				_j只能匹配尾部是j的两位字符
				_j_只能匹配中间是j的三位字符
"""
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<3 or id>6;
select *  from emp where id not between 3 and 6; 

# 6.查询岗位描述为空的员工的姓名和岗位名称
select name,post from emp where post_comment=null;  # 不行
select name,post from emp where post_comment is NULL;  # 可以
'''针对null不能用等号需要用is'''

查询关键字之group by(分组)

分组
	将单个单个的数据按照一定的条件分门别类，之后以分类之后的整体作为运算集
1.按照部门进行分组
select * from emp group by post;
"""
需要注意的是在MySQL5.6版本及之前的版本上述语法不会报错
但是真正情况下分组之后不能够再获取到分组内单个数据的信息，只能拿到分组的依据不能直接再获取到单个数据信息
MySQL5.7及之后的版本会直接报错

我们需要设置严格模式，让它符合分组之后的规律
"""
set global sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';
select post from emp group by post;

2.获取每个部门的最高工资
select post,max(salary) from emp group by post;

3.获取每个部门的最低工资
select post,min(salary) from emp group by post;

4.获取每个部门的平均工资
select post,avg(salary) from emp group by post;

5.获取每个部门的总和工资
select post,sum(salary) from emp group by post;

6.获取每个部门的人数
select post,count(id) from emp group by post;

7.查询按照部门分组之后的每个部门名称和每个部门下员工姓名
select post,group_concat(name) from emp group by post;
"""
group_concat用于分组之后获取非分组依据的其他字段数据
括号里面可以接受多个数据 展示的时候会自动帮你拼接到一起展示出来
"""
8.查询按照部门分组之后的每个部门名称和每个部门下员工姓名和薪资
select post,group_concat(name,':',salary) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;

"""
concat功能跟group_concat是一致的 
只不过concat只能用于分组之前
group_concat用于分组之后
"""
# 查询每个人的年薪
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp;  # as可以省略

9.统计各部门年龄在30岁以上的员工平均工资
	1.先筛选出年龄在30岁以上的员工
    2.再按照部门分组运算即可
select post,avg(salary) from emp where age>30 group by post;

查询关键之having(过滤)

"""
where是分组之前用于数据的筛选
having是分组之后用于数据的筛选
"""
1.统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp 
			where age>30 
    		group by post 
        	having avg(salary) > 10000;

查询关键字之distinct(去重)

"""
去重的前提是数据必须是一模一样的才可以去重
"""
select distinct post from emp;

查询关键字之order by(排序)

select * from emp order by salary  # 默认是升序
select * from emp order by salary asc;  # asc升序的意思 默认可以不写
select * from emp order by salary desc;  # 降序

"""排序的时候如果出现了相同值的情况 那么我们可以指定多个排序规则"""
select * from emp order by age asc,salary desc;
# 先按照年龄升序排序 如果年龄相同再按照薪资降序排序

查询关键字之limit(限制数据条数)

"""
limit的使用场景最常见的就是数据的分页展示
"""
select * from emp limit 5;  # 只获取前五条数据
select * from emp limit 5,5;  # 第一个数字表示起始位置 第二个数字表示数据个数

# 求薪资最高的用户的用户名和薪资
	1.选按照薪资降序排序
    2.再利用limit只取第一条
select name,salary from emp order by salary desc limit 1;

查询关键字之regexp(正则表达式)

"""
正则表达式
	通过一些符合的组合从字符串中筛选出你想要的内容
爬虫的时候再具体讲解
"""
select * from emp where name regexp '^j.*(n|y)$';
^j.*(n|y)$ 
	# 匹配字符串
    1.必须是以字母j开头
    2.必须是以字母n或者y结尾
    3.中间可以是任意个数任意字符

补充:正则表达式是一门独立的语言，任何学科任何语言都可以学习使用，规律是一模一样的不受其他语言的影响
参考书籍:《正则指引》

聚合函数

'''聚合函数是在分组之后使用的'''
max		最大值
min		最小值
count	计数
avg		平均值
sum		求和

修改展示字段

# 关键字as可以给字段起别名 用于展示的时候显示出来给用户看
select post as '部门',max(salary) as '最高薪资' from emp group by post;
# 并且as可以省略不写  但是建议你不要省略(容易造成混淆)
select post '部门',max(salary) '最高薪资' from emp group by post;