MySQL基础

MySQL基础

---

---

一、MySQL简介

 SQL：Structured Query Language, 结构化查询语言，用于访问和处理数据库的标准的计算机语言。
 SQL具有以下语法特点：

• SQL对关键字大小写不敏感。
• SQL语句可以以单行或者多行书写，以分号结束。

 MySQL 是最流行的关系型数据库管理系统之一，具有以下特点：

• MySQL数据库使用C和C++语言编写，保证了源码的可移植性。
• 支持Windows、Linux、Mac OS等多个操作系统
• 为多种编程语言提供API，包括C、Java、Python等
• 优化了SQL算法，有效提高了查询速度
• 开源且无版权制约

---

二、SQL数据类型

1.数值类型

类型	大小	范围（有符号）	范围（无符号）
TINYINT	1 byte	(-128, 127]	(0, 255]
SMALLINT	2 bytes	(-32768, 32767)	(0, 65535]
MEDIUMINT	3 bytes	(-8388608, 8388607]	(0, 16777215]
INT	4 bytes	(–2147483648, 2147483647]	(0, 4294967295]
BIGINT	8 bytes	(-9223372036854775808, 9223372036854775807]	(0, 18446744073709551615]
FLOAT	4 bytes	(-3.402823466 E+38, -3.402823466351 E+38)	0, (1.175494351 E-38~3.402823466 E+38)
DOUBLE	8 bytes	（-1.7976931348623157 E+308， 1.7976931348623157 E-308）	0， （2.2250738585072014 E-308， 1.7976931348623157 E+308）
DECIMAL		依赖于M和D的值	依赖于M和D的值

decimal(M,D) —— M为有效位数，D为保留小数位数

2.字符串类型

类型	大小
CHAR	0-255 bytes
VARCHAR	0-65535 bytes
TINYBLOB	0-255 bytes
TINYTEXT	0-255 bytes
BLOB	0-65535 bytes
TEXT	0-65535 bytes
MEDIUMBLOB	0-16777215 bytes
MEDIUMTEXT	0-16777215 bytes
LONGBLOB	0-4294967295 bytes
LONGTEXT	0-4294967295 bytes

3.日期类型

类型	大小	范围	格式
DATE	3 bytes	1000-01-01/9999-12-31	YYYY-MM-DD
TIME	3 bytes	-838:59:59/838:59:59	HH:MM:SS
YEAR	1 bytes	1901/2155	YYYY
DATETIME	8 bytes	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	1970-01-01 00:00:00/2038	YYYY-MM-DD HH:MM:SS

---

三、DDL基本操作

 DDL（Data Definition Language），数据定义语言。包括对数据库的常用操作、对表结构的常用操作、修改表结构。

---

1.数据库基本操作

SHOW DATABASES; -- 查看所有数据库

CREATE DATABASES IF NOT EXISTS myDatabase1; -- 创建新数据库

USE myDatabase1; -- 切换数据库

DROP DATABASE IF NOT EXISTS myDatabase1; -- 删除数据库

ALTER DATABASE myDatabase1 CHARACTER SET utf8; -- 修改数据库编码

2.表操作

对表进行操作前需先创建表：

-- 创建表

-- 1、选择数据库
USE myDatabase1;

-- 2、创建表

-- 创建表的语法格式
create table [if not exists] 表名 (
  字段名1 类型[(宽度)] [约束条件] [COMMENT '字段说明'],
  字段名2 类型[(宽度)] [约束条件] [COMMENT '字段说明']
)[表的一些设置];
-- [中括号]中的内容可以省略

-- 举例：
CREATE TABLE IF NOT EXISTS mytable(
	id INT,
	name VARCHAR(10),
	gender VARCHAR(1),
	age INT,	
	birth DATE
);

在创建表时可以在类型后添加 UNSIGNED将数值类型规定为无符号

对表进行操作：

SHOW TABLES; -- 查看当前数据库的所有表

SHOW CREATE TABLE mytable; -- 查看指定表的创建语句

DESC mytable; -- 查看表结构

DROP TABLE mytable; -- 删除表

初始创建的表的结构为：

修改表的结构：

ALTER TABLE mytable ADD 'number' INT; -- 添加列

ALTER TABLE mytable CHANGE birth birthday VARCHAR(20); 
-- 修改列名和类型

