MySQL中DDL和DML

MySQL中DDL和DML

一、SQL

1.SQL是什么？

• SQL指结构化查询语言，全称是Structured Query Language。
• SQL可以访问和处理数据库，包括数据查询，插入，更新和删除。
• SQL在1986年成为ANSI的一项标准，在1987成为国际化标准组织标准。

2.SQL能做什么？

• SQL可以从数据库中查询数据，插入数据，更新和删除数据。
• SQL可以创建新数据库，新的表和创建存储过程。
• SQL可以创建视图，并设置表，存储过程，视图的权限等。

注意：

• SQL对大小写不敏感，SELECT和select是一样。但是SQL引擎通常会把语句都转换为大写字符来执行。
• 某些数据库系统要求每条SQL语句的末端使用分号，有些不要求。

3.SQL分类

1.数据库查询语言(DML)

-- 主要用于数据库的查询，基本结构是：select 子句 from 子句 where 子句 ... 
-- 组合查询一条或多条数据

2.数据库操作语言(DML)

-- 主要针对数据的操作（增删改操作）
-- 主要包括：insert(增)，delete(删) ，update(改)

3.数据库定义语言(DDL)

-- 主要对数据库对象(数据库、表、索引、视图)进行创建，修改，和删除操作
-- 主要包括：create(创建)，alter(修改),drop(删除)

4.数据库控制语言(DCL)

-- 用来授予或回收访问数据库的权限
-- 主要包括：grant（授予用户某种权限），revoke（回收授予的某种权限）

5.事务控制语言(TCL)

-- 用于对数据库的事务管理
-- 主要包括：start transaction(开启事务)，set transaction（设置事务属性），commit(提交事务)，rollback（回滚事务）

二、DDL操作数据库表

1.MySQL中常见的数据类型

1.1.整数型

类型	描述
tinyint	十分小的整数，范围(-128,127)
smallint	较小的整数，范围(-32768，32767)
mediumint	中等大小的整型，范围(-8388608，8388607)
int（常用）	标准型，范围(-214748364，2147483647)
bigint（工作常用）	大整型，范围(-9233372036854775808，9223372036854775807)

1.2.浮点型

浮点数类型	大小	作用
float	4字节	单精度浮点数值
double	8字节	双精度浮点数值

注意

1.一般不推荐使用浮点型，用decimal类型

2.浮点数类型有个缺陷，就是不精准。例如：
我们设计一个表，有f1，f2 这两个个字段，都分别插入值0.47, 0.44, 0.19，我们期待对f1求和后的结果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询：

查询结果是 1.0999999999999999。虽然误差很小，但确实有误差。

如果把数据类型改成 float，然后运行求和查询，得到的是， 1.0999999940395355。显然，误差更大了。

那么，为什么会存在这样的误差呢？问题还是出在 MySQL 对浮点类型数据的存储方式上。

MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。无论哪个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2³。如果尾数不是 0或5（比如9.624），你就无法用一个二进制数来精确表达。进而，就只好在取值允许的范围内进行四舍五入。

注意：在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。同时，在一些对精确度要求较高的项目中，千万不要使用浮点数（一般用decimal），不然会导致结果错误，甚至是造成不可挽回的损失。

1.3.字符串类型

字符串类型	大小	描述
char(m)	0~255个字符	允许长度0~M个字符的定长字符串
varchar(m)	0~65535字符	允许长度0~M个字符的变长字符串，常用
binary	0~255字节	允许长度0~M个字节的定长二进制字符串
varbinary	0~65535字节	允许长度0~M个字节的变长二进制字符串
tinyblob	0~255字节	二进制形式的短文本数据（长度为不超过255个字符）
tinytext	0~255字节	短文本数据
mediumtext	0~16777215字节	中等长度文本数据
longblob	0~4294967295字节	二进制形式的极大文本数据
longtext	0~4294967295字节	极大文本数据

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

1.4.日期类型

类型	格式	取值范围
time	'HH:MM:SS'	'-838:59:59’, ‘838:59:59'
date	'YYYY-MM-DD'	'1000-01-01'，'9999-12-31'
year	YYYY	1901,2155
datetime	'YYYY-MM-DD HH:MM:SS'	'1000-01-01 00:00:00', '9999-12-31 23:59:59'
timestamp	'YYYY-MM-DD HH:MM:SS'	'1970-01-01 00:00:01’UTC，'2038-01-19 03:14:07' UTC

2.创建数据库表

1.1.语法

create table 表名(
	字段1	数据类型,
    字段2 数据类型(长度)
);
-- 注意：最后一个字段不要加逗号，可以给字段的数据类型指定长度

-- 例如
-- 创建一个商品(category) 表
create table category(
	cid int,
    cname varchar(20)
);

-- 创建一个学生表
/* 要求：
表名：student
表中的字段：
	学号：sid，整型
	姓名：sname，字符串类型，长度为10
	性别：sex，字符串类型，长度为1
	年龄：age，整型
	入学日期：createdate，日期类型
	班级：class，字符串类型，长度为15
	邮箱：email，字符串类型，长度为25
*/
create table student(
	sid int,
    sname varchar(10),
    sex varchar(1),
    age int,
    createdate date,
    class varchar(15),
    email varchar(25)
);

