mysql进阶

阅读量

约束

外键约束
  • 让表和表之间产生关系，从而保证数据的准确性
建表时添加外键约束
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 创建user用户表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	NAME VARCHAR(20) NOT NULL             -- 姓名                                                                            
);
-- 添加用户数据
INSERT INTO USER VALUES (NULL,'张三'),(NULL,'李四'),(NULL,'王五');

-- 创建orderlist订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,    -- id
	number VARCHAR(20) NOT NULL,          -- 订单编号
	uid INT,                              -- 订单所属用户
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)   -- 添加外键约束
);
-- 添加订单数据
INSERT INTO orderlist VALUES (NULL,'hh001',1),(NULL,'hh002',1),
(NULL,'hh003',2),(NULL,'hh004',2),
(NULL,'hh005',3),(NULL,'hh006',3);

-- 添加一个订单，但是没有所属用户。无法添加
INSERT INTO orderlist VALUES (NULL,'hh007',8);
-- 删除王五这个用户，但是订单表中王五还有很多个订单呢。无法删除
DELETE FROM USER WHERE NAME='王五';
建表后添加外键约束
1
2
-- 建表后添加外键
alter table orderlist ADD constraint ou_fk1 foreign key (uid) references `USER`(id);
删除外键约束
1
2
-- 删除外键约束
alter table orderlist drop foreign key ou_fk1;

多表查询

数据准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;

-- 创建user表
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 用户id
	NAME VARCHAR(20),			        -- 用户姓名
	age INT                             -- 用户年龄
);
-- 添加数据
INSERT INTO USER VALUES (1,'张三',23);
INSERT INTO USER VALUES (2,'李四',24);
INSERT INTO USER VALUES (3,'王五',25);
INSERT INTO USER VALUES (4,'赵六',26);


-- 订单表
CREATE TABLE orderlist(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 订单id
	number VARCHAR(30),					-- 订单编号
	uid INT,    -- 外键字段
	CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'hm001',1);
INSERT INTO orderlist VALUES (2,'hm002',1);
INSERT INTO orderlist VALUES (3,'hm003',2);
INSERT INTO orderlist VALUES (4,'hm004',2);
INSERT INTO orderlist VALUES (5,'hm005',3);
INSERT INTO orderlist VALUES (6,'hm006',3);
INSERT INTO orderlist VALUES (7,'hm007',NULL);


-- 商品分类表
CREATE TABLE category(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
	NAME VARCHAR(10)                    -- 商品分类名称
);
-- 添加数据
INSERT INTO category VALUES (1,'手机数码');
INSERT INTO category VALUES (2,'电脑办公');
INSERT INTO category VALUES (3,'烟酒茶糖');
INSERT INTO category VALUES (4,'鞋靴箱包');


-- 商品表
CREATE TABLE product(
	id INT PRIMARY KEY AUTO_INCREMENT,   -- 商品id
	NAME VARCHAR(30),                    -- 商品名称
	cid INT, -- 外键字段
	CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id)
);
-- 添加数据
INSERT INTO product VALUES (1,'华为手机',1);
INSERT INTO product VALUES (2,'小米手机',1);
INSERT INTO product VALUES (3,'联想电脑',2);
INSERT INTO product VALUES (4,'苹果电脑',2);
INSERT INTO product VALUES (5,'中华香烟',3);
INSERT INTO product VALUES (6,'玉溪香烟',3);
INSERT INTO product VALUES (7,'计生用品',NULL);


-- 中间表
CREATE TABLE us_pro(
	upid INT PRIMARY KEY AUTO_INCREMENT,  -- 中间表id
	uid INT, -- 外键字段。需要和用户表的主键产生关联
	pid INT, -- 外键字段。需要和商品表的主键产生关联
	CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),
	CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);
笛卡尔积查询
1
2
-- 查询user表和orderlist表
SELECT * FROM user,orderlist;
多表查询-内连接查询

  • 内连接查询的是两张表有交集的部分数据（有主外键关联的数据）

  • 显示内连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查询用户信息和对应的订单信息
