【数据库】-----02初识MySQL(DML and DDL)

一、DML

DML是什么

DML（Data Manipulation Language）语句: 数据操纵语言，主要是对表中数据进行增加、删除、修改操作。常用的语句关键字有 INSERT、UPDATE、DELETE 等。

DML插入语句

方式一、经典插入法

语法：

insert into 表名(列名，……)
values(值1，……);

经典插入注意事项

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

INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(1,'进宝','女','1990-4-23','12345645123',NULL,2);

2、不可以为null的列必须插入值。

//方式一：
INSERT INTO beauty(id,NAME,sex,borndate,phone,photo,boyfriend_id)
VALUES(1,'进宝','女','1990-4-23','12345645123',NULL,2);

//方式二：
INSERT INTO beauty(id,NAME,sex,phone)
VALUES(1,'进宝','女','12345645123');

	3、列的顺序可以调换，但值一定要和列一一对应
			4、列数和值的个数必须一致
				5、可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

方式二

语法：

insert into 表名
set 列名=值，列名=值，……

INSERT INTO beauty
SET id=1,NAME='进宝',phone='12345645123';

两种方式比较

	插入多行	子查询
方式一	支持	支持
方式二	不支持	不支持

DML修改语句

修改单表记录

语法：

update 表名
set 列=新值,列=新值,...
where 筛选条件;

案例：

//案例1：修改beauty表中姓张的电话改为12345678956
UPDATE beauty SET phone = '12345678956'
WHERE NAME LIKE '张%';

修改多表记录

语法：
sql92语法：

update 表1 别名,表2 别名
set 列=值,...
where 连接条件
and 筛选条件;

sql99语法：

update 表1 别名
inner|left|right join 表2 别名
on 连接条件
set 列=值,...
where 筛选条件;

一般用sql99语法

案例：

#案例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;

DML删除语句

方式一：delete语句

单表删除

语法：

delete 
from 表名 
where 筛选条件;

案例：

//案例：删除手机号以9结尾的女生信息
DELETE 
FROM beauty 
WHERE phone LIKE '%9';

多表删除

sql92语法：

delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

sql99语法：

delete 表1的别名,表2的别名
from 表1 别名
inner|left|right join 表2 别名 on 连接条件
where 筛选条件;

案例：

//案例：删除进宝的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.`boyfriend_id`=bo.`id`
WHERE bo.`boyName`='进宝';

方式二：truncate语句

语法：

truncate table 表名;

两种方式比较

	能否添加where条件	删除自增长列，再插入数据	有无返回值	能否回滚
delete	可以	从断点开始	有	可以
truncate	不能	从1开始	没有	不可以

---

二、DDL

DDL是什么

DDL（Data Definition Language）语句： 数据定义语言，主要是对库或表进行创建、修改、删除管理。常用的语句关键字有 CREATE、DROP、ALTER 等。

创建: create语句

库的创建

语法：

create database if not exists 库名;//if not exists 判断该库名是否存在

案例：

#案例：创建库Books
CREATE DATABASE IF NOT EXISTS books ;

表的创建

语法：

create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)

案例：创建表Book

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

修改：alter语句

库的修改

语法：

RENAME DATABASE books TO 新库名;

通用写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

一般不建议对库进行修改，但可以修改库的字符集
案例：

ALTER DATABASE books CHARACTER SET gbk;

表的修改

语法：

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

通用写法：
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

案例：

①修改列名
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

②修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③添加新列
ALTER TABLE author ADD COLUMN annual DOUBLE; 

④删除列
ALTER TABLE book_author DROP COLUMN  annual;

⑤修改表名
ALTER TABLE author RENAME TO book_author;

修改表时添加或删除约束:
添加列级约束语法：

ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束;

添加表级约束语法：

ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】约束类型(字段名)【外键的引用】;

删除：drop语句

库的删除

语法：

drop database if exists 库名;

表的删除

语法：

drop table if exists 要删除的表名

