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

 DML(data manipulation language)：

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

DDL(data definition language)：

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

sql crud 基本语句使用

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;

 判空显示内容

select ifnull(salary,"显示内容"),ifnull(salary,0)*12 年薪 from users;

 

使用root用户登录,创建新用户rabbit 密码 123

 create user rabbit identified by '123';

 

 备份与恢复单个数据库的命令

C:\WINDOWS\system32>mysqldump -uroot -p gjp > d:/gjp.sql
Enter password: ****

C:\WINDOWS\system32>mysql -uroot -p gjp < d:/gjp.sql
Enter password: ****

备份与恢复多个数据库命令

C:\WINDOWS\system32>mysqldump -uroot -p --databases gjp gjj > d:/gjj.sql
Enter password: ****

C:\WINDOWS\system32>mysql -uroot -p < d:/gjj.sql
Enter password: ****

备份所有表

C:\WINDOWS\system32>mysqldump -uroot -p --all-databases > d:/all.sql
Enter password: ****

 

自关联

--使用--自关联--方式，表达一对多关系
--创建一个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);

多表联查 子查询 内联查 inner join on 左外联查 left outer join on 右外联查 right outer join on  (inner 和 outer可以省略)

-- 多表查询练习
        -- 创建分类表：
        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);

 

SELECT PASSWORD FROM sw_user WHERE username = ( SELECT username FROM sw_user WHERE id = 2 ); 

子查询 只对select直接有效，把查询结果作为条件再次查询

子查询 对delete并不直接有效，需要把查询结果起别名，例如 t，当成1张表，

对这张表中的字段再次select出来的，例如id才可以删除。

子查询中的完整语句不要用“；”结束

mysql面试题 truncate与delete的区别

TRUNCATE TABLE department; 削平 摧毁表,id重新开始
DELETE FROM department; 数据删除,但id继续在原基础上增加