SQL介绍—数据操纵/定义语言(DML/DDL)

目录

DML (Data Manipulate Language)

插入语句(insert)

修改语句(update)

删除语句(delete)

DDL (Data Define Languge)

库的管理

表的管理

常见的数据类型

常见约束

标识列

DML (Data Manipulate Language)

---

概述：数据操纵语言，用于添加、删除、修改数据库记录，并检查数据完整性。

关键字：insert, delete, update 等。

插入语句(insert)

方式一	语法：insert into 表名 (字段名, . . .) values (值1, . . .), (值2, . . .). . . ;
方式二	语法：insert into 表名 set 列名=值, 列名=值, . . . ;
特点	1. 插入的值的类型要与字段的类型一致或兼容 2. 不可以为null的列必须插入值，可以为空的插入值     方式一：可以为空字段对应values里面的值填null     方式二：省略可以为空的字段 3. 字段的顺序可以调换 4. 字段数和值的个数必须一致 5. 可以省略字段名，默认所有列，字段的顺序与表中字段顺序要一致
两种方式的比较	方式一支持插入多行，支持子查询

修改语句(update)

修改单表的记录	语法： update 表名 set 字段=新值, 字段=新值, . . 【where 筛选条件】;
修改多表的记录	sql92语法： update 表1 别名1, 表2 别名2 set 字段=新值, 字段=新值, . . where 连接条件 and 筛选条件;
	sql99语法： update 表1 别名1 inner|left|right| join 表2 别名2 on 连接条件 set 字段=新值, 字段=新值, . . 【where 筛选条件】;

删除语句(delete)

方式一 (delete)	单表的删除	语法：delete from 表名 【where 筛选条件】【limit 条目数】;
	多表的删除	sql92语法： delete 表1/2的别名 from 表1 别名1, 表2 别名2 where 连接条件 and 筛选条件;
		sql99语法： delete 表1/2的别名 from 表1 别名1 inner|left|right| join 表2 别名2 on 连接条件 【where 筛选条件】;
方式二 (truncate)	语法：truncate table 表名;
两种方式的比较	1. delete可以加where条件，truncate不能加 2. truncate删除，效率高一丢丢 3. 假如要删除的表中有自增长列：     用delete删除后，再插入数据，自增长列的值从断点开始     truncate删除后，再插入数据，自增长列的值从1开始 4. truncate删除没有返回值，delete删除有返回值 5. truncate删除不能回滚，delete删除可以回滚

DDL (Data Define Languge)

---

概述：数据定义语言，用于库和表的创建、修改、删除。

关键字：create, alter, drop 等。

库的管理

库的创建	语法：create database【if not exists】库名; 创建数据库，并指定字符集：create database 库名 character set 字符集名;
库的修改	不建议更改，可停止服务后修改文件夹名称，再重新启动 修改库的字符集：alter database 库名 character set 字符集名称;
库的删除	语法：drop database【if exists】库名;
库的查看	查询某个数据库的字符集：show create database 库名; 查询当前正在使用的数据库名称：select database();

表的管理

表的创建	语法： create table【if not exists】表名(     字段名  字段类型【(长度) 约束】,     字段名  字段类型【(长度) 约束】,     . . .     字段名  字段类型【(长度) 约束】 );
表的修改	语法：alter table 表名 add|drop|modify|changecolumn 列名【列类型 约束】; 修改列名称、类型：     alter table 表名 change【column】旧列名 新列名 新类型;     alter table 表名 modify【column】列名 新类型【新约束】; 添加列：alter table 表名 add【column】列名 类型【first|after 字段名】; 删除列：alter table 表名 drop【column】列名; 修改表名：alter table 表名 rename【to】新表名; 修改表的字符集：alter table 表名 character set 字符集名称;
表的删除	语法：drop table【if exists】表名;
表的复制	复制表的结构：create table 新表名 like 旧表名; 复制表的部分结构：create table 新表名 select 字段1,字段2 from 表名 where 0; 复制表的结构+数据：create table 新表名 select * from 表名【where 筛选条件】; 跨库复制表：create table 新表名 select * from 库名.表名; 跨库复制表案例： #将表departments中的数据插入新表dept2中 CREATE TABLE dept2 SELECT * FROM myemployees.departments;
创建库、表的通用写法	drop database/table if exists 旧库名/旧表名; create database/table 新库名/表名();

