MySQL基础-note-4.22

最新推荐文章于 2024-09-27 10:06:02 发布

oa_s_i_s

最新推荐文章于 2024-09-27 10:06:02 发布

阅读量128

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/oa_s_i_s/article/details/116000525

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

昨日内容

1.字段类型

整型
	tinyint
	smallint
	int
	bigint
浮点型
	float
	double
	decimal
字符类型
	char
	varchar
	#注意两者区别及各自特性
枚举与集合类型
	enum
	set
日期类型
	datetime
	date
	time
	year

2.创建表的完整语法

create table 表名(
	字段名 字段类型(宽度 约束条件1 约束条件2……),
	字段名 字段类型(宽度 约束条件1 约束条件2……),
	字段名 字段类型(宽度 约束条件1 约束条件2……),
);

3.约束条件

1. unsigned	无符号
2. zerofill	0填充
3. not null	非空
4. default	默认值
5. unique	唯一
6. primary key	主键
"""
InnoDB存储引擎规定了表有且仅有一个主键
1.如果没有主键也没有非空且唯一的字段，那么采取默认的
2.如果没有主键但是有非空且唯一的字段那么自动升级
总结：创建表的时候都应该有一个序号字段，并且该字段一般都是主键
"""
7. auto_increment	自增
# id int primary key auto_increment

4.主键自增特性

delete from	删除数据并不会重置自增
truncate	可以重置主键

5.外键

外键是用来建立表与表之间关系的字段

表关系的判断
	"换位思考"
	以AB为例：
		站在A的角度：一个A能否对应多个B;
		站在B的角度：一个B能否对应多个A
1.一对多
	双方只有一方是成立的
	#一对多，关系字段建在多的一方
2.多对多
	双方都成立
	#多对多，关系字段需要创建一张全新的关系表存储
3.一对一
	双方都不成立
	#一对一，关键字段建在任何一方都可以，推荐建在查询频率较高的

6.外键字段的SQL语句

1.先创建没有外键字段的表
2.先录入没有外键字段表的数据
3.修改或者删除外键字段关联的主键有限制
	级联更新	on update cascade
	级联删除	on delete cascade

7.作业讲解

1.学生表与课程表
	先站在学生表角度，问一个学生能否对应多门课程：	可以
	再站在课程表角度，问一门课程能否对应多名学生：	可以
	结论：双方都成立，则关系为"多对多关系"

2.老师表与班级表
	先站在老师表角度，问一名老师能否对应多个班级：	可以
	再站在班级表角度，问一个班级能否对应多名老师：	可以
	结论：双方都成立则关系为"多对多关系"

3.成绩表与学生表
	先站在成绩表角度，问一份成绩能否对应多名学生：	不可以
	再站在学生表角度，问一名学生能否对应多个成绩：	不可以（分学科）
	结论：双方都不成立，则关系为"一对一关系"

4.老师表与课程表
	先站在老师表角度，问一名老师能否对应多个课程：	不可以
	再站在课程表角度，问一门课程能否对应多名老师：	可以
	结论：关系为"一对多关系"

今日内容

1.修改表

# mysql对大小写不敏感
(1).修改表名
	alter table 表名 rename 新表名；

(2).增加字段
	alter table 表名 add 字段名 数据类型[完整性约束条件……];
	alter table 表名 add 字段名 数据类型[完整性约束条件……] after 字段名;
	#after 在……后添加字段	；	first，在表首添加字段，一般用不上

(3).删除字段
	alter table 表名 drop 字段名

(4).修改字段
#modify 只能改字段数据类型以及约束条件，不能改变字段名；但change可以
	alter table 表名 modify 字段名 数据类型[完整性约束条件……];
	alter table 表名 change 旧字段名 新字段名 数据类型 [完整性约束条件……]；

2.查询语句关键字

'''前期表准备'''
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);

2.1 where 筛选

select * from emp where id=1;
"""
select	指定字段
from	定位
where	筛选
"""
(1).查询id大于等于3，小于等于6的数据
select * 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;
select * from emp where salary in (20000,18000,17000);

#######模糊查询#######
关键字	like
	show variables like '%mode%';
关键符号	% _
	%:匹配任意个数的任意字符
	_:匹配单个个数的任意字符
######################
(3).查询员工姓名中包含o字母的员工姓名和薪资
#在刚开始接触mysql查询的时候，建议按照查询的优先级顺序拼写出sql语句
"""
先是查那张表	from emp
再是根据什么条件去查	where name like '%o%'
再是对查询出来的数据筛选展示部分	select name,salary
"""

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

(4).查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';	#4个_
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;

2.2 group by 分组

分组	就是按照指定的依据将单个的个体分门别类(整体)
#分组之后默认只能直接获取到分组的依据，无法再直接获取分组内单个数据的详细信息

聚合函数
	max	最大值
	min	最小值
	sum	求和
	count	计数
	avg	平均值
#数据分组应用场景：每个部门的平均薪资，男女比例等