SELECT * FROM `USER` INNER JOIN
orderlist ON `USER`.id=orderlist.uid;

-- 查询用户信息和对应的订单信息 起别名
SELECT * FROM `USER` u JOIN orderlist o ON u.id=o.uid;

-- 查询用户姓名，年龄。和订单编号
SELECT
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u	
JOIN
	orderlist o
ON
	u.id=o.uid;
  • 隐式内连接
1
2
3
4
5
6
7
8
9
10
-- 查询用户姓名，年龄。和订单编号
SELECT
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u,
	orderlist o
where
	u.id=o.uid;
多表查询-外连接查询
  • 左外连接
    • 查询左表的全部数据，和左右两张表有交集部分的数据
1
2
3
4
5
6
7
8
9
10
11
-- 查询用户所有信息，以及用户对应的订单信息
SELECT
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u
LEFT JOIN
	orderlist o
ON 
	u.id=o.uid;
  • 右外连接
    • 查询右表的全部数据，和左右两张表有交集部分的数据
1
2
3
4
5
6
7
8
9
10
11
-- 	查询所有订单信息，以及订单所属的用户信息
SELECT
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u
LEFT JOIN
	orderlist o
ON 
	u.id=o.uid;
多表查询-子查询
  • 查询语句中嵌套了查询语句–成为子查询
  • 可以作为条件，使用运算符进行判断
  • 结果是单行单列的
1
2
3
4
5
6
7
-- 查询年龄最高的用户姓名
SELECT MAX(age) FROM `USER`;

-- 根据查询出来的最高年龄，查询姓名和年龄
SELECT `NAME`,age FROM `USER` WHERE age=26;

SELECT `NAME`,age FROM `USER` WHERE age = (SELECT MAX(age) FROM `USER`);
  • 子查询- 结果是多行多列的
    • 可以作为条件，使用运算符in或not in 进行判断！
1
2
3
4
5
6
7
-- 查询张三和李四的订单信息
SELECT id FROM `USER` WHERE `NAME`='张三' OR `NAME`='李四'; --查询张三和李四的id

-- 根据查找出来的id进而查询订单
SELECT number,uid FROM orderlist where uid=1 OR uid=2;

SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM `USER` WHERE `NAME`='张三' OR `NAME`='李四');
  • 子查询-结果是多行多列的
    • 可以作为一张虚拟表参与查询
1
2
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM `USER` u,(SELECT * FROM orderlist where id>4) o WHERE u.id=o.uid;

多表查询练习

查询用户的编号、姓名、年龄、订单编号
1
2
3
4
5
6
7
8
9
10
SELECT
	u.id,
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u,
	orderlist o
WHERE
	u.id=o.uid;
查询所有的用户。用户的编号、姓名、年龄，订单编号
  • 左外连接
1
2
3
4
5
6
7
8
9
10
11
SELECT
	u.id,
	u.NAME,
	u.age,
	o.number
FROM
	`USER` u
LEFT JOIN
	orderlist o
ON 
	u.id=o.uid;
查询所有订单，用户的编号、姓名、年龄、订单编号
  • 右外连接
1
2
3
4
5
6
7
8
9
10
11
SELECT
	u.id,
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u
RIGHT JOIN
	orderlist o
ON
	u.id=o.uid;
查询用户年龄大于23岁的信息。显示用户的编号、姓名、年龄。订单编号
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
	u.id,
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u

LEFT JOIN
	orderlist o
ON
	u.id = o.uid
WHERE
	u.age>23;
查询张三和李四用户的信息。显示用户的编号、姓名、年龄。订单编号
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	u.id,
	u.`NAME`,
	u.age,
	o.number
FROM
	`USER` u,
	orderlist o
WHERE
	u.id=o.uid
	AND 
	u.NAME IN('张三','李四');
赏

谢谢你请我吃糖果

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true

一枚17级软件工程学生<br><br>愿待java如初恋<br>也希java诚待我