mysql4

目录

自增特性

 create table t1(
	id int primary key auto_increment,
  name varchar(32)
);
insert into t1(name) values('jason'),('kevin'),('tony');
insert into t1(name) values('oscar');  # id=4
delete from t1 where id=4;
insert into t1(name) values('oscar');  # id=5
'''自增不会随着数据的删除而回退'''
delete from t1;  # 删除数据但无法重置主键
insert into t1(name) values('jason'),('kevin'),('tony');
truncate t1;  # 删除数据并重置主键值
insert into t1(name) values('jason'),('kevin'),('tony');

外键简介

# 外键前戏
	创建一张员工表
  	id	name age dep_name dep_desc
  缺陷
  	1.表的重点不清晰(可以忽略) 
    	既可以说是员工表也可以说是部门表
    2.表中某些字段对应的数据一直在重复(可以忽略)
    	浪费存储空间
    3.表的扩展性极差 牵一发而动全身(不能忽略)
    	耦合度太高 不利于维护
  解决
  	将一张表一分为二
    员工表								部门表
    id	name 	age				id 		dep_name		dep_desc
    # 拆表之后上述的三个缺陷解决 
  但是迎来了一个致命问题  >>>	员工与部门之间没有了绑定关系
    在员工表中添加一个部门编号字段与部门表中的主键字段对应
	"""
	该字段其实就是 外键字段
		外键字段就是用来记录表与表之间数据的关系
	"""

外键关系

总共就四种
	1.一对多
  2.多对多
  3.一对一
  4.没有关系
  
"""
关系的判断记住四字口诀:换位思考
"""
# 一对多
1.以员工表与部门表为例
	先站在员工表的角度
  	问:一个员工能否对应多个部门
    答:不可以
  再站在部门表的角度
  	问:一个部门能否对应多个员工
    答:可以
  结论:换位思考之后得出的答案是一个可以一个不可以
    所以关系是"一对多" 部门是'一'员工是'多'
    '''关系表达只能用一对多 不能用多对一'''
  	一对多关系 外键字段建在"多"的一方(员工表)

外键SQL语句

"""
如果表中有外键字段 那么建议先编写普通字段 最后再考虑外键字段
"""
# 在创建表字段的时候也可以给每个字段添加中文注释
create table emp(
	id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep(id)
);
create table dep(
	id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);

# foreign key的约束效果
1.创建表的时候 应该先创建被关联表(没有外键字段的表)
2.插入数据的时候 应该先插入被关联表(没有外键字段的表)
	外键字段填入的值只能是被关联表中已经存在的值
3.修改、删除被关联表数据都会出现障碍
	update dep set id=200 where id=2;
  delete from dep where id=2;
 