(1).按部门分组
select * from emp group by post;	#分组后取出的是每个组的第一条数据
select id,name,sex from emp group by post;	#验证
"""
设置sql_mode为为only_full_group_by,意味着以后但凡分组，只能取到分组的依据，
不应该再去取组里面的单个元素的值，那样的话分组就没有意义了，因为不分组就是对单个元素信息的所以获取

set global
sql_mode="strict_trans_tables,only_full_group_by";
#重新连接客户端
select * from emp group by post;	#报错
select id,name,sex from emp group by post;	#报错
select post from emp group by post	#获取部门信息
#强调：只要分组了，就不能再“直接”查到单个数据信息了，只能获取到组名
"""

(2).获取每个部门的最高工资
#以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
#每个部门的最高工资
select post,max(salary) from emp group by post;
补充：在显示的时候还可以给字段取别名
select post as '部名',max(salary) as '最高工资' from emp group by post;
#as 也可以省略，但是不推荐省去，因为寓意不明确
#每个部门的最低工资
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;
统计的时候只要是非空字段，效果都是一致的
这里显示age，salary，id最后演示特殊情况post_comment

(3).查询分组之后的部门名称和每个部门下所有的学生姓名
#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;

(4).补充concat(不分组时用)拼接字符串达到更好的显示效果，as语法使用
select name as '姓名',salary as '薪资' from emp;
select concat('NAME:',name) as 姓名,concat('SAL:',salary) as 薪资 from emp;

#补充as语法 即可以给字段起别名也可以给表起
select emp.id,emp.name from emp as t1;	#报错	因为表名已经被改成了t1
select t1.id,t1.name from emp as t1;

#查询四则运算
#查询每个人的年薪
select name,salary*12 as annual_salary from emp;
select name,salary*12 annual_salary from emp;	#as可以省略

2.4 having过滤

having的功能与where是一致的 都是用来筛选数据
只不过where是用在分组之前，
having是在分组之后进行的筛选，即where不能使用聚合函数，但是having可以

(1).统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
	where age>=30
	group by post
	having avg(salary)>10000;
#强调having必须在group by后面使用
select * from emp having avg(salary)>10000;	#报错

2.5 distinct去重

#对有重复的展示数据进行去重操作，一定要是重复的数据
select distinct id,age from emp;
select distinct post from emp;

2.6 order by排序

select * from emp order by salary asc;	#默认升序
select * from emp order by salary desc;	#降序

#先按照age降序排，在年纪相同的情况下再按照薪资升序排
select * from emp order by age desc,salary asc;

#统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
	where age>10
	group by post
	having avg(salary)>1000
	order by avg(salary);

2.7 limit分页

#限制展示条数
select * from emp limit 3;

#查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;

#分页显示
select * from emp limit 0,5;	#第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * form emp limit 5,5

2.8 正则

使用一些特殊符号的组合去字符串中筛选出符合条件的数据
select * from emp where name regexp '^j.*(n|y)$';

^j.*(n|y)$
筛选以字符j开头，字母n或者字母y结尾的数据
#后续在爬虫，会详细讲

3.多表操作

思路：
1.子查询
	分步操作，当一条sql语句的查询结果当作另外一条sql语句的条件


2.连表操作(重点)
	先将多张表拼接成一张大表，之后再查询即可(将多表查询变成单表查询)
	涉及到多表查询，字段前面最好用表名做区分，防止出现字段名冲突的情况

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

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

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务')
;

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

#为什么要分表——就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

3.1连表操作(重点)

(1)inner join	内连接	#只会拼接两张表都有的数据
	select * from emp inner join dep on emp.dep_id=dep.id
(2)left join	左连接	#以左表为基准展示所有，对应不上用NULL填充
	select * from emp left join dep on emp.dep_id=dep.id;
right join	右连接	#以右表为基准展示所有，对应不上用NULL填充
	select * from emp right join dep on emp.dep_id=dep.id;
(3)union	全连接	
	select * from emp left join dep on emp.dep_id=dep.id 
	union	
	select * from emp right join dep on emp.dep_id=dep.id

3.2 子查询

#就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用
#1.查询部门是技术或者人力资源的员工信息
"""
先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息
"""
select * from emp where dep_id in(select id from dep where name='技术' or name='人力资源');

4.可视化软件

Navicat 可视化软件
	将SQL语句操作全部封装成了鼠标点点点就可以实现的功能

破解版本
破解版本:https://pan.baidu.com/s/1bpo5mqj

作业

1.整理今日内容至笔记
2.将课上所有的查询题目拷贝出来不看答案自己写SQL语句
3.重点掌握多表操作
	子查询
	连表查询
4.自己安装Navicat并链接数据库完成库、表和记录的操作
"""
1.查询岗位名以及岗位包含的所有员工名字
2.查询岗位名以及各岗位内包含的员工个数
3.查询公司内男员工和女员工的个数
4.查询岗位名以及各岗位的平均薪资
5.查询岗位名以及各岗位的最高薪资
6.查询岗位名以及各岗位的最低薪资
7.查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
"""