库，表，记录的相关操作

系统数据库

information_schema：虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
performance_schema： MySQL 5.5开始新增一个数据库：主要用于收集数据库服务器性能参数，记录处理查询请求时发生的各种事件、锁等现象
mysql：授权库，主要存储系统用户的权限信息
test： MySQL数据库系统自动创建的测试数据库

创建数据库

语法 CREATE DATABASE 数据库名 charset utf8;

数据库命名规则：

可以由字母、数字、下划线、＠、＃、＄
区分大小写
唯一性
不能使用关键字如 create select
不能单独使用数字
最长128位

View Code

数据库相关操作

查看数据库
show databases;
show create database db1;
select database();

选择数据库
USE 数据库名

删除数据库
DROP DATABASE 数据库名;

修改数据库
alter database db1 charset utf8;

存储引擎介绍

什么是存储引擎

mysql中建立的库===>文件夹

库中建立的表===>文件

现实生活中我们用来存储数据的文件有不同的类型，每种文件类型对应各自不同的处理机制：比如处理文本用txt类型，处理表格用excel，处理图片用png等

数据库中的表也应该有不同的类型，表的类型不同，会对应mysql不同的存取机制，表类型又称为存储引擎。

存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方
法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和
操作此表的类型）

在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql
数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据
自己的需要编写自己的存储引擎

SQL 解析器、SQL 优化器、缓冲池、存储引擎等组件在每个数据库中都存在,但不是每个数据库都有这么多存储引擎。MySQL 的插件式存储引擎可以让存储引擎层的开发人员设计他们希望的存储层,例如,有的应用需要满足事务的要求,有的应用则不需要对事务有这么强的要求 ;有的希望数据能持久存储,有的只希望放在内存中,临时并快速地提供对数据的查询。

mysql支持的存储引擎

mysql> show engines\G  #查看所有支持的存储引擎
mysql> show variables like '%storage_engine%'; #查看正在使用的存储引擎
mysql> show create table 表名;你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎)

#InnoDB 存储引擎
支持事务,其设计目标主要面向联机事务处理(OLTP)的应用。其
特点是行锁设计、支持外键,并支持类似 Oracle 的非锁定读,即默认读取操作不会产生锁。 从 MySQL 5.5.8 版本开始是默认的存储引擎。
InnoDB 存储引擎将数据放在一个逻辑的表空间中,这个表空间就像黑盒一样由 InnoDB 存储引擎自身来管理。从 MySQL 4.1(包括 4.1)版本开始,可以将每个 InnoDB 存储引擎的 表单独存放到一个独立的 ibd 文件中。此外,InnoDB 存储引擎支持将裸设备(row disk)用 于建立其表空间。
InnoDB 通过使用多版本并发控制(MVCC)来获得高并发性,并且实现了 SQL 标准 的 4 种隔离级别,默认为 REPEATABLE 级别,同时使用一种称为 netx-key locking 的策略来 避免幻读(phantom)现象的产生。除此之外,InnoDB 存储引擎还提供了插入缓冲(insert buffer)、二次写(double write)、自适应哈希索引(adaptive hash index)、预读(read ahead) 等高性能和高可用的功能。
对于表中数据的存储,InnoDB 存储引擎采用了聚集(clustered)的方式,每张表都是按 主键的顺序进行存储的,如果没有显式地在表定义时指定主键,InnoDB 存储引擎会为每一 行生成一个 6 字节的 ROWID,并以此作为主键。
InnoDB 存储引擎是 MySQL 数据库最为常用的一种引擎,Facebook、Google、Yahoo 等 公司的成功应用已经证明了 InnoDB 存储引擎具备高可用性、高性能以及高可扩展性。对其 底层实现的掌握和理解也需要时间和技术的积累。如果想深入了解 InnoDB 存储引擎的工作 原理、实现和应用,可以参考《MySQL 技术内幕:InnoDB 存储引擎》一书。

#MyISAM 存储引擎
不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。

#NDB 存储引擎
2003 年,MySQL AB 公司从 Sony Ericsson 公司收购了 NDB 存储引擎。 NDB 存储引擎是一个集群存储引擎,类似于 Oracle 的 RAC 集群,不过与 Oracle RAC 的 share everything 结构不同的是,其结构是 share nothing 的集群架构,因此能提供更高级别的 高可用性。NDB 存储引擎的特点是数据全部放在内存中(从 5.1 版本开始,可以将非索引数 据放在磁盘上),因此主键查找(primary key lookups)的速度极快,并且能够在线添加 NDB 数据存储节点(data node)以便线性地提高数据库性能。由此可见,NDB 存储引擎是高可用、 高性能、高可扩展性的数据库集群系统,其面向的也是 OLTP 的数据库应用类型。

#Memory 存储引擎
正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。

#Infobright 存储引擎
第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。

#NTSE 存储引擎
网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。

#BLACKHOLE
黑洞存储引擎，可以应用于主备复制中的分发主库。

MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

mysql存储引擎介绍

使用存储引擎

方法1：建表时指定

mysql> create table innodb_t1(id int,name char)engine=innodb;
mysql> create table innodb_t2(id int)engine=innodb;
mysql> show create table innodb_t1;
mysql> show create table innodb_t2;

方法2：在配置文件中指定默认的存储引擎

/etc/my.cnf
[mysqld]
default-storage-engine=INNODB
innodb_file_per_table=1

练习

创建四个表，分别使用innodb，myisam，memory，blackhole存储引擎，进行插入数据测试

mysql> create table t1(id int)engine=innodb;
mysql> create table t2(id int)engine=myisam;
mysql> create table t3(id int)engine=memory;
mysql> create table t4(id int)engine=blackhole;
mysql> quit
[root@haha db1]# ls /var/lib/mysql/db1/ #发现后两种存储引擎只有表结构，无数据
db.opt  t1.frm  t1.ibd  t2.MYD  t2.MYI  t2.frm  t3.frm  t4.frm

