mysql的表操作

一, mysql中的存储引擎

  1. mysql中的数据用各种不同的技术存储在文件(或者内存)中.这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力

  2. 这些不同的技术以及配套的相关功能在MySQL中被称作存储引擎(也称作表类型)

  3. mysql支持的存储引擎:

    mysql 5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、CSV、BLACKHOLE、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA.其中NDB和InnoDB提供事务安全表,其他存储引擎都是非事务安全表

  4. 各种存储引擎的特性

    并发性: 某些应用程序比其他应用程序具有很多的颗粒级锁定要求(如行级锁定).
事务支持: 并非所有的应用程序都需要事务,但对的确需要事务的应用程序来说,有着定义良好的需求,如ACID兼容等.
引用完整性: 通过DDL定义的外键,服务器需要强制保持关联数据库的引用完整性.
物理存储: 它包括各种各样的事项,从表和索引的总的页大小,到存储数据所需的格式,到物理磁盘.
索引支持: 不同的应用程序倾向于采用不同的索引策略,每种存储引擎通常有自己的编制索引方法,但某些索引方法(如B-tree索引)对几乎所有的存储引擎来说是共同的.
内存高速缓冲: 与其他应用程序相比,不同的应用程序对某些内存高速缓冲策略的响应更好,因此,尽管某些内存高速缓冲对所有存储引擎来说是共同的(如用于用户连接的高速缓冲,MySQL的高速查询高速缓冲等),其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义.
性能帮助: 包括针对并行操作的多I/O线程,线程并发性,数据库检查点,成批插入处理等.
其他目标特性: 可能包括对地理空间操作的支持,对特定数据处理操作的安全限制等.

    以上要求会在不同的需求中予以体现,通过单独一个系统实现是不可能的,以上特点有些本身就是相互矛盾的,鱼和熊掌的问题.对以上内容做些选择,形成的存储引擎就是一个插件引擎了,某些特定的需求可以使用.如下图,部分现有的存储引擎以及基本特点:

    InnoDB:
MySql 5.6 版本默认的存储引擎.InnoDB是一个事务安全(transactions)的存储引擎,保证数据安全和完整性;支持行级锁(row-level locking)和表级锁(table_level locking),提升了它的多用户并发数以及性能;树tree-加速查询(树形结构(数据 + 树) + 表结构),提升查询速度;为了保证数据的完整性,InnoDB还支持外键约束(foreign keys).
MyISAM:
MyISAM既不支持事务、也不支持外键、其优势是访问速度快,但是表级别的锁定限制了它在读写负载方面的性能,因此它经常应用于只读或者以读为主的数据场景.树tree-加速查询(树形结构 + 数据 + 表结构)
Memory:
在内存中存储所有数据,应用于对非关键数据快速查找的场景.Memory类型的表访问数据非常快,因为它的数据是存放在内存中的,并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会丢失

  5. 常用存储引擎及适用场景

    • InnoDB:

      用于事务处理应用程序,支持外键和行级锁.如果应用对事物的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询之外,还包括很多更新和删除操作,那么InnoDB存储引擎是比较合适的.InnoDB除了有效的降低由删除和更新导致的锁定,还可以确保事务的完整提交和回滚,对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择.(产生两个文件)

    • MyISAM:

      如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不高,那么可以选择这个存储引擎.(产生三个文件)

    • Memory:

      将所有的数据保存在内存中,在需要快速定位记录和其他类似数据的环境下,可以提供极快的访问.Memory的缺陷是对表的大小有限制,虽然数据库因为异常终止的话数据可以正常恢复,但是一旦数据库关闭,存储在内存中的数据都会丢失.(产生一个文件)

  6. 存储引擎在mysql中的使用

    
查看当前的默认存储引擎:
mysql> show variables like "default_storage_engine";

查询当前数据库支持的存储引擎:
mysql> show engines \G;

  7. 指定存储引擎建表

    # 在建表时指定:
mysql> create table innodb_t(id int,name char(12));
mysql> create table myisam_t(id int,name char(12)) engine=myisam;
mysql> create table memory_t(id int,name char(12)) engine=memory;

