Mysql基础3
2021/4/29 19:26:58
本文主要是介绍Mysql基础3,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
内容概要
-
约束条件
-
约束条件之外键(重点)
表与表之间建立关系
-
查询关键字(重点)
select from where group by having distinct order by limit regexp
内容详细
约束条件
在字段类型的基础之上添加的额外约束 1.unsigned 无符号(正负号) 2.zerofill 填充 3.not null 非空 create table t2( id int, name varchar(32) not null ); 4.default 默认值 create table t3( id int, name char(32), gender enum('male','female','others') default 'male' ); 5.unique 唯一 # 单列唯一 create table t4( id int, name varchar(32) unique ); # 多列唯一 create table t5( id int, host varchar(32), port int, unique(host,port) ); 6.primary key 主键 """ 1.单从限制角度上来看 主键相当于是非空且唯一 id int primary key == id int not null unique create table t6( id int primary key ); 2.InnoDB存储引擎规定了在创建表的时候必须有且只有一个主键 1.为什么之前创建表的时候没有主键也可以呢? 当表中没有任何的约束条件字段,为了能够顺利的创建出表,InnoDB存储引擎会采用内部隐藏的字段作为主键 隐藏意味着我们无法使用到,主键其实可以帮助我们加快数据的查询速度 2.没有主键但是有非空且唯一的字段 那么会自动将该字段升级为主键 create table t7( id int, age int not null unique, pwd int not null unique ); 结论:我们在创建表的时候一般都会创建一个id字段(序号\编号) 并且我们会将该字段设置为主键 id int primary key ps:id名称可以不固定 uid cid pid """ 7.auto_increment 自增 # 单一主键 专门用来给key键使用(你就理解为是专门给主键用的) create table t8( id int primary key auto_increment, name varchar(32) ); # 联合主键 create table t8( id int, nid int, name varchar(32), primary key(id,nid)... ); """以后的主键字段固定写法""" id int primary key auto_increment
主键的特征
# 1.delete from 不会影响主键的自增 # 2.truncate 清空表数据并且重置主键
添加数据的两种方式
insert into t1 values(...); # 按照字段顺序一一传值 insert into t1(name) values(...); # 指名道姓的传值
外键前戏
'''员工表''' id emp_name emp_salary dep_name dep_desc # 问题 1.表结构不够明确(忽略) 到底是员工表还是部门表 2.浪费存储空间(忽略) 硬盘不值钱 不够花钱买 3.表扩展性极差(不能忽略) # 拆分 上述三个问题都能够解决 但是员工与部门之间没有了关联 外键字段 用来建立表与表之间的关系的字段
表关系判断
表关系分类 1.一对多 2.多对多 3.一对一 4.没有关系 表关系判断 "换位思考" 1.以员工表与部门表为例 1.先站在员工表角度 问:一名员工能否对应多个部门 答:不可以 2.再站在部门表角度 问:一个部门能否对应多名员工 答:可以 结论:两边一个可以一个不可以 那么表关系就是"一对多" ''' 针对一对多表关系 外键字段建在多的一方(建在员工表中) ''' 2.以书籍表与作者表为例 1.先站在书籍表角度 问:一本书能否对应多名作者 答:可以 2.再站在作者表角度 问:一名作者能否对应多本书 答:可以 结论:两边都可以 那么表关系就是"多对多" """ 针对多对多表关系 外键字段并不会建在任何一张关系表 而是单独开设一张新表专门存储 """ 3.以用户表与用户详情表为例 1.先站在用户表角度 问:一名用户能否对应多个用户详情 答:不可以 2.再站在用户详情表角度 问:一个用户详情能否对应多名用户 答:不可以 结论:两边都不可以 那么要么没关系 要么"一对一" """ 针对一对一表关系 外键字段建在任何一方都可以 但是推荐你建在查询频率较高的表中 """
SQL语句实现
############################一对多######################### # 1.先创建基本字段 # 2.再考虑外键字段 create table emp( id int primary key auto_increment, emp_name varchar(32), emp_salary int, dep_id int, foreign key(dep_id) references dep(id) ); create table dep( id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(64) ); """ 1.在创建表的是先创建被关联的表(没有外键字段的表) 2.在录入数据的时候先录入被关联表的数据 3.修改或者删除被关联表数据 """ ####################多对多###################### create table book( id int primary key auto_increment, title varchar(32), price float(8,2) ); create table author( id int primary key auto_increment, name varchar(32), age int ); create table book2author( id int primary key auto_increment, author_id int, foreign key(author_id) references author(id) on update cascade on delete cascade, book_id int, foreign key(book_id) references book(id) on update cascade on delete cascade ); ######################一对一#################### create table userinfo( id int primary key auto_increment, name varchar(32), age int, detail_id int unique, foreign key(detail_id) references userinfo_detail(id) on update cascade on delete cascade ); create table userinfo_detail( id int primary key auto_increment, addr varchar(32), phone bigint );
级联更新级联删除
create table dep( id int primary key auto_increment, dep_name varchar(32), dep_desc varchar(64) ); create table emp( id int primary key auto_increment, emp_name varchar(32), emp_salary int, dep_id int, foreign key(dep_id) references dep(id) on update cascade # 级联更新 on delete cascade # 级联删除 );
作业
1.书籍表与出版社表2.课程表与老师表3.班级表与学生表4.作者表与作者详情表"""书写出完整的判断过程并且写上对应的SQL语句"""
查询关键字
表准备
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);
1.select 用来指定表的字段数据 select * from emp; select id,name from emp; """ 在工作中一般很少使用*号 我们只是为了教学方便 """ 2.from 后面跟需要查询的表名即可 3.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); # 简写 """ 模糊查询 关键字 like 关键符号 %:匹配任意个数的任意字符 _:匹配单个个数的任意字符 show variables like '%mode%'; """ # 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 = NULL; # 查询为空! select name,post from emp where post_comment is NULL; select name,post from emp where post_comment is not NULL;
查询关键字之group by分组
分组 将单个单个的个体按照指定的条件分成一个个整体 """ 分组之后默认只能直接获取到分组的依据 其他字段无法再直接获取(可以间接获取) """ # 严格模式 set global sql_mode='STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH,only_full_group_by' # 1.每个部门的最高薪资 select post,max(salary) from emp group by post; # 2.每个部门的最低薪资 select post,min(salary) from emp group by post; # 3.每个部门的平均薪资 select post,avg(salary) from emp group by post; # 4.每个部门的人数 select post,count(id) from emp group by post; # 5.每个部门的月工资总和 select post,sum(salary) from emp group by post; """ 可以给字段起别名(as还可以给表起别名) select post as '部门',sum(salary) as '总和' from emp group by post; """ # 查询分组之后的部门名称和每个部门下所有的员工姓名 """ group_concat() 获取分组以外的字段数据 并且支持拼接操作 select post,group_concat(name) from emp group by post; select post,group_concat(name,':',salary) from emp group by post; concat() 未分组之前使用的拼接功能 select concat(name,':',sex) from emp; concat_ws() select concat_ws(':',name,sex,salary,age) from emp; """
聚合函数
分组之后频繁需要使用的 max 最大值 min 最小值 sum 求和 count 计数 avg 平均值
作业
1. 查询岗位名以及岗位包含的所有员工名字 2. 查询岗位名以及各岗位内包含的员工个数 3. 查询公司内男员工和女员工的个数 4. 查询岗位名以及各岗位的平均薪资 5. 查询岗位名以及各岗位的最高薪资 6. 查询岗位名以及各岗位的最低薪资 7. 查询男员工与男员工的平均薪资,女员工与女员工的平均薪资
这篇关于Mysql基础3的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-06-25MySQL报错Duplicate entry '0' for key 'PRIMARY'
- 2024-05-29阿里 Canal 实时同步 MySQL 增量数据至 ClickHouse 数据库
- 2024-05-24在Linux下管理MySQL的大小写敏感性
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享