DML DDL 约束外键 数据类型 标识列

一、DML 语言

insert
update
delete

1. 插入语句insert

# 方式1语法
insert into 表名(列名,...) values(值1,...)，(值2,...)...;

# 方式2语法
insert into 表名
set 列名1=值1,列名2=值2,...;

#注意点:
1.插入的值类型要与列类型一致或兼容
2.不可以为null的列必须有值,可以为null的列可以插入null或者直接不写.
3.列的顺序可以调换,但是要和值匹配.
4.可以省略列名,默认所有列,而且列的顺序和表的顺序一致.

# 方式1可以一次插入多行,方式2不支持
语法:
insert into 表
values
(id1,name1,...),
(id2,name2,...),
(id3,name3,...),
...;

# 方式1
insert into 表(id,name,phone)
select 21,'Tom','119';
效果等于插入一条21,Tom,119的数据.

#从别的表查询多行结果插入到本表：
1.不支持此方式多条插入
insert into 表1(id,name,phone)
select 表2.id,表2.name,表2.phone
from 表2 where id<3;

2.需要使用如下方式：
insert into 表1(select 表2.id,表2.name,表2.phone
from 表2 where id<3);
或者
insert into 表1(select * from 表2 where id<3);

"表的列内容要匹配"

2. 修改语句update

修改单表的记录

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

修改多表的记录

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

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

# 案例:修改没有bf的女表中的bf编号为9
mysql> select * from tf;
+------+------+------+
| id   | name | bf   |
+------+------+------+
|    1 | fa   |    2 |
|    2 | fb   |    3 |
|    3 | fc   |    4 |
|    4 | fd   |    2 |
+------+------+------+
mysql> select * from tm;
+------+------+
| id   | name |
+------+------+
|    1 | ma   |
|    2 | mb   |
|    3 | mc   |
+------+------+

第一步:查看要筛选的条件
mysql> select tf.*,tm.id from tm right join tf on tf.bf=tm.id;
+------+------+------+------+
| id   | name | bf   | id   |
+------+------+------+------+
|    1 | fa   |    2 |    2 |
|    4 | fd   |    2 |    2 |
|    2 | fb   |    3 |    3 |
|    3 | fc   |    4 | NULL |
+------+------+------+------+

mysql> select tf.*,tm.id mid from tm right join tf on tf.bf=tm.id where tm.id is null;
+------+------+------+------+
| id   | name | bf   | mid  |
+------+------+------+------+
|    3 | fc   |    4 | NULL |
+------+------+------+------+

第二步:修改编号
mysql> update tm m
    -> right join tf f on m.id=f.bf
    -> set f.bf=9
    -> where m.id is null;

mysql> select * from tf;
+------+------+------+
| id   | name | bf   |
+------+------+------+
|    1 | fa   |    2 |
|    2 | fb   |    3 |
|    3 | fc   |    9 |
|    4 | fd   |    2 |
+------+------+------+

3. 删除语句delete

 方式一 语法:
# 1.单表数据的删除
delete from 表名 where 条件;

# 2.多表数据的删除 
sql192语法
delete 表1的别名,表2的别名
from 表1 别名,表2 别名
where 连接条件
and 筛选条件;

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

用左外连接查看
mysql> select tf.*,tm.* from tm left join tf on tf.bf=tm.id; 
+------+------+------+------+------+
| id   | name | bf   | id   | name |
+------+------+------+------+------+
|    1 | fa   |    2 |    2 | mb   |
|    2 | fb   |    3 |    3 | mc   |
|    4 | fd   |    2 |    2 | mb   |
| NULL | NULL | NULL |    1 | ma   |
+------+------+------+------+------+

右外连接查看
mysql> select tf.*,tm.* from tf left join tm on tf.bf=tm.id;  
+------+------+------+------+------+
| id   | name | bf   | id   | name |
+------+------+------+------+------+
|    1 | fa   |    2 |    2 | mb   |
|    4 | fd   |    2 |    2 | mb   |
|    2 | fb   |    3 |    3 | mc   |
|    3 | fc   |    9 | NULL | NULL |
+------+------+------+------+------+

mysql> delete tf from tf left join tm on tf.bf=tm.id where tm.id=3;
# 结果删除了女表中bf为mc的数据,男表不受影响.
mysql> select * from tf;
+------+------+------+
| id   | name | bf   |
+------+------+------+
|    1 | fa   |    2 |
|    3 | fc   |    9 |
|    4 | fd   |    2 |
+------+------+------+
把需要要删除数据的表放在delete的后面.

