MySQL表操作
MySQL支持的数据类型
1. 数值类型
# MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。 # 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。 # MySQL支持的整数类型有TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。 # 对于小数的表示，MYSQL分为两种方式：浮点数和定点数。浮点数包括float(单精度)和double(双精度),而定点数只有decimal一种，在mysql中以字符串的形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。 # BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
# 对int类型的长度约束其实并没有实际的效果,比如设置宽度为5,约束只是在不足长度的时候的显示宽度是5,而不是你只能存储5位数 # 创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table t1 (id1 int,id2 int(5)); # 给t1中插入数据1，1 mysql> insert into t1 values (1,1); # 插入了比宽度更大的值，也不会报错 mysql> insert into t1 values (111111,111111); # 修改id1字段 给字段添加一个unsigned表示无符号 mysql> alter table t1 modify id1 int unsigned;
# 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table t2 (id1 float(5,2),id2 double(5,2),id3 decimal(5,2)); # 向表中插入1.234，会发现4都被截断了 mysql> insert into t2 values (1.234,1.234,1.234); # 向表中插入1.235发现数据虽然被截断，但是遵循了四舍五入的规则 mysql> insert into t2 values (1.235,1.235,1.235); # 当对小数位没有约束的时候，输入超长的小数，会发现float和double的区别 mysql> insert into t3 values (1.2355555555555555555,1.2355555555555555555,1.2355555555555555555555); mysql> select * from t3; +---------+--------------------+------+ | id1 | id2 | id3 | +---------+--------------------+------+ | 1.234 | 1.234 | 1 | | 1.23556 | 1.2355555555555555 | 1 | +---------+--------------------+------+ 2 rows in set (0.00 sec) # float能精确道小数点后5位 # double能多精确一些位数，但仍存在不精确的情况 # decimal默认是整数，通过设置最多可以表示到小数点后30位
2. 字符串类型
常用的字符串类型有：CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。
最常用的就是char和varchar类型了
char：
能表示的长度较小，浪费存储空间，读写速度快，是定长字符串，如果数据不足指定长度，会以空格填充；但是在显示时，会去掉所有空格显示，给用户造成视觉欺骗
varchar：
能表示的长度大，能节省存储空间，但是读写效率慢，是变长字符串；
越是长度固定，char比较省空间；越是长度不固定，varchar比较省空间
3. 时间类型
mysql> create table t4 (d date,t time,dt datetime); mysql> insert into t4 values (now(),now(),now()); mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2018-09-21 | 14:51:51 | 2018-09-21 14:51:51 | +------------+----------+---------------------+ 1 row in set (0.00 sec)
mysql> create table t5 (id1 timestamp); # 插入数据null，会自动插入当前时间的时间 mysql> insert into t5 values (null); mysql> select * from t5; +---------------------+ | id1 | +---------------------+ | 2018-09-21 14:56:50 | +---------------------+ 1 row in set (0.00 sec) # 添加一列 默认值是'0000-00-00 00:00:00' mysql> alter table t5 add id2 timestamp; # 手动修改新的列默认值为当前时间 mysql> alter table t5 modify id2 timestamp default current_timestamp; # 插入null mysql> insert into t5 values (null,null); mysql> select * from t5; +---------------------+---------------------+ | id1 | id2 | +---------------------+---------------------+ | 2018-09-21 14:56:50 | 0000-00-00 00:00:00 | | 2018-09-21 14:59:31 | 2018-09-21 14:59:31 | +---------------------+---------------------+ 2 rows in set (0.00 sec) # 插入如下数字：19700101080001； mysql> insert into t5 values (19700101080001); mysql> select * from t6; +---------------------+ | t1 | +---------------------+ | 1970-01-01 08:00:01 | +---------------------+ 1 row in set (0.00 sec) # timestamp时间的下限是19700101080001 mysql> insert into t6 values (19700101080000);就会报错 # timestamp时间的上限是2038-01-19 11:14:07 mysql> insert into t6 values ('2038-01-19 11:14:08');就会报错
mysql> create table t6 (y year); mysql> insert into t6 values (2019); mysql> select * from t6; +------+ | y | +------+ | 2018 | +------+ 1 row in set (0.00 sec)
mysql> create table t8 (dt datetime); Query OK, 0 rows affected (0.01 sec) mysql> insert into t8 values ('2018-9-26 12:20:10'); Query OK, 1 row affected (0.01 sec) mysql> insert into t8 values ('2018/9/26 12+20+10'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values ('20180926122010'); Query OK, 1 row affected (0.00 sec) mysql> insert into t8 values (20180926122010); Query OK, 1 row affected (0.00 sec) mysql> select * from t8; +---------------------+ | dt | +---------------------+ | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | | 2018-09-26 12:20:10 | +---------------------+ 4 rows in set (0.00 sec)
4. set和enum类型
ENUM，在中文中叫枚举类型，它的值范围需要在创建表时通过枚举方式显示；ENUM只允许从值集合中选取单个值，不能一次取多个值；set和enum非常相似，也是字符串对象，里面可以包含0-64个成员。根据成员的不同，存储上也有所不同。set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容将不允许注入，而对重复的值将进行自动去重。
mysql> create table t10 (name char(20),gender enum('female','male')); # 选择enum('female','male')中的一项作为gender的值，可以正常插入 mysql> insert into t10 values ('haha','male'); # 不能同时插入'male,female'两个值，也不能插入不属于'male,female'的值 mysql> insert into t10 values ('haha','male,female'); ERROR 1265 (01000): Data truncated for column 'gender' at row 1 mysql> create table t11 (name char(20),hobby set('抽烟','喝酒','烫头','翻车')); # 可以任意选择set('抽烟','喝酒','烫头','翻车')中的项，并自带去重功能 mysql> insert into t11 values ('qian','烫头,喝酒,烫头'); mysql> select * from t11; +------+---------------+ | name | hobby | +------+---------------+ | yuan | 喝酒,烫头 | +------+---------------+ 1 row in set (0.00 sec) # 不能选择不属于set('抽烟','喝酒','烫头','翻车')中的项， mysql> insert into t11 values ('alex','烫头,翻车,看妹子'); ERROR 1265 (01000): Data truncated for column 'hobby' at row 1
表的完整性约束
1. not null：not null表示不能为空；null表示可以为空
mysql> create table t7 (id int not null); mysql> select * from t7; mysql> desc t7; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) #不能向id列插入空元素。 mysql> insert into t7 values (null); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t7 values (1);
2. default：约束某一列不为空，如果这一列中经常有重复的内容，就需要我们频繁的插入重复内容，会给操作带来负担，我们可以选择用默认值；创建列时可以指定默认值，如果插入数据时未进行设置，就会自动添加默认值
mysql> create table t13 (id1 int not null,id2 int not null default 222); mysql> desc t13; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | | NULL | | | id2 | int(11) | NO | | 222 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.01 sec) # 只向id1字段添加值，会发现id2字段会使用默认值填充 mysql> insert into t13 (id1) values (111); mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 222 | +-----+-----+ 1 row in set (0.00 sec) # id1字段不能为空，所以不能单独向id2字段填充值； mysql> insert into t13 (id2) values (223); ERROR 1364 (HY000): Field 'id1' doesn't have a default value # 向id1，id2中分别填充数据，id2的填充数据会覆盖默认值 mysql> insert into t13 (id1,id2) values (112,223); mysql> select * from t13; +-----+-----+ | id1 | id2 | +-----+-----+ | 111 | 222 | | 112 | 223 | +-----+-----+ 2 rows in set (0.00 sec)
设置严格模式： 不支持对not null字段插入null值 不支持对自增长字段插入”值 不支持text字段有默认值 直接在mysql中生效(重启失效): mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; 配置文件添加(永久失效)： sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
3. UNIQUE
方法一： create table department1( id int, name varchar(20) unique, comment varchar(100) ); 方法二： create table department2( id int, name varchar(20), comment varchar(100), unique(name) );
mysql> create table t1(id int not null unique); mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
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'
4. PRIMARY KEY
主键为了保证表中的每一条数据的该字段都是表格中的唯一值，是用来独一无二地确认一个表格中的每一行数据。 它可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键 (Composite Key),也可以叫联合主键。主键可以在建置新表格时设定 (运用 CREATE TABLE 语句)，或是以改变现有的表格架构方式设定 (运用 ALTER TABLE)。
主键必须非空 + 唯一；可以是单一字段，也可以是多字段组合。
# 方法一：not null + unique mysql> create table department1( id int not null unique, #主键 name varchar(20) not null unique, comment varchar(100) ); # 方法二：某个字段后 + primary key mysql> create table department2( id int primary key, name varchar(20), comment varchar(100) ); # 方法三：所有字段后单独定义primary key mysql> create table department3( id int, name varchar(20), comment varchar(100), primary key(id)); # 方法四：给已建成的表天价主键约束 mysql> alter table department4 modify id int primary key;
create table service( ip varchar(15), port char(5), service_name varchar(10) not null, primary key(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'
5. AUTO_INCREMENT
约束某列为自增字段，被约束的字段同时必须被key约束
#不指定id，则自动增长 mysql> create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' ); #对于自增的字段，在用delete删除后，再插入值，该字段仍按照删除前的位置继续增长 mysql> delete from student; #应该用truncate清空表，比起delete一条一条地删除记录，truncate是直接清空表，在删除大表时用它 mysql> truncate student;
# 创建完表后，修改自增字段的起始值 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; # 此时给表中插入数据，id就会从3开始； # 在创建表时指定auto_increment的初始值，初始值的设置为表选项，应放到括号外 mysql> create table student(id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male')auto_increment=3;
6. FOREIGNKEY
假设我们要描述所有公司的员工，需要描述的属性有这些 ： 工号、姓名、部门；公司有3个部门，但是有1个亿的员工，那意味着部门这个字段需要重复存储，部门名字越长，越浪费；有一个解决方法：可以定义一个部门表 然后让员工信息表关联该表，如何关联，即foreign key
# 当表A的某个字段是唯一字段时，才可以在表B中设置外键关联表A mysql> create table A (dep_id int(4) unique, dep_name varchar(11)); # 创建表B，并关联表A create table B (s_id int,name varchar(20),dep_id int,foreign key(dep_id) references A(dep_id));
# 表的类型是必须是InnoDB存储引擎，且被关联的字段即references指定的另外一个表的字段，必须保证唯一 mysql> create table department(id int primary key, name varchar(20) not null)engine=innodb; #dpt_id外键，关联父表（department主键id），同步更新，同步删除 mysql> create table employee(id int primary key, name varchar(20) not null, dpt_id int, foreign key(dpt_id) references department(id) on delete cascade # 级连删除 on update cascade # 级连更新)engine=innodb; #先往父表department中插入记录 insert into department values(1,'教质部'),(2,'技术部'),(3,'人力资源部'); #再往子表employee中插入记录 insert into employee values(1,'yuan',1),(2,'nezha',2),(3,'egon',2),(4,'alex',2),(5,'wusir',3),(6,'李沁洋',3),(7,'皮卡丘',3),(8,'程咬金',3),(9,'程咬银',3); #删父表department，子表employee中对应的记录跟着删 mysql> delete from department where id=2; mysql> select * from employee; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 3 | | 6 | 李沁洋 | 3 | | 7 | 皮卡丘 | 3 | | 8 | 程咬金 | 3 | | 9 | 程咬银 | 3 | +----+-----------+--------+ 6 rows in set (0.00 sec) #更新父表department，子表employee中对应的记录跟着改 mysql> update department set id=2 where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee; +----+-----------+--------+ | id | name | dpt_id | +----+-----------+--------+ | 1 | yuan | 1 | | 5 | wusir | 2 | | 6 | 李沁洋 | 2 | | 7 | 皮卡丘 | 2 | | 8 | 程咬金 | 2 | | 9 | 程咬银 | 2 | +----+-----------+--------+ 6 rows in set (0.00 sec)
. cascade方式 在父表上update/delete记录时，同步update/delete掉子表的匹配记录 . set null方式 在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null . No action方式 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 . Restrict方式 同no action, 都是立即检查外键约束 . Set default方式 父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
建表
# 创建数据库 mysql> create database message; # 切换到数据库中 mysql> use message; # 创建表 mysql> create table 表名(字段1 类型(宽度约束) 约束条件, 字段2 类型(宽度约束) 约束条件,字段3 类型(宽度约束) 约束条件); # 同一张表中字段名不能重复,宽度和约束条件可选,字段名和类型必须填 # 插入数据 # 指定字段进行数据插入 mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'haha',83,'female',13651054608,'IT'); # 不指定,就要把数据和字段类型对应 mysql> insert into staff_info values(2,'heihei',26,'male',13304320533,'Teacher');
查看表结构
查看表结构有两种方式：describe tablename；这种方法和desc tablename效果相同；可以查看当前的表结构。虽然desc可以查看表结构，但输出的信息不够全面，为了得到更全面的表信息，有时候就需要查看创建表的SQL语句，使用show create table语法。除了可以看到表定义之外，还可以看到engine（存储引擎）和charset（字符集）等信息。（\G选项的含义是是的记录能够竖向排列，以便更好的显示内容较长的记录。）
mysql> describe staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> show create table staff_info\G; *************************** 1. row *************************** Table: staff_info Create Table: CREATE TABLE `staff_info` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, `phone` bigint(11) DEFAULT NULL, `job` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) ERROR: No query specified
修改表结构
# 修改表名 alter table 表名 rename 新表名； # 增加字段 alter table 表名 add 字段名 数据类型[完整性约束条件]； # 删除字段 alter table 表名 drop 字段名； # 修改字段 alter table 表名 modify 字段名 数据类型[完整性约束条件]； alter table 表名 change 旧字段名 新字段名 旧数据类型[约束条件]； alter table 表名 change 旧字段名 新字段名 新数据类型[约束条件]； # 修改字段排列顺序/增加时指定字段位置 alter table 表名 add 字段名 类型[条件] first；表的第一列 alter table 表名 add 字段名 类型[条件] after 字段；在已存的某个字段后 alter table 表名 change 字段名 旧字段名 新字段名 新数据类型[约束条件] first； alter table 表名 modify 字段名 数据类型[约束条件] after 字段名；
alter操作非空、唯一、主键、外键：
create table t1(id int unique;name char(10) not null); # 去掉null约束 alter table t1 modify name char(10) null; # 添加null约束 alter table t1 modify name char(10) not null;
create table t(id int unique,name char(10) not null); # 去掉unique约束 alter table t drop index id； # 添加unique约束 alter table t modify id int unique；
# 创建一个数据表table_test： mysql> create table table_test( `id` varchar(100) NOT NULL, `name` varchar(100) NOT NULL, PRIMARY KEY (`name`) ); # 发现主键设置错了,应该是id是主键,但如今表里已经有好多数据了,不能删除表再重建了，仅仅能在这基础上改动表结构。 # 先删除主键 alter table table_test drop primary key; # 再增加主键 alter table table_test add primary key(id); 注:在增加主键之前,必须先把反复的id删除掉。
# 创建press表 CREATE TABLE `press` (`id` int(11) NOT NULL,`name` char(10) DEFAULT NULL, PRIMARY KEY (`id`)) ； # 创建book表 CREATE TABLE `book` (`id` int(11) DEFAULT NULL,`bk_name` char(12) DEFAULT NULL, `press_id` int(11) NOT NULL,KEY `press_id` (`press_id`)) ； # 为book表添加外键 alter table book add constraint fk_id foreign key(press_id)references press(id); # 删除外键 alter table book drop foreign key fk_id;
删除表
drop table 表名；
多表结构的创建和分析
1. 找出两张表之间的关系
a. 先站在左表的角度找，看左表中是否有多条记录可以对应右表的一条记录，如果是，则证明左表有一个foreign key关联右表的一个字段，通常是id
b. 再从右表的角度去看，由表中是否有多条记录对应左表的一条记录，如果是，则证明右表有一个foreign key关联左表的一个字段，通常是id
2. 表和表之间的关系
一对多：a中的情况存在，则是：(左)多对一(右)；b中的情况存在，就是：(左)一对多(右)；
多对多：如果a和b的情况同时存在，则证明左右两张表是一个双向的一对多，即多对多，这时就需要新建一张表来专门存放二者的关系
一对一：如果a和b都不成立，而是左表的一条记录唯一对应右表的一条记录，就在左表foreign key关联右表的基础上，将左表的外键字段设置成unique即可，反之亦然。
3. 建立表之间的关系
# 出版社和书 # 出版社表 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); # 又或者是班级和学生，主机和机房，部门和员工等
# 出版社，作者，书籍 # 作者表 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) # 联合主键 ); # 又或者服务和机器，一个机器可以部署多个服务，一个服务也可以部署到多个机器上
# 用户和博客 # 用户表 create table user(id int primary key auto_increment, name varchar(20) not null); # 博客表：foreignkey + unique create table blogs(id int primary key auto_incremen, url varchar(20) not null, name_id int unique, foreign key(name_id) referenses user(id) on delete cascade on update cascade);
记录操作
MySQL的数据操作：DML；在MySQL管理软件中，可以通过SQL语句中的DML语言实现数据的操作，包括：使用INSERT插入数据，UPDATE实现数据更新，DELETE实现数据删除，SELECT实现数据查询。
insert插入数据
顺序插入数据：
insert into 表名 values (值1，值2，值3)；
指定字段插入数据：
insert into 表名(字段1，字段2，...字段n) values(值1，值2,...值n)；
插入多条数据：
insert into 表名 values
(值1，值2，...，值n),
(值1，值2，...，值n),
(值1，值2，...，值n);
插入查询结果：
insert into 表名 (字段1，字段2，...，字段n) select (字段1，字段2，...，字段n) from 表2 where ...；
update更新数据
update 表名 set 字段1=值1，字段2=值2 where 条件；
delete删除数据
delete from 表名 where 条件；
