MYSQL--数据定义语言DDL/数据管理语言DML

最新推荐文章于 2022-04-28 20:51:33 发布

_twinkle_

最新推荐文章于 2022-04-28 20:51:33 发布

阅读量276

点赞数 1

分类专栏： MYSQL 每天学习8h 文章标签： mysql 数据库 sql

原文链接：https://www.bilibili.com/video/BV1xW411u7ax?p=124&spm_id_from=pageDriver

版权

每天学习8h 同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

MYSQL

3 篇文章 0 订阅

订阅专栏

DML语言

数据操作语言：

插入：insert

修改：update

删除：delete

#一、插入语句

#方式一：经典的插入

语法：

insert into 表名(列名,...) values(值1,...);

SELECT * FROM beauty;

#1.插入的值的类型要与列的类型一致或兼容

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);

#2.不可以为null的列必须插入值。可以为null的列如何插入值？

#方式1：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(13,'唐艺昕','女','1990-4-23','1898888888',NULL,2);
#方式2：
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(15,'娜扎','女','1388888888');

#3.列的顺序可调换

INSERT INTO beauty(NAME,sex,id,phone)
VALUES('蒋欣','女',16,'110');

#4.列数和值的个数必须一致

INSERT INTO beauty(NAME,sex,id,phone)
VALUES('关晓彤','女',17,'110');

#5.可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

INSERT INTO beauty
VALUES(18,'张飞','男',NULL,'119',NULL,NULL);

#方式二：

语法：

insert into 表名

set 列名=值,列名=值,...

INSERT INTO beauty

SET id=19,NAME='刘涛',phone='999';

#两种方式大pk ★

#1、方式一支持插入多行,方式二不支持

INSERT INTO beauty
VALUES(23,'唐艺昕1','女','1990-4-23','1898888888',NULL,2)
,(24,'唐艺昕2','女','1990-4-23','1898888888',NULL,2)
,(25,'唐艺昕3','女','1990-4-23','1898888888',NULL,2);

#2、方式一支持子查询，方式二不支持

INSERT INTO beauty(id,NAME,phone)
SELECT 26,'宋茜','11809866';

INSERT INTO beauty(id,NAME,phone)
SELECT id,boyname,'1234567'
FROM boys WHERE id<3;

#二、修改语句

1.修改单表的记录★

语法：

update 表名

set 列=新值,列=新值,...

where 筛选条件;

2.修改多表的记录【补充】

语法：

sql92语法：

update 表1 别名,表2 别名

set 列=值,...

where 连接条件

and 筛选条件;

sql99语法：

update 表1 别名

inner|left|right join 表2 别名

on 连接条件

set 列=值,...

where 筛选条件;

#1.修改单表的记录

#案例1：修改beauty表中姓唐的女神的电话为13899888899

UPDATE beauty SET phone = '13899888899'
WHERE NAME LIKE '唐%';

#案例2：修改boys表中id好为2的名称为张飞，魅力值 10

UPDATE boys SET boyname='张飞',usercp=10
WHERE id=2;

#2.修改多表的记录

#案例 1：修改张无忌的女朋友的手机号为114

UPDATE boys bo
INNER JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`phone`='119',bo.`userCP`=1000
WHERE bo.`boyName`='张无忌';

#案例2：修改没有男朋友的女神的男朋友编号都为2号

UPDATE boys bo
RIGHT JOIN beauty b ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE bo.`id` IS NULL;
SELECT * FROM boys;

#三、删除语句

方式一：delete

语法：

1、单表的删除【★】

delete from 表名 where 筛选条件

2、多表的删除【补充】

sql92语法：

delete 表1的别名,表2的别名

from 表1 别名,表2 别名

where 连接条件

and 筛选条件;

sql99语法：

delete 表1的别名,表2的别名

from 表1 别名

inner|left|right join 表2 别名 on 连接条件

where 筛选条件;

方式二：truncate

语法：truncate table 表名;

#方式一：delete

#1.单表的删除

#案例：删除手机号以9结尾的女神信息

DELETE FROM beauty WHERE phone LIKE '%9';
SELECT * FROM beauty;

#2.多表的删除

#案例：删除张无忌的女朋友的信息

