MySql高级加常用命令合集

最新推荐文章于 2023-05-15 10:42:40 发布

echo程序鱼

最新推荐文章于 2023-05-15 10:42:40 发布

阅读量649

点赞数

分类专栏： linux mysql 笔记 php 文章标签： mysql mysql高级 mysql事务数据库常用命令

本文链接：https://blog.csdn.net/qq_38032300/article/details/80685417

版权

笔记同时被 3 个专栏收录

26 篇文章 0 订阅

订阅专栏

php

9 篇文章 0 订阅

订阅专栏

linux

7 篇文章 0 订阅

订阅专栏

大家看完别不说话，给点意见，或者有其他好用的东西分享一下，欢迎欢迎

ʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔ

听说看完不点赞的人将来都会敲这一条命令 (´-ω ･)▄︻┻┳══━一 rm -rf /

----------------------------------分割线----------------------------

支持中文的编码

gb2312 简体中文(一个汉字占用2字符)
gkb 简繁体中文(一个汉字占用2字符)
utf-8 简繁体中文(一个汉子占用3字符)

出现编码的地方：
1、文件本身的编码
2、<meta http-equiv="content-type" content="text/html";charset=utf-8> 这是个服务器说的我是html 格式，utf-8编码。
3、header("content-type:text/html;charset=utf-8");
4、mysql本省的编码
5、set names gbk;
6、set names utf8

MySQL命令
1、退出
exit;

2、设置客户端的编码
set names gkb;

3、查看数据库编码
show variables like 'character%';

4、进入库
use 库命

5、进入库(使用库)
use 库名;

6、查看库现存的所有表
show tables;

7、查看表结构
desc 表名;

8、建库
create database 库名;

9、删除库
drop database 库名;

10、建表
create table 表名
(
字段名数据类型约束,
字段名数据类型约束,
字段名数据类型约束
);

11、删除表
drop table 表名;

12、修改表结构
1)添加字段
alter table 表名 add column 字段名数据类型约束;
alter table 表名 add 字段名数据类型约束;
2)删除字段
alter table 表名 drop column 字段名;
alter table 表名 drop 字段名;
3)修改字段
alter table 表名 change 原字段新字段名数据类型约束;
4)修改表名
alter table 原表名 rename to 新表名;

13、常用的数据类型
int float char varchar text timestamp

14、约束
primary key 主键约束
unique 唯一约束
auto_increment 自增长约束
not null 非空约束
null 空约束
default 默认值
foreign key 外键约束

15、添加记录
insert into 表名(字段,字段...)values(值,值...);
insert into 表名 values(值,值...);

16、修改记录
update 表名 set 字段=值,字段=值...;
update 表名 set 字段=值,字段=值... where 条件;

17、删除记录
delete from 表名;
delete from 表名 where 条件;

18、清空表
delete from 表名; 不恢复自增长id 慢可恢复支持事务
truncate table 表名; 恢复自增长id 快不可恢复不支持事务

19、普通查询
select * from 表名;
select 字段,字段... from 表名;

20、条件查询
select * from 表名 where 条件;

21、排序
select * from 表名 order by 字段;#升序
select * from 表名 order by 字段 desc;#降序
select * from 表名 where 条件 order by 字段;

22、分组
select * from 表名 group by 字段;

23、聚合查询(统计查询)
select count(*) from 表名 where 条件;
select sum(字段) from 表名 where 条件;
select avg(字段) from 表名 where 条件;
select max(字段) from 表名 where 条件;
select min(字段) from 表名 where 条件;

24、模糊查询
通配符：%(代表任意长度的任意字符)、_(代表任意一个字符)
select * from 表名 where 字段 like '%_内容';

25、分页查询
select * from 表名 limit 起始值,条数;
select * from 表名 where 条件 order by 字段 limit 起始值,条数;

26、子查询(嵌套查询)
select * from 表 where 字段 in (select语句);
delete from 表 where 字段 in (select语句);
update 表 set 字段=值 where 字段 in (select语句);

