安装相关
- 启动cmd的时候得用管理员权限
- mysql -uroot -p 连接mysql
- show databases; 显示逻辑空间(文件夹)
数据库操作
- CREATE DATABASE test1; 创建数据库
修改root密码
- 创建一个TXT文件 ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘123456’
- 右键左下角windows图标,以管理员的身份打开windows powershell
- net stop mysql80 停止mysql
- mysqld --defaults–file=“C:\ProgramData\MySQL\MySQL Server 8.0\my.ini” --init-file=“D:/temp.txt” --console
- 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语句注意事项
- SQL语句不区分大小写,但是字符串区分大小写
SELECT “HelloWorld”; - SQL语句必须以分号结尾
- SQL语句中的空白和换行没有限制,但是不能破坏语法
SQL语句的注释
- #这是一段注释
- /* 这是一段注释 */
创建逻辑库
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;
索引的使用原则
- 数据量很大,而且经常被查询的数据表可以设置索引
- 索引只添加在经常被用作检索条件的字段上面
- 不要在大字段上创建索引