mysql里表与表之间建立外键的语句_mysql表与表之间创建外键关系

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