27、内连接
select * from 表1 inner join 表2 on 关联的字段相等;

28、外连接
select * from 表1 left outer join 表2 on 关联的字段相等;
select * from 表1 right outer join 表2 on 关联的字段相等;

多表查询的使用场合
1、子查询：查询出来的结果中只有一个表的数据
使用场合：本来就写了一个单表查询，
但在条件中出现了另一张表中的数据
2、内连接：合并多张表的字段(查询出来的结果集中有多张表的数据)
使用场合：页面上一个功能中要显示多张表中的数据
3、外连接：合并多张表的字段(查询出来的结果集中有多张表的数据)
使用场拾：不用

内连接写法
一种
select * from 表1 inner join 表2 on 关联的字段相等
inner join 表3 on 关联的字段相等;
二种
select * from 表1,表2,表3... where 关联的字段相等;

MySQL高级
1、视图View
2、触发器Trigger
3、存储过程Procedure
4、T-SQL编程
5、索引Index
6、存储引擎Engine
7、事务Transaction

----------------------------------------------------------------1、视图View---------------------------------------------------------------------------------

***
***
视图View ***
***
作用：简化查询语句 ***
***
创建视图 ***
create view 视图名 ***
as ***
select语句; ***
***
查看视图 ***
1、show tables;
2、select table_schema,table_name from information_schema.views;

使用视图：把视图当表用

select title,content from newsview where ...;
注意：只对视图执行查询操作，不建议对视图执行增、删、改操作

删除视图
drop view 视图名;

创建视图的例子
create view newsView
as
select title,content from newsArticles a,newsTypes b where a.typeId=b.typeId order by articleId;

视图总结：
1、介绍、作用
2、创建、查看、使用、删除
3、注意：
1)视图中只能存储查询语句
2)不建议对视图执行增、删、改操作

使用场合：
有一个多表查询语句频繁的使用，那么就将这个语句存放到视图中

-----------------------------------------------------------------------2、触发器Trigger---------------------------------------------------------------------

***
***
触发器Trigger:触发器是数据库中的事件 ***
***
***
作用：当用户对表进行insert、update、delete操作的同时是否想要执行其他操作 ***

触发器的执行时间
before:先调用触发器，后执行insert、update、delete操作
()
after:先执行insert、update、delete操作，后调用触发器
()

触发器的类型
insert：当对表执行insert操作时，系统将自动调用触发器
update：当对表执行update操作时，系统将自动调用触发器
delete：当对表执行delete操作时，系统将自动调用触发器


修改mysql的定界符
delimiter(定界符)
delimiter 符号 --===========--delimiter 后面的符号变为了定界符


创建触发器--------------- 一旦创建永久存在
delimiter //
drop trigger if exists 触发器名//
create trigger 触发器名
before|after insert|update|delete
on 表名
for each row 监听每一条记录
begin
sql语句;
sql语句;
sql语句;
end//
delimiter ;

查看现存的触发器
1、information_schema库->triggers表
select trigger_schema,trigger_name from information_schema.triggers;

删除触发器
1、把触发器施加给的表删除
drop table 表名;
2、删除触发器所在的库
drop databases 库名;
3、删除触发器
drop trigger 触发器名;

例子一：修改bbsInfo表中的记录，
同时将userInfo表中记录全部删除
delimiter //
drop trigger if exists updateBbs//
create trigger updateBbs
after update
on bbsInfo
for each row
begin
delete from userInfo;
end//
delimiter ;

update bbsInfo set clickTimes=100 where bbsId=7;

例子二：
#部门表
create table bumen
(
bmId int auto_increment primary key,
bmName varchar(20) not null,
dateandtime timestamp default current_timestamp
);
#员工表
create table employee
(
empId int auto_increment primary key,
userName varchar(20) not null,
age int default 0,
dateandtime timestamp default current_timestamp
);
#统计表
create table tongji
(
bmCount int default 0,#部门总数
empCount int default 0#员工总数
);
insert into tongji values(0,0);

