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

2022-11-02 22:44:16

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 = );

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

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

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

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

mysql面试题 truncate与delete的区别

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

码农公寓

相关文章