mysql记录操作

自增特性

自增的数字不会随着数据的删除自动回退

约束条件之外键

外键简介

外键（foreign key）是用于建立和加强两个表数据之间的链接的一列或多列。外键表示一个表中的一个字段被另一个表中的一个字段引用。外键对相关表中的数据造成了限制，使MySQL能够保持参照完整性。
外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）。

外键关系

一对多
多对多
一对一
没有关系

外键sql语句

添加中文注释

create table department(
	id int primary key auto_increment comment '部门编号',
     department_name varchar(42) comment '部门名称',
     department_duty varchar(42) comment '部门职责',
     department_manager varchar(42) comment '部门经理'
);
insert into department (department_name,department_duty,department_manager) values('finance','mannege money','peter'),('market','sale products','richad'),('human_resouse','hire staffs and fire workers','bob');


create table staff(
	id int primary key auto_increment comment '员工编号',
  	name varchar(42) comment '姓名',
    gender enum('male','female') comment'性别',
    work_age int comment '工龄',
    department_id int comment '部门编号',
    foreign key(department_id) references department(id)
);
insert into staff (name,gender,work_age,department_id) values('smith','male',2,1),('boris','male',4,2),('macron','female',3,3);

foreign key的约束效果

1.创建表的时候,应该先创建被关联表即没有外键字段的表
2.插入数据的时候，应该先插入被关联表即没有外键字段的表
3.外键字段填入的值只能是被关联表中已经存在的值
4.修改、删除被关联表数据都会报错
update department set id=11 where id=1;
delete from department where id=1;

级联更新级联删除

create table department_plus(
	id int primary key auto_increment comment '部门编号',
     department_name varchar(42) comment '部门名称',
     department_duty varchar(42) comment '部门职责',
     department_manager varchar(42) comment '部门经理'
);
insert into department_plus (department_name,department_duty,department_manager) values('finance','mannege money','peter'),('market','sale products','richad'),('human_resouse','hire staffs and fire workers','bob');


create table staff_plus(
	id int primary key auto_increment comment '员工编号',
  	name varchar(42) comment '姓名',
    gender enum('male','female') comment'性别',
    work_age int comment '工龄',
    department_id int comment '部门编号',
    foreign key(department_id) references department_plus(id)
    on update cascade  # 级联更新
    on delete cascade  # 级联删除
);
insert into staff_plus (name,gender,work_age,department_id) values('smith','male',2,1),('boris','male',4,2),('macron','female',3,3);

 update department_plus set id=12 where id=2;
 delete from department_plus where id=3;

多对多关系

以图书与作者表为例
# 先站在图书表的角度
一本书籍能否对应多名作者？
答案:可以
# 再站在作者表的角度
一名作者能否对应多本书籍？
答案:可以
    
# 针对多对多关系，需要单独开设第三张表专门存储关系
    create table book(
    id int primary key auto_increment comment '编号',
    title varchar(42) comment '图书名',
    price float(10,2) comment '图书价格'
);
insert into book (title,price) values('pride and prejudice',25),('the whale',20),('the big sleep',30),('little prince',20);
    
	create table author(
    id int primary key auto_increment comment '编号',
    name varchar(42) comment '作者名',
    gender enum('male','female','others') comment '性别',
    age int comment '年龄'
);
    
insert into author (name,gender,age) values('jane austen','male',100),('moby dick','male',110),('raymond chandler','male',120),('antoine','male',130);
	create table book_author(
    id int primary key auto_increment comment '编号',
    author_id int comment '作者编号',
    book_id int comment '图书编号',
    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  # 级联删除
);
insert into book_author (author_id,book_id) values(1,1),(2,2),(3,3),(4,4);
insert into book_author (author_id,book_id) values(1,2),(2,1),(3,4),(4,3);

一对一关系

针对qq用户信息，里面的数据可以分成两类
热数据:经常使用的数据
eg:qq号码 昵称 性别
冷数据:不经常使用的数据
eg:邮箱  学校 年龄

# 先站在用户表的角度
一个用户数据能否对应多个用户详情数据？
答案:不可以
# 再站在用户详情表的角度
一个用户详情数据能否对应多个用户数据？
答案:不可以
# 结论
用户表和用户详情表之间的关系是：'一对一关系'。针对'一对一关系'外键字段设置任意一方都可,推荐设置在查询频率较高的表
 create table UserDetail(
  id int primary key auto_increment,
  email  varchar(42),
  age int,
  school varchar(42)
);
insert into UserDetail (email,age,school) values('777777@qq.com',22,'nanjing university'),('666666@qq.com',24,'wuhan university'),('888888@qq.com',20,'chongqing university');


create table QQUser(
  id int primary key auto_increment,
  nickname varchar(42),
  gender enum('male','female','others'),
  qq_num int unique,
  UserDetailId int,
  foreign key(UserDetailId) references UserDetail(id) 
  on update cascade
  on delete cascade
);
insert into QQUser (nickname,gender,qq_num,UserDetailId) values('老虎','male',666666,2),('太阳','male',888888,3),('夏花','female',777777,1);

查询关键字

准备查询表格