# 在配置文件中指定:
# my.ini文件
[mysqld]
default-storage-engine=INNODB

  8. mysql的工作流程

    • 首先,最上层的服务并不是MySQL独有的,大多数基于网络的客户端/服务器的工具或者服务都有类似的架构.比如: 连接处理、授权认证、安全等.
    • 第二层的架构包括大多数的MySQL的核心服务.包括: 查询解析、分析、优化、缓存以及所有的内置函数(例如: 日期、时间、数学和加密函数).同时,所有的跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等.
    • 第三层包含了存储引擎.存储引擎负责MySQL中数据的存储和提取.服务器通过API和存储引擎进行通信.这些接口屏蔽了不同存储引擎之间的差异,使得这些差异对上层的查询过程透明化.存储引擎API包含十几个底层函数,用于执行“开始一个事务”等操作.但存储引擎一般不会去解析SQL(InnoDB会解析外键定义,因为其本身没有实现该功能),不同存储引擎之间也不会相互通信,而只是简单的响应上层的服务器请求.
    • 第四层包含了文件系统,所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上.

二, mysql的数据类型

  1. 表就相当于文件,表中的一条记录就相当于文件的一行内容,不同的是,表中的一条记录有对应的标题,称为表的字段

  2. 例如:

    id name age sex phone job
    1 孙悟空 500 男 13838383838 和尚
    2 猪八戒 2000 男 18888888888 和尚
    3 沙悟净 2000 男 16666666666 和尚

  3. 创建表

    # 语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

# 注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

    mysql> create database python;
mysql> use python;
mysql> create table staff_info(id int,name char(12),age int(3),sex enum('男','女'),phone bigint(11),job varchar(11));
mysql> show tables;
mysql> desc staff_info;
mysql> insert into staff_info values(1,'猪八戒',18,'女',13838383838,'护士');
mysql> insert into staff_info values(2,'孙悟空',18,'男',16868686868,'院长');
mysql> select * from staff_info;

  4. 查看表结构

    mysql> desc 表名和describe 表名效果相同,可以查看当前的表结构
mysql> show create table 表名;  查看更全面的表定义信息