方式二: truncate
truncate table 表名;
清空所有数据

# truncate 和 delete 的区别
1.truncate效率比delete高一点点
2.如果要删除的表中有自增长列,
再插入数据时用delete删除的自增长列的值从断点开始.
而truncate是从1开始.
3.delete删除有返回值,truncate没有返回值
4.truncate删除不能回滚,delete可以回滚.

二、DML 语言

1. 库的管理

# 创建库
create databases 库名;
create databases if not exists 库名;

# 修改库名(会导致数据丢失,不推荐修改)
rename database books to 新库名
或者停库后直接修改路径下物理文件名.

# 创建库设置默认字符集
CREATE DATABASE book DEFAULT CHARACTER SET utf8;

# 更改字符集
alter database 库名 char set utf8;

# 删除库
drop database if exists 库名;

2. 表的管理

# 创建表
create table 表名(
    列名 列类型[(长度) 约束],
    列名 列类型[(长度) 约束],
    ...)

1.如果指定了if not exists语句来创建表,如果表存在,也不会报错
#2.创建表的语句不会验证要创建的表与已经存在的表的结构是否一致,只要名字相同就不会创建.
#3.创建表时指定的varchar长度为字符长度.(英文和中文字都为1个字符.)

# 创建表设定默认字符集和存储引擎
CREATE TABLE aaa(id int,name char(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 修改表
语法:
alter table 表名 change|add|drop|modify 列名 新列名 类型[(长度) 约束];


1. #修改列名
alter table 表名 change [column] 列名 新列名 类型[(长度) 约束];

2. #修改列的类型和约束
alter table 表名 modify [column] 列名 新列名 类型[(长度) 约束];

3. #添加新列
alter table 表名 add [column] 新列名 类型[(长度) 约束];

4. #删除列
alter table 表名 drop [column] 列名;

5. #修改表名
alter table 表名 rename [to] 新表名;

# 表的删除
drop table if exists 表名;

# 表的复制
1.复制表结构
create table 新表名 like 表名;

2.仅复制某些字段
create table 新表名
select 表1.列1,表1.列2,...
from 表1;

3.复制表结构+数据
create table 新表 select * from 表名;

# 以上2和3会有如下报错:

# 错误原因
这是因为在5.6及以上的版本内，开启了 enforce_gtid_consistency=true 功能导致的，MySQL官方解释说当启用 enforce_gtid_consistency 功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

# 解决方法
方法一：关闭gtid
修改 ：SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;
配置文件中 ：ENFORCE_GTID_CONSISTENCY = off;

# 方法二：
create table 表名 select 的方式会拆分成两部分。

create table 新表名 like 表名;
insert into 新表名 select * from 表名;

三、常见数据类型的使用

数值型:
    整型
    小数:
        定点数
        浮点数
字符型:
    较短的文本: char  varchar
    较长的文本: text  blob(较长的二进制数据)
日期型

1.整型

 如果不设置长度.有些会设置默认的最大长度.
 如果不设置,默认有负数,设置无符号用unsigned
# 当插入值超出范围的时候会有警告提示,然后实际插入的值为临界值.
 长度可以不指定,默认会有最大宽度,不过不够则左边用0填充,需要搭配zerofill.并且默认会变为无符号的整型.

2.小数型

浮点型
float(M,D)
double(M,D)
定点型
dec(M,D)
decimal(M,D)

#特点1:
M 和 D
M表示整数和小数的位数和
D表示小数的位数
如果超出也会自动写入临界值

#特点2:
M和D都可以省略,
如果是decimal 则M默认为10,D默认为0
如果是float和double,默认最大范围

#特点3:
定点型的精度更高,比如货币运算.

3.字符型

# 常用字符型
char  固定长度 字符数
varchar 可变长度 字符数

# 特点对比
# 注意 都是字符数,中文字也为1个字符.
char(最大长度)    耗费空间 效率高 可以省略长度.默认1
varchar(最大长度) 节省空间 效率低 不可省略长度

# 较长的文本
text

# 保存较短的二进制字符串(类似char和varchar)
binary 
varbinary

# 保存较长的二进制
blob 

# 枚举型
enum 不区分大小写
例如
create table 表(a1 enum('a','b','c'));
insert into 表 values('a');  成功
insert into 表 values('m');  警告,会插入null
insert into 表 values('A');  也可以成功

# set 集合类型
不区分大小写
如下图

4.日期型

# timestamp使用更多,耗费少,受时区影响更准确反映当地时间.

5.json类型 (5.7以后版本才支持)

{
id:101
name:'aaa'
}
以后mysql定位会偏向nosql.

四、常见约束

1.添加约束

## 一. 添加约束
1.创建表时添加约束
2.修改表时添加约束
alter table stu modify name varchar(20) not null;

# 表级 列级
1.列级约束(6大约束语法上都支持.但外键会无效)
2.表级约束(除了非空,默认,其他约束都支持.)

# 列级约束写法:
alter table 表名 modify 字段名 字段类型 新约束;
例如:
alter table stu modify id int primary key;

# 表级约束写法:
alter table 表名 add 【constraint 约束名】 约束类型(字段名)
例如:
alter table stu add primary key(id);

"只有同时支持两种约束的才可以使用两种方式."

=========================

2.添加外键

# 表级约束的添加 (主要是用来添加外键)
语法:在各个字段的最下面添加
【constraint 约束名】 约束类型(字段名)

例如:
create table stu(
    id int,
    name varchar(20),
    gender char,
    seat int,
    age int,
    majorid int,  #以上列不带约束,通过表级约束来做.
    constraint pri_key primary key(id), #主键
    constraint uni_key unique(seat), #唯一键
    constraint ck check(gender ='男' or gender ='女'), #检查键
    constraint out_info foreign key(major) references major(id) #外键
);

通用写法:
create table stu(
    id int primary key,
    name varchar(20) not null,
    age int default 18,
    ...
    majorid int,
    constraint fk_stu_major foreign key(majorid) references major(id)
);
# fk_stu_major 为外键,stu本表的majorid列连接到主表major的id列.

# 添加外键的要求:
1.需要在从表设置外键关系
2.从表的外键列的类型和主表的关联列的类型要求一致或者兼容.名称无要求.
3.'主表的关联列必须是一个key(主键或唯一键)'
4.添加数据时,先插入主表,再插入从表,删除数据时,先删除从表,再删除主表.

# 直接添加外键的方式:
alter table stu add foreign key(majorid) references major(id);

# 删除外键
1. 级联删除 (删除从表内容的同时也删除主表中外键对应的行内容.)
alter table stu add constraint key1 foreign key(majorid) references major(id) on delete cascade;
delete from major where id=3;

2.级联置空 (删除从表内容的同时不删除主表中外键对应的行内容并而是将它设置为null.)
alter table stu add constraint key1 foreign key(majorid) references major(id) on delete set null;

3.主键和唯一键

主键和唯一键的区别:
1.主键   不能为空  只能有一个主键
2.唯一键 可以为空(但是只能有一个null,2个null即为重复) 可以有多个唯一键

# 组合主键/唯一键: 可以多列使用一个主键 / 唯一键
create table stu(
    id int,
    stuname varchar(20),
    gender char,
    seat int,
    ...
    primary key(id,stuname), #两个列共用1个主键
    unique(gender,seat), #两个列共用1个唯一键
    ...

'将id和stuname作为组合主键:'
只有当id和stuname同时相同的情况下才会产生主键重复.
如下图:不是2个主键,而是组合主键

4.修改表时删除约束

1.删除非空约束
alter table stu modify name vachat(20) null;  #null也可以不写

2.删除默认约束
alter table stu modify age int;

3.删除主键
alter table stu drop primary key;  #无需指定,一个表只有1个主键.

4.删除唯一键
alter table stu drop index seat; 

5.删除外键
alter table stu drop foreign key fk_stu_major;

实例测试:

5.标识列

1. 又称为自增长列,由系统提供默认的序列值.
2. 必须是一个key(主键,唯一键,外键...)并且一个表中最多只能有1列.
3. 标识列的类型必须是整数型.

# 1.创建表时创建标识列auto_increament
create table id int primary key auto_increment;

mysql> show variables like '%auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
#mysql中不支持修改默认起始值.修改后不起作用.但是手动插入初始序列值即可达到效果.

可以修改默认步长
set auto_increment_increment=3

# 2.修改标识列auto_increament
alter table 表 modify id int;