MySql表与表之间的关系

MySql表与表之间的关系
当前问题共有如下(2)个解决方案
  • badkano
    badkano
    CREATE DATABASE bankDB;

    USE bankDB;

    ##drop TABLE user_info
    CREATE TABLE user_info(
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    user_name NVARCHAR(30),
    user_sex NVARCHAR(4)
    );

    ##drop TABLE card_type
    CREATE TABLE card_type(
    usertype_id INT AUTO_INCREMENT PRIMARY KEY,
    usertype_Name NVARCHAR(30)
    );

    ##DROP TABLE user_card
    CREATE TABLE user_card(
    usercard_id INT AUTO_INCREMENT PRIMARY KEY,
    usercard_number NVARCHAR(30),
    usercard_pwd INT,
    usercard_remark TEXT,
    usertype_id INT,#外键user_type
    user_id INT,#外键user_info
    FOREIGN KEY(usertype_id) REFERENCES card_type(usertype_id) ON DELETE CASCADE,##创建外键关系语句
    FOREIGN KEY(user_id) REFERENCES user_info(user_id) ON DELETE CASCADE ##创建外键关系语句
    )TYPE=INNODB;

    INSERT INTO user_card VALUES (NULL,'500226198705088574',123456,'备注',1,1);
    INSERT INTO user_card VALUES (NULL,'500226199995088574',123456,'备注',2,1);
    INSERT INTO user_card VALUES (NULL,'500226155505088574',123456,'备注',3,1);
    INSERT INTO user_card VALUES (NULL,'500226198705088574',123456,'备注',1,2);
    INSERT INTO user_card VALUES (NULL,'500226777705088574',123456,'备注',1,3);
    INSERT INTO user_card VALUES (NULL,'500226192115088574',123456,'备注',1,4);
    INSERT INTO user_card VALUES (NULL,'500226097050488574',123456,'备注',1,6);
    INSERT INTO user_card VALUES (NULL,'500226777705088574',123456,'备注',2,3);
    INSERT INTO user_card VALUES (NULL,'500226192115088574',123456,'备注',3,4);
    INSERT INTO user_card VALUES (NULL,'500226097050488574',123456,'备注',1,8);

    INSERT INTO user_info VALUES (NULL,'张三0','男');
    INSERT INTO user_info VALUES (NULL,'张三1','女');
    INSERT INTO user_info VALUES (NULL,'张三2','男');
    INSERT INTO user_info VALUES (NULL,'张三3','男');
    INSERT INTO user_info VALUES (NULL,'张三4','男');
    INSERT INTO user_info VALUES (NULL,'张三5','男');
    INSERT INTO user_info VALUES (NULL,'张三6','女');
    INSERT INTO user_info VALUES (NULL,'张三7','女');
    INSERT INTO card_type VALUES (NULL,'信贷卡');
    INSERT INTO card_type VALUES (NULL,'借记卡');
    INSERT INTO card_type VALUES (NULL,'消费卡');

    SELECT * FROM user_info;
    SELECT * FROM card_type;
    SELECT * FROM user_card;

    ######查询卡:信贷卡的用户信息记录#######
    ##方法一:
    SELECT user_info.user_id,user_info.user_name,user_info.user_sex
    FROM user_info,user_card,card_type
    WHERE user_info.user_id=user_card.user_id
    AND user_card.usertype_id=card_type.usertype_id
    AND card_type.usertype_Name='信贷卡';

    SELECT * FROM user_info,user_card,card_type
    ##方法二
    SELECT * FROM user_info WHERE user_id IN(
    SELECT user_id FROM user_card WHERE usertype_id =(
    SELECT usertype_id FROM card_type WHERE usertype_Name='信贷卡')
    );

    ##左连接(以user_info为显示基础,user_card没有符合条件则以null填充显示)
    SELECT DISTINCT * FROM user_info LEFT JOIN user_card ON user_info.user_id=user_card.user_id
    ##右连接(与上面相反)
    SELECT DISTINCT * FROM user_info RIGHT JOIN user_card ON user_info.user_id=user_card.user_id
    ##内连接(ON等同于where)
    SELECT DISTINCT * FROM user_info INNER JOIN user_card ON user_info.user_id=user_card.user_id
  • 阿里服务器租用
    阿里服务器租用
    1.1对1,2.1对多 3无关系
上一篇:php mysql里的 join如何使用
下一篇:如何安装MySql