mysql> desc staff_info;
mysql> show create table staff_info;

  5. 数值类型

    MySQL支持所有标准SQL数值数据类型,这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC),以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION).关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词.MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT.下面的表显示了需要的每个整数类型的存储和范围.对于小数的表示,MYSQL分为两种方式：浮点数和定点数.浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种,在mysql中以字符串的形式存放,比浮点数更精确,适合用来表示货币等精度高的数据.

    类型 大小 范围（有符号） 范围（无符号）unsigned约束 用途
    INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
    TINYINT 1 字节 (-128，127) (0，255) 小整数值
    FLOAT 4 字节float(255,30) (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
    DOUBLE 8 字节double(255,30) (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
    DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2double(65,30) 依赖于M和D的值 依赖于M和D的值 小数值 
    # 整数示例  常用int tinyint
# 创建表一个是默认宽度的int，一个是指定宽度的int(5)
mysql> create table int_t(id1 int,id2 int(5));
mysql> insert into int_t values(1,1);
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
# 那么当我们插入了比宽度更大的值，会发生什么呢？
mysql> insert into int_t values(111111,111111);
+--------+--------+
| id1    | id2    |
+- ------+--------+
|      1 |      1 |
| 111111 | 111111 |
+--------+--------+
# id2仍然显示了正确的数值，没有受到宽度限制的影响
# 修改id1字段 给字段添加一个unsigned表示无符号
mysql> alter table int_t modify id1 int unsigned;
# id1不能为负数,且范围变为(0，4 294 967 295)

    # 小数示例 常用float double decimal
# float(一共多少位,小数点保留的位数)
# float默认五位小数
# decimal的精度最好,默认(10,0)
mysql> create table fd_t(f_t float(5,2),d_t double(5,2),dc_t decimal(5,2));
mysql> insert into fd_t values(1.23,1.23,1.23);
mysql> select * from fd_t;
+------+------+------+
| f_t  | d_t  | dc_t |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+
mysql> insert into fd_t values(1.234,1.234,1.234);
+------+------+------+
| f_t  | d_t  | dc_t |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
+------+------+------+ # 四舍五入

  6. 日期和时间类型

    表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR.每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值.TIMESTAMP类型有专有的自动更新特性.

    类型 大小 (字节) 范围 格式 用途
    DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 年月日时分秒
    DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 年月日
    TIME 3 '838:59:59'/'838:59:59' HH:MM:SS 时分秒
    YEAR 1 1901/2155 YYYY 年份值
    TIMESTAMP 4 2038年1月19日结束 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳 
    # datetime  date  time 示例
# now() 获取当前时间
mysql> create table time1(d date,t time,dt datetime);
mysql> desc time1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
mysql> insert into time1 values(now(),now(),now());
mysql> select * from time1;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-07-30 | 19:36:21 | 2019-07-30 19:36:21 |
+------------+----------+---------------------+
mysql> insert into time1 values(null,null,null);
mysql> select * from time1;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2019-07-30 | 19:36:21 | 2019-07-30 19:36:21 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+

    # timestamp示例
mysql> create table time2(time timestamp);
mysql> desc time2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
# 插入数据null,会自动插入当前时间的时间
mysql> insert into time2 values(null);
mysql> select * from time2;
+---------------------+
| time                |
+---------------------+
| 2019-07-30 19:46:50 |
+---------------------+
# 添加一列 默认值是'0000-00-00 00:00:00'
mysql> alter table time2 add time2 timestamp;
mysql> desc time2;
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type      | Null | Key | Default             | Extra                       |
+-------+-----------+------+-----+---------------------+-----------------------------+
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| time2 | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
+-------+-----------+------+-----+---------------------+-----------------------------+
# 手动修改新的列默认值为当前时间
mysql> alter table time2 modify time2 timestamp default current_timestamp;
mysql> desc time2;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| time  | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time2 | timestamp | NO   |     | CURRENT_TIMESTAMP |                             |
+-------+-----------+------+-----+-------------------+-----------------------------+
mysql> insert into time2 values(null,null);
mysql> select * from time2;
+---------------------+---------------------+
| time                | time2               |
+---------------------+---------------------+
| 2019-07-30 19:46:50 | 0000-00-00 00:00:00 |
| 2019-07-30 20:12:08 | 2019-07-30 20:12:08 |
+---------------------+---------------------+
# timestamp时间的下限是19700101080001
# timestamp时间的上限是20380119111407

    # year示例
mysql> create table time3(y year);
mysql> insert into time3 values(2019);
mysql> select * from time3;
+------+
| y    |
+------+
| 2019 |
+------+

    # datetime示例
mysql> create table time4(dt datetime);
mysql> insert into time4 values('2018-9-26 12:20:10');
mysql> insert into time4 values('20180926122010');
mysql> insert into time4 values(20180926122010);
mysql> select * from time4;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
| 2018-09-26 12:20:10 |
+---------------------+
# 使datetime具有timestamp的特性: 非空,默认当前时间,修改其它字段时自动更新
mysql> create table time5(dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> desc time5;
+-------+----------+------+-----+-------------------+-----------------------------+
| Field | Type     | Null | Key | Default           | Extra                       |
+-------+----------+------+-----+-------------------+-----------------------------+
| dt    | datetime | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+----------+------+-----+-------------------+-----------------------------+
mysql> insert into time5 values(null);
ERROR 1048 (23000): Column 'dt' cannot be null
mysql> insert into time5 values();
mysql> select * from time5;
+---------------------+
| dt                  |
+---------------------+
| 2019-07-30 20:24:15 |
+---------------------+

  7. 字符串类型

    字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

    类型 大小 用途
    CHAR 0-255字符(5.6版本) 定长字符串
    VARCHAR 0-65535字符 变长字符串

    CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同.它们的最大长度和是否尾部空格被保留等方面也不同.在存储或检索过程中不进行大小写转换.CHAR列的长度固定为创建表是声明的长度(自动补空格),范围(0-255);而VARCHAR的值是可变长字符串范围(0-65535).

    # char 定长存储,存储速度更快,占用更多的空间,默认为1
# varchar 变长存储,存储速度相对慢,占用的空间小,不能不设置
# char和varchar示例
mysql> create table char1(c char(4),v varchar(4));
mysql> insert into char1 values('tp ','tp ');
# 在检索的时候char数据类型会去掉空格
mysql> select * from char1;
+------+------+
| c    | v    |
+------+------+
| tp   | tp   |
+------+------+
# 来看看对查询结果计算的长度
mysql> select length(c),length(v) from char1;
+-----------+-----------+
| length(c) | length(v) |
+-----------+-----------+
|         2 |         3 |
+-----------+-----------+
# 给结果拼上一个加号会更清楚
mysql> select concat(c,'+'),concat(v,'+') from char1;
+---------------+---------------+
| concat(c,'+') | concat(v,'+') |
+---------------+---------------+
| tp+           | tp +          |
+---------------+---------------+
# 当存储的长度超出定义的长度，会截断
mysql> insert into char1 values('hahaha','hahaha');
mysql> select * from char1;
+------+------+
| c    | v    |
+------+------+
| tp   | tp   |
| haha | haha |
+------+------+

  8. ENUM和SET类型

    ENUM中文名称叫枚举类型,它的值范围需要在创建表时通过枚举方式显示.ENUM只允许从值集合中选取单个值,而不能一次取多个值.

    SET和ENUM非常相似,也是一个字符串对象,里面可以包含0-64个成员.根据成员的不同,存储上也有所不同.set类型可以允许值集合中任意选择1或多个元素进行组合.对超出范围的内容将不允许注入,而对重复的值将进行自动去重.

    类型 大小 用途
    ENUM 对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储;最多允许65535个成员。 单选：选择性别
    SET 1-8个成员的集合，占1个字节9-16个成员的集合，占2个字节17-24个成员的集合，占3个字节25-32个成员的集合，占4个字节33-64个成员的集合，占8个字节 多选：兴趣爱好 
    # enum示例
mysql> create table enum(name char(20),sex enum('男','女'));
# 选择enum('男','女')中的一项作为sex的值,可以正常插入
mysql> insert into enum values('孙悟空','男');
# 不能同时插入'男','女'两个值,也不能插入不属于'男','女'的值
mysql> insert into enum values('猪八戒','男,女');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1

# set示例
mysql> create table set1(name char(12),hobby set('抽烟','喝酒','烫头'));
# 可以任意选择set('抽烟','喝酒','烫头')中的项,并自带去重功能
mysql> insert into set1 values('于谦','抽烟,喝酒,烫头,烫头');
mysql> select * from set1;
+--------+----------------------+
| name   | hobby                |
+--------+----------------------+
| 于谦   | 抽烟,喝酒,烫头        |
+--------+----------------------+
# 不能选择不属于set('抽烟','喝酒','烫头')中的项
mysql> insert into set1 values('蔡旭鲲','唱跳,篮球');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

三, mysql表的完整性约束

  1. 为了防止不符合规范的数据进入数据库,在用户对数据进行插入,修改,删除等操作时,DBMS自动按照一定的约束条件对数据进行监测,使不符合规范的数据不能进入数据库,以确保数据库中存储的数据正确,有效,相容. 约束条件与数据类型的宽度一样,都是可选参数,主要分为以下几种:

    命令 约束
    UNSIGNED 无符号的:数字
    DEFAULT 默认值
    NOT NULL 非空约束,指定某列不能为空
    UNIQUE 唯一约束,指定某列或者几列组合不能重复
    PRIMARY KEY 主键,指定该列的值可以唯一地标识该列记录
    FOREIGN KEY 外键,指定该行记录从属于主表中的一条记录,主要用于参照完整性
    auto_increment 自增

  2. NOT NULL

    是否可空,null表示空,非字符串
    not null - 不可空
    null - 可空

    mysql> create table nn(id int not null);
mysql> desc nn;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
# 不能向id列插入空元素
mysql> insert into nn values(null);
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into nn values(1);
Query OK, 1 row affected (0.01 sec)

  3. DEFAULT

    我们约束某一列不为空,如果这一列中经常有重复的内容,就需要我们频繁的插入,这样会给我们的操作带来新的负担,于是就出现了默认值的概念.默认值,创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值

    # not null+default
mysql> create table nd(id1 int not null,id2 int not null default 250);
mysql> desc nd;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | NO   |     | NULL    |       |
| id2   | int(11) | NO   |     | 250     |       |
+-------+---------+------+-----+---------+-------+
# 只向id1字段添加值，会发现id2字段会使用默认值填充
mysql> insert into nd(id1) values(1);
mysql> select * from nd;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 | 250 |
+-----+-----+
# id1字段不能为空,所以不能单独向id2字段填充值
mysql> insert into nd(id2) values(111);
ERROR 1364 (HY000): Field 'id1' doesn't have a default value
# 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值
mysql> insert into nd values(520,521);
mysql> select * from nd;
+-----+-----+
| id1 | id2 |
+-----+-----+
|   1 | 250 |
| 520 | 521 |
+-----+-----+

    设置严格模式：
    不支持对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"

  4. UNIQUE

    唯一约束,指定某列或者几列组合不能重复

    # 方式一:
mysql> create table un(id int unique,name char(12));
# 方式二:
mysql> create table un2(id int,name char(12),unique(id));
mysql> insert into un values(1,'孙悟空');
mysql> insert into un values(1,'孙悟空');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'

    # 唯一加非空  unique + not null
mysql> create table nnun(id int not null unique);
mysql> desc nnun;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
# 如果一张表中没有设置primary key主键,那么第一个设置非空+唯一的字段会被设置成主    键

# 唯一和空的关系,unique能不能重复插入多个null?
# 对于mysql来说,数据与数据之间相等就是重复,但null不能用=判断
# 所以,对于unique来说,可以插入多个空值

    # 联合唯一
create table service(tname
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) # 联合唯一,两者结合起来唯一
)

  5. PRIMARY KEY

    主键为了保证表中的每一条数据的该字段都是表格中的唯一值.换言之,它是用来独一无二地确认一个表格中的每一行数据.主键可以包含一个字段或多个字段.当主键包含多个栏位时,称为组合键 (Composite Key),也可以叫联合主键.主键可以在创建新表格时设定 (运用 CREATE TABLE 语句),或是以改变现有的表格架构方式设定 (运用 ALTER TABLE).主键必须唯一,主键值非空;可以是单一字段,也可以是多字段组合.

    # 单列做主键
# 方法一：not null+unique  第一个会被设为主键
mysql> create table t1(id int not null unique, # 主键
    -> name varchar(20) not null unique,
    -> comment varchar(100));
mysql> desc t1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
# 方法二：在某一个字段后用primary key
mysql> create table t2(id int primary key, # 主键
    -> name varchar(20),comment varchar(100));
mysql> desc t2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
# 方法三：在所有字段后单独定义primary key
mysql> create table t3(id int,
    -> name varchar(20),comment varchar(100),
    -> primary key(id));
mysql> desc t3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | 0       |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
# 方法四：给已经建成的表添加主键约束
mysql> create table t3(id int,
    -> name varchar(20),comment varchar(100),
    -> primary key(id));
mysql> desc t4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
mysql> alter table t4 modify id int primary key;
mysql> desc t4;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+

    # 联合主键  非空,且联合唯一
mysql> create table t5(
    -> ip varchar(15),
    -> port char(5),
    -> s_name varchar(10) not null,
    -> primary key(ip,port));
mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| ip     | varchar(15) | NO   | PRI |         |       |
| port   | char(5)     | NO   | PRI |         |       |
| s_name | varchar(10) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
mysql> insert into t5 values('192.168.13.10','8848','QQ'),
    -> ('192.168.13.10','3306','MYSQL');
mysql> select * from t5;
+---------------+------+--------+
| ip            | port | s_name |
+---------------+------+--------+
| 192.168.13.10 | 3306 | MYSQL  |
| 192.168.13.10 | 8848 | QQ     |
+---------------+------+--------+
mysql> insert into t5 values('192.168.13.10','8848','微信');
ERROR 1062 (23000): Duplicate entry '192.168.13.10-8848' for key 'PRIMARY'

  6. AUTO_INCREMENT

    约束字段为自动增长,被约束的字段必须同时被key约束

    1. 只能操作数字
    2. 自带非空属性
    3. 只能对unique字段进行设置
    4. 不受删除影响,内部会记录
    # 不指定id,则自动增长
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20) not null);
mysql> desc student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('孙悟空'),('猪八戒'),('沙悟净');
mysql> select * from student;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 猪八戒    |
|  3 | 沙悟净    |
+----+-----------+
# 也可以指定id,不可以为空
# 对于自增的字段,在用delete删除后,再插入值,该字段仍按照删除前的位置继续增长
# 应该用truncate清空表,比起delete一条一条地删除记录,truncate是直接清空表,在删除大表时用它

    # 在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('男','女') default '男'
    -> );
