文章目录
MySQL命令大全
Windows下大小写不敏感,但是Linux下是敏感的;根据阿里开发手册,MySQL建表规约,为避免节外生枝,建议:
数据库名,表名,字段名,一律不要出现大写。
小提示:想查看哪个关键字包含哪些指令,Mysql command line client中输入help + 关键字 + 分号; 即可查看。(忘了分号能查到算我输)
以下是最常用的,为人熟知的,其余可自行查找~
一. SHOW
show databases; 返回可用数据库列表
show tables from 库名; 返回当前数据库内可用表的列表
show columns from 表名 (from 库名); 返回该表的所有列信息
show status; 显示服务器状态信息
show create database 库名; 显示创建数据库的语句
show create table 表名; 显示创建表的语句
show create view viewname; 显示创建视图的语句
show grants; 显示授予用户的权限
show errors; show warnings; 显示服务器错误或警告信息
二. 查询 SELECT
select 列名 from 表名; 查询表中某列
select 列名1,列名2,… from 表名; 查询多个列
select * from 表名; 查询所有列
select distinct 列名 from 表名; 查询某一列不重复的数据
select * from 表名 group by 列名; 查询某列不重复数据且显示所有数据
select * from 表名 limit 4; 查找表的前4条记录
select * from 表名 limit 3, 5; 查找从第4条记录开始的5条行数据
select * from 库名.表名 ; 查找对应数据库表数据
order by 子句:
select 列名 from 表名 order by 列名; 查询该列数据且按字母顺序排序
select 列名1,列名2,列名3 from 表名 order by 列名1,列名3; 查询3列的结果并按照先列名1后列名2对结果排序
select id, price, name from product order by price ASC(或不写) / desc ; 查询结果按照升序/降序排列。
where子句:
指定搜索条件过滤。
select id, name from user where id = 1; 查找两列数据但只返回id为1的行数据。
操作符有:
= <>(不等于) != < <= > >= between(左闭右闭)
is null 空值检查
select id, name from user where name is null;
and / or
select id, name ,age from user
where name = ‘zhangsan’ and age = 10;
or 匹配任一条件都行
select id, name ,age from user
where name = ‘zhangsan’ or age = 10;
注意这里优先级and比or高,某些场景下需要先计算or时记得相关语句加括号。
in
select * from user where id in(1001,1002,1003) order by name;
按名字排序查询id在100,1002,1003的行信息。
in 比 or 更高效。
not
否定后面的条件
select * from user where id not in(1001,1002,1003) order by name;
通配符
%
表示任意字符出现任意次数。
select name from user where name like ‘zh%’; 查找所有以zh开头的名字
‘%xxx%’ 表示任意包含xxx的值
‘sss%eee’ 表示以sss开头eee结尾的任意值
_ 下划线
只匹配单个字符。功能与 % 类似。
正则表达式
REGEXP 后跟的语句为正则表达式。
. 为正则表达式中特殊的字符
表示匹配任意一个字符。
| 表示 or操作
[ ] 表示匹配括号内多个值的or操作
[0 – 9] 表示匹配0 – 9 范围内的数字
特殊字符匹配 字符前加 \\ 转义
匹配字符类
匹配多个实例
定位符
concat 拼接
拼接字符串。
select concat(‘My’, ‘S’, ‘QL’); 返回 ‘MySQL’
SELECT CONCAT(‘My’, NULL, ‘QL’); 返回 NULL
SELECT CONCAT(14.3); 返回 ‘14.3’
ltrim rtrim trim 删除左侧/右侧/两侧多余空格
alias 别名
as 关键字替换别名。
select user_name as name from user; 将结果集user_name 列名替换为name 返回
别名用作计算
select price, quantity, quantity * price as total_price from products;
支持算术操作符 + – * /
三. 数据处理函数
文本处理函数
> Soundex() 匹配发音类似的值
日期时间处理函数
注意日期格式必须为 yyyy-mm-dd 。
使用Date() 函数, 仅提取列的日期部分,
select id, name from user where Date(birth_date) = ‘2021-09-09’;
还有更多日期函数,此处略。
数值处理函数
四. 汇总数据
聚集函数
来一条组合语句:
select count(*) as num_counts,
min(price) as price_min,
max(price) as price_max,
avg(price) as price_avg
from products;
五. 分组数据
分组是在group by 子句中建立的。
group by
例子:
select 字段,count(*) as total
from 表名
group by 分组字段
having
再加上having子句进行分组筛选。注意having子句会先查询到所有数据再进行过滤。涉及到SQL优化。
select studentID as 学员编号,courseID as 内部测试,avg(score) as 内部测试平均成绩
from score
group by studentID,courseID
having avg(score)>60
分组+排序
一般使用分组时,也应该有order by 子句来保证正确排序。
注意select子句中的顺序:
select > from > where > group by > having > order by
> limit
六. 使用子查询
select * from t1 where col1 = (select col1 from t2);
select id, name, price from db_goods
where price > (select avg(price) from db_goods)
order by price desc;
查询商品id,名字,价格,且价格大于所有商品价格的均价,按降序排序。
子查询涉及到 ANY , ALL, IN 这几个关键字。
七. 联结表
笛卡尔积,等值连接,内连接,外连接,左连接,右连接
应该保证所有联结都有where子句,以减少返回的数据量。
八. 组合查询
2种情况:
① 单个查询中从不同的表返回类似结构的数据;
② 对单个表执行多个查询,按单个查询返回数据。
使用union创建组合查询
多条语句,每句之间加上关键字union即可。
注意union从结果集中自动去除了重复的行!!!如果想阻止这一行为,可以使用union all代替union。
组合查询排序
只允许写一条group by,且该子句必须出现在最后一条select语句。
九. 插入数据
插入完整的行
insert into 表名 values(v1, v2, null, v4); 要求每列必须给定值,没有的用null代替
给出了列名:(推荐此方式)
insert into user(id, name, age) values(‘001’, ‘zhangsan’, 14)
后面的值完全以前面列名为准,不必像第一种必须填充所有列值。
插入多个行
方法:
① 可以使用多条insert语句,每条语句用一个分号结束。
② 使用一条语句,前提是每条insert语句的列名和次序相同,就可以只写一条insert和多个values.
插入检索出的结果集
由insert语句和select语句组成。
insert into new1(col1, col2, col3 …)
select c1, c2, c3… from new2;
new2空表也不会爆报错。语句是合法的。
十. 更新和删除数据
更新—update
- 更新特定行
- 更新所有行
update user set user_name = ‘zhangan’
where user_id = 001;
注意一旦没有where子句,将更新所有行,想必不是我们愿意看到的。
update user set user_name = ‘zhangan’,
user_age = 18
where user_id = 001;
注意更新多列时加逗号,最后一列不加。
十一. 删除数据
删除—delete
1、删除特定行
2、删除所有行
同样不要省略where子句。否则删除所有行。
delete from user where id = 1001; 删除id为1001的客户信息。
要删除指定的列,请使用update。
delete还可用于删除view。
delete 、 drop 、truncate
delete 删除表的内容,但不删除表本身结构。执行时走事务,触发trigger;磁盘所占空间不会变小,空间未被释放,下次插入可重用;delete后自增序列不会重置为1(但重启数据库后会被置为1)
delete from 表名 where xxx;
truncate table 表名; 快速清空一个表,并create
不走事务,执行立即生效,无法找回;重置自增序列为1.
小心使用 truncate !!!
drop table 表名;
同truncate,执行立即生效,无法找回!!!
只保留依赖于该表的存储过程/函数,但变为invalid状态。
执行速度:
drop > truncate >> delete
推荐博客:https://blog.csdn.net/qq_39390545/article/details/107144859
需求:
①删除表,用drop;
②保留表将数据删除,与事务无关,用truncate;
③和事务有关,或想触发trigger,用delete
推荐博客2:https://www.cnblogs.com/zhizhao/p/7825469.html
十二. 创建和操纵表
创建
create table customers
{
cust_id int not null auto_increment,
cust_name char(50) not null,
cust_sex char(50) not null default ‘男’,
cust_addr varchar(50) null,
…
primary key (cust_id)
} engine=innodb;
更新表(表必须存在)
追加新字段
alter table 表名
add newCol char(20);
给表新增一个名为newCol的列。
添加删除外键
alter table 表名 add CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表名(主键列名);
alter table 表名 drop foreign key 外键名;
删除列
alter table 表名 drop column 列名;
修改列属性
alter table 表名 modify 列名 类型(长度) COMMENT ‘注释’;
添加/删除某字段的not null约束
alter table 表名 modify 列名 类型(长度) not null/null COMMENT ‘注释’;
添加新索引
ALTER TABLE 表名 ADD 索引类型 INDEX 索引名 (列名);
索引名可以省略不写
删除表
drop table customer2;
重命名表
rename table customer1 to customer2;
多个表重命名:
rename table customer1 to customer2,
cus to cus1,
new1 to new2;
十三. 视图view
为什么用视图?
视图规则和限制
视图创建
视图与select语句家族结合使用。
更新视图
一般而言,视图用来检索,而不是更新。
存在以下情况时视图不能更新:
- 分组 (使用group by 和 having)
- 联结
- 子查询
- 并
- 聚集函数
- distinct
- 导出(计算)列
十四. 存储过程procedure
为什么要使用存储过程?因为很多时候,根据业务,对数据库的操作不是一两条简单语句就可以完成的。需要针对很多表的很多sql语句,还可能动态变化。这时候就需要用到存储过程了。
通俗来讲,存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。
使用它的理由:
存储过程的编写和访问是区别开来的。所以即使不能自己编写存储过程,也能执行别的存储过程。
执行存储过程
关键字 call
call productpricing (@pricelow,
@pricehigh,
@priceaverage) ;
执行名为productpricing的存储过程,返回产品的最低、最高和平均价格。
创建存储过程
关键字 create procedure
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;
执行此存储结果:
call productpricing() ;
创建存储过程2:
相应地,调用磁存储过程也必须指定三个参数:
call productpricing (@pricelow,
@pricehigh,
@priceaverage) ;
删除存储过程
drop procedure productpricing ;
注意只有存储过程名,而没有后面的括号()。
考虑到存储过程可能不存在,使用时加上 if exists
drop procedure if exists;
检查存储过程
显示存储过程语句:
show create procedure 存储过程名;
显示详细信息的存储过程列表:
show procedure status;
十五. 使用游标cursor
游标主要用于交互式应用,用户需要滚动屏幕上的数据,并对数据浏览或作出更改。
只能用于存储过程
使用游标
创建游标
打开/关闭游标
open ordernumbers ;
游标处理完后,关闭游标:
close ordernumbers ;
十六. 触发器
希望某条或某些语句在事件发生时自动执行,(表更改时自动处理)这就是触发器。
创建触发器
需要给出4个信息:
- 唯一的触发器名;
- 触发器关联的表;
- 触发器应该响应的活动(增、删、改)
- 触发器何时执行(处理之前或之后)
仅支持表
每个表最多支持6个触发器
删除触发器
drop trigger newproduct ;
使用触发器
insert触发器
在insert语句执行之前或之后执行。
- 在insert触发器代码内,可饮用一个名为new的虚拟表,访问被插入的行;
- 在before insert触发器中,new中的值也可以被更新(允许更改被插入的值);
- 对于auto_increment 列,new 在insert执行之前包含0.在insert执行之后包含新的自动生成值。
delete触发器
DELETE触发器在DELETE语句执行之前或之后执行
-
在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访 问被删除的行
-
OLD中的值全都是只读的,不能更新。
例子:
update触发器
UPDATE触发器在UPDATE语句执行之前或之后执行。
- 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问 以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新 更新的值;
- 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改 将要用于UPDATE语句中的值);
- OLD中的值全都是只读的,不能更新。
相关介绍:
与其他DBMS相比,MySQL 5中支持的触发器相当初级。未来的 MySQL版本中有一些改进和增强触发器支持的计划。
创建触发器可能需要特殊的安全访问权限,但是,触发器的执行 是自动的。如果INSERT、UPDATE或DELETE语句能够执行,则相关 的触发器也能执行。
应该用触发器来保证数据的一致性(大小写、格式等)。在触发器 中执行这种类型的处理的优点是它总是进行这种处理,而且是透 明地进行,与客户机应用无关。
触发器的一种非常有意义的使用是创建审计跟踪。使用触发器, 把更改(如果需要,甚至还有之前和之后的状态)记录到另一个 表非常容易。
遗憾的是,MySQL触发器中不支持CALL语句。这表示不能从触发 器内调用存储过程。所需的存储过程代码需要复制到触发器内。
十七. 管理事务处理
事务处理
事务处理(transaction processing)可以用来维护数据库的完整性,它 保证成批的MySQL操作要么完全执行,要么完全不执行。
——>
事务处理是一种 机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完 整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们 或者作为整体执行,或者完全不执行(除非明确指示)。如果没有错误发 生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤 销)以恢复数据库到某个已知且安全的状态。
事务的几个关键词:
事务(transaction)指一组SQL语句;
回退(rollback)指撤销指定SQL语句的过程;
提交(commit)指将未存储的SQL语句结果写入数据库表;
保留点(savepoint)指事务处理中设置的临时占位符(place-holder),你可以对它发布回退(与回退整个事务处理不同)。
控制事务处理
MySQL使用如下语句来标识事务的开始:
start transaction
rollback 回滚
例子:
select * from ordertotals;
start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;
select * from ordertotals;
这个例子从显示ordertotals表的内 容开始。首先执行一条SELECT以显示该表不为空。然后开始一 个事务处理,用一条DELETE语句删除ordertotals中的所有行。另一条 SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退 START TRANSACTION之后的所有语句,最后一条SELECT语句显示该表不为 空
- rollback只能在开启一个事务之后使用;
- 可回退的只能是增、删、改,不能回退create、select或drop操作。
commit 提交
事务的提交需要主动进行。
举例:
start transaction;
delete from orderitems where order_num = 2000;
delete from orders where order_num = 2000;
commit;
这个例子中,从系统中完全删除订单20010。因为涉及更新 两个数据库表orders和orderItems,所以使用事务处理块来 保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如 果第一条DELETE起作用,但第二条失败,则DELETE不会提交(实际上, 它是被自动撤销的)。
执行commit、rollback后事务自动关闭。
使用保留点回退部分事务
针对更复杂的事务处理,可能需要部分提交或回退。
为了支持回退部分事务处理,必须能在事务处理块中合适的位置放 置占位符。这样,如果需要回退,可以回退到某个占位符。
save point
更改默认提交行为
mysql默认自动提交所有更改。如果想控制它,则需要执行以下:
set autocommit=0;
autocommit标志决定是否自动提交更改。(直到autocommit设置为真mysql才自动提交更改。)
该标志针对每个mysql连接(非服务器)。
十八. 全球化和本地化
数据库表被用来存储和检索数据。不同的语言和字符集需要以不同 的方式存储和检索。因此,MySQL需要适应不同的字符集(不同的字母 和字符),适应不同的排序和检索数据的方法。
重要术语:
- 字符集 (字母和符号的集合)
- 编码 (某个字符集的内部表示)
- 校对 (规定字符如何比较的指令)
需要明白的是,使用何种字符集和校对的决定在服务器、数据库、和表级进行,我们只需要一开始决定好字符集即可。
查看字符集列表
show character set;
显示所有可用字符集信息。
show collation;
显示所有可用的校对,及它们适用的字符集。
通常系统管理在安装时定义一个默认的字符集和校对。此外,也可 以在创建数据库时,指定默认的字符集和校对。为了确定所用的字符集 和校对,可以使用以下语句:
show variables like ‘character%’;
show variables like ‘collation%’;
可以在创建表时指定字符集:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
`runoob_id` INT UNSIGNED AUTO_INCREMENT,
`runoob_title` VARCHAR(100) NOT NULL,
`runoob_author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
默认字符集utf-8。
十九. 安全管理
MySQL服务器的安全基础是:用户应该对他们需要的数据具有适当 的访问权,既不能多也不能少。
考虑以下内容:
多数用户只需要对表进行读和写,但少数用户甚至需要能创建和 删除表;
某些用户需要读表,但可能不需要更新表;
你可能想允许用户添加数据,但不允许他们删除数据;
某些用户(管理员)可能需要处理用户账号的权限,但多数用户 不需要;
你可能想让用户通过存储过程访问数据,但不允许他们直接访问 数据;
你可能想根据用户登录的地点限制对某些功能的访问。
即我们需要给用户提供他们需要的访问权限,不能多也不能少。管理访问控制需要创建和管理用户账号。
管理用户
查看用户账户
use mysql;
select user from user;
// mysql有一个user的表,包含所有用户账号。表中有user的列,包含所有用户登录名。
创建用户账户
create user ben (identified by password);
// 括号部分可选,给出了口令作为加密。
grant、insert也可以创建用户账号:
参考博客:https://www.cnblogs.com/perseverancevictory/p/4223867.html
删除用户账户
drop user 账户名; 删除用户名及所有相关权限。
设置、撤销权限grant、revoke
grant要求给出以下信息:
- 要授予的权限
- 被授予访问权限的数据库或表;
- 用户名
例如:
grant select on db1.* to lisi ; 授权用户李四数据库db1的所有表访问的权限。
show grants for lisi; 显示用户权限的更改
GRANT的反操作为REVOKE,用它来撤销特定的权限。
例子:
revoke select on db1.* from lisi;
撤销用户lisi对db1数据库所有表的访问权限。
被 撤销的访问权限必须存在,否则会出错。
二十. 数据库维护
备份数据
使用命令行实用程序mysqldump转储所有数据库内容到某个外部 文件。在进行常规备份前这个实用程序应该正常运行,以便能正 确地备份转储文件。
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据 (并非所有数据库引擎都支持这个实用程序)。
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所 有数据到某个外部文件。这两条语句都接受将要创建的系统文件 名,此系统文件必须不存在,否则会出错。数据可以用RESTORE TABLE来复原。
维护
语句:
- analyze table 表名; 检查表键是否正确。
- check table 表名; 检查表
诊断启动问题
服务器启动问题通常在对MySQL配置或服务器本身进行更改时出 现。MySQL在这个问题发生时报告错误,但由于多数MySQL服务器是作 为系统进程或服务自动启动的,这些消息可能看不到。
在排除系统启动问题时,首先应该尽量用手动启动服务器。MySQL 服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld 命令行选项:
–help显示帮助——一个选项列表;
–safe-mode装载减去某些最佳配置的服务器;
–verbose显示全文本消息(为获得更详细的帮助消息与–help 联合使用);
–version显示版本信息然后退出。
查看日志文件
MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下 几种。
错误日志。它包含启动和关闭问题以及任意关键错误的细节。此 日志通常名为hostname.err,位于data目录中。此日志名可用 –log-error命令行选项更改。
查询日志。它记录所有MySQL活动,在诊断问题时非常有用。此 日志文件可能会很快地变得非常大,因此不应该长期使用它。此 日志通常名为hostname.log,位于data目录中。此名字可以用 –log命令行选项更改。
二进制日志。它记录更新过数据(或者可能更新过数据)的所有 语句。此日志通常名为hostname-bin,位于data目录内。此名字 可以用–log-bin命令行选项更改。
缓慢查询日志。顾名思义,此日志记录执行缓慢的任何查询。这 个日志在确定数据库何处需要优化很有用。此日志通常名为 hostname-slow.log ,位于 data 目录中。此名字可以用 –log-slow-queries命令行选项更改。
在使用日志时,可用FLUSH LOGS语句来刷新和重新开始所有日志文 件。
二十一. 改善性能
- 遵循MySQL硬件建议
- 生产环境运行在自己专用服务器上
- 根据需求调整内存分配、缓冲区大小等(show variables、show status 等查看当前设置)
- 作为多用户多线程DBMS,遇到性能不良,用show processlist 显示所有活动进程,用kill命令终结某个进程
- 编写sql语句,尽可能去优化
- explain语句让MySQL解释它如何执行一条select语句。
- 存储过程比执行一条条sql语句要快
- 使用正确的数据类型
- 按需求检索,例如不要使用 select *
- 有的操作(包括INSERT)支持一个可选的DELAYED关键字,如果 使用它,将把控制立即返回给调用程序,并且一旦有可能就实际 执行该操作。
- 导入数据时,关闭自动提交。导入完成后重建索引。
- 使用索引改善检索性能
- sql语句有复杂的or条件,建议使用多条select语句和连接它们的union语句,性能会极大改善
- 非查询时不要用索引
- like很慢。最好使用fulltext而不是like。
- 理想的优化和配置可能需要经常改变
- 没有完美的配置规则
数据类型表
表1:串数据类型 (串值用单引号括起来)
数据类型 | 说明 |
---|---|
char | 1~255个字符的定长串。它的长度必须在创建时指定,否则MySQL 假定为CHAR(1) |
enum | 接受最多64 K个串组成的一个预定义集合的某个串 |
longtext | 与TEXT相同,但最大长度为4 GB |
mediumtext | 与TEXT相同,但最大长度为16 K |
set | 接受最多64个串组成的一个预定义集合的零个或多个串 |
text | 接受最多64个串组成的一个预定义集合的零个或多个串 |
tinytext | 与TEXT相同,但最大长度为255字节 |
varchar | 长度可变,最多不超过255字节。如果在创建时指定为VARCHAR(n), 则可存储0到n个字符的变长串(其中n≤255) |
表2 数值数据类型
数据类型 | 说明 |
---|---|
bit | 位字段,1~64位。(在MySQL 5之前,BIT在功能上等价于 TINYINT |
bigint | 整数值,支持9223372036854775808~9223372036854775807 (如果是UNSIGNED,为0~18446744073709551615)的数 |
boolean / bool | 布尔标志,或者为0或者为1,主要用于开/关(on/off)标志 |
decimal / dec | 精度可变的浮点值 |
double | 双精度浮点值 |
float | 单精度浮点值 |
int / integer | 整数值,支持2147483648~2147483647(如果是UNSIGNED, 为0~4294967295)的数 |
mediumint | 整数值,支持8388608~8388607(如果是UNSIGNED,为0~ 16777215)的数 |
real | 4字节的浮点值 |
smallint | 整数值,支持32768~32767(如果是UNSIGNED,为0~ 65535)的数 |
tinyint | 整数值,支持128~127(如果为UNSIGNED,为0~255)的数 |
注意:① 数值不适用引号
② 存储货币数据类型 使用 DECIMAL(8, 2)
表3:日期时间类型
数据类型 | 说明 |
---|---|
date | 表示1000-01-01~9999-12-31的日期,格式为 YYYY-MM-DD |
datetime | DATE和TIME的组合 |
timestamp | 功能和DATETIME相同(但范围较小) |
time | 格式为HH:MM:SS |
year | 用2位数字表示,范围是70(1970年)~69(2069 年),用4位数字表示,范围是1901年~2155年 |
表4:二进制数据类型
数据类型 | 说明 |
---|---|
blob | Blob最大长度为64 KB |
mediumblob | Blob最大长度为16 MB |
longblob | Blob最大长度为4 GB |
tinyblob | Blob最大长度为255字节 |
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之家整理,本文链接:https://www.bmabk.com/index.php/post/157329.html