MySQL的学习（一）

安装相关

1. 启动cmd的时候得用管理员权限
2. mysql -uroot -p 连接mysql
3. show databases; 显示逻辑空间（文件夹）

数据库操作

1. CREATE DATABASE test1; 创建数据库

修改root密码

1. 创建一个TXT文件 ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’
2. 右键左下角windows图标，以管理员的身份打开windows powershell
3. net stop mysql80 停止mysql
4. mysqld --defaults–file=“C:\ProgramData\MySQL\MySQL Server 8.0\my.ini” --init-file=“D:/temp.txt” --console
   
5. net start mysql80

MySQL my.ini配置

[client]
# 设置客户端连接端口，如果没有设置，默认连接3306端口
port=3306
# mysql报错时，是否启动蜂鸣声
[mysql]
no-beep

# mysql 设置

[mysqld]
port=3306   # 设置mysql端口号
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data  # 数据目录
default_authentication_plugin=caching_sha2_password   #密码认证插件
default-storage-engine=INNODB  # 默认存储引擎
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"  # 开启严格模式
log-output=FILE  # 用文件记录日志
general-log=0  # 关闭日志输出，默认关，1为开
general_log_file="DESKTOP-MPP4GCJ.log"  # 日志文件名称
slow-query-log=1  # 开启慢查询日志
slow_query_log_file="DESKTOP-MPP4GCJ-slow.log"  #慢查询日志文件名称
long_query_time=10  # 大于多少秒的执行SQL被记录在慢查询日志
log-bin="DESKTOP-MPP4GCJ-bin"  #
log-error="DESKTOP-MPP4GCJ.err"  # 错误日志名称
server-id=1  # 数据库ID ,mysql集群的时候用
lower_case_table_names=1  # 把表名转换成小写
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"  # 导入导出数据文件存放的目录地址
max_connections=151  #  最大的连接数
table_open_cache=2000
tmp_table_size=20M
thread_cache_size=10  #  线程数量
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=30M
key_buffer_size=8M
read_buffer_size=61K

read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=8M
innodb_log_file_size=48M
innodb_thread_concurrency=9
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000

innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=4M
max_connect_errors=100
open_files_limit=4161
sort_buffer_size=256K

table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

loose_mysqlx_port=33060

SQL语句注意事项

1. SQL语句不区分大小写，但是字符串区分大小写
   SELECT “HelloWorld”;
2. SQL语句必须以分号结尾
3. SQL语句中的空白和换行没有限制，但是不能破坏语法

SQL语句的注释

1. #这是一段注释
2. /* 这是一段注释 */

创建逻辑库

mysql > CREATE DATABASE 逻辑库名称; 创建逻辑库
mysql > SHOW DATABASES; 查看逻辑库
mysql > DROP DATABASE 逻辑库名称; 删除逻辑库

创建数据表

CREATE TABLE 数据表 （
列表1 数据类型 [ 约束] [ COMMENT 注释],
列表2 数据类型 [ 约束] [ COMMENT 注释],
列表3 数据类型 [ 约束] [ COMMENT 注释],
…
）[ COMMENT = 注释];

USE test1;
CREATE TABLE student (
	id INT UNSIGNED PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	sex CHAR(1) NOT NULL,
	birthday DATE NOT NULL,
	tel CHAR(11) NOT NULL,
	remark VARCHAR(200)
);
INSERT INTO student VALUES(1,"谭超","男","1990-11-01","18512345678",NULL);

# 显示所拥有的表
SHOW tables;
# 显示表的详情
DESC student;
# 显示创建这个表的时候的语句
SHOW CREATE TABLE student;
# 删除这个表
DROP TABLE student;

数据类型： 数字

类型	大小	说明
TINYINT	1字节	小整数
SMALLINT	2字节	普通整数
MEDIUMINT	3字节	普通整数
INT	4字节	较大整数
BIGINT	8字节	大整数
FLOAT	4字节	单精度浮点数，精度丢失
DOUBLE	8字节	双精度浮点数 ，精度丢失
DECIMAL	————	DECIMAL(10,2)，以字符串形式存储，不存在精度丢失

不精确的浮点数

十进制的浮点数无法在计算机中用二进制精确表达
如何用二进制的小数表示十进制的0.2？
1/8+1/16+1/128 + …

字符串数据类型

类型	大小	说明
CHAR	1-255字符	固定长度字符串
VARCHAR	1-265535字符	不固定长度字符串
TEXT	1-265535字符	不确定长度字符串
MEDIUMTEXT	1-1千6百万字符	不确定长度字符串
LONGTEXT	1-42亿字符	不确定长度字符串

数据类型：日期类型

类型	大小	说明
DATE	3字节	日期
TIME	3字节	时间
YEAR	1字节	年份
DATETIME	8字节	日期时间
TIMESTAMP	4字节	时间戳（1970-01-01之后的）

修改表结构

添加字段

ALTER TABLE 表名称 
ADD 列1 数据类型 [约束] [COMMENT 注释],
ADD 列2 数据类型 [约束] [COMMENT 注释],
...;

ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;

修改字段类型和约束

