mysql|MySQL进阶语法 mysql|sql

MySQL进阶语法约束外键约束
作用：让表和表之间产生关联关系，从而保证数据的准确性！
建表时添加外键约束语法：

CREATE TABLE 表名( 列名数据类型约束,-- id number VARCHAR(20) NOT NULL,-- number ...-- 外键列 CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主表主键列名) );

举例：

CREATE TABLE orderList( id INT PRIMARY KEY AUTO_INCREMENT,-- id number VARCHAR(20) NOT NULL,-- number uid INT,-- 外键列 CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) );

删除外键约束语法：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

举例：

ALTER TABLE orderList DROP FOREIGN KEY ou_fk1;

建表后单独添加外键约束语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES USER(主键列名);

举例：

ALTER TABLE orderList ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id);

外键的级联更新和级联删除
级联更新：当主表中的数据进行修改时，从表中相关联的数据也会随之修改。
级联删除：当主表中的数据进行删除时，从表中有关联的数据也会随之删除。
添加级联更新

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES USER(主键列名) ON UPDATE CASCADE;

添加级联删除

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES USER(主键列名) ON DELETE CASCADE;

同时添加级联更新和级联删除语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES USER(主键列名) ON UPDATE CASCADE ON DELETE CASCADE;

举例：

-- 添加外键约束，同时添加级联更新和级联删除 ALTER TABLE orderList ADD CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) ON UPDATE CASCADE ON DELETE CASCADE;

多表操作多表概念：就是多张数据表，然而表与表之间是有一定的关联关系的，这种关联关系通过外键约束实现。
多表分类：一对一、一对多、多对多
一对一
适用场景： ? 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人。
建表原则： ? 在任意一个表建立外键，去关联另外一张表的主键。
举例：

-- 创建一个testdb06的库 CREATE DATABASE testdb06; -- 使用testdb06 use testdb06; -- 创建person表 CREATE TABLE person( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id name VARCHAR(20)-- 姓名 ); -- 添加数据 INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四'); -- 创建card表 CREATE TABLE card( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id number VARCHAR(20) UNIQUE NOT NULL,-- 身份证号 pid INT UNIQUE,-- 外键列 CONSTRAINT cp_fk1FOREIGN KEY (pid) REFERENCES person(id) ); -- 添加数据 INSERT INTO card VALUES(NULL,'12345',1),(NULL,'564789',2);

-- 创建card表 CREATE TABLE card( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id number VARCHAR(20) UNIQUE NOT NULL,-- 身份证号 pid INT UNIQUE,-- 外键列 CONSTRAINT cp_fk1FOREIGN KEY (pid) REFERENCES person(id) )

模型图：

文章图片

一对多
适用场景： ? 用户和订单。一个用户可以有多个订单。
【mysql|MySQL进阶语法】? 商品分类和商品。一个分类可以有多个商品。
建表原则： ? 在多的一方，建立外键约束，去关联一的一方主键。
举例：

-- 创建测试数据库 CREATE DATABASE testdb07; use test07; -- 创建category表 CREATE TABLE category( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id name VARCHAR(10)-- 分类名称 ); -- 添加数据 INSERT INTO category VALUES(NULL,'手机数码'),(NULL,'电脑办公'); -- 创建product表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id name VARCHAR(30),-- 商品名称 cid INT,-- 外键列 CONSTRAINT pc_fk1 FOREIGN key (cid) REFERENCES category(id) ); -- 添加数据 INSERT INTO product VALUES(NULL,'华为P30',1),(NULL,'小米note3',1),(NULL,'联想电脑',2),(NULL,'苹果电脑',2); SELECT * FROM category; SELECT * FROM product;

添加外键约束的SQL

-- 创建product表 CREATE TABLE product( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id name VARCHAR(30),-- 商品名称 cid INT,-- 外键列 CONSTRAINT pc_fk1 FOREIGN key (cid) REFERENCES category(id) );