题目：
1、向employee表中添加或删除记录时，应修改tongji表中的empCount
2、向bumen表中添加或删除记录时，应修改tongji表中的bmCount

delimiter //
drop trigger if exists addem//
create trigger addem
after insert
on employee
for each row
begin
update tongji set empCount=empCount+1;
end//
delimiter ;

------------------------------------
delimiter //
drop trigger if exists addbm//
create trigger addbm
after insert
on bumen
for each row
begin
update tongji set bmCount=bmCount+1;
end//
delimiter ;

------------------------------------
delimiter //
drop trigger if exists delem//
create trigger delem
after delete
on employee
for each row
begin
update tongji set empCount=empCount-1;
end//
delimiter ;

-----------------------------------
delimiter //
drop trigger if exists delbm//
create trigger delbm
after delete
on bumen
for each row
begin
update tongji set bmCount=bmCount-1;
end//
delimiter ;
--------------------------

------------------------------------------------------------------------------3、存储过程Procedure----------------------------------------------------
存储过程Procedure：数据库中的自定义函数

创建存储过程
delimiter //
create procedure 存储过程名(参数名数据类型,参数名数据类型)
begin
sql语句;
sql语句;
sql语句;
end//
delimiter ;

调用存储过程
call 存储过程名(值,值...);

删除存储过程
1、删除存储过程所在的库
drop database 库名;
2、删除存储过程
drop procedure 存储过程名

查看存储过程
1、mysql库->proc表
select db,name from mysql.proc;

存储过程的输出参数(返回值)
存储过程参数的种类--------------|
输入参数：外->里 |
输出参数：里->外 |
|
|
|
|
例子一： |
delimiter // |
drop procedure if exists hello// |
create procedure hello() |
begin |
select * from bbsInfo; |
end// |
delimiter ; |
|
call hello(); |
--------------------- |
例子二： |
delimiter // |
drop procedure if exists hello// |
create procedure hello(bid int) |
begin |
select * from bbsInfo where bbsId=bid; |
end// |
delimiter ; |
|
call hello(7); |
------------------------ |
例子三： |
delimiter // |
drop procedure if exists hello// |
create procedure hello(t varchar(50)) |
begin |
select * from bbsInfo where title=t; |
end// |
delimiter ; |
call hello('全局文件的方法'); |
|
|
存储过程总结 |
1、介绍、作用 |
2、创建、调用、删除、查看 |
注意： |
1、有参数、有返回值 |
2、一次编写多次调用 |
3、用户必须显式的来调用 |


触发器与存储过程异同？ |
1、触发器由系统调用，存储过程由用户调用 |
2、触发器没有参数、没有返回值 |
存储过程有参数、有返回值 |

---------------------------------------------------------------------------4、T-SQL编程-------------------------------------------------------------------

Transaction-SQL编程 |
sql编程的编码只能出现在begin、end里 |
1、定义变量 |
declare 变量名数据类型; |
declare 变量名数据类型 default 值; |
注意：定义变量的代码必须出现在begin、end的开头 |
|
2、给变量赋值 |
给变量赋值 |
set 变量名 = 值; |
set 变量名 = (select 字段 from 表名 where 条件); |
3、运算 |
|-运算符 |
|-算数运算符: + - * / % |
|-比较运算符: < <= > >= = != <> |
|-逻辑运算符: and or not |
|-赋值运算符: = |