#memory，在重启mysql或者重启机器后，表内数据清空
#blackhole，往表内插入任何数据，都相当于丢入黑洞，表内永远不存记录

表介绍

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

id	name	age	sex
1	剑圣	18	male
2	鬼泣	19	male
3	阿修罗	25	male
4	狂战	30	male

id，name，qq，age称为字段，其余的，一行内容称为一条记录

创建表

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

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

mysql> create database db3 charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use db3;
Database changed
mysql> create table t1(
    -> id int,
    -> name varchar(50),
    -> sex enum("male","female"),
    -> age int(3)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

mysql> desc t1;　　查看表结构
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select id,name,sex,age from t1;  查看表里面的所有字段
Empty set (0.00 sec)

mysql> select * from t1;  查看表里面的所有字段
Empty set (0.00 sec)

mysql> select id,name from t1;　　查看表里面的部分字段
Empty set (0.00 sec)

往表中插入数据
mysql> insert into t1 values
    -> (1,"jiansheng","male","20"),
    -> (2,"guiqi","male",25);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+-----------+------+------+
| id   | name      | sex  | age  |
+------+-----------+------+------+
|    1 | jiansheng | male |   20 |
|    2 | guiqi     | male |   25 |
+------+-----------+------+------+
2 rows in set (0.00 sec)

mysql> insert into t1(id) values
    -> (3),
    -> (4);
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+-----------+------+------+
| id   | name      | sex  | age  |
+------+-----------+------+------+
|    1 | jiansheng | male |   20 |
|    2 | guiqi     | male |   25 |
|    3 | NULL      | NULL | NULL |
|    4 | NULL      | NULL | NULL |
+------+-----------+------+------+
4 rows in set (0.00 sec)

注意注意注意：表中的最后一个字段不要加逗号

查看表结构

mysql> describe t1;			#查看表结构，可简写为desc  表名
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> show create table t1\G;		#查看表的详细结构，可加\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `age` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

数据类型

严格模式与非严格模式

什么是严格模式
对插入的数据严格要求 不再范围内直接报错 例如往tinyint中插入大于255的值将报错

什么是非严格模式
不对插入的数据严格要求 不再范围内也可以保存 保存的当前类型最大支持的值

5.6默认是非严格模式
5.7以后默认严格模式

查看SQL模式
select @@sql_mode;
show variables like "sql_mode";

修改SQL模式
set @@sql_mode = "值";

正常情况不需要改

数据类型介绍

存储引擎决定了表的类型，而表内存放的数据也要有不同的类型，每种数据类型都有自己的宽度，但宽度是可选的

详细参考：

http://www.runoob.com/mysql/mysql-data-types.html
http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html

Mysql常用数据类型概览

1. 数字：
整型：tinyinit  int  bigint
小数：
   float ：在位数比较短的情况下不精准
   double ：在位数比较长的情况下不精准
         0.000001230123123123
         存成：0.000001230000
   decimal：（如果用小数，则用推荐使用decimal）
         精准
          内部原理是以字符串形式去存
2. 字符串：
    char（10）：简单粗暴，浪费空间，存取速度快
    root存成root000000
    varchar：精准，节省空间，存取速度慢
    sql优化：创建表时，定长的类型往前放，变长的往后放
                 比如性别           比如地址或描述信息
    >255个字符，超了就把文件路径存放到数据库中。
            比如图片，视频等找一个文件服务器，数据库中只存路径或url。
3. 时间类型：
    最常用：datetime
4. 枚举类型与集合类型

数值类型

整数类型

整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

作用：存储年龄，等级，id，各种号码等

tinyint[(m)] [unsigned] [zerofill]
小整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -128 ～ 127
            无符号：
                0 ～ 255
            PS： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]
整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -2147483648 ～ 2147483647
            无符号：
                    0 ～ 4294967295
bigint[(m)][unsigned][zerofill]
大整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                    -9223372036854775808 ～ 9223372036854775807
            无符号：
                    0  ～  18446744073709551615

#有符号和无符号tinyint     这是5.6版本的mysql，5.7的mysql不一样
#tinyint默认为有符号
mysql> create table t1(x tinyint); #默认为有符号，即数字前有正负号
mysql> desc t1;
mysql> insert into t1 values
    -> (-129),
    -> (-128),
    -> (127),
    -> (128);
mysql> select * from t1;
+------+
| x    |
+------+
| -128 | #-129存成了-128
| -128 | #有符号，最小值为-128
|  127 | #有符号，最大值127
|  127 | #128存成了127
+------+

#设置无符号tinyint　　　　　　
mysql> create table t2(x tinyint unsigned);
mysql> insert into t2 values
    -> (-1),
    -> (0),
    -> (255),
    -> (256);
mysql> select * from t2;
+------+
| x    |
+------+
|    0 | -1存成了0
|    0 | #无符号，最小值为0
|  255 | #无符号，最大值为255
|  255 | #256存成了255
+------+

建表后用alter修改

#有符号和无符号int
#int默认为有符号
mysql> create table t3(x int); #默认为有符号整数
mysql> insert into t3 values
    -> (-2147483649),
    -> (-2147483648),
    -> (2147483647),
    -> (2147483648);
mysql> select * from t3;
+-------------+
| x           |
+-------------+
| -2147483648 | #-2147483649存成了-2147483648
| -2147483648 | #有符号，最小值为-2147483648
|  2147483647 | #有符号，最大值为2147483647
|  2147483647 | #2147483648存成了2147483647
+-------------+


#设置无符号int
mysql> create table t4(x int unsigned);
mysql> insert into t4 values
    -> (-1),
    -> (0),
    -> (4294967295),
    -> (4294967296);
mysql> select * from t4;
+------------+
| x          |
+------------+
|          0 | #-1存成了0
|          0 | #无符号，最小值为0
| 4294967295 | #无符号，最大值为4294967295
| 4294967295 | #4294967296存成了4294967295
+------------+