多对多
适用场景：学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择。
建表原则： ? 需要借助第三张中间表，中间表至少包含两列。这两列作为中间表的外键，分别关联两张表的主键。
举例：

-- 创建测试数据库 CREATE DATABASE testdb08; -- 使用测试数据库 use testdb08; -- 创建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id name VARCHAR(20)-- 学生姓名 ); -- 添加数据 INSERT INTO student VALUES(NULL,'张三'),(NULL,'李四'); -- 创建course表 CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id name VARCHAR(20)-- 课程名称 ); -- 添加数据 INSERT INTO course VALUES(NULL,'语文'),(NULL,'数学'); -- 创建中间表 CREATE TABLE stu_cur( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id sid INT,-- 用于和student表中的id进行外键关联 cid INT,-- 用于和course表中的id进行外键关联 CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student (id), -- 添加外键约束 CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course (id)-- 添加外键约束 ); -- 添加数据 INSERT INTO stu_cur VALUES(NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);

添加外键约束的SQL

-- 创建中间表 CREATE TABLE stu_cur( id INT PRIMARY KEY AUTO_INCREMENT,-- 主键id sid INT,-- 用于和student表中的id进行外键关联 cid INT,-- 用于和course表中的id进行外键关联 CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student (id), -- 添加外键约束 CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course (id)-- 添加外键约束 );

多表查询多表查询分类
内连接查询
外连接查询
子查询
自关联查询
多表查询数据准备

-- 创建测试数据库 CREATE DATABASE testdb09; -- 使用数据库 use testdb09; -- 创建USER表 CREATE TABLE IF NOT EXISTS 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 ( idINT 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); -- 创建category商品分类表 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);

内连接查询查询原理： ? 内连接查询的是两张表有交集的部分数据（有主外键关联的数据）。
查询语法显示内连接

SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 条件;

举例：

/* 显示内连接标准语法： SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件; */-- 查询用户信息和对应的订单信息 SELECT * FROM USER INNER JOIN orderlist ON USER.id=orderlist.uid; -- 查询用户信息和对应的订单信息，起别名 SELECT * FROM USER u INNER JOIN orderlist o ON u.id=o.uid; -- 查询用户姓名，年龄。和订单编号 SELECT u.name,-- 用户姓名 u.age,-- 用户年龄 o.number-- 订单编号 FROM USER u-- 用户表 INNER JOIN orderlist o -- 订单表 ON u.id=o.uid;

隐式内连接

SELECT 列名 FROM 表名1,表名2 WHERE 条件;

举例：

/* 隐式内连接标准语法： SELECT 列名 FROM 表名1,表名2 WHERE 关联条件; */-- 查询用户姓名，年龄和订单编号 SELECT u.name, u.age, o.number FROM USER u, orderlist o WHERE u.id=o.uid;

外连接查询左外连接：查询原理
? 查询左表的全部数据，和左右两张表有交集部分的数据
查询语法

SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件;

举例：

/* 左外链接：标准语法: SELECT 列名 FROM 表名1 LEFT [OUTER] JOIN 表名2 ON 条件; */ -- 查询所有用户信息，以及用户对应的订单信息 SELECT u.*, o.number FROM USER u LEFT OUTER JOIN orderlist o ON u.id = o.uid;

右外连接：查询原理
? 查询右表的全部数据，和左右两张表有交集部分的数据。
查询语法

SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件;

举例：

/* 右外链接：标准语法: SELECT 列名 FROM 表名1 RIGHT [OUTER] JOIN 表名2 ON 条件; */ -- 查询所有订单信息，以及订单所属的用户信息 SELECT o.*, u.NAME FROM USER u RIGHT JOIN orderlist o ON o.uid = u.id;

子查询子查询概念
? 查询语句中嵌套了查询语句，我们就将嵌套的查询称为子查询。
子查询分类：结果是单行单列的查询作用 ? 可以将查询的结果作为另一条语句的查询条件，使用运算符判断。= > >= < <=等等。
查询语法

SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);

举例：