DELETE b
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id` = bo.`id`
WHERE bo.`boyName`='张无忌';

#案例：删除黄晓明的信息以及他女朋友的信息

DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='黄晓明';

#方式二：truncate语句 [不能加where，直接删表格]

#案例：将魅力值>100的男神信息删除

TRUNCATE TABLE boys ;

#delete pk truncate【面试题★】

1.delete 可以加where 条件，truncate不能加

2.truncate删除，效率高一丢丢

3.假如要删除的表中有自增长列，

如果用delete删除后，再插入数据，自增长列的值从断点开始，

而truncate删除后，再插入数据，自增长列的值从1开始。

4.truncate删除没有返回值，delete删除有返回值

5.truncate删除不能回滚，delete删除可以回滚.

SELECT * FROM boys;
DELETE FROM boys;
TRUNCATE TABLE boys;

INSERT INTO boys (boyname,usercp)
VALUES('张飞',100),('刘备',100),('关云长',100);

DDL

数据定义语言

库和表的管理

一、库的管理

创建、修改、删除

二、表的管理

创建、修改、删除

创建： create

修改： alter

删除： drop

#一、库的管理

#1、库的创建

语法：

create database [if not exists]库名;

#案例：创建库Books

CREATE DATABASE IF NOT EXISTS books ;

#2、库的修改

RENAME DATABASE books TO 新库名; # [不安全，已废弃]
#更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除

DROP DATABASE IF EXISTS books;

#二、表的管理

#1.表的创建 ★

语法：

create table 表名(

列名列的类型【(长度) 约束】,

...

列名列的类型【(长度) 约束】

)

#案例：创建表Book

CREATE TABLE book(
    id INT,#编号
    bName VARCHAR(20),#图书名
    price DOUBLE,#价格
    authorId  INT,#作者编号
    publishDate DATETIME #出版日期
);
DESC book; # 查看表

#案例：创建表author

CREATE TABLE IF NOT EXISTS author(
    id INT,
    au_name VARCHAR(20),
    nation VARCHAR(10)
)
DESC author;

#2.表的修改

语法

alter table 表名 add|drop|modify|change column 列名【列类型约束】;

#①修改列名 [ 需要把列的类型也加上 COLUMN可以省略]

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

#②修改列的类型或约束 [ COLUMN不可以省略]

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

#③添加新列 [ COLUMN不可以省略]

ALTER TABLE author ADD COLUMN annual DOUBLE;

#④删除列 [ COLUMN不可以省略]

ALTER TABLE book_author DROP COLUMN  annual;

#⑤修改表名

ALTER TABLE author RENAME TO book_author;
DESC book_author;

#3.表的删除

DROP TABLE IF EXISTS book_author;
SHOW TABLES; # 查看当前库所有表

#通用的写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

#4.表的复制

INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');

SELECT * FROM Author;

SELECT * FROM copy2;

#1.仅仅复制表的结构 [ 不包括内容]

CREATE TABLE copy LIKE author;

#2.复制表的结构+数据

CREATE TABLE copy2
SELECT * FROM author;

#只复制部分数据

CREATE TABLE copy3
SELECT id,au_name
FROM author
WHERE nation='中国';

#仅仅复制某些字段 [ 只要部分结构，不要数据]

CREATE TABLE copy4
SELECT id,au_name
FROM author
WHERE 0;

常见的数据类型

数值型：

整型

小数：

定点数

浮点数

字符型：

较短的文本：char、varchar

较长的文本：text、blob（较长的二进制数据）

日期型：

#一、整型

分类：

tinyint 1字节

smallint 2字节

mediumint 3字节

int/integer 4字节

bigint 8字节

特点：

① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加 unsigned关键字

② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值

③ 如果不设置长度，会有默认的长度[ 限制不了数值范围，只是显示宽度]

长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！

#1.如何设置无符号和有符号 [ 加上(7) 以及 ZEROFILL来限制填充长度后，自动变为无符号的int]

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
    t1 INT(7) ZEROFILL,
    t2 INT(7) ZEROFILL
);
DESC tab_int;

INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);
INSERT INTO tab_int VALUES(123,123);

SELECT * FROM tab_int;

#二、小数

分类：

1.浮点数型

float(M,D)

double(M,D)

2.定点数型[精确度高一点点]

decimal(M,D)或简写dec(M，D)

特点：

①

M：整数部位+小数部位

D：小数部位

如果超过范围，则插入临界值

②

M和D都可以省略

如果是decimal，则M默认为10，D默认为0

如果是float和double，则会根据插入的数值的精度来决定精度

③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

#测试M和D

DROP TABLE tab_float;
CREATE TABLE tab_float(
    f1 FLOAT(5,2),
    f2 DOUBLE(5,2),
    f3 DECIMAL(5,2)
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
INSERT INTO tab_float VALUES(123.456,123.456,123.456); # 限制小数位数四舍五入
INSERT INTO tab_float VALUES(123.4,123.4,123.4); # 保存为123.40
INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4); #保存为999.99 因为(5,2)时最大值就是这个

#原则：

所选择的类型越简单越好，能保存数值的类型越小越好

#三、字符型

较短的文本：

char

varchar

其他：

binary和varbinary用于保存较短的二进制

enum用于保存枚举

set用于保存集合

较长的文本：

text

blob(较大的二进制)

特点： char和varchar的区别

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

CREATE TABLE tab_char(
    c1 ENUM('a','b','c') # 对类型加限制 不区分大小写
);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m'); # 出错
INSERT INTO tab_char VALUES('A'); # 可以
SELECT * FROM tab_set;

CREATE TABLE tab_set(
    s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B'); # 可以
INSERT INTO tab_set VALUES('a,c,d'); # 可以

#四、日期型

分类：

date只保存日期

time 只保存时间

year只保存年

datetime保存日期+时间

timestamp保存日期+时间[用得更多]

特点： datetime和 timestamp的区别

	字节	范围	时区等的影响
datetime	8	1000——9999	不受
timestamp	4	1970-2038	受

CREATE TABLE tab_date(
    t1 DATETIME,
    t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());
SELECT * FROM tab_date;
SHOW VARIABLES LIKE 'time_zone'; # 显示当前时区
SET time_zone='+9:00'; # 设置东九时区，设置后TIMESTAMP格式会更改

以上全部来自于尚硅谷视频的笔记：视频链接