mysql——DML语言与DDL语言

一、DML语言

数据操作语言
插入：insert
修改：update
删除：delete

1.插入语句

语法：
insert into 表名(列名,…) values(值1,…)

1）插入的值的类型与列的类型一致或兼容
insert into beauty（id,name,sex,borndate,phone,photo,boyfriend_id）
values(13,‘唐红’,‘女’,‘1990-5-22’,‘1356666666’,null,2)

2)不可以为null的列必须插入值，可以为null的列如何插入值
方式一：
insert into beauty（id,name,sex,borndate,phone,photo,boyfriend_id）
values(13,‘唐红’,‘女’,‘1990-5-22’,‘1356666666’,null,2)

方式二：
insert into beauty（id,name,sex,borndate,phone,boyfriend_id）
values(14,‘小红’,‘女’,‘1990-5-12’,‘1356666666’,8)

3）列的顺序可以调换

4）列数和值的个数必须一致

5）可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致
inset into beauty
values(15,‘张三’,‘女’,null,null,null,null)

方式二：
insert into 表名
set 列名=值,列名=值,…

两种方法对比
1.方式一支持插入多行
insert into beauty
values(16,‘唐小红’,‘女’,‘1990-5-22’,‘1356666666’,null,2),
values(17,‘小飞象’,‘女’,‘1990-5-22’,‘1356666666’,null,2),
values(18,王红’,‘女’,‘1990-5-22’,‘1356666666’,null,2);
2.方式一支持子查询，方式二不支持

insert into beauty(id,name,phone)
select 26,‘宋茜’,‘13499999999’

2、修改语句

语法：
1.修改单标的记录
update 表名
set 列=新值,

2.修改多表的记录【补充】
sql192
update 表1 别名, 表2 别名
set 列=值,…
where 连接条件
and 筛选条件

199语法
update 表1 别名
inner、left、right join 表2 别名
on 连接条件
set 列=值
where 筛选条件

案例1：修改beauty 表中姓唐的女神的电话为12345678910;

update beauty set phone =‘12345678910’
where name like’唐%’

案例2：修改boys表中id号为2的名称为张飞,魅力值为10
update boys set boyname=‘张飞’,usercp=10
where id = 2;

2）修改多表的记录
案例1：修改张无忌的女朋友的手机号为114
update boys as bo
inner join beauty b on bo.id=b.boyfrien_id
set b.phone=‘114’
where bo.boyName=‘张无忌’

案例2：修改没有男朋友的女神的男朋友编号都为2
update boys as bo
right join beauty b on bo.id=b.boyfrien_id
set b.boyfriend_id=2
where b.id is null

3、删除语句

方式一：delete
1、单表删除
delete from 表名 where 筛选条件

2、多表删除【补充】
sql192语法
delete 表1的别名,表2别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件

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

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

方式一：delete

1）单表删除

案例1：删除手机号以9结尾的女神信息
delete from beauty where phone like’%9’

2）多表删除

案例1：删除张无忌的女朋友的信息
delete b
from beauty b
inner join boys on b.boyfriend_id = boys.id
where boys.boyName=‘张无忌’

案例2：删除黄晓明的信息以及他女朋友的信息
delete b, boy
from beauty b
inner join boys on b.boyfriend_id = boys.id
where boys.boyName=‘黄晓明’;

方式二：truncate
案例1：清空全部数据
truncate table boys;

delete与truncate

1、delete可以加where条件，truncate 不能加
2、truncate删除，效率高一点
3、假如要删除的表中有自增长列，
如果delete删除后，再插入数据，自增长列值从断点开始。
而truncate删除后，再插入数据，自增长列的值从1开始
4、truncate删除没有返回值，delete删除有返回值
5、truncate删除不能回滚，delete删除可以回滚

二、DDL语言

1、库的管理
创建、修改、删除
2、表管理
创建、修改、删除
创建：create
修改：alter
删除：drop

1.库的管理

1）库的创建
语法
create database 【if not exists】 库名
案例：创建books
create database books;

2）库的修改
rename database books to 新库名;

更改库的字符集
alter database books character set gbk

3）库的删除
drop database if exists books

2.表的管理

1)表的创建

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

)

create table book(
id int,
bName varchar(20)
price double,
authorId int,
publishDate datetime
)
desc book

cteate table author(
id int,
auName varchar(20),
nation varchar(10)
)

2）表的修改

alter table 表名 add|drop|modify|change column 列名【列类型 约束】
①修改列名
alter table book change column publishDate 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,‘金庸’,‘中国’)
a.仅仅复制表的结构
create table copy like author

b.复制表的结构+数据
create table copy2
select * from author