常见的数据类型

数值型
整型
	特点： 1. 如果不设置无符号还是有符号，默认是有符号，设置无符号需要在设定的类型后方添加 unsigned关键字 2. 如果插入的数值超出了整型的范围，就会报out of range异常，并且插入临界值 3. 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须在类型后搭配 zerofill使用，并默认变为无符号整型
小数
	特点： 1. M：整数部位+小数部位，D：小数部位，如果超过范围，则插入临界值 2. M和D都可以省略     如果是decimal，则M默认为10，D默认为0     如果是float和double，则会根据插入数值的精度来决定精度 3. 定点型的精确度较高，要求精度较高时使用，如货币运算

字符型
较短的文本 (保存MySQL中较短的字符串)	char(M)	固定长度字符，最多字符数M，可以省略，默认为1 M为0~255之间的整数 比较耗费空间，效率高
	varchar(M)	可变长度字符，最多字符数M，不可省略 M为0~65535之间的整数 比较节省空间，效率低
	binary、 varbinary	包含二进制字符串而不包含非二进制字符串
	enum	枚举类型，要求插入的值必须属于列表中指定的值之一
	set	和枚举类似，set类型一次可以选取多个成员，而enum只能选一个
较长的文本 (text、blob较大的二进制)

日期型
	datetime不受时区影响 timestamp受时区、语法模式、版本的影响 (更能反映当前时区) 查看时区：show variables like 'time_zone'; 设置时区：set time_zone='+9:00';

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

常见约束

概述：一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠。

分类：

not null：非空，保证该字段的值不能为空 (姓名、学号)

default：默认，保证该字段有默认值 (性别)

primary key：主键，字段值具有唯一性，非空，最多设置1个，允许字段组合 (不推荐)

unique：唯一，字段值唯一，可以为空，可以设置多个，允许字段组合 (不推荐)

check：检查约束【mysql中不支持】(年龄、性别)

foreign key：外键，用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值 特点： 1. 在从表中设置外键关系，引用主表中某列的值 2. 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求 3. 主表的关联列必须是一个key (一般是主键或唯一) 4. 插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

添加约束的时机：

创建表时	添加列级约束	概述：六大约束语法上都支持，外键约束没效果，不可以取约束名 语法：直接在字段名和类型后面追加约束类型即可 只支持：默认、非空、主键、唯一
	添加表级约束	概述：除了非空、默认，其他的都支持，可以取约束名对主键无效 语法：在各个字段的最下面 【constraint 约束名】约束类型(字段名)
	通用的写法案例	CREATE TABLE IF NOT EXISTS stusifo(     id INT PRIMARY KEY, #主键     stuname VARCHAR(20) NOT NULL, #非空     sex CHAR(1),     age INT DEFAULT 18, #默认     seat INT UNIQUE, #唯一     majorid INT,     CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外键 );
修改表时	添加列级约束	语法：alter table 表名 modify column 字段名 字段类型 新约束;
	添加列级约束	语法：alter table 表名 add【constraint 约束名】约束类型(字段名)【外键的引用】;
	添加约束案例	#非空约束 ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL; #默认约束 ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18; #添加主键 ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;#列级约束 ALTER TABLE stuinfo ADD PRIMARY KEY(id);#表级约束 #添加唯一键 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 DROP PRIMARY KEY; #删除唯一 ALTER TABLE stuinfo DROP INDEX seat; #删除外键 ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

查看表中所有的索引：SHOW INDEX FROM 表名;

标识列

概述：又称自增长列，可以不用手动的插入值，系统提供默认的序列值。

分类	创建表时设置标识列	案例： CREATE TABLE IF NOT EXISTS tab_identity(     id INT PRIMARY KEY AUTO_INCREMENT,     `name` VARCHAR(20) );
	修改表时设置标识列	案例： ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;
	修改表时删除标识列	案例： ALTER TABLE tab_identity MODIFY COLUMN id INT;
特点	1. 标识列必须和是key的列搭配使用 2. 一个表最多只能有一个标识列 3. 标识列的类型只能是数值型 4. 标识列可以通过SET auto_increment_increment=3;设置步长，可以通过手动插入值，设置起始值

---