2-MySQL数据库之数据的增删改

一、创建表

# 语法
create table 表名(字段名1 数据类型1, 字段名2 数据类型2, 字段名3 数据类型3);

1.1 常见的字段数据类型

varchar：可变长字符串。根据实际数据长度动态分配空间。（省空间，但是效率比char低）
char：定长字符串。（不管实际数据长度多少，分配固定的长度存储数据，使用不当会导致空间浪费）（最长255）
int：数字中的整数型
bigint：长整型
float：单精度浮点型
double：双精度浮点型
date：短日期类型
datetime：长日期类型
clob：字符大对象（用于存储文字类型的）（小于255字符用char，大于255字符用clob）
blob：二进制大对象（用于存储图片、声音、视频等流媒体数据）

# 创建表，并设置字段的数据类型。default是如果未指定值，则显示默认值。
create table stu(id int(3),name varchar(8), sex char(2) default '男',email varchar(30),bir date);
# 快速创建表(就是复制表)
create table t_stu2 as select * from t_stu;
# 快速创建表（将查询结果复制到一个新表）
create table stu as select id,name from stu where name like "%二%";

1.2 创建表的约束

创建表的时候可以给字段加上约束，保证表中的数据有有效

外键约束：foreign key（简称fk）
检查约束：check(mysql不支持，oracle支持)

1.2.1 非空约束（not null）

该字段的值不能未空值

create table t(id int(4),name varchar(6) not null,sex chat(2));

1.2.2 唯一性约束（unique）

表内的这个字段的值是唯一的，不能重复，但是可以未null

# 某个字段唯一
create table t(id int(4) unique,name varchar(6) not null,email varchar(20));

# 姓名和邮箱两个字段联合起来要有唯一性，and关系。两个字段都要唯一
create table t(id int(4),name varchar(6) not null,email varchar(20),unique(name,email));

1.2.3 主键约束（primary key）

主键的作用：
主键是每一行记录的唯一标识，是唯一身份认证，不能为空。任何一张表都要有一个主键
主键的特征：not null + unique

# 给表添加主键约束
create table t(id int(4) primary key,name varchar(6) not null,email varchar(20),unique(name,email));
# 也可以用表级约束来写
create table t(id int(4),name varchar(6) not null,email varchar(20),unique(name,email), primary key(id));
# 复合主键(两个字段联合起来做主键。and关系。不建议使用复合主键，用单一主键即可)
create table t(id int(4),name varchar(6) not null,email varchar(20),unique(name,email), primary key(id,name));

# 主键值建议用：int bigint char类型，不建议使用varchat类型。主键一般是数字，一般是定长的
# 主键分为自然主键（比如id编号）和业务主键（比如身份证号）。实际自然主键用的多，因为当业务发生变动时可能会影响到主键值
在mysql中有一种机制可以自动维护主键值（自增：auto_increment）
create table t(id int(4)primary key auto_increment,name varchar(6) not null);
insert into t (name)values('张三');
insert into t (name)values('李四');
# mysql会自动给ID从1开始，自增1

1.2.4 外键约束（foreign key）

外键约束相当于网页的下拉框，只能选择指定的选项。

场景：很多地方需要引用市里行政办事处的地址，但是办事处的地址很长，且是固定的。如果每张表都存完整的地址的话将占用很大的空间，且不便于维护（如果地址变动，所有的表的地址都得修改）
可以给行政办事处新建一个独立的表，让其他需要引用这个地址的表调用其编号即可。这样就可以解决上述的两个问题。

一个表的外键可以为空值，若不为空值，则每一个外键的值必须等于另一个表中主键的某个值。

外键约束的语法：
[CONSTRAINT 外键名] FOREIGN KEY (字段名) REFERENCES 父表名(字段名)
CONSTRAINT 外键名 可忽略不写

# 注：被引用的外键的表称为父表，去引用的表称为主表。父表被引用的编号要有唯一性
create table banshichu(id int(8) primary key,address varchar(80));

