精华内容
下载资源
问答
  • 查询表之间的依赖关系 如 子表 父表 等的关系
  • 和表的关系 单表查询 多表查询 055 ... 找出两张表之间的关系  分析步骤 :  # 1 先站在左表的角度去找  是否左表的多条记录可以对应右表的一条记录 , 如果是 则证明左表的一个字段  fo...

    表和表的关系 单表查询 多表查询 055

    一 . 表和表的关系 ---外键的变种



      一对多 或者 多对一    多对多  一对一

      找出两张表之间的关系

        分析步骤 :

          # 1 先站在左表的角度去找

            是否左表的多条记录可以对应右表的一条记录 , 如果是 则证明左表的一个字段

          foreign key 右表的一个字段

          # 2 再站在右表的角度去找

            是否右表的多条记录可以对应左表的一条记录  , 如果是 则证明右表的一个字段

          foreign key 坐标的一个字段

          # 3 总结 : 

            # 多对一

              如果只有步骤1 成立 则是左表多对一右表

              如果只有步骤2 成立 则是右表多对一左表

            # 多对多

              步骤1和 2 同时成立 证明这两张表是一个双向的多对一 即多对多, 需要

              定义一个这两张表的关系表来专门存放两者之间的关系

            # 一对一

              1 2 都不成立 而是左表的一条记录唯一对应右表的一条记录 , 反之亦然

               这种情况很简单 , 就是在左表foreign key 右表的基础上 , 将左表的外键

              字段设置成unique即可

            多对一的关系 : 例如 一个出版社可以出版多本书 

            关联方式: foreign key

    # 创建主表
    create table press(
        id int primary key auto_increment,
        name varchar(20)
    );
    # 创建从表
    create table book(
        id int primary key auto_increment,
        name varchar(20),
        press_id int not null,
            constraint fk_book_press foreign key(press_id) references press(id)
        on delete cascade
        on update cascade,
    );
    # 先往被关联表中插入记录
    insert into press(name) values
    ('北京工业地雷出版社'),
    ('人民音乐不好听出版社'),
    ('知识产权没有用出版社');
    # 再往关联表中插入记录
    insert into book(name,press_id) values
    ('九阳神功',1),
    ('九阴真经',2),
    ('九阴白骨爪',2),
    ('独孤九剑',3),
    ('降龙十巴掌',2),
    ('葵花宝典',3)

    查询结果:
    mysql> select * from book;
    +----+-----------------+----------+
    | id | name            | press_id |
    +----+-----------------+----------+
    |  1 | 九阳神功        |        1 |
    |  2 | 九阴真经        |        2 |
    |  3 | 九阴白骨爪      |        2 |
    |  4 | 独孤九剑        |        3 |
    |  5 | 降龙十巴掌      |        2 |
    |  6 | 葵花宝典        |        3 |
    +----+-----------------+----------+
    6 rows in set (0.00 sec)
    
    mysql> select * from press;
    +----+--------------------------------+
    | id | name                           |
    +----+--------------------------------+
    |  1 | 北京工业地雷出版社             |
    |  2 | 人民音乐不好听出版社           |
    |  3 | 知识产权没有用出版社           |
    +----+--------------------------------+
    3 rows in set (0.00 sec)

            多对多的 关系(作者与书籍的关系)

              多对多 : 一个作者可以写多本书 一本书也可以有多个作者,双向的一对多,

            即多对多  关联方式 : foreign key + 一张新的表

         用户和博客 一对一关系  一个用户只能注册一个博客

          关联方式 : foreign key + unique

    # 例如 一个用户只能注册一个博客
    # 两张表 : 用户表(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
    ('http://www.cnblog/alex',1),
    ('http://www.cnblog/wusir',1),
    ('http://www.cnblog/egon',1),
    ('http://www.cnblog/xiaoma',1),

    单表查询 :


     

    一 . 单表查询的语法
        select 字段1,字段2,...from 表名
                    where 条件
                    group by field
                    having 筛选条件
                    order by field
                    limit 限制条数
    二 . 关键字的执行优先级(重点)
        关键字的执行优先级
        from 
        where
        group by
        having
        select
        distinct
        order by
        limit
        
        1 . 找到表 : from
        2 . 拿着where指定的约束条件 ,去文件/表中取出一条条记录
        3 . 将取出的一条条记录进行分组 group by 如果没有 group by ,则整体作为一组
        4 . 将分组的结果进行having过滤
        5 . 执行select
        6 . 去重
        7 . 将结果按条件排序 : order by
        8 . 限制结果的显示条数 例如 只看第一名
    group by

    group by 是发生在where之后的,where条件是可选的

    针对于相同字段进行归类

    select * from employee group by post;

    注意 : 分组之后 只能获取分组的字段,如果想获取组内的信息要通过聚合函数进行计算

     聚合函数
    max()最大值
    min()最小值
    sum()求和
    count()求总个数
    avg()求平均值
    limit0,5第一个参数表示查询的起始位置  第二个参数表示参数的获取个数   0,5表示取前五名 2,5表示取3-7名
    group-concat(name) 将选中的条件中的名称全部显示出来
    虚拟表
    # 起别名
    select  A.a from (select post,count(1)  as a from employee group by post) as A
    将第一次筛选出来的表作为主表进行第二次筛选

    having

    mysql5.7整体作为一组 可以执行sql

    select * from employee having salary>1000000;
    不加group 默认将整个表单分为一组

    mysql 5.6 5.5

    mysql>  select * from employee having salary>1000000;
    ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
    # 必须分组之后才能使用having

    多表查询

    语法:
    select 字段列表
        from 表1 inner |left |right |join 表2
        on 表1.字段 = 表2.字段
     
    多表连接查询
    # 符合条件查询
    select  * from employee,department where employee.dep_id = department.id;
    内连接

     

    只获取匹配的数据

    select * from employee inner join department on employee.dep_id = department.id;
    左连接或者右连接

     

    只显示左表或右表所有记录

    select * from employee left join department on employee.dep_id = department.id;
    全外连接

     
    select * from employee left join department on employee.dep_id = department.id
      union
    select * from employee right join department on employee.dep_id = department.id;
    子查询

     
    #1:子查询是将一个查询语句嵌套在另一个查询语句中。
    #2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
    #3:子查询中可以包含:INNOT INANYALLEXISTSNOT EXISTS等关键字
    #4:还可以包含比较运算符:=!=><

    #小练习

    #查询平均年龄在25岁以上的部门名
    select * from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);

     

     

     
    posted @ 2018-11-22 16:03 你没有想象的那么重要 阅读(...) 评论(...) 编辑 收藏
    展开全文
  • 表与表之间的关系 可以分成三类 一对一关系:一般情况下,一对一关系基本不用,当发现两张表是一对一的关系,就合成一张表,例如:人和身份证号 一对多关系:在表关系中是最多的情况,也是最常见的,比如:部门...

    表与表之间的关系

    可以分成三类

    一对一关系:一般情况下,一对一关系基本不用,当发现两张表是一对一的关系,就合成一张表,例如:人和身份证号

    一对多关系:在表关系中是最多的情况,也是最常见的,比如:部门和员工

    多对多关系:从两个方向观察发现都是1-n关系,比如:企业和应聘者

    操作:

    -- 创建学生表
    DROP table student;
    CREATE table student (
    	s_id int PRIMARY key auto_increment, -- 主键约束,自增
    	s_name VARCHAR(10) not null, -- 非空约束
    	s_num int UNIQUE not null -- 唯一,非空约束
    );
    -- 创建课程表
    CREATE TABLE course (
    	c_id int PRIMARY KEY auto_increment COMMENT '课程id', -- 主键约束
    	c_name VARCHAR(10) not NULL unique COMMENT '课程名称' -- 唯一,非空约束
    );
    -- 创建中间表
    create table t_stu_cour (
    	s_id int,
    	c_id int,
    	constraint stu_id foreign KEY(s_id) REFERENCES student(s_id),
    	constraint cout_id foreign KEY(c_id) REFERENCES course(c_id)
    )
    -- 创建用户表
    DROP table `user`;
    create table `user` (
    	u_id int PRIMARY KEY auto_increment COMMENT '用户表主键id', -- 主键约束
    	u_name VARCHAR(10) NOT null, -- 非空约束
    	u_phone VARCHAR(11) UNIQUE, -- 唯一约束
    	u_rid int, -- 需要添加的外键信息
    	CONSTRAINT user_rid FOREIGN KEY(u_rid) REFERENCES role(r_id)
    );
    -- 创建角色表
    create table role (
    	r_id int PRIMARY KEY auto_increment COMMENT '角色表主键id', -- 主键约束
    	r_name VARCHAR(10) NOT NULL UNIQUE, -- 唯一非空约束
    	r_desc VARCHAR(255)
    );
    -- 如果两张表是一对多关系,设计时先创建主表,再创建从表
    

    表与表之间关系总结

    表之间关系 关系维护,创建
    一对一 合表,互为外键约束,表之间关系很少
    一对多 在从表(多的那一方)的那一方创建外键,关联主表的主键字段,先创建主表,再创建从表
    多对多 创建中间表,中间表分别添加外键约束关联各自对应的主键

    数据库设计的范式

    什么是范式

    ​ 在设计数据库的时候,需要遵从的一些规范要求,根据这些规范要求设计出合理的数据库。这些规范被称作范式。这些范式针对的是关系型数据库。

    ​ 目前关系型数据库的范式有六种:第一范式(1NF),第二范式(2NF),第三范式(3NF),第四范式(4NF),第五范式(完美范式)(5NF),巴斯-科德范式(BCNF)。

    ​ 各种范式呈递次规范,越高的范式数据库的冗余性就越低。

    前三种范式介绍:

    第一范式(1NF):数据库中的每一列是不可分割的原子数据项。

    第二范式(2NF):在第一范式的基础上,非码属性必须完全依赖于码(在第一范式的基础上消除非主属性对主属性的部分函数依赖)

    概念:

    1. 函数依赖:A–>B 如果通过A属性(属性组)的值,可以确定唯一的B属性值,可以称B依赖与A
    2. 完全函数依赖:A–>B 如果A是一个属性组,则B属性值的确定需要依赖于A属性组中所有的属性值。
    3. 部分函数依赖:A -->B 如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中某一个或某一些属性即可。
    4. 传递函数依赖:A–>B B–>C 如果通过A属性(属性组)的值,可以唯一确定B属性的值,再通过B属性的值可以唯一确定C属性的值,可以称C传递函数依赖于A。
    5. 码:如果再一张表中,一个属性或属性组,被其他所有属性完全依赖,则称这个属性(属性组)为该表的码
      • 主属性:码属性中的所有属性
      • 非主属性:除主属性之外的其他属性

    第三范式(3NF):在第二范式的基础上,任何非主属性不依赖于其他的非主属性。(在2NF基础上,消除传递函数依赖。)

    三大范式总结:

    范式 描述
    第一范式(1NF) 表中的每一列具有原子性,表中的每一列不可分割
    第二范式(2NF) 消除部分函数依赖,一张表只做一件事
    第三范式(3NF) 消除传递函数依赖,表中的每一列都直接依赖于码(主键)不需要通过其他的字段(列)间接依赖于主键

    多表连接查询

    分类:

    内连接(显示内连接,隐式内连接),外连接(左外连接,右外连接)

    笛卡尔积现象:

    ​ 左表中的每条记录和右表中的每条记录全关联组合,这种效果就称之为笛卡尔积现象

    消除笛卡尔积现象:

    ​ 添加条件过滤,使用where条件语句,达到过滤掉无效的数据。

    内连接 inner join

    • 隐式内连接:

      省略掉内连接关键字 inner join

      语法:select 字段列表 from 表名1,表名2,...... where 条件语句

      操作:select * from employee,department where e_did = d_id;

    • 显示内连接:

      使用内连接关键字 inner join … on 语句 inner 可以省略

      语法:select 字段列表 from 表名1 [inner] join 表名2 on 条件语句

      操作:select * from employee inner join department on e_did = d_id;

    ​ 总结:

    ​ 1.查询那些表

    ​ 2.确定表关联的条件

    ​ 3.使用连接的方式

    ​ 4.确定查询的字段信息,尽量少用*

    外连接

    • 左外连接

      使用left [outer] join … on 条件语句 outer关键字可以省略

      语法:select 字段列表 from 左表(主表) left [outer] join 右表(子标/辅表) on 条件语句

      注意事项:用左表中的记录数据去匹配右表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null。保证左表中的数据全部显示。

      操作:select d.*,e.e_username from department as d left outer join employee as e on e.e_did = d.d_id;

    • 右外连接

      语法:select 字段列表 from 左表(主表) right [outer] join 右表(子标/辅表) on 条件语句

      注意事项:用右表中的记录去匹配左表中的记录数据,如果符合条件的则显示,不显示的数据一律显示为null,保证右表中的数据全部显示。

      select * from employee right

    子查询

    什么是子查询

    ​ 一个查询的结果是另一个查询的条件,形成查询嵌套,里面的查询称之为子查询。一定要出现小括号。

    子查询有三种情况:

    • 子查询的结果可以是单行单列,结果只有一个字段,这一个字段只有一个值

    • 可以是多行单列,只有一个字段,这个字段有多个值

    • 还可以是多行多列,有个多个字段,多个字段分别有多个值。

      操作:

      • 第一种情况:单行单列

        语法:select 查询字段列表 from 表名 where 字段 比较运算符 (子查询);

        特征:我们可以在where的后面使用比较运算符

      • 第二种情况:多行单列

        语法:select 查询字段列表 from 表名 where 字段 in (子查询);

        特征:结果值是一个集合或者一个数组,父查询使用in运算符

        操作:
        select d_name FROM department WHERE d_id in(
        SELECT
        	DISTINCT e_did 
        FROM
        	employee
        where 
        	age <= (
        SELECT
        	avg(age)
        FROM
        	employee
        )
        )
        
      • 第三种情况:多行多列,一般情况下我们可以作为一张虚拟表,进行关联二次查询,一般需要给这个虚拟表起一个别名来实现。

        语法:select 查询字段列表 from 表名,(子查询) as 新表名 where 条件语句;

        特征:多行多列不能再使用in运算符或者比较运算符,而是需要进行多表关联,给查询出来的多行多列起别名。

    子查询总结:
    • 单行单列:只有一个值,再where后面可以使用比较运算符,作为条件
    • 多行单列:是一个集合或者数据值,再where后面使用的是in运算符,作为条件
    • 多行多列:大多数多列结果值方法from后面,作为多表关联的,也可以进行二次条件查询。

    事务

    什么是事务:一个业务操作中,这个操作要么被完全执行成功,要么被撤销掉。这个业务操作是一个整体,在这个整体中所有的sql语句要么全部执行成功,要么被回滚 (业务执行失败)。

    操作:张三给李四转账10000,张三的账户剪掉1万块,李四的账户增加1万块。

    -- 转账业务
    -- 张三 李四 张三给李四转账1万块
    -- 创建账户表
    create table account (
    	id int PRIMARY KEY auto_increment, -- 主键id
    	username VARCHAR(20) NOT NULL, -- 账户
    	balance DOUBLE -- 账户余额
    );
    -- 插入两条数据
    insert into account VALUES(null,'张三',20000),(null,'李四',20000);
    -- 张三给李四转账一万块钱
    -- 先让张三的钱减掉一万
    update account set balance = balance - 10000 where username = '张三';
    -- 添加一条错误语句
    update account set balance = balance - 10000  username = '张三';
    -- 再让李四的钱增加一万
    update account set balance = balance + 10000 where username = '李四';
    -- 查询账户表
    SELECT * FROM account;
    -- 还原数据
    UPDATE account set balance = 20000;
    

    手动操作事务:

    三个动作

    开启事务:start transaction;

    提交事务:commit;

    回滚事务:rollback;

    事务的四大特性

    1. 原子性:事务是一个整体,在这个整体中,是不可分割的,在事务中所有的sql语句要么完全的执行成功,要么都失败。
    2. 一致性:事务在执行前和执行后数据库中的数据状态时一致的。转账:张三和李四转账前余额都是20000,转账后,如果成功:张三是10000,李四是30000。如果失败:张三是20000,李四是20000。
    3. 隔离性:事务与事务之间是互不影响,在多个事务并发执行的时候应该处于隔离的状态。
    4. 持久性:一旦事务执行成功,对数据库的影响是持久的。

    事务的隔离级别

    ​ 读未提交–>read uncommitted

    ​ 读已提交–>read committed

    ​ 可重复读–>repeatable read

    ​ 串行化–>serializable 锁表 安全性最高,性能最低

    由事务隔离级别引发并发事务操作的问题:脏读,不可重复读,幻读。

    展开全文
  • 外键通常在实际工作中,数据库中表格都不是独立存在,且表与表之间是有种联系,比如两张表格,一张为分类表category,一张为商品表product。在分类表中有两个信息,cid、cname,商品表中有三个数据信息pid、name...


    外键


    通常在实际工作中,数据库中表格都不是独立存在的,且表与表之间是有种联系的,比如两张表格,一张为分类表category,一张为商品表product。在分类表中有两个信息,cid、cname,商品表中有三个数据信息pid、name、price。两张表要想有着某种联系,需要设定主键和外键两个属性,其中在分类表(主表)中将cid设置为主键商品表(从表)中pid设置为外键

    外键特点:

    从表外键的值是对主表键的引用。

    从表外键类型,必须与主表主键类型一致。


    声明外键约束:

    1. alter table produnct add constraint key_fk(外键名称) foreign key category_id(从表外键字段名) references category(cid);  

    外键名称用于删除外键约束时使用,也可不设置,一般建议“_fk”为结尾。

    删除外键约束

    1. alter table product drop foreign key key_fk;  

    在不接触外键约束时,主表不能直接删除与从表有约束关系的数据信息,如:

    1. delete from category where cid="XXX";  

    实例:

    1. CREATE TABLE category(  
    2.     cid VARCHAR(32) PRIMARY KEY,  
    3.     cname VARCHAR(100)  
    4. );  
    5. DESC category;  
    6. CREATE TABLE product(  
    7.     pid VARCHAR(32) PRIMARY KEY,  
    8.     pname VARCHAR(40),  
    9.     price DOUBLE,  
    10.     category_id VARCHAR(32)  
    11. );  
    12. INSERT INTO category(cid,cname) VALUES('c001','家电');  
    13. INSERT INTO category(cid,cname) VALUES('c002','服饰');  
    14. INSERT INTO category(cid,cname) VALUES('c003','化妆品');  
    15.   
    16. INSERT INTO product(pid,pname,price,category_id) VALUES('p001','联想','5000','c001');  
    17. INSERT INTO product(pid,pname,price,category_id) VALUES('p002','海尔','5000','c001');  
    18. INSERT INTO product(pid,pname,price,category_id) VALUES('p003','雷神','5000','c001');  
    19.   
    20. INSERT INTO product(pid,pname,price,category_id) VALUES('p004','JACK JONES','800','c002');  
    21. INSERT INTO product(pid,pname,price,category_id) VALUES('p005','真维斯','200','c002');  
    22. INSERT INTO product(pid,pname,price,category_id) VALUES('p006','花花公子','440','c002');  
    23. INSERT INTO product(pid,pname,price,category_id) VALUES('p007','劲霸','2000','c002');  
    24.   
    25. INSERT INTO product(pid,pname,price,category_id) VALUES('p008','香奈儿','800','c003');  
    26. INSERT INTO product(pid,pname,price,category_id) VALUES('p009','相宜本草','200','c003');  
    27.   
    28. ALTER TABLE product ADD CONSTRAINT key_fk FOREIGN KEY(category_id) REFERENCES category(cid);   

    表与表之间的关系:


    1.一对多关系

    一对多建表原则:在从表创建一个字段,字段作为外键指向主表的主键

    实例同上

    2.多对多关系

    多对多关系建表原则:需要创建第三张表,中间表中至少有两个字段,这两个字段分别作为外键指向各自一方的主键。

    实例:

    1. CREATE TABLE orders(  
    2.     oid VARCHAR(32) PRIMARY KEY,  
    3.     totalprice DOUBLE  
    4. );  
    5.   
    6. CREATE TABLE orderitem(  
    7.     oid VARCHAR(50),  
    8.     pid VARCHAR(50)  
    9. );  
    10.   
    11. CREATE TABLE product(  
    12.     pid VARCHAR(32) PRIMARY KEY,  
    13.     pname VARCHAR(40),  
    14.     price DOUBLE,  
    15.     category_id VARCHAR(32)  
    16. );  
    17.   
    18. 订单表和订单项表的主外键关系  
    19. ALTER TABLE orderitem ADD CONSTRAINT orderitem_orders_fk FOREIGN KEY(oid) REFERENCES orders(oid);  
    20.   
    21. 商品表和订单项表的主外键关系  
    22. ALTER TABLE orderitem ADD CONSTRAINT orderitem_product_fk FOREIGN KEY(pid) REFERENCES product(pid);  

    多表查询


    1. 1.交叉连接查询(一般不用)  
    2. select * from A,B  
    3. 2.内连接查询(使用关键字inner join --inner可以省略)  
    4. 隐式内连接:select * from A,B where 条件;  
    5. 显示内连接:selcet * from A inner join B on 条件;  
    6. 3.外连接查询:(使用关键字outer join --outer 可以省略)  
    7. 左外连接:left outer join  
    8. select * from A left outer join B on 条件;  
    9. 右外连接: right outer join  
    10. select * from A right outer join B on 条件;  
    原文链接:https://blog.csdn.net/xiaoyuxianshenging/article/details/76280614
    展开全文
  • <p>I have three tables (MySQL): <li><code>families</code> where I <strong>define</strong> the products' families <li><code>products</code> where I <strong>define</strong> the products ...
  • 一.约束条件 为了确保数据完整性 可以给列加上约束条件 完整性分类: 1....实体完整性 实体:即一行(一条)记录 代表一个实体 entity 实体完整性作用:标识每一行数据不重复 约束类型: 1.1 主键约束(prima...

    一.约束条件
        为了确保数据的完整性 可以给列加上约束条件
        完整性的分类:
            1.实体完整性
            2.域完整性
            3.引用完整性
        1.实体完整性
            实体:即表中的一行(一条)记录 代表一个实体 entity
            实体完整性的作用:标识每一行数据不重复
            约束类型:
                1.1 主键约束(primary key)
                1.2 唯一约束(unique)
                1.3 自动增长列(auto_increment)
            1.1 主键约束
                每一个表中要有一个主键(可以是一列 也可以是多列组成)
                特点: 数据唯一 且不能为null

                第一种添加方式 在创建表的同时 直接添加主键约束
                create table 表名(
                    列名1 列值1 primary key,
                    列名2 列值2,
                    ....
                    列名n 列值n
                )
                第二种添加方式 后期补充主键约束
                create table 表名(
                    列名1 列值1,
                    列名2 列值2,
                    ....
                    列名n 列值n
                )
                alter table 表名 add primary key(列名1);

                联合主键 (由多个列组成的主键)
                create table 表名(
                    列名1 列值1,
                    列名2 列值2,
                    ....
                    列名n 列值n,
                    primary key(列名1,列名2);
                )
                删除主键约束
                alter table 表名 drop primary key;
            1.2 唯一约束
                特点:数据不能重复 值可以为null
                第一种添加方式 在建表的时候添加
                create table 表名(
                    列名1 列值1 约束条件,
                    列名2 列值2 unique,
                    ....
                )
                第二种创建方式 后期追加
                alter table 表名 add unique(列名);
            1.3 自动增长列
                mysql --> auto_increment
                sqlserver --> idenitiy
                oracle --> sequence
                特点:
                    1.设置自动增长的时候 列只能是整型
                    2.当有行被删除 自增会继续自增 在删除行的基础上+1
        2.域完整性
            域完整性的作用:限制此单元格的数据正确 不对照此列的其他单元格
            域代表当前单元格
            域完整性约束:
                数据类型 非空约束(not null) 默认值约束(default)
                2.1 数据类型约束
                2.2 非空约束
                    not null
                    alter table 表名 modify 字段名 字段类型 not null;
                2.3 默认值约束
                    default
                    第一种添加方式 创建表时添加
                    create table 表名(
                        列名1 列值1 约束,
                        列名2 列值2 约束 default 默认值,
                        ....
                        列名n 列值n
                    )
                    第二种添加方式 后期追加
                    alter table 表名 alter 列名 set default 默认值;
        3.引用完整性
            外键约束 foreign key
            用来约束 表与表之间的关系
                建立外键关系 首先 明确 主从关系
                外键一定是在从表中创建 从而找到与主表之间的关系
    例:
                主表 : 部门表 学生表
                从表 : 员工表 分数表
            外键必须要满足的条件:
                1.至少涉及到两张表
                2.建立外键关系 对应列具有相似的数据类型
                3.必须建立索引 主键 和 外键
            注意:外键是可以有重复的 也可以有空值
            创建外键的两种方式
                第一种 在创建表的时候 添加外键
                constraint 外键关系名 foreign key(从表的字段) references 主表名(主表的字段)

                constraint 外键关系名 可以不写 就是一个外键约束的名字 如果不写 系统会自动给
                foreign key(从表的字段) 将 从表中的字段作为外键
                references 主表名(主表的字段) 映射到主表的字段

                CREATE TABLE student(
                    sid INT PRIMARY KEY AUTO_INCREMENT,//默认自动增加
                    sname VARCHAR(10) NOT NULL,//值不能为空
                    gender VARCHAR(10) DEFAULT "男"
                )
                CREATE TABLE score(
                    id INT PRIMARY KEY,
                    score INT,
                    fsid INT,
                    CONSTRAINT fk_student_score FOREIGN KEY(fsid) REFERENCES student(sid)//这里不加分号也不加逗号
                )
                第二种 表已经创建 后期追加
                alter table 主表 add primary key(主表列名)
                alter table 从表 add foreign key(从表列名) references 主表(主表列名)
                注意:
                    1.在从表中添加数据时 外键关联列中存储的数据 必须是主表中存在的数据
                    2.想要删除主表中 正在被从表使用的行 必须先删除从表 再删除主表
                删除外键关联:
                    alter table 表名 drop foreign key 外键关系名//就是你起得名字fk_student_score
    二.表与表之间的关系
        三种关系
         1.一对一 1 vs 1
            person 表 card 表 人表 和 身份证表
            人是主表 身份证表是从表

         2.一对多 1 vs N
         3.多对多 N vs N
    三.多表联合查询***
        在关系型数据库中 一个查询往往会涉及到多张表 多张表如何进行联合查询 就成为了学习sql查询的关键
        如果单纯的只将数据放在一张表中 那么这个表存在大量的数据冗余 这种数据库的设计是不合理的(一般不用)
        1.合并结果集
            union
            union all
            作用 合并结果集 把两个select语句的查询结果合并到一起

            CREATE TABLE g(
                gname VARCHAR(10),
                gage INT
            )
            CREATE TABLE h(
                hname VARCHAR(10),
                hage INT
            )
            INSERT INTO g VALUES("a",1),("b",2),("c",3);
            INSERT INTO h VALUES("d",1),("e",2),("c",3);
            /*union 去除重复数据 合并*/
            SELECT * FROM g UNION SELECT * FROM h;
            /*union all 不去除重复数据 合并*/
            SELECT * FROM g UNION ALL SELECT * FROM h;

            要求:被合并的两个结果集 列数/列类型 必须相同


        2.连接查询
            连接查询 就是求多个表的乘积
            例如 a表 连接 b表 那么查询结果就是 a表*b表
            连接查询 产生 笛卡尔积(在sql中 实现方式 交叉连接 cross join)
            所有的连接方式 都会先产生笛卡尔积 然后 加上限制条件 才会有实际查询意义
            select * from emp,dept;
            join 就是把多个表连接成一个表的手段
            多表查询 分为 内/外连接

            SELECT emp.ename,dept.dname,emp.job FROM emp,dept WHERE emp.deptno = dept.deptno;
            SELECT e.ename,d.dname,e.job FROM emp AS e,dept AS d WHERE e.deptno = d.deptno;
            
            AS关键字 可以给表 起别名 as可省略

            内连接 [INNER]join...on INNER可省略
            [隐式]
            SELECT e.ename,d.dname,e.job FROM emp AS e,dept AS d WHERE e.deptno = d.deptno;
            [显示]
            SELECT e.ename,d.dname,e.job FROM emp e INNER join dept d ON e.deptno = d.deptno;
            特点: 查询结果必须是满足条件的

            外连接 [OUTER]join...on OUTER可省略
                左外连接 Left [OUTER]join
                右外连接 right [OUTER]join
                全外连接 full join (Mysql不支持)

                左外连接
                    以左表为主
                    SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
                    左连接 是先查询出左表(以左表为主)
                    然后查询右表 右表中满足条件的显示 不满足条件的显示为null
                右外连接
                    以右表为主
                    SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;
                    右连接 是先查询出右表(以右表为主)
                    然后查询左表 做表中满足条件的显示 不满足条件的显示为null
                /*练习 查询出 员工姓名 工作 部门 部门所在地 当部门为 RESEARCH 的 不显示 */
                SELECT * FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno WHERE dept.dname!="RESEARCH";
                SELECT * FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno AND dept.dname!="RESEARCH" WHERE emp.ename !="SMITH";

                /* 步骤
                    1.生成笛卡尔积 select * from emp cross join dept
                    2.应用 on 筛选器 emp.deptno = dept.deptno
                    3.看外连接 是 Left join 还是 right join
                        如果是 left join 会以左表为主 作为保留表
                    4.where过滤器 过滤的是 left join 之后的数据
                */
                总结:
                    1.如果sql 用的是Left join
                        on 后面条件 对left表没作用 只是对right表起过滤作用
                        where语句可以对生成临时表后起到过滤 相当于过滤了 left表
                    2.如果sql 用的是right join
                        on 后面条件 对right表没作用 只是对left表起过滤作用
                        where语句可以对生成临时表后起到过滤 相当于过滤了 right表
                on 和 where 的区别:
                    在使用left join时 on 和 where 区别如下:
                        1.on条件是在 生成临时表 时 使用的条件
                            不管on的条件是否为真 都会返回左边表的数据
                        2.where条件在 生成临时表 后 再对临时表进行过滤
                            如果条件不为真 就会全部过滤
            自然连接 Natural join
                自然连接 无需你去给出外键关联等式 它会自动找到这一等式
                两张连接表中 名称 和 类型 完全一致的列 作为条件

                /*自然连接*/
                SELECT * FROM emp NATURAL JOIN dept;
                SELECT * FROM emp NATURAL LEFT JOIN dept;
                SELECT * FROM emp NATURAL RIGHT JOIN dept;
        总结:
            连接查询时 sql查询的核心 按照实际需求来选择对应的类型
            如果选择不当 非但不能提高效率 反而会带来一系列的逻辑错误 或者性能下降
            选择依据:
                1.查两张表关联列相等的数据 用内连接
                2.right表是left表的子集 用左外连接
                3.left表是right表的子集 用右外连接
                4.right 和 left 彼此之间有交集 但是互不为子集 使用全外连接
        3.子查询
            一个select语句中 包含另一个完整的select语句
            子查询 就是 嵌套查询
            子查询出现的位置:
                where 后: 作为查询条件的一部分
                from 后:作为表出现
            当子查询出现在where后 作为条件时 还可以使用如下关键字
                any 任意一个 ---> min
                all 所有 ---> max
            子查询结果集的形式:
                1.单行单列 (用于条件)
                2.单行多列 (用于条件)
                3.多行单列 (用于条件)
                4.多行多列 (表)
            1.单行单列 (用于条件)
                例如 :员工表中 工资高于CLARK的员工都有谁
                分析 :
                    1.查询CLARK的工资
                        select sal from emp where ename="CLARK"
                    2.查询高于CLARK的员工
                        select * from emp where sal > 第一步的结果
                    3.结论
                        select * from emp where sal > (select sal from emp where ename="CLARK")

            2.单行多列 (用于条件)
                例如 :查询员工表中 工作 和 工资 与 MARTIN完全相同的员工信息
                分析 :
                    1.查询MARTIN的工作和工资
                        select job,sal from emp where ename="MARTIN";
                    2.查询与MARTIN工作和工资相同的人
                        select * from emp where (job,sal) in 第一步的结果
                    3.结论
                        select * from emp where (job,sal) in (select job,sal from emp where ename="MARTIN")
            3.多行单列 (用于条件)
                多行子查询 返回多行数据
                在where 使用多行子查询时 要用 all any in

                例如 :查找工资高于30号部门所有人的 员工
                分析 :
                    1.查找30号部门所有人的工资
                        select sal from emp where deptno=30;
                    2.查找高于30号部门所有人工资的员工信息
                        select * from emp where sal > all 第一步的结果
                    3.结论
                        SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30);

                        SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno=30);

                    /*练习 部门编号为20的员工工资 并且 大于部门编号为30的所有人工资的 员工列表*/
                    SELECT * FROM emp WHERE deptno=20 AND sal> ALL(SELECT sal FROM emp WHERE deptno=30)
            4.多行多列 (表)
                例如 :查询员工编号为7788的员工 姓名 工资 部门名称 部门地址
                分析 :涉及到 emp dept两张表
                    1.查询 姓名 和 工资
                        select ename,sal from emp where empno=7788
                    2.查询 部门名称 和 部门地址
                        select e.ename,e.sal,d.dname,d.loc
                        from emp e,dept d
                        where e.deptno = d.deptno and e.empno=7788
                    改版:
                        select e.ename,e.sal,d.dname,d.loc
                        from emp e,(select dname,loc,deptno from dept) d
                        where e.deptno = d.deptno and e.empno=7788

                    /*练习 求7369员工 姓名 经理姓名 经理编号 自己连接自己*/
                        SELECT e1.ename,e2.ename,e2.empno
                        FROM emp e1,(SELECT empno,ename FROM emp) e2
                        WHERE e1.mgr = e2.empno AND e1.empno = 7369

    转载于:https://www.cnblogs.com/wanghuaying/p/9484112.html

    展开全文
  • 1 a b 是1对多的关系,bc是1对多的关系,已知c,如何找到a,sql如下: select a.*,b.*,c.* from spma_app_prodouctorder aleft join spma_pay_order b on a.oid=a.pay_order_oidleft join spma_app_...
  • 表与表之间的关系

    2021-04-13 09:51:09
    表与表之间的关系 为什么要拆表 拆分表的目的: 为了解决数据冗余问题,可以减少重复数据的储存,表的结构也更加清晰拆分之后: 在某些业务夏,需要查询多个表,速度减低了。 空间时间 表与表之间的关系 1.一对一 ...
  • ※MySQL中字符编码... //查看系统变量//查询字符编码相关系统变量SHOW VARIABLES WHERE variable_name LIKE 'character%'; 解决MySQK中文乱码:character_set_client,character_set_connectioncharacter_...
  • QBC 查询: QUery By Criteria 根据条件查询, 更加面向对象: 1:开发步骤: a: 获得一个Criteria 对象: b: Criteria.add(“条件”).add("").add(""); 条件: 单独封装成了一个对象:...
  • 表与表之间的关系 一对一 人与身份证之间的关系 注意: (1)一对一不存在数据冗余的问题??可以不拆分表么?可以。 (2)什么场景需要拆分 用户信息 + 身份证 + …->表中的字段太多了。 一对多 最最常见的关系...
  • create table guest (  Sid int identity primary key,  Sname varchar(200),  Gender varchar(1) ) create table host (  Uid int identity primary key,  Duty varchar(200) ...
  • 初识MySQL(二)查询语句, 约束,多表之间的关系,范式, 数据库的备份还原一. DQL:查询语句1. 排序查询2. 聚合函数3. 分组查询4. 分页查询二. 约束1. 概念2. 分类3. 非空约束4. 唯一约束5. 主键约束6. 外键约束...
  • 3. 多表之间的关系 4. 范式 5. 数据库的备份还原 DQL:查询语句 1. 排序查询 * 语法:order by 子句 * order by 排序字段1 排序方式1 , 排序字段2 排序方式2... * 排序方式: * ASC:升序,默认的。 * D...
  • 3. 多表之间的关系 4. 范式 5. 数据库的备份还原 DQL:查询语句 1. 排序查询 * 语法:order by 子句 * order by 排序字段1 排序方式1 , 排序字段2 排序方式2... * 排序方式: * ASC:升序,默认的。 * ...
  • 表与表之间的关系 数据库的三范式 练习题 查询学习课程"python"比课程 "java" 成绩高的学生的学号; 查询平均成绩大于65分的同学的姓名平均成绩(保留两位小数) 查询所有同学的姓名、选课数、总成绩; 查询...
  • 当你写表时候有没有出现过一条数据要打很多行,而且有很多重复,看起来也极其不舒服,而且也不方便。 如: 张三 计算机成绩100分,如果再多加几科成绩,会是什么样子呢? 根据表图就能发现,除了...表与表之间
  • DQL:查询语句 排序查询 * 语法:order by 子句 * order by 排序字段1 排序方式1 , 排序字段2 排序方式2… 排序方式: * ASC:升序,默认。 * DESC:降序。 注意: 如果有多个排序条件,则当前边条件值...
  • 表与表之间关系

    2021-04-19 09:16:25
    表与表之间的关系 11.1为什么要拆表 拆分表的目的: 为了解决数据冗余问题,可以减少重复数据的存储,表的结构也更加清晰了。 拆分之后: 在某些业务下,需要查询多个表,速度减低了 空间时间 11.2表与表之间的...
  • 表与表之间的关系 一对一:用户表身份信息表,用户表是主表 男人表 、女人表 create table man( mid int primary key auto_increment, mname varchar(32), wid int unique ); create ...
  • 在介绍数据库时介绍了表之间的关系,而现在我们已经上升到了框架,我们的表也在java代码中有了体现————bean类。那么我们在以后的开发中对表关系的利用以及多表查询操作都该基于多个bean之间的关系进行。类与类...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,820
精华内容 1,528
关键字:

查询和表之间的关系