# 级联更新级联删除
create table emp1(
	id int primary key auto_increment comment '编号',
  name varchar(32) comment '姓名',
  age int comment '年龄',
  dep_id int comment '部门编号',
  foreign key(dep_id) references dep1(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
);
create table dep1(
	id int primary key auto_increment comment '编号',
  dep_name varchar(32) comment '部门名称',
  dep_desc varchar(32) comment '部门描述'
);
"""
在实际工作中外键也可能不会使用 因为外键会消耗额外的资源
并且会增加表的复杂度 
表很多的情况下 我们也可以通过SQL语句的形式建立逻辑意义上的表关系
"""

多对多

以图书与作者表为例
	1.先站在图书表的角度
  	问:一本书籍能否对应多名作者
    答:可以
  2.再站在作者表的角度
  	问:一名作者能否对应多本书籍
    答:可以
  结论:换位思考之后两边都可以 那么就是"多对多"关系
create table book(
	id int primary key auto_increment,
  title varchar(32),
  price float(10,2),
  author_id int,
  foreign key(author_id) references author(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
);
create table author(
	id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others'),
  book_id int,
  foreign key(book_id) references book(id) 
  on update cascade  # 级联更新
  on delete cascade  # 级联删除
);
  """
  针对多对多关系 需要单独开设第三张表专门存储关系
  """
  create table book(
    id int primary key auto_increment,
    title varchar(32),
    price float(10,2)
);
	create table author(
    id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female','others')
);
	create table book2author(
    id int primary key auto_increment,
    author_id int,
    book_id int,
    foreign key(author_id) references author(id) 
    on update cascade   # 级联更新
    on delete cascade,  # 级联删除
    foreign key(book_id) references book(id) 
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);
  # 针对多对多表关系 
  两张基表内的数据没有在第三张表内绑定关系的情况下随意新增修改删除

一对一关系

针对qq用户表 其实里面的数据可以分成两类
	热数据:经常需要使用的数据
    	eg:qq号码 座右铭 个人简介 爱好
  冷数据:不怎么经常需要使用的数据
    	eg:邮箱 电话 学校 ...
  为了节省资源并降低数据库压力 会将表一分为二
  	用户表
    	存使用频率较高的数据字段
    用户详情表
    	存使用频率较低的数据字段
1.先站在用户表的角度
	问:一个用户数据能否对应多个用户详情数据
  答:不可以
2.再站在用户详情表的角度
	问:一个用户详情数据能否对应多个用户数据
  答:不可以
结论:换位思考之后两边都不可以 那么关系可能有两种
  	'没有关系'
    '一对一关系'
   	针对'一对一关系'外键字段建在任意一方都可以,但是推荐建在查询频率较高的较好的一方
    
create table User(
	id int primary key auto_increment,
  name varchar(32),
  gender enum('male','female','others'),
  user_detail_id int unique,  
  foreign key(user_detail_id) references UserDetail(id) 
  on update cascade   # 级联更新
  on delete cascade,  # 级联删除
);
create table UserDetail(
	id int primary key auto_increment,
  phone bigint,
  age int
);

表查询关键字

# 数据准备
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);

查询关键字之select与from

 select用于指定查询的字段
from用于指定查询的表
select id,name from mysql.user;
'''查询关键字其实有先后执行顺序'''

查询关键字之where筛选

# 1.查询id大于等于3 小于等于6的数据
select * from t1 where id>=3 and id <=6;
select * from t1 where id between 3 and 6;
# 2.查询薪资是20000或者18000或者17000的数据
selsct * from t1 where solary in (20000,18000,17000);
select * from t1 where salary=20000 or salary=18000 or salary=17000;
# 3.查询id小于3或者大于6的数据
select * from t1 where id not between 3 and 6;
# 4.查询薪资不在20000,18000,17000的数据
	select * from emp where salary not in (20000,18000,17000);
# 5.查询岗位描述为空的数据
	select * from emp where post_comment=null;  # 不可以
	'''针对null只能用is不能用等号'''
	select * from emp where post_comment is null;  # 可以
    
# 6.查询员工姓名中包含字母o的员工姓名和薪资

"""
	查询条件如果不是很明确的情况下 我们统一称之为'模糊查询'
	关键字 				
			like:开启模糊查询的关键字
	关键符号
			%:匹配任意个数的任意字符	
			_:匹配单个个数的任意字符
"""   
select name,salary from t1 where name like '%o%'
# 7.查询员工姓名是由四个字符组成的数据
select * from t1 where name like '____';
select * from t1 where char_length(name) = 4;

查询关键字group by分组

# 什么是分组?
	按照指定的条件将单个单个的个体组织成一个个整体
  eg:按照性别分组 按照部门分组 按照年龄分组 按照国家分组...
# 为什么需要分组?
	分组的好处在于可以快速统计出某些数据
  eg:最大薪资 平均年龄 最小年龄 总人数
# 如何分组
	'''按照部分分组'''
	select * from emp group by post;
  """
  mysql5.7及以上版本默认自带sql_mode=only_full_group_by
  该模式要求分组之后默认只可以直接获取分组的依据不能直接获取其他字段
  
  原因是分组的目的就是按照分组的条件来管理诸多数据 最小单位应该是分组的依据而不是单个单个的数据
  
  如果是MySQL5.6及以下版本 需要自己手动添加
  """
# 聚合函数
	专门用于分组之后的数据统计
  	max				统计最大值
    min				统计最小值
    sum				统计求和
    count			统计计数
    avg				统计平均值
# 1.统计每个部门的最高薪资
select post,max(salary) from emp group by post;
# 2.统计每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 3.统计每个部门的员工人数
select post,count(id) from emp group by post;
# 4.统计每个部门的月工资开销
select post,sum(salary) from emp group by post;
# 5.统计每个部门最小的年龄数
select post,min(age) from emp group by post;
 """间接获取分组以外其他字段的数据"""
# 1.统计每个部门下所有员工的姓名
select post,group_concat(name) from emp group by post;
# 2.统计每个部门下所有员工的姓名和年龄
sekect post,group_concat(name,age) from emp group by post
"""字段起别名"""
	select post,group_concat(name) as '姓名' from emp group by post; 
  select id as '序号',name as '姓名' from emp;
  # as关键字也可以不写 但是语义不明确 建议加上
  select id '序号',name '姓名' from emp;

作业

1.判断表关系
	# 班级表
    cid	caption
    # 学生表
    sid sname gender class_id
    # 老师表
    tid	tname
    # 课程表
    cid	cname	teacher_id
    # 成绩表
    sid	student_id course_id number
班级 学生：一对多  学生一
班级 老师表： 多对多
学生 课程：多对多
老师 课程：一对一
课程 成绩：一对一
分组练习题
  1. 查询岗位名以及岗位包含的所有员工名字
     select post,group_concat(name) from emp group by post;
  2. 查询岗位名以及各岗位内包含的员工个数
select post,count(name) from emp group by post;
  3. 查询公司内男员工和女员工的个数
    select sex,count(id) 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;
posted @   名字只需六字  阅读(18)  评论(0)  编辑  收藏  举报
相关博文：
·  mysql6
·  mysql2
·  03MYSQL
·  MySQL操作
·  前段（三）
阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· winform 绘制太阳，地球，月球 运作规律
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
点击右上角即可分享
微信分享提示
AI IDE Trae