create table zhinan(id int(8) primary key auto_increment,addr int(8), constraint fk_test foreign key(addr) references banshichu(id));

insert into banshichu(id,address)values(1000,'广东省广州市天河区子虚街道1234号1楼行政办事处');
insert into banshichu(id,address)values(1001,'广东省广州市天河区乌有街道4444号3楼302号行政办事中心');

insert into zhinan(`addr`)values(1000);
insert into zhinan(`addr`)values(1001);
insert into zhinan(`addr`)values(1002);

二、插入数据（insert）

2.1 插入数据

# 语法：
insert into 表名(字段名1，字段名2，字段名3) values (值1，值2，值3);
inster into 表名 values(值1，值2，值3); 
# 可以不加字段名，但是值要按顺序写

# 按key中自己写的字段名顺序插入数据
insert into t_stu(id,name,sex,email) values('1','张三','男','1@qq.com')；
# 若为指定key，则按表字段顺序一次插入数据
insert into t_stu values('2','李四','女','2@qq.com');
# 一次插入多条记录
insert into t_stu(id,name,bir,createtime)values('1','张三','1998-01-02',now()),('2','李四','1999-02-03',now()),('3','王五','1996-03-04',now());
# 将查询结果插入到一张存在的表中。很少用，因为对表的结构有要求
insert into t_stu select * from t_test;

2.2 插入日期

str_to_date：将字符串varchar类型转为date类型
date_format：将date类型转换成具有格式的varchar类型
mysql里日期的表示方式：
年：Y
月：m
日：d
时：h
分：i
秒：s

2.2.1 字符串转日期（str_to_date）

# 插入日期
# 以下插入会报错。因为bir是date类型，而插入的是字符串类型
insert into t_stu(id,name,bir) values('1','王五','01-01-2020');
# 插入日期格式：
insert into t_stu(id,name,bir) values('1','王五',str_to_date('01-01-2020','%d-%m-%Y'));
# 如果年月日用‘2000-01-01’表示，则可以省略str_to_date
insert into t_stu(id,name,bir) values('1','王五','2000-01-01');

2.2.2 日期转为字符串

将日期格式用指定字符串的方式展示
通常用在查询日期方面，用于展示日期格式

# 比如将查出来的日期用 年_月_日 的方式展示
select name,date_format(bir,'%Y_%m_%d') from t_stu;
# 不用date_format会自动将数据库中的date类型转换成varchar格式。展示结果是%Y-%m-%d
select name,bir from t_stu;

date和datetime的区别:
date:年月日(默认格式%Y-%m-%d)
datetime:包括年月日时分秒(默认格式%Y-%m-%d %h:%i:%s)
在mysql中获取当前系统时间:
使用now()可以获取当前系统的时间。是datetime类型的，(%Y-%m-%d %h:%i:%s)

三、改数据

3.1 修改数据（update）

语法：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3 where 条件;
注意：没有where条件限制会将所有数据全部更新！

update t_stu set name='张三',create=now() where id=1;

3.2 删除数据（delete）

语法： delete from 表名 where 条件;
注意：没有where条件限制会删除整张表的全部数据！

# 删除表指定条件的数据
delete from t_stu where id=1;
# 删除表中所有数据(删除速度比较慢，而且删除后的磁盘空间不会释放。优点是删除后数据可以恢复。回滚：rollback)
delete from t_stu;
# truncate快速删除数据（物理删除，删除速度快，不支持回滚）
truncate table t_stu;

3.3 删除表（drop）

语法：drop table 表名; // 当表不存在时会报错。所以建议用下面的语法：
drop table if exists 表名;

drop table if exists t_student;

四、表结构的增删改

对表字段的增删改查。
对表的结构修改最好使用工具。比如Navicat。

4.1 添加字段

# 给表t_stu添加一个email字段
alter table t_stu add email varchar(30);

4.2 修改字段

# 把email字段的属性修改
alter table t_stu modify email varchar(20);

4.3 删除字段

# 删除表的email字段
alter table t_stu drop email;