MySql

https://www.mysql.com/cn/

DML（Data Manipulation Language）

数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge

DDL（Data Definition Language）

数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：create、alter、drop、truncate、comment、grant、revoke

控制台登录: mysql -u root -p
设置密码: set password = password(‘123456’);

授权远程访问:
grant all privileges on . to ‘root’ @‘’%’ identified by ’123456’;

授权后刷新权限列表:
flush privileges;

mysql8* 登录无权限问题

mysqld --console --skip-grant-tables --sharedmemory

Docker bash:vi:command not found

bash: vi: command not found
vim没有安装 使用安装命令
apt-get install vim
一般会出现 E: Unable to locate package vim
需要 apt-get update 
然后 apt-get install vim

查找mysql安装目录

// 查看mysql安装路径
whereis mysql
// 查询运行文件所在路径
which mysql 


// 用户登录
mysql -h 127.0.0.1 -u root -p
mysql -u root -p

create user 'username'@'%' identified by '123456';
grant all on *.* to username@'%';
flush privileges;

创建用户

// user:用户名 host:指定主机可以是localhost 可以远程主机,可以通配符%
create user 'username'@'host' identified by 'password'
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

授权

// privileges: 用户权限 -- select,insert,update等 所有ALL
// databasename:数据库名
// tablename: 表明 所有数据库和表可用 *
GRANT privileges ON databasename.tablename TO 'username'@'host'
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%';
//可以授权用户
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
// 刷新权限
flush privileges;

更改密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
// 如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");

撤销权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';
REVOKE SELECT ON *.* FROM 'pig'@'%';
//删除用户
drop user 'username'@'host';

索引

作用:

帮助 mysql 高效查询的有序数据结构
无索引情况下按照数据库存储顺序依次进行查询越靠后的数据所需时间越长

优势:

提高数据检索效率,降低数据库的 IO 成本
通过索引对数据排序,降低排序成本,降低 CPU 消耗

缺点:

索引也相当于一张表保存了主键与索引字段并指向实体记录,占用空间
虽然提高查询效率但是降低了表更新的速度, 进行增删改操作时不仅要保存数据
还需要保存索引更新索引信息

结构:

索引在 mysql 存储引擎层实现,所以没中存储引擎的索引不完全相同 mysql 提供 4 种索引

    *  btree 索引:   最常见的索引类型,大部分索引都支持 类似二叉树算法
    * hash 索引: 只有 Memory 引擎支持,场景简单
    * R-tree 索引 (空间索引): MyISAM引擎的特殊索引类型 , 主要用于地理空间数据类型
    * Full-text (全文索引) : 特殊索引类型,用于全文索引

分类:
- 单值索引:
  - 一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:
  - 索引列的值必须唯一,允许包含多个空值
- 复合索引:
  - 一个索引包含多个列
语法:
- 创建表时同事创建, 也可以新增索引
- 创建索引:
  - 如果当前字段为主键当前字段默认有索引

-- 创建索引--
create index idx_city_name on city(city_name);
-- 查看索引--
show index from city;
-- 删除索引 --
drop index idx_city_name on city;
-- alter命令
   alter  table  tb_name  add  primary  key(column_list);
-- 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	 alter  table  tb_name  add  unique index_name(column_list);
-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	 alter  table  tb_name  add  index index_name(column_list);
-- 添加普通索引， 索引值可以出现多次。
	 alter  table  tb_name  add  fulltext  index_name(column_list);
-- 该语句指定了索引为FULLTEXT， 用于全文索引

设计原则:

对查询量比较高且数据量大的表建立索引

索引字段选择最佳列应从 where 子句条件中提取, 如果 where 组合较多挑选最常用过滤最好的列

唯一索引区分度越高索引效率越高

索引可以提升查询效率但是索引越多维护索引代价越高,对于插入更新删除等 DML 操作频繁的表
来说,索引过多维护代价越高效率越低,

短索引索引创建后也是硬盘存储,提升索引访问 I/O 效率也能提升访问效率
索引字段总长度较短就可以存储更多索引值可以提升访问索引效率

利用左前缀 , N 个列组合的一个索引相当于 N 个索引,如果查询 where 使用了索引的前几个字段
相当于 a ab abc abcd 顺序来使用索引查询

临时表

create temporary table xxxx (   select * from oldtable);
drop temporary table if exists xxxx;

视图

视图为虚拟存在的表,数据为使用视图时动态生成的,相当于视图封装了一条 select 语句
可以限制某列可见的访问权限
创建:

create view view_name as select_statement

查询:

select * from view_name

更新:

update view view_name set column_name = ‘xxx’ where column_id = 1;

修改:

alter view_name [(column_list)] as select_statement

查看视图:

show tables; 查看表以及视图

查看视图创建语句

show create view view_name;

删除:

drop view if exists view_name;

### 存储过程和存储函数

存储过程和存储函数

都是经过编译且存储在数据库的一段 sql 语句的集合
可以简化开发,减少数据库和服务器的传输,提高数据处理效率
区别:

函数必须有返回值,存储过程没有

创建:

需要注意使用 delimiter & delimiter ; 来更换结束符

-- 创建:
create procedure p_name 
begin 
	—sql语句
end 
-- 调用:
call procedure_name();
--查看: 
select name from mysql.proc where `db` = demo_01’;  --报错
show procedure status;
show create procedure pro_name; -- 查看创建语句
--删除:  
drop procedure if exists pro_name;

