mysql对库,表,数据类型的操作以及完整性约束

一丶对库的操作

　　求救语法:　help create database;

　　1.创建数据库

CREATE DATABASE 数据库名 charset utf8;

　　2.数据库的命名规则:

可以由字母、数字、下划线、＠、＃、＄区分大小写唯一性不能使用关键字如 create select不能单独使用数字最长128位# 基本上跟python或者js的命名规则一样

　　3.数据库的相关操作:

#查看数据库show databases;#查看当前库show create database db1;#查看所在的库select database();#选择数据库use 数据库名#删除数据库DROP DATABASE 数据库名;# 修改数据库alter database db1 charset utf8;

　　4.了解内容

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：1、DDL语句数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER2、DML语句数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT3、DCL语句数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

二丶对表的操作

　　1.存储引擎(了解)

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

mysql> show engines\G;# 查看所有支持的引擎mysql> show variables like 'storage_engine%'; # 查看正在使用的存储引擎

1、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 存储引擎》一书。2、MyISAM 存储引擎不支持事务、表锁设计、支持全文索引,主要面向一些 OLAP 数 据库应用,在 MySQL 5.5.8 版本之前是默认的存储引擎(除 Windows 版本外)。数据库系统 与文件系统一个很大的不同在于对事务的支持,MyISAM 存储引擎是不支持事务的。究其根 本,这也并不难理解。用户在所有的应用中是否都需要事务呢?在数据仓库中,如果没有 ETL 这些操作,只是简单地通过报表查询还需要事务的支持吗?此外,MyISAM 存储引擎的 另一个与众不同的地方是,它的缓冲池只缓存(cache)索引文件,而不缓存数据文件,这与 大多数的数据库都不相同。3、NDB 存储引擎年,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 的数据库应用类型。4、Memory 存储引擎正如其名,Memory 存储引擎中的数据都存放在内存中,数据库重 启或发生崩溃,表中的数据都将消失。它非常适合于存储 OLTP 数据库应用中临时数据的临时表,也可以作为 OLAP 数据库应用中数据仓库的维度表。Memory 存储引擎默认使用哈希 索引,而不是通常熟悉的 B+ 树索引。5、Infobright 存储引擎第三方的存储引擎。其特点是存储是按照列而非行的,因此非常 适合 OLAP 的数据库应用。其官方网站是 http://www.infobright.org/,上面有不少成功的数据 仓库案例可供分析。6、NTSE 存储引擎网易公司开发的面向其内部使用的存储引擎。目前的版本不支持事务, 但提供压缩、行级缓存等特性,不久的将来会实现面向内存的事务支持。7、BLACKHOLE黑洞存储引擎，可以应用于主备复制中的分发主库。MySQL 数据库还有很多其他存储引擎,上述只是列举了最为常用的一些引擎。如果 你喜欢,完全可以编写专属于自己的引擎,这就是开源赋予我们的能力,也是开源的魅 力所在。

　　2.表的操作

　　语法:

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

　　1.创建数据库

create database db2 charset utf8;

　　2.使用数据库

use db2;

　　3.创建a1表:

create table a1(　　id int,　　name varchar(50),　　age int(3));

　　4.插入表的记录

insert into a1 values(1,'mjj',18),(2,'wusir',28);

　　5.查看表的数据和结构

　　(1)查询a1表中的存储数据

select * from a1;

　　(2)查看a1表的结构

desc a1;

　　(3)查看表的详细结构

show create table a1\G;

　　6.复制表

　　(1)新创建一个数据库db3

create database db3 charset utf8;

　　(2)使用db3

use db3

　　(3)复制db2.a1的表结构和记录

create table b1 select * from db2.a1;

　　(4)查看db3.b1中的数据和表结构

select * from db3.b1;

　　还有一种做法，使用like(只拷贝表结构，不拷贝记录)

create table b3 like db2.a1;desc b3;select * from db3.b3;

　　7.删除表

drop table 表名;