/* 结果是单行单列的标准语法： SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]); */ -- 查询年龄最高的用户姓名 SELECT MAX(age) FROM USER; SELECT name,age FROM USER WHERE age=(SELECT MAX(age) FROM USER); -- 小括号可以提升执行的优先级

结果是多行单列的查询作用 ? 可以作为条件，使用运算符IN或者NOT IN 进行判断。
查询语法

SELECT 列名 FROM 表名 WHERE [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]);

举例：

/* 结果是多行单列的标准语法： SELECT 列名 FROM 表名 WHERE [NOT] IN (SELECT 列名 FROM 表名 [WHERE 条件]); */-- 查询张三和李四的订单信息 SELECT * FROM orderlist WHERE uid IN (1,2); SELECT id FROM USER WHERE name IN ('张三','李四'); SELECT * FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE name IN ('张三','李四'));

结果是多行多列的查询作用 ? 查询的结果可以作为一张虚拟表参与查询。
查询语法

SELECT 列名 FROM 表名[别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件];

举例：

/* 结果是多行多列的标准语法： SELECT 列名 FROM 表名[别名],(SELECT 列名 FROM 表名 [WHERE 条件]) [别名] [WHERE 条件]; */-- 查询订单表中id大于4的订单信息和所属用户信息 SELECT * FROM orderlist WHERE id>4; SELECT u.name, o.number FROM USER u, (SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;

自关联查询自关联查询概念
? 在同一张表中数据有关联性，我们可以把这张表当成多个表来查询。
举例：数据准备

-- 创建员工表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号 NAME VARCHAR(20),-- 员工姓名 mgr INT,-- 上级编号 salary DOUBLE-- 员工工资 ); -- 添加数据 INSERT INTO employee VALUES (1001,'孙悟空',1005,9000.00), (1002,'猪八戒',1005,8000.00), (1003,'沙和尚',1005,8500.00), (1004,'小白龙',1005,7900.00), (1005,'唐僧',NULL,15000.00), (1006,'武松',1009,7600.00), (1007,'李逵',1009,7400.00), (1008,'林冲',1009,8100.00), (1009,'宋江',NULL,16000.00);

-- 查询所有员工的姓名及其直接上级的姓名，没有上级的员工也需要查询 SELECT e1.id, e1.name, e1.mgr, e2.id, e2.name FROM employee e1 LEFT OUTER JOIN employee e2 ON e1.mgr=e2.id;

多表查询练习
1.查询用户的编号、姓名、年龄。订单编号

-- 1.查询用户的编号、姓名、年龄。订单编号 /* 分析用户的编号、姓名、年龄 USER表订单编号 orderlist表条件：USER.id=orderlist.uid */ -- 隐式内连接写法 SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id=o.uid; -- 显示内连接写法 SELECT u.*, o.number FROM USER u INNER JOIN orderlist o WHERE u.id=o.uid;

2.查询所有的用户。用户的编号、姓名、年龄、订单编号

-- 2.查询所有的用户。用户的编号、姓名、年龄、订单编号 /* 分析：用户的编号、姓名、年龄、订单编号 USER表订单编号 orderlist表条件：USER.id=orderlist.uid 查询所有的用户，左外连接 */ -- 左外连接查询写法 SELECT u.*, o.number FROM USER u LEFT OUTER JOIN orderlist o ON u.id=o.uid; -- 右外连接查询写法 SELECT u.*, o.number FROM orderlist o RIGHT OUTER JOIN USER u ON o.uid=u.id;

3.查询所有的订单。用户的编号、姓名、年龄、订单编号

-- 3.查询所有的订单。用户的编号、姓名、年龄、订单编号 /* 分析：用户的编号、姓名、年龄、订单编号 USER表订单编号 orderlist表条件：USER.id=orderlist.uid 查询所有的订单，右外连接 */ -- 右外连接查询写法 SELECT u.*, o.number FROM USER u RIGHT JOIN orderlist o ON u.id=o.uid; -- 左外连接查询写法 SELECT u.*, o.number FROM orderlist o LEFT JOIN USER u ON u.id=o.uid;

