MySQL笔记

数据库操作

创建数据库

• 当数据库不存在时就创建
• if not exists
• character set utf8 创建时设置数据库字符集或charset utf8;

create database if not exists 数据库名 character set utf8;

• 修改数据库字符集

alter database 数据库名 character set utf8mb4;

删除数据库

• 当数据库存在时就删除

drop database if exists 数据库名;

表的操作

创建表

主外键的声明

创建表的同时设置主键与外键约束，
声明方式一：直接在字段后面添加主键(PRIMARY KEY)
声明方式二：别处定义 PRIMARY KEY ( id ),
auto_increment ：自动递增
外键的约束模式：
$restrict$: 严格模式(默认的)，父表不能删除或更新一个已经被子表数据引用的记录
$cascade$: 级联模式，父表的操作，对应子表关联的数据也跟着操作(被改变)。
$setnull$： 置空模式，父表被操作之后，子表对应的外键字段被置空。

通常情况下，合理的模式应该是这样的：删除父表中的数据，子表置空(set null)，更新父表的时候，子表做级联操作(cascade)。

CREATE TABLE if not exists chiled2 (
	id INT NOT NULL auto_increment,
	pid INT ( 20 ),
	name VARCHAR ( 50 ),
	PRIMARY KEY ( id ),
	FOREIGN KEY ( pid ) REFERENCES parent ( id ) 
	ON DELETE SET NULL ON UPDATE CASCADE 
	) charset utf8;

上述外键的声明没有指定约束名，会有一个默认的约束名；下面指定约束名直接在foreign 前面加上CONSTRAINT 约束名

CONSTRAINT 约束名 FOREIGN KEY ( pid ) REFERENCES parent ( id ) ON DELETE SET NULL ON UPDATE CASCADE 

查看表的字段信息：

desc 表名;

查看表创建的所有信息包括约束名：

show create  table 表名;

执行该语句后会有如下结果

其中Create Table 的值如下(下面的 CONSTRAINT 后面的就是约束名，这里在创建表的时候没有指定约束名，所以是默认的约束名chiled2_ibfk_1)

CREATE TABLE `chiled2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pid` int DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  CONSTRAINT `chiled2_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `parent` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

上面的信息中有一个AUTO_INCREMENT=7 表示设置自动递增的字段在添加一条数据时该字段值为7（即使之前已经删除了一些数据），这就引发了设置auto_increment 重新计数

alter table test auto_increment=1 可以设置AUTO_INCREMENT 为任意值开始(大于表中存在的该字段的最大值才有效)；
truncate table test AUTO_INCREMENT 值重新开始计数，会彻底删除所有记录。

• 在建表之后添加主外键

添加主键约束：alter table 表名 add constraint 约束名 （形如：PK_表名） primary key 表名(主键字段);
添加外键约束：alter table 从表 add constraint 约束名（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除主键约束：alter table 表名 drop primary key 约束名 ;
删除外键约束：alter table 表名 drop foreign key 约束名（区分大小写）;

设置字段唯一性

• 建表同时在字段后面添加 unique 关键字
• 建表之后设置唯一性如下

ALTER TABLE table ADD UNIQUE (field);
ALTER TABLE table ADD UNIQUE (field1,field2);

truncate、delete、drop区别

truncate table 表名;
drop table 表名;
delete from 表名;

• truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
• truncate只能作用于表；delete，drop可作用于表、视图等。
• truncate、delete会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除整个表。
• truncate会重置表的自增值；delete不会。 truncate不会激活与表有关的删除触发器；delete可以。
• truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。

字段值后面约束not null default

表示该字段可以不给值(使用默认的值),但是给值的话不能给null