mysql使用

发表于 更新于

事务

原子性 Atomicity

BUSINESS
sql语句1
sql语句2
COMMIT

原子性：事务操作要么同时发生，要么同时失败，不存在中间情况

通过Undo Log回滚实现

一致性 Consistency

账户500元 -> 扣除1000元 -> 账户-500元
-- 非法操作

一致性：每个操作都必须是合法的，账户信息应该从一个有效状态到另一个有效状态。

隔离性 Isolation

商户1转账500元 -> 余额更新为500元
商户2转账500元 -> 余额更新为500元
-- 没有隔离性

隔离性：两个操作对同一个账户并发操作时，应该表现为不相互影响类似串行的操作。

持久性 Durability

转账500元到余额 --服务器宕机--> 余额0元

持久性：操作更新成功后，更新的结果应该永久地保留下来，不会因为宕机等问题而丢失。

数据库设计范式

1NF

字段原子性

确保每一个字段都不可再分割。避免“省-市-区”，而是划分为三个字段；避免“课程”的值为“语文、数学、英语”，而是插入三行数据。

2NF

主键全依赖

必须有主键，而且要依赖整个主键（而不是复合主键的一部分）。
例如一张成绩表，又依赖学生ID、又依赖课程，就是不好的。姓名只需要学生ID做主键就可以了。

学生ID 课程 姓名 成绩
001 数学 小明 85
001 语文 小明 90

应该拆分成两张表

学生ID 姓名
001 小明
002 小红
学生ID 课程 成绩
001 数学 85
001 语文 90

3NF

直接依赖主键

字段不能依赖主键以外的属性。
例如下面这张表，“班主任电话”依赖“班主任”，但是“班主任”不是主键。同样要拆分为两张表。

学生ID 姓名 班主任 班主任电话
001 小明 张老师 123456
002 小红 张老师 123456

使用

建表注意事项

  1. 遵守三大范式（原子性、主键全依赖、主键直接依赖）
  2. 合理的字段设计，如时间datetime/timestamp、布尔值用tinyint、避免Text、Blob、定长char、不定长varchar
  3. 非空、唯一约束、默认值等，主键要递增。
  4. 运行一段时间后，根据业务使用的sql语句建立索引。（最左前缀原则）

创建用户

-- 创建用户
CREATE USER 'devuser'@'192.168.1.%' IDENTIFIED BY 'Secure@123';
-- 授予权限
GRANT ALL PRIVILEGES ON appdb.* TO 'devuser'@'192.168.1.%';
-- 刷新权限
FLUSH PRIVILEGES;

-- 撤销
REVOKE ALL PRIVILEGES ON mydb.* FROM 'newuser'@'%';

启动数据库

# 需要管理员权限
net start <mysql-service_name>

登录数据库

mysql -u <username> -p
$ <password>

导入数据库

-- 设置中文字符集
mysql> SET NAMES 'utf8mb4';
mysql> SET character_set_server = 'utf8mb4';
mysql> SOURCE /path/to/database.sql;

数据库

-- 创建数据库
CREATE DATABASE yourDatabase
  CHARACTER SET utf8mb4;

-- 查看所有数据库
show databases;

-- 进入数据库
use yourDatabase

表

-- 建表
CREATE TABLE yourTable (
  id   INT         AUTO_INCREMENT PRIMARY KEY,
  name varchar(50) NOT NULL       DEFAULT 'worker',
  age  INT         ,
  addr varchar(50) 
);

-- 删除表
drop <table>;

-- 查看表 
show tables;

-- 修改表
alter table <table>
add <col> char(20)
drop column <col>;

-- 删除整张表
drop table <table>;

查询

-- \G 参数单独展示每一行，避免过宽
select * from <table>\G

-- 查询多列
select <column1>, <column2>, <column3>
from <table>;

-- 只显示不同项
-- 注意，对多列使用时，两列完全一样才会被屏蔽
select distinct <column> from <table>;

-- 检错前5行
select <col> from <table> limit 5;

排序

ORDER BY 需要放在 WHERE 之后

-- 按列升序排序
select <col>
from <table>
order by <col> (asc); -- 默认Ascending

-- 降序排序。对多列排序，每列都要跟desc
select <col>
from <table>
order by <col1> desc, <col2> desc; -- Descending降序排序

筛选

-- 筛选col = value的字段
select <col>
from <table>
where <col> = <value>;

-- 筛选between 0 and 10的字段
select <col>
from <table>
where <col> between 0 and 10;

-- 筛选col为空的字段
select <col>
from <table>
where <col> is null;

逻辑操作符

优先级： AND > OR

-- AND, OR
select <col>
from <table>
where <cond1> or (<cond2> and <cond3>); -- 不加括号，结果会改变

-- IN, NOT
select <col>
from <table>
-- 相当于OR，性能稍好一点，也更直观；NOT主要配合IN使用
where <col> not in (<value1>, <value2>);

插入

-- 插入相应字段的值
insert into <table>(<col1>,
                    <col2>)
values(<val1>,
       <val2>);

insert into yourTable (name, email, age) values ('eric', 'example@email.com', 19);

更新

update <table>
set <col1> = <val1>,
    <col2> = <val2>
where <col3> = <val3>;

删除

delete from <table>
where <col> = <val>;

联结

设计数据库时，应遵循将数据分解到不同的数据表这一原则。
然而，在使用数据时，常常需要将多个表的数据一起检索出来。
这时就需要用到联结。

-- 使用WHERE联结
select <col1>, <col2>
from <table1>, <table2>
where <table1>.<col> = <table2>.<col>;

-- 使用ON联结
select <col1>, <col2>
from <table1> inner join <table2>
on <table1>.<col> = <table2>.<col>;

安装

压缩包安装

  1. 初始化
# 初始化，生成data文件夹，console参数可以看见密码
mysqld --initialize --console > mysql_info
# > mysql_info把命令行输出结果存到文件里，防止密码丢失
  1. 配置mysql.ini
[mysqld]

# 设置3306端口
port=3306

# 设置mysql目录路径
basedir=C:\\your\\path\\MySQL

# 设置mysql数据库的数据的存放目录
datadir=C:\\your\\path\\MySQL\\Data

# 允许最大连接数
max_connections=200

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

[mysql]

# 设置mysql客户端默认字符集
default-character-set=utf8

[client]

# 设置mysql客户端连接服务端时默认使用的端口
port=3306

default-character-set=utf8
  1. 安装服务
mysqld --install MySQL8 --defaults-file="C:\your\path\MySQL\mysql.ini" # 设置服务名

# 默认安装
mysqld install
  1. 修改密码
# 设置YourPassword为新密码，再输入旧密码确认修改即可
mysqladmin -u root -p password (YourPassword) --port 3306