数据库之表操作
mysql 中的存储引擎
InnoDB MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。 MyISAM MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。 Memory 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失 BLACKHOLE 黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。 CSV 它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。 NDB (又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意：NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。目前能够支持 MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。 Merge 允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。 Federated 提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。 Example 这种存储引擎用以保存阐明如何开始写新的存储引擎的 MySql 源码的例子。它主要针对于有兴趣的开发人员。这种存储引擎就是一个啥事也不做的 "存根"。你可以使用这种引擎创建表，但是你无法向其保存任何数据，也无法从它们检索任何索引。
常用的存储引擎及适用场景
InnoDB
支持事务、行级锁、外键
事务：事务具有原子性，事务的结束有两种，一种是操作全部执行成功，事务提交，另一种是其中一个操作失败，发生回滚，撤销事务的所有操作。
行级锁：同一时间同一行内只能一个人进行修改操作，不同行的记录可以被同时修改。
外键：一个表的外键是另一张表的主键，可以有重复，可以是空值，用来和其他表建立联系，可以有多个外键
MyIsam
表级锁，查询多修改少的时候用，同一张表内的数据不能被同时修改
Memory
数据全部存储在内存中，存取速度快，数据量小，并对服务器的内存有要求，断电即消失
blackhole 黑洞
放进去的所有数据都不存储
MySQL架构总共四层，在上图中以虚线作为划分。 首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。 第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。 第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。 第四层包含了文件系统，所有的表结构和数据以及用户操作的日志最终还是以文件的形式存储在硬盘上。
1 #创建表 2 mysql> create table staff_info (id int, 3 name varchar(20), 4 age int,sex enum('female','male'), 5 phone char(11), 6 job varchar(20)); 7 #Query OK, 0 rows affected (0.20 sec) 8 9 #查看表结构 10 mysql> desc staff_info; 11 #+-------+-----------------------+------+-----+---------+-------+ 12 | Field | Type | Null | Key | Default | Extra | 13 +-------+-----------------------+------+-----+---------+-------+ 14 | id | int(11) | YES | | NULL | | 15 | name | varchar(20) | YES | | NULL | | 16 | age | int(11) | YES | | NULL | | 17 | sex | enum('female','male') | YES | | NULL | | 18 | phone | char(11) | YES | | NULL | | 19 | job | varchar(20) | YES | | NULL | | 20 #+-------+-----------------------+------+-----+---------+-------+ 21 22 # 插入数据 23 # 指定列名插入数据 24 mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'wang',66,'female','15666652731','IT'); 25 # Query OK, 1 row affected (0.02 sec) 26 27 #插入单行数据 28 mysql> insert into staff_info values (1,'wang',83,'female','15666652731','IT'); 29 #Query OK, 1 row affected (0.02 sec) 30 31 #插入多行数据 32 mysql> insert into staff_info values 33 -> (2,'zhao',26,'male','13304320533','Tearcher'), 34 -> (3,'qian',25,'male','13332353222','IT'), 35 -> (4,'sun',40,'male','13332353333','IT'); 36 37 #查询数据 38 #查看所有列的数据 39 mysql> select * from staff_info; 40 #+------+----------+------+--------+-------------+----------+ 41 | id | name | age | sex | phone | job | 42 +------+----------+------+--------+-------------+----------+ 43 | 1 | wang | 83 | female | 13651054608 | IT | 44 | 2 | zhao | 26 | male | 13304320533 | Tearcher | 45 | 3 | qian | 25 | male | 13332353222 | IT | 46 | 4 | sun | 40 | male | 13332353333 | IT | 47 #+------+----------+------+--------+-------------+----------+ 48 49 #查看指定列的数据 50 mysql> select name,age from staff_info; 51 #+----------+------+ 52 | name | age | 53 +----------+------+ 54 | wang | 83 | 55 | zhao | 26 | 56 | qian | 25 | 57 | sun | 40 | 58 +----------+------+ 59 #4 rows in set (0.00 sec)
数值类型：
#常用 #int 整数 #float 小数 #decimal 精准 #整数部分 mysql> create table t5 (id1 int(4),id2 int); #Query OK, 0 rows affected (0.16 sec) mysql> desc t5; #+-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(4) | YES | | NULL | | | id2 | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ #2 rows in set (0.01 sec) mysql> insert into t5 values (123,123); #Query OK, 1 row affected (0.03 sec) mysql> select * from t5; #+------+------+ | id1 | id2 | +------+------+ | 123 | 123 | +------+------+ #1 row in set (0.00 sec) mysql> insert into t5 values (12345,12345); #Query OK, 1 row affected (0.03 sec) mysql> select * from t5; #+-------+-------+ | id1 | id2 | +-------+-------+ | 123 | 123 | | 12345 | 12345 | +-------+-------+ #2 rows in set (0.00 sec) mysql> insert into t5 values (123,2147483648); #Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t5; #+-------+------------+ | id1 | id2 | +-------+------------+ | 123 | 123 | | 12345 | 12345 | | 123 | 2147483647 | +-------+------------+ #3 rows in set (0.00 sec) #范围测试 有符号和无符号 mysql> create table t6 (id1 int(4),id2 int unsigned); #Query OK, 0 rows affected (0.25 sec) mysql> insert into t6 values (123,2147483648); #Query OK, 1 row affected (0.03 sec) mysql> select * from t6; #+------+------------+ | id1 | id2 | +------+------------+ | 123 | 2147483648 | +------+------------+ #1 row in set (0.00 sec) #小数 #长度约束测试 mysql> create table t7 (f float(10,3),d double(10,3),d2 decimal(10,3)); #Query OK, 0 rows affected (0.20 sec) mysql> insert into t7 values (1.23456789,2.34567,3.56789); #Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t7; #+-------+-------+-------+ | f | d | d2 | +-------+-------+-------+ | 1.235 | 2.346 | 3.568 | +-------+-------+-------+ #1 row in set (0.00 sec) 精度测试 mysql> create table t8 (f float(255,30),d double(255,30),d2 decimal(65,30)); #Query OK, 0 rows affected (0.18 sec) mysql> insert into t8 values (1.11111111111111111111111111111111111111111111111111111111111,1.11111111111111111111111111111111111111111111111111111111111,1.11111111111111111111111111111111111111111111111111111111111); #Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t8; #+----------------------------------+----------------------------------+----------------------------------+ | f | d | d2 | +----------------------------------+----------------------------------+----------------------------------+ | 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111111111111111 | #+-----------------------------
时间类型
#常用 #date 描述年月日 #datetime 描述年月日时分秒 #timestamp字段默认不为空 mysql> create table t9 (d date,t time,y year,dt datetime,ts timestamp); #Query OK, 0 rows affected (0.18 sec) mysql> desc t9; #+-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | y | year(4) | YES | | NULL | | | dt | datetime | YES | | NULL | | | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ #5 rows in set (0.01 sec) mysql> insert into t9 values (null,null,null,null,null); #Query OK, 1 row affected (0.03 sec) mysql> select * from t9; #+------+------+------+------+---------------------+ | d | t | y | dt | ts | +------+------+------+------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | +------+------+------+------+---------------------+ #1 row in set (0.00 sec) mysql> insert into t9 values(now(),now(),now(),now(),now()); #Query OK, 1 row affected, 1 warning (0.03 sec) #每种数据类型表示的时间格式 mysql> select * from t9; #+------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | +------------+----------+------+---------------------+---------------------+ #2 rows in set (0.00 sec) #datetime 和 timestamp的范围控制 mysql> insert into t9 (dt) values (10010101000000); #Query OK, 1 row affected (0.03 sec) mysql> select * from t9; #+------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 11:30:33 | +------------+----------+------+---------------------+---------------------+ #3 rows in set (0.00 sec) mysql> mysql> insert into t9 (ts) values (10010101000000); #Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from t9; #+------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 11:30:33 | | NULL | NULL | NULL | NULL | 0000-00-00 00:00:00 | +------------+----------+------+---------------------+---------------------+ #4 rows in set (0.00 sec)
CHAR 0-255字节 定长字符串 定长 浪费磁盘 存取速度非常快 VARCHAR 0-65535 字节 变长字符串 变长 节省磁盘空间 存取速度相对慢 char(5) ' abc' 5 'abcde' 5 这一列数据的长度变化小 手机号 身份证号 学号 频繁存取、对效率要求高 短数据 varchar(5) '3abc' 4 '5abcde' 6 这一列的数据长度变化大 name 描述信息 对效率要求相对小 相对长 mysql> create table t10 (c char(5),vc varchar(5)); #Query OK, 0 rows affected (0.14 sec) mysql> desc t10; #+-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(5) | YES | | NULL | | | vc | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ #2 rows in set (0.01 sec) 插入ab，实际上存储中c占用5个字节，vc只占用3个字节，但是我们查询的额时候感知不到 因为char类型在查询的时候会默认去掉所有补全的空格 mysql> insert into t10 values ('ab','ab'); #Query OK, 1 row affected (0.03 sec) mysql> select * from t10; #+------+------+ | c | vc | +------+------+ | ab | ab | +------+------+ #1 row in set (0.00 sec) 插入的数据超过了约束的范围，会截断数据 mysql> insert into t10 values ('abcdef','abcdef'); #Query OK, 1 row affected, 2 warnings (0.02 sec) mysql> select * from t10; #+-------+-------+ | c | vc | +-------+-------+ | ab | ab | | abcde | abcde | +-------+-------+ #2 rows in set (0.00 sec) 插入带有空格的数据，查询的时候能看到varchar字段是带空格显示的，char字段仍然在显示的时候去掉了空格 mysql> insert into t10 values ('ab ','ab '); #Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from t10; #+-------+-------+ | c | vc | +-------+-------+ | ab | ab | | abcde | abcde | | ab | ab | +-------+-------+ #3 rows in set (0.00 sec) mysql> select concat(c,'+'),concat(vc,'+') from t10; #+---------------+----------------+ | concat(c,'+') | concat(vc,'+') | +---------------+----------------+ | ab+ | ab+ | | abcde+ | abcde+ | | ab+ | ab + | +---------------+----------------+ #3 rows in set (0.01 sec)
1 枚举 enum 单选 2 集合 set 多选 3 4 5 mysql> create table t11 (name varchar(20),sex enum('male','female'),hobby set('抽烟','喝酒','烫头','翻车')); 6 #Query OK, 0 rows affected (0.15 sec) 7 8 mysql> desc t11; 9 #+-------+------------------------------------------+------+-----+---------+-------+ 10 | Field | Type | Null | Key | Default | Extra | 11 +-------+------------------------------------------+------+-----+---------+-------+ 12 | name | varchar(20) | YES | | NULL | | 13 | sex | enum('male','female') | YES | | NULL | | 14 | hobby | set('抽烟','喝酒','烫头','翻车') | YES | | NULL | | 15 +-------+------------------------------------------+------+-----+---------+-------+ 16 #3 rows in set (0.01 sec) 17 18 如果插入的数据不在枚举或者集合范围内，数据无法插入表 19 mysql> insert into t11 values ('wang','aaaa','bbbb'); 20 Query OK, 1 row affected, 2 warnings (0.02 sec) 21 22 mysql> select * from t11; 23 +------+------+-------+ 24 | name | sex | hobby | 25 +------+------+-------+ 26 | wang | | | 27 +------+------+-------+ 28 1 row in set (0.00 sec) 29 30 向集合中插入数据，自动去重 31 mysql> insert into t11 values ('wang','female','抽烟,抽烟,烫头'); 32 Query OK, 1 row affected (0.03 sec) 33 34 mysql> select * from t11; 35 +------+--------+---------------+ 36 | name | sex | hobby | 37 +------+--------+---------------+ 38 | wang | | | 39 | wang | female | 抽烟,烫头 | 40 +------+--------+---------------+ 41 2 rows in set (0.00 sec) 42 43 向集合中插入多条数据，不存在的项无法插入 44 mysql> insert into t11 values ('wang','female','抽烟,抽烟,烫头,打豆豆'); 45 Query OK, 1 row affected, 1 warning (0.03 sec) 46 47 mysql> select * from t11; 48 +------+--------+---------------+ 49 | name | sex | hobby | 50 +------+--------+---------------+ 51 | wang| | | 52 | wang| female | 抽烟,烫头 | 53 | wang| female | 抽烟,烫头 | 54 +------+--------+---------------+ 55 3 rows in set (0.00 sec)
语法： 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 字段名; 6.删除表 DROP TABLE 表名;
# not null 非空 # default 默认值 # 如果不输入就是用默认的实质 # unique 唯一 : 唯一可以有一个空 # auto_increment 只有数字类型才能设置自增 # 联合唯一 # 就是给一个以上的字段设置 唯一约束 # primary key 主键 唯一+非空 加速查询 每张表只能有一个主键 # 当我们以非空并且唯一的约束来创建一个表的时候， # 如果我们没有指定主键，那么第一个非空唯一的字段将会被设置成主键 # 联合主键 # 就是给一个以上的字段设置 唯一非空约束 # # foreign key 外键 Innodb # 外表中的一个唯一字段 # on delete cascade # on update cascade # # course : cid,cname,cprice # student : sid,sname,age,course_id # # 如果我们没有指定主键，那么第一个非空唯一的字段将会被设置成主键 # mysql> create table t13 (id int unique not null); # Query OK, 0 rows affected (0.15 sec) # # mysql> desc t13;de # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id | int(11) | NO | PRI | NULL | | # +-------+---------+------+-----+---------+-------+ # 1 row in set (0.01 sec) # # # 非空 + 唯一约束不能插入空值 # mysql> insert into t13 values (null); # ERROR 1048 (23000): Column 'id' cannot be null # mysql> create table t14 (id1 int unique not null,id2 int unique not null); # Query OK, 0 rows affected (0.17 sec) # # mysql> desc t14; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id1 | int(11) | NO | PRI | NULL | | # | id2 | int(11) | NO | UNI | NULL | | # +-------+---------+------+-----+---------+-------+ # 2 rows in set (0.01 sec) # # # 主键不能为空值 # mysql> insert into t14 (id1) values (1); # ERROR 1364 (HY000): Field 'id2' doesn't have a default value # # 指定主键之后 其他的非空 + 唯一约束都不会再成为主键 # mysql> create table t15 (id1 int unique not null,id2 int primary key); # Query OK, 0 rows affected (0.23 sec) # # mysql> desc t15; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id1 | int(11) | NO | UNI | NULL | | # | id2 | int(11) | NO | PRI | NULL | | # +-------+---------+------+-----+---------+-------+ # 2 rows in set (0.01 sec) # # mysql> insert into t15 values (1,2); # Query OK, 1 row affected (0.03 sec) # # mysql> insert into t15 values (2,3); # Query OK, 1 row affected (0.02 sec) # # mysql> insert into t15 values (4,4); # Query OK, 1 row affected (0.03 sec) # # mysql> insert into t15 values (4,5); # ERROR 1062 (23000): Duplicate entry '4' for key 'id1' # mysql> # # 设置联合主键 # mysql> create table t16 (id int,ip char(15),port int ,primary key(ip,port)); # Query OK, 0 rows affected (0.15 sec) # # mysql> desc t16; # +-------+----------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+----------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | ip | char(15) | NO | PRI | | | # | port | int(11) | NO | PRI | 0 | | # +-------+----------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # # mysql> insert into t16 values (1,'192.168.0.1','9000'); # Query OK, 1 row affected (0.02 sec) # # mysql> insert into t16 values (1,'192.168.0.1','9001'); # Query OK, 1 row affected (0.03 sec) # # mysql> insert into t16 values (1,'192.168.0.2','9000'); # Query OK, 1 row affected (0.03 sec) # # mysql> select * from t16 # -> ; # +------+-------------+------+ # | id | ip | port | # +------+-------------+------+ # | 1 | 192.168.0.1 | 9000 | # | 1 | 192.168.0.1 | 9001 | # | 1 | 192.168.0.2 | 9000 | # +------+-------------+------+ # 3 rows in set (0.00 sec) # # mysql> insert into t16 values (2,'192.168.0.2','9000'); # ERROR 1062 (23000): Duplicate entry '192.168.0.2-9000' for key 'PRIMARY' # mysql> # # 设置ip和port两个字段联合唯一 # mysql> create table t17 (id int primary key,ip char(15) not null ,port int not null, unique(ip,port)); # Query OK, 0 rows affected (0.17 sec) # # mysql> desc t17; # +-------+----------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+----------+------+-----+---------+-------+ # | id | int(11) | NO | PRI | NULL | | # | ip | char(15) | NO | MUL | NULL | | # | port | int(11) | NO | | NULL | | # +-------+----------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # # mysql> insert into t17 values (1,'192.168.0.1',9000); # Query OK, 1 row affected (0.03 sec) # # mysql> insert into t17 values (2,'192.168.0.1',9000); # ERROR 1062 (23000): Duplicate entry '192.168.0.1-9000' for key 'ip' # mysql> insert into t17 values (2,'192.168.0.1',9001); # Query OK, 1 row affected (0.03 sec) # # mysql> insert into t17 values (3,'192.168.0.2',9001); # Query OK, 1 row affected (0.03 sec) # # # # default 设置默认值 # mysql> create table t18 (id int primary key auto_increment,name varchar(20) not null,sex enum('male','female') # -> not null default 'male'); # Query OK, 0 rows affected (0.15 sec) # # mysql> desc t18; # +-------+-----------------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-------+-----------------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | name | varchar(20) | NO | | NULL | | # | sex | enum('male','female') | NO | | male | | # +-------+-----------------------+------+-----+---------+----------------+ # 3 rows in set (0.01 sec) # # mysql> insert into t18 (name) values ('alex'); # Query OK, 1 row affected (0.03 sec) # # mysql> select * from t18; # +----+------+------+ # | id | name | sex | # +----+------+------+ # | 1 | alex | male | # +----+------+------+ # 1 row in set (0.00 sec) # # mysql> insert into t18 (name) values ('egon'),('yuan'),('nazha'); # Query OK, 3 rows affected (0.02 sec) # Records: 3 Duplicates: 0 Warnings: 0 # # mysql> select * from t18; # +----+-------+------+ # | id | name | sex | # +----+-------+------+ # | 1 | alex | male | # | 2 | egon | male | # | 3 | yuan | male | # +----+-------+------+ # 4 rows in set (0.00 sec) # # mysql> insert into t18 (name,sex) values ('yanglan','female'); # Query OK, 1 row affected (0.03 sec) # # mysql> select * from t18; # +----+---------+--------+ # | id | name | sex | # +----+---------+--------+ # | 1 | alex | male | # | 2 | egon | male | # | 3 | yuan | male | # | 4 | nazha | male | # | 5 | yanglan | female | # +----+---------+--------+ # 5 rows in set (0.00 sec) # 外键 只有另一个表中设置了unique的字段才能作为本表的外键 # mysql> create table t20 (id int,age int,t19_id int,foreign key(t19_id) references t19(id)); # ERROR 1215 (HY000): Cannot add foreign key constraint # mysql> create table t21 (id int unique ,name varchar(20)); # Query OK, 0 rows affected (0.17 sec) # # mysql> create table t20 (id int,age int,t_id int,foreign key(t_id) references t21(id)); # Query OK, 0 rows affected (0.21 sec) # # mysql> insert into t21 values (1,'python'),(2,'linux'); # Query OK, 2 rows affected (0.02 sec) # 如果一个表找中的字段作为外键对另一个表提供服务，那么默认不能直接删除外表中正在使用的数据 # mysql> insert into t20 values (1,18,1); # Query OK, 1 row affected (0.02 sec) # # mysql> insert into t20 values (2,38,2); # Query OK, 1 row affected (0.02 sec) # # mysql> select * from t21; # +------+--------+ # | id | name | # +------+--------+ # | 1 | python | # | 2 | linux | # +------+--------+ # 2 rows in set (0.00 sec) # # mysql> delete from t21 where id=1; # ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db2`.`t20`, CONSTRAINT `t20_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `t21` (`id`)) # 外键 on delete cascade on update cascade # mysql> create table course (cid int primary key auto_increment,cname varchar(20) not null, # -> cprice int not null); # Query OK, 0 rows affected (0.16 sec) # # mysql> create table student (sid int primary key auto_increment, # -> sname varchar(20) not null, # -> age int not null, # -> course_id int, # -> foreign key(course_id) # -> references course(cid) # -> on delete cascade # -> on update cascade); # Query OK, 0 rows affected (0.17 sec) # # mysql> insert into course (cname,cprice) values ('python',19800),('linux',15800); # Query OK, 2 rows affected (0.03 sec) # Records: 2 Duplicates: 0 Warnings: 0 # # mysql> insert into student (sname,age,course_id) values ('yangzonghe',18,1),('hesihao',88,2); # Query OK, 2 rows affected (0.03 sec) # Records: 2 Duplicates: 0 Warnings: 0 # # mysql> delete from course where cid = 1; # Query OK, 1 row affected (0.03 sec) # # mysql> select * from student; # +-----+---------+-----+-----------+ # | sid | sname | age | course_id | # +-----+---------+-----+-----------+ # | 2 | hesihao | 88 | 2 | # +-----+---------+-----+-----------+ # 1 row in set (0.00 sec) # # mysql> update course set cid = 1 where cid = 2; # Query OK, 1 row affected (0.03 sec) # Rows matched: 1 Changed: 1 Warnings: 0 # # mysql> select * from course; # +-----+-------+--------+ # | cid | cname | cprice | # +-----+-------+--------+ # | 1 | linux | 15800 | # +-----+-------+--------+ # 1 row in set (0.00 sec) # # mysql> select * from student; # +-----+---------+-----+-----------+ # | sid | sname | age | course_id | # +-----+---------+-----+-----------+ # | 2 | hesihao | 88 | 1 | # +-----+---------+-----+-----------+ # 1 row in set (0.00 sec)
#############################
################################
# 存储引擎
# InnoDB\MyISAM\blackhole\memory
# InnoDB 行级锁\支持事务\外键 保持事务的完整性 在修改数据的效率比较高
# MyISAM 表级锁 查询速度快 但是对插入和修改数据效率差
# memory 数据存在内存里 处理数据的速度快 但是对内存要求高 且重启服务数据消失
# blackhole 不存数据 有一个日志记录着插入的数据，利用日志分流数据
# 数据类型
# 数值
# tinyint smallint mediumint int bigint
# int 2**32-1 2**31-1 10
# 时间
# date time datetime year timestamp
# date / datetime
# timestamp / datetime :
# 范围不同，timestamp范围小，timestamp默认不为空，时间默认是当前时间
# 字符串
# char ：定长存储 查询速度快
# varchar ：不定长存储 查询相对慢
# 枚举和集合
# enum 单选
# set 去重+多选
# 约束
# not null
# default
# unique
# auto_increment
# unique (字段1，字段2，...)
# primary key
# primary key(字段1，字段2，...)
# foreign key 本表中的字段
# references 外表名(外表的unique字段)
# on delete cascade
# on update cascade
# 表的增删改查
# drop table 表名
# create table 表名 (
# 列名 数据类型【(长度) 其他约束条件】，
# 列名 数据类型【(长度) 其他约束条件】，
# 列名 数据类型【(长度) 其他约束条件】，
# ...
# )
# alter table 表名
# rename 新表名
# drop 列名
# add 新列名 数据类型【(长度) 其他约束条件】 【after (字段名)/first】
# modify 列名 (新)数据类型【((新)长度) (新)其他约束条件】 【after (字段名)/first】
# change 列名 新列名 (新)数据类型【((新)长度) (新)其他约束条件】 【after (字段名)/first】
# 查
# desc 表名；
# show create table 表名 \G;
清风深知杨柳意，啤酒龙虾难相聚。
浙公网安备 33010602011771号