MySQL数据库 crud语句 ifnull() 创建新账户 备份数据库 一对多关系 多对多(中间表) 外键约束 自关联 子查询注意事项 DML DDL DQL mysql面试题 truncate与delete的区别

CREATE DATABASE swift_db;
USE swift_db;
SHOW DATABASES;

DROP DATABASE swift_db;

SHOW CREATE DATABASE swift_db;

CREATE TABLE test1(
    tid INT PRIMARY KEY,
    tdate DATE);

DROP TABLE test1;
DESC category;

ALTER TABLE category ADD cdesc VARCHAR(20);
ALTER TABLE category MODIFY cdesc VARCHAR(50);

ALTER TABLE category DROP description;

RENAME TABLE category TO category2;

INSERT INTO category
            (cid,
             cname)
VALUES ('c001',
        '电器');

UPDATE category
SET cname = '小明'
WHERE cid = 'c001';

ALTER TABLE p4 DROP INDEX `primary`;
ALTER TABLE `swift_db`.`p5` DROP INDEX `PRIMARY`;
ALTER TABLE p5 ADD CONSTRAINT UNIQUE (NAME);
ALTER TABLE p5 DROP INDEX `name`;
ALTER TABLE p4 ADD CONSTRAINT PRIMARY KEY (id);
ALTER TABLE p4 ADD CONSTRAINT UNIQUE (p3name);
ALTER TABLE p4 ADD age INT;
ALTER TABLE p4 ADD CONSTRAINT UNIQUE (age);
ALTER TABLE p4 DROP INDEX age;
ALTER TABLE p4 DROP INDEX p3name;
ALTER TABLE p4 DROP INDEX `primary`;
ALTER TABLE p4 ADD CONSTRAINT UNIQUE (age);
ALTER TABLE p4 DROP INDEX age;
ALTER TABLE p4 CHANGE p3name p3name VARCHAR(30) NOT NULL;
   
DELETE FROM product WHERE NAME='电视机';

TRUNCATE product;
DELETE FROM product;

--使用--自关联--方式，表达一对多关系
--创建一个AREA，来进行省市的自关联。
CREATE TABLE AREA(
id INT PRIMARY KEY,
NAME VARCHAR(20),
description VARCHAR(20),
parent_id INT
);
--  要求自关联前，要填入数据
数据准备：（自关联必须有数据才能添加外键约束）
INSERT INTO AREA VALUES (1,'河北省','河北省',NULL);
INSERT INTO AREA VALUES (2,'保定市','保定市',1);
INSERT INTO AREA VALUES (3,'石家庄市','石家庄市',1);

--自关联,自我约束
ALTER TABLE AREA ADD CONSTRAINT fk_area FOREIGN KEY (parent_id) REFERENCES AREA (id);

        CREATE TABLE category(
            cid VARCHAR(32) PRIMARY KEY,#主键ID
            cname VARCHAR(20)#分类名称
        );
        
        INSERT INTO category VALUES('c001','电脑办公');
        INSERT INTO category VALUES('c002','服装');
        -- 创建商品表：
        
        CREATE TABLE product(
            pid INT PRIMARY KEY,#主键ID
            pname VARCHAR(20),#商品名称
            price DOUBLE,#商品价格
            category_cid VARCHAR(32)#外键
        );
        
        -- 为product表 添加外键约束
        ALTER TABLE product ADD CONSTRAINT p_fk FOREIGN KEY (category_cid) REFERENCES category(cid);
        -- 导入数据
        INSERT INTO product(pid,pname,price,category_cid) VALUES(1,'联想电脑',5000,'c001');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(2,'海尔电脑',3000,'c001');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(3,'雷神电脑',5000,'c001');
        
        INSERT INTO product(pid,pname,price,category_cid) VALUES(4,'JACK JONES',800,'c002');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(5,'真维斯',200,'c002');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(6,'花花公子',440,'c002');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(7,'劲霸',2000,'c002');

多对多关系（用户与角色）
--创建用户表
CREATE TABLE USER(
uid INT PRIMARY KEY,
username VARCHAR(30),
PASSWORD VARCHAR(30)
);

--创建角色
CREATE TABLE role(
rid INT PRIMARY KEY,
NAME VARCHAR(30)
);

--创建中间表
CREATE TABLE user_role(
u_uid INT,
r_rid INT
);

--建立关联关系：    
--1）为中间表u_uid添加外键    
ALTER TABLE user_role ADD CONSTRAINT fk_ur_uid FOREIGN KEY (u_uid) REFERENCES USER (uid);
--2）为中间表r_rid添加外键
ALTER TABLE user_role ADD CONSTRAINT fk_ur_rid FOREIGN KEY (r_rid) REFERENCES role (rid);

-- 多表查询练习
        -- 创建分类表：
        CREATE TABLE category(
            cid VARCHAR(32) PRIMARY KEY,#主键ID
            cname VARCHAR(20)#分类名称
        );
        
        INSERT INTO category VALUES('c001','电脑办公');
        INSERT INTO category VALUES('c002','服装');
        -- 创建商品表：
        
        CREATE TABLE product(
            pid INT PRIMARY KEY,#主键ID
            pname VARCHAR(20),#商品名称
            price DOUBLE,#商品价格
            category_cid VARCHAR(32)#外键
        );
        
        -- 为product表 添加外键约束
        ALTER TABLE product ADD CONSTRAINT p_fk FOREIGN KEY (category_cid) REFERENCES category(cid);
        -- 导入数据
        INSERT INTO product(pid,pname,price,category_cid) VALUES(1,'联想电脑',5000,'c001');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(2,'海尔电脑',3000,'c001');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(3,'雷神电脑',5000,'c001');
        
        INSERT INTO product(pid,pname,price,category_cid) VALUES(4,'JACK JONES',800,'c002');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(5,'真维斯',200,'c002');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(6,'花花公子',440,'c002');
        INSERT INTO product(pid,pname,price,category_cid) VALUES(7,'劲霸',2000,'c002');

-- 写出交叉查询的SQL
     -- 答：交叉连接查询(基本不会使用-得到的是两个表的乘积，笛卡尔积)
        SELECT * FROM category c , product p;
        
-- 写出两种 内连接查询的SQL (使用的关键字 inner join  -- inner可以省略)
    -- 隐式内连接：
        SELECT * FROM category c, product p WHERE c.cid=p.category_cid;
    -- 显示内连接：
        SELECT * FROM category c INNER JOIN product p ON c.cid=p.category_cid;
    -- 注意：内连接书写极为简单，效率不高，内存占用率偏高
        
-- 外连接查询(使用的关键字 outer join -- outer可以省略)
    -- 左外连接：left outer join
        SELECT * FROM product p LEFT OUTER JOIN category c ON p.category_cid=c.cid;
    -- 右外连接：right outer join    
        SELECT * FROM product p RIGHT OUTER JOIN category c ON p.category_cid=c.cid;        
-- 子查询 （将其他select的查询结果，作为select的查询条件。）
    --    格式：进行SELECT嵌套。
    --    例如：SELECT 字段 FROM 表名 WHERE (SELECT 字段 FROM 表名…..)…..
    --    例如：SELECT 字段 FROM (SELECT 字段 FROM 表);
    
    --#查询最贵的商品信息
    SELECT * FROM product WHERE price=(SELECT MAX(price) FROM product);