#有符号和无符号bigint
mysql> create table t6(x bigint);
mysql> insert into t5 values  
    -> (-9223372036854775809),
    -> (-9223372036854775808),
    -> (9223372036854775807),
    -> (9223372036854775808);

mysql> select * from t5;
+----------------------+
| x                    |
+----------------------+
| -9223372036854775808 |
| -9223372036854775808 |
|  9223372036854775807 |
|  9223372036854775807 |
+----------------------+

mysql> create table t6(x bigint unsigned);
mysql> insert into t6 values  
    -> (-1),
    -> (0),
    -> (18446744073709551615),
    -> (18446744073709551616);

mysql> select * from t6;
+----------------------+
| x                    |
+----------------------+
|                    0 |
|                    0 |
| 18446744073709551615 |
| 18446744073709551615 |
+----------------------+

#用zerofill测试整数类型的显示宽度　　　　
加上zerofill 指定当存储的数值的十进制位数  小于所设置的显示宽度时 用0来填充

mysql> create table t7(x int(3) zerofill);
mysql> insert into t7 values
    -> (1),
    -> (11),
    -> (111),
    -> (1111);
mysql> select * from t7;
+------+
| x    |
+------+
|  001 |
|  011 |
|  111 |
| 1111 | #超过宽度限制仍然可以存，整数类型的后面的括号里面的数字仅仅代表最小显示宽度，不足就补齐
+------+

注意：为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关，存储范围如下

其实我们完全没必要为整数类型指定显示宽度，使用默认的就可以了

默认的显示宽度，都是在最大值的基础上加1

int的存储宽度是4个Bytes，即32个bit，即2**32

无符号最大值为：4294967296-1

有符号最大值：2147483648-1

有符号和无符号的最大数字需要的显示宽度均为10，而针对有符号的最小值则需要11位才能显示完全，所以int类型默认的显示宽度为11是非常合理的

最后：整形类型，其实没有必要指定显示宽度，使用默认的就ok

浮点型

定点数类型 DEC等同于DECIMAL　　

浮点类型：FLOAT DOUBLE

作用：存储薪资、身高、体重、体质参数等

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
          单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
精确度： 
           **** 随着小数的增多，精度变得不准确 ****

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
定义：
           双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30        
精确度：
           ****随着小数的增多，精度比float要高，但也会变得不准确 ****

decimal[(m[,d])] [unsigned] [zerofill]
定义：
          准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
精确度：
           **** 随着小数的增多，精度始终准确 ****
           对于精确数值计算时需要用此类型
           decaimal能够存储精确值的原因在于其内部按照字符串存储。