三丶数据类型

 　　http://www.runoob.com/mysql/mysql-data-types.html

　　mysql常用数据类型概况:

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

　　1.数值类型

　　整数类型：TINYINT SMALLINT MEDIUMINT INT BIGINT

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

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

　　2.浮点型

　　定点数类型: DEC等同于DECIMAL

　　浮点类型：FLOAT DOUBLE

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

　　语法:

-------------------------FLOAT-------------------FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]#参数解释：单精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30#有符号：-3.402823466E+38 to -1.175494351E-38,1.175494351E-38 to 3.402823466E+38#无符号：1.175494351E-38 to 3.402823466E+38#精确度：**** 随着小数的增多，精度变得不准确 **** -------------------------DOUBLE-----------------------DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]#参数解释： 双精度浮点数（非准确小数值），M是全长，D是小数点后个数。M最大值为255，D最大值为30#有符号：-1.7976931348623157E+308 to -2.2250738585072014E-3082.2250738585072014E-308 to 1.7976931348623157E+308#无符号：2.2250738585072014E-308 to 1.7976931348623157E+308#精确度：****随着小数的增多，精度比float要高，但也会变得不准确 ****======================================--------------------DECIMAL------------------------decimal[(m[,d])] [unsigned] [zerofill]#参数解释：准确的小数值，M是整数部分总个数（负号不算），D是小数点后个数。 M最大值为65，D最大值为30。#精确度：**** 随着小数的增多，精度始终准确 ****对于精确数值计算时需要用此类型decaimal能够存储精确值的原因在于其内部按照字符串存储。

　　3.日期类型

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

语法：YEARYYYY（1901/2155）DATEYYYY-MM-DD（1000-01-01/9999-12-31）TIMEHH:MM:SS（'-838:59:59'/'838:59:59'）DATETIMEYYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59Y）TIMESTAMPYYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

　　datetime与timestamp的区别

在实际应用的很多场景中，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语句中没有指定该列的更新值，则默认更新为当前时间。

　　注意:

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

　　练习:创建一张学生表(student),要求有id,姓名,出生年份,出生的年月日,进班的时间,以及来学习的具体时间

mysql> create table student(-> id int,-> name varchar(20),-> bo_year year,-> birth date,-> class_time time,-> reg_time datetime-> );Query OK, 0 rows affected (0.02 sec)mysql> insert into student values->(1,'alex',"1995","1995-11-11","11:11:11","2017-11-11 11:11:11"),->(2,'egon',"1997","1997-12-12","12:12:12","2017-12-12 12:12:12"),->(3,'wsb',"1998","1998-01-01","13:13:13","2017-01-01 13:13:13");Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Waings: 0mysql>select * from student;+------+------+-----------+------------+------------+---------------------+| id| name | bo_year | birth  | class_time | reg_time|+------+------+-----------+------------+------------+---------------------+|1 | alex |  1995 | 1995-11-11 | 11:11:11| 2017-11-11 11:11:11 ||2 | egon |  1997 | 1997-12-12 | 12:12:12| 2017-12-12 12:12:12 ||3 | wsb  |  1998 | 1998-01-01 | 13:13:13| 2017-01-01 13:13:13 |+------+------+-----------+------------+------------+---------------------+rows in set (0.00 sec)

　　4.字符类型

#官网：https://dev.mysql.com/doc/refman/5.7/en/char.html#注意：char和varchar括号内的参数指的都是字符的长度#char类型：定长，简单粗暴，浪费空间，存取速度快字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）存储：存储char类型的值时，会往右填充空格来满足长度例如：指定长度为10，存>10个字符则报错，存<10个字符则用空格填充直到凑够10个字符存储检索：在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（设置SQL模式：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';　　　　　　查询sql的默认模式：select @@sql_mode;）#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）检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

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