语法:

变量: declare var_name [,,,,,] type [default value]

在 begin end 中使用

声明: declare num int default 99 ;

赋值: set num = 20;

select count(*) into num from tb_name;

条件判断: 
if num >= 22 then
set num = 99;
else if num >= 10 then 
set num = 66;
end if;
use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp()
begin
declare num int default 99 ;
if num &gt;= 22 then
set num = 99;
end if;
select num;
end\)

DELIMITER ;

call pp();
参数传递:

in:输入参数  out:输出参数   inout:都可以 默认输入参数

create procedure pro_name ( [in/out/inout] 参数名 参数类型)
in:输入参数:

use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp(in num int)
begin
if num &gt;= 22 then
set num = 99;
end if;
select num;
end\)

DELIMITER ;

call pp(33);
out:输出参数:

@var :代表当前用户会话变量,当前会话关闭后变量释放   @@var 代表系统变量

use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp(in num int , out des varchar(100))
begin
if num &gt;= 22 then
set des = 11 ;
end if;
select num;
end\)

DELIMITER ;

call pp(33,@des);

select @des;
case 结构:

case case_val

when when_val then statement_list

when when_val then statement_list

end case;

case

case search_condition then statement_list

end case;
use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp(mon int )
begin
declare result varchar(10);
case
when mon &gt;= 1 and mon &lt;= 3 then
set result = '第1季度';
when mon &gt;= 4 and mon &lt;= 6 then
set result = '第2季度';
when mon &gt;= 7 and mon &lt;= 8 then
set result = '第3季度';
else 
set result = '第4季度';
end case;
select concat('传值为:',mon,'结果为:',result) as content;
end\)

DELIMITER ;

call pp(4);
while循环:

满足条件进入循环

use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp(n int )
begin
declare total int default 0;
declare num int default 1;
while num &lt;= n do
set total = total + num;
set num = num + 1;
end while;
select total;
end\)

DELIMITER ;

call pp(3);
repeat 循环:

满足条件退出循环

use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp(n int )
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n = 0 
end repeat;
select total;
end\)

DELIMITER ;

call pp(3);
loop 循环语句 leave 退出语句 :

use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp(n int )
begin
declare total int default 0;
ccc:loop
set total = total + n;
set n = n - 1;
if n &lt; 0 then
leave ccc;
end if;
end loop ccc;
select total;
end\)

DELIMITER ;

call pp(3);
游标:

用来存储查询结果集的数据类型.

声明:

declare cursor_name cursor for select_statement;

打开游标:

open cursor_name;

fetch 游标:

每 fetch 一次 数据再结果集+1

fetch cursor_name into var_name [, var_name] ….

close 游标:

close cursor_name;
use demo_01;

DROP procedure IF EXISTS pp;

DELIMITER \(
create procedure pp()
begin
declare has_data int default 1;
declare cid int(11);
declare cname varchar(50);
declare countryid int(11); 
declare result cursor for select * from city;
declare exit handler for not found set has_data = 0;
open result;
repeat
fetch result into cid, cname,countryid;
select concat('id=',cid,',name=',cname,',countryid=',countryid);
until has_data = 0
end repeat;
close result;
end\)

DELIMITER ;

call pp();

create function fun_name(  [ param type] )
return type
begin
…
end ;

SET GLOBAL log_bin_trust_function_creators = 1
DELIMITER $
create function m_fun(cid int)

returns int
begin
declare tmpid int;
select count(*) into tmpid from city where country_id = cid;
return tmpid;
end $
DELIMITER ;
select m_fun(1);

触发器:
再表的增删改前或后触发

存储引擎:

show engines; 查看数据库所支持的存储引擎

存储引擎是基于每张表的,

InnoDB 和 BDB 提供事务安全锁 其他引擎提供非安全锁

InnoDB 为 mysql 默认引擎 , MyISAM 为 5.5 版本之前默认的 

MyISAM: 不支持事务不支持外键访问速度快对数据完整性要求不高可使用

查看 sql 执行频率:

语法:

show [session | gloable] status ; 默认 session
整个数据库操作的各种增删改查等的次数
show status like 'Com___';
Innodb使用的操作数量
show global status like 'Innodb_rows_%';

定位低效率执行 sql:

1. 慢查询日志

2. show processlist ;   查看 mysql 进行的线程 包含状态 锁表等.

id: 当前登录用户的 id 可以用 select connection_id(); 查看当前登录的 id 多少
user 登录的用户
db: 使用的数据库
command: 执行的操作类型
time:耗时
state: 当前 sql 链接语句的状态, 描述的是语句执行的一个状态
info: 显示 sql 的语句

explain 分析执行计划:

	explain select * from city where city_id != 0;

	id: select 查询的序列号 操作表的顺序 id 越大优先级越高 越先执行

	select_type: 查询属性 simple:简单表不使用表连接或子查询, primary:主查询 ,union: union 的第二个或者后面的子查询

		subquery: 子查询的第一个 select

	type: 链接类型, 性能由好到坏顺序为  system > const > eq_ref > ref > ref_or_null > index_merge > 

		index_subquery > range > index > all

	possible_keys: 查询可能使用的索引

	key:  实际使用的索引

	key_len : 索引长度

	rows :扫描行的数量

posted @ 2024-12-17 15:53 若云阅读(59) 评论(0) 收藏举报

刷新页面返回顶部

若云

加油吧少年