MySQL知识汇总（第一篇）基本命令+常用类型与属性+外键+DML语言

前言：部分内容学习自B站视频（https://www.bilibili.com/video/BV1NJ411J79W?p=20），
部分内容参考自《MySQL必知必会》
本文用于回顾MySQL知识点

一、MySQL基本命令

1.连接：

进入mysql\bin下，打开cmd

mysql -h 服务器主机地址 -u 用户名 -p[用户密码]

或利用默认的服务器主机地址，localhost

2.退出：

exit;或\q;


3.寻求帮助：

? 命令关键词;

4.注释：

（1）--注释内容
或
（2）/*注释内容*/表示注释

跟在命令后面，具有解释说明的作用

二、基本的数据库操作命令

1.创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名;

具体例子：
CREATE DATABASE 数据库名 CHARACTER SET utf8 COLLATE utf8_general_ci;

2.删除数据库：DROP DATABASE [IF EXISTS] 数据库名;

3.显示所有数据库：SHOW DATABASES;

4.修改root用户密码：UPDATE user SET password = password(‘123456’) WHERE user = ‘root’;

5.刷新数据库：FLUSH PRIVILEGES;

6.打开某个数据库：USE 数据库名;

7.显示创建数据库的语句：SHOW CREATE DATABASE 数据库名;

二、数据库中常用的列类型

并不全，例如还有二进制数据类型，此处只列举了常用的，具体可参考《MySQL必知必会》

1.数值:

类型	描述	占用空间
tinyint	十分小的整数	1个字节
smallint	较小的整数	2个字节
mediumint	中等大小的整数	3个字节
int	标准的整数	4个字节
bigint	较大的整数	8个字节
float	浮点数	4个字节
double	浮点数	8个字节
decimal	字符串形式的浮点数，常用于金融计算	字符串形式

2.字符串：

类型	描述	占用空间
char	定长的字符串	0~255
varchar	可变长字符串	0~65535
tinytext	微型文本	2^8 - 1
text	文本	2^16 - 1

3.时间日期：

类型	描述	格式
date	日期格式	YYYY-MM-DD
time	时间格式	HH:MM:SS
datetime	日期时间格式	YYYY-MM-DD HH:MM:SS
timestamp	时间戳，1970.1.1到现在的毫秒数	数值
year	年份	数值

三、常用数据字段属性

属性名称	描述	作用
UnSigned	无符号的	不允许出现负数
ZeroFill	0填充的	不足位用0填充，如int(3),5则为005
Auto_InCrement	自增	在上一条记录的此属性值上加1，也可设置初始值和步长
NULL与NOT NULL	允许为空和不允许为空	能否不插入此属性的数据
DEFAULT	默认的	设置默认值

四、基本的表操作命令

1.创建表

CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
   PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

格式：

CREATE TABLE [IF NOT EXISTS] `表名` (
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释],
	...,
	`字段名` 列类型 [属性] [索引] [注释]
	PRIMARY KEY(`主键名`)
)[表的引擎][表的字符集][注释]

2.查看数据表的定义语句：SHOW CREATE TABLE 表名;

3.显示数据库中所有表：SHOW TABLES;

4.显示某数据库的表中各列信息：
DESCRIBE 表名;
或
DESC 表名;

6.表的类型：: MyISAM , InnoDB , HEAP , BOB , CSV等…

常用的MyISAM和InnoDB类型比较

	MyISAM	InnoDB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大

常规使用操作：

• MYISAM：节省空间，速度较快
• INNOBD：安全性高，支持事务处理，多表多用户操作

7.数据库在物理空间的存储位置：

所有数据库都存在data目录下，一个文件夹对应一个数据库，本质还是文件的存储

8.修改表名称：ALTER TABLE 表名 RENAME AS 新表名;

9.增加表的字段：ALTER TABLE 表名 ADD `字段名` 列类型 [属性] [索引] [注释]

10.修改表的字段：

• 重命名字段：
  ALTER TABLE 表名 CHANGE 字段名 新字段名

• 修改字段约束：
  ALTER TABLE 表名 MODIFY 字段名 列类型 [属性] [索引] [注释]

• 删除字段：
  ALTER TABLE 表名 DROP 字段名

11.删除表：DROP TABLE [IF EXISTS] 表名;

五、外键

1.创建表时指定外键：

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	`gradedean` VARCHAR(20) NULL DEFAULT 'wait' COMMENT '年级主任',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8



CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

2.已创建表后，修改表时指定外键：

ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 被引用的表名(字段名)

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	`gradedean` VARCHAR(20) NULL DEFAULT 'wait' COMMENT '年级主任',
	PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8



CREATE TABLE IF NOT EXISTS `student` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',
  `gradeid` INT(10) NOT NULL COMMENT '学生的年级',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

注：

• 删除有外键关系的表时，需先删除有引用的的表（从表），然后再删除被引用的表（主表）
• 不建议使用外键与级联，一切外键都可在应用层程序中解决
• 外键过多，每次DELETE或UPDATE时，都必须考虑外键的约束，会造成麻烦与混乱

六、数据操纵语言（DML语言）

（1）增加（INSERT）

1.插入一行：

INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …);

INSERT INTO `grade` (gradename, gradedean) 
VALUES ('a1', 'zlc1'); 

2.插入多行：

INSERT INTO 表名 (字段名1, 字段名2, …) VALUES (值1, 值2, …), (值1, 值2, …), (值1, 值2, …), …;

INSERT INTO `grade` (gradename, gradedean)
VALUES ('a2', 'zlc2'),
       ('a3', 'zlc3');

（2）修改（UPDATE）

1.修改一个字段：

UPDATE 表名 SET 字段名 = 新值 WHERE 条件

UPDATE `grade` SET `gradename` = 'b1' WHERE gradeid = 1;

2.修改多个字段：

UPDATE 表名 SET 字段名1 = 新值1, 字段名2 = 新值2, … WHERE 条件

UPDATE `grade` SET `gradename` = 'aa1', `gradedean` = 'zzlc1' WHERE `gradeid` = 1;

（3）删除（DELETE）

1.删除表中指定行：

DELETE FROM 表名 WHERE 条件;

DELETE FROM grade WHERE gradeid = 2;

2.删除表中全部行（表的结构和索引约束不变）：

TRUNCATE 表名;

TRUNCATE grade;

3.DELETE FROM与TRUNCATE删除整表数据的区别：

相同点：都能删除表的所有行，保留表的结构

不同点：

• TRUNCATE会重新设置自增列，计数器归零
• TRUNCATE不会影响事务
• TRUNCATE删除整表的行时效率高

另一个知识：

DELETE FROM删除整表后，重启数据库

• InnoDB：自增列会从1开始（因为此计数值是存在与内存中）
• MyISAM：自增列仍从之前数值延续（因为此计数值存在于文件中）