mysql 创建师徒_MySQL学习笔记——连接查询

什么是连接查询？

连接查询是将两个或两个以上的表按某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在相同意义的字段时，可以通过该字段连接这几个表。

内连接查询

JOIN|CROSS JOIN INNER JOIN

select * from a_table a inner join b_table b on a.a_id = b.b_id;

组合两个表中的记录，返回关联字段相符的记录。内连接查询是为了避免笛卡尔积的现象，内连接查询的结果是对笛卡尔积结果的约束筛选子集。

# ]

USE excise;

-- 创建省份表 provinces

CREATE TABLE provinces

(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

proName VARCHAR(10) NOT NULL UNIQUE

);

INSERT provinces(proName)

VALUES ('北京'),

('上海'),

('深圳'),

('广州'),

('重庆');

-- 创建用户表cms_user

CREATE TABLE cms_user

(

id INT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL UNIQUE,

password CHAR(32) NOT NULL,

email VARCHAR(50) NOT NULL DEFAULT 'user@qq.com',

regTime INT UNSIGNED NOT NULL,

face VARCHAR(100) NOT NULL DEFAULT 'user.jpg',

proId TINYINT UNSIGNED NOT NULL COMMENT '用户所属省份'

);

INSERT cms_user(username, password, regTime, proId)

VALUES ('张三', 'zhangsan', 1419811708, 1),

('张三丰', 'zhangsanfeng', 1419812708, 2),

('章子怡', 'zhangsan', 1419813708, 3),

('long', 'long', 1419814708, 4),

('ring', 'ring', 1419815708, 2),

('queen', 'queen', 1419861708, 3),

('king', 'king', 1419817708, 5),

('blek', 'blek', 1419818708, 1),

('rose', 'rose', 1419821708, 2),

('lily', 'lily', 1419831708, 2),

('john', 'john', 1419841708, 2);

-- 笛卡尔积现象

SELECT cms_user.id, username, proName

FROM cms_user,

provinces;

-- cms_user的proId对应省份表中的id,采用where条件过滤笛卡尔积结果

SELECT cms_user.id, username, proName

FROM cms_user,

provinces

WHERE proId = provinces.id;

-- 查询cms_user表中id,username,email

-- 查询provinces表proName

-- 采用内连接的方式

SELECT c.id, c.username, c.email, p.proName

FROM cms_user AS c

INNER JOIN provinces AS p

WHERE c.proId = p.id;

SELECT c.id, c.username, c.email, p.proName

FROM provinces AS p

CROSS JOIN cms_user AS c

WHERE c.proId = p.id;

外连接查询

左外连接

LEFT [OUTER] JOIN

SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

-- 插入错误数据

INSERT cms_user(username,password,regTime,proId)

VALUES('TEST2','TEST2','1381203974',20);

-- 查询cms_user表中id,username,email

-- 查询provinces表proName

-- 左外连接

SELECT c.id, c.username, c.email, p.proName FROM cms_user AS c

LEFT JOIN provinces AS p

ON c.proId = p.id;

右外连接

RIGHT [OUTERRIGHT [OUTER] JOIN] JOIN

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

-- 删除左侧数据

DELETE FROM cms_user WHERE proId=5;

-- 查询cms_user表中id,username,email

-- 查询provinces表proName

-- 右外连接

SELECT c.id, c.username, c.email, p.proName FROM cms_user AS c

RIGHT JOIN provinces AS p

ON c.proId = p.id;

外键

外键是表的一个特殊字段。被参照的表是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。

外键的作用：

外键的作用保持数据的一致性和完整性。

可以实现一对一或一对多的关系。

外键需要注意：

父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

数据表的存储引擎只能为InnoDB。

外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。

外键约束的参照操作：

CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。

SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。

RESTRICT：拒绝对父表的删除或更新操作。

NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。

-- 创建部门表department(主表)

-- id depName

CREATE TABLE IF NOT EXISTS department

(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

depName VARCHAR(20) NOT NULL UNIQUE

) ENGINE = INNODB;

INSERT department(depName)

VALUES ('教学部'),

('市场部'),

('运营部'),

('督导部');

-- 创建员工表employee(子表)

-- id ,username ,depId

CREATE TABLE IF NOT EXISTS employee

(

id SMALLINT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL UNIQUE,

depId TINYINT UNSIGNED,

FOREIGN KEY (depId) REFERENCES department (id)

) ENGINE = INNODB;

INSERT employee(username, depId)

VALUES ('king', 1),

('queen', 2),

('张三', 3),

('李四', 4),

('王五', 1);

-- 删除主表中的记录

DELETE

FROM department

WHERE id = 1;

-- 删除employee中的属于1部门的人

DELETE

FROM employee

WHERE depId = 1;

INSERT employee(username, depId)

VALUES ('test', 11);

DROP TABLE employee;

DROP TABLE department;

CREATE TABLE IF NOT EXISTS department

(

id TINYINT UNSIGNED AUTO_INCREMENT KEY,

depName VARCHAR(20) NOT NULL UNIQUE

) ENGINE = INNODB;

INSERT department(depName)

VALUES ('教学部'),

('市场部'),

('运营部'),

('督导部');

-- 创建员工表employee(子表)

-- id ,username ,depId

CREATE TABLE IF NOT EXISTS employee

(

id SMALLINT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL UNIQUE,

depId TINYINT UNSIGNED,

FOREIGN KEY (depId) REFERENCES department (id) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE = INNODB;;

INSERT employee(username, depId)

VALUES ('king', 1),

('queen', 2),

('张三', 3),

('李四', 4),

('王五', 1);

SELECT * FROM department;

SELECT * FROM employee;

-- 删除部门表中的第一个部门

DELETE FROM department WHERE id=1;

UPDATE department SET id=id+10;

-- 创建员工表employee(子表)

-- id ,username ,depId

CREATE TABLE IF NOT EXISTS employee(

id SMALLINT UNSIGNED AUTO_INCREMENT KEY,

username VARCHAR(20) NOT NULL UNIQUE,

depId TINYINT UNSIGNED,

FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL

)ENGINE=INNODB;

INSERT employee(username,depId) VALUES('king',1),

('queen',2),

('张三',3),

('李四',4),

('王五',1);

全连接

关键字：union /union all

(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )

(select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )

union语句注意事项：

通过union连接的SQL它们分别单独取出的列数必须相同；

不要求合并的表列名称相同时，以第一个sql 表列名为准；

使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；

被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；

(select id,name from A order by id) union all (select id,name from B order by id); 没有排序效果。

(select id,name from A ) union all (select id,name from B ) order by id; 有排序效果。

-- 联合查询测试

SELECT id,username FROM employee AS e

UNION SELECT id,username FROM cms_user AS c;

SELECT username FROM employee AS e

UNION SELECT username FROM cms_user AS c;

未经允许不得转载：作者:1495-冯同学，

转载或复制请以 超链接形式 并注明出处 拜师资源博客。