mysql数据库

create table 表名(
   字段名 类型(长度) 约束,
   字段名 类型(长度) 约束
);

例如：

创建分类表

CREATE TABLE sort (
  sid INT, #分类ID
  sname VARCHAR(100) #分类名称
);
 
温馨提示：你创建了数据库，就创建了一块逻辑空间，实际在磁盘上创建了一个文件夹，你创建了一个表，实际磁盘生成了一个.ibd文件，你可以在C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下验证一下，路径中的ProgramData是隐藏文件夹。

举个例子，你创建了test数据库，然后你执行建表语句如下

CREATE TABLE temp(/*实验精度丢失问题*/
    id INT UNSIGNED PRIMARY KEY,
    num DECIMAL(20, 10) /*数字总位数20，保留小数点后10位*/
)

实际在你的磁盘上是这样存储的

3.4.2 查看表
查看数据库中的所有表：

格式：

show tables;
 图形化结果类似于下图

这里的命名就告诉了你是 test 数据库里面的表

查看表结构：

有两种方式

方法一： desc 表名;

方法二： SHOW COLUMNS FROM 表名;

例如：

DESC student;
 
SHOW COLUMNS FROM student;
 
/* 这两种方式结果一模一样，第一种更常见，显然命令更短你也更愿意用 */
 图形化结果类似于下图

3.4.3 删除表
格式：drop table 表名;

例如：

drop table sort;
3.4.4 修改表结构格式（实际开发最常用）
alter table 表名 add 列名 类型(长度) 约束;

作用：修改表添加列.

例如：

#1，为分类表添加一个新的字段为 分类描述 varchar(20)

ALTER TABLE sort ADD sdesc VARCHAR(20);
当然，想添加多个字段分类怎么做呢？

/*添加多个列方法一*/
ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;
/*add语句之间用逗号分隔，最后用分号结束*/
 
/*添加多个列方法二*/
ALTER TABLE student
ADD (address VARCHAR(200) NOT NULL,home_tel CHAR(11) NOT NULL);
值得注意的是：

如果表需要添加多列，而有一列字段home_tel之前已经添加过了，结果会显示Duplicate column name 'home_tel'，那么你本次添加的多列字段都是无效的，即全部添加失败

如果我想将这个字段添加到表中间而不是末尾怎么办呢？

alter table 表名 add 列名 类型(长度) 约束 after 某个字段;

比如我想在age字段的后面加一个字段sex，而不是在最后一个字段末尾添加

alter table student add column sex char(1) not null comment '性别' after age;

alter table 表名 modify 列名 类型(长度) 约束;

作用：修改表修改列的类型长度及约束.

例如：

#2, 为分类表的分类名称字段进行修改，类型varchar(50) 添加约束 not null

ALTER TABLE sort MODIFY sname VARCHAR(50) NOT NULL; /* 添加约束NOT NULL */
 
ALTER TABLE student
MODIFY home_tel VARCHAR(20) NOT NULL; /*CHAR(11)修改为VARCHAR(200)*/
同理，和add类似，需要修改多个列的类型长度及约束，那么modify语句之间用逗号分隔，最后一句的末尾用分号结束。

alter table 表名 change 旧列名 新列名 类型(长度) 约束;

作用：修改表修改列名.

例如：

#3, 为分类表的分类名称字段进行更换 更换为 snamesname varchar(30)

ALTER TABLE sort CHANGE sname snamename VARCHAR(30);
同理，和add类似，需要修改多个列的字段名，那么change语句之间用逗号分隔，最后一句的末尾用分号结束。

直接来个例题：

假设有2个选项, 选择哪一个

A. ALTER TABLE cource CHANGE cname VARCHAR(30) NOT NULL FIRST;

B. ALTER TABLE cource MODIFY  cname VARCHAR(30) NOT NULL FIRST;

请注意CHANGE和MODIFY的区别, MODIFY可以修改字段类型、字段属性，而CHANGE可修改字段名称，并且CHANGE需要旧列名和新列名，答案是B

注意：change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不太方便，但是change的优点是可以修改列名称，modify则不行。

alter table 表名 drop 列名;

作用：修改表删除列.

例如：

#4, 删除分类表中snamename这列

ALTER TABLE sort DROP snamename;
 
ALTER TABLE student
DROP home_address,
DROP home_tel;
同理，和add类似，需要删除多列，那么drop语句之间用逗号分隔，最后一句的末尾用分号结束。