4.查询年龄大于23岁的信息，显示用户的编号、姓名、年龄、订单编号

-- 4.查询年龄大于23岁的信息，显示用户的编号、姓名、年龄、订单编号 /* 分析：用户的编号、姓名、年龄、订单编号 USER表订单编号 orderlist表条件：USER.id=orderlist.uid AND USER.age > 23 */ SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.id IN(SELECT id FROM USER WHERE age>23); -- 年龄大于23的USER id SELECT id FROM USER WHERE age>23; SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.age>23;

5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄、订单编号。

-- 5.查询张三和李四用户的信息。显示用户的编号、姓名、年龄、订单编号。 /* 分析：用户的编号、姓名、年龄、订单编号 USER表订单编号 orderlist表条件：USER.id=orderlist.uid AND USER.name IN ('张三','李四') */ SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.id IN(SELECT id FROM USER WHERE name IN('张三','李四')); SELECT u.*, o.number FROM USER u, orderlist o WHERE u.id=o.uid AND u.name IN ('张三','李四');

6.查询商品分类的编号、分类名称、分类下的商品名称

-- 6.查询商品分类的编号、分类名称、分类下的商品名称 /* 分析：商品分类的编号、分类的名称 category表商品名称 product表条件：category.id=product.cid */ SELECT c.id, c.name, p.name FROM category c, product p WHERE c.id=p.cid; SELECT c.id c_id, c.`name` c_name, p.`name` p_name FROM `category` c INNER JOIN `product` p ON c.id=p.cid;

7.查询所有的商品分类。商品分类的编号、分类的名称。分类下的商品名称

-- 7.查询所有的商品分类。商品分类的编号、分类的名称。分类下的商品名称 /* 分析：商品分类的编号、分类的名称 category表商品名称product表条件：category.id=product.cid */ SELECT c.id, c.name, p.name FROM category c LEFT OUTER JOIN product p ON c.id=p.cid; SELECT c.id, c.name, p.name FROM product p RIGHT OUTER JOIN category c ON p.cid=c.id;

8.查询所有的商品信息。商品分类的编号、分类的名称、分类下的商品名称

-- 8.查询所有的商品信息。商品分类的编号、分类的名称、分类下的商品名称 /* 分析：商品分类的编号、分类名称 category表商品名称product表条件：category.id=product.cid 查询所有的商品信息，右外连接 */ SELECT c.id, c.name cname, p.name pname FROM product p LEFT OUTER JOIN category c ON p.cid=c.id; SELECT c.id, c.name cname, p.name pname FROM category c RIGHT OUTER JOIN product p ON p.cid=c.id;

9.查询所有的用户和该用户能看到的所有的商品。显示用户的编号、姓名、年龄。商品名称

-- 9.查询所有的用户和该用户能看到的所有的商品。显示用户的编号、姓名、年龄。商品名称 /* 分析：用户的编号、姓名、年龄 USER表商品名称product表中间表us_pro 条件：us_pro.uid=USER.id AND us_pro.pid=product.id */ SELECT u.id, u.name, u.age, p.name FROM us_pro up, USER u, product p WHERE up.uid=u.id AND up.pid=p.id;

10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称

-- 10.查询张三和李四这两个用户可以看到的商品。显示用户的编号、姓名、年龄。商品名称 /* 分析：用户的编号、姓名、年龄 USER表商品名称product表中间表us_pro 条件：us_pro.pid=product.id AND us_pro.uid=USER.id AND USER.name IN('张三','李四') */ SELECT u.id, u.name, u.age, o.pname FROM USER u, (SELECT u.id, u.name uname, u.age, p.name pname FROM us_pro up, USER u, product p WHERE up.uid=u.id AND up.pid=p.id) o WHERE o.uname IN ('张三','李四') AND u.name IN('张三','李四') AND o.uname=u.name; SELECT u.id, u.name, u.age, p.name FROM us_pro up, USER u, product p WHERE up.uid=u.id AND up.pid=p.id AND u.name IN('张三','李四');