数据库（Mysql表操作）
一、mysql存储引擎
1.1 常用引擎：
- 第一种方式: (Myisam: 是5.5之前默认的存储引擎)
- 数据存在硬盘上,存三个文件,表结构,数据,和搜索目录
- 既不支持事务、也不支持外键、不支持行级锁
- 只支持表锁
- 对于只读操作比较多的情况 查询速度相对快
- 第二种方式: (Innodb: 是5.6之后的默认存储引擎)
- 数据存在硬盘上,存两个文件,表结构,(数据和搜索目录)
- 支持事务
- 支持行级锁
- 支持外键
- 第三种方式: (Memory)
- 数据存在内存中,存一个文件,表结构(在硬盘上)
- 数据容易丢失,但读写速度都快
1.2 存储引擎相关sql语句：
点击查看代码
查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";
查询当前数据库支持的存储引擎
mysql> show engines \G;
1.3 在建表时指定引擎类型：
点击查看代码
mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM;
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;
也可以使用alter table语句，修改一个已经存在的表的存储引擎。
mysql> alter table ai engine = innodb;
1.4 配置文件中指定(my.ini)：
点击查看代码
#my.ini文件
[mysqld]
default-storage-engine=INNODB
二、创建表与查询结构
2.1 创建表的语法结构：
点击查看代码
#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);
#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
2.2 查看表结构：
describe [tablename];这种方法和desc [tablename];效果相同；可以查看当前的表结构
show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（\G选项的含义是使记录能够竖向排列，以便更好的显示内容较长的记录。）
点击查看代码
# 查看表结构：
mysql> describe staff_info; # 方式一
mysql> desc staff_info; # 方式二
mysql> show create table staff_info\G; # 方式三
三、mysql中的数据类型
3.1 常用数据类型：
点击查看代码
mysql中的基础数据类型:
数值类型:
int --> 大整数值(4字节)
float --> 单精度(4字节)
字符串类型:
char --> 定长字符串(0-255字节)
varchar --> 变长字符串(0-65535 字节)
时间类型:
datetime --> 年月日时分秒(YYYY-MM-DD HH:MM:SS)
set和enum类型:
enum --> 单选(一次选取一个值)
set --> 多选(一次选择多个值)
3.2 示例：
整型与浮点数示例
# 整型与浮点数示例：
i系列：
create table i1(id1 int,id2 tinyint,id3 int unsigned); # tinyint(小整数值:1字节)
create table i2(id1 int(2),id2 int(11)); # 对int类型的长度进行的约束无效
浮点数系列 f系列：
create table f1(f float(5,2),d double(5,2),d2 decimal(5,2));
create table f2(f float,d double,d2 decimal);
create table f3(d double,d2 decimal(65,30));
# 总结：
# float精确到小数点后5位
# double能多精确一些位数,但仍然存在不精确的情况
# decimal默认是整数,但是通过设置,最多可以表示到小数点后30位
日期类型示例
# 日期：
# year(类型：now(),2019) # now()表示当前时间
# date(类型：now(),20191010 ,'2019-01-01')
# time(类型：now(),121212,'12:12:12')
# datetime(类型：now(),20191010121212,'2019-01-01 12:12:12')
# timestamp(建议少用：时间戳时间(4字节)快结束了)
# 时间示例：
# datetime 能表示的时间范围大 可以为空,没有默认值
# timestamp 能表示的时间范围小 不能为空,默认值是当前时间
create table time1(y year,d date,t time);
insert into time1 values (now(),now(),now()); # 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51
insert into time1 values (null,null,null); # NULL | NULL | NULL
create table time2(dt datetime,ts timestamp);
insert into time2 values(null, null); # NULL | 2019-04-23 18:15:04
create table time2(dt datetime default current_timestamp,ts timestamp); # 人为设置datetime类型的默认值是当前时间
字符串类型示例
# 字符串:
# char 能表示的长度小,浪费存储空间,读写效率快
# 定长字符串
# 在显示的时候会去掉所有空格显示,对用户的视觉造成欺骗
# varchar 能表示的长度大,节省存储空间,读写效率慢
# 变长字符串
# varchar(5) 'ab'-->'ab2' 'abc'-->'abc3' 'abcde'-->'abcde5' # 存储样式2表示ab为2个长度
# 示例：
create table s1(c char(4),v varchar(4));
insert into s1 values ('ab ','ab '); # 在检索的时候char数据类型会去掉空格
select length(v),length(c) from s1; # 来看看对查询结果计算的长度
select concat(v,'+'),concat(c,'+') from s1; # 给结果拼上一个加号会更清楚
insert into s1 values ('abcd ','abcd '); # 当存储的长度超出定义的长度，会截断
enum和set示例
# enum和set:
# 枚举,单选,且自动剔除不存在的选项
# enum('male','female')
# 集合,多选,自动剔除不存在的选项,自动去重
# set('洗脚','洗头','抽烟','喝酒','烫头')
# 示例：
create table es(name char(10),sex enum('male','female'),hobby set('洗脚','洗头','抽烟','喝酒','烫头'));
insert into es values('Lisa','male','烫头,抽烟,洗脚,按摩');
insert into es values('Annie','人妖','烫头'); # 不存在的选项则无法添加
insert into es values('Andy','male','抽烟,喝酒,喝酒,喝酒') # 自动去重
四、mysql表的完整性约束
为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：
字段类型
# 字段类型：
# 设置整形无符号 int unsigned
# 设置默认值 default
# 是否可以为空 not null
# 是否唯一 unique
# 自增 auto_increment
# 主键 primary key
# 外建 foreign key
示例：
not null类型示例
# not null类型示例：
# 表结构 : id,name,phone,sex
create table stu1(
id int,
name char(12) not null, # 不能为NULL
phone char(11),
sex enum('male','female')
);
not null + default 类型示例
# not null + default 类型示例：
create table stu2(
id int,
name char(12) not null,
phone char(11),
sex enum('male','female') not null default 'male'
)
唯一(unique)示例
# 唯一(unique)示例:
# unique只是约束在char数据类型内不能重复,但是不能约束null
id name ident
create table stu3(
id int,
name char(12),
ident char(18) unique
)
联合唯一 (unique)示例
# 联合唯一 (unique)示例：
# 一台机器上跑着多少个服务
# 把每一个正在运行的应用程序的信息都统计下来
# ip + port
# 192.168.16.13 mysql 3306
# 192.168.16.13 kugou 8080
# 192.168.16.13 flask 5000
# 192.168.16.15 mysql 3306
# 192.168.16.16 mysql 3306
create table service(
id int,
ip char(15),
name char(15),
port int(5),
unique(ip,port) # 联合唯一
)
auto_increment 自增的条件示例
# auto_increment 自增的条件(这一列必须是数字,这一列必须是uniuqe)示例：
# userinfo
# 1,alex,'alex3714'
create table userinfo(
id int unique auto_increment,
name char(12),
password char(32)
)
not null 非空 + unique 唯一 示例
# not null 非空 + unique 唯一 == primary key示例：
# 登录时候的用户名 一定是唯一的
create table userinfo3(
id int unique,
username char(18) not null unique,
password char(32),
ident char(18) not null unique
)
create table pri1(
id1 int unique not null,
id3 int unique not null
)
# 一张表中只能有一个主键 : 主键从约束的角度上来说 就是非空且唯一的
# 只不过,非空+唯一可以设置多个字段,但是主键只能给一个表中的一个字段设置
auto_increment = not null
create table userinfo2(
id int unique auto_increment,
username char(18) not null unique,
password char(32),
ident char(18) not null unique
)
主键 primary key示例
# 主键 primary key :在约束中就是非空 + 唯一 示例：
#一般情况下,我们给id字段设置为主键,不允许一张表不设置主键
create table pri2(
id1 int primary key,
id3 int unique not null
)
create table pri3(
id1 int primary key,
id3 int primary key
) # 报错 一张表只能有一个主键
联合主键 示例
# 联合主键 : 约束多个字段各自不能为空,并且联合唯一 示例：
create table pri4(
id1 int,
num int,
primary key(id1,num)
);
外键示例
# 外键
# 创建两张表
#
# 表2 班级表 cid class_name
create table clas(
cid int primary key,
class_name char(20)
)
# 表1 学生表 id name class_id
create table stu(
id int primary key ,
name char(18),
class_id int,
foreign Key(class_id) references clas(cid) # 设置外键
)
# 总结：
# 有外键之后所有的新增和删除都会受到外表的约束
# 比如
# 如果新增了一个学生所在的班级不存在,那么不能写入学生
# 如果删除一个还有学生指向的班级,也不能删除,也不能修改外键指向的键
级联更新 级联删除 示例
# 级联更新 级联删除 示例：
create table stu4(
id int primary key ,
name char(18),
class_id int,
foreign Key(class_id) references clas(cid)
on update cascade on delete cascade # 设置联合更新和删除(关联表操作)
)
五、 修改表结构
5.1 修改表的语法结构：
修改表的语法结构
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
5.2 示例
表修改(alter)示例
# 表重命名
mysql> alter table staff_info rename staff;
# 删除sex列
mysql> alter table staff drop sex;
# 添加列
mysql> alter table staff add sex enum('male','female');
# 修改id的宽度
mysql> alter table staff modify id int(4);
# 修改name列的字段名
mysql> alter table staff change name sname varchar(20);
# 修改sex列的位置
mysql> alter table staff modify sex enum('male','female') after sname;
# 创建自增id主键
mysql> alter table staff modify id int(4) primary key auto_increment;
# 删除主键，可以看到删除一个自增主键会报错
mysql> alter table staff drop primary key;
# 需要先去掉主键的自增约束，然后再删除主键约束
mysql> alter table staff modify id int(11);
# 添加联合主键
mysql> alter table staff add primary key (sname,age);
# 删除主键
mysql> alter table staff drop primary key;
# 添加主键id
mysql> alter table staff add primary key (id);
# 为主键添加自增属性
mysql> alter table staff modify id int(4) auto_increment;
DROP TABLE 表名; # 删除表
alter操作非空和唯一
create table t(id int unique,name char(10) not null);
#添加null约束
alter table t modify name char(10) null;
#去掉null约束
alter table t modify name char(10) not null;
# 去掉unique约束
alter table t drop index id;
# 添加unique约束
alter table t modify id int unique;
alter处理null和unique约束
alter操作主键
1、首先创建一个数据表table_test：
create table table_test(
`id` varchar(100) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`name`)
);
2、如果发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。
先删除主键
alter table table_test drop primary key;
然后再增加主键
alter table table_test add primary key(id);
注:在增加主键之前,必须先把反复的id删除掉。
为表添加外键
创建press表
CREATE TABLE `press` (
`id` int(11) NOT NULL,
`name` char(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ；
创建book表
CREATE TABLE `book` (
`id` int(11) DEFAULT NULL,
`bk_name` char(12) DEFAULT NULL,
`press_id` int(11) NOT NULL,
) ；
为book表添加外键
alter table book add constraint fk_id foreign key(press_id) references press(id); # constraint fk_id (指定外键名，可忽略不写)
删除外键
alter table book drop foreign key fk_id; # fk_id 外键名(可查看： show create table 表名)
5.3 修改编码类型
点击查看代码
# 修改字段编码类型
alter table 表名 modify 字段名 字段类型 character set "utf-8"
alter table tab1 modify name varchar(30) character set "utf-8"
# 修改表编码类型
alter table 表名 default character set "utf-8"
alter table tab2 default character set "utf-8"
# 修改库编码类型
alter database 库名 default character set "utf-8"
alter database db1 default character set "utf-8"
六、多表结构的创建与分析(一对多，多对多，一对一)
6.1 建立表之间 的关系：
一对多
#一对多或称为多对一
三张表：出版社，作者信息，书
一对多（或多对一）：一个出版社可以出版多本书
关联方式：foreign key
一对多示例
=====================多对一=====================
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
多对多
#多对多
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多
关联方式：foreign key+一张新的表
多对多示例
=====================多对多=====================
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('yuanhao'),('wpq');
#每个作者与自己的代表作如下
egon:
九阳神功
九阴真经
九阴白骨爪
独孤九剑
降龙十巴掌
葵花宝典
alex:
九阳神功
葵花宝典
yuanhao:
独孤九剑
降龙十巴掌
葵花宝典
wpq:
九阳神功
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
一对一
#一对一
两张表：学生表和客户表
一对一：一个学生是一个客户
关联方式：foreign key+unique
一对一示例
create table customer(
-> id int primary key auto_increment,
-> name varchar(20) not null,
-> qq varchar(10) not null,
-> phone char(16) not null
-> );
create table student(
-> id int primary key auto_increment,
-> class_name varchar(20) not null,
-> customer_id int unique, #该字段一定要是唯一的
-> foreign key(customer_id) references customer(id) #外键的字段一定要保证unique
-> on delete cascade
-> on update cascade
-> );
#增加客户
mysql> insert into customer(name,qq,phone) values
-> ('韩蕾','31811231',13811341220),
-> ('杨澜','123123123',15213146809),
-> ('翁惠天','283818181',1867141331),
-> ('杨宗河','283818181',1851143312),
-> ('袁承明','888818181',1861243314),
-> ('袁清','112312312',18811431230)
mysql> #增加学生
mysql> insert into student(class_name,customer_id) values
-> ('脱产1班',3),
-> ('周末1期',4),
-> ('周末1期',5)
-> ;
七、设置数据的严格模式
7.1 查看数据库当前模式：
select @@sql_mode
7.2 设置数据严格模式(临时):
# server重启后失效：
set sql_mode="STRICT_TRANS_TABLES"
7.3 not null 不生效：
设置严格模式：
不支持对not null字段插入null值
不支持对自增长字段插入”值
不支持text字段有默认值
直接在mysql中生效(重启失效):
mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
配置文件添加(永久失效)：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
原文链接 https://www.cnblogs.com/WiseAdministrator/articles/10758977.html
浙公网安备 33010602011771号