# 准备查询表格
create table MainDepartment(
 id int primary key auto_increment,  # 编号
 department_name varchar(42)
);
insert into  MainDepartment (department_name) values('lecturer_department'),('sale_department'),('rear_service_department'),('school administraction committee');

create table PythonSchool(
  id int primary key auto_increment,  # 编号
  name varchar(42) not null,  # 姓名
  gender enum('male','female','others') not null default 'male', # 默认性别为男
  age int(3) unsigned not null default 28,  # 年龄
  recruit_date date not null,  # 招聘时间
  position varchar(50),  # 岗位
  position_description varchar(100), # 岗位简介
  salary int,  #薪资
  office int, #一个部门一间办公室
  department_id int,  # 部门编号
  department_director varchar(42),  # 部门负责人
  foreign key(department_id) references MainDepartment(id)
  on update cascade
  on delete cascade
);

#四个部门：学校管理委员会，教学，销售，运营
insert into PythonSchool(name,gender,age,recruit_date,position,position_description,salary,office,department_id,department_director) values
('Dominic','male',28,'20150901','headmaster','manage school',2000000,101,4,'Dominic'), # 学校管理委员会
('Rachel','female',32,'20160609','vice headmaster','manage school',1500000,101,4,'Dominic'),
('Tom','male',45,'20200201','lecturer','teach a course',50000,201,1,'Tom'), # 讲师部
('Mathew','male',65,'20190405','lecturer','teach a course',8000,201,1,'Tom'),
('Robin','male',35,'20190501','lecturerr','teach a course',7500,201,1,'Tom'),
('Mitchell','male',28,'20180101','lecturer','teach a course',4200,201,1,'Tom'),
('Austin','female',18,'20190211','lecturer','teach a course',12000,201,1,'Tom'),
('Raphael','male',142,'20180903','lecturer','teach a course',22000,201,1,'Tom'),
('Brandon','male',38,'20181011','lecturer','teach a course',300000,201,1,'Tom'),
('Elizabeth','female',48,'20180325','sale','sale courses',6000.13,301,2,'Elizabeth'), # 销售部
('Jane','female',38,'20191102','sale','sale courses',8000.35,301,2,'Elizabeth'),
('Kitty','female',50,'20190412','sale','sale courses',5000.37,301,2,'Elizabeth'),
('Harris','female',13,'20190513','sale','sale courses',6000.29,301,2,'Elizabeth'),
('Tianna','female',25,'20190127','sale','sale courses',4000.33,301,2,'Elizabeth'),
('Casey','male',25,'20210311','operation','assist the lecturer',6000.13,401,3,'Casey'), # 运营部
('Taylor','male',28,'20220312','operation','assist the lecturer',8000,401,3,'Casey'),
('Amanda','female',17,'20190311','operation','assist the lecturer',40000,401,3,'Casey'),
('Simon','male',18,'20190411','operation','assist the lecturer',8000,401,3,'Casey'),
('Demi','female',20,'20200511','operation','assist the lecturer',5000,401,3,'Casey'),
('Anya','female',30,'20200426','operation','assist the lecturer',8000,401,3,'Casey'),
('Aleena','female',40,'20200701','operation','assist the lecturer',5000,401,3,'Casey');

查询关键字之select与from

select用于指定查询的字段
from用于指定查询的表

查询关键字之where筛选

查询id大于等于6,小于等于10的数据

select * from pythonschool where id>=6 and id<=10;
select * from pythonschool where id between 6 and 10;

查询id小于6或者大于10的数据

select * from pythonschool where id not between 6 and 10;

查询薪资是8000或者6000或者5000或者12000的数据

select * from pythonschool where salary=8000 or salary=6000 or salary=5000 or salary=12000;
select * from pythonschool where salary in (8000,6000,5000,12000);

查询薪资不在8000,6000,5000,12000的数据

select * from pythonschool where salary not in (8000,6000,5000,12000);

查询员工姓名中包含字母a的员工姓名和薪资

select name,salary from pythonschool where name like '%a%';

查询姓名字母为六位的员工

select * from pythonschool where name like '______';
select * from pythonschool where char_length(name)=6;

查询关键字之group by分组

聚合函数

函数	功能
max	统计最大值
min	统计最小值
sum	统计求和
avg	统计平均值
count	统计计数

统计每个岗位的月工资开销

select position,sum(salary) from pythonschool group by position;

统计每个岗位最小的年龄数

select position,min(age) from pythonschool group by position;

统计每个岗位的最高薪资

select position,max(salary) from pythonschool group by position;

统计每个岗位的平均薪资

select position,avg(salary) from pythonschool group by position;

统计每个岗位的员工人数

select position,count(id) from pythonschool group by position;

统计每个岗位下所有员工的姓名和年龄

select  position,group_concat('姓名：',name,'年龄：',age) from pythonschool group by position;

posted @ 2022-05-06 00:25 一梦便是数千载阅读(68) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· mysql表操作

· mysql数据库之查询

· 数据库---mysql外键和查询

· mysql之外键与关键字详解

· MySQL之外键介绍

阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？

公告

昵称：一梦便是数千载
园龄： 3年
粉丝： 8
关注： 21

+加关注

2025年3月

日

一

二

三

四

五

六

一梦便是数千载