来一道选择题，题目是：删除数据表中多余的列的语句是哪些，有同学上去就选了个B，认为删除就是DELETE，这里的答案是AC。

rename table 表名 to 新表名;

作用：修改表名

例如：

#5, 为分类表sort 改名成 category

RENAME TABLE sort TO category;
alter table 表名 character set 字符集;

作用：修改表的字符集

例如：

#6, 为分类表 category 的编码表进行修改，修改成 gbk

ALTER TABLE category CHARACTER SET gbk;

字段属性
主键, 唯一键和自增长.

4.1 主键
主键: primary key,主要的键. 一张表只能有一个字段可以使用对应的键, 用来唯一的约束该字段里面的数据, 不能重复: 这种称之为主键.

一张表只能有最多一个主键, 主键请尽量使用整数类型而不是字符串类型

4.1.1增加主键
SQL操作中有多种方式可以给表增加主键: 大体分为三种.

方案1: 在创建表的时候,直接在字段之后,跟primary key关键字(主键本身不允许为空)

优点: 非常直接; 缺点: 只能使用一个字段作为主键

方案2: 在创建表的时候, 在所有的字段之后, 使用primary key(主键字段列表)来创建主键(如果有多个字段作为主键,可以是复合主键)

方案3: 当表已经创建好之后, 额外追加主键: 可以通过修改表字段属性, 也可以直接追加.

Alter table 表名  add primary key(字段列表);

前提: 表中字段对应的数据本身是独立的(不重复)

4.1.2 主键约束
创建约束的目的就是保证数据的完整性和一致性。

主键对应的字段中的数据必须唯一，且不能为NULL， 一旦重复,数据操作失败(增和改)

建议主键使用数字类型，因为数字的检索速度非常快，并且主键如果是数字类型，还可以设置自动增长。

主键的原理其实就是一个计数器。

4.1.3 更新主键 & 删除主键
没有办法更新主键: 主键必须先删除,才能增加.

Alter table 表名 drop primary key;

4.1.4 主键分类
在实际创建表的过程中, 很少使用真实业务数据作为主键字段(业务主键,如学号,课程号); 大部分的时候是使用逻辑性的字段(字段没有业务含义,值是什么都没有关系), 将这种字段主键称之为逻辑主键.

Create table my_student(
 
Id int primary key auto_increment comment ‘逻辑主键: 自增长’, -- 逻辑主键
 
Number char(10) not null  comment ‘学号’,
 
Name varchar(10) not null
 
)
 
4.2 自动增长
自增长: 当对应的字段,不给值,或者说给默认值,或者给NULL的时候, 会自动的被系统触发, 系统会从当前字段中已有的最大值再进行+1操作,得到一个新的在不同的字段.

自增长的字段必须定义为主键，默认起始值是1而不是0

4.2.1 新增自增长
自增长特点: 

   任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)，auto_increment表示自动编号

   自增长字段必须是数字(整型)

  一张表最多只能有一个自增长

4.2.2 自增长使用
当自增长被给定的值为NULL或者默认值的时候会触发自动增长.

自增长如果对应的字段输入了值,那么自增长失效: 但是下一次还是能够正确的自增长(从最大值+1)

如何确定下一次是什么自增长呢? 可以通过查看表创建语句看到.

4.2.3 修改自增长
自增长如果是涉及到字段改变: 必须先删除自增长,后增加(一张表只能有一个自增长)

修改当前自增长已经存在的值: 修改只能比当前已有的自增长的最大值大,不能小(小不生效)

Alter table 表名 auto_increment  = 值;

向上修改可以

思考: 为什么自增长是从1开始?为什么每次都是自增1呢?

所有系统的变现(如字符集,校对集)都是由系统内部的变量进行控制的.

查看自增长对应的变量: show variables like ‘auto_increment%’;

可以修改变量实现不同的效果: 修改是对整个数据修改,而不是单张表: (修改是会话级)

Set auto_increment_increment = 5; -- 一次自增5

测试效果: 自动使用自增长

4.2.4 删除自增长
自增长是字段的一个属性: 可以通过modify来进行修改(保证字段没有auto_increment即可)

Alter table 表名 modify 字段 类型;

4.3 唯一键
一张表往往有很多字段需要具有唯一性,数据不能重复: 但是一张表中只能有一个主键: 唯一键(unique key)就可以解决表中有多个字段需要唯一性约束的问题.

唯一键的本质与主键差不多: 唯一键默认的允许自动为空,而且可以多个为空(空字段不参与唯一性比较)

