mysql数据库—表的管理
SQL是结构化查询语言，是一种用来操作RDBMS(关系型数据库管理系统)的数据库语言，当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作oracle，sql server,mysql等关系型数据库。
SQL语句主要分为：
- DQL：数据查询语言，用于对数据进行查询，这是最基本的sql语句；
- DML：数据操作语言，对数据进行增加、修改、删除，，以insert、update、delete三种指令为核心；
- DDL：数据定义语言，进行数据库、表的管理等，由create、alter、drop三个语法所组成；
- DCL 数据控制语言：它可以控制特定用户帐户对数据访问权限。由grant、revoke两个组成。
数据完整性
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束。
1、数据类型
1.1、数值类型
数值型
| 类型 | 名称 | 范围(有符号) | 范围(无符号) | 用途 |
| tinyint | 微小整数 | -128~127 | 0~255 | 大整数值 |
| smallint | 小整数 | -32768~32767 | 0~65535 | |
| mediumint | 中整型 | -223~223-1 | 0~224-1 | |
| int | 大整型 | -231~231-1 | 0~232-1 | |
| bigint | 极大整型 | -263~263-1 | 0~264-1 | |
| unsigned | 使用无符号存储范围 | 不能单独使用，需要和数据类型连用。 |
| zerofill | 不满足规定的值时在右边自动补齐0 | 一般用于显示 |
浮点类型
| 类型 | 名称 | 范围 | 说明 |
| float(m,d) | 单精度 | 8位精度 | m总个数 d小数位 |
| double(m,d) | 双精度 | 16位精度 |
| decimal(m,d) | m表示十进制数字总个数，d表示小数点后面数字的位数。一般用于货币 |
1.2、字符串类型
常用字符类型
| 类型 | 名称 | 用途 |
| char(字符个数) | 定义长度类型 | 最多255个字符 |
| varchar(字符个数) | 变长类型 | 最多65532个字符 |
- char类型：不够制定字符个数时，右边用空格补全字符个数，超出无法写入数据。
- varchar类型：按照实际大小分配存储空间，字符个数超出时无法写入数据。
- char读取速度比较快，varchar在读取时会进行判断。
- 以上的两种数据类型默认不能存储汉字字符集。
其他字符类型
| 类型 | 名称 | 范围 |
| tinytext | 短文本 | 0-255 |
| tinyblob | 二进制形式短文本 | 0-255 |
| text | 长文本数据 | 0-65535 |
| blob | 二进制形式的长文本 | 0-65535 |
| mediumblob | 二进制形式的中等长度文本 | 0-16777215 |
| mediumtext | 中等长度文本数据 | 0-16777215 |
| longblob | 二进制形式极大文本 | 0-4284867295 |
| longtext | 极大文本 | 0-4284867295 |
1.3、日期和时间型
| 类型 | 名称 | 范围 | 赋值格式 |
| year | 年 | 1901~2155 | yyyy |
| date | 日期 | 0001-01-01~9999-12-31 | yyyymmdd |
| time | 时间 | 01:00:00~23:59:59 | hhmmss |
| datetime | 日期时间 | 1000-01-01 00:00:00~9999-12-31 23:59:59 | yyyymmddhhmmss |
| timestamp | 1970-01-01 00:00:00~2038-01-19 00:00:00 |
- 当year类型要求4位数赋值
- 当时用2位数赋值时：-01~69视为2001~2069、-70~99视为1970~1999
- timestamp与datetime的区别是时间间隔长短不一样，还有timestamp在其他同一行单元的赋值时，timestamp格式的单元格没有值，会自动调用系统当前时间。
1.4、枚举类型
- enum(值列表)：单选，字段值仅能在范围内选择1个值。
- set(值列表)：多选，字段值能在范围内选择1个或多个值。
- 给表头赋值时，必须在类型规定的范围内选择。
2、字段约束：约束是一种限制，设置在字段上，用来控制字段的赋值。
2.1、not null：非空，用于保证该字段的值不能为空（表头“Null”）；
2.2、default：默认值，用于该字段有默认值,当default默认值为空时，在查看desc时为空白显示。
2.3、unique：唯一索引，用于保证该字段的值具有唯一性，可以为空（表头“Key”）。2.4、
2.5、auto_increment：
2.6、foreign key ：
2.7、限制字段赋值，通过desc查看表的格式：
- Null：表示为空
- Key：键值
- Default：默认值
- Extra：额外值
3、创建表
创建数据表的规则：
- 数据表创建的是否合理，遵循建表范式：
- 1NF，表头划分到不能再分
- 2NF，一个数据表中不能存放多类信息
- 3NF，一个数据表的表头不能依赖其他表头的数据。
- 2NF与3NF的区别：在于有没有分出2张表，2NF如果一张表包含多种不同实体的属性，那么必须要分成多张表，3NF已经分成多张表后，一张表中只能有另一张表中的主键，而不能有其他的信息。
- 创建数据表之前先创建数据库
格式：
- create table 库名.表名(表名1 数据类型[(长度) 约束]，表名1 数据类型[(长度) 约束]，……)
3.1、主键（primary key）：primary key：主键，用于保证该字段的值不能为空，且不能重复（表头“Key”），主键使用规则：
- 字段值不允许重复，且不允许赋NULL值
- 一个表中只能有一个primary key字段
- 多个字段都作为主键，称为复合主键，必须一起创建
- 主键字段的标志是PRI
创建
- create table 库.表 (表头 数据类型 primary key，....);
- create table 库.表 (表头 数据类型 数据约束，表头 数据类型 数据约束，primary key（表头）....);
删除与添加
- alter table 库.表 drop primary key; #删除
- alter table 库.表 add primary key（表头）; #添加，如果已有主键，无法添加
3.2、auto_increment用法：额外设置，自增一般和主键连用，一般一张表只有一个自增列,truncate后从1开始，不支持where，delete继续编号（表头“Extra”）。主键与auto_increment自增长连用，使用规则：
- 不给表头赋值时是“自加1的计算结果”给表头赋值
- 表头的数据类型必须是整型数值类型的，并且必须是主键
- 要先删除自增长auto_increment，主键才能删除。
- 如果启动自动计算的功能，注意在赋值过程中切记查看系统中的赋值是否已存在，如果存在会报错。
- 在查看表结构时，在Extra位置显示。
- 通常把表中唯一标识记录的字段设置为主键[记录编号字段]
- truncate后从1开始不能回滚，delete继续之前的编号可以回滚
创建：
- create tables 库名.表名 ( 表头 数据类型 数据约束 auto_increment，数据类型 数据约束，primary key （表头,表头）....);
- create tables 库名.表名 ( 表头 数据类型 数据约束 primary key auto_increment，表头 数据类型 数据约束，....);
删除：
- alter table 库.表 modify 表头 数据类型 not null;
3.2、复合主键：多个字段一起做主键，复合主键的值只要不同时重复即可。
创建：
- create tables 库.表 (表头 数据类型 数据约束，表头 数据类型 数据约束……primary key （表头,表头））；
删除：
- alter table 库.表 drop primary key;
添加：
- alter table 库.表 add primary key（表头，表头）;
3.3、外键（foreign key）：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表的添加外键约束，用于引用主表中某些的值。外键作用：插入记录时，字段值在另一个表字段值范围内选择。外键使用规则：
- 表存存储引擎必须是innodb
- 字段类型要一致
- 被参照字段必须要是索引类型的一种（primary key）
- 通过表结构无法查看到表结构看不到外键
创建：
- create table 库.表（字段列表，foreign key(表头) references 库名.表名(表头) on update cascade on delete cascade ）engine=innodb;
- foreign key(表头) #制定需要同步的表头
- references 库名.表名(表头) #另一个数据表的表头
- on update cascade #同步更新
- on delete cascade #同步删除
- engine=innodb; #制定存储引擎
查看：
- show create table 库名.表名 G #CONSTRAINT后面跟着外键名称
删除（通过外键名称删除表头外键）：
- alter table 库名.表名 drop foreign key 外键名称；
添加：
- alter table 库.表 add foreign key(表头) references 库.表(表头) on update cascade on delete cascade；
4、表记录的管理
管理表记录的规则：
- 不指定列名插入记录，必须给所有列赋值，值的顺序必须和表中列的顺序一致，且数据类型要匹配；
- 指定列名插入记录，仅须给指定列赋值，列和值的顺序要一致，列名先后顺序不重要，没有赋值的列使用默认值、自增长赋值；
- 在向数据库中的数据表中添加数据时注意查看数据类型，数据类型要与数据表中定义的数据类型一致。
- truncate不支持where条件，truncate后从1开始，delete继续编号；
- truncate不能回滚，delete支持回滚，效率略高于delete。
匹配条件的命令，用在表头字段：
- as或空格：别名
- concat【康科特】()：拼接
- distinct：去重
数值比较符号：=、!=、>、>=、<、<=（符号两边必须是数据类型）。
字符比较：=、！=、is null（空）、is not mull（非空），必须是字符类型比较。
- is null(空)：表头下面没有数据
- is not null(非空)：表头下面有数据
- mysql服务使用关键字null或NULL表示没有数据，null不能用引号引起来
范围匹配：
- in(值列表)：在..里..
- not in(值列表）：不在..里..
- between【bəˈtwēn】 数字 and 数字：在..之间..
- 在范围内部如果是字符串，需要用单引号或双引号引起来，如果字符串中有特殊字符，最好用双引号引起来，不至于报错。
模糊匹配：
- where 表头 like ‘字符表示’;
- “_”表示一个字符
- “%”表示0到多个字符
正则表示的的匹配：^ 行首、$ 行尾、. 1个字符、[]范围内、* 前边表达式出现零次或多次、| 或
- 格式：select 表头 from 库名.表名 where 表头 regexp “正则匹配”;
逻辑匹配：
- 多个判断条件时使用and的优先级高于or!
- and、&&：逻辑与，多个判断条件必须同时成立
- or、||：逻辑或，多个条件判断其中某个条件
- not、!：逻辑非，取反
注意重点：
- 当进行逻辑匹配过程中，有多个逻辑匹配符号且有小括号，则先先判断小括号里面的，再匹配其他。
- 不加查询条件时，会显示所有行和列，加条件只会显示与条件匹配的行和列。
查询：
- select 表字段…… from 库名.表名;
- select 原名 as 别名 ，原名 as 别名 from 库名.表名； #创建临时/别名：
- select concat(原名1,”+ ”,原名2) from 库名.表名； #将标选中表标头使用符号进行拼接
- select distinct 表头 from 库名.表名； #将选中表头下面的数据进行去重筛选
- select 表头 from 库名 persons limit n; #打印出从开始的n行：
插入：
- insert into 库名.表名 values(值列表)，（值列表）……； #不指定表头
- insert into 库名.表名（表头1，表头2） values（值列表），（值列表）……； #指定表头
- insert into 库名.表名 （表头1，表头2） (select 表头字段 from 库.表 where 条件)； #使用查询结果赋值
- insert into 库名.表名 set 表名1=值1，表名2=值2； #使用set命令赋值
修改：
- update 表名 set 表字段=值，表字段=值； #批量修改
- update 表名 set 表字段='新值' where 表字段='值'； #仅修改与条件匹配的
删除：
- delete from 库名.表名 where 条件; #仅删除与条件匹配的行
- delete from 库名.表名； #清空记录
- truncate table 库名.表名； #清空标记录,数据无法恢复
5、索引
索引是帮助mysql高效获取数据的数据结构，为快速查找数据而排好序的一种数据结构，类似书的目录，可以用来快速查询表中的特定记录，所有的数据类型都可以被索引。
创建：index
- create table 库名.表名(表头名 数值类型 约束条件，……index(表头)，index(表头)，);
查看：key列的值是MUI
- desc 库名.表名；
- show index from 库名.表名 G #查看索引的详细信息
- explain select 表头 from 库名.表名 where 条件判断； #查询是否使用索引进行查询：
添加：
- create index 索引名 on 库名.表名(表头名)；
删除：
- drop index 含索引的表名 on 库名.表名；
