MySQL基础篇

简介

SQL 语句根据功能分为四种：

数据定义语言 DDL：Data Definition Language
数据操纵语言 DML：Data Maniplation Language
数据控制语言 DCL：Data Control Language
数据查询语言 DQL：Data Query Language

DDL

数据定义语言

作用：用于定义数据库的结构，例如数据库对象（表、索引、视图、存储过程）的创建、修改和删除

主要语句：

CREATE：创建数据库对象，例如：CREATE TABLE，CREATE INDEX，CREATE INDEX
ALTER：修改数据库对象，例如 ALTER TABLE
DROP：删除数据库对象，例如 DROP TABLE，DROP INDEX，DROP VIEW
TRUNCATE：清空表中数据，但保留表结构，例如 TRUNCATE TABLE
RENAME：重命名数据库对象，例如 REANME TABLE

DML

DCL

DQL

视图

简介

视图是一个或多个表的逻辑显示，它是一种虚拟表，不会存储数据，视图建立在已有表的基础上。通常不做对底层数据的修改

为什么要使用视图？

基于视图的特性，可以针对不同的上层需求创建不同的视图，而不需要全部展示数据

查看

查看当前数据库的所有视图

1

show full tables where table_type = 'VIEW';

注意 VIEW必须是大写

1
2
3
4
5
6


select
    *
from
    information_schema.views
where
    table_schema = database();

查看所有数据库的视图

1
2
3
4


select
    *
from
    information_schema.views;

查看视图结构

1

desc 视图名称;

查看视图详细定义

1

show create view 视图名称;

查看视图属性

1

show table status like 视图名称;

创建

语法

1
2


create view 视图名称
as 查询语句;

示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


-- 创建视图
create view employees_view
as
select
    *
from
    employees;

-- 查询视图
select * from employees_view;

修改

ALTER VIEW

修尬现有视图的定义，保留权限和依赖

1
2


alter view 视图名称
as 查询语句;

CRETE OR REPLACE VIEW

视图不存在则创建，不保留权限和依赖

1
2


create or replace view 视图名称
as 查询语句;

删除

删除视图不会删除数据

1

drop view 视图名称;

存储过程

简介

存储过程（Stored Procedure）是一组预先编译的 SQL 语句封装，好比编程语言中的函数封转

查看

查看当前数据库所有存储过程

1
2


show procedure status
where db = database();

1
2
3
4
5
6


select
    routine_name
from
    information_schema.routines
where routine_type = 'PROCEDURE'
  and routine_schema = database();

查看所有数据库所有存储过程

1

show procedure status;

1

select * from information_schema.routines where routine_type = 'PROCEDURE';

注意 PROCEDURE 大写

查看存储过程定义

1

show create procedure 存储过程名;

查看指定的存储过程状态

1

show procedure status like 'pattern'

创建

语法

1
2
3
4


create procedure 存储过程名称(in|out|inout 参数名 参数类型, ...)
begin
	具体要执行的sql语句
end

in：输入参数，如果没有定义参数，默认为 in
out：输出参数
inout：既是输入参数，又是输出参数

参数类型可以是 MySQL 数据库中的任意类型。

由于会存在多条 SQL 语句，所以一般情况下会修改 SQL 结束标识符 ;

1
2
3
4
5
6


delimiter $
create procedure 存储过程名称(in|out|inout 参数名 参数类型, ...)
begin
	具体要执行的sql语句
end $
delimiter ;

in 模式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 无参数
-- 创建一个存储过程，查询 employees 表员工数量
delimiter $
create procedure select_employees_procedure()
begin
	select
		count(*)
	from
		employees;
end $
delimiter ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14


-- 输入参数
-- 创建一个存储过程，使用输入参数 in 模式，查询指定员工姓名的信息
delimiter $
create procedure select_employee_salary_specify_name_procedure_in(
    in p_first_name varchar(50),
    in p_last_name varchar(50)
)
begin
    select *
    from employees
    where first_name = p_first_name
    and last_name = p_last_name;
end $
delimiter ;

out 模式

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 输出参数
-- 创建存储过程show_min_salary()，查看 employees 表的最低薪资值。并将最低薪资通过OUT参数 minsalary 输出
delimiter $
create procedure select_employees_min_salary_procedure(out minsalary double)
begin
	select
		min(salary) into minsalary
	from
		employees;
end $
delimiter ;

调用

存储调用需要使用 call关键字

格式

1

call 存储过程名(参数列表);

in 模式

1
2


-- 调用之前创建的 select_employees_procedure
call select_employees_procedure();

1
2
3
4


-- 调用存储过程
set @first_name = 'Adam';
set @last_name = 'Fripp';
call select_employee_salary_specify_name_procedure_in(@first_name, @last_name);

out 模式

1
2
3


-- 调用之前创建的 select_employees_min_salary_procedure
call select_employees_min_salary_procedure(@min_salary);
select @min_salary;

inout 模式

修改

删除

用户权限和管理

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 创建新数据库
CREATE DATABASE IF NOT EXISTS `new_database`;

-- 创建新用户并赋予密码
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'user_password';

-- 授权新用户对新数据库的所有权限
GRANT ALL PRIVILEGES ON `new_database`.* TO 'new_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

更新用户

1
2
3
4
5
6


RENAME USER 'maccms'@'%' TO 'maccms'@'maccms10';
FLUSH PRIVILEGES;


RENAME USER 'maccms'@'maccms10' TO 'maccms'@'%';
FLUSH PRIVILEGES;