数据库（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('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类型示例：
# 表结构 : 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 类型示例：
create table stu2(
id int,
name char(12) not null,
phone char(11),
sex enum('male','female') not null default 'male'
)

　　

# 唯一(unique)示例:
# unique只是约束在char数据类型内不能重复,但是不能约束null
id name ident
create table stu3(
    id int,
    name char(12),
    ident char(18) 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 自增的条件(这一列必须是数字,这一列必须是uniuqe)示例：
# userinfo
# 1,alex,'alex3714'
create table userinfo(
  id int unique auto_increment,
  name char(12),
  password char(32)
)

　　

# 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  :在约束中就是非空 + 唯一 示例：
    #一般情况下,我们给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 示例

　　

# 表重命名
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 表名;  # 删除表

　　

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约束

　　

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"