ALTER TABLE 表名称 
MODIFY 列1 数据类型 [约束] [COMMENT 注释],
MODIFY 列2 数据类型 [约束] [COMMENT 注释],
...;

ALTER TABLE student
MODIFY address VARCHAR(201) NOT NULL,
MODIFY home_tel CHAR(18) NOT NULL;

修改字段名称

ALTER TABLE 表名称 
CHANGE 列1 新列名1 数据类型 [约束] [COMMENT 注释],
CHANGE 列2 新列名2 数据类型 [约束] [COMMENT 注释],
...;

ALTER TABLE student
CHANGE address home_address VARCHAR(202) NOT NULL;

删除字段

ALTER TABLE 表名称 
DROP 列1,
DROP 列1,
...;

ALTER TABLE student
DROP home_address;

发，

字段约束

数据库的范式

• 构造数据库必须遵循一定的规则，这种规则就是范式
• 目前关系数据库有6种，一般情况下，只满足第三范式即可

1.第一范式：原子性

• 第一范式是数据库的基本要求，不满足这一点就不是关系数据库
• 数据表的每一列都是不可分割的基本数据项，同一列中不能有多个值，也不能存在重复的属性
  例：

不符合第一范式

学号	姓名	班级
1000	北极熊	高三年级1班

符合第一范式

学号	姓名	年级	班级
1000	北极熊	高三	1班

2.第二范式：唯一性

数据表中的每条记录必须是唯一的。为了实现区分，通常要为表加上一个列用来存储唯一标识，这个唯一属性列被称为主键列
例：
无法区分重复的数据

学号	考试成绩	日期
1000	58	2018-07-15
1000	58	2018-07-15

数据具有唯一性

流水号	学号	考试成绩	日期
201807152687	1000	58	2018-07-15
201807152688	1000	58	2018-07-15

3.第三范式：关联性

每列都与主键有直接关系，不存在传递依赖
不正确示例：

爸爸	儿子	女儿	女儿的玩具	女儿的衣服
aa	a1	a2	海绵宝宝	校服

正确示例：

爸爸	儿子	女儿
aa	a1	a2

女儿	女儿的玩具	女儿的衣服
a2	海绵宝宝	校服

依照第三范例，数据可以拆分保存到不同的数据表，彼此保持关联

编号	部门	电话
10	技术部	1001

编号	姓名	性别	部门	入职日期
1	aa	男	技术部	2018-05-01

MySQL中的字段约束共有四种：

约束名称	关键字	描述
主键约束	PRIMARY KEY	字段值唯一，且不能为NULL
非空约束	NOT NULL	字段值不能为NULL
唯一约束	UNIQUE	字段值唯一，且可以为NULL
外键约束	FOREIGN KEY	保持关联数据的逻辑性

主键约束

• 主键约束要求字段的值在全表必须唯一，而且不能为NULL值
• 建议主键一定要使用数字类型，因为数字的检索速度会非常快
• 如果主键是数字类型，还可以设置自动增长

CREATE TABLE t_teacher (
	id INT PRIMARY KEY AUTO_INCREMENT,
	...
);

非空约束

• 非空约束要求字段的值不能为NULL值
• NULL值以为没有值，而不是’'空字符串

CREATE TABLE t_teacher (
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(200) NOT NULL,
	married BOOLEAN NOT NULL DEFAULT FALSE
);

唯一约束

唯一约束要求字段值如果不为NULL，那么在全表必须唯一

CREATE TABLE t_teacher (
	...
	tel CHAR(11) NOT NULL UNIQUE
);

外键约束

外键约束用来保证关联数据的逻辑关系
父表：

deptno	dname	tel
10	财务部	1001
20	技术部	1002
30	销售部	1003

子表：

empno	name	sex	deptno	hiredate
1	aa	男	10	2018-05-01
2	bb	女	20	2018-05-22

外键约束的定义是写在子表上的

CREATE TABLE t_teacher (
	empno INT UNSIGNED PRIMARY KEY,
	ename VARCHAR(20) NOT NULL,
	sex ENUM("男","女") NOT NULL,
	deptno INT UNSIGNED,
	hiredate DATE NOT NULL,
	FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
);

外键约束的闭环问题

如果形成外键闭环，我们将无法删除任何一张表的记录

数据定义语句：索引

数据排序的好处

• 一旦数据排序之后，查找的速度就会翻倍，现实世界跟程序世界都是如此

如何创建索引

CREATE TABLE 表名称 (
	...,
	INDEX [索引名称] (字段),
	...
);

如何添加与删除索引

CREATE INDEX 索引名称 ON 表名(字段);
ALTER TABLE 表名称 ADD INDEX [索引名](字段);
SHOW INDEX FROM 表名;
DROP INDEX 索引名称 ON 表名;

CREATE INDEX idx_type ON t_message(type);
ALTER TABLE t_message ADD INDEX idx_type(type);
SHOW INDEX FROM t_message;
DROP INDEX idx_type ON t_message;

索引的使用原则

• 数据量很大，而且经常被查询的数据表可以设置索引
• 索引只添加在经常被用作检索条件的字段上面
• 不要在大字段上创建索引