4、判断 |
if 条件 then |
sql 语句; |
elseif 条件 then |
sql 语句; |
elseif 条件 then |
sql 语句; |
else |
sql 语句; |
end if; |
|
delimiter // |
drop procedure if exists hello// |
create procedure hello(age int) |
begin |
if age < 18 then |
select '未成年' as msg; |
elseif age = 18 then |
select '刚成年' as msg; |
else |
select '己成年' as msg; |
end if; |
end// |
delimiter ; |
|
|
|
|
5、循环(了解) |
一种 |
while 条件 do |
sql语句; |
end while; |
二种 |
repeat |
sql语句; |
until 条件 end repeat; |
三种 |
别名:loop |
sql语句; |
end loop; |
|
|
delimiter // |
drop procedure if exists hello// |
create procedure hello() |
begin |
declare i int default 0; |
haha:loop |
select i; |
set i=i+1; |
if i=5 then |
leave haha; |
end if; |
end loop; |
end// |
delimiter ; |
|
|
delimiter // |
drop procedure if exists hello// |
create procedure hello() |
begin |
declare i int default 0; |
repeat |
select i; |
set i=i+1; |
until i=5 end repeat; |
end// |
delimiter ; |
|
|
delimiter // |
drop procedure if exists hello// |
create procedure hello() |
begin |
declare i int default 0; |
while i<5 do |
select i; |
set i=i+1; |
end while; |
end// |
delimiter ; |
||
||
T-SQL总结 ||
1、定义变量、赋值、运算、判断 ||
2、注意：sql编程代码只能出现在trigger、procedure ||
------------------------------------------------------ ||
==================================================================================
存储过程参数的种类 ||
输入参数：外->里 ||
输出参数：里->外 ||
||
定义存储过程的参数：输入、输出
delimiter // ||
drop procedure if exists hello// ||
create procedure hello(in a int,out b varchar(20)) ||
begin ||
逻辑处理; ||
逻辑处理; ||
逻辑处理; ||
将最终的结果赋给输出参数
end// ||
delimiter ; ||
||
call hello(10,@名); ||
select @名; ||
||
||
例子 ||
delimiter // ||
drop procedure if exists hello// ||
create procedure hello(id int,out t varchar(50)) ||
begin ||
set t = (select title from bbsInfo where bbsId=id); ||
end// ||
delimiter ; ||
||
call hello(7,@aaa); ||
select @aaa; ||
||
||
存储过程参数注意： ||
1、可以有多个参数 ||
2、可以有多个输入参数、可以有多个输出参数 ||
3、多个参数在定义上没有先后顺序 ||
4、输出参数相当于函数的返回值 ||
||
||
输出参数与函数返回值的异同？ ||
相同：将里面的数据传递到外面来 ||
不同：输出参数可以有多个，但返回值只能有一个 ||
====================================================================================




--------------------------------------------------------------------------5、索引Index------------------------------------------------------
索引Index

作用：提高表的where条件查询语句的执行速度

select * from 表 where 字段 < 值
注意：-----------------------------------------------------------
1、索引都是添加给字段的 |
2、经常充当where条件的字段，应该添加索引 |
|
-查看表的索引信息 |
show index from 表名 |
|
-删除索引 |
drop index 索引名 on 表名; |
|
|
|
-索引分类 |
1、主键索引:非空、唯一、一个表最多只能加一个主键索引 |
建表的同时直接指定主键 |
|
2、唯一索引:唯一。 |
建表的同时直接指定唯一约束 |
create unique index 索引名 on 表名(字段名); |
例子：create unique index t_index on bbsInfo(title); |
3、普通索引:没限制 |
create index 索引名 on 表名(字段名); |
例子：create index t_index on bbsInfo(title); |
4、全文索引:text类型的字段 |
create fulltext index 索引名 on 表名(字段名); |
例子：create fulltext index t_index on bbsInfo(title); |
|
|
索引总结 |
1、介绍、作用 |
2、索引分类 |
3、添加索引、查看表的索引、删除表的索引 |
|
注意：--------------------------------------------------|
1、索引的作用
2、索引分类(选择索引的顺序：全文、主键、唯一)
3、什么样的字段应该添加索引

-------------------------------------------------------------------6、存储引擎Engine---------------------------------------------------------
6、存储引擎(表的类型)Engine

作用：临时改变数据库的性能

存储引擎种类
1、ISAM: 查询速度快、不支持外键、不支持事务、支持全文索引
2、MyISAM: ISAM升级版是表级锁
3、Memory: 速度快、所有数据驻留在内存中、数据管理不稳定、断电后数据全部丢失
4、InnoDB: 速度较慢、支持外键、支持事务、不支持全文索引是行级锁

