MySQL学习笔记（四）— 数据操作（DML语言）+ 数据定义（DDL语言）

`AllureLove

已于 2022-03-15 19:27:56 修改

阅读量774

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2021-11-13 17:53:36 首次发布

本文链接：https://blog.csdn.net/weixin_36488653/article/details/121305279

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文详细讲解了DML（数据操纵语言）中的数据插入、修改和删除，以及DDL（数据定义语言）的库管理、表操作和常见约束。通过实例演示了如何使用SQL进行表结构创建、修改和数据操作，是数据库初学者和开发者必备的参考资源。

摘要由CSDN通过智能技术生成

1. DML语言

1.1 数据插入

/*
方式一
insert into 表名（列名，...） values（值1，...）
插入的值的类型要与列的类型一致或兼容
不可为null的列必须插入值，可以为null的列可以不插入
列的顺序可以调换
列数和值要一一对应，可以不写插入列名，则需要将全部值插入
*/
INSERT INTO beauty(id, NAME, sex, borndate, phone, photo, boyfriend, photo, boyfriend_id)
VALUES(13, '唐艺昕', '女', '1990-4-23', '18988888888', NULL, 2);
/*
方式二
insert into 表名
set 列名=值，列名=值，...
*/
INSERT INTO beauty 
set id=19, NAME='刘涛', phone='999';

# 方式一可以插入多行
INSERT INTO beauty
VALUES(24, 'xxx', 'female', '1990-4-23', '1726183791', NULL, 2),(24, 'xxx', 'female', '1990-4-23', '1726183791', NULL, 2),(24, 'xxx', 'female', '1990-4-23', '1726183791', NULL, 2);
# 方式一支持子查询
INSERT INTO beauty(id, NAME, phone)
SELECT 26, '宋茜', '1169809';

1.2 修改数据

/*
1. 修改单表记录
update 表名
set 列=新值, 列=新值, ...
2. 修改多表记录
sql92语法
update 表1 别名， 表2， 别名
set 列=值， ...
where 连接条件
and 筛选条件

sql99语法
update 表1 别名
inner/left/right join 表2 别名
on 连接条件
set 列=值，...
where 筛选条件
*/
# 1. 单表修改
# 修改beauty表中姓唐的电话
UPDATE beauty SET phone='1289729' 
WHERE NAME LIKE '唐%';
# 修改boys表中id为2的名称为张飞，魅力值为10
UPDATE boys 
SET boyname='张飞', usercp=10
WHERE id=2;
# 2. 多表修改记录
# 案例1:修改张无忌的女朋友的手机号为1310390193
UPDATE boys b
INNER JOIN beauty b 
ON bo.`id`=b.`boyfriend_id`
SET b.`phone`=1310390193
WHERE bo.`boyName`='张无忌'；
# 修改没有男朋友的女神的男朋友编号都为2号
UPDATE boys bo
RIGHT JOIN beauty b 
ON bo.`id`=b.`boyfriend_id`
SET b.`boyfriend_id`=2
WHERE b.`id` IS NULL;

1.3 删除语句

/*
方式一：delete
delete from 表名 where 条件

方式二：truncate
语法：truncate table 表名

两种方式的区别：
delete 可以加where条件，truncate不可
delete有返回值，truncate无返回值
若删除的表有自增长列，则插入数据时delete从删除位置继续自增，truncate删除后插入数据从1开始自增
*/
# 方式一：delete
# 1. 单表删除
# 删除手机号9结尾的女神信息
DELETE FROM beauty WHERE phone LIKE %9%;
# 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
# 将魅力值>100的男神信息删除
TRUNCATE TABLE boys;

2. DDL语言

数据定义语言，创建、修改、删除库或表（create、alte、drop）

2.1 库的管理

# 1. 库的创建
/*
create database [if not exists] 库名；
*/
CREATE DATABASE IF NOT EXISTS books;
# 2. 库的修改
RENAME DATABASE books TO 新库名;
# 更改库的字符集
ALTER DATABASE books CHARACTER SET gbk;
# 3. 库的删除
DROP DATABASE IF EXISTS books;

2.2 表的管理

# 1. 表的创建
/*
create table 表名 (
	列名 列的类型【(长度) 列的约束】,
	列名 列的类型【(长度) 列的约束】,
	列名 列的类型【(长度) 列的约束】,
	...
	列名 列的类型【(长度) 列的约束】
)
*/
CREATE TABLE book(
	id INT, # 编号
	bName VARCHAR(20), # 图书名
	price DOUBLE, # 价格
	authorID INT, # 作者
	publishDate DATETIME # 出版日期
)
DESC book;
CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
)
DESC author;
# 2. 表的修改
# 修改列名
ALTER TABLE book CHANGE COLUMN publishdDate pubDate DATETIME;
# 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
# 添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE;
# 删除列
ALTER TABLE author DROP COLUMN annual;
# 修改表名
ALTER TABLE author RENAME TO 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, '冯唐', '中国'),
(3, '金庸', '中国');
# 1. 仅仅复制表的结构
CREATE TABLE copy LIKE author;
# 2. 复制结构+数据
CREATE TABLE copy2 
SELECT * FROM author;
# 3. 复制部分数据
CREATE TABLE copy3
SELECT id, au_name
FROM author
WHERE nation='中国';
# 仅复制部分结构，即某些字段的结构
CREATE TABLE copy4 
SELECT id, au_name
FROM author
WHERE 1=2;

2.3 常见约束

/*
含义：一种限制，用于限制表中数据的准确和可靠性

分类：六大约束
	NOT NULL：非空约束，用于保障该字段不能为空，如姓名、学号等
	DEFAULT：默认，保证该字段有默认值，如性别
	PRIMARY KEY：主键，用于保证该字段的值具有唯一性，且非空，如学号、员工编号等
	UNIQUE：唯一，用于保证该字段的值具有唯一性，可为空，如座位号
	CHECK：检查约束【mysql中不支持】，如年龄、性别
	FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，从表添加外键约束用于引用主表中的数据类型，如学生表的专业编号、员工表的部门编号、员工表的工种编号

添加约束的时机：
	创建表时
	修改表时

约束的添加分类：
	列级约束：六大约束语法都支持，但外键约束没有效果
	表级约束：除了非空和默认，其他都支持
*/
CREATE TABLE 表名(
	字段名 字段类型 列级约束,
	字段名 字段类型,
	表级约束
)

# 一、创建表时添加约束
# 1. 添加列级约束
/*
直接在字段名和类型后追加约束类型即可
只支持：默认、非空、主键、唯一
*/
USE students;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY, 
	stuName VARCHAR(20) NOT NULL,
	gender CHAR(1) CHECK(gender='男' OR gender='女'),
	seat INT UNIQUE,
	age INT DEFAULT 18, # 默认约束
	majorId INT REFERENCES major(id) # 外键
)

CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
)

SHOW INDEX FROM stuinfo;

# 2. 添加表级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT , 
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT, # 默认约束
	majorId INT, # 外键

	CONSTRAINT pk PRIMARY KEY(id),
	CONSTRAINT uq UNIQUE(seat),
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

# 通用写法
CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY, 
	stuName VARCHAR(20) NOT NULL,
	gender CHAR(1),
	seat INT UNIQUE,
	age INT DEFAULT 18, # 默认约束
	majorId INT, # 外键

	CONSTRAINT ck CHECK(gender='男' OR gender='女'),
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
)

# 二、修改表时添加约束
/*
1. 添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2. 添加表级约束
alter table add [constraint 约束名] 约束类型(字段名) [外键的引用];
*/
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT , 
	stuName VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT, # 默认约束
	majorId INT, # 外键
);
# 1. 添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
# 2. 添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
# 3. 添加主键
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
# 4. 添加唯一键
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE stuinfo ADD UNIQUE(seat);
# 添加外键
ALTER TABLE stuinfo ADD [CONSTRAINT fk_stuinfo_major] FOREIGN KEY(majorid) REFERENCES major(id);

# 三、修改表时删除约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
ALTER TABLE stuinfo MODIFY COLUMN age INT;
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY;
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

# 标识列：自增长列，自动插入序列值
/*
一个表只能有一个标识列，且加在key列，标识列的类型必须是数值型，标识列可以设置步长，也可以手动插入值设置起始值
*/
# 一、创建表时设置标识列
DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity VALUES(NULL, 'john');

# 改步长
SET auto_increment_increment=3;

# 二、修改表时设置
ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY  AUTO_INCREMENT;
ALTER TABLE tab_identity MODIFY COLUMN id INT;

3. 常见的数据类型

/*
数值型
	整型
	小数：
		定点数
		浮点数
字符型
	较短的文本：char、varchar
	较长的文本：text、blob（较长的二进制数据）
日期型	
*/
# 一、整型
# Tinyint、Smallint、Mediumint、Int、Integer、Bigint
# 设置无符号和有符号
# 默认为有符号的
CREATE TABLE tab_int(
	t1 INT
);
DESC tab_int;
INSERT INTO tab_int VALUES(-123456);
DROP TABLE IF EXISTS tab_int;
# 创建无符号的，不包含正负号
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);
# ZEROFILL默认无符号，显示结果的最大宽度，若不够则左边0填充
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT UNSIGNED
);
# 二、小数
# 1. 符点型：float（M，D）、double（M，D）
# 2. 定点型：DEC（M，D）/DECIMAL（M，D），精度要求较高
# M、D可以省略，M表示整数部分和整数部分总长度，D表示小数点后几位
# DECIMAL M默认为10， D默认为0
# float、double自适应M、D
CREATE TABLE tab_float(
	f1 FLOAT(5, 2),
	f2 DOUBLE(5, 2),
	f3 DECIMAL(5, 2)
);
INSERT INTO tab_float VALUES(123.45, 123.45, 123.456)

# 三、字符型（串数据）
/*
较短的文本：char（M）（固定长度）、varchar（M）（可变长度字符）
M为最大字符个数
较长的文本：text、blob（较大的二进制）
*/
# enum仅可以插入给定列表的值，不区分大小写
CREATE TABLE tab_char(
	c1 ENUM('a', 'b', 'c')
);
INSERT INTO tab_char VALUES('a');
# set 可以插入给定列表的值，不区分大小写
CREATE TABLE tab_set(
	c1 SET('a', 'b', 'c')
);
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
*/
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(), NOW());
SELECT * FROM tab_date;
SET time_zone='+9:00';