Mysql

Mysql管理

net start mysql # 启动mysql服务
net stop mysql # 停止mysql服务

Mysql连接

连接

mysql -u root -p -h [ip] -P [port]

• -u指定用户名
• -p表示需要密码
• -h指定host，默认为localhost
• -P指定port，默认为3306

断开

exit;

数据库操作、字符集和储存引擎

数据库操作

show databases; # 查看所有数据库
select database(); # 查看当前数据库
use db_name; # 选择操作数据库
alter db_name; # 修改数据

创建数据库

create database [if not exists] db_name
default character set utf8 # 默认字符集
default collate utf8_general_ci; # 默认排序方式

If not exists 用于避免数据库已经存在的报错

删除数据库

drop database [if exists] db_name;

if exists 用于避免数据库不存在的报错

选择数据库

use db_name;

修改数据库

alter database test_db
character set utf8 # 字符集
collate utf8_general_ci; # 排序方式

查询数据库

show databases; # 查看所有数据库
select database(); # 查看当前数据库
select schema_name from schemata; # 查看所有数据库
# 实际上在Mysql5.7之后新增了一个 sys 库，也可以用于查看所有数据库

字符集

字符集简介

show character set; # 查看所有字符集

字符集问题不仅会影响数据存储，还会影响客户端程序与 MySQL 服务器之间的通信。如果希望客户端程序使用与默认字符集不同的字符集与服务器通信，则需要指明是哪一个。

set names 'utf8'

常用字符集

储存引擎

储存引擎简介

show variables like 'default_storage_engine'; # 查看默认储存引擎
show engines; # 查看所有储存引擎

修改表的储存引擎

alter table tb_name
engines = InnoDB;

储存引擎

表的操作和完整性约束

表的概述

介绍

MySQL 数据表是用来存储数据的基本结构，它由列和行组成，每列代表一个字段，每行代表一条记录。数据表是数据库中的核心组成部分，用于组织和存储数据，以便进行查询、插入、更新和删除操作。

表的基本组成

数据类型

数值类型

字符串类型

枚举类型与集合类型

表的操作

创建表

create table [if not exists] tb_name(
    column datatype ...,
  ...
);

示例

create table [if not exists] users (
    id int auto_increment primary key,
    username varchar(50) not null,
    email varchar(100) not null,
    birthdate date,
    is_active boolean default true
) character set utf8 collate utf8_general_ci;

创建一个user表

• id: 用户 id，整数类型，自增长，作为主键。
• username: 用户名，变长字符串，不允许为空。
• email: 用户邮箱，变长字符串，不允许为空。
• birthdate: 用户的生日，日期类型。
• is_active: 用户是否已经激活，布尔类型，默认值为 true。

并规定字符集为utf8排序规则为utf8_general_ci

删除表

drop table [if not exists] tb_name;

修改表

alter table tb_name
rename to new_tb_name # 修改表名
add column cl_name datatype # 添加列
modify column cl_name new_datatype # 修改列的数据类型
change column old_cl_name new_cl_name datatype # 修改列名
drop column cl_name; # 删除列
add primary key (cl_name) # 添加 primary key 主键
/*添加foreign key 外建*/
add constraint fk_name
foreign key (cl_name)
references tb_name (cl_name)
/**/

数据完整性约束

create

列级约束

create table tb_name(
    cl_name datatype primary key
)

表级约束

create table tb_name(
    cl_name datatype
  constraint ctr_name ctr_type(cl_name)
)；

alter

alter table tb_name
modify cl_name datatype primary key; # 列级主键约束
add constraint pk_name primary key(cl_name); # 表级主键约束
modify cl_name datatype datatype unique key; # 列级唯一键约束
add constraint uk_name unique key(cl_name); # 表级唯一键约束
add constraint fk_name# start
foreign key (cl_name)# 外键约束
references tb_name (cl_name)# end

删除约束

alter table tb_name
drop primary key pk_name # 主键
drop index uk_name # 唯一键
drop foreign key fk_name # 外键

表记录的操作

查询

select column1, column2, ...
from tb_name
[where condition]
[order by cl_name [ASC | DESC]]
[limit number];

插入

insert into tb_name (c1_name1, cl_name2, cl_name3, ...)
values (value1, value2, value3, ...);

更新

update tb_name
set column1 = value1, column2 = value2, ...
where condition;

删除

delete from tb_name
where condition;

表记录的查询

查询语句

select [all|distinct] column1, column2, ...
from tb_source
[where condition]
[order by exp [ASC | DESC]]
[limit number];

查询的列除列名之外，也可以是表达式和常量，tb_source也不一定是表名，子查询或文件等等都可以是数据源

all显示所有数据，distinct去重

where子句

where子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。

表达式

order by子句

• asc按照表达式exp结果集升序排列
• desc按照表达式exp结果集降序排列

聚合函数

group by子句

对聚合函数查询出的结果集进行分组，having用于过滤分组后的结果

多表连接查询

在MySQL中，多表连接查询（JOIN）是一种从多个表中检索数据的方式。常见的连接类型包括CROSS JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN

交叉连接