mysql> alter table student auto_increment=3;
mysql> insert into student(name) values('马倩');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  3 | 马倩 |  男  |
+----+------+------+
# 也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('男','女') default '男'
) auto_increment=3;

  7. FOREIGN KEY

    # 外键约束  对应外表中的至少是unique,推荐使用主键作为关联字段
# 表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
# 必须先创建被关联的表
mysql> create table department(
    -> id int primary key,
    -> name varchar(20) not null
    -> );
# dpt_id外键,关联父表(department主键id),同步更新,同步删除
mysql> create table employee(
    -> id int primary key,
    -> name varchar(20) not null,
    -> dpt_id int,
    -> foreign key(dpt_id)
    -> references department(id)
    -> on delete cascade   # 级联删除
    -> on update cascade); # 级联更新
# 先往父表department中插入记录
mysql> insert into department values
    -> (1,'人事部'),(2,'研发部'),(3,'产品部');
# 再往子表employee中插入记录
mysql> insert into employee values
    -> (1,'孙悟空',1),
    -> (2,'猪八戒',3),
    -> (3,'沙悟净',3),
    -> (4,'唐僧',2);
# 删父表department,子表employee中对应的记录跟着删
# 更新父表department,子表employee中对应的记录跟着改

    on delete    on update
   . cascade方式
