day54mysql
内容回顾：
1.mysql的基本语法
对库的操作
show databases;
create database db1;
使用数据库：
use db1;
对表
create table t1(
id int not null unique unsigned,
name char(10) not null,
sex enum('男','女'),
fav set('唱歌','跳舞','烫头'),
born_date_time datetime
);
对sql只允许 一张表有一个主键
create table t2(
id int primary key auto_increment unsigned,
name char(10) not null,
sex enum('男','女'),
fav set('唱歌','跳舞','烫头'),
born_date_time datetime
);
查看所有的表：
show tables;
对记录
insert into
t1(name,sex,fav,born_date_time)
values
('alex','男','唱歌',now()),
('alex','男','唱歌',now());
select * from t1 where id < 2;
对于查询来说 从大范围去筛选，查找小的范围，越来越接近业务需求
增删改 ****查****
2.数据类型
整型：
tinyint int bigint
浮点型：
float
double
decimal
字符：
char
varchar
text
时间：
datetime、year、date、time
枚举和集合
enum
set
布尔：
tinyint(1) 存0表示false，存1表示true
boolean
3.对表的设计
id name sex addr iphoneNum teacher score
今日内容：
1.完整性约束,作用（保证数据的完整性和一致性）
not null 和default
unique
单列唯一
仅仅给一列设置unique(id)
多列唯一
给多列设置unique(id),unique(name)
联合唯一
unique(id, name)
主键
primary key
化学反应： not null + unique
自增长 auto_increment
插入时对于自增长的也可以指定数值
创建student
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);
对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
z
create table t1(id int primary key auto_increment,name varchar(20) not null);
create table t2(id int primary key auto_increment,name varchar(20) not null);
insert into t1(name) values ('一宁');
insert into t1(id,name) values (4,'一宁');
清空表区分delete和truncate的区别：
delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。添加数据会以之前的id 插入
truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。；清空 id
作用：保证数据的完整性和一致性
2.完整性约束补充
foreign key 外键 建立两张表之间的联系
dep : 被关联表 主表；先有主表才有从表；有 foreign key的是从表
create table dep(
id int primary key auto_increment,
name varchar(20) not null,
des varchar(30) not null
);
create table dep2(
id int primary key auto_increment,
name varchar(20) not null,
des varchar(30) not null
);
emp ： 关联表 从表
create table emp(
eid int primary key auto_increment,
name char(10) not null,
age int not null,
dep_id int not null,
dep2_id int not null,
constraint fk_dep foreign key(dep_id) references dep(id)
on delete cascade # 同步删除，删除主表，即从表的相关信息就会被删除;如果没有这，则删除主表数据的时候会因为从表的数据而报错
on update cascade, # 同步更新，更新主表，即从表的相关信息会同步变化
外键后面这一行后面没有，
constraint fk_dep2 foreign key(dep2_id) references dep2(id)
on delete cascade # 同步删除
on update cascade # 同步更新
);
create table emp(
eid int primary key auto_increment,
name char(10) not null,
age int not null,
dep_id int not null,
dep2_id int not null,
constraint fk_dep foreign key(dep_id) references dep(id),
constraint fk_dep2 foreign key(dep2_id) references dep2(id),
on delete cascade,
on update cascade
);
2.外键的变种
三种 关系
分析步骤：
#1、先站在左表的角度去找是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id）
#2、再站在右表的角度去找是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id）
#3、总结：
#多对一：如果只有步骤1成立，则是左表多对一右表
如果只有步骤2成立，则是右表多对一左表
#多对多如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系
#一对一:
如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
多对多：
使用第三张表 建立多对多的关系
重要
create table book(
id int primary key auto_increment,
name varchar(20)
);
create table author(
id int primary key auto_increment,
name varchar(20)
);
create table autho_book(
id int primary key auto_increment,
book_id int not null,
author_id int not null,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade
);
insert into author(name) values
('beijing'),
('renmin'),
('zhishi'),
('walker')
;
insert into book(name) values
('jiuyangshengong'),
('jiuyangzhenjing'),
('jiuyangbaiguzha'),
('dugujiujian'),
('shibazhan'),
('kuihuabaodian');
插入数据的时候 外键 foregin key中的键值的数据必须在主键中存在不然insert时报错
Cannot add or update a child row: a foreign key constraint fail
insert into autho_book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
建表
作业：
create table class(cid int not null primary key auto_increment, caption varchar(20) not null);
create table student(sid int not null primary key auto_increment,sname varchar(20) not null,gender varchar(20) not null,class_id int not null,
constraint st_key foreign key(class_id) references class(cid) on delete cascade on update cascade );
create table teacher(tid int not null primary key auto_increment, tname varchar(20) not null);
create table course(cid int not null primary key auto_increment,cname varchar(20) not null,tearch_id int not null,
constraint course_key foreign key(tearch_id) references teacher(tid) on delete cascade on update cascade );
create table score(
sid int not null primary key auto_increment,
students_id int not null,
corse_id int not null,
number int not null,
constraint cos_id foreign key(students_id) references student(sid)
on delete cascade
on update cascade,
constraint sosk_id foreign key(corse_id) references course(tearch_id)
);
#例如： 一个用户只能注册一个博客
一对一
#两张表： 用户表 （user）和 博客表(blog)
# 创建用户表
create table user(
id int primary key auto_increment,
name varchar(20)
);
# 创建博客表
create table blog(
id int primary key auto_increment,
url varchar(100),
user_id int unique,
constraint fk_user foreign key(user_id) references user(id)
on delete cascade
on update cascade
);
#插入用户表中的记录
insert into user(name) values
('alex'),
('wusir'),
('egon'),
('xiaoma')
;
# 插入博客表的记录
insert into blog(url,user_id) values
;
# 查询wusir的博客地址
select url from blog where user_id=2;
用户和博客（一对一）
3.单表查询
一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二、关键字的执行优先级（重点）
重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct 去重用distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 ；只能查询分组的字段
4.将分组的结果进行having过滤
5.执行select
6.去重 distinct
7.将结果按条件排序：order by
8.限制结果的显示条数
单表查询
12-单表查询
where 约束
where子句中可以使用
1.比较运算符：>、<、>=、<=、<>、!=
2.between 80 and 100 ：值在80到100之间
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符，_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
重点，里面有数据
（2）group by 分组查询
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的，也可以没有where
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的如果想分组，
则必须要设置全局的sql的模式为ONLY_FULL_GROUP_BY
最好加在配置文件
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
#查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
+--------------------+
| @@global.sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
mysql> exit;#设置成功后，一定要退出，然后重新登录方可生效
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
# 每个部门有多少个员工
select post,count(id) from employee group by post;
# 每个部门的最高薪水
select post,max(salary) from employee group by post;
# 每个部门的最低薪水
select post,min(salary) from employee group by post;
# 每个部门的平均薪水
select post,avg(salary) from employee group by post;
# 每个部门的所有薪水
select post,sum(age) from employee group by post;
1. 查询岗位名以及岗位包含的所有员工名字
select post_comment,name from employee
2. 查询岗位名以及各岗位内包含的员工个数
select post,count(1) from employee group by post;
3. 查询公司内男员工和女员工的个数
select count(1),sex from employee group by sex;
4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;
5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;
6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from employee group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;
