SQL学习之DDL和DML

jrians

已于 2024-09-02 18:30:31 修改

阅读量1.1k

点赞数 9

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

于 2024-09-02 18:27:17 首次发布

本文链接：https://blog.csdn.net/qq_54842049/article/details/141825036

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

2 DDL(数据定义语言)

注意：DDL一旦执行，不可回滚。

2.1 数据库的DDL

2.1.1 创建

CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET '字符集';#若无指定数据库，则创建指定字符集的数据库。字符集默认utf8.

2.1.2 查看

语句	作用
SHOW DATABASES;
SHOW TABLES;
USE myDatabase;
SELECT database() FROM DUAL	显示当前使用数据库

2.1.3 修改

注意：数据库名不可修改，一般只修改字符集

ALTER DATABASE b CHARACTER SET 'utf8';#修改字符集

2.1.4 删除

DROP DATABASE IF EXISTS 数据库名;

2.2 表的DDL

2.2.1 创建

计算列

create table test1(a int,b int,c int generated always as 表达式(a,b) virtual)#字段c为计算列，virtual为虚拟列，不会存储，查询时动态得到

直接建表

CREATE TABLE IF NOT EXISTS 表名 ( 属性名 类型 [约束条件] [默认值],...，[表约束条件]);

利用已有表

CREATE TABLE 表名 AS (子表查询);#将子表查询的结果作为新表

2.2.2 修改

操作对象：字段（属性）

添加字段

ALTER TABLE 表名 ADD 属性名 类型 [约束条件] [默认值] [FIRST|after 属性名];#在[头部|指定属性后面]添加属性

修改字段

ALTER TABLE 表名 MODIFY 属性名 类型 [约束条件] [默认值] [FIRST|after 属性名];

删除字段

ALTER TABLE 表名 DROP COLUMN 属性名;

重命名字段

ALTER TABLE 表名 CHANGE 原属姓名 新属性名 类型 [约束条件] [默认值] [FIRST|after 新属性名];

2.2.3 重命名表

RENAME TABLE 原表名 TO 新表名;

2.2.4 删除表

DROP TABLE if EXISTS 表名;

清空表

TRUNCATE table 表名;

2.2.5 类型和约束条件

类型

类型	例子	说明
整数	TINYINT(1字节)、SMALLINT(2字节)、MEDIUMINT(3字节)、INT(4字节)、BIGINT(8字节)	1. 可用unsigned修饰 INT(10) zerofull显示10位，不足用0填充
浮点数	FLOAT(4字节)、DOUBLE(8字节)	1. 可用unsigned修饰，但是表示范围不会变大，mysql特性 2.指定显示格式 FLOAT(5,2)总长5，小数点后2位
定点数	DECIMAL、DECIMAL(M,D)(M+2字节)，M默认10，D默认0	1.底层使用字符串存储，精度高，但存储空间大。空间充足推荐这个。 2.DECIMAL(5,2)总长5，小数点后2位的小数
位类型	BIT(1比特)	BIT(M)(M比特)，但M要小于64。表示占M比特的整数
日期类型	YEAR（1字节）、TIME（3字节）、DATE（3字节）、DATETIME（8字节）、TIMESTAMP（4字节）	1.year(年)存储类型位字符串型,格式’yyyy’，如INSERT INTO a VALUES (‘2012’); 2.date(年月日)使用字符串存储，格式’yyyy-mm-dd’如INSERT INTO a VALUES (‘2012-10-21’);特殊函数：current_date()返回当前日期 3.time(时分秒)使用字符串存储，格式’hh:mm:ss’,如INSERT INTO a VALUES (‘17:56:58’);current_time()返回当前时间 4.datatime使用字符串存储，格式’yyyy-mm-dd hh:mm:ss’,如INSERT INTO a VALUES (‘2012-10-21 17:56:58’);now()返回当前时间 5.timestamp同datetime,但表示范围小，底层使用毫秒数存，受时区影响
文本类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT	1.char固定长度文本，char(m),占m（每个字符占用字节）个字节。长度不够，右侧空格填充（查询时不显示）。varvhar可变长度文本，varchar(m),占（实际长度+1）（每个字符占用字节）个字节。 2.tinytext，text，mediumtext，longtext存储长文本数据，占用空间分别为 l+2,l+3,l+4，四个之间的关系与整型之间的关系类似。
枚举	ENUM	限制取值只能为其中一种，如 sex enum(‘男’，‘女’) ,表示属性sex的值只能是男女的一种
集合	SET	限制取值为其中的子集如course set(‘chinese’,‘math’,‘english’),表示属性course的值为三种课程的子集，会自动去重，如`insert into a values ('chinese','math','math')#结果会去重`
二进制	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB	一般用于存储视频，图片等二进制文件¹。 1.BINARY、VARBINARY与CHAR、VARCHAR类似，不过VARBINARY(M)表示M字节 2.TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB占用空间分别为 l+2,l+3,l+4，l为文件大小。四个之间的关系与整型之间的关系类似。