# 创建t1表,分别指明字段x为char类型，字段y为varchar类型mysql> create table t1(x char(5),y varchar(4));Query OK, 0 rows affected (0.16 sec)# char存放的是5个字符，而varchar存4个字符mysql>  insert into t1 values('你瞅啥 ','你瞅啥 ');Query OK, 1 row affected (0.01 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 |+-----------+----------------+------------+----------------+row in set (0.02 sec) #略施小计，让char现原形 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';Query OK, 0 rows affected (0.00 sec)#查看当前mysql的mode模式mysql> select @@sql_mode;+-------------------------+| @@sql_mode  |+-------------------------+| PAD_CHAR_TO_FULL_LENGTH |+-------------------------+row in set (0.00 sec)#原形毕露了吧。。。。mysql> select x,char_length(x) y,char_length(y) from t1;+-------------+------+----------------+| x| y| char_length(y) |+-------------+------+----------------+| 你瞅啥  |5 |  4 |+-------------+------+----------------+row in set (0.00 sec)# 查看字节数#char类型：3个中文字符+2个空格=11Bytes#varchar类型:3个中文字符+1个空格=10Bytesmysql> select x,length(x),y,length(y) from t1;+-------------+-----------+------------+-----------+| x| length(x) | y  | length(y) |+-------------+-----------+------------+-----------+| 你瞅啥  |11 | 你瞅啥 |10 |+-------------+-----------+------------+-----------+row in set (0.02 sec)

#常用字符串系列：char与varchar注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡#其他字符串系列（效率：char>varchar>text）TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXTBLOB 系列TINYBLOB BLOB MEDIUMBLOB LONGBLOB BINARY系列 BINARY VARBINARYtext：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.

 　　5.枚举类型和集合类型

　　字段的值只能在给定范围中选择，如单选框，多选框

　　enum 单选 只能在给定的范围内选一个值，如性别 sex 男male/女female

　　set 多选 在给定的范围内可以选择一个或一个以上的值（爱好1,爱好2,爱好3...）

