内容概要

• 约束条件

• 约束条件之外键(重点)

  表与表之间建立关系

• 查询关键字(重点)

  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. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资