约束条件

包括：实体完整性（唯一且非空）、参照完整性、自定义完整性

约束	说明	增加²	删除³
primary key	1.唯一标识元组。唯一且非空 2.一个表中只能有一个。 3.会生成主键索引，根据主键查效率高。	1. `id INT primary key#建表时` 2.`primary key(id,name)#双主键（表级约束）#建表时` 3.`ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);#建表后`	`alter table 表名称 drop primary key;`
foreign key	1.外键值必须在域内，或者为空 2.外键所在表的某一行删除，要有处理措施。如级联删除，禁止删除等，以免不满足条件1。故不建议使用外键约束。 3.会生成普通索引，删除后外键约束必须手动删除索引。 4.关联外表的键必须是主键或者unique修饰的列。	1. `[CONSTRAINT <外键约束名称>] FOREIGN KEY (从表的某个字段) REFERENCES 主表名(被参考字段)#建表时` 2.`ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主表名(被引用字段)#建表后`	`ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;` `SHOW INDEX FROM 表名称; #查看某个表的索引名` `ALTER TABLE 从表名 DROP INDEX 索引名;`
not null	非空	1. `id INT not null#建表时` 2.`ALTER TABLE a MODIFY id INT not null#建表后`	`ALTER TABLE a MODIFY id INT`
unique	1.自动创建唯一索引，删除unique约束时，只能删除索引 2.唯一约束名与唯一索引名一致。未指定约束名时，若是单列，唯一索引名是列名，若是多列，则为第一个的列名。	1. `id INT unique#建表时` 2.`[constraint 约束名] unique(字段列表)#建表时` 3.`ALTER TABLE id MODIFY id unique#建表后` 4.`alter table 表名称 add [constraint 约束名] unique(字段列表);#建表后`	`alter table a drop index 索引名`
auto_increment	1.一个表中最多一个，一般为主键，也可是unique 2.修饰的必须是整型的 3.自增变量持久化：自增是通过赋予新元组自增变量的值实现的。这个变量在外存中，意味着这个值只会增大不会减小。	1.`id INT primary key auto_increment` 2. `ALTER TABLE a MODIFY id INT auto_increment`⁴	`ALTER TABLE a MODIFY id INT`
check	自定义约束，如：check(a > 10)	1.`CREATE TABLE IF NOT EXISTS a ( salary INT CHECK(salary > 1000));` 2.`CREATE TABLE IF NOT EXISTS a ( salary INT ,CHECK(salary > 1000));` 3.`ALTER TABLE a MODIFY salary INT CHECK(salary > 1000) ;` 4.`ALTER TABLE a ADD CONSTRAINT c CHECK(salary > 1000);`	1.`ALTER TABLE a modify salary Int ;` 2.`ALTER TABLE a DROP CHECK c;`
default	默认值约束	1.`CREATE TABLE if NOT EXISTS b ( id INT,num INT DEFAULT(1));` 2.`ALTER TABLE b MODIFY num INT DEFAULT(1);`	`ALTER TABLE b MODIFY num INT;`

表约束

表级约束可以实现列级约束。

[constraint 表级约束名] 列级约束(字段名)#对多列或一列约束

添加表约束

ALTER TABLE 从表名  ADD [constraint 表级约束名] 列级约束(字段名)#对多列或一列约束

删除表约束

ALTER TABLE 从表名  DROP  列级约束 [表级约束名]

3 DML(数据管理语言)

作用对象：元组

作用范围：增删改

3.1 增

单条数据

#向表中加入元组
INSERT INTO 表名 VALUES (value1,value2,...);#法1，顺序必须与表中的相同
INSERT INTO 表名(field1,field2) VALUES (value1,value2);#法2，valuei顺序与fieldi的顺序相同即可，且fieldi可以不全，没有的设为null。推荐

多条数据

#插入多条单条
INSERT INTO 表名 VALUES (value1,...),(value2,...),...

#插入查询结果
INSERT INTO 表名 (select查询)

3.2 改

UPDATE a SET field1 = 表达式1,field2 = 表达式2,... WHERE 条件;#插入满足条件的元组

3.3 删

DELETE FROM a WHERE 条件;#删除满足条件的元组

实际开发中存的是路径 ↩︎
增加约束条件分两类。一种是表上增加，另一种是列上增加。表上增加包括 表约束语句和 alter .. add 表约束语句。列上增加包括 列约束语句和 alter ... modify 列约束语句语句 ↩︎
删除约束条件分两类。一种是表上删除，另一种是列上删除。表上删除是alter .. drop 约束类型约束名。列上删除是alter ... modify 列约束语句 ↩︎
主键约束不可在alter modify语句中定义和删除。 ↩︎