day42
-
约束条件
unsigned
zerofill
not null
unique
primary key
auto_increment
default
foreign key (外键 重要) -
约束条件之外键（重要）
一对一
一对多
多对多 -
查询语句
select
from
where
group by # 分组
order by # 排序
distinct # 去重
limit # 分页
having
约束条件
1. unsigned 设置无符号（不存在负号）
create table t2 (id int unsigned, name varchar(4));
2. zerofill 零填充（当增加的数字小于默认int大小，会在所增加数字前填充零，直至达到默认int大小）
create table t3 (id int zerofill); # 创建零填充的表
desc t3 #查看表结构，获取默认int大小
3. not null 非空
注意：创建有字符串格式的列表的时候，不加限制时，默认为空（用insert增加数据的时候什么都不写会有默认值NULL）不等于 ''（是有一个空值，看上去真正为空）
4. default 默认值
create table t4 (id int, name varchar(4), gender enum('male', 'female', 'other') default 'male'); # 创建一个默认性别为‘male’
insert into t4 (id, name) values (1, 'jack');
5. unique 唯一
# 单列唯一
create table t6 (id int, name varchar(16) unique)
localhost
192.168. 局域网
47.193.1***** 公网
host port
mysql -h 127.0.0.1 -P 3306 -u root -p 密码 # 127.0.0.1代表的就是本机的ip地址
# 多列唯一
create table t8 (
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 t9 (
id int primary key
)
# InnoDB存储引擎规定一张表中必须有一个主键，
为什么之前的创建表没有指定主键，也能创建成功？
InnoDB引擎帮你用一个隐藏的字段创建了一个主键，隐藏意味着看不到，也不能用。
# 主键的功能: 查询速度快， 主键本质也是一种索引
7. auto_increment
# 你就理解为为主键使用的，自增，每次增加1
create table t13 (
id int primary key auto_increment,
name varchar(16)
)
# 结论：
id int primary key auto_increment
cid nt primary key auto_increment
uid int primary key auto_increment -
删除的方式：
1、物理删除， 相当于delete删除，是真正意义上的从磁盘上删除
2、软删除，没有真正意义上的删除
is_delect
0 没有删除
1 删除
create_time 创建时间
update_time 更新时间
delete_time 删除时间
清空表数据
1. delete from t13; #当有主键自增不会重置
2. truncate from t13; #以后只要是清空表数据，推荐你用truncate可以通过binlog 恢复数据（当有主键自增会重置） -
外键前期准备
1. 在数据表中一般不存中文
# 问题
扩展性差
表关系判断
一对一
一对多
多对多
#########################一对多######################
站在不同的角度去判断
1. 一个用户可以有多个部门吗？
不可以
2. 一个部门可以有多个用户吗？
可以
# 结论：只要是一个可以，一个不可以，那么，他们就是 一对多 的关系
#########################多对多######################
站在不同的角度去判断
1. 一个图书可以有多个作者吗？
可以
2. 一个作者可以有多个书吗？
可以
# 结论：只要是一个可以，另外一个可以，那么，他们就是 多对多 的关系
#########################一对一######################
站在不同的角度去判断
1. 一个作者可以有多个作者详情吗？
不可以
2. 一个作者详情可以有多个作者吗？
不可以
# 结论：只要是一个不可以，另外一个也不可以，那么，他们就是 一对一 的关系
一对一关系外键建在任何一个表中都可以
但是，推荐建在查询频率高的一个表，
SQL语句实现表关系
####################一对多#######################
1. 实现一对多, 一对多的外键建在多的一方
# 创建带有表关系的表
# 1.先创建基表，
# 2.在添加外键关系
# 3.先创建被关联的表， 先创建没有外键的表
create table userinfo(
id int primary key auto_increment,
username varchar(32),
salary decimal(8, 2),
dep_id int,
foreign key (dep_id) references dep(id)
)
create table dep(
id int primary key auto_increment,
name varchar(32),
descript varchar(64)
)
####################多对多#######################
create table book(
id int primary key auto_increment,
title varchar(16),
price int
)
create table author(
id int primary key auto_increment,
name varchar(16),
phone int
)
# 多对多一定要先创建第三张表
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key (book_id) references author(id)
on update cascade # 级联更新
on delete cascade, # 级联删除
foreign key (author_id) references book(id)
on update cascade # 级联更新
on delete cascade # 级联删除
)
####################一对一#######################
create table author_detail(
id int primary key auto_increment,
title varchar(16),
price int
)
create table author_2(
id int primary key auto_increment,
name varchar(16),
phone int,
author_detail_id int unique,
foreign key (author_detail_id) references author_detail(id)
on update cascade # 级联更新
on delete cascade # 级联删除
)
级联更新级联删除
create table userinfo_1 (
id int primary key auto_increment,
username varchar(32),
salary decimal(8, 2),
dep_id int,
foreign key (dep_id) references dep_1(id)
on update cascade # 级联更新
on delete cascade # 级联删除
)
create table dep_1(
id int primary key auto_increment,
name varchar(32),
descript varchar(64)
)
insert into dep_1 (name , descript) values ('技术部', '技术');
insert into dep_1 (name , descript) values ('外交部', '外交');
查询关键字
表准备
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
('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),
('哈哈','femal e',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%';
""" elasticsearch
# 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范围的数据
not in 不走索引
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.作者表与作者详情表
"""
书写出完整的判断过程
并且写上对应的SQL语句
"""
book
publish
图书和出版社是一对多关系
author
书和作者是多对多关系
author_detail
作者和作者详情一对一
浙公网安备 33010602011771号