可用的存储引擎
MyISAM: 内容管理系统
InnoDB: 技术型网站(bbs、blog、webo、mail、oa.......)

查看表的存储引擎
show create table 表名;

设置数据库的存储引擎
1、修改my.ini配置文件
default-storage-engine=INNODB
修改完后，必须重启mysql服务
2、建表的同时，直接指定存储引擎
MySQL 5.0 以前的版本
create table 表名
(
字段数据类型约束,
字段数据类型约束,
字段数据类型约束
)type=存储引擎;

MySQL 5.0以后的版本
create table 表名
(
字段数据类型约束,
字段数据类型约束,
字段数据类型约束
)engine = MyISAM;

例子: 建表同时指定表的存储引擎
create table newsInfo
(
newsId int auto_increment primary key,
title varchar(50) not null,
content text not null,
dateandtime time
)engine= MyISAM;

给content字段添加一个全文索引

---------------------------------------------------------7、事务Transaction---------------------------------------------------------
7、事务Transaction(安全机制)

作用：保证数据的完整性

特点: 原子性、一致性、隔离性、持久性

开启事务
start transaction;

结束事务
commit; 提交事务(操作成功)
rollback; 回滚事件(操作失败)

设置回滚点
savepoint 名; 设置一个回滚点
rollback to 名; 回滚到指定的回滚点

例子一
start transaction;

delete from bbsInfo;

rollback;|commit;

例子二
start transaction;
delete from bbsInfo;
savepoint hello;
delete from newsarticles;
rollback to hello;
.
.
....

不支持事务的语句有：
１、areate、alte、drop、truncate table

事务总结
1、介绍、作用
2、特点
3、开启事务、结束事务、设置回滚点、

-------------------------------------------------------------

数据库的备份与还原
1、数据库的备份
myssqldump -h主机地址 -u用户名 -p密码库名>d:/hello.sql(路径)

2、数据库还原
1)登录mysql数据库
2)创建一个新库，并进入该库
3)执行如下语句
source d://hello.sql

-----------------------------------------

数据库优化
1、给字段选择合适的数据类型
1)能用数字类型就不用字符串类型
2)注意char、varchar、text区别
char: 长度范围小长度是定长
varchar: 长度范围打(0~8000) 长度能伸缩
text: 长度没限制

建议: 普通字符串(varchar)、内容字段(text)
2、为每张表都添加一个主键约束
3、主键最好是短主键、最好是数字类型
4、合理的为字段添加外键约束
5、不要使用select * from 表名
6、用数字类型的字段充当where条件
7、避免where 条件中的额字段发生数据类型的转换
select * from 表名 where age = '30';
8、给字段合理的添加索引(给充当where条件的字段加索引)
9、避免在添加索引的字段上使用聚合函数，索引会失效
10、尽可能少用like模糊查询(效率低)
11、尽可能的少用子查询(效率低)
select * from 表 where 字段 in (select语句);
12、拆分表
1)拆分字段
2)拆分记录
13、给网站的页面加缓存
14、网站静态化
15、mysql功能: 主从数据库
16、负载均衡

2、MyISAM: ISAM升级版是表级锁

4、InnoDB: 速度较慢、支持外键、支持事务、不支持全文索引是行级锁

----------------------------------分割线----------------------------

听说看完不点赞的人将来都会敲这一条命令 (´-ω ･)▄︻┻┳══━一 rm -rf /

大家看完别不说话，给点意见，或者有其他好用的东西分享一下，欢迎欢迎

echo程序鱼

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySql高级加常用命令合集

大家看完别不说话，给点意见，或者有其他好用的东西分享一下，欢迎欢迎ʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•ʔʕ•̫͡ʕ•̫͡ʕ•̫͡ʕ•̫͡•ʔ•̫͡•ʔ•̫͡•...
复制链接

扫一扫