在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
   . set null方式
在父表上update/delete记录时，将子表上匹配记录的列设为null
要注意子表的外键列不能为not null  
   . No action方式
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  
   . Restrict方式
同no action, 都是立即检查外键约束

四, 表结构

  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 字段名;

    # alter操作非空和唯一
mysql> create table t1(id int unique,name char(10) not null);
# 去除name字段的非null约束
mysql> alter table t1 modify name char(10) null;
# 添加非null约束
mysql> alter table t1 modify name char(10) not null;
# 去除id字段的unique约束
mysql> alter table t1 drop index id;
# 添加unique约束
mysql> alter table t1 modify id int unique;

    # alter操作主键
mysql> create table t2(id int, name varchar(12));
# 设定id为主键
mysql> alter table t2 add primary key(id);
# 注:在增加主键之前,必须先把反复的id删除掉
# 删除主键
mysql> alter table t2 drop primary key;
# 注意,删除时,因主键产生的not null约束不会删除

    # 为表添加外键
mysql> create table press(
    -> id int primary key,
    -> name char(10) default null
    -> );
mysql> create table book(
    -> id int primary key,
    -> book_name char(12) default null,
    -> press_id int not null
    -> );
# 为book表添加外键
mysql> alter table book add constraint fk_id foreign key(press_id) references press(id);
# 删除外键
mysql> alter table book drop foreign key fk_id;

  2. 删除表

    drop table 表名;

  3. 多表结构的创建和分析

    分析步骤：
