Mysql入门学习

建库

CREATE DATABASE DB;

引用库

USE bankDB;

建表

##创建用户表##drop TABLE user_infoCREATE TABLE IF NOT  EXISTS user_info(    user_id INT AUTO_INCREMENT PRIMARY KEY,    user_name NVARCHAR(30),    user_sex NVARCHAR(4));##创建用户卡类型表##drop TABLE card_typeCREATE TABLE IF NOT  EXISTS card_type(    usertype_id INT AUTO_INCREMENT PRIMARY KEY,    usertype_Name NVARCHAR(30));##创建用户卡信息表##DROP TABLE user_card CREATE TABLE IF NOT  EXISTS 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 ##创建外键关系语句)engine=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=5;##外键事务引擎,数据库字符集,数据库校对规则

录入表数据

##录入用户信息数据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,'消费卡');##录入用户卡数据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);

数据查询

##分别查询每张表的所有数据SELECT * FROM user_info;SELECT * FROM card_type;SELECT * FROM user_card;##查询三张表的所有数据SELECT  * FROM user_info,user_card,card_type ;######查询卡:信贷卡的用户信息记录#########方法一：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 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.判断表是否存在

IF NOT  EXISTS例：CREATE TABLE IF NOT  EXISTS 表名

2.标识自增列（一般设置主键）

AUTO_INCREMENT例： user_id INT AUTO_INCREMENT

3.标识主键

PRIMARY KEY例： user_id INT PRIMARY KEY

4.标识外键（4，5，6一般一起使用）

FOREIGN KEY例：FOREIGN KEY(要设置的外键字段)

5.外键引用（4，5，6一般一起使用）

REFERENCES例：REFERENCES 表名（关联字段）

6.设置级联删除（4，5，6一般一起使用）

ON DELETE CASCADE例： FOREIGN KEY(usertype_id) REFERENCES  card_type(usertype_id) ON DELETE CASCADE

7.设置外键处理引擎（设置在表后面，见表user_card）

ENGINE=InnoDB例：ENGINE=InnoDB 

8.设置外键字符集（设置在表后面，见表user_card）

CHARACTER SET utf8例： CHARACTER SET utf8

9.设置数据库校对规则（设置在表后面，见表user_card）

COLLATE utf8_general_ci AUTO_INCREMENT=5例：COLLATE utf8_general_ci AUTO_INCREMENT=5例：ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=5