3.DDL常用语法

描述	语法	案例
DDL-查看数据库表	show tables;	显示当前数据库的所有的表：show tables;
	desc 表名;	显示student表的结构：desc student;
	show create table 表名	查看student表完整的建表语句：show create table student;
DDL-删除数据库表	drop table 表名	删除当前数据库中的student表：drop table student;
DDL-修改表名	rename table 旧表名 to 新表名;	修改student表名为stu： rename table student to stu;
DDL-向表中添加字段	alter table 表名 add 添加字段的名称 字段类型;	向表stu中添加字段address，长度为50： 默认添加到最后：alter table stu add address varchar(50); 添加到第一个：alter table stu add address varchar(50) first; 添加到age字段之后：alter table stu add address varchar(50) after age;
DDL_修改表中字段属性	alter table 表名 modify 字段名称 字段类型;	将表stu中的address字段长度改为30： alter table stu modify address varchar(30);
DDL_修改表中字段名字及属性	alter table 表名 change 旧字段名 新字段名 类型(长度);	将表stu中的address字段，修改为city，长度为10： alter table stu change address city varchar(10);
DDL_删除表中字段	alter table 表名 drop 字段名;	删除stu表中 address2字段： alter table stu drop address2;

三、DML数据操作语言

1.增加数据

1.1.语法：

-- 这种方式是给表中所有字段都添加数据，值1，值2，值3....  都需要和表中字段顺序相对应
-- 比如：stu表中字段为：sid,sname,address  那么下面的值1要和sid对应，值2要和aname对应，值3要和address对应
insert into 表名 values(值1,值2，值3,......);

-- 只给某字段添加数据
insert into 表名(字段1,字段2,字段3,....) values(字段1的值,字段2的值,字段3的值,.....);

-- 一次性插入多条数据，插入的数据用 , 隔开
insert into 表名 values(值1,值2，值3,......),(值1,值2，值3,......),(值1,值2，值3,......),.......;

insert into 表名(字段1,字段2,字段3,...) values(字段1的值,字段2的值,字段3的值,...),(字段1的值,字段2的值,字段3的值,...),.....;

1.2.案例

-- 1.给stu所有字段添加数据       stu表中的字段：sid，sname，address
insert into stu values(1,'王小二','北京');

-- 2.给stu表中的 sname字段、address字段 添加数据
insert into stu(sname,address) values('王小三','河北');

-- 3.给stu表中所有字段一次性添加多条数据    
insert into stu values(3,'王小四','山东'),(4,'王小伍','黑龙江'),(5,'王小六','辽宁');
-- 如果sid字段是自增的话，可以这样写
insert into stu values(null,'王小四','山东'),(null,'王小伍','黑龙江'),(null,'王小六','辽宁');

-- 4.给stu表中字段：sname、address 一次性添加多条数据
insert into stu(sname,address) values('王多鱼','沈阳'),('王少鱼','内蒙'),('王多多','宁夏');

1.3.注意事项：

1. 值与字段必须要对应，个数相同、数据类型相同
2. 值的数据大小，必须在字段指定的长度范围内
3. varchar char date类型的值必须使用单引号，或者双引号包裹
4. 如果要插入空值，可以忽略不写，或者写null
5. 如果插入指定字段的值，必须要上写列名(字段名)
6. 字符串日期类型支持 "2000-12-23" "2000/12/23" "2000.12.23"

2.删除数据

2.1.语法：

-- 语法格式
-- delete from 表名 where 条件;
-- delete from 表名; -- 慎用：删除表中的所有数据
-- truncate table 表名; -- 慎用：删除表中的所有数据

-- 案例1：删除sid=5的学生信息
delete from stu where sid=5;
-- 案例2：删除stu表中所有的数据
-- 方式一：
delete from stu;
-- 方式二：
truncate table stu; -- 推荐使用

2.2.truncate 和 delete的对比：

这两种操作都可以删除表中的数据

1. delete为数据操作语言DML；truncate 为数据定义语言DDL。
2. delete操作是将表中所有记录一条一条删除直到删除完；truncate 操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，truncate 操作的效率更高。
3. delete操作可以回滚（可以通过事务回滚）；truncate 操作会导致隐式提交，因此不能回滚。
4. delete操作执行成功后会返回已删除的行数（如删除4行记录，则会显示“affected rows：4”）；truncate 操作不会返回已删的行量，结果通常是“affected rows：0”。
5. delete操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；truncate 操作会重新从1开始自增。

3.修改数据

-- 语法格式
-- update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
-- update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用：不加条件会修改所有的数据

-- 案例1：将stu表中sid=1的学生，address修改为 上海
update stu set address='上海' where sid=1;
-- 案例2：将sid=2的学生name(姓名)改为 张老三 ，address(住址) 改为 深圳
update stu set name='张老三',address='深圳' where sid=2;