c.只复制部分数据
create table copy3
select * from author
where nation=‘中国’;

d.仅仅复制某些字段
create table copy4
select id,au_name from author
where 1=2|0;

3、常见的数据类型

数值型：
整型
小数：定点数，浮点数

字符型：
较短的文本：char，varchar
较长的文本：text、blob（较长的二进制数据）

日期型：

1.整数

分类

tinyint	smallint	mediumint	int、integer	bigint
1	2	3	4	8

特点
①如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加usigned关键字
②如果插入的数值超过了整型的范围，会报异常，并且插入临界值
③如果不设置长度，会有默认的长度
长度代表了显示的最大长度，如果不够会用0在左边填充，但必须搭配zerofill使用

create table tab_int(
t1 int 【zerofill】
t2 int unsigned(无符号)
)

2.小数

1）浮点型
float(M,D)
double(M,D)
2）定点型
dec(M,D)
decimal(M,D)
特点
M=整数部分+小数部分
D：小数位数
如果超过范围，则插入临界值

cteate table tab_float(
f1 float(5,2),
f2 double(5,2),
f3 decimal(5,2)
)

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

4、字符型

较短的文本
char
varchar

binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合

较长的文本
text
blob(较大的二进制)

create table tab_char(
c1 enum(‘a’,‘b’,‘c’)
)

insert into tab_char values(‘a’)
insert into tab_char values(‘a’)
insert into tab_char values(‘A’)
insert into tab_char values(‘M’)

cteate table tab_set(
s1 set(‘a’,‘b’,‘c’,‘d’)
)

5、日期型

分类：
date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间
timestamp 保存日期+时间

create talbe tab_date(
t1 datetime
t2 timestamp
)

6、常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：
六大约束
not null 非空，用于保证该字段的值不能为空
比如姓名、学号等

default：默认，用于保证该字段有默认值
比如性别
primary key：主键，用于保证该字段的值具有唯一性，且非空
比如学号、员工编号

unique：唯一，用于保证该字段的值具有唯一性，可以为空
比如座位号

check：检查约束 mysql不支持
比如性别和年龄

foreign key 用于限制两个表的关系，用于保证该字段值必须来自于主表的关联列的值

约束的添加分类：
列级约束：六大约束语法上都支持，但外键约束没有效果
表级约束：除了非空，其他都支持

1、创建表时添加约束

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

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)
)

DESC stuinfo
#查看stuinfo表中所有的索引，包括主键，外键，唯一
SHOW INDEX FROM stuinfo

2.添加表级约束

语法：在各个字段的最下面
constraint 约束名 约束类型(字段名)

外键：①要求在从表设置外键关系
②从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
③主表的关联列必须是一个key(一般是主键或唯一)
④插入数据时，先插入主表，在插入从表
删除数据时，先删除从表，再删除主表

CREATE TABLE stuinfo(
id INT,
stuName VARCHAR(20),
gender(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)
)

3.修改表时添加非空约束

①添加非空约束
alter table stuinfo modify column stuname varchar(20) not null

②添加默认约束
alter table stuinfo modify column age int defautl 18

③添加主键
a.列级约束
alter table stuinfo modify column id int primary key;
b.表级约束
alter table stuinfo add primary key(id);

④添加唯一性
a.列级约束
alter table stuinfo modify column seat int unique;
b.表级约束
alter table stuinfo add unique(id);

⑤添加外键
alter table stuinfo add constraint fk_stuinfo_major（约束名） foreign key (majorid) references major(id)

4修改表时删除约束

①删除非空约束
alter table stuinfo modify column stu那么 varchar(20) null;

②删除默认约束
alter table stuinfo modify column age int

③删除主键
alter table stuinfo drop primary key

④删除唯一
alter table stuinfo drop index 索引名

⑤删除外键
alter table stuinfo drop foreign key 约束名

show index from stuinfo查看表索引关系

7、标识列

又称为自增长列
含义：可以不用手动的插入值，系统提供默认的序列值
标识列必须和主键搭配吗?不一定，但要求是一个key
一个表可以有几个标识列吗？最多有一个
标识列的类型只能是数值型
标识列可以通过 set auto_increment_increment=3，设置步长

1、创建表时的设置标识列

create table tab_identity(
id int primary key auto_increment,
name varchar(20)
)

show variables like ‘auto_increment%’

set auto_increment_increment=3

2.修改表时设置标识列

alter table tab_indentity modify column id int primary key auto_increment;

3.修改表时删除标识列

alter table tab_indentity modify column id int primary key ;

级联删除
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(marjorid) REFERENCES major(id) ON DELETE CASCADE
级联置空
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(marjorid) REFERENCES major(id) ON DELETE set null