select tb1_name.cl1,tb2_name.cl2
from tb1_name
cross join tb2_name;

交叉连接返回两个表的笛卡尔积

内连接

select tb1_name.cl1,tb2_name.cl2
from tb1_name
inner join tb2_name 
on tb1_name.comon_cl=tb2_name.common_cl;

返回两个表中匹配的记录

外连接

select tb1_name.cl1,tb2_name.cl2
from tb1_name
left|right outer join tb2_name 
on tb1_name.comon_cl=tb2_name.common_cl;

左/右外连接返回对应表的所有记录，即使其他表中没有的记录用NULL占位

子查询

就是内联查询

存在一个表employees

select name from employees where year_joined<(select year(now())-1); # 查询去年之前入职的员工

同理，插入删除更新也是一样的

索引

普通索引

对值没有任何要求

create index idx_name
on tb_name(cl_name);

唯一索引

可以是空值，但值唯一

create unique index uidx_name
on tb_name(cl_name);

主键索引

就是数据完整性约束中的主键

创建索引

和数据完整性约束一样

[constraint idx_name] [unique|fulltext] [idx_name] (cl_name);

Explain语句

explain语句用于分析 SQL 查询语句的执行计划

执行语句

explain select * from company_db.employees;

返回结果分析

视图

视图是一个虚拟表，它并不在数据库中存储数据，而是存储一个查询语句。当我们对视图进行查询时，MySQL 会使用该查询语句来生成结果集。

创建视图

create view v_name as
# 查询语句

修改视图

alter view v_name as
# 查询语句

删除视图

drop view v_name;

视图的应用

视图的增删改查与数据表一致

Mysql编程

基础语法

变量的声明

set @var=1; # 会话变量
declare var int # 局部变量

其他内容

参考前面的部分

用户自定义函数

用户自定义函数有以下要求：

• 必须返回一个值
• 只能使用select语句返回值，不能用insert、update、delete等修改表中的记录

创建/删除函数

create function func_name([p_name datatype,[...]]) returns datatype
begin
# 函数体
end;

drop function func_name;

实际上，Mysql的UDF可以使用C\C++编写动态链接库，实现更底层与系统交互的功能

函数体的编写

declare v int # 声明变量
select 1 into v # 给变量赋值
return v # 返回值

函数的调用

函数可以在一个语句中的任何地方使用，构成表达式的一部分

编写一个简单的函数，求两数之和

create function sum(a int,b int) returns int
begin
declare result int
select (a+b) into result
end;

储存过程

储存过程时一段储存在数据库中的sql语句

创建/删除储存过程

create procedure proc_name([type p datatype,[...]])
# sql语句

触发器

应该不会考，自己查

用户权限管理

用户管理

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; # 创建用户
DROP USER 'newuser'@'localhost'; # 删除用户

权限管理

Mysql权限列表

• 全局权限

• ALL [PRIVILEGES]: 授予所有权限，等同于授予所有单独的权限。

• GRANT OPTION: 允许用户授予自己拥有的权限给其他用户。

• 数据库权限

• CREATE: 允许创建数据库和表。

• DROP: 允许删除数据库和表。

• DELETE: 允许删除表中的记录。

• INSERT: 允许向表中插入记录。

• SELECT: 允许读取表中的数据。

• UPDATE: 允许更新表中的记录。

• INDEX: 允许创建和删除索引。

• ALTER: 允许修改表结构。

• CREATE TEMPORARY TABLES: 允许创建临时表。

• LOCK TABLES: 允许使用 LOCK TABLES 语句。

• REFERENCES: 允许创建外键（尽管 MySQL 目前并不严格检查这一点）。

• SHOW VIEW: 允许执行 SHOW CREATE VIEW 命令。

• CREATE VIEW: 允许创建视图。

• ALTER ROUTINE: 允许修改或删除存储例程。

• EXECUTE: 允许执行存储过程和函数。

• 表权限
• SELECT: 允许读取表中的数据。
• INSERT: 允许向表中插入记录。
• UPDATE: 允许更新表中的记录。
• DELETE: 允许删除表中的记录。
• CREATE: 允许创建表。
• DROP: 允许删除表。
• INDEX: 允许创建和删除索引。
• ALTER: 允许修改表结构。

• REFERENCES: 允许创建外键。

• 列权限

• SELECT: 允许读取表中特定列的数据。
• INSERT: 允许向表中特定列插入数据。

• UPDATE: 允许更新表中特定列的数据。

• 存储过程和函数权限

• EXECUTE: 允许执行存储过程和函数。

• ALTER ROUTINE: 允许修改或删除存储例程。

• 高级权限

应该用不到

权限管理

GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION; # 全局权限
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'username'@'localhost'; # 数据库级别权限
GRANT SELECT, INSERT ON mydatabase.mytable TO 'username'@'localhost'; # 表级别权限
GRANT SELECT (col1, col2), INSERT (col3) ON mydatabase.mytable TO 'username'@'localhost'; # 列级别权限
FLUSH PRIVILEGES; # 刷新权限
show grants for 'user'@'localhost'; # 查看用户权限