4/6课后总结
约束条件
# 就是在数据类型的基础上添加额外的限制
1. unsigend
# 无符号型
create table t1(id int unsigned);
insert into t1 values(-100); # 报错
insert into t1 values(100);
2. zerofill
# 0填充
create table t1(id int(4));
insert into t1 values(1); # 存进去的是0001
3. not null
# 非空
create table t2(id int,name varchar(32) not null);
insert into t2(id) values(20); # 报错
insert into t2 values(20，'zhangsan');
4. default默认值
create table t4(id int,name varchar(32) default 'zhangsan');
insert into t4 values(10,'wanger');
insert into t4(id) values(20); # 没有给name值，将会使用默认值
5. 唯一
# 唯一索引 = 唯一索引 + 唯一约束
# 单列唯一
create table t6(
id int unique,
age int
);
insert into t6 values(1,20);
insert into t6 values(1,20); # ID重复报错
insert into t6 values(2,22);
insert into t6(age) values(22); # 如果是空，不报错
insert into t6(age) values(22);
insert into t6(age) values(22);
# 多列唯一
create table t7(
id int,
name varchar(32),
age int,
unique(name,age)
);
insert into t7 values(1,'zhangsan',18);
insert into t7 values(1,'zhangsan',18); # 报错
insert into t7 values(1,'zhangsan',19);
insert into t7 values(1,'lisi',19); # 必须要两个都同时重复才报错
insert into t7(id age) values(1,19); # 如果有空，则不会报错
insert into t7(id age) values(1,19);
insert into t7(id,name) values(1,'mazi');
insert into t7(id,name) values(1,'mazi');
insert into t7(name,age) values('mazi',20);
insert into t7(name,age) values('mazi',20); # 报错
6. 主键
# 主键索引 = 唯一约束+唯一索引+非空约束
"""
主键本质上也是一种索引，可以提高查询速度
InnoDB存储引擎规定了一张表至少有一个主键，但是创建表时不设立主键也不会报错，因为自动生成了隐藏主键，但是这个隐藏主键不会提高查询速度。
所以以后创建表时都要有一个主键
一般这个主键都设为id
"""
create table t8(id int primary key,age int);
insert into t8 values(1,18);
insert into t8 values(2,18); # id重复会报错
7. 自增
# 在原来的基础上每次自增1
create table t9 (
id int primary key auto_increment,
name varchar(32)
);
# 有了自增后，以后主键可以不用手动输入了
insert into t9(name) values('zhangsan');
补充一些其他SQL语句
语法：
1. 修改表名
ALTER TABLE 表名 RENAME 新表名;
2. 增加字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…]；# 在最后添加一个字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST;# 在最一开始添加一个字段
ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 在某个字段的后面添加一个字段
3. 删除字段
ALTER TABLE 表名 DROP 字段名; # 删除某个字段
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
查询关键字
select--->跟字段名---->*----->所有字段
from----->指定表名
where------>查询条件
数据准备
# 数据准备
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age smallint(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
('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);
查询关键字之where筛选
"""
模糊查询:没有明确的筛选条件
关键字:like
关键符号:
%:匹配任意个数任意字符
_:匹配单个个数任意字符
show variables like '%mode%se';
在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
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 = 17000;
select *from emp where salary in (20000,18000,17000);
3.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
5.查询id小于3或者大于6的数据
select *from emp where id < 3 or id>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 is NULL;
'''在sql中，NULL和''不一样''
查询关键字之group by分组
# 分组就是按照某个特征分到一组
# 分组之后默认只能够直接过去到分组的依据 其他数据都不能直接获取
针对5.6需要自己设置sql_mode
set global sql_mode = 'only_full_group_by,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
# 聚合函数
聚合函数主要就是配合分组一起使用
max min sum count avg
1.按部门分组
select post from emp group by post;
"""
由于已经添加了only_full_group_by，所以只能拿post，或者用group_concat()来获取组内的值
"""
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;
# 统计的时候只要是非空字段 效果都是一致的
# 比如post_comment就是空的，如果统计只会是0，不会是具体多少个
# group_concat 分组之后使用
select post,group_concat(name) from emp group by post;
# concat 不分组使用
select concat(name,'|',sex) from emp;
# concat_ws()
select concat_ws('|',name,sex) from emp;
关键字之having过滤
"""
where与having都是筛选功能 但是有区别
where在分组之前对数据进行筛选
having在分组之后对数据进行筛选
关键字where group by 同时出现的情况下，group by必须在where之后
where 先对整张表进行一次筛选，group by再对筛选过后的表进行分组
我们一定要有一个简单的认识 一条SQL语句的结果也可以看成是一张全新的表
"""
1.统计各部门年龄在30岁以上的员工平均薪资,并且保留平均薪资大于10000的部门.
select post from emp where age>30 group by post having avg(salary)>10000;
关键字之distinct去重
# 对有重复的展示数据进行去重操作
select distinct id,age from emp; # 无效去重
select distinct post from emp;
关键字之order by排序
select * from emp order by salary; #默认升序排
select * from emp order by salary desc; #降序排
1.先按照age降序排，在年轻相同的情况下再按照薪资升序排
select * from emp order by age desc,salary;
'''多字段排序，如果想让后面的字段排序生效，前提：前面的排序字段必须一样'''
2.统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post from emp where age>10 group by post having avg(salary)>1000 order by avg(salary) desc;
关键字之limit分页
1. 限制展示条数
select * from emp limit 3;
2. 查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
3. 分页显示
select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置
select * from emp limit 5,5;
关键字之regexp正则
select * from emp where name regexp '^j.*(n|y)$';
清空表补充
1. delete from t; # 不能把主键重置，可以删除某一行记录
2. truncate t; # 可以把主键重置为1开始,这个是删除整个表，无法作用于记录
'''所以，以后如果有清空表的需求，我们选择truncate，因为truncate清空表之后，还可以恢复数据'''
3. DBA可以恢复数据------>binlog日志----->记录你操作sql语句的过程
4. DBA:数据库管理员
外键
# 原本创建一张员工表 id name age dep_name dep_desc
"""
缺陷
1.表的重点不清晰 可以忽略
到底是员工表还是部门表
2.表中相关字段一直在重复存储 可以忽略
浪费存储空间
3.表的扩展性极差,牵一发而动全身 不能忽略
"""
# 解决方式，将上述一张表拆分成两张表：emp与dep。上述三个缺陷全部解决，但是带来了一个小问题 表与表之间的数据没有对应关系了
"""
外键字段>>>:部门编号
其实就是用来标识表与表之间的数据关系
简单的理解为该字段可以让你去到其他表中查找数据
"""
表与表之间建关系
"""
表关系总共就四种
一对多
多对多
一对一
没有关系
判断表关系的方式:换位思考
"""
一对多：
一个部门可以有多个员工
但是一个员工不可以在多个部门
表关系中一对多和多对一都是一对多
在创建外键关系的时候，先创建出来基础字段，
"""
使用SQL语句建立真正意义上的表关系 可以先创建不含外键字段的基本表
之后再添加外键字段
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 级联更新
on delete cascade # 级联删除
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
"""
外键约束
1.在创建表的时候 需要先创建被关联表(没有外键字段的表)
2.在插入新数据的时候 应该先确保被关联表中有数据
3.在插入新数据的时候 外键字段只能填写被关联表中已经存在的数据
4.在修改和删除被关联表中的数据的时候 无法直接操作
"""
由于外键有实质性的诸多约束 当表特别多的时候外键的增多反而会增加耦合程度
所以在实际开发项目中 有时候并不会使用外键创建表关系
而是通过SQL语句层面 建立逻辑意义上的表关系
eg:操作员工表的sql执行完毕之后 立刻跟着执行操作部门的sql
"""
作业
1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) 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;
浙公网安备 33010602011771号