4.3.1 增加唯一键
基本与主键差不多: 三种方案

方案1: 在创建表的时候,字段之后直接跟unique/ unique key

方案2: 在所有的字段之后增加unique key(字段列表); -- 复合唯一键

方案3: 在创建表之后增加唯一键

4.3.2 唯一键约束
唯一键与主键本质相同: 唯一的区别就是唯一键默认允许为空,而且是多个为空.

如果唯一键也不允许为空: 与主键的约束作用是一致的.

4.3.3 更新唯一键 & 删除唯一键
更新唯一键

先删除后新增(唯一键可以有多个: 可以不删除).

删除唯一键

Alter table 表名 drop unique key; -- 错误: 唯一键有多个

Alter table 表名 drop index 索引名字; -- 唯一键默认的使用字段名作为索引名字

4.4 外键
外键: foreign key, 外面的键(键不在自己表中): 如果一张表中有一个字段(非主键)指向另外一张表的主键,那么将该字段称之为外键.

4.4.1 增加外键
外键可以在创建表的时候或者创建表之后增加(但是要考虑数据的问题).

一张表可以有多个外键.

创建表的时候增加外键: 在所有的表字段之后,使用foreign key(外键字段) references 外部表(主键字段)

在新增表之后增加外键: 修改表结构

Alter table 表名 add [constraint 外键名字] foreign key(外键字段) references 父表(主键字段);

4.4.2 修改外键&删除外键
外键不可修改

只能先删除后新增.

删除外键语法

Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键,但是名字不能相同

4.4.3 外键作用
外键默认的作用有两点: 一个对父表,一个对子表(外键字段所在的表)

对子表约束: 子表数据进行写操作(增和改)的时候, 如果对应的外键字段在父表找不到对应的匹配: 那么操作会失败.(约束子表数据操作)

对父表约束: 父表数据进行写操作(删和改: 都必须涉及到主键本身), 如果对应的主键在子表中已经被数据所引用, 那么就不允许操作

4.4.4 外键条件
1.外键要存在: 首先必须保证表的存储引擎是innodb(默认的存储引擎): 如果不是innodb存储引擎,那么外键可以创建成功,但是没有约束效果.
2.外键字段的字段类型(列类型)必须与父表的主键类型完全一致.
3.一张表中的外键名字不能重复.
4,增加外键的字段(数据已经存在),必须保证数据与父表主键要求对应.

4.4.5 外键约束
所谓外键约束: 就是指外键的作用.

之前所讲的外键作用: 是默认的作用; 其实可以通过对外键的需求, 进行定制操作.

需要注意的是：外键约束的定义是写在子表上的，但是不推荐使用外键约束

MySQL字段约束有四种，主键约束，非空约束，唯一约束，外键约束。外键约束是唯一不推荐的约束

提示：主键约束其实就是非空约束和唯一约束合二为一的情况

外键约束有三种约束模式: 都是针对父表的约束(子表约束父表)

District: 严格模式(默认的), 父表不能删除或者更新一个已经被子表数据引用的记录

Cascade: 级联模式: 父表的操作, 对应子表关联的数据也跟着被删除

Set null: 置空模式: 父表的操作之后,子表对应的数据(外键字段)被置空

通常的一个合理的做法(约束模式): 删除的时候子表置空, 更新的时候子表级联操作

指定模式的语法

Foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;

更新操作: 级联更新

删除操作: 置空

删除置空的前提条件: 外键字段允许为空(如果不满足条件,外键无法创建)

外键虽然很强大, 能够进行各种约束: 但是对于PHP来讲, 外键的约束降低了PHP对数据的可控性: 通常在实际开发中, 很少使用外键来处理.

4.4.6 创建外键约束的要求
创建外键约束的目的是保持数据一致性和完整性，以及实现一对一或者一对多的关系。

创建外键约束要求有以下几点：

1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。

注意：具有外键列的表称为子表；子表所参照的表称为父表。

2. 数据表的存储引擎只能是InnoDB。

3. 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同。

注意：加 FOREIGN KEY 关键字的列称为外键列；外键列所参照的列称为参照列。

4. 外键列和参照列必须创建索引。如果外键列不存在索引的话，MySQL将自动创建索引。如果参照列不存在索引的话，MySQL不会自动创建索引。

注意:MySQL会为主键自动创建索引。

4.4.7 外键约束的闭环问题
比如说我们创建了2张表