# 1,先站在左表的角度去找
是否左表的多条记录可以对应右表的一条记录,如果是,则证明左表的一个字段foreign key 右表一个字段(通常是id)

# 2,再站在右表的角度去找
是否右表的多条记录可以对应左表的一条记录,如果是,则证明右表的一个字段foreign key 左表一个字段(通常是id)

# 3,总结：
# 多对一：
如果只有步骤1成立,则是左表多对一右表
如果只有步骤2成立,则是右表多对一左表

# 多对多
如果步骤1和2同时成立,则证明这两张表时一个双向的多对一,即多对多,需要定义一个这两张表的关系表来专门存放二者的关系

# 一对一:
如果1和2都不成立,而是左表的一条记录唯一对应右表的一条记录,反之亦然.这种情况很简单,就是在左表foreign key右表的基础上,将左表的外键字段设置成unique即可

    # 一对多或多对一 示例
mysql> create table press(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
mysql> 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
    -> );
mysql> insert into press(name) values
    -> ('北京工业地雷出版社'),
    -> ('人民音乐不好听出版社'),
    -> ('知识产权没有用出版社');
mysql> insert into book(name,press_id) values
    -> ('九阳神功',1),
    -> ('九阴真经',2),
    -> ('九阴白骨爪',2),
    -> ('独孤九剑',3),
    -> ('降龙十巴掌',2),
    -> ('葵花宝典',3);
mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功         |        1 |
|  2 | 九阴真经         |        2 |
|  3 | 九阴白骨爪       |        2 |
|  4 | 独孤九剑         |        3 |
|  5 | 降龙十巴掌       |        2 |
|  6 | 葵花宝典         |        3 |
+----+-----------------+----------+

    # 多对多
# 三张表：作者,书,对应关系
# 多对多：一个作者可以写多本书,一本书也可以有多个作者,双向的一对多,即多对多
# 关联方式：foreign key+一张新的表
# 作者表
mysql>  create table author(
    ->  id int primary key auto_increment,
    ->  name varchar(20)
    -> );
# 这张表就存放作者表与书表的关系，即查询二者的关系查这表就可以了
mysql> create table author_book(
    -> id int primary key 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,
    -> unique(author_id,book_id)
    -> );

mysql> insert into author(name) values
    -> ('孙悟空'),('猪八戒'),('唐僧'),('沙和尚');
mysql> insert into author_book(author_id,book_id) values
    -> (1,4),(1,2),(1,3),(2,1),(2,6),(3,5),(4,2),(4,6);
    
mysql> select * from book;
+----+-----------------+----------+
| id | name            | press_id |
+----+-----------------+----------+
|  1 | 九阳神功        |        1 |
|  2 | 九阴真经        |        2 |
|  3 | 九阴白骨爪      |        2 |
|  4 | 独孤九剑        |        3 |
|  5 | 降龙十巴掌      |        2 |
|  6 | 葵花宝典        |        3 |
+----+-----------------+----------+
mysql> select * from author;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 猪八戒    |
|  3 | 唐僧      |
|  4 | 沙和尚    |
+----+-----------+
mysql> select * from author_book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
|  2 |         1 |       2 |
|  3 |         1 |       3 |
|  1 |         1 |       4 |
|  4 |         2 |       1 |
|  5 |         2 |       6 |
|  6 |         3 |       5 |
|  7 |         4 |       2 |
|  8 |         4 |       6 |
+----+-----------+---------+

    # 一对一
# 两张表：用户表和博客表
# 一对一：一个用户只有一个博客
# 关联方式：foreign key+unique
# 用户表
mysql> create table user(
    -> id int primary key auto_increment,
    -> name varchar(12));
mysql> insert into user(name) values
    -> ('孙悟空'),('猪八戒'),('沙悟净');
# 博客表
mysql> create table blog(
    -> id int primary key auto_increment,
    -> url varchar(40) not null unique,
    -> name_id int unique,
    -> foreign key(name_id) references user(id)
    -> on delete cascade on update cascade);
mysql> insert into blog(url,name_id) values
    -> ('www.baidu.com',1),('www.sohu.com',3),('www.taobao.com',2);
mysql> select * from user;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 孙悟空    |
|  2 | 猪八戒    |
|  3 | 沙悟净    |
+----+-----------+
mysql> select * from blog;
+----+----------------+---------+
| id | url            | name_id |
+----+----------------+---------+
|  1 | www.baidu.com  |       1 |
|  2 | www.sohu.com   |       3 |
|  3 | www.taobao.com |       2 |
+----+----------------+---------+
posted @ 2019-08-01 16:47  怀心抱素  阅读(154)  评论(0)  编辑  收藏  举报