表的复制

仅仅复制表的结构

复制全部结构语法：

CREATE TABLE 新表 LIKE 旧表; //（将旧表的结构复制给新表）

复制部分结构语法：

CREATE TABLE 新表
SELECT id,au_name//这里选择复制的列
FROM 旧表
WHERE 0或者(1=2);

复制表的结构+数据

语法：

CREATE TABLE 新表
SELECT * FROM 旧表

复制部分数据语法：

CREATE TABLE 新表
SELECT * 
FROM 旧表
WHERE 复制条件

常见的数据类型

数值型

整型：

整数类型	字节
Tinyint	1
Smallint	2
Mediumint	3
Int\integer	4
Bigint	8

特点
如果不设置无符号还是有符号，默认是有符号，如果设置无符号，需要追加 unsigned 关键字
如果插入的数值超出了整形的范围，会报异常，并且插入的是临界值
如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0再左边填充，但必须搭配zerofill使用！
写上zerofill关键字默认为无符号，可省略unsigned 关键字

小数：

浮点数类型	字节
float	4
double	8
定点数类型	字节
–	–
DEC(M,D) 或DECIMAL(M,D)	M+2

M:代表整数部位+小数部位
D:小数部位
如果超过范围，则插入临界值

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

M和D都可以省略
如果是DECIMAL,则M默认为10，D默认为0
如果是FLOAT 和 DOUBLE,则会根据插入的数值的精度来决定精度

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

字符型

char、varchar用于保存较短的文本，text、blob(保存较大的二进制数据)

字符串类型	M的意思	特点	空间的消耗	效率
char(M)	最大字符数，可以省略，默认为1	固定长度的字符	大	高
varchar(M)	最大字符数，不可省略	可变长度的字符	小	低

日期型

日期和时间类型	字节	最小值	最大值
date	4	1000-01-01	9999-12-31
datetime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	2038年的某个时刻
time	3	-838:59:59	838:59:59
year	1	1901	2155

timestamp容易受时区、语法模式、版本等的影响，更能反映当前时区的真实时间
datetime只能反映出插入时的当地时区

常见约束

约束是一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

约束	作用
not null:非空约束	用于保证该字段的值不能为空
default:默认约束	用于保证该字段有默认值
primary key:主键约束	用于保证该字段的值具有唯一性，并且非空
unique:唯一约束	用于保证该字段的值具有唯一性，可以为空
check:检查约束	【mysql中不支持】
foreign key:外键约束	用于限制两个表的关系，保证该字段的值必须来自于主表的关联列的值(从表添加外键约束，引用主表的值)

foreign key:外键约束的要求：

1、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
	2、主表的关联列必须是一个key(一般是主键、唯一)
		3、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表。再删除主表

添加列级约束：
直接在字段名和类型后面追加约束类型即可。 只支持：默认、非空、主键、唯一
语法：

CREATE TABLE 表名(
	字段名 字段类型 列级约束
	);

添加表级约束：
除了非空、默认，其他的都支持
语法：

CREATE TABLE 表名(
	字段名 字段类型 列级约束
	字段名 字段类型 列级约束
	表级约束
	);

	保证唯一性	允许为空	一个表可以有多个	允许组合
主键	yes	no	最多1有1个	可以，但不推荐
唯一	yes	yes	可以有多个	可以，但不推荐

	位置	支持的约束类型	是否可以起约束名
列级约束	列的后面	都支持，但外键无效	不可以
表级约束	所有列的后面	默认和非空不支持	可以(主键无效)

标识列(自增长列)

可以不需要手动的插入值，系统提供默认的序列值
如何添加标识列：在需要添加标识列后添加：auto_increment

特点
标识列不一定非要和主键搭配，但要求是一个key(键)
一个表最多可以有一个标识列
标识列的类型只能是数值型
标识列可以通过：SET auto_increment_increment=3;设置步长,也可以通过手动插入值，设置起始值长