/*先创建父表*/
CREATE TABLE t_dept(
    deptno INT UNSIGNED PRIMARY KEY,
    dname VARCHAR(20) NOT NULL UNIQUE,
    tel CHAR(4) UNIQUE
)
/*再创建子表*/
CREATE TABLE t_emp(
    empno INT UNSIGNED PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    sex ENUM("男", "女") NOT NULL,
    deptno INT UNSIGNED NOT NULL,
    hiredate DATE NOT NULL,
    FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);
父表t_dept加一个数据如下：

子表t_emp加一个数据如下：

此时我想删除父表的数据，结果报错

结果发现有子表t_emp外键约束着父表，删除失败。必须先删除子表的约束数据才能删除父表的数据，那这样就失去了增减改查的灵活性了，并且更严重的是，

如果形成外键闭环，我们将无法删除任何一张表的数据记录。

如上图，A约束B，B约束C......，这样每一个表都算作父表，所谓的先删除子表的数据就是不可能的。因为有外键闭环的存在，所以我们不推荐外键约束

4.5 索引
几乎所有的索引都是建立在字段之上.

索引: 系统根据某种算法, 将已有的数据(未来可能新增的数据),单独建立一个文件: 文件能够实现快速的匹配数据, 并且能够快速的找到对应表中的记录.

4.5.1 创建索引
建表的时候创建索引，也可以在已存在的表上添加索引。

CREATE TABLE 表名称(
       ......,
       INDEX [索引名称] (字段),
       ......
);

CREATE TABLE t_message(
    id INT UNSIGNED PRIMARY KEY,
    content VARCHAR(200) NOT NULL,
    type ENUM("公告", "通报", "个人通知") NOT NULL,
    create_time TIMESTAMP NOT NULL,
    INDEX idx_type (type)
);
4.5.2 添加索引
向已存在的表中添加索引的方式如下

普通索引：

CREATE INDEX 索引名称 ON 表名(字段);  /*添加索引方式1*/

ALTER TABLE 表名 ADD INDEX 索引名称(字段); /*添加索引方式2*/

唯一索引：

CREATE UNIQUE INDEX 索引名称 ON 表名(字段)

联合索引：

CREATE INDEX 索引名称 ON 表名(字段1，字段2...)

-- 普通索引：
CREATE INDEX idx_type ON t_message(type); /*添加索引方式1*/
 
ALTER TABLE t_message ADD INDEX idx_type(type);/*添加索引方式2*/
 
-- 唯一索引：
CREATE UNIQUE INDEX uidx_type ON t_message(type);
 
-- 联合索引
CREATE INDEX idx_type1_type2 ON t_message(type1, type2);
 经常被用来做检索条件的字段需要加上索引，原理是B+树，所以查询很快。如果是几千条数据，不必加索引，全表扫描也很快

练习题：

已有新闻表(tb_news)，表中有type字段，下列选中项中能为该字段添加索引的是？

这个就是记忆题目，记住语法即可，答案是AC

4.5.3 查询索引
SHOW INDEX FROM 表名;

/*查看t_message表的索引*/
SHOW INDEX FROM t_message;
 查出来如下，有添加的普通索引和主键索引

4.5.4 删除索引
DROP INDEX 索引名称 ON 表名;

/* 在t_message表中删除idx_type索引 */
DROP INDEX idx_type ON t_message;
4.5.5 索引的使用原则
1. 数据量很大，且经常被查询的数据表可以设置索引  (即读多写少的表可以设置索引)

2. 索引只添加在经常被用作检索条件的字段上 (比如电子商城需要在物品名称关键字加索引)

3.不要在大字段上创建索引 (比如长度很长的字符串不适合做索引，因为查找排序时间变的很长)

4.5.6 索引的意义
提升查询数据的效率
约束数据的有效性(唯一性等)
增加索引的前提条件: 索引本身会产生索引文件(有时候有可能比数据文件还大) ,会非常耗费磁盘空间.

如果某个字段需要作为查询的条件经常使用, 那么可以使用索引(一定会想办法增加);

如果某个字段需要进行数据的有效性约束, 也可能使用索引(主键,唯一键)

Mysql中提供了多种索引

主键索引: primary key
唯一索引: unique key
全文索引: fulltext index
普通索引: index
全文索引: 针对文章内部的关键字进行索引

全文索引最大的问题: 在于如何确定关键字

英文很容易: 英文单词与单词之间有空格

中文很难: 没有空格, 而且中文可以各种随意组合(分词: sphinx)