mysql> create table t1(x float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t1(x float(256,30));
ERROR 1439 (42000): Display width out of range for column 'x' (max = 255)
mysql> create table t1(x float(255,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(x double(255,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)

mysql> create table t3(x decimal(66,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t3(x decimal(66,30));
ERROR 1426 (42000): Too-big precision 66 specified for 'x'. Maximum is 65.
mysql> create table t3(x decimal(65,30)); #建表成功
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1            |
| t2            |
| t3            |
+---------------+
3 rows in set (0.00 sec)



mysql> insert into t1 values(1.1111111111111111111111111111111); #小数点后31个1
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values(1.1111111111111111111111111111111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(1.1111111111111111111111111111111);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t1; #随着小数的增多，精度开始不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t2; #精度比float要准确点，但随着小数的增多，同样变得不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t3; #精度始终准确,d为30，于是只留了30位小数
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

验证结果

位类型（了解）

BIT(M)可以用来存放多位二进制数，M范围从1~64，如果不写默认为1位。
注意：对于位字段需要使用函数读取
bin()显示为二进制
hex()显示为十六进制

#5.6版本的mysql才有这种操作
mysql> create table t9(id bit);
mysql> desc t9; #bit默认宽度为1
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+

mysql> insert into t9 values(8);
mysql> select * from t9; #直接查看是无法显示二进制位的
+------+
| id   |
+------+
|     |
+------+
mysql> select bin(id),hex(id) from t9; #需要转换才能看到
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+

mysql> alter table t9 modify id bit(5);
mysql> insert into t9 values(8);
mysql> select bin(id),hex(id) from t9;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
| 1000    | 8       |
+---------+---------+

#5.7版本的会报错

View Code

日期类型

DATE TIME DATETIME TIMESTAMP YEAR

作用：存储用户注册时间，文章发布时间，员工入职时间，出生时间，过期时间等

YEAR
    YYYY（1901/2155）
DATE
    YYYY-MM-DD（1000-01-01/9999-12-31）
TIME
    HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME
    YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）
TIMESTAMP
    YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时


#year   5.6版本mysql
mysql> create table t10(born_year year); #无论year指定何种宽度，最后都默认是year(4)
mysql> insert into t10 values  
    -> (1900),
    -> (1901),
    -> (2155),
    -> (2156);
mysql> select * from t10;
+-----------+
| born_year |
+-----------+
|      0000 |
|      1901 |
|      2155 |
|      0000 |
+-----------+

#5.7版本会报错
mysql> create table t10(born_year year);
Query OK, 0 rows affected (0.24 sec)

mysql> insert into t10 values
    -> (1900),
    -> (1901),
    -> (2155),
    -> (2156);
ERROR 1264 (22003): Out of range value for column 'born_year' at row 1


#date,time,datetime
mysql> create table t11(d date,t time,dt datetime);
mysql> desc t11;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d     | date     | YES  |     | NULL    |       |
| t     | time     | YES  |     | NULL    |       |
| dt    | datetime | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

mysql> insert into t11 values(now(),now(),now());
mysql> select * from t11;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2017-07-25 | 16:26:54 | 2017-07-25 16:26:54 |
+------------+----------+---------------------+

mysql> insert into t11 values(null,null,null);
Query OK, 1 row affected (0.07 sec)

mysql> select * from t11;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-11-21 | 14:27:17 | 2018-11-21 14:27:17 |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
2 rows in set (0.00 sec)

mysql> insert into t11 values();
Query OK, 1 row affected (0.06 sec)

mysql> select * from t11;
+------------+----------+---------------------+
| d          | t        | dt                  |
+------------+----------+---------------------+
| 2018-11-21 | 14:27:17 | 2018-11-21 14:27:17 |
| NULL       | NULL     | NULL                |
| NULL       | NULL     | NULL                |
+------------+----------+---------------------+
3 rows in set (0.00 sec)

#timestamp
mysql> create table t12(time timestamp);
mysql> insert into t12 values();
mysql> insert into t12 values(null);
mysql> select * from t12;
+---------------------+
| time                |
+---------------------+
| 2017-07-25 16:29:17 |
| 2017-07-25 16:30:01 |
+---------------------+



注意啦，注意啦，注意啦
1. 单独插入时间时，需要以字符串的形式，按照对应的格式插入
2. 插入年份时，尽量使用4位值
3. 插入两位年份时，<=69，以20开头，比如50,  结果2050      
                >=70，以19开头，比如71，结果1971
mysql> create table t12(y year);
mysql> insert into t12 values  
    -> (50),
    -> (71);
mysql> select * from t12;
+------+
| y    |
+------+
| 2050 |
| 1971 |
+------+

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> born_year year,
    -> birth date,
    -> class_time time,
    -> reg_time datetime);

mysql> insert into student values
    -> (1,'dsb',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"),
    -> (2,'xsb',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"),
    -> (3,'lsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");

mysql> select * from student;
+------+------+-----------+------------+------------+---------------------+
| id   | name | born_year | birth      | class_time | reg_time            |
+------+------+-----------+------------+------------+---------------------+
|    1 | dsb |      1995 | 1995-11-11 | 11:11:11   | 2017-11-11 11:11:11 |
|    2 | xsb |      1997 | 1997-12-12 | 12:12:12   | 2017-12-12 12:12:12 |
|    3 | lsb  |      1998 | 1998-01-01 | 13:13:13   | 2017-01-01 13:13:13 |
+------+------+-----------+------------+------------+---------------------+

四种时间类型的实例

在实际应用的很多场景中，MySQL的这两种日期类型都能够满足我们的需要，存储精度都为秒，但在某些情况下，会展现出他们各自的优劣。下面就来总结一下两种日期类型的区别。

1.DATETIME的日期范围是1001——9999年，TIMESTAMP的时间范围是1970——2038年。

2.DATETIME存储时间与时区无关，TIMESTAMP存储时间与时区有关，显示的值也依赖于时区。在mysql服务器，操作系统以及客户端连接都有时区的设置。

3.DATETIME使用8字节的存储空间，TIMESTAMP的存储空间为4字节。因此，TIMESTAMP比DATETIME的空间利用率更高。

4.DATETIME的默认值为null；TIMESTAMP的字段默认不为空（not null）,默认值为当前时间（CURRENT_TIMESTAMP），如果不做特殊处理，并且update语句中没有指定该列的更新值，则默认更新为当前时间。

datatime与timestamp的区别

mysql> create table t1(x datetime not null default now()); # 需要指定传入空值时默认取当前时间
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(x timestamp); # 无需任何设置，在传空值的情况下自动传入当前时间
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+---------------------+
| x                   |
+---------------------+
| 2018-07-07 01:26:14 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from t2;
+---------------------+
| x                   |
+---------------------+
| 2018-07-07 01:26:17 |
+---------------------+
1 row in set (0.00 sec)

datatime与timestamp的实例

字符串类型

注意：char和varchar括号内的参数指的都是字符的长度

char类型：定长，浪费空间，存取速度快
    字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
    存储：
        存储char类型的值时，会往右填充空格来满足长度
        例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储
    检索：
        在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
varchar类型：可变长，精准，节省空间，存取速度慢
    字符长度范围：0-65535（如果大于21845会提示用其他类型 。mysql行最大限制为65535字节，字符编码为utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
    存储：
        varchar类型存储数据的真实内容，不会用空格填充，如果'ab  ',尾部的空格也会被存起来
        强调：varchar类型会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数（1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用）
        如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
        如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）  
    检索：
        尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

官网：https://dev.mysql.com/doc/refman/5.7/en/char.html

Value	`CHAR(4)`	Storage Required	`VARCHAR(4)`	Storage Required
`''`	`' '`	4 bytes	`''`	1 byte
`'ab'`	`'ab '`	4 bytes	`'ab'`	3 bytes
`'abcd'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes
`'abcdefgh'`	`'abcd'`	4 bytes	`'abcd'`	5 bytes

测试前了解
两个函数 length：查看字节数
char_length:查看字符数

mysql会在存储数据时自动将数据末尾的空格去掉
如果必须要存空格需要修改sql_mode 增加 PAD_CHAR_TO_FULL_LENGTH 意思是把空格当作有效数据

由于自动去除空格这个机制在使用等于符号和like时有区别
select *from t1 where name = "yh "; 会自动去除空格
select *from t1 where name like "yh "; 不会自动去除空格
like 用于模糊匹配使用%表示0或任意个任意字符使用_表示一个任意字符

1. char填充空格来满足固定长度，但是在查询时却会很不要脸地删除尾部的空格（装作自己好像没有浪费过空间一样），然后修改sql_mode让其现出原形

mysql> create table t1(x char(5),y varchar(5));
Query OK, 0 rows affected (0.26 sec)

#char存5个字符，而varchar存4个字符
mysql> insert into t1 values('你瞅啥 ','你瞅啥 ');
Query OK, 1 row affected (0.05 sec)

mysql> SET sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

#在检索时char很不要脸地将自己浪费的2个字符给删掉了，装的好像自己没浪费过空间一样，而varchar很老实，存了多少，就显示多少
mysql> select x,char_length(x),y,char_length(y) from t1; 
+-----------+----------------+------------+----------------+
| x         | char_length(x) | y          | char_length(y) |
+-----------+----------------+------------+----------------+
| 你瞅啥    |              3 | 你瞅啥     |              4 |
+-----------+----------------+------------+----------------+
1 row in set (0.00 sec)

#略施小计，让char现出原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected (0.00 sec)

#这下子char原形毕露了......
mysql> select x,char_length(x),y,char_length(y) from t1;
+-------------+----------------+------------+----------------+
| x           | char_length(x) | y          | char_length(y) |
+-------------+----------------+------------+----------------+
| 你瞅啥      |              5 | 你瞅啥     |              4 |
+-------------+----------------+------------+----------------+
1 row in set (0.00 sec)


#char类型：3个中文字符+2个空格=11Bytes
#varchar类型:3个中文字符+1个空格=10Bytes
mysql> select x,length(x),y,length(y) from t1;
+-------------+-----------+------------+-----------+
| x           | length(x) | y          | length(y) |
+-------------+-----------+------------+-----------+
| 你瞅啥      |        11 | 你瞅啥     |        10 |
+-------------+-----------+------------+-----------+
1 row in set (0.00 sec)

View Code

mysql> select concat('数据: ',x,'长度: ',char_length(x)),concat(y,char_length(y)
) from t1;
+------------------------------------------------+--------------------------+
| concat('数据: ',x,'长度: ',char_length(x))     | concat(y,char_length(y)) |
+------------------------------------------------+--------------------------+
| 数据: 你瞅啥  长度: 5                          | 你瞅啥 4                 |
+------------------------------------------------+--------------------------+
1 row in set (0.00 sec)

了解concat

官方手册专门有一节介绍 https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html 。 SQL Mode 定义了两个方面：MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查

2.虽然 CHAR 和 VARCHAR 的存储方式不太相同,但是对于两个字符串的比较,都只比较其值,忽略 CHAR 值存在的右填充,即使将 SQL _MODE 设置为 PAD_CHAR_TO_FULL_ LENGTH 也一样,,但这不适用于like

Values in CHAR and VARCHAR columns are sorted and compared according to the character set collation assigned to the column.

All MySQL collations are of type PAD SPACE. This means that all CHAR, VARCHAR, and TEXT values are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. For example:

mysql> CREATE TABLE names (myname CHAR(10));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO names VALUES ('Monty');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty', myname = 'Monty  ' FROM names;
+------------------+--------------------+
| myname = 'Monty' | myname = 'Monty  ' |
+------------------+--------------------+
|                1 |                  1 |
+------------------+--------------------+
1 row in set (0.00 sec)

mysql> SELECT myname LIKE 'Monty', myname LIKE 'Monty  ' FROM names;
+---------------------+-----------------------+
| myname LIKE 'Monty' | myname LIKE 'Monty  ' |
+---------------------+-----------------------+
|                   1 |                     0 |
+---------------------+-----------------------+
1 row in set (0.00 sec)

View Code

3.总结

#InnoDB存储引擎：建议使用VARCHAR类型
单从数据类型的实现机制去考虑，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。

但对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

#其他字符串系列（效率：char>varchar>text）
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列    TINYBLOB BLOB MEDIUMBLOB LONGBLOB 
BINARY系列 BINARY VARBINARY

text：text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

View Code

枚举类型与集合类型

字段的值只能在给定范围中选择，如单选框，多选框
enum 单选只能在给定的范围内选一个值，如性别 sex 男male/女female
set 多选在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）　集合的记录要把两个值放在一个双（单）引号中

mysql> create table consumer( 
    -> name varchar(50),
    -> sex enum('male','female'),
    -> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定范围内，多选一
    -> hobby set('play','music','read','study') #在指定范围内，多选多
    -> );

mysql> insert into consumer values  
    -> ('dsb','male','vip5','read,study'),
    -> ('xsb','female','vip1','girl');

mysql> select * from consumer;
+------+--------+-------+------------+
| name | sex    | level | hobby      |
+------+--------+-------+------------+
| dsb | male   | vip5  | read,study |
| xsb | female | vip1  |            |
+------+--------+-------+------------+

View Code

表完整性约束

表完整性约束介绍

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

常见约束和基础语法如下：

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

语法:
        创建时指定约束
        create table 表名称(字段名 类型(长度) 约束名称1 约束名称n,....)
        后期修改的方式添加 约束
        alter table 表名称 modify 字段名 类型(长度) 约束名称1 约束名称n,....

1. 是否允许为空 默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum('male','female') not null default 'male'
age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20
3. 是否是key
主键 primary key
外键 foreign key
索引 (index,unique...)

not null与default

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

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
nid int not null defalut 2,
num int not null
)

#not null
mysql> create table t1(id int); #id字段默认可以插入空
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t1 values(); #可以插入空


mysql> create table t2(id int not null); #设置字段id不为空
mysql> desc t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> insert into t2 values(); #不能插入空
ERROR 1364 (HY000): Field 'id' doesn't have a default value

#default
#设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值
mysql> create table t3(id int default 1);
mysql> alter table t3 modify id int not null default 1;

#综合练习
mysql> create table student(
    -> name varchar(20) not null,
    -> age int(3) unsigned not null default 18,
    -> sex enum('male','female') default 'male',
    -> hobby set('play','study','read','music') default 'play,music'
    -> );
mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type                               | Null | Key | Default    | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name  | varchar(20)                        | NO   |     | NULL       |       |
| age   | int(3) unsigned                    | NO   |     | 18         |       |
| sex   | enum('male','female')              | YES  |     | male       |       |
| hobby | set('play','study','read','music') | YES  |     | play,music |       |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('egon');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex  | hobby      |
+------+-----+------+------------+
| egon |  18 | male | play,music |
+------+-----+------+------------+

unique

#设置唯一约束 UNIQUE
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);

方法二：
create table department2(
id int,
name varchar(20),
comment varchar(100),
constraint uk_name unique(name)
);

mysql> insert into department1 values(1,'IT','技术');
Query OK, 1 row affected (0.00 sec)
mysql> insert into department1 values(1,'IT','技术');
ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

每一个表都应该有一个主键 需要唯一标识 否则可以可能出现完全相同的两个数据 无法区分
UNIQUE 不能重复  但是可以为空 这样也不能唯一标识
UNIQUE NOT NULL 不能为空且唯一  可以唯一标识一条数据  书写顺序无所谓


UNIQUE NOT NULL 与 主键的区别

UNIQUE NOT NULL 不能被其他表引用 (不能作为其它表的外键)
UNIQUE NOT NULL 约束一个表中可以有多个   但是主键只能有一个


mysql> use b2
Database changed
mysql> create table t1(id int not null unique);
Query OK, 0 rows affected (0.30 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

not null+unique的详细分析

如果业务中要求两个字符联合起了是唯一的，比如“地址”+“名称”是唯一的，这就需要对两列，甚至多列添加联合唯一约束
多字段联合主键: 不常用
        学生表  stu_id course_id 做为联合主键
        1 1 已有数据
        1 2 可以插入
        2 1 可以插入
        1 1 不能插入
        只有当两个字段都重复才算重复


create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);

mysql> insert into service values
    -> (1,'nginx','192.168.0.10',80),
    -> (2,'haproxy','192.168.0.20',80),
    -> (3,'mysql','192.168.0.30',3306)
    -> ;
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

补充：
增加唯一性约束：
alter table tableName add unique(column_name)1
查看唯一性约束
show keys from tableName;1
会出现一个大表格从里面可以找到需要删除字段的Key_name。
删除唯一性约束
drop index Key_name on tableName;


还有一种情况就是,我们需要为以前的表 创建这个索引,有可能以前的数据中存在重复的记录 那怎么办呢? 
alter ignore table t_aa add unique index(aa,bb); 
它会删除重复的记录（会保留一条），然后建立唯一索引，高效而且人性化。

联合唯一约束及及拓展

primary key

从约束角度看primary key字段的值不为空且唯一，那我们直接使用not null+unique不就可以了吗，要它干什么？

主键primary key是innodb存储引擎组织数据的依据，innodb称之为索引组织表，一张表中必须有且只有一个主键。

索引:用于加速查询
InnoDB 中索引是树形结构
为了提高查询效率 InnoDB为找一个不为空且唯一的字段作为主键
如果表中不存在这样的字段会自动帮你建一个隐藏主键字段但是无法提升查询效率

只要是使用innoDB 就应该为每个表指定一个非空且唯一的字段
InnoDB阻止数据时首先使用主键如果没有主键找一个非空且唯一如果也没有建一个隐藏字段

一个表中可以：

单列做主键
多列做主键（复合主键）

#单列做主键
#方法一：not null+unique
create table department1(
id int not null unique, #主键
name varchar(20) not null unique,
comment varchar(100)
);

mysql> desc department1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | NO   | UNI | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)

#方法二：在某一个字段后用primary key
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);

mysql> desc department2;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.00 sec)

#方法三：在所有字段后单独定义primary key
create table department3(
id int,
name varchar(20),
comment varchar(100),
constraint pk_name primary key(id); #创建主键并为其命名pk_name，这个pk_name是约束名称。约束名称是一个约束条件的名称，不是列的名称


mysql> desc department3;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | NO   | PRI | NULL    |       |
| name    | varchar(20)  | YES  |     | NULL    |       |
| comment | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
rows in set (0.01 sec)


mysql想要删除这个主键约束，语法和其他数据库都不一样，语法为
alter table 表名 drop primary key

单列主键

#多列做主键
create table service(
ip varchar(15),
port char(5),
service_name varchar(10) not null,
primary key(ip,port)
);


mysql> desc service;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| ip           | varchar(15) | NO   | PRI | NULL    |       |
| port         | char(5)     | NO   | PRI | NULL    |       |
| service_name | varchar(10) | NO   |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into service values
    -> ('172.16.45.10','3306','mysqld'),
    -> ('172.16.45.11','3306','mariadb')
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
你会发现这条语句是完全可行的，是完全没有错误的，这是为什么呢，这是因为这是一个联合主键，它的作用是ip不能重复 且 port也不能重复，注意是且
mysql> insert into service values ('172.16.45.10','3306','nginx');
ERROR 1062 (23000): Duplicate entry '172.16.45.10-3306' for key 'PRIMARY'
如果这样写的话就是大错特错了，因为这违反了联合主键。因为ip和port同时相同了就是这个道理。
那么要怎样才能实现ip不能相同，同时port也不能相同呢，这就是之前讲的unique的作用了，加两个unipue。

多列主键

auto_increment

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

#不指定id，则自动增长
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values
    -> ('egon'),
    -> ('alex')
    -> ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
|  2 | alex | male |
+----+------+------+


#也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | egon | male   |
|  2 | alex | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


#对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | egon | male |
+----+------+------+
1 row in set (0.00 sec)

实例

了解知识：

#在创建完表后，修改自增字段的起始值
mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> sex enum('male','female') default 'male'
    -> );

mysql> alter table student auto_increment=3;

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

mysql> insert into student(name) values('egon');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  3 | egon | male |
+----+------+------+
row in set (0.00 sec)

mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8


#也可以创建表时指定auto_increment的初始值，注意初始值的设置为表选项，应该放到括号外
create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
)auto_increment=3;




#设置步长
sqlserver：自增步长
    基于表级别
    create table t1（
        id int。。。
    ）engine=innodb,auto_increment=2 步长=2 default charset=utf8

mysql自增的步长：
    show session variables like 'auto_inc%';
    
    #基于会话级别
    set session auth_increment_increment=2 #修改会话级别的步长

    #基于全局级别的
    set global auth_increment_increment=2 #修改全局级别的步长（所有会话都生效）


#！！！注意了注意了注意了！！！
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. 
翻译：如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略 ，这相当于第一步步子就迈大了，扯着了蛋
比如：设置auto_increment_offset=3，auto_increment_increment=2




mysql> set global auto_increment_increment=5;
Query OK, 0 rows affected (0.00 sec)

mysql> set global auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'auto_incre%'; #需要退出重新登录
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+



create table student(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') default 'male'
);

mysql> insert into student(name) values('egon1'),('egon2'),('egon3');
mysql> select * from student;
+----+-------+------+
| id | name  | sex  |
+----+-------+------+
|  3 | egon1 | male |
|  8 | egon2 | male |
| 13 | egon3 | male |
+----+-------+------+

步长:auto_increment_increment,起始偏移量:auto_increment_offset

foreign key

快速理解foreign key

员工信息表有三个字段：工号姓名部门

公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储

将部门数据与员工数据放到同一张表中会造成
数据重复
结构混乱
扩展维护性差
需要分表

解决方法：

我们完全可以定义一个部门表

然后让员工信息表关联该表，如何关联，即foreign key

外键约束

使用外键时 必须分清 主从关系

外键的第一种约束
先建主表
再建从表
create table dept(id int primary key auto_increment,name char(20),job char(20));
create table emp(id int primary key auto_increment,name char(20),d_id int,foreign key(d_id) references dept(id));

外键的第二个约束
先插入主表
再插入从表

外键的第三个约束
删除记录时
先删除从表记录
再删主表记录

外键的第四个约束
从表更新外键时 必须保证外键是存在的

外键的第五个约束
更新主表的id时
必须先删除从表关联的数据
或者把关联数据 关联其他的主表id
　
外键的第六个约束　
删除主表时 要先删除从表

有了这几个约束后 主表和从表中的数据必然是完整的
相应的收到外键约束  主表的删除  更新操作受到限制
很多情况下 我们就是要删除一个部门 然而需要至少两条sql语句   麻烦
可以使用级联
create table emp(
id int primary key auto_increment,
name char(20),
d_id int,
foreign key(d_id) references dept(id)
on delete cascade
on update cascade
);

on delete cascade 当主表删除记录时 从表相关联的记录同步删除
on update cascade 当主表id更新时 从表相关联的记录同步更新
注意是单向的  主表变化是 级联操作从表   从表的变化不会级联到主表

#表类型必须是innodb存储引擎，且被关联的字段，即references指定的另外一个表的字段，必须保证唯一
create table department(
id int primary key,
name varchar(20) not null
)engine=innodb;

#dpt_id外键，关联父表（department主键id），同步更新，同步删除
create table employee(
id int primary key,
name varchar(20) not null,
dpt_id int,
constraint fk_name foreign key(dpt_id)
references department(id)
on delete cascade
on update cascade 
)engine=innodb;


#先往父表department中插入记录
insert into department values
(1,`事业部`),
(2,`资源部`),
(3,`销售部`);


#再往子表employee中插入记录
insert into employee values
(1,'sss',1),
(2,'sssddd',2),
(3,'sssssddd',2),
(4,'sssssweddd',2),
(5,'sweddd',3),
(6,'sweddd',3)
;


#删父表department，子表employee中对应的记录跟着删
mysql> delete from department where id=3;
mysql> select * from employee;
+----+------------+--------+
| id | name       | dpt_id |
+----+------------+--------+
|  1 | sss        |      1 |
|  2 | sssddd     |      2 |
|  3 | sssssddd   |      2 |
|  4 | sssssweddd |      2 |
+----+------------+--------+


#更新父表department，子表employee中对应的记录跟着改
mysql> update department set id=22222 where id=2;
mysql> select * from employee;
+----+------------+--------+
| id | name       | dpt_id |
+----+------------+--------+
|  1 | sss        |      1 |
|  2 | sssddd     |  22222 |
|  3 | sssssddd   |  22222 |
|  4 | sssssweddd |  22222 |
+----+------------+--------+

实例运用

如何找出两张表之间的关系

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

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

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

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

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

View Code

建立表之间的关系

一对多或称为多对一
三张表：出版社，作者信息，书

一对多（或多对一）：一个出版社可以出版多本书

关联方式：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)
;

View Code

多对多
三张表：出版社，作者信息，书
多对多：一个作者可以写多本书，一本书也可以有多个作者，双向的一对多，即多对多　
关联方式：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');

#每个作者与自己的代表作如下
1 egon: 
      1 九阳神功
      2 九阴真经
      3 九阴白骨爪
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典

2 alex: 
      1 九阳神功
      6 葵花宝典

3 yuanhao:
      4 独孤九剑
      5 降龙十巴掌
      6 葵花宝典

4 wpq:
      1 九阳神功

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)
;

中间那一张存放关系的表，对外关联的字段可以联合唯一

多对多实例1

create table book(
id int primary key auto_increment,
name varchar(20) not null
);


create table author (
id int primary key auto_increment,
name varchar(20),
price float(5)
);


create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on delete cascade
on update cascade,
foreign key(author_id) references author(id)
on delete cascade
on update cascade,
unique(book_id,author_id)
);

对对多实例2

一对一
两张表：学生表和客户表
一对一：一个学生是一个客户，一个客户有可能变成一个学校，即一对一的关系
关联方式：foreign key+unique

#一定是student来 references 表customer，这样就保证了：
#1 学生一定是一个客户，
#2 客户不一定是学生，但有可能成为一个学生


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
);


#增加客户
insert into customer(name,qq,phone) values
('李xx','31811231',13811341220),
('王xx,'123123123',15213146809),
('守xx','283818181',1867141331),
('吴xx','283818181',1851143312)
;

#增加学生
insert into student(class_name,customer_id) values
('3班',3),
('19班',4),
('19班',5)
;

一对一实例

目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。
这时候我们就可以通过分表降低单次查询数据量，从而提高查询速度，一般分表的方式有两种：水平拆分和垂直拆分，两者各有利弊，适用于不同的情况。

水平拆分 
水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。 
 
通常情况下，我们使用取模的方式来进行表的拆分;比如一张有400W的用户表users，为提高其查询效率我们把其分成4张表users1，users2，users3，users4 
通过用ID取模的方法把数据分散到四张表内Id%4+1 = [1,2,3,4] 
然后查询,更新,删除也是通过取模的方法来查询。

水平拆分的优点：  
表关联基本能够在数据库端全部完成；  
不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；  
应用程序端整体架构改动相对较少；  
事务处理相对简单；  
只要切分规则能够定义好，基本上较难遇到扩展性限制；

水平切分的缺点：  
切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则；  
后期数据的维护难度有所增加，人为手工定位数据更困难；  
应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

垂直拆分 
垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。 


通常我们按以下原则进行垂直拆分: 
1，把不常用的字段单独放在一张表;, 
2，把text，blob等大字段拆分出来放在附表中; 
3，经常组合查询的列放在一张表中;

垂直切分的优点 
数据库的拆分简单明了，拆分规则明确；  
应用程序模块清晰明确，整合容易；  
数据维护方便易行，容易定位；

垂直切分的缺点 
部分表关联无法在数据库级别完成，需要在程序中完成；  
对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求；  
事务处理相对更为复杂；  
切分达到一定程度之后，扩展性会遇到限制；  
过读切分可能会带来系统过渡复杂而难以维护。

水平拆分与垂直拆分

修改表ALTER TABLE

语法：
1. 修改表名
      ALTER TABLE 表名 
                          RENAME 新表名;

2. 增加字段
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…],
                          ADD 字段名  数据类型 [完整性约束条件…];
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
      ALTER TABLE 表名
                          ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
                            
3. 删除字段
      ALTER TABLE 表名 
                          DROP 字段名;

4. 修改字段　　　
　　　change意为改变，modify意为修改  也就是说change可以将这个字段名字 属性 和注释全都改了，然而modify不可以改字段名字　　
      ALTER TABLE 表名 
                          MODIFY  字段名 数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
      ALTER TABLE 表名 
                          CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

1. 修改存储引擎
mysql> alter table service 
    -> engine=innodb;

2. 添加字段
mysql> alter table student10
    -> add name varchar(20) not null,
    -> add age int(3) not null default 22;
    
mysql> alter table student10
    -> add stu_num varchar(10) not null after name;                //添加name字段之后

mysql> alter table student10                        
    -> add sex enum('male','female') default 'male' first;          //添加到最前面

3. 删除字段
mysql> alter table student10
    -> drop sex;

mysql> alter table service
    -> drop mac;

alter table 表名 drop column 列名   也可以删除字段

4. 修改字段类型modify
mysql> alter table student10
    -> modify age int(3);
mysql> alter table student10
    -> modify id int(11) not null primary key auto_increment;    //修改为主键

5. 增加约束（针对已有的主键增加auto_increment）
mysql> alter table student10 modify id int(11) not null primary key auto_increment;
ERROR 1068 (42000): Multiple primary key defined

mysql> alter table student10 modify id int(11) not null auto_increment;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6. 对已经存在的表增加复合主键
mysql> alter table service2
    -> add primary key(host_ip,port);        

7. 增加主键
mysql> alter table student1
    -> modify name varchar(10) not null primary key;

8. 增加主键和自动增长
mysql> alter table student1
    -> modify id int not null primary key auto_increment;

9. 删除主键
a. 删除自增约束
mysql> alter table student10 modify id int(11) not null; 

b. 删除主键
mysql> alter table student10                                 
    -> drop primary key;

修改表的常用操作

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引） 不复制约束
mysql> create table new_service select * from service;

只复制表结构
mysql> select * from service where 1=2;        //条件为假，查不到任何记录
Empty set (0.00 sec)
mysql> create table new1_service select * from service where 1=2;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table t4 like employees;

蠕虫复制
自我复制
insert into 表名称 select *from 表名;
如果有主键 避开主键字段
insert into 表名称(其他字段) select 其他字段 from 表名;

sql注入攻击
一个了解sql语法的攻击者  可以在输入框输入sql语句

删除表

DROP TABLE 表名;

记录相关操作

简介

MySQL数据操作： DML

在MySQL管理软件中，可以通过SQL语句中的DML语言来实现数据的操作，包括

使用INSERT实现数据的插入
UPDATE实现数据的更新
使用DELETE实现数据的删除
使用SELECT查询数据以及。

插入数据INSERT

1. 插入完整数据（顺序插入）
    语法一：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) VALUES(值1,值2,值3…值n);
    语法二：
    INSERT INTO 表名 VALUES (值1,值2,值3…值n);
2. 指定字段插入数据
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…) VALUES (值1,值2,值3…);
3. 插入多条记录
    语法：
    INSERT INTO 表名 VALUES
        (值1,值2,值3…值n),
        (值1,值2,值3…值n),
        (值1,值2,值3…值n);    
4. 插入查询结果
    语法：
    INSERT INTO 表名(字段1,字段2,字段3…字段n) 
                    SELECT (字段1,字段2,字段3…字段n) FROM 表2
                    WHERE …;

更新数据UPDATE

语法：
    UPDATE 表名 SET
        字段1=值1,
        字段2=值2,
        WHERE CONDITION;

示例：
    UPDATE mysql.user SET password=password(‘123’) 
        where user=’root’ and host=’localhost’;

删除数据DELETE

语法：
    DELETE FROM 表名 
        WHERE CONITION;
示例：
    DELETE FROM mysql.user 
        WHERE password=’’;

查询数据SELECT

见后面的单表查询和多表查询

posted @ 2018-11-19 17:12 丶无根生阅读(349) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

不积跬步无以至千里

库，表，记录的相关操作

系统数据库

创建数据库

数据库相关操作

存储引擎介绍

什么是存储引擎

mysql支持的存储引擎

使用存储引擎

表介绍

创建表

查看表结构

数据类型

严格模式与非严格模式

数据类型介绍

数值类型

日期类型

字符串类型

枚举类型与集合类型

表完整性约束

表完整性约束介绍

not null与default

unique

primary key

auto_increment

foreign key

修改表ALTER TABLE

复制表

删除表

记录相关操作

简介

插入数据INSERT

更新数据UPDATE

删除数据DELETE

查询数据SELECT

公告