mysql> create table consumer(-> id int,-> name varchar(50),-> sex enum('male','female','other'),-> level enum('vip1','vip2','vip3','vip4'),#在指定范围内，多选一-> fav set('play','music','read','study') #在指定范围内，多选多-> );Query OK, 0 rows affected (0.03 sec)mysql> insert into consumer values-> (1,'赵云','male','vip2','read,study'),-> (2,'赵云2','other','vip4','play');Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Waings: 0mysql> select * from consumer;+------+---------+-------+-------+------------+| id| name| sex| level | fav|+------+---------+-------+-------+------------+|1 | 赵云| male  | vip2  | read,study ||2 | 赵云2| other | vip4  | play|+------+---------+-------+-------+------------+rows in set (0.00 sec)

 四丶完整性约束

 　　1.介绍:

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

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

　　主要分为：

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

　　说明：

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

 　　2.not null 与default

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

　　默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

create table tb1(nid int not null defalut 2,num int not null);

 　　验证1:

mysql> create table t11(id int);# id字段默认可以为空Query OK, 0 rows affected (0.05 sec)mysql> desc t11;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | YES|  | NULL||+-------+---------+------+-----+---------+-------+row in set (0.03 sec)mysql> insert into t11 values(); #给t11表插一个空的值Query OK, 1 row affected (0.00 sec)#查询结果如下mysql> select * from t11;+------+| id|+------+| NULL |+------+row in set (0.00 sec)默认值可以为空

　　验证2:

mysql> create table t12(id int not null);#设置字段id不为空Query OK, 0 rows affected (0.03 sec)mysql> desc t12;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO| | NULL||+-------+---------+------+-----+---------+-------+row in set (0.01 sec)mysql> insert into t12 values();#不能插入空ERROR 1364 (HY000): Field 'id' doesn't have a default value设置not null，插入值时不能为空

　　验证3

# 第一种情况mysql> create table t13(id int default 1);Query OK, 0 rows affected (0.03 sec)mysql> desc t13;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | YES  || 1  |  |+-------+---------+------+-----+---------+-------+row in set (0.01 sec)mysql> insert into t13 values();Query OK, 1 row affected (0.00 sec)mysql> select * from t13;+------+| id|+------+|1  |+------+row in set (0.00 sec)# 第二种情况mysql> create table t14(id int not null default 2);Query OK, 0 rows affected (0.02 sec)mysql> desc t14;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| id| int(11) | NO  | | 2| |+-------+---------+------+-----+---------+-------+row in set (0.01 sec)mysql> select * from t14;+----+| id |+----+|  2 |+----+row in set (0.00 sec)设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值

 　　练习:创建学生表student2,设置每个字段的约束条件

mysql> create table student2(-> id int not null,-> name varchar(50) not null,-> age int(3) unsigned not null default 18,-> sex enum('male','female') default 'male',-> fav set('smoke','drink','tangtou') default 'drink,tangtou'-> );Query OK, 0 rows affected (0.01 sec)# 只插入了not null约束条件的字段对应的值mysql> insert into student2(id,name) values(1,'mjj');Query OK, 1 row affected (0.00 sec)# 查询结果如下mysql> select * from student2;+----+------+-----+------+---------------+| id | name | age | sex  | fav|+----+------+-----+------+---------------+|  1 | mjj  |  18 | male | drink,tangtou |+----+------+-----+------+---------------+row in set (0.00 sec)

　　3.unique

　　中文翻译：不同的。在mysql中称为单列唯一

　　举例说明：创建公司部门表（每个公司都有唯一的一个部门）。

 　　接下来，使用约束条件unique，来对公司部门的字段进行设置。

#第一种创建unique的方式#例子1：create table department(id int,name char(10) unique);mysql> insert into department values(1,'it'),(2,'it');ERROR 1062 (23000): Duplicate entry 'it' for key 'name'#例子2：create table department(id int unique,name char(10) unique);insert into department values(1,'it'),(2,'sale');#第二种创建unique的方式create table department(id int,name char(10) ,unique(id),unique(name));insert into department values(1,'it'),(2,'sale');

　　联合唯一:

# 创建services表mysql> create table services(-> id int,-> ip char(15),-> port int,-> unique(id),-> unique(ip,port)-> );Query OK, 0 rows affected (0.05 sec)mysql> desc services;+-------+----------+------+-----+---------+-------+| Field | Type  | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id| int(11)| YES| UNI  | NULL| || ip| char(15) | YES| MUL  | NULL| || port| int(11) | YES|  | NULL| |+-------+----------+------+-----+---------+-------+rows in set (0.01 sec)#联合唯一，只要两列记录，有一列不同，既符合联合唯一的约束mysql> insert into services values-> (1,'192,168,11,23',80),-> (2,'192,168,11,23',81),-> (3,'192,168,11,25',80);Query OK, 3 rows affected (0.01 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from services;+------+---------------+------+| id| ip| port |+------+---------------+------+|1 | 192,168,11,23 |80 ||2 | 192,168,11,23 |81 ||3 | 192,168,11,25 |80 |+------+---------------+------+rows in set (0.00 sec)mysql> insert into services values (4,'192,168,11,23',80);ERROR 1062 (23000): Duplicate entry '192,168,11,23-80' for key 'ip'

　　4.primary key

　　一个表中可以：

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

　　约束：等价于 not null unique,字段的值不为空且唯一

　　存储引擎默认是（innodb）:对于innodb存储引擎来说，一张表必须有一个主键。

　　单列主键:

# 创建t14表，为id字段设置主键，唯一的不同的记录create table t14(id int primary key,name char(16));insert into t14 values(1,'xiaoma'),(2,'xiaohong');mysql> insert into t14 values(2,'wxxx');ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'#not null + unique的化学反应，相当于给id设置primary keycreate table t15(id int not null unique,name char(16));mysql> create table t15(-> id int not null unique,-> name char(16)-> );Query OK, 0 rows affected (0.01 sec)mysql> desc t15;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| id| int(11)  | NO | PRI | NULL| || name| char(16) | YES  | | NULL| |+-------+----------+------+-----+---------+-------+rows in set (0.02 sec)

　　复合主键:

create table t16(ip char(15),port int,primary key(ip,port));insert into t16 values('1.1.1.2',80),('1.1.1.2',81);验证复合主键的使用

　　5.auto_increment

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

　　验证1:(重点)

# 创建studentcreate 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||+-------+-----------------------+------+-----+---------+----------------+rows in set (0.17 sec)#插入记录mysql>  insert into student(name) values ('老白'),('小白');Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Waings: 0mysql> select * from student;+----+--------+------+| id | name| sex  |+----+--------+------+|  1 | 老白| male ||  2 | 小白| male |+----+--------+------+rows in set (0.00 sec)不指定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.01 sec)mysql> select * from student;+----+--------+--------+| id | name| sex|+----+--------+--------+|  1 | 老白| male||  2 | 小白| male||  4 | asb| female ||  7 | wsb| female |+----+--------+--------+rows in set (0.00 sec)# 再次插入一条不指定id的记录，会在之前的最后一条记录继续增长mysql>  insert into student(name) values ('大白');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+--------+--------+| id | name| sex|+----+--------+--------+|  1 | 老白| male||  2 | 小白| male||  4 | asb| female ||  7 | wsb| female ||  8 | 大白| male|+----+--------+--------+rows in set (0.00 sec)也可以指定id

mysql> delete from student;Query OK, 5 rows affected (0.00 sec)mysql> select * from student;Empty set (0.00 sec)mysql> select * from student;Empty set (0.00 sec)mysql> insert into student(name) values('ysb');Query OK, 1 row affected (0.01 sec)mysql> select * from student;+----+------+------+| id | name | sex  |+----+------+------+|  9 | ysb  | male |+----+------+------+row in set (0.00 sec)#应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它mysql> truncate student;Query OK, 0 rows affected (0.03 sec)mysql>  insert into student(name) values('xiaobai');Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+---------+------+| id | name| sex  |+----+---------+------+|  1 | xiaobai | male |+----+---------+------+row in set (0.00 sec)mysql>对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长

　　了解:

查看可用的 开头auto_inc的词mysql> show variables like 'auto_inc%';+--------------------------+-------+| Variable_name| Value |+--------------------------+-------+| auto_increment_increment | 1 || auto_increment_offset| 1 |+--------------------------+-------+rows in set (0.02 sec)# 步长auto_increment_increment,默认为1# 起始的偏移量auto_increment_offset, 默认是1 # 设置步长 为会话设置，只在本次连接中有效 set session auto_increment_increment=5; #全局设置步长 都有效。 set global auto_increment_increment=5; # 设置起始偏移量 set global  auto_increment_offset=3;#强调：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的值会被忽略 # 设置完起始偏移量和步长之后，再次执行show variables like'auto_inc%';发现跟之前一样，必须先exit,再登录才有效。mysql> show variables like'auto_inc%';+--------------------------+-------+| Variable_name| Value |+--------------------------+-------+| auto_increment_increment | 5 || auto_increment_offset| 3 |+--------------------------+-------+rows in set (0.00 sec)#因为之前有一条记录id=1mysql> select * from student;+----+---------+------+| id | name| sex  |+----+---------+------+|  1 | xiaobai | male |+----+---------+------+row in set (0.00 sec)# 下次插入的时候，从起始位置3开始，每次插入记录id+5mysql> insert into student(name) values('ma1'),('ma2'),('ma3');Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Waings: 0mysql> select * from student;+----+---------+------+| id | name| sex  |+----+---------+------+|  1 | xiaobai | male ||  3 | ma1 | male ||  8 | ma2 | male || 13 | ma3 | male |+----+---------+------+auto_increment_increment和 auto_increment_offset

　　清空表区分delete和truncate的区别：

　　delete from t1; #如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。

　　truncate table t1;数据量大，删除速度比上一条快，且直接从零开始。

 　　6.foreign key

 　　foreign key的意思是将两个表 关联起来,通过一个表能知道另一个表的某些信息

　　此时有两张表，一张是employee表,简称emp表（关联表，也就从表）。一张是department表，简称dep表（被关联表，也叫主表）。

#1.创建表时先创建被关联表，再创建关联表# 先创建被关联表（dep表）create table dep(id int primary key,name varchar(20) not null,descripe varchar(20) not null);#再创建关联表（emp表）create table emp(id int primary key,name varchar(20) not null,age int not null,dep_id int,constraint fk_dep foreign key(dep_id) references dep(id) );#2.插入记录时，先往被关联表中插入记录，再往关联表中插入记录insert into dep values(1,'IT','IT技术有限部门'),(2,'销售部','销售部门'),(3,'财务部','花钱太多部门');insert into emp values(1,'zhangsan',18,1),(2,'lisi',19,1),(3,'egon',20,2),(4,'yuanhao',40,3),(5,'alex',18,2);3.删除表#按道理来说，删除了部门表中的某个部门，员工表的有关联的记录相继删除。mysql> delete from dep where id=3;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db5`.`emp`, CONSTRAINT `fk_name` FOREIGN KEY (`dep_id`) REFERENCES `dep` (`id`))#但是先删除员工表的记录之后，再删除当前部门就没有任何问题mysql> delete from emp where dep =3;Query OK, 1 row affected (0.00 sec)mysql> select * from emp;+----+----------+-----+--------+| id | name | age | dep_id |+----+----------+-----+--------+|  1 | zhangsan |  18 |  1 ||  2 | lisi |  18 |  1 ||  3 | egon |  20 |  2 ||  5 | alex |  18 |  2 |+----+----------+-----+--------+rows in set (0.00 sec)mysql> delete from dep where id=3;Query OK, 1 row affected (0.00 sec)mysql> select * from dep;+----+-----------+----------------------+| id | name  | descripe |+----+-----------+----------------------+|  1 | IT| IT技术有限部门||  2 | 销售部| 销售部门 |+----+-----------+----------------------+rows in set (0.00 sec)

　　创建完后你会发现这两张表很不好删除,删除一个 需要另一个里面没有调用的,于是又来了个

　　on delete cascade #同步删除
　　on update cascade #同步更新

create table emp(id int primary key,name varchar(20) not null,age int not null,dep_id int,constraint fk_dep foreign key(dep_id) references dep(id) on delete cascade #同步删除on update cascade #同步更新);

#再去删被关联表(dep)的记录，关联表(emp)中的记录也跟着删除mysql> delete from dep where id=3;Query OK, 1 row affected (0.00 sec)mysql> select * from dep;+----+-----------+----------------------+| id | name  | descripe |+----+-----------+----------------------+|  1 | IT| IT技术有限部门||  2 | 销售部| 销售部门 |+----+-----------+----------------------+rows in set (0.00 sec)mysql> select * from emp;+----+----------+-----+--------+| id | name | age | dep_id |+----+----------+-----+--------+|  1 | zhangsan |  18 |  1 ||  2 | lisi |  19 |  1 ||  3 | egon |  20 |  2 ||  5 | alex |  18 |  2 |+----+----------+-----+--------+rows in set (0.00 sec)#再去更改被关联表(dep)的记录，关联表(emp)中的记录也跟着更改mysql> update dep set id=222 where id=2;Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Waings: 0# 赶紧去查看一下两张表是否都被删除了，是否都被更改了mysql> select * from dep;+-----+-----------+----------------------+| id  | name  | descripe |+-----+-----------+----------------------+|1 | IT| IT技术有限部门|| 222 | 销售部| 销售部门 |+-----+-----------+----------------------+rows in set (0.00 sec)mysql> select * from emp;+----+----------+-----+--------+| id | name | age | dep_id |+----+----------+-----+--------+|  1 | zhangsan |  18 |  1 ||  2 | lisi |  19 |  1 ||  3 | egon |  20 |222 ||  5 | alex |  18 |222 |+----+----------+-----+--------+rows in set (0.00 sec)