ALTER TABLE mytable DROP birthday; -- 删除列

RENAME TABLE mytable TO mytables; -- 修改表名

经过上述修改后的表结构为：

---

四、DML基本操作

 DML（Data manipulation Language）,用于对数据库中表的数据记录进行更新。
向表中插入值：

INSERT INTO mytables(age，gender) VALUES(18,'男'); 
-- 向表中某列插入值

INSERT INTO mytables(age，gender) VALUES(19,'男'),
										(20,'女'); 
-- 一次向表中某些列插入多行值

INSERT INTO mytables VALUES(4,'Andrew','男',16,123); 
-- 向表中所有列中插入值,同样可以一次插入多行值

插入值后的表格：

对表中的值进行修改：

-- 对表中的值进行修改

UPDATE mytables SET age = 18; 
-- 修改表中某字段名的所有数值

UPDATE mytables SET gender = '女' WHERE id >= 2;
-- 修改特定列的某字段名数值

修改后的表格：

删除表中的数值或删除表：

-- 删除表中的数值

DELETE FROM mytable WHERE id = 4; 
-- 删除符合where条件的一行数值

DELETE FROM mytable; 
-- 删除表所有数据

TRUNCATE [TABLE] mytale;
-- 删除表中所有数据

 delete 和 truncate原理不同，delete 只删除内容，而 truncate 类似于drop table，可理解为将整个表删除后再创建该表。

---

五、约束

 约束（constraint），表中数据的限制条件。用于保证表中记录的完整性和有效性。分为：主键约束、自增长约束、非空约束、唯一性约束、默认约束、零填充约束、外键约束。

1、主键约束

• 主键约束是一列或多列的组合，其值能唯一的标识表中的每一行，方便在RDBMS中尽快的找到某一行
• 主键约束相当于唯一约束+非空约束的组合
• 每个表最多只允许一个主键
• 主键约束的关键字为 PRIMARY KEY
• 当创建主键约束时，系统默认会在所在的列和列组合上建立唯一的对应索引

主键约束 -> PRIMARY KEY
-- 创建单列主键

-- 1、定义字段的同时指定主键
CREATE mydatabase1.TABLE majorkey1(
	id INT PRIMARY KEY,
	name VARCHAR(20),
	number DOUBLE
);

-- 2、定义字段后再指定主键
CREATE mydatabase1.TABLE majorkey2(
	number DOUBLE,
	id INT,
	name VARCHAR(20),
	[CONSTRAINT pk] PRIMARY KEY(number)
	-- CONSTRAINT  pk可加可不加
);

使用DESC + 表名 可以查询表的结构从而看到表中是否有主键：

添加联合主键：

-- 添加联合主键

CREATE mydatabase1.TABLE majorkey3(
	id INT,
	name VARCHAR(20),
	number DOUBLE,
	CONSTRAINT pk PRIMARY KEY(id,number)
);
-- 联合主键各列的值不能完全相同，但可部分相同。联合主键的各列均不能为空

其他添加主键的形式：

-- 通过修改表结构添加主键

CREATE mydatabase1.TABLE majorkey4(
	id INT,
	number INT,
	name VARCHAR(20)
);
ALTER TABLE majorkey4 ADD PRIMARY KEY(id,name); 
-- 既可以添加单列主键，也可以添加联合主键，此处添加了一个联合主键

删除主键：

-- 删除主键

ALTER TABLE majorkey4 DROP PRIMARY KEY; 
-- 不区分单列主键和联合主键。因为本质上都为一个主键

---

2、自增长约束

• 关键字为 auto_increment
• 默认情况下初始值为1，没新增一条记录，字段值自动增加1
• 一个表中只能有一个字段使用自增长约束，且该字段必须有唯一索引，以避免序号重复（即为主键或主键的一部分）
• 自增长约束的字段必须具有非空（NOT NULL）属性
• 自增长约束的字段只能为整数类型，如TINYINT、SMALLINT、INT等
• 自增长约束的字段受该字段的数据类型的约束，达到上限自增长就会失效

创建自增长约束：

-- 自增长约束

CREATE mydatabase1.TABLE incretable1(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
);
INSERT INTO incretable1(name) VALUES('Andrew'),
								   ('Bob');

执行后的结果：

可以看到即使未指定 id 的值，id 的值也会从1开始自增长。

指定自增字段的初始值：

-- 指定自增字段的初始值

-- 1、创建表时指定
CREATE mydatabase1.TABLE incretable2(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
)auto_increment = 100;
INSERT INTO incretable2(name) VALUES('Andrew'),
								    ('Bob');
-- 2、创建表后指定
CREATE mydatabase1.TABLE incretable3(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
);
ALTER TABLE incretable3 auto_increment = 10;
INSERT INTO incretable3(name) VALUES('Andrew'),
								    ('Bob');

执行后两表格的结果：


删除表中的数据：

-- DELETE删除数据后自增长从断点开始		
			
CREATE mydatabase1.TABLE incretable4(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
)
INSERT INTO incretable4(name) VALUES('Andrew'),
									('Bob');
DELETE FROM incretable4;
INSERT INTO incretable4(name) VALUES('Andrew'),
									('Bob');

-- TRUNCATE删除数据后从默认起始值开始

CREATE mydatabase1.TABLE incretable5(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(20)
)auto_increment = 100;
INSERT INTO incretable5(name) VALUES('Andrew'),
									('Bob');
TRUNCATE incretable5;
INSERT INTO incretable5(name) VALUES('Andrew'),
									('Bob');

执行语句后的结果：

---

3、非空约束

NOT NULL，指字段的值不能为空，如果用户在添加数据时没有指定值，数据库系统就会出错。

创建非空约束：

-- 非空约束

-- 创建表时指定

CREATE mydatabase1.TABLE nonull1(
	id INT,
	name VARCHAR(20) NOT NULL
);

-- 创建表后指定

CREATE mydatabase1.TABLE nonull2(
	id INT,
	name VARCHAR(20)
);
ALTER TABLE nonull2 MODIFY id INT NOT NULL;

测试：

DESC nonull2;
INSERT INTO nonull2(name) VALUES('Carlos');

结果如图：


删除非空约束：

ALTER TABLE nonull2 MODIFY id INT; 
-- 删除非空约束

---

4、唯一约束

UNIQUE KEY，指所有记录中字段的值不能重复出现。

创建唯一约束：

-- 唯一约束

-- 1、创建表时指定

CREATE TABLE mydatabase1.TABLE onlytable1(
	id INT,
	name VARCHAR(20) UNIQUE
);

-- 2、创建表后指定

CREATE TABLE mydatabase1.TABLE onlytable1(
	id INT,
	name VARCHAR(20)
);
ALTER TABLE onlytable2 ADD CONSTRAINT uni UNIQUE(name);
-- CONSTRAINT uni指定唯一约束的字段名，便于删除时使用

受到唯一约束的字段的值可以均为NULL，在MySQL中，NULL与任何值都不相同，包括NULL

删除唯一约束：

-- 删除唯一约束

ALTER TABLE onlytable2 DROP INDEX uni;
-- 

---

5、默认约束

DEFAULT,用于指定某列的默认值。

创建默认约束：

-- 1、创建表时指定

CREATE mydatabase1.TABLE deftable1(
	id INT,
	name VARCHAR(20),
	address VARCHAR(20) DEFAULT '长春'
);

-- 2、创建表后指定

CREATE mydatabase1.TABLE deftable2(
	id INT,
	name VARCHAR(20),
	address VARCHAR(20)
);
ALTER TABLE deftable2 MODIFY address VARCHAR(20) DEFAULT '长春';

测试：

INSERT INTO deftable2(id,name) VALUES(1,'Andrew'),
						           	 (2,'Bob');

结果如图：

删除默认约束：

-- 删除默认约束

ALTER TABLE deftable2 MODIFY address VARCHAR(20) DEFAULT NULL;

---

6、零填充约束

• 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上对应的0
• 关键字为ZEROFILL，默认为INT(10)
• 使用ZEROFILL时，系统默认自动添加UNSIGNED（无符号）属性

创建零填充约束：

-- 零填充约束

CREATE mydatabase1.TABLE zerotable(
	id INT ZEROFILL,
	name VARCHAR(20)
);
INSERT INTO zerotable VALUES(1, 'Andrew');

结果如图：

删除零填充约束：

-- 删除零填充约束

ALTER TABLE zerotable MODIFY id INT;

---