mysql语法进阶

一、MySQL的约束

1.1主键约束；PRIMARY KEY

 

 

 

 1.2主键自增约束；PRIMARY KEY AUTO_INCREMENT

 

 

 

1.3 唯一约束；UNIQUE

 

 

 

 

 

1.4 非空约束; NOT NULL

 

 

 

 

 

1.5 外键约束

 

 

/*
    外键约束
    标准语法：
        CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主键名(主表主键列名)；
*/

DROP TABLE user;--会报错，直接删除
DROP TABLE orderlist;
-- 建表时添加外键约束
-- 创建user用户表
CREATE TABLE USER(
    id INT PRIMARY KEY AUTO_INCREMENT,    -- id
    NAME VARCHAR(20) NOT NULL             -- 姓名
);
-- 添加用户数据
INSERT INTO USER VALUES (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,'hm001',1),(NULL,'hm002',1),
(NULL,'hm003',2),(NULL,'hm004',2);

 

 

 

 二、MySQL多表设计

2.1表关系一对一

 

 

 

2.2 表关系一对多

 

 2.3表关系多对多

 

三、 MySQL多表查询

 

 3.1内连接查询

/*
    显示内连接
    标准语法：
        SELECT 列名 FROM 表名1 [INNER] JOIN 表名2 ON 关联条件;
*/
-- 查询用户信息和对应的订单信息
SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = user.id;


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


-- 查询用户姓名，年龄。和订单编号
SELECT
    u.name,        -- 用户姓名
    u.age,        -- 用户年龄
    o.number    -- 订单编号
FROM
    USER u          -- 用户表
INNER JOIN
    orderlist o     -- 订单表
ON
    o.uid=u.id;

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

3.2外连接查询

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

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

3.3子查询

 

 

/*
    结果是单行单列的
    标准语法：
        SELECT 列名 FROM 表名 WHERE 列名=(SELECT 列名 FROM 表名 [WHERE 条件]);
*/
-- 查询年龄最高的用户姓名
SELECT MAX(age) FROM USER;
SELECT NAME,age FROM USER WHERE